Транслитерация текста
(описание используемых условных обозначений, а также список других публикаций канала по теме электронных таблиц, находится здесь).
Транслитерация – это запись текста символами письменности другого языка. У нас наиболее часто для практических нужд используется написание русских слов латиницей, например, «Иван Петрович Сидоров» будет выглядеть как “Ivan Petrovich Sidorov”.
Ниже предлагается описание процесса создания в среде электронных таблиц средства для выполнения транслитерации небольших по объёму текстов, но сначала хотелось бы заострить внимание на нескольких следующих моментах.
Во-первых, в заметке «Расчёт сырьевой себестоимости производства краски» использована формула, которая при помощи функции «вертикального просмотра таблицы»
ВПР( ; ; ; )
VLOOKUP( ; ; ; )
выполняет в указанном ей диапазоне ячеек поиск названия сырьевого компонента краски и возвращает соответствующее численное значение – закупочную цену этого компонента. Те, кто хорошо усвоил работу функции
СУММЕСЛИ( ; ; )
SUMIF( ; ; )
(Пособие, с. 52) могут догадаться, что ей в данном случае легко заменить этот самый «вертикальный просмотр», так как требуется получение именно числа. Для решения же рассматриваемой здесь задачи так сделать уже не получится (ну или будет крайне затруднительно), поскольку понадобится из совокупности данных извлекать текстовые символы.
Во-вторых, часто встречается ситуация, когда разработка расчётной программы в среде электронных таблиц (не забываем, что табличные процессоры позволяют выполнять «программирование без программирования» – Пособие, с. 30) осуществляется одними людьми, а её использование – другими. В этом случае имеет смысл позаботиться о снижении риска непреднамеренного повреждения пользователем формул в ячейках. Кроме установки защиты ячеек (Пособие, с. 55), можно пользоваться другим приёмом, описанным ниже.
В-третьих, стоит помнить, что транслитерация по сути своей – просто замена в тексте одних символов другими , поэтому у получившейся «программы» возможности будут несколько шире – об этом также будет рассказано дальше.
Ну что ж, а теперь приступим от слов к делу.
Создайте новую книгу и сделайте в ней два листа (Пособие, с. 8), один назовите “interface”, а другой – “code”:
Это нужно для чёткого разграничения в книге областей, предназначенных для пользователя (лист “interface”) и разработчика (лист “code”). Содержимое листа “interface” следует отформатировать и заполнить так:
Ячейки диапазона “B2:B16” и диапазона “D2:D16” объединены, для них установлено обрамление, перенос содержимого по словам и выравнивание по верхнему краю ячейки. В ячейках столбцов “G” и “H” перечислены буквы русского алфавита и соответствующие им часто встречающиеся варианты транслитерования – дело в том, что нет единого стандарта в соответствии с которым оно должно выполняться (например, буква «ц» может быть представлена и как “ts”, и как “c”). Дополнительно хотелось бы обратить внимание на то, что хотя в русском алфавите 33 буквы, но в таблице транслитерации (диапазон “G2:H34”) заготовлено место с запасом – под 50 символов.
С буквами «ъ» и «ь» получается небольшая загвоздка, так как они при транслитерации обозначаются с помощью апострофа, а в Excel этот знак используется с целью дать понять программе, что введённая в ячейку последовательность символов должна отображаться как есть и с ней не надо ничего пытаться делать (например, распознавать в ней формулу). Из-за этого в ячейку “H31”нужно ввести два апострофа, а в ячейку “H29” – три (но отображаться на листе при этом будут один и два апострофа соответственно). Альтернативный способ – ввести в ячейки формулы, результатом выполнения которых будут нужные текстовые символы:
="''"
="'"
Теперь можно активировать лист “code” и перед вводом необходимых формул отформатировать его как показано на рисунке ниже – в столбце “D” при помощи автозаполнения (Пособие, с. 40) нужно проставить числа от 1 до 1000, а в “A1” поместить фразу «Подготовленный текст:» :
Введённые данные будут играть вспомогательную роль и облегчать ориентирование. В ячейку “B1” нужно ввести формулу такого вида:
=ПЕЧСИМВ(СЖПРОБЕЛЫ(interface!B2))
=CLEAN(TRIM($interface.B2))
Данная формула берёт текст из ячейки “B2” листа “interface” и проводит его подготовку: убирает из него лишние пробелы и специальные непечатаемые знаки. Также следует обратить внимание на форму записи адреса ячейки, находящейся в другом листе книги.
В таких случаях сначала пишется имя листа, после него ставится восклицательный знак, а уже затем – собственно адрес ячейки.
В таких случаях сначала пишется имя листа (знак доллара "$" перед ним можно опустить), а затем через точку – собственно адрес ячейки.
Прописывать имя листа вручную не обязательно – достаточно в процессе набора формулы переключиться на интересующий лист, щёлкнув по его ярлычку и там выбрать ячейку или их диапазон, после чего продолжить ввод формулы.
После подготовки текст нужно «разобрать» на отдельные символы, для этого в ячейку “E1” вводится формула
=ПСТР($B$1;D1;1)
=MID($B$1;D1;1)
Эту формулу маркером заполнения следует размножить вниз, до ячейки “E1000”. Далее можно приступать к замене русских букв на буквы латинские (или их комбинации). В “F1” нужно ввести следующую формулу:
=ЕСЛИ(E1="";"";ЕСЛИ(СЧЁТЕСЛИ(interface!$G$2:$G$51;E1)=0; E1 ; ВПР(E1; interface!$G$2:$H$51; 2; 0)))
=IF(E1="";"";IF(COUNTIF($interface.$G$2:$G$51;E1)=0; E1; VLOOKUP(E1; $interface.$G$2:$H$51; 2;0)))
Работу такой формулы для большей наглядности и понятности лучше представить в виде блок-схемы алгоритма, по которому она действует:
Формулу в “F1” также надо откопировать вниз до “F1000” – получится набор ячеек, в которых распределён транслитерованный текст. Остаётся только собрать его в единое целое, но прежде нужно сказать о следующей особенности работы табличного процессора с текстом: он «малочувствителен» к регистру – многие функции просто игнорируют разницу между заглавными (прописными) и строчными буквами. В создаваемой программе транслитерации предполагается, что прописные и строчные должны после обработки различаться (например «Вася» должен транслитероваться как “Vasya”, а не как “vasya”). Чтобы такое осуществить, нужно сначала в “G1” ввести следующую формулу:
=ЕСЛИ(СОВПАД(E1;ПРОПИСН(E1));ПРОПНАЧ(F1);F1)
=IF(EXACT(E1;UPPER(E1));PROPER(F1);F1)
Рассмотрим использованные в ней функции. Первая – это
ПРОПНАЧ( )
PROPER( )
Она обрабатывает текстовую строку так: начинает её с заглавной буквы и делает прописной первую букву в каждом слове текста, преобразуя при этом все другие буквы в строчные. Например строка «абв ГДЕ жЗи.кЛМ» переделывается в «Абв Где Жзи.Клм». Для нашей программы такая функция будет, в частности, превращать “ch” в “Ch”.
Функция
ПРОПИСН( )
UPPER( )
делает все буквы в текстовой строке прописными. Интересней работает
СОВПАД( ; )
EXACT( ; )
– она производит сравнение двух текстовых строк между собой на предмет их совпадения, возвращая значение ЛОЖЬ / ИСТИНА, причём именно эта функция различает прописные буквы и строчные. В итоге формула в “G1” рассматривает символ и если он является прописной буквой, то выполняется соответствующее этому преобразование знаков транслитерации.
В ячейку “G2” нужно ввести формулу вида
=G1 & ЕСЛИ(СОВПАД(E2;ПРОПИСН(E2));ПРОПНАЧ(F2);F2)
=G1 & IF(EXACT(E2;UPPER(E2));PROPER(F2);F2)
Работает она аналогично формуле в “G1”, только вначале ещё присоединяется содержимое ячейки сверху – если эту формулу откопировать вниз, то в “G1000” получится транслитерованный, «собранный» воедино из отдельных знаков, текст, то есть конечный результат работы программы, поэтому его оттуда нужно «перебросить» в лист “interface”, поместив там в ячейке “D2” формулу:
=code!G1000
=$code.G1000
Теперь пора рассказать про обещанный в самом начале способ дополнительно обезопасить формулы. Он весьма прост: лист книги, формулы на котором выполняют основную работу, можно скрыть. Для этого следует выполнить щелчок правой кнопкой мыши по ярлычку скрываемого листа и в появившемся контекстном меню воспользоваться соответствующим пунктом:
Скрытые листы при необходимости также легко снова показать – делаем правый щелчок по ярлычку любого видимого листа и выбираем в меню нужный пункт:
После этого появится диалоговое окно, в котором останется указать лист, который требуется сделать видимым:
Программа транслитерации готова к работе – ниже приведён результат преобразования текста, составленного из нескольких панграмм (примеры взяты отсюда):
Стоит отметить, что программа позволяет использовать разные варианты замены для одних и тех же букв русского алфавита. Если в ячейке “H25” поменять “ts” на “с”, то приведённый выше обработанный текст с панграммами станет таким:
S''esh' eshchyo e'tih myagkih francuzskih bulochek, da vypej chayu. Yuzhno-e'fiopskij grach uvyol mysh' za hobot na s''ezd yashcheric. Myuzikl-buff «Ob''evshijsya ognedyshashchij yozh prostuzhaetsya noch'yu» (v 12345 scenah i 67890 e'pizodah).
Как уже говорилось, одной лишь транслитерацией возможности программы не ограничиваются – вот несколько примеров.
1. Замена знаков препинания их буквенными обозначениями
Если в таблице символов и их замен указать только это (непосредственно перед «Тчк» и «Зпт» в ячейках поставлены пробелы):
то можно преобразовать панграммы к виду:
Съешь ещё этих мягких французских булочек Зпт да выпей чаю Тчк Южно-эфиопский грач увёл мышь за хобот на съезд ящериц Тчк Мюзикл-буфф «Объевшийся огнедышащий ёж простужается ночью» (в 12345 сценах и 67890 эпизодах) Тчк
Также можно было бы оставить все варианты транслитерации на месте, добавив к ним ещё два таких (английские наименования точки и запятой, взятые в квадратные скобки) – всё-таки место под дополнительные замены заранее предусмотрено:
В результате получается:
S''esh' eshchyo e'tih myagkih frantsuzskih bulochek[Comma] da vypej chayu[Full Point] Yuzhno-e'fiopskij grach uvyol mysh' za hobot na s''ezd yashcherits[Full Point] Myuzikl-buff «Ob''evshijsya ognedyshashchij yozh prostuzhaetsya noch'yu» (v 12345 stsenah i 67890 e'pizodah)[Full Point]
Английские названия точки и запятой прописываются с заглавных букв – это следствие специфики работы использованных формул.
2. Удаление отдельных символов из текста
Программу можно настроить так, чтобы она просто удаляла из текста отдельные символы. Допустим из фразы «Однажды, обходя окрестности Онежского озера, отец Онуфрий обнаружил обнаженную отроковицу Оксану» нужно убрать все буквы «о». Для этого в таблице замен нужно оставить только указанную букву:
а в ячейку с заменой (в данному случае это должна быть “H2”) ввести формулу:
=""
Последнее необходимо, чтобы программа заменяла «о» именно на пустую текстовую строку (то есть фактически удаляла букву) и тогда получится требуемое:
днажды, бхдя крестнсти нежскг зера, тец нуфрий бнаружил бнаженную трквицу ксану
Если же оставить “H2” пустой, то программа начнёт подставлять вместо буквы «о» численное значение ячейки “H2” (то есть «ноль») и выйдет вот это:
0днажды, 0бх0дя 0крестн0сти 0нежск0г0 0зера, 0тец 0нуфрий 0бнаружил 0бнаженную 0тр0к0вицу 0ксану
3. Генерирование html-кода для веб-страницы
Данный способ использования программы может быть интересен тем, кто знаком с языком гипертекстовой разметки HTML. В заметке «Секретный алфавит, ассоциативная память и английский лексикон» (мой сайт / Дзен) среди прочего рассказывается о придуманном мною в детстве варианте написания шифрованных сообщений посредством замены букв на специальные научные и технические символы. Там же приведён перечень этих замен и два примера панграмм, демонстрирующих мой «секретный алфавит» в действии.
Из созданной программы для транслитерации можно сделать средство, производящее «автоматизированное шифрование» произвольной фразы моей «тайной азбукой», но это потребует небольшой подготовки. Для отображения на сайте каждый знак, заменяющий букву русского алфавита, оформлен в виде отдельного файла формата PNG, поэтому делаем так.
а) сохраняем с сайта shurichimik.narod.ru файлы-картинки c «секретными» изображениями букв в какую-нибудь папку на компьютере:
б ) в этой же папке создаём файл веб-страницы, содержащий следующий html- код:
в) указываем в программе транслитерации перечень замен:
Нетрудно видеть, что эти замены являются тегами для вставки в веб-страницу изображений, каждое из которых соответствует определённой букве.
Теперь в качестве исходного текста берём слова Пушкина А.С. «О сколько нам открытий чудных Готовят просвещенья дух И опыт сын ошибок трудных И гений парадоксов друг И случай бог изобретатель» (все знаки препинания предварительно были удалены) и пропускаем через программу, после чего сгенерированный html-код вставляем в нужное место веб-страницы, сохраняем изменения и открываем её браузером:
Как видно, в данном случае не требуется даже помнить «тайную азбуку» наизусть (или пользоваться шпаргалкой), чтобы составить «шифровку», и уж тем более нет необходимости писать её от руки на бумаге, а затем сканировать и обрабатывать скан, как это делалось ранее при создании и подготовке к публикации выше упомянутой заметки.
Перечень публикаций на канале