Formuła ogólna
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Podsumowanie
Aby dynamicznie sortować dane, które zawierają tylko wartości liczbowe, możesz użyć kolumny pomocniczej i formuły utworzonej za pomocą funkcji POZYCJA i LICZ.JEŻELI. W przedstawionym przykładzie formuła w D5 to:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
gdzie „sprzedaż” to nazwany zakres C5: C11.
Wyjaśnienie
Uwaga: ta formuła jest konfiguracją formuły, która może wyodrębniać i wyświetlać dane przy użyciu predefiniowanej kolejności sortowania w kolumnie pomocniczej. Oto jeden przykład.
Rdzeniem tej formuły jest funkcja RANK, która służy do generowania rangi wartości sprzedaży, gdzie najwyższa liczba zajmuje pozycję 1:
=RANK(C5,sales)
Tutaj RANK używa dla wygody nazwanego zakresu „sprzedaż” (C5: C11). Domyślnie RANK przypisze 1 do najwyższej wartości, 2 do drugiej co do wartości i tak dalej. Działa to doskonale, o ile wartości liczbowe są unikalne. Jednak aby obsłużyć wartości liczbowe, które zawierają duplikaty, musimy użyć funkcji LICZ.JEŻELI, aby zerwać więzi. Odbywa się to poprzez dodanie wyniku tego fragmentu do wartości zwracanej przez RANK:
COUNTIF($C$5:C5,C5)-1
Zwróć uwagę, że zakres jest wprowadzany jako odwołanie mieszane, które rozszerzy się w miarę kopiowania formuły w dół tabeli. Jak napisano, to odwołanie będzie zawierało bieżący wiersz, więc odejmujemy 1 do „wyzerowania” pierwszego wystąpienia. Oznacza to, że wyrażenie zwróci zero dla każdej wartości liczbowej do momentu napotkania duplikatu. W drugim przypadku wyrażenie zwróci 1, w trzecim wystąpienie zwróci 2 i tak dalej. To skutecznie zrywa więzi i pozwala formule wygenerować sekwencyjną listę liczb bez przerw.
Po utworzeniu formuły dane można sortować według kolumny pomocniczej. Można go również pobrać za pomocą INDEKSU, używając wartości w kolumnie pomocnika.
Uwaga: Ta formuła została zaczerpnięta z przykładu w doskonałej książce Control + Shift + Enter autorstwa Mike'a Girvina.
