Περίληψη
Σε ορισμένες περιπτώσεις, μπορείτε να χρησιμοποιήσετε το SUMIFS όπως έναν τύπο αναζήτησης για να ανακτήσετε μια αριθμητική τιμή. Στο παράδειγμα που εμφανίζεται, ο τύπος στο G6 είναι:
=SUMIFS(sales,region,G4,quarter,G5)
όπου η περιοχή (B5: B20), το τέταρτο (C5: C20) και οι πωλήσεις (D5: D20) ονομάζονται εύρη.
Το αποτέλεσμα είναι οι πωλήσεις Q3 για την Κεντρική περιοχή, 127.250.
Εξήγηση
Εάν είστε νέοι στη συνάρτηση SUMIFS, μπορείτε να βρείτε μια βασική επισκόπηση με πολλά παραδείγματα εδώ.
Η συνάρτηση SUMIFS έχει σχεδιαστεί για να αθροίζει αριθμητικές τιμές με βάση ένα ή περισσότερα κριτήρια. Ωστόσο, σε συγκεκριμένες περιπτώσεις, ενδέχεται να μπορείτε να χρησιμοποιήσετε το SUMIFS για να "αναζητήσετε" μια αριθμητική τιμή που πληροί τα απαιτούμενα κριτήρια. Οι κύριοι λόγοι για να γίνει αυτό είναι η απλότητα και η ταχύτητα.
Στο παράδειγμα που εμφανίζεται, έχουμε τριμηνιαία στοιχεία πωλήσεων για τέσσερις περιοχές. Ξεκινάμε δίνοντας στο SUMIFS ένα εύρος αθροίσεων και την πρώτη συνθήκη, η οποία ελέγχει την περιοχή για την τιμή στο G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Το εύρος αθροίσματος είναι οι πωλήσεις (D5: D20)
- Το εύρος κριτηρίων 1 είναι περιοχή (B5: B20)
- Τα κριτήρια 1 είναι G4 ("Κεντρικό")
Στη συνέχεια προσθέτουμε το δεύτερο ζεύγος εύρους / κριτηρίων, το οποίο ελέγχει το τρίμηνο:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Το εύρος κριτηρίων 2 είναι τρίμηνο (C5: C20)
- Τα κριτήρια 2 είναι G5 ("Q3")
Με αυτά τα κριτήρια, το SUMIFS επιστρέφει 127.250, τον κεντρικό αριθμό πωλήσεων Q3.
Η συμπεριφορά του SUMIFS είναι να αθροίσουμε όλες τις αντίστοιχες τιμές. Ωστόσο, επειδή υπάρχει μόνο μία τιμή που ταιριάζει, το αποτέλεσμα είναι το ίδιο με την ίδια την τιμή.
Παρακάτω, εξετάζουμε διάφορες επιλογές τύπου αναζήτησης.
Επιλογές τύπου αναζήτησης
Αυτή η ενότητα εξετάζει εν συντομία άλλες επιλογές τύπου που αποδίδουν το ίδιο αποτέλεσμα. Με εξαίρεση το SUMPRODUCT (στο κάτω μέρος), αυτοί είναι πιο παραδοσιακοί τύποι αναζήτησης που εντοπίζουν τη θέση της τιμής στόχου και επιστρέφουν την τιμή σε αυτήν τη θέση.
Με VLOOKUP
Δυστυχώς, το VLOOKUP δεν είναι μια καλή λύση σε αυτό το πρόβλημα. Με μια βοηθητική στήλη, είναι δυνατό να δημιουργήσετε έναν τύπο VLOOKUP που να ταιριάζει με πολλαπλά κριτήρια (παράδειγμα εδώ), αλλά είναι μια δύσκολη διαδικασία που απαιτεί από εσάς να παρακολουθείτε τα δεδομένα προέλευσης.
Με INDEX και MATCH
Το INDEX και το MATCH είναι ένας πολύ ευέλικτος συνδυασμός αναζήτησης που μπορεί να χρησιμοποιηθεί για όλα τα είδη προβλημάτων αναζήτησης και αυτό το παράδειγμα δεν αποτελεί εξαίρεση. Με το INDEX και το MATCH, μπορούμε να αναζητήσουμε τις πωλήσεις ανά περιοχή και τρίμηνο με έναν τύπο πίνακα όπως:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter.
Το κόλπο με αυτήν την προσέγγιση είναι να χρησιμοποιήσετε λογική boolean με λειτουργίες συστοιχιών μέσα στη συνάρτηση MATCH για να δημιουργήσετε έναν πίνακα 1s και 0s ως πίνακα αναζήτησης. Στη συνέχεια, μπορούμε να ζητήσουμε από τη συνάρτηση MATCH να βρει τον αριθμό 1. Μόλις δημιουργηθεί ο πίνακας αναζήτησης, ο τύπος επιλύει:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Με μόνο 1 εναπομείναντα στη συστοιχία αναζήτησης, το MATCH επιστρέφει μια θέση 11 στη συνάρτηση INDEX και το INDEX επιστρέφει τον αριθμό πώλησης σε αυτήν τη θέση, 127.250.
Για περισσότερες λεπτομέρειες, δείτε: INDEX και MATCH με πολλαπλά κριτήρια
Με XLOOKUP
Το XLOOKUP είναι μια ευέλικτη νέα λειτουργία στο Excel που μπορεί να χειριστεί τις συστοιχίες εγγενώς. Με το XLOOKUP, μπορούμε να χρησιμοποιήσουμε ακριβώς την ίδια προσέγγιση όπως με το INDEX και MATCH, χρησιμοποιώντας δυαδικές λογικές και λειτουργίες πίνακα για να δημιουργήσουμε έναν πίνακα αναζήτησης:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Μόλις εκτελεστούν οι λειτουργίες του πίνακα, ο τύπος επιλύει:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Και το XLOOKUP επιστρέφει το ίδιο αποτέλεσμα όπως παραπάνω, 127.250.
Περισσότερα: XLOOKUP με πολλαπλά κριτήρια
Με LOOKUP
Η συνάρτηση LOOKUP είναι μια παλαιότερη συνάρτηση στο Excel για την οποία πολλοί άνθρωποι δεν γνωρίζουν καν. Ένα από τα βασικά πλεονεκτήματα του LOOKUP είναι ότι μπορεί να χειριστεί τις συστοιχίες εγγενώς. Ωστόσο, το LOOKUP έχει μερικές ξεχωριστές αδυναμίες:
- Δεν είναι κλειδωμένο σε "λειτουργία ακριβούς αντιστοίχισης"
- Υποθέτει πάντα ότι τα δεδομένα αναζήτησης είναι ταξινομημένα, AZ
- Επιστρέφει πάντα μια κατά προσέγγιση αντιστοίχιση (εάν δεν μπορεί να βρεθεί ακριβής αντιστοίχιση)
Ωστόσο, το LOOKUP μπορεί να χρησιμοποιηθεί για την επίλυση αυτού του προβλήματος όπως:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
που απλοποιεί:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Εάν η κατάσταση δεν πληροί και τις δύο προϋποθέσεις, το SUMIFS δεν είναι καλή επιλογή.
