Как одна ячейка получает формулу другой ячейки в виде текста без использования VBA? Я вижу, что этот вопрос уже задан много раз, и ответ всегда заключается в написании пользовательской функции в VBA.
Однако, я нашел сообщение сделанное в 2006 году, в котором утверждалось, что он нашел решение, отличное от VBA, но ссылка, предоставленная в этом сообщении, уже сломан.
Хороший способ сделать это без VBA. Он использует макросы XL4 (это макросы, но это не VBA, как было задано).
Как показано на рисунке 1, ячейки A2: A4 содержат обычные формулы.
Переход к формулам → Определить имя, я определил два именованных диапазона (см. рис .2), с информацией, отображаемой в ячейках A6: B8.
Введите в ячейку B2 =FormulaAsText. Это приведет к извлечению формулы в ячейке A2 в виде текста.
Объяснение:
Именованный диапазон FormulaAsText использует =GET.CELL(info_type,reference). В этом случае ìnfo_type = 6 извлекает формулу, а reference = OFFSET(INDIRECT("RC",FALSE),0,-1) использует ячейку с 0 строками и -1 столбцами, смещенными от той, в которой используется формула.
Скопируйте B2 и вставьте в B3: B4. Это покажет формулы в A3: A4. Cell A4 показывает, что функция рабочего листа CELL извлекает только значения, а не формулы (в отличие от GET.CELL).
Так как FormulaAsText получает формулу из ячейки с фиксированным смещением (0, -1) из текущего, я определял другой диапазон FormulaAsText2, который использует смещение (строки, столбцы), считанные с листа сам. Клетки D2: D4 содержат =FormulaAsText2. Таким образом, ячейка D2 показывает содержимое ячейки B3 (=OffSET(D2,1,-2)), которая равна FormulaAsText. ячейки D3: D4 показывают содержимое самих себя. Это добавляет некоторую гибкость. YMMV.
PS1: Сущность была взята из http://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html
PS2: Тим Уильямс упомянул в комментарии "старый XLM GET.FORMULA()". Этот ответ, возможно, связан (не тот, который использует GET.CELL()).
PS3: простое решение VBA дается, например, в http://dmcritchie.mvps.org/excel/formula.htm
EDIT: дополняя этот приятный ответ, доступна функция рабочего листа FormulaText для Excel 2013 и более поздней.
=FormulaText(Reference) выполнит трюк Документация
Есть способ сделать это. В моем примере у меня была таблица, в которой была дата. Дата взята из листа G91. В моей таблице я также столкнулся с именем листа. Я добавил еще два столбца в таблицу. Первая колонка имела столбец (Sheet! G91), который возвращает число 7, потому что G - седьмая буква в алфавите. Затем я преобразовал число в букву (G), используя другую таблицу в моей книге. Во втором столбце, который я добавил, я сделал строку формулы (Sheet! G91), которая возвращает число 91. Примечание. Строка и столбец могут отображаться как изменчивые формулы, которые пересчитываются при каждом вычислении рабочей книги.
Я хотел, чтобы еще одна колонка отображала содержимое формулы ячейки даты, упомянутой в начале этого сообщения. Я включил следующую строковую функцию (вы также можете использовать CONCATENATE).
"=" и AJ9 и "!" и AM9 и AN9
Элементы, разделенные амперсандами, соединяются друг с другом (т.е. конкатенируются). AJ9 в моем примере содержит имя листа, AM9 содержит букву столбца, а AN9 содержит номер строки.
Теперь у меня есть столбец, который динамически обновляет его содержимое, чтобы отразить имя листа и ссылку на ячейку. Результаты в моей ячейке рабочей книги:
= Sheet! G91.
Это предложение может быть полезно для тех, кто после извлечения блока формул и переноса их в новую таблицу хочет снова заставить их работать. Функция Excels FORMULATEXT отлично подходит для подбора формул, но оставляет их как непригодные текстовые строки. Если вы хотите вернуть их в качестве полностью функционирующих формул, вы должны отредактировать каждый из них отдельно, чтобы удалить строковый символ, но вот ярлык для больших блоков. Перейдите в позицию, где у вас есть необходимые формулы в виде текста (другими словами, после использования FORMULATEXT - вы сделали копию и (только значение)). Следующий шаг включает выделение всех ячеек, которые вы хотите преобразовать, а затем переход к пункту меню [Text-To-Columns] ({Data} в Excel 2016). Вы можете выбрать "Разделить", но на следующем экране просто убедитесь, что вы отменили отметки, которые появляются в ваших формулах. Затем "Готово". Excel должен автоматически анализировать ячейки как содержащие формулы, и теперь вы должны снова заставить их работать.
Вы не можете. Это, скорее всего, выбор дизайна, чтобы исключить случайного получения пользователем среднего пользователя Excel из-за того, что они не хотели.
То, что вы читаете, верно - писать UDF - это решение, которое вы хотите.