Γενική φόρμουλα
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1
Περίληψη
Για να εντοπίσετε τη θέση μιας τιμής σε έναν πίνακα 2D, μπορείτε να χρησιμοποιήσετε τη συνάρτηση SUMPRODUCT. Στο παράδειγμα που εμφανίζεται, οι τύποι που χρησιμοποιούνται για τον εντοπισμό των αριθμών γραμμής και στηλών της μέγιστης τιμής στον πίνακα είναι:
=SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 =SUMPRODUCT((data=MAX(data))*COLUMN(data))-COLUMN(data)+1
όπου "δεδομένα" είναι το ονομαζόμενο εύρος C5: G14.
Σημείωση: για αυτό το παράδειγμα, βρίσκουμε αυθαίρετα τη θέση της μέγιστης τιμής στα δεδομένα, αλλά μπορείτε να αντικαταστήσετε τα δεδομένα = MAX (δεδομένα) με οποιαδήποτε άλλη λογική δοκιμή που θα απομονώσει μια δεδομένη τιμή. Σημειώστε επίσης ότι αυτοί οι τύποι θα αποτύχουν εάν υπάρχουν διπλές τιμές στον πίνακα.
Εξήγηση
Για να λάβετε τον αριθμό σειράς, τα δεδομένα συγκρίνονται με τη μέγιστη τιμή, η οποία δημιουργεί μια σειρά αποτελεσμάτων TRUE FALSE. Αυτοί πολλαπλασιάζονται με το αποτέλεσμα του ROW (δεδομένα) που δημιουργεί και τον πίνακα αριθμών σειρών που σχετίζονται με το ονομαζόμενο εύρος "δεδομένα"
=SUMPRODUCT((FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,TRUE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE)*(5;6;7;8;9;10;11))
Η λειτουργία πολλαπλασιασμού αναγκάζει το Excel να εξαναγκάσει τις τιμές TRUE FALSE στον πρώτο πίνακα σε 1s και 0s, ώστε να μπορούμε να απεικονίσουμε ένα ενδιάμεσο βήμα όπως αυτό:
=SUMPRODUCT((0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,0,0,0;0,0,1,0,0;0,0,0,0,0;0,0,0,0,0)*(5;6;7;8;9;10;11))
Στη συνέχεια, το SUMPRODUCT επιστρέφει ένα αποτέλεσμα του 9, το οποίο αντιστοιχεί στην 9η σειρά στο φύλλο εργασίας. Για να λάβουμε ένα ευρετήριο σε σχέση με το ονομαζόμενο εύρος "δεδομένα", χρησιμοποιούμε:
-ROW(data)+1
Το τελικό αποτέλεσμα είναι ο πίνακας (5; 4; 3; 2; 1; 0; -1), από τον οποίο εμφανίζεται μόνο η πρώτη τιμή (5).
Ο τύπος για τον προσδιορισμό της θέσης της στήλης λειτουργεί με τον ίδιο τρόπο.
Σημείωση: Συνάντησα αυτήν την προσέγγιση σε ένα σχόλιο του Mike Erickson στο MrExcel.com. Υπάρχουν και άλλες καλές ιδέες σε αυτό το νήμα, συμπεριλαμβανομένης μιας επιλογής τύπου πίνακα.
