Ввод переменной формулы мастера Excel

Question

не могли бы вы дать мне знать, достижимо ли то, что я пытаюсь сделать здесь? У меня есть ощущение, что INDIRECT может пригодиться, но мой разум не смог придумать, как это будет выглядеть. Моя цель в принципе аналогична анализу «что если», но вместо таблицы она будет использовать формулу из одной ячейки, ссылающуюся на основную формулу и некоторые входные данные. Что-если не работает здесь, потому что входы не организованы аккуратно в моем аналитическом листе.

Вот история:

Я создаю электронную таблицу ("AnalysisSheet"), которую я использую для прогнозирования и анализа результатов во время варки пива.

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

Эти формулы могут быть сложными и поэтому беспорядочно повторяться каждый раз, поэтому я хотел бы создать отдельный лист ("FormulaSheet"), в котором аккуратно разложены "основные" формулы (вместе с фиктивными входными данными), чтобы я мог просто ссылаться эти основные формулы из "AnalysisSheet" вместе со входными значениями, которые будут подставлены в фиктивные входные данные.

Вот пример простой формулы:

В "FormulaSheet":

A1: 20 [объем ввода фиктивного значения]

A2: 80 [значение фиктивной температуры]

A3: = A2 *(1-0,04 *(A3-20)/80) [формула "Master"]

Теперь в "AnalysisSheet":

F7: 90 [показания температуры]

F11: 40 [чтение тома]

F13: = Расчет в виде FormulaSheet!A3, но заменяет его входной ссылкой FormulaSheet!A1 с AnalysisSheet!F11 и FormulaSheet!A2 с AnalysisSheet!F7

Мой вопрос в основном:

Что нужно вставить в F13, чтобы выполнить то, что я описал?

Answer 1

Я не уверен, что вы можете делать то, что вы описываете, но я видел очень хорошую страницу по определению ваших собственных функций:
https://www.dummies.com/software/microsoft-office/excel/how-to-create-custom-excel-functions/

Требуется написать функцию на VBA, но это не так сложно, как кажется.

1. Откройте Инструменты разработчика. Обычно вам нужно перейти в «Настройки Excel»> «Просмотр»> «Показать вкладку разработчика».
2. Перейдите на новую вкладку «Разработчик» и нажмите « Visual Basic .
3. В открывшемся окне VB Editor выберите « Insert > Module .
4. Введите ваши функции

Например:

Public Function HotToColdVolume(temperature As Integer, volume As Integer)
    HotToColdVolume = volume * (1 - 0.04 * (temperature - 20) / 80)
End Function

Примечание. Вам нужно будет сохранить электронную таблицу как книгу с поддержкой макросов (.xlsm).

Теперь в вашей электронной таблице вы можете просто позвонить:

=HotToColdVolume(80,20)

Answer 2

Хорошо, этот способ не использует VBA, но это, возможно, хуже. Очевидно, в Excel были такие функции «Excel 4.0», которые можно было использовать в определенных именах. Они делают то, что вы хотите, но они все еще должны быть сохранены в файле .xlsm :

Уровень взлома 1

Есть функция (?) называется EVALUATE (я не могу найти никакой официальной документации по этому вопросу, но эта страница объясняет это), которая может оценивать текст как функцию. Но вы не можете ввести его непосредственно в ячейку, его нужно "запустить" из именованного диапазона:

Конечно, проблема в том, что если вы попытаетесь оценить формулу A2*(1-0.04*(A3-20)/80) вы не сможете указать свои собственные входные данные.

Взлом уровня 2

В своей основной формуле вы можете указать OFFSET, но вам нужна ячейка ссылки. Вы не можете просто ссылаться на A1 по тем же причинам, что и раньше. Вам нужен способ ссылки на эту ячейку. Введите еще один гениальный взлом, который я нашел здесь. Выберите ячейку A1 , создайте именованный диапазон (например, THIS_CELL), введите формулу =!A1 (! просто означает "этот лист").

Теперь, если вы хотите сослаться на ячейку сразу справа (через 1 столбец), вы используете:

OFFSET(THIS_CELL,0,1)

Таким образом, на главной странице формул вы помещаете формулы в столбец A, а аргументы - в столбцы B, C, D и т.д. И вы получите очень уродливую формулу, как это:

=OFFSET(THIS_CELL,0,1)*(1-0.04*(OFFSET(THIS_CELL,0,2)-20)/80)

Объедините хаки

Теперь вам просто нужно создать именованный диапазон, который EVALUATE FORMULATEXT причудливой формулы со встроенными смещениями:

=EVALUATE(FORMULATEXT(MasterFormulaTest!$A$2))

Введите названный диапазон в ячейку, и все готово!

Все без VBA, те же недостатки, как если бы вы использовали VBA, но гораздо сложнее, чем просто с помощью VBA. Точно хуже.