ПРИМЕР 1. Операции над текстовыми строками.

Текстовые функции

Дайте первому рабочему листу имя "Строки". Познакомьтесь с функциями для работы со строками, которые приходится очень часто использовать при работе с данными, импортируемыми из текстовых баз данных. В программе Excel эти функции вызываются через «Мастер функций» из категории “Текстовые”.

ЗНАЧЕН(<текст>) – превращает текстовое отображение числа в числовое.

Пример: ЗНАЧЕН("1232,56")= 1232,56.

ТЕКСТ(<число>; <формат>) – преобразует <число> в текст, учитывая заданный <формат> представления.

Примеры: ТЕКСТ(2,715;"00,00")="02,72",

ТЕКСТ("25.3.98";"ДДДД")="Среда".

Число и формулу можно превратить в текстовую строку также следующим образом: нажать функциональную клавишу F2 (редактирование), поставить курсор в крайнюю левую позицию (клавиша Ноmе) и ввести одиночную кавычку (она расположена на клавише с буквой "Э"). Например, введите в Е1 число 12 (число выровнено по правому краю), превратите его в текстовую строку (число будет выровнено по левому краю).

ФИКСИРОВАННЫЙ(<число>[;<количество десятичных знаков>])

– функция округляет <число> до заданного количества знаков и преобразует результат в текстовую форму. Если второй аргумент опущен, он полагается равным двум.

Пример: ФИКСИРОВАННЫЙ(12345,678; 2)="12345,68",

ФИКСИРОВАННЫЙ(12345,678; -3)="12000".

ДЛСТР(<текст>)

– возвращает длину текстовой строки.

Пример: ДЛСТР("Клавиша Enter")=13.

СЖПРОБЕЛЫ(<текст>)

– функция удаляет из текстовой строки концевые пробелы и уменьшает число пробелов внутри строк (если есть) до одного.

Примеры: СЖПРОБЕЛЫ(" 1 Мая ")= "1 Мая",

СЖПРОБЕЛЫ(" Пере ")&"кресток"= "Перекресток".

СОВПАД(<текст1>;<текст2>) – проверяет совпадают ли два аргумента. Если да, результатом является ИСТИНА, иначе – ЛОЖЬ. Здесь подразумевается полное совпадение, включая содержание и длину. Прописные и строчные буквы считаются разными.

Примеры: СОВПАД("Саша";"Саша")=ИСТИНА,

СОВПАД("Саша";"саша")=ЛОЖЬ,

СОВПАД("Саша";"Саша ")=ЛОЖЬ.

НАЙТИ(<искомый текст»; <текст, где ведется поиск>[;<начальная позиция;»])

– возвращает номер позиции, где <искомый фрагмент> найден в просматриваемом тексте. Необязательный параметр <начальная позиция> задает номер в искомом фрагменте, с которого следует вести поиск. Если параметр <начальная позиция> опущен, он считается равным 1 (поиск ведется с первого символа).

Пример: НАЙТИ("Петропавловский";"павлов")=6.

поиск(<искомый текст>;<текст, где ведется поиск> [;<начальная позиция>])

– функция аналогична предыдущей, однако допускает включение в <искомый текст> символов шаблона (? обозначает один произвольный символ и * обозначает любое число произвольных символов). Применение шаблона удобно при поиске по неточно заданному ключу.

Например, вы хотите выяснить факт нахождения в клетке С12 фамилии, начинающейся на букву И с инициалами А.В. Функция

ПОИСК("И*А.В";С12)

вернет значение 1, если в анализируемой ячейке находится, например, текст Ивановский А.В. и значение ошибки #ЗНАЧ!, если поиск неуспешный.

ПРАВСИМВ(<текст>;<длина фрагмента>)

– функция извлекает с правой стороны <текста> фрагмент заданной <длины>.

Пример: ПРАВСИМВ("в нашем случае";6)= "случае".

ЛЕВСИМВ(<текст>;<длина фрагмента>) – функция извлекает с левой стороны <текста> фрагмент заданной <длины>.

Пример: ЛЕВСИМВ("в нашем случае";7)= "в нашем".

ПСТР(<текст>;<номер первого символа>;<длина фрагмента>)

– функция извлекает из текстовой строки фрагмент, начиная с заданного <номера> указанной <длины>. Отсчет номера позиции ведется слева направо. Если <длина> оказалась больше, чем остаток текста, результатом явится вся оставшаяся часть текста.

Пример: ПСТР("в нашем случае";3;5)= "нашем".

СЦЕПИТЬ(<текст1>;<текст2>; ...)

– возвращает строку, образованную соединением всех перечисленных аргументов. Действие функции аналогично применению оператора &.

Пример: СЦЕПИТЬ("Саша ";"Петров")="Саша Петров".

СТРОЧН(<текст>) – преобразует буквы <текста> в строчные.

Пример: СТРОЧН("А. Петров")="а. петров".

ПРОПИСН(<текст>) – преобразует буквы <текста> в прописные.

Пример: ПРОПИСН("А. Петров")= "А. ПЕТРОВ".

