Formuła ogólna
=COUNTIF(data,"<="&A1)+(COUNT(data)*ISTEXT(A1))
Podsumowanie
Aby dynamicznie sortować dane z liczbami i tekstem w porządku alfabetycznym, można użyć formuły do wygenerowania rangi liczbowej w kolumnie pomocniczej, a następnie użyć funkcji INDEKS i PODAJ.POZYCJĘ, aby wyświetlić wartości na podstawie rangi. W przedstawionym przykładzie formuła w C5 to:
=COUNTIF(data,"<="&B5)+(COUNT(data)*ISTEXT(B5))
gdzie „dane” to nazwany zakres B5: B13.
Wyjaśnienie
Ta formuła najpierw generuje wartość rankingu przy użyciu wyrażenia opartego na LICZ.JEŻELI:
=COUNTIF(data,"<="&B5)
co jest wyjaśnione bardziej szczegółowo tutaj. Jeśli dane zawierają wszystkie wartości tekstowe lub wszystkie wartości liczbowe, pozycja będzie poprawna. Jeśli jednak dane zawierają zarówno tekst, jak i liczby, musimy „przesunąć” rangę wszystkich wartości tekstowych, aby uwzględnić wartości liczbowe. Odbywa się to za pomocą drugiej części wzoru tutaj:
+(COUNT(data)*ISTEXT(B7))
Tutaj używamy funkcji COUNT, aby uzyskać liczbę wartości liczbowych w danych, a następnie mnożymy wynik przez wynik logiczny ISTEXT, który sprawdza, czy wartość jest tekstem i zwraca PRAWDA lub FAŁSZ. To skutecznie anuluje wynik COUNT, gdy pracujemy z liczbą w bieżącym wierszu.
Obsługa duplikatów
Jeśli dane zawierają duplikaty, formułę można zmienić, jak pokazano poniżej, aby przypisać kolejną rangę wartościom, które pojawiają się więcej niż raz:
=COUNTIF(data,"<"&B5)+(COUNT(data)*ISTEXT(B5))+COUNTIF($B$5:B5,B5)
Ta wersja dostosowuje logikę początkowej funkcji LICZ.JEŻELI i dodaje kolejną LICZ.JEŻELI z rozszerzającym się odniesieniem do przyrostu duplikatów.
Wyświetl posortowane wartości
Aby pobrać i wyświetlić wartości posortowane w kolejności alfabetycznej przy użyciu obliczonej wartości rangi, E5 zawiera następującą formułę INDEKS i PODAJ.POZYCJĘ:
=INDEX(data,MATCH(ROWS($E$5:E5),rank,0))
gdzie „dane” to nazwany zakres B5: B13, a „pozycja” to nazwany zakres C5: C13.
Aby uzyskać więcej informacji na temat działania tej formuły, zobacz przykład tutaj.
Radzenie sobie ze spacjami
Puste komórki wygenerują rangę zerową. Zakładając, że chcesz zignorować puste komórki, działa to dobrze, ponieważ powyższa formuła INDEKS i PODAJ.POZYCJĘ zaczyna się od 1. Jednak na końcu posortowanych wartości zobaczysz błędy # N / A, po jednym dla każdej pustej komórki. Łatwym sposobem na poradzenie sobie z tym jest zawijanie formuły INDEKS i PODAJ.POZYCJĘ w następujący sposób:
=IFERROR(INDEX(data,MATCH(ROWS($E$5:E5),rank,0)),"")
