Generisk formel
=MATCH(RAND(),cumulative_probability)
Resumé
For at generere et tilfældigt tal, vægtet med en given sandsynlighed, kan du bruge en hjælperetabel sammen med en formel baseret på funktionerne RAND og MATCH.
I det viste eksempel er formlen i F5:
=MATCH(RAND(),D$5:D$10)
Forklaring
Denne formel er afhængig af hjælperbordet, der er synligt i området B4: D10. Kolonne B indeholder de seks tal, vi ønsker som et endeligt resultat. Kolonne C indeholder sandsynlighedsvægten tildelt hvert nummer, indtastet i procent. Kolonne D indeholder den kumulative sandsynlighed, der er oprettet med denne formel i D5, kopieret ned:
=SUM(D4,C4)
Bemærk, at vi bevidst skifter den kumulative sandsynlighed ned en række, så værdien i D5 er nul. Dette er for at sikre, at MATCH er i stand til at finde en position for alle værdier ned til nul som forklaret nedenfor.
For at generere en tilfældig værdi ved hjælp af den vægtede sandsynlighed i hjælpetabellen indeholder F5 denne formel, kopieret ned:
=MATCH(RAND(),D$5:D$10)
Inde i MATCH leveres opslagsværdien af RAND-funktionen. RAND genererer en tilfældig værdi mellem nul og 1. Opslagsarrayet er området D5: D10, låst, så det ikke ændres, da formlen kopieres ned i kolonnen.
Det tredje argument for MATCH, matchtype, er udeladt. Når matchtype udelades, returnerer MATCH placeringen af den største værdi mindre end eller lig med opslagsværdien *. Rent praktisk betyder det, at MATCH-funktionen bevæger sig langs værdierne i D5: D10, indtil der er en større værdi, og derefter "træder tilbage" til den forrige position. Når MATCH støder på en værdi, der er større end den største sidste værdi i D5: D10 (.7 i eksemplet), returnerer den den sidste position (6 i eksemplet). Som nævnt ovenfor er den første værdi i D5: D10 bevidst nul for at sikre, at værdier under .1 "fanges" af opslagstabellen og returnerer en position på 1.
* Værdier i opslagsområdet skal sorteres i stigende rækkefølge.
Tilfældig vægtet tekstværdi
For at returnere en tilfældig vægtet tekstværdi (dvs. en ikke-numerisk værdi) kan du indtaste tekstværdier i området B5: B10 og derefter tilføje INDEX for at returnere en værdi i dette interval, baseret på den position, der returneres af MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Bemærkninger
- Jeg løb ind i denne tilgang i et forumindlæg på mrexcel.com
- RAND er en flygtig funktion og genberegnes med hver ændring af regnearket
- Når du har tilfældige værdier, skal du bruge indsæt specielle> værdier til at erstatte formlen, hvis det er nødvendigt
