Spis treści
Jeśli potrzebujesz posortować listę, możesz skorzystać z wielu sposobów, z których najłatwiejszym są przyciski sortowania na karcie lub w menu Dane (Dane — Sortuj). Zdarzają się jednak sytuacje, w których sortowanie listy musi odbywać się automatycznie, czyli formuły. Może to być wymagane np. przy generowaniu danych do listy rozwijanej, przy obliczaniu danych do wykresów itp. Jak w locie sortować listę z formułą?
Metoda 1. Dane liczbowe
Jeśli lista zawiera tylko informacje liczbowe, sortowanie można łatwo wykonać za pomocą funkcji NAJMNIEJ (MAŁY) и LINE (WIERSZ):
Funkcjonować NAJMNIEJ (MAŁY) wyciąga z tablicy (kolumny A) n-ty najmniejszy element z rzędu. Tych. SMALL(A:A;1) to najmniejsza liczba w kolumnie, SMALL(A:A;2) to druga najmniejsza liczba i tak dalej.
Funkcjonować LINE (WIERSZ) zwraca numer wiersza dla określonej komórki, tj. ROW(A1)=1, ROW(A2)=2 itd. W tym przypadku jest używany po prostu jako generator ciągu liczb n=1,2,3… dla nasza posortowana lista. Z takim samym sukcesem udało się zrobić dodatkową kolumnę, wypełnić ją ręcznie ciągiem liczbowym 1,2,3… i odwołać się do niej zamiast funkcji WIERSZ.
Metoda 2. Lista tekstowa i regularne formuły
Jeśli lista zawiera nie liczby, ale tekst, to funkcja MAŁA przestanie działać, więc trzeba iść inną, nieco dłuższą ścieżką.
Najpierw dodajmy kolumnę usługi z formułą, w której numer seryjny każdej nazwy w przyszłej posortowanej liście zostanie obliczony za pomocą funkcji COUNTIF (LICZ.JEŻELI):
W wersji angielskiej będzie to:
=LICZ.JEŻELI(A:A;»<"&A1)+LICZ.JEŻELI($A$1:A1,"="&A1)
Pierwszy termin to funkcja służąca do zliczania liczby komórek mniejszych niż aktualna. Drugi to siatka bezpieczeństwa na wypadek, gdyby jakakolwiek nazwa pojawiła się więcej niż raz. Wtedy nie będą mieli tych samych, ale sukcesywnie rosnących liczebności.
Teraz otrzymane liczby muszą być ułożone kolejno w kolejności rosnącej. W tym celu możesz użyć funkcji NAJMNIEJ (MAŁY) od pierwszego sposobu:
Cóż, w końcu pozostaje tylko wyciągnąć nazwiska z listy po ich numerach. Aby to zrobić, możesz użyć następującej formuły:
Funkcjonować WIĘCEJ EKSPOZYCYJNE (MECZ) wyszukuje w kolumnie B żądany numer seryjny (1, 2, 3 itd.) i faktycznie zwraca numer wiersza, w którym znajduje się ten numer. Funkcjonować INDEKS (INDEKS) wyciąga z kolumny A nazwę pod tym numerem wiersza.
Metoda 3: Formuła tablicowa
Ta metoda jest w rzeczywistości tym samym algorytmem umieszczania, co w Method-2, ale zaimplementowanym przez formułę tablicową. Aby uprościć formułę, zakresowi komórek C1:C10 nadano nazwę Lista (wybierz komórki, naciśnij Ctrl + F3 i przycisk Stwórz):
W komórce E1 skopiuj naszą formułę:
=INDEKS(Lista; PODAJ.POZYCJĘ(MAŁE(LICZ.JEŻELI(Lista; „<"&Lista); WIERSZ(1:1)); LICZ.JEŻELI(Lista; "<"&Lista); 0))
Lub w wersji angielskiej:
=INDEKS(Lista; PODAJ.POZYCJĘ(MAŁY(LICZ.JEŻELI(Lista; «<"&Lista); WIERSZ(1:1)); LICZ.JEŻELI(Lista; "<"&Lista); 0))
i pchaj Ctrl + Shift + Enteraby wprowadzić go jako formułę tablicową. Następnie otrzymaną formułę można skopiować na całą długość listy.
Jeśli chcesz, aby formuła uwzględniała nie ustalony zakres, ale była w stanie dostosować się podczas dodawania nowych elementów do listy, będziesz musiał nieznacznie zmienić strategię.
Po pierwsze, zakres listy będzie musiał być ustawiony dynamicznie. Aby to zrobić, podczas tworzenia musisz określić nie stały zakres C3:C10, ale specjalną formułę, która będzie odnosić się do wszystkich dostępnych wartości, niezależnie od ich liczby. Kliknij Alt + F3 lub otwórz kartę Formuły – Menedżer nazw (Formuły — Menedżer nazw), utwórz nową nazwę i w polu Połączyć (Odniesienie) wpisz następującą formułę (zakładam, że zakres sortowanych danych zaczyna się od komórki C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=ODSUNIĘCIE(C1,0,0;SCHÖTZ(C1:C1000);1)
Po drugie, powyższa formuła tablicowa będzie musiała zostać rozciągnięta z pewnym marginesem – w oczekiwaniu na dodatkowe dane wprowadzone w przyszłości. W takim przypadku formuła tablicowa zacznie zwracać błąd #NUMBER w komórkach, które nie są jeszcze wypełnione. Aby go przechwycić, możesz użyć funkcji JEŻELI BŁĄD, który należy dodać „wokół” naszej formuły tablicowej:
=JEŻELI BŁĄD(INDEKS(Lista; PODAJ.POZYCJĘ(MAŁE(LICZ.JEŻELI(Lista; „<”&Lista); WIERSZ(1:1)); LICZ.JEŻELI(Lista; „<”&Lista); 0));»»)
=JEŻELI.BŁĄD(NDEX(lista, PODAJ.POZYCJĘ(MAŁE(LICZ.JEŻELI(lista; «<"&lista), WIERSZ(1:1)), LICZ.JEŻELI(lista; "<"&lista), 0));"")
Przechwytuje błąd #NUMBER i zamiast tego wyświetla void (puste cudzysłowy).
:
- Sortuj zakres według koloru
- Co to są formuły tablicowe i dlaczego są potrzebne
- Sortowanie SORT i tablice dynamiczne w nowym Office 365