W tym samouczku pokazano, jak używać funkcji programu Excel AVERAGEIF i AVERAGEIFS w programie Excel i Arkuszach Google do uśredniania danych spełniających określone kryteria.
AVERAGEIF Przegląd funkcji
Możesz użyć funkcji ŚREDNIA.JEŻELI w programie Excel, aby zliczyć komórki zawierające określoną wartość, zliczyć komórki większe lub równe wartości itp.
Aby użyć funkcji arkusza programu ŚREDNIA.JEŻELI, zaznacz komórkę i wpisz:
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
AVERAGEIF Składnia i argumenty funkcji:
=ŚREDNIA.JEŻELI (zakres, kryteria, [średnia_zakres])
zasięg - Zakres komórek do zliczenia.
kryteria - Kryteria kontrolujące, które komórki powinny być liczone.
średni zasięg - [opcjonalne] Komórki do średniej. W przypadku pominięcia używany jest zakres.
Co to jest funkcja ŚREDNIA.JEŻELI?
Funkcja ŚREDNIA.JEŻELI jest jedną ze starszych funkcji używanych w arkuszach kalkulacyjnych. Służy do skanowania zakresu komórek, sprawdzając określone kryterium, a następnie podając średnią (tzw. średnią matematyczną) wartości z zakresu odpowiadającego tym wartościom. Oryginalna funkcja ŚREDNIA.JEŻELI była ograniczona tylko do jednego kryterium. Po 2007 roku powstała funkcja ŚREDNIA.IFS, która pozwala na stosowanie wielu kryteriów. Większość ogólnych zastosowań pozostaje taka sama między tymi dwoma, ale istnieją pewne krytyczne różnice w składni, które omówimy w tym artykule.
Jeśli jeszcze tego nie zrobiłeś, możesz przejrzeć wiele podobnych struktur i przykładów w artykule COUNTIFS .
Podstawowy przykład
Rozważmy tę listę zarejestrowanych sprzedaży i chcemy poznać średni dochód.
Ponieważ mieliśmy wydatek, wartość ujemną, nie możemy po prostu zrobić podstawowej średniej. Zamiast tego chcemy uśrednić tylko wartości, które są większe niż 0. „Większe niż 0” będzie naszymi kryteriami w funkcji ŚREDNIA. Nasza formuła, aby to stwierdzić, to
=ŚREDNIA.JEŻELI(A2:A7; ">0")
Przykład dwukolumnowy
Chociaż oryginalna funkcja ŚREDNIA.JEŻELI została zaprojektowana, aby umożliwić zastosowanie kryterium do zakresu liczb, które chcesz zsumować, przez większość czasu będziesz musiał zastosować jedno lub więcej kryteriów do innych kolumn. Rozważmy tę tabelę:
Teraz, jeśli użyjemy oryginalnej funkcji AVERAGEIF, aby dowiedzieć się, ile średnio mamy bananów. Umieścimy nasze kryteria w komórce D1 i będziemy musieli podać zakres, który chcemy przeciętny jako ostatni argument, a więc nasza formuła byłaby
=ŚREDNIA.JEŻELI(A2:A7;D1;B2:B7)
Jednak gdy programiści w końcu zdali sobie sprawę, że użytkownicy chcą podać więcej niż jedno kryterium, powstała funkcja ŚREDNIA. Aby utworzyć jedną strukturę, która działałaby dla dowolnej liczby kryteriów, ŚREDNIA.IFS wymaga, aby zakres sumy był wymieniony jako pierwszy. W naszym przykładzie oznacza to, że formuła musi być
=ŚREDNIA.JEŻELI(B2:B7;A2:A7;D1)
UWAGA: Te dwie formuły dają ten sam wynik i mogą wyglądać podobnie, więc zwróć szczególną uwagę na to, która funkcja jest używana, aby upewnić się, że wszystkie argumenty zostały wymienione we właściwej kolejności.
Praca z datami, wiele kryteriów
Podczas pracy z datami w arkuszu kalkulacyjnym, chociaż można wprowadzić datę bezpośrednio do formuły, najlepiej jest umieścić datę w komórce, aby można było odwoływać się do komórki w formule. Na przykład pomaga to komputerowi wiedzieć, że chcesz użyć daty 27.05.2020, a nie liczby 5 podzielonej przez 27 podzielonej przez 2022.
Przyjrzyjmy się kolejnej tabeli, w której co dwa tygodnie rejestrujemy liczbę odwiedzających witrynę.
Możemy określić punkt początkowy i końcowy zakresu, na który chcemy spojrzeć w D2 i E2. Nasz wzór, aby znaleźć średnią liczbę odwiedzających w tym zakresie, mógłby wyglądać następująco:
=ŚREDNIA.JEŻELI(B2:B7;A2:A7;">="&D2;A2:A7;"<="&E2)
Zwróć uwagę, jak udało nam się połączyć porównania „=” z odwołaniami do komórek, aby utworzyć kryteria. Ponadto, mimo że oba kryteria były stosowane do tego samego zakresu komórek (A2:A7), zakres należy wpisać dwukrotnie, raz na każde kryterium.
Wiele kolumn
Korzystając z wielu kryteriów, możesz zastosować je do tego samego zakresu, co w poprzednim przykładzie, lub możesz zastosować je do różnych zakresów. Połączmy nasze przykładowe dane w tej tabeli:
Skonfigurowaliśmy kilka komórek, aby użytkownik mógł wprowadzić to, czego chce szukać w komórkach od E2 do G2. Potrzebujemy zatem formuły, która zsumuje całkowitą liczbę jabłek zebranych w lutym. Nasza formuła wygląda tak:
=ŚREDNIA.JEŻELI(C2:C7;B2:B7;">="&F2;B2:B7;"<="&G2, A2:A7;E2)
ŚREDNIA JEŻELI z logiką typu OR
Do tego momentu wszystkie przykłady, których używaliśmy, były porównaniem opartym na AND, w którym szukamy wierszy spełniających wszystkie nasze kryteria. Teraz rozważymy przypadek, w którym chcesz poszukać możliwości wiersza spełniającego to lub inne kryterium.
Spójrzmy na tę listę sprzedaży:
Chcielibyśmy zsumować średnią sprzedaż dla Adama i Boba. Najpierw krótka dyskusja na temat obliczania średnich. Jeśli masz nieparzystą liczbę rzeczy, na przykład 3 wpisy dla Adama i 2 dla Boba, nie możesz po prostu wziąć średniej sprzedaży każdej osoby. Nazywa się to braniem średniej ze średnich, a ostatecznie nadajesz niesprawiedliwą wagę elementowi, który ma niewiele wpisów. Jeśli tak jest w przypadku Twoich danych, musisz obliczyć średnią w „ręczny” sposób: weź sumę wszystkich Twoich produktów podzieloną przez ich liczbę. Aby dowiedzieć się, jak to zrobić, możesz zapoznać się z artykułami tutaj:
Teraz, jeśli liczba wpisów jest taka sama, jak w naszej tabeli, masz kilka opcji, które możesz zrobić. Najprościej jest dodać dwie ŚREDNIEJEŻELI razem, tak jak tak, a następnie podzielić przez 2 (liczba pozycji na naszej liście)
=(ŚREDNIA.JEŻELI(B2:B7;A2:A7;"Adam")+ŚREDNIA.JEŻELI(B2:B7;A2:A7;"Bob"))/2
Tutaj komputer obliczył nasze indywidualne wyniki, a następnie dodaliśmy je do siebie.
Nasza następna opcja jest dobra, gdy masz więcej zakresów kryteriów, tak że nie chcesz wielokrotnie przepisywać całej formuły. W poprzedniej formule ręcznie powiedzieliśmy komputerowi, aby dodał razem dwie różne wartości AVERAGEIFS. Możesz to jednak również zrobić, pisząc swoje kryteria w tablicy, na przykład:
=ŚREDNIA(ŚREDNIA.JEŻELI(B2:B7;A2:A7;{"Adam";"Bob"}))
Zobacz, jak zbudowana jest tablica w nawiasach klamrowych. Kiedy komputer oceni tę formułę, będzie wiedział, że chcemy obliczyć funkcję ŚREDNIA.JEŻELI dla każdego elementu w naszej tablicy, tworząc w ten sposób tablicę liczb. Zewnętrzna funkcja ŚREDNIA weźmie następnie tę tablicę liczb i zamieni ją w pojedynczą liczbę. Przechodząc przez ocenę formuły, wyglądałoby to tak:
=ŚREDNIA(ŚREDNIA.JEŻELI(B2:B7;A2:A7;{"Adam";"Bob"})) =ŚREDNIA(13701; 21735) =17718
Uzyskujemy ten sam wynik, ale byliśmy w stanie napisać formułę nieco bardziej zwięźle.
Radzenie sobie z pustymi miejscami
Czasami Twój zestaw danych będzie zawierał puste komórki, które musisz znaleźć lub uniknąć. Ustalenie kryteriów dla nich może być trochę trudne, więc spójrzmy na inny przykład.
Zauważ, że komórka A3 jest naprawdę pusta, podczas gdy komórka A5 ma formułę zwracającą ciąg znaków o zerowej długości „”. Jeśli chcemy znaleźć łączną średnią naprawdę puste komórki, użyjemy kryterium „=", a nasza formuła będzie wyglądać tak:
=ŚREDNIA.JEŻELI(B2:B7;A2:A7;"=")
Z drugiej strony, jeśli chcemy uzyskać średnią dla wszystkich komórek, które wizualnie wyglądają na puste, zmienimy kryteria na „”, a formuła wygląda tak
=ŚREDNIA.JEŻELI(B2:B7;A2:A7;"")
Odwróćmy to: co jeśli chcesz znaleźć średnią niepustych komórek? Niestety obecny projekt nie pozwala uniknąć ciągu o zerowej długości. Możesz użyć kryterium „”, ale jak widać w przykładzie, nadal zawiera ono wartość z wiersza 5.
=ŚREDNIA.JEŻELI(B2:B7;A2:A7;"")
Jeśli nie chcesz zliczać komórek zawierających ciągi o zerowej długości, rozważ użycie funkcji LEN wewnątrz SUMPRODUCT
ŚREDNIA JEŻELI w Arkuszach Google
Funkcja AVERAGEIF działa dokładnie tak samo w Arkuszach Google, jak w Excelu:
