Получение формулы другой ячейки в целевой ячейке

Как одна ячейка получает формулу другой ячейки в виде текста без использования VBA? Я вижу, что этот вопрос уже задан много раз, и ответ всегда заключается в написании пользовательской функции в VBA.

Однако, я нашел сообщение сделанное в 2006 году, в котором утверждалось, что он нашел решение, отличное от VBA, но ссылка, предоставленная в этом сообщении, уже сломан.

Ответы

Ответ 1

Хороший способ сделать это без VBA. Он использует макросы XL4 (это макросы, но это не VBA, как было задано).

Как показано на рисунке 1, ячейки A2: A4 содержат обычные формулы.

enter image description here

  • Переход к формулам → Определить имя, я определил два именованных диапазона (см. рис .2), с информацией, отображаемой в ячейках A6: B8.

    enter image description here

  • Введите в ячейку 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 и более поздней.

Ответ 3

Есть способ сделать это. В моем примере у меня была таблица, в которой была дата. Дата взята из листа G91. В моей таблице я также столкнулся с именем листа. Я добавил еще два столбца в таблицу. Первая колонка имела столбец (Sheet! G91), который возвращает число 7, потому что G - седьмая буква в алфавите. Затем я преобразовал число в букву (G), используя другую таблицу в моей книге. Во втором столбце, который я добавил, я сделал строку формулы (Sheet! G91), которая возвращает число 91. Примечание. Строка и столбец могут отображаться как изменчивые формулы, которые пересчитываются при каждом вычислении рабочей книги.

Я хотел, чтобы еще одна колонка отображала содержимое формулы ячейки даты, упомянутой в начале этого сообщения. Я включил следующую строковую функцию (вы также можете использовать CONCATENATE).

"=" и AJ9 и "!" и AM9 и AN9

Элементы, разделенные амперсандами, соединяются друг с другом (т.е. конкатенируются). AJ9 в моем примере содержит имя листа, AM9 содержит букву столбца, а AN9 содержит номер строки.

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

= Sheet! G91.

Ответ 4

Это предложение может быть полезно для тех, кто после извлечения блока формул и переноса их в новую таблицу хочет снова заставить их работать. Функция Excels FORMULATEXT отлично подходит для подбора формул, но оставляет их как непригодные текстовые строки. Если вы хотите вернуть их в качестве полностью функционирующих формул, вы должны отредактировать каждый из них отдельно, чтобы удалить строковый символ, но вот ярлык для больших блоков. Перейдите в позицию, где у вас есть необходимые формулы в виде текста (другими словами, после использования FORMULATEXT - вы сделали копию и (только значение)). Следующий шаг включает выделение всех ячеек, которые вы хотите преобразовать, а затем переход к пункту меню [Text-To-Columns] ({Data} в Excel 2016). Вы можете выбрать "Разделить", но на следующем экране просто убедитесь, что вы отменили отметки, которые появляются в ваших формулах. Затем "Готово". Excel должен автоматически анализировать ячейки как содержащие формулы, и теперь вы должны снова заставить их работать.

Ответ 5

Вы не можете. Это, скорее всего, выбор дизайна, чтобы исключить случайного получения пользователем среднего пользователя Excel из-за того, что они не хотели.

То, что вы читаете, верно - писать UDF - это решение, которое вы хотите.