Транслитерация в Excel и Google Таблицах: формулы и массовая обработка

Задача

Типичный сценарий: есть выгрузка каталога, в столбце A названия категорий или товаров на русском, нужно получить столбец с готовыми адресами. Требования те же, что и всегда: нижний регистр, латиница по таблице Яндекса, дефис вместо пробелов, никаких лишних символов.

Способ 1. Формула ПОДСТАВИТЬ

Самый прямолинейный вариант: вложенные замены по одной букве. Работает в любой версии Excel, но формула получается длинной — 33 вложения.

=НИЖНИЙ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A2;"а";"a");"б";"b");"в";"v"))

На практике формулу не пишут в одну строку, а разбивают на этапы в соседних столбцах: в первом приводят текст к нижнему регистру и убирают лишние символы, во втором заменяют буквы, в третьем схлопывают дефисы. После обработки столбцы скрывают или копируют результат как значения.

Плюс: не требует макросов, файл остаётся обычной книгой. Минус: формула нечитаемая, ошибку в ней почти невозможно найти.

Способ 2. Google Таблицы и REGEXREPLACE

В Google Таблицах есть регулярные выражения, поэтому финальная очистка делается одной функцией. Замену букв всё равно приходится описывать, но чистка адреса становится короткой.

=REGEXREPLACE(REGEXREPLACE(НИЖНИЙ(B2); "[^a-z0-9]+"; "-"); "^-+|-+$"; "")

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

Способ 3. Макрос на VBA

Если такая задача возникает регулярно, разумнее один раз написать пользовательскую функцию. Логика простая: словарь замен, проход по символам, финальная очистка.

Function Translit(s As String) As String
    Dim ru, en, i As Integer, r As String
    ru = Array("а","б","в","г","д","е","ё","ж","з","и","й","к","л","м","н","о","п","р","с","т","у","ф","х","ц","ч","ш","щ","ъ","ы","ь","э","ю","я")
    en = Array("a","b","v","g","d","e","e","zh","z","i","j","k","l","m","n","o","p","r","s","t","u","f","h","c","ch","sh","shh","","y","","e","yu","ya")
    r = LCase(s)
    For i = 0 To UBound(ru)
        r = Replace(r, ru(i), en(i))
    Next i
    Translit = r
End Function

Функция вызывается как обычная: =Translit(A2). После неё остаётся заменить пробелы и знаки препинания на дефисы — это можно дописать в тот же макрос.

Минус способа: файл придётся сохранять с поддержкой макросов, а получатель выгрузки может их отключить.

Сравнение способов

Самый короткий путь

Если задача разовая или список умещается в несколько сотен строк, формулы не нужны совсем:

1. Выделите столбец с названиями и скопируйте его.
2. Откройте генератор, переключитесь в режим «Список строк» и вставьте.
3. Скопируйте готовые адреса кнопкой «Копировать все» или скачайте файлом.
4. Вставьте обратно в соседний столбец таблицы — порядок строк сохраняется.

Пустые строки пропускаются, поэтому если в исходном списке есть разрывы, проверьте соответствие строк перед вставкой обратно.

Что проверить после обработки

• Дубли. Отсортируйте столбец с адресами и найдите повторы: разные названия могли схлопнуться в один адрес.
• Длину. Формулой =ДЛСТР(B2) найдите строки длиннее 60 символов — их стоит сократить, о чём подробно в статье про длину URL.
• Пустые значения. Строки, где после обработки не осталось ничего, обычно состояли только из цифр и знаков.
• Спорные буквы. Поиском проверьте, нет ли в результате сочетаний shch, kh, ts — это признак чужой таблицы.

Power Query для регулярных выгрузок

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

Порядок такой: загрузите таблицу в редактор запросов, добавьте настраиваемый столбец с функцией Text.Lower, затем цепочку Text.Replace по таблице соответствий, и в конце — замену посторонних символов. Запрос сохраняется вместе с книгой и применяется к любому новому файлу той же структуры.

Готовый порядок работы с выгрузкой каталога

1. Столбец A — исходное название категории или товара.
2. Столбец B — очищенное название: убраны артикулы, единицы измерения, служебные пометки.
3. Столбец C — транслитерация столбца B.
4. Столбец D — длина адреса формулой =ДЛСТР(C2) для контроля.
5. Столбец E — пометка о дубле формулой =СЧЁТЕСЛИ(C:C; C2): значения больше единицы требуют внимания.

Такая раскладка позволяет проверить всю выгрузку за минуту: отсортировали по столбцу E — увидели повторы, отсортировали по D — нашли слишком длинные адреса.

Частые проблемы при работе с выгрузками

• Лишние пробелы. В выгрузках из учётных систем они встречаются постоянно. Функция СЖПРОБЕЛЫ решает вопрос до транслитерации.
• Неразрывные пробелы. Обычная очистка их не видит — заменяйте отдельно, символ с кодом 160.
• Переносы строк внутри ячейки. Дают неожиданные дефисы в середине адреса.
• Одинаковые названия в разных категориях. Дадут одинаковые адреса — решается добавлением раздела в путь, о чём в статье про структуру URL интернет-магазина.

Частые вопросы