Περιεχόμενα
Η αναζήτηση λέξεων-κλειδιών στο κείμενο προέλευσης είναι μια από τις πιο συνηθισμένες εργασίες κατά την εργασία με δεδομένα. Ας δούμε τη λύση του με διάφορους τρόπους χρησιμοποιώντας το ακόλουθο παράδειγμα:
Ας υποθέσουμε ότι εσείς και εγώ έχουμε μια λίστα με λέξεις-κλειδιά – τα ονόματα των επωνυμιών αυτοκινήτων – και έναν μεγάλο πίνακα με όλα τα είδη ανταλλακτικών, όπου οι περιγραφές μπορεί μερικές φορές να περιέχουν μία ή περισσότερες τέτοιες μάρκες ταυτόχρονα, εάν το ανταλλακτικό χωράει περισσότερες από μία μάρκα αυτοκινήτου. Το καθήκον μας είναι να βρούμε και να εμφανίσουμε όλες τις λέξεις-κλειδιά που εντοπίστηκαν σε γειτονικά κελιά μέσω ενός δεδομένου διαχωριστικού χαρακτήρα (για παράδειγμα, κόμμα).
Μέθοδος 1. Power Query
Φυσικά, πρώτα μετατρέπουμε τα τραπέζια μας σε δυναμικά («έξυπνα») χρησιμοποιώντας μια συντόμευση πληκτρολογίου Ctrl+T ή εντολές Αρχική σελίδα – Μορφοποίηση ως πίνακα (Αρχική σελίδα — Μορφοποίηση ως πίνακα), δώστε τους ονόματα (για παράδειγμα Γραμματόσημαи Ανταλλακτικά) και φορτώστε ένα προς ένα στο πρόγραμμα επεξεργασίας Power Query επιλέγοντας στην καρτέλα Δεδομένα – Από πίνακα/Εύρος (Δεδομένα — Από πίνακα/εύρος). Εάν έχετε παλαιότερες εκδόσεις του Excel 2010-2013, όπου το Power Query είναι εγκατεστημένο ως ξεχωριστό πρόσθετο, τότε το επιθυμητό κουμπί θα βρίσκεται στην καρτέλα Ερώτημα ισχύος. Εάν έχετε μια ολοκαίνουργια έκδοση του Excel 365, τότε το κουμπί Από Πίνακας/Εύρος τηλεφώνησε εκεί τώρα Με φύλλα (Από Φύλλο).
Μετά τη φόρτωση κάθε πίνακα στο Power Query, επιστρέφουμε στο Excel με την εντολή Αρχική σελίδα — Κλείσιμο και φόρτωση — Κλείσιμο και φόρτωση σε… — Δημιουργία μόνο σύνδεσης (Αρχική — Κλείσιμο & Φόρτωση — Κλείσιμο & Φόρτωση σε… — Δημιουργία μόνο σύνδεσης).
Τώρα ας δημιουργήσουμε ένα διπλό αίτημα Ανταλλακτικάκάνοντας δεξί κλικ πάνω του και επιλέγοντας Διπλότυπο αίτημα (Διπλότυπο ερώτημα), στη συνέχεια μετονομάστε το αίτημα αντιγραφής που προκύπτει σε Τα αποτελέσματα και θα συνεχίσουμε να συνεργαζόμαστε μαζί του.
Η λογική των ενεργειών είναι η εξής:
- Στην καρτέλα Για προχωρημένους Προσθήκη στήλης επιλέξτε μια ομάδα Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσαρμοσμένη στήλη) και εισάγετε τον τύπο = Μάρκες. Αφού κάνετε κλικ στο OK θα λάβουμε μια νέα στήλη, όπου σε κάθε κελί θα υπάρχει ένας ένθετος πίνακας με μια λίστα με τις λέξεις-κλειδιά μας – μάρκες αυτοκινητοβιομηχανιών:
- Χρησιμοποιήστε το κουμπί με τα διπλά βέλη στην κεφαλίδα της στήλης που προστέθηκε για να αναπτύξετε όλους τους ένθετους πίνακες. Ταυτόχρονα, οι γραμμές με περιγραφές ανταλλακτικών θα πολλαπλασιαστούν επί πολλαπλάσιο του αριθμού των εμπορικών σημάτων και θα λάβουμε όλα τα πιθανά ζεύγη-συνδυασμούς «εμπορικής μάρκας ανταλλακτικών»:
- Στην καρτέλα Για προχωρημένους Προσθήκη στήλης επιλέξτε μια ομάδα Στήλη υπό όρους (Στήλη υπό όρους) και ορίστε μια συνθήκη για τον έλεγχο της εμφάνισης μιας λέξης-κλειδιού (μάρκας) στο κείμενο προέλευσης (περιγραφή μέρους):
- Για να κάνετε την αναζήτηση μη ευαίσθητη, προσθέστε μη αυτόματα το τρίτο όρισμα στη γραμμή τύπων Σύγκριση.OrdinalIgnoreCase στη λειτουργία ελέγχου εμφάνισης Κείμενο.Περιέχει (εάν η γραμμή τύπων δεν είναι ορατή, τότε μπορεί να ενεργοποιηθεί στην καρτέλα Βαθμολογία Κριτικής):
- Φιλτράρουμε τον πίνακα που προκύπτει αφήνοντας μόνο έναν στην τελευταία στήλη, δηλαδή αντιστοιχίες και αφαιρούμε την περιττή στήλη Περιστατικά.
- Ομαδοποίηση πανομοιότυπων περιγραφών με την εντολή Ομαδοποίηση από αυτί Μεταμόρφωση (Μετατροπή — Ομαδοποίηση κατά). Ως λειτουργία συγκέντρωσης, επιλέξτε Όλες οι γραμμές (Όλες οι σειρές). Στην έξοδο, παίρνουμε μια στήλη με πίνακες, η οποία περιέχει όλες τις λεπτομέρειες για κάθε ανταλλακτικό, συμπεριλαμβανομένων των εμπορικών σημάτων των αυτοκινητοβιομηχανιών που χρειαζόμαστε:
- Για να εξαγάγετε βαθμούς για κάθε μέρος, προσθέστε μια άλλη υπολογισμένη στήλη στην καρτέλα Προσθήκη στήλης – Προσαρμοσμένη στήλη (Προσθήκη στήλης — Προσαρμοσμένη στήλη) και χρησιμοποιήστε έναν τύπο που αποτελείται από έναν πίνακα (βρίσκονται στη στήλη μας Περιγραφή) και το όνομα της εξαγόμενης στήλης:
- Κάνουμε κλικ στο κουμπί με διπλά βέλη στην κεφαλίδα της στήλης που προκύπτει και επιλέγουμε την εντολή Εξαγωγή τιμών (Εξαγωγή τιμών)για να εξάγετε γραμματόσημα με οποιονδήποτε χαρακτήρα οριοθέτη θέλετε:
- Αφαίρεση περιττής στήλης Περιγραφή.
- Για να προσθέσουμε στον πίνακα που προκύπτει τα εξαρτήματα που εξαφανίστηκαν από αυτόν, όπου δεν βρέθηκαν μάρκες στις περιγραφές, εκτελούμε τη διαδικασία συνδυασμού του ερωτήματος Αποτέλεσμα με αρχικό αίτημα Ανταλλακτικά κουμπί Συνδυασμός αυτί Αρχική (Αρχική σελίδα — Ερωτήματα συγχώνευσης). Τύπος σύνδεσης - Εξωτερική Σύνδεση Δεξιά (Δεξιά εξωτερική ένωση):
- Το μόνο που μένει είναι να αφαιρέσουμε τις επιπλέον στήλες και να μετονομάσουμε-μετακινήσουμε τις υπόλοιπες – και το έργο μας επιλύθηκε:
Μέθοδος 2. Φόρμουλες
Εάν έχετε μια έκδοση του Excel 2016 ή μεταγενέστερη, τότε το πρόβλημά μας μπορεί να λυθεί με πολύ συμπαγή και κομψό τρόπο χρησιμοποιώντας τη νέα λειτουργία ΣΥΝΔΥΑΣΜΟΣ (ΣΥΝΔΕΣΗ ΚΕΙΜΕΝΟΥ):
Η λογική πίσω από αυτόν τον τύπο είναι απλή:
- Λειτουργία ΑΝΑΖΗΤΗΣΗ (ΕΥΡΗΜΑ) αναζητά την εμφάνιση κάθε μάρκας με τη σειρά της στην τρέχουσα περιγραφή του ανταλλακτικού και επιστρέφει είτε τον αύξοντα αριθμό του συμβόλου, από το οποίο βρέθηκε η μάρκα, είτε το σφάλμα #VALUE! εάν η μάρκα δεν είναι στην περιγραφή.
- Στη συνέχεια, χρησιμοποιώντας τη λειτουργία IF (ΑΝ) и EOSHIBKA (ISERROR) Αντικαθιστούμε τα σφάλματα με μια κενή συμβολοσειρά κειμένου "", και τους τακτικούς αριθμούς των χαρακτήρων με τις ίδιες τις επωνυμίες.
- Η προκύπτουσα συστοιχία κενών κελιών και εμπορικών σημάτων που βρέθηκαν συγκεντρώνεται σε μια ενιαία συμβολοσειρά μέσω ενός δεδομένου διαχωριστικού χαρακτήρα χρησιμοποιώντας τη συνάρτηση ΣΥΝΔΥΑΣΜΟΣ (ΣΥΝΔΕΣΗ ΚΕΙΜΕΝΟΥ).
Σύγκριση απόδοσης και Power Query Query Buffering για Speedup
Για τη δοκιμή απόδοσης, ας πάρουμε έναν πίνακα με 100 περιγραφές ανταλλακτικών ως αρχικά δεδομένα. Σε αυτό παίρνουμε τα ακόλουθα αποτελέσματα:
- Χρόνος επανυπολογισμού με τύπους (Μέθοδος 2) – 9 sec. όταν αντιγράψετε για πρώτη φορά τον τύπο σε ολόκληρη τη στήλη και 2 δευτ. σε επαναλαμβανόμενες (πιθανώς επηρεάζει την προσωρινή μνήμη).
- Ο χρόνος ενημέρωσης του ερωτήματος Power Query (Μέθοδος 1) είναι πολύ χειρότερος – 110 δευτερόλεπτα.
Φυσικά, πολλά εξαρτώνται από το υλικό ενός συγκεκριμένου υπολογιστή και την εγκατεστημένη έκδοση του Office και τις ενημερώσεις, αλλά η συνολική εικόνα, νομίζω, είναι ξεκάθαρη.
Για να επιταχύνουμε ένα ερώτημα Power Query, ας αποθηκεύσουμε προσωρινά τον πίνακα αναζήτησης Γραμματόσημα, γιατί δεν αλλάζει στη διαδικασία εκτέλεσης του ερωτήματος και δεν είναι απαραίτητος ο συνεχώς επανυπολογισμός του (όπως κάνει de facto το Power Query). Για αυτό χρησιμοποιούμε τη συνάρτηση Table.Buffer από την ενσωματωμένη γλώσσα Power Query M.
Για να το κάνετε αυτό, ανοίξτε ένα ερώτημα Τα αποτελέσματα και στην καρτέλα Βαθμολογία Κριτικής πάτα το κουμπί Advanced Editor (Προβολή — Σύνθετη επεξεργασία). Στο παράθυρο που ανοίγει, προσθέστε μια γραμμή με μια νέα μεταβλητή Marky 2, η οποία θα είναι μια έκδοση προσωρινής αποθήκευσης του καταλόγου μας της αυτοκινητοβιομηχανίας και χρησιμοποιήστε αυτήν τη νέα μεταβλητή αργότερα στην ακόλουθη εντολή ερωτήματος:
Μετά από μια τέτοια βελτίωση, η ταχύτητα ενημέρωσης του αιτήματός μας αυξάνεται σχεδόν κατά 7 φορές – έως και 15 δευτερόλεπτα. Πολύ διαφορετικό πράγμα 🙂
- Αναζήτηση ασαφούς κειμένου στο Power Query
- Αντικατάσταση μαζικού κειμένου με τύπους
- Αντικατάσταση μαζικού κειμένου στο Power Query με λειτουργία List.Acumulate