Повторите нижеприведенные примеры и выполните упражнения и задачи.

ПРИМЕР 1. Операции над текстовыми строками.

1. Введите в ячейку А1 — "Иванов", в ячейку В1 — "Иван", в ячейку С1 — "Иванович" (кавычки не вводить!). В ячейке А2 нужно получить строку "Иванов Иван Иванович". Для этого введем в А2 формулу =А1&" "&В1&" "&C1.[1] Обратите внимание, что между строками, взятыми из ячеек, вставлены пробелы " ". Введите в ячейку В1 имя "Николай". Изменится и А2.

2. Превратите содержимое ячейки А2 из формулы в значение. Это можно сделать разными способами. Проще всего так: поместить курсор мыши на рамку ячейки А2 (он приобретает вид. стрелки), нажать правую кнопку мыши и, не отпуская ее, перетащить А2 в сторону и тут же вернуть на место, отпустить правую кнопку мыши (появится контекстное меню), выбрать пункт "Копировать только значения".

3. Отредактируйте содержимое ячейки А2, вставив по несколько дополнительных пробелов до, после и между словами. В ячейку A3 введите формулу =СЖПРОБЕЛЫ(А2). Несколько пробелов преобразуются в один, начальные и конечные пробелы исчезают. Этот прием может быть полезен при импортировании данных из какой-либо внешней базы данных. При этом часто приходится удалять лишние пробелы.

4. Требуется содержимое ячейки A3 разнести в три ячейки: отдельно фамилию, имя и отчество. Это довольно сложная задача, поэтому будем решать ее по частям, а затем соберем эти части в одну формулу.

Найдем номер позиции, соответствующий первому пробелу. В ячейку А4 внесем формулу =ПОИСК(" ",АЗ).[2] В нашем примере искомая подстрока — пробел " ", строка, где производится поиск, расположена в ячейке A3, третий аргумент не указан, поэтому поиск ведется с первой позиции. В ячейку В4 введем формулу =ПОИСК(" ",АЗ,А4+1), т.е. мы ищем в строке пробел не с первой позиции, а с позиции, следующей за первым пробелом. В ячейке С4 вычислим длину строки =ДЛСТР(А3). Должны получиться результаты: 7,15, 23.

Теперь для выделения подстрок воспользуемся функцией ПСТР(текст, нач_номер, число_литер) — из строки, начиная с заданной позиции, извлекается подстрока заданной длины. Поместим в А5, А6, А7 строки "Фамилия", "Имя", "Отчество". Запишем в 85 формулу =ПСТР(АЗ,1,А4-1), в В6 =ПСТР(АЗ,А4+1,В4-А4-1), в В7 =ПСТР(АЗ,В4+1,С4-В4).

Проанализируйте аргументы этих функций. Запишите в С5:С7 формулы с использованием вложенных функций (не привлекая промежуточных ячеек).

5. Подставим в ячейку А2 другую строку: "Новиков Геннадий Павлович". Наши формулы аккуратно извлекают фамилию, имя и отчество. А теперь введем: "Сейфетдинова Наиля". В четвертой строке получаем результат, показанный на рис. 1.

#ЗНАЧ!

Рис.1

Функция ПОИСК не нашла второго пробела. Как предусмотреть такой случай? Для этого воспользуемся функцией ЕОШ (Если ОШибка), которая возвращает значение ИСТИНА, если ее аргумент — адрес ячейки, в которой содержится ошибочное значение. Запишем в В6 формулу

=ЕСЛИ(ЕОШ(В4),ПСТР(АЗ,А4+1,С4-А4), ПСТР(АЗ, А4+1.В4-А4-1)).

а вВ7 запишем формулу

=ЕСЛИ(ЕОШ(В4),"",ПСТР(АЗ,В4+1,С4-В4)).

Формула в В6 имеет слишком громоздкий вид. Ее читабельность можно увеличить, если расположить ее на нескольких строках.

=ЕСЛИ(ЕОШ(В4),

ПСТР(АЗ.А4+1,С4-А4), ПСТР(АЗ.А4+1, В4-А4-1)).

Чтобы получить такой вид формулы в окне ввода, введите Alt+Enter в точках формулы, где должен быть переход на следующую строку (Enter нажимать нельзя — это завершение ввода). В начале второй и третьей строк введите серию пробелов, которые обеспечат выравнивание частей формулы.

6. Задания в пунктах 4 и 5 можно выполнить, не применяя формул, а воспользовавшись средствами, предоставляемыми Excel. Поместите в ячейку А10 строку "Иванов Иван Иванович". Выберите пункт меню "Данные/ Текст по столбцам". Начнет работу Мастер текстов. На первом шаге укажите, что текст "с разделителями", на втором шаге укажите, что разделителем является пробел. Третий шаг можно не выполнять и сразу нажать "Готово". В результате текст будет разбит на три ячейки А10, В10, С10.

ЗАДАЧА 1.

