Περίληψη
Για να μετρήσετε σειρές με λογική OR, μπορείτε να χρησιμοποιήσετε έναν τύπο που βασίζεται στη συνάρτηση SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο G6 είναι:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
όπου η ομάδα (B5: B15), το χρώμα1 (C5: C15) και το χρώμα2 (D5: D15) ονομάζονται περιοχές.
Εξήγηση
Ένα από τα πιο δύσκολα προβλήματα στο Excel είναι η μέτρηση σειρών σε ένα σύνολο δεδομένων με "λογική Ή". Υπάρχουν δύο βασικά σενάρια: (1) θέλετε να μετρήσετε σειρές όπου μια τιμή σε μια στήλη είναι "x" Ή "y" (2) θέλετε να μετρήσετε σειρές όπου μια τιμή, "x", υπάρχει σε μία στήλη Ή άλλη .
Σε αυτό το παράδειγμα, ο στόχος είναι η μέτρηση σειρών όπου η ομάδα = "a" AND Color1 Ή Color2 είναι "κόκκινη". Αυτό σημαίνει ότι εργαζόμαστε με το σενάριο 2 παραπάνω.
Με COUNTIFS
Μπορεί να φτάσετε στην αρχή για τη συνάρτηση COUNTIFS, η οποία χειρίζεται πολλά κριτήρια φυσικά. Ωστόσο, η συνάρτηση COUNTIFS ενώνει τις συνθήκες με τη λογική AND, οπότε όλα τα κριτήρια πρέπει να είναι ΑΛΗΘΕΙΑ για να συμπεριληφθούν στο πλήθος:
=COUNTIFS(group,"a",color1,"red",color2,"red") // returns 1
Αυτό καθιστά COUNTIFS ανεφάρμοστο, εκτός και αν χρησιμοποιούμε πολλές παρουσίες COUNTIFS:
=COUNTIFS(group,"a",color1,"red")+COUNTIFS(group,"a",color2,"red")-COUNTIFS(group,"a",color1,"red",color2,"red")
Μετάφραση: μέτρηση σειρών όπου η ομάδα είναι "a" και το color1 είναι "κόκκινο" + μέτρηση σειρών όπου η ομάδα είναι "a" και το color2 είναι "κόκκινο" - μέτρηση σειρών όπου η ομάδα είναι "a" και το color1 είναι "κόκκινο" και το color2 είναι " κόκκινο "(για να αποφύγετε τη διπλή μέτρηση).
Αυτό λειτουργεί, αλλά μπορείτε να δείτε ότι είναι μια κάπως περίπλοκη και περιττή φόρμουλα.
Με λογική Boolean
Μια καλύτερη λύση είναι να χρησιμοποιήσετε τη λογική Boolean και να επεξεργαστείτε το αποτέλεσμα με τη συνάρτηση SUMPRODUCT. (Εάν χρειάζεστε ένα αστάρι στην άλγεβρα Boolean, αυτό το βίντεο παρέχει μια εισαγωγή.) Στο παράδειγμα που εμφανίζεται, ο τύπος στο G6 είναι:
=SUMPRODUCT((group="a")*((color1="red")+(color2="red")>0))
όπου η ομάδα (B5: B15), το χρώμα1 (C5: C15) και το χρώμα2 (D5: D15) ονομάζονται περιοχές.
Το πρώτο μέρος του προβλήματος είναι να δοκιμάσετε για την ομάδα = "a" που κάνουμε έτσι:
(group="a")
Επειδή το εύρος B5: B15 περιέχει 11 κελιά, αυτή η έκφραση επιστρέφει έναν πίνακα 11 τιμών TRUE και FALSE όπως αυτό:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Κάθε TRUE αντιπροσωπεύει μια σειρά όπου η ομάδα είναι "A"
Στη συνέχεια, πρέπει να ελέγξουμε για την τιμή "κόκκινο" είτε στη στήλη1 είτε στη στήλη2. Το κάνουμε αυτό με δύο εκφράσεις που ενώνονται με την προσθήκη (+), καθώς η προσθήκη αντιστοιχεί στη λογική OR στην άλγεβρα Boolean:
(color1="red")+(color2="red")
Το Excel αξιολογεί αυτόματα τις τιμές TRUE και FALSE ως 1s και 0s κατά τη διάρκεια οποιασδήποτε μαθηματικής λειτουργίας, επομένως το αποτέλεσμα από την παραπάνω έκφραση είναι ένας πίνακας όπως αυτός:
(2;0;0;1;1;0;1;0;0;0;1)
Ο πρώτος αριθμός στη συστοιχία είναι 2, επειδή τόσο το Color1 όσο και το Color2 είναι "κόκκινο" στην πρώτη σειρά. Για λόγους που εξηγούνται παρακάτω, πρέπει να προφυλάξουμε αυτήν την κατάσταση ελέγχοντας τιμές μεγαλύτερες από το μηδέν:
((2;0;0;1;1;0;1;0;0;0;1))>0
Τώρα έχουμε και πάλι μια σειρά από ΑΛΗΘΕΙΑ και ΛΑΘΗ τιμές
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Ο παρακάτω πίνακας συνοψίζει τον τρόπο με τον οποίο το Excel αξιολογεί τη λογική χρωμάτων που εξηγείται παραπάνω:
Σε αυτό το σημείο, έχουμε αποτελέσματα από τη δοκιμή Group = "a" σε έναν πίνακα:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE)
Και τα αποτελέσματα από τη δοκιμή "κόκκινο" στο Color1 ή Color2 σε άλλο πίνακα:
(TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Το επόμενο βήμα είναι να συνδυάσετε αυτές τις δύο συστοιχίες με το "AND logic". Για να γίνει αυτό, χρησιμοποιούμε τον πολλαπλασιασμό (*), καθώς ο πολλαπλασιασμός αντιστοιχεί στη λογική AND στη άλγεβρα Boolean.
Μετά τον πολλαπλασιασμό των δύο συστοιχιών μαζί, έχουμε έναν ενιαίο πίνακα 1s και 0s, ο οποίος παραδίδεται απευθείας στη συνάρτηση SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;1;0;0;0;0;0;0))
Η συνάρτηση SUMPRODUCT επιστρέφει το άθροισμα των αριθμών, 2, ως τελικό αποτέλεσμα. Αυτή είναι η μέτρηση των σειρών όπου η ομάδα = "a" AND Color1 OR Color2 είναι "κόκκινη".
Για να αποφύγετε τη διπλή μέτρηση
Δεν θέλουμε να διπλασιάσουμε τις σειρές όπου τόσο το Color1 όσο και το Color2 είναι "κόκκινο". Γι 'αυτό ελέγχουμε τα αποτελέσματα από (color1 = "red") + (color2 = "red") για τιμές μεγαλύτερες από το μηδέν στον παρακάτω κώδικα:
((color1="red")+(color2="red"))>0
Χωρίς αυτόν τον έλεγχο, τα 2 από την πρώτη σειρά στα δεδομένα θα εμφανιστούν στον τελικό πίνακα και θα προκαλούσαν εσφαλμένη επιστροφή του τύπου 3 ως τον τελικό αριθμό.
Επιλογή ΦΙΛΤΡΟΥ
Ένα ωραίο πράγμα για τη λογική Boolean είναι ότι λειτουργεί τέλεια με τις νεότερες λειτουργίες του Excel, όπως το XLOOKUP και το FILTER. Για παράδειγμα, η συνάρτηση FILTER μπορεί να χρησιμοποιήσει ακριβώς την ίδια λογική που εξηγήθηκε παραπάνω για να εξαγάγει τις αντίστοιχες σειρές:
=FILTER(B5:D15,(group="a")*((color1="red")+(color2="red")>0))
Το αποτέλεσμα από το FILTER είναι οι δύο σειρές που πληρούν τα κριτήρια, όπως φαίνεται παρακάτω:
Εάν θέλετε να μάθετε περισσότερα σχετικά με αυτές τις νέες λειτουργίες, έχουμε μια επισκόπηση και εκπαίδευση βίντεο.
