Изтеглете примерната работна книга
Този урок ще покаже как да използвате функцията SUMIFS за сумиране на данни, съответстващи на празни или празни клетки в Excel и Google Sheets.
Сума, ако е празно
Първо ще покажем как да сумираме редове с празни клетки.
Функцията SUMIFS сумира данни, които отговарят на определени критерии.
Можем да използваме функцията SUMIFS, за да сумираме всички Резултати за Играчи без имена в примера по -долу.
| 1 | = SUMIFS (C3: C8, B3: B8, "") |
Използваме двойни кавички (“”), за да представим празна клетка в Excel. Нашият пример игнорира Играчи A, B, C и D и сумира резултатите за неизвестни Играчи.
Третиране на пространствата като празни клетки - с помощна колона
Трябва да бъдете внимателни, когато взаимодействате с празни клетки в Excel. Клетките може да ви се сторят празни, но Excel няма да ги третира като празни. Това може да се случи, ако клетката съдържа интервали, прекъсвания на редове или други невидими знаци. Това е често срещан проблем при импортиране на данни в Excel от други източници.
Ако трябва да третираме всички клетки, които съдържат само интервали по същия начин, сякаш са празни, формулата в предишния пример няма да работи. Забележете как формулата SUMIFS не счита клетка В9 по -долу (”“) за празна:
За да третираме клетка, съдържаща само интервали, сякаш е празна клетка, можем да добавим помощна колона с функцията TRIM, за да премахнем допълнителните интервали от стойността на всяка клетка:
| 1 | = TRIM (B3) |
Прилагаме функцията SUMIFS към помощната колона и сега тя изчислява сумата точно.
| 1 | = SUMIFS (E3: E9, D3: D9, "") |
Помощната колона е лесна за създаване и лесна за четене, но може да искате да имате една формула за изпълнение на задачата. Това е обхванато в следващия раздел.
Третиране на пространствата като празни клетки - без помощна колона
Ако помощна колона не е подходяща за вашите нужди, тогава можете да използвате функцията SUMPRODUCT в комбинация с функциите LEN и TRIM, за да сумирате празни редове.
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
В този пример използваме функцията SUMPRODUCT, за да извършим сложно изчисление на „сума, ако“. Нека преминем през формулата.
Това е нашата последна формула:
| 1 | = SUMPRODUCT (-(LEN (TRIM (B3: B9)) = 0), D3: D9) |
Първо, функцията SUMPRODUCT изброява масива от стойности от двата диапазона на клетки:
| 1 | = SUMPRODUCT (-(LEN (TRIM ({"A"; "B"; ""; "C"; ""; "XX"; ""})) = 0), {25; 10; 15; 5 ; 8; 17; 50) |
След това функцията TRIM премахва водещите и крайните интервали от Играч имена:
| 1 | = SUMPRODUCT (-(LEN ({"A"; "B"; ""; "C"; ""; "XX"; ""}) = 0), {25; 10; 15; 5; 8; 17; 50) |
Функцията LEN изчислява дължините на подрязаните Играч имена:
| 1 | = СУМПРОДУКТ (-({1; 1; 0; 1; 0; 2; 0} = 0), {25; 10; 15; 5; 8; 17; 50) |
С логическия тест (= 0) всеки отрязан Играч имената с 0 знака се променят на TRUE:
| 1 | = SUMPRODUCT (-({FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE}), {25; 10; 15; 5; 8; 17; 50) |
След това двойните тирета (-) преобразуват стойностите TRUE и FALSE в 1s и 0s:
| 1 | = СУМПРОДУКТ ({0; 0; 1; 0; 1; 0; 1}, {25; 10; 15; 5; 8; 17; 50) |
След това функцията SUMPRODUCT умножава всяка двойка записи в масивите, за да създаде масив от Резултати само за Играч имена, които са празни или са направени само от интервали:
| 1 | = СУМПРОДУКТ ({0; 0; 15; 0; 8; 0; 50) |
И накрая, числата в масива се сумират заедно:
| 1 | =73 |
Повече подробности за използването на булеви изрази и командата “-” във функция SUMPRODUCT можете да намерите тук.
Сума, ако е празна в Google Таблици
Тези формули работят абсолютно същите в Google Sheets, както и в Excel.