В А10, В10, С10 помещены текстовые строки "Иванов", "Иван", "Иванович". Требуется разместить в D10 формулу, которая выдаст результат "Иванов И.И.". (Вместо функции ПСТР здесь проще воспользоваться функцией ЛЕВСИМВ.)

ЗАДАЧА 2.

Скорректировать формулу для предыдущей задачи, чтобы формула правильно работала для исходных данных:

в ячейке А10 — "Сейфетдинова", в В10 — "Наиля", в С10— пусто. (Воспользуйтесь функцией ЕПУСТО.)

ЗАДАЧА 3.

В столбце А расположено несколько инвентарных номеров (рис. 2).

255-377-421

34-442-37

7583-81-344

Рис.2

В столбец В поместить цифры, размещенные после второго дефиса. Сделать это двумя способами:

1) с использованием функций работы с текстовыми строками;

2) с использованием пункта меню "Данные/ Текст по столбцам".

ЗАДАЧА 4.

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

Упражнение 1. По Справке изучите различия между функциями ПОИСК и НАЙТИ. Придумайте примеры, на которых проявляются эти различия.

Функции ПОИСК и НАЙТИ отыскивают позицию первого вхождения заданной подстроки в строку. Если точно известно количество вхождений подстроки, можно найти позицию и второго, и третьего вхождения, как это сделано в примере. К сожалению, среди функций Excel отсутствует функция, которая подсчитывает количество вхождений подстроки в строку. Но такую функцию можно написать на языке VBA.

ЗАДАЧА 5.

Ранее Вы решали задачу о вычислении суммы цифр трехзначного целого числа. Еще раз решите эту задачу, но с использованием текстовых функций. (Формула должна подсчитывать сумму цифр также для двузначных и однозначных чисел.)

Указание. Воспользуйтесь функцией ТЕКСТ для преобразования числа в строку.

Пример 2.

Что произойдет, если воспользоваться операцией конкатенации (сцепки) строк для чисел? Введите в ячейку F3 число 12, в ячейку G3 число 14, а в ячейку G4 формулу = F3 & G3. Эта формула вернет текстовое значение 1214. Попытаемся превратить его в число с помощью функции Ч(значение): в G5 поместите формулу =Ч(G4). Она вернет 0. Неудача! Как же преобразовать содержимое ячейки G4 к числу? Здесь поможет следующий трюк: введите в ячейку G6 формулу =G4*1. Эта формула вернет число 1214. Чтобы выполнить умножение. Excel пытается преобразовать результат в ячейке G4 в число; это ему удается.

Этот прием полезно запомнить. Пусть в диапазоне F8:F10 записаны текстовые строки; '10, '12 и '15, Как быстро преобразоватьих в числа? Введите в ячейку G8 число 1; выполните над этой ячейкой команду "Копировать" (Ctrl+C); выделите блок F8:F10; в контекстном меню выберите "Специальная вставка"; в диалоговом окне установите переключатель "умножить", нажмите "ОК", нажмите Esc.

Перейдите на новый рабочий лист.

Пример 3.

Данные в столбце А (начиная с ячейки А1) имеют вид:

АБВГД

ZBNWQ

Том

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

Решение. В ячейку В1 введите формулу

=ЕСЛИ(ЕЧИСЛО(ПСТР(А1,1,3)*1),

СЖПРОБЕЛЫ(ПРАВСИМВ(А1 ,ДЛСТР(А1) -3)),

ЕСЛИ(ЕЧИСЛО(ПСТР(А1,1,2)*1),

СЖПРОБЕЛЫ(ПРАВСИМВ(А1 ,ДЛСТР(А1) -2)),

СЖПРОБЕЛЫ(ПРАВСИМВ(А1 ,ДЛСТР(А1) -1))))

Тщательно разберите эту формулу. Если назначение какой-либо из функций Вам непонятно, обратитесь к Справке. Обратите внимание, что эту задачу невозможно решить с использованием Мастера текстов, так как у данных в строках нет фиксированной ширины и нет разделителей, о

ЗАДАЧА 6.

Пусть в ячейке содержится возраст человека в годах (целое число — количество полных лет). В соседней ячейке выводить текстовую строку: возраст с указанием "год", "года", "лет" (например, "42 года"; заметьте, что в возрасте от 11 до 14 нужно добавлять "лет"),

Упражнение 2. Самостоятельно изучите по Справке функцию СОВПАД. Предложите примеры ее использования.

ЗАДАЧА 7.

В ячейках блока А2:А10 содержатся строки цифр, разделенные запятыми (например, в А2 строка "1,2,4", в A3 строка" 1,3,6" и т.д.). В В1 пользователь вводит цифру, допустим 2. Поместить в В2:В10 формулы, которые возвращают строки "есть" или "нет" в зависимости от того, имеется ли в строке из соответствующей ячейки столбца А цифра из В1 или нет (в В2 будет выведено "есть", а в В3 — "нет"). В В11 сосчитать количество ячеек блока А2:А10, содержащих искомую цифру.

[1] & (амперсенд) — символ операции склейки (конкатенации) строк.

[2] Прочитайте описание этой функции в Справке.