Χρησιμοποιήστε τη λειτουργία 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 περιέχει:
- Οι αναφορές κυττάρων Ε1 και Ε2, οι οποίες περιέχουν τα δεδομένα κειμένου D1 και D6.
- Ο χειριστής εμβέλειας, το παχύ έντερο (:) που περιβάλλεται από διπλά εισαγωγικά (' ') που μετατρέπει το κόλον σε μια συμβολοσειρά κειμένου.
- Δύο ampersands (&) που χρησιμοποιούνται για να συγκολλήσουν ή να ενώσουν μαζί το παχύ έντερο με τις αναφορές κυττάρων Ε1 και Ε2.
Το αποτέλεσμα είναι ότι το 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 αναφέρει τα κελιά Β1 έως Β6. Η συνάρτηση 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 είναι ότι οι τιμές κειμένου μπορούν να εισαχθούν ως αναφορές κυττάρων και ότι ενημερώνουν δυναμικά τις σειρές κάθε φορά που αλλάζει το υπολογιστικό φύλλο. Αυτό καθιστά τη συνολική συντήρηση του υπολογιστικού φύλλου πολύ πιο εύκολη, ειδικά για πολύ μεγάλα υπολογιστικά φύλλα. Γιατί να χρησιμοποιήσετε μια λειτουργία INDIRECT;
