30 функцій Excel за 30 днів: ЗАМІНА

Вчора на марафоні 30 функцій Excel за 30 днів ми використали функцію OFFSET (OFFSET), щоб повернути посилання, а також побачив, що це дуже схоже на функцію ІНДЕКС (ІНДЕКС). Крім того, ми дізналися, що функція OFFSET (ЗМІЩЕННЯ) перераховується щоразу, коли змінюються дані на робочому аркуші ІНДЕКС (INDEX) лише при зміні його аргументів.

На 27 день марафону ми вивчатимемо функцію ЗАМІННИК (ЗАМІНА). Як функція ЗАМІНА (REPLACE), він замінює старий текст на новий, а також може робити кілька замін для того самого тексту в рядку.

Як показує практика, в деяких ситуаціях використовувати команди швидше і простіше знайти/заміщати (Знайти/Замінити), коли необхідно зробити заміну чутливою до регістру.

Отже, давайте детальніше розглянемо інформацію та приклади ЗАМІННИК (ЗАМІНА). Якщо у вас є інша інформація чи приклади використання цієї функції, поділіться нею в коментарях.

Функція 27: ЗАМІНА

функція ЗАМІННИК (ЗАМІНА) замінює старий текст новим у текстовому рядку. Функція замінить усі повторення старого тексту, доки не буде виконано певну умову. Він чутливий до регістру.

Як можна використовувати функцію ЗАМІНА?

функція ЗАМІННИК (ЗАМІНА) замінює старий текст новим у текстовому рядку. Ви можете використовувати його для:

  • Змініть назву регіону в шапці звіту.
  • Видаліть недруковані символи.
  • Замінити останній пробіл.

ЗАМІНА Синтаксис

функція ЗАМІННИК (ЗАМІНА) має такий синтаксис:

SUBSTITUTE(text,old_text,new_text,instance_num)

ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)

  • текст (текст) – текстовий рядок або посилання, де буде замінено текст.
  • старий_текст (старий_текст) – текст для заміни.
  • новий_текст (новий_текст) – текст для вставки.
  • номер_примірника (номер_запису) — номер входження тексту, який потрібно замінити (необов’язково).

ЗАМІНА Пастки

  • функція ЗАМІННИК (ЗАМІНА) може замінити всі повторення старого тексту, тому, якщо вам потрібно замінити лише конкретне входження, використовуйте аргумент номер_примірника (номер_запису).
  • Якщо вам потрібно зробити заміну без урахування регістру, скористайтеся функцією ЗАМІНА (ЗАМІНИТИ).

Приклад 1: Зміна назви регіону в заголовку звіту

Використання функцій ЗАМІННИК (ЗАМІНА) Ви можете створити назву звіту, яка автоматично змінюватиметься залежно від вибраного регіону. У цьому прикладі заголовок звіту вводиться в клітинку C11, яка має назву RptTitle. символ yyy у тексті заголовка буде замінено назвою регіону, вибраного в клітинці D13.

=SUBSTITUTE(RptTitle,"yyy",D13)

=ПОДСТАВИТЬ(RptTitle;"yyy";D13)

30 функцій Excel за 30 днів: ЗАМІНА

Приклад 2: Видалення недрукованих символів

Під час копіювання даних із веб-сайту в тексті можуть з’являтися зайві пробіли. Текст може містити як звичайні пробіли (символ 32), так і нерозривні пробіли (символ 160). Коли ви спробуєте їх видалити, ви побачите, що функція TRIM (TRIM) не може видалити нерозривні пробіли.

На щастя, ви можете скористатися цією функцією ЗАМІННИК (ЗАМІНА), щоб замінити кожен нерозривний пробіл звичайним, а потім за допомогою функції TRIM (TRIM), видаліть усі зайві пробіли.

=TRIM(SUBSTITUTE(B3,CHAR(160)," "))

=СЖПРОБЕЛЫ(ПОДСТАВИТЬ(B3;СИМВОЛ(160);" "))

30 функцій Excel за 30 днів: ЗАМІНА

Приклад 3: Заміна останнього символу пробілу

Щоб уникнути заміни всіх входжень текстового рядка, ви можете використовувати аргумент номер_примірника (номер_запису), щоб вказати, яке входження замінити. У наступному прикладі наведено список інгредієнтів для рецепту, де потрібно замінити лише останній символ пробілу.

функція LEN (DLSTR) у клітинці C3 підраховує кількість символів у клітинці B3. функція ЗАМІННИК (SUBSTITUTE) замінює всі пробіли порожнім рядком, а друга функція LEN (DLSTR) знаходить довжину обробленого рядка. Довжина на 2 символи коротша, тобто в рядку було 2 пробіли.

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))

=ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";""))

30 функцій Excel за 30 днів: ЗАМІНА

У клітинці D3 функція ЗАМІННИК (ЗАМІНА) замінює другий символ пробілу новим рядком » | “.

=SUBSTITUTE(B3," "," | ",C3)

=ПОДСТАВИТЬ(B3;" ";" | ";C3)

30 функцій Excel за 30 днів: ЗАМІНА

Щоб не використовувати дві формули для вирішення цієї задачі, можна об'єднати їх в одну довгу:

=SUBSTITUTE(B3," "," | ",LEN(B3)-LEN(SUBSTITUTE(B3," ","")))

=ПОДСТАВИТЬ(B3;" ";" | ";ДЛСТР(B3)-ДЛСТР(ПОДСТАВИТЬ(B3;" ";"")))

залишити коментар