Ten samouczek pokazuje, jak używać Excel MAXiFunkcja FS i MINIFcje w programie Excel, aby zliczyć dane spełniające określone kryteria.
MAXIFS Przegląd funkcji
Możesz użyć funkcji MAXIFS w programie Excel do zliczania komórek zawierających określoną wartość, zliczania komórek większych lub równych wartości itp.
(Zwróć uwagę, jak pojawiają się dane wejściowe formuły)
MAXIFS Składnia funkcji i argumenty:
=MAXIFS (maks.zakres, zakres1, kryteria1, [zakres2], [kryteria2],… )
maksymalny_zakres - Zakres wartości użytych do określenia maksimum.
zakres1 - Pierwszy zakres do oceny.
kryteria1 - Kryteria do zastosowania w zakresie1.
zasięg2 - [opcjonalne] Drugi zakres do oceny.
zasięg2 - [opcjonalne] Drugi zakres do oceny.
Jakie są funkcje MAXIFS i MINIFS?
Podczas gdy Microsoft był w stanie dać nam wcześnie sposób na znalezienie sumy lub liczby komórek w oparciu o kryteria, MAXIFS i MINIFS pojawiły się później w życiu arkuszy kalkulacyjnych. Na szczęście mamy je teraz i mają bardzo podobną strukturę i zastosowanie. Mówiąc najprościej, te dwie funkcje mogą skanować zakres komórek, sprawdzając określone kryterium, a następnie podając maksimum lub wartości z zakresu odpowiadającego tym wartościom. Ponieważ zostały utworzone po dużej aktualizacji z 2007 roku, nie ma o co martwić się starszym „MAXIF/MINIF”.
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ę tabelę:
Jeśli chcemy dowiedzieć się, jaka była maksymalna liczba zebranych bananów, możemy użyć funkcji MAXIFS. Jako pierwszy argument zawsze podajemy zakres liczb, z którego chcemy zwrócić wynik. Następnie wymienimy zakresy kryteriów i odpowiadające im kryteria. W naszym przykładzie możemy umieścić tę formułę w D2:
=MAKS.WARUNKI(B2:B7;A2:A7;D1)
Podobnie, gdybyśmy chcieli znaleźć najmniejszą/minimalną liczbę zebranych bananów, możemy po prostu zmienić nazwę funkcji, pozostawiając resztę bez zmian.
=MIN.WARUNKI(B2:B7;A2:A7;D1)
W dalszej części artykułu będziemy przedstawiać przykłady przy użyciu MAXIFS lub MINIFS, zamiast wymieniać oba za każdym razem. Pamiętaj tylko, że możesz łatwo przełączać się między nimi w zależności od tego, jaki wynik chcesz osiągnąć.
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 na znalezienie jak największej liczby odwiedzających w tym zakresie mógłby wyglądać następująco:
=MAKS.WARUNKI(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 najmniejszą liczbę jabłek zebranych w lutym. Nasza formuła wygląda tak:
=MINIFY(C2:C7; B2:B7; ">="&F2, B2:B7; "<="&G2, A2:A7; E2)
MAXIFS/MINIFS 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 znaleźć maksymalną sprzedaż zarówno dla Adama, jak i Boba. Najprościej jest wziąć dwa MAXIFS, a następnie wziąć MAX obu tych funkcji.
=MAKS(MAKS.WARUNKI(B2:B7;A2:A7;"Adam");MAKS.WARUNKI(B2:B7;A2:A7;"Robert"))
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 poprzednim wzorze ręcznie nakazaliśmy komputerowi obliczenie dwóch różnych wartości MAXIFS. Możesz to jednak również zrobić, pisząc swoje kryteria w tablicy, na przykład:
=MAKS(MAKS.(B2:B7;A2:A7; {"Adam";"Robert"}))
Zobacz, jak zbudowana jest tablica w nawiasach klamrowych. Kiedy komputer oceni tę formułę, będzie wiedział, że chcemy obliczyć funkcję MAXIFS dla każdego elementu w naszej tablicy, tworząc w ten sposób tablicę liczb. Zewnętrzna funkcja MAX weźmie następnie tę tablicę liczb i zamieni ją w pojedynczą liczbę. Przechodząc przez ocenę formuły, wyglądałoby to tak:
=MAKS(MAKS.(B2:B7;A2:A7;{"Adam";"Bob"})) =MAKS(14548; 24956) =24956
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:
=MAKS.WARUNKI(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
= MAKSIFY (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.
= MAKSIFY (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
MAXIFS i MINIIFS w Arkuszach Google
Funkcja MAXIFS i MINIIFS działa dokładnie tak samo w Arkuszach Google, jak w Excelu:
