Vor kurzem hat mich ein Leser angeschrieben, er möchte COVID-19-Zahlen aufbereiten, dabei gab es jedoch ein Problem, dass die Zahlen leider mit Sonderzeichen vermischt waren.

Hier die Originalanfrage:

Hallo Michael,

mein derzeitiges Problem ist das Auslesen der Zahl aus einer sich ändernden Zelle.

Zellinhalt:  XXX(+yy). X und y sind Ziffern. Ich benötige nur X.  Dabei kann X in diesem Fall 1 bis 3-stellig sein. Die Zahl ist immer größer null. Entweder die Klammer gibt es nicht oder der Wert ist + (Plus) und eine Zahl. Der Klammerwert wird nicht benötigt, nur der Wert XXX. Ich hoffe, ich konnte mich verständlich ausdrücken.

Gruß Gerd.

Gerd

Er war damit einverstanden, dass ich euch an seinem Problem und der Lösung teilhaben lasse, vielleicht hilft es dir ja auch.

Danke Gerd, dass du dein Problem mit uns teilst.

Ich bin überzeugt, der ein oder andere Leser hat ein ähnliches Problem und freut sich über die Lösung.

Dazu habe ich zwei Wege aufbereitet, einmal mittels Formeln in einem „normalen“ Tabellenblatt.

Und einmal mittels Power Query. Du wirst überrascht sein wie einfach das ist im Vergleich zu Formeln.

Los geht’s.

Das Problem: COVID-19-Zahlen aufbereiten von einer Webseite

Hintergrund der Frage war eine Übersicht der aktuellen COVID-19-Zahlen.

Gerd ruft diese von einer Webseite ab und lädt sie in Excel.

Das Problem ist, dass die Werte nicht als saubere Zahl aufgeführt sind, sondern als Text mit Sonderzeichen in der Form „123 (+4)“ oder „56 +7“.

Wir brauchen also eine Formel, die die Zahlen trennt und gesondert in eine Spalte ausgibt.

Über das Thema Zahlen aus einer Zelle auslesen, habe ich bereits einen sehr beliebten Artikel geschrieben, welchen du hier findest: Du willst aus einer Zelle eine Zahl auslesen? So funktionierts

Allerdings möchte ich ganz speziell auf dieses Problem eingehen und werde daher eine neue Formel aufbauen.

Dazu findest du wie immer in der Übungsdatei alle Formeln und Funktionen, die ich dir in dem Artikel zeige. Die Übungsdatei kannst du herunterladen, wenn du dem Link folgst. Übungsdatei COVID-19-Zahlen aufbereiten

Daten aus dem Web abrufen

Im ersten Schritt müssen wir die COVID-19-Zahlen irgendwie in unser Arbeitsblatt transferieren.

Dazu nutzt du die Option „Daten abrufen“, diese findest du in der Registerkarte Daten –> Gruppe Daten abrufen und transformieren –> Aus dem Web

Im folgenden Dialogfeld fügst du nun deine gewünschte URL also die Adresse der Internetseite mit den Daten ein.

In unserem Fall diese hier: https://www.kreis-goerlitz.de/city_info/webaccessibility/index.cfm?item_id=873097

Bestätige dann das Dialogfeld mit „OK“.

Du kannst im nächsten Dialogfeld wählen, wie du auf die Webseite zugreifen möchtest. Hier wählst du „Anonym“ und bestätigst mit „Verbinden“.

In dem folgenden Dialogfeld kannst du nun die Daten auswählen, die abgerufen werden sollen.

Excel erkennt dabei automatisch alle in Tabellen Form vorkommenden Daten und zeigt dir diese an.

Du kannst dich durch die Tabellen klicken bis du jene gefunden hast, die deine Daten enthält.

Abschließend bestätigst du noch mit „Laden“.

Jetzt öffnet sich der Power Query Editor.

In diesem könntest du die Daten umwandeln und ändern bevor du sie ins Arbeitsblatt lädst, dazu aber später mehr. Bestätige vorerst links oben mit „Schließen & Laden

Du hast deine Daten nun in ein neues Tabellenblatt eingefügt in Form einer echten Tabelle auf der linken Seite. Rechts davon siehst du ein Feld mit den vorhandenen Verbindungen.

Das war's auch schon.

Mehr ist nicht zu tun, um Daten aus dem Internet in Excel zu laden.

Doch warum so umständlich und nicht einfach die Daten von der Webseite kopieren?

Ganz einfach, wann immer sich auf der Seite etwas ändert, müsstest du die Daten erneut kopieren.

Du hast jedoch nun eine Verbindung hergestellt zwischen den Daten im Web und Excel.

Somit kannst du die COVID-19-Zahlen abrufen.

