68463

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

Лекция

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

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

Русский

2014-09-22

64.5 KB

12 чел.

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


 

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

31202. Сейсмические форматы и запись на магнитную ленту 30 KB
  По рекомендации SEG Общества геофизиковразведчиков в сейсморазведочных станциях в качестве стандартных для 9дорожечных магнитофонов при записи на ленту приняты следующие основные мультиплексные форматы: SEGB с длиной сейсмического слова 25 байта; SEGD с длиной сейсмического слова 4 байта. В качестве демультиплексных форматов в сейсморазведке по рекомендации SEG в настоящее время наиболее широко используются следующие: SEGD801520битный формат с длиной сейсмического слова в 25 байта; SEGD804832битный формат с длиной...
31203. Сейсморазведочная аппаратура первого поколения 30 KB
  Сейсморазведочная станция СС30 6056 конструкции 1956 года содержала уже 60 сейсмических каналов группы по 15 каналов с раздельными фильтрами НЧ и ВЧ. Она была смонтирована в виде набора блоков два блока усилителей по 12 каналов осциллограф пульт управления блок питания переносная фотолаборатория соединительные кабели. Сейсморазведочная станция СС605 содержала 60 сейсмических каналов и была первой отечественной широкодиапазонной станцией приспособленной для регистрации колебаний в диапазоне от 15 до 350 Гц.
31204. Сейсморазведочные станции с промежуточной аналоговой записью 30 KB
  Главным средством создания воспроизводимой сейсмической записи оказалась магнитная аналоговая запись. Сейсморазведочные станции этого типа состоят из двух основных частей: блока записи и блока воспроизведения. Сейсморазведочная станция СС2461М имела 24 основных канала записи и 4 вспомогательных канала для регистрации марок времени отметки моментов взрыва и вертикального времени. Использовался прямой способ записи на ленту с высокочастотным подмагничиванием.
31205. Сейсморазведочные станции с цифровой магнитной регистрацией 30 KB
  Первой отечественной цифровой сейсморазведочной станцией была станция ССЦ1 созданная в 1966 г. Сейсморазведочная станция ССЦ2 была первой отечественной цифровой сейсморазведочной станцией которая достаточно успешно и сравнительно долго 1970 1976 гг. Сейсморазведочная станция ССЦ3 была разработана институтом ВНИИГеофизика Москва при участии фирмы SERSEL Франция в 1972 году. С 1976 года выпускался модернизированный вариант станции под маркой ССЦ4.
31206. Сети наблюдений 36.5 KB
  Сейсморазведочные работы 2D проводятся для изучения строения земной коры по отдельным профилям или сети профилей с целью решения задач на региональном поисковом детальном а иногда даже на детализационном этапах геологоразведочного процесса. Цели и задачи конкретной сейсмической съемки определяются этапом геологоразведочных работ на данной территории. Как известно в России принято выделять три этапа геологоразведочных работ региональный поисковый и детальный. Исследования по отдельным протяженным профилям на региональном этапе работ...
31207. Системы записи и предварительной обработки сейсмической информации 33 KB
  С точки зрения технологии применения сейсмической разведки в главном направлении в области поисков и разведки углеводородов всю выпускаемую аппаратуру можно условно разделить на два класса: аппаратура и оборудование для исследований по отдельным профилям линиям с использованием относительно ограниченного числа каналов. В ее названии присутствует индекс Л или L ; аппаратура и оборудование для исследований на площадях достаточно больших размерив с одновременной регистрацией волнового поля большим числом каналов. Для сейсморазведочных...
31208. Системы наблюдений со сложными но форме линиями приема или возбуждения 28.5 KB
  Система наблюдений при правильном планировании может обладать хорошим распределением удалений и азимутов. Предложено и ряд систем наблюдений регулярного типа в которых используются сложные по форме линии приема ЛПП или возбуждения ЛПВ. Среди систем наблюдений такого типа следует прежде всего указать на системы типа звезда и радиальная .
31209. Суда для сейсморазведочных работ 32.5 KB
  иметь специальное радионавигационное оборудование для уверенного ведения судна по запроектированной системе сейсмических профилей; обладать достаточной автономностью плавания 30 60 суток. м в наиболее комфортной части судна. Процесс смотки и размотки сейсмических кос требует установки на корме судна в полузакрытом помещении специальных барабанов с электроприводом и емкостью размещаемых кос объемом до 10 15 м3. Кроме этого весьма важно чтобы шумы самого судна шумы двигателя были бы также достаточно малыми.
31210. Типы систем наблюдений 38.5 KB
  В сейсморазведке при исследованиях по линейным профилям наиболее часто используются следующие системы наблюдений: фланговые с пунктами возбуждения расположенными по одну сторону базы приема линии пунктов приема ЛПП на ее конце или за ее пределами фланговые с выносом; встречные фланговые с пунктами возбуждения расположенными на обоих концах базы приема ЛПП или с двух сторон за ее пределами встречные фланговые с выносом; центральные с пунктом возбуждения в центре базы приема симметричные и с пунктом возбуждения...