Excel SUM και INDIRECT Dynamic Range Formula

Το Microsoft Excel έχει μερικά δροσερά κόλπα και χρησιμοποιώντας τους τύπους δυναμικής κλίμακας SUM και INDIRECT είναι μόνο δύο τρόποι για εύκολο χειρισμό των δεδομένων που έχετε.

SUM - ΕΜΜΕΣΗ Επισκόπηση τύπων

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

Το INDIRECT μπορεί να χρησιμοποιηθεί με πολλές λειτουργίες που δέχονται μια αναφορά κυψέλης ως ένα επιχείρημα όπως οι λειτουργίες OFFSET και SUM.

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

Το ΕΜΜΕΣΟ κάνει αυτό αναφερόμενο στα δεδομένα των κελιών έμμεσα μέσω μιας ενδιάμεσης θέσης.

Παράδειγμα: SUM - INDIRECT Ο τύπος που χρησιμοποιείται για τη συνολική δυναμική περιοχή τιμών

Αυτό το παράδειγμα βασίζεται στα δεδομένα που εμφανίζονται στην παραπάνω εικόνα.

Ο τύπος SUM - INDIRECT που δημιουργείται χρησιμοποιώντας τα παρακάτω βήματα οδηγιών είναι:

= SUM (ΕΜΜΕΣΗ ("D" & E1 & ": D" & E2))

Στον τύπο αυτό, το όρισμα της ένθετης ΕΝΔΙΑΜΕΣΗΣ συνάρτησης περιέχει αναφορές στα κελιά E1 και E2. Οι αριθμοί σε αυτά τα κελιά, 1 και 4, όταν συνδυάζονται με το υπόλοιπο του όρου INDIRECT, αποτελούν τις αναφορές κυττάρων D1 και D4.

Ως αποτέλεσμα, η περιοχή των αριθμών που ανέρχονται από τη συνάρτηση SUM είναι τα δεδομένα που περιέχονται στην περιοχή των κυττάρων D1 έως D4 - η οποία είναι 50.

Αλλάζοντας τους αριθμούς που βρίσκονται στα κελιά E1 και E2. Ωστόσο, το εύρος που πρόκειται να συμπληρωθεί μπορεί εύκολα να αλλάξει.

Αυτό το παράδειγμα θα χρησιμοποιήσει πρώτα τον παραπάνω τύπο για να συγκεντρώσει τα δεδομένα στα κελιά D1: D4 και στη συνέχεια να αλλάξει το αθροισμένο εύρος σε D3: D6 χωρίς να επεξεργαστεί τον τύπο στο κελί F1.

01 από 03

Εισαγωγή στη φόρμουλα - Επιλογές

Δημιουργήστε μια δυναμική περιοχή σε τύπους Excel. © Ted French

Οι επιλογές για την εισαγωγή του τύπου περιλαμβάνουν:

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

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

Τα παρακάτω βήματα χρησιμοποιούν το παράθυρο διαλόγου SUM για να εισαγάγετε τον τύπο.

Εισαγάγετε τα Δεδομένα Tutorial

Δεδομένα κυττάρων D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4
  1. Καταχωρίστε τα ακόλουθα δεδομένα στα κελιά D1 έως E2

Εκκίνηση του SUM - INDIRECT Formula - Ανοίγοντας το παράθυρο διαλόγου Function SUM

  1. Κάντε κλικ στο κελί F1 - εδώ θα εμφανιστούν τα αποτελέσματα αυτού του παραδείγματος
  2. Κάντε κλικ στην καρτέλα Φόρμες του μενού κορδέλας
  3. Επιλέξτε Math & Trig από την κορδέλα για να ανοίξετε την αναπτυσσόμενη λίστα λειτουργιών
  4. Κάντε κλικ στο SUM στη λίστα για να ανοίξετε το παράθυρο διαλόγου της λειτουργίας

02 του 03

Εισαγωγή στη λειτουργία INDIRECT - Κάντε κλικ για να δείτε μεγαλύτερη εικόνα

Κάντε κλικ για να δείτε μεγαλύτερη εικόνα. © Ted French

