Овај водич показује како се користи Екцел СУМИФ и СУМИФС Фунцтионс у Екцел -у и Гоогле табелама за збир података који задовољавају одређене критеријуме.
Преглед функције СУМИФ
Функцију СУМИФ у Екцелу можете користити за збир ћелија које садрже одређену вредност, сума ћелија које су веће или једнаке вредности итд.
(Обратите пажњу на то како се појављују уноси формуле)
Синтакса и аргументи функције СУМИФ:
| 1 | = СУМИФ (опсег, критеријуми, [опсег_збира]) |
домет - Распон ћелија на које желите да примените критеријуме.
критеријума - Критеријуми који се користе за одређивање ћелија које треба додати.
сум_ранге - [опционално] Ћелије за додавање заједно. Ако је сум_ранге изостављен, ћелије у опсегу се уместо тога сабирају.
Шта је функција СУМИФ?
Функција СУМИФ једна је од старијих функција која се користи у табелама. Користи се за скенирање кроз низ ћелија провером одређеног критеријума, а затим сабирање вредности у опсегу који одговара тим вредностима. Оригинална функција СУМИФ била је ограничена на само један критеријум. Након 2007. године створена је функција СУМИФС која омогућава мноштво критеријума. Већина опште употребе остаје иста између њих две, али постоје неке критичне разлике у синтакси о којима ћемо расправљати у овом чланку.
Ако већ нисте, можете погледати већи део сличне структуре и примере у чланку ЦОУНТИФС.
Основни пример
Размотримо ову листу евидентиране продаје и желимо да знамо укупан приход.
Пошто смо имали трошак, негативну вредност, не можемо само направити основни износ. Уместо тога, желимо да сумирамо само вредности које су веће од 0. „Више од 0“ ће бити наши критеријуми у функцији СУМИФ. Наша формула да то кажемо је
| 1 | = СУМИФ (А2: А7, "> 0") |
Пример са две колоне
Иако је оригинална функција СУМИФ осмишљена тако да вам омогући да примените критеријум на опсег бројева које желите да саберете, већину времена ћете морати да примените један или више критеријума на друге колоне. Размотримо ову табелу:
Сада, ако користимо оригиналну функцију СУМИФ да сазнамо колико банана имамо (наведених у ћелији Д1), мораћемо да дамо опсег који желимо да збир као последњи аргумент, па би наша формула била
| 1 | = СУМИФ (А2: А7, Д1, Б2: Б7) |
Међутим, када су програмери на крају схватили да корисници желе да дају више критеријума, створена је функција СУМИФС. Да би се створила једна структура која би радила за било који број критеријума, СУМИФС захтева да се прво наведе опсег збира. У нашем примеру то значи да формула мора бити
| 1 | = СУМИФС (Б2: Б7, А2: А7, Д1) |
НАПОМЕНА: Ове две формуле добијају исти резултат и могу изгледати слично, па обратите пажњу на то која се функција користи да бисте били сигурни да сте све аргументе навели правилним редоследом.
Рад са датумима, више критеријума
Када радите са датумима у прорачунској табели, иако је могуће унети датум директно у формулу, најбоље је да датум буде у ћелији тако да се можете једноставно позвати на ћелију у формули. На пример, ово помаже рачунару да зна да желите да користите датум 27.5.2020, а не број 5 подељен са 27 подељен са 2022. годином.
Погледајмо нашу следећу табелу која бележи број посетилаца веб странице сваке две недеље.
Можемо одредити почетну и завршну тачку опсега који желимо да погледамо у Д2 и Е2. Наша формула за сумирање броја посетилаца у овом опсегу може бити:
| 1 | = СУМИФС (Б2: Б7, А2: А7, "> =" & Д2, А2: А7, "<=" & Е2) |
Запазите како смо успели да спојимо поређења „=“ са референцама ћелија да бисмо креирали критеријуме. Такође, иако су оба критеријума примењена на исти опсег ћелија (А2: А7), морате да испишете опсег два пута, једном по сваком критеријуму.
Више колона
Када користите више критеријума, можете их применити на исти опсег као што смо то урадили у претходном примеру, или их можете применити на различите опсеге. Комбинујмо наше узорке података у ову табелу:
Подесили смо неке ћелије за корисника да унесе оно што жели да тражи у ћелијама Е2 до Г2. Стога нам је потребна формула која ће збрајати укупан број убраних јабука у фебруару. Наша формула изгледа овако:
| 1 | = СУМИФС (Ц2: Ц7, Б2: Б7, "> =" & Ф2, Б2: Б7, "<=" & Г2, А2: А7, Е2) |
СУМИФС са логиком типа ИЛИ
До сада су сви примери које смо користили били поређење засновано на И, где тражимо редове који испуњавају све наше критеријуме. Сада ћемо размотрити случај када желите да потражите могућност да ред задовољи један или други критеријум.
Погледајмо ову листу продаје:
Желели бисмо да збројимо укупну продају и за Адама и за Боба. Да бисте то урадили, имате неколико опција. Најједноставније је додати два СУМИФ -а заједно, овако:
| 1 | = СУМИФС (Б2: Б7, А2: А7, "Адам")+СУМИФС (Б2: Б7, А2: А7, "Боб") |
Овде смо дали рачунару да израчуна наше појединачне резултате, а затим их саберемо.
Наша следећа опција је добра ако имате више опсега критеријума, тако да не желите да морате да мењате целу формулу више пута. У претходној формули смо ручно рекли рачунару да дода два различита СУМИФ -а заједно. Међутим, то можете учинити и тако што ћете уписати своје критеријуме унутар низа, на следећи начин:
| 1 | = ЗБИР (СУМИФС (Б2: Б7, А2: А7, {"Адам", "Боб"})) |
Погледајте како је низ конструисан унутар увијених заграда. Када рачунар процени ову формулу, знаће да желимо да израчунамо функцију СУМИФС за сваку ставку у нашем низу, стварајући тако низ бројева. Спољашња функција СУМ ће тада узети низ бројева и претворити га у један број. Кораком кроз процену формуле, то би изгледало овако:
| 123 | = ЗБИР (СУМИФС (Б2: Б7, А2: А7, {"Адам", "Боб"}))= СУМ (27401, 43470)= 70871 |
Добијамо исти резултат, али смо формулу могли сажетије написати.
Суочавање са празнинама
Понекад ће ваш скуп података имати празне ћелије које морате пронаћи или избећи. Постављање ових критеријума може бити мало незгодно, па погледајмо други пример.
Имајте на уму да је ћелија А3 заиста празна, док ћелија А5 има формулу која враћа низ нулте дужине „“. Ако желимо да пронађемо укупан збир заиста празне ћелије, користили бисмо критеријум „=“, а наша формула би изгледала овако:
| 1 | = СУМИФС (Б2: Б7, А2: А7, "=") |
С друге стране, ако желимо да добијемо збир за све ћелије које визуелно изгледају празне, променићемо критеријум у „“, а формула изгледа овако
| 1 | = СУМИФС (Б2: Б7, А2: А7, "") |
Хајде да преокренемо: шта ако желите да пронађете збир ћелија које нису празне? Нажалост, тренутни дизајн неће вам омогућити да избегнете низ нулте дужине. Можете користити критеријум „”, али као што видите у примеру, он и даље укључује вредност из 5. реда.
| 1 | = СУМИФС (Б2: Б7, А2: А7, "") |
Ако не морате да бројите ћелије које садрже низове нулте дужине, размислите о употреби функције ЛЕН унутар СУМПРОДУЦТ -а
СУМИФ у Гоогле табелама
Функција СУМИФ ради потпуно исто у Гоогле таблицама као и у Екцелу:
