Γενική φόρμουλα
=MATCH(RAND(),cumulative_probability)
Περίληψη
Για να δημιουργήσετε έναν τυχαίο αριθμό, σταθμισμένο με μια δεδομένη πιθανότητα, μπορείτε να χρησιμοποιήσετε έναν βοηθητικό πίνακα μαζί με έναν τύπο που βασίζεται στις συναρτήσεις RAND και MATCH.
Στο παράδειγμα που εμφανίζεται, ο τύπος στο F5 είναι:
=MATCH(RAND(),D$5:D$10)
Εξήγηση
Αυτός ο τύπος βασίζεται στον πίνακα βοηθού που είναι ορατός στην περιοχή B4: D10. Η στήλη Β περιέχει τους έξι αριθμούς που θέλουμε ως τελικό αποτέλεσμα. Η στήλη C περιέχει το βάρος πιθανότητας που αντιστοιχεί σε κάθε αριθμό, που καταχωρείται ως ποσοστό Η στήλη D περιέχει την αθροιστική πιθανότητα, που δημιουργήθηκε με αυτόν τον τύπο στο D5, που αντιγράφηκε:
=SUM(D4,C4)
Παρατηρήστε, μετατοπίζουμε σκόπιμα τη σωρευτική πιθανότητα σε μία σειρά, έτσι ώστε η τιμή στο D5 να είναι μηδέν. Αυτό γίνεται για να βεβαιωθείτε ότι το MATCH είναι σε θέση να βρει μια θέση για όλες τις τιμές στο μηδέν όπως εξηγείται παρακάτω.
Για να δημιουργήσετε μια τυχαία τιμή, χρησιμοποιώντας τη σταθμισμένη πιθανότητα στον βοηθητικό πίνακα, το F5 περιέχει αυτόν τον τύπο, αντιγράφεται:
=MATCH(RAND(),D$5:D$10)
Μέσα στο MATCH, η τιμή αναζήτησης παρέχεται από τη συνάρτηση RAND. Το RAND δημιουργεί μια τυχαία τιμή μεταξύ μηδέν και 1. Ο πίνακας αναζήτησης είναι το εύρος D5: D10, κλειδωμένο, ώστε να μην αλλάζει καθώς ο τύπος αντιγράφεται στη στήλη.
Το τρίτο όρισμα για MATCH, τύπος αντιστοίχισης, παραλείπεται. Όταν παραλειφθεί ο τύπος αντιστοίχισης, το MATCH θα επιστρέψει τη θέση της μεγαλύτερης τιμής μικρότερη ή ίση με την τιμή αναζήτησης *. Στην πράξη, αυτό σημαίνει ότι η συνάρτηση MATCH κινείται κατά μήκος των τιμών στο D5: D10 έως ότου συναντηθεί μεγαλύτερη τιμή, και στη συνέχεια "επιστρέψει" στην προηγούμενη θέση. Όταν το MATCH συναντά μια τιμή μεγαλύτερη από τη μεγαλύτερη τελευταία τιμή στο D5: D10 (.7 στο παράδειγμα), επιστρέφει την τελευταία θέση (6 στο παράδειγμα). Όπως αναφέρθηκε παραπάνω, η πρώτη τιμή στο D5: D10 είναι σκόπιμα μηδέν για να διασφαλίσει ότι οι τιμές παρακάτω .1 "πιάνονται" από τον πίνακα αναζήτησης και επιστρέφουν μια θέση 1.
* Οι τιμές στο εύρος αναζήτησης πρέπει να ταξινομηθούν με αύξουσα σειρά.
Τυχαία σταθμισμένη τιμή κειμένου
Για να επιστρέψετε μια τιμή τυχαίου σταθμισμένου κειμένου (δηλαδή μια μη αριθμητική τιμή), μπορείτε να εισαγάγετε τιμές κειμένου στο εύρος B5: B10 και, στη συνέχεια, να προσθέσετε το INDEX για να επιστρέψετε μια τιμή σε αυτό το εύρος, με βάση τη θέση που επιστρέφεται από το MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Σημειώσεις
- Συνάντησα αυτήν την προσέγγιση σε μια δημοσίευση φόρουμ στο mrexcel.com
- Το RAND είναι μια πτητική συνάρτηση και θα υπολογίζεται εκ νέου με κάθε αλλαγή φύλλου εργασίας
- Μόλις έχετε τυχαίες τιμές, χρησιμοποιήστε ειδικές επικόλληση> τιμές για να αντικαταστήσετε τον τύπο, εάν χρειάζεται
