Το Excel έχει πολλές λειτουργίες όπου ένας χρήστης πρέπει να καθορίσει ένα ή περισσότερα κριτήρια για να πάρει το αποτέλεσμα. Για παράδειγμα, εάν θέλετε να μετρήσετε κελιά με πολλαπλά κριτήρια, μπορείτε να χρησιμοποιήσετε τις συναρτήσεις COUNTIF ή COUNTIFS στο Excel.
Αυτό το σεμινάριο καλύπτει διάφορους τρόπους χρήσης ενός ή πολλαπλών κριτηρίων στη λειτουργία COUNTIF και COUNTIFS στο Excel.
Ενώ θα εστιάσω κυρίως σε συναρτήσεις COUNTIF και COUNTIFS σε αυτό το σεμινάριο, όλα αυτά τα παραδείγματα μπορούν επίσης να χρησιμοποιηθούν σε άλλες λειτουργίες του Excel που λαμβάνουν πολλαπλά κριτήρια ως εισόδους (όπως SUMIF, SUMIFS, AVERAGEIF και AVERAGEIFS).
Εισαγωγή στις λειτουργίες Excel COUNTIF και COUNTIFS
Ας καταλάβουμε πρώτα τη χρήση των συναρτήσεων COUNTIF και COUNTIFS στο Excel.
Συνάρτηση Excel COUNTIF (λαμβάνει μεμονωμένα κριτήρια)
Η συνάρτηση Excel COUNTIF είναι η πλέον κατάλληλη για καταστάσεις όταν θέλετε να μετρήσετε κελιά με βάση ένα μόνο κριτήριο. Εάν θέλετε να μετράτε βάσει πολλαπλών κριτηρίων, χρησιμοποιήστε τη συνάρτηση COUNTIFS.
Σύνταξη
= COUNTIF (εύρος, κριτήρια)
Ορίσματα εισόδου
- εύρος - το εύρος των κελιών που θέλετε να μετρήσετε.
- κριτήρια - τα κριτήρια που πρέπει να αξιολογηθούν με βάση το εύρος των κυττάρων για να μετρηθεί ένα κύτταρο.
Συνάρτηση Excel COUNTIFS (λαμβάνει πολλαπλά κριτήρια)
Η συνάρτηση Excel COUNTIFS είναι η πλέον κατάλληλη για καταστάσεις όταν θέλετε να μετρήσετε κελιά βάσει πολλαπλών κριτηρίων.
Σύνταξη
= COUNTIFS (κριτήριο_περιοχή1, κριτήριο1, [κριτήριο_διάγραμμα2, κριτήριο2]…)
Ορίσματα εισόδου
- κριτήριο_ζώνης1 - Το εύρος των κελιών για τα οποία θέλετε να αξιολογήσετε με κριτήρια
- κριτήρια 1 - τα κριτήρια που θέλετε να αξιολογήσετε για το κριτήριο_ range1 για να καθορίσετε ποια κελιά θα μετρήσετε.
- [κριτήριο_περιοχής2] - Το εύρος των κελιών για τα οποία θέλετε να αξιολογήσετε με κριτήρια2.
- [κριτήρια2] - τα κριτήρια που θέλετε να αξιολογήσετε για το κριτήριο_ range2 για να καθορίσετε ποια κελιά θα μετρήσετε.
Τώρα ας ρίξουμε μια ματιά σε μερικά παραδείγματα χρήσης πολλαπλών κριτηρίων σε συναρτήσεις COUNTIF στο Excel.
Χρήση NUMBER κριτηρίων σε λειτουργίες COUNTIF του Excel
#1 Μετρήστε κελιά όταν τα κριτήρια είναι ΙΣΑ με μια τιμή
Για να λάβετε τον αριθμό των κελιών όπου το όρισμα κριτηρίου είναι ίσο με μια καθορισμένη τιμή, μπορείτε είτε να εισαγάγετε απευθείας τα κριτήρια είτε να χρησιμοποιήσετε την αναφορά κελιού που περιέχει τα κριτήρια.
Παρακάτω είναι ένα παράδειγμα όπου μετράμε τα κελιά που περιέχουν τον αριθμό 9 (που σημαίνει ότι το όρισμα κριτηρίων είναι ίσο με 9). Εδώ είναι ο τύπος:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Στο παραπάνω παράδειγμα (στην εικόνα), τα κριτήρια είναι στο κελί D3. Μπορείτε επίσης να εισαγάγετε τα κριτήρια απευθείας στον τύπο. Για παράδειγμα, μπορείτε επίσης να χρησιμοποιήσετε:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Μετρήστε κελιά όταν τα κριτήρια είναι μεγαλύτερα από μια τιμή
Για να λάβουμε τον αριθμό των κελιών με τιμή μεγαλύτερη από μια καθορισμένη τιμή, χρησιμοποιούμε τον τελεστή μεγαλύτερο από (">"). Θα μπορούσαμε είτε να το χρησιμοποιήσουμε απευθείας στον τύπο είτε να χρησιμοποιήσουμε μια αναφορά κελιού που έχει τα κριτήρια.
Κάθε φορά που χρησιμοποιούμε έναν τελεστή σε κριτήρια στο Excel, πρέπει να τον θέσουμε σε διπλά εισαγωγικά. Για παράδειγμα, εάν τα κριτήρια είναι μεγαλύτερα από 10, τότε πρέπει να εισαγάγουμε ως κριτήρια το "> 10" (δείτε την παρακάτω εικόνα):
Εδώ είναι ο τύπος:
= COUNTIF ($ B $ 2: $ B $ 11, "> 10 ″)
Μπορείτε επίσης να έχετε τα κριτήρια σε ένα κελί και να χρησιμοποιήσετε την αναφορά κελιού ως κριτήρια. Σε αυτή την περίπτωση, ΔΕΝ χρειάζεται να βάλετε τα κριτήρια σε διπλά εισαγωγικά:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Θα μπορούσε επίσης να υπάρξει περίπτωση που θέλετε τα κριτήρια να είναι σε ένα κελί, αλλά δεν το θέλετε με τον χειριστή. Για παράδειγμα, μπορεί να θέλετε το κελί D3 να έχει τον αριθμό 10 και όχι> 10.
Σε αυτή την περίπτωση, πρέπει να δημιουργήσετε ένα όρισμα κριτηρίων που είναι ένας συνδυασμός αναφοράς τελεστή και κελιού (δείτε την παρακάτω εικόνα):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
ΣΗΜΕΙΩΣΗ: Όταν συνδυάζετε έναν τελεστή και μια αναφορά κελιού, ο τελεστής είναι πάντα σε διπλά εισαγωγικά. Ο χειριστής και η αναφορά κυψέλης ενώνονται με ένα ενδιάμεσο πλάσμα (&).
#3 Μετρήστε κελιά όταν τα κριτήρια είναι λιγότερα από μια τιμή
Για να λάβουμε τον αριθμό των κελιών με τιμή μικρότερη από μια καθορισμένη τιμή, χρησιμοποιούμε τον τελεστή λιγότερο από το ("<"). Θα μπορούσαμε είτε να το χρησιμοποιήσουμε απευθείας στον τύπο είτε να χρησιμοποιήσουμε μια αναφορά κελιού που έχει τα κριτήρια.
Κάθε φορά που χρησιμοποιούμε έναν τελεστή σε κριτήρια στο Excel, πρέπει να τον θέσουμε σε διπλά εισαγωγικά. Για παράδειγμα, εάν το κριτήριο είναι ότι ο αριθμός πρέπει να είναι μικρότερος από 5, τότε πρέπει να εισαγάγουμε ως κριτήρια το "<5" (βλ. Εικόνα παρακάτω):
= COUNTIF ($ B $ 2: $ B $ 11, "<5 ″)
Μπορείτε επίσης να έχετε τα κριτήρια σε ένα κελί και να χρησιμοποιήσετε την αναφορά κελιού ως κριτήρια. Σε αυτή την περίπτωση, ΔΕΝ χρειάζεται να βάλετε τα κριτήρια σε διπλά εισαγωγικά (δείτε την παρακάτω εικόνα):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Επίσης, θα μπορούσε να υπάρξει περίπτωση που θέλετε τα κριτήρια να είναι σε ένα κελί, αλλά δεν το θέλετε με τον χειριστή. Για παράδειγμα, μπορεί να θέλετε το κελί D3 να έχει τον αριθμό 5 και όχι <5.
Σε αυτή την περίπτωση, πρέπει να δημιουργήσετε ένα όρισμα κριτηρίων που είναι ένας συνδυασμός αναφοράς τελεστή και κελιού:
= COUNTIF ($ B $ 2: $ B $ 11, "<" & D3)
ΣΗΜΕΙΩΣΗ: Όταν συνδυάζετε έναν τελεστή και μια αναφορά κελιού, ο τελεστής είναι πάντα σε διπλά εισαγωγικά. Ο χειριστής και η αναφορά κυψέλης ενώνονται με ένα ενδιάμεσο πλάσμα (&).
#4 Μετρήστε κελιά με πολλαπλά κριτήρια - μεταξύ δύο τιμών
Για να λάβουμε έναν αριθμό τιμών μεταξύ δύο τιμών, πρέπει να χρησιμοποιήσουμε πολλαπλά κριτήρια στη συνάρτηση COUNTIF.
Ακολουθούν δύο μέθοδοι για να το κάνετε αυτό:
ΜΕΘΟΔΟΣ 1: Χρήση της συνάρτησης COUNTIFS
Η συνάρτηση COUNTIFS μπορεί να χειριστεί πολλαπλά κριτήρια ως ορίσματα και μετράει τα κελιά μόνο όταν όλα τα κριτήρια είναι ΑΛΗΘΕΙΑ. Για να μετρήσουμε κελιά με τιμές μεταξύ δύο καθορισμένων τιμών (ας πούμε 5 και 10), μπορούμε να χρησιμοποιήσουμε την ακόλουθη συνάρτηση COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11 ",> 5 ″, $ B $ 2: $ B $ 11," <10 ″)
ΣΗΜΕΙΩΣΗ: Ο παραπάνω τύπος δεν μετράει κελιά που περιέχουν 5 ή 10. Εάν θέλετε να συμπεριλάβετε αυτά τα κελιά, χρησιμοποιήστε τελεστές μεγαλύτερους από ίσους προς (> =) και μικρότερους από ίσους με (<=). Εδώ είναι ο τύπος:
= COUNTIFS ($ B $ 2: $ B $ 11, "> = 5 ″, $ B $ 2: $ B $ 11," <= 10 ″)
Μπορείτε επίσης να έχετε αυτά τα κριτήρια σε κελιά και να χρησιμοποιήσετε την αναφορά κελιού ως κριτήρια. Σε αυτή την περίπτωση, ΔΕΝ χρειάζεται να βάλετε τα κριτήρια σε διπλά εισαγωγικά (δείτε την παρακάτω εικόνα):
Μπορείτε επίσης να χρησιμοποιήσετε έναν συνδυασμό αναφορών κελιών και τελεστών (όπου ο τελεστής εισάγεται απευθείας στον τύπο). Όταν συνδυάζετε έναν τελεστή και μια αναφορά κελιού, ο τελεστής είναι πάντα σε διπλά εισαγωγικά. Ο χειριστής και η αναφορά κυψέλης ενώνονται με ένα ενδιάμεσο πλάσμα (&).
ΜΕΘΟΔΟΣ 2: Χρήση δύο συναρτήσεων COUNTIF
Εάν έχετε πολλά κριτήρια, μπορείτε είτε να χρησιμοποιήσετε COUNTIFS είτε να δημιουργήσετε έναν συνδυασμό συναρτήσεων COUNTIF. Ο παρακάτω τύπος θα έκανε επίσης το ίδιο πράγμα:
= COUNTIF ($ B $ 2: $ B $ 11, "> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,"> 10 ″)
Στον παραπάνω τύπο, βρίσκουμε πρώτα τον αριθμό των κελιών που έχουν τιμή μεγαλύτερη από 5 και αφαιρούμε τον αριθμό των κελιών με τιμή μεγαλύτερη από 10. Αυτό θα μας έδινε το αποτέλεσμα ως 5 (που είναι ο αριθμός των κελιών που έχουν τιμές πάνω από 5 και μικρότερες από ίσες με 10).
Εάν θέλετε ο τύπος να περιλαμβάνει και τα 5 και τα 10, χρησιμοποιήστε αντ 'αυτού τον ακόλουθο τύπο:
= COUNTIF ($ B $ 2: $ B $ 11, "> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,"> 10 ″)
Εάν θέλετε ο τύπος να εξαιρεί τόσο το '5' και το '10' από τη μέτρηση, χρησιμοποιήστε τον ακόλουθο τύπο:
= COUNTIF ($ B $ 2: $ B $ 11, "> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,"> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Μπορείτε να έχετε αυτά τα κριτήρια σε κελιά και να χρησιμοποιήσετε τις αναφορές κελιών ή μπορείτε να χρησιμοποιήσετε ένα συνδυασμό αναφορών τελεστών και κελιών.
Χρήση κριτηρίων TEXT σε λειτουργίες Excel
#1 Καταμετρήστε κελιά όταν τα κριτήρια είναι ΙΣΑ με ένα καθορισμένο κείμενο
Για να μετρήσουμε κελιά που περιέχουν ακριβή αντιστοίχιση του καθορισμένου κειμένου, μπορούμε απλά να χρησιμοποιήσουμε αυτό το κείμενο ως κριτήρια. Για παράδειγμα, στο σύνολο δεδομένων (φαίνεται παρακάτω στην εικόνα), αν θέλω να μετρήσω όλα τα κελιά με το όνομα Joe σε αυτό, μπορώ να χρησιμοποιήσω τον παρακάτω τύπο:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Δεδομένου ότι πρόκειται για συμβολοσειρά κειμένου, πρέπει να βάλω τα κριτήρια κειμένου σε διπλά εισαγωγικά.
Μπορείτε επίσης να έχετε τα κριτήρια σε ένα κελί και στη συνέχεια να χρησιμοποιήσετε αυτήν την αναφορά κελιού (όπως φαίνεται παρακάτω):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
ΣΗΜΕΙΩΣΗ: Μπορείτε να λάβετε λανθασμένα αποτελέσματα εάν υπάρχουν κεντρικοί/πίσω χώροι στο εύρος κριτηρίων ή κριτηρίων. Βεβαιωθείτε ότι έχετε καθαρίσει τα δεδομένα πριν χρησιμοποιήσετε αυτούς τους τύπους.
#2 Μετρήστε κελιά όταν τα κριτήρια δεν είναι ίσα με ένα καθορισμένο κείμενο
Παρόμοια με αυτό που είδαμε στο παραπάνω παράδειγμα, μπορείτε επίσης να μετρήσετε κελιά που δεν περιέχουν καθορισμένο κείμενο. Για να γίνει αυτό, πρέπει να χρησιμοποιήσουμε το μη ίσο με τον τελεστή ().
Ας υποθέσουμε ότι θέλετε να μετρήσετε όλα τα κελιά που δεν περιέχουν το όνομα JOE, εδώ είναι ο τύπος που θα το κάνει:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Μπορείτε επίσης να έχετε τα κριτήρια σε ένα κελί και να χρησιμοποιήσετε την αναφορά κελιού ως κριτήρια. Σε αυτή την περίπτωση, ΔΕΝ χρειάζεται να βάλετε τα κριτήρια σε διπλά εισαγωγικά (δείτε την παρακάτω εικόνα):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Θα μπορούσε επίσης να υπάρξει περίπτωση που θέλετε τα κριτήρια να είναι σε ένα κελί αλλά δεν τα θέλετε με τον χειριστή. Για παράδειγμα, μπορεί να θέλετε το κελί D3 να έχει το όνομα Joe και όχι Joe.
Σε αυτή την περίπτωση, πρέπει να δημιουργήσετε ένα όρισμα κριτηρίων που είναι ένας συνδυασμός αναφοράς τελεστή και κελιού (δείτε την παρακάτω εικόνα):
= COUNTIF ($ B $ 2: $ B $ 11, "" & E3)
Όταν συνδυάζετε έναν τελεστή και μια αναφορά κελιού, ο τελεστής είναι πάντα σε διπλά εισαγωγικά. Ο χειριστής και η αναφορά κυψέλης ενώνονται με ένα ενδιάμεσο πλάσμα (&).
Χρήση κριτηρίων DATE στις λειτουργίες Excel COUNTIF και COUNTIFS
Το Excel αποθηκεύει ημερομηνία και ώρα ως αριθμούς. Μπορούμε λοιπόν να το χρησιμοποιήσουμε με τον ίδιο τρόπο που χρησιμοποιούμε τους αριθμούς.
#1 Καταμετρήστε κελιά όταν τα κριτήρια είναι ΙΣΑ σε μια καθορισμένη ημερομηνία
Για να λάβουμε τον αριθμό των κελιών που περιέχουν την καθορισμένη ημερομηνία, θα χρησιμοποιούσαμε το ίσο με τον τελεστή (=) μαζί με την ημερομηνία.
Για να χρησιμοποιήσετε την ημερομηνία, συνιστώ να χρησιμοποιήσετε τη συνάρτηση DATE, καθώς απαλλάσσεται από κάθε πιθανό σφάλμα στην τιμή ημερομηνίας. Έτσι, για παράδειγμα, εάν θέλω να χρησιμοποιήσω την ημερομηνία 1 Σεπτεμβρίου 2015, μπορώ να χρησιμοποιήσω τη συνάρτηση DATE όπως φαίνεται παρακάτω:
= DATE (2015,9,1)
Αυτός ο τύπος θα επέστρεφε την ίδια ημερομηνία παρά τις περιφερειακές διαφορές. Για παράδειγμα, 01-09-2015 θα ήταν η 1η Σεπτεμβρίου 2015 σύμφωνα με τη σύνταξη ημερομηνίας στις ΗΠΑ και 09 Ιανουαρίου 2015 σύμφωνα με τη σύνταξη ημερομηνίας στο Ηνωμένο Βασίλειο. Ωστόσο, αυτός ο τύπος θα επέστρεφε πάντα την 1η Σεπτεμβρίου 2105.
Ακολουθεί ο τύπος για να μετρήσετε τον αριθμό των κελιών που περιέχουν την ημερομηνία 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Μετρήστε κελιά όταν τα κριτήρια είναι ΠΡΙΝ ή ΜΕΤΑ από μια καθορισμένη ημερομηνία
Για να μετρήσουμε κελιά που περιέχουν ημερομηνία πριν ή μετά από μια καθορισμένη ημερομηνία, μπορούμε να χρησιμοποιήσουμε τους τελικούς λιγότερους από/μεγαλύτερους από.
Για παράδειγμα, εάν θέλω να μετρήσω όλα τα κελιά που περιέχουν ημερομηνία μετά την 02 Σεπτεμβρίου 2015, μπορώ να χρησιμοποιήσω τον τύπο:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
Ομοίως, μπορείτε επίσης να μετρήσετε τον αριθμό των κελιών πριν από μια καθορισμένη ημερομηνία. Εάν θέλετε να συμπεριλάβετε μια ημερομηνία στην καταμέτρηση, χρησιμοποιήστε τον τελεστή "ίσο με" μαζί με τον τελεστή "μεγαλύτερο από/μικρότερο από".
Μπορείτε επίσης να χρησιμοποιήσετε μια αναφορά κελιού που περιέχει μια ημερομηνία. Σε αυτήν την περίπτωση, πρέπει να συνδυάσετε τον τελεστή (εντός διπλών εισαγωγικών) με την ημερομηνία χρησιμοποιώντας ένα αμπερόσημα (&).
Δείτε το παράδειγμα παρακάτω:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & F3)
#3 Μετρήστε κελιά με πολλαπλά κριτήρια - μεταξύ δύο ημερομηνιών
Για να λάβουμε έναν αριθμό τιμών μεταξύ δύο τιμών, πρέπει να χρησιμοποιήσουμε πολλαπλά κριτήρια στη συνάρτηση COUNTIF.
Μπορούμε να το κάνουμε χρησιμοποιώντας δύο μεθόδους - Μία μόνο συνάρτηση COUNTIFS ή δύο συναρτήσεις COUNTIF.
ΜΕΘΟΔΟΣ 1: Χρήση της συνάρτησης COUNTIFS
Η συνάρτηση COUNTIFS μπορεί να λάβει πολλαπλά κριτήρια ως ορίσματα και μετράει τα κελιά μόνο όταν όλα τα κριτήρια είναι ΑΛΗΘΕΙΑ. Για να μετρήσουμε κελιά με τιμές μεταξύ δύο καθορισμένων ημερομηνιών (ας πούμε 2 Σεπτεμβρίου και 7 Σεπτεμβρίου), μπορούμε να χρησιμοποιήσουμε την ακόλουθη συνάρτηση COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2), $ A $ 2: $ A $ 11, "<" & DATE (2015,9,7))
Ο παραπάνω τύπος δεν μετρά τα κελιά που περιέχουν τις καθορισμένες ημερομηνίες. Εάν θέλετε να συμπεριλάβετε και αυτές τις ημερομηνίες, χρησιμοποιήστε τελεστές μεγαλύτερους από ίσο με (> =) και μικρότερους από ίσους με (<=). Εδώ είναι ο τύπος:
= COUNTIFS ($ A $ 2: $ A $ 11, "> =" & DATE (2015,9,2), $ A $ 2: $ A $ 11, "<=" & DATE (2015,9,7))
Μπορείτε επίσης να έχετε τις ημερομηνίες σε ένα κελί και να χρησιμοποιήσετε την αναφορά κελιού ως κριτήριο. Σε αυτήν την περίπτωση, δεν μπορείτε να έχετε τον τελεστή με την ημερομηνία στα κελιά. Πρέπει να προσθέσετε χειροκίνητα τελεστές στον τύπο (σε διπλά εισαγωγικά) και να προσθέσετε αναφορά κελιού χρησιμοποιώντας ένα αμπερσάντερ (&). Δείτε την παρακάτω φωτογραφία:
= COUNTIFS ($ A $ 2: $ A $ 11, ">" & F3, $ A $ 2: $ A $ 11, "<" & G3)
ΜΕΘΟΔΟΣ 2: Χρήση συναρτήσεων COUNTIF
Εάν έχετε πολλά κριτήρια, μπορείτε είτε να χρησιμοποιήσετε μία συνάρτηση COUNTIFS είτε να δημιουργήσετε έναν συνδυασμό δύο συναρτήσεων COUNTIF. Ο παρακάτω τύπος θα κάνει επίσης το κόλπο:
= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7))
Στον παραπάνω τύπο, βρίσκουμε πρώτα τον αριθμό των κελιών που έχουν ημερομηνία μετά τις 2 Σεπτεμβρίου και αφαιρούμε τον αριθμό των κελιών με ημερομηνίες μετά τις 7 Σεπτεμβρίου. Αυτό θα μας έδινε το αποτέλεσμα ως 7 (που είναι ο αριθμός των κελιών που έχουν ημερομηνίες μετά τις 2 Σεπτεμβρίου και επάνω ή πριν από τις 7 Σεπτεμβρίου).
Εάν δεν θέλετε ο τύπος να υπολογίζεται τόσο στις 2 Σεπτεμβρίου όσο και στις 7 Σεπτεμβρίου, χρησιμοποιήστε τον ακόλουθο τύπο αντ 'αυτού:
= COUNTIF ($ A $ 2: $ A $ 11, "> =" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7))
Εάν θέλετε να εξαιρέσετε και τις δύο ημερομηνίες από την καταμέτρηση, χρησιμοποιήστε τον ακόλουθο τύπο:
= COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ">" & DATE (2015,9,7) -COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,7)))
Επίσης, μπορείτε να έχετε τις ημερομηνίες κριτηρίων στα κελιά και να χρησιμοποιήσετε τις αναφορές κελιών (μαζί με τους τελεστές σε διπλά εισαγωγικά που ενώνονται χρησιμοποιώντας το αμπερσάντερ).
Χρήση WILDCARD CHARACTERS στα Κριτήρια στις συναρτήσεις COUNTIF & COUNTIFS
Υπάρχουν τρεις χαρακτήρες μπαλαντέρ στο Excel:
- * (αστερίσκος) - Αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων. Για παράδειγμα, ex* θα μπορούσε να σημαίνει excel, υπερέχει, παράδειγμα, εμπειρογνώμονας κ.λπ.
- ; (ερωτηματικό) - Αντιπροσωπεύει έναν μόνο χαρακτήρα. Για παράδειγμα, Tr? Mp θα μπορούσε να σημαίνει Trump ή Tramp.
- (tilde) - Χρησιμοποιείται για τον προσδιορισμό ενός χαρακτήρα μπαλαντέρ (~, *,?) Στο κείμενο.
Μπορείτε να χρησιμοποιήσετε τη συνάρτηση COUNTIF με χαρακτήρες μπαλαντέρ για να μετρήσετε κελιά όταν αποτύχει άλλη ενσωματωμένη λειτουργία καταμέτρησης. Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω:
Τώρα ας πάρουμε διάφορα παραδείγματα:
#1 Μετρήστε κελιά που περιέχουν κείμενο
Για να μετρήσουμε κελιά με κείμενο, μπορούμε να χρησιμοποιήσουμε τον χαρακτήρα μπαλαντέρ * (αστερίσκο). Δεδομένου ότι ο αστερίσκος αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων, θα μετράει όλα τα κελιά που έχουν οποιοδήποτε κείμενο σε αυτόν. Εδώ είναι ο τύπος:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Σημείωση: Ο παραπάνω τύπος αγνοεί κελιά που περιέχουν αριθμούς, κενά κελιά και λογικές τιμές, αλλά θα μετράει τα κελιά που περιέχουν απόστροφο (και ως εκ τούτου εμφανίζονται κενά) ή κελιά που περιέχουν κενή συμβολοσειρά (= ””) τα οποία ενδέχεται να έχουν επιστραφεί ως μέρος ενός τύπου.
Ακολουθεί ένα αναλυτικό σεμινάριο για το χειρισμό περιπτώσεων όπου υπάρχει κενή συμβολοσειρά ή απόστροφο.
Εδώ είναι ένα λεπτομερές σεμινάριο για το χειρισμό περιπτώσεων όπου υπάρχουν κενές συμβολοσειρές ή απόστροφες.
Παρακάτω είναι ένα βίντεο που εξηγεί διαφορετικά σενάρια καταμέτρησης κελιών με κείμενο σε αυτό.
#2 Μετρήστε μη κενά κελιά
Εάν σκέφτεστε να χρησιμοποιήσετε τη λειτουργία COUNTA, ξανασκεφτείτε το.
Δοκιμάστε το και μπορεί να σας αποτύχει. Το COUNTA θα μετρήσει επίσης ένα κελί που περιέχει μια κενή συμβολοσειρά (συχνά επιστρέφεται με τύπους ως = ”” ή όταν οι άνθρωποι εισάγουν μόνο απόστροφο σε ένα κελί). Τα κελιά που περιέχουν κενές συμβολοσειρές φαίνονται κενά αλλά δεν είναι, και έτσι καταμετρώνται από τη συνάρτηση COUNTA.
Το COUNTA θα μετρήσει επίσης ένα κελί που περιέχει μια κενή συμβολοσειρά (συχνά επιστρέφεται με τύπους ως = ”” ή όταν οι άνθρωποι εισάγουν μόνο απόστροφο σε ένα κελί). Τα κελιά που περιέχουν κενές συμβολοσειρές φαίνονται κενά αλλά δεν είναι, και έτσι καταμετρώνται από τη συνάρτηση COUNTA.
Έτσι, εάν χρησιμοποιείτε τον τύπο = COUNTA (A1: A11), επιστρέφει 11, ενώ θα πρέπει να επιστρέψει 10.
Εδώ είναι η διόρθωση:
= COUNTIF ($ A $ 1: $ A $ 11, "?*")+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Ας κατανοήσουμε αυτόν τον τύπο αναλύοντας τον:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Αυτό το μέρος του τύπου επιστρέφει 5. Αυτό περιλαμβάνει οποιοδήποτε κελί έχει χαρακτήρα κειμένου. ΕΝΑ ? αντιπροσωπεύει έναν χαρακτήρα και * αντιπροσωπεύει οποιονδήποτε αριθμό χαρακτήρων. Ως εκ τούτου, ο συνδυασμός?* Στα κριτήρια αναγκάζει το excel να μετράει κελιά που έχουν τουλάχιστον έναν χαρακτήρα κειμένου.
- COUNT ($ A $ 1: $ A $ 11) - Μετράει όλα τα κελιά που περιέχουν αριθμούς. Στο παραπάνω παράδειγμα, επιστρέφει 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11) - Αυτό μετράει όλα τα κελιά που περιέχουν λογικές τιμές. Στο παραπάνω παράδειγμα, επιστρέφει 2.
#3 Καταμέτρηση κελιών που περιέχουν συγκεκριμένο κείμενο
Ας υποθέσουμε ότι θέλουμε να μετρήσουμε όλα τα κελιά όπου αρχίζει το όνομα αντιπροσώπου πωλήσεων με J. Αυτό μπορεί εύκολα να επιτευχθεί χρησιμοποιώντας έναν χαρακτήρα μπαλαντέρ στη συνάρτηση COUNTIF. Εδώ είναι ο τύπος:
= COUNTIFS ($ C $ 2: $ C $ 11, "J*")
Τα κριτήρια J* καθορίζουν ότι το κείμενο σε ένα κελί πρέπει να ξεκινά με J και μπορεί να περιέχει οποιονδήποτε αριθμό χαρακτήρων.
Εάν θέλετε να μετρήσετε κελιά που περιέχουν το αλφάβητο οπουδήποτε στο κείμενο, τοποθετήστε το δίπλα με έναν αστερίσκο και στις δύο πλευρές. Για παράδειγμα, εάν θέλετε να μετρήσετε κελιά που περιέχουν το αλφάβητο "a" σε αυτό, χρησιμοποιήστε * a * ως κριτήρια.
Αυτό το άρθρο είναι ασυνήθιστα μακρύ σε σύγκριση με άλλα άρθρα μου. Ελπίζω να το απολαύσατε. Πείτε μου τις σκέψεις σας αφήνοντας ένα σχόλιο.
Μπορείτε επίσης να βρείτε χρήσιμα τα ακόλουθα μαθήματα Excel:
- Μετρήστε τον αριθμό των λέξεων στο Excel.
- Μετρήστε κελιά με βάση το χρώμα φόντου στο Excel.
- Πώς να αθροίσετε μια στήλη στο Excel (5 πραγματικά απλοί τρόποι)
