68463

Работа с текстом в Excel

Лекция

Информатика, кибернетика и программирование

Excel предоставляет очень много функций для работы с текстом позволяющие преобразовывать исходные текстовые данные и производить разнообразные действия. Все функции для обработки текста можно просмотреть в диалоговом окне Мастер функций кнопочка fx в категории Текстовые.

Русский

2014-09-22

64.5 KB

15 чел.

инеок Александра Борисовна       Лекция № 6   “Информационные технологии в производстве текстильных изделий”               стр. 20.

РАБОТА С ТЕКСТОМ

Excel предоставляет очень много функций для работы с текстом, позволяющие преобразовывать исходные текстовые данные и производить разнообразные действия. Все функции для обработки текста можно просмотреть в диалоговом окне Мастер функций (кнопочка  fx  ) в категории Текстовые. Для удобства работы в данной лекции функции разбиты по категориям. В мастере функций они расположены в алфавитном порядке. Кроме этого, можно просмотреть развернутую встроенную справку Microsoft Excel по каждой функции с подробными примерами. Для этого работая в мастере функций нужно вызвать справку по необходимой функции.

При открытии новой книги для всех ячеек по умолчанию выставляется тип данных – Общий. Это позволяет записать в ячейку как число, так и текст. Но для того, что бы текстовые данные обрабатывались правильно, следует перед введением данных в ячейки присвоить используемым ячейкам тестовый формат:

окно Формат  Ячеек   →   закладка Число   →   формат Текстовый.

Если выбран текстовый формат ячеек, то данные в них будут обрабатываться как текст  независимо от того, какая информация внесена в ячейки.

EXCEL трактует текст как набор отдельных символов. Текстовая ячейка может содержать до 255 символов. EXCEL поддерживает 16-битовую кодировку символов (Юникод), что позволяет использовать огромный выбор символов (смотри доп. Лекцию – Кодировка символов). Однако при записи данных чаще всего используются русские и английские буквы, цифры, символы со знаками препинания и  математические символы. Если текст располагается в ячейке, то он не ограничивается никакими символами. Если текстовые константы используются в формулах, то они ограничиваются двойными кавычками, например: = СОВПАД(А1; "Маша").

В некоторых ситуациях бывает полезно проверить тип данных, хранящихся в ячейке. Проверить данные на текст можно при помощи 4 функций:

ЕТЕКСТ(ячейка)

ЕНЕТЕКСТ(ячейка)

ТИП(ячейка)

ЯЧЕЙКА(ячейка)

Функция ЕТЕКСТ проверяет содержимое указанной ячейки и возвращает значение Истина в том случае, если в ячейке содержится текст. Функция ЕНЕТЕКСТ работает ”с точностью до наоборот“, однако эта функция также вернет значение Истина, если значение ссылается на пустую ячейку.

Функция ТИП возвращает числовое значение (код), указывающий на тип данных в ячейке. Функция ТИП используется, когда результаты вычисления другой функции. Значение - это любое допустимое значение, например, число, текст, логическое значение или адрес ячейки. Функция ТИП возвращает следующие значения:

1 – для числа,

2 – текст,

4 – логическое значение

16 – код ошибки.

Однако если в ячейке содержится более 255 символов, то функция ТИП не может определить тип данных и возвращает код ошибки – 16. Функция ЯЧЕЙКА работает аналогично функции ТИП, но возвращает сложный текстовый код, соответствующий формату, положению информации в ячейке и т.д. (для самостоятельного изучения).

Определение идентичности текстовых строк. Самый простой способ – это использование формулы. Для проверки совпадения содержимого ячеек В2 и В3 можно применить формулу: = В2 = В3. Следует учитывать, что Excel при сравнении текстовых строк не учитывает регистр символов, поэтому приведенная формула вернет значение Истина, для текстовых данных “МАША” и “Маша”, хотя эти данные не совсем идентичны. Если регистр символов имеет значение, тогда следует использовать функцию СОВПАД.

Функция СОВПАД(текст1; текст2) сравнивает две строки текста и возвращает значение Истина, если они в точности совпадают, и Ложь в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании.

Объединение содержимого ячеек. Для объединения содержимого ячеек используется символ конкатенации -.&. Например, если в ячейке А1 содержится текст Маша, а в ячейке А2 текст Миша, то  следующая формула  = А1 & А2 вернет значение МашаМиша. Для более корректного результата следует вставлять символы пробела и знаки препинания, например:

= А1 & А2 вернет значение  МашаМиша 

