Sonderzeichen erkennen und definieren (1)

In einem Excel-Forum hat ein Fragesteller um die Lösung nachge­fragt, wie er spezielle Dup­likate erken­nen und ent­fer­nen kann. Das „spezielle” bezog sich darauf, dass bei Namen für die gle­iche Per­son mitunter ver­schiedene Schreib­weisen ver­wen­det wor­den sind; das kön­nen beispiel­sweise Umlaute sein, die teil­weise (kor­rekt) als Umlaut (zum Beispiel Ü) geschrieben wer­den, mitunter aber auch der Inter­na­tion­al­ität wegen als Ue. Oder aber der Vor­name René auch ein­mal in eingedeutscht Schreib­weise als Rene.

Der Fragesteller wollte nun ins­beson­dere die zweit­ge­nan­nten Son­derze­ichen erken­nen und als Liste darstellen, um die entsprechen­den Daten­sätze zu kor­rigieren und anschließend eventuelle durch die Kor­rek­tur ent­stande­nen Dup­likate zu ent­fer­nen. Der Hil­fe suchende hat (natür­lich) keine Muster-Datei beige­fügt was mich dazu ver­an­lasst hat, eine eigene Namens-Auf­stel­lung zu erstellen und dann zu nutzen.

Da im 20. Deutschen Bun­destag ver­schiedene Abge­ord­nete vertreten sind deren Namen Zeichen enthält, die außer­halb des Bere­ichs des deutschen, 26 Zeichen umfassenden Alpha­bets als auch der Umlaute bzw. des ß liegen, habe ich mir solch eine Liste aus dem Inter­net gezo­gen. Die Bun­destagsver­wal­tung selb­st bietet mir derzeit (dem Anschein nach) keine entsprechende direkt herun­ter­lad­bare Datei an, darum habe ich erst ein­mal per copy/paste den rel­e­van­ten Inhalt der entsprechen­den Seite in ein Excel-Arbeits­blatt einge­fügt.

Ich empfinde das Ergeb­nis als abso­lut benutzerun­fre­undlich. Aber immer­hin ist das eine gute Vor­lage für das The­ma „Aufar­beit­en von schlecht struk­turi­erten Dat­en”. 😉  Hier kön­nen Sie die von mir erstellte Rohver­sion der *.xlsx herun­ter­laden. Hin­weis: In diesem Beitrag wer­den Sie die Dat­en (die Datei) nicht brauchen, sehr wohl aber in einem weit­eren Beitrag zu diesem The­ma.

Eine im End­ef­fekt wesentlich leichtere Möglichkeit und auch noch ein besseres, weil daten­seit­ig umfan­gre­icheres Ergeb­nis bietet hier Wikipedia mit ein­er sehr aus­führlichen Auf­stel­lung. Dort ist ein direk­ter Import in Pow­er Query über Dat­en | Aus dem Web möglich. 🙂 Ein Tipp: Es gibt eigentlich nur 1 Tabelle (Table), welche (neben anderen Spal­ten) Name und Frak­tion bzw. Partei darstellt.

Da sich diese Über­sicht (von Wikipedia) her­vor­ra­gend für Übun­gen aller Art eignet, würde ich die Abfrage per Schließen & laden in… sich­ern, in das leere Arbeits­blatt Tabelle1 posi­tion­ieren, diese Datei sofort ohne Änderun­gen abspe­ich­ern und anschließend für diese zuerst anste­hende Übung in Sachen Son­derze­ichen noch ein­mal öff­nen. Mein Vorschlag: Sofort nach dem Öff­nen unter einem anderen Namen (beispiel­sweise Son­derze­ichen find­en) abspe­ich­ern, damit das unverän­derte Orig­i­nal erhal­ten bleibt. Diese Vorge­hensweise hat sich in meinem Excel-Leben weit mehr als ein Mal bewährt!

▲ nach oben …

