Зовсім недавно ми обговорювали використання функції FILTER.XML для імпорту XML-даних з Інтернету – основного завдання, для якого ця функція, власне, і призначена. Однак по дорозі з’явилося ще одне несподіване та гарне використання цієї функції – для швидкого розподілу закріпленого тексту на комірки.
Скажімо, у нас є такий стовпець даних:
Звичайно, для зручності хотілося б розділити його на окремі графи: назва компанії, місто, вулиця, будинок. Ви можете зробити це кількома способами:
- Скористайтесь Текст по колонках із вкладки дані (Дані — Текст у стовпці) і зроби три кроки Текстовий аналізатор. Але якщо завтра дані зміняться, вам доведеться повторити весь процес заново.
- Завантажте ці дані в Power Query і розділіть їх там, а потім завантажте назад на аркуш, а потім оновіть запит, коли дані зміняться (що вже простіше).
- Якщо вам потрібно оновлювати на льоту, ви можете написати кілька дуже складних формул, щоб знаходити коми та витягувати текст між ними.
І ви можете зробити це більш елегантно і використовувати функцію FILTER.XML, але яке це має до цього відношення?
Функція FILTER.XML отримує в якості початкового аргументу XML-код — текст, розмічений спеціальними тегами й атрибутами, а потім розбирає його на компоненти, витягуючи потрібні нам фрагменти даних. Код XML зазвичай виглядає приблизно так:
У XML кожен елемент даних повинен бути укладений у теги. Тег — це деякий текст (у наведеному вище прикладі це менеджер, ім’я, прибуток), укладений у кутові дужки. Теги завжди йдуть парами – відкривають і закривають (з похилою рискою на початку).
Функція FILTER.XML може легко витягнути вміст усіх потрібних нам тегів, наприклад, імена всіх менеджерів, і (що найголовніше) відобразити їх усіх одразу в одному списку. Тому наше завдання — додати теги до вихідного тексту, перетворивши його на XML-код, придатний для подальшого аналізу функцією FILTER.XML.
Якщо ми візьмемо для прикладу першу адресу з нашого списку, то нам потрібно буде перетворити її на таку конструкцію:
Я назвав глобальний тег відкриття та закриття всього тексту t, і теги, що обрамляють кожен елемент s., але ви можете використовувати будь-які інші позначення – це не має значення.
Якщо ми приберемо з цього коду відступи і розриви рядків – зовсім, до речі, необов’язкові і додані лише для наочності, то все це перетвориться на рядок:
І його вже відносно легко можна отримати з адреси джерела, замінивши в ньому коми парою тегів за допомогою функції ЗАМІННИК (ЗАМІНА) і склеювання з символом & на початку та в кінці початкових і закриваючих тегів:
Щоб розгорнути отриманий діапазон по горизонталі, використовуємо стандартну функцію TRANSP (ТРАНСПОНУВАТИ), загорнувши в нього нашу формулу:
Важливою особливістю всього цього дизайну є те, що в новій версії Office 2021 і Office 365 з підтримкою динамічних масивів для введення не потрібні спеціальні жести – просто введіть і натисніть на Що натомість? Створіть віртуальну версію себе у – сама формула займає необхідну кількість комірок і все працює на ура. У попередніх версіях, де ще не було динамічних масивів, перед введенням формули потрібно буде спочатку виділити достатню кількість порожніх комірок (можна з відступом), а після створення формули натиснути комбінацію клавіш Ctrl+Shift+ Що натомість? Створіть віртуальну версію себе у щоб ввести його як формулу масиву.
Подібний трюк можна використати, коли розділяєте текст, що злипся в одну комірку, через розрив рядка:
Єдина відмінність від попереднього прикладу полягає в тому, що замість коми тут ми замінюємо невидимий символ розриву рядка Alt + Enter, який можна вказати у формулі за допомогою функції CHAR на код 10.
- Тонкощі роботи з розривами рядків (Alt + Enter) в Excel
- Розбийте текст на стовпці в Excel
- Заміна тексту на ЗАМІНУ