= А1 & ” ” & А2 вернет значение Маша Миша

= А1 & ”+” & А2 & ”!” вернет значение Маша + Миша!.

В следующем примере функция объединяет текст с максимальное значение в столбце С:

= “Максимум равен“ & МАКС(С:С).

Кроме этого для объединения содержимого ячеек можно использовать функцию СЦЕПИТЬ, которая позволяет объединить до 30 данных (ячеек). При отладке удобнее использовать функцию СЦЕПИТЬ, так как при вызове Мастера функций она позволяет сразу просмотреть результаты работы с текстом, включая промежуточные вычисления.

Примеры использования функции СЦЕПИТЬ:

Пусть в если в ячейках С1, С2 и С3 находятся фамилия имя и отчество, например Иванов Иван Иванович.

= СЦЕПИТЬ(С1; С2; С3) вернет значение  ИвановИванИванович

= СЦЕПИТЬ(С1; “  “;С2; “  “; С3) вернет значение  Иванов  Иван  Иванович

= СЦЕПИТЬ(С1; “ “;ЛЕВСИМВ(С2); “.  “; ЛЕВСИМВ(С3); “.“) вернет значение  Иванов  И.  И.

Повторение текста. Для повторения текста используется функция:

ПОВТОР(текст; количество). Например, следующая функция:

 = ПОВТОР(“Ля“;3) - вернет значения    ЛяЛяЛ.

Часто функция ПОВТОР используется для заполнения текста до конца строки определенным символом – звездочкой, точками ,символом подчеркивания или пробелами. Пусть в ячейке А1 хранится некий текст, рассмотрим примеры формул:

= А1 & ПОВТОР(” ” ;20 - ДЛСТР(А1)) – добавит пробелы к содержимому ячейки А1 так, чтобы длина текста стала 20 символов. 

= ПОВТОР(”_” ;40 - ДЛСТР(А1)) & А1 – добавит символ подчеркивания перед содержимым ячейки А1 так, чтобы длина результата стала 40 символов.

Кроме этого для заполнения пустого места можно использовать пользовательский формат.

Удаление пробелов и непечатных символов. При импорте данных  в рабочие листы из внешних источников данные часто содержат много ненужных пробелов и специальных непечатаемых символов, например символ конца строки или конца файла. Следующие символы позволяют избавиться от лишних пробелов и непечатных символов:

СЖПРОБЕЛЫ(текст). Удаляет из текста все избыточные пробелы, за исключением одиночных пробелов между словами.

ПЕЧСИМВ(текст). Удаляет все непечатаемые знаки из текста.

Подсчет количества символов в строке. При работе с формулами часто возникает необходимость знать длину строки. Для этого используется функция ДЛСТР(текст).

Изменение регистра текста. Excel имеет три функции, позволяющие изменить регистр текста, что особенно удобно при приведении импортируемых данных к единому виду.

СТРОЧН(текст) – переводит все символы текста в нижний регистр (строчные символы).

ПРОПИСН(текст) – Переводит все символы в верхний регистр (заглавные символы).

ПРОПНАЧ(текст) – делает все первые буквы слов заглавными.

Все эти функции оперируют только буквами русского или английского алфавита, все остальные символы игнорируются и остаются без изменений.

Извлечение символов из текста. Извлечение символов из текста является одной из наиболее часто встречающихся операций при работе с текстом. Например, требуется извлечь фамилию из полного имени клиента или сократить полное имя или отчество до одного символа.

ЛЕВСИМВ( текст; количество знаков) –возвращает указанное число знаков с начала текстовой строки. Количество знаков должно быть больше либо равно нулю. Если количество знаков больше длины текста, то функция ЛЕВСИМВ возвращает весь текст. Если количество знаков опущено, то возвращается один символ.

ПРАВСИМВ(текст; количество знаков) – ПРАВСИМВ возвращает заданное число последних знаков текстовой строки. Число знаков должно быть больше либо равно нулю. Если количество знаков больше длины текста, то функция ПРАВСИМВ возвращает весь текст. Если количество знаков опущено, то функция вернет последний символ строки текста.

ПСТР(текст; начало; число_знаков) – возвращает указанное число знаков из текстовой строки, начиная с указанной позиции. Первый символ в тексте имеет начальную позицию 1. Число знаков  указывает, сколько знаков требуется вернуть. Если значение начало больше, чем длина текста, то функция ПСТР возвращает пустую строку "" (пустой текст). Если начало меньше, чем длина текста, но начало плюс число знаков превышают длину текста, то функция ПСТР возвращает знаки вплоть до конца текста. Значение ошибки #ЗНАЧ! возвращается, если значение начало меньше 1 или если число знаков отрицательно.

