Текстові функції в excel опис і приклади

Всі знають, що табличний процесор Microsoft Excel призначений для обробки числової інформації. Його функціонал дозволяє обробляти великі обсяги обчислюються даних. А адже з його допомогою можна обробити і текст. Це можуть бути прізвища, імена, по батькові співробітників, поштові адреси і багато іншої текстової інформації, записаної в осередку таблиць.

Різні сервіси інтернету дозволяють здійснювати імпорт даних в форматі електронних таблиць і не завжди ці дані зручні для подальшої обробки. А якщо потрібно скорегувати текст на сотнях рядків, то без текстових функцій не обійтися. У цій статті я пропоную вам ознайомитися з текстовими функціями в Excel і розібратися на прикладах з їх використанням.

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

Спочатку розглянемо основні текстові функції, які використані в прикладах нижче. Excel використовує російські назви функцій на відміну від свого найближчого аналога LibreOffice.Calc. Нижче наведено формат запису текстової функції і її дію.

СЦЕПИТЬ (текст1; [Текст2]; …) – з’єднує кілька текстових рядків з різних осередків в одну. ЛЕВСИМВ (текст; [чісло_знаков]) – виводить перший символ або задане число перших символів з текстового рядка. ШУКАТИ (іскомий_текст; просматріваемий_текст; [нач_позіція]) – знаходить позицію початку входження шуканого тексту в заданій текстовому рядку з урахуванням регістру. ПОШУК (іскомий_текст; просматріваемий_текст; [начальная_позіція]) – те ж саме, як і Знайти, але без урахування регістру. ПСТР (текст; начальная_позіція; чісло_знаков) – виводить заданий число символів з рядка тексту, починаючи з вказаної позиції. ДЛСТР (текст) – вважає кількість знаків у текстовому рядку.

Завдання 1. Об’єднання текстових рядків

Є список співробітників. Прізвища, імена та по батькові рознесені в окремі стовпці. Необхідно об’єднати дані в один стовпець.

Рішення. Завдання досить проста і для її реалізації скористаємося функцією СЦЕПИТЬ.

У осередок D1 запишемо формулу = СЦЕПИТЬ (A1; “”; B1; “”; C1). Можна скористатися майстром функцій.

Далі скопіюємо її на весь необхідний діапазон стовпця D.

Якщо в функції вказати тільки адреси осередків, то текст склеїться в одну довгу рядок. Тому додаємо пробіл в лапках між адресами осередків.

Подивіться на малюнок нижче. Результат перетворення в стовпці D.

Вікно майстра функції СЦЕПИТЬ

Завдання 2. Поділ текстових рядків

Після імпорту даних співробітників їх прізвища, імена та по батькові виявилися в одному стовпці. Необхідно розділити дані за стовпцями.

Рішення. Завдання складніше попередньої і для її реалізації знадобиться кілька текстових функцій.

Для відділення прізвища співробітника і запишемо в клітинку B1 формулу

= ЛЕВСИМВ (A1; ШУКАТИ ( “”; A1))

У цій формулі кількість виведених знаків підраховується функцією знайти, яка визначає місцезнаходження першого пробілу в рядку.

Рядок формул при поділі ПІБ

Для запису імені в клітинку C1 запишемо наступну формулу

= ПСТР (A1; ШУКАТИ ( “”; A1) +1; ПОШУК ( “”; A1; ШУКАТИ ( “”; A1) +1) -Знайти ( “”; A1) -1)

Якщо подивитися на синтаксис запису даної функції, то отримуємо:

  1. вираз ШУКАТИ ( “”; A1) +1 відповідає пошук позиції першого пробілу в текстовому рядку. А щоб отримати позицію першої літери імені, додається одиниця.
  2. Для визначення кількості символів в імені використовується конструкція складніше  ПОШУК ( “”; A1; ШУКАТИ ( “”; A1) +1) -Знайти ( “”; A1) -1. Кількість символів визначається як різниця позицій прогалин, що відокремлюють ім’я. Щоб виключити з числа знайдених символів самі прогалини, в початковій позиції додається одиниця, а потім з отриманого результату віднімається одиниця.

По батькові виходить в осередку D1 по більш складною формулою

= ПСТР (A1; ПОШУК ( “”; A1; ШУКАТИ ( “”; A1) +1) +1; ДЛСТР (A1) -Пошук ( “”; A1; ШУКАТИ ( “”; A1) +1) +1)

Тут кількість знаків в батькові визначається як різниця загальної кількості символів (ДЛСТР) і позицією другого пробілу.

У розглянутих прикладах функції ПОШУК і ШУКАТИ виконують однакові операції, так як різниця в регістрах символів не враховується. Можна обійтися тільки однією з них.

Завдання 3. Як обрізати текстових рядків

У список співробітників внести зміни. Записати в одному стовпці Прізвища та ініціали.

Рішення. Залежно від вихідного стану списку можливі два варіант
и.

1 варіант. Вихідні дані містяться в одному стовпці. ПІБ розділені одинарним пропуском.

Записуємо наступну формулу

= СЦЕПИТЬ (ЛЕВСИМВ (A1; ШУКАТИ ( “”; A1)); ПСТР (A1; ШУКАТИ ( “”; A1); 2); “.”; ПСТР (A1; ШУКАТИ ( “”; A1; ШУКАТИ ( “” ; A1) +1); 2); “.”)

Перетворимо ім’я та по батькові в ініціали (вихідні дані в одному стовпці)

2 варіант. Вихідні дані містяться в різних стовпчиках.

Формула для перетворення

= СЦЕПИТЬ (A1; “”; ЛЕВСИМВ (B1); “.”; ЛЕВСИМВ (C1); “.”)

Перетворимо ім’я та по батькові в ініціали (вихідні дані в різних стовпчиках)

Друзі, якщо ви можете привести свої приклади використання текстових функцій в Excel, розкажіть про них в коментарях. Рекомендую подивитися статтю про побудову графіків функцій в Excel. Сподобалася стаття, поділіться з друзями. Кнопочки соціальних мереж внизу.

Ссылка на основную публикацию