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