☰

Получить формулу для ячейки в Excel, заменив все ссылки на ячейку другой формулой с ее содержимым.

tag-icon • tag-icon microsoft-excel worksheet-function vba
Получить формулу для ячейки в Excel, заменив все ссылки на ячейку другой формулой с ее содержимым.

На основании комментариев я поясняю, о чем прошу.

Мне нужно объединить вложенную формулу из нескольких ячеек в одну.

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

Мне было интересно, возможно ли следующее, используя код/инструменты/Excel: Excel с формулами, показывающими

Как вы можете видеть на изображении, in hoursформула довольно проста, но на самом деле она состоит из кучи формул из других ячеек. Мне было интересно, можно ли избавиться от промежуточных вычислений, получив одну формулу, которая генерируется путем использования всех формул в одной гигантской формуле.

Хотя таким образом легко проектировать и отлаживать большие формулы, в производстве наивные пользователи могут легко разрушить части формулы, пытаясь изменить листы. Вы можете защитить ее, заблокировав листы и сделав другие вещи разблокированными, но это также имеет непреднамеренные побочные эффекты, такие как невозможность расширить диапазон формулы или добавить новые строки ... и т. д.

Поэтому для производства мне было интересно, возможно ли получить единую формулу ответа для чего-то, что составлено таким образом.

В приведенном выше примере мы пытаемся вычислить числовое значение часов из строкового значения часов.

in hours = in seconds / 3600 = O2 / 3600
in seconds = HOURS * 3600 + MINUTES * 60 + SECONDS
HOURS = extract digits if they exist between 'h' and beginning of string
MINUTES = extract digits if they exist between 'h' and 'm'
SECONDS = extract digits if they exist between 'm' and end of string

Итак, если бы вы подставили формулы вручную, то получилось бы вот так:

in hours = (HOURS * 3600 + MINUTES * 60 + SECONDS) / 3600 
= (extract digits if they exist between 'h' and beginning of string * 3600 + extract digits if they exist between 'h' and 'm' * 60 + extract digits if they exist between 'm' and end of string) / 3600

Как видите, этот пример довольно прост, но мне стало интересно, можно ли сделать то же самое для других сложных составных функций?

Excel уже сам выполняет все эти вычисления в каком-то родеАСТЯ предполагаю, поскольку он уже обнаруживает циклические зависимости и т. п. Так что должен быть способ сделать это самостоятельно? или подключиться к основам Excel, чтобы извлечь ту же формулу?

решение1

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

Option Explicit

Function MyHrs(MyType As String, target As String) As Single

Dim x As Long, y As Long, z As Long
Dim Hrs As Long, Mns As Long, Ss As Long
On Error Resume Next

x = InStr(target, "h")
y = InStr(target, "m")
z = InStr(target, "s")

Hrs = Left(target, x - 1)
Mns = Mid(target, x + 1, y - x - 1)
Ss = Mid(target, y + 1, z - y - 1)

If MyType = "h" Or MyType = 1 Then MyHrs = Hrs + Mns / 60 + Ss / 3600
If MyType = "m" Or MyType = 2 Then MyHrs = Hrs * 60 + Mns + Ss / 60
If MyType = "s" Or MyType = 3 Then MyHrs = Hrs * 3600 + Mns * 60 + Ss

End Function

Откройте окно разработчика с помощью ALT + F11, затем вставьте модуль и вставьте код. В таблице используйте его следующим образом: введите описание изображения здесь

Вероятно, здесь не помешало бы немного больше кода, чтобы избежать генерируемых ошибок, но для демонстрации концепции он должен подойти.

Связанный контент