Замена текста. В некоторых ситуациях требуется заменить часть текстовой строки другим текстом. Для того, чтобы произвести замены во всей книге удобнее всего воспользоваться командой замены:

Главное меню Правка  →   Заменить.

При этом Excel предоставит возможность либо автоматически произвести замену во всем тексте, либо вручную производить просмотр всех вхождений заменяемого текста. Для работы с формулами в Excel существуют две функции:

ПОДСТАВИТЬ(текст; старый текст; новый текст; номер вхождения). Заменяет указанный текст в текстовой строке. Эту функцию следует использовать, когда известен заменяемый текст, но неизвестно его местоположение. Текст — это либо текст, либо ссылка на ячейку, содержащую текст, в котором заменяются символы. Номер вхождения – необязательный параметр, определяющий, какое именно вхождение текста нужно заменить на новый текст. Если номер вхождения задан, то заменяется только это вхождение текста. 

ЗАМЕНИТЬ(старый текст; начало; число знаков; новый текст). Замещает часть строки другим текстом, начиная с указанной позиции. Эту функцию следует применять, когда известно местоположение заменяемого текста, но неизвестен сам текст. Старый текст - текст, в котором желательно заменить символы. Начало - позиция знака в тексте, начиная с которой знаки заменяются. Число знаков – количество заменяемых символов.

Для одновременного выполнения сразу нескольких замен можно использовать несколько функций в одной формуле.

Наиболее эффектным средством работы с текстовыми данными является изменение внешнего вида текстовых данных в зависимости от содержимого ячейки (самого текста). Добиться этого можно при помощи средства Условное форматирование. При этом в окне условного форматирования следует указывать опцию формула. Работа с формулой в окне Условного форматирования позволяет работать не только с содержимым ячейки, на которую накладывается условное форматирование, а  сослаться на содержимое нескольких ячеек, расположенных не только в разных местах, но и на других листах и книгах. Это позволяет описать разнообразные и сложные условия для выделения данных, причем выделяться будут не те ячейки, где эти данные находятся, а совсем другие ячейки, те, на которые наложены условия при помощи Условного форматирования.

Формула, записанная в условном форматировании должна возвращать логическое значение или содержать операции сравнения (<, >,<=, >=, =). Если формула возвращает значение Истина, то условное форматирование накладывается на выбранную ячейку. Если формула возвращает значение Ложь, то условное форматирование не срабатывает. При написании формул следует использовать различные типы адресации для ячеек, так как это позволит правильно размножить формулу в условном форматировании.

Примеры выделения текстовых данных при помощи Условного форматирования:

Пусть в ЕXCEL создана таблица:

В примерах указана формула для выделения ячеек в верхней строки таблицы. Ссылки в формулах (смешанные) адаптированы для размножения формулы по столбцу (вниз).

Фамилия начинается с буквы  Л : = ЛЕВСИМВ($С3) = ”Л”

Имя оканчивается на букву  Н : = ПРАВСИМВ($D3) = ”н”

Отчество оканчивается на “евич”: = ПРАВСИМВ($Е3;4) = ”евич”

Имя содержит букву я: = ПОИСК(“я”;$D3; 1) > 0

Длина имени 10 символов: = ДЛСТР($D3) = 10

Длина фамилия менее 6 символов: = ДЛСТР($С3) < 6

Имя начинается с символов “АБВ”: = ПОИСК(ЛЕВСИМВ($D3); “АБВ”; 1) > 0

Имя и отчество начинается с одной буквы:   

                                  = ЛЕВСИМВ($D3) = ЛЕВСИМВ($E3)

Отчество начинается с гласной буквы:

                                  = ПОИСК(ЛЕВСИМВ($Е3); “аеёиоуыэюя”;1) > 0

Самое короткое отчество. Для этого используем дополнительный столбец F, в котором при помощи функции ДЛСТР записываем длину отчества, хранящегося в столбце D: = ДЛСТР($D3), а затем для ячейки D3 записываем формулу в окне условного форматирования:  = $F3=МИН($F$4:$F$6)


 

А также другие работы, которые могут Вас заинтересовать