Wann immer du es möchtest, kannst du über den Menüpunkt Daten –> Abfragen und Verbindungen –> Alle aktualisieren deine Daten ganz einfach und schnell aktualisieren und bist so immer auf dem neuesten Stand.

COVID-19-Zahlen aufbereiten mittels Formel

Die richtigen Daten hast du somit in deiner Excel Datei verfügbar und kannst sie jederzeit mit nur einem Knopfdruck aktualisieren.

Jetzt musst du dich nur noch um die Formeln kümmern.

Doch zuerst, noch mal ein Blick auf die Anforderungen.

Es soll immer die Linke Zahl eines Textes ausgelesen werden.

Dabei kann der Text folgendermaßen aussehen:

Nur Zahl 2134
Zahl plus Zahl in Klammern 123 (+23)
Zahlen getrennt mit einem + und Leerzeichen 123 +12

 

Für alle drei Fälle brauchen wir nun eine Formel, die die Linke Zahl ausliest.

Dazu nutzen wir die Textfunktion LINKS, schließlich wollen wir ja eine Zahl auf der linken Seite des Textes auslesen.

Die Funktion besteht aus zwei Argumenten, dem Text und der Anzahl der Zeichen, die ausgelesen werden sollen.

COVID-19-Zahlen aufbereiten: die Formelbestandteile

Ich habe dazu die drei möglichen Varianten in ein separates Arbeitsblatt eingetragen, um die Formel dort zu entwickeln und dann in das entsprechende Arbeitsblatt zu kopieren.

Der Text für die Funktion LINKS ist jene Zelle, die den Text enthält.

Doch wie viele Zeichen sollen ausgelesen werden und wie schaffen wir es, dass die Anzahl automatisiert bestimmt wird?

Was diese Werte gemeinsam haben, ist ein Sonderzeichen, nämlich ein Leerzeichen nach der Zahl. Und genau danach suchen wir.

Dazu nutzt du entweder die Funktion SUCHEN oder die Funktion FINDEN.

Der Unterschied der beiden besteht nur darin, dass die Funktion FINDEN Groß- und Kleinbuchstaben unterscheidet.

Die Funktion FINDEN hat folgenden Syntax:

= FINDEN (Suchtext; Text; [Erstes_Zeichen])

COVID-19-Zahlen aufbereiten: der Formelaufbau

Und so sieht dann die Formel aus, die nach dem ersten Leerzeichen im Text sucht.

= FINDEN(“ „;  B5)

= FINDEN(“ „;  „123 (+23)“)

= 4

B5 enthält in unserem Fall den Text, der durchsucht werden soll. Als Ergebnis erhältst du eine Zahl, nämlich jene Stelle, an der das Zeichen im Text auftritt.

Und genau diese Funktion bauen wir nun in die Funktion LINKS ein.

= LINKS(B5FINDEN(“ „;  B5)  –  1)

= LINKS(B5FINDEN(“ „;  „123 (+23)“)  –  1)

= LINKS(B5 –  1)

= LINKS(„123 (+23)“;  3)

= 123

Die Funktion sucht nun nach dem ersten Leerzeichen im Text in B5 und gibt alle Werte vor dieser Stelle aus.

Würdest du das -1 am Ende nicht einfügen, dann würde die Funktion LINKS alle Werte bis zur vierten Stelle ausgeben, da ja an dieser Stelle das Leerzeichen steht. Und wir möchten nur die Zahl haben.

Diese Formel funktioniert für die Variante mit Klammer als auch ohne.

Lediglich bei einer reinen Zahl gibt die Formel einen Fehler aus.

Dazu kannst du die Formel aber in eine WENN Funktion einsetzen.

Die prüft, ob es sich um eine Zahl handelt und nur dann die Funktion LINKS ausführt, wenn die Zelle keine reine Zahl enthält.

COVID-19-Zahlen aufbereiten: die fertige Formel

So sieht nun die fertige Formel aus:

=WENNISTZAHL(B5);  B5LINKS(B5FINDEN(“ „;  B5)  –  1))

Enthält die Zelle eine reine Zahl, dann wird die Zelle zurückgegeben ansonsten wird der Linke Wert bis zum Leerzeichen ausgegeben.

Nicht vergessen, damit du auch in drei Monaten noch weißt, wie die Formel funktioniert, solltest du sie erstens übersichtlich darstellen und zweitens die Formelanalyse beherrschen.

Dazu findest du in folgenden Artikeln weitere Hinweise:

Deshalb solltest du eine Excel Formel übersichtlich darstellen

So gelingt dir jede Formel Analyse

Die rechte Zahl auslesen

Jetzt habe ich noch einen kleinen Bonus für dich.

Gerd wollte zwar nur die linke Zahl auslesen, wenn wir aber schon dabei sind, lesen wir auch gleich die rechte Zahl aus.

Dazu nutzt du am besten die Funktion TEIL, die nur einen Teil eines Textes zurückgibt.

