Všeobecný vzorec
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Zhrnutie
Ak chcete dynamicky triediť údaje, ktoré obsahujú iba číselné hodnoty, môžete použiť pomocný stĺpec a vzorec vytvorený pomocou funkcií RANK a COUNTIF. V zobrazenom príklade je vzorec v D5:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
kde „predaj“ je pomenovaný rozsah C5: C11.
Vysvetlenie
Poznámka: tento vzorec predstavuje nastavenie pre vzorec, ktorý umožňuje extrahovať a zobrazovať údaje pomocou preddefinovaného poradia zoradenia v pomocnom stĺpci. Jeden príklad tu.
Jadrom tohto vzorca je funkcia RANK, ktorá sa používa na vygenerovanie poradia hodnôt predaja, kde sa najvyššie číslo umiestni na prvom mieste:
=RANK(C5,sales)
Tu RANK pre pohodlie používa pomenovaný rozsah „predaj“ (C5: C11). Predvolene RANK priradí 1 k najvyššej hodnote, 2 k druhej najvyššej hodnote atď. Funguje to perfektne, pokiaľ sú číselné hodnoty jedinečné. Na spracovanie číselných hodnôt obsahujúcich duplikáty však musíme na prerušenie väzieb použiť funkciu COUNTIF. To sa deje pridaním výsledku tohto úryvku k hodnote vrátenej RANK:
COUNTIF($C$5:C5,C5)-1
Všimnite si, že rozsah je zadaný ako zmiešaná referencia, ktorá sa rozšíri pri kopírovaní vzorca dolu v tabuľke. Ako už bolo uvedené, bude táto referencia obsahovať aktuálny riadok, takže od prvého výsledku odpočítame 1, aby sa hodnota „vynulovala“. To znamená, že výraz vráti nulu pre každú číselnú hodnotu, kým sa nenájde duplikát. V druhom prípade výraz vráti 1, v treťom prípade vráti 2 atď. To efektívne rozbije väzby a umožní vzorec generovať postupný zoznam čísel bez medzier.
Keď je vzorec na mieste, údaje je možné triediť podľa pomocného stĺpca. Môže sa tiež načítať pomocou INDEXU pomocou hodnôt v stĺpci Pomocník.
Poznámka: Tento vzorec je prevzatý z príkladu vo vynikajúcej knihe Control + Shift + Enter od Mika Girvina.
