Οι συναρτήσεις του Excel μπορεί να είναι πολύ ισχυρές εάν έχετε τον κόπο να συνδυάσετε διαφορετικούς τύπους.Αυτό που κάποτε φαινόταν αδύνατο μπορεί ξαφνικά να γίνει σαν παιδικό παιχνίδι.
Ένα τέτοιο παράδειγμα είναι η εύρεση της πλησιέστερης τιμής σε ένα σύνολο δεδομένων του Excel.
Υπάρχουν πολλές χρήσιμες συναρτήσεις αναζήτησης στο Excel (όπως το VLOOKUP και το INDEX MATCH) που μπορούν να βρουν τις πλησιέστερες值(όπως φαίνεται στο παρακάτω παράδειγμα).
Αλλά το καλύτερο μέρος είναι ότι μπορείτε να συνδυάσετε αυτές τις συναρτήσεις αναζήτησης με άλλες συναρτήσεις του 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.
