зміст
Я вже писав про те, як можна швидко склеїти текст з декількох комірок в одну і, навпаки, розібрати довгий текстовий рядок на складові. Тепер давайте подивимося на близьку, але трохи більш складну задачу - як склеїти текст з декількох комірок при виконанні певної заданої умови.
Припустимо, у нас є база даних клієнтів, де одній назві компанії може відповідати кілька різних електронних адрес її співробітників. Наше завдання - зібрати всі адреси за назвами компаній і об'єднати їх (через кому або крапку з комою), щоб скласти, наприклад, список розсилки для клієнтів, тобто отримати на виході щось на зразок:
Іншими словами, нам потрібен інструмент, який буде склеювати (зв'язувати) текст за умовою – аналог функції СУММЕСЛІ (SUMIF), але для тексту.
Спосіб 0. Формула
Не дуже елегантний, але найпростіший спосіб. Можна написати просту формулу, яка перевірить, чи відрізняється компанія в наступному рядку від попередньої. Якщо не відрізняється, то приклейте наступну адресу через кому. Якщо відрізняється, то «скидаємо» накопичене, починаючи заново:
Недоліки такого підходу очевидні: з усіх комірок отриманого додаткового стовпця потрібні лише останні для кожної компанії (жовті). Якщо список великий, то для швидкого їх вибору доведеться додати ще один стовпець за допомогою функції DLSTR (LEN), перевіряючи довжину накопичених рядків:
Тепер ви можете відфільтрувати ті та скопіювати необхідну склейку адрес для подальшого використання.
Спосіб 1. Макрофункція склеювання за однією умовою
Якщо оригінальний список не відсортовано за компаніями, наведена вище проста формула не працює, але ви можете легко обійти її за допомогою невеликої спеціальної функції у VBA. Відкрийте редактор Visual Basic, натиснувши комбінацію клавіш Alt + F11 або за допомогою кнопки Візуальний Бейсік таб розробник (розробник). У вікні, що відкриється, вставте новий порожній модуль через меню Вставка – модуль і скопіюйте туди текст нашої функції:
Функція MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " склеювання не дорівнюють одне одному - ми виходимо з помилкою If SearchRange.Count <> TextRange.Count Тоді MergeIf = CVErr(xlErrRef) Exit Function End If 'перегляньте всі клітинки, перевірте умову та зберіть текст у змінній OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'показати результати без останнього розділювача MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End функція
Якщо тепер повернутися до Microsoft Excel, то в списку функцій (кнопка fx у рядку формул або вкладці Формули – вставити функцію) можна буде знайти нашу функцію MergeIf в категорії Визначений користувачем (Визначений користувачем). Аргументи функції такі:
Спосіб 2. Об'єднання тексту за неточною умовою
Якщо ми замінимо перший символ у 13-му рядку нашого макросу = до оператора наближеної відповідності Люблю, то можна буде здійснити склейку за неточним збігом вихідних даних з критерієм відбору. Наприклад, якщо назва компанії може бути написана в різних варіантах, то ми можемо перевірити та зібрати їх усі за допомогою однієї функції:
Підтримуються стандартні символи підстановки:
- зірочка (*) – позначає будь-яку кількість будь-яких символів (включаючи їх відсутність)
- знак питання (?) – позначає будь-який окремий символ
- знак фунта (#) – позначає одну цифру (0-9)
За замовчуванням оператор Like чутливий до регістру, тобто розуміє, наприклад, «Оріон» і «Оріон» як різні компанії. Щоб ігнорувати регістр, ви можете додати рядок на самому початку модуля в редакторі Visual Basic Опція порівняння тексту, який перемикає Like на регістр.
Таким чином можна скласти дуже складні маски для перевірки умов, наприклад:
- ?1##??777RUS – вибір всіх номерних знаків 777 регіону, починаючи з 1
- LLC* – усі компанії, назва яких починається на LLC
- ##7## – всі товари з п’ятизначним цифровим кодом, де третя цифра – 7
- ?????? – усі назви з п’яти букв тощо.
Спосіб 3. Макрофункція для склеювання тексту за двох умов
У роботі може виникнути проблема, коли потрібно зв'язати текст більше однієї умови. Наприклад, уявімо, що в нашій попередній таблиці додалася ще одна колонка з містом, і склейка повинна проводитися не тільки для даної компанії, але і для даного міста. В цьому випадку нашу функцію доведеться трохи модернізувати, додавши до неї ще одну перевірку діапазону:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'символи-роздільники (можна замінити пробілом або ; тощо) e.) 'якщо діапазони перевірки та склеювання не дорівнюють один одному, вийти з помилкою If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'перегляньте всі комірки, перевірте всі умови та зберіть текст у змінну OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'відображати результати без останнього роздільника MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Він буде застосований точно так само – тільки аргументів тепер потрібно вказати більше:
Спосіб 4. Групування та склеювання в Power Query
Ви можете вирішити проблему без програмування на VBA, якщо скористаєтеся безкоштовною надбудовою Power Query. Для Excel 2010-2013 його можна завантажити тут, а в Excel 2016 він уже вбудований за замовчуванням. Послідовність дій буде наступною:
Power Query не вміє працювати зі звичайними таблицями, тому для початку потрібно перетворити нашу таблицю на «розумну». Для цього виберіть його і натисніть комбінацію Ctrl+T або виберіть із вкладки Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю). На вкладці, яка потім з’явиться Конструктор (Дизайн) можна задати назву таблиці (я залишив стандартне Таблиця 1):
Тепер давайте завантажимо нашу таблицю в надбудову Power Query. Для цього на табл дані (якщо у вас Excel 2016) або на вкладці Power Query (якщо у вас Excel 2010-2013) натисніть Зі столу (Дані — з таблиці):
У вікні редактора запитів, що відкриється, виберіть стовпець, клацнувши на заголовку О компании і натисніть кнопку вище Group (Групувати за). Введіть назву нового стовпця та тип операції в групуванні – Всі лінії (Усі рядки):
Натискаємо ОК і отримуємо міні-таблицю згрупованих значень для кожної компанії. Вміст таблиць добре видно, якщо клацнути лівою кнопкою миші на білому тлі комірок (а не на тексті!) в отриманому стовпці:
Тепер додамо ще один стовпець, куди за допомогою функції склеюємо вміст стовпців Адреса в кожну з міні-таблиць, розділяючи їх комами. Для цього на табл Додати стовпець натискаємо Спеціальний стовпець (Додати стовпець — спеціальний стовпець) і у вікні, що з’явиться, введіть ім’я нового стовпця та формулу зв’язку мовою M, вбудованою в Power Query:
Зауважте, що всі M-функції чутливі до регістру (на відміну від Excel). Після натискання на OK отримуємо нову колонку зі склеєними адресами:
Залишилося прибрати вже непотрібну колонку Адреси таблиці (правий клік на заголовку) Видалити стовпець) і завантажити результати на аркуш, натиснувши вкладку Головна — закрити та завантажити (Додому — Закрити та завантажити):
Важливий нюанс: На відміну від попередніх методів (функцій), таблиці з Power Query не оновлюються автоматично. Якщо в майбутньому відбудуться будь-які зміни у вихідних даних, то вам потрібно буде клацнути правою кнопкою миші в будь-якому місці таблиці результатів і вибрати команду Update & Save (Оновити).
- Як розбити довгий текстовий рядок на частини
- Кілька способів склеювання тексту з різних комірок в одну
- Використання оператора Like для перевірки тексту на маску