Περίληψη
Για να εμφανίσετε μια λίστα ονομάτων, ταξινομημένα με αριθμητική τιμή, μπορείτε να χρησιμοποιήσετε ένα σύνολο τύπων που βασίζονται σε ΜΕΓΑΛΗ, ΔΕΙΚΤΗΣ, ΜΑΡΤΥΡΙΑ, με βοήθεια από τη συνάρτηση TEXT. Στο παράδειγμα που εμφανίζεται, ο τύπος στο G5 είναι:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Και ο τύπος στο G10 είναι:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
όπου ο πελάτης (B5: B17) ημερομηνία (C5: C17) και το ποσό (C5: C17) ονομάζονται εύρη.
Σημείωση: αυτοί είναι τύποι συστοιχιών και πρέπει να εισαχθούν με το control + shift + enter, εκτός από το Excel 365.
Εξήγηση
Αυτό το παράδειγμα διαμορφώνεται σε δύο μέρη για σαφήνεια: (1) ένας τύπος για τον προσδιορισμό των 3 κορυφαίων ποσών για κάθε μήνα και (2) ένας τύπος για την ανάκτηση του ονόματος πελάτη για καθένα από τα 3 κορυφαία μηνιαία ποσά.
Σημειώστε ότι δεν υπάρχει πραγματική κατάταξη στα δεδομένα προέλευσης. Αντ 'αυτού, χρησιμοποιούμε τη συνάρτηση LARGE για να εργαζόμαστε άμεσα με ποσά. Μια άλλη προσέγγιση θα ήταν η προσθήκη κατάταξης στα δεδομένα προέλευσης με τη συνάρτηση RANK και η χρήση της τιμής κατάταξης για την ανάκτηση ονομάτων πελατών.
Μέρος 1: ανάκτηση των κορυφαίων 3 ποσών κάθε μήνα
Για να ανακτήσετε τα 3 κορυφαία ποσά για κάθε εβδομάδα, ο τύπος στο G5 είναι:
=LARGE(IF(TEXT(date,"mmmm")=G$4,amount),$F5)
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter, εκτός από το Excel 365.
Δουλεύοντας από μέσα προς τα έξω, χρησιμοποιούμε πρώτα τη συνάρτηση TEXT για να λάβουμε ονόματα μήνα για κάθε ημερομηνία στην ονομαστική ημερομηνία εύρους :
TEXT(date,"mmmm") // get month names
Η προσαρμοσμένη μορφή αριθμού "mmmm" θα επιστρέψει μια συμβολοσειρά όπως "Απρίλιος", "Μάιος", "Ιούνιος" για κάθε όνομα στην ονομαζόμενη ημερομηνία εύρους . Το αποτέλεσμα είναι μια σειρά ονομάτων μήνα όπως αυτό:
("April";"April";"April";"April";"May";"May";"May";"May";"May";"June";"June";"June";"June")
Η συνάρτηση TEXT παραδίδει αυτόν τον πίνακα στη συνάρτηση IF, η οποία έχει διαμορφωθεί για να φιλτράρει ημερομηνίες σε έναν δεδομένο μήνα, δοκιμάζοντας το όνομα του μήνα έναντι της τιμής στο G4 (μια μικτή αναφορά, έτσι ο τύπος μπορεί να αντιγραφεί κάτω και απέναντι):
IF(TEXT(date,"mmmm")=G$4,amount) // filter on month
Μόνο ποσά τον Απρίλιο επιβιώνουν και τα καταφέρνουν μέσω IF όλες οι άλλες τιμές είναι FALSE:
(10500;15200;18500;12500;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Τέλος, η συνάρτηση LARGE χρησιμοποιεί την τιμή στο F5 (επίσης μια μικτή αναφορά) για να επιστρέψει τη "nth" μεγαλύτερη τιμή που απομένει. Στο κελί G5, το LARGE επιστρέφει 18.500, τη "1η" μεγαλύτερη τιμή. Καθώς ο τύπος αντιγράφεται προς τα κάτω και σε ολόκληρο τον πίνακα, η συνάρτηση LARGE επιστρέφει τα 3 κορυφαία ποσά σε καθέναν από τους τρεις μήνες.
Τώρα που γνωρίζουμε τις 3 κορυφαίες τιμές κάθε μήνα, μπορούμε να χρησιμοποιήσουμε αυτές τις πληροφορίες σαν "κλειδί" για να ανακτήσουμε το όνομα πελάτη για κάθε ένα.
Μέρος 2: ανάκτηση ονομάτων πελατών
Σημείωση: Αυτό είναι ένα παράδειγμα χρήσης INDEX και MATCH με πολλαπλά κριτήρια. Εάν αυτή η ιδέα είναι νέα για εσάς, εδώ είναι ένα βασικό παράδειγμα.
Για να ανακτήσουμε το όνομα που σχετίζεται με τις τρεις πρώτες τιμές στο G5: I7, χρησιμοποιούμε INDEX και MATCH:
=INDEX(client,MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0))
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter, εκτός από το Excel 365.
Λειτουργώντας από μέσα προς τα έξω, η συνάρτηση MATCH έχει διαμορφωθεί έτσι ώστε να χρησιμοποιεί λογική Boolean ως εξής:
MATCH(1,(amount=G5)*(TEXT(date,"mmmm")=G$9),0)
Η τιμή αναζήτησης είναι 1 και ο πίνακας αναζήτησης είναι κατασκευασμένος με αυτήν την έκφραση:
(amount=G5)*(TEXT(date,"mmmm")=G$9)
Η παράσταση που δημιουργεί τον πίνακα αναζήτησης χρησιμοποιεί λογική Boolean για να "φιλτράρει" ποσά που (1) όχι τον Απρίλιο και (2) όχι η τιμή στο G5 (18.500). Το αποτέλεσμα είναι ένας πίνακας 1s και 0s ως εξής:
(0;0;1;0;0;0;0;0;0;0;0;0;0)
Με τιμή αναζήτησης 1 και μηδέν για τον τύπο αγώνα (για να εξαναγκάσετε έναν ακριβή αγώνα), το MATCH επιστρέφει 3 απευθείας στη συνάρτηση INDEX
=INDEX(client,3) // returns "Janus"
Το INDEX επιστρέφει την τρίτη τιμή στο όνομα πελάτη εύρους, "Janus".
Καθώς ο τύπος αντιγράφεται κάτω και πέρα από τον πίνακα, επιστρέφει τους 3 πρώτους πελάτες σε καθέναν από τους τρεις μήνες.
