Αναζήτηση και επισήμανση δεδομένων στο Excel (με μορφοποίηση υπό όρους)

Παρακολουθήστε βίντεο - Αναζήτηση και επισημάνετε δεδομένα χρησιμοποιώντας μορφοποίηση υπό όρους

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

Παρόλο που δεν υπάρχει άμεσος τρόπος για να το κάνετε αυτό στο Excel, μπορείτε να δημιουργήσετε λειτουργίες αναζήτησης χρησιμοποιώντας μορφοποίηση υπό όρους.

Για παράδειγμα, ας υποθέσουμε ότι έχετε ένα σύνολο δεδομένων όπως φαίνεται παρακάτω (στην εικόνα). Έχει στήλες για το όνομα προϊόντος, τον αντιπρόσωπο πωλήσεων και τη χώρα.

Τώρα μπορείτε να χρησιμοποιήσετε μορφοποίηση υπό όρους για να αναζητήσετε μια λέξη -κλειδί (εισάγοντάς την στο κελί C2) και να επισημάνετε όλα τα κελιά που έχουν αυτήν τη λέξη -κλειδί.

Κάτι όπως φαίνεται παρακάτω (όπου εισάγω το όνομα του στοιχείου στο κελί B2 και πατάω Enter, επισημαίνεται ολόκληρη η σειρά):

Σε αυτό το σεμινάριο, θα σας δείξω πώς να δημιουργήσετε αυτήν τη λειτουργία αναζήτησης και να επισημάνετε στο Excel.

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

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου και ακολουθήστε.

Αναζήτηση και επισήμανση αντιστοίχισης κελιών

Σε ΑΥΤΗΝ την ΕΝΟΤΗΤΑ. Θα σας δείξω πώς μπορείτε να αναζητήσετε και να επισημάνετε μόνο τα αντίστοιχα κελιά σε ένα σύνολο δεδομένων.

Κάτι όπως φαίνεται παρακάτω:

Ακολουθούν τα βήματα για την αναζήτηση και την επισήμανση όλων των κελιών που έχουν το αντίστοιχο κείμενο:

1. Επιλέξτε το σύνολο δεδομένων στο οποίο θέλετε να εφαρμόσετε μορφοποίηση υπό όρους (A4: F19 σε αυτό το παράδειγμα).
2. Κάντε κλικ στην καρτέλα Αρχική σελίδα.
3. Στην ομάδα Στυλ, κάντε κλικ στην επιλογή Μορφοποίηση υπό όρους.
4. Στις αναπτυσσόμενες επιλογές, κάντε κλικ στο Νέος κανόνας.
   
5. Στο παράθυρο διαλόγου «Νέος κανόνας μορφοποίησης», κάντε κλικ στην επιλογή «Χρήση ενός τύπου για να καθορίσετε ποια κελιά θα μορφοποιήσετε».
6. Εισαγάγετε τον ακόλουθο τύπο: = A4 = $ B $ 1
7. Κάντε κλικ στο κουμπί «Μορφοποίηση…».
8. Καθορίστε τη μορφοποίηση (για να επισημάνετε κελιά που ταιριάζουν με τη λέξη -κλειδί που αναζητήσατε).
9. Κάντε κλικ στο OK.

Τώρα πληκτρολογήστε οτιδήποτε στο κελί Β1 και πατήστε enter. Θα επισημάνει τα αντίστοιχα κελιά στο σύνολο δεδομένων που περιέχουν τη λέξη -κλειδί στο Β1.

Πως λειτουργεί αυτό?

Η μορφοποίηση υπό όρους εφαρμόζεται κάθε φορά που ο τύπος που καθορίζεται σε αυτήν επιστρέφει TRUE.

Στο παραπάνω παράδειγμα, ελέγχουμε κάθε κελί χρησιμοποιώντας τον τύπο = A4 = $ B $ 1

Η υπό όρους μορφοποίηση ελέγχει κάθε κελί και επαληθεύει ότι το περιεχόμενο στο κελί είναι το ίδιο με αυτό στο κελί Β1. Εάν είναι το ίδιο, ο τύπος επιστρέφει TRUE και το κελί επισημαίνεται. Εάν δεν είναι το ίδιο, ο τύπος επιστρέφει ΛΑΘΟΣ και δεν συμβαίνει τίποτα.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου και ακολουθήστε.

Αναζήτηση και επισήμανση σειρών με αντιστοίχιση δεδομένων

Εάν θέλετε να επισημάνετε ολόκληρη τη σειρά αντί για τα αντίστοιχα κελιά, μπορείτε να το κάνετε τροποποιώντας λίγο τον τύπο.

Παρακάτω είναι ένα παράδειγμα όπου ολόκληρη η γραμμή επισημαίνεται εάν ο τύπος προϊόντος ταιριάζει με αυτόν στο κελί B1.

Ακολουθούν τα βήματα για την αναζήτηση και την επισήμανση ολόκληρης της σειράς:

1. Επιλέξτε το σύνολο δεδομένων στο οποίο θέλετε να εφαρμόσετε μορφοποίηση υπό όρους (A4: F19 σε αυτό το παράδειγμα).
2. Κάντε κλικ στην καρτέλα Αρχική σελίδα.
3. Στην ομάδα Στυλ, κάντε κλικ στην επιλογή Μορφοποίηση υπό όρους.
4. Στις αναπτυσσόμενες επιλογές, κάντε κλικ στο Νέος κανόνας.
   
5. Στο παράθυρο διαλόγου «Νέος κανόνας μορφοποίησης», κάντε κλικ στην επιλογή «Χρήση ενός τύπου για να καθορίσετε ποια κελιά θα μορφοποιήσετε».
6. Εισαγάγετε τον ακόλουθο τύπο: = $ B4 = $ B $ 1
7. Κάντε κλικ στο κουμπί "Μορφοποίηση …".
8. Καθορίστε τη μορφοποίηση (για να επισημάνετε κελιά που ταιριάζουν με τη λέξη -κλειδί που αναζητήσατε).
9. Κάντε κλικ στο OK.

Τα παραπάνω βήματα θα αναζητήσουν το καθορισμένο στοιχείο στο σύνολο δεδομένων και αν βρει το στοιχείο που ταιριάζει, θα επισημάνει ολόκληρη τη σειρά.

Λάβετε υπόψη ότι αυτό θα ελέγξει μόνο για τη στήλη στοιχείου. Εάν εισαγάγετε ένα όνομα Αντιπροσώπου πωλήσεων εδώ, δεν θα λειτουργήσει. Εάν θέλετε να λειτουργεί για το όνομα Sales Rep, πρέπει να αλλάξετε τον τύπο σε = $ C4 = $ B $ 1

Σημείωση: Ο λόγος που επισημαίνει ολόκληρη τη σειρά και όχι μόνο το κελί που ταιριάζει είναι ότι χρησιμοποιήσαμε ένα σύμβολο $ πριν από την αναφορά στήλης ($ B4). Τώρα, όταν η μορφοποίηση υπό όρους αναλύει κελιά σε μια σειρά, ελέγχει εάν η τιμή στη στήλη Β αυτής της σειράς είναι ίση με την τιμή στο κελί Β1. Έτσι, ακόμη και όταν αναλύει το Α4 ή το Β4 ή το C4 και ούτω καθεξής, ελέγχει μόνο την τιμή Β4 (καθώς έχουμε κλειδώσει τη στήλη Β χρησιμοποιώντας το σύμβολο του δολαρίου).

Μπορείτε να διαβάσετε περισσότερα για τις απόλυτες, σχετικές και μικτές αναφορές εδώ.

Αναζήτηση και επισήμανση γραμμών (βάσει Μερικής αντιστοίχισης)

Σε ορισμένες περιπτώσεις, μπορεί να θέλετε να επισημάνετε σειρές βάσει μερικής αντιστοίχισης.

Για παράδειγμα, εάν έχετε στοιχεία όπως White Board, Green Board και Grey Board και θέλετε να τα επισημάνετε όλα με βάση τη λέξη Board, τότε μπορείτε να το κάνετε αυτό χρησιμοποιώντας τη συνάρτηση SEARCH.

Κάτι όπως φαίνεται παρακάτω:

Ακολουθούν τα βήματα για να το κάνετε αυτό:

