Συναρτήσεις τυχαίων αριθμών

MATHΓια την παραγωγή τυχαίων αριθμών το excel έχει δύο συναρτήσεις φύλλου, την =RAND() και την =RANDBETWEEN(bottom,top).
Η RAND() είναι συνάρτηση χωρίς ορίσματα και επιστρέφει ένα τυχαίο θετικό δεκαδικό αριθμό μικρότερο του 1, δηλαδή έναν αριθμό μεταξύ 0 και 1. Όπως μπορείτε να δείτε και στην βοήθεια της Microsoft για την συνάρτηση, για να πάρετε ένα τυχαίο αριθμό σε ένα άλλο διάστημα αριθμών, έστω μεταξύ a και b, χρησιμοποιήστε τον τύπο: =RAND()*(b-a)+a. Έτσι, ο τύπος =RAND()*(6-2)+2 θα επιστρέψει ένα τυχαίο δεκαδικό αριθμό στο διάστημα (2,6).
Για να πάρετε έναν ακέραιο αριθμό μεγαλύτερο του ακέραιου α και μικρότερο του ακέραιου β, χρησιμοποιήστε τον τύπο:

=INT(RAND()*(β-1-α))+α+1

Για να πάρετε έναν ακέραιο αριθμό μεγαλύτερο ή ίσο του ακέραιου α και μικρότερο ή ίσο του ακέραιου β, χρησιμοποιήστε τον τύπο:

   
=INT(RAND()*(β+1-α))+α

Η συνάρτηση RANDBETWEEN αποτελεί μόνιμο μέλος της συλλογής συναρτήσεων φύλλου του excel από την έκδοση 2007 και μετά. Για να την χρησιμοποιήσετε στις παλιότερες εκδόσεις πρέπει να έχετε εγκαταστήσει το πρόσθετο “Πακέτο εργαλείων ανάλυσης”. Δέχεται δύο ορίσματα, τους αριθμούς: bottom και top και επιστρέφει έναν τυχαίο ακέραιο αριθμό, μεταξύ των δύο αριθμών bottom και top. Αν τα ορίσματα είναι ακέραιοι τότε συμπεριλαμβάνονται στις πιθανές τιμές που θα επιστρέψει η συνάρτηση. Δηλαδή η =RANDBETWEEN(1;20) θα επιστρέψει έναν ακέραιο αριθμό μεγαλύτερο ή ίσο του 1 και μικρότερο ή ίσο του 20.
Προσέξτε ότι και οι δύο συναρτήσεις, κάθε φορά που υπολογίζεται το φύλλο, αποδίδουν ένα νέο τυχαίο αριθμό. Σε κάθε αλλαγή οποιουδήποτε κελιού του βιβλίου ή σε κάθε πάτημα του πλήκτρου F9, ή σε κάθε νέο άνοιγμα του βιβλίου, οι συναρτήσεις επαναϋπολογίζονται και θα επιστρέψουν νέους τυχαίους αριθμούς. Ίσως αυτό δεν το θέλετε. Αν θέλετε να κρατήσετε τους αριθμούς που επέστρεψαν αρχικά οι συναρτήσεις, πρέπει να τις αντικαταστήσετε αμέσως με αξίες. Πιο κάτω, στο σημερινό σημείωμα, θα συζητήσουμε και μία άλλη τεχνική, με την βοήθεια της vba, ώστε η συνάρτηση RAND να μην είναι ευμετάβλητη και επαναϋπολογίζεται αυτόματα, αλλά ο υπολογισμός να είναι στον δικό σας έλεγχο. Επίσης η RAND και η RANDBETWEEN δεν είναι κατάλληλες για να πάρετε περισσότερους από έναν μοναδικούς τυχαίους αριθμούς. Αν εισάγετε σε δύο κελιά την =RANDBETWEEN(1,10), είναι πολύ πιθανό να επιστραφεί ο ίδιος αριθμός. Για μοναδικούς τυχαίους θα χρειαστείτε μία άλλη συνάρτηση.

