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

Рубрика: 7. Полезняшки Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


Прокомментировать