Πιο πρόσφατα, συζητήσαμε τη χρήση της συνάρτησης FILTER.XML για την εισαγωγή δεδομένων XML από το Διαδίκτυο – η κύρια εργασία για την οποία προορίζεται στην πραγματικότητα αυτή η λειτουργία. Στην πορεία, ωστόσο, εμφανίστηκε μια άλλη απροσδόκητη και όμορφη χρήση αυτής της λειτουργίας – για γρήγορη διαίρεση του κολλώδους κειμένου σε κελιά.
Ας υποθέσουμε ότι έχουμε μια στήλη δεδομένων όπως αυτή:
Φυσικά, για ευκολία, θα ήθελα να το χωρίσω σε ξεχωριστές στήλες: όνομα εταιρείας, πόλη, δρόμος, σπίτι. Μπορείτε να το κάνετε αυτό με πολλούς διαφορετικούς τρόπους:
- Χρήση Κείμενο ανά στήλες από την καρτέλα ημερομηνία (Δεδομένα — Κείμενο σε στήλες) και κάνε τρία βήματα Αναλυτής κειμένου. Αλλά αν τα δεδομένα αλλάξουν αύριο, θα πρέπει να επαναλάβετε ξανά όλη τη διαδικασία.
- Φορτώστε αυτά τα δεδομένα στο Power Query και διαιρέστε τα εκεί και, στη συνέχεια, ανεβάστε τα ξανά στο φύλλο και, στη συνέχεια, ενημερώστε το ερώτημα όταν αλλάξουν τα δεδομένα (κάτι που είναι ήδη πιο εύκολο).
- Εάν χρειάζεται να ενημερώνεστε αμέσως, τότε μπορείτε να γράψετε μερικούς πολύ σύνθετους τύπους για να βρείτε κόμματα και να εξαγάγετε το κείμενο μεταξύ τους.
Και μπορείτε να το κάνετε πιο κομψά και να χρησιμοποιήσετε τη λειτουργία FILTER.XML, αλλά τι σχέση έχει;
Η συνάρτηση FILTER.XML λαμβάνει ως αρχικό της όρισμα έναν κώδικα XML — κείμενο επισημασμένο με ειδικές ετικέτες και χαρακτηριστικά, και στη συνέχεια το αναλύει στα στοιχεία της, εξάγοντας τα τμήματα δεδομένων που χρειαζόμαστε. Ο κώδικας XML μοιάζει συνήθως κάπως έτσι:
Στην XML, κάθε στοιχείο δεδομένων πρέπει να περικλείεται σε ετικέτες. Μια ετικέτα είναι κάποιο κείμενο (στο παραπάνω παράδειγμα είναι διαχειριστής, όνομα, κέρδος) που περικλείεται σε αγκύλες. Οι ετικέτες έρχονται πάντα σε ζευγάρια – ανοίγουν και κλείνουν (με μια κάθετο προσθήκη στην αρχή).
Η συνάρτηση FILTER.XML μπορεί εύκολα να εξαγάγει τα περιεχόμενα όλων των ετικετών που χρειαζόμαστε, για παράδειγμα, τα ονόματα όλων των διαχειριστών και (το πιο σημαντικό) να τα εμφανίζει όλα ταυτόχρονα σε μια λίστα. Έτσι, το καθήκον μας είναι να προσθέσουμε ετικέτες στο κείμενο προέλευσης, μετατρέποντάς το σε κώδικα XML κατάλληλο για μετέπειτα ανάλυση από τη συνάρτηση FILTER.XML.
Αν πάρουμε για παράδειγμα την πρώτη διεύθυνση από τη λίστα μας, τότε θα χρειαστεί να τη μετατρέψουμε σε αυτήν την κατασκευή:
Κάλεσα το καθολικό άνοιγμα και κλείσιμο όλων των ετικετών κειμένου t, και οι ετικέτες που πλαισιώνουν κάθε στοιχείο είναι s., αλλά μπορείτε να χρησιμοποιήσετε οποιεσδήποτε άλλες ονομασίες - δεν έχει σημασία.
Εάν αφαιρέσουμε εσοχές και αλλαγές γραμμής από αυτόν τον κώδικα - εντελώς, παρεμπιπτόντως, προαιρετικά και προστίθενται μόνο για λόγους σαφήνειας, τότε όλα αυτά θα μετατραπούν σε γραμμή:
Και μπορεί ήδη να ληφθεί σχετικά εύκολα από τη διεύθυνση πηγής αντικαθιστώντας τα κόμματα σε αυτήν με μερικές ετικέτες χρησιμοποιώντας τη συνάρτηση ΥΠΟΚΑΤΑΣΤΑΤΟ (ΥΠΟΚΑΤΑΣΤΑΤΟ) και κόλληση με το σύμβολο & στην αρχή και στο τέλος των ετικετών ανοίγματος και κλεισίματος:
Για να επεκτείνουμε το εύρος που προκύπτει οριζόντια, χρησιμοποιούμε την τυπική συνάρτηση ΜΕΤΑΦΟΡΑ (ΜΕΤΑΘΕΤΩ), τυλίγοντας τη φόρμουλα μας σε αυτό:
Ένα σημαντικό χαρακτηριστικό ολόκληρου αυτού του σχεδιασμού είναι ότι στη νέα έκδοση του Office 2021 και του Office 365 με υποστήριξη για δυναμικούς πίνακες, δεν απαιτούνται ειδικές χειρονομίες για εισαγωγή – απλώς εισάγετε και κάντε κλικ στο εισάγετε – η ίδια η φόρμουλα καταλαμβάνει τον αριθμό των κελιών που χρειάζεται και όλα λειτουργούν με ένα χτύπημα. Σε προηγούμενες εκδόσεις, όπου δεν υπήρχαν ακόμη δυναμικοί πίνακες, θα πρέπει πρώτα να επιλέξετε επαρκή αριθμό κενών κελιών πριν εισαγάγετε τον τύπο (μπορείτε με περιθώριο) και αφού δημιουργήσετε τον τύπο, πατήστε τη συντόμευση πληκτρολογίου Ctrl+αλλαγή+εισάγετεγια να το εισάγετε ως τύπο πίνακα.
Ένα παρόμοιο τέχνασμα μπορεί να χρησιμοποιηθεί κατά τον διαχωρισμό κειμένου που είναι κολλημένο μαζί σε ένα κελί μέσω μιας αλλαγής γραμμής:
Η μόνη διαφορά με το προηγούμενο παράδειγμα είναι ότι αντί για κόμμα, εδώ αντικαθιστούμε τον αόρατο χαρακτήρα αλλαγής γραμμής Alt + Enter, ο οποίος μπορεί να καθοριστεί στον τύπο χρησιμοποιώντας τη συνάρτηση CHAR με κωδικό 10.
- Οι λεπτές αποχρώσεις της εργασίας με αλλαγές γραμμής (Alt + Enter) στο Excel
- Διαιρέστε το κείμενο ανά στήλες στο Excel
- Αντικατάσταση κειμένου με SUBSTITUTE