Преузмите пример радне свеске
Овај водич ће показати како се користи функција СУМИФС за сумирање података који се односе на ћелије које нису празне или које нису празне у Екцел-у и Гоогле табелама.
Зброј ако није празно
Прво ћемо показати како се сумирају подаци који се односе на ћелије које нису празне.
Можемо користити функцију СУМИФС да збројимо све Сцорес за Играчи са именима која нису празна.
| 1 | = СУМИФС (Ц3: Ц8, Б3: Б8, "") |
Да сумирамо редове са ћелијама које нису празне, искључујемо Сцорес са несталим Плаиер имена. Користимо критеријум „није једнако празном месту“ („”) унутар функције СУМИФС.
Третирање простора као празних ћелија - помоћу колоне помоћника
Морате бити опрезни при интеракцији са празним ћелијама у Екцелу. Ћелије вам могу изгледати празне, али Екцел их неће третирати као празне. То се може догодити ако ћелија садржи размаке, пријеломе линија или друге невидљиве знакове. Ово је чест проблем при увозу података у Екцел из других извора.
Ако морамо да третирамо све ћелије које садрже размаке на исти начин као да су празне, формула у претходном примеру неће радити. Обратите пажњу на то како формула СУМИФС не сматра ћелију Б9 испод (”“) празном:
| 1 | = СУМИФС (Д3: Д9, Б3: Б9, "") |
Да бисмо третирали ћелију која садржи само размаке као да је празна ћелија, можемо додати помоћну колону помоћу функција ЛЕН и ТРИМ за идентификацију Играчи са именима.
ТРИМ функција уклања додатне размаке од почетка и краја вредности сваке ћелије, а функција ЛЕН затим броји број преосталих знакова. Ако је резултат ЛЕН функције 0, онда је Плаиер име мора бити празно или само са размацима:
| 1 | = ЛЕН (ТРИМ (Б3)) |
Примењујемо функцију СУМИФС на помоћну колону (Збрајање ако је веће од 0), и она сада тачно израчунава збир.
| 1 | = СУМИФС (Е3: Е9, Д3: Д9, "> 0") |
Колона помоћника се лако ствара и лако се чита, али можда бисте желели да имате јединствену формулу за обављање задатка. Ово је обрађено у следећем одељку.
Третирање простора као празних ћелија - без колоне помоћника
Ако је потребно да се све ћелије које садрже само размаке третирају на исти начин као да су празне, али употреба помоћне колоне није прикладна, тада можемо користити функцију СУМПРОДУЦТ у комбинацији са функцијама ЛЕН и ТРИМ за сумирање података који се односе на ћелије који садржи празно поље Плаиер имена:
| 1 | = СУМПРОДУЦТ (-(ЛЕН (ТРИМ (Б3: Б9))> 0), Д3: Д9) |
У овом примеру користимо функцију СУМПРОДУЦТ за извођење компликованих прорачуна „збир ако“. Прођимо кроз формулу.
Ово је наша коначна формула:
| 1 | = СУМПРОДУЦТ (-(ЛЕН (ТРИМ (Б3: Б9))> 0), Д3: Д9) |
Прво, функција СУМПРОДУЦТ наводи низ вредности из два опсега ћелија:
| 1 | = СУМПРОДУЦТ (-(ЛЕН (ТРИМ ({"А"; "Б"; ""; "Ц"; ""; "КСКС"; ""}))> 0), {25; 10; 15; 5 ; 8; 17; 50) |
Затим функција ТРИМ уклања почетне и крајње размаке из Плаиер имена:
| 1 | = СУМПРОДУЦТ (-(ЛЕН ({"А"; "Б"; ""; "Ц"; ""; "КСКС"; ""})> 0), {25; 10; 15; 5; 8; 17; 50) |
Функција ЛЕН израчунава дужине обрезаних делова Плаиер имена:
| 1 | = СУМПРОДУЦТ (-({1; 1; 0; 1; 0; 2; 0}> 0), {25; 10; 15; 5; 8; 17; 50) |
Са логичким тестом (> 0), све је исечено Плаиер имена са више од 0 знакова се мењају у ТРУЕ:
| 1 | = СУМПРОДУЦТ (-({ТРУЕ; ТРУЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ; ТРУЕ; ФАЛСЕ}), {25; 10; 15; 5; 8; 17; 50) |
Затим двоструке цртице (-) претварају ТРУЕ и ФАЛСЕ вредности у 1с и 0с:
| 1 | = СУМПРОДУЦТ ({1; 1; 0; 1; 0; 1; 0}, {25; 10; 15; 5; 8; 17; 50) |
Функција СУМПРОДУЦТ затим множи сваки пар уноса у низовима да би произвела низ Сцорес само за Плаиер имена која нису празна или нису направљена само из размака:
| 1 | = СУМПРОДУЦТ ({25; 10; 0; 5; 0; 17; 0) |
Коначно, бројеви у низу се сабирају заједно
| 1 | =57 |
Више детаља о коришћењу логичких исказа и команде “-” у функцији СУМПРОДУЦТ можете пронаћи овде
Зброј ако није празно у Гоогле табелама
Ове формуле раде потпуно исто у Гоогле таблицама као и у Екцелу.
