Περίληψη
Για να πραγματοποιήσετε μια αμφίδρομη αναζήτηση κατά προσέγγιση αντιστοίχισης με πολλαπλά κριτήρια, μπορείτε να χρησιμοποιήσετε έναν τύπο πίνακα που βασίζεται σε INDEX και MATCH, με βοήθεια από τη λειτουργία IF για να εφαρμόσετε κριτήρια. Στο παράδειγμα που εμφανίζεται, ο τύπος στο K8 είναι:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
όπου τα δεδομένα (D6: H16), διάμετρος (D5: H5), υλικό (B6: B16) και σκληρότητα (C6: C16) ονομάζονται περιοχές που χρησιμοποιούνται μόνο για ευκολία.
Σημείωση: αυτός είναι ένας τύπος πίνακα και πρέπει να εισαχθεί με Control + Shift + Enter
Εξήγηση
Ο στόχος είναι να αναζητήσετε έναν ρυθμό τροφοδοσίας με βάση το υλικό, τη σκληρότητα και τη διάμετρο των τρυπανιών. Οι τιμές του ρυθμού τροφοδοσίας βρίσκονται στα δεδομένα εύρους ονομασίας (D6: H16).
Αυτό μπορεί να γίνει με έναν αμφίδρομο τύπο INDEX και MATCH. Μια συνάρτηση MATCH υπολογίζει τον αριθμό σειράς (υλικό και σκληρότητα) και η άλλη συνάρτηση MATCH βρίσκει τον αριθμό στήλης (διάμετρος). Η συνάρτηση INDEX επιστρέφει το τελικό αποτέλεσμα.
Στο παράδειγμα που εμφανίζεται, ο τύπος στο K8 είναι:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Προστέθηκαν αλλαγές γραμμής μόνο για αναγνωσιμότητα).
Το δύσκολο κομμάτι είναι ότι το υλικό και η σκληρότητα πρέπει να αντιμετωπιστούν μαζί. Πρέπει να περιορίσουμε το MATCH στις τιμές σκληρότητας για ένα δεδομένο υλικό (Low Carbon Steel στο παράδειγμα που φαίνεται).
Μπορούμε να το κάνουμε με τη λειτουργία IF. Ουσιαστικά, χρησιμοποιούμε IF για να "πετάξουμε" άσχετες τιμές προτού αναζητήσουμε έναν αγώνα.
Λεπτομέριες
Στη συνάρτηση INDEX δίνεται το ονομασμένο εύρος δεδομένων (D6: H16) όπως για τον πίνακα. Η πρώτη συνάρτηση MATCH επεξεργάζεται τον αριθμό σειράς:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Για να εντοπίσουμε τη σωστή σειρά, πρέπει να κάνουμε μια ακριβή αντιστοίχιση στο υλικό και μια κατά προσέγγιση αντιστοίχιση στη σκληρότητα. Αυτό το κάνουμε χρησιμοποιώντας τη συνάρτηση IF για να φιλτράρετε πρώτα τη μη σχετική σκληρότητα:
IF(material=K5,hardness) // filter
Δοκιμάζουμε όλες τις τιμές στο υλικό (B6: B16) για να δούμε αν ταιριάζουν με την τιμή στο K5 ("Χάλυβας χαμηλού άνθρακα"). Εάν ναι, η τιμή σκληρότητας περνά. Εάν όχι, το IF επιστρέφει FALSE. Το αποτέλεσμα είναι ένας πίνακας ως εξής:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Σημειώστε ότι οι μόνες επιζώντες τιμές είναι αυτές που σχετίζονται με τον χαμηλό άνθρακα χάλυβα. Οι άλλες τιμές είναι τώρα FALSE. Αυτός ο πίνακας επιστρέφεται απευθείας στη συνάρτηση MATCH ως το lookup_array.
Η τιμή αναζήτησης για αντιστοίχιση προέρχεται από το K6, το οποίο περιέχει τη δεδομένη σκληρότητα, 176. Το MATCH διαμορφώνεται για κατά προσέγγιση αντιστοίχιση, ορίζοντας το match_type σε 1. Με αυτές τις ρυθμίσεις, το MATCH αγνοεί τις τιμές FALSE και επιστρέφει τη θέση ενός ακριβούς αγώνα ή την επόμενη μικρότερη τιμή .
Σημείωση: οι τιμές σκληρότητας πρέπει να ταξινομηθούν σε αύξουσα σειρά για κάθε υλικό.
Με σκληρότητα ως 176, το MATCH επιστρέφει 6, παραδίδεται απευθείας στο INDEX ως αριθμό σειράς. Τώρα μπορούμε να ξαναγράψουμε τον αρχικό τύπο ως εξής:
=INDEX(data,6,MATCH(K7,diameter,1))
Ο δεύτερος τύπος MATCH βρίσκει τον σωστό αριθμό στήλης εκτελώντας κατά προσέγγιση αντιστοίχιση στη διάμετρο:
MATCH(K7,diameter,1) // get column num
Σημείωση: οι τιμές σε διάμετρο D5: H5 πρέπει να ταξινομηθούν σε αύξουσα σειρά.
Η τιμή αναζήτησης προέρχεται από το K7 (0,75) και το lookup_array είναι η ονομαστική διάμετρος εύρους (D5: H5).
Όπως και προηγουμένως, το MATCH έχει ρυθμιστεί στην κατά προσέγγιση αντιστοίχιση, ορίζοντας το match_type σε 1.
Με διάμετρο ως 0,75, το MATCH επιστρέφει 3, παραδίδεται απευθείας στη συνάρτηση INDEX ως αριθμός στήλης. Ο αρχικός τύπος επιλύει τώρα:
=INDEX(data,6,3) // returns 0.015
Το INDEX επιστρέφει ένα τελικό αποτέλεσμα 0,015, η τιμή από το F11.
