Γνωρίζουμε ότι η συνάρτηση COUNTIFS στο Excel μπορεί να βασίζεται σε πολλά κριτήρια. Λαμβάνει επιχειρήματα ως ζεύγος κριτηρίων εύρους και κριτηρίων. Μπορούμε να αλλάξουμε τα κριτήρια δυναμικά δίνοντας την αναφορά του κελιού, αλλά δεν μπορούμε να αλλάξουμε δυναμικά τη στήλη κριτηρίων. Λοιπόν, όχι άμεσα, αλλά μπορούμε. Αυτό μαθαίνουμε εκ των προτέρων τύπους excel. Κάνοντας πράγματα σε excel που δεν μπορούν να γίνουν κανονικά. Ας δούμε πώς.
Ας μάθουμε με το παράδειγμα.
Συμφραζόμενα
Εδώ έχω ετοιμάσει δεδομένα πωλήσεων που πραγματοποιήθηκαν σε διαφορετικούς μήνες του έτους από τους συμβούλους πωλήσεών μας. Πωλούν διαφορετικά μοντέλα του προϊόντος μας, γενικά ονομάζονται model1, model2 και ούτω καθεξής. Αυτό που πρέπει να κάνουμε είναι να μετράμε δυναμικά τις πωλήσεις διαφορετικών μοντέλων σε διαφορετικούς μήνες.
Στο κελί I2 θα επιλέξουμε τον μήνα. Στο κελί I2 θα επιλέξουμε το μοντέλο. Αυτές οι τιμές μπορεί να αλλάξουν. Και η καταμέτρηση πρέπει επίσης να αλλάξει. Η συνάρτηση COUNTIFS θα πρέπει να αναζητήσει τη στήλη μήνα που θα είναι εύρος κριτηρίων. Στη συνέχεια, θα αναζητήσει μοντέλο στη στήλη εκείνων των μηνών.
Έτσι, εδώ τα κριτήρια και το κριτήριο_διάταγμα είναι και τα δύο μεταβλητά. Πώς μπορούμε λοιπόν να κάνουμε μεταβλητή στήλη στα COUNTIFS; Εδώ είναι πώς;
Χρήση ονόματος εύρους για μεταβλητή στήλη ή εύρος κριτηρίων
Γενικός τύπος
= COUNTIFS (INDIRECT (ονομασία_οριο), κριτήρια)
Αρχικά, ονομάστε κάθε στήλη σύμφωνα με τις επικεφαλίδες τους. Για να το κάνετε αυτό, επιλέξτε τον πίνακα και πατήστε CTRL+SHIFT+F3 και ονομάστε τις στήλες σύμφωνα με την επάνω σειρά. Διαβάστε σχετικά εδώ.
Έτσι, το εύρος B3: B11, C3: C11, D3: D11 και E3: E11 ονομάζονται Jan, Feb, Mar και Apr αντίστοιχα.
Γράψτε αυτόν τον τύπο στο I4.
= COUNTIFS (INDIRECT (I2), I3)
Τώρα αν αλλάξετε τον μήνα στο I4, ο αντίστοιχος αριθμός μηνών του Model4 θα εμφανιστεί στο I4.
Πως δουλεύει?
Ο τύπος είναι απλός. Ας ξεκινήσουμε από μέσα.
INDIRECT (I2): Όπως γνωρίζουμε η λειτουργία INDIRECT μετατρέπει το κείμενο ref σε πραγματική αναφορά. Το παρέχουμε I2. Το I2 περιέχει Απρ. Δεδομένου ότι έχουμε το εύρος E3: E11 όνομα Apr, INDIRECT (I2) μεταφράζεται σε E3: E11.
Ο τύπος απλοποιήθηκε σε = COUNTIFS (E3: E11, I3). Το COUNTIFS μετρά ό, τι υπάρχει στο I3 στο εύρος E3: E11.
Όταν αλλάζετε μήνα, η στήλη θα αλλάξει δυναμικά. Αυτό ονομάζεται COUNTIFS με στήλες μεταβλητής. Στο gif έχω χρησιμοποιήσει κάποια μορφοποίηση υπό όρους που βασίζεται σε άλλο κελί.
Ο τύπος μπορεί επίσης να λειτουργήσει με συνάρτηση countif. Αλλά εάν θέλετε να έχετε πολλαπλές συνθήκες, χρησιμοποιήστε τη λειτουργία COUNTIFS.
Δημιουργικό γράφημα στηλών που περιλαμβάνει σύνολα
Δημιουργήστε διάγραμμα επικάλυψης στο Excel 2016
Εκτέλεση γραφήματος και ανάλυσης Pareto στο Excel
Εκτέλεση γραφήματος καταρράκτη στο Excel
Excel Sparklines: The Tiny Charts in Cell
Γράφημα ταχύμετρου (μετρητή) στο Excel 2016