Μετρήστε μοναδικές τιμές στο Excel χρησιμοποιώντας τη συνάρτηση COUNTIF

Σε αυτό το σεμινάριο, θα μάθετε πώς να μετράτε μοναδικές τιμές στο Excel χρησιμοποιώντας τύπους (συναρτήσεις COUNTIF και SUMPRODUCT).

Πώς να μετρήσετε μοναδικές τιμές στο Excel

Ας υποθέσουμε ότι έχουμε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:

Για τους σκοπούς αυτού του σεμιναρίου, θα ονομάσω το εύρος A2: A10 ως ΟΝΟΜΑΤΑ. Προχωρώντας θα χρησιμοποιήσουμε αυτό το ονομαστικό εύρος στους τύπους.

Δείτε επίσης: Πώς να δημιουργήσετε ονόματα εύρους στο Excel.

Σε αυτό το σύνολο δεδομένων, υπάρχει μια επανάληψη στο εύρος NAMES. Για να λάβουμε τον αριθμό των μοναδικών ονομάτων από αυτό το σύνολο δεδομένων (A2: A10), μπορούμε να χρησιμοποιήσουμε έναν συνδυασμό συναρτήσεων COUNTIF και SUMPRODUCT, όπως φαίνεται παρακάτω:

= SUMPRODUCT (1/COUNTIF (ΟΝΟΜΑΤΑ, ΟΝΟΜΑΤΑ))

Πώς λειτουργεί αυτός ο τύπος;

Ας σπάσουμε αυτόν τον τύπο για καλύτερη κατανόηση:

• COUNTIF (ΟΝΟΜΑΤΑ, ΟΝΟΜΑΤΑ)
  • Αυτό το μέρος του τύπου επιστρέφει έναν πίνακα. Στο παραπάνω παράδειγμα, θα ήταν {2; 2; 3; 1; 3; 1; 2; 3; 2}. Οι αριθμοί εδώ υποδεικνύουν πόσες φορές εμφανίζεται μια τιμή στο δεδομένο εύρος κελιών.
    Για παράδειγμα, το όνομα είναι Bob, το οποίο εμφανίζεται δύο φορές στη λίστα, επομένως θα επιστρέψει τον αριθμό 2 για τον Bob. Ομοίως, ο Steve εμφανίζεται τρεις φορές και ως εκ τούτου το 3 επιστρέφεται για τον Steve.
• 1/COUNTIF (ΟΝΟΜΑΤΑ, ΟΝΟΜΑΤΑ)
  • Αυτό το μέρος του τύπου θα επέστρεφε έναν πίνακα - {0,5; 0,5; 0,3333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333
    Δεδομένου ότι έχουμε διαιρέσει το 1 με τον πίνακα, επιστρέφει αυτόν τον πίνακα.
    Για παράδειγμα, το πρώτο στοιχείο του πίνακα που επιστρέφεται παραπάνω ήταν 2. Όταν το 1 διαιρείται με το 2, επιστρέφει .5.
• SUMPRODUCT (1/COUNTIF (ΟΝΟΜΑΤΑ, ΟΝΟΜΑΤΑ))
  • Το SUMPRODUCT προσθέτει απλώς όλους αυτούς τους αριθμούς. Σημειώστε ότι εάν ο Bob εμφανιστεί δύο φορές στη λίστα, ο παραπάνω πίνακας επιστρέφει .5 όπου και να εμφανίστηκε το όνομα Bob στη λίστα. Ομοίως, αφού ο Steve εμφανίζεται τρεις φορές στη λίστα, ο πίνακας επιστρέφει .3333333 όποτε εμφανίζεται το όνομα του Steve. Όταν προσθέτουμε τους αριθμούς για κάθε όνομα, θα επιστρέφει πάντα 1. Και αν προσθέσουμε όλους τους αριθμούς, θα επιστρέψει τον συνολικό αριθμό των μοναδικών ονομάτων στη λίστα.

Αυτός ο τύπος λειτουργεί καλά μέχρι να μην έχετε κενά κελιά στην περιοχή. Αλλά αν έχετε κενά κελιά, θα επιστρέψει ένα #DIV/0! λάθος.

Πώς να χειριστείτε τα ΜΕΛΑ κελιά;

Ας καταλάβουμε πρώτα γιατί επιστρέφει ένα σφάλμα όταν υπάρχει ένα κενό κελί στην περιοχή. Ας υποθέσουμε ότι έχουμε το σύνολο δεδομένων όπως φαίνεται παρακάτω (με το κελί Α3 να είναι κενό):

Εάν χρησιμοποιούμε τον ίδιο τύπο που χρησιμοποιήσαμε παραπάνω, το τμήμα COUNTIF του τύπου επιστρέφει έναν πίνακα {2; 0; 3; 1; 3; 1; 2; 3; 1}. Δεδομένου ότι δεν υπάρχει κείμενο στο κελί A3, ο αριθμός του επιστρέφεται ως 0.

Και επειδή διαιρούμε το 1 με ολόκληρο αυτόν τον πίνακα, επιστρέφει ένα #DIV/0! λάθος.

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

= SUMPRODUCT ((1/COUNTIF (NAMES, NAMES & ””)))

Μια αλλαγή που κάναμε σε αυτόν τον τύπο είναι το μέρος κριτηρίων της συνάρτησης COUNTIF. Χρησιμοποιήσαμε NAMES & ”” αντί για NAMES. Με αυτόν τον τρόπο, ο τύπος θα επιστρέψει τον αριθμό των κενών κελιών (νωρίτερα επέστρεψε 0 όπου υπήρχε ένα κενό κελί).

ΣΗΜΕΙΩΣΗ: Αυτός ο τύπος θα υπολογίζει τα κενά κελιά ως μοναδική τιμή και θα την επιστρέφει στο αποτέλεσμα.

Στο παραπάνω παράδειγμα, το αποτέλεσμα πρέπει να είναι 5, αλλά επιστρέφει 6 καθώς το κενό κελί υπολογίζεται ως μία από τις μοναδικές τιμές.

Ακολουθεί ο τύπος που φροντίζει τα κενά κελιά και δεν τα υπολογίζει στο τελικό αποτέλεσμα:

= SUMPRODUCT ((ΟΝΟΜΑΤΑ "")/COUNTIF (ΟΝΟΜΑΤΑ, ΟΝΟΜΑΤΑ & ""))

Σε αυτόν τον τύπο, αντί του 1 ως αριθμητή, χρησιμοποιήσαμε ΟΝΟΜΑΤΑ ”». Αυτό επιστρέφει μια σειρά TRUE και FALSEs. Επιστρέφει ΛΑΘΟΣ κάθε φορά που υπάρχει κενό κελί. Δεδομένου ότι το TRUE ισούται με 1 και το FALSE ισούται με 0 στους υπολογισμούς, τα κενά κελιά δεν υπολογίζονται καθώς ο αριθμητής είναι 0 (FALSE).

Τώρα που έχουμε έτοιμο τον βασικό σκελετό του τύπου, μπορούμε να προχωρήσουμε ένα βήμα παραπέρα και να μετρήσουμε διαφορετικούς τύπους δεδομένων.

Πώς να μετρήσετε μοναδικές τιμές στο Excel που είναι κείμενο

Θα χρησιμοποιήσουμε την ίδια έννοια που συζητήθηκε παραπάνω για να δημιουργήσουμε τον τύπο που θα μετρά μόνο τις μοναδικές τιμές κειμένου.

Ακολουθεί ο τύπος που θα μετρήσει μοναδικές τιμές κειμένου στο Excel:

= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))

Το μόνο που έχουμε κάνει είναι να χρησιμοποιήσουμε τον τύπο ISTEXT (ΟΝΟΜΑΤΑ) ως αριθμητή. Επιστρέφει TRUE όταν το κελί περιέχει κείμενο και FALSE αν δεν περιέχει. Δεν θα μετρήσει κενά κελιά, αλλά θα μετρήσει κελιά που έχουν κενή συμβολοσειρά ("").

Πώς να μετρήσετε μοναδικές τιμές στο Excel που είναι αριθμητικές

Εδώ είναι ο τύπος που θα μετρήσει μοναδικές αριθμητικές τιμές στο Excel

= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))

Εδώ, χρησιμοποιούμε το ISNUMBER (NAMES) ως αριθμητή. Επιστρέφει TRUE όταν το κελί περιέχει αριθμητικούς τύπους δεδομένων και FALSE αν δεν περιέχει. Δεν μετρά κενά κελιά.