Obecný vzorec
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
souhrn
Chcete-li náhodně seřadit stávající hodnoty pomocí vzorce, můžete použít vzorec INDEX a MATCH společně se sloupci pomocníka, jak je znázorněno na snímku obrazovky. V zobrazeném příkladu je vzorec v E5:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
kde „names“ je pojmenovaný rozsah B5: B11, „rand“ je pojmenovaný rozsah C5: C11 a „sort“ je pojmenovaný rozsah D5: D11.
Vysvětlení
Tento vzorec závisí na dvou pomocných sloupcích. První pomocný sloupec obsahuje náhodné hodnoty vytvořené pomocí funkce RAND (). Zkopírovaný vzorec v C5 je:
=RAND()
Funkce RAND generuje na každém řádku náhodnou hodnotu.
Poznámka: RAND je nestálá funkce a při každé změně listu bude generovat nové hodnoty.
Druhý pomocný sloupec obsahuje čísla použitá ke třídění dat generovaných vzorcem. Vzorec v D5 je:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Na této stránce naleznete vysvětlení tohoto vzorce.
Vzorec v E5 je:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Zde se funkce INDEX používá k načtení hodnot v pojmenovaném rozsahu „names“ pomocí hodnot řazení v pojmenovaném rozsahu „sort“. Skutečná práce při zjišťování, jakou hodnotu načíst, se provádí pomocí funkce MATCH v tomto úryvku:
MATCH(ROWS($D$5:$D5),sort,0)
Uvnitř MATCH funkce ROWS dostává rozšiřující se rozsah jako vyhledávací hodnotu, která začíná jako jedna buňka a rozšiřuje se při kopírování vzorce dolů do sloupce. Tím se zvýší vyhledávací hodnota, počínaje 1 a pokračující po 7. MATCH pak vrátí pozici vyhledávací hodnoty v seznamu.
Pozice se přivádí na INDEX jako číslo řádku a INDEX načte název na této pozici.
