Спикер Алексей Борисов

Корпоративные выгрузки часто приходят с одним полем, в котором через запятую перечислено несколько значений. В справочнике поставщиков — «ООО Ромашка, ИП Иванов, Лютик ЛТД». В списке регионов — «02, 102, 702». С таким полем не работает ни ВПР, ни сводная, ни объединение запросов — значение находится в середине текста, и его не вытащишь. Power Query решает это одной операцией — «Разделить столбец по разделителю» с опцией «Строки» в расширенных параметрах.

Учебная задача: справочник кодов ГИБДД

У каждого региона РФ — один или несколько кодов ГИБДД. Полный справочник лежит на Википедии в статье «Коды субъектов Российской Федерации». Первые строчки выглядят так:

Субъект	Код ГИБДД
Республика Адыгея	01
Республика Башкортостан	02, 102, 702
Республика Дагестан	05
Москва	77, 99, 97, 177, 197, 777

Типовая задача: коллега прислал список клиентов, где в поле «Регион» стоит не название, а код. У Ивана Иванова указан код 102 — нужно понять, что это Башкирия, и подтянуть название через объединение запросов. ВПР по коду не сработает: в справочнике Башкирия лежит одной строкой «02, 102, 702», а код 102 — в середине. Нужно превратить одну строку в три — «02 → Башкирия», «102 → Башкирия», «702 → Башкирия».

Загружаем справочник

В пустой книге Excel: Данные → Получить данные → Из других источников → Из интернета → вставляем ссылку на статью Википедии. Power Query показывает список таблиц на странице. Выбираем ту, что начинается с Адыгеи — это полный перечень субъектов. Нажимаем «Преобразовать данные».

Чистим справочник в три шага:

1. Повышаем заголовки — Преобразование → Использовать первую строку в качестве заголовков.
2. Удаляем лишние столбцы — ОКАТО, ОКТМО, ISO и прочие. Остаётся только «Субъект» и «Код ГИБДД».
3. Переносим «Код ГИБДД» влево — правильнее, чтобы первым стоял столбец с уникальным ключом. Просто перетаскиваем мышкой.

Шаг 1. Разбиваем по строкам

Выделяем столбец «Код ГИБДД» → Преобразование → Разделить столбец → По разделителю. В открывшемся окне:

• разделитель — запятая;
• режим — по каждому вхождению разделителя;
• раскрываем «Расширенные параметры» → выбираем «Строки» (по умолчанию стоит «Столбцы»).

Нажимаем ОК. Матрица разворачивается: у Башкирии было одно значение «02, 102, 702» — стало три строки, в каждой «Башкирия» напротив одного кода. У Москвы — шесть строк. Теперь по коду 102 справочник вернёт «Башкирия», по 197 — «Москва».

Шаг 2. Ведущий ноль исчез — возвращаем

После разбиения в столбце «Код ГИБДД» вместо «02» стоит «2», вместо «05» — «5». Power Query при загрузке из интернета автоматически определил тип данных и отбросил ведущие нули. Для региона это критично: 02 (Башкирия) и 2 — это разные значения, код 2 в справочнике вообще отсутствует.

Решение — в панели «Применённые шаги» справа находим последний шаг «Измененный тип» (обычно он идёт сразу после повышения заголовков) и удаляем его крестиком. Все данные возвращаются в текстовый формат. Поскольку столбец «Субъект» и так был текстом, единственное, что мы теряем — автоопределение для отсутствующих здесь столбцов. А «Код ГИБДД» теперь снова «02, 102, 702», как и должно быть.

Важный момент: в Power Query шаги нельзя отменить через Ctrl+Z. Удалённый шаг удалён навсегда. Если сомневаетесь — сохраните копию файла перед тем, как убирать шаги.

Шаг 3. Убираем лишние пробелы

В исходных данных значения разделены не просто запятой, а запятой с пробелом — «02, 102, 702». После разбиения только по запятой в столбце появляются варианты с пробелом в начале: «02», « 102», « 702». Для Excel « 102» и «102» — разные значения, и при объединении с таблицей клиентов они не совпадут.

