В тази статия ще научим изчисляването на средната стойност за числата, отговарящи на зададените критерии в Excel.
Сценарий:
Когато работим с данни, където трябва да получим средната стойност на числата със стойности на критерии. Защо стойностите на критериите създават разлика в резултата, функцията Excel трябва да я счита за нула и да вземе резултата. Нулева стойност може да направи разлика в средната стойност на числата. Средната стойност на числата е сумата от необходимите числа, разделена на броя на числата. Когато нулата не допринася за сумата, но увеличава броя. Разберете как да решите проблема.
Как да решим проблема?
Тук трябва да намерим средната стойност на числата с критерии. Критериите са игнориране на стойностите на критериите. Ако имате само положителни или отрицателни числа, можете да използвате функцията AVERAGEIF
Синтаксис на формулата (положителни числа):
| = AVERAGEIF (стойности, "> = 0") |
Горната формула включва нули, но пренебрегва стойностите на критериите. Ако искате да игнорирате нули и стойности на критерии, просто използвайте критериите като "> 0".
По същия начин можете да получите средната стойност на отрицателните стойности, включително нули, като използвате формулата:
Синтаксис на формулата (отрицателни числа):
| = AVERAGEIF (стойности, "<= 0") |
Горната формула включва нули, но пренебрегва стойностите на критериите. Ако искате да игнорирате нули и стойности на критерии, просто използвайте критериите като "> 0"
Но проблемът с разглеждането на числата, пренебрегващи критериите, не е решен. Може би си мислите, че приемането на средната стойност от горните два резултата (средна стойност на положителни числа и средна стойност на отрицателни числа) ще даде необходимата стойност. Не работи добре, докато броят на положителните числа не се изравни с броя на отрицателните числа. За този проблем ще използваме функцията AGGREGATE. Функцията AGREGATE е пакет от математически функции. Помага при общото решаване на подобни проблеми. За да разопаковате този пакет за средната стойност, пренебрегвайки критериите. Използвайте формулата по -долу.
Синтаксис на формулата (за всички числа):
| = АГРЕГАТ (1, 6, стойности) |
1: Аргумент за средни числа
6: игнориране на стойностите на критериите
Пример:
Всичко това може да е объркващо за разбиране. Нека вземем пример и да разберем как да използваме тези формули в пример. Тук имаме списък със стойности, за които се изисква средна стойност.
Първо изчисляваме средната стойност за положителните числа. За целта ще използваме по -долу за данни.
Използвайте формулата:
| = СРЕДЕН (B3: B15, "> = 0") |
Обяснение
- Критериите са числа, които са по -големи или равни на нула.
- Функцията AVERAGEIF разглежда само числата, които отговарят на критериите и връща средната стойност на числата
Тук формулата връща 58.6, като се вземат предвид само положителните стойности, където броят на положителните стойности е 7. По подобен начин можете да получите средната стойност на отрицателните числа.
Използвайте формулата:
| = СРЕДЕН (B3: B15, "<= 0") |
Обяснение
- Критериите са числа, които са по -малки или равни на нула.
- Функцията AVERAGEIF разглежда само числата, които отговарят на критериите и връща средната стойност на числата.
За отрицателни числа средната стойност е -21,0, включително нули, пренебрегващи стойностите на критериите. Преброяването към взетите числа е 4.
Тук в този пример броят на отрицателните числа не е равен на броя на положителните числа. Така че средната стойност на горните 2 резултата няма да даде средната стойност на всички числа. Използвайте формулата по -долу за тези случаи.
Използвайте формулата:
| = АГРЕГАТ (1, 6, B3: B15) |
Обяснение:
- Аргумент 1 е даден за използване на функция Average.
- Аргумент 6 е даден за пренебрегване на стойностите на критерия.
- Функцията AGGREGATE връща средната стойност на всички числа в масива B3: B15, пренебрегвайки стойностите на критериите.
Средната стойност на всички числа, пренебрегващи стойностите на всички критерии, е 36,2. Тук броят на числата е 9. Функцията AGREGATE е най -добрият начин за решаване на тези проблеми, тъй като тези функции са отлични вградени функции.
Ако се опитвате да получите средната стойност на числата с помощта на основната функция AVERAGE, трябва да използвате формулата по -долу, за да получите средната стойност на числата, пренебрегвайки критериите.
Използвайте формулата:
| { = СРЕДНА (IF (ISкритерии (B3: B15), "", B3: B15)) } |
Забележка : не използвайте къдравите скоби ръчно. Просто използвайте Ctrl + Shift + Enter на мястото на Въведете за да получите резултата.
Обяснение:
- Функцията IF проверява условието с функцията IScriteria в масива B3: B15.
- Функцията IScriteria връща True за стойността, където стойността е критерий, и False, където не.
- Функцията IF връща празна стойност, ако стойността е критерий, и връща съответната стойност, ако стойността не е критерии.
- Функцията AVERAGE връща средната стойност на върнатите стойности.
- Къдравите скоби карат тези логически функции да работят за масив.
Както можете да видите, формулата работи добре и връща същия резултат. Но използвайте функцията AGGREGATE, тя е лесна за използване за различни математически функции с различни вградени критерии.
Ако нямате нулева стойност в масива. Можете да използвате връщащата нула от всички стойности на критерия и да използвате формулата как да вземете средната стойност на числата, игнорирайки нули тук. Но тази формула не работи със стойности на критерия.
Ето всички забележки от наблюденията относно използването на формулата.
Бележки:
- Функцията AGREGATE е най -добрата функция за тези проблеми.
- Функцията връща #VALUE! критерии, ако никоя стойност не отговаря на дадените критерии.
- Приемат се различни критерии във формулата, като например равно на (=), по -малко от (<), по -малко или равно на (=), по -голямо от (>) и не е равно на ().
По този начин научихме Изчисляването на средната стойност за числата, отговарящи на определени критерии в Excel. Можете да използвате тези функции в Excel 2016, 2013 и 2010. Намерете още статии за математическа формулировка с различни критерии. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Как да подчертаем клетки над и под средната стойност: маркирайте стойности, които са над или под средната стойност, като използвате условното форматиране в Excel.
Игнорирайте нулата в средната стойност на числата: изчислете средната стойност на числата в масива, игнорирайки нули, като използвате функцията AVERAGEIF в Excel.
Изчислете среднопретеглена стойност: намерете средната стойност на стойностите с различно тегло, като използвате функцията SUMPRODUCT в Excel.
Средна разлика между списъците: изчислете средната разлика между два различни списъка. Научете повече за това как да изчислите средната стойност, използвайки основната математическа формула за средна стойност.
Средни числа, ако не са празни в Excel: извличане на средната стойност, ако клетката не е празна в Excel.
СРЕДНО на първите 3 резултата в списък в Excel: Намерете средната стойност на числата с критерии като най -високите 3 числа от списъка в Excel
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.