Έτσι, έχουμε ήδη μάθει τι είναι η αναφορά 3D στο Excel. Το διασκεδαστικό γεγονός είναι ότι η κανονική αναφορά 3D στο Excel δεν λειτουργεί με συναρτήσεις υπό όρους, όπως η συνάρτηση SUMIF. Σε αυτό το άρθρο, θα μάθουμε πώς μπορούμε να κάνουμε 3D αναφορές σε λειτουργία με τη λειτουργία SUMIF.
Ο γενικός τύπος SUMIF με αναφορά 3D στο Excel
Φαίνεται περίπλοκο αλλά δεν είναι (τόσο πολύ).
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Κριτήριο_ range"), κριτήρια, INDIRECT ("'" & name_range_of_sheet_names & "'!" & "Sum_range"))) |
"'" name_range_of_sheet_names "'":Είναι μια ονομαστική περιοχή που περιέχει τα ονόματα των φύλλων. Αυτό είναι πολύ σημαντικό.
"κριτήριο_περιοχής":Είναι η αναφορά κειμένου των κριτηρίων που περιέχουν εύρος. (Θα πρέπει να είναι το ίδιο σε όλα τα φύλλα για τις εργασίες αναφοράς 3-D.)
κριτήρια:Είναι απλώς η συνθήκη που θέλετε να βάλετε για σύνοψη. Μπορεί να είναι αναφορά κειμένου ή κελιού.
"sum_range":Είναι η αναφορά κειμένου της περιοχής αθροίσματος. (Θα πρέπει να είναι το ίδιο σε όλα τα φύλλα για τις εργασίες αναφοράς 3-D.)
Αρκετά από τη θεωρία, ας κάνουμε 3D αναφορά με τη λειτουργία SUMIF να λειτουργεί.
Παράδειγμα: Άθροισμα ανά περιοχή από πολλαπλά φύλλα χρησιμοποιώντας τρισδιάστατη αναφορά του Excel:
Λαμβάνουμε τα ίδια δεδομένα που πήραμε στο απλό παράδειγμα αναφοράς 3D. Σε αυτό το παράδειγμα, έχω πέντε διαφορετικά φύλλα που περιέχουν παρόμοια δεδομένα. Κάθε φύλλο περιέχει δεδομένα ενός μήνα. Στο κύριο φύλλο, θέλω το άθροισμα μονάδων και συλλογής ανά περιοχή από όλα τα φύλλα. Ας το κάνουμε πρώτα για τις Μονάδες. Οι μονάδες βρίσκονται στην περιοχή D2: D14 σε όλα τα φύλλα.
Τώρα αν χρησιμοποιείτε την κανονική αναφορά 3D με τη λειτουργία SUMIF,
= SUMIF (Jan: Apr! A2: A14, Master! B4, Jan: Apr! D2: D14)
Θα επιστρέψει #ΑΞΙΑ! λάθος. Δεν μπορούμε λοιπόν να το χρησιμοποιήσουμε. Θα χρησιμοποιήσουμε τον γενικό τύπο που αναφέρθηκε παραπάνω.
Χρησιμοποιώντας τον παραπάνω γενικό τύπο αναφοράς SUMIF του Excel, γράψτε αυτόν τον τύπο στο κελί C3:
| = SUMPRODUCT (SUMIF (INDIRECT ("'" & Months & "'!" & "A2: A14"), Master! B3, INDIRECT ("" "& Months &" '! "&" D2: D14 "))) |
Εδώ μηνών είναι μια ονομαστική περιοχή που περιέχει τα ονόματα των φύλλων. Αυτό είναι κρίσιμο.
Όταν πατήσετε enter, λαμβάνετε την ακριβή έξοδο.
Πώς λειτουργεί;
Ο πυρήνας του τύπου είναι η συνάρτηση INDIRECT και η ονομασμένο εύροςΤο Εδώ η συμβολοσειρά"'" & Μήνες & "'!" & "A2: A14"μεταφράζεται σε μια σειρά από αναφορές εύρους κάθε φύλλου στο ονομασμένο εύρος.
| {"" Jan "! D2: D14"; "" Feb "! D2: D14"; "" Mar "! D2: D14"; "" Apr "! D2: D14"} |
Αυτός ο πίνακας περιέχει το αναφορά κειμένουεύρους, όχι τα πραγματικά εύρη. Τώρα δεδομένου ότι είναι μια αναφορά κειμένου, μπορεί να χρησιμοποιηθεί από τη συνάρτηση INDIRECT για τη μετατροπή τους σε πραγματικές περιοχές. Αυτό συμβαίνει και για τις δύο άμεσες συναρτήσεις. Αφού επιλύσετε τα κείμενα μέσα στις λειτουργίες INDIRECT (κρατήστε σφιχτά), ο τύπος μοιάζει με αυτόν:
| = SUMPRODUCT (SUMIF (INDIRECT (({"" Jan "! A2: A14"; "" Feb "! A2: A14"; "" Mar "! A2: A14"; "" Apr "! A2: A14"}) , Master! B3, INDIRECT ({"" Jan "! D2: D14"; "" Feb "! D2: D14"; "" Mar "! D2: D14"; "" Apr "! D2: D14"}))) |
Τώρα, η συνάρτηση SUMIF τίθεται σε δράση (όχι η ΕΜΜΕΣΗ, όπως ίσως έχετε μαντέψει). Η συνθήκη αντιστοιχεί στο πρώτο εύρος"'Jan'! A2: A14". Εδώ η συνάρτηση INDIRECT λειτουργεί δυναμικά και μετατρέπει αυτό το κείμενο στο πραγματικό εύρος (Αυτός είναι ο λόγος για τον οποίο εάν προσπαθήσετε να λύσετε το INDIRECT πρώτα χρησιμοποιώντας το πλήκτρο F9, δεν θα έχετε το αποτέλεσμα). Επόμενο αθροίζει τις αντιστοιχισμένες τιμές στο εύρος"'Jan'! D2: D14".Αυτό συμβαίνει για κάθε εύρος του πίνακα. Τέλος, θα έχουμε έναν πίνακα που επιστρέφεται από τη συνάρτηση SUMIF.
| = SUMPRODUCT ({97; 82; 63; 73}) |
Τώρα το SUMPRODUCT κάνει αυτό που κάνει καλύτερα. Συνοψίζει αυτές τις τιμές και η λειτουργία 3D SUMIF λειτουργεί.
Λοιπόν, ναι, έτσι μπορείτε να επιτύχετε μια λειτουργία 3D SUMIF. Αυτό είναι λίγο περίπλοκο, συμφωνώ σε αυτό. Υπάρχουν πολλά περιθώρια για σφάλματα σε αυτόν τον τρισδιάστατο τύπο. Θα σας πρότεινα να χρησιμοποιήσετε τη συνάρτηση SUMIF σε κάθε φύλλο σε ένα συγκεκριμένο κελί και στη συνέχεια να χρησιμοποιήσετε την κανονική αναφορά 3D για να συνοψίσετε αυτές τις τιμές.
Ελπίζω να ήμουν αρκετά επεξηγηματικός. Εάν έχετε αμφιβολίες σχετικά με την παραπομπή του excel σε οποιαδήποτε άλλη ερώτηση που σχετίζεται με το Excel/VBA, ρωτήστε την παρακάτω ενότητα σχολίων.
Σχετική και απόλυτη αναφορά στο Excel | Η αναφορά στο excel είναι ένα σημαντικό θέμα για κάθε αρχάριο. Ακόμα και έμπειροι χρήστες excel κάνουν λάθη στην αναφορά.
Αναφορά δυναμικού φύλλου εργασίας | Δώστε φύλλα αναφοράς δυναμικά χρησιμοποιώντας την ΕΜΜΕΣΗ συνάρτηση του excel. Αυτό είναι απλό…
Επέκταση αναφορών στο Excel | Η αναπτυσσόμενη αναφορά επεκτείνεται όταν αντιγράφεται προς τα κάτω ή προς τα δεξιά. Χρησιμοποιούμε το σύμβολο $ πριν από τον αριθμό στήλης και σειράς για να το κάνουμε. Εδώ είναι ένα παράδειγμα…
Όλα για την Απόλυτη Αναφορά | Ο προεπιλεγμένος τύπος αναφοράς στο excel είναι σχετικός, αλλά αν θέλετε η αναφορά των κελιών και των περιοχών να είναι απόλυτη, χρησιμοποιήστε το σύμβολο $. Ακολουθούν όλες οι πτυχές της απόλυτης αναφοράς στο Excel.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα σας κάνουν να εργάζεστε ακόμη πιο γρήγορα στο Excel.
Η συνάρτηση VLOOKUP στο Excel | Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
COUNTIF στο Excel 2016 | Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένη τιμή. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.