Транслитерация в Excel — Советы по WORD и Excel

Содержимое скрыть 1 Формула транслитерации с использованием стандартных функций Excel 1.1 Формула для 64 уровней вложенности без прописных букв b и b 1.2 Скачать файл шаблона с транслитерацией 1.3 Формулы транслитерации, итоги 2 Функция VBA 3 Заменить русские буквы английскими буквами в Excel и наоборот

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

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

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

Excel и Google-таблицы: от новичка до эксперта

Практический онлайн-курс, где вы освоите все функции Excel и Google-таблиц. Занятия проходят на интерактивных тренажерах – полной копии интерфейса Microsoft Excel.

1C: Бухгалтерия 8.3

Практический онлайн-курс, после которого вы сможете быстро и уверенно проводить любые операции в 1C. Сможете претендовать на более высокую зарплату или начать работу на фрилансе.

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

Наименее элегантное, но самое простое решение проблемы. В Excel есть функция REPLACE для замены символов. Формула принимает 4 аргумента в качестве входных данных:

Текст для обработки, в котором будет произведена замена (текст)
Фрагмент для замены (old_text)
На что заменить (новый_текст)
Какой фрагмент встречается по смыслу, подлежащему замене. (число_звук)

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

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

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

В русском алфавите 33 буквы
Идеальная формула транслитерации должна сохранять заглавные буквы во время замены. К счастью, функция REPLACE чувствительна к регистру. Получается, нужно сделать не 33, а 66 замен. Но
в Excel есть ограничение на количество уровней вложенности в формуле: 64.

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

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

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

Вы знаете слова, которые начинаются на боб? 🙂

Похоже, их пропустили. Так что давайте сохраним два уровня вложенности и настроимся на пределы Excel (66 — 2 = 64)!

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

= ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАВИТЬ (ПОДСТАВИТЬ)) (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ПОДСТАВИТЬ (ПОДСТАВИТЬ) (ЗАМЕНА (A1; «)»; « В »;« В »);« Г »;« Г »);« Д »;« Д »);« Е »;« Д »);« Е »;« Йо »);« Ж »;« Ж » «);» З «;» Z «);» И «;» I «);» Y «;» Y «);» К «;» K «);» Л «;» L «);» М »;« M »);« N »;« N »);« O »;« O »);« P »;« P »);« P »;« R »);« C »;« S »); «Т»; «Т»); «У»; «U»); «Ф»; «F»); «Х»; «Х»); «Ц»; «Ц»); «Ч»; «Ч»); «Ш»; «Ш»); «Щ»; «Щ»); «Y»; «Y»); «А ТАКЖЕ»; «А ТАКЖЕ»); «U»; «Ю»); «Я ЕСМЬ»; «Я»); «а»; «а»); «б»; «б»); «с»; «v»); «грамм»; «грамм»); «д»; «г»); «А также»; «А также»); «А также»; «йо»); «е»; «ж»); «з»; «з»); «е»; «я»); «у»; «у»); «к»; «к»); «L»; «L»); «м»; «м»); «п»; «п»); «или»; «или»); «п»; «п»); «п»; «r»); «c»; «s»); «t»; «t»); «y»; «u»); «f»; «f»); «x»; «kh»); «ц»; «ц»); «час»; «ч»); «ш»; «ш»); «u»; «щ»); «ъ»; «у»); «с»; «y»); «b»; «»); «e»; «e»); «yu»; «yu»); «i»; «ya»)

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

Вышеупомянутая формула, основанная на функции REPLACE с различными условиями, может быть изменена: вместо явного написания текстовых значений вы можете основывать ее на определенных ячейках. Почему такой подход дешевле? Тот факт, что формулу менять не нужно, а данные для замены представлены в удобном для чтения и редактирования формате в столбцах A и B.

Транслитерация с таблицей подстановок и формулой с 64 уровнями вложенности

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

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

Подход к созданию таких мега-формул имеет свои плюсы и минусы. Есть только один недостаток — они громоздкие, и их не так-то просто набрать сразу. Поэтому их нужно где-то хранить и при необходимости копировать.
Плюсы в основном связаны с недостатками кода VBA:

По иронии судьбы формулы электронных таблиц будут работать в любой версии Excel на любом компьютере, даже с самыми строгими политиками защиты данных
Их легко редактировать, если вам нужно переделать правила транслитерации, и они довольно просты
Переносной, т.е формулы не «улетают», если их отправить кому-то и открыть на другом компьютере

Функция на VBA

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

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

Функция транслита (x как строка) как строка cyr = «abvgdeozhziyklmnoprstufkhtschshshchshch’yueyuya» 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 «) Для i = от 1 до 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 варианта замены в дополнение к основному:

Обратная транслитерация (с латиницы на кириллицу)
«Изменить макет» — asdf -> fiva
Замена букв в едином стиле — с кириллицы на латиницу
Аналогичная замена английских букв, похожих на русские, на истинно русские

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