Γενική φόρμουλα
=INDEX(data,MATCH(ROWS(exp_range),sort_values,0))
Περίληψη
Για να ταξινομήσετε τυχαία τις υπάρχουσες τιμές με έναν τύπο, μπορείτε να χρησιμοποιήσετε έναν τύπο INDEX και MATCH μαζί με στήλες βοηθού όπως φαίνεται στο στιγμιότυπο οθόνης. Στο παράδειγμα που παρουσιάζεται, ο τύπος στο E5 είναι:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
όπου "ονόματα" είναι η ονομαζόμενη περιοχή B5: B11, "rand" είναι η ονομαζόμενη περιοχή C5: C11 και "ταξινόμηση" είναι η ονομαστική περιοχή D5: D11.
Εξήγηση
Αυτός ο τύπος εξαρτάται από δύο στήλες βοηθού. Η πρώτη στήλη βοηθού περιέχει τυχαίες τιμές που δημιουργήθηκαν με τη συνάρτηση RAND (). Ο τύπος στο C5, αντιγράφεται παρακάτω:
=RAND()
Η συνάρτηση RAND δημιουργεί μια τυχαία τιμή σε κάθε σειρά.
Σημείωση: Το RAND είναι μια πτητική συνάρτηση και θα δημιουργήσει νέες τιμές με κάθε αλλαγή φύλλου εργασίας.
Η δεύτερη στήλη βοηθού περιέχει τους αριθμούς που χρησιμοποιούνται για την ταξινόμηση δεδομένων, που δημιουργούνται με έναν τύπο. Ο τύπος στο D5 είναι:
=RANK(C5,rand)+COUNTIF($C$5:C5,C5)-1
Δείτε αυτήν τη σελίδα για μια εξήγηση αυτού του τύπου.
Ο τύπος στο E5 είναι:
=INDEX(names,MATCH(ROWS($D$5:$D5),sort,0))
Εδώ, η συνάρτηση INDEX χρησιμοποιείται για την ανάκτηση τιμών στο ονομασμένο εύρος "ονόματα", χρησιμοποιώντας τις τιμές ταξινόμησης στην ονομαζόμενη περιοχή "ταξινόμηση". Η πραγματική δουλειά του να καταλάβω τι αξία να ανακτήσω έχει γίνει η συνάρτηση MATCH σε αυτό το κομμάτι:
MATCH(ROWS($D$5:$D5),sort,0)
Μέσα στο MATCH, στη συνάρτηση ROWS δίνεται ένα επεκτεινόμενο εύρος ως η τιμή αναζήτησης, η οποία ξεκινά ως ένα κελί και επεκτείνεται καθώς ο τύπος αντιγράφεται στη στήλη. Αυτό αυξάνει την τιμή αναζήτησης, ξεκινώντας από το 1 και συνεχίζοντας σε 7. Η MATCH επιστρέφει στη συνέχεια τη θέση της τιμής αναζήτησης στη λίστα.
Η θέση τροφοδοτείται στο INDEX ως αριθμός σειράς και το INDEX ανακτά το όνομα σε αυτήν τη θέση.
