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

=SUM(January:December!B19)

Это работает нормально, за исключением тех случаев, когда одна из ячеек B19 в диапазоне может содержать значение # N / A. В этом случае Крис также получает # N / A в результате на сводном листе. Крис хотел бы, чтобы результаты # N / A игнорировались для суммы, как если бы ячейки были пустыми.

Есть несколько способов решить эту проблему. Возможно, лучший способ — взглянуть на формулу, используемую в ячейке B19 рабочего листа каждого месяца. Например, предположим, что формула на каждом листе выглядела так:

=SUM(B1:B18)

Вы можете изменить формулы на этих отдельных листах, чтобы они учитывали возможность значений # N / A. Например, на каждом листе в B19 отлично подойдет следующее:

=SUMIF(B1: B18,"<>#N/A")

Это приводит к тому, что сумма в ячейке B19 на каждом листе основывается на всех значениях диапазона, отличных от N / A. Из-за этого вы могли подумать, что можете сделать это на сводном листе:

=SUMIF(January:December!B19,"<>#N/A")

Однако это не сработает, потому что функция СУММЕСЛИ не является «трехмерной» по своей природе; его нельзя использовать на различных листах указанным способом. По этой причине лучшее решение — вернуться к отдельным значениям на каждом листе, которые подсчитываются в итоговом листе.

Если ваша формула на листах отдельных месяцев не использует функцию СУММ, очевидно, не так просто изменить их для использования СУММЕСЛИ. В этом случае вы можете «заключить» существующую формулу в проверку, чтобы проверить, возвращает ли формула значение ошибки. Эта техника выполняется так:

=IF(ISERROR(<current_B19_formula>),0,<current_B19_formula>)

Функция IF ищет значение True / False, которое возвращается функцией ISERROR. Таким образом, если формула возвращает значение ошибки (например, # N / A), тогда функция ЕСЛИ возвращает 0, в противном случае она возвращает результат исходной формулы. Этот подход проверяет любой результат ошибки; если вы предпочитаете, чтобы он проверял только # N / A результатов и игнорировал их, вы можете использовать следующий вариант:

=IF(ISNA(<current_B19_formula>),0,<current_B19_formula>)

Существует большая разница между этими подходами, основанными на ЕСЛИ, и подходом СУММЕСЛИ, упомянутым ранее в подсказке. Подход СУММЕСЛИ возвращает сумму для всех значений, отличных от Н / Д в диапазоне, но подход на основе ЕСЛИ возвращает 0 для всей суммы, если в диапазоне есть какие-либо значения # Н / Д. Очевидно, это может повлиять на то, что отображается на сводном листе, поэтому вам нужно будет определить, какой подход лучше всего подходит для данных, с которыми вы работаете.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3156) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Ignoring_N_A_Values_in_a_Sum [Игнорирование значений N / A в сумме].