Obecný vzorec
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
souhrn
Chcete-li dynamicky řadit data, která obsahují pouze číselné hodnoty, můžete použít pomocný sloupec a vzorec vytvořený pomocí funkcí RANK a COUNTIF. V zobrazeném příkladu je vzorec v D5:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
kde „sales“ je pojmenovaný rozsah C5: C11.
Vysvětlení
Poznámka: tento vzorec je nastavení vzorce, který umožňuje extrahovat a zobrazit data pomocí předdefinovaného pořadí řazení ve sloupci pomocníka. Jeden příklad zde.
Jádrem tohoto vzorce je funkce RANK, která se používá ke generování hodnot prodejních hodnot, kde je nejvyšší číslo hodnoceno jako 1:
=RANK(C5,sales)
Zde RANK pro větší pohodlí používá pojmenovaný rozsah „prodeje“ (C5: C11). Ve výchozím nastavení RANK přiřadí 1 nejvyšší hodnotě, 2 druhé nejvyšší hodnotě atd. To funguje perfektně, pokud jsou číselné hodnoty jedinečné. Abychom však mohli zpracovávat číselné hodnoty, které obsahují duplikáty, musíme k přerušení vazeb použít funkci COUNTIF. To se provádí přidáním výsledku tohoto úryvku k hodnotě vrácené RANK:
COUNTIF($C$5:C5,C5)-1
Všimněte si, že rozsah je zadán jako smíšený odkaz, který se rozbalí při kopírování vzorce dolů v tabulce. Jak již bylo napsáno, tato reference bude zahrnovat aktuální řádek, takže odečteme 1 až „vynulovat“ první výskyt. To znamená, že výraz vrátí nulu pro každou číselnou hodnotu, dokud nenarazí na duplikát. Ve druhé instanci vrátí výraz 1, ve třetí instanci vrátí 2 atd. To efektivně přeruší vazby a umožní vzorci generovat sekvenční seznam čísel bez mezer.
Jakmile je vzorec na místě, lze data seřadit podle sloupce pomocníka. Lze jej také načíst pomocí INDEXU pomocí hodnot ve sloupci pomocníka.
Poznámka: Tento vzorec je převzat z příkladu ve skvělé knize Mike Girvin Control + Shift + Enter.
