W tym artykule nauczymy się obliczania średniej dla liczb spełniających określone kryteria w programie Excel.
Scenariusz:
Podczas pracy z danymi, gdzie musimy uzyskać średnią liczb mających wartości kryteriów. Dlaczego wartości kryteriów powodują różnicę w wyniku, funkcja Excela powinna uznać ją za zero i przyjąć wynik. Wartość zero może mieć wpływ na średnią liczb. Średnia liczb to suma wymaganych liczb podzielona przez liczbę liczb. Gdzie zero nie przyczynia się do sumy, ale zwiększa liczbę. Dowiedz się, jak rozwiązać problem.
Jak rozwiązać problem?
Tutaj musimy znaleźć średnią liczb z kryteriami. Kryteria ignorują wartości kryteriów. Jeśli masz tylko liczby dodatnie lub liczby ujemne, możesz użyć funkcji ŚREDNIA
Składnia formuły (liczby dodatnie):
| =ŚREDNIA.JEŻELI(wartości; ">=0") |
Powyższy wzór zawiera zera, ale ignoruje wartości kryteriów. Jeśli chcesz zignorować zera i wartości kryteriów, użyj po prostu kryteriów jako „>0”.
Podobnie możesz uzyskać średnią wartości ujemnych, w tym zer, korzystając ze wzoru:
Składnia formuły (liczby ujemne):
| =ŚREDNIA.JEŻELI(wartości; "<=0") |
Powyższy wzór zawiera zera, ale ignoruje wartości kryteriów. Jeśli chcesz zignorować zera i wartości kryteriów, użyj kryteriów jako „>0”
Ale problem uwzględniania liczb ignorujących kryteria nie został rozwiązany. Możesz pomyśleć, że biorąc średnią z powyższych dwóch wyników (średnia liczb dodatnich i średnia liczb ujemnych) da wymaganą wartość. Nie działa dobrze, dopóki liczba liczb dodatnich nie będzie równa liczbie liczb ujemnych. Do tego problemu użyjemy funkcji AGREGAT. Funkcja AGREGACJA to pakiet funkcji matematycznych. Pomaga w ogólnym rozwiązywaniu tego rodzaju problemów. Aby rozpakować ten pakiet za średnią wartości ignorujących kryteria. Użyj poniższego wzoru.
Składnia formuły (dla wszystkich liczb):
| =AGREGUJ(1, 6; wartości) |
1: Argument do liczb średnich
6: ignorowanie wartości kryteriów
Przykład :
Wszystko to może być trudne do zrozumienia. Weźmy przykład i zrozummy, jak używać tych formuł w przykładzie. Tutaj mamy listę wartości, dla których wymagana jest średnia.
Najpierw obliczamy średnią dla liczb dodatnich. W tym celu użyjemy poniższych danych.
Użyj wzoru:
| =ŚREDNIA.JEŻELI(B3:B15;">=0") |
Wyjaśnienie
- Kryteria to liczby większe lub równe zeru.
- Funkcja AVERAGEIF uwzględnia tylko liczby spełniające kryteria i zwraca średnią z liczb
Tutaj formuła zwraca 58,6, biorąc pod uwagę tylko wartości dodatnie, gdzie liczba wartości dodatnich wynosi 7. Podobnie można uzyskać średnią liczb ujemnych.
Użyj wzoru:
| =ŚREDNIA.JEŻELI(B3:B15;"<=0") |
Wyjaśnienie
- Kryteria to liczby mniejsze lub równe zeru.
- Funkcja AVERAGEIF uwzględnia tylko liczby spełniające kryteria i zwraca średnią z liczb.
Dla liczb ujemnych średnia wynosi -21,0 z uwzględnieniem zer ignorujących wartości kryteriów. Policz do rozważanych liczb to 4.
W tym przykładzie liczba liczb ujemnych nie jest równa liczbie liczb dodatnich. Tak więc średnia z 2 powyższych wyników nie da średniej wszystkich liczb. W takich przypadkach użyj poniższego wzoru.
Użyj wzoru:
| =AGREGUJ(1, 6, B3:B15) |
Wyjaśnienie:
- Argumentem 1 jest użycie funkcji Średnia.
- Podano argument 6, aby zignorować wartości kryteriów.
- Funkcja AGREGACJA zwraca średnią wszystkich liczb w tablicy B3:B15 ignorując wartości kryteriów.
Średnia wszystkich liczb ignorujących wszystkie wartości kryteriów wynosi 36,2. Tutaj liczba liczb wynosi 9. Funkcja AGREGAT jest najlepszym sposobem rozwiązania tych problemów, ponieważ są to funkcje wbudowane w Excela.
Jeśli próbujesz uzyskać średnią liczb za pomocą podstawowej funkcji ŚREDNIA, musisz użyć poniższego wzoru, aby uzyskać średnią liczb ignorujących kryteria.
Użyj wzoru:
| { =ŚREDNIA(JEŻELI(J.kryteria(B3:B15);"",B3:B15)) } |
Notatka : nie używaj nawiasów klamrowych ręcznie. Po prostu użyj Ctrl + Shift + Enter zamiast Wejść aby uzyskać wynik.
Wyjaśnienie:
- Funkcja JEŻELI sprawdza warunek za pomocą funkcji IScriteria w tablicy B3:B15.
- Funkcja IScriteria zwraca True dla wartości, gdzie wartość jest kryterium, a False, gdy nie.
- Funkcja IF zwraca pustą wartość, jeśli wartość jest kryterium i zwraca odpowiednią wartość, jeśli wartość nie jest kryterium.
- Funkcja AVERAGE zwraca średnią z zwracanych wartości.
- Nawiasy klamrowe sprawiają, że te funkcje logiczne działają dla tablicy.
Jak widać formuła działa dobrze i zwraca ten sam wynik. Ale użyj funkcji AGREGUJ, która jest łatwa w użyciu dla różnych funkcji matematycznych z różnymi wbudowanymi kryteriami.
Jeśli nie masz żadnej wartości zerowej w tablicy. Możesz użyć zwracanego zera ze wszystkich wartości kryteriów i użyć wzoru, jak wziąć tutaj średnią liczb ignorujących zera. Ale ta formuła nie działa z wartościami kryteriów.
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Funkcja AGREGATE jest najlepszą funkcją do zastosowania w przypadku tych problemów.
- Funkcja zwraca #ARG! kryteria, jeśli żadna wartość nie odpowiada podanym kryteriom.
- Akceptowane są różne kryteria wewnątrz formuły, takie jak równy (=), mniejszy niż (<) , mniejszy lub równy (=) , większy niż (>) i nierówny ().
W ten sposób nauczyliśmy się obliczania średniej dla liczb spełniających określone kryteria w programie Excel. Możesz używać tych funkcji w Excel 2016, 2013 i 2010. Znajdź więcej artykułów na temat formuł matematycznych z różnymi kryteriami. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail
Jak wyróżnić komórki powyżej i poniżej średniej wartości: podświetl wartości, które są powyżej lub poniżej średniej wartości, używając formatowania warunkowego w programie Excel.
Ignoruj zero w średniej liczb: obliczyć średnią liczb w tablicy ignorując zera za pomocą funkcji ŚREDNIA.JEŻELI w programie Excel.
Oblicz średnią ważoną : znajdź średnią wartości o różnej wadze za pomocą funkcji SUMPRODUCT w Excelu.
Średnia różnica między listami : obliczyć różnicę w średniej z dwóch różnych list. Dowiedz się więcej o tym, jak obliczyć średnią za pomocą podstawowego wzoru matematycznego na średnią.
Średnie liczby, jeśli nie są puste w programie Excel: wyodrębnij średnią wartości, jeśli komórka nie jest pusta w programie Excel.
ŚREDNIA 3 najlepszych wyników na liście w programie Excel: Znajdź średnią liczb z kryteriami jako najwyższe 3 liczby z listy w Excelu
Popularne artykuły:
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.