20185. ТРИГГЕРЫ. Активные и пассивные логические уровни элементов И-НЕ и ИЛИ-НЕ 207.5 KB
  Назначение триггера. Воздействуя на входы триггера его устанавливают в нужное состояние. Логический уровень на инверсном выходе представляет собой инверсию состояния триггера в состоянии 0 Q = 1 и наоборот. Приведем обозначение и назначение входов триггеров: R от английского RESET раздельный вход установки в состояние 0; S от английского SET раздельный вход установки в состояние 1; K вход установки универсального триггера в состояние 0; J вход установки универсального триггера в состояние 1; T счетный вход; D от английского...
20186. МУЛЬТИПЛЕКСОРЫ/ ДЕМУЛЬТИПЛЕКСОРЫ 74 KB
  Мультиплексор является устройством которое осуществляет выборку одного из нескольких входов и подключает его к своему выходу. Мультиплексор имеет несколько информационных входов D0 D1 . При подаче стробирующего сигнала на вход С мультиплексор выбирает один из входов адрес которого задается двоичным кодом на адресных входах и подключает его к выходу.26 Таким образом подавая на адресные входы адреса различных информационных входов можно передавать цифровые сигналы с этих входов на выход Q.
20187. ШИФРАТОРЫ/ДЕШИФРАТОРЫ 1.84 MB
  Входы дешифратора предназначаются для подачи двоичных чисел выходы последовательно нумеруются десятичными числами. В таких устройствах двоичное число поступая на вход дешифратора вызывает появление сигнала на определенном его выходе.19а приведено символическое изображение дешифратора. Рассмотрим построение дешифратора осуществляющего преобразование заданное табл.
20188. Совершенствование деятельности административно-хозяйственной службы АЗИМУТ Отель Санкт-Петербург 423.5 KB
  Рассмотреть теоретические аспекты процесса обслуживания номерного фонда и его особенностей; исследовать и проанализировать организацию деятельность административно-хозяйственной службы АЗИМУТ Отеля Санкт-Петербург, обосновать экономическую эффективность предложенных мероприятий...
20189. ЛОГИЧЕСКИЕ ФУНКЦИИ Понятие о логической функции и логическом устройстве 153 KB
  Для того чтобы подчеркнуть особенности таких функций состоящую в том что сама функция и ее аргументы могут принимать значения логического нуля и логической единицы будем эти функции называть функциями алгебры логики ФАЛ. Такое устройство является комбинационным в котором значение формируемой на выходе логической функции определяется лишь значениями ее аргументов в данный момент времени. Элементарные логические функции.
20190. ЛОГИЧЕСКИЕ ЭЛЕМЕНТЫ 1.58 MB
  1 используется напряжение двух уровней: высокий уровень соответствует лог. При этом на резистре R образуется напряжение той полярности при которой диоды в цепях остальных входов оказываются под действием запирающего напряжения. На резисторе R образуется напряжение близкое к наибольшему из напряжений действующих на входах. В момент подачи на вход импульса изза емкости Сэк напряжение на выходе не может возрасти скачком; оно растет по экспоненциальному закону с постоянной времени так как Rвых R стремясь к значению Uвх R R Rвых.
20191. РЕГИСТРЫ 43.5 KB
  Основная выполняемая регистром функция заключается в хранении одного многоразрядного числа. Регистр строится в виде набора триггеров каждый из которых предназначается для хранения цифр определенного разряда двоичного числа. Таким образом регистр для хранения nразрядного двоичного числа должен содержать n триггеров. Регистры могут использоваться для выполнения и некоторых других функций: сдвиг хранимого в регистре числа на определенное число разрядов влево или вправо преобразование числа из последовательной формы при которой оно...
20192. СИНТЕЗ КОМБИНАЦИОННЫХ УСТРОЙСТВ. Канонические формы представления логических функций 396.5 KB
  Для начального представления функции обычно используется базис И ИЛИ НЕ независимо от того какой базис будет использоваться для построения логического устройства. Дизъюнктивной нормальной формой ДНФ называется такая форма представления функции при которой логическое выражение функции строится в виде дизъюнкции ряда членов каждый из которых является простой конъюнкцией аргументов или их инверсий. Также не является ДНФ следующая форма представления функции: Если в каждом члене ДНФ представлены все аргументы или их инверсии функции то...
20193. Природные ресурсы и их рациональное использование 60 KB
  По назначению ресурсы делятся на четыре группы: Пищевые. По исчерпаемости ресурсы делятся на исчерпаемые и неисчерпаемые. Космические ресурсы – это солнечное излучение энергия приливов и отливов и т.