Пользовательская функция пользователя без использования VBA

Question

Можно ли создать пользовательскую функцию в Excel без использования VBA?
Моя проблема в том, что у меня есть длинная строка стандартных функций Excel, соединенных вместе для создания одной очень длинной функции. Эта функция используется в 25 различных листах моей книги. Если мне нужно внести в него изменения, я бы хотел сделать это только в одном месте, чтобы изменения распространялись по всем листам автоматически.

Например, простым и тривиальным случаем было бы добавить единицу к результату SUM(), т.е. SUM(mySeries)+1 и назовите его новой функцией MYSUM().

Я не решаюсь переводить длинную формулу в VBA из-за возможных ошибок, которые могут возникнуть, и из-за дополнительной сложности.

Answer 1

Да, это возможно, если вы используете именованные формулы Excel.

Например, предположим, что вам необходимо вычислить разницу между суммами двух последовательных столбцов (A5:Ax - B5:Bx) в разных местах вашей рабочей книги (x - последняя строка каждого столбца):

Таким образом, вы определяете в A11 имя с именем diff (может использоваться любое имя) как = Sum (A $ 5:A10) -Sum (B $ 5:B10), предполагая, что данные начинаются со строки 5 до предыдущей строки. Это может быть любая ячейка, не только A11, но определение меняется таким же образом.

К сожалению, в Excel 2010 вставляются абсолютные префиксы ($) и префиксы рабочих листов, поэтому вам нужно стереть префиксы, но сохранить восклицательные знаки и стереть большинство символов $ .

При перемещении формул большинство ссылок являются относительными. Таким образом, он всегда вычисляет разницу между текущим столбцом и последующим, начиная со строки 5 до строки перед итоговой строкой.

Таким образом, если у вас есть данные между C5 и D100, вы просто указываете = Diff в C101, и он вычисляет сумму (C5:C100) - Sum (D5:D100).

Очевидно, что вы можете использовать локальные или глобальные имена в именованных формулах, как вы упоминали в своем вопросе.

Вы можете прочитать более подробную информацию в Именованные формулы.

Answer 2

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

Создайте пользовательскую функцию для извлечения формулы из ячейки в виде строки:

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function

Создайте другой, чтобы оценить строку как формула:

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

Если у вас была основная формула в sheet1!a1, тогда вы поместите это в каждую ячейку, которая должна использовать это:

=eval(getformula(sheet1!a1))