VLOOKUP στο Excel, μέρος 2 Χρησιμοποιώντας VLOOKUP χωρίς βάση δεδομένων
Σε ένα πρόσφατο άρθρο, παρουσιάσαμε τη λειτουργία 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, λειτουργεί έτσι:
ΑΝ(κατάσταση, τιμή εάν είναι αληθής, τιμή αν είναι ψευδής)
Όπου το κατάσταση είναι μια έκφραση που αξιολογεί και τα δύο αληθής ή ψευδής. Στο παραπάνω παράδειγμα, το κατάσταση είναι η έκφραση B1
Όπως μπορείτε να δείτε, χρησιμοποιώντας ένα σύνολο πωλήσεων $ 20.000 μας δίνει ένα ποσοστό προμήθειας 20% στο κελί B2. Αν εισάγουμε αξία $ 40.000, λαμβάνουμε ένα διαφορετικό ποσοστό προμήθειας:
Έτσι, το υπολογιστικό φύλλο μας λειτουργεί.
Ας το καταστήσουμε πιο περίπλοκο. Ας εισαγάγουμε ένα δεύτερο όριο: Εάν ο πωλητής κερδίσει περισσότερα από $ 40.000, τότε το ποσοστό προμήθειών του αυξάνεται στο 40%:
Αρκετά εύκολο να καταλάβετε στον πραγματικό κόσμο, αλλά στο κελί B2 η φόρμουλά μας γίνεται πιο περίπλοκη. Αν κοιτάξετε προσεκτικά τη φόρμουλα, θα δείτε ότι η τρίτη παράμετρος της αρχικής λειτουργίας IF (η αν είναι ψευδής) είναι πλέον μια ολόκληρη λειτουργία IF από μόνος του. Αυτό ονομάζεται a ένθετη λειτουργία (λειτουργία εντός μιας συνάρτησης). Είναι απολύτως έγκυρο στο 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 από το ΜΑΘΗΜΑΤΙΚΟΙ τυποι αυτί:
ο Λειτουργικά επιχειρήματα εμφανίζεται το πλαίσιο για το VLOOKUP. Συμπληρώνουμε τα επιχειρήματα (παραμέτρους) ένα προς ένα, ξεκινώντας από το Τιμή_αναζήτησης, που είναι, στην περίπτωση αυτή, το σύνολο πωλήσεων από το κελί B1. Τοποθετούμε το δρομέα στο Τιμή_αναζήτησης και στη συνέχεια κάντε κλικ μία φορά στο κελί B1:
Στη συνέχεια πρέπει να καθορίσουμε στο VLOOKUP τι πίνακα για να αναζητήσετε αυτά τα δεδομένα μέσα. Σε αυτό το παράδειγμα, είναι ο πίνακας τιμών, φυσικά. Τοποθετούμε το δρομέα στο Table_array πεδίο και, στη συνέχεια, επισημάνετε ολόκληρο τον πίνακα τιμών - εξαιρουμένων των επικεφαλίδων:
Στη συνέχεια, πρέπει να καθορίσουμε ποια στήλη στον πίνακα περιέχει τις πληροφορίες που θέλουμε ο τύπος μας να επιστρέψει σε εμάς. Σε αυτή την περίπτωση θέλουμε το ποσοστό προμήθειας, το οποίο βρίσκεται στη δεύτερη στήλη του πίνακα, οπότε εισάγουμε a 2 μέσα στο Col_index_num πεδίο:
Τέλος εισάγουμε μια τιμή στο Range_lookup πεδίο.
Σημαντικό: Είναι η χρήση αυτού του πεδίου που διαφοροποιεί τους δύο τρόπους χρήσης του VLOOKUP. Για να χρησιμοποιήσετε το VLOOKUP με μια βάση δεδομένων, αυτή η τελική παράμετρος, Range_lookup, πρέπει πάντα να ρυθμιστεί σε ΨΕΥΔΗΣ, αλλά με αυτήν την άλλη χρήση του VLOOKUP, πρέπει είτε να το αφήσουμε κενό είτε να εισάγουμε μια τιμή ΑΛΗΘΗΣ. Όταν χρησιμοποιείτε το VLOOKUP, είναι σημαντικό να κάνετε τη σωστή επιλογή για αυτήν την τελική παράμετρο.
Για να είμαστε σαφείς, θα εισαγάγουμε μια τιμή αληθής στο Range_lookup πεδίο. Θα ήταν επίσης ωραίο να το αφήσετε κενό, καθώς αυτή είναι η προεπιλεγμένη τιμή:
Έχουμε ολοκληρώσει όλες τις παραμέτρους. Τώρα κάνουμε κλικ στο Εντάξει και το Excel δημιουργεί τον τύπο VLOOKUP για εμάς:
Αν δοκιμάσουμε μερικά διαφορετικά συνολικά ποσά πωλήσεων, μπορούμε να ικανοποιήσουμε τον εαυτό μας ότι ο τύπος λειτουργεί.
συμπέρασμα
Στην έκδοση "βάση δεδομένων" του VLOOKUP, όπου το Range_lookup είναι η παράμετρος ΨΕΥΔΗΣ, η τιμή που πέρασε στην πρώτη παράμετρο (Τιμή_αναζήτησης) πρέπει να υπάρχει στη βάση δεδομένων. Με άλλα λόγια, ψάχνουμε για ακριβή αντιστοίχιση.
Αλλά σε αυτή την άλλη χρήση του VLOOKUP, δεν αναζητούμε απαραιτήτως έναν ακριβή αγώνα. Στην περίπτωση αυτή, "αρκετά κοντά είναι αρκετά καλό". Αλλά τι εννοούμε με "αρκετά κοντά"; Ας χρησιμοποιήσουμε ένα παράδειγμα: Όταν ψάχνουμε για ένα ποσοστό προμήθειας σε ένα σύνολο πωλήσεων $ 34.988, ο τύπος VLOOKUP μας θα μας επιστρέψει μια τιμή 30%, η οποία είναι η σωστή απάντηση. Γιατί επέλεξε τη σειρά στον πίνακα που περιέχει το 30%; Τι, στην πραγματικότητα, σημαίνει "αρκετά κοντά" στην περίπτωση αυτή; Ας είμαστε ακριβείς:
Πότε Range_lookup Έχει οριστεί ΑΛΗΘΗΣ (ή παραλείπεται), το VLOOKUP θα εξετάσει τη στήλη 1 και θα ταιριάξει η υψηλότερη τιμή που δεν είναι μεγαλύτερη από ο Τιμή_αναζήτησης παράμετρο.
Είναι επίσης σημαντικό να σημειωθεί ότι για να λειτουργήσει αυτό το σύστημα, ο πίνακας πρέπει να ταξινομηθεί με αύξουσα σειρά στη στήλη 1!
Αν θέλετε να εξασκηθείτε με το VLOOKUP, το αρχείο δείγματος που απεικονίζεται σε αυτό το άρθρο μπορείτε να το κατεβάσετε από εδώ.