Der Syntax der Funktion sieht so aus:

COVID-19-Zahlen aufbereiten: die Formelbestandteile

Das erste Argument ist wieder die Zelle mit dem Text.

Das zweite Argument, also das erste Zeichen ist immer das Plus vor der zweiten Zahl. Hier kannst du wieder über die Funktion FINDEN das Plus suchen.

Du musst allerdings +1 addieren, da du ja die Zahl ohne das Plus ausgeben möchtest.

Das letzte Argument bestimmt nun die Anzahl an Zeichen, die du ausgeben möchtest, diese kann jedoch variieren zwischen einer und drei Zahlen.

Wenn eine Klammer im Text vorhanden ist, willst du den Wert bis zur geschlossenen Klammer ausgeben ansonsten bis ans Ende.

Dazu musst du die Gesamtlänge des Wertes ermitteln und davon die Anzahl der Stellen bis zum Plus abziehen.

Wenn dann noch eine schließende Klammer vorhanden ist, musst du noch eine weiter Stelle abziehen.

Die Funktion ist gegeben aber nur bei Werten mit einer zweiten Zahl. Wenn es sich um eine reine Zahl handelt, würdest du eine Fehlermeldung erhalten, daher musst du das auch noch berücksichtigen.

COVID-19-Zahlen aufbereiten: der Formelaufbau

Die einzelnen Teile sehen also so aus:

Das erste Zeichen ermitteln:

Die Formel sucht nach dem Plus und addiert eine Eins, um das erste Zeichen nach dem Plus auszugeben.

= FINDEN(„+„;  B10)  +1

= FINDEN(„+„;  „123 (+23)“)  +1

= 6 +1

= 7

Anzahl der Zeichen ermitteln:

Die Formel ermittelt die gesamte Länge der Werte in der Zelle. Davon wird eine 1 abgezogen, falls eine schließende Klammer vorhanden ist und davon wieder die Stelle bis zum Plus.

= LÄNGE(B10)  –  (WENNISTZAHLFINDEN(„)„;  B10));  10))  –  FINDEN(„+„;  B10)

= LÄNGE(„123 (+23)“)  –  (WENNISTZAHLFINDEN(„)„;  „123 (+23)“));  1;  0))  –  FINDEN(„+„;  „123 (+23)“)

= 9 – (WENNISTZAHL9);  10))  –  6

= 91–  6

=2

Den Text auslesen:

Die beiden Argumente verpackst du nun in der Funktion TEIL.

COVID-19-Zahlen aufbereiten: die fertige Formel

Das wiederum verschachtelst du zusätzlich in einer WENNFEHLER Funktion, um im Fehlerfall nur den Zellwert auszugeben,

= WENNFEHLERTEIL(B10FINDEN(„+„;  B10)  +1LÄNGE(B10)  –  (  WENNISTZAHLFINDEN(„)„;  B10));  10))  –  FINDEN(„+„;  B10)  );  0)

= WENNFEHLERTEIL(„123 (+23)“;  FINDEN(„+„;  „123 (+23)“)  +1LÄNGE(„123 (+23)“)  –  (  WENNISTZAHLFINDEN(„)„;  „123 (+23)“));  10))  –  FINDEN(„+„;  „123 (+23)“)  );  0)

= WENNFEHLERTEIL(„123 (+23)“;  +1 –  (  WENNISTZAHL9);  10))  –  );  0)

= WENNFEHLERTEIL(„123 (+23)“;  79  –  1  –  6  );  0)

= WENNFEHLERTEIL(„123 (+23)“;  7 );  0)

= WENNFEHLER230)

= 23

Damit hast du auch schon die zweite Zahl erfolgreich ausgelesen.

Die Formeln kannst du nun beliebig in deinem Arbeitsblatt verwenden, du musst nur die Bezüge richtigstellen.

ACHTUNG: Das Ergebnis der Formeln ist ein Text. Excel erkennt zwar bei Berechnungen automatisch, dass es sich um eine Zahl handelt, ich empfehle dir aber trotzdem, das Ergebnis in eine Zahl umzuwandeln. Dazu kannst du die Funktion WERT verwenden, die eine als Text gespeicherte Zeichenfolge in eine Zahl umwandelt.

Alle Formeln und Funktionen, die ich dir hier gerade gezeigt habe, findest du natürlich auch in der kostenlosen Übungsdatei, die du dir hier herunterladen kannst: Übungsdatei COVID-19-Zahlen aufbereitet

Mit Hilfe einer Formel klappt das aufbereiten der Werte schon gut, doch nun zum spaßigen Teil.

COVID-19-Zahlen aufbereiten mittels Power Query

Mit der Formel die Zahl auszulesen, ist zwar nicht schwer, aber zeitaufwändig.

Da die Werte ohnehin aus dem Web abgerufen werden, könntest du auch gleich den Power Query Editor dazu nutzen, die Daten aufzubereiten.

Du wirst erstaunt sein, wie einfach das ist.

Dazu erstellst du einfach, wie vorher gezeigt, die Datenabfrage über die Internetadresse und lädst die Daten in den Power Query Editor.

Hier wirst du nun alle Änderungen vornehmen und erst dann die Daten in dein Arbeitsblatt laden.

COVID-19-Zahlen aufbereiten mittels Power Query die Arbeitsschritte

  1. Ändere den Namen deiner Tabelle

2. Markiere die Spalten, deren Werte du ändern möchtest.

3.Wähle Registerkarte Spalte hinzufügen –> Spalte aus Beispielen –> Aus Auswahl

4. Am rechten Rand erscheint eine neue Spalte, diese kannst du durch einen Doppelklick auf die Kopfzeile umbenennen.

5. Und jetzt kommt die wahre Erleichterung. Du willst ja nur die erste Zahl, ohne die Klammern Werte ausgeben. Also markierst du die erste Zelle der neuen Spalte und gibst nur den Wert ohne den Wert in Klammern ein und bestätigst mit Enter. Ähnlich der Blitzvorschau in einem Arbeitsblatt erkennt Excel nun Muster in den Daten und führt diese Muster fort. Das war auch schon der ganze Aufwand.

6. Bestätige noch mit „Ok“ und die neue Spalte wurde hinzugefügt.

7. Diesen Vorgang kannst du nun beliebig wiederholen bis deine Daten so aussehen wie du möchtest. Zum Beispiel das Auslesen der Klammern Werte.

Wie lange hat das jetzt gedauert, zwei Minuten?  Das nenne ich Zeitersparnis.

COVID-19-Zahlen aufbereiten mittels Power Query der Abschluss

Das Schöne daran ist, du kannst die Ausgangsspalte ganz einfach über Registerkarte Home –> Spalte entfernen löschen. Die neuen Spalten bleiben davon unverändert.

Mit „Schließen und Laden“ lädst du die Daten in dein Arbeitsblatt.

Und siehe da, die fertig aufbereiteten Spalten sind ganz rechts vorhanden.

Und der große Vorteil ist, wenn du die Tabelle aktualisierst, führt Excel die gerade getätigten Arbeitsschritte erneut aus und präsentiert dir die fertigen Daten. Ohne, dass du die Spalten erneut aufbereiten musst.

TIPP: Solltest du Änderungen oder Erweiterungen an der in Power Query erstellten Tabelle nachträglich vornehmen wollen, markierst du einfach eine Zelle innerhalb der Tabelle und wählst Registerkarte Daten –> Abfragen und Verbindungen –> Abfragen und Verbindungen

Dann erscheint auf der rechten Seite eine Übersicht aller Tabellen.

Mittels Doppelklick kannst du die Tabelle erneut in den Editor laden.

Fazit

Danke noch mal an Gerd für die Möglichkeit, dass wir alle etwas Neues lernen durften.

An diesem Beispiel zeigt sich sehr gut die Vielfältigkeit von Excel und wie Probleme gelöst werden können.

Vielleicht kanntest du die Power Query Funktion noch gar nicht.

Vielleicht hast du schon von ihr gehört und hast dich bisher nicht getraut sie einzusetzen.

Ich hoffe, ich konnte dir die Scheu etwas nehmen.

Was ich dir noch mitgeben möchte ist, dich auf dein Ziel oder die Problemlösung zu konzentrieren.

Du hast auf diesem Blog schon viele komplexe Formeln kennengelernt, die alle Eventualitäten abdecken.

Es wäre auch hier mögliche gewesen, noch mit einzubeziehen, dass auch Werte mit einem negativen Vorzeichen ausgelesen werden können oder Werte ohne Leerzeichen.

Das war aber gar nicht nötig, weil die Werte in einer ganz bestimmten Form vorliegen.

Somit erreichst du die größte Zeitersparnis indem du dich auf die Aufgabenstellung konzentrierst und mit der Lösung nicht übers Ziel hinausschießt.

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor Excel der smarte Excel Problem Löser

PS.: Wenn der Artikel hilfreich für dich war, dann teile ihn doch gerne mit anderen, denen er auch helfen könnte.

Doktor Excel

Doktor Excel

Heißt eigentlich Michael und arbeitet seit über 10 Jahren privat und beruflich mit Excel. Er hat dabei viele Zeitsparende Arbeitsweisen kennen gelernt und teilweise selbst entwickelt.

Hier findest du seine Geschichte: Wer ist dieser Doktor Excel und was hat ein Arzt mit Excel zu tun?

Du kannst ihm auch jederzeit über die Kontakseite eine Nachricht zukommen lassen.