Η συνάρτηση RANDBETWEENplus για μοναδικούς τυχαίους αριθμούς.
Η vba συνάρτηση RANDBETWEENplus (bottom; top; Lots), τον κώδικα της οποίας θα βρείτε στο τέλος του σημειώματος, δέχεται τρία υποχρεωτικά ορίσματα. Τα bottom και top είναι οι αριθμοί που καθορίζουν το διάστημα μέσα στο οποίο αναζητούμε μοναδικούς τυχαίους ακέραιους αριθμούς και Lots είναι το πλήθος των αριθμών που αναζητούμε. Παράδειγμα η συνάρτηση =RANDBETWEENplus(1;20;5) θα επιστρέψει 5 μοναδικούς ακέραιους αριθμούς μεταξύ των αριθμών 1 και 20. Οι αριθμοί 1 και 20 συμπεριλαμβάνονται στις πιθανές τιμές που θα επιστραφούν. Φυσικά, αφού η συνάρτηση θα επιστρέψει 5 αριθμούς, πρέπει να εισαχθεί σαν συνάρτηση-πίνακας πολλών κελιών. Επιλέξτε το κελί που την γράψατε και τα 4 διπλανά κελιά, δεξιά στην ίδια γραμμή, πατήστε F2 και μετά πατήστε Ctrl+Shift+Enter. Για τις συναρτήσεις-πίνακες, δείτε το σχετικό σημείωμα του ιστολογίου “συναρτήσεις – πίνακες ή Ctrl+Shift+Enter συναρτήσεις”. Τα ορίσματα bottom και top είναι αριθμοί ακέραιοι ή δεκαδικοί, θετικοί ή αρνητικοί, αλλά το όρισμα Lots είναι μόνο ακέραιος θετικός αφού εκφράζει πλήθος και η συνάρτηση επιστρέφει μόνο ακέραιους αριθμούς. Παραδείγματα:
Η συνάρτηση =RANDBETWEENplus(3,2;20,7;5) θα επιστρέψει 5 ακέραιους αριθμούς μεταξύ 4 και 20.
Η συνάρτηση =RANDBETWEENplus(-10,2;-2,7;5) θα επιστρέψει 5 ακέραιους αριθμούς μεταξύ -10 και -3.
Η συνάρτηση =RANDBETWEENplus(1;10;10) θα επιστρέψει μία τυχαία αναδιάταξη των αριθμών 1 έως 10
Η πλήρης σύνταξη της συνάρτησης είναι:
RANDBETWEENplus(bottom ; top ; Lots ; horizontal ; exception)
Τα προαιρετικό όρισμα horizontal παίρνει τιμές TRUE και FALSE. Αν ορίσετε το όρισμα σε FALSE, η συνάρτηση επιστρέφει τις τιμές σε στήλη, κάθετα. Γράψτε τη συνάρτηση =RANDBETWEENplus(1;20;5;FALSE) σε ένα κελί, επιλέξτε το κελί αυτό και τα 4 κάτω από αυτό κελιά στην ίδια στήλη, πατήστε F2 και μετά πατήστε Ctrl+Shift+Enter.
Με το προαιρετικό όρισμα exception μπορείτε να αποκλείσετε μία ή περισσότερες τιμές από την τυχαία επιλογή. Το όρισμα exception μπορεί να γραφεί σαν πίνακας απευθείας στην συνάρτηση ή να είναι μια άλλη συνάρτηση που επιστρέφει πίνακα ή να είναι μία γραμμή ή στήλη ή ορθογώνια περιοχή του φύλλου. Παραδείγματα:
Η συνάρτηση:

 
=RANDBETWEENplus(10;100;5;TRUE;77)

θα επιστρέψει (οριζόντια) 5 ακέραιους αριθμούς μεταξύ 10 και 100 εξαιρώντας τον αριθμό 77
Η συνάρτηση:

=RANDBETWEENplus(10;100;5;TRUE;{10;20;30;40})

θα επιστρέψει (οριζόντια) 5 ακέραιους αριθμούς μεταξύ 10 και 100 εξαιρώντας τους αριθμούς 10, 20, 30, 40.
Το ίδιο και η συνάρτηση:

=RANDBETWEENplus(10;100;5;TRUE;{10\20\30\40})

Η συνάρτηση:

 
=RANDBETWEENplus(10;100;5;FALSE;2*ROW(INDIRECT("5:50")))

θα επιστρέψει (κάθετα) 5 ακέραιους αριθμούς μεταξύ 10 και 100, αποκλείοντας τους ζυγούς.
Η συνάρτηση:

=RANDBETWEENplus(10;100;5;FALSE;A1:B10)

θα επιστρέψει (κάθετα) 5 ακέραιους αριθμούς μεταξύ 10 και 100, αποκλείοντας τους ακέραιους αριθμούς μεταξύ 10 και 100 που θα βρει στην περιοχή των κελιών A1:B10. Η περιοχή εδώ είναι ορθογώνια, αλλά μπορεί να είναι μια στήλη ή μία γραμμή ή ακόμα και συνάθροιση πολλών περιοχών. Στην τελευταία αυτή περίπτωση κλείστε τις περιοχές σε παρένθεση, όπως στο παράδειγμα:

=RANDBETWEENplus(10;100;5;FALSE;(A1:A4;A8:C8;C1:C5))

Αν στην περιοχή υπάρχουν αλφαριθμητικά, ή δεκαδικοί αριθμοί, ή ακέραιοι εκτός του διαστήματος (bottom , top), θα αγνοηθούν.

Όπως και οι δύο προηγούμενες συναρτήσεις φύλλου, έτσι και η vba συνάρτηση RANDBETWEENplus είναι ευμετάβλητη και επαναϋπολογίζεται σε κάθε μεταβολή που θα συμβεί στο βιβλίο. Αυτό το καθορίζει η πρώτη γραμμή του κώδικα Application.Volatile η οποία έχει οριστεί σε True. Αλλάξτε σε False, και τότε η συνάρτηση θα πάψει να επαναϋπολογίζεται αυτόματα. Σε αυτή την περίπτωση, που η Volatile οριστεί False, η συνάρτηση ξανα-υπολογίζεται στις παρακάτω περιπτώσεις: α) Όταν η συνάρτηση επανεισαχθεί β) όταν γίνει οποιαδήποτε αλλαγή στα ορίσματα της συνάρτησης ή στις τιμές των κελιών που επηρεάζουν άμεσα ή έμμεσα τα αποτελέσματά της και γ) Χειριστικά, αν πατήσετε Ctrl+Alt+F9.
Έτσι, αν θέλετε να έχει παρόμοια “σταθερή” συμπεριφορά η συνάρτηση φύλλου RAND(), χρησιμοποιήστε στη θέση της μια vba συνάρτηση:

Function nonVolatileRand() As Double
Application.Volatile False
nonVolatileRand = VBA.Rnd
End Function

η οποία είναι απολύτως ισοδύναμη της RAND() και επειδή όπως και η RAND() δεν έχει ορίσματα, επαναϋπολογίζεται μόνο αν θέλετε, χειριστικά, με Ctrl+Alt+F9.

Σταθμισμένη κλήρωση – συνάρτηση WeightedRand
Η επόμενη συνάρτηση WeightedRand (LotArray ; WeightedArray ), κληρώνει ένα λαχνό από τους λαχνούς που θα του δοθούν στο όρισμα LotArray σε μορφή πίνακα ή από μία στήλη ή μία γραμμή του φύλλου. Οι λαχνοί δεν αντιμετωπίζονται ισοπίθανα από την συνάρτηση, αλλά σταθμίζονται από ακέραια αριθμητικά δεδομένα που θα δοθούν στο όρισμα WeightedArray πάλι σε μορφή πίνακα ή από στήλη ή από γραμμή. Στην εικόνα, στη στήλη Α υπάρχουν εννέα ενδεχόμενα (λαχνοί) και στη στήλη Β, οι αντίστοιχες ακέραιες σταθμίσεις τους.

WeightedRand
Η συνάρτηση θα “κληρώσει” και θα επιστρέψει ένα ενδεχόμενο απομιμούμενη τον πρακτικό τρόπο της κληρωτίδας. Θα κατασκευάσει μια κληρωτίδα (πίνακα) στην οποία, για κάθε ενδεχόμενο, θα βάλει τόσους κλήρους (λαχνούς), όσους ορίζει ο αντίστοιχος ακέραιος στάθμισης. Στο παράδειγμα: 5 κλήρους για το ενδεχόμενο-1,   4 κλήρους για το ενδεχόμενο-2,   0 για το ενδεχόμενο-3 (δηλαδή το ενδεχόμενο-3 δεν συμμετέχει στην κλήρωση) κλπ. Από την κληρωτίδα η συνάρτηση θα κληρώσει και θα επιστρέψει ένα τυχαίο λαχνό (κελί Α13).
Τα ενδεχόμενα μπορούν να είναι αριθμητικά, αλφαριθμητικά, οτιδήποτε, αλλά οι συντελεστές στάθμισης πρέπει να είναι ακέραιοι. Αν έχετε συντελεστές δεκαδικούς, η συνάρτηση θα τους δεχτεί αλλά θα τους στρογγυλοποιήσει, πράγμα που δεν θα το θέλετε. Το σωστό είναι να χρησιμοποιήσετε πολλαπλάσιά τους, με 10, 100 κλπ. Αντί για 5-7,2-3,5-0,9 χρησιμοποιήστε ισοδύναμα: 50-72-35-9.
Ο κώδικας για τη συνάρτηση RANDBETWEENplus:


Function RANDBETWEENplus(bottom As Double, top As Double, Lots As Integer, _
 Optional horizontal As Boolean = True, Optional exception As Variant) As Variant
'από το Excel Λύσεις
Application.Volatile True

Dim RBWcoll As New Collection
Dim EXCcoll As New Collection
Dim RBWarr() As Variant
Dim num As Double
Dim exStr As String
Dim item As Variant
Dim i As Integer

If bottom - Int(bottom) > 0 Then bottom = Int(bottom) + 1
top = Int(top)

If Not IsMissing(exception) Then
If VBA.TypeName(exception) = "Double" Then exception = Array(exception)
 For Each item In exception
 If IsNumeric(item) Then
 If Int(item) = item And item < top + 1 And item > bottom - 1 Then
 On Error Resume Next
 EXCcoll.Add item, CStr(item)
 On Error GoTo 0
 End If
 End If
 Next
End If

Select Case True
 Case bottom >= top, Lots > top + 1 - bottom - EXCcoll.Count, Lots <= 0
 RANDBETWEENplus = CVErr(xlErrValue)
 GoTo telos
End Select

Do While RBWcoll.Count < Lots
 num = Int((top - bottom + 1) * Rnd + bottom)
 On Error Resume Next
 RBWcoll.Add num, CStr(num)
 For i = 1 To EXCcoll.Count
 exStr = EXCcoll(i)
 RBWcoll.Remove (exStr)
 Next
 On Error GoTo 0
Loop

ReDim RBWarr(1 To Lots)
For i = 1 To Lots
 RBWarr(i) = RBWcoll(i)
Next i
RANDBETWEENplus = RBWarr()
If horizontal = False Then RANDBETWEENplus = Application.Transpose(RBWarr())
telos:
End Function

Ο κώδικας για τη συνάρτηση WeightedRand:


Function WeightedRand(LotArray As Variant, _
 WeightedArray As Variant) As String
'από το Excel Λύσεις
Application.Volatile True
Dim Lot As Long
Dim Sum As Long
Dim X As Variant

For Lot = 1 To LotArray.Count
 Sum = Sum + WeightedArray(Lot)
 X = X & Application.Rept(LotArray(Lot) & _
 ChrW(1), WeightedArray(Lot))
Next
X = Split(X, ChrW(1))
Lot = Int(Sum * Rnd)
WeightedRand = X(Lot)
End Function

 

This entry was posted in excel, Μακροεντολές, Συναρτήσεις φύλλου, Συναρτήσεις Χρήστη, VBA and tagged , , , , , , . Bookmark the permalink.

4 Responses to Συναρτήσεις τυχαίων αριθμών

  1. Ο/Η Δημήτρης λέει:

    Γεια σου φίλε Γιάννη. Είδα τις συναρτήσεις τυχαίων αριθμών που είχες αναρτήσει (Πολύ καλή δουλειά!!!), και θα ήθελα να σε ρωτήσω εάν υπάρχει τρόπος να συνδυασθούν μεταξύ τους. Παράδειγμα: Η RANDBETWEENplus με την WeightedRand, έτσι ώστε η WeightedRand να δίνει έναν αριθμό που δεν έχει κληρωθεί ξανά.
    Σε ένα πρόγραμμα ΤΖΟΚΕΡ που έχω φτιάξει, έχω χρησιμοποιήσει μία παλαιότερη ρουτίνα πού είχες τροποποιήσει για εμένα μετά από ερώτηση μου, την Sub Dtest()
    (‘Κάνει αμερόληπτη κλήρωση 5 αριθμών από 1 έως 45,
    ‘εξαιρώντας τους αριθμούς που θα βρει στη στήλη Α1:40. ‘
    ‘Στην περιοχή εξαιρέσεων Α1:Α40 δεν πρέπει να υπάρχουν ίδιοι αριθμοί.
    ‘Μπορούν όμως, να υπάρχουν κενά, αλφαριθμητικά ή αριθμοί
    ‘εκτός του διαστήματος (1,45),διότι θα αγνοηθούν.
    ‘Οι 5 αριθμοί που θα κληρωθούν επιστρέφουν στα κελιά C1 έως G1.).
    Αναρωτιέμαι αν είναι δυνατόν να τροποποιηθεί αυτή η ρουτίνα έτσι ώστε να συνεργάζεται με την WeightedRand.

  2. Ο/Η vioannis λέει:

    Δημήτρη, έστω ότι έχω 5 ενδεχόμενα ε1, ε2, ε3, ε4, ε5 με συντελεστές στάθμισης 10, 15, 20, 25, 30 αντίστοιχα. Στην πρώτη κλήρωση η WeightedRand κληρώνει, ας πούμε το ε4 και, φυσικά, στη συνέχεια αφαιρείται το ενδεχόμενο αυτό από την κληρωτίδα. Πως θα αναπροσαρμοστούν στους εναπομείναντες λαχνούς οι συντελεστές στάθμισης; Αν απλώς διαγραφεί το ενδεχόμενο ε4 μαζί με τη συχνότητά του, πριν ξανακληθεί η WeightedRand, παρατήρησε ότι τότε, ενώ αρχικά τα ενδεχόμενα ε1, ε2, ε3, ε5 είχαν σε ποσοστό % συντελεστές αντίστοιχα 10%, 15%, 20%, 30%, τώρα θα έχουν 13,3%, 20%, 26,7%, 40%.Αυτό θες ή κάτι άλλο; Αυτό είναι δικό σου θέμα να το απαντήσεις, εσύ παίζεις και άρα βάζεις τους όρους του παιχνιδιού. Μετά θα το ξαναδούμε.

  3. Ο/Η Takis V. λέει:

    Κύριε Βαρλάμη, επιτρέψτε μου να επαναφέρω το ερώτημα που έβαλε παραπάνω ο Δημήτρης, για μία συνάρτηση σαν την Weighted Rand που θα κληρώνει και θα επιστρέφει περισσότερα από ένα ενδεχόμενα, μοναδικά, χωρίς επανάληψη. Με τον τρόπο που περιγράφετε στην απάντηση σας στο Δημήτρη, δηλαδή με διαγραφή του ενδεχόμενου που κληρώνεται κάθε φορά, ανεξάρτητα πόσο μεγάλο ή μικρό συντελεστή έχει. Είναι δυνατό κάτι τέτοιο; Σας ευχαριστώ πολύ.

  4. Ο/Η vioannis λέει:

    Τάκη, διατηρώντας όλες τις επιφυλάξεις που διατύπωσα στην απάντησή μου στον Δημήτρη, ο κώδικας της συνάρτησης που ζητάς είναι:

    Function WeightedRandPlus(LotArray As Variant, _
        WeightedArray As Variant, n As Integer) As Variant
    Dim WArr() As Variant
    Dim DrawArr() As Variant
    Dim j As Integer
    Dim LotArrayIndex As Integer
    
    ReDim WArr(1 To WeightedArray.Count)
    For j = 1 To WeightedArray.Count
        WArr(j) = WeightedArray(j)
    Next j
    
    ReDim DrawArr(1 To n)
    For j = 1 To n
        DrawArr(j) = WeightedRand(LotArray, WArr)
        LotArrayIndex = FindIndex(LotArray, DrawArr(j))
        WArr(LotArrayIndex) = 0
    Next j
    WeightedRandPlus = DrawArr()
    End Function
    
    Private Function FindIndex(VArray As Variant, _
        item As Variant) As Integer
    Dim IndexNum As Integer
    For IndexNum = 1 To VArray.Count
        If item = VArray(IndexNum) Then
            FindIndex = IndexNum
            Exit For
        End If
    Next IndexNum
    End Function
    

    Ο κώδικας της συνάρτησης WeightedRandPlus (μαζί με τον κώδικα της βοηθητικής συνάρτησης FindIndex), πρέπει να γραφτεί στο ίδιο module με την αρχική WeightedRand, διότι την χρησιμοποιεί. Τα ορίσματα LotArray και WeightedArray της συνάρτησης είναι τα ίδια με τα ορίσματα της WeightedRand, δες την περιγραφή τους πιο πάνω, και το όρισμα n είναι το πλήθος των μοναδικών ενδεχομένων που ζητάς να επιστραφούν. Φυσικά το n είναι ακέραιος και μικρότερος από το πλήθος των στοιχείων των άλλων δύο ορισμάτων, και η συνάρτηση είναι “συνάρτηση – πίνακας” και εισάγεται σε n συνεχόμενα κελιά μιας γραμμής με Ctrl+Shift+Enter. Αν θέλεις επιστροφή σε στήλη (κάθετη) χρησιμοποίησε τη σύνταξη: =TRANSPOSE(WeightedRandPlus(… ))

Τα σχόλια είναι απενεργοποιημένα.