Γενική φόρμουλα
=MAX(INDEX(data,0,MATCH(column,header,0)))
Περίληψη
Για να ανακτήσετε τη μέγιστη τιμή σε ένα σύνολο δεδομένων, όπου η στήλη είναι μεταβλητή, μπορείτε να χρησιμοποιήσετε INDEX και MATCH μαζί με τη συνάρτηση MAX. Στο παράδειγμα που εμφανίζεται ο τύπος στο J5 είναι:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
όπου τα δεδομένα (B5: F15) και η κεφαλίδα (B4: F4) ονομάζονται εύρη.
Εξήγηση
Σημείωση: Εάν είστε νέοι στο INDEX και MATCH, δείτε: Πώς να χρησιμοποιήσετε το INDEX και MATCH
Σε μια τυπική διαμόρφωση, η συνάρτηση INDEX ανακτά μια τιμή σε μια δεδομένη σειρά και στήλη. Για παράδειγμα, για να λάβετε την τιμή στη σειρά 2 και στη στήλη 3 σε ένα συγκεκριμένο εύρος:
=INDEX(range,2,3) // get value at row 2, column 3
Ωστόσο, το INDEX έχει ένα ειδικό τέχνασμα - τη δυνατότητα ανάκτησης ολόκληρων στηλών και σειρών. Η σύνταξη περιλαμβάνει την παροχή μηδέν για το όρισμα "other". Εάν θέλετε μια ολόκληρη στήλη, παρέχετε τη σειρά ως μηδέν. Εάν θέλετε μια ολόκληρη σειρά, παρέχετε τη στήλη ως μηδέν:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
Στο παράδειγμα που εμφανίζεται, θέλουμε να βρούμε τη μέγιστη τιμή σε μια δεδομένη στήλη. Η ανατροπή είναι ότι η στήλη πρέπει να είναι μεταβλητή, ώστε να μπορεί εύκολα να αλλάξει. Στο F5, ο τύπος είναι:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Δουλεύοντας από μέσα προς τα έξω, χρησιμοποιούμε πρώτα τη συνάρτηση MATCH για να πάρουμε το "ευρετήριο" της στήλης που ζητήθηκε στο κελί J4:
MATCH(J4,header,0) // get column index
Με το "Green" στο J4, η συνάρτηση MATCH επιστρέφει 3, καθώς το Green είναι η τρίτη τιμή στην επικεφαλίδα εύρους . Αφού το MATCH επιστρέψει ένα αποτέλεσμα, ο τύπος μπορεί να απλοποιηθεί σε αυτό:
=MAX(INDEX(data,0,3))
Με μηδέν που παρέχεται ως αριθμός σειράς, το INDEX επιστρέφει όλες τις τιμές στη στήλη 3 των ονομασμένων δεδομένων εύρους . Το αποτέλεσμα επιστρέφεται στη συνάρτηση MAX σε έναν πίνακα όπως αυτό:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Και το MAX επιστρέφει το τελικό αποτέλεσμα, 93.
Ελάχιστη τιμή
Για να λάβετε την ελάχιστη τιμή με μια μεταβλητή στήλη, μπορείτε απλά να αντικαταστήσετε τη συνάρτηση MAX με τη συνάρτηση MIN. Ο τύπος στο J6 είναι:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Με ΦΙΛΤΡΟ
Η νέα συνάρτηση FILTER μπορεί επίσης να χρησιμοποιηθεί για την επίλυση αυτού του προβλήματος, καθώς το FILTER μπορεί να φιλτράρει δεδομένα ανά σειρά ή ανά στήλη. Το κόλπο είναι να δημιουργήσετε ένα λογικό φίλτρο που θα αποκλείει άλλες στήλες. COUNTIF λειτουργεί καλά σε αυτήν την περίπτωση, αλλά πρέπει να διαμορφωθεί "προς τα πίσω", με J4 ως εύρος και κεφαλίδα για κριτήρια:
=MAX(FILTER(data,COUNTIF(J4,header)))
Μετά την εκτέλεση του COUNTIF, έχουμε:
=MAX(FILTER(data,(0,0,1,0,0)))
Και το FILTER παρέχει την 3η στήλη στο MAX, όπως και η συνάρτηση INDEX παραπάνω.
Ως εναλλακτική λύση για το COUNTIF, μπορείτε να χρησιμοποιήσετε το ISNUMBER + MATCH αντί:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
Η συνάρτηση MATCH έχει ρυθμιστεί ξανά "προς τα πίσω", έτσι ώστε να έχουμε έναν πίνακα με 5 τιμές που θα χρησιμεύσουν ως λογικό φίλτρο. Μετά την εκτέλεση του ISNUMBER και του MATCH, έχουμε:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Και το FILTER παραδίδει ξανά την 3η στήλη στο MAX.