1. Επιλέξτε το σύνολο δεδομένων στο οποίο θέλετε να εφαρμόσετε μορφοποίηση υπό όρους (A4: F19 σε αυτό το παράδειγμα).
2. Κάντε κλικ στην καρτέλα Αρχική σελίδα.
3. Στην ομάδα Στυλ, κάντε κλικ στην επιλογή Μορφοποίηση υπό όρους.
4. Στις αναπτυσσόμενες επιλογές, κάντε κλικ στο Νέος κανόνας.
   
5. Στο παράθυρο διαλόγου «Νέος κανόνας μορφοποίησης», κάντε κλικ στην επιλογή «Χρήση ενός τύπου για να καθορίσετε ποια κελιά θα μορφοποιήσετε».
6. Εισαγάγετε τον ακόλουθο τύπο: = AND ($ B $ 1 ””, ISNUMBER (SEARCH ($ B $ 1, $ B4)))
7. Κάντε κλικ στο κουμπί «Μορφοποίηση…».
8. Καθορίστε τη μορφοποίηση (για να επισημάνετε κελιά που ταιριάζουν με τη λέξη -κλειδί που αναζητήσατε).
9. Κάντε κλικ στο OK.

Πως λειτουργεί αυτό?

• Η συνάρτηση SEARCH αναζητά τη συμβολοσειρά/λέξη -κλειδί αναζήτησης σε όλα τα κελιά σε μια σειρά. Επιστρέφει ένα σφάλμα εάν η λέξη -κλειδί αναζήτησης δεν βρίσκεται και επιστρέφει έναν αριθμό αν βρει μια αντιστοίχιση.
• Η συνάρτηση ISNUMBER μετατρέπει το σφάλμα σε FALSE και τις αριθμητικές τιμές σε TRUE.
• Η συνάρτηση AND ελέγχει για μια πρόσθετη συνθήκη - ότι το κελί C2 δεν πρέπει να είναι κενό.

Έτσι, τώρα, κάθε φορά που πληκτρολογείτε μια λέξη -κλειδί στο κελί Β1 και πατάτε Enter, επισημαίνει όλες τις γραμμές που έχουν τα κελιά που περιέχουν αυτήν τη λέξη -κλειδί.

Συμβουλή μπόνους: Εάν θέλετε να κάνετε ευαίσθητη την πεζά αναζήτηση, χρησιμοποιήστε τη συνάρτηση FIND αντί για ΑΝΑΖΗΤΗΣΗ.

Κάντε κλικ εδώ για λήψη του παραδείγματος αρχείου και ακολουθήστε.

Λειτουργικότητα δυναμικής αναζήτησης και επισήμανσης (επισημαίνει κατά την πληκτρολόγηση)

Χρησιμοποιώντας τα ίδια κόλπα μορφοποίησης υπό όρους που καλύπτονται παραπάνω, μπορείτε επίσης να το κάνετε ένα βήμα παραπέρα και να το κάνετε δυναμικό.

Για παράδειγμα, μπορείτε να δημιουργήσετε μια γραμμή αναζήτησης όπου τα αντίστοιχα δεδομένα επισημαίνονται καθώς πληκτρολογείτε στη γραμμή αναζήτησης.

Κάτι όπως φαίνεται παρακάτω:

Αυτό μπορεί να γίνει χρησιμοποιώντας τα στοιχεία ελέγχου ActiveX και μπορεί να είναι μια καλή λειτουργία για χρήση κατά τη δημιουργία αναφορών ή πίνακα ελέγχου.

Παρακάτω είναι ένα βίντεο όπου δείχνω πώς να το δημιουργήσετε:

Βρήκατε χρήσιμο αυτό το σεμινάριο; Πείτε μου τις σκέψεις σας στην ενότητα σχολίων.

Μπορεί επίσης να σας αρέσουν τα ακόλουθα σεμινάρια Excel:

• Δυναμικό φίλτρο Excel - Εξάγει δεδομένα καθώς πληκτρολογείτε.
• Δημιουργήστε μια αναπτυσσόμενη λίστα με πρόταση αναζήτησης.
• Δημιουργία χάρτη θερμότητας στο Excel.
• Επισήμανση γραμμών με βάση την τιμή κελιού στο Excel.
• Επισημάνετε την ενεργή γραμμή και στήλη σε μια περιοχή δεδομένων στο Excel.
• Πώς να επισημάνετε τα κενά κελιά στο Excel.