W tym artykule dowiemy się, jak znaleźć n-ty najmniejszy z kryteriami i n-ty największy z kryteriami z podanej tabeli w programie Excel.
Scenariusz:
W prostych słowach, podczas pracy z liczbami w liczbach danych, czasami podaje się warunek, tj. kiedy musimy wyszukać 5 najwyższą podaną wartość . Możesz łatwo rozwiązać ten problem, korzystając z funkcji programu Excel, jak wyjaśniono poniżej.
N-ty największy z kryteriami
Jak rozwiązać problem?
W tym artykule będziemy musieli użyć funkcji LARGE. Teraz zrobimy formułę z tych funkcji. Tutaj otrzymujemy tabelę i musimy znaleźć n-tą największą wartość w zakresie przy podanych kryteriach.
Wzór ogólny:
{ = DUŻE ( JEŻELI ( c_zakres = "wartość" , zakres ) , n ) }
c_zakres : zakres kryteriów
wartość : kryteria pasują do wartości
zasięg : tablica wyników
n : n-ty największy
Notatka : Nie umieszczaj nawiasów klamrowych ręcznie. To jest formuła tablicowa, należy użyć Ctrl + Shift + Enter zamiast po prostu Enter, gdzie zwraca #NUM! błąd.
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj mamy szczegóły zamówienia z datą zamówienia, regionem i ceną zamówienia.
Musimy znaleźć piątą co do wielkości cenę zamówienia z regionu wschodniego. Tutaj zakres jest podany jako użycie nazwanego narzędzia Excel do zakresu.
region (C3:C16)
cena (D3:D16)
Najpierw musimy znaleźć piątą największą wartość za pomocą funkcji LARGE, a następnie wykonać operację sumowania na tych 5 wartościach. Teraz użyjemy następującego wzoru, aby uzyskać sumę
Użyj formuły:
{ = DUŻE ( JEŻELI ( region = G5 , cena ) , F5 )}
Wyjaśnienie:
- Funkcja JEŻELI sprawdza kryteria pasujące do wszystkich wartości w regionie o podanej wartości Wschód w komórce G5 i zwraca odpowiadające wartości TRUE z przedziału cenowego.
= DUŻE ( { 58,41; 303,63 ; FAŁSZ ; 153,34 ; 82,84 ; FAŁSZ ; 520,01 ; FAŁSZ ; 177 ; FAŁSZ ; FAŁSZ ; 57,97 ; 97,72 ; FAŁSZ } ) , F5) )
- Funkcja LARGE pobiera tablicę, która ma cały zakres cen, jak pokazano powyżej, i zwraca piątą co do wielkości wartość z tablicy, jak pokazano na poniższym zrzucie.
Możesz wyświetlić formułę w polu formuły, jak pokazano powyżej. Użyj Ctrl + Shift + Enter, aby uzyskać wynik.
Jak widać, formuła tablicowa zwraca piątą co do wielkości cenę 97,72 USD z regionem wschodnim.
Możesz również podać tablicę jako tablicę, kryteria w cudzysłowie i wartość n bezpośrednio do funkcji zamiast używać odwołania do komórki lub nazwanego zakresu.
Użyj formuły:
{ = DUŻE ( JEŻELI ( C3:C16 = "Zachód" , D3:D16 ) , 3 )}
Posługiwać się Ctrl + Shift + Enter
Możesz zobaczyć, że formuła działa dobrze, gdy znajduje się n-ta największa wartość mająca kryteria w tabeli.
N-ty najniższy z kryteriami
Jak rozwiązać problem?
W tym artykule będziemy musieli użyć funkcji MAŁY. Teraz zrobimy formułę z tych funkcji. Tutaj otrzymujemy tabelę i musimy znaleźć n-tą najmniejszą wartość w zakresie przy podanych kryteriach.
Wzór ogólny:
{ = MAŁY ( JEŻELI ( c_zakres = "wartość" , zakres ) , n ) }
c_zakres : zakres kryteriów
wartość : kryteria pasują do wartości
zasięg : tablica wyników
n : n-ty największy
Notatka : Nie umieszczaj nawiasów klamrowych ręcznie. To jest formuła tablicowa, należy użyć Ctrl + Shift + Enter zamiast po prostu Enter, gdzie zwraca #NUM! błąd.
Przykład:
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie.
Tutaj mamy szczegóły zamówienia z datą zamówienia, regionem i ceną zamówienia.
Musimy znaleźć n-tą najmniejszą cenę zamówienia z regionu wschodniego. Tutaj zakres jest podany jako użycie nazwanego narzędzia Excel do zakresu.
region (C3:C16)
cena (D3:D16)
Najpierw musimy znaleźć piątą najmniejszą lub najniższą wartość za pomocą funkcji SMALL, a następnie wykonać operację wyszukiwania nad wartościami. Teraz użyjemy następującego wzoru, aby uzyskać sumę
Użyj formuły:
{ = MAŁY ( JEŻELI ( region = G5 , cena ) , F5 )}
Wyjaśnienie:
- Funkcja JEŻELI sprawdza kryteria pasujące do wszystkich wartości w regionie z podaną wartością Wschód w komórce G5 i zwraca odpowiadające wartości TRUE z przedziału cenowego.
= DUŻE ( { 58,41; 303,63 ; FAŁSZ ; 153,34 ; 82,84 ; FAŁSZ ; 520,01 ; FAŁSZ ; 177 ; FAŁSZ ; FAŁSZ ; 57,97 ; 97,72 ; FAŁSZ } ) , F5) )
- Funkcja SMALL pobiera tablicę, która ma cały zakres cen, jak pokazano powyżej, i zwraca piątą co do wielkości wartość z tablicy, jak pokazano na poniższym zrzucie.
Możesz wyświetlić formułę w polu formuły, jak pokazano powyżej. Posługiwać się Ctrl + Shift + Enter aby uzyskać wynik.
Jak widać, formuła tablicowa zwraca piątą najmniejszą cenę 153,34 USD z regionem wschodnim.
Możesz również podać tablicę jako tablicę, kryteria w cudzysłowie i wartość n bezpośrednio do funkcji zamiast używać odwołania do komórki lub nazwanego zakresu.
Użyj formuły:
{ = MAŁY ( JEŻELI ( C3:C16 = "zachód" , D3:D16 ) , 3 )}
Posługiwać się Ctrl + Shift + Enter
Możesz zobaczyć, że formuła działa dobrze, gdy znajdziesz n-tą największą wartość mającą kryteria w tabeli.
Oto kilka uwag obserwacyjnych pokazanych poniżej.
Uwagi:
- Formuła działa tylko z liczbami.
- Nie umieszczaj nawiasów klamrowych ręcznie. To jest formuła tablicowa, należy użyć Ctrl + Shift + Enter zamiast po prostu Enter, gdzie zwraca #NUM! błąd.
- Wartość nie może być większa niż liczba wartości kryteriów lub zwraca #LICZBA! Błąd.
- JEŻELI kryteria nie pasują do formuły zwraca błąd.
- Argument tablica musi mieć tę samą długość, w przeciwnym razie funkcja.
Mam nadzieję, że ten artykuł o tym, jak znaleźć n-ty największy z kryteriami i n-ty najmniejszy z kryteriami w programie Excel, jest objaśniający. Zapoznaj się z dodatkowymi artykułami na temat formuł wyszukiwania w programie Excel tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
SUMA, jeśli data jest między : Zwraca SUMA wartości między podanymi datami lub okresem w programie Excel.
Suma, jeśli data jest większa niż podana: Zwraca SUMA wartości po podanej dacie lub okresie w programie Excel.
2 sposoby sumowania według miesiąca w Excelu: Zwraca SUMA wartości w danym miesiącu w programie Excel.
Jak sumować wiele kolumn z warunkiem: Zwraca SUMA wartości w wielu kolumnach mających warunek w programie Excel
Jak używać symboli wieloznacznych w programie Excel : Policz komórki pasujące do fraz za pomocą symboli wieloznacznych w programie Excel
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.