Познакомимся с вариантами преобразования формулы Excel в текст (в результате получается не значение ячейки, а формульное выражение в текстовом виде, например, "=A1+A2", "=СЕГОДНЯ()" и т.д.).
Возникают ситуации когда необходимо отобразить в ячейке не значение формульного выражения, а именно ее текстовую запись.
С помощью режима отображения формул мы можем увидеть все формульные выражения листа и книги в текстовом виде, однако если мы хотим показать формулу не для всех, а для каких-то конкретных ячеек, то такой вариант не подходит.
Однако вне зависимости от целей преобразования нам понадобится способ извлечь из ячейки строку с формулой.
Преобразование формулы в текст в Excel
Начнем с более простого варианта, а именно предположим, что нам нужно преобразовать формулу в текст в самой ячейке (т.е. заменить значение на текстовую запись). Тогда в этом случае есть несколько способов преобразования:
- Поменять формат ячейки на текстовый, а затем произвести вычисление формулы;При этом для каждой ячейки нужно будет вручную производить изменение.
- Добавить апостроф (символ «‘») перед знаком равно (символ «=») в формульном выражении.В данном варианте подставить апостроф можно как вручную, так и через замену («=» на «‘=» с помощью инструмента «Найти и заменить»).
Теперь перейдем к более общему случаю и рассмотрим 2 основных варианта перевода формулы в текст (т.е. получить текстовую запись):
- Функция Ф.ТЕКСТ (доступна начиная с версии Excel 2013);
- Пользовательская функция (UDF).
Первый способ позволит нам перевести формулу в текст стандартными средствами в Excel, а во втором способе мы напишем пользовательскую функцию, которая будет выполнять аналогичные по функционалу преобразования (что и в первом способе) с небольшими видоизменениями.
Давайте подробнее остановимся на каждом из них.
Функция Ф.ТЕКСТ в Excel
Начиная с версии Excel 2013 для применения доступна функция Ф.ТЕКСТ (FORMULATEXT в английской версии):
Ф.ТЕКСТ(ссылка)
Возвращает формулу в виде строки.
- Ссылка (обязательный аргумент) — ссылка на ячейку или диапазон ячеек.
Перейдем к примерам. Применим Ф.ТЕКСТ, в качестве аргумента укажем ссылку на произвольную ячейку, где содержится какое-либо формульное выражение:
При этом в зависимости от выбранного у вас параметра отображения стиля ссылок (A1 или R1C1) формула автоматически будет подстраиваться под формат записи:
Замечания
При работе с данной функцией есть несколько важных особенностей, на которые необходимо обратить внимание:
- Аргумент "Ссылка" может ссылаться на другие листы и книги;
- Если аргумент "Ссылка" не содержит формульное выражение или содержит ссылку на закрытую книгу, то в результате будет возвращено значение ошибки.
Пользовательская функция (UDF)
При использовании версии Excel 2010 или более ранней, стандартными инструментами Excel воспользоваться уже не получится.
Однако данную проблему мы можем решить с помощью создания пользовательской функции (UDF), которая позволит преобразовать формулу в текст в различных вариантах записи в зависимости от стиля ссылок.
Перейдем в редактор Visual Basic (сочетание клавиш Alt + F11), вставляем новый модуль и добавляем следующий код:
|
1 2 3 |
Public Function FText(myRange As Range) As String FText = myRange.FormulaLocal End Function |
Как обычно, к новой функции мы можем обратиться либо через мастер функций (выбрав ее из категории Определенные пользователем), либо ввести формульное выражение в пустую ячейку:
Как видим результат работы пользовательской функции FText получился точно таким же, как и у стандартной Ф.ТЕКСТ.
В данном примере мы использовали свойство диапазона FormulaLocal, которое позволяет преобразовать формульное выражение со стилем ссылок A1, однако в зависимости от предпочтений стиль записи можно изменить, а именно поменять свойство FormulaLocal на один из следующих вариантов:
- Formula — формат A1 (англоязычная формула);
- FormulaR1C1 — формат R1C1 (англоязычная);
- FormulaLocal — формат A1 (неанглоязычная/местная);
- FormulaR1C1Local — формат R1C1 (неанглоязычная/местная).
Выбираем необходимый формат записи, корректируем код FText в VBA и на выходе получаем итоговое преобразование:
Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!