Използвайте AVERAGEIF на Excel за игнориране на нулеви стойности при намиране на средната стойност

Функцията AVERAGEIF бе добавена в Excel 2007, за да улесни намирането на средната стойност в набор от данни, които отговарят на определен критерий.

Едно такова използване на функцията е да се наложи да се игнорират нулеви стойности в данните, които изваждат средната или аритметичната средна стойност при използване на редовната функция AVERAGE .

В допълнение към данните, които се добавят към работен лист, нулевите стойности могат да бъдат резултат от изчисленията на формулите - особено при непълни работни листове .

Игнориране на нули при намиране на средната стойност

Изображението по-горе съдържа формула, използваща AVERAGEIF, която игнорира нулеви стойности. Критерият във формулата, който прави това, е " <> 0".

"<>" Символът не е равен символ в Excel и се създава чрез натискане на ъгловите скоби - разположени в долния десен ъгъл на клавиатурата - обратно назад;

Примерите в изображението използват една и съща основна формула - само промените в диапазона. Различните получени резултати се дължат на различните данни, използвани във формулата.

Синтаксис и разширения на функцията AVERAGEIF

Синтаксисът на функцията се отнася до оформлението на функцията и включва името на функцията , скобите и аргументите .

Синтаксисът за функцията AVERAGEIF е:

= AVERAGEIF (диапазон, критерии, средна стойност)

Аргументите за функцията AVERAGEIF са:

Обхват - (изисква се) групата клетки, която функцията ще търси, за да намери съвпадения за аргумента " Критерии" по-долу.

Критерии - (задължително) определя дали данните в клетката трябва да бъдат осреднени или не

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

Функцията AVERAGEIF пренебрегва:

• Клетки в аргумента Average_range , съдържащи булеви (TRUE или FALSE) стойности - ред 5 по-горе;
• Клетки в Average_range, които са празни - ред 4 по-горе;

Забележка:

• Ако никоя клетка от обхвата не отговаря на определените критерии, AVERAGEIF връща # DIV / 0! стойност на грешката - ред 7 в примера, където всички клетки в диапазона са равни на нула.
• Ако аргументът на Range е напълно празен или съдържа само текстови стойности, AVERAGEIF връща # DIV0! грешка value.- редове 6 и 8 по-горе;

Игнориране на нули Пример

Опциите за въвеждане на функцията AVERAGEIF и нейните аргументи включват:

1. Въвеждане на пълната функция, като например: = AVERAGEIF (A3: C3, "<> 0") в клетка на работен лист;
2. Избиране на функцията и нейните аргументи чрез диалоговия прозорец на функцията AVERAGEIF .

Въпреки че е възможно просто да въведете пълната функция ръчно, много хора смятат, че е по-лесно да се използва диалоговият прозорец, тъй като се грижи за въвеждането на синтаксиса на функциите - например скобите и сепараторите на запетаи, които се изискват между аргументите.

Освен това, ако функцията и нейните аргументи са въведени ръчно, аргументът за критериите трябва да бъде заобиколен от кавички: "<> 0" . Ако диалоговият прозорец се използва за въвеждане на функцията, той ще добави кавичките за вас.

По-долу са изброени стъпките, използвани за въвеждане на AVERAGEIF в клетка D3 на горния пример, използвайки диалоговия прозорец на функцията.

Отваряне на диалоговия прозорец AVERAGEIF

1. Кликнете върху клетката D3, за да стане активната клетка - мястото, където ще се показват резултатите от функцията;
2. Кликнете върху раздела Формули на лентата ;
3. Изберете още функции> Статистически от лентата, за да отворите падащия списък на функциите;
4. Кликнете върху AVERAGEIF в списъка, за да изведете диалоговия прозорец на функцията;
5. В диалоговия прозорец кликнете върху линията Range ;
6. Маркирайте клетки А3 до С3 в работния лист, за да влезете в диапазона;
7. В диалоговия прозорец " Критерии " въведете: <> 0 ;
8. Забележка: Средната_редактика е оставена празна, тъй като намираме средната стойност за едни и същи клетки, въведени за аргумента " Range" ;
9. Кликнете върху OK, за да затворите диалоговия прозорец и да се върнете в работния лист;
10. Отговорът 5 трябва да се появи в клетка D3;
11. Тъй като функцията пренебрегва нулевата стойност в клетка B3, средната стойност на останалите две клетки е 5: (4 + 6) / 2 = 10;
12. Ако кликнете върху клетката D8 пълната функция = AVERAGEIF (A3: C3, "<> 0") се появява в лентата за формули над работния лист.