Im ersten Schritt dieser Übung / Lösung geht es um die erste Zeile der Abfrage, welche ja nur eine Wieder­hol­ung der Über­schrift ist. Also wer­den Sie diese Zeile löschen. Und da es bei der gegebe­nen Auf­gabe ja nur um die beson­deren Zeichen in den Namen geht, lösche ich alle Spal­ten außer Name und belasse vielle­icht auch Frak­tion (Partei). Und um die Spalte Name für Ver­gle­ich­szwecke unverän­dert zu lassen, erstelle ich hier­von eine Kopie, wo ich später bei Bedarf Änderun­gen nach Lust und Laune vornehmen kann. PQ stört sich nicht an dem Dup­likat, denn die Spal­tenüber­schrift unter­schei­det sich ja automa­tisch; und ich bin auf dem sicheren und über­sichtlicheren Weg.

Hin­weis: Ich werde Sie in mehreren Schrit­ten zum Ziel führen, denn auch ich mache bei mir sel­ber immer wieder die Erfahrung, dass ich nicht immer alles kon­se­quent bis zum Ende durch­denke und beachte. Noch ein­mal zur Erin­nerung bzw. zur Klarstel­lung: Es soll eine Liste erzeugt wer­den, wo alle nicht ursprünglich zur deutschen Sprache gehören­den Zeichen in den Namen (der Abge­ord­neten) aufge­führt sind. Da diese Auf­stel­lung der Namen nur im deutschen Sprachge­brauch genutzt wer­den soll, kön­nen Umlaute beste­hen bleiben und brauchen nicht geän­dert, nicht in der Liste aufge­führt wer­den. Zugegeben, im ersten beschriebe­nen Schritt wer­den auch die Umlaute als Son­derze­ichen aus­gegeben, aber das wird dann auch rasch geän­dert.

Gehen Sie über Spalte hinzufü­gen | Benutzerdefinierte Spalte und tra­gen Sie im Dia­log bei Neuer Spal­tenname beispiel­sweise Son­derze­ichen ein. Als Benutzerdefinierte Spal­tenformel schreiben Sie diesen Text (diese Formel):
Text.Remove([Name],{"A".."Z","a".."z"})

Beacht­en Sie bitte die exak­te Groß- Klein­schrei­bung und dass in der Formel nach dem Kom­ma und nach dem let­zten " eine geschwun­gene und keine runde Klam­mer ste­ht. – Sie erken­nen, dass in dieser Spalte die meis­ten Zeilen leer sind aber auch alle Umlaute und son­stige nicht zum typ­is­chen Alpha­bet gehörende Zeichen aufge­lis­tet sind. Unter dem Aspekt, dass die Umlaute und das ß nicht als Son­derze­ichen aus­gewiesen wer­den sollen, ergänzen oder ändern Sie die obige Formel so:
Text.Remove([Name],{"A".."Z","a".."z","Ä","ä","Ö","ö","Ü","ü","ß"})

Sie sehen, dass nun deut­lich weniger Zeilen mit einem sicht­baren Ein­trag erhal­ten geblieben sind. Und vielle­icht haben Sie auch bemerkt, dass es keine einzige Zeile mit dem Wert null gibt? Aber auch dazu später mehr.

Für einen schnellen Überblick kön­nen Sie die Spalte Son­derze­ichen natür­lich dahinge­hend fil­tern, dass alle „leeren” Zeilen nicht angezeigt wer­den. Und gewiss fällt Ihnen bei diesem Fil­ter­vor­gang auf, dass die ersten drei Ein­träge der (automa­tisch sortierten) Liste sich schein­bar gar nicht unter­schei­den. Dieses Phänomen soll­ten Sie noch etwas näher erkun­den … 🙂 Erforder­lichen­falls löschen Sie wieder einen geset­zten Fil­ter.

Wie schon ange­merkt: Keine einzige Zelle der Spalte Name ist wirk­lich leer, denn dann würde bei Son­derze­ichen im Fil­ter-Dia­log der unge­filterten Abfrage (NULL) oder (leer) aufge­lis­tet sein. Der vielle­icht sich­er­ste Weg zur „Erleuch­tung” ist, eine neue Spalte mit fol­gen­der Formel anzule­gen:
=Text.Length([Sonderzeichen])
und benen­nen Sie die Spalte beispiel­sweise Textlänge (Son­derze­ichen). Sie kön­nen es aber auch bei dem angegebe­nen Default­wert „Benutzerdefiniert” belassen, denn Sie wer­den diese Spalte nur kurz nutzen.

Sie wer­den erken­nen, dass in den Zeilen mit diesen „inhalt­slosen” Zellen die Werte 1 bis 3 aus­gegeben wer­den. Mein Tipp: Ich fil­tere vorüberge­hend die Spalte Son­derze­ichen dergestalt, dass nur die ersten 3 leer erscheinen­den Zellen erhal­ten bleiben. Nach ein­er Kon­troll-Auswer­tung und Analyse ent­ferne ich natür­lich wieder den Fil­ter-Vor­gang im recht­en Seit­en­fen­ster.

Und das zwis­chen­zeitliche Ergeb­nis lässt Sie gewiss rasch zur Erken­nt­nis kom­men, dass es sich dabei um Leerze­ichen han­delt. Ein kurz­er Blick in das jew­eilige Feld Name wird Ihnen auch bestäti­gen, dass der Name in irgen­dein­er Form bzw. an irgen­dein­er Stelle Leerze­ichen enthält. Und klar, die sollen natür­lich auch nicht als Son­derze­ichen son­dern als „ganz nor­male Zeichen” gew­ertet wer­den. Also ergänzen Sie die Benutzerdefinierte Formel auch noch um das Argu­ment , " " :
Text.Remove([Name], {"A".."Z", "a".."z", "Ä", "ä", "Ö", "ö", "Ü", "ü", "ß", " "})

Hin­weis: In der durch Sie importierten Datei sind wed­er führende noch ange­hängte Leerze­ichen enthal­ten, denken Sie aber bei Importen eigen­er Dat­en auch an die Möglichkeit und bere­ini­gen Sie bei Bedarf die Daten­quelle in Excel oder Pow­er Query entsprechend.

Wie auch immer, Textlänge (Son­derze­ichen) wird nun (nach dieser Änderung) für alle leer erscheinen­den Felder der Spalte Son­derze­ichen die Zahl 0 anzeigen, alter­na­tiv die 1 oder 2. Ide­al­er­weise fil­tern Sie Textlänge (Son­derze­ichen) dergestalt, dass nur Zellen mit dem Wert >0 erhal­ten bleiben.

Nun ist das Ganze durch die wesentlich gerin­gere Zahl der Daten­sätze deut­lich über­sichtlich­er. Aber 67 Zeilen bzw Namen mit Son­derze­ichen sind immer noch deut­lich zu viel. Und klar, das Zeichen - (Binde­strich, Minus) ist bei den Dop­pel­na­men vertreten. Sie ahnen es, die benutzerdefinierte Funktion/Formel muss noch ein­mal um dieses Zeichen ergänzt wer­den; das kön­nen Sie aber nun auch ohne meine Hil­fe. 😎 

Oh ja, in der gefilterten Query sind nur noch 13 Zeilen, das scheint zu passen. Scheint aber nur der Fall zu sein, denn bei ver­schiede­nen Abge­ord­neten ist der zweite Vor­name nach dem ersten Zeichen mit einem Punkt nach dem ersten Zeichen abgekürzt. Ich denke, dass auch dieses keines der aufzulis­ten­den (und später anzu­passe­nen) Son­derze­ichen ist. Darum gehört auch der Punkt in die Text.Remove()-Formel.

Bin­go, nun sind in den 11 Zeilen nur noch „echte” Son­derze­ichen aufge­führt. Und das war das erwün­schte Ziel, oder?… Nicht ganz, denn es sind ja noch Dup­likate wie beispiel­sweise das é enthal­ten. Son­derze­ichen markieren und über Trans­formieren | In Liste kon­vertieren erzeu­gen Sie eine Liste der gefilterten Son­derze­ichen. Dup­likate ent­fer­nen und ger­ade noch 5 verbleibende (unter­schiedliche) Son­derze­ichen liegen als PQ-Liste vor. Ver­mut­lich wer­den Sie nun diese Liste wieder zu ein­er Tabelle/Abfrage kon­vertieren, um damit später in gewohn­ter Weise zu arbeit­en. Naturgemäß bietet es sich an, die Liste oder Abfrage zu Son­derze­ichen-Unikate umzube­nen­nen.

Na ja, jet­zt haben Sie zwar die eigentlich angestrebte Liste aber alle anderen Spal­ten sind nicht mehr exis­tent. Und Sie fra­gen sich gewiss zu Recht, warum denn die Spalte Name – Kopie erstellt wor­den ist… 😕 Ich sehe hier zwei prag­ma­tis­che Möglichkeit­en, den „Gordis­chen Knoten” zu ent­flecht­en: 

1) Falls Ihre Excel-Ver­sion einiger­maßn aktuell ist, kön­nen Sie so vorge­hen: Markieren Sie im recht­en Seit­en­fen­ster bei Angewen­dete Schritte jene Zeile, wo Sie Son­derze­ichen in eine Liste kon­vertiert haben; das ist hier die Zeile Son­derze­ichen. Recht­sklick und wählen Sie die Option Vorherige extrahieren :

Recht unbekan­nt aber wirkungsvoll!

Es fol­gt ein Dia­log, wo Sie der neu zu erstel­len­den Abfrage einen Namen geben wer­den, hier beispiel­sweise Namen-Liste :

Der Name der neuen Abfrage

Anschließend sind 2 Abfra­gen exis­tent, die eben erstellte und jene, wo nur die Son­derze­ichen-Unikate enthal­ten sind. Dieser let­zt­ge­nan­nten Query wer­den Sie eventuell einen anderen Namen geben.

2) Sie löschen alle Schritte ab dem Punkt, wo Sie aus der Spalte Son­derze­ichen eine Liste erzeugt hat­ten. Dann Son­derze­ichen markieren und per Recht­sklick Als neue Abfrage hinzufü­gen. Dann haben Sie auch die entsprechende Liste und Sie kön­nen die Dup­likate ent­fer­nen und anschließend eine Abfrage daraus erstellen.

Der Hil­fe suchende Fragesteller (oder auch Sie) könnte(n) nun die ursprüngliche Abfrage als Nur Verbindung spe­ich­ern und anschließend die ver­mut­lich auch als Nur Verbindung gespe­icherte Tabelle in ein neues Arbeits­blatt oder eine definierte Posi­tion eines beliebi­gen Arbeits­blatts spe­ich­ern (siehe auch hier). Natür­lich sind bei diesen weni­gen Zeichen ein­fach Erset­zen-Vorgänge ein denkbar­er Weg, anson­sten kann Pow­er Query auch mit größeren zu erset­zen­den Daten­men­gen umge­hen und Ihnen die Arbeit erle­ichtern.

▲ nach oben …

Im allerersten Teil dieses Beitrages habe ich Ihnn aufgezeigt, dass es seit­ens der Bun­destagsver­wal­tung (derzeit) keine Möglichkeit gibt, die Namen und Frak­tio­nen der Abge­ord­neten direkt herun­terzu­laden; und ich hat­te Ihnen auch einen Link ange­boten, wo ich bere­its per copy/paste eine Excel-Mappe erstellt habe. Die anfängliche Auf­bere­itung der Dat­en ist kom­plett anders als in der Datei aus dem Wikipedia-Import. Zwei Möglichkeit­en mit ein­er anderen Möglichkeit des Extrak­ts der Son­derze­ichen find­en Sie im Beitrag Son­derze­ichen erken­nen und definieren (2) und ohne Bes­tim­mung der Son­derze­ichen aber ein­er weit­eren Möglichkeit der Auf­bere­itung der „gewöh­nungs­bedürfti­gen” (kopierten) Dat­en in Son­derze­ichen erken­nen und definieren (3).