1Aug
Σε ένα πρόσφατο άρθρο παρουσιάσαμε τη λειτουργία Excel που ονομάζεται VLOOKUP και εξήγησα πώς θα μπορούσε να χρησιμοποιηθεί για την ανάκτηση πληροφοριών από μια βάση δεδομένων σε ένα κελί σε ένα τοπικό φύλλο εργασίας.Σε αυτό το άρθρο αναφέραμε ότι υπήρχαν δύο χρήσεις για το VLOOKUP και μόνο ένας από αυτούς ασχολήθηκε με την αναζήτηση βάσεων δεδομένων.Σε αυτό το άρθρο, το δεύτερο και τελευταίο στην σειρά VLOOKUP, εξετάζουμε αυτή την άλλη, λιγότερο γνωστή χρήση για τη λειτουργία VLOOKUP.
Αν δεν το έχετε κάνει ήδη, διαβάστε το πρώτο άρθρο VLOOKUP - αυτό το άρθρο θα υποθέσει ότι πολλές από τις έννοιες που εξηγούνται σε αυτό το άρθρο είναι ήδη γνωστές στον αναγνώστη.
Όταν εργάζεστε με βάσεις δεδομένων, το VLOOKUP περνάει ένα "μοναδικό αναγνωριστικό" που χρησιμεύει για τον προσδιορισμό του καταλόγου δεδομένων που επιθυμούμε να βρούμε στη βάση δεδομένων( π.χ. κωδικός προϊόντος ή αναγνωριστικό πελάτη).Αυτό το μοναδικό αναγνωριστικό πρέπει να υπάρχει στη βάση δεδομένων, διαφορετικά το VLOOKUP μας επιστρέφει σφάλμα.Σε αυτό το άρθρο, θα εξετάσουμε έναν τρόπο χρήσης του VLOOKUP όπου το αναγνωριστικό δεν χρειάζεται να υπάρχει στη βάση δεδομένων καθόλου.Είναι σχεδόν σαν το VLOOKUP να υιοθετήσει μια προσέγγιση "αρκετά κοντά είναι αρκετά καλή" για την επιστροφή των δεδομένων που ψάχνουμε.Σε ορισμένες περιπτώσεις, αυτό είναι
ακριβώς αυτό που χρειαζόμαστε.Θα παρουσιάσουμε αυτό το άρθρο με ένα παράδειγμα πραγματικού κόσμου - το παράδειγμα του υπολογισμού των προμηθειών που παράγονται σε ένα σύνολο στοιχείων πωλήσεων.Θα ξεκινήσουμε με ένα πολύ απλό σενάριο και στη συνέχεια θα το καταστήσουμε πιο σύνθετο, μέχρις ότου η μόνη λογική λύση στο πρόβλημα είναι η χρήση του VLOOKUP.Το αρχικό σενάριο της φανταστικής εταιρείας μας λειτουργεί ως εξής: Εάν ένας πωλητής δημιουργήσει πωλήσεις άνω των 30.000 δολαρίων σε ένα δεδομένο έτος, η προμήθεια που κερδίζουν σε αυτές τις πωλήσεις είναι 30%.Διαφορετικά, η προμήθειά τους είναι μόνο 20%.Μέχρι στιγμής, αυτό είναι ένα αρκετά απλό φύλλο εργασίας:
Για να χρησιμοποιήσετε αυτό το φύλλο εργασίας, ο πωλητής εισάγει τα στοιχεία πωλήσεών του στο κελί B1 και ο τύπος στο κελί B2 υπολογίζει το σωστό ποσοστό προμήθειας που δικαιούται να λάβει, το οποίο χρησιμοποιείται στο κελί B3 για τον υπολογισμόη συνολική προμήθεια που οφείλεται στον πωλητή( που είναι ένας απλός πολλαπλασιασμός των Β1 και Β2).
Το κελί B2 περιέχει το μοναδικό ενδιαφέρον μέρος αυτού του φύλλου εργασίας - τον τύπο για να αποφασιστεί ποιο ποσοστό προμηθειών θα χρησιμοποιηθεί: το ένα κάτω από το το κατώτατο όριο των $ 30.000 ή το ένα πάνω από το το όριο.Αυτός ο τύπος χρησιμοποιεί τη λειτουργία Excel που ονομάζεται IF .Για τους αναγνώστες που δεν είναι εξοικειωμένοι με το IF, λειτουργεί ως εξής:
IF( κατάσταση , τιμή αν είναι αληθής, τιμή αν είναι ψευδής )
Όπου η συνθήκη είναι μια έκφραση που αξιολογεί είτε είτε false .Στο παραπάνω παράδειγμα, η συνθήκη είναι η έκφραση B1 & lt; B5 , η οποία μπορεί να διαβαστεί ως "Is B1 μικρότερη από B5;" ή, με άλλο τρόπο, "Οι συνολικές πωλήσεις είναι μικρότερες από το όριο".Εάν η απάντηση σε αυτή την ερώτηση είναι "ναι"( αληθής), τότε χρησιμοποιούμε την τιμή εάν ισχύει η παράμετρος της συνάρτησης, δηλαδή η B6 σε αυτή την περίπτωση - το ποσοστό προμήθειας εάν το σύνολο πωλήσεων ήταν κάτω από το το όριο.Εάν η απάντηση στην ερώτηση είναι "όχι"( ψευδής), τότε χρησιμοποιούμε την τιμή αν είναι ψευδής παράμετρος της συνάρτησης, δηλαδή B7 σε αυτή την περίπτωση - το ποσοστό προμήθειας εάν το σύνολο πωλήσεων ήταν πάνω από το το κατώτατο όριο.
Όπως βλέπετε, χρησιμοποιώντας ένα σύνολο πωλήσεων $ 20.000 μας δίνει ένα ποσοστό προμήθειας 20% στο κελί B2.Αν εισάγουμε αξία $ 40.000, λαμβάνουμε ένα διαφορετικό ποσοστό προμήθειας:
Έτσι, το υπολογιστικό φύλλο μας λειτουργεί.
Ας το κάνουμε πιο περίπλοκο.Ας εισαγάγουμε ένα δεύτερο όριο: Εάν ο πωλητής κερδίσει περισσότερα από 40.000 δολάρια, τότε το ποσοστό προμήθειών του αυξάνεται στο 40%:
Αρκετά εύκολο να καταλάβει κανείς στον πραγματικό κόσμο, αλλά στο κελί B2 η φόρμουλά μας γίνεται πιο περίπλοκη.Αν κοιτάξετε προσεκτικά τη φόρμουλα, θα δείτε ότι η τρίτη παράμετρος της αρχικής συνάρτησης IF( η τιμή αν είναι ψευδής ) είναι τώρα μια ολόκληρη συνάρτηση IF από μόνη της.Αυτό ονομάζεται λειτουργία ( μια συνάρτηση εντός μιας συνάρτησης).Είναι απολύτως έγκυρο στο Excel( λειτουργεί ακόμα!), Αλλά είναι πιο δύσκολο να το διαβάσετε και να το καταλάβετε.
Δεν πρόκειται να πάμε στα καρύδια και τα μπουλόνια του πώς και γιατί λειτουργεί αυτό, ούτε θα εξετάσουμε τις αποχρώσεις των φωλιζόμενων λειτουργιών.Αυτό είναι ένα σεμινάριο στο VLOOKUP, όχι στο Excel γενικά.
Τέλος πάντων, χειροτερεύει!Τι γίνεται όταν αποφασίσουμε ότι αν κερδίσουν περισσότερα από $ 50.000 τότε δικαιούνται 50% προμήθεια και αν κερδίσουν περισσότερα από $ 60.000 τότε δικαιούνται 60% προμήθεια;
Τώρα ο τύπος στο κελί B2, ενώ είναι σωστός, έχει γίνει σχεδόν δυσανάγνωστος.Κανείς δεν θα πρέπει να γράψει τύπους όπου οι λειτουργίες είναι ένθετες σε τέσσερα επίπεδα βαθιά!Σίγουρα πρέπει να υπάρχει ένας απλούστερος τρόπος;
Υπάρχει βεβαίως.VLOOKUP στη διάσωση!
Ας επανασχεδιάσουμε λίγο το φύλλο εργασίας.Θα κρατήσουμε όλες τις ίδιες φιγούρες, αλλά θα το οργανώσουμε με έναν νέο τρόπο, έναν τρόπο :
Πάρτε μια στιγμή και βεβαιωθείτε ότι ο νέος πίνακας ποσοστού λειτουργεί ακριβώς όπως και οι σειρές κατωφλίων παραπάνω.
Εννοιολογικά, αυτό που πρόκειται να κάνουμε είναι να χρησιμοποιήσουμε το VLOOKUP για να αναζητήσουμε το σύνολο των πωλήσεων του πωλητή( από το Β1) στον πίνακα τιμών και να μας επιστρέψετε το αντίστοιχο επιτόκιο προμήθειας.Σημειώστε ότι ο πωλητής μπορεί πράγματι να έχει δημιουργήσει πωλήσεις που είναι και όχι μία από τις πέντε τιμές στον πίνακα τιμών( $ 0, $ 30,000, $ 40,000, $ 50,000 ή $ 60,000).Μπορεί να έχουν δημιουργήσει πωλήσεις $ 34.988.Είναι σημαντικό να σημειωθεί ότι $ 34.988 δεν εμφανίζεται το στον πίνακα τιμών.Ας δούμε αν το VLOOKUP μπορεί να λύσει το πρόβλημά μας ούτως ή άλλως. ..
Επιλέγουμε το στοιχείο B2( την τοποθεσία που θέλουμε να βάλουμε τον τύπο μας) και στη συνέχεια εισάγουμε τη λειτουργία VLOOKUP από τις καρτέλες :
Η παράμετρος Arguments Function για VLOOKUP εμφανίζεται.Συμπληρώνουμε τα επιχειρήματα( παραμέτρους) ένα προς ένα, ξεκινώντας από το Lookup_value , το οποίο είναι, στην περίπτωση αυτή, το σύνολο πωλήσεων από το κελί B1.Τοποθετούμε τον κέρσορα στο πεδίο Lookup_value και στη συνέχεια κάνουμε κλικ μία φορά στο κελί B1:
Επόμενος πρέπει να καθορίσουμε στον VLOOKUP τον πίνακα στον οποίο πρέπει να αναζητήσουμε αυτά τα δεδομένα. Σε αυτό το παράδειγμα, είναι φυσικά ο πίνακας τιμών.Τοποθετούμε το δρομέα στο πεδίο Table_array και στη συνέχεια τονίζεται ολόκληρος ο πίνακας τιμών - εκτός από τις επικεφαλίδες :
Επόμενο πρέπει να καθορίσουμε ποια στήλη στον πίνακα περιέχει τις πληροφορίες που θέλουμε ο τύπος μας να επιστρέψει σε μας.Σε αυτή την περίπτωση θέλουμε το ποσοστό προμήθειας, το οποίο βρίσκεται στη δεύτερη στήλη του πίνακα, οπότε εισάγουμε ένα 2 στο πεδίο Col_index_num :
Τέλος εισάγουμε μια τιμή στο πεδίο Range_lookup .
Σημαντικό: Η χρήση αυτού του πεδίου διαφοροποιεί τους δύο τρόπους χρήσης του VLOOKUP.Για να χρησιμοποιήσετε το VLOOKUP με μια βάση δεδομένων, αυτή η τελική παράμετρος, Range_lookup , πρέπει πάντοτε να οριστεί σε FALSE , αλλά με αυτήν την άλλη χρήση του VLOOKUP, πρέπει είτε να την αφήσετε κενή είτε να εισαγάγετε τιμή TRUE .Όταν χρησιμοποιείτε το VLOOKUP, είναι σημαντικό να κάνετε τη σωστή επιλογή για αυτήν την τελική παράμετρο.
Για να είμαστε σαφείς, θα εισαγάγουμε μια τιμή του true στο πεδίο Range_lookup .Θα ήταν επίσης ωραία να το αφήσετε κενό, καθώς αυτή είναι η προεπιλεγμένη τιμή:
Έχουμε ολοκληρώσει όλες τις παραμέτρους.Τώρα κάνουμε κλικ στο κουμπί OK και το Excel χτίζει τον τύπο VLOOKUP για εμάς:
Αν πειραματιστούμε με λίγα διαφορετικά συνολικά ποσά πωλήσεων, μπορούμε να ικανοποιήσουμε τον εαυτό μας ότι ο τύπος λειτουργεί.
Συμπεράσματα
Στην έκδοση "βάση δεδομένων" του VLOOKUP, όπου η Range_lookup παράμετρος είναι FALSE , η τιμή που πέρασε στην πρώτη παράμετρο( Lookup_value ) πρέπει να είναι στη βάση δεδομένων.Με άλλα λόγια, ψάχνουμε για ακριβή αντιστοίχιση.
Αλλά σε αυτή την άλλη χρήση του VLOOKUP, δεν αναζητούμε απαραιτήτως ακριβή αντιστοίχιση.Σε αυτή την περίπτωση, "αρκετά κοντά είναι αρκετά καλό".Αλλά τι εννοούμε με "αρκετά κοντά";Ας χρησιμοποιήσουμε ένα παράδειγμα: Όταν ψάχνουμε για ένα ποσοστό προμήθειας σε ένα σύνολο πωλήσεων $ 34.988, ο τύπος VLOOKUP μας θα μας επιστρέψει μια τιμή 30%, η οποία είναι η σωστή απάντηση.Γιατί επέλεξε τη σειρά στον πίνακα που περιέχει 30%;Τι, στην πραγματικότητα, σημαίνει "αρκετά κοντά" σε αυτή την περίπτωση;Ας είμαστε ακριβείς:
Όταν το Range_lookup είναι ρυθμισμένο σε TRUE ( ή παραλείπεται), το VLOOKUP θα εξετάσει στη στήλη 1 και θα ταιριάξει με την την υψηλότερη τιμή που δεν είναι μεγαλύτερη από την παράμετρο Lookup_value .
Είναι επίσης σημαντικό να σημειωθεί ότι για να λειτουργήσει αυτό το σύστημα, το πρέπει να ταξινομηθεί με αύξουσα σειρά στη στήλη 1 !
Αν θέλετε να εξασκηθείτε με το VLOOKUP, μπορείτε να κατεβάσετε το δείγμα που απεικονίζεται σε αυτό το άρθρο από εδώ.