Текстові функції

В комірку можна ввести число, формулу, текст. Число і формулу можна перетворити в текстовий рядок: натиснути функціональну клавішу F2 (редагування), поставити курсор в крайню ліву позицію (клавіша Home) і ввести одинарну лапку (‘).

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

1) Введіть в комірку А1 – Іванов, в комірку В1 – Іван, в комірку С1 – Іванович. В комірці А2 необхідно отримати рядок Іванов Іван Іванович. Для цього в А2 вводиться формула =A1&“ ”&B1&“ ”&C1. Знак & (амперсенд) – символ операцію склеювання (конкатенації) рядків. Зверніть увагу, що між рядками, взятими із комірок, вставлені пропуски “ ”.

2) Перетворіть вміст комірки А2 із формули в значення. Це можна зробити декількома способами. Найпростіший: розмістити курсор на рамку комірки А2 (він прийме вигляд стрілки), натиснути праву кнопку миші і, не відпускаючи її, перетягнути А2 вбік і тут же повернути на місце, відпустити праву кнопку миші (з’явиться контекстне меню), вибрати пункт Копировать только значения.

3) Відредагуйте вміст комірки А2, встановивши декілька додаткових пропусків до, після і між словами. В комірку А3 введіть формулу =СЖПРОБЕЛЫ(А2). Декілька пропусків перетворяться в один, початкові і кінцеві пропуски щезнуть.

4) Потрібно вміст комірки А3 рознести в три комірки: окремо прізвище, ім’я, по батькові. Це складна задача, тому розв’язувати її потрібно поетапно, зібравши потім одну формулу.

Знайдемо номер позиції, відповідний першому пропуску. В комірку А4 внесемо формулу =ПОИСК(“ ”, А3). В комірку В4 введемо формулу =ПОИСК(“ ”, А3, А4+1), тобто ми шукаємо в рядку пропуск не з першої позиції, а з позиції, що йде після першого пропуску. В комірці С4 обчислимо довжину рядка =ДЛСТР(А3). Повинні отриматись результати: 7, 15, 23.

Тепер для виділення підрядів скористаємось функцією ПСТР(текст, нач_номер, число_литер) – із рядка, починаючи із заданої позиції, витягається підрядок заданої довжини. Розмістимо в А5, А6, А7 рядки Прізвище, Ім’я, По батькові відповідно. Запишемо в В5 формулу =ПСТР(А3, 1, А4-1), в В6 =ПСТР(А3, А4+1, В4-А4-1), в В7 =ПСТР(А3, В4+1, С4-В4). Проаналізуйте аргументи цих функцій. Запишіть в С5:С7 формули з використанням вкладених функцій (не враховуючи проміжних комірок).

5) Підставте в комірку А2 інший рядок: Новиков Геннадій Павлович. Наші формули акуратно витягають прізвище, ім’я, по батькові. А тепер введіть Сейфетдинова Наіля. В четвертому рядку отримуємо результат:

13

#ЗНАЧ!

18

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

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

ПСТР(А3, А4+1, С4-А4),

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

Ця формула розташована в декілька рядків для збільшення її читабельності. Для отримання такого вигляду формули у вікні введення, натисніть Alt+Enter в точках формули, де має бути перехід на наступний рядок. На початку другого і третього рядків введіть серію пропусків, що забезпечать вирівнювання частин формули.

А в комірку В7 введемо формулу:

=ЕСЛИ(ЕОШ(В4), “”, ПСТР(А3, В4+1, С4-В4))

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

Перетворення тексту в числовий формат

Введіть в комірку А3 число 12, в комірку В3 число 14, а в комірку В4 формулу =А3&В3. Ця формула поверне текстове значення 1214. Спробуємо перетворити його в число за допомогою функції Ч(значение): в В5 помістимо формулу =Ч(В4). Вона поверне 0. Для перетворення вмісту комірки В4 в число введемо в В6 формулу =В4*1. Ця формула поверне число 1214. Щоб виконати множення, Excel намагається перетворити результат в комірці В4 в число, і робить це.

Нехай в діапазоні А8:А10 записані текстові рядки: ’10, ’12, ’15. Як швидко перетворити їх в числа? Введіть в В8 число 1; виконайте над цією коміркою команду Копировать; виділіть блок А8:А10; в контекстному меню оберіть Специальная вставка; в діалоговому вікні встановіть перемикач умножить, натисніть ОК, натисніть Esc.

Приклад. Дані в стовпчику А (починаючи з комірки А1 мають вигляд):

123 АБВГД

5ZBNWQ

84Том

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

В комірку В1 введіть формулу:

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

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

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

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

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

Ця задачу неможливо розв’язати з використанням Майстра текстів, так як у даних в рядках немає фіксованої довжини і немає роздільників.