68463

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

Лекция

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

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

Русский

2014-09-22

64.5 KB

16 чел.

инеок Александра Борисовна       Лекция № 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)


 

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

40072. Технология расчета контрольного числа для кодов классификаторов технико-экономической и социальной информации 45.5 KB
  Рассмотреть методы расчета контрольного числа для кодов для кодов классификаторов ТЭСИ. Приобрести навыки расчета контрольного числа для кодов классификаторов ТЭСИ. Обеспечивающие средства: методика расчета контрольного числа перечни кодов.
40073. Лингвистическое обеспечение сайтов 63 KB
  Приложение 1 Перечень сайтов библиотек и информационных учреждений подлежащих анализу Российская государственная библиотека www.ru Российская национальная библиотека www.ru Государственная публичная научнотехническая библиотека России www.ru Государственная публичная научнотехническая библиотека Сибирского отделения Российской Академии наук Библиотека по естественным наукам РАН www.
40074. Классификаторы технико-экономической и социальной информации как ИПЯ 70.5 KB
  Охарактеризовать классификаторы ТЭСИ как ИПЯ. Овладеть навыками кодирования с помощью классификаторов ТЭСИ. Определить возможности использования классификаторов ТЭСИ при упорядочении объектов и сферу их применения. Обеспечивающие средства: классификаторы ТЭСИ ББК ГРНТИ УДК Библионорматив машиночитаемый вариант УДК ГРНТИ первичные...
40075. Алфавитно-предметная классификация как ИПЯ 69.5 KB
  Приобрести навыки формулирования предметных рубрик 2. Определить возможности использования АПК Обеспечивающие средства: первичные и вторичные документы; перечни лексических единиц; приложение Правила формулирования предметных рубрик. Задание 1: Определить вид заданных предметных рубрик Требования к отчету: Итоги выполнения задания представить в виде таблицы 1: Таблица 1. Виды предметных рубрик Предметная рубрика Вид и подвид предметной рубрики Технология работы: Проанализировать структуру заданной...
40076. Сфера применения дескрипторных информационно-поисковых языков 56 KB
  Оценить возможности использования дескрипторных ИПЯ при индексировании документов и запросов. Обеспечивающие средства: дескрипторные словари отраслевые информационнопоисковые тезаурусы перечни ключевых слов. Определить необходимый для проведения операции координатного индексирования отраслевой информационнопоисковый тезаурус или дескрипторный словарь.
40077. Дескрипторные информационно-поисковые языки 63.5 KB
  Охарактеризовать дескрипторные словари и информационно поисковые тезаурусы как ИПЯ. Обеспечивающие средства: дескрипторные словари отраслевые информационнопоисковые тезаурусы перечни ключевых слов. Задание 1: Охарактеризовать дескрипторные словари и информационнопоисковые тезаурусы как информационнопоисковый язык.
40078. Технология уплотнения сигнала в волоконно-оптических линиях связи 49.08 KB
  При подходе под названием мультиплексирование по длине волны Wvelength Division Multiplexing WDM свет с разными длинами волн от нескольких лазеров передается по одному световоду. WDM работает следующим образом. WDM разбивает оптический спектр на каналы каждый с различной длиной волны. Организация потока данных в WDM.
40079. Радиолинии и системы передачи сообщений с радиоканалами 45.28 KB
  Антенны подключаются к приемопередающему оборудованию при помощи фидерных трактов Ф. Пространственная избирательность достигается за счет использования антенны обеспечивающей прием нужных радиосигналов с одного направления и ослабление радиосигналов с других направлений от посторонних источников. Антенны и фидеры Антенна представляет собой элемент сопряжения между передающим или приемным оборудованием и средой распространения радиоволн. Антенны имеющие вид проводов или поверхностей обеспечивают излучение электромагнитных колебаний при...
40080. Принципы построения радиорелейных (РРЛ) и спутниковых систем связи (ССС) 38.88 KB
  Цепочку радиорелейной линии составляют радиорелейные станции трех типов: оконечные радиорелейные станции ОРС промежуточные радиорелейные станции ПРС узловые радиорелейные станции УРС.1 Радиорелейная линия связи На оконечной радиорелейной станции начинается и заканчивается тракт передачи. Аппаратура ОРС осуществляет преобразование сигналов поступающих от разных источников информации телефонные сигналы от междугородней телефонной станции телевизионные сигналы от междугородней телевизионной аппаратной и т. Радиосигналы ОРС с помощью...