Generieke formule
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Samenvatting
Om bestaande waarden willekeurig te sorteren met een formule, kunt u een INDEX- en MATCH-formule samen met hulpkolommen gebruiken, zoals weergegeven in de schermafbeelding. In het getoonde voorbeeld is de formule in E5:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
waar "namen" het benoemde bereik B5: B11 is, "rand" het benoemde bereik C5: C11 en "sort" het benoemde bereik D5: D11 is.
Uitleg
Deze formule is afhankelijk van twee hulpkolommen. De eerste hulpkolom bevat willekeurige waarden die zijn gemaakt met de functie RAND (). De formule in C5, gekopieerd, is:
=RAND()
De RAND-functie genereert een willekeurige waarde voor elke rij.
Opmerking: RAND is een vluchtige functie en genereert nieuwe waarden bij elke wijziging van het werkblad.
De tweede hulpkolom bevat de nummers die worden gebruikt om gegevens te sorteren, gegenereerd met een formule. De formule in D5 is:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Zie deze pagina voor uitleg van deze formule.
De formule in E5 is:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Hier wordt de INDEX-functie gebruikt om waarden in het benoemde bereik "namen" op te halen, met behulp van de sorteerwaarden in het benoemde bereik "sort". Het eigenlijke werk om uit te zoeken welke waarde moet worden opgehaald, wordt gedaan met de MATCH-functie in dit fragment:
MATCH(ROWS($D$5:$D5),sort,0)
Binnen MATCH krijgt de ROWS-functie een uitbreidend bereik als de opzoekwaarde, die begint als één cel en groter wordt naarmate de formule door de kolom wordt gekopieerd. Dit verhoogt de opzoekwaarde, beginnend bij 1 en doorlopend tot 7. VERGELIJKEN retourneert vervolgens de positie van de opzoekwaarde in de lijst.
De positie wordt naar INDEX gestuurd als het rijnummer, en INDEX haalt de naam op die positie op.
