=ŚREDNIA.JEŻELI(A2:A7;"North";C2:C7) (po angielsku AVERAGEIF) liczy średnią sprzedaży z C2:C7 w wierszach, w których kolumna A to North. Działa jak SUMA.JEŻELI, tylko dzieli sumę przez liczbę pasujących wierszy. Tabele pokazują formuły po angielsku, ale możesz w nich wpisywać formuły także po polsku, ze średnikami.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Average | |
| 2 | North | Apple | 120 | North | 90 | |
| 3 | South | Pear | 45 | North, Apple | 80 | |
| 4 | North | Pear | 110 | Over 50 | 120 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 195 | |||
| 7 | North | Apple | 40 |
=ŚREDNIA.JEŻELI(A2:A7;"North";C2:C7)F2 liczy średnią z trzech wierszy North, 120, 110 i 40, i pokazuje 90. F3 potrzebuje dwóch warunków, North i Apple, więc używa ŚREDNIA.WARUNKÓW (AVERAGEIFS): (120 + 40) / 2 = 80. F4 nie ma osobnego zakresu średniej, więc liczy średnią z samych pasujących wartości sprzedaży.
Składnia funkcji ŚREDNIA.JEŻELI i ŚREDNIA.WARUNKÓW
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Kolejność argumentów to ta sama pułapka co w SUMA.JEŻELI i SUMA.WARUNKÓW: ŚREDNIA.JEŻELI stawia zakres do uśrednienia na końcu (i pozwala go pominąć), a ŚREDNIA.WARUNKÓW na początku. Kryteria zapisuje się w obu tak samo: "North", ">50", "<>0", "*apple*" albo operator dołączony do komórki, ">"&F5. Puste komórki i tekst w zakresie średniej są pomijane.
Średnia bez zer
ŚREDNIA (AVERAGE) traktuje 0 jak wartość, więc dwóch nieobecnych uczniów z wynikiem 0 obniża średnią klasy. Z pustymi komórkami jest inaczej: ŚREDNIA je pomija. =ŚREDNIA.JEŻELI(B2:B7;"<>0") pomija także zera.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Method | Result | |
| 2 | Ana | 80 | AVERAGE | 48 | |
| 3 | Ben | 0 | Ignore zeros | 80 | |
| 4 | Cara | 90 | Count of zeros | 2 | |
| 5 | Dan | Count of numbers | 5 | ||
| 6 | Eva | 70 | |||
| 7 | Finn | 0 |
=ŚREDNIA.JEŻELI(B2:B7;"<>0")ŚREDNIA dzieli 240 przez 5, bo pusta komórka Dana jest pominięta, ale dwa zera są liczone, i pokazuje 48. ŚREDNIA.JEŻELI z "<>0" dzieli 240 przez 3 i pokazuje 80. Wpisz 60 w B5, a zmienią się obie; wpisz 0 w B5, a zmieni się tylko ŚREDNIA. Aby pominąć także liczby ujemne, użyj ">0".
Dlaczego ŚREDNIA.JEŻELI zwraca #DZIEL/0!
Gdy nic nie pasuje, ŚREDNIA.JEŻELI nie ma przez co dzielić i zwraca #DZIEL/0! (po angielsku #DIV/0!; tabele pokazują angielskie nazwy błędów). Obejmij ją funkcją JEŻELI.BŁĄD (IFERROR), aby zamiast tego pokazać kreskę, komunikat albo pustą komórkę.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | West average | #DIV/0! | |
| 3 | South | Pear | 45 | With IFERROR | No sales | |
| 4 | North | Pear | 110 | North max | 120 | |
| 5 | East | Apple | 55 | North min | 40 | |
| 6 | South | Apple | 195 | Apple max | 195 | |
| 7 | North | Apple | 40 |
#DIV/0! Formuła dzieli przez zero lub przez pustą komórkę.W polskim Excelu: =ŚREDNIA.JEŻELI(A2:A7;"West";C2:C7)Nie ma wiersza West, więc F2 pokazuje #DIV/0!, a F3 komunikat. Zmień A3 na West, a obie komórki pokażą 45.
MAKS.WARUNKÓW i MIN.WARUNKÓW
F4 do F6 w tabeli powyżej znajdują największą i najmniejszą wartość z warunkiem. Używają kolejności ŚREDNIA.WARUNKÓW, z przeszukiwanym zakresem na początku: =MAKS.WARUNKÓW(C2:C7;A2:A7;"North") (po angielsku MAXIFS) zwraca 120, a =MIN.WARUNKÓW(C2:C7;A2:A7;"North") (MINIFS) zwraca 40. W przeciwieństwie do ŚREDNIA.JEŻELI zwracają 0, a nie błąd, gdy nic nie pasuje.
MAKS.WARUNKÓW i MIN.WARUNKÓW wymagają Excela 2019 lub nowszego albo Microsoft 365. W Excelu 2016 i starszym to samo robi =MAX(JEŻELI(A2:A7="North";C2:C7)); w tych wersjach zatwierdź ją klawiszami Ctrl+Shift+Enter (Cmd+Shift+Enter na Macu).
Ćwiczenie: średnia z dwoma warunkami
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Class | Score | Condition | Average | |
| 2 | Ana | A | 80 | Class A, no zeros | ||
| 3 | Ben | B | 75 | |||
| 4 | Cara | A | 0 | |||
| 5 | Dan | B | 60 | |||
| 6 | Eva | A | 90 | |||
| 7 | Finn | B | 0 | |||
| 8 | Gus | A | 70 |
Twoja kolej: Policz średnią wyników klasy A, pomijając zera (nieobecnych uczniów). Wpisz formułę w F2.
Średnia ze średnich: częsty błąd
Średnia ze średnich grup o różnej wielkości daje złą średnią ogólną. North ma trzy wiersze, a South dwa, więc w średniej z dwóch średnich każdy wiersz South waży więcej, niż powinien.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Formula | Result | |
| 2 | North | 120 | North | 90 | |
| 3 | South | 45 | South | 120 | |
| 4 | North | 110 | Average of the two | 105 | |
| 5 | South | 195 | All rows | 102 | |
| 6 | North | 40 |
=ŚREDNIA(E2:E3)E4 pokazuje 105, a E5 prawdziwą średnią z pięciu wierszy, 102. Gdy grupy mają różną wielkość, licz średnią z samych wierszy jedną funkcją ŚREDNIA.WARUNKÓW albo podziel SUMA.WARUNKÓW przez LICZ.WARUNKI z tymi samymi warunkami:
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
W polskim Excelu: =SUMA.WARUNKÓW(B2:B6;A2:A6;"North")/LICZ.WARUNKI(A2:A6;"North").
Wynik ważony punktami ECTS albo ilością to jeszcze inne obliczenie: to średnia ważona.
Najczęściej zadawane pytania
Czym różni się ŚREDNIA.JEŻELI od ŚREDNIA.WARUNKÓW?
ŚREDNIA.JEŻELI przyjmuje jeden warunek i stawia zakres średniej na końcu: =ŚREDNIA.JEŻELI(A2:A7;"North";C2:C7). ŚREDNIA.WARUNKÓW przyjmuje kilka warunków i stawia zakres średniej na początku: =ŚREDNIA.WARUNKÓW(C2:C7;A2:A7;"North";B2:B7;"Apple").
Jak policzyć średnią w Excelu bez zer?
Użyj =ŚREDNIA.JEŻELI(B2:B7;"<>0"). Liczy średnią tylko z komórek różnych od 0. Puste komórki ŚREDNIA i ŚREDNIA.JEŻELI i tak pomijają, więc warunku potrzebują tylko prawdziwe zera.
Dlaczego ŚREDNIA.JEŻELI zwraca #DZIEL/0!?
Żadna komórka nie spełniła warunku, więc Excel dzieli sumę 0 przez liczbę 0. Obejmij formułę funkcją, aby pokazać coś innego: =JEŻELI.BŁĄD(ŚREDNIA.JEŻELI(A2:A7;"West";C2:C7);"No data").
Jak znaleźć największą wartość z warunkiem?
Użyj MAKS.WARUNKÓW, z przeszukiwanym zakresem na początku: =MAKS.WARUNKÓW(C2:C7;A2:A7;"North") zwraca największą wartość North. MIN.WARUNKÓW działa tak samo dla najmniejszej. Obie wymagają Excela 2019 lub nowszego albo Microsoft 365.
