Γενική φόρμουλα
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Περίληψη
Για να βρείτε την πλησιέστερη αντιστοίχιση σε αριθμητικά δεδομένα, μπορείτε να χρησιμοποιήσετε INDEX και MATCH, με τη βοήθεια των λειτουργιών ABS και MIN. Στο παράδειγμα που εμφανίζεται, ο τύπος στο F5, αντιγράφεται κάτω, είναι:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
όπου το ταξίδι (B5: B14) και το κόστος (C5: C14) ονομάζονται εύρη.
Στα F5, F6 και F7, ο τύπος επιστρέφει το πλησιέστερο ταξίδι στο κόστος σε 500, 1000 και 1500, αντίστοιχα.
Σημείωση: πρόκειται για έναν τύπο πίνακα και πρέπει να εισαχθεί με τον έλεγχο + shift + enter, εκτός από το Excel 365.
Εξήγηση
Στον πυρήνα, αυτός είναι ένας τύπος INDEX και MATCH: Το MATCH εντοπίζει τη θέση του πλησιέστερου αγώνα, τροφοδοτεί τη θέση στο INDEX και το INDEX επιστρέφει την τιμή σε αυτήν τη θέση στη στήλη Trip. Η σκληρή δουλειά γίνεται με τη λειτουργία MATCH, η οποία είναι προσεκτικά διαμορφωμένη ώστε να ταιριάζει με την "ελάχιστη διαφορά" όπως αυτή:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Λαμβάνοντας τα πράγματα βήμα προς βήμα, η τιμή αναζήτησης υπολογίζεται με MIN και ABS ως εξής:
MIN(ABS(cost-E5)
Πρώτον, η τιμή στο E5 αφαιρείται από το ονομαστικό κόστος εύρους (C5: C14). Πρόκειται για μια λειτουργία πίνακα και δεδομένου ότι υπάρχουν 10 τιμές στο εύρος, το αποτέλεσμα είναι ένας πίνακας με 10 τιμές όπως αυτή:
(899;199;250;-201;495;1000;450;-101;500;795)
Αυτοί οι αριθμοί αντιπροσωπεύουν τη διαφορά μεταξύ κάθε κόστους σε C5: C15 και του κόστους στο κελί E5, 700. Ορισμένες τιμές είναι αρνητικές επειδή το κόστος είναι χαμηλότερο από τον αριθμό στο E5. Για να μετατρέψουμε τις αρνητικές τιμές σε θετικές τιμές, χρησιμοποιούμε τη συνάρτηση ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
που επιστρέφει:
(899;199;250;201;495;1000;450;101;500;795)
Ψάχνουμε για τον πλησιέστερο αγώνα, οπότε χρησιμοποιούμε τη συνάρτηση MIN για να βρούμε τη μικρότερη διαφορά, η οποία είναι 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Αυτό γίνεται η τιμή αναζήτησης μέσα στο MATCH. Ο πίνακας αναζήτησης δημιουργείται όπως πριν:
ABS(cost-E5) // generate lookup array
που επιστρέφει τον ίδιο πίνακα που είδαμε νωρίτερα:
(899;199;250;201;495;1000;450;101;500;795)
Τώρα έχουμε ό, τι χρειαζόμαστε για να βρούμε τη θέση του πλησιέστερου αγώνα (μικρότερη διαφορά) και μπορούμε να ξαναγράψουμε το τμήμα MATCH του τύπου όπως αυτό:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Με το 101 ως τιμή αναζήτησης, το MATCH επιστρέφει 8, καθώς το 101 βρίσκεται στην 8η θέση του πίνακα. Τέλος, αυτή η θέση τροφοδοτείται στο INDEX ως όρισμα σειράς, με το όνομα εύρος ταξιδιού ως πίνακα:
=INDEX(trip,8)
και η INDEX επιστρέφει το 8ο ταξίδι στην περιοχή, "Ισπανία". Όταν ο τύπος αντιγράφεται στα κελιά F6 και F7, βρίσκει την πλησιέστερη αντιστοίχιση με 1000 και 1500, "Γαλλία" και "Ταϊλάνδη" όπως φαίνεται.
Σημείωση: εάν υπάρχει ισοπαλία, αυτός ο τύπος θα επιστρέψει τον πρώτο αγώνα.
Με XLOOKUP
Η συνάρτηση XLOOKUP παρέχει έναν ενδιαφέρον τρόπο για την επίλυση αυτού του προβλήματος, επειδή ένας τύπος αντιστοίχισης 1 (ακριβής αντιστοίχιση ή επόμενος μεγαλύτερος) ή -1 (ακριβής αντιστοίχιση ή επόμενος μικρότερος) δεν απαιτεί ταξινόμηση δεδομένων. Αυτό σημαίνει ότι μπορούμε να γράψουμε έναν τύπο ως εξής:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Όπως παραπάνω, χρησιμοποιούμε την απόλυτη τιμή του (cost-E5) για να δημιουργήσουμε έναν πίνακα αναζήτησης:
(899;199;250;201;495;1000;450;101;500;795)
Στη συνέχεια, διαμορφώνουμε το XLOOKUP για να ψάχνουμε για μηδέν, με τον τύπο αντιστοίχισης που έχει οριστεί σε 1, για ακριβή αντιστοίχιση ή επόμενο μεγαλύτερο. Παρέχουμε το ονομαστικό ταξίδι εύρους ως πίνακα επιστροφής, οπότε το αποτέλεσμα είναι "Ισπανία" όπως πριν.
