Κάντε λήψη του παραδείγματος βιβλίου εργασίας
Αυτό το σεμινάριο θα δείξει πώς να χρησιμοποιήσετε τις συναρτήσεις SUMPRODUCT και SUMIFS για να αθροίσετε δεδομένα που πληρούν ορισμένα κριτήρια σε πολλά φύλλα στο Excel και στα Υπολογιστικά φύλλα Google.
Τακτικό άθροισμα σε πολλά φύλλα
Μερικές φορές τα δεδομένα σας μπορεί να εκτείνονται σε πολλά φύλλα εργασίας σε ένα αρχείο Excel. Αυτό είναι κοινό για τα δεδομένα που συλλέγονται περιοδικά. Κάθε φύλλο σε ένα βιβλίο εργασίας μπορεί να περιέχει δεδομένα για μια καθορισμένη χρονική περίοδο. Θέλουμε έναν τύπο που αθροίζει τα δεδομένα που περιέχονται σε δύο ή περισσότερα φύλλα.
Η συνάρτηση SUM σάς επιτρέπει να αθροίζετε εύκολα δεδομένα σε πολλά φύλλα χρησιμοποιώντας ένα 3D αναφορά:
| 1 | = SUM (Sheet1: Sheet2! A1) |
Ωστόσο, αυτό δεν είναι δυνατό με τη συνάρτηση SUMIFS. Αντ 'αυτού, πρέπει να χρησιμοποιήσουμε έναν πιο περίπλοκο τύπο.
Άθροισμα εάν σε πολλά φύλλα
Αυτό το παράδειγμα θα συνοψίσει το Αριθμός προγραμματισμένων παραδόσεων για κάθε Πελάτης σε πολλαπλά φύλλα εργασίας, κάθε ένα από τα οποία περιέχει δεδομένα σχετικά με διαφορετικό μήνα, χρησιμοποιώντας τις συναρτήσεις SUMIFS, SUMPRODUCT και INDIRECT:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Ας περάσουμε από αυτόν τον τύπο.
Βήμα 1: Δημιουργήστε έναν τύπο SUMIFS μόνο για 1 φύλλο εισόδου:
Χρησιμοποιούμε τη συνάρτηση SUMIFS για να αθροίσουμε το Αριθμός προγραμματισμένων παραδόσεων με Πελάτης για ένα φύλλο δεδομένων εισόδου:
| 1 | = SUMIFS (D3: D7, C3: C7, H3) |
Βήμα 2: Προσθέστε μια αναφορά φύλλου στον τύπο
Διατηρούμε το αποτέλεσμα του τύπου τον ίδιο, αλλά καθορίζουμε ότι τα δεδομένα εισόδου βρίσκονται στο φύλλο που καλείται 'Βήμα 2'
| 1 | = SUMIFS («Βήμα 2»! D3: D7, «Βήμα 2»! C3: C7, H3) |
Βήμα 3: Φωλιά μέσα σε μια συνάρτηση SUMPRODUCT
Για να προετοιμάσουμε τον τύπο για να εκτελέσουμε υπολογισμούς SUMIFS σε πολλά φύλλα και στη συνέχεια να αθροίσουμε τα αποτελέσματα μαζί, προσθέτουμε μια συνάρτηση SUMPRODUCT γύρω από τον τύπο
| 1 | = SUMPRODUCT (SUMIFS («Βήμα 3»! D3: D7, «Βήμα 3»! C3: C7, H3)) |
Η χρήση της συνάρτησης SUMIFS σε ένα φύλλο δίνει μια μόνο τιμή. Σε πολλά φύλλα, η συνάρτηση SUMIFS εξάγει έναν πίνακα τιμών (μία για κάθε φύλλο εργασίας). Χρησιμοποιούμε τη συνάρτηση SUMPRODUCT για να αθροίσουμε τις τιμές σε αυτόν τον πίνακα.
Βήμα 4: Αντικαταστήστε την αναφορά φύλλου με μια λίστα με ονόματα φύλλων
Επιθυμούμε να αντικαταστήσουμε το Όνομα φύλλου μέρος του τύπου με μια λίστα δεδομένων που περιέχει τις τιμές: Ιαν, Φεβρουάριος, Παραμορφώνω, και ΑπρΤο Αυτή η λίστα αποθηκεύεται στα κελιά F3: F6.
Η ΕΜΜΕΣΗ συνάρτηση διασφαλίζει ότι εμφανίζεται η λίστα κειμένου Ονόματα φύλλων αντιμετωπίζεται ως μέρος μιας έγκυρης αναφοράς κελιού στη συνάρτηση SUMIFS.
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Σε αυτόν τον τύπο, η προηγούμενη γραφή αναφοράς εύρους:
| 1 | «Βήμα 3»! D3: D7 |
Αντικαθίσταται από:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Τα εισαγωγικά καθιστούν δύσκολη την ανάγνωση του τύπου, οπότε εδώ εμφανίζεται με πρόσθετα κενά:
| 1 | INDIRECT ("'" & F3: F6 & "'!" & "D3: D7") |
Η χρήση αυτού του τρόπου αναφοράς μιας λίστας κελιών μας επιτρέπει επίσης να συνοψίσουμε δεδομένα από πολλά φύλλα που δεν ακολουθούν ένα στυλ αριθμητικής λίστας. Μια τυπική αναφορά 3D θα απαιτούσε τα ονόματα των φύλλων να έχουν το στυλ: Εισαγωγή1, Εισαγωγή2, Εισαγωγή3 κ.λπ., αλλά το παραπάνω παράδειγμα σάς επιτρέπει να χρησιμοποιήσετε μια λίστα οποιωνδήποτε Ονόματα φύλλων και να αναφέρονται σε ξεχωριστό κελί.
Κλείδωμα αναφορών κυττάρων
Για να διευκολύνουμε την ανάγνωση των τύπων μας, δείξαμε τους τύπους χωρίς κλειδωμένες αναφορές κελιών:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & F3: F6 & "'!" & "D3: D7"), INDIRECT ("'" & F3: F6 & "'!" & "C3: C7"), H3)) |
Αλλά αυτοί οι τύποι δεν θα λειτουργήσουν σωστά όταν αντιγράφονται και επικολλούνται αλλού στο αρχείο σας. Αντ 'αυτού, θα πρέπει να χρησιμοποιήσετε αναφορές κλειδωμένων κελιών όπως αυτό:
| 1 | = SUMPRODUCT (SUMIFS (INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "D3: D7"), INDIRECT ("'" & $ F $ 3: $ F $ 6 & "'!" & "C3: C7"), H3)) |
Διαβάστε το άρθρο μας σχετικά με το κλείδωμα αναφορών κυττάρων για να μάθετε περισσότερα.
Άθροισμα σε πολλά φύλλα στα Υπολογιστικά φύλλα Google
Η χρήση της ΕΜΜΕΣΗΣ συνάρτησης για αναφορά μιας λίστας φύλλων σε μια συνάρτηση SUMPRODUCT και SUMIFS δεν είναι προς το παρόν δυνατή στα Υπολογιστικά φύλλα Google.
Αντ 'αυτού, μπορούν να γίνουν ξεχωριστοί υπολογισμοί SUMIFS για κάθε φύλλο εισόδου και τα αποτελέσματα να προστεθούν μαζί:
| 1234 | = SUMIFS (Jan! D3: D7, Jan! C3: C7, H3)+SUMIFS (Φεβ! D3: D7, Φεβ! C3: C7, H3)+SUMIFS (Μαρ! Δ3: Δ7, Μαρ! Γ3: Γ7, Η3)+SUMIFS (Απρ! Δ3: Δ7, Απρ! Γ3: Γ7, Η3) |