Выделяем столбец «Код ГИБДД» → Преобразование → Формат → Усечь. Лишние пробелы в начале и конце значения исчезают.

Небольшое отступление про переводы. В обычных формулах Excel функция называется СЖПРОБЕЛЫ, в Power Query та же функция — «Усечь». На английском и там, и там это TRIM. Разные переводчики Microsoft в разные годы работали с разными командами, поэтому один и тот же инструмент получил два названия. Рядом есть кнопка «Очистить» — это уже другая функция (английская CLEAN), она удаляет не пробелы, а непечатаемые символы: переносы строк, табуляцию, неразрывные пробелы. Когда после разбиения по запятой строка стала визуально выше — значит в ячейке остался перенос строки, и тогда нужна «Очистить».

Если у Красноярского края в скобках указан год

В справочниках регионов часто встречаются сноски: «24 [с 2019]» у Красноярского края, «24 [до 2019]» и так далее. После разбиения по запятой такая запись остаётся одним куском, и в справочнике будет «24 [с 2019]» вместо «24». Решение — Преобразование → Извлечь → Текст перед разделителем, в качестве разделителя указываем пробел. В столбце останется только числовой код.

Что делать, если нет опции «Строки»

В старых версиях Power Query (Excel 2013, ранние сборки 2016) в расширенных параметрах нет переключателя «Строки», только «Столбцы». Прямой путь закрыт, есть обход:

1. Разделить по запятой на столбцы. Power Query создаст столько колонок, сколько кодов было в самой длинной строке (у Москвы — шесть).
2. Выделить столбец «Субъект» → Преобразование → Отменить свёртывание других столбцов (Unpivot).
3. Удалить получившийся столбец «Атрибут», оставив только «Значение» — это и будет код ГИБДД.

Способ рабочий, но в разы длиннее. Если Power Query в компании редко обновляется — повод написать заявку в техподдержку.

Готовый M-код

Если нужно встроить операцию в существующий запрос или настроить её без UI, вот минимальная комбинация шагов:

let
    Source = Excel.CurrentWorkbook(){[Name="Регионы"]}[Content],
    #"Тип в текст" = Table.TransformColumnTypes(Source, {{"Код ГИБДД", type text}}),
    #"Разделение по строкам" = Table.ExpandListColumn(
        Table.TransformColumns(
            #"Тип в текст",
            {{"Код ГИБДД", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), type text}}
        ),
        "Код ГИБДД"
    ),
    #"Усечь пробелы" = Table.TransformColumns(
        #"Разделение по строкам",
        {{"Код ГИБДД", Text.Trim, type text}}
    )
in
    #"Усечь пробелы"

Порядок принципиален — сначала перевод в текст, потом разбиение, потом усечение. Если типы данных поменять после разбиения, ведущие нули уже будут потеряны.

Другой типовой случай: поставщики через запятую

Разбиение по строкам нужно не только для справочников. В CRM или выгрузке из 1С менеджер часто пишет в одной ячейке несколько поставщиков через запятую: «ООО Ромашка, ИП Иванов, Лютик ЛТД». Пока значения слеплены — нельзя посчитать, сколько закупок у каждого поставщика, нельзя связать с отдельным справочником контрагентов.

Техника та же: выделяем столбец → Разделить столбец по разделителю → запятая → Строки → ОК → Усечь. Теперь каждый поставщик занимает отдельную строку, и с ним можно работать как с полноценной сущностью.

Куда двигаться дальше

Разбиение по строкам — одна из базовых операций нормализации данных. В связке с другими приёмами (отмена свёртывания, объединение запросов, сохранение ведущих нулей) она позволяет привести в порядок почти любую корпоративную выгрузку без макросов и ручной правки.

Эти приёмы разбираются на курсе Power Query и входят в программу Excel Эксперт.