Транслит excel

Транслитерация текста в Excel

Разберем пользовательскую функцию для транслитерации текста в Excel из кириллицы (русского текста) на латиницу (английский транслит), к примеру «Слон» в «Slon».

Иногда возникают ситуации, когда нам необходимо преобразовать текст на кириллице в транслит и наоборот.
Если это разовое преобразование, то можно воспользоваться одним из множества сервисов в интернете, однако если перевод носит систематический характер и имеет определенную специфику использования в Excel, то такой вариант уже не подойдет.

Рассмотрим 2 различных способа транслитерации текста:

  • Формула. Транслит текста можно получить с помощью функции ПОДСТАВИТЬ и последовательной замены символов из кириллицы на символ из латиницы (например, замена «б» на «b», «в» на «v», «г» на «g» и т.д.);
  • Пользовательская функция (UDF), макрос. Аналогичная процедура написанная на языке VBA.

В первом случае (вариант формулы) нам придется воспользоваться функцией ПОДСТАВИТЬ 66 раз (для преобразования 33 букв из русского алфавита в нижнем и верхнем регистрах), что делает применение данного варианта неудобным из-за размера применяемой формулы, поэтому подробно остановимся на варианте с пользовательской функцией.

Функция транслита в Excel

Как и всегда при создании пользовательской функции, сначала переходим в редактор Visual Basic (сочетание клавиш Alt + F11), затем создаем новый модуль и вставляем в него следующий код:

Небольшой макрос Excel для транслита русских букв

Оценка: 99.5 % — 4 Голосов

Если вам необходимо выполнить транслит русских букв в английские.

Сохраняем файл excel как Книга Excel с поддержкой макросов (*xlsm) . Вызываем сочетанием клавиш Alt+F11 Microsoft Visual Basic For Applications, в окне Project кликаем правой кнопкой мыши, выбираем Insert — Module и вставляем следующее:

Function TRANSLIT(Txt As String) As String
Dim Rus As Variant
Rus = Array(«а», «б», «в», «г», «д», «е», «ё», «ж», «з», «и», «й», «к», _
«л», «м», «н», «о», «п», «р», «с», «т», «у», «ф», «х», «ц», «ч», «ш», _
«щ», «ъ», «ы», «ь», «э», «ю», «я», «А», «Б», «В», «Г», «Д», «Е», _
«Ё», «Ж», «З», «И», «Й», «К», «Л», «М», «Н», «О», «П», «Р», _
«С», «Т», «У», «Ф», «Х», «Ц», «Ч», «Ш», «Щ», «Ъ», «Ы», «Ь», «Э», «Ю», «Я»)
Dim Eng As Variant
Eng = 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», «sch», «»», «y», «», «e», «yu», «ya», «A», «B», «V», «G», «D», _
«E», «Jo», «Zh», «Z», «I», «J», «K», «L», «M», «N», «O», «P», «R», _
«S», «T», «U», «F», «H», «C», «Ch», «Sh», «Sch», «»», «Y», «», «E», «Yu», «Ya»)
For I = 1 To Len(Txt)
с = Mid(Txt, I, 1)
flag = 0
For J = 0 To 65
If Rus(J) = с Then
outchr = Eng(J)
flag = 1
Exit For
End If
Next J
If flag Then outstr = outstr & outchr Else outstr = outstr & с
Next I
TRANSLIT = outstr
End Function

Читать еще:  В excel написание макроса

Для транслита русских букв в английские в ячейке вводим: =TRANSLIT(НомерЯчейки)

Данная функция является частью надстройки MulTEx

  • Описание, установка, удаление и обновление
  • Полный список команд и функций MulTEx
  • Часто задаваемые вопросы по MulTEx
  • Скачать MulTEx

Перевод с транслита и обратно

Функция переводит текст, написанный на транслите на русский( Privet=Привет ), и наоборот — с русского на транслит( Привет=Privet ). Функция может пригодиться для создания корпоративных адресов эл.почты на основании фамилий сотрудников и для других подобных целей.

Вызов команды через стандартный диалог:

Мастер функцийКатегория «MulTEx»Транслит

Вызов с панели MulTEx:

Сумма/Поиск/ФункцииТекстовыеТранслит

Синтаксис:
=Транслит( B8 ;0)
=Транслит( B8 ;1)

ТекстДляПеревода( B8 ) — указывается ячейка, текст в которой необходимо перевести.

НаЛатиницу(0) — Необязательный аргумент. По умолчанию принимает значение 1(ИСТИНА). Всего может использоваться два типа: 1 или ИСТИНА — преобразование с транслита на русский; 0 или ЛОЖЬ- с русского на транслит.
Заглавные и строчные буквы различаются.

Следует помнить, что не все пишут на транслите по установленным правилам(поверьте, они существуют!), вследствие чего не всегда текст, написанный на транслите может быть корректно переведен на русский при помощи данной функции.

Сводные таблицы Excel

Если вам часто приходится переводить кириллицу в транслит (а попробуйте-ка сделать это для фамилии Кржижановский, например), то специальная пользовательская функция на VBA может очень помочь.

Откройте редактор Visual Basic сочетанием клавиш Alt+F11, вставьте через меню новый пустой программный модуль (Insert → Module) и введите туда текст этой функции.

Function Translit(Txt As String) As String Dim Rus As Variant Rus = Array(«а», «б», «в», «г», «д», «е», «ё», «ж», «з», «и», «й», «к», _ «л», «м», «н», «о», «п», «р», «с», «т», «у», «ф», «х», «ц», «ч», «ш», _ «щ», «ъ», «ы», «ь», «э», «ю», «я», «А», «Б», «В», «Г», «Д», «Е», _ «Ё», «Ж», «З», «И», «Й», «К», «Л», «М», «Н», «О», «П», «Р», _ «С», «Т», «У», «Ф», «Х», «Ц», «Ч», «Ш», «Щ», «Ъ», «Ы», «Ь», «Э», «Ю», «Я») Dim Eng As Variant Eng = Array(«a», «b», «v», «g», «d», «e», «jo», «zh», «z», «i», «j», _ «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», «JO», «ZH», «Z», «I», «J», «K», «L», «M», «N», «O», «P», «R», _ «S», «T», «U», «F», «KH», «TS», «CH», «SH», «SCH», «»», «Y», «‘», «E», «YU», «YA») For I = 1 To Len(Txt) с = Mid(Txt, I, 1) flag = 0 For J = 0 To 65 If Rus(J) = с Then outchr = Eng(J) flag = 1 Exit For End If Next J If flag Then outstr = outstr & outchr Else outstr = outstr & с Next I Translit = outstr End Function

Читать еще:  Символы в экселе

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

Как правильно в Excel переводить русские буквы в транслит

В этом макросе варианты замены русских букв английскими эквивалентами представлены согласно ГОСТу. Если для вашей задачи нужны другие версии (например, русская «я» должна выводиться как «ja», а не как «ya» и т.д.), то можно свободно подкорректировать это в теле макроса. Как легко догадаться, два первых массива Rus и Eng как раз и задают эти подстановки.

Транслитерация текстовой строки средствами VBA

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

(добавлено в связи с просьбой выполнить обратную транслитерацию)
К сожалению, процесс обратной транслитерации весьма затруднён (я бы даже сказал, что в общем случае невозможно сделать такую функцию, которая преобразовывала бы строку после транслитерации к исходному виду).

Попытаюсь объяснить, почему так происходит:
Допустим, в качестве исходной строки у нас будет текст «щзч схш жзх»

И что же мы видим на выходе?
А вот что: «zchzch shsh zhzh»

Достаточно похожие сочетания букв, не правда ли?
И как теперь макросу определить, что означает сочетание «zch sh zh» — «щ сх ж» или «зч ш ж»?
Или, может, «зч сх зх»? Все варианты для макроса ведь равнозначны.

А сочетание «zhzh» следует перевести как «зхзх» или как «жж»?
То же самое касается некоторых других буквосочетаний.

Специально проверил транслитерацию подобных сочетаний на популярном сервисе http://www.translit.ru/
Результат — при обратном переводе на русский исходная строка изменилась: схш жзх —> shsh zhzh —> шш жж

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

Другой вариант функции:

Результат работы (другой набор символов для замены, учитывается регистр)

Исходная строка: «А-Б-В-Г-Д-Е-Ё-Ж-З-И-Й-К-Л-М-Н-О-П-Р-С-Т-У-Ф-Х-Ц-Ч-Ш-Щ-Ъ-Ы-Ь-Э-Ю-Я»
Итоговая строка: «A-B-V-G-D-E-E-ZH-Z-I-Y-K-L-M-N-O-P-R-S-T-U-F-KH-TS-TCH-SH-SCH—Y—E-YU-YA»

  • 42279 просмотров

Комментарии

можно. с любого языка на любой язык.
в статье есть ссылка на надстройку для транслитерации, — там вы можете сами настроить, какие буквы на какие заменять
(буквы могут быть из любых алфавитов)

а как можно Excel-e транслитеровать текст из армянского языка на английский

Язык VBA не учил, не работал на нём, поэтому хотел бы узнать мнение специалиста о степени пригодности VBA для создания транслитератора, работающего по правилам систем транслитерации translit1 и translit2 (http://translit1.com/).

Читать еще:  Выделить повторяющиеся значения в excel

Замените vbTextCompare на vbBinaryCompare — и всё заработает

Function Translit(ByVal txt As String) As String
iRussian$ = «абвгдеёжзийклмнопрстуфхцчшщъыьэюяАБВГДЕЁЖЗИЙКЛМНОПРСТУФХЦЧШЩЪЫЬЭЮЯ»
iTranslit = Array(«», «a», «b», «v», «g», «d», «e», «e», «zh», «z», «i», «y», «k», _
«l», «m», «n», «o», «p», «r», «s», «t», «u», «f», «h», «ts», «ch», _
«sh», «sch», «», «y», «», «e», «yu», «ya», «A», «B», «V», «G», «D», _
«E», «E», «Zh», «Z», «I», «Y», «K», «L», «M», «N», «O», «P», «R», «S», _
«T», «U», «F», «H», «Ts», «Ch», «Sh», «Sch», «», «Y», «», «E», «Yu», «Ya»)
For iCount% = 1 To 66
txt = Replace(txt, Mid(iRussian$, iCount%, 1), iTranslit(iCount%), , , vbTextCompare)
Next
Translit$ = txt
End Function

Пытался сделать Регистр по другому, ибо перевод в составные большие буквы не устраивает. Ибо имеем вместо «Ч» — «CH», а требуется «Ch»
НО! Не смотря на расширенный массив, всё равно всё переводит в мелкий шрифт.
Подозреваю что нужно писать проверку, но как это делается не знаю.

Удобно делать транслитерацию с помощью Punto Switcher, у него есть такая функция, можно назначить сочетание клавиш, например Ctrl+Alt+T и транслитерировать любой выделенный текст (в Экселе в т.ч.) нажав ее.

Почему же нельзя обратно? Если в начале прогнать на проверку (хотябы основных) сочетания латинских букв и заменить их кириллицей, то оставшиеся символы можно будет прогнать вашим скриптом.

О, разобрался почему регистр не учитывался в access (в ворде все работало)! 🙂
вот так теперь учитывается:
txt = Replace(txt, Mid(iRussian$, iCount%, 1), iTranslit(iCount%), , , vbBinaryCompare)
txt = Replace(txt, UCase(Mid(iRussian$, iCount%, 1)), UCase(iTranslit(iCount%)), , , vbBinaryCompare)

Ну так а вы указали в макросе, что надо преобразовывать текст ячейки?

Попробуйте добавить ниже такие макросы:

PS: Можете использовать функцию транслита как формулу в ячейке.
К примеру, в ячейке A1 у вас расположен исходный текст.
В ячейке справа напишите формулу =Translit(A1)
и увидите аналогичный результат.

Не могу запустить ни один из Ваших макросов.
Win7x64 Office2010x64
В настройках EXCEL
Параметры макросов — Включить все макросы
Доверять доступ к объектной модели проектаVBA
(перезагрузку делал — всего)
Порядок запуска макроса:
1. Копирую с сайта код
2. EXCEL (Alt+F11), Insert > Module, (Ctrl+V), (Ctrl+S), (Alt+Q)-выход
3. EXCEL в формате *.xls
4. Выделяю ячейку с Русским языком (A1)
5. (Alt+F8)-макросы, «ПримерИспользованияФункцииTranslit» > выполнить
6. Показывает
7. Строка «Проверка Работы ТРАНСЛИТА»
Преобразована в строку «Proverka Rabot’y TRANSLITA»
Подскажите что не так? Почему не переводит ячейку?

Странно — почему то все равно в нижний регистр все приводит..

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector