Οι λειτουργίες του Excel μπορεί να είναι εξαιρετικά ισχυρές εάν συνδυάζεστε με διαφορετικούς τύπους. Πράγματα που μπορεί να φαίνονταν αδύνατα ξαφνικά θα έμοιαζαν με παιδικό παιχνίδι.
Ένα τέτοιο παράδειγμα είναι να βρείτε την πλησιέστερη αντιστοίχιση μιας τιμής αναζήτησης σε ένα σύνολο δεδομένων στο Excel.
Υπάρχουν μερικές χρήσιμες λειτουργίες αναζήτησης στο Excel (όπως VLOOKUP & INDEX MATCH), οι οποίες μπορούν να βρουν την πλησιέστερη αντιστοίχιση σε μερικές απλές περιπτώσεις (όπως θα δείξω με παραδείγματα παρακάτω).
Αλλά το καλύτερο μέρος είναι ότι μπορείτε να συνδυάσετε αυτές τις λειτουργίες αναζήτησης με άλλες λειτουργίες του Excel για να κάνετε πολλά περισσότερα (συμπεριλαμβανομένης της εύρεσης της πλησιέστερης αντιστοίχισης μιας τιμής αναζήτησης σε μια μη ταξινομημένη λίστα).
Σε αυτό το σεμινάριο, θα σας δείξω πώς μπορείτε να βρείτε την πλησιέστερη αντιστοίχιση μιας τιμής αναζήτησης στο Excel με τύπους αναζήτησης.
Βρείτε την πιο κοντινή αντιστοίχιση στο Excel
Μπορεί να υπάρχουν πολλά διαφορετικά σενάρια όπου πρέπει να αναζητήσετε την πλησιέστερη αντιστοίχιση (ή την πλησιέστερη τιμή αντιστοίχισης).
Παρακάτω είναι τα παραδείγματα που θα καλύψω σε αυτό το άρθρο:
- Βρείτε το ποσοστό προμήθειας με βάση τις πωλήσεις
- Βρείτε τον καλύτερο υποψήφιο (με βάση την πλησιέστερη εμπειρία)
- Εύρεση της επόμενης ημερομηνίας εκδήλωσης
Ας αρχίσουμε!
Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου
Εύρεση τιμής προμήθειας (Αναζητώντας την πλησιέστερη αξία πωλήσεων)
Ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω, όπου θέλετε να βρείτε τα ποσοστά προμήθειας όλου του προσωπικού πωλήσεων.
Η προμήθεια εκχωρείται με βάση την αξία πώλησης. Και αυτό υπολογίζεται χρησιμοποιώντας τον πίνακα στα δεξιά.
Για παράδειγμα, εάν ένας πωλητής κάνει τις συνολικές πωλήσεις των 5000, τότε η προμήθεια είναι 0% και εάν κάνει τις συνολικές πωλήσεις των 15000, τότε η προμήθεια είναι 5%.
Για να λάβετε το επιτόκιο προμήθειας, πρέπει να βρείτε το πλησιέστερο εύρος πώλησης ακριβώς χαμηλότερο από την αξία πώλησης. Για παράδειγμα, για αξία πώλησης 15000, η προμήθεια θα είναι 10.000 (που είναι 5%) και για αξία πώλησης 25000, το ποσοστό προμήθειας θα είναι 20.000 (που είναι 7%).
Για να βρείτε την πλησιέστερη τιμή πώλησης και να λάβετε το ποσοστό προμήθειας, μπορείτε να χρησιμοποιήσετε την κατά προσέγγιση αντιστοίχιση στο VLOOKUP.
Ο παρακάτω τύπος θα το κάνει αυτό:
= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)
Σημειώστε ότι σε αυτόν τον τύπο, το τελευταίο όρισμα είναι 1, το οποίο λέει στον τύπο να χρησιμοποιεί μια κατά προσέγγιση αναζήτηση. Αυτό σημαίνει ότι ο τύπος θα περάσει από τις τιμές πώλησης στη στήλη Ε και θα βρει την τιμή που είναι ακριβώς χαμηλότερη από την τιμή αναζήτησης.
Στη συνέχεια, ο τύπος VLOOKUP θα δώσει το ποσοστό προμήθειας για αυτήν την τιμή.
Σημείωση: Για να λειτουργήσει αυτό, πρέπει να έχετε ταξινομήσει τα δεδομένα κατά αύξουσα σειρά.Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου
Βρείτε τον καλύτερο υποψήφιο (με βάση την πλησιέστερη εμπειρία)
Στο παραπάνω παράδειγμα, τα δεδομένα έπρεπε να ταξινομηθούν με αύξουσα σειρά. Αλλά θα μπορούσαν να υπάρχουν περιπτώσεις όπου τα δεδομένα δεν είναι ταξινομημένα.
Ας καλύψουμε λοιπόν ένα παράδειγμα και θα δούμε πώς μπορούμε να βρούμε την πλησιέστερη αντιστοίχιση στο Excel χρησιμοποιώντας έναν συνδυασμό τύπων.
Παρακάτω είναι ένα δείγμα συνόλου δεδομένων όπου πρέπει να βρω το όνομα του υπαλλήλου που έχει την εργασιακή εμπειρία πιο κοντά στην επιθυμητή τιμή. Η επιθυμητή τιμή σε αυτή την περίπτωση σε 2,5 χρόνια.
Λάβετε υπόψη ότι τα δεδομένα δεν είναι ταξινομημένα. Επίσης, η πλησιέστερη εμπειρία μπορεί να είναι είτε μικρότερη είτε μεγαλύτερη από την εμπειρία προσφοράς. Για παράδειγμα, 2 έτη και 3 έτη είναι και τα δύο εξίσου κοντά (διαφορά 0,5 ετών).
Παρακάτω είναι ο τύπος που θα μας δώσει το αποτέλεσμα:
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Το κόλπο σε αυτόν τον τύπο είναι να αλλάξετε τον πίνακα αναζήτησης και την τιμή αναζήτησης για να βρείτε την ελάχιστη διαφορά εμπειρίας στις απαιτούμενες και πραγματικές τιμές.
Ας καταλάβουμε πρώτα πώς θα το κάνατε χειροκίνητα (και στη συνέχεια θα εξηγήσω πώς λειτουργεί αυτός ο τύπος).
Όταν το κάνετε αυτό χειροκίνητα, θα περάσετε από κάθε κελί στη στήλη Β και θα βρείτε τη διαφορά στην εμπειρία μεταξύ των απαιτούμενων και εκείνων που έχει ένα άτομο. Μόλις έχετε όλες τις διαφορές, θα βρείτε αυτό που είναι το ελάχιστο και θα βρείτε το όνομα αυτού του ατόμου.
Αυτό ακριβώς κάνουμε με αυτόν τον τύπο.
ΑΣΕ με να εξηγήσω.
Η τιμή αναζήτησης στον τύπο MATCH είναι MIN (ABS (D2-B2: B15)).
Αυτό το μέρος σας δίνει την ελάχιστη διαφορά μεταξύ της δεδομένης εμπειρίας (η οποία είναι 2,5 χρόνια) και όλων των άλλων εμπειριών. Σε αυτό το παράδειγμα, επιστρέφει 0,3
Σημειώστε ότι έχω χρησιμοποιήσει ABS για να βεβαιωθώ ότι ψάχνω το πλησιέστερο (το οποίο μπορεί να είναι περισσότερο ή λιγότερο από τη δεδομένη εμπειρία).
Τώρα, αυτή η ελάχιστη τιμή γίνεται η τιμή αναζήτησης.
Ο πίνακας αναζήτησης στη συνάρτηση MATCH είναι ABS (D2- $ B $ 2: $ B $ 15).
Αυτό μας δίνει μια σειρά αριθμών από τους οποίους έχει αφαιρεθεί το 2,5 (η απαιτούμενη εμπειρία).
Έτσι, τώρα έχουμε μια τιμή αναζήτησης (0.3) και έναν πίνακα αναζήτησης ({6.8; 0.8; 19.5; 21.8; 14.5; 11.2; 0.3; 9.2; 2; 9.8; 14.8; 0.4; 23.8; 2.9})
Η συνάρτηση MATCH βρίσκει τη θέση 0,3 σε αυτόν τον πίνακα, η οποία είναι επίσης η θέση του ονόματος του ατόμου που έχει την πλησιέστερη εμπειρία.
Αυτός ο αριθμός θέσης χρησιμοποιείται στη συνέχεια από τη λειτουργία INDEX για να επιστρέψει το όνομα του ατόμου.
Σημείωση: Σε περίπτωση που υπάρχουν πολλοί υποψήφιοι που έχουν την ίδια ελάχιστη εμπειρία, ο παραπάνω τύπος θα δώσει το όνομα του πρώτου αντίστοιχου υπαλλήλου.
Βρείτε την επόμενη ημερομηνία συμβάντος
Αυτό είναι ένα άλλο παράδειγμα όπου μπορείτε να χρησιμοποιήσετε τύπους αναζήτησης για να βρείτε την επόμενη ημερομηνία ενός συμβάντος με βάση την τρέχουσα ημερομηνία.
Παρακάτω είναι το σύνολο δεδομένων όπου έχω ονόματα συμβάντων και ημερομηνίες συμβάντων.
Αυτό που θέλω είναι το όνομα της επόμενης εκδήλωσης και η ημερομηνία εκδήλωσης για αυτήν την επερχόμενη εκδήλωση.
Παρακάτω είναι ο τύπος που θα δώσει το όνομα της επερχόμενης εκδήλωσης:
= INDEX ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)
Και ο παρακάτω τύπος θα δώσει την επερχόμενη ημερομηνία εκδήλωσης:
= INDEX ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)
Επιτρέψτε μου να εξηγήσω πώς λειτουργεί αυτός ο τύπος.
Για να λάβετε την ημερομηνία συμβάντος, η συνάρτηση MATCH αναζητά την τρέχουσα ημερομηνία στη στήλη Β. Σε αυτή την περίπτωση, δεν αναζητούμε μια ακριβή αντιστοίχιση, αλλά μια κατά προσέγγιση. Και ως εκ τούτου, το τελευταίο όρισμα της συνάρτησης MATCH είναι 1 (το οποίο βρίσκει τη μεγαλύτερη τιμή που είναι μικρότερη ή ίση με την τιμή αναζήτησης).
Έτσι, η συνάρτηση MATCH θα επιστρέψει τη θέση του κελιού που έχει την ημερομηνία που είναι μόλις μικρότερη ή ίση με την τρέχουσα ημερομηνία. Έτσι, το επόμενο συμβάν, σε αυτήν την περίπτωση, θα ήταν στο επόμενο κελί (καθώς η λίστα έχει ταξινομηθεί με αύξουσα σειρά).
Έτσι, για να λάβετε την επερχόμενη ημερομηνία συμβάντος, απλώς προσθέστε ένα στη θέση κελιού που επιστρέφεται από τη συνάρτηση MATCH και θα σας δώσει τη θέση κελιού της επόμενης ημερομηνίας συμβάντος.
Αυτή η τιμή δίνεται στη συνέχεια από τη συνάρτηση INDEX.
Για να λάβετε το όνομα του συμβάντος, χρησιμοποιείται ο ίδιος τύπος και το εύρος στη συνάρτηση INDEX αλλάζει από τη στήλη Β στη στήλη Α.
Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου
Η ιδέα αυτού του παραδείγματος μου ήρθε όταν ένας φίλος με πλησίασε με ένα αίτημα. Είχε μια λίστα με όλα τα γενέθλια των φίλων/συγγενών του σε μια στήλη και ήθελε να μάθει τα επόμενα γενέθλια που έρχονται (και το όνομα του ατόμου).Αυτά είναι τρία παραδείγματα που δείχνουν πώς να βρείτε την πλησιέστερη τιμή αντιστοίχισης στο Excel χρησιμοποιώντας τύπους αναζήτησης.
Μπορεί επίσης να σας αρέσουν οι ακόλουθες συμβουλές/σεμινάρια Excel
- Λάβετε τον τελευταίο αριθμό από μια λίστα χρησιμοποιώντας τη λειτουργία VLOOKUP.
- Λάβετε πολλαπλές τιμές αναζήτησης χωρίς επανάληψη σε ένα κελί.
- VLOOKUP Vs. INDEX/MATCH
- Excel INDEX MATCH
- Βρείτε την τελευταία εμφάνιση μιας τιμής αναζήτησης μιας λίστας στο Excel
- Εύρεση και κατάργηση διπλότυπων στο Excel
- Μορφοποίηση υπό όρους.
