Άλλη μια "μαγική" συνάρτηση του Excel, πολύ χρήσιμη όταν θέλετε να κάνετε αναζητήσεις στα δεδομένα σας.
Ας υποθέσουμε ότι έχετε έναν πίνακα πελατών σε ένα φύλλο του Excel που λέγεται ΠΕΛΑΤΟΛΟΓΙΟ, με κάποια βασικά στοιχεία. Όπως ο παρακάτω:
Θέλετε σε ένα άλλο φύλλο να φτιάξετε μια φόρμα αναζήτησης ώστε γράφοντας τον κωδικό του πελάτη, να βλέπετε τα υπόλοιπα στοιχεία του, τηλέφωνο, ονοματεπώνυμο και διεύθυνση.
Όπως κάθε σωστή φόρμα, η φόρμα που θα φτιάξετε θα πρέπει να περιέχει ένα κουτί αναζήτησης. Όπως η παρακάτω:
Η φόρμα αυτή θα πρέπει να έχει μια συνάρτηση, ώστε όταν ο χρήστης γράφει έναν κωδικό πελάτη, η συνάρτηση να επιστρέφει τα υπόλοιπα στοιχεία στα αντίστοιχα πεδία.
Η συνάρτηση αυτή είναι η VLOOKUP:
Έχει 4 τμήματα και συντάσσεται ώς εξής:
=VLOOKUP(Η ΤΙΜΗ ΠΟΥ ΑΝΑΖΗΤΑΤΕ;Η ΠΕΡΙΟΧΗ ΑΝΑΖΗΤΗΣΗΣ;Η ΣΤΗΛΗ ΠΟΥ ΘΕΛΕΤΕ ΝΑ ΕΠΙΣΤΡΑΦΕΙ;ΑΚΡΙΒΗΣ Ή ΚΑΤΑ ΠΡΟΣΕΓΓΙΣΗ ΑΝΑΖΗΤΗΣΗ)
Στο παράδειγμά μας, για να βρούμε το τηλέφωνο του πελάτη, η λογική σύνταξη θα ήταν:
=VLOOKUP(Κωδικός πελάτη;Πίνακας πελατολογίου;Αριθμός στήλης τηλεφώνων;Ακριβής αναζήτηση)
Σημείωση: Αν στο τελευταίο τμήμα της VLOOKUP επιλέξετε την κατά προσέγγιση αναζήτηση, τότε η συνάρτηση εάν δεν βρει ακριβή αντιστοιχία, θα επιστρέψει την αμέσως μεγαλύτερη τιμή που είναι μικρότερη από την τιμή αναζήτησης. Για παράδειγμα, αν αναζητάτε το τηλέφωνο του πελάτη με κωδικό 8 αλλά δεν υπάρχει τέτοιος κωδικός στο πελατολόγιό σας, τότε με κατά προσέγγιση αναζήτηση η VLOOKUP θα σας επιστρέψει το τηλέφωνο του πελάτη με κωδικό 7.
Η συνάρτηση που θα γράψουμε για να βρούμε το τηλέφωνο του πελάτη είναι:
=VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;2;0)
Τη συνάρτηση αυτή θα γράψουμε στο κελί Β4 της φόρμας αναζήτησης.
Ας δούμε τη συνάρτηση βήμα - βήμα:
Ξεκινάμε με το "=VLOOKUP(": ορίζουμε τη συνάρτηση και ανοίγουμε την παρένθεση που θα φιλοξενήσει τα 4 τμήματα
1. "B3": το κελί Β3 περιέχει την τιμή που αναζητούμε, δηλαδή τον κωδικό πελάτη
2. ";ΠΕΛΑΤΟΛΟΓΙΟ!A:D": η περιοχή αναζήτησης βρίσκεται στις στήλες Α έως D, στο φύλλο ΠΕΛΑΤΟΛΟΓΙΟ. Αντί για στήλες θα μπορούσαμε να επιλέξουμε συγκεκριμένα κελιά, πχ A1:D11
3. ";2": η τιμή που θέλουμε να μας επιστραφεί (Τηλέφωνο πελάτη) βρίσκεται στη δεύτερη στήλη της περιοχής αναζήτησης, οπότε εισάγουμε το 2 στο αντίστοιχο τμήμα της VLOOKUP.
4. ";1)": επιλέγουμε την ακριβή αναζήτηση γράφοντας 0 (μηδέν). Αν θέλαμε να επιλέξουμε την κατά προσέγγιση, τότε θα έπρεπε να γράψουμε 1.
Στο τέλος κλείνουμε την παρένθεση και πατάμε Enter.
Αντίστοιχα γράφουμε τις VLOOKUP που θα επιστρέψουν το ονοματεπώνυμο και τη διεύθυνση κάθε πελάτη που θα αναζητήσουμε:
Στο κελί Β5 της φόρμας αναζήτησης θα φέρουμε το ονοματεπώνυμο πελάτη: =VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;3;0)
Στο κελί Β6 της φόρμας αναζήτησης θα φέρουμε τη διεύθυνση πελάτη: =VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;4;0)
Προσέξτε πως το μόνο που αλλάζει είναι ο αριθμός της στήλης που θέλουμε να επιστραφεί, μιας και το ονοματεπώνυμο βρίσκεται στην στήλη 3 και η διεύθυνση στη στήλη 4 της περιοχής αναζήτησης.
Οπότε έχουμε κάνει τη φόρμα μας να λειτουργεί και να επιστρέφει τα στοιχεία των πελατών:
Αλλάζοντας τον κωδικό στο πράσινο κελί, η φόρμα επιστρέφει τα σωστά στοιχεία κάθε πελάτη.
Αν αφήσετε τον κωδικό πελάτη κενό, θα προσέξετε ότι οι 3 VLOOKUP επιστρέφουν σφάλμα υπολογισμού:
Μπορείτε να το αποφύγετε εύκολα αυτό συνδυάζοντας την VLOOKUP με μια IF ώστε αν ο κωδικός πελάτη είναι κενός, τότε και τα 3 κελιά τιμών να παραμένουν κενά.
Η συνάρτησή μας για να βρούμε το τηλέφωνο πελάτη θα γίνει:
=IF(B3="";"";VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;2;0))
και αντίστοιχα για να βρούμε το ονοματεπώνυμο και τη διεύθυνση:
=IF(B3="";"";VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;3;0))
=IF(B3="";"";VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;4;0))
Μπορείτε να μάθετε περισσότερα για τη συνάρτηση IF, εδώ:
www.customexcelapps.gr/2018/04/if-excel.html
Θέλετε σε ένα άλλο φύλλο να φτιάξετε μια φόρμα αναζήτησης ώστε γράφοντας τον κωδικό του πελάτη, να βλέπετε τα υπόλοιπα στοιχεία του, τηλέφωνο, ονοματεπώνυμο και διεύθυνση.
Όπως κάθε σωστή φόρμα, η φόρμα που θα φτιάξετε θα πρέπει να περιέχει ένα κουτί αναζήτησης. Όπως η παρακάτω:
Η φόρμα αυτή θα πρέπει να έχει μια συνάρτηση, ώστε όταν ο χρήστης γράφει έναν κωδικό πελάτη, η συνάρτηση να επιστρέφει τα υπόλοιπα στοιχεία στα αντίστοιχα πεδία.
Η συνάρτηση αυτή είναι η VLOOKUP:
Έχει 4 τμήματα και συντάσσεται ώς εξής:
=VLOOKUP(Η ΤΙΜΗ ΠΟΥ ΑΝΑΖΗΤΑΤΕ;Η ΠΕΡΙΟΧΗ ΑΝΑΖΗΤΗΣΗΣ;Η ΣΤΗΛΗ ΠΟΥ ΘΕΛΕΤΕ ΝΑ ΕΠΙΣΤΡΑΦΕΙ;ΑΚΡΙΒΗΣ Ή ΚΑΤΑ ΠΡΟΣΕΓΓΙΣΗ ΑΝΑΖΗΤΗΣΗ)
Στο παράδειγμά μας, για να βρούμε το τηλέφωνο του πελάτη, η λογική σύνταξη θα ήταν:
=VLOOKUP(Κωδικός πελάτη;Πίνακας πελατολογίου;Αριθμός στήλης τηλεφώνων;Ακριβής αναζήτηση)
Σημείωση: Αν στο τελευταίο τμήμα της VLOOKUP επιλέξετε την κατά προσέγγιση αναζήτηση, τότε η συνάρτηση εάν δεν βρει ακριβή αντιστοιχία, θα επιστρέψει την αμέσως μεγαλύτερη τιμή που είναι μικρότερη από την τιμή αναζήτησης. Για παράδειγμα, αν αναζητάτε το τηλέφωνο του πελάτη με κωδικό 8 αλλά δεν υπάρχει τέτοιος κωδικός στο πελατολόγιό σας, τότε με κατά προσέγγιση αναζήτηση η VLOOKUP θα σας επιστρέψει το τηλέφωνο του πελάτη με κωδικό 7.
Η συνάρτηση που θα γράψουμε για να βρούμε το τηλέφωνο του πελάτη είναι:
=VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;2;0)
Τη συνάρτηση αυτή θα γράψουμε στο κελί Β4 της φόρμας αναζήτησης.
Ας δούμε τη συνάρτηση βήμα - βήμα:
Ξεκινάμε με το "=VLOOKUP(": ορίζουμε τη συνάρτηση και ανοίγουμε την παρένθεση που θα φιλοξενήσει τα 4 τμήματα
1. "B3": το κελί Β3 περιέχει την τιμή που αναζητούμε, δηλαδή τον κωδικό πελάτη
2. ";ΠΕΛΑΤΟΛΟΓΙΟ!A:D": η περιοχή αναζήτησης βρίσκεται στις στήλες Α έως D, στο φύλλο ΠΕΛΑΤΟΛΟΓΙΟ. Αντί για στήλες θα μπορούσαμε να επιλέξουμε συγκεκριμένα κελιά, πχ A1:D11
3. ";2": η τιμή που θέλουμε να μας επιστραφεί (Τηλέφωνο πελάτη) βρίσκεται στη δεύτερη στήλη της περιοχής αναζήτησης, οπότε εισάγουμε το 2 στο αντίστοιχο τμήμα της VLOOKUP.
4. ";1)": επιλέγουμε την ακριβή αναζήτηση γράφοντας 0 (μηδέν). Αν θέλαμε να επιλέξουμε την κατά προσέγγιση, τότε θα έπρεπε να γράψουμε 1.
Στο τέλος κλείνουμε την παρένθεση και πατάμε Enter.
Αντίστοιχα γράφουμε τις VLOOKUP που θα επιστρέψουν το ονοματεπώνυμο και τη διεύθυνση κάθε πελάτη που θα αναζητήσουμε:
Στο κελί Β5 της φόρμας αναζήτησης θα φέρουμε το ονοματεπώνυμο πελάτη: =VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;3;0)
Στο κελί Β6 της φόρμας αναζήτησης θα φέρουμε τη διεύθυνση πελάτη: =VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;4;0)
Προσέξτε πως το μόνο που αλλάζει είναι ο αριθμός της στήλης που θέλουμε να επιστραφεί, μιας και το ονοματεπώνυμο βρίσκεται στην στήλη 3 και η διεύθυνση στη στήλη 4 της περιοχής αναζήτησης.
Οπότε έχουμε κάνει τη φόρμα μας να λειτουργεί και να επιστρέφει τα στοιχεία των πελατών:
Αλλάζοντας τον κωδικό στο πράσινο κελί, η φόρμα επιστρέφει τα σωστά στοιχεία κάθε πελάτη.
Αν αφήσετε τον κωδικό πελάτη κενό, θα προσέξετε ότι οι 3 VLOOKUP επιστρέφουν σφάλμα υπολογισμού:
Μπορείτε να το αποφύγετε εύκολα αυτό συνδυάζοντας την VLOOKUP με μια IF ώστε αν ο κωδικός πελάτη είναι κενός, τότε και τα 3 κελιά τιμών να παραμένουν κενά.
Η συνάρτησή μας για να βρούμε το τηλέφωνο πελάτη θα γίνει:
=IF(B3="";"";VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;2;0))
και αντίστοιχα για να βρούμε το ονοματεπώνυμο και τη διεύθυνση:
=IF(B3="";"";VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;3;0))
=IF(B3="";"";VLOOKUP(B3;ΠΕΛΑΤΟΛΟΓΙΟ!A:D;4;0))
Μπορείτε να μάθετε περισσότερα για τη συνάρτηση IF, εδώ:
www.customexcelapps.gr/2018/04/if-excel.html
