Средно, ако не е празно - Excel и Google Таблици

Изтеглете Примерна работна книга

Изтеглете примерната работна книга

Този урок ще покаже как да се изчисли средната стойност на набор от числа, като се игнорират празни стойности или категории в Excel и Google Sheets.

Игнорирайте празни стойности с функция AVERAGE

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

Този пример използва функцията AVERAGE за изчисляване на Среден резултат като игнорира текстовата стойност на Резултат на Екип Б и празната стойност на Резултат на Екип В.:

1	= СРЕДНА (C3: C7)

Функцията AVERAGE използва само числените стойности, свързани с Отбори A, D и E

Третирайте текстовите стойности като нула с функция AVERAGEA

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

Този пример използва функцията AVERAGEA за изчисляване на Среден резултат като игнорира празното място Резултат стойност на Екип В. и третиране на текста Резултат стойност на Екип Б като 0:

1	= СРЕДНА (C3: C7)

Този пример изчислява средната стойност на стойностите 4, 0, 5 и 3

Игнорирайте празни стойности на категория, използвайки AVERAGEIFS

Този пример използва функцията AVERAGEIFS за изчисляване на Среден резултат за екипи с непразни имена и игнориращи Резултати това са текстови стойности

1	= СРЕДНИ (C3: C7, B3: B7, "")

Игнориране на празни стойности на категория - масив

Ако нямате достъп до функцията AVERAGEIF, можете да използвате вложена функция AVERAGE и IF.

Този пример изчислява Среден резултат за екипи с непразни имена и игнорира всички Резултати това е текстова стойност

1	= СРЕДНО (АКО (B3: B7 "", C3: C7))

Тази формула използва функцията IF, за да прецени дали името на всеки от тях Екип не е празно и използва само Резултати от Екипи с непразни имена във функцията AVERAGE. Резултатът от 100 се игнорира като Екип името е празно. Резултатът от 'Няма данни' се игнорира, тъй като не е числова стойност.

Във версии на Excel от 2022 г. и по -рано тази формула трябва да бъде въведена като формула на масив чрез натискане на CTRL + SHIFT + ENTER. Това не се изисква в по -късните версии

Този пример може също да се изчисли с помощта на функция AVERAGEIFS, както е показано по -долу

Игнорирайте празни стойности на категорията и третирайте текста като 0

За да се третира текстовата стойност Резултати като 0, като все още се игнорира Екипи с празни имена, можем да използваме вложена функция AVERAGEA и IF:

1	= СРЕДНА СРЕДА (АКО (B3: B7 "", C3: C7))

Тази формула използва функцията IF, за да прецени дали името на всеки от тях Екип не е празно и използва само Резултати от Екипи с непразни имена във функцията AVERAGEA. Резултатът от 100 се игнорира като Екип името е празно. Резултатът от 'Няма данни' се третира като 0 от функцията AVERAGEA и така се изчислява средно 3.0.

Във версии на Excel от 2022 г. и по -рано тази формула трябва да бъде въведена като формула на масив чрез натискане на CTRL + SHIFT + ENTER. Това не се изисква в по -късните версии

Средно, ако не е празно в Google Таблици

Повечето от тези примери за формули работят по същия начин в Google Sheets, както и в Excel, но с някои изключения:

Когато функцията IF е вложена в друга функция и се позовава на диапазон от клетки, Google Sheets трябва да третира формулата като формула на масив, като използва функцията ARRAYFORMULA:

1	= ArrayFormula (СРЕДНА (IF (B3: B7 "", C3: C7)))

За съжаление, функцията AVERAGEA работи по различен начин в Google Sheets, както в Excel, така че примерът на = AVERAGEA (IF (B3: B7 ””, C3: C7)) не може да се използва. Това е така, защото когато функцията IF намери празна стойност в диапазона B3: B7, тя произвежда нулева стойност, но след това тя се отчита като 0 от функцията Google Sheets AVERAGEA, вместо да игнорира тази стойност, както би било в случая Excel.