Как да използваме COUNTIFS, SUMIFS, AVERAGEIFS в Excel

Трите най-често използвани формули в Excel, които изпълняват прости математически изчисления, са БРОЯ, SUM и СРЕДНО АРИТМЕТИЧНО. Независимо дали управлявате финансов бюджет в Excel или просто следите следващата си ваканция, вероятно сте използвали някоя от тези функции преди.

В тази статия ще преминем през основите на тези три функции и техните подходящи и полезни партньори: COUNTIFS, SUMIFS и AVERAGEIFS.

Да предположим, че започваме нов онлайн бизнес, продаващ мобилни телефони, и имаме лист, в който са изброени продажбите, които сме направили през първите два месеца. Изтеглете примерната електронна таблица на Excel тук.

Excel COUNT, SUM и AVERAGE

За да знаем колко мобилни телефона сме продали, можем бързо да използваме БРОЯ формула, както е показано по-долу:

= COUNT (Е2: E16)

От друга страна, за да получим общата сума на продажбите, която сме направили, можем да използваме SUM формула, както е показано по-долу:

= SUM (Е2: E16)

И накрая, за да разберем средните продажби, които сме направили за всички телефони, можем да използваме СРЕДНО АРИТМЕТИЧНО формула по-долу:

= Средна (Е2: E16)

Резултатът трябва да бъде както следва:

Формулите COUNT, SUM и AVERAGE ще работят само за записи, за които стойността на клетката е във формат за числа. Всеки запис в обхвата на формулата (т.е.. E2: E16 в този пример) няма да бъде пренебрегнат.

Затова се уверете, че всички клетки в формулата COUNT, SUM и AVERAGE са форматирани като номер, не Текст. Опитайте се да използвате същата формула, но с Д: Д като диапазон вместо E2: E16. Той ще върне същия резултат, както преди, защото игнорира заглавката (т.е.. Продажна цена), който е в текстов формат.

Ами ако искаме да знаем броя на продажбите, общия обем на продажбите и средния размер на продажбите на телефон, само за тези, които се продават в САЩ? Тук важна роля играят COUNTIFS, SUMIFS и AVERAGEIFS. Спазвайте формулата по-долу:

COUNTIFS

Разбивка по формулата:

1. = COUNTIFS ( - Най- "=" показва началото на формула в клетката и COUNTIFS е първата част от функцията на Excel, която използваме.
2. D2: D16 - Отнася се за диапазон от данни, за да провери дали той отговаря на критериите, за да бъде включен в формулата за броене.
3. "САЩ" - Критерии за търсене в посочения диапазон от данни (D2: D16)
4. ) - Затваряща скоба, показваща края на формулата.

Формулата връща 6, която е броят на продажбите за продуктите, доставени от склад в САЩ.

SUMIFS

Разбивка по формулата:

1. = SUMIFS ( - Най- "=" показва отново началото на формулата.
2. E2: E16 - Отнася се за диапазон от данни, които бихме искали да обединим, т.е. продажната цена в нашия пример.
3. D2: D16 - Отнася се за набор от данни, за да се провери дали отговаря на критериите, за да бъдат включени в общата сума.
4. "САЩ" - Критерии за търсене в посочения диапазон от данни (D2: D16)
5. ) - Затваряща скоба, показваща края на формулата.

Формулата показва $ 6050 общите продажби, извършени за продукти, доставени от склад в САЩ.

AVERAGEIFS

Разбивка по формулата:

1. = AVERAGEIFS ( - Най- "=" показват началото на формулата.
2. E2: E16 - Отнася се за набор от данни, които бихме искали да изчислим средно. В този пример искаме да получим средния размер на продажбите за всички продадени телефони в САЩ.
3. D2: D16 - Отнася се за обхвата от данни, за да провери дали той отговаря на критериите, за да бъде включен в средната формула.
4. "САЩ" - Критерии за търсене в посочения диапазон от данни
5. ) - Затваряща скоба, показваща краищата на формулата.

Формулата показва, че сме продали продукта наоколо $ 1008 на телефон в САЩ.

Всички три формула могат да приемат повече от един критерий. Например, ако искаме да знаем същите цифри (т.е.. БРОЯ, SUM и СРЕДНО АРИТМЕТИЧНО) за продукти, продадени в САЩ, но специално само за Samsung марка, ние просто трябва да добавим обхвата на данните, който да бъде проверен, последван от неговите критерии.

Моля, вижте примера по-долу, където към критериите за първоначални критерии се добавя втори критерий. (Синият текст показва първите критерии, а червеният - втория критерий)

= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")

Ще забележите, че Excel също има COUNTIF, SUMIF и AVERAGEIF формули без суфикса "С". Те се използват подобно на COUNTIFS, SUMIFS и AVERAGEIFS. Въпреки това, тези без наставка "С" във формулата имат ограничение само за допускане на един критерий за формула.

Тъй като синтаксисът е малко по-различен, бих препоръчал да го използвате COUNTIFS, SUMIFS и AVERAGEIFS само ако може да се използва за един или повече критерии, ако е необходимо. Наслади се!