Podsumowanie
Aby wyświetlić listę nazw uporządkowanych według wartości liczbowej, możesz użyć zestawu formuł opartych na LARGE, INDEX, MATCH, z pomocą funkcji TEXT. W pokazanym przykładzie formuła w G5 to:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
A wzór w G10 to:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
gdzie data klienta (B5: B17) (C5: C17) i kwota (C5: C17) to nazwane zakresy.
Uwaga: są to formuły tablicowe i należy je wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Wyjaśnienie
Ten przykład jest podzielony na dwie części dla przejrzystości: (1) formuła określająca 3 najwyższe kwoty w każdym miesiącu oraz (2) formuła umożliwiająca pobranie nazwy klienta dla każdej z 3 najwyższych kwot miesięcznych.
Uwaga: w danych źródłowych nie ma rzeczywistej pozycji. Zamiast tego używamy funkcji LARGE do bezpośredniej pracy z kwotami. Innym podejściem byłoby dodanie rangi do danych źródłowych za pomocą funkcji RANK i wykorzystanie wartości rangi do pobierania nazw klientów.
Część 1: Pobierz 3 najważniejsze kwoty każdego miesiąca
Aby pobrać 3 najlepsze kwoty z każdego tygodnia, wzór w G5 jest następujący:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Pracując od wewnątrz, najpierw używamy funkcji TEKST, aby uzyskać nazwy miesięcy dla każdej daty z nazwanego zakresu dat :
TEXT(date,"mmmm") // get month names
Niestandardowy format liczbowy „mmmm” zwróci ciąg, taki jak „kwiecień”, „maj”, „czerwiec” dla każdej nazwy z podanego zakresu dat . Wynikiem jest tablica nazw miesięcy, takich jak ta:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Funkcja TEKST dostarcza tę tablicę do funkcji JEŻELI, która jest skonfigurowana do filtrowania dat w danym miesiącu, porównując nazwę miesiąca z wartością w G4 (odwołanie mieszane, dzięki czemu formuła może być kopiowana w dół iw poprzek):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Tylko kwoty z kwietnia przeżywają i przechodzą przez IF; wszystkie inne wartości są FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Na koniec funkcja LARGE używa wartości w F5 (również odwołanie mieszane) do zwrócenia „n-tej” największej wartości, która pozostaje. W komórce G5 funkcja LARGE zwraca 18 500, czyli „pierwszą” największą wartość. Gdy formuła jest kopiowana w dół iw poprzek tabeli, funkcja DUŻA zwraca 3 pierwsze kwoty w każdym z trzech miesięcy.
Teraz, gdy znamy 3 najważniejsze wartości w każdym miesiącu, możemy użyć tych informacji jako „klucza” do pobrania nazwy klienta dla każdej z nich.
Część 2: pobieranie nazw klientów
Uwaga: to jest przykład użycia indeksu i dopasowania z wieloma kryteriami. Jeśli ta koncepcja jest dla Ciebie nowa, oto podstawowy przykład.
Aby pobrać nazwę związaną z trzema najwyższymi wartościami w G5: I7, używamy INDEKSU i PODAJNIKA:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, z wyjątkiem Excel 365.
Działając od wewnątrz, funkcja MATCH jest skonfigurowana do korzystania z logiki boolowskiej w następujący sposób:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Wartość wyszukiwania to 1, a tablica wyszukiwania jest tworzona za pomocą tego wyrażenia:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Wyrażenie, które tworzy tablicę odnośników, używa logiki boolowskiej do „odfiltrowania” kwot, które (1) nie są w kwietniu i (2) nie są wartością z G5 (18 500). Wynikiem jest tablica jedynek i zer w następujący sposób:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Z wartością wyszukiwania równą 1 i zero dla typu dopasowania (aby wymusić dokładne dopasowanie) PODAJ.POZYCJĘ zwraca 3 bezpośrednio do funkcji INDEKS:
=INDEX(client,3) // returns "Janus"
INDEKS zwraca trzecią wartość w kliencie o nazwie „Janus”.
Ponieważ formuła jest kopiowana w dół i w poprzek tabeli, zwraca 3 najlepszych klientów w każdym z trzech miesięcy.
