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

=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")

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

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

=IFERROR(<current_B19_formula>,0)

Функция ЕСЛИОШИБКА просто проверяет, нет ли ошибки, возвращаемой формулой. Если есть, то возвращается 0; если нет, то возвращается результат формулы.

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

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

Этот совет (10233) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

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