Разберем пользовательскую функцию для транслитерации текста в Excel из кириллицы (русского текста) на латиницу (английский транслит), к примеру "Слон" в "Slon".
Иногда возникают ситуации, когда нам необходимо преобразовать текст на кириллице в транслит и наоборот.
Если это разовое преобразование, то можно воспользоваться одним из множества сервисов в интернете, однако если перевод носит систематический характер и имеет определенную специфику использования в Excel, то такой вариант уже не подойдет.
Рассмотрим 2 различных способа транслитерации текста:
- Формула. Транслит текста можно получить с помощью функции ПОДСТАВИТЬ и последовательной замены символов из кириллицы на символ из латиницы (например, замена "б" на "b", "в" на "v", "г" на "g" и т.д.);
- Пользовательская функция (UDF), макрос. Аналогичная процедура написанная на языке VBA.
В первом случае (вариант формулы) нам придется воспользоваться функцией ПОДСТАВИТЬ 66 раз (для преобразования 33 букв из русского алфавита в нижнем и верхнем регистрах), что делает применение данного варианта неудобным из-за размера применяемой формулы, поэтому подробно остановимся на варианте с пользовательской функцией.
Функция транслита в Excel
Как и всегда при создании пользовательской функции, сначала переходим в редактор Visual Basic (сочетание клавиш Alt + F11), затем создаем новый модуль и вставляем в него следующий код:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Function TranslitText(RusText As String) As String Dim RusAlphabet As Variant 'массив из букв русского алфавита RusAlphabet = Array("а", "б", "в", "г", "д", "е", "ё", "ж", "з", "и", "й", "к", "л", "м", "н", "о", "п", "р", "с", "т", "у", "ф", "х", "ц", "ч", "ш", "щ", "ъ", "ы", "ь", "э", "ю", "я") Dim EngAlphabet As Variant 'массив из букв английского алфавита EngAlphabet = Array("a", "b", "v", "g", "d", "e", "e", "zh", "z", "i", "i", "k", "l", "m", "n", "o", "p", "r", "s", "t", "u", "f", "kh", "tc", "ch", "sh", "shch", "", "y", "", "e", "iu", "ia") Dim EngText As String, Letter As String, Flag As Boolean For i = 1 To Len(RusText) 'цикл по всем символам русского текста Letter = Mid(RusText, i, 1) Flag = 0 For j = 0 To 32 'цикл по всем буквам русского алфавита If RusAlphabet(j) = LCase(Letter) Then 'если символ из текста совпал с буквой из русского алфавита... Flag = 1 If RusAlphabet(j) = Letter Then 'проверка на регистр (верхний или нижний) EngText = EngText & EngAlphabet(j) '... то добавляем соответствующую букву из английского алфавита Exit For Else EngText = EngText & UCase(EngAlphabet(j)) Exit For End If End If Next j If Flag = 0 Then EngText = EngText & Letter 'если символа из текста в алфавите нет (например, знаки препинания и т.п.), то добавляем символ без изменения Next i TranslitText = EngText End Function |
Обратите внимание, что в коде макроса мы использовали вариант транслитерации по правилам перевода загранпаспорта.
Если есть необходимость скорректировать какие-либо правила перевода под свои задачи, то это легко можно сделать в теле функции (изменив массив EngAlphabet в строке 6).
Вызвать пользовательскую функцию в Excel мы можем стандартным способом — либо непосредственно ввести формулу в ячейку, либо воспользоваться мастером функций (выбрав из категории Определенные пользователем):
Обратная транслитерация (с английского на русский)
В случае обратной транслитерации (из латиницы в кириллицу) возникает ряд трудностей, все из которых достаточно тяжело предусмотреть в макросе.
Например, ряд символов "sch" можно интерпретировать и как букву "щ", так и как набор букв "сч", и множество других подобных сочетаний, где нет однозначного соответствия между буквами русского и английского алфавитов.
Поэтому, на мой взгляд, предпочтительнее сделать перевод латиницы в кириллицу вручную во избежание возможных ошибок.
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!
Добрый день!
Что делать, если ручном вводе функции TranslitText в итоге выходит #ИМЯ?
В мастере функций такую формулу найти не получается dthcbz Excei 2013
Заранее спасибо за помощь
Если появляется ошибка #ИМЯ, то вероятно в книге просто не включены макросы — попробуйте их включить.
пожалуйста, напишите точный текст, который нужно вводить в модуль….
Так,чтобы можно было просто скопировать отсюда и вставить в модуль.
Спасибо заранее
Объясните пошагово как установить данный модуль? Постоянно показывает ошибку.
Для удобства добавил пример с работающим модулем в статью.
А как будет выглядеть код, чтобы переименовывалось название файла?
Благодарю. Но у меня возник вопрос: Мне нужно в одной ячейки данные из трёх.
То есть в одной ячейке у меня Фамилия, в другой имя, в третьей отчество. Мне нужна ячейка с транслитом из этих трёх с разделителем точкой.
=TranslitText(B2;C2;D2) выдаёт ошибку #ЗНАЧ
Подскажите, пожалуйста, как мне это реализовать.
Игорь, Вы ввели в функцию 3 аргумента, а нужно только 1.
Вместо =TranslitText(B2;C2;D2) поставьте =TranslitText(B2&C2&D2), тогда данные из 3 ячеек склеятся в 1 (если необходимо, то поставьте в формуле еще разделительные знаки).
У меня почему-то не работает. Выводит то же значение на русском языке, которое было в изначальной ячейке. Скачал пример, тот же эффект. В чем может быть проблема?
Сложно сказать, если в файле с примером тоже не работает, то возможно дело в настройках системы.
Если не сложно, то пришлите ваш файл мне на почту (info@tutorexcel.ru), я посмотрю.
Отправил.
Та же проблема, есть решение?
Возможная причина в версии Excel — в языке интерфейса. У меня стоит русский язык для программы, транслитерация проходит нормально.
Но, к сожалению, не могу проверить насколько корректно работает перевод на других версиях.
У вас русский или английский язык интерфейса?
Добрый день! Подскажите как сделать чтобы заглавные буквы Б, Ю, Ж, Э, Х заменялись на знаки , :, «, { соответственно ?
В коде макроса можно поменять соответствующие буквы (в массиве RusAlphabet) на нужные знаки (в массиве EngAlphabet) и добавить дополнительную проверку регистра (что это заглавные буквы).
Спасибо за материал, подскажите пожалуйста как заменить пробел на знак «-«
Можно прописать в массиве RusAlphabet знак пробела, а в EngAlphabet в соответствующем месте знак «-«.
Как добавить этот символ
334 (код символа) — Ŏ (буква-Ў)
335 (код символа) — ŏ (буква-ў)
Спасибо! Макрос сработал.
Здравствуйте, все работает но как сделать так что бы можно было копировать те названия которые уже в транслите или ими заменить старые (которые были на кириллице).
Здравствуйте, спасибо огромное, очень помогло!!!