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

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

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

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

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

Найдем номер позиции, соответствующий первому пробелу. В ячейку А4 внесем формулу =ПОИСК(" ",АЗ).³ В нашем примере искомая подстрока — пробел " ", строка, где производится поиск, расположена в ячейке 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.

13

#ЗНАЧ!

18

Рис.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.

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

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