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

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

Дайте первому рабочему листу имя " Строки".

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

ПРИМЕР 6.1.

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

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

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

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

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

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

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

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

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

аккуратно извлекают фамилию, имя и отчество. А теперь введем: " Сейфетдинова Наиля". В

четвертой строке получаем результат, показанный на рис. 6.1.

13! #3HAЧ18
Рис.6.1

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

=ЕСЛИ(ЕОШ(В4), ПСТР(АЗ, А4-И, С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 строку " Иванов Иван Иванович". ] Выберите пункт меню " Данные/ Текст по столбцам". Начнет 1 работу Мастер текстов. На первом шаге укажите, что текст " с разделителями", на втором шаге укажите, что разделителем яв­ляется пробел. Третий шаг можно не выполнять и сразу нажать 1 " Готово". В результате текст будет разбит на три ячейки А10, В10, С10.

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

ЗАДАЧА 6.2, Скорректировать формулу для предыдущей задачи, чтобы формула правильно работала для исходных данных: \ в ячейке А10 — " Сейфетдинова", в В10 — " Найдя", в С10 — пусто. (Воспользуйтесь функцией ЕПУСТО.)

ЗАДАЧА 6.3. В столбце А расположено несколько инвентар­ных номеров (рис. 6.2),

В столбец В поместить цифры, размещены после

второго дефиса. Сделать это двумя способами:

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

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

Текст по столбцам". р_нс. 6.2

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

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

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

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

чисел.)

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