Czasami zamiast znajdować największą lub maksymalną liczbę dla wszystkich swoich danych; musisz znaleźć największą liczbę w podzbiorze - na przykład największą liczbę dodatnią lub ujemną.
Jeśli ilość danych jest niewielka, zadanie można łatwo wykonać, wybierając ręcznie prawidłowy zakres funkcji MAX.
W innych okolicznościach, takich jak duża nieposortowana próbka danych, prawidłowe dobranie zakresu może okazać się trudne, jeśli nie niemożliwe.
Łącząc funkcję IF z MAX w formule tablicowej, warunki - takie jak tylko liczby dodatnie lub ujemne - można łatwo ustawić tak, że tylko dane pasujące do tych parametrów są testowane za pomocą formuły.
MAX IF Array Formula Breakdown
Formuła użyta w tym samouczku do znalezienia największej liczby pozytywnej to:
= MAX (JEŻELI (A1: B5> 0, A1: B5))
Uwaga: Argument value_if_false funkcji IF, który jest opcjonalny, jest pomijany w celu skrócenia formuły. W przypadku, gdy dane w wybranym zakresie nie spełniają ustalonego kryterium - liczby większe od zera - formuła zwróci zero ( 0 )
Zadaniem każdej części formuły jest:
- Funkcja IF filtruje dane tak, że tylko te liczby, które spełniają wybrane kryterium, są przekazywane do funkcji MAX
- funkcja MAX znajduje najwyższą wartość dla filtrowanych danych
- Formuła tablicowa - wskazana przez nawiasy klamrowe { } otaczająca formułę - pozwala logicznemu argumentowi testu funkcji IF przeszukać cały zakres danych dla dopasowania - np. liczby większe od zera - zamiast tylko jednej komórki danych
Formuły CSE
Formuły tablicowe tworzone są przez naciśnięcie klawisza Ctrl, Przesunięcie, i Wchodzić klawisze na klawiaturze w tym samym czasie po wpisaniu formuły.
W rezultacie cała formuła - w tym znak równości - jest otoczona nawiasami klamrowymi. Przykładem może być:
{= MAX (JEŻELI (A1: B5> 0, A1: B5))}
Ze względu na klawisze naciśnięte, aby utworzyć formułę tablicową, czasami są określane jako CSE formuły.
Przykład formuły MAX IF Array w Excelu
Jak widać na powyższym obrazku, ten samouczek wykorzystuje formułę tablicową MAX IF do znajdowania największych wartości dodatnich i ujemnych w zakresie liczb.
W poniższych krokach najpierw utwórz formułę, aby znaleźć największą liczbę dodatnią, a następnie kroki niezbędne do znalezienia największej liczby ujemnej.
Wprowadzanie danych samouczka
- Wprowadź liczby widoczne na obrazku powyżej w komórkach A1 do B5 arkusza roboczego
- W komórkach A6 i A7 wpisz etykiety Maks. Pozytywna i Max Negatywny
Wprowadzanie formuły zagnieżdżonej MAX IF
Ponieważ tworzymy zarówno formułę zagnieżdżoną, jak i formułę tablicową, będziemy musieli wpisać całą formułę w pojedynczą komórkę arkusza roboczego.
Po wprowadzeniu formuły NIE RÓB wciśnij Wchodzić naciśnij klawisz na klawiaturze lub kliknij inną komórkę za pomocą myszy, ponieważ musimy zmienić formułę w formułę tablicową.
- Kliknij komórkę B6 - miejsce, w którym wyświetlone zostaną wyniki pierwszej formuły
- Wpisz następujące polecenie:
= MAX (JEŻELI (A1: B5> 0, A1: B5))
Tworzenie formuły macierzy
- Naciśnij i przytrzymaj przycisk Ctrl i Przesunięcie klawisze na klawiaturze
- wciśnij Wchodzić klawisz na klawiaturze, aby utworzyć formułę tablicową
- Odpowiedź 45 powinien pojawić się w komórce B6, ponieważ jest to największa liczba dodatnia na liście
- Jeśli klikniesz komórkę B6, pełna formuła macierzy
{= MAX (JEŻELI (A1: B5> 0, A1: B5))}
można zobaczyć na pasku formuły nad arkuszem
Znajdowanie największej liczby ujemnej
Wzór do znalezienia największej liczby ujemnej różni się od pierwszej formuły tylko w operatorze porównania stosowanym w argumencie testu logicznego funkcji IF.
Ponieważ celem jest znalezienie największej liczby ujemnej, druga formuła wykorzystuje mniej niż operator ( < ), a nie większy niż operator ( > ), aby przetestować tylko dane, które są mniejsze od zera.
- Kliknij komórkę B7
- Wpisz następujące polecenie:
= MAX (JEŻELI (A1: B5 <0, A1: B5))
- Wykonaj powyższe kroki, aby utworzyć formułę tablicową
- Odpowiedź -8 powinien pojawić się w komórce B7, ponieważ jest to największa liczba ujemna na liście
Uzyskanie #VALUE! za odpowiedź
Jeśli komórki B6 i B7 wyświetlają wartość #VALUE! wartość błędu, a nie odpowiedzi wskazane powyżej, prawdopodobnie dlatego, że formuła tablicowa nie została poprawnie utworzona.
Aby rozwiązać ten problem, kliknij formułę na pasku formuły i naciśnij klawisz Ctrl, Przesunięcie i Wchodzić ponownie klawisze na klawiaturze.
