Μέχρι στιγμής έχουμε μάθει πώς να αθροίζουμε ολόκληρη τη στήλη αντιστοίχισης ενός πίνακα στο Excel. Αλλά πώς αθροίζουμε τιμές όταν πρέπει να αντιστοιχίσουμε στήλη και γραμμή. Σε αυτό το άρθρο, θα μάθουμε πώς να κάνουμε το άθροισμα των ταινιών και των στηλών που ταιριάζουν.
Υπάρχουν δύο τύποι για να το κάνετε αυτό, αλλά πρώτα, αφήστε το σενάριο.
Εδώ, έχω έναν πίνακα που καταγράφει τις πωλήσεις που πραγματοποιούν οι εργαζόμενοι σε διαφορετικούς μήνες. Το όνομα των εργαζομένων μπορεί να επαναληφθεί. Δείτε το παρακάτω σχήμα:
Πρέπει να πάρουμε το άθροισμα του μήνα Μαΐου όπου ο πωλητής είναι ο Ντόναλντ.
Μέθοδος 1: Συνοψίζοντας την αντίστοιχη κεφαλίδα στήλης και κεφαλίδα γραμμής χρησιμοποιώντας τη συνάρτηση SUMPRODUCT.
ο Λειτουργία SUMPRODUCT είναι η πιο ευέλικτη συνάρτηση όταν πρόκειται για άθροιση και καταμέτρηση τιμών με περίπλοκα κριτήρια. Η γενική συνάρτηση που αθροίζεται με αντιστοίχιση στήλης και γραμμής είναι:
| = SUMPRODUCT ((στήλες)*(head_headers = column_heading)*(row_headers = row_heading) |
Στήλες:Είναι το δισδιάστατο εύρος των στηλών που θέλετε να αθροίσετε. Δεν πρέπει να περιέχει κεφαλίδες. Στον παραπάνω πίνακα είναι C3: N7.
column_headers:Είναι το εύρος κεφαλίδων τουστήλες που θέλετε να αθροίσετε. Στα παραπάνω δεδομένα, είναι C2: N2.
επικεφαλίδα στήλης: Είναι ο τίτλος που θέλετε να αντιστοιχίσετε. Στο παραπάνω παράδειγμα, είναι στο Β13.
Χωρίς περαιτέρω καθυστέρηση ας χρησιμοποιήσουμε τον τύπο.
row_headers:Είναι το εύρος κεφαλίδων τουσειρές που θέλετε να αθροίσετε. Στα παραπάνω δεδομένα, είναι B3: B10.
επικεφαλίδα σειράς: Είναι η επικεφαλίδα που θέλετε να αντιστοιχίσετε σε σειρές. Στο παραπάνω παράδειγμα, είναι στο F13.
Χωρίς περαιτέρω καθυστέρηση ας χρησιμοποιήσουμε τον τύπο.
Γράψτε αυτόν τον τύπο στο κελί D13 και αφήστε το excel να κάνει τη μαγεία (δεν υπάρχει μαγεία)…
| = SUMPRODUCT ((C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)) |
Αυτό επιστρέφει την τιμή:
Τώρα, όταν αλλάζετε τον μήνα ή τον πωλητή, το άθροισμα θα αλλάξει σύμφωνα με την επικεφαλίδα της γραμμής και την επικεφαλίδα της στήλης.
Πώς λειτουργεί;
Αυτή η απλή boolean λογική.
(C2: N2 = B13): Αυτή η δήλωση θα επιστρέψει έναν πίνακα TRUE και FALSE. Όλες οι τιμές αντιστοίχισης στη στήλη έχουν true και άλλες θα έχουν λάθος. Σε αυτήν την περίπτωση, θα έχουμε μόνο ένα True, καθώς το εύρος C2: N2 περιέχει μόνο μία παρουσία στις 5 Μαΐουου Τοποθεσία.
(Β3: Β10 = Ε13): Αυτό θα λειτουργήσει όπως και παραπάνω και θα επιστρέψει έναν πίνακα TRUE και FALSE. Όλες οι τιμές αντιστοίχισης θα έχουν TRUE και άλλες θα έχουν FALSE. Σε αυτήν την περίπτωση, θα έχουμε 2 ΑΛΗΘΙΝΕΣ καθώς το εύρος B3: το B10 έχει δύο περιπτώσεις "Donald".
(C2: N2 = B13)*(B3: B10 = E13): Τώρα πολλαπλασιάζουμε τους πίνακες που επιστρέφονται με προτάσεις. Αυτό θα εφαρμόσει και λογική και θα λάβουμε έναν πίνακα 1s και 0s. Τώρα θα έχουμε έναν πίνακα 2D που θα περιέχει 2 1s και υπόλοιπα 0s.
(C3: N10)*(C2: N2 = B13)*(B3: B10 = E13)= Τέλος πολλαπλασιάζουμε τον πίνακα 2D με τον πίνακα 2D. Θα επιστρέψει ξανά έναν πίνακα 0 και τους αριθμούς που ταιριάζουν με τα κριτήρια.
Τέλος, το SUMPRODUCT συνάρτηση θα συνοψίσει τον πίνακα που θα έχει ως αποτέλεσμα την επιθυμητή έξοδο.
Μέθοδος 2: Συνοψίζοντας την αντίστοιχη κεφαλίδα στήλης και κεφαλίδα γραμμής Χρησιμοποιώντας τη συνάρτηση SUM και IF
Ο γενικός τύπος για την σύνοψη αντιστοίχισης γραμμής και στήλης χρησιμοποιώντας συνάρτηση SUM και IF Excel είναι:
| = SUM (IF (head_headers = column_heading, IF (row_headers = row_heading, στήλες))) |
Όλες οι μεταβλητές είναι ίδιες όπως στην παραπάνω εξηγηθείσα μέθοδο. Εδώ, πρέπει απλώς να χρησιμοποιηθούν με διαφορετική σειρά.
Γράψτε αυτόν τον τύπο στο κελί D13:
| = SUM (IF (C2: N2 = B13, IF (B3: B10 = E13, C3: N10))) |
Αυτό επιστρέφει τη σωστή απάντηση. Δείτε το στιγμιότυπο οθόνης παρακάτω:
Πώς λειτουργεί;
Η λογική είναι η ίδια με την πρώτη μέθοδο SUMPRODCUT μόνο ο μηχανισμός είναι διαφορετικός. Αν το εξηγήσω εν συντομία, η εσωτερική συνάρτηση IF επιστρέφει έναν πίνακα 2D της ίδιας διάστασης με τον πίνακα. Αυτός ο πίνακας περιέχει τον αριθμό δύο αντιστοιχισμένων σειρών. Στη συνέχεια, η εσωτερική συνάρτηση IF ταιριάζει με τις επικεφαλίδες δύο στηλών σε αυτόν τον πίνακα και επιστρέφει τον πίνακα 2D που περιέχει μόνο τους αριθμούς που ταιριάζουν με τη στήλη και την επικεφαλίδα. Όλα τα άλλα στοιχεία του πίνακα θα είναι FALSE.
Τέλος, η συνάρτηση SUM αθροίζει αυτόν τον πίνακα και παίρνουμε το άθροισμά μας.
Λοιπόν, ναι, έτσι μπορείτε να συνοψίσετε τις αντίστοιχες γραμμές και στήλες από έναν πίνακα στο excel. Ελπίζω να ήταν επεξηγηματικό και χρήσιμο για εσάς. Εάν έχετε αμφιβολίες σχετικά με αυτό το θέμα ή έχετε οποιαδήποτε άλλη αμφιβολία σχετικά με το excel/VBA, ρωτήστε την παρακάτω ενότητα σχολίων.
Πώς να αθροίσετε τη στήλη σε ένα Excel με αντιστοίχιση επικεφαλίδας | Εάν θέλετε να πάρετε το άθροισμα μιας στήλης χρησιμοποιώντας μόνο το όνομα της στήλης, μπορείτε να το κάνετε με 3 εύκολους τρόπους στο Excel. Η σύνταξη της μεθόδου SUMPRODUCT για να αθροίσετε τη στήλη που ταιριάζει είναι:
SUMIF με αναφορά 3D στο Excel |Το διασκεδαστικό γεγονός είναι ότι η κανονική αναφορά 3D στο Excel δεν λειτουργεί με συναρτήσεις υπό όρους, όπως η συνάρτηση SUMIF. Σε αυτό το άρθρο, θα μάθουμε πώς μπορούμε να κάνουμε 3D αναφορές σε λειτουργία με τη λειτουργία SUMIF.
Σχετική και απόλυτη αναφορά στο Excel | Η αναφορά στο excel είναι ένα σημαντικό θέμα για κάθε αρχάριο. Ακόμα και έμπειροι χρήστες excel κάνουν λάθη στην αναφορά.
Αναφορά δυναμικού φύλλου εργασίας | Δώστε φύλλα αναφοράς δυναμικά χρησιμοποιώντας την ΕΜΜΕΣΗ συνάρτηση του excel. Αυτό είναι απλό…
Επέκταση αναφορών στο Excel | Η αναπτυσσόμενη αναφορά επεκτείνεται όταν αντιγράφεται προς τα κάτω ή προς τα δεξιά. Χρησιμοποιούμε το σύμβολο $ πριν από τον αριθμό στήλης και σειράς για να το κάνουμε. Εδώ είναι ένα παράδειγμα…
Όλα για την Απόλυτη Αναφορά | Ο προεπιλεγμένος τύπος αναφοράς στο excel είναι σχετικός, αλλά αν θέλετε η αναφορά των κελιών και των περιοχών να είναι απόλυτη, χρησιμοποιήστε το σύμβολο $. Ακολουθούν όλες οι πτυχές της απόλυτης αναφοράς στο Excel.
Δημοφιλή άρθρα:
50 συντομεύσεις Excel για να αυξήσετε την παραγωγικότητά σας | Γίνετε πιο γρήγοροι στην εργασία σας. Αυτές οι 50 συντομεύσεις θα κάνουν τη δουλειά σας ακόμη πιο γρήγορη στο Excel.
Η συνάρτηση VLOOKUP στο Excel | Αυτή είναι μια από τις πιο δημοφιλείς και δημοφιλείς λειτουργίες του excel που χρησιμοποιείται για την αναζήτηση τιμών από διαφορετικά εύρη και φύλλα.
COUNTIF στο Excel 2016 | Μετρήστε τιμές με συνθήκες χρησιμοποιώντας αυτήν την εκπληκτική συνάρτηση. Δεν χρειάζεται να φιλτράρετε τα δεδομένα σας για να μετρήσετε συγκεκριμένη τιμή. Η λειτουργία Countif είναι απαραίτητη για την προετοιμασία του ταμπλό σας.
Πώς να χρησιμοποιήσετε τη συνάρτηση SUMIF στο Excel | Αυτή είναι μια άλλη βασική λειτουργία του ταμπλό. Αυτό σας βοηθά να συνοψίσετε τιμές υπό συγκεκριμένες συνθήκες.