5

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

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

РАССМАТРИВАТЬ:

         =IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),(IF((D2=(VLOOKUP(D2,$A$2:$A$9,1,0))),0,D2)),E2)
0        =IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),L17,E2)
TRUE     =ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))
0        =IF((D2=L18),0,D2)
the      =VLOOKUP(D2,$A$2:$A$9,1,0)

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

|источник

3 ответа3

7

Вот несколько основных правил, которые я использую самостоятельно и рекомендую для персонала во время тренингов:

  1. Создавайте сложные формулы (особенно IF) по частям (или аргумент за аргументом), т.е. если у вас есть столбец данных, который вы хотите проанализировать в соответствии с определенным условием, начните с простого =IF(LEFT(A1,2)="AB",1,0) - таким образом, вы уверены, что условие работает должным образом и возвращает правильные результаты для каждого типа значений. Далее вы заменяете каждый аргумент его собственными вычислениями (см. Ниже), но таким образом вы уверены, что любая ошибка / неправильный результат НЕ вызвана самим неправильным условием, в противном случае ошибки могут помешать, и общие результаты формулы будут совершенно неожиданными и чрезвычайно трудными для понимания. правильный.
  2. Каждый аргумент подготавливается в отдельном столбце как независимая формула, и только если он работает, он объединяется с основной формулой (или мегаформулой, как ее называют в некоторых библиотеках Excel).
  3. Используйте жестко закодированные значения в качестве промежуточных решений. Например, если вы строите динамический график, диапазон обычно рассчитывается с использованием функций OFFSET и COUNTA . Итак, сначала создайте свой OFFSET используя чистые числа, например, =OFFSET(A1,0,0,1,10) , убедитесь, что он возвращает то, что вам действительно нужно, и только затем начинайте строить вычисленную часть, например, =OFFSET(A1,0,0,1,COUNTA(A:A)-1) .
  4. Не пренебрегайте средством оценки формул (Ribbon Formulas > Evaluate Formula): для людей, которые видят его впервые, это как луч света) Действительно, наличие правильного синтаксиса НЕ означает в общем случае получение ожидаемых / правильных результатов, и это похоже на отладчик Excel ,

  5. Чтобы улучшить читаемость сложных формул, вы можете использовать новые разделители строк (могут быть добавлены через ALT+ENTER в строке формул), а также дополнительные пробелы, чтобы они выглядели как код с отступами (все это игнорируется при оценке формулы):

    =IF(
    LEFT(A1,2)="AB",
       MONTH(TODAY()),
       MONTH(B1)
   )

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

|источник
5

Вообще, деление больших формул по столбцам, на мой взгляд, является лучшей практикой!

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

  1. Используйте именованные диапазоны для констант - облегчает чтение ваших формул - лучше иметь =IF(A1>SalesThreshold,A1,0) чем =IF(D2>Assumptions!$B$239,D2,0)
  2. Используйте таблицы Excel и замените обычные ссылки на имена столбцов. Таким образом, =IF(AND(YEAR(B2)=2013,C2="MyProduct",D2>SalesThreshold),D2,0) становится =IF(AND(YEAR([Date])=2013,[Product]="MyProduct",[Sales]>SalesThreshold),[Sales],0)
  3. Если формула все еще сложна, вы можете использовать функцию N() для комментариев. Функция преобразует любой текст в 0, поэтому у вас может быть такая функция:
    = IF(N4> N20+N("Быстрая проверка"), N4 ^ 2+N(«Нужно взять в квадрат, потому что я не могу придумать лучшего примера»), N4/2+N(«Не знаю, что комментировать» Вот!«))

Надеюсь, это поможет!

|источник
1

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

[Раскрытие информации: я автор FormulaDesk]

|источник

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .