Транслитерация в Excel, замена русских букв на английские

Как перевести кириллицу в транслит в Excel?

Технически транслитерация — это замена по списку значений — символов на определенные их эквиваленты в другой раскладке. Значит, и задача состоит в том, чтобы в Excel осуществить эту серию замен.

Удивительно, но сделать такую массовую замену и транслитерировать текст в Excel можно множеством способов. Обо всех подробнее далее.

Формула транслита с помощью стандартных функций Excel

Наименее изящное, тем не менее, самое простое решение задачи. В Excel для замены символов есть функция ПОДСТАВИТЬ (англ. SUBSTITUTE). Формула принимает на вход 4 аргумента:

1. Текст для обработки, в котором будет производиться замена (текст);
2. Заменяемый фрагмент (стар_текст);
3. На что заменить (нов_текст);
4. Какой по счету встреченный в значении фрагмент заменить. (номер_вхождения).

Четвертый параметр является опциональным и, если он не заполнен, заменяются все встреченные в тексте фрагменты.

Как раз то, что нам нужно! Поскольку Excel позволяет составлять сложные вложенные функции, мы можем сделать эту серию замен в одной много раз вложенной формуле.

Но есть несколько проблем, которые в сочетании не дают возможность сделать идеальную единую формулу транслита. Вот они:

• В русском алфавите 33 буквы.
• Идеальная формула транслита должна сохранять при замене регистр символов. Благо, функция “ПОДСТАВИТЬ” — регистрозависимая. Получается, что нужно делать не 33, а 66 замен. Но
• в Excel есть ограничение количества уровней вложенности в одной формуле — 64.

Будь в русском алфавите хотя бы на одну букву меньше, мы бы уложились в ограничения Excel. Есть ли решение? Да, довольно простое.

Формула на 64 уровня вложенности без заглавных Ъ и Ь

Как правило, заглавные буквы в словах бывают, если это начало слова. Или если это аббревиатура, но аббревиатуры тоже составляются из первых букв слов.

А вы знаете слова, начинающиеся с Ъ или Ь? :)

Кажется, их можно пропустить. Так мы экономим два уровня вложенности и укладываемся в лимиты Excel (66 – 2 = 64)!

Важно также учитывать, что при транслитерации заглавных букв Ж, Ш, Ч, Щ и т.д., когда английский эквивалент состоит из нескольких букв, заглавной на латинице должна быть только первая. Интернет полон решениями, где этот момент не учтен. Итак, вот наиболее полная корректная формула:

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(
A1;
"А";"A");"Б";"B");"В";"V");"Г";"G");"Д";"D");"Е";"E");"Ё";"Yo");"Ж";"Zh");"З";"Z");"И";"I");"Й";"Y");
"К";"K");"Л";"L");"М";"M");"Н";"N");"О";"O");"П";"P");"Р";"R");"С";"S");"Т";"T");"У";"U");"Ф";"F");
"Х";"Kh");"Ц";"Ts");"Ч";"Ch");"Ш";"Sh");"Щ";"Sch");"Ы";"Y");"Э";"E");"Ю";"Yu");"Я";"Ya");"а";"a");
"б";"b");"в";"v");"г";"g");"д";"d");"е";"e");"ё";"yo");"ж";"zh");"з";"z");"и";"i");"й";"y");"к";"k");
"л";"l");"м";"m");"н";"n");"о";"o");"п";"p");"р";"r");"с";"s");"т";"t");"у";"u");"ф";"f");"х";"kh");
"ц";"ts");"ч";"ch");"ш";"sh");"щ";"sch");"ъ";"y");"ы";"y");"ь";"");"э";"e");"ю";"yu");"я";"ya")

Скачать файл-шаблон с транслитерацией

Формула выше на основе функции ПОДСТАВИТЬ с несколькими условиями может быть видоизменена – вместо явного прописывания текстовых значений, можно сделать ее на основе конкретных ячеек. Чем удобнее такой подход? Тем, что формулу менять не нужно, а данные для замены представлены в удобном для восприятия и редактирования формате в столбцах A и B.

По ссылке можно скачать файл-шаблон, в котором применены эти функции.

Формулы транслита, итоги

У подхода с созданием таких мега формул есть свои плюсы и минусы. Минус один – они громоздки и такое не так-то просто сходу набрать. Поэтому их нужно где-то хранить и копировать при необходимости.

Плюсы в основном происходят из недостатков VBA-кода:

• Формулы листа железно сработают в любой версии Excel на любом компьютере, даже с самыми жесткими политиками защиты данных.
• Они легко изменяются, если нужно переделать правила транслитерации, и довольно просты.
• Транспортабельны, т.е. формулы не “слетят”, если их отправить кому-либо и открыть на другом компьютере.

Функция на VBA

Код пользовательской функции, которая транслитерирует строчные в строчные, а заглавные – в заглавные, и при этом транслитерирует заглавные буквы Ж, Ш, Х, Ч, Щ и подобные, делая в транслите заглавной только первую букву (Zh, Sh, Kh, Ch, Sch…).

Точь-в-точь эта функция присутствует в надстройке !SEMTools и доступна всем желающим – нужно только скачать и установить !SEMTools. Далее функцию можно применять как обычную функцию Excel на листе. Например, =Translit(A1)

Function Translit(x As String) As String
    cyr = "абвгдеёжзийклмнопрстуфхцчшщъыьэюя"
    lat = Array("", "a", "b", "v", "g", "d", "e", "e", "zh", "z", "i", "y", "k", _
        "l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "ts", "ch", _
        "sh", "sch", "y", "y", "", "e", "yu", "ya")
    For i = 1 To 33
        x = Replace(x, Mid(cyr, i, 1), lat(i), , , vbBinaryCompare)
        x = Replace(x, UCase(Mid(cyr, i, 1)), StrConv(lat(i), vbProperCase), , , vbBinaryCompare)
    Next
    Translit = x
End Function

Заменить русские буквы на английские в Excel и наоборот

Надстройка !SEMTools предлагает помимо основного еще 4 варианта замены:

1. Обратный транслит (из латиницы в кириллицу);
2. “Смена раскладки” – asdf -> фыва;
3. Замена букв с идентичным начертанием — кириллица в латиницу;
4. Аналогичная замена английских букв, похожих на русские, на действительно русские.

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

Чтобы произвести транслитерацию, достаточно просто выделить столбец с данными и вызвать нужную процедуру. Данные изменятся на месте.