Note: The other languages of the website are Google-translated. Back to English

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))
Uwagi: 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)))
Uwagi: 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:

Wskazówki:

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:

Wskazówki:

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

Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%

  • Ponowne użycie: Szybko włóż złożone wzory, wykresy i wszystko, czego używałeś wcześniej; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
  • Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
  • Scal komórki / wiersze / kolumny bez utraty danych; Podziel zawartość komórek; Połącz zduplikowane wiersze / kolumny... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
  • Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
  • Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
  • Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
  • Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
  • Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
  • Ponad 300 zaawansowanych funkcji. Obsługuje Office / Excel 2007-2021 i 365. Obsługuje wszystkie języki. Łatwe wdrażanie w przedsiębiorstwie lub organizacji. Pełne funkcje 30-dniowa bezpłatna wersja próbna. 60-dniowa gwarancja zwrotu pieniędzy.
karta kte 201905

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!
officetab dół
Sortuj komentarze według
Komentarze (0)
Brak ocen. Oceń jako pierwszy!
Nie ma tu jeszcze żadnych komentarzy
Zostaw swój komentarz
Publikowanie jako gość
×
Oceń ten post:
0   Postacie
Sugerowane lokalizacje

Bądż na bieżąco

Prawa autorskie © 2009 - www.extendoffice.com. | Wszelkie prawa zastrzeżone. Zasilany przez ExtendOffice, | Mapa strony
Microsoft i logo Office są znakami towarowymi lub zastrzeżonymi znakami towarowymi Microsoft Corporation w Stanach Zjednoczonych i / lub innych krajach.
Chronione przez Sectigo SSL