Как суммировать диапазон ячеек со значениями N / A?

89113
Nam G VU

У меня есть диапазон, и мне нужно суммировать его с помощью функции SUM, но результат будет N / A, если есть какое-либо значение N / A. Как сделать так, чтобы функция SUM обрабатывала значение N / A как значение 0? Пожалуйста помоги!

11

7 ответов на вопрос

15
wilson

Использовать формулу массива

=SUM(IF(ISNA(A1:A4),0,A1:A4))

Нажмите Ctrl+ Shift+ Enterпосле ввода формулы

Примечание: замените A1:A4на ваш диапазон

+1: хорошее улучшение моего решения - нет необходимости изменять исходные данные. Mike Fitzpatrick 14 лет назад 1
Ницца. Никогда не думал об использовании формулы массива. Я был доволен использованием = SUMIF (A1: A4, "<> # N / A"), пока не обнаружил, что он ломается, если электронная таблица открывается в копии Excel, локализованной для другого языка! К сожалению, замена "<> # N / A" на "ISNA ()" не работает, но формула массива является хорошим многоязычным решением. К сожалению, этот подход не подходит для такой же задачи с AVERAGEIF ()… :-( Joe Carroll 10 лет назад 1
@JoeCarroll, вы можете использовать `= AVERAGE (IF (ISNA (A1: A4)," ", A1: A4))` wilson 10 лет назад 0
Спасибо @wilson! Когда я обдумал это кратко, я не думал об использовании "" вместо нуля, глупый я :-) Использование нуля, конечно, влияет на размер выборки, что привело меня к отклонению решения, но пустые ячейки просто игнорируются. Joe Carroll 10 лет назад 0
Не работает с магическим Cr @ pXcel 2013, но ответ ниже (= агрегат) работает как шарм! 8 лет назад 0
@ Марк, согласитесь, `= aggregate (...)` - более элегантное решение для Excel 2010 и последующих версий. Кстати, у меня есть только Office 2010/2003 под рукой, поэтому я не могу проверить формулу массива в 2013 году. wilson 8 лет назад 0
@wilson Хотелось бы, чтобы у меня тоже не было 2013 года, но это corp.policy :) Кажется, формулы работают * как * интерпретаторы (определение неверно на «техническом уровне», я знаю ...) в 2013 году, так что если вы что-то скажете например, «SUM A1: Axe IF A1: Ax [условие] ...». Excel останавливает обработку при первом попадании и игнорирует желаемую СУММУ диапазона, а не «СУММУ первой действительной записи» (с самим собой?). Странно и некрасиво. 8 лет назад 0
5
JNK

Самый простой способ:

Используйте SUMIFзначение> 0.

Таким образом, синтаксис для этого

=SUMIF(A1:B2, "> 0")

Вы получите тот же результат, поскольку он будет игнорировать любые нечисловые или 0 значения.

Ваше предложение не будет работать с отрицательными числами. Спасибо, в любом случае. Nam G VU 14 лет назад 2
Может сделать `= SUMIF (A1: B2,"> = 0 ") + SUMIF (A1: B2," <0 ")`, чтобы включить отрицательные числа. Я подозреваю, что это будет быстрее, чем принятое решение по формуле массива, если производительность является проблемой. kizzx2 11 лет назад 1
3
Muhammad Wajid Raza

There is a new function in Excel which will add all the values either positive or negative while ignoring NA's. 

=aggregate(9,6, range of your data)

9 is used for sum while 6 is for ignoring all NA's. There are other options, as well, beside addition. For example, you can do product, standard deviation, and so on.

More information on AGGREGATE here.

элегантное решение для Excel 2010 и далее :) wilson 8 лет назад 0
0
variant

Используйте SUBTOTAL вместо:

=SUBTOTAL(9,A1:A50)
Я пытаюсь "sumtotal", но у меня та же проблема, что и с "sum" fguillen 9 лет назад 0
0
Mike Fitzpatrick

Вы можете преобразовать # N / A в нули в исходном диапазоне, а затем использовать обычную функцию SUM:

= IF (ISNA (OriginalFormula), 0, OriginalFormula)
Начиная с Office 2007, появилась новая функция `IFERROR`, и тогда она может выглядеть так:` = IFERROR (OriginalFormula, 0) `, делая ее намного приятнее. Michael 12 лет назад 1
@agentmike, согласен, я использую его часто в эти дни. Mike Fitzpatrick 12 лет назад 0
0
cynthia

If there are formulas I'd copy as numbers, replace #N/A in column with 0 and then sum as usually

-1
Bruno

This way it seems to work: 

SUMIF(range;"<>#N/A";range)

Not so robust but effective!

Похожие вопросы