Εύκολη και γρήγορη αναζήτηση σε πολλές στήλες.

Στην βάση της παρακάτω εικόνας:

easy_search1

θέλετε να ψάξετε σε όλα ή σε πολλά πεδία (στήλες) για να βρείτε σε ποιες εγγραφές (γραμμές) εμφανίζεται ένα από τα νησιά Κως, Σύμη, Θήρα. Μπορείτε να ψάξετε με το εργαλείο του excel ‘Εύρεση’ ή πιο καλά με ‘Σύνθετο φίλτρο’. Ας δούμε όμως μια πιο εύκολη και παραστατική τεχνική με τη βοήθεια μιας συνάρτησης φύλλου. Γράψτε τις λέξεις που ψάχνετε σε μια άδεια στήλη του φύλλου, όπως στην εικόνα, όπου οι λέξεις γράφτηκαν στα κελιά G2:G4:

easy_search2
Στην πρώτη άδεια στήλη, δεξιά του πίνακα, γράψτε τη συνάρτηση:

=SUMPRODUCT(--ISNUMBER(FIND($G$2:$G$4;B2:D2)))>0

και αντιγράψτε την έως την τελευταία γραμμή της βάσης. Η συνάρτηση ψάχνει στις στήλες B, C, D και όπου βρει μία ή περισσότερες από τις λέξεις που έχετε γράψει στα κελιά G2:G4, για την εγγραφή (γραμμή) αυτή επιστρέφει TRUE και για τις άλλες FALSE

easy_search3
Τώρα μπορείτε αν θέλετε να κάνετε γρήγορο φιλτράρισμα ως προς τη στήλη E, και να πάρετε τις εγγραφές που σας ενδιαφέρουν:

easy_search4
Όλα καλά έως εδώ, φαντάζομαι βρίσκετε και εσείς γρήγορη και εύχρηστη αυτή την τεχνική, αλλά υπάρχει ένα μικρό πρόβλημα. Στην επόμενη αναζήτησή σας, που θα ψάξετε για περισσότερες ή λιγότερες λέξεις, το όρισμα $G$2:$G$4 της συνάρτησης πρέπει να αλλάξει. Αν τώρα ψάχνετε για 5 νησιά το όρισμα πρέπει να γίνει $G$2:$G$6. Άρα η συνάρτηση πρέπει στο κελί Ε2 να διορθωθεί και να αντιγραφτεί ξανά κάτω-κάτω ως το τέλος της βάσης. Το πρόβλημα όμως αυτό λύνεται και όλα θα γίνουν πιο απλά. Θα ορίσετε με ένα δυναμικό όνομα την στήλη G της αναζήτησης, το οποίο θα παρακολουθεί το πλήθος των λέξεων που γράφετε εκεί, και αυτό θα αυτό θα βάλετε σαν όρισμα στη συνάρτηση. Επιλέξτε το κελί G2 και από το μενού ‘Τύποι’ επιλέξτε ‘Ορισμός ονόματος’. Στο πλαίσιο που θα εμφανιστεί:

easy_search5
γράψτε ‘Όνομα:’ search_for (ή όποιο εσείς θέλετε) και στην ‘Αναφορά σε:’ γράψτε τον τύπο:

=OFFSET(Φύλλο2!$G$2;0;0;COUNTA(Φύλλο2!$G$2:$G$100);1)

Η συνάρτηση OFFSET παρακολουθεί πόσες λέξεις γράψατε στη στήλη G και μέσω του δυναμικού ονόματος search_for επιστρέψει την περιοχή από το κελί G2 έως το τελευταίο κελί της G που περιέχει λέξη, αρκεί να μην αφήνετε κενά κελιά μεταξύ των λέξεων. Αλλάξτε τον αριθμό 100 στον τύπο, με ένα μεγαλύτερο αν θέλετε να ψάξετε για περισσότερες από 99 λέξεις ταυτόχρονα και αλλάξτε το Φύλλο2 με το δικό σας φύλλο.
Τώρα στον κελί Ε2 τροποποιήστε τον τύπο αντικαθιστώντας το όρισμα $G$2:$G$4 με το δυναμικό όνομα search_for

=SUMPRODUCT(--ISNUMBER(FIND(search_for;B2:D2)))>0

και φυσικά αντιγράψτε τον νέο τύπο έως το τέλος του πίνακα. Δεν θα χρειάζεται πια να κάνετε αλλαγές στα ορίσματα και στους τύπους. Καθώς προσθέτετε, αφαιρείτε ή τροποποιείτε τις λέξεις στις στήλη G, αυτόματα γίνεται η ενημέρωση της στήλης E.
Αν δουλεύετε σε excel 2007 ή νεώτερο, μπορείτε αντί να ορίσετε με δυναμικό όνομα τη στήλη G, να την ορίσετε σαν πίνακα. Τότε ο τύπος της στήλης Ε πρέπει να γραφτεί:

=SUMPRODUCT(--ISNUMBER(FIND(Πίνακας1;B2:D2)))>0

όπου Πίνακας1 είναι το όνομα του πίνακα. Σημειώστε ότι οι πίνακες έχουν μηδενική συμβατότητα με προηγούμενες εκδόσεις του excel και ακόμα πρέπει να θυμάστε να ρυθμίζετε κάθε φορά το μέγεθος του πίνακα στο κατάλληλο πλήθος γραμμών.
Αν θέλετε μια πιο ξεκάθαρη εικόνα στη στήλη Ε, η επόμενη σύνταξη της συνάρτησης:

=IF(SUMPRODUCT(--ISNUMBER(FIND(search_for;B2:D2)))>0;"ΥΠΑΡΧΕΙ";"")	

επιστρέφει ‘ΥΠΑΡΧΕΙ’ στη θέση του TRUE και αφήνει τα αλλά κελιά της E κενά.
Επειδή η συνάρτηση FIND κάνει διάκριση πεζών-κεφαλαίων γραμμάτων και τονισμένων-άτονων, αν θέλετε μία συνάρτηση που δεν θα κάνει αυτή τη διάκριση τότε χρησιμοποιήστε τον τύπο:

=SUMPRODUCT(--ISNUMBER(FIND(UPPER(LOWER(search_for));UPPER(LOWER(B2:D2)))))>0	

Εναλλακτικά, αν η βάση σας είναι μεγάλη, και δεν θέλετε να βαραίνει το φύλλο με τις πολλές συναρτήσεις της στήλης E, χρησιμοποιείστε την ίδια συνάρτηση για να εισάγετε μορφοποίηση υπό όρους, η όποια θα εντοπίζει τις εγγραφές που περιέχουν τις λέξεις που ψάχνετε, χρωματίζοντάς τες.

easy_search6
Πιο αναλυτικά, για να εισάγετε τη μορφοποίηση στο παραπάνω παράδειγμα, επιλέξτε όλο τον πίνακα Α2:D16 και από το κεντρικό μενού επιλέξτε ‘Μορφοποίηση υπό όρους’ και στη συνέχεια ‘Δημιουργία κανόνα.. ‘. Ανοίγει η καρτέλα: ‘Δημιουργία κανόνα μορφοποίησης’ και επιλέξτε: ‘Χρήση τύπου για τον καθορισμό των κελιών που θα μορφοποιηθούν’ και στο πλαίσιο: ‘Μορφοποίηση τιμών όπου ο τύπος είναι αληθής’ γράψτε τον τύπο:

=SUMPRODUCT(--ISNUMBER(FIND(search_for;$B2:$D2)))>0	

Στη συνέχεια επιλέξτε το πλήκτρο ‘Μορφοποίηση’ και από την καρτέλα ‘Γέμισμα’ διαλέξτε ένα χρώμα. Πατήστε ΟΚ και ξανά ΟΚ και έχετε έτσι, ολοκληρώσει τη μορφοποίηση. Κάθε φορά που αλλάζουν οι λέξεις στη στήλη G, θα αλλάζουν οι χρωματισμένες γραμμές στη βάση, υπογραμμίζοντας άμεσα αυτές που θέλετε.
Κατεβάστε αν θέλετε το excel βιβλίο easy_seatch.xls όπου θα δείτε ένα παράδειγμα στην πράξη και θα διαβάσετε μερικές ακόμα λεπτομέρειες. Το βιβλίο μπορείτε να κατεβάσετε και από εδώ.

This entry was posted in excel, Μορφοποίηση υπό όρους, Συναρτήσεις φύλλου, Φίλτρα and tagged , , , , . Bookmark the permalink.

4 Responses to Εύκολη και γρήγορη αναζήτηση σε πολλές στήλες.

  1. Ο/Η Apostolos orfanidis λέει:

    Αγαπητέ δάσκαλε ευχαριστω Πολυ για την νεα σου αναρτηση .ψαγμενο θέμα Πολυ χρήσιμο σε όλους μας .καλη συνέχεια

  2. Ο/Η vioannis λέει:

    Απόστολε, χαίρομαι που το βρήκες χρήσιμο. Νομίζω και εγώ ότι είναι ένας απλός και πρακτικός τρόπος αναζήτησης, και για προχωρημένους και αρχάριους χρήστες.

  3. Ο/Η Vaggelis Balakas λέει:

    Γιαννη δουλευει αψογα.Αυριο παει για android test με καποιο δωρεαν προγραμμα για βιβλια excel! Nα ρωτησω για κατι ξανα…….. εαν η λιστα ειναι αρκετες σειρες ,ας πουμε 833 σειρες , η αναζητηση γινεται και ειναι τελεια… τα κελια τα αλλα τα οποια δεν εχουν καμια επαφη με το αποτελεσμα της αναζητησης μπορουν να συρικνωθουν και απλα να ειναι εκει και να μην φαινονται? Να πω ξανα στην αναζητηση αυτην εδω.

  4. Ο/Η vioannis λέει:

    Βαγγέλη, ναι γίνεται. Για excel έως και 2003 μπορείς να φιλτράρεις τις χρωματισμένες εγγραφές και να κρύψεις τις άλλες, μόνο αν έχεις εισάγει και την συνάρτηση και φιλτράρεις ως προς TRUE, όπως το περιγράφω στο σημείωμα. Από το excel 2007 και πάνω, δίνεται η δυνατότητα (στο απλό φίλτρο) να φιλτράρεις ως προς χρώμα, αρά το κρύψιμο των μη χρωματισμένων εγγραφών είναι άμεσο και χωρίς τη συνάρτηση.

Τα σχόλια είναι απενεργοποιημένα.