Πώς να χρησιμοποιήσετε ένα δυναμικό εύρος στο Excel με COUNTIF και INDIRECT

2024 Συγγραφέας: Abigail Brown | [email protected]. Τελευταία τροποποίηση: 2023-12-17 06:44

Τι πρέπει να γνωρίζετε

• Η συνάρτηση INDIRECT αλλάζει το εύρος των αναφορών κελιών σε έναν τύπο χωρίς επεξεργασία του τύπου.
• Χρησιμοποιήστε το INDIRECT ως όρισμα για το COUNTIF για να δημιουργήσετε ένα δυναμικό εύρος κελιών που πληρούν καθορισμένα κριτήρια.
• Τα κριτήρια καθορίζονται από τη συνάρτηση INDIRECT και υπολογίζονται μόνο τα κελιά που πληρούν τα κριτήρια.

Αυτό το άρθρο εξηγεί πώς να χρησιμοποιήσετε τη συνάρτηση INDIRECT σε τύπους Excel για να αλλάξετε το εύρος των αναφορών κελιών που χρησιμοποιούνται σε έναν τύπο χωρίς να χρειάζεται να επεξεργαστείτε τον ίδιο τον τύπο. Αυτό διασφαλίζει ότι χρησιμοποιούνται τα ίδια κελιά, ακόμη και όταν αλλάζει το υπολογιστικό φύλλο σας. Οι πληροφορίες ισχύουν για Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel για Mac και Excel Online.

Χρησιμοποιήστε ένα δυναμικό εύρος με τον τύπο COUNTIF - INDIRECT

Η συνάρτηση INDIRECT μπορεί να χρησιμοποιηθεί με έναν αριθμό συναρτήσεων που δέχονται μια αναφορά κελιού ως όρισμα, όπως οι συναρτήσεις SUM και COUNTIF.

Χρησιμοποιώντας το INDIRECT ως το όρισμα για το COUNTIF δημιουργείται ένα δυναμικό εύρος αναφορών κελιών που μπορούν να μετρηθούν από τη συνάρτηση εάν οι τιμές των κελιών πληρούν ένα κριτήριο. Αυτό το κάνει μετατρέποντας δεδομένα κειμένου, που μερικές φορές αναφέρονται ως συμβολοσειρά κειμένου, σε αναφορά κελιού.

Αυτό το παράδειγμα βασίζεται στα δεδομένα που εμφανίζονται στην παραπάνω εικόνα. Ο τύπος COUNTIF - INDIRECT που δημιουργήθηκε στο σεμινάριο είναι:

=COUNTIF(ΕΜΜΕΣΗ(E1&":"&E2), ">10")

Σε αυτόν τον τύπο, το όρισμα για τη συνάρτηση INDIRECT περιέχει:

• Οι αναφορές κελιών E1 και E2, που περιέχουν τα δεδομένα κειμένου D1 και D6.
• Ο τελεστής εύρους, η άνω και κάτω τελεία (:) που περιβάλλεται από διπλά εισαγωγικά (" ") που μετατρέπει την άνω και κάτω τελεία σε κείμενο συμβολοσειρά.
• Δύο συμπλεκτικά σύμβολα (&) που χρησιμοποιούνται για τη σύνδεση ή τη συνένωση του άνω και κάτω τελείου με τις αναφορές κελιών E1 και E2.

Το αποτέλεσμα είναι ότι το INDIRECT μετατρέπει τη συμβολοσειρά κειμένου D1:D6 σε αναφορά κελιού και τη μεταβιβάζει στη συνάρτηση COUNTIF που θα μετρηθεί εάν τα αναφερόμενα κελιά είναι μεγαλύτερα από 10.

Η συνάρτηση INDIRECT δέχεται οποιαδήποτε εισαγωγή κειμένου. Αυτά μπορεί να είναι κελιά στο φύλλο εργασίας που περιέχουν κείμενο ή αναφορές κελιών κειμένου που εισάγονται απευθείας στη συνάρτηση.

Δυναμική αλλαγή του εύρους της φόρμουλα

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

Αλλάζοντας τα δεδομένα κειμένου που βρίσκονται στα κελιά E1 και E2, από D1 και D6 σε D3 και D7, το εύρος που αθροίζεται από τη συνάρτηση μπορεί εύκολα να αλλάξει από D1:D6 σε D3:D7. Αυτό εξαλείφει την ανάγκη άμεσης επεξεργασίας του τύπου στο κελί G1.

Η συνάρτηση COUNTIF σε αυτό το παράδειγμα μετρά μόνο κελιά που περιέχουν αριθμούς εάν είναι μεγαλύτεροι από 10. Παρόλο που τέσσερα από τα πέντε κελιά στην περιοχή D1:D6 περιέχουν δεδομένα, μόνο τρία κελιά περιέχουν αριθμούς. Τα κελιά που είναι κενά ή περιέχουν δεδομένα κειμένου αγνοούνται από τη συνάρτηση.

Μετρώντας κείμενο με COUNTIF

Η συνάρτηση COUNTIF δεν περιορίζεται στην καταμέτρηση αριθμητικών δεδομένων. Μετρά επίσης κελιά που περιέχουν κείμενο ελέγχοντας αν ταιριάζουν με ένα συγκεκριμένο κείμενο.

Για να γίνει αυτό, εισάγεται ο ακόλουθος τύπος στο κελί G2:

=COUNTIF(ΕΜΜΕΣΗ(E1&":"&E2), "δύο")

Σε αυτόν τον τύπο, η συνάρτηση INDIRECT αναφέρεται στα κελιά B1 έως B6. Η συνάρτηση COUNTIF αθροίζει τον αριθμό των κελιών που έχουν την τιμή κειμένου δύο.

Σε αυτήν την περίπτωση, το αποτέλεσμα είναι 1.

COUNTA, COUNTBLANK και INDIRECT

Δύο άλλες συναρτήσεις καταμέτρησης του Excel είναι η COUNTA, η οποία μετράει κελιά που περιέχουν οποιονδήποτε τύπο δεδομένων, ενώ αγνοεί μόνο κενά ή κενά κελιά και COUNTBLANK, που μετρά μόνο κενά ή κενά κελιά σε μια περιοχή.

Δεδομένου ότι και οι δύο αυτές συναρτήσεις έχουν παρόμοια σύνταξη με τη συνάρτηση COUNTIF, μπορούν να αντικατασταθούν στο παραπάνω παράδειγμα με INDIRECT για να δημιουργηθούν οι ακόλουθοι τύποι:

=COUNTA(ΕΜΜΕΣΗ(E1&":"&E2))
=COUNTBLANK(ΕΜΜΕΣΗ(E1&":"&E2)
Για το εύρος D1:D6, το COUNTA επιστρέφει απάντηση 4, καθώς τέσσερα από τα πέντε κελιά περιέχουν δεδομένα. Το COUNTBLANK επιστρέφει απάντηση 1 αφού υπάρχει μόνο ένα κενό κελί στην περιοχή.

Γιατί να χρησιμοποιήσετε μια ΕΜΜΕΣΗ συνάρτηση;
Το πλεονέκτημα της χρήσης της συνάρτησης INDIRECT σε όλους αυτούς τους τύπους είναι ότι μπορούν να εισαχθούν νέα κελιά οπουδήποτε στην περιοχή.
Το εύρος μετατοπίζεται δυναμικά μέσα στις διάφορες λειτουργίες και τα αποτελέσματα ενημερώνονται ανάλογα.





Χωρίς τη συνάρτηση INDIRECT, κάθε συνάρτηση θα πρέπει να επεξεργαστεί ώστε να περιλαμβάνει και τα 7 κελιά, συμπεριλαμβανομένου του νέου.
Τα πλεονεκτήματα της συνάρτησης INDIRECT είναι ότι οι τιμές κειμένου μπορούν να εισαχθούν ως αναφορές κελιών και ότι ενημερώνει δυναμικά τις περιοχές κάθε φορά που αλλάζει το υπολογιστικό φύλλο σας.
Αυτό κάνει τη συνολική συντήρηση υπολογιστικών φύλλων πολύ πιο εύκολη, ειδικά για πολύ μεγάλα υπολογιστικά φύλλα.