Запись текста в обратном порядке на основе формулы с циклической ссылкой

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: если вам требуется запись текста в обратном порядке, можно воспользоваться кодом VBA. Однако, существует нетривиальный способ выполнить задачу с помощью формулы.

Техника, описанная в этой заметке не совсем обычна. Предлагается формула, основанная на циклической ссылке. Как правило, циклические формулы – зло, но иногда они полезны (см., например, Использование циклических ссылок для решения уравнений итерационным способом).

Выполните следующие действия.

Пройдите по меню ФАЙЛ –> Параметры. Перейдите на вкладку Формулы, поставьте галку напротив Включить итеративные вычисления. Установите Предельное число итераций – 100 (рис. 1). Нажмите Ok.

Рис. 1. Включить итеративные вычисления

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

Введите любую фразу в А1, например, телефон. В ячейке B1 введите формулу =ЕСЛИ(ДЛСТР(B1)<ДЛСТР(A1)+1;B1&ПСТР(A1;ДЛСТР(A1)+1-ДЛСТР(B1);1);ЕСЛИ(ПСТР(B1;1;1)<> "0";B1;ПРАВСИМВ(B1;ДЛСТР(A1))&" ")) и нажмите клавишу Ввод (Enter). После этого в ячейке отразится результат первой итерации – ноль плюс последний символ, введенной в А1 строки, в нашем случае – 0н. Это нормально. Продолжайте нажимать F9. Длина текста в В1 при каждом нажатии будет увеличиваться на один символ. Можно остановиться, когда при очередном нажатии первый ноль удалится. В В1 имеется текст, обратный тексту в А1. Чтобы начать всё сначала, нажмите F2 (перейдя в режим редактирования формулы), и опять нажмите Ввод.

Проследим за работой формулы: допустим, вы набрали в ячейке A1 телефон. Когда формула стартует, значение в В1 равно 0. Формула берет значение из В1 (ноль) и объединяет его со значением, возвращаемым функцией ПСТР(A1;ДЛСТР(A1)+1-ДЛСТР(B1);1). Функция ПСТР возвращает заданное число знаков (третий аргумент = 1) из строки текста (первый аргумент = А1), начиная с указанной позиции – второй аргумент = ДЛСТР(A1)+1-ДЛСТР(B1), принимающий значение 7 при первой итерации. В результате первого расчета вы получаете 0н (рис. 2).

Рис. 2. Результат работы первой итерации формулы

С каждым последующим нажатием F9 изменяется только второй аргумент функции ПСТР – ДЛСТР(A1)+1-ДЛСТР(B1). Т.к., ДЛСТР(B1) растет на единицу при каждом нажатии, значение второго аргумента уменьшается на единицу при каждом нажатии. И вся формула возвращает последовательно символ за символом из строки А1, двигаясь справа налево (рис. 3). В конце концов вы получите в В1 текст 0нофелет (рис. 4).

Рис. 3. При каждом нажатии на F9, вы получаете дополнительный символ

Рис. 4. После того, как все символы из А1 напечатаны в В1, всё еще остается лишний начальный ноль

Наконец, когда первый аргумент функции ЕСЛИ – ДЛСТР(B1)<ДЛСТР(A1)+1 – перестал возвращать значение ИСТИНА, т.е., когда длина текста в В1 стала на один символ больше, чем длина текста в А1, формула переходит ко второй своей части. Она берет все правые символы из В1, кроме первого и добавляет пробел – ПРАВСИМВ(B1;ДЛСТР(A1))&" " (рис. 5). Пробел нужен чтобы при «лишнем» нажатии F9 (после того, как ноль уже удален), формула перестала добавлять новые символы. Если пробел не добавить, то еще одно нажатие приведет к еще одной (последней) итерации формулы и текст станет нофелетт.

Рис. 5. После того, как длина В1 становится больше длины А1, удаляется первый ноль

Дополнительные сведения: хотя эта формула заменяет код VBA, она имеет ограниченное применение. Например, ее нельзя скопировать вниз вдоль столбца. Если вы измените значение в А1, значение в В1 не изменится. Вам нужно нажать F2, Ввод и несколько раз F9.

См. также несколько вариаций на эту тему на форуме MrExcel: добавление точки после каждого символа, и еще, и еще.

Резюме: вместо кода VBA вы можете использовать формулу с циклической ссылкой для записи текста в обратном порядке.

Источник. Идея Andrew Fergus.