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