Функції для роботи з текстом в excel
Для зручності роботи з текстом в Excel існують текстові функції. Вони полегшують обробку відразу сотень рядків. Розглянемо деякі з них на прикладах.
Приклади функції ТЕКСТ в Excel
Найкорисніша можливість функції ТЕКСТ - форматування числових даних для об'єднання з текстовими даними. Без використання функції Excel «не розуміє», як показувати числа, і перетворює їх в базовий формат.
Покажемо на прикладі. Припустимо, потрібно об'єднати текст в рядках і числові значення:
Використання амперсанда без функції ТЕКСТ дає «неадекватний» результат:
Excel повернув порядковий номер для дати і загальний формат замість грошового. Щоб уникнути подібного результату, застосовується функція ТЕКСТ. Вона форматує значення за завданням користувача.
Формула «для дати» тепер виглядає так:
Другий аргумент функції - формат. Де брати рядок формату? Клацаємо правою кнопкою миші по комірці зі значенням. Натискаємо «Формат ячеек». У вікні вибираємо «всі формати». Копіюємо потрібний в рядку «Тип». Вставляємо скопійоване значення в формулу.
Наведемо ще приклад, де може бути корисна ця функція. Додамо нулі на початку числа. Якщо ввести вручну, Excel їх видалить. Тому введемо формулу:
Якщо потрібно повернути колишні числові значення (без нулів), то використовуємо оператор «-»:
Зверніть увагу, що значення тепер відображаються в числовому форматі.
Функція поділу тексту в Excel
Окремі текстові функції і їх комбінації дозволяють розподілити слова з одного осередку в окремі осередки:
При поділі тексту в рядку враховується стан кожного знака. Прогалини показують початок або кінець шуканого імені.
Розподілимо за допомогою функцій ім'я, прізвище та по батькові в різні стовпці.
У першому рядку є тільки ім'я і прізвище, розділені пропуском. Формула для отримання імені: = ЛЕВСИМВ (A2; ПОШУК ( ""; A2; 1)). Для визначення другого аргументу функції ЛЕВСИМВ - кількості знаків - використовується функція ПОШУК. Вона знаходить пробіл в осередку А2, починаючи зліва.
Формула для вилучення прізвища:
За допомогою функції ПОШУК Excel визначає кількість знаків для функції ПРАВСИМВ. Функція ДЛСТР «вважає» загальну довжину тексту. Потім віднімається кількість знаків до першого пробілу (знайдене пошуку).
Другий рядок містить ім'я, по батькові та прізвище. Для імені використовуємо таку ж формулу:
Формула для вилучення прізвища дещо інша: Це п'ять знаків праворуч. Вкладені функції ПОШУК шукають другий і третій прогалини в рядку. ПОШУК ( ""; A3; 1) знаходить перший пробіл зліва (перед батькові). До знайденому результату додаємо одиницю (+1). Отримуємо ту позицію, з якою будемо шукати другий пробіл.
Частина формули - ПОШУК ( ""; A3; ПОШУК ( ""; A3; 1) +1) - знаходить другий пробіл. Це буде кінцева позиція по батькові.
Далі із загальної довжини рядка віднімається кількість знаків з початку рядка до другого пробілу. Результат - число символів справа, які потрібно повернути.
Формула «для батькові» будується за тими ж принципами:
Функція об'єднання тексту в Excel
Для об'єднання значень з декількох осередків в один рядок використовується оператор амперсанд () або функція СЦЕПИТЬ.
Наприклад, значення розташовані в різних стовпчиках (осередках):
Ставимо курсор в осередок, де будуть знаходитися об'єднані три значення. Вводимо одно. Вибираємо перший осередок з текстом і натискаємо на клавіатурі . Потім - знак пробілу, укладений в лапки ( ""). знову - . І так послідовно з'єднуємо осередки з текстом і прогалини.
Отримуємо в одній комірці об'єднані значення:
Використання функції СЦЕПИТЬ:
За допомогою лапок у формулі можна додати в кінцеве вираз будь-який знак або текст.
Функція ПОШУК тексту в Excel
Функція ПОШУК повертає початкову позицію шуканого тексту (без урахування регістру). наприклад:
Функція ПОШУК повернула позицію 10, тому що слово «Захар» починається з десятого символу в рядку. Де це може стати в нагоді?
Функція ПОШУК визначає положення знака в текстовому рядку. А функція ПСТР повертає текстові значення (див. Приклад вище). Або можна замінити знайдений текст за допомогою функції ЗАМІНИТИ.
Синтаксис функції ПОШУК:
- «Шуканий текст» - що потрібно знайти;
- «Проглядається текст» - де шукати;
- «Початкова позиція» - з якої позиції починати шукати (за замовчуванням - 1).
Якщо потрібно враховувати регістр, використовується функція ШУКАТИ.