Excel: 'Sumifs' игнорируя # н / д

Question

Я пытаюсь суммировать некоторые значения (как положительные, так и отрицательные) в столбце, но это не суммируется, поскольку есть значения # n / a. Есть ли способ обойти это? См. Пример ниже:

В каждом городе есть районы с меньшими показателями дохода, преступности и безработицы (положительные или отрицательные). Моя цель - получить общегородские оценки доходов, преступности и безработицы путем суммирования оценок по более низким районам. Я использую sumifs в G2 как in = SUMIFS (C$2:C$10,$A$2:$A$10, $ A2), а затем перетащите его на I10. В этом игрушечном примере всего 10 строк, но в моих данных 1 миллион строк, поэтому я не могу перетаскивать. Любые предложения по этому поводу также будут полезны!

Но, что наиболее важно, моя проблема в том, что я не могу использовать sumifs из-за значений # N / A. Я хочу игнорировать их.

Или просто замените все значения # N / A на 0 в 1 миллионе строк.

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

Answer 1

Использование СУММЕСЛИМН с <0 И> 0

Вы можете использовать это:

=SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,"<0")+SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,">0")

Он избегает нуля (что в данном случае не имеет значения). Он также избегает ячеек, которые содержат значения, не являющиеся числами, например NA, что часто препятствует суммированию (в данном случае это решает вашу проблему).

Если я правильно понимаю, вы хотите получить сумму как положительных, так и отрицательных чисел (то есть их существующих значений, а не абсолютных значений), однако в настоящее время это предотвращается с использованием вашего существующего подхода из-за наличия элементов NA.

Если это не так, сообщите.

ИСПОЛЬЗУЯ ПРОМЕЖУТОЧНЫЙ ПРОСМОТР, ЗАТЕМ СУММЕСЛИ С <0 И> 0

В качестве альтернативы вы можете вставить столбец подстановки, который объединяет значения для оценки, а затем использовать СУММЕСЛИ.

Например, новый столбец J:

=$A2&C2

(отмечая $ для A, а также отсутствие $ для C)

Затем заполните J1 справа до K1 и L1 Затем в M1:

=SUMIF(J$2:J$10,"<0",C$2:C$10)+SUMIF(J$2:J$10,">0",C$2:C$10)

Затем заполните вправо до N1 и O1

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

ЗАМЕНА NA

Замена NA на 0 также будет работать, но вы можете не захотеть потерять различие между «0» и «не отображается в исходных данных».

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

Часто при поиске данных предпочтительнее уловить возможность NA и вернуть какой-либо другой, более значимый (или, в данном случае, более удобный для суммирования) результат.

Answer 2

Возможно, самый простой способ - использовать "<>#N/A"критерии, в которых NA в столбце суммы игнорируется. Вы можете добавить дополнительные критерии для других столбцов условий, если они мешают проверке числовых условий.=SUMIFS(C$2:C$10,$A$2:$A$10,$A2, C$2:C$10, "<>#N/A")

Спасибо: https://www.mrexcel.com/board/threads/sumifs-while-ignoring-n-a.1042338/ за чаевые.