понедельник, 29 августа 2016 г.

Как транслитерировать текст в Excel (русский текст преобразовать в английский транслит)

14:50 By , No comments

Очень часто требуется преобразовать русский текст (кириллицу) в английский (транслит), не перевести, а написать русские слова английскими буквами.

Такое, например, может понадобиться для импорта данных в какую-нибудь программу, базу данных, которая не поддерживает русские буквы. Задача распространенная, но стандартными средствами Excel не решается.
как преобразовать английй текст в транслит в Excel
Мы предлагаем вам два варианта решения данной задачи.

Первый способ транслитерировать русский текст

Первый способ транслита русского текста достаточно топорный — с использованием приложения Punto Switcher от Яндекс.
Скачиваем программу Punto Switcher на официальном сайте: http://punto.yandex.ru/
Punto Switcher — программа, которая автоматически переключает раскладку клавиатуры. Она знает, что «ghbdtn» — это «привет», а «пщщв ьщктштп» — это «good morning». 
Так вот, данная программа, помимо своей основной функции можете переводить текст в транслит, менять регистр выделенного текста (сделать из «иванов» в «ИВАНОВ») и другое.
Устанавливаем программу себе на компьютер, в системном трее должен появится значок программы
punto-switcher
Если зайти в настройки (правой кнопкой мыши по значку, выбрать настройки, горячие клавиши), то можно увидеть, что по умолчанию установлено переводить текст в транслит с помощью горячих клавиш Alt+Scroll Lock
Горячие клавиши в Punto Switcher
К сожалению, в самом Excel программа транслитерировать текст не сможет, необходимо промежуточное действие. Копируем столбец с русскими буквами, открываем блокнот на компьютере (Notepad) и вставляем туда текст, далее выделяем текст в блокноте и нажимаем клавиши Alt+Scroll Lock, текст автоматически поменяется на транслит.
Далее вы просто копируете этот текст и вставляете обратно в Excel.
Это удобно если нужно например перевести в translit, например один объемный столбец данных на русском языке, но иногда преобразование текста в транслит требуется с помощью формул Excel, поэтому рассмотрим второй способ с использованием пользовательской функции Excel, написанной на VBA

Второй способ перевода русского текста в транслит в Excel с помощью пользовательской функции

Есть специальный макрос для создания пользовательской функции.
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
29
30
31
32
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
Переключите раскладку на русский язык!! Копируем данный код, открываем среду VBA, для это нажмите правой кнопкой на активный лист и выберите пункт «Исходные данные», либо нажмите Alt+F11далее через меню выберите новый программный модуль (Insert — Module)
Открываем среду VBA для вставки кода
и в открывшееся окно вставляете данный код и закрываете это окно.
После этого в любом месте данного листа (а при вставке кода с помощью Insert — Module — в любом месте данной книги) вы сможете использовать пользовательскую функцию, которую можно вызвать выбрав:
Вставка — Функция (Insert — Function)
в Excel 2013 Функция — Вставить функцию
Выбираем Категория: определенные пользователем, в данном списке должна появится наша функция Translit
функция транслит
Можно просто в нужной ячейке набрать =Transit (ссылка_на_русский_текст) 
Как преобразовать тект в транслит - функция Excel
После этого протягиваем формулу и получаем нужный нам результат.
Если вы присмотритесь в полученный результат, то увидите, что данные немного отличаются, например
Ivanov Sergej Petrovich и Ivanov Sergey Petrovich
то есть буква «й» преобразовалась по-разному, если для вас это критично, то нужно будет немного поменять указанный выше код. В коде есть две таблицы — одна на русском языке, другая латиницей. Не трудно догадаться, что вам необходимо поменять нужные вам буквы. В данном случае в коде нужно найти «j» и поменять на «y», если требуется, то по аналогии поступаем с другими буквам.
Важно!
  • Начиная с Excel 2007 не забудьте сохранить файл в формате поддержки макросов — xlsm, иначе макрос не сохранится;
  • Данный макрос необходимо вставлять в каждую отдельную книгу, в которой вы хотите, чтобы он работал;
  • Иногда требуется, чтобы была не встроенная функция, а макрос при запуске которого текст будет переводится в транслит, в этом случае нужно вставить код, указанный ниже. В итоге, для перевода текста в transit необходимо выделить нужный текст на русском языке (диапазон ячеек) и запустить макрос «Translit» и данный текст будет обработан.
  • Важно! Перед копированием данного кода, обязательно переключите раскладку клавиатуры на русский язык.
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
29
30
31
32
33
34
35
36
37
38
39
40
41
Sub Translit()
Dim Txt As Variant
Dim i As Integer
Dim J As Integer
Dim c As String
Dim flag As Integer
Dim outchr As String
Dim outstr 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 Each Txt In Selection.Cells
 outstr = Empty
 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
 Txt.Value = outstr
 Next Txt
End Sub
Итак, надеюсь вы нашли ответ на свой вопрос о том, как проще всего транслитерировать текст в Excel (преобразовать текст на русском языке в транслит).
Ставьте лайки, подписывайтесь на наши группы в социальных сетях и следите за следующими уроками.
SirExcel — безграничные возможности Excel

0 коммент.:

Отправить комментарий