Как транслитерировать текст в Excel (русский текст преобразовать в английский транслит)
Очень часто требуется преобразовать русский текст (кириллицу) в английский (транслит), не перевести, а написать русские слова английскими буквами.
Такое, например, может понадобиться для импорта данных в какую-нибудь программу, базу данных, которая не поддерживает русские буквы. Задача распространенная, но стандартными средствами Excel не решается.
Мы предлагаем вам два варианта решения данной задачи.
Первый способ транслитерировать русский текст
Первый способ транслита русского текста достаточно топорный — с использованием приложения Punto Switcher от Яндекс.
Скачиваем программу Punto Switcher на официальном сайте: http://punto.yandex.ru/
Punto Switcher — программа, которая автоматически переключает раскладку клавиатуры. Она знает, что «ghbdtn» — это «привет», а «пщщв ьщктштп» — это «good morning».
Так вот, данная программа, помимо своей основной функции можете переводить текст в транслит, менять регистр выделенного текста (сделать из «иванов» в «ИВАНОВ») и другое.
Устанавливаем программу себе на компьютер, в системном трее должен появится значок программы
Если зайти в настройки (правой кнопкой мыши по значку, выбрать настройки, горячие клавиши), то можно увидеть, что по умолчанию установлено переводить текст в транслит с помощью горячих клавиш Alt+Scroll Lock
К сожалению, в самом Excel программа транслитерировать текст не сможет, необходимо промежуточное действие. Копируем столбец с русскими буквами, открываем блокнот на компьютере (Notepad) и вставляем туда текст, далее выделяем текст в блокноте и нажимаем клавиши Alt+Scroll Lock, текст автоматически поменяется на транслит.
Далее вы просто копируете этот текст и вставляете обратно в Excel.
Это удобно если нужно например перевести в translit, например один объемный столбец данных на русском языке, но иногда преобразование текста в транслит требуется с помощью формул Excel, поэтому рассмотрим второй способ с использованием пользовательской функции Excel, написанной на VBA
Второй способ перевода русского текста в транслит в Excel с помощью пользовательской функции
Есть специальный макрос для создания пользовательской функции.
Переключите раскладку на русский язык!! Копируем данный код, открываем среду VBA, для это нажмите правой кнопкой на активный лист и выберите пункт «Исходные данные«, либо нажмите Alt+F11 далее через меню выберите новый программный модуль (Insert — Module)
и в открывшееся окно вставляете данный код и закрываете это окно.
После этого в любом месте данного листа (а при вставке кода с помощью Insert — Module — в любом месте данной книги) вы сможете использовать пользовательскую функцию, которую можно вызвать выбрав:
Вставка — Функция ( Insert — Function )
в Excel 2013 Функция — Вставить функцию
Выбираем Категория: определенные пользователем, в данном списке должна появится наша функция Translit
Можно просто в нужной ячейке набрать =Transit(ссылка_на_русский_текст)
После этого протягиваем формулу и получаем нужный нам результат.
Если вы присмотритесь в полученный результат, то увидите, что данные немного отличаются, например
Ivanov Serge j Petrovich и Ivanov Serge y Petro vich
то есть буква «й» преобразовалась по-разному, если для вас это критично, то нужно будет немного поменять указанный выше код. В коде есть две таблицы — одна на русском языке, другая латиницей. Не трудно догадаться, что вам необходимо поменять нужные вам буквы. В данном случае в коде нужно найти «j» и поменять на «y», если требуется, то по аналогии поступаем с другими буквам.
- Начиная с Excel 2007 не забудьте сохранить файл в формате поддержки макросов — xlsm, иначе макрос не сохранится;
- Данный макрос необходимо вставлять в каждую отдельную книгу, в которой вы хотите, чтобы он работал;
- Иногда требуется, чтобы была не встроенная функция, а макрос при запуске которого текст будет переводится в транслит, в этом случае нужно вставить код, указанный ниже. В итоге, для перевода текста в transit необходимо выделить нужный текст на русском языке (диапазон ячеек) и запустить макрос «Translit» и данный текст будет обработан.
- Важно! Перед копированием данного кода, обязательно переключите раскладку клавиатуры на русский язык.
Итак, надеюсь вы нашли ответ на свой вопрос о том, как проще всего транслитерировать текст в Excel (преобразовать текст на русском языке в транслит).
Ставьте лайки, подписывайтесь на наши группы в социальных сетях и следите за следующими уроками.
Надстройка для транслитерации (кодирования) диапазона ячеек
При запуске надстройки в Excel формируется панель инструментов, при помощи которой можно выполнять следующие действия:
- Нажатием кнопки RU->EN выполнить транслитерацию выделенного диапазона ячеек
- Нажатием кнопки EN->RU выполнить транслитерацию выделенного диапазона ячеек
- В выпадающем списке «Режим работы» можно переключить режим замены символов — второй пункт выпадающего списка «Коды символов» вместо транслита включает режим замены символов их кодамиAscW (и обратный режим замены кодо исходным текстом, используя вызов функции ChrW)
- Нажатием последних двух кнопок (галочка и крестик) можно управлять видимостью листа надстройки, на котором расположена таблица соответствия символов кириллицы и латиницы
- Самая левая кнопка (со значком дискеты) выполняет сохранение изменений в файле надстройки, и создание её резервной копии в специально созданной папке.
Особенности работы надстройки:
1) Все формулы в обрабатываемом диапазоне заменяются значениями.
2) производится замена ТЕКСТА ячейки, а не её ЗНАЧЕНИЯ.
Таким образом, если в ячейке была формула, возвращающая логическое значение 0 (в ячейке отображается текст FALSE или ЛОЖЬ), то после обработки этой ячейки в ней окажется текст LO^ZH`)
3) Обратная транслитерация корректно работает только в режиме «Коды символов» — причина в том, что если в исходном тексте присутствуют как символы кириллицы, так и символы латиницы, то после применения функции "транслит" все кириллические символы заменяются латиницей, а латиница не изменяется. При обратном преобразовании макрос не знает, какие символы обрабатываемой ячейки до преобразования были латиницей, а какие — кириллицей, — потому заменяет ВСЮ латиницу на кириллицу.
Разницу в результатах вижно на этом скриншоте
4) Функции транслита можно применять и в ячейках листа Excel, как любые другие UDF:
Используйте для этого формулы =Translit2EN($A2;1) и =Translit2RU(F2;1), если требуется преобразование текста в коды символов,
или же формулы =Translit2EN($A2) и =Translit2RU(F2), если нужен именно транслит и обратный транслит.
(второй параметр формулы для режима «транслит» равен нулю, его можно опустить — он используется по-умолчанию)
Пример использования формул можно посмотреть в прикреплённом к статье тестовом файле Excel.
5) В таблице соответствия символов латиницы и кириллицы не должно быть повторяющихся кодов
Сейчас используется примерно такая таблица соответствия:
Почему перед некотороми из символов добавлен символ "крышечка" — станет понятно после прочтения этой статьи
(чтобы исключить проблемы с обратной транслитерацией)
6) После обработки диапазона ячеек производится автоматическое копирование этого диапазона в буфер обмена
(аналогично нажатию комбинации клавиш Ctrl + C)
7) В связи с необходимостью копирования диапазона в буфер обмена (и последующей вставки в другую программу), была отключена функция обработки выделенных несмежных диапазонов.
8) Программа обрабатывает ячейки только в используемом диапазоне листа, независимо от того, какой диапазон выделен изначально.
Т.е. вы можете выделить столбец целиком — макрос автоматически уменьшит высоту выделенной области, чтобы ограничить обрабатываемый диапазон только используемыми ячейками.
9) Программа перебирает все ячейки в цикле — поэтому обработка может работать медленно при больших диапазонах обрабатываемых данных.
При необходимости макрос можно существенно ускорить, если обрабатывать не текст ячеек, а их значения.
Транслитерация в Excel: как сделать русские буквы латинскими автоматически
Часто неопытные пользователи делают транслитерацию в excel вручную, а более опытные используют многоэтажные формулы с подстановкой значений.
В этой статье мы научимся создавать небольшую пользовательскую функцию, которая умеет делать транслитерацию в Excel. В данном случае не нужно знать VBA, поскольку текст функции есть в статье, его можно просто скопировать.
Видео для тех, кто не любит читать
Итак, у нас есть таблица с ФИО сотрудников. Нужно сделать транслитерацию этих данных.
1. Нажмем клавиши Alt + F11, чтобы перейти в редактор Visual Basic.
2. В окне редактора Visual Basic перейдем в меню Insert — Module
3. Выделите следующий текст функции, скопируйте его (Ctrl + C) и вставьте в пустое окно модуля (Ctrl + V)
Function Translit(Txt As String) As String
Dim Rus As Variant
Dim Eng As Variant
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
Обратите внимание, что в этом макросе знаки русского алфавита заменяются латиницей согласно ГОСТу. Если вам нужно, чтобы буквы заменялись иначе, измените их внутри макроса.
4. Закройте редактор Visual Basic. Данные сохранятся в нем автоматически.
5. Теперь установите курсор в первую ячейку, в которой нужно сделать транслитерацию.
6. Перейдите на вкладку меню Формулы — Вставить функцию.
7. В выпадающем списке Категория выберите Определенные пользователем.
8. Созданная для транслитерации функция называется Translit. Выделите ее и нажмите Ок.
9. Установив курсор в поле Txt, щелкните по первой ячейке, для которой нужно сделать транслитерацию. Нажмите Ок.
10. Скопируйте формулу до конца списка, потянув за нижний правый уголок выделенной ячейки. Транслитерация в Excel готова.
Теперь транслитерация ячеек excel будет производиться макросом автоматически, достаточно указать или вызвать формулу =Translit(). Это удобно, если у вас большой список слов для транслитерации и вручную сделать это сложно.
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Транслит
Если Вам часто приходится переводить кириллицу в транслит (а попробуйте-ка сделать это для фамилии "Кржижановский", например), то эта функция — для Вас.
Откройте редактор Visual Basic (Alt+F11), вставьте через меню новый пустой программный модуль (Insert — Module) и скопируйте туда текст этой функции:
Теперь на любом листе этой книги Вы можете использовать эту функцию, вставив ее через Мастер Функций:
из категории Определенные пользователем (User defined) :
В этом макросе варианты замены русских букв английскими эквивалентами представлены согласно ГОСТ. Если для вашей задачи нужны другие версии (например, русская "я" должна выводиться как "ja", а не как "ya" и т.д.), то можно свободно подкорректировать это в теле макроса. Как легко догадаться, два первых массива Rus и Eng как раз и кодируют эти подстановки.
Для новых версий Excel 2007-2010 и т.д. не забудьте сохранить вашу книгу как файл с поддержкой макросов (Macro Enabled Workbook) , т.е. в формате XLSM.