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:
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:
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:
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:
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:
A potem po prostu naciśnij Wchodzę klucz, wszystkie dane z pierwszej 10 listy są wyświetlane jednocześnie, patrz zrzut ekranu:
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:
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:
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 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!
