Γενική φόρμουλα
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Περίληψη
Για να μετρήσετε μοναδικές τιμές κειμένου σε μια περιοχή με κριτήρια, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα που βασίζεται στις συναρτήσεις FREQUENCY και MATCH. Στο παράδειγμα που εμφανίζεται, ο τύπος στο G6 είναι:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
που επιστρέφει 3, καθώς τρεις διαφορετικοί άνθρωποι εργάστηκαν στο έργο Omega.
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter.
Εξήγηση
Αυτός είναι ένας σύνθετος τύπος που χρησιμοποιεί τη συχνότητα για να μετρήσει αριθμητικές τιμές που προέρχονται από τη συνάρτηση MATCH. Δουλεύοντας από μέσα προς τα έξω, η συνάρτηση MATCH χρησιμοποιείται για να πάρει τη θέση κάθε τιμής που εμφανίζεται στα δεδομένα:
MATCH(B5:B11,B5:B11,0)
Το αποτέλεσμα από το MATCH είναι ένας πίνακας ως εξής:
(1;1;3;1;1;6;7)
Επειδή το MATCH επιστρέφει πάντα τη θέση του πρώτου αγώνα, οι τιμές που εμφανίζονται περισσότερες από μία φορές στα δεδομένα επιστρέφουν στην ίδια θέση. Για παράδειγμα, επειδή το "Jim" εμφανίζεται 4 φορές στη λίστα, εμφανίζεται σε αυτόν τον πίνακα 4 φορές ως τον αριθμό 1.
Εκτός της συνάρτησης MATCH, η συνάρτηση IF χρησιμοποιείται για την εφαρμογή κριτηρίων, τα οποία σε αυτήν την περίπτωση περιλαμβάνουν δοκιμή εάν το έργο είναι "ωμέγα" (από το κελί G5):
IF(C5:C11=G5 // filter on "omega"
Η συνάρτηση IF λειτουργεί σαν φίλτρο, επιτρέποντας να περάσουν οι τιμές από το MATCH εάν σχετίζονται με το "ωμέγα". Το αποτέλεσμα είναι ένας πίνακας ως εξής:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Ο φιλτραρισμένος πίνακας παραδίδεται απευθείας στη συνάρτηση FREQUENCY ως όρισμα data_array . Στη συνέχεια, η συνάρτηση ROW χρησιμοποιείται για τη δημιουργία μιας διαδοχικής λίστας αριθμών για κάθε τιμή στα δεδομένα:
ROW(B3:B12)-ROW(B3)+1
Αυτό δημιουργεί έναν πίνακα όπως αυτό:
(1;2;3;4;5;6;7;8;9;10)
που γίνεται το όρισμα bins_array στο ΦΙΛΤΡΟ. Σε αυτό το σημείο, έχουμε:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY επιστρέφει έναν πίνακα αριθμών που υποδεικνύουν μια μέτρηση για κάθε τιμή στη συστοιχία δεδομένων, οργανωμένη από τον κάδο. Όταν ένας αριθμός έχει ήδη μετρηθεί, το FREQUENCY θα επιστρέψει μηδέν. Το αποτέλεσμα από το FREQUENCY είναι ένας πίνακας ως εξής:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Σημείωση: Το FREQUENCY επιστρέφει πάντα έναν πίνακα με ένα ακόμη στοιχείο από το bins_array .
Σε αυτό το σημείο, μπορούμε να ξαναγράψουμε τον τύπο ως εξής:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Ελέγχουμε τιμές μεγαλύτερες από μηδέν, οι οποίες μετατρέπουν τους αριθμούς σε ΑΛΗΘΕΙΑ ή ΛΑΘΟΣ:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Στη συνέχεια, χρησιμοποιούμε ένα διπλό αρνητικό για να εξαναγκάσουμε τις λογικές τιμές σε 1s και 0s:
=SUM((1;0;0;0;0;1;1;0))
Τέλος, η συνάρτηση SUM επιστρέφει 3 ως τελικό αποτέλεσμα.
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί χρησιμοποιώντας Control + Shift + Enter.
Χειρισμός κενών κελιών στην περιοχή
Εάν οποιαδήποτε κελιά στην περιοχή είναι κενά, θα πρέπει να προσαρμόσετε τον τύπο για να αποτρέψετε τη μεταφορά κενών κελιών στη λειτουργία MATCH, η οποία θα προκαλέσει σφάλμα. Μπορείτε να το κάνετε προσθέτοντας μια άλλη ένθετη συνάρτηση IF για να ελέγξετε για κενά κελιά:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Με δύο κριτήρια
Εάν έχετε δύο κριτήρια, μπορείτε να επεκτείνετε τη λογική του τύπου προσθέτοντας ένα άλλο ένθετο IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Όπου c1 = κριτήρια1, c2 = κριτήρια2 και vals = οι τιμές κυμαίνονται.
Με δυαδική λογική
Με τη δυαδική λογική, μπορείτε να μειώσετε τα ένθετα IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Αυτό διευκολύνει την προσθήκη και τη διαχείριση πρόσθετων κριτηρίων.
