Yleinen kaava
=MATCH(RAND(),cumulative_probability)
Yhteenveto
Voit luoda satunnaisluvun, joka on painotettu tietyllä todennäköisyydellä, käyttämällä aputaulukkoa RAND- ja MATCH-funktioihin perustuvan kaavan kanssa.
Esitetyssä esimerkissä kaava kohdassa F5 on:
=MATCH(RAND(),D$5:D$10)
Selitys
Tämä kaava perustuu apupöytään, joka näkyy alueella B4: D10. Sarake B sisältää kuusi numeroa, jotka haluamme lopputuloksena. Sarake C sisältää kullekin numerolle määritetyn todennäköisyyspainon, joka syötetään prosentteina. Sarake D sisältää kumulatiivisen todennäköisyyden, joka on luotu tällä kaavalla D5: ssä, kopioitu alaspäin:
=SUM(D4,C4)
Huomaa, että tarkoituksellisesti siirrämme kumulatiivista todennäköisyyttä alaspäin yhdellä rivillä, niin että D5: n arvo on nolla. Näin varmistetaan, että MATCH pystyy löytämään sijainnin kaikille arvoille nollaan asti, kuten alla selitetään.
Satunnaisarvon luomiseksi auttajataulukon painotettua todennäköisyyttä käyttäen F5 sisältää tämän kaavan, kopioitu alaspäin:
=MATCH(RAND(),D$5:D$10)
MATCH: n sisällä hakuarvo saadaan RAND-toiminnolta. RAND tuottaa satunnaisen arvon välillä nolla ja 1. Hakutaulukko on alue D5: D10, lukittu, joten se ei muutu, kun kaava kopioidaan alas saraketta.
Kolmas MATCH-argumentti, hakutyyppi, jätetään pois. Kun hakutyyppi jätetään pois, MATCH palauttaa suurimman arvon sijainnin, joka on pienempi tai yhtä suuri kuin hakuarvo *. Käytännössä tämä tarkoittaa, että MATCH-funktio kulkee D5: D10: n arvoja pitkin, kunnes havaitaan suurempi arvo, ja sitten "palaa" edelliseen sijaintiin. Kun MATCH kohtaa arvon, joka on suurempi kuin D5: D10: n viimeisin viimeinen arvo (esimerkissä .7), se palauttaa viimeisen sijainnin (esimerkissä 6). Kuten edellä mainittiin, D5: D10: n ensimmäinen arvo on tarkoituksella nolla sen varmistamiseksi, että hakutaulukko "tarttuu" alle 1: n arvoihin ja palauttaa arvon 1.
* Hakualueen arvot on lajiteltava nousevassa järjestyksessä.
Satunnainen painotettu tekstiarvo
Voit palauttaa satunnaisen painotetun tekstiarvon (eli ei-numeerisen arvon) syöttämällä tekstiarvot alueelle B5: B10 ja lisäämällä sitten INDEX palauttamaan kyseisen alueen arvon MATCH: n palauttaman sijainnin perusteella:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Huomautuksia
- Törmäsin tähän lähestymistapaan mrexcel.com -foorumin viestissä
- RAND on haihtuva toiminto, joka laskee uudelleen jokaisen laskentataulukon muutoksen yhteydessä
- Kun sinulla on satunnaisia arvoja, korvaa kaava tarvittaessa liittämällä erityiset> arvot
