Αυτό το σεμινάριο δείχνει πώς να χρησιμοποιήσετε τοΕκτel COUNTIF και COUNTIFS Λειτουργίααναφορές στο Excel για να μετρήσετε δεδομένα που πληρούν ορισμένα κριτήρια.
Επισκόπηση συνάρτησης COUNTIF
Μπορείτε να χρησιμοποιήσετε τη συνάρτηση COUNTIF στο Excel για να μετρήσετε κελιά που περιέχουν μια συγκεκριμένη τιμή, να μετρήσετε κελιά που είναι μεγαλύτερα ή ίσα με μια τιμή κ.λπ.
(Παρατηρήστε πώς εμφανίζονται οι εισαγωγές τύπου)
Σύνταξη και επιχειρήματα συνάρτησης COUNTIF:
= COUNTIF (εύρος, κριτήρια)
εύρος - Το εύρος κυττάρων προς καταμέτρηση.
κριτήρια - Τα κριτήρια που ελέγχουν ποια κύτταρα πρέπει να καταμετρηθούν.
Τι είναι η συνάρτηση COUNTIF;
Η συνάρτηση COUNTIF είναι μία από τις παλαιότερες συναρτήσεις που χρησιμοποιούνται στα υπολογιστικά φύλλα. Με απλά λόγια, είναι υπέροχο να σαρώνει ένα εύρος και να σας λέει πόσα από τα κύτταρα πληρούν αυτήν την προϋπόθεση. Θα δούμε πώς λειτουργεί η συνάρτηση με κείμενο, αριθμούς και ημερομηνίες. καθώς και κάποιες άλλες καταστάσεις που μπορεί να προκύψουν.
Βασικό παράδειγμα
Ας ξεκινήσουμε κοιτάζοντας αυτήν τη λίστα τυχαίων στοιχείων. Έχουμε μερικούς αριθμούς, κενά κελιά και μερικές συμβολοσειρές κειμένου.
Εάν θέλετε να μάθετε πόσα στοιχεία είναι ακριβώς τα κριτήρια, μπορείτε να καθορίσετε τι θέλετε να αναζητήσετε ως δεύτερο όρισμα. Ένα παράδειγμα αυτού του τύπου μπορεί να μοιάζει
= COUNTIF (A2: A9, "Apple")
Αυτός ο τύπος θα επιστρέψει τον αριθμό 3, καθώς υπάρχουν 3 κελιά στην περιοχή μας που πληρούν αυτά τα κριτήρια. Εναλλακτικά, μπορούμε να χρησιμοποιήσουμε μια αναφορά κελιού αντί να κωδικοποιήσουμε μια τιμή. Αν γράφαμε "Apple" στο κελί G2, θα μπορούσαμε να αλλάξουμε τον τύπο σε
= COUNTIF (A2: A9, G2)
Όταν ασχολείστε με τον αριθμό, είναι σημαντικό να κάνετε διάκριση μεταξύ αριθμών και αριθμών που έχουν αποθηκευτεί ως κείμενο. Γενικά, δεν βάζετε εισαγωγικά γύρω από τους αριθμούς όταν γράφετε τύπους. Έτσι, για να γράψετε έναν τύπο που ελέγχει τον αριθμό 5, θα γράφατε
= COUNTIF (A2: A9, 5)
Τέλος, θα μπορούσαμε επίσης να ελέγξουμε για κενά κελιά χρησιμοποιώντας μια συμβολοσειρά μηδενικού μήκους. Θα γράφαμε αυτόν τον τύπο ως
= COUNTIF (A2: A9, "")
Σημείωση: Αυτός ο τύπος θα μετρήσει τόσο τα κελιά που είναι πραγματικά κενά, όσο και αυτά που είναι κενά ως αποτέλεσμα ενός τύπου, όπως μια συνάρτηση IF.
Μερικοί αγώνες
Η συνάρτηση COUNTIF υποστηρίζει τη χρήση μπαλαντέρ, "*" ή ";", στα κριτήρια. Ας δούμε αυτή τη λίστα με νόστιμα είδη αρτοποιίας:
Για να βρείτε όλα τα στοιχεία που ξεκινούν με την Apple, θα μπορούσαμε να γράψουμε "Apple*". Έτσι, για να λάβουμε μια απάντηση 3, ο τύπος μας στο D2 είναι
= COUNTIF (A2: A5, "Apple*")
Σημείωση: Η συνάρτηση COUNTIF δεν έχει διάκριση πεζών-κεφαλαίων, οπότε μπορείτε επίσης να γράψετε "μήλο*" αν θέλετε.
Πίσω στα αρτοσκευάσματα μας, ίσως θελήσουμε να μάθουμε πόσες πίτες έχουμε στη λίστα μας. Αυτό μπορούμε να το διαπιστώσουμε τοποθετώντας την μπαλαντέρ στην αρχή του όρου αναζήτησης και γράψουμε
= COUNTIF (A2: A5, "*πίτα")
Αυτός ο τύπος δίνει το αποτέλεσμα 2.
Μπορούμε επίσης να χρησιμοποιήσουμε μπαλαντέρ για να ελέγξουμε για τυχόν κελιά με κείμενο. Ας επιστρέψουμε στην αρχική μας λίστα δεδομένων.
Για να μετρήσουμε τον αριθμό των κελιών που έχουν τουλάχιστον κάποιο κείμενο, χωρίς να υπολογίζουμε αριθμούς ή κενό κελί, μπορούμε να γράψουμε
= COUNTIF (A2: A9, "*")
Μπορείτε να δείτε ότι ο τύπος μας επιστρέφει σωστά ένα αποτέλεσμα 4.
Σύγκριση τελεστών στο COUNTIF
Κατά τη σύνταξη των κριτηρίων μέχρι τώρα, υπονοούσαμε ότι ο τελεστής σύγκρισης είναι "=". Στην πραγματικότητα, θα μπορούσαμε να γράψουμε αυτό:
= COUNTIF (A2: A9, "= Apple")
Ωστόσο, είναι ένας επιπλέον χαρακτήρας για να γράψετε, οπότε συνήθως παραλείπεται. Ωστόσο, αυτό σημαίνει ότι μπορείτε να χρησιμοποιήσετε τους άλλους τελεστές, όπως μεγαλύτερους από, μικρότερους ή μη ίσους με. Ας δούμε αυτήν τη λίστα καταγεγραμμένων ηλικιών:
Αν θέλαμε να μάθουμε πόσα παιδιά είναι τουλάχιστον 5 ετών, μπορούμε να γράψουμε μια σύγκριση "μεγαλύτερη ή ίση με" όπως παρακάτω:
= COUNTIF (A2: A8, "> = 5")
Σημείωση: Ο τελεστής σύγκρισης δίνεται πάντα ως συμβολοσειρά κειμένου και επομένως πρέπει να βρίσκεται μέσα σε εισαγωγικά.
Ομοίως, μπορείτε επίσης να ελέγξετε για στοιχεία που είναι μικρότερα από μια δεδομένη τιμή. Εάν πρέπει να μάθουμε πόσοι είναι μικρότεροι από 8, μπορούμε να γράψουμε
= COUNTIF (A2: A8, "<8")
Αυτό μας δίνει το επιθυμητό αποτέλεσμα του 5. Τώρα ας φανταστούμε ότι όλα τα παιδιά ηλικίας 6 ετών πηγαίνουν μια έξοδο. Πόσα παιδιά θα μείνουν; Μπορούμε να το καταλάβουμε χρησιμοποιώντας μια σύγκριση "όχι ίση με" όπως αυτή:
= COUNTIF (A2: A8, "6")
Τώρα μπορούμε γρήγορα να δούμε ότι έχουμε 6 παιδιά που δεν είναι 6 ετών.
Σε αυτά τα παραδείγματα σύγκρισης μέχρι στιγμής, δυσκολευτήκαμε να κωδικοποιήσουμε τις τιμές που θέλαμε. Μπορείτε επίσης να χρησιμοποιήσετε μια αναφορά κελιού. Το κόλπο είναι ότι πρέπει να συνδέσετε τον τελεστή σύγκρισης με την αναφορά κελιού. Ας πούμε ότι βάζουμε τον αριθμό 7 στο κελί C2 και θέλουμε τον τύπο μας στο D2 να δείχνει πόσα παιδιά είναι κάτω των 7 ετών.
Ο τύπος μας στο D2 πρέπει να μοιάζει με αυτόν:
= COUNTIF (A2: A8, "<" & C2)
Σημείωση: Δώστε ιδιαίτερη προσοχή κατά τη σύνταξη αυτών των τύπων στο εάν πρέπει να βάλετε ένα στοιχείο εντός εισαγωγικών ή έξω. Οι τελεστές βρίσκονται πάντα μέσα σε εισαγωγικά, οι αναφορές κελιών είναι πάντα εκτός εισαγωγικών. Οι αριθμοί είναι εκτός εάν κάνετε ακριβή αντιστοίχιση, αλλά μέσα εάν κάνετε τελεστή σύγκρισης.
Εργασία με ημερομηνίες
Έχουμε δει πώς μπορείτε να δώσετε ένα κείμενο ή έναν αριθμό ως κριτήριο, αλλά τι γίνεται όταν πρέπει να δουλέψουμε με ημερομηνίες; Ακολουθεί μια γρήγορη λίστα δειγμάτων με την οποία μπορούμε να συνεργαστούμε:
Για να μετρήσουμε πόσες ημερομηνίες είναι μετά τις 4 Μαΐου, πρέπει να προσέξουμε. Οι υπολογιστές αποθηκεύουν ημερομηνίες ως αριθμούς, οπότε πρέπει να βεβαιωθούμε ότι ο υπολογιστής χρησιμοποιεί τον σωστό αριθμό. Αν γράφαμε αυτόν τον τύπο, θα είχαμε το σωστό αποτέλεσμα;
= COUNTIF (A2: A9, "
Η απάντηση είναι «πιθανώς». Επειδή παραλείψαμε το έτος από τα κριτήριά μας, ο υπολογιστής θα υποθέσει ότι εννοούμε το τρέχον έτος. Εάν όλες οι ημερομηνίες με τις οποίες εργαζόμαστε είναι για το τρέχον έτος, τότε θα λάβουμε τη σωστή απάντηση. Αν υπάρχουν κάποιες ημερομηνίες στο μέλλον, θα λάβουμε λάθος απάντηση. Επίσης, μόλις ξεκινήσει το επόμενο έτος, αυτός ο τύπος θα επιστρέψει ένα διαφορετικό αποτέλεσμα. Ως εκ τούτου, αυτή η σύνταξη θα πρέπει πιθανώς να αποφευχθεί.
Επειδή μπορεί να είναι δύσκολο να γράψετε σωστά ημερομηνίες μέσα σε έναν τύπο, είναι η καλύτερη πρακτική να γράψετε την ημερομηνία που θέλετε να χρησιμοποιήσετε σε ένα κελί και, στη συνέχεια, μπορείτε να χρησιμοποιήσετε αυτήν την αναφορά κελιού εντός του τύπου COUNTIF. Έτσι, ας γράψουμε την ημερομηνία 7-Μαΐου-2020 στο κελί C2 και στη συνέχεια μπορούμε να βάλουμε τον τύπο μας στο C4.
Ο τύπος στο C4 είναι
= COUNTIF (A2: A9, "<" & C2)
Τώρα γνωρίζουμε ότι το αποτέλεσμα του 7 είναι σωστό και η απάντηση δεν πρόκειται να αλλάξει απροσδόκητα αν ανοίξουμε αυτό το υπολογιστικό φύλλο κάποια στιγμή στο μέλλον.
Πριν φύγουμε από αυτήν την ενότητα, είναι συνηθισμένο να χρησιμοποιούμε τη λειτουργία ΣΗΜΕΡΑ όταν δουλεύουμε με ημερομηνίες. Μπορούμε να το χρησιμοποιήσουμε ακριβώς όπως θα κάναμε μια αναφορά κελιού. Για παράδειγμα, θα μπορούσαμε να αλλάξουμε τον προηγούμενο τύπο ως εξής:
= COUNTIF (A2: A9, "<" & TODAY ())
Τώρα ο τύπος μας θα εξακολουθεί να ενημερώνεται καθώς προχωρά ο πραγματικός χρόνος και θα έχουμε έναν αριθμό στοιχείων που είναι λιγότερα από σήμερα.
Πολλαπλά κριτήρια και COUNTIFS
Η αρχική συνάρτηση COUNTIF βελτιώθηκε το 2007 όταν κυκλοφόρησε το COUNTIFS. Η σύνταξη μεταξύ των δύο είναι πολύ παρόμοια, με την τελευταία να σας επιτρέπει να δώσετε επιπλέον εύρη και κριτήρια. Μπορείτε εύκολα να χρησιμοποιήσετε το COUNTIFS σε οποιαδήποτε κατάσταση υπάρχει COUNTIF. Είναι καλή ιδέα να γνωρίζετε ότι υπάρχουν και οι δύο συναρτήσεις.
Ας δούμε αυτόν τον πίνακα δεδομένων:
Για να μάθετε πόσα άτομα βρίσκονται στα επίπεδα αμοιβής 1 έως 2, μπορείτε να γράψετε μια σύνοψη συναρτήσεων COUNTIF όπως αυτή:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Αυτός ο τύπος θα λειτουργήσει, καθώς βρίσκετε ό, τι είναι πάνω από το 1, αλλά στη συνέχεια αφαιρείτε τον αριθμό των εγγραφών που είναι πέρα από το σημείο αποκοπής σας. Εναλλακτικά, μπορείτε να χρησιμοποιήσετε COUNTIFS όπως αυτό:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Το τελευταίο είναι πιο διαισθητικό για ανάγνωση, οπότε ίσως θελήσετε να χρησιμοποιήσετε αυτήν τη διαδρομή. Επίσης, το COUNTIFS είναι πιο ισχυρό όταν πρέπει να λάβετε υπόψη πολλές στήλες. Ας υποθέσουμε ότι θέλουμε να μάθουμε πόσα άτομα είναι στη Διοίκηση και στο Επίπεδο Αμοιβής 1. Δεν μπορείτε να το κάνετε μόνο με ένα COUNTIF. πρέπει να γράψεις
= COUNTIFS (A2: A7, "Management", B2: B7, 1)
Αυτός ο τύπος θα σας δώσει το σωστό αποτέλεσμα του 2. Πριν φύγουμε από αυτήν την ενότητα, ας εξετάσουμε μια λογική τύπου Or. Τι γίνεται αν θέλαμε να μάθουμε πόσα άτομα είναι στη Διοίκηση ή; Θα χρειαστεί να προσθέσετε μερικά COUNTIFS μαζί, αλλά υπάρχουν δύο τρόποι για να το κάνετε αυτό. Ο απλούστερος τρόπος είναι να το γράψετε ως εξής:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Μπορείτε επίσης να χρησιμοποιήσετε έναν πίνακα και να γράψετε αυτόν τον τύπο πίνακα:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))
Σημείωση: Οι τύποι πίνακα πρέπει να επιβεβαιωθούν χρησιμοποιώντας `Ctrl+Shift+Enter` και όχι μόνο` Enter`.
Πώς θα λειτουργήσει αυτός ο τύπος, θα δει ότι έχετε δώσει έναν πίνακα ως είσοδο. Έτσι θα υπολογίσει το αποτέλεσμα σε δύο διαφορετικές συναρτήσεις COUNTIF και θα τις αποθηκεύσει σε έναν πίνακα. Η συνάρτηση SUM θα προσθέσει στη συνέχεια όλα τα αποτελέσματα στη συστοιχία μας για να δημιουργήσουμε μία μόνο έξοδο. Έτσι, ο τύπος μας θα αξιολογηθεί ως εξής:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Μετρήστε μοναδικές τιμές
Τώρα που είδαμε πώς να χρησιμοποιούμε έναν πίνακα με τη συνάρτηση COUNTIF, μπορούμε να το κάνουμε ένα βήμα παραπάνω για να μας βοηθήσει να μετρήσουμε πόσες μοναδικές τιμές υπάρχουν σε ένα εύρος. Αρχικά, ας δούμε ξανά τη λίστα των Τμημάτων μας.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Μπορούμε να δούμε ότι υπάρχουν 6 δεδομένα κελιών, αλλά υπάρχουν μόνο 3 διαφορετικά στοιχεία. Για να λειτουργήσουν τα μαθηματικά, θα χρειαζόμασταν κάθε στοιχείο να αξίζει 1/Β, όπου Ν είναι ο αριθμός των επαναλήψεων ενός στοιχείου. Για παράδειγμα, εάν κάθε HR άξιζε μόνο το 1/2, τότε όταν τα προσθέτατε θα λάβατε 1, για 1 μοναδική τιμή.
Επιστροφή στο COUNTIF, το οποίο έχει σχεδιαστεί για να υπολογίζει πόσες φορές εμφανίζεται ένα στοιχείο σε μια περιοχή. Στο D2, θα γράψουμε τον τύπο πίνακα
= SUM (1/COUNTIF (A2: A7, A2: A7))
Πώς θα λειτουργήσει αυτός ο τύπος, είναι για κάθε κελί στην περιοχή του A2: A7, θα ελέγξει για να δει πόσες φορές εμφανίζεται. Με το δείγμα μας, αυτό θα παράγει μια σειρά από
{2, 2, 3, 3, 3, 1}
Στη συνέχεια, μετατρέπουμε όλους αυτούς τους αριθμούς σε κλάσματα κάνοντας διαίρεση. Τώρα ο πίνακας μας μοιάζει
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Όταν προσθέσουμε όλα αυτά, έχουμε το επιθυμητό αποτέλεσμα 3.
Countif με δύο ή πολλαπλές προϋποθέσεις - Η συνάρτηση Countifs
Μέχρι στιγμής έχουμε εργαστεί μόνο με τη συνάρτηση COUNTIF. Η συνάρτηση COUNTIF μπορεί να χειριστεί μόνο ένα κριτήριο κάθε φορά. Για COUNTIF με πολλαπλά κριτήρια πρέπει να χρησιμοποιήσετε τη συνάρτηση COUNTIFS. Το COUNTIFS συμπεριφέρεται ακριβώς όπως το COUNTIF. Απλώς προσθέτετε επιπλέον κριτήρια. Ας ρίξουμε μια ματιά στο παρακάτω παράδειγμα.
= COUNTIFS (B2: B7, "= 130")
COUNTIF & COUNTIFS στα Υπολογιστικά φύλλα Google
Η συνάρτηση COUNTIF & COUNTIFS λειτουργεί ακριβώς το ίδιο στα Υπολογιστικά φύλλα Google όπως στο Excel:
