W tym artykule dowiemy się, jak uzyskać średnią z wieloma kryteriami w programie Excel.
Problem?
Na przykład mamy dużą listę danych i musimy znaleźć średnią ceny lub kwoty, biorąc pod uwagę pewne kryteria. Kryteria można zastosować do dowolnej kolumny danych.
Jak rozwiązać problem.
Teraz zrobimy formułę z funkcji. Tutaj mamy dane i musieliśmy znaleźć ŚREDNIĄ liczb mających pewne kryteria
Wzór ogólny:
= ŚREDNIA.WARUNKI (zakres_średni , zakres1, kryterium1, [zakres2, kryterium2 ], [zakres3, kryterium3 ],… )
- Średnia_zakres : zakres wartości, w którym należy ocenić średnią
- range1 : pierwszy zakres, w którym zastosowano kryterium1.
- kryteria1 : pierwsze kryteria zastosowane w zakresie1.
- range2 : drugi zakres, w którym zastosowano kryterium1.
- Kryteria2 : drugie kryterium zastosowane w zakresie2.
- range3 : trzeci zakres, w którym zastosowano kryterium1.
- Kryteria3 : trzecie kryterium stosowane w zakresie3.
Uwagi: nie dostarczaj Data bezpośrednio do funkcji. Użyj funkcji DATA lub użyj odwołania do komórki dla argumentu daty w programie Excel, ponieważ Excel odczytuje datę tylko w prawidłowej kolejności.
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj podaliśmy dane z A1:D51. Musimy znaleźć ŚREDNIĄ otrzymaną ilość według różnych kryteriów.
Warunki są następujące.
- Data musi przypadać przed 31.01.2019.
- Miasto "Boston"
- Produkt należy zdefiniować jako „Marchew”
- Ilość musi być większa niż 50 (>50).
Powyższe 4 podane warunki lub kryteria muszą być spełnione. Funkcja AVERAGEIFS może pomóc nam wyodrębnić średnią o różnych kryteriach.
Użyj formuły:
= ŚREDNIA JEŻELI (Ilość , data_zamówienia , "<=" & F4 , miasto , "Boston" , produkt , F5 , Ilość , F6 )
Ilość: nazwany zakres używany dla tablicy D2: D50
Order_date : nazwany zakres użyty dla tablicy A2:A50
Miasto : nazwany zakres używany w tablicy B2:B50
Produkt: nazwany zakres używany dla tablicy C2: C50
F4 : odwołanie do komórki używane dla kryterium 1
F5 : odwołanie do komórki używane dla kryterium 2
F6 : odwołanie do komórki używane dla kryterium 3
Wyjaśnienie:
- data_zamówienia , "<=" & F4pierwsze kryteria sugerujące datę przed 31.01.2019 w F4.
- miasto , "Boston" drugie kryterium, w którym miasto pasuje do „Boston”
- produkt , F5 gdzie produkt pasuje do marchewki w komórce F5
- Ilość, musi podlegać kryteriom w Komórka F6 tj. powyżej 50.
Tutaj A2 jest podany jako odwołanie do komórki i nazwane zakresy podane jako rng (D2: D51) i Data zamówienia (A2:A51).
Jak widać na powyższym zrzucie, ŚREDNIA ilości w styczniu wynosi 54.00. Ponieważ istnieje tylko jedna wartość spełniająca wszystkie warunki.
Przykład 2:
Tutaj podaliśmy dane z A1:D51. Musimy znaleźć ŚREDNIĄ otrzymaną ilość według różnych kryteriów.
Warunki są następujące.
- Data musi przypadać przed 27.02.2019.
- Miasto "Nowy Jork"
- Produkt należy zdefiniować jako „Czekoladowy Chip”.
- Ilość musi być mniejsza niż 50 ( < 50 ).
Powyższe 4 podane warunki lub kryteria muszą być spełnione. Funkcja AVERAGEIFS może pomóc nam wyodrębnić średnią o różnych kryteriach.
Użyj formuły:
= ŚREDNIA JEŻELI (Ilość , data_zamówienia , "<=" & F4 , miasto , "Nowy Jork" , produkt , F5 , Ilość , F6 )
Ilość: nazwany zakres używany dla tablicy D2: D50
Order_date : nazwany zakres użyty dla tablicy A2:A50
Miasto : nazwany zakres używany w tablicy B2:B50
Produkt: nazwany zakres używany dla tablicy C2: C50
F4 : odwołanie do komórki używane dla kryterium 1
F5 : odwołanie do komórki używane dla kryterium 2
F6 : odwołanie do komórki używane dla kryterium 3
Wyjaśnienie:
- data_zamówienia , "<=" i F4pierwsze kryterium sugerujące datę przed 27.02.2019 w komórce F4.
- miasto , "Boston" drugie kryterium, w którym miasto pasuje do „Boston”
- produkt , F5 gdzie produkt pasuje do marchewki w komórce F5
- Ilość, musi podlegać kryteriom w Komórka F6 tj. mniej niż 50.
Tutaj A2 jest podany jako odwołanie do komórki i nazwane zakresy podane jako rng (D2: D51) i Data zamówienia (A2:A51).
Jak widać na powyższym zrzucie, ŚREDNIA ilości w styczniu wynosi 33.33… . Ponieważ istnieje tylko jedna wartość spełniająca wszystkie warunki.
Oto kilka obserwacji dotyczących użycia formuły poniżej.
Uwagi:
- Funkcja zwraca #ARG! błąd, jeśli argument funkcji nie jest numeryczny.
- Funkcja zwraca #ARG! błąd, jeśli żadna wartość nie spełnia podanych kryteriów.
- Formuła akceptuje operatory, takie jak =, < , = , <= & .
- Nie rób podaj argument date bezpośrednio do funkcji. Użyj funkcji DATA lub użyj odwołania do komórki dla argumentu daty w programie Excel, ponieważ Excel odczytuje datę tylko w prawidłowej kolejności.
Mam nadzieję, że ten artykuł o tym, jak uśredniać z wieloma kryteriami w programie Excel, jest objaśniający. Więcej artykułów na temat funkcji SUMPRODUCT znajdziesz tutaj. Podziel się swoim zapytaniem poniżej w polu komentarza. Pomożemy Ci.
Jak korzystać z funkcji ŚREDNIA.JEŻELI w programie Excel?
Jak korzystać z funkcji DAVERAGE w programie Excel?
Jak wyróżnić komórki powyżej i poniżej średniej wartości
Ignoruj zero w średniej liczb
Oblicz średnią ważoną
Średnia różnica między listami
Walidacja wpisów tekstowych
Utwórz listę rozwijaną w programie Excel z kolorem
popularne artykuły
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Przeglądaj według daty
Dołącz imię i nazwisko w programie Excel
Policz komórki, które pasują do A lub B
Konwertuj cale na stopy i cale w programie Excel 2016
50 skrótów Excela, aby zwiększyć produktywność