Pobierz przykładowy skoroszyt
Ten samouczek pokaże, jak znaleźć największą wartość komórki, która spełnia określone warunki w Excelu i Arkuszach Google.
Maks. jeśli funkcja tablicy
Funkcja MAX identyfikuje największą wartość w szeregu liczb.
Możemy użyć funkcji MAX w połączeniu z funkcją IF, aby zidentyfikować największą wartość, która spełnia określony warunek.
Użytkownikom Arkuszy Google i programu Excel 2022 lub nowszego zaleca się korzystanie z prostszej funkcji MAXIFS. Wyjaśniono to w dalszej części.
Ten przykład użyje funkcji MAX i IF w formule tablicowej do zidentyfikowania największego Rozmiar zamówienia dla każdego Nazwa sklepu
| 1 | {=MAKS(JEŻELI(B3:B8="A",D3:D8))} |
W usłudze Office 365 i wersjach programu Excel po 2022 r. możesz po prostu wprowadzić powyższą formułę w normalny sposób (naciskając klawisz ENTER).
Jednak w przypadku programu Excel 2022 i starszych musisz wprowadzić formułę, naciskając klawisze CTRL + SHIFT + ENTER. Po wykonaniu tej czynności zauważysz, że wokół formuły pojawiają się nawiasy klamrowe.
Aby pokazać, jak działa ta formuła, podzielmy ją na kroki.
To jest nasza ostateczna formuła (pokazana bez automatycznie dodanych nawiasów formuły tablicowej):
| 1 | =MAKS(JEŻELI(B3:B8="A";D3:D8)) |
Najpierw wartości zakresu komórek są dodawane do formuły jako tablice:
| 1 | =MAX(JEŻELI({"A"; "B"; "A"; "B"; "A"; "B"}="A",{500; 400; 300; 700; 600; 200})) |
Dalej Nazwa sklepu = „A” warunek tworzy tablicę wartości TRUE/FALSE:
| 1 | =MAX(JEŻELI({PRAWDA; FAŁSZ; PRAWDA; FAŁSZ; PRAWDA; FAŁSZ};{500; 400; 300; 700; 600; 200})) |
Następnie funkcja IF zmienia wszystkie wartości TRUE na odpowiednie Rozmiar zamówienia:
| 1 | =MAX({500; FAŁSZ; 300; FAŁSZ; 600; FAŁSZ}) |
Funkcja MAX identyfikuje największą liczbę w tablicy, ignorując wszelkie wartości FALSE, aby wyświetlić największą Rozmiar zamówienia dla Nazwa sklepu = „A”:
| 1 | =600 |
Maks. jeśli - wiele kryteriów
Możemy również zidentyfikować największą wartość na podstawie wielu kryteriów za pomocą logiki Boole'a.
Ten przykład pokaże największy Rozmiar zamówienia dla każdego Nazwa sklepu, ale dla Terminy zamówień przed 30.04.2021 za pomocą funkcji MAX, IF i DATE:
| 1 | {=MAKS(JEŻELI((B3:B8="A")*(C3:C8 |
Zauważ, że tutaj mnożymy dwa zestawy kryteriów PRAWDA/FAŁSZ:
| 1 | (B3:B8="A") * (C3:C8 |
Jeśli oba kryteria mają wartość PRAWDA, całkowity stan zostanie obliczony jako PRAWDA, ale jeśli jedno (lub więcej) kryteriów jest FAŁSZ, zostanie obliczony jako FAŁSZ.
Stosując tę metodologię, do tej formuły można dodać wiele różnych kryteriów.
Max If - wiele kryteriów z odniesieniami do komórek
Zwykle nie jest dobrą praktyką zakodowanie wartości na stałe w formułach. Zamiast tego bardziej elastyczne jest używanie oddzielnych komórek do definiowania kryteriów.
Aby dopasować Nazwa sklepu do wartości pokazanej w kolumnie F, możemy zaktualizować formułę tak, aby była:
| 1 | {=MAKS(JEŻELI((B3:B8=F3)*(C3:C8 |
Blokowanie odwołań do komórek
Aby ułatwić czytanie naszych formuł, pokazaliśmy formuły bez zablokowanych odwołań do komórek:
| 1 | {=MAKS(JEŻELI((B3:B8=F3)*(C3:C8 |
Ale te formuły nie będą działać poprawnie po skopiowaniu i wklejeniu w innym miejscu pliku. Zamiast tego powinieneś używać zablokowanych odwołań do komórek w następujący sposób:
| 1 | {=MAKS(JEŻELI((($B3:$B8$=F3))*($C$3:$C$8 |
Przeczytaj nasz artykuł na temat blokowania odwołań do komórek, aby dowiedzieć się więcej.
Funkcja MAXIFS
Użytkownicy Arkuszy Google i programu Excel 2022 lub nowszego mogą używać pojedynczej funkcji MAXIFS do replikowania zachowania funkcji MAX i IF przedstawionych we wcześniejszych przykładach.
W następnym przykładzie użyto funkcji MAXIFS i DATE, aby pokazać największy Rozmiar zamówienia dla każdego Nazwa sklepu dla Terminy zamówień przed 30.04.2021:
| 1 | =MAKS.WARUNKI(D3:D8;B3:B8;"A";C3:C8;"<"&DATA(2021,4,30)) |
Funkcja MAXIFS nie wymaga od użytkownika naciskania klawiszy CTRL + SHIFT + ENTER podczas wprowadzania formuły.
Max If (maksymalna wartość z warunkiem) w Arkuszach Google
Powyższe przykłady działają dokładnie tak samo w Arkuszach Google, jak w Excelu, ale ponieważ funkcja MAXIFS jest dostępna, zaleca się użycie tej pojedynczej funkcji zamiast łączenia funkcji MAX i IF.
Jeśli potrzebne są przykłady z użyciem funkcji MAX i IF, Arkusze Google wymagają wprowadzenia ich jako formuł tablicowych. Zamiast pokazywać formułę z nawiasami klamrowymi tablicy Excel { }, naciśnięcie klawiszy CTRL + SHIFT + ENTER automatycznie dodaje funkcję ARRAYFORMULA wokół formuły:
| 1 | =FORMULASZYK(MAKS(JEŻELI((B3:B8="A")*(C3:C8 |