Ο τύπος INDIRECT πρέπει να εισαχθεί ως επιχείρημα για τη λειτουργία SUM.

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

Συνεπώς, η λειτουργία INDIRECT πρέπει να εισαχθεί με μη αυτόματο τρόπο στη γραμμή Αριθμός1 του παραθύρου διαλόγου Λειτουργία SUM.

  1. Στο παράθυρο διαλόγου, κάντε κλικ στη γραμμή Αριθμός1
  2. Εισάγετε την ακόλουθη λειτουργία ΕΜΜΕΣΗ: ΕΜΜΕΣΗ ("D" & E1 & ": D" & E2)
  3. Κάντε κλικ στο κουμπί OK για να ολοκληρώσετε τη λειτουργία και να κλείσετε το παράθυρο διαλόγου
  4. Ο αριθμός 50 θα πρέπει να εμφανίζεται στο κελί F1, καθώς αυτό είναι το σύνολο των δεδομένων που βρίσκονται στα κελιά D1 έως D4
  5. Όταν κάνετε κλικ στο κελί F1 ο πλήρης τύπος = SUM (INDIRECT ("D" & E1 & ": D" & E2)) εμφανίζεται στη γραμμή τύπων πάνω από το φύλλο εργασίας

Καταργώντας τη λειτουργία INDIRECT

Για να δημιουργήσετε ένα δυναμικό εύρος στη στήλη D χρησιμοποιώντας INDIRECT, πρέπει να συνδυάσουμε το γράμμα D στο όρισμα της συνάρτησης INDIRECT με τους αριθμούς που περιέχονται στα κελιά E1 και E2.

Αυτό επιτυγχάνεται με τα εξής:

Επομένως, το σημείο εκκίνησης του εύρους ορίζεται από τους χαρακτήρες: "D" & E1 .

Το δεύτερο σύνολο χαρακτήρων: ": D" & E2 συνδυάζει το παχύ έντερο με το τελικό σημείο. Αυτό γίνεται επειδή το παχύ έντερο είναι ένας χαρακτήρας κειμένου και συνεπώς πρέπει να συμπεριληφθεί μέσα σε εισαγωγικά.

Το τρίτο και στη μέση χρησιμοποιείται για να συνενώσει τα δύο μέρη σε ένα επιχείρημα :

"D" & E1 & ": D" & E2

03 του 03

Αλλαγή δυναμικά της εμβέλειας της λειτουργίας SUM

Αλλαγή δυναμικής εμβέλειας. © Ted French

Ολόκληρο το σημείο αυτής της φόρμουλας είναι να καταστήσει εύκολη την αλλαγή του εύρους που καθορίζεται από τη λειτουργία SUM χωρίς να χρειάζεται να επεξεργαστείτε το επιχείρημα της λειτουργίας.

Συμπεριλαμβάνοντας τη συνάρτηση INDIRECT στον τύπο, αλλάζοντας τους αριθμούς στα κελιά E1 και E2 θα αλλάξει η περιοχή των κελιών που διαβάζεται από τη λειτουργία SUM.

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

  1. Κάντε κλικ στο κελί E1
  2. Πληκτρολογήστε τον αριθμό 3
  3. Πατήστε το πλήκτρο Enter στο πληκτρολόγιο
  4. Κάντε κλικ στο κελί E2
  5. Πληκτρολογήστε τον αριθμό 6
  6. Πατήστε το πλήκτρο Enter στο πληκτρολόγιο
  7. Η απάντηση στο κελί F1 θα πρέπει να αλλάξει σε 90 - που είναι το σύνολο των αριθμών που περιέχονται στα κελιά D3 έως D6
  8. Περαιτέρω δοκιμάστε τον τύπο μεταβάλλοντας τα περιεχόμενα των κελιών B1 και B2 σε οποιονδήποτε αριθμό μεταξύ 1 και 6

INDIRECT και το # REF! Τιμή σφάλματος

Το #REF! η τιμή σφάλματος θα εμφανιστεί στο κελί F1 εάν το παράθυρο της λειτουργίας INDIRECT: