Как одна ячейка получает формулу другой ячейки в виде текста без использования 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. Ячейка 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) сделает свое дело Документация
Есть способ сделать это. В моем примере у меня была таблица с датой. Дата взята из Sheet!G91. В моей таблице у меня также был столбец, в котором показывалось имя листа. Я добавил еще два столбца в свою таблицу. В первом столбце есть столбец (Лист!g91), который возвращает число 7, потому что G — седьмая буква в алфавите. Затем я преобразовал число в букву (G), используя другую таблицу в моей рабочей тетради. Во втором столбце, который я добавил, я создал строку формулы (Лист! G91), которая возвращает число 91. Примечание. Строка и столбец могут отображаться как изменчивые формулы, которые пересчитываются при каждом расчете рабочей книги.
Я хотел, чтобы в другом столбце отображалось содержимое формулы ячейки даты, упомянутой в начале этого поста. Я включил следующую строковую функцию (вы также можете использовать CONCATENATE).
"=" & AJ9 & "!" и АМ9 и АН9
Элементы, разделенные амперсандами, объединяются (т. е. конкатенируются). AJ9 в моем примере содержит имя листа, AM9 содержит букву столбца, а AN9 содержит номер строки.
Теперь у меня есть столбец, который динамически обновляет свое содержимое, чтобы отразить имя листа и ссылку на ячейку. Результаты в ячейке моей рабочей книги:
=Лист!G91.
Это предложение может быть полезно для тех, кто после извлечения блока формул и переноса их в новую электронную таблицу хочет снова заставить их работать. Функция Excel FORMULATEXT отлично подходит для подбора формул, но оставляет их в виде непригодных текстовых строк. Если вы хотите вернуть их в виде полностью функционирующих формул, вам нужно отредактировать каждую отдельно, чтобы удалить строковый символ, но вот ярлык для больших блоков. Перейдите к позиции, в которой у вас есть необходимые формулы в виде текста (другими словами, после использования FORMULATEXT - вы сделали копию и (только значение) вставку). Следующий шаг включает в себя выделение всех ячеек, которые вы хотите преобразовать, а затем переход к пункту меню [Текст в столбцы] (панель {Данные} в Excel 2016). Вы можете выбрать «С разделителями», но на следующем экране просто убедитесь, что вы сняли отметки со всех меток, которые появляются в ваших формулах. Затем «Готово». Excel должен автоматически анализировать ячейки как содержащие формулы, и теперь они должны снова работать.
Вы не можете. Скорее всего, это конструктивный выбор, призванный избавить среднего пользователя Excel от случайного получения чего-то, чего он не хотел.
То, что вы читаете, верно - написание UDF - это решение, которое вам нужно.
GET.FORMULA(), но если вы готовы зайти так далеко, вам следует просто использовать обычную функцию VBA. - person Tim Williams schedule 03.02.2012