Γενική φόρμουλα
=SUMPRODUCT(1/COUNTIF(data,data))
Περίληψη
Για να μετρήσετε τον αριθμό των μοναδικών τιμών σε ένα εύρος κελιών, μπορείτε να χρησιμοποιήσετε έναν τύπο βασισμένο στις συναρτήσεις COUNTIF και SUMPRODUCT. Στο παράδειγμα δείχνουν, ο τύπος στο F6 είναι:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Εξήγηση
Λειτουργώντας από μέσα προς τα έξω, το COUNTIF έχει διαμορφωθεί σε τιμές στην περιοχή B5: B14, χρησιμοποιώντας όλες αυτές τις ίδιες τιμές ως κριτήρια:
COUNTIF(B5:B14,B5:B14)
Επειδή παρέχουμε 10 τιμές για κριτήρια, λαμβάνουμε έναν πίνακα με 10 αποτελέσματα όπως αυτό:
(3;3;3;2;2;3;3;3;2;2)
Κάθε αριθμός αντιπροσωπεύει μια μέτρηση - "Jim" εμφανίζεται 3 φορές, "Sue" εμφανίζεται 2 φορές, και ούτω καθεξής.
Αυτός ο πίνακας έχει διαμορφωθεί ως διαιρέτης με 1 ως αριθμητής Μετά τη διαίρεση, λαμβάνουμε έναν άλλο πίνακα:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Τυχόν τιμές που εμφανίζονται μόνο μία φορά στο εύρος θα εμφανίζονται ως 1s, αλλά οι τιμές που εμφανίζονται πολλές φορές θα εμφανίζονται ως κλασματικές τιμές που αντιστοιχούν στα πολλαπλάσια. (δηλαδή μια τιμή που εμφανίζεται 4 φορές στα δεδομένα θα δημιουργήσει 4 τιμές = 0,25).
Τέλος, η συνάρτηση SUMPRODUCT αθροίζει όλες τις τιμές στον πίνακα και επιστρέφει το αποτέλεσμα.
Χειρισμός κενών κελιών
Ένας τρόπος για να χειριστείτε κενά ή κενά κελιά είναι να προσαρμόσετε τον τύπο ως εξής:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Συνδυάζοντας μια κενή συμβολοσειρά ("") στα δεδομένα, εμποδίζουμε το μηδενικό να καταλήγει στον πίνακα που δημιουργήθηκε από το COUNTIF όταν υπάρχουν κενά κελιά στα δεδομένα. Αυτό είναι σημαντικό, επειδή το μηδέν στο διαιρέτη θα προκαλέσει τον τύπο να ρίξει ένα σφάλμα # DIV / 0. Λειτουργεί επειδή η χρήση μιας κενής συμβολοσειράς ("") για κριτήρια θα μετρήσει τα κενά κελιά.
Ωστόσο, παρόλο που αυτή η έκδοση του τύπου δεν θα εμφανίσει σφάλμα # DIV / 0 όταν με κενά κελιά, θα περιλαμβάνει κενά κελιά στο πλήθος. Εάν θέλετε να εξαιρέσετε κενά κελιά από τον αριθμό, χρησιμοποιήστε:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Αυτό έχει ως αποτέλεσμα την ακύρωση του αριθμού των κενών κελιών καθιστώντας τον αριθμητή μηδέν για τις σχετικές μετρήσεις.
Αργή απόδοση;
Αυτή είναι μια δροσερή και κομψή φόρμουλα, αλλά υπολογίζει πολύ πιο αργά από τους τύπους που χρησιμοποιούν FREQUENCY για να μετρήσουν μοναδικές τιμές. Για μεγαλύτερα σύνολα δεδομένων, μπορεί να θέλετε να μεταβείτε σε έναν τύπο που βασίζεται στη συνάρτηση FREQUENCY. Ακολουθεί ένας τύπος για αριθμητικές τιμές και ένας για τιμές κειμένου.
