Проверка кода на наличие русских букв с помощью функции LAMBDA

В декабре 2020 года Microsoft анонсировал функцию LAMBDA, которая позволяет определять новые функции, написанные на языке формул Excel. На сегодня функция доступна только для Office 365. Функция LAMBDA поддерживает рекурсию, т.е. может вызывать сама себя. Это позволяет организовать циклы. Ранее я опубликовал заметку, демонстрирующую мощь LAMBDA. А недавно возникла практическая задача, для решения которой LAMBDA подошла, как нельзя лучше! Я готовил к публикации конспект книги Алексея Васильева Числовые расчеты в Excel. Код VBA, полученный с помощью программы распознавания текста, не работал. Я предполагал, что при обработке pdf файла некоторые символы были неверно распознаны. Например, где-то «с» английское, а где-то – русское. Для обычных формул Excel эта задача – не из простых. A LAMBDA справилась!

Рис. 1. Формула ЧРУС() на основе LAMBDA

Скачать заметку в формате Word или pdf, примеры в формате Excel

Синтаксис функции LAMBDA

В общем виде синтаксис функции LAMBDA:

Рис. 2. Синтаксис функции LAMBDA

Последний аргумент функции LAMBDA – расчет (он же вычисление). Все предыдущие аргументы – параметры. Обязательный аргумент один. И это вычисление. Параметров может быть до 253.

Любопытной особенностью функции LAMBDA является отсутствие ссылок на ячейки в ее синтаксисе. Вместо них используются условные обозначения – параметры. Для того, чтобы можно было протестировать функцию в ячейке, разработчики добавили в синтаксис вызов функции

Вызов функции можно реализовать через передачу ей значений параметров:

Рис. 3. Тестирование функции LAMBDA

В нашем примере LAMBDA имеет два параметра – х (текст) и k (счетчик числа русских букв). В ячейке В2 они переданы после окончания кода функции в скобках – (А2;0). Поскольку в коде функции есть рекурсивная ссылка на неё саму, функцию ЧРУС не будет работать, пока не присвоить ей это имя.

Код и имя функции LAMBDA

Чтобы присвоить имя функции LAMBDA, пройдите Формулы –> Диспетчер имен, и введите ЧРУС в поле Имя в окне Создание имени.

Рис. 4. Присвоении функции имени

В поле Диапазон введите код функции:

Для упрощения восприятия я ввел две вспомогательных функции: ГОЛОВА(х) и ХВОСТ(Х):

Рис. 5. Вспомогательные функции

Эти функции я также определил с помощью имен. ГОЛОВА(х) возвращает первый символ текста, а ХВОСТ(х) – все остальные символы.

Как работает функция LAMBDA

Функция LAMBDA имеет три аргумента: два параметра…

… и расчет…

Суть расчета заключается в двух проверках и рекурсии. Первая проверка позволяет выйти из цикла, когда все символы проверяемого текста x просмотрены:

Если просматриваемый текст пустой…

вернуть…

… если же не пустой, приступить ко второй проверке…

Если первый символ текста х (именно он возвращается функцией ГОЛОВА) принадлежит русскому алфавиту, т.е., его код лежит в диапазоне от 192 до 255, то…

… находясь внутри функции ЧРУС запустить функцию повторно (рекурсивно) с параметрами х = ХВОСТ(x) и k = k + 1. Т.е., повторно запускаемая функция ЧРУС принимает не весь текст х, а его часть – без первого символа, который только что был просмотрен и изучен, а параметр k (счетчик) увеличен на единицу, так как символ оказался русским…

Если же символ не принадлежит русскому алфавиту…

… запустить функцию повторно (рекурсивно) с параметрами х = ХВОСТ(x) и k = k. Т.е., тоже передать функции не весь текст х, а его часть – без первого символа, а счетчик k не менять, так как символ был не русским…

В итоге для текста в ячейке А2 функция ЧРУС() вернет значение 9 (см. рис. 3). И действительно, в тексте «Sub Дихотомия()» девять русских символов. Для ячейки А2 этот вывод тривиален, а вот для ячейки А15 мы ожидали значение 0, так как это код VBA, но вернулось значение 1. Следовательно надо заменить на английскую одну из букв, которые одинаково выглядят в обоих языках. Русской оказалась первая буква строки – «а».

Перед запуском функции ЧРУС(), я дополнительно исключил из проверки строки с комментариями, начинающиеся с апострофа, а также исключил возможный пробел в начале строки, добавив функцию СЖПРОБЕЛЫ():

Можно обойтись без вспомогательных функций и повысить наглядность формулы, если добавить конструкцию с LET. Назовем новую функцию ЧРУС1(x;k):