Този урок демонстрира как да използвате функциите на Excel AVERAGEIF и AVERAGEIFS в Excel и Google Sheets за осредняване на данни, които отговарят на определени критерии.
AVERAGEIF Общ преглед на функцията
Можете да използвате функцията AVERAGEIF в Excel, за да броите клетки, които съдържат определена стойност, да броите клетки, които са по -големи или равни на стойност и т.н.
За да използвате функцията AVERAGEIF Excel на работен лист, изберете клетка и въведете:
(Забележете как се появяват входовете на формулата)
Синтаксис и аргументи на функцията AVERAGEIF:
= AVERAGEIF (диапазон, критерии, [среден_ диапазон])
диапазон - Обхватът на клетките за броене.
критерии - Критериите, които контролират кои клетки трябва да бъдат преброени.
среден_диапазон - [по избор] Клетките за осредняване. Когато е пропуснато, се използва диапазон.
Какво представлява функцията AVERAGEIF?
Функцията AVERAGEIF е една от по -старите функции, използвани в електронните таблици. Използва се за сканиране през диапазон от клетки, за да се провери за определен критерий, и след това да се даде средната стойност (известна още като математическата средна стойност), ако стойностите в диапазон, които съответстват на тези стойности. Първоначалната функция AVERAGEIF беше ограничена само до един критерий. След 2007 г. бе създадена функцията AVERAGEIFS, която позволява множество критерии. По -голямата част от общата употреба остава същата между двете, но има някои критични разлики в синтаксиса, които ще обсъдим в тази статия.
Ако все още не сте го направили, можете да прегледате голяма част от подобната структура и примери в статията COUNTIFS.
Основен пример
Нека разгледаме този списък с регистрирани продажби и искаме да знаем средния доход.
Тъй като имахме разход, отрицателната стойност, не можем просто да направим основна средна стойност. Вместо това искаме да усредним само стойностите, които са по -големи от 0. „По -голямо от 0“ е това, което ще бъде нашият критерий във функция AVERAGEIF. Нашата формула да заявим това е
= СРЕДЕН (A2: A7, "> 0")
Пример с две колони
Докато оригиналната функция AVERAGEIF е проектирана да ви позволи да приложите критерий към диапазона от числа, които искате да сумирате, голяма част от времето ще трябва да приложите един или повече критерии към други колони. Нека разгледаме тази таблица:
Сега, ако използваме оригиналната функция AVERAGEIF, за да разберем колко банани имаме средно. Ще поставим нашите критерии в клетка D1 и ще трябва да дадем диапазона, който искаме средно аритметично като последен аргумент и нашата формула би била такава
= СРЕДЕН (A2: A7, D1, B2: B7)
Когато обаче програмистите в крайна сметка разбраха, че потребителите искат да дадат повече от един критерий, беше създадена функцията AVERAGEIFS. За да се създаде една структура, която да работи за произволен брой критерии, AVERAGEIFS изисква първо да бъде посочен диапазонът от суми. В нашия пример това означава, че формулата трябва да бъде
= СРЕДНИ (B2: B7, A2: A7, D1)
ЗАБЕЛЕЖКА: Тези две формули получават един и същ резултат и могат да изглеждат сходни, затова обърнете специално внимание коя функция се използва, за да сте сигурни, че изброявате всички аргументи в правилния ред.
Работа с дати, множество критерии
Когато работите с дати в електронна таблица, въпреки че е възможно да въведете датата директно във формулата, най -добре е датата да е в клетка, така че да можете просто да се позовавате на клетката във формула. Например това помага на компютъра да знае, че искате да използвате датата 27.05.2020 г., а не числото 5, разделено на 27, разделено на 2022.
Нека да разгледаме следващата таблица, която записва броя посетители на сайт на всеки две седмици.
Можем да посочим началната и крайната точка на диапазона, който искаме да разгледаме в D2 и E2. Тогава нашата формула за намиране на средния брой посетители в този диапазон може да бъде:
= СРЕДНИ (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Забележете как успяхме да обединим сравненията на „=“ с препратките към клетки, за да създадем критериите. Също така, въпреки че и двата критерия бяха приложени към един и същ диапазон от клетки (A2: A7), трябва да изпишете диапазона два пъти, веднъж за всеки критерий.
Множество колони
Когато използвате множество критерии, можете да ги приложите към същия диапазон, както направихме в предишния пример, или да ги приложите към различни диапазони. Нека комбинираме нашите примерни данни в тази таблица:
Ние сме настроили някои клетки за потребителя да въведе това, което иска да търси в клетки E2 до G2. По този начин се нуждаем от формула, която ще събере общия брой на ябълките, събрани през февруари. Нашата формула изглежда така:
= СРЕДНИ (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
СРЕДНИ с логика тип ИЛИ
До този момент примерите, които използвахме, бяха сравнения на база И, където търсим редове, които отговарят на всички наши критерии. Сега ще разгледаме случая, когато искате да потърсите възможността ред да отговаря на един или друг критерий.
Нека да разгледаме този списък с продажби:
Бихме искали да добавим средните продажби както за Адам, така и за Боб. Първо, бърза дискусия относно вземането на средни стойности. Ако имате неравен брой неща, като например 3 записа за Адам и 2 за Боб, не можете просто да вземете средната стойност на продажбите на всеки човек. Това е известно като приемане на средната стойност и в крайна сметка давате несправедливо претегляне на елемента, който има малко записи. Ако случаят е такъв с вашите данни, ще трябва да изчислите средно по „ръчния“ начин: вземете сумата от всичките си елементи, разделена на броя на вашите елементи. За да прегледате как да направите това, можете да разгледате статиите тук:
Сега, ако броят на записите е същият, например в нашата таблица, тогава имате няколко опции, които можете да направите. Най -простото е да добавите два AVERAGEIF заедно така и след това да разделите на 2 (броят на елементите в нашия списък)
= (СРЕДНИ (B2: B7, A2: A7, "Адам")+СРЕДНИ (B2: B7, A2: A7, "Боб"))/2
Тук сме накарали компютъра да изчисли нашите индивидуални резултати и след това да ги съберем.
Следващата ни опция е добра, когато имате повече диапазони от критерии, така че не искате да пренаписвате цялата формула многократно. В предишната формула ръчно казахме на компютъра да добави два различни AVERAGEIFS заедно. Можете обаче да направите това, като напишете критериите си в масив, например:
= СРЕДНИ (СРЕДНИ (B2: B7, A2: A7, {"Адам", "Боб"}))
Вижте как е конструиран масивът в къдравите скоби. Когато компютърът оцени тази формула, той ще знае, че искаме да изчислим функция AVERAGEIFS за всеки елемент в нашия масив, като по този начин създаваме масив от числа. Външната функция AVERAGE ще вземе този масив от числа и ще го превърне в едно число. Преминавайки през оценката на формулата, тя ще изглежда така:
= СРЕДНИ (СРЕДНИ (B2: B7, A2: A7, {"Адам", "Боб"})) = СРЕДНИ (13701, 21735) = 17718
Получаваме същия резултат, но успяхме да напишем формулата малко по -кратко.
Справяне с заготовки
Понякога вашият набор от данни ще има празни клетки, които трябва да намерите или да избегнете. Задаването на критериите за тях може да бъде малко сложно, така че нека разгледаме друг пример.
Обърнете внимание, че клетка A3 е наистина празна, докато клетка A5 има формула, връщаща низ с нулева дължина на „”. Ако искаме да намерим общата средна стойност на наистина празни клетки, бихме използвали критерий „=“ и нашата формула ще изглежда така:
= СРЕДНИ (B2: B7, A2: A7, "=")
От друга страна, ако искаме да получим средната стойност за всички клетки, които визуално изглеждат празни, ще променим критериите да бъдат „”, а формулата изглежда така
= СРЕДНИ (B2: B7, A2: A7, "")
Нека го обърнем: ами ако искате да намерите средната стойност на непразни клетки? За съжаление, настоящият дизайн няма да ви позволи да избегнете низ с нулева дължина. Можете да използвате критерий „“, но както можете да видите в примера, той все още включва стойността от ред 5.
= СРЕДНИ (B2: B7, A2: A7, "")
Ако не трябва да броите клетки, съдържащи низове с нулева дължина, ще искате да обмислите използването на функцията LEN вътре в SUMPRODUCT
AVERAGEIF в Google Таблици
Функцията AVERAGEIF работи абсолютно същото в Google Sheets като в Excel:
