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

Трите най-често използвани формули в Excel , които извършват прости математически изчисления, са COUNT , SUM и AVERAGE . Независимо дали управлявате финансов бюджет в Excel или просто следите следващата си ваканция, вероятно сте използвали една от тези функции преди.

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

Excel БРОЙ, СУМ и СРЕДНО

За да разберем колко мобилни телефона сме продали, можем бързо да използваме формулата COUNT , както е показано по-долу:(COUNT)

=COUNT(E2:E16)

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

=SUM(E2:E16)

И накрая, за да разберем средните продажби, които направихме за всички телефони, можем да използваме формулата СРЕДЕН(AVERAGE) , както е посочено по-долу:

=AVERAGE(E2:E16)

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

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

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

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

COUNTIFS

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

  1. =COUNTIFS( – “=” показва началото на формула в клетката, а COUNTIFS е първата част от функцията на Excel , която използваме.
  2. D2:D16 – Отнася се(Refers) за диапазон от данни, за да се провери дали отговаря на критериите, които трябва да бъдат включени във формулата за броене.
  3. „САЩ“(“USA”) – Критерии(Criteria) за търсене в посочения диапазон от данни ( D2:D16 )
  4. ) – Затваряща(Closing) скоба, указваща края на формулата.

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

СУМИФИ(SUMIFS)

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

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

Формулата показва общите продажби $6,050 , направени за продукти, изпратени от склада в САЩ .(USA)

СРЕДНИ КОФИ(AVERAGEIFS)

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

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

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

И трите формули могат да приемат повече от един критерий. Например, ако искаме да знаем едни и същи цифри (т.е. COUNT , SUM и AVERAGE ) за продукти, продавани в САЩ(USA) , но специално само за  марката Samsung , просто трябва да добавим диапазона от данни, който да бъде проверен, последван от неговите критерии.

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

=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 без суфикса „S“ . Те се използват подобно на COUNTIFS , SUMIFS и AVERAGEIFS . Въпреки това, тези без наставка „S“ във формулата имат ограничението да разрешават само един критерий за формула.

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



Цветан Келешев

About the author

Аз съм рецензент на софтуер и експерт по продуктивността. Преглеждам и пиша софтуерни рецензии за различни софтуерни приложения, като Excel, Outlook и Photoshop. Отзивите ми са добре информирани и дават обективна представа за качеството на приложението. Пиша софтуерни ревюта от 2007 г.


Related posts