AVERAGE в Excel: защо празната клетка и нулата дават различен резултат

Разграничавайте липсващото измерване от отчетената нула

В работилница записват колко минути е престояла една машина при четири проверки. Въведени са 12, 0, празна клетка и 18. Нулата означава, че проверката е направена и не е отчетен престой. Празната клетка означава, че за тази проверка няма записан резултат.

Поставете имената на проверките в A2:A5, а стойностите в B2:B5. Оставете B4 действително празна. Това разграничение е важно още преди формулата: таблицата не може да отгатне дали липсващата стойност е трябвало да бъде нула.

Изчислете средната стойност на записаните измервания

Има три числови измервания: 12, 0 и 18. Сборът им е 30, затова средната аритметична стойност е 30, разделено на 3, или 10 минути. AVERAGE върху този диапазон включва нулата и пропуска празната клетка.

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

=AVERAGE(B2:B5)

Покажете броя на измерванията до резултата

Самата средна стойност не показва дали са записани и четирите проверки. COUNT връща 3 за този диапазон. Ако покажете едновременно 10 минути и 3 записани проверки, липсващото измерване се забелязва по-лесно.

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

=COUNT(B2:B5)

Вижте какво се променя, когато въведете нула

Въведете 0 в B4. Сборът остава 30, но броят става 4, така че средната стойност вече е 7,5 минути. Това е правилно само ако при тази проверка действително е отчетен нулев престой.

Не въвеждайте нула само за да изглежда таблицата попълнена. Така променяте смисъла на данните и намалявате средната стойност. Не изтривайте и реална нула само защото искате средна стойност на положителните измервания — това е друг въпрос и трябва да бъде обозначен ясно.

Текстовата бележка не е числово измерване

Ако B4 съдържа бележката „не е записано“, AVERAGE върху диапазона продължава да използва 12, 0 и 18 и да връща 10. Бележката може да обяснява пропуска, но не добавя числово измерване. Формула, която връща празен текст, също не добавя число.

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

Обработете случая без нито едно измерване

Ако всички клетки с измервания са празни, няма средна аритметична стойност за изчисляване. AVERAGE връща #DIV/0!, защото броят на числовите измервания е нула. Ако вместо това покажете 0, ще създадете невярното впечатление за измерена стойност.

Формулата с проверка по-долу връща „Няма измервания“, когато диапазонът не съдържа числа. При поне едно числово измерване изчислява средната стойност. Тя предполага, че входните клетки не съдържат грешки във формули: при само грешки и празни клетки COUNT също е нула и съобщението би скрило грешките. Проверете ги отделно, преди да използвате тази формула.

=IF(COUNT(B2:B5)=0,"Няма измервания",AVERAGE(B2:B5))

Изпробвайте промените в данните

Започнете от първоначалния резултат 10 минути. Въведете 0 в B4 и изчислете отново — ще получите 7,5. Заменете нулата с 6, за да получите 9: сборът на четирите измервания вече е 36. Възстановяването на примера връща първоначалната празна клетка.

За да изпробвате формулата с проверка, поставете я в полето за формула и изчистете B2:B5. Резултатът трябва да е „Няма измервания“. Въведете само 0 в B3: сега резултатът трябва да бъде 0, защото вече има едно реално числово измерване.

Допълнително четене

• Microsoft: функция AVERAGE (на английски)
• Microsoft: функция COUNT (на английски)