Formuła ogólna
=COUNTIF(range,"<="&A1)
Podsumowanie
Aby dynamicznie sortować wartości tekstowe w kolejności alfabetycznej, możesz użyć formuły opartej na funkcji LICZ.JEŻELI. W przedstawionym przykładzie formuła w C5 to:
=COUNTIF(countries,"<="&B5)
gdzie „kraje” to nazwany zakres B4: B13
Wyjaśnienie
Ta formuła używa operatora „większe lub równe” z tekstem, czego wcześniej nie próbowałeś. Kiedy Excel porównuje tekst, decyduje, która wartość jest „większa” niż inne reguły oparte na schemacie numeracji ASCII.
Wewnątrz LICZ.JEŻELI argument zakresu jest podawany jako nazwany zakres „kraje” (B4: B13), a kryteria są podawane jako „mniejsze lub równe” wartości w C5. W każdym wierszu funkcja COUNTIFS zwraca liczbę wartości, które są mniejsze lub równe bieżącej wartości, co tworzy sekwencyjną listę liczb (tj. Pozycję) w kolumnie pomocniczej.
Lista posortowanych wartości
Kolumny pomocniczej można użyć do pobrania posortowanych wartości według rangi. W E5 formuła używana do pobierania wartości to:
=INDEX(countries,MATCH(ROWS($E$5:E5),helper,0))
To jest formuła INDEKS i PODAJ.POZYCJĘ, która wykorzystuje rozwijane odwołanie do generowania kolejnych liczb, które są wprowadzane do PODAJ.POZYCJĘ jako wartości wyszukiwania. MATCH oblicza, gdzie w danych znajduje się każda liczba, a INDEKS pobiera wartość z tej pozycji. Zobacz tę stronę, aby uzyskać bardziej szczegółowe wyjaśnienie.
Obsługa duplikatów
Jeśli dane zawierają zduplikowane wartości tekstowe, sekwencja numerów sortowania będzie również zawierać duplikaty, co spowoduje problemy, jeśli próbujesz pobrać wartości za pomocą funkcji INDEKS. Aby obejść ten problem, możesz użyć odmiany formuły, która zwiększa duplikaty o drugi LICZ.JEŻELI:
=COUNTIF(countries,"<"&B5)+COUNTIF($B$5:B5,B5)
Zwróć uwagę, że operator logiczny w pierwszej funkcji LICZ.JEŻELI został dostosowany, a zakres w drugiej funkcji LICZ.JEŻELI jest rozszerzającym odniesieniem.
