Περιεχόμενα
Πρόσφατα αφιερώσαμε ένα άρθρο σε μια από τις πιο χρήσιμες λειτουργίες του Excel που ονομάζεται VPR και έδειξε πώς μπορεί να χρησιμοποιηθεί για την εξαγωγή των απαιτούμενων πληροφοριών από μια βάση δεδομένων σε ένα κελί φύλλου εργασίας. Αναφέραμε επίσης ότι υπάρχουν δύο περιπτώσεις χρήσης για τη λειτουργία VPR και μόνο ένα από αυτά ασχολείται με ερωτήματα βάσης δεδομένων. Σε αυτό το άρθρο, θα μάθετε έναν άλλο λιγότερο γνωστό τρόπο χρήσης της συνάρτησης VPR στο Excel.
Εάν δεν το έχετε κάνει ακόμα, τότε φροντίστε να διαβάσετε το τελευταίο άρθρο σχετικά με τη λειτουργία VPR, γιατί όλες οι παρακάτω πληροφορίες προϋποθέτουν ότι είστε ήδη εξοικειωμένοι με τις αρχές που περιγράφονται στο πρώτο άρθρο.
Κατά την εργασία με βάσεις δεδομένων, συναρτήσεις VPR διαβιβάζεται ένα μοναδικό αναγνωριστικό, το οποίο χρησιμοποιείται για την αναγνώριση των πληροφοριών που θέλουμε να βρούμε (για παράδειγμα, έναν κωδικό προϊόντος ή έναν αριθμό αναγνώρισης πελάτη). Αυτός ο μοναδικός κωδικός πρέπει να υπάρχει στη βάση δεδομένων, διαφορετικά VPR θα αναφέρει ένα σφάλμα. Σε αυτό το άρθρο, θα εξετάσουμε αυτόν τον τρόπο χρήσης της συνάρτησης VPRόταν το αναγνωριστικό δεν υπάρχει καθόλου στη βάση δεδομένων. Σαν η συνάρτηση VPR αλλάζει στην κατά προσέγγιση λειτουργία και επιλέγει ποια δεδομένα θα μας παρέχει όταν θέλουμε να βρούμε κάτι. Σε ορισμένες περιπτώσεις, αυτό είναι ακριβώς αυτό που χρειάζεται.
Ένα παράδειγμα από τη ζωή. Θέσαμε το καθήκον
Ας επεξηγήσουμε αυτό το άρθρο με ένα πραγματικό παράδειγμα – τον υπολογισμό των προμηθειών με βάση ένα ευρύ φάσμα μετρήσεων πωλήσεων. Θα ξεκινήσουμε με μια πολύ απλή επιλογή και στη συνέχεια θα την περιπλέκουμε σταδιακά έως ότου η μόνη λογική λύση στο πρόβλημα είναι η χρήση της συνάρτησης VPR. Το αρχικό σενάριο για το πλασματικό μας έργο είναι το εξής: εάν ένας πωλητής κάνει περισσότερες από 30000 $ σε πωλήσεις σε ένα χρόνο, τότε η προμήθεια του είναι 30%. Διαφορετικά, η προμήθεια είναι μόνο 20%. Ας το βάλουμε σε μορφή πίνακα:
Ο πωλητής εισάγει τα δεδομένα πωλήσεών του στο κελί B1 και ο τύπος στο κελί B2 καθορίζει το σωστό ποσοστό προμήθειας που μπορεί να αναμένει ο πωλητής. Με τη σειρά του, το προκύπτον ποσοστό χρησιμοποιείται στο κελί B3 για τον υπολογισμό της συνολικής προμήθειας που πρέπει να λάβει ο πωλητής (απλώς πολλαπλασιάζοντας τα κελιά B1 και B2).
Το πιο ενδιαφέρον μέρος του πίνακα περιέχεται στο κελί B2 - αυτός είναι ο τύπος για τον προσδιορισμό του ποσοστού προμήθειας. Αυτός ο τύπος περιέχει μια συνάρτηση Excel που ονομάζεται IF (ΑΝ). Για όσους αναγνώστες δεν είναι εξοικειωμένοι με αυτήν τη λειτουργία, θα εξηγήσω πώς λειτουργεί:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
Κατάσταση είναι ένα όρισμα συνάρτησης που παίρνει την τιμή ενός από τα δύο ΑΛΗΘΙΝΟΣ ΚΩΔΙΚΟΣ (ΑΛΗΘΗΣ), ή ΨΕΥΔΗΣ (ΨΕΥΔΗΣ). Στο παραπάνω παράδειγμα, η έκφραση Β1
Είναι αλήθεια ότι το Β1 είναι μικρότερο από το Β5;
Ή μπορείτε να το πείτε διαφορετικά:
Είναι αλήθεια ότι το συνολικό ποσό των πωλήσεων για το έτος είναι μικρότερο από την τιμή κατωφλίου;
Αν απαντήσουμε σε αυτό το ερώτημα ΝΑΙ (TRUE), στη συνέχεια η συνάρτηση επιστρέφει τιμή αν αληθεύει (τιμή αν TRUE). Στην περίπτωσή μας, αυτή θα είναι η τιμή του κελιού B6, δηλαδή το ποσοστό προμήθειας όταν οι συνολικές πωλήσεις είναι κάτω από το όριο. Αν απαντήσουμε στην ερώτηση ΟΧΙ (FALSE) και στη συνέχεια επιστρέφει τιμή εάν είναι ψευδής (τιμή εάν FALSE). Στην περίπτωσή μας, αυτή είναι η τιμή του κελιού B7, δηλαδή το ποσοστό προμήθειας όταν οι συνολικές πωλήσεις είναι πάνω από το όριο.
Όπως μπορείτε να δείτε, αν λάβουμε τις συνολικές πωλήσεις των 20000 $, παίρνουμε ένα ποσοστό προμήθειας 2% στο κελί B20. Εάν εισάγουμε μια τιμή 40000 $, τότε το ποσοστό προμήθειας θα αλλάξει κατά 30%:
Έτσι λειτουργεί το τραπέζι μας.
Περιπλέκουμε το έργο
Ας κάνουμε τα πράγματα λίγο πιο δύσκολα. Ας ορίσουμε ένα άλλο όριο: εάν ο πωλητής κερδίζει περισσότερα από 40000 $, τότε το ποσοστό προμήθειας αυξάνεται στο 40%:
Όλα φαίνονται απλά και ξεκάθαρα, αλλά ο τύπος μας στο κελί Β2 γίνεται αισθητά πιο περίπλοκος. Αν κοιτάξετε προσεκτικά τον τύπο, θα δείτε ότι το τρίτο όρισμα της συνάρτησης IF (IF) μετατράπηκε σε μια άλλη πλήρη λειτουργία IF (ΑΝ). Αυτή η κατασκευή ονομάζεται ένθεση συναρτήσεων μεταξύ τους. Το Excel ευτυχώς επιτρέπει αυτές τις κατασκευές, και μάλιστα λειτουργούν, αλλά είναι πολύ πιο δύσκολο να διαβαστούν και να κατανοηθούν.
Δεν θα εμβαθύνουμε στις τεχνικές λεπτομέρειες - γιατί και πώς λειτουργεί, και δεν θα μπούμε στις αποχρώσεις της σύνταξης ένθετων λειτουργιών. Μετά από όλα, αυτό είναι ένα άρθρο αφιερωμένο στη λειτουργία VPR, δεν είναι πλήρης οδηγός για το Excel.
Όποια και αν είναι η περίπτωση, η φόρμουλα γίνεται πιο περίπλοκη! Τι θα συμβεί αν εισάγουμε μια άλλη επιλογή για ποσοστό προμήθειας 50% για εκείνους τους πωλητές που πραγματοποιούν πωλήσεις άνω των 50000 $. Και αν κάποιος έχει πουλήσει περισσότερα από $60000, θα πληρώσει 60% προμήθεια;
Τώρα ο τύπος στο κελί Β2, ακόμα κι αν γράφτηκε χωρίς σφάλματα, έχει γίνει εντελώς αδιάβαστος. Νομίζω ότι είναι λίγοι αυτοί που θέλουν να χρησιμοποιήσουν τύπους με 4 επίπεδα ένθεσης στα έργα τους. Πρέπει να υπάρχει πιο εύκολος τρόπος;!
Και υπάρχει τέτοιος τρόπος! Η συνάρτηση θα μας βοηθήσει VPR.
Εφαρμόζουμε τη συνάρτηση VLOOKUP για να λύσουμε το πρόβλημα
Ας αλλάξουμε λίγο το σχέδιο του τραπεζιού μας. Θα διατηρήσουμε όλα τα ίδια πεδία και δεδομένα, αλλά θα τα τακτοποιήσουμε με έναν νέο, πιο συμπαγή τρόπο:
Αφιερώστε λίγο χρόνο και βεβαιωθείτε ότι το νέο τραπέζι Πίνακας βαθμολογιών περιλαμβάνει τα ίδια δεδομένα με τον προηγούμενο πίνακα ορίου.
Η κύρια ιδέα είναι να χρησιμοποιήσετε τη συνάρτηση VPR για τον προσδιορισμό του επιθυμητού τιμολογίου σύμφωνα με τον πίνακα Πίνακας βαθμολογιών ανάλογα με τον όγκο των πωλήσεων. Λάβετε υπόψη ότι ο πωλητής μπορεί να πουλήσει αγαθά για ένα ποσό που δεν είναι ίσο με ένα από τα πέντε όρια του πίνακα. Για παράδειγμα, θα μπορούσε να πουλήσει για $34988, αλλά δεν υπάρχει τέτοιο ποσό. Ας δούμε πώς λειτουργεί VPR μπορεί να αντιμετωπίσει μια τέτοια κατάσταση.
Εισαγωγή συνάρτησης VLOOKUP
Επιλέξτε το κελί Β2 (όπου θέλουμε να εισαγάγουμε τον τύπο μας) και βρείτε VLOOKUP (VLOOKUP) στη Βιβλιοθήκη Λειτουργιών του Excel: ΜΑΘΗΜΑΤΙΚΟΙ τυποι (τύποι) > Βιβλιοθήκη λειτουργιών (Βιβλιοθήκη λειτουργιών) > Αναζήτηση και αναφορά (Αναφορές και πίνακες).
Εμφανίζεται ένα πλαίσιο διαλόγου Επιχειρήματα Λειτουργίας (Ορίσματα συνάρτησης). Συμπληρώνουμε τις τιμές των ορισμάτων μία προς μία, ξεκινώντας από Αναζήτηση_τιμής (Τιμή_αναζήτησης). Σε αυτό το παράδειγμα, αυτό είναι το συνολικό ποσό πωλήσεων από το κελί B1. Βάλτε τον κέρσορα στο πεδίο Αναζήτηση_τιμής (Lookup_value) και επιλέξτε το κελί B1.
Στη συνέχεια, πρέπει να καθορίσετε τις λειτουργίες VPRπού να αναζητήσετε δεδομένα. Στο παράδειγμά μας, αυτός είναι ένας πίνακας Πίνακας βαθμολογιών. Βάλτε τον κέρσορα στο πεδίο Πίνακας_συστοιχίας (Πίνακας) και επιλέξτε ολόκληρο τον πίνακα Πίνακας βαθμολογιώνεκτός από τις κεφαλίδες.
Στη συνέχεια, πρέπει να καθορίσουμε από ποια στήλη θα εξαγάγουμε δεδομένα χρησιμοποιώντας τον τύπο μας. Μας ενδιαφέρει το ποσοστό προμήθειας, το οποίο βρίσκεται στη δεύτερη στήλη του πίνακα. Επομένως, για το επιχείρημα Col_index_number (Αριθμός_στήλης) εισάγετε την τιμή 2.
Και τέλος, εισάγουμε το τελευταίο επιχείρημα - Εύρος_αναζήτησης (Interval_lookup).
Σημαντικό: Είναι η χρήση αυτού του ορίσματος που κάνει τη διαφορά μεταξύ των δύο τρόπων εφαρμογής της συνάρτησης VPR. Όταν εργάζεστε με βάσεις δεδομένων, το όρισμα Εύρος_αναζήτησης (range_lookup) πρέπει πάντα να έχει μια τιμή ΨΕΥΔΗΣ (FALSE) για αναζήτηση ακριβούς αντιστοίχισης. Στη χρήση της συνάρτησης VPR, πρέπει να αφήσουμε αυτό το πεδίο κενό ή να εισαγάγουμε μια τιμή ΑΛΗΘΙΝΟΣ ΚΩΔΙΚΟΣ (ΑΛΗΘΗΣ). Είναι εξαιρετικά σημαντικό να επιλέξετε σωστά αυτή την επιλογή.
Για να γίνει πιο σαφές, θα παρουσιάσουμε ΑΛΗΘΙΝΟΣ ΚΩΔΙΚΟΣ (ΑΛΗΘΕΙΑ) στο χωράφι Εύρος_αναζήτησης (Interval_lookup). Αν και, αν αφήσετε το πεδίο κενό, αυτό δεν θα είναι σφάλμα, αφού ΑΛΗΘΙΝΟΣ ΚΩΔΙΚΟΣ είναι η προεπιλεγμένη τιμή του:
Έχουμε συμπληρώσει όλες τις παραμέτρους. Τώρα πατάμε OK, και το Excel δημιουργεί έναν τύπο για εμάς με μια συνάρτηση VPR.
Εάν πειραματιστούμε με πολλές διαφορετικές τιμές για το συνολικό ποσό πωλήσεων, τότε θα βεβαιωθούμε ότι ο τύπος λειτουργεί σωστά.
Συμπέρασμα
Όταν η συνάρτηση VPR λειτουργεί με βάσεις δεδομένων, όρισμα Εύρος_αναζήτησης (range_lookup) πρέπει να αποδεχτεί ΨΕΥΔΗΣ (ΨΕΥΔΗΣ). Και η τιμή εισάγεται ως Αναζήτηση_τιμής (Lookup_value) πρέπει να υπάρχει στη βάση δεδομένων. Ψάχνει δηλαδή για ακριβή ταύτιση.
Στο παράδειγμα που εξετάσαμε σε αυτό το άρθρο, δεν χρειάζεται να βρείτε μια ακριβή αντιστοίχιση. Αυτό συμβαίνει όταν η συνάρτηση VPR πρέπει να μεταβεί σε κατά προσέγγιση λειτουργία για να επιστρέψει το επιθυμητό αποτέλεσμα.
Για παράδειγμα: Θέλουμε να καθορίσουμε ποιο ποσοστό θα χρησιμοποιήσουμε στον υπολογισμό της προμήθειας για έναν πωλητή με όγκο πωλήσεων 34988 $. Λειτουργία VPR μας επιστρέφει μια τιμή 30%, η οποία είναι απολύτως σωστή. Γιατί όμως ο τύπος επέλεξε τη σειρά που περιέχει ακριβώς 30% και όχι 20% ή 40%; Τι σημαίνει κατά προσέγγιση αναζήτηση; Ας είμαστε ξεκάθαροι.
Όταν το επιχείρημα Εύρος_αναζήτησης (interval_lookup) έχει μια τιμή ΑΛΗΘΙΝΟΣ ΚΩΔΙΚΟΣ (TRUE) ή παραλειφθείσα, συνάρτηση VPR επαναλαμβάνει την πρώτη στήλη και επιλέγει τη μεγαλύτερη τιμή που δεν υπερβαίνει την τιμή αναζήτησης.
Σημαντικό σημείο: Για να λειτουργήσει αυτό το σχήμα, η πρώτη στήλη του πίνακα πρέπει να ταξινομηθεί με αύξουσα σειρά.