Ένα πρόβλημα που προκύπτει πολύ στο Excel είναι η μέτρηση ή το άθροισμα με βάση πολλές συνθήκες OR. Για παράδειγμα, ίσως πρέπει να αναλύσετε δεδομένα και να μετρήσετε παραγγελίες στο Σιάτλ ή στο Ντένβερ, για αντικείμενα που είναι Κόκκινα, Μπλε ή Πράσινα; Αυτό μπορεί να είναι εκπληκτικά δύσκολο, οπότε φυσικά αποτελεί μια καλή πρόκληση!
Η πρόκληση
Τα παρακάτω δεδομένα αντιπροσωπεύουν παραγγελίες, μία παραγγελία ανά σειρά. Υπάρχουν τρεις ξεχωριστές προκλήσεις.
Ποιοι τύποι στα F9, G9 και H9 θα μετρήσουν σωστά τις παραγγελίες με τις ακόλουθες συνθήκες:
- F9 - Tshirt ή Hoodie
- G9 - (Tshirt ή Hoodie) και (Κόκκινο, Μπλε ή Πράσινο)
- H9 - (Tshirt ή Hoodie) και (Κόκκινο, Μπλε ή Πράσινο) και (Ντένβερ ή Σιάτλ)
Η πράσινη σκίαση εφαρμόζεται με μορφοποίηση υπό όρους και δείχνει τιμές αντιστοίχισης για κάθε σύνολο κριτηρίων Ή σε κάθε στήλη.
Για τη δική σας ευκολία, διατίθενται οι ακόλουθες ονομαστικές περιοχές:
είδος = B3: B16
χρώμα = C3: C16
πόλη = D3: D16
Επισυνάπτεται το φύλλο εργασίας. Αφήστε τις απαντήσεις σας παρακάτω ως σχόλια!
Απάντηση (κάντε κλικ για επέκταση)Η λύση μου χρησιμοποιεί το SUMPRODUCT με ISNUMBER και MATCH ως εξής:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Ποιο θα μετράει παραγγελίες όπου…
- Το αντικείμενο είναι (Tshirt ή Hoodie) και
- Το χρώμα είναι (Κόκκινο, Μπλε ή Πράσινο) και
- Η πόλη είναι (Ντένβερ ή Σιάτλ)
Αρκετοί άνθρωποι πρότειναν επίσης την ίδια προσέγγιση. Μου αρέσει αυτή η δομή γιατί κλιμακώνεται εύκολα για να χειριστεί περισσότερα κριτήρια και λειτουργεί επίσης με αναφορές κυττάρων (αντί για τιμές με σκληρή κωδικοποίηση). Με αναφορές κυττάρων, ο τύπος στο H9 είναι:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Το κλειδί σε αυτόν τον τύπο είναι η κατασκευή ISNUMBER + MATCH. Το MATCH έχει ρυθμιστεί "προς τα πίσω" - οι τιμές αναζήτησης προέρχονται από τα δεδομένα και χρησιμοποιούνται κριτήρια για τον πίνακα. Το αποτέλεσμα είναι ένας πίνακας μίας στήλης κάθε φορά που χρησιμοποιείται MATCH. Αυτός ο πίνακας περιέχει είτε # N / A σφάλματα (χωρίς αντιστοίχιση) είτε αριθμούς (match), οπότε το ISNUMBER χρησιμοποιείται για τη μετατροπή στις δυαδικές τιμές TRUE και FALSE. Η λειτουργία πολλαπλασιασμού των συστοιχιών συνδυάζει τις τιμές TRUE FALSE σε 1s και 0s, και ο τελικός πίνακας στο SUMPRODUCT περιέχει 1s όπου οι σειρές πληρούν τα κριτήρια. Στη συνέχεια, το SUMPRODUCT αθροίζει τον πίνακα και επιστρέφει το αποτέλεσμα.
