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