Przejdź do głównej zawartości

Jak utworzyć dynamiczną listę top 10 lub n w programie Excel?

Top lista służy do uszeregowania firm lub osób na podstawie wartości. Przypuśćmy, że masz listę ocen uczniów z klasy, teraz chcesz utworzyć dynamiczną listę 10 najlepszych uczniów, jak pokazano na zrzucie ekranu. W tym artykule przedstawię kilka formuł tworzenia listy 10 lub n w arkuszu programu Excel.

Utwórz dynamiczną listę 10 najlepszych w programie Excel

W programie Excel 2019 i wcześniejszych wersjach, aby wyodrębnić listę 10 najlepszych lub listę 10 najlepszych z kryteriami, należy zastosować następujące formuły:

Formuły do ​​tworzenia dynamicznej listy top 10

1. Najpierw należy wyodrębnić 10 najwyższych wartości z zakresu danych, użyj poniższej formuły do ​​pustej komórki - G2 i przeciągnij uchwyt wypełniania w dół, aby uzyskać 10 najwyższych wartości, patrz zrzut ekranu:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
Note: W tym wzorze B2: B20 to lista danych, na której chcesz uzyskać 10 najlepszych wartości, oraz B2 to pierwsza komórka listy danych.

2. Następnie zastosuj następującą formułę do komórki - F2 i naciśnij Ctrl + Shift + Enter klucze razem, aby uzyskać pierwszy wynik, patrz zrzut ekranu:

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))
Note: W powyższym wzorze, A2: A20 to lista danych, z której chcesz pobrać nazwy 10 największych wartości, B2: B20 to lista danych zawierająca wszystkie wartości, G2 to komórka o największej wartości wyodrębnionej z kolumny B i B1 jest komórką nagłówka listy wartości.

3. Po uzyskaniu pierwszego wyniku wybierz komórkę z formułą i przeciągnij uchwyt wypełniania w dół, aby uzyskać inne nazwy naraz, zobacz zrzut ekranu:

Formuły do ​​tworzenia dynamicznej listy top 10 z kryteriami

Czasami może być konieczne uzyskanie listy 10 najlepszych na podstawie kryteriów. Na przykład, aby uzyskać 10 najlepszych nazw i wyników Klasy 1, jak pokazano na zrzucie ekranu.

1. Aby uzyskać listę 10 najlepszych, powinieneś również wyodrębnić 10 najlepszych wyników za pomocą tej formuły:

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2. A następnie naciśnij Ctrl + Shift + Enter klawisze razem, aby uzyskać pierwszy wynik, a następnie przeciągnij tę formułę w dół, aby wyświetlić inne wartości, zobacz zrzut ekranu:

3. Następnie skopiuj i wklej następującą formułę do komórki - I2 i naciśnij Ctrl + Shift + Enter klawisze jednocześnie, aby wyodrębnić pierwszy wynik, a następnie przeciągnij tę formułę, aby wypełnić ją do innych komórek, a 10 najlepszych nazw zostanie wyświetlonych, jak pokazano na poniższym zrzucie ekranu:

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

Utwórz dynamiczną listę 10 najlepszych w Office 365

Powyższe formuły mogą być dla nas trudne do zrozumienia, jeśli korzystasz z Office 365 z funkcjami INDEX, SORT i SEQUENCE, możesz tworzyć proste formuły do ​​wykonania tego zadania.

Formuła tworzenia dynamicznej listy top 10

Aby uzyskać listę 10 danych, skorzystaj z poniższego wzoru:

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

A potem po prostu naciśnij Wchodzę klucz, wszystkie dane z pierwszej 10 listy są wyświetlane jednocześnie, patrz zrzut ekranu:

Tips:

Funkcja SORTOWANIA:

=SORTUJ(tablica; [indeks_sortowania]; [kolejność_sortowania]; [według_kolumny])

  • szyk: zakres komórek, które chcesz posortować;
  • [indeks_sortowania]: Numer kolumny lub wiersza do sortowania według tablicy. Na przykład, aby posortować według drugiej kolumny zakresu danych, indeks sortowania będzie wynosił 2;
  • [sortowanie]: Liczba 1 (lub pominięta) wskazuje na sortowanie w porządku rosnącym; liczba -1, sortuj w porządku malejącym;
  • [przez_kolumn]: Kierunek sortowania. TRUE, sortuj według kolumn, FALSE lub pominięte, sortuj według wierszy.

W formule używamy funkcji SORTUJ w następujący sposób:

SORTUJ(A2:B20,2,-1): oznacza sortowanie zakresu komórek A2:A20 w drugiej kolumnie w kolejności malejącej.

SEKWENCJA funkcja:

=SEKWENCJA(wiersze; [kolumny]; [początek]; [krok])

  • wydziwianie: liczba wierszy do zwrócenia,
  • [kolumny]: liczba kolumn do zwrócenia. Jeśli zostanie pominięty, zwróci jedną kolumnę.
  • [początek]: Pierwsza liczba w sekwencji. Jeśli zostanie pominięty, rozpocznie się od 1.
  • [krok]: Przyrost między każdą liczbą. W przypadku wyłączenia każdy przyrost będzie wynosił 1.

W tej formule używamy SEQUENCE(10), aby utworzyć listę od 1 do 10.

Na koniec umieść funkcje SORT i SEQUENCE w funkcji INDEX: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}), to zwróci pierwszych 10 rekordów z zakresu danych i zwróci kolumny 1 i 2.

Formuła do tworzenia dynamicznej listy top 10 z kryteriami

Aby wyświetlić listę 10 najlepszych z kryteriami, należy osadzić funkcję FILTER w funkcji SORT w następujący sposób:

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

I po prostu naciśnij Wchodzę klucz, wszystkie dane z listy 10 najlepszych na podstawie podanych kryteriów są wyświetlane jednocześnie, patrz zrzut ekranu:

Tips:

FILTR, funkcja:

=FILTR(tablica;włącz;[jeśli_pusty])

  • szyk: Zakres komórek do filtrowania.
  • zawierać: Warunek używany do filtrowania tablicy, aby uzyskać tablicę wyników TRUE lub FALSE, dzięki czemu wartości TRUE zostaną zachowane w filtrze.
  • [jeśli_pusty]: Wartość do wyświetlenia, jeśli nie zostaną zwrócone żadne pasujące wyniki.

W tej formule: =FILTER(A2:C25,B2:B25=F2) służy do filtrowania w zakresie A2:C25, gdzie wartości z B2:B25 są równe określonej komórce F2.

Najlepsze narzędzia biurowe

🤖 Pomocnik AI Kutools: Zrewolucjonizuj analizę danych w oparciu o: Inteligentne wykonanie   |  Wygeneruj kod  |  Twórz niestandardowe formuły  |  Analizuj dane i generuj wykresy  |  Wywołaj funkcje Kutools...
Popularne funkcje: Znajdź, wyróżnij lub zidentyfikuj duplikaty   |  Usuń puste wiersze   |  Łącz kolumny lub komórki bez utraty danych   |   Okrągły bez wzoru ...
Super wyszukiwanie: Wiele kryteriów VLookup  |   Wiele wartości VLookup  |   Przeglądanie pionowe na wielu arkuszach   |   Wyszukiwanie rozmyte ....
Zaawansowana lista rozwijana: Szybko twórz listę rozwijaną   |  Zależna lista rozwijana   |  Lista rozwijana wielokrotnego wyboru ....
Menedżer kolumn: Dodaj określoną liczbę kolumn  |  Przesuń kolumny  |  Przełącz stan widoczności ukrytych kolumn  |  Porównaj zakresy i kolumny ...
Polecane funkcje: Fokus siatki   |  Widok projektu   |   Duży pasek formuły   |  Menedżer skoroszytów i arkuszy   |  Biblioteka zasobów (Automatyczny tekst)   |  Selektor dat   |  Połącz arkusze   |  Szyfruj/odszyfruj komórki   |  Wysyłaj e-maile według listy   |  Super filtr   |   Specjalny filtr (filtruj pogrubienie/kursywa/przekreślenie...) ...
15 najlepszych zestawów narzędzi:  12 Tekst Tools (Dodaj tekst, Usuń znaki, ...)   |   50 + Wykres rodzaje (Wykres Gantta, ...)   |   40+ Praktyczne Wzory (Oblicz wiek na podstawie urodzin, ...)   |   19 Wprowadzenie Tools (Wstaw kod QR, Wstaw obraz ze ścieżki, ...)   |   12 Konwersja Tools (Liczby na słowa, Przeliczanie walut, ...)   |   7 Połącz i podziel Tools (Zaawansowane wiersze łączenia, Podział komórki, ...)   |   ... i więcej

Zwiększ swoje umiejętności Excela dzięki Kutools for Excel i doświadcz wydajności jak nigdy dotąd. Kutools dla programu Excel oferuje ponad 300 zaawansowanych funkcji zwiększających produktywność i oszczędzających czas.  Kliknij tutaj, aby uzyskać funkcję, której najbardziej potrzebujesz...

Opis

Karta Office wprowadza interfejs z zakładkami do pakietu Office i znacznie ułatwia pracę

  • Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
  • Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
  • Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations