Es fällt mir ein: in der Liste befinden sich mehr als 140.000 Datensätze – einige enthalten die Information „< 1919“. Da nur die ersten 1.000 Zeilen ausgewertet werden, ist dieser Fehler nicht in Power Query sichtbar.
Das heißt: der Datentyp der Spalte muss korrekt in Text verwandelt werden
Dann funktioniert die Sortierung:
Frage: Wer macht denn so etwas? – Zahlen und Texte in einer Spalte mischen? Seltsames Datenbanksystem, das hier verwendet wurde …
Bekomme beim Aktualisieren der Abfragen (255 angefügte Abfragen)
in Power Query immer den Hinweis ‚unerwarteter Fehler‘ zu lesen.
Die 255 Abfragen habe ich mir vom Internet nur als Verbindung heruntergeladen. Nach mehreren Versuchen wird die Abfrage doch aktualisiert.
Hallo Peter,
verstehe ich dich richtig: du kommst über den Fehler drüber, aber er nervt dich?
Du hast mehr als 220 Abfragen, die ALLE auf diese Internetseite zugreifen. Und alle liegen in einer Datei!
Frage: Gibt es keine Datenbank, die man direkt anzapfen kann? Ich habe mir die Seite angesehen – die Ergebnisse stehen wirklich auf jeder einzelnen Seite. Ich vermute, dass sie im Hintergrund per Programmierung (PHP?) erzeugt werden.
Ich habe mehrmals über das Thema «langsames Power Query» referiert – ich denke, Power Query schafft es nicht so schnell ALLE Abfragen zu aktualisieren und «verheddert» sich.
Ich fürchte die viele, viele Mühe, die du dir gemacht hast, führt zu dieser Fehlermeldung:
Benötigst du alle Abfragen?
Ich habe übrigens festgestellt, dass zu viele Abfragen – vor allem Abfrage auf Abfrage auf Abfrage auf … Power Query in die Knie zwingen. Lieber flach halten!
Hilft das?
Hallo Renè,
vielen, vielen Dank.
Ja, deine Information hat mir geholfen.
Bin zufrieden, dass der Fehler nicht bei mir liegt.
Dass Power Query viel Zeit benötigt um alle Abfragen zu aktualisiern
ist klar.
Nicht bedacht habe ich, dass es sich dabei ‚verheddern‘ könnte.
Alle Abfragen benötige ich.
Es gibt bedauerlicherweise viele Paare, die sich an die Verpflichtung
das Ergebnis innerhalb von drei Tagen zu melden, nicht halten.
Der Verband bönigt die Infdormationen der Platzierungen für die
Entscheidung bei Entsendungen zu Turnieren.
Anderer Ansatz.
Eine Tabelle für jedes Paar hatte ich mir schon überlegt, aber verworfen
da ich dachte, dass das mehr Zeit benötigt.
Erstelle nun Abfragen für jedes Paar einzeln.
Frage beim Verband nach, ob sie eine Abfragemöglichkeit bei WDSF
bekommen, bei der die gesamten Ergebnisse abzufragen sind.
Davon sprechen sie bei jedem Meeting seit Jahren. :-(((
Auf einem Tabellenblatt befindet sich eine Liste. Sie soll an anderer Stelle mit PowerQuery wieder angezeigt – das heißt: per Power Query verknüpft werden. Das heißt: die Liste liegt nicht als intelligente Tabelle vor und soll auch nicht in eine (intelligente) Tabelle konvertiert werden.
Also greife ich mit Power Query auf die gleiche Datei zu und hole die Daten, die transformiert werden:
Ich teste und ändere eine Information. Das Aktualisieren funktioniert allerdings nicht!?!
Klar! Ich muss die Datei vor der Aktualisierung speichern!
Erstaunlich: Manchmal – nicht immer – stehen die „englischen“ Länder zwei Mal untereinander im Gebietsschema bei Power Query. Nach Zypern beginnt die Liste neu mit American Samoa. Das wäre nicht so schlimm – jedoch: Englisch (USA) steht nur einmal in der Liste – im oberen Teil. Ich weiß nicht, wann das passiert und wie man das wegbekommt …
wenn du bei deinen Kunden eine Excel-Datei im Einsatz hast, die dann mit PowerQuery ausgestattet ist, wie lege ich die Schutzoptionen im Dialog fest, dass bei geschütztem Blatt die Option
Ich habe in einer Datei 46 Abfragen programmiert. Wenn ich die Abfragen manuell einzeln aktualisiere funktioniert das einwandfrei.
Wenn ich aber alle Daten aktualisieren lasse, dann stürzt mein Excel aufgrund zu wenig Ram ab.
Gibt es eventuell Einstellungen die ich ändern muss um Ram zu sparen?
Ich nutze aktuell eine 32 Bit Version von Excel. Laut unserer IT könnte ich eine 64 Bit Version bekommen. Liegt es eventuell daran?
Problem ist nur, dass später andere Personen die Datei nutzen sollen die unter Umständen keine 64 Bit Version nutzen.
Vielen Dank
####
Ich schaue es mir an: in verschiedenen Ordnern liegen Excelmappen:
Davon wird jeweils die neuste Datei verwendet, was man mit Sortieren und Zeilen beibehalten leicht erreichen kann.
Aus dieser Datei werden bestimmte Informationen (Datum, Status) ausgelesen:
Für eine Datei gibt es zwei (!) Abfragen, deren Tabellen nebeneinander stehen. Also jeweils: eine Zeile Überschrift und eine Zeile Inhalt:
Und tatsächlich: Bei Aktualisierung der Abfragen stürzt Excel auf einer 32-Bit-Maschine ab:
Die Lösung: Wir versuchen es. Wir erstellen EINE Abfrage, welche auf den übergeordneten Ordner zugreift, dort die Dateien der untergeordneten Ordner ausliest und mit geschickten Transformationen erhalten wir das Ergebnis in einer Tabelle. Diese lässt sich problemlos aktualisieren.
Was macht denn der? Plötzlich sind ganz viele Tabellen nach Schließen von Power Query in der Excelmappe.
Die Antwort: Er klickt im Power Query-Editor auf Datei / Schließen (wie auch in den anderen Office-Programmen).
Und so verwendet Power Query die Grundeinstellung, die man über die Abfrageoptionen ändern kann:
Ich erkläre ihm den Unterschied zwischen „Schließen und Laden“ und „Schließen und Laden in“ und empfehle ihm IMMER die letzte der beiden Varianten zu verwenden.
Manchmal sind einfache Fragen verblüffenderweise gar nicht einfach..
Ich zeige in der Power Query-Schulung, wie man in Excel einer Zelle einen Namen geben kann und diesen als Parameter für die Filterung einer Liste verwenden kann:
Dann kommt die Frage, wie man alle Daten sehen kann, wenn das FIlterkriterium leer ist:
Da Power Query kein If-Statement kennt, um Befehle bedingt auszuführen, also IF nicht in der Abfolge der M-Befehle kennt, sondern nur als Funktion, muss man sich mit einem Trick behelfen. Beispielsweise mit einer Funktion;
=if Ortsfilter = null then
"x"
else
if [Ort] = Ortsfilter then
[Ort]
else
null
Ach wie doof! Die Funktion OR (ebenso wie AND) kann in Power Pivot – anders als in Excel – nur zwei Argumente aufnehmen! Also genau so wie die Funktion CONCATENATE
Eine Abfrage wird mit Power Query auf Basis einer anderen Datei erstellt. Man kann in den Optionen einstellen, dass sie beim Öffnen der Datei aktualisiert wird.
Auf Basis dieser Tabelle wird eine Pivottabelle erstellt. Auch dort wird festgelegt, dass sie bei Öffnen aktualisiert wird:
Allerdings ist die Reihenfolge wichtig: zuerst muss die Abfrage aktualisiert werden und anschließend die Pivottabelle. Das ist so nicht gegeben.
Die Lösung: man muss mit dem Datenmodell arbeiten. Verwendet die Pivottabelle das Datenmodell, wird korrekt aktualisiert.
ich arbeite gerade an einer Excel-Datei (zum Üben).
In der Spalte A ab A2 bis A31 habe ich eine Liste hinterlegt, die sich auf die Nachnamen im Arbeitsblatt Belegung bezieht.
Nun würde ich gern die Matrix im Arbeitsblatt Belegung in eine formatierte Tabelle umwandeln. (Das ist kein Problem.) Nachdem ich dies getan, möchte ich gern folgendes erreichen:
wenn ich in die formatierte Tabelle einen neuen Namen einfüge, alle Nachnamen nochmals alphabetisch sortiere, soll der neu eingetragene Name in der Liste (Spalte A ab A2 bis A31) auftauchen.
Ist das möglich? Wenn ja, wie?
Vielen Dank im Voraus für Ihre Hilfe.
Mit freundlichen Grüßen
####
Hallo Herr F.,
der Trick ist, dass Sie dem Bereich der intelligenten Tabelle einen Namen geben müssen. Dann können Sie den Namen in der Dropdownliste verwenden.
Wird die Liste erweitert (oder verringert), passt sich die Liste an.
Sehr seltsam! In einer (intelligenten) Tabelle (tbl_Kunden1) wurde zeilenweise gerechnet. Der Betrag aus der Spalte Rechnungsbetrag wurde um 100 erhöht.
Kopiert man nun diese Formel in eine andere (intelligente) Tabelle, in der sich auch eine Spalte Rechnungsbetrag befindet, wird leider der Bezug auf die erste Tabelle mitgenommen:
Ob ich mal kurz Zeit hätte. Eine Excel-Frage. Ganz dringend. Und sehr kompliziert.
Ich hatte Zeit und schaute mir das Problem an.
In einer intelligenten Tabelle befinden sich in einer Spalte lange Texte. Leider kann man sie nicht über die Zellen daneben zentrieren:
Stimmt – das ist in der intelligenten Tabelle verboten. Und das ist auch gut so.
Aber das ist so hässlich und nimmt so viel Platz weg. Und man kann nicht gut lesen.
Meine Antwort: Markieren Sie die Zellen, wählen Zellen formatieren / Ausrichtung und dort „Über Auswahl zentrieren“
Leider bleibt der Text zentriert (man kann ihn nicht linksbündig formatieren); aber damit konnte sie leben. Und war begeistert.
Und war noch begeisterter, als ich ihr den Tipp gab: mit [Strg] + [1] wechseln Sie in den Dialog „Zellen formatieren“ und mit [Strg] + [Y] oder [F4] wiederholen sie den letzten Schritt. So kann man die Tabelle schnell formatieren:
Der Auftrag hörte sich einfach an: Der Kunde wollte ein Add-In, welches alle Dateien aus allen Unterordnern vom firmeneigenen Sharepoint herunterlädt und in bestimmten Zellen Werte einfügt.
Der Knackpunkt war: Sharepoint!
Ich habe lange getüftelt, wie ich „auf den Sharepoint komme“, wie ich die Ordner und Unterordner und die dort befindlichen Dateien auslesen könne. Und herunterladen und bearbeiten.
Irgendwann kam mir die Idee: nicht mit VBA und DIR oder den FileScription-Objekt auf den Ordner losgehen, sondern mit Power Query! Damit kann man leicht alle Dateien aller Unterordner auslesen und auflisten. Der Befehl
SharePoint.Files
macht es möglich. Dieses Power Query-Skript kann leicht mit VBA aufgerufen werden (der Makrorekorder zeigt, wie das funktioniert:
Eine Teilnehmerin in der Excelschulung fragte mich, warum auf einem Rechner folgendes funktioniert, auf einem anderen nicht:
Normalerweise bewirkt ein Klick auf eine Zelle in der Pivottabelle, dass die Formel
=PIVOTDATENZUORDNEN
erzeugt wird:
Bei ihr jedoch nicht:
Auch das Eintippen der Formel hilft nicht – Intellisense versagt:
Des Rätsels Lösung war schnell gefunden: sie hatte in den Optionen / Formeln die Option „GetPivotData-Funktionen für PivotTable-Bezüge verwenden“ ausgeschaltet.
Kennst ihr DPQ-Dateien? Das sind Textdateien, in denen ein Query auf eine Datenbank gespeichert sind. Diese Data-Query kann man in Excel einbinden.
Soweit so gut.
Nun wollte ich so eine Datei in Power BI einbinden.
„Einen direkten Konnektor über diese Datenverbindung gibt es zur Zeit in Power BI Desktop nicht.“ schreibt Frank Arendt-Theilen. Hans-Peter Pfister hat es bestätigt. Schade.
Ach wie doof! Wenn man in PowerBi in das Visual Tabelle oder Matrix Werte (oder besser: ein Measure) in die Werte zieht, kann man mit einem Klick auf den Spaltenkopf danach sortieren:
Verwendet man jedoch eine Kategorie in den Spalten, kann man damit nicht sortieren – auch nicht mit gedrückter [Strg]-Taste:
Mit einem Klick wird die Spalte selektiert (und damit die anderen Visuals gefiltert).
Peter schickt mir eine Mail und fragt mich, warum die Sortierung in Excel nicht funktioniere.
Natürlich funktioniert sie, lautet meine Antwort. Er solle mir die Datei zusenden; ich würde sie mir ansehen. Ich vermutete eine leere Spalte oder Zeile zwischen den Daten.
Er erklärt mir:
Sortiert man die Liste nach dem Namen ist alles prima:
Sortiert man sie nach dem Ort, funktioniert es auch:
Jedoch bei der Sortierung nach der ID stimmt die Zuordnung zum Länderkennzeichen und dem Land nicht mehr:
Ich sehe mir die Liste genau an – die Lösung ist bald gefunden: Auf halber Strecke wurde ein Teil der Liste nach unten verschoben, das heißt: der linke Teil befindet sich eine Zeile tiefer als der rechte. So kann es gehen:
Ich öffne eine uralte Datei, die noch im XLS-Format vorliegt:
Ich speichere sie als XLSX.
Und erstelle eine Pivottabelle:
Aber irgendwie sieht die komisch aus.
Auch als ich weiterarbeite, „fühlt“ sich das Ganze sehr seltsam an:
Ups – und meine Diagramme – wo sind meine Diagramme?
Dann dämmert es mir: Nach dem Konvertieren ins XLSX-Format muss ich die Datei schließen und erneut öffnen – sonst verbleibt sie im Kompatibilitätsmodus:
Werden in PowerQuery Spalten mit einem Verkettungsoperator „&“ zusammengefügt und befindet sich in einer der Zellen der Wert null, dann ergibt Inhalt & null -> null:
Nur wenn alle Zellen mit Text gefüllt waren, wird das Ergebnis angezeigt.
Anders dagegen der Assistent „Spalten zusammenführen“.
Amüsant, was manchen Teilnehmerinnen und Teilnehmern in Schulungen auffällt. Dinge, die ich noch nie beachtet habe oder denen ich keine Beachtung beigemessen habe.
Beispielsweise ist mir noch nie aufgefallen, dass Zahlen in PowerQuery kursiv stehen, während Texte immer „aufrecht“, also nicht kursiv im Editor dargestellt werden:
Amüsant. In der Excelschulung fragt mich ein Teilnehmer, warum manchmal der Filter bei der Auswahl „Textfilter“ / „Ist gleich“ die letzte Filterung anzeigt und manchmal nicht:
Oder so:
Nun – das hängt damit zusammen, wie man filtert. Wählt man den Befehl „Textfilter“ / „Ist gleich“ aus
und trägt dort mehrere Varianten ein, beispielsweise M?nchen oder M??nchen:
wird dies als Auswahl unter „Benutzerdefinierter Filter“ angezeigt. Der grüne Haken gibt Auskunft darüber.
Wählt man nun die Option „Ist gleich“, wird der vorher eingegeben Text gelöscht.
Allerdings: wählt man als Filterkriterium München oder Muenchen, so wird gar nichts angezeigt:
Ich möchte mit Power BI auf eine Access-Datenbank zugreifen. Leider erhalte ich folgende Meldung:
Beim Herstellen einer Verbindung ist ein Fehler aufgetreten.
Schade!
Auf der genannten Seite von Microsoft lese ich:
Ich soll mein Office deinstallieren und in der gleichen Version wie Power BI zu installieren, finde ich nun keine wertvolle Hilfe. Ich scrolle nach unten und lese dort:
Ah, okay – zwei Versionen von Access.
Gut – ich warte bis nächste Woche und teste alles auf meiner 64-Bit-Maschine.
Vor der Excelschulung bat mich der Teilnehmer einen Blick auf seine Exceldatei zu werfen. Er könne seit einer Weile keinen Datenschnitt mehr einfügen. Das Symbol sei inaktiv.
„Zu viele Formeln?“ schoss es mir durch den Kopf? „Zu viele bedingte Formatierungen?“ „Oder andere Formate?“
Als ich die Datei erhielt, fiel mein erster Blick auf die Dateigröße – sie war nicht einmal ein MByte groß. Also wohl kein „zu viel an“.
Die Pivottabellen habe ich schnell in der Datei gefunden – und wirklich: warum kann man keinen Datenschnitt einfügen?
An den „Filterverbindungen“ kann es nicht liegen – dort wird festgelegt, welche Pivottabelle welchen Datenschnitt verwendet.
Eine Weile habe ich gegrübelt.
Des Rätsels Lösung habe ich auf der Registerkarte „Einfügen“ gefunden:
DORT kann man auch den Datenschnitt aktivieren. Und natürlich Bilder, SmartArts, Diagramme einfügen. Eben nicht – all diese Symbole sind inaktiv. DORT war natürlich auch der Datenschnitt inaktiv.
Mir dämmerte es: mit der Tastenkombination [Strg] + [6] kann man Bilder (und Diagramme) ausblenden. Eben: und auch Datenschnitte. Diese Option findet man auch in den Exceloptionen in der Kategorie „Erweitert“ / „Optionen für diese Arbeitsmappe anzeigen“:
Werden nun SO oder mit der Tastenkombination [Strg] + [6] die Objekte wieder angezeigt, erscheint auch der Datenschnitt. Und sogar ein Diagramm!
Der Teilnehmer war sehr froh über diese Information.
Dummerweise hat ein Teilnehmer eine intelligente Tabelle über das gesamte Tabellenblatt erstellt.
Ich möchte den Bereich auf die benötigte Größe verkleinern. Hierfür tut das Symbol „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ gute Dienste. Schneller als das grüne Eck nach oben zu ziehen ist sicherlich das Eintragen des Bereichs:
Allerdings: wir wundern uns, dass das Ergebnis nicht das gewünschte ist – die intelligente Tabelle ist verschwunden. Genauer: sie „hängt“ irgendwo oben:
Des Rätsels Lösung: der Bildschirm war so verschoben, dass die erste Ziffer der Zeilennummer nicht sichtbar war. Also auf ein Neues – beim zweiten Mal klappt es auch!
In ein Tabellenblatt wird eine Überschrift eingetragen. Sie wird formatiert:
Sie wird in eine (leere) intelligente Tabelle (mit einer Zeile) umgewandelt:
Fügt man nun eine Zeile ein, wird die Farbe der Überschrift übernommen:
Man darf also nicht, wenn man in einer intelligenten Tabelle die Überschrift per Hand formatiert (beispielsweise, wenn man unterschiedliche Farben für verschiedene Bereiche verwenden möchte) vor der ersten Datenzeile eine Zeile einfügen.
Für die leere Tabelle heißt das: man muss sie so anlegen, dass mehrere leere Zeilen vorhanden sind.
Katharina hat auf eine Differenz zwischen scheinbar gleichen, aber auf unterschiedlichen Rechner installierte Excel 2016-Version aufmerksam gemacht. Power Query hat bei der Abfrage auf einen Ordner nicht nachvollziehbare Fehlermeldungen:
Das Dialogfeld beim Zugriff auf Ordner wird gar nicht angezeigt.
Mark hat uns für eine mögliche Lösung den folgenden Link genannt:
Eigentlich dürfte das doch nicht so schwierig sein, denke ich. Weit gefehlt!
Die Aufgabe: In einer Liste sollen alle Zellen mit einem bestimmten Begriff, beispielsweise „Fehler“ ans Ende der Liste sortiert werden:
Nun – im benutzerdefinierten Sortieren gibt es die Möglichkeit eine benutzerdefinierte Liste zum Sortieren zu definieren. Diese Liste kann auch nur aus einem Wort bestehen:
Für diese Liste gibt es nun zwei Varianten: an den Anfang oder ans Ende:
Wählt man die untere Option – also: ans Ende – dann stehen die anderen Werte auch in umgekehrter Sortierreihenfolge in der Liste:
DAS ist allerdings nicht gewünscht – die oberen Werte sollen in alphabetisch aufsteigender Reihenfolge stehen und DANN der letzte Text „Fehler“. Und: zwei Mal sortieren verbietet Excel:
Also sortieren wir den gewünschten Text „Fehler“ doch nach oben … DAS funktioniert.
Trägt man in einer List in der Spalte der Ort beim Autofilter in das Suchen-Feld den Text Ulm ein, werden auch Orte wie Kulmbach oder Neckarsulm gefunden:
Abhilfe schafft ein Anführungszeichen am Anfang und am Ende: „Ulm“
Allerdings: beim Suchen wird „Ulm“ nicht gefunden:
Hier muss man die Option „Gesamten Zellinhalt“ bemühen.
Max fragt mich, ob ich ihm helfe könne, per VBA einen PowerBI-Bericht anzuzeigen.
Er möchte in Excel über eine Schaltfläche ein Makro aufrufen, das einen Bericht öffnet, so dass die Anwenderinnen und Anwender den Bericht sehen können:
Er hat den Befehl „Shell“ im Internet gefunden. Richtig: Mit Shell rufe ich Programme auf, die ich nicht direkt über einen eingebundenen Verweis starten kann. Wir machen uns auf die Suche. Wie denn der Pfad auf seinem SharePoint lautet, will ich wissen. Den finden wir heraus. Er hat etwa die Form:
Microsoft Excel können keine neuen Zellen einfügen, weil in dem Ende des Arbeits Blatts Push-Zellen nicht leer sind. Diese nicht-leeren Zellen werden möglich erweise leer angezeigt, aber leere Werte, einige Formatierungen oder Formeln. Löschen Sie genügend Zeilen oder Spalten, um Speicher Platz zu schaffen, was Sie einfügen möchten, und versuchen Sie es dann erneut.
Zugegeben: diese Fehlermeldung verstehe ich nicht. Ich schaue nach: Die Zellen sind leer:
Dann entdecke ich die Ursache der Fehlermeldung und den Grund, warum Excel keine Spalten einfügen kann: in der Firma wurde ein Filter von der ersten bis zur letzten Spalte eingeschaltet!
ich möchte (mit [Strg] + [+]) eine weitere Zeilen einer intelligenten Tabelle einfügen. Und erhalte folgende Fehlermeldung:
Hierdurch wird ein gefilterter Bereich in Ihrem Arbeitsblatt geändert. Um diesen Vorgang abzuschließen, entfernen Sie bitte die AutoFilter.
Seltsam, denke ich: in meiner Tabelle habe ich noch nichts gefiltert. Auch das Entfernen des Autofilters bringt kein Licht ins Dunkel. Aber dann entdecke ich UNTER der Tabelle eine weitere Liste mit einer Filterung:
DAS ist des Rätsels Lösung. Obwohl noch Platz für eine weitere Zeile wäre, kann Excel, aufgrund des gesetzten Filters oben keine weitere Zeile einfügen.
Da Leute dies unterschiedlich aussprechen, wiederhole ich den Namen „Pivot“ in verschiedenen Aussprachevarianten. Auch in der „französischen“ – ohne „t“.
Eine Teilnehmerin aus der Slowakei schaut irritiert auf. Ihr Nachbar kommentiert: „Du denkst jetzt an Bier, oder?“
Nach der Excelschulung zeigt mir ein Teilnehmer eine Pivottabelle. Er möchte dort eine Berechnung durchführen. Eigentlich recht einfach: einen Wert durch 1.000 teilen. Kein Problem, oder?
Allerdings erhalten wir eine Fehlermeldung:
Es dauert eine Weile, bis ich dahinter komme, dass ich mir die Liste einmal anschauen sollte. Und tatsächlich: dort finde ich eine Spaltenüberschrift
in der sich nicht nur Leerzeichen und Sonderzeichen ([ ]) befinden, sondern auch noch Zeilenumbrüche ([Alt] + [Enter]).
Also noch ein Versuch: Da ich die unterste Zeile nicht sehe, drücke ich so lange die Taste [Pfeil unten] und [Pfeil rechts], bis ich glaube, das Ende erreicht zu haben. Dann füge ich die Rechnung (hier: / 1000) ein.
Hallo Herr Martin, können Sie mir als Excel-Virtuose beim Umgang mit intelligenten Tabellen helfen? Es handelt sich um folgende Sache:
gegeben: ein Bereich mit insgesamt 14 Spalten und beliebig vielen Zeilen
Ziel: eine intelligente Tabelle bei der Spalte 1, Spalte 2-5, Spalte 6-10; Spalte 11-14 jeweils ein eigenes coloriertes Stripset besitzen (siehe Abbildung = 1. Versuch)
meine Versuche + aufgetretene Problematik:
Versuch: mehrere intelligente Tabellen mit dem jeweiligen Stripset (für Zeilen und Spalten) aneinanderfügen –> beim Hinzufügen einer neuen Zeile in Spalte 1 (und ff. neu alphabetisch sortieren lassen), sortieren sich die Zeilen der anderen Tabellen nicht automatisch mit –> Chaos Idee: Verknüpfung der aneinanderliegenden Tabellen, damit jede auf die Veränderung bspw. in der ersten Spalte (bzw. Tabelle) reagiert
Versuch: eine 14 Spalten umfassende intelligente Tabelle mit benutzerdefinierter Tabellenformatierung –> da die Tabellenabschnitte aus einer unterschiedlichen Anzahl aus Spalten bestehen, lässt sich nicht mal ansatzweise mein Ziel über das Formatieren des Stripsets „erste Spalte“ / „zweite Spalte“ verfolgen Idee: Hinzufügen der Auswahlfelder Stripset „dritte Spalte“, „vierte Spalte“, usw.
Versuch: eine 14 Spalten umfassende intelligente Tabelle mit „überdeckender“ händisch eingestellten Formatierung –> händisch eingestellte Formatierung ist nicht intelligent, sodass bei Filterfunktion die farbliche Unterscheidung zwischen den Zeilen nicht mehr gegeben war (Bsp. Zeile 1 gelb, Zeile 2 grün, Zeile 3 gelb; Filter Z.2 ; Zeile 1 gelb, Zeile 3 gelb)
Idee: Anwenden der bedingten Formatierung. Hierbei habe ich schnell festgestellt, dass mir der Ansatz gänzlich fehlt –> an welche „Variable“ od. „Konstante“ binde ich die Formatierung, damit ich einfach eine normale intelligente Tabelle erhalte, bei der sich die Hintergrundfarben unterschiedlich vieler Spalten abschnittsweise unterscheiden, die sich zusätzlich mitverändern, sollte man filtern oder neue Zeilen an unterschiedlicher Stelle hinzufügen
Ich hoffe, ich konnte es einigermaßen verständlich machen, woran es bei mir scheitert. Weiterhin hoffe ich, dass Sie Zeit und Lust haben, sich mit dieser Problematik zu befassen. Mit freundlichen Grüßen Marcel
PS: Ihr Forum „Excel nervt …“ ist mega unterhaltsam und hilfreich und befasst sich verständlich mit atypischen Fragen im Umgang mit dem Programm. Einfach genial. Hat mir sehr gefallen!
#####
Hallo Herr Gröschel,
1.) ich würde nicht mehrere intelligente Tabellen verwenden. Das widerspricht dem Gedanken der Tabellen.
2.) Ich habe nachgeschaut: es geht nicht mit dem Stripset. DAS ist recht einfach aufgebaut und erlaubt nicht so viele Varianten, wie Sie es gerne hätten.
3.) Warum nicht bedingte Formatierung? Setze ich auch gerne ein. Beispielsweise um eine Zeile farblich zu hinterlegen.
Die Formel (bspw.):
=UND(SPALTE()>=11;SPALTE()<=14)
Hilft das?
Liebe Grüße
Rene Martin
####
Guten Abend Herr Martin,
vielen Dank, dass Sie so schnell geantwortet haben! Ihre Formel hat mir grundsätzlich weitergeholfen. Damit konnte ich das Problem der verschiedenen Spaltenfarben in meiner Tabelle lösen. Es war simpel und genial.
Dennoch blieb das Problem der farblichen Abgrenzung zwei aufeinanderfolgender Zeilen bestehen. Zuerst probierte ich es mit:
=REST(ZEILE();2)
Hat soweit gut funktioniert, bis ich dann die Filterfunktion der intelligenten Tabelle verwendet habe. Im Ergebnis unterschied das Programm nicht zwischen eingeblendeten und ausgeblendeten Zeilen.
Basierend darauf kam ich durch weitere Recherche zur Ziellösung:
=REST(TEILERGEBNIS(3;$B$5:$B5);2)=0
(wobei die erste Spalte meiner Tabelle in Blattspalte B beginnt und ab Zeile 5 stets einen Wert beinhaltet)
Hierbei ist zu beachten, dass der gewünschte Effekt bzw. konkret die gewünschte farbliche Formatierung in der gesamten Tabelle nur dann funktioniert, wenn in Spalte B, in jeder Zelle der Tabelle auch ein beliebiger Wert steht. In meinem Fall befinden sich hier jeweils die Bezeichnungen.
Das wiederum bedeutet, dass beim Hinzufügen einer neuen Zeile vorerst nicht der gewünschte Effekt eintritt, sondern erst nach befüllen der neuen Zelle in Spalte B. Es ist unterm Strich noch nicht perfekt, aber es lässt sich damit arbeiten.
Ich unterrichte PowerQuery in einer internationalen Firma. Einige haben die englische Oberfläche eingestellt und damit auch das Dezimaltrennzeichen „.“ und die Datumsschreibweise „MM/TT/JJJJ“, einige Teilnehmer und Teilnehmerinnen „sprechen“ deutschen, also das Komma als Dezimaltrennzeichen und Datum in der Form „TT.MM.JJJJ“.
Beim Festlegen des Datentyps müssen nun einige auf „Dezimalzahl“ klicken, andere das Gebietsschema festlegen. Da ich verschiedene Übungsbeispiele mitgebracht habe, muss man entweder die eine oder andere Variante wählen:
Ich überlege: Wenn nun eine solche Datei mit einem PowerQuery-Zugriff ausgetauscht wird, wäre es doch sinnvoll IMMER das Gebietsschema der Quelle festzulegen, da es ansonsten zu Fehlern kommen kann:
Warum sie eine intelligente Tabelle nicht umbenennen dürfe, fragt eine Teilnehmerin in der Excelschulung.
Seltsam, denke ich: ein Tabellenblatt, eine intelligente Tabelle – Excel behauptet, dass dieser Name bereits vorhanden sei.
Ich werfe einen Blick in den Namensmanager:
Dort finde ich eine intelligente Tabelle und einen Namen. Was hat die Teilnehmerin gemacht?
Schritt 1: Wandle den Bereich in eine Tabelle um. Sie heißt nun Tabelle1:
Markiere die Liste und vergebe ihr einen Namen. Also nicht der Tabelle, sondern dem Bereich (hier: A1:F25)
Und so sieht man den Namen der Tabelle, aber nicht, dass ein anderer Bereich bereits mit dem Namen belegt ist, den man selbst gerne vergeben möchte … Perfide!
VBA-Schulung. Ein Teilnehmer wollte JSON-Datein in Excel haben. Kein Problem, sagte ich und zeigte ihm PowerQuery.
Allerdings – nach einigen fragenden Blicken – stellten wir fest, dass in seinem Excel 2016 (anders als meines in Microsoft 365) noch kein JSON-Zugriff vorhanden ist.- Ärgerlich!
Mit PowerQuery wurde eine Abfrage erstellt. Diese enthält eine Liste. Wie kann man eine Dropdownliste mit einer Datenüberprüfung auf diese Liste erstellen, ohne dass die Daten nach Excel geladen werden?
Etwas irritiert bin ich schon. In einer PowerBI-Schulung erzählt mir ein Teilnehmer, dass er die Daten gerne in Excel hätte. Und dass er eigentlich gerne mit PowerQuery in Excel darauf zugreifen würde. Und nicht mit PowerBI. Aber die IT hätte ihm gesagt, dass es nicht gut ist, wenn mit PowerQuery so viele Abfragen auf die Datenbank abgesetzt werden. Besser wäre es, mit PowerBI auf die Datenbank zuzugreifen.
Ich bin mir nicht sicher, ob die Damen und Herren von der IT nicht wissen, dass PowerBI auch PowerQuery verwendet.
Schöne Frage in der Excelschulung: Wir haben auf SharePoint mehrere Excelmappen, in denen der Autofilter eingeschaltet ist. Kolleginnen und Kollegen laden die Dateien runter, filtern, vergessen den Filter auszuschalten und – nun ist für den nächsten Kollegen der „alte“ Filter aktiviert. Ob man das einstellen könne, dass beim Schließen der Datei zwar der Autofilter gesetzt bleibt, jedoch nicht eine mögliche Filterung.
Leider gibt es dafür keine Einstellung, lautete meine Antwort – das müsse man programmieren. Ein langes Gesicht war die Folge.
Ich erstelle ein Add-In für eine Firma. Es soll eine große Liste per Knopfdruck in Einzelteile zerlegen und diese an bestimmten Stellen auf der Festplatte speichern.
Dazu benötige ich eine eindeutige Liste der Kategorien:
Ich überlege: da die Firma Excel 2016 einsetzt, hat sie noch nicht die Funktion EINDEUTIG. Also erzeuge ich per Programmierung eine Pivottabelle und erhalte so eine (sogar sortierte) Liste der einzelnen Kategorien:
Sub MachePivot()
Dim xlBlattAktiv As Worksheet
Dim xlBlattHilf As Worksheet
Dim xlPivotCache As PivotCache
Dim xlPivotTabelle As PivotTable
Dim lngZeilen As Long
Dim lngSpalten As Long
Set xlBlattAktiv = ActiveSheet
Set xlBlattHilf = ThisWorkbook.Worksheets.Add
lngZeilen = xlBlattAktiv.Range("A1").CurrentRegion.Rows.Count
lngSpalten = xlBlattAktiv.Range("A1").CurrentRegion.Columns.Count
Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C" & lngSpalten, _
Version:=8)
Set xlPivotTabelle = xlPivotCache.CreatePivotTable( _
TableDestination:=xlBlattHilf.Range("A1"), _
TableName:="RenesPivot2", _
DefaultVersion:=8)
xlPivotTabelle.ColumnGrand = False
xlPivotTabelle.RowGrand = False
With xlPivotTabelle.PivotFields("Kategorie")
.Orientation = xlRowField
.Position = 1
End With
End Sub
Ich teste – und: padautz: Fehler!
Ich brauche eine Weile, bis ich die Ursache finde. In der Liste gab es zwei Mal eine Spalte mit der Bezeichnung „Kommentar“. Excel 2016 schafft es nicht, die Spalten für die Pivottabelle umzubenennen (wie neuere Excelversionen):
Wenn man mit PowerQuery auf eine „ältere“ XLS-Excelmappe zugreift, werden folgende Spalten angezeigt:
Beim aktuellen Dateiformat XLSX dagegen drei weitere:
Die drei Spalten (mit ihren Informationen) Item, Kind und Hidden fehlen.
Würde man eine XLS-Datei in XLSX umbenennen, wäre das Ergebnis das Gleiche wie bei XLS:
Wer macht denn so etwas? Und: DAS würde man in Excel doch sofort bemerken.
Ich erhalte vorgestern die Frage, warum das PowerQuery-Tool, das ich für die Firma erstellt habe bei einer Datei nicht läuft. Ich stelle fest – obwohl die Datei vom Format XLSX ist, werden nur die beiden Spalte Name und Date angezeigt, nicht jedoch Item, Kind und Hidden. Warum?
Ich gehe auf die Suche.
Ich stelle fest, dass die im openXML-Format verwendete interne Datei app.xml (im Ordner docProps) folgendermaßen aussieht:
Wenn ich die Datei, die von Apache POI erstellt wurde, öffne, speichere und schließe, sieht diese XML-Datei so aus:
SO jetzt jede XLSX-Datei aus, die von Excel erzeugt und in Excel gespeichert wurde.
Das bedeutet: das (umstrittene) Werkzeug Apache POI produziert XLSX-Dateien, die nicht genau der Spezifikation von Microsoft entsprechen. Ist das schlimm?
Ja, weil mein Werkzeug auf die Spalte „Kind“ zugreift und diese nicht findet …
Ein Dankeschön an Mark Risner. Er hat die Fehlermeldung „Wir haben alle Daten neben Ihrer Auswahl untersucht und konnten kein Muster zum Ausfüllen mit Werten erkennen.“ erhalten.
Was hat er gemacht: mehrmals hintereinander das Ergebnis der Blitzvorschau gelöscht und erneut die Blitzvorschau aktiviert. Irgendwann funktionierte sie nicht mehr.
Ich konnte diesen Fehler und diese Fehlermeldung leider nicht reproduzieren …
Erstellt man in Pivottabellen ein Stripset, wird es nur angezeigt, wenn die Option „Gebänderte Zeilen“ aktiviert ist.
Erstaunlicherweise verschwinden die Farben auch dann, wenn keine Werte vorhanden ist – das heißt: man kann mit den Stripsets keine gruppierte Liste formatieren:
In Excel erstellt man einen Zeilenumbruch in einer Zelle mit der Tastenkombination [ALT] + [Enter]. Der dahinterliegende Code hat die Nummer 10. Importiert man eine Liste mit Zellen mit Zeilenumbrüchen nach PowerQuery, sieht man schnell, dass #(lf), also Linefeed diesem Zeichen entspricht.
Aha, denke ich mir: es wäre doch schön, wenn eine Liste nicht mit Semikola getrennt wären:
Ich öffne den Dialog und trage statt Semikolon in der Kategorie „benutzerderfiniert“ #(lf) ein;
Das Ergebnis irritiert:
PowerQuery hat den Code geändert in:
= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), "#(#)(lf)"), type text})
Natürlich könnte man es per Hand ändern in „#(lf)“
Oder durch die Funktion
Character.FromNumber(10)
ersetzen:
= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), Character.FromNumber(10)), type text})
Schließlich entdecke ich, dass in der Kategorie „benutzerdefiniert“, dass man die Texte „mithilfe von Sonderzeichen verketten“ kann. Und dort findet sich auch der Zeilenvorschub. Und dieses #(lf) wird von PowerQuery auch nicht geändert.
Schöne Frage in der Excelschulung als wir (intelligente) Tabellen anschauen:
Wenn man hinter (oder unter) der Tabelle eine neue Spalte einfügt, wird diese in die Tabelle übernommen:
Wird jedoch VOR der Tabelle eine Spalte eingefügt, ist sie nicht Teil der Tabelle. Abhilfe schafft das Symbol „Tabellengröße ändern“, mit dessen Hilfe der Bereich der Tabelle erweitert werden kann:
ich habe die Formel so angepasst, dass auf die richtigen Werte zugegriffen wird (d.h. es wird der Zinssatz verwendet, der als Drilldown-Liste angelegt wurde, s.u.). Leider kommt immer wieder eine Fehlermeldung, die ich nicht lösen konnte, auch nicht durch eine Internetrecherche. Den Code habe ich mit Notepade++ zusammengebaut und Ihnen angehängt. Das ist der Fehler (beim Komma):
Hallo Frau I.,
1. PowerQuery unterschiedet zwischen Groß- und Kleinschreibung.
Der Befehl lautet
Number.Power
(groß „N“, groß „P“)
2. Stimmt – ist mir später aufgefallen – ich habe die zweite Formel (P) vergessen. Und: NEIN: es gibt keine Barwert- oder andere finanzmathematische Funktion in PowerQuery
Liebe Grüße
Rene Martin
Nachtrag:
so könnte die Lösung aussehen:
Starten Sie den PowerQuery-Abfrageeditor.
Klicken Sie auf die Funktion fnBarwert.
Klicken Sie auf Ansicht / Erweiterter Editor.
Dort sehen Sie die Berechnung:
let
Barwert = (Zins as number, Restnutzdauer as number) as number =>
let
q = 1 + Zins / 100,
Ergebnis = (Number.Power(q , Restnutzdauer) - 1) / (Number.Power(q , Restnutzdauer) * (q-1))
in
Ergebnis
in Barwert
Ist der Zins bei Ihnen eine Zahl oder eine Prozentzahl – also 3 oder 3%? Ist die Restnutzungsdauer in Jahren oder Monaten?
Tragen Sie einfach die entsprechenden Zahlen links ein – rechts wird der Barwert berechnet. Korrekt?
Wenn ich den Datentyp über das Gebietsschema ändere (beispielsweise Englisch (USA), habe ich die Möglichkeit mit einem Klick auf Zahnrad-Symbol hinter dem Schritt den Schritt zu ändern:
Wenn allerdings für sehr viele Spalten der Datentyp geändert wurde, gibt es für DIESEN Schritt kein Zahnradsymbol. Was macht man nun, wenn einer der Datentypen einer Spalte falsch ist?
Die einfachste Möglichkeit: Man markiert die Spalte und ändert den Datentyp in den richtigen Typ. Dann wird dieses Element ersetzt.
Natürlich kann man auch in der Bearbeitungsleiste den Teil per Hand korrigieren:
Oder: man löscht den ganzen Schritt und erstellt ihn neu. Letztere Variante ist natürlich wenig sinnvoll …
haben Sie vielen Dank, das hat prima funktioniert!
Nun soll aber im Tabellenblatt ein Wert manuell eingegeben werden, auf den dann die Berechnung der Spalte zugreift (ähnlich der Eingabe bei den Filterkriterien). Geht das?
#####
Hallo Frau I.,
Wenn Sie Werte auslagern möchten, dann „ziehen“ Sie die Daten nach PowerQuery, wählen den korrekten Datentyp (Text oder Zahl) und machen ein Drilldown, so dass nur noch ein Wert übrig bleibt.
Erstellen Sie eine neue Spalte, rechnen dort zuerst mit einem „harten“ Wert (beispielsweise +5) und ersetzen dann die zahl durch Ihre Variable (hier: + tbl_Plus)
Hilft das?
Hallo Herr Martin,
haben Sie vielen Dank!
Ich habe ein bisschen damit herum probiert. Sofern ich bei einer Tabelle neue Spalten aus derselben Tabelle hinzufüge, klappt alle prime. Allerdings erhalte ich einen Fehler beim Hinzufügen einer Spalte in die Tabelle qry_Datenzugriff, die sich berechnen soll aus „Bodenwert“ (Spalte mit vielen Werten der Tabelle qry_Datenzugriff) mal „Zinssatz“ (Spalte der Tabelle tbl_Zinssatz mit nur einem Wert). Wie kann ich das lösen?
Hallo Frau Issel,
die ersten beiden Schritte sind richtig: Sie laden die Tabelle nach PowerQuery; Sie wandeln den Typ in Dezimalzahl (oder Prozentzahl) um.
Aber dann fehlt der Drilldown: Sie müssen den Wert der Zelle in einen Wert verwandeln. Klicken Sie mit der rechten Maustaste auf die Zelle und führen den Drilldown durch:
Das Ergebnis sieht so aus:
Kann in einer Zeile geschrieben werden:
= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz]
Und diese lange Formel können Sie nun in Ihrer Berechnung verwenden, also statt:
Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* 1.3)
Schreiben Sie:
Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz] )
haben Sie vielen Dank, das hat prima funktioniert!
Nun habe ich eine neue Herausforderung: ich möchte weitere Spalten mit unterschiedlichen Berechnungen hinzufügen, bei denen neue Spalten auf Werte von zuvor hinzugefügte Spalten zugreifen. Das sollte möglich sein, vermute ich.
#####
Hallo Frau Issel,
zu Ihren Fragen: klar können sie mit einer berechneten Spalte weiterrechnen: Sie fügen eine benutzerdefinierte Spalte ein: MWST = [Netto] + 0.07
ich möchte aus den Werten von 2 Spalten Werte für eine 3. Spalte berechnen lassen und damit dann weiter rechnen. Kann ich das in PowerQuery erreichen?
Bspw. im Dokument Report09f.xlxs die Werte der Spalte K mal die der Spalte L. Anschließend möchte ich darüber Min, Max, Mittelwert berechnen, analog zu den Spalten, die im Exportdokument schon vorhanden sind.
Viele Grüße,
####
Hallo Frau I.,
in PowerQuery können Sie über „Spalte hinzufügen“ / Benutzerdefinierte Spalte eine Berechnung hinzufügen. Geben Sie dort den Namen der neuen Spalte an und die Berechnung, indem Sie auf diese langen Feldnamen doppelklicken!
Diese Spalte wird ans Ende der Tabelle gesetzt; Sie können sie schnell (über das Kontextmenü) an den Anfang verschieben:
Und dann per Hand etwas nach rechts:
Für die Aggregatfunktionen: erstellen Sie einen Verweis auf die Tabelle
Markieren die Spalte und wählen aus Transformieren / Statistiken die gewünschte Funktion aus.
In PowerQuery-Schulungen empfehle ich die Option „Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen.“ einzuschalten. Warum? Beim Import von Textdateien und CSV-Dateien werden Datumsinformationen in Zahlen konvertiert, wie folgende Screenshots zeigen:
Diese Option steht in Microsoft 365 – jedoch nicht in Excel 2016 zur Verfügung:
Allerdings: in einem Ordner befinden sich eine Reihe gleichförmig aufgebauter Excelmappen:
Greift man mit PowerQuery auf den Ordner zu und lässt sich den Content anzeigen, dann wird die erste Zeile nicht als Überschrift erkannt und in die Liste eingefügt:
Natürlich kann man die erste Zeile zur Überschrift machen und die übrigen Zwischenzeilen löschen. Ist aber nervig. Was tun?
Ich weiß es nicht?
Sich an der Variante orientieren, die man häufiger verwendet: Zugriff auf Ordner oder Zugriff auf Text/CSV-Dateien?
Vor dem Zugriff die entsprechende, geeignete Variante einschalten, beziehungsweise ausschalten?
Schöne Frage in der letzten PowerQuery-Schulung: wo befindet sich das (auch Excel bekannte) Symbol, das erlaubt nicht nur eine Spalte zu sortieren, sondern nach mehreren:
Die Antwort: ein SYMBOL hierfür gibt es nicht – man muss die Spalten in der gewünschten Sortierreihenfolge anklicken und sortieren, beispielsweise zuerst Ort; innerhalb eines Ortes (Aachen) nach der PLZ, innerhalb einer PLZ (beispielsweise 52062) nach der Straße, …
PowerQuery quittiert die Mehrfachsortierung mit dem Befehl
herzlichen Dank für deine Bemühungen! Das bringt mich ein großes Stück weiter. Leider funktioniert aber irgendeine Kleinigkeit noch nicht… Ich hab viel probiert, komm aber nicht auf den Fehler.
Expression.Error: Der Wert "2022" kann nicht in den Typ "Text" konvertiert werden.
Die Fehlermeldung verstehe ich nicht. Das Jahr, nach welchem gefiltert werden soll.
Hallo Nadine,
ist die Zahl 2023 in der Zelle als Text formatiert?
Und: was macht „geänderter Typ“? – in Text oder Zahl konvertieren?
Liebe Grüße
Rene
Hallo Rene,
genau, ich habe dann extra die 2023 in Text formatiert. Ursprünglich hatte ich es als Zahl, da kam allerdings auch diese Fehlermeldung, weshalb ich die 2023 dann in Text formatiert habe.
Hier die Schritte, welche ich in der Jahrestabelle ausgeführt habe:
Dort wo dann die Formel eingefügt wird, also dort, wo nacher nach diesem Jahr gesucht werden soll, sieht die Formatierung so aus:
Hallo Nadine,
Folgende Ursache: Ich vermute in deiner Zelle stehe die Jahreszahl als ZAHL – in meiner ersten Städtedatei hatte ich sie als Text formatiert.
Damit du auch einen Text erhältst, muss dein zweiter Schritt
= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SOP"]}[Content],{{"Column1", type text}}){0}[Column1]
verwendet werden
In deinem Code
= Table.SelectRows(Quelle, each (Record.Field(_ , Excel.CurrentWorkbook(){[Name="SOP"]}[Content]{0}[Column1]) <> null))
Zusammengefasst: der in Excel eingetragene Wert in eine Zahl. Die Spaltenüberschrift jedoch ein Text. Irgendwann muss die Zahl in einen Text konvertiert werden!
Ich habe schon eine Weile überlegen müssen. Folgende Frage erreichte mich:
„Allerdings möchte ich nun in einer Spalte, die nicht fest definiert ist, filtern. Ist dies möglich?
Ziel: Ich möchte das es mir nur die Zeilen anzeigt, die in einer bestimmten Jahres-Spalte einen Wert haben.
Der Anwender des Tools sollte die Möglichkeit haben, ein Jahr einzugeben in einem bestimmten Feld.
Hier wählt der Anwender das Jahr aus. Daraufhin sollte in Power Query in der Spalte, mit der Bezeichnung 2026, nach Werten <> null gefilter werden. Im Screenshot würde dann nur noch die erste Zeile erscheinen, da in der Spalte 2026 nur in der ersten Zeile ein Wert enthalten ist. Ich hab schon viel rumprobiert und bekomm es nicht hin.“
Ich ziehe das Ergebnis des Filters (versehen mit dem Namen „Jahr“) nach PowerQuery und benennen die Abfrage „Jahr“. Sie sieht folgendermaßen aus:
Eine schöne Frage in der letzten PowerQuery-Schulung.
Eine Tabelle soll verändert werden. Im linken Bereich befinden sich Informationen (nennen wir sie „Metadaten“), im rechten Bereich in mehreren Spalten weitere Informationen zu diesen Metadaten.
Jede dieser Gruppen, bestehend aus jeweils drei Spalten, soll neben die anderen Daten geschrieben werden, so dass die Metainformation so oft auftaucht, wie Gruppen vorhanden sind. Dabei können beliebig vieler dieser Gruppen auftauchen.
Das Ziel:
Mein erster Gedanke:
Ich fasse mit dem Befehl „Spalten zusammenführen“ jeweils die einzelnen Spalten einer Gruppe zusammen:
Anschließend kann man diese Spalten entpivotieren
und danach am Trennzeichen (hier: „|“) teilen.
Aber: das Verfahren ist umständlich, weil (hier:) bei 17 Gruppen 17 Mal entpivotiert werden muss. Da die Anzahl der Gruppen variabel ist, ging ich auf die Suche, ob man das mit geschickten M-Befehlen (einer Schleife!) abkürzen und dynamisch halten kann.
Da fiel mit der Artikel von Hildegard Hügemann in die Finger:
Anschließend werden die Überschriften benötigt in der Form A – B – C. Leider stehen sie hier als A1 – B1 – C1 – A2 – B2 – C2 – A3 – … Die Zahlen müssen entfernt werden. Man kann sie mit dem Assistenten „Spalte teilen“ und er Option „Nach Wechsel von Nicht-Ziffer zu Ziffer“ herauslösen:
DIESE (spätere Überschriftsspalte) wird nun pivotiert, wobei die Werte (letzte Spalte) natürlich nicht aggregiert werden (verbirgt sich in den „Erweiterten Optionen“):
Der Rest ist „Kosmetik“: Datentypen festlegen, Spalten löschen, leere Spalten entfernen (wegfiltern), Spalten umbenennen, …
Klasse!
Ein großes Dankeschön an Hildegard Hügemann für die Lösung – hier habe ich glatt „in die falsche Richtung gedacht“.
Kennst du das? Man möchte in PowerQuery in mehreren Spalten den Datentyp ändern. Ein Klick auf das kleine Symbol und alle Markierungen werden aufgehoben:
Abhilfe schafft der Befehl Transformieren / Datentyp. Lästig:
Die Lösung zeigt Frank Arendt-Theilen:
Man muss die [Strg]-Taste halten und zwei Mal auf das kleine Symbol klicken. Dann klappt es:
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Details:
Key=[Record]
Table=[Table]
nicht weiter.“
Ich schaue mir das Ganze an. Was haben wir gemacht?
In einem Tabellenblatt werden drei Dateien aufgelistet und der Pfad, in dem sich diese Dateien befinden. Diese vier Zellen haben Namen – hier: Schweinchen1, Schweinchen2, Schweinchen3 und Pfad:
Über Daten / Daten abrufen / aus Datei greife ich auf eine der drei Dateien zu:
Die Datentypen werden nicht automatisch erkannt; übrig bleiben drei Schritte; das Ergebnis wird nach Excel zuzrückgegeben:
Eine der drei Zellen mit Namen wird über Daten / Daten abrufen / Aus Tabelle/Bereich in PowerQuery verwendet. Nach einem Drilldown erhält man den Inhalt der Zelle:
Dies wird für die übrigen Zellen wiederholt. Fügt man nun diese Variablen in den Befehl Excel.Workbook ein, so ist eine Firewall-Meldung die Folge:
Diese kann man umgehen, indem man den Code (Zugriff auf den Inhalt einer Zelle mit Namen) in eine Zeile schreibt:
Dies wird auch für die anderen beiden Dateien durchgeführt, die anschließend in Excel geladen werden:
Die Hilfsabfragen Schweinchen1, Schweinchen2, … kann man getrost löschen.
So habe ich die Vorlage erstellt. Und nun kommt die Fehlermeldung:
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Ich begebe mich auf die Suche. Der Fehler taucht beim Zugriff auf das Tabellenblatt „Tabelle1“ auf. Nachgeschaut: bei einer anderen Datei heißt das Tabellenblatt „Sheet1“:
Also muss ich auch noch den „harten“ Namen entfernen. Ich mache es so:
Ein Teilnehmer zeigt mir seine Tabelle. Sie hat sehr viele Spalten, in denen Informationszahlen stehen:
Seine Frage:
„Wenn ich eine Pivottabelle estelle (in der ich die vorkommenden Werte zähle), kann ich nicht die einzelnen Spalten in die Werte ziehen. Was muss ich tun?
Die Aufgabe: Die Orte werden in den Zeilen gruppiert, in die Statusangaben in den Spalten. Man kann nun eine Person in die Zeilen ziehen und sich in den Werten die Anzahl der Einträge anzeigen lassen:
Jedoch: sobald eine zweite Person hinzukommt, arbeitet die Pivottabelle nicht so wie gewünscht:
Da das Ziel war aus einer Pivottabelle ein Diagramm zu erzeugen, scheiden mehrere Pivottabellen aus.
Ich überlege: die Form der Tabelle ist unglücklich gewählt. Man darf die Informationen nicht in Zeilen und Spalten abtragen. Man muss die Tabelle entpivotieren. Hier bietet sich PowerQuery an:
Gesagt, getan – die Liste wird erstellt. Eine Pivottabelle erzeugt:
Genau SO wollte er es haben! Als Basis für ein Diagramm. Er war begeistert.
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Die Möglichkeit, den Datentyp über ein Gebietsschema auszuwählen, ist hinlänglich bekannt:
(Randbemerkung: mit der Taste [F] gelangt man am schnellsten zu Englisch / USA)
Benötigt man das andere Gebietsschema mehrmals, kann man dies in den Optionen in den Regionalen Einstellungen der Arbeitsmappe festlegen:
Hinweis: Nicht verwechseln mit den Regionalen Einstellungen, welche die Sprache der Namen der Variablen (beispielsweise Gefilterte Zeilen, Geänderter Typ, Sortierte Zeilen, …) festlegt:
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Kennt ihr das Problem? Man erstellt eine Reihe von Abfragen:
schließt den Editor, aber aus Versehen lädt man die Abfragen nicht als Verbindung, sondern als Tabelle – padautz – schon hat man 20 (ungewünschte) Tabellen.
Abhilfe schafft in den Optionen die „Standardeinstellung zum Laden von Abfragen“. Wählt man dort die Option „benutzerdefinierte Standardeinstellung“ und deaktiviert alle Kontrollkästchen, so werden die Abfragen nicht als Tabelle in Excel eingetragen.
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Beim Importieren von Daten „erkennt“ PoweryQuery den Datentyp der Spalten. Das kann nervig oder lästig sein oder auch zu Fehlern führen:
Diese Option kann man deaktivieren:
„Spaltentypen und -überschriften für unstrukturierte Tabellen niemals erkennen.“
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Wir haben drei Möglichkeiten gefunden, um Werte, die sich in Excel befinden, als Parameter in PowerQuery zu verwenden:
Die Werte stehen in einer intelligenten Tabelle:
2. Die Zellen, in denen sich die Werte befinden, werden mit einem Namen versehen:
3. Die Werte sind das Ergebnis von Berechnungen von Array-Funktionen, beispielsweise FILTER oder SEQUENZ:
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Lagert man in Excel einen Datenpfad in eine Zelle, kann man den Wert in PowerQuery verwenden, wenn man einen Drilldown erzeugt hat:
Verwendet man nun diesen Wert, also diese Variable, ist eine Firewall-Meldung die Folge:
Natürlich kann man in den Optionen diese Firewall-Einstellungen ausschalten. Oder man kann das Problem umgehen, indem man den Verweis auf die Excelzelle nicht in einer getrennten Abfrage belässt, sondern in die Formel einbaut, beispielsweise so:
Ich habe ein Tabellenblatt, in dem jeden Tag neue Daten eingetragen werden und dann diese wieder gelöscht werden, da diese Daten mit einer Auswertung zusammenhängen. Da am nächsten Tag dort wieder neue Daten eingetragen werden müssen.
Und Power-Query aktualisiert ja im Normalfall nur die aktuellen.
Ich habe nun folgendes versucht wie James Baylay in folgendem Beitrag:
let
Quelle = Excel.CurrentWorkbook(){[Name="tblZusammenfassung"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Schulz", Int64.Type}, {"Jackli", type number}, {"Marli", Int64.Type}, {"Jergli", Int64.Type}, {"Michal", Int64.Type}, {"Hans", Int64.Type}, {"Veitli", Int64.Type}}),
#"Angefügte Abfrage" = Table.Combine({#"Geänderter Typ", tblDieSieben}),
#"Entfernte Duplikate" = Table.Distinct(#"Angefügte Abfrage")
in
#"Entfernte Duplikate"
Ich habe folgendes Problem. Wenn ich bei der Abfrage «tblZusammenfassung» Laden-in / Nur Verbindung erstellen ausführe, dann kommt die untenstehende Fehlermeldung:
Vielleicht haben sie einen Tipp für mich, wie ich das Problem lösen könnte.
mir ist doch noch etwas besseres eingefallen, lässt sich aber auch nicht umsetzen…
Ich wollte jetzt direkt in Power Query filtern nach den Zeilen, welche im Jahr, welches ich im Excel Blatt auswähle, ungleich 0 sind.
Irgendwo passt was noch nicht, hoffe du kannst mir helfen 😀
Hallo Nadine,
Wenn du Leerzeilen / Leerzellen rausfiltern möchtest, dann nicht wie in Excel mit zwei Anführungszeichen, sondern mit Null. Ähnlich wie Datenbanken hat PowerQuery einen eigenen Datentyp für leere Zellen: null:
= Table.SelectRows(#“Geänderter Typ“, each [Datum] <> null)
Und: der Feldname darf nicht in Anführungszeichen gesetzt werden – das Feld heißt: [Datum], nicht [„Datum“].
ich habe schon einiges in Power Query bearbeitet und bin gerade auf dem Stand, dass ich mir durch Filter genau die Daten aus Power Query ziehe welche ich benötige.
Aktuelles Problem: Ich möchte nur die Zeilen haben, welche in dem Jahr Werte haben, welches ich als Filter eingebe. Ich will aber nicht nur das Jahr, sondern alle Werte dann, wenn in diesem speziellen Jahr ein Wert vorhanden ist.
Den Filter „Jahr“ habe ich nicht in Power Query benutzt, da ich noch keine Lösung gefunden habe.
###
Hallo Nadine,
1.) wenn du einen Filter definierst, kannst du ihn auch in PowerQuery „reinziehen“. Danach würde ich ihn als Drilldown in einen Wert umwandeln – etwas so:
2.) Filtere ein beliebiges Datum. Es sieht dann so aus:
= Table.SelectRows(#“Gefilterte Zeilen“, each [Datum] >= #date(2020, 1, 1))
Und nun ersetze ich die Jahreszahl 2020 durch meine „Variable“ aus Schritt 1.
regex.Pattern = strMuster1
regexRaus.Pattern = strMuster1_Raus
regexRaus.Global = True
For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
If regex.Test(Range("I" & i).Value) = True Then
Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
j = 0
For Each strFund In strTreffer
strTemp = strFund
If IsDate(strTemp) Then
If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
Else
Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
If Year(CDate(strTemp)) > Year(Date) Then
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
End If
Else
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
j = j + 1
Next
End If
Next
Vier andere Varianten werden analog abgearbeitet. Klappt.
Warum hat VBA nicht als Standard Regex eingebunden?
Warum kennt PowerQuery keine regulären Ausdrücke?
Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?
Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:
Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.
kennst Du das Problem beim Löschen von Zeilen bei ausgeblendeten Spalten?
Eine intelligente Tabelle mit Filter.
Hier sind auch die ganzen Zeilen und alle gefilterten Zeilen markiert und STRG + Minus funktioniert nicht. Spalte B ist ausgeblendet.
Zeilen in einem gefilterten Bereich oder in einer gefilterten Tabelle können nicht verschoben werden.
Sind nur die Daten markiert und nicht die ganzen Zeilen, funktioniert es doch wieder mit Meldung. Es sind dann aber wieder alle Daten der Zeile weg und nicht nur die markierten Tabellenwerte.
Einer gewinnt! Oder: es kann nur einen Highlander geben.
Schöne Frage in der Excel-Schulung: kann ich eine (intelligente) Tabelle mit einer bedingten Formatierung versehen? Wissen Sie es? Welche Farbe gewinnt?
Gegeben sei ein Listenbereich:
Er wird in eine (intelligente) Tabelle konvertiert:
Natürlich kann ich die Schriftfarbe festlegen (beispielsweise für alle Flüsse aus Asien):
Aber: wer gewinnt, wenn ich eine Hintergrundfarbe hinzufüge?
Wer wohl?
Richtig: die bedingte Formatierung!
Und: was passiert, wenn die Liste verlängert wird? Richtig: dann wird die bedingte Formatierung, wie auch andere Formatierungen, mitgenommen:
Ich empfehle dennoch: entweder intelligente Tabelle OHNE Formatierung oder bedingte Formatierung nur mit Schriftfarbe.
Ich erkläre den Nutzen und die Vorteile von (intelligenten) Tabellen. Beispielsweise Diagramme. Setzt man auf eine intelligente Tabelle ein Diagramm auf:
so wird die Erweiterung der Liste sofort ins Diagramm aufgenommen:
Was mich jedoch irritiert: Warum zeigt das Diagramm weder im Diagrammbereich, in den Achsenbeschriftungen noch in den Legendeneinträgen den Namen der Tabelle an – sondern immer noch den Bereich?
Die Pivottabelle zeigt doch auch die „korrekte“ Datenquelle an:
Ich will in der Excelschulung demonstrieren, dass Listen eine Überschriftszeile haben sollten; ja – dass Pivottabellen auf Listen aufsetzen, bei denen jede Spalte eine eindeutige Überschrift besitzt.
Ich lösche eine Überschrift heraus:
erstelle eine Pivottabelle, vergesse aber, den Haken beim Datenmodell zu entfernen:
Da die Daten ins Datenmodell geladen werden, muss jede Spalte eine Überschrift haben. Da dies nicht gegeben ist, füllt Excel nicht die leere Überschrift auf, sondern beginnt ab der zweiten Zeile, die als Überschrift verwendet wird:
Eigentlich wollte ich die Fehlermeldung
Der PivotTable-Feldname ist ungültig. Um einen PivotTable-Bericht zu erstellen, müssen Sie Daten verwenden, die einer Liste mit Spaltenüberschriften organisiert sind. Wenn Sie den Namen eines PivotTable-Berichtsfeldes ändern, müssen Sie einen neuen Namen für das Feld eingeben.
Excelschulung. Thema: Listen. Ich beginne mit der Antwort auf die Frage, wie eine Liste in Excel aufgebaut werden soll:
„EINE Überschriftszeile“ deklamiere ich: „EINE, genau EINE – jede Überschrift MUSS eine Überschrift haben.“
Wir ändern die Daten, verschieben, löschen, benennen um, … und: sortieren.
Ein Teilnehmer fragt, warum die Überschrift IN der Liste steht. Meine Antwort: „Weil Sie nicht tun, was ich Ihnen sage!“ Sie haben sicherlich eine Spalte OHNE Überschrift versehen. Machen Sie mal bitte den letzten Schritt zurück!“
„Tatsächlich“, lautet seine Antwort: „eine Spalte hatte keine Überschrift!“
Ich schiebe nach: Wenn Sie unsicher sind, ob Ihre Liste eine Überschrift hat, beziehungsweise die erste Zeile als Überschrift erkennt, dann verwenden Sie die „benutzerdefinierte Sortierung“. Dort ist deutlich zu erkennen: Überschrift oder keine Überschrift; dort kann man auch explizit einschalten: „Bitte mit Überschrift“. Das heißt: die erste Zeile bleibt beim Sortieren bitte oben stehen!
Es ist zum Haare-Raufen. Hätte ich welche auf meinem Kopf! Unglaublich! Excel ärgert mich, wo es nur kann. Wenn ich schon einen Fehler haben möchte – was passiert? – Richtig – natürlich kein Fehler! Es ist zum Haare-Raufen!
Was ist geschehen?
Excelschulung. Turboschulung: ich zeige in einer Stunde Listen: sortieren, filtern, intelligente Tabellen, Datenschnitt und Pivottabellen. Eine Teilnehmerin bedankt sich für die Infos zu den Pivottabellen – das hätte ihr sehr weitergeholfen; nun verstehe sie den Gedanken, der dahinter steht. Und: „so schwierig ist das gar nicht“:
Ich wiederhole. „Der Aufbau der Tabelle ist wichtig: Entweder Sie nehmen eine intelligente Tabelle oder Sie achten darauf, dass Ihre Liste keine Leerzeile und keine Leerspalte hat. Und: jede Spalte muss eine Überschrift haben.“
Ich demonstriere es, lösche eine Spaltenüberschrift raus
erstelle eine Pivottabelle – und: es klappt! Excel unterläuft meine Schulung. Jetzt, wo Excel einen Fehler erzeugen sollte tut Excel: NICHTS! Fügt den gelöschten Spaltennamen ein:
Der Gedanke: Klar – eine zweite Pivottabelle wird nicht auf der Liste aufgesetzt, sondern auf dem Pivotcache. Deshalb weiß Excel auch den Namen der fehlenden Spaltenüberschrift. Der Fehler käme beim Aktualisieren zum Tragen.
Oder – damit die Teilnehmerin mir glaubt – ich kopiere die Liste in eine andere Datei, erstelle dort die Pivottabelle und:
Du, ich muss mich verzweifelt bei dir melden mit einem Excel-Problem. Ich mache einen Import zu WordPress und der Kunde hat mir die Inhalte als Excel geliefert. Es geht um Schadbilder (Gärtner-Themen). Jedes Schadbild wird ein Artikel und sollte deshalb eine Zeile sein. Soweit so gut, jetzt der Kniff: Jeder Text hat Zwischentitel und diese sind aber als Spalten im Excel File angelegt. Also sind die verschiedenen Spalten nicht einzelne Felder in WordPress, sondern ein grosses Textfeld. Und die Spaltentitel sollten jeweils als Zwischentitel in diesen Texten zu finden sein. Die Zwischentitel sollten zudem ein HTML H-Tag erhalten und nicht einfach „fett und grösser“ sein.
Kannst du mir da vielleicht sagen, wie ich weiterkommen kann? Bitte sei ehrlich, wenn das deine Kapazitäten sprengt. Dann machen wir das manuell, das würde auch gehen, es sind um die 140 Artikel.
Ich gestehe – ich habe zuerst überlegt, dieses Problem mit TEXTVERKETTEN zu lösen. Als Trennzeichen hätte ich „</p><p>“ oder Ähnliches eingegeben. Aber irgendwie gefiel mir die Rechnerei nicht.
Warum nicht PowerQuery?
Klar: 1. Schritt: Liste in Tabelle verwandeln. Die Daten aus Tabelle/Bereich importieren:
Das Zauberwort heißt „entpivotieren“. Und schon habe ich eine Tabelle mit zwei Spalten: in der ersten steht die Überschrift, in der zweite die Daten aus den entsprechenden Tabellen:
Und das kann problemlos zu einer Spalte verkettet werden:
Die Idee ist gut – sie funktioniert nur leider nicht.
Ein Teilnehmer einer Excelschulung möchte eine fortlaufende Reihe erzeugen. Er möchte, dass „Lücken übersprungen“ werden und dass die Reihe bequem fortgesetzt werden kann.
Kein Problem, oder:
Die Formel
=WENN(B2="";"";MAX($A$1:A1)+1)
hilft hierbei.
Damit unter der Liste neue Daten mit einer fortlaufenden Nummer eingetragen werden können, wandle ich die Liste in eine (intelligente) Tabelle um:
Ein neuer Name:Lücke und ein weiterer Name:
Klappt.
Wird eine Zeile gelöscht:
funktioniert der Mechanismus hervorragend:
Jedoch: wird eine Zeile eingefügt:
Dann versagt der Mechanismus leider:
Was man feststellen kann, wenn man einen Namen einträgt:
ich versuche gerade eine PowerQuery-Auswertung aus den Interviewfragebogen zu erstellen.
Ich erhalte allerdings die Fehlermeldung „Die Konvertierung in Number war nicht möglich.
Was mache ich da falsch?“
Was mache ich mit so einer Mail? Richtig: ich schlage vor, mir das Ganze über teams anzusehen. Und tatsächlich:
Okay. Langsam. Von vorne bitte. Können wir uns das Ganze mal bitte in Ruhe ansehen? Was machen Sie?
In einem Ordner befinden sich mehr als 50 Excelmappen:
Jede dieser Mappen hat folgenden Aufbau:
In Spalte A befindet sich in jedem Formular eine Nummer der Form 0., 1., 2., …
Aus einigen dieser Gruppen sollen Informationen ausgelesen werden. Diese Informationen befinden sich in Spalten rechts daneben. Soweit so gut – PowerQuery ist das richtige Werkzeug hierfür. Wir schauen uns das Ganze an – Schritt für Schritt:
Schritt: Leere Arbeitsmappe. Daten / Daten abrufen und transformieren / Daten abrufen / Aus Datei / Aus Ordner
2. Schritt. Der Ordner wird ausgewählt; die Daten werden transformiert.
3. Schritt: Unterordner werden ausgeschlossen; andere Dateitypen ebenso:
4. Schritt: In der Spalte „Content“ befindet sich der Inhalt. Da die Spalten alle den gleichen Aufbau haben, kann man die anderen Spalten löschen und diese Spalte „entpacken“:
Da alle Dateien den gleichen Aufbau und das gleiche Tabellenblatt haben, stellt dies kein Problem dar:
Das Ergebnis:
Da Informationen aus bestimmten „Gruppen“ geholt werden, wird die erste Spalte über Transformieren / Ausfüllen „nach unten gezogen“:
Einige Spalten werden gelöscht. Aus der ersten Spalte werden einige der benötigten Spalten selektiert:
Das Ergebnis wird zurück nach Excel geschrieben (Start / Schließen & Laden / Schießen & Laden in). Obwohl die Daten in Powerquery korrekt angezeigt werden:
ist die Fehlermeldung die Folge:
[DataFormat.Error]. Die Konvertierung in „Number“ war nicht möglich.
Ich stutze. Zurück zu PowerQuery. Vielleicht ist „irgend etwas“ in der ersten Spalte?!? Es sieht nicht so aus:
Aber: „Die Liste kann unvollständig sein.“ Ich lasse mir über Ansicht die „Spaltenqualität“ anzeigen:
Kein Fehler in der ersten Spalte!?!
Wirklich nicht?
Wir wissen, dass PowerQuery zu Beginn nur 1.000 Zeilen auswertet. Bei 50 Formularen x zirka 150 Zeilen sind das 7.500 Zeilen. Okay – ich lasse ALLE Zeilen auswerten, indem ich auf der Statuszeile von 1.000 auf „alle“ wechsle:
Und tatsächlich: JETZT lautet die Beschriftung der Zeile „Spaltenqualität“
Unerwarteter Fehler.
Aha!
Ich gehe auf die Suche – Schritt für Schritt zurück. Schon bald ist klar, dass die Häufigkeit der Fehler unter 1% liegt:
Der Fehler tritt auf, als der Typ geändert wird. Moment – DAS habe ich doch gar nicht gemacht:
Richtig: in Datei / Optionen und Einstellungen / Abfrageoptionen lautet die Grundeinstellung:
Spaltentypen und -überschriften für unstrukturierte Quellen immer erkennen. Und richtig: Das produziert den Fehler:
[DataFormat.Error]
Aha – diese Einstellung bewirkt, dass aus 0., 1., 2., … die Zahlen 1, 2, 3, … werden. Das heißt: in einer der Dateien befindet sich wahrscheinlich in Spalte A eine andere Informationen.
Welche Datei? Zurück zum Anfang:
Ich entferne die erste und die zweite Spalte (den Dateinamen) nicht:
Bevor der Datentyp geändert wird, lasse ich mir alle Inhalte anzeigen:
und stelle fest, dass in einer (oder mehreren) Zellen ein Punkt vorhanden ist:
Da ich die Dateinamen „sehe“, kann ich die Spalte in den Datentyp „Text“ konvertieren und den Übeltäter filtern:
Als Text erzeugt der Punkt kein Problem, allerdings bei der (automatischen) Umwandlung in Zahl.
Die Lösung liegt auf der Hand: entweder man löscht den Punkt in PowerQuery raus oder man geht auf die Suche in der Datei:
Und dann funktioniert die Zusammenfassung problemlos:
Fazit: Vermeiden Sie – wenn möglich – die automatische Datenkonvertierung.
Verwenden Sie ALLE Daten bei der Fehlersuche.
Verwenden Sie die Werkzeuge der Registerkarte Ansicht, also: Spaltenqualität, Spaltenprofil und Spaltenverteilung.
In der letzten Excelschulung wurde ich gefragt, ob man Pivottabellen auf einem geschützten Tabellenblatt erzeugen, ändern und aktualisieren kann?
Ich gebe zu – ich war ein bisschen unsicher.
Und das sind die Antworten:
Auf ein schreibgeschütztes Blatt kann keine Pivottabelle eingefügt werden. Auch dann nicht, wenn alle Optionen zum Zulassen aktiviert sind. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.
Eine Aktualisierung ist nicht möglich, wenn das Blatt geschützt ist. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.
Wurde beim Blattschutz die Option “ PivotTable und PivotChart verwenden“ aktiviert wurde, kann man die Felder in die Zeilen, Spalten, Filter, … ziehen und von dort wieder entfernen – auch wenn die Zellen gesperrt sind.
Wurde das Tabellenblatt mit der Datenquelle geschützt, kann man keine Pivottabelle erstellen:
PowerQuery stellt für Zahlenformate alle (nur denkbaren) Varianten auf Basis der Gebietsschemata zur Verfügung. Allerdings fehlt die ISO-Norm bei der Kalenderwoche.
Okay.
Wir haben eine Liste mit Ländernamen, die sortiert werden:
Es fällt auf, dass PowerQuery streng nach Groß- und Kleinschreibung sortiert. Deshalb steht die USA vor Ungarn:
Das kann man mit dem Befehl each Text.Upper korrigieren:
Aber: Österreich befindet sich am Ende. Das Alphabet wird US-amerikanisch sortiert. Und: der Befehl Sort stellt keinen Parameter zur Verfügung eine Länderkennung einzutragen. Im Deutschen wird a < ä < b sortiert, im Spanischen a < b < c < ch < d … < l < ll < m < n < ñ < o …
Für jedes Land, das heißt: für jede Sprache müsste man eine Hilfstabelle anlegen. Sehr mühsam!
Ich öffne im Windows-Explorer das Eigenschaftenfenster einer Datei und wechsle auf die Registerkarte „Sicherheit“. Dort finde ich den Dateinamen mit Pfad, den ich markiere und nach Excel kopiere. Achtung: Ich markiere von rechts nach links:
Ein zweites Mal – jetzt wird von links nach rechts markiert und anschließend kopiert:
Ich kopiere beide Varianten nach Excel – die erste ist oben, die zweite unten. Ich ermittle die Anzahl der Zeichen mit LÄNGE und bin erstaunt. Ich löse das erste Zeichen mit der Funktion LINKS heraus und bin wieder erstaunt:
Wandelt man das Zeichen vor dem Laufwerksbuchstaben D mit Code in den ASCII-Code um und mit ZEICHEN wieder zurück, so erhält man ein „?“
Ich bin erstaunt.
Noch schlimmer wird es, wenn man mit PowerQuery und diese Access-Datenbank zugreift
und den Pfad durch den ersten Text ersetzt:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
lautet die Fehlermeldung. Man sieht das Zeichen nicht – weder in Excel noch in Word, im Editor oder in PowerQuery. Und wundert sich über diese merkwürdige Fehlermeldung.
Da gibt es nur eine Lösung: Will man den Dateinamen mit Pfad ermitteln, muss man im Explorer über das Symbol „Pfad kopieren“ den Verzeichnisnamen und Dateinamen in die Zwischenablage kopieren. (danke an Martin Weiß für diesen Tipp)
Gestern in der Excelschulung. Wir besprechen den Autofilter. Ich erkläre die Option „Daten haben Überschriften“ beim Assistenten „benutzerdefiniertes Sortieren“.
Ein Teilnehmer fragt, warum bei ihm diese Option ausgegraut, also inaktiv sei:
Die Antwort ist schnell gefunden: der Autofilter wurde eingeschaltet – dadurch wird die erste Zeile als Überschriftszeile definiert.
Gestern in der Excelschulung. Ich erkläre den Autofilter. Wir haben eine Liste mit zirka 12.000 Datensätzen. Wir filtern alle Hamburger und Hamburgerinnen:
Zu der gefilterten Liste fügen wir alle Personen hinzu, die in Flensburg wohnen:
Und so machen wir weiter mit Bremen, Husum, Kiel, Buxtehude, Uelzen, Itzehoe, …
Ein Teilnehmer meldet sich und fragt, wie und ob man denn erkennen könne, welche Orte gefiltert seien:
Ich habe eine Weile überlegen müssen. Fährt man mit der Maus über das Filtersymbol, werde alle Filterkriterien im Quickinfo angezeigt:
Nennen wir ihn B. B. kann für Björn stehen. Oder für Benno. Für Benjamin oder für Boris. Egal. Wir nennen ihn B.
B. ist Teilnehmer meiner Excelschulung und stellt eine Frage zum Aufbereiten von CSV-Dateien, die er in regelmäßigen Abständen erhält. Er denkt an eine VBA-Lösung – ich schlage PowerQuery vor. Die Datei wird aufgerufen, transformiert und nach Excel zurück geschrieben.
Allerdings: der Pfad, beziehungsweise der Dateiname soll variabel sein. Eigentlich kein Problem, denke ich, und lasse B. Pfad und Dateiname in die Excelmappe schreiben, mit einer Überschrift versehen und in eine (intelligente) Tabelle umwandeln.
Beide Tabellen werden nach PowerQuery gezogen, und dort mit einem Drilldown in einen Text verwandelt. Sie werden in dem Befehl
File.Contents
verwendet; die Sicherheitsstufe dieser Arbeitsmappe wurde ignoriert. Und dann das Erstaunliche:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
Stirnrunzeln.
Probieren. Beispielsweise Pfad und Dateiname in PowerQuery (oder in Excel) zu verketten und diese Zeichenkette zu verwenden. Beides schlägt fehl:
Immer wieder die gleiche Fehlermeldung:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
In Ruhe, alleine, und ohne B. schaue ich mir die Zeichenkette genau an und probiere. Erstaunt stelle ich fest, dass das erste Zeichen nicht der Laufwerksbuchstabe ist. In Excel kann man das mit der Funktion LINKS oder TEIL ermitteln. Der ASCII-Code lautet 63 – eigentlich ein Fragezeichen.
Ich überlege, probiere und frage B. Er hat eigentlich nur den Namen des Verzeichnisses aus den Dateieigenschaften kopiert. Und ich habe ihm zugesehen.
Ich weiß nicht, wie dieses merkwürdige Zeichen in die Excelzelle gelangt ist. Ich weiß, dass Excel bei einigen Zeichen (geschützte Leerzeichen, bedingte Trennstriche, …), die man über Word, Outlook oder eine Webseite nach Excel kopieren kann, Probleme hat. Aber hier? Keine Ahnung.
Lösung des Problems: Pfad neu tippen – und dann klappt es!?!
Schöne Frage in der letzten PowerQuery-Schulung: warum kann man eigentlich keine Duplikate ermitteln lassen? Oder – wie in Excel – Duplikate löschen lassen?
Stimmt – DAFÜR gibt es in PowerQuery leider keinen Assistenten. Muss man „per Hand“ machen.
Tanja Kuhn schreibt: „Das geht beides. Duplikate löschen per Rechtsklick. Duplikate anzeigen über Gruppierung.“
Danke für den Hinweis – zur Gruppierung hätte der Teilnehme, der sich so eine Option beim Import der Daten gewünscht hatte, sicherlich angemerkt, dass man es dann auch „Duplikatensuche“ nennen sollte. Das „Duplikate löschen“ habe ich glatt übersehen / vergessen … (ich schäme mich! *lach*)
Der Teilnehmer dachte übrigens beim Verknüpfen von zwei Tabellen in einer 1:n-Beziehung an Access, bei dem beim Aktivieren der referentiellen Integrität automatisch überprüft wird, ob alle n-Elemente auf der 1-Seite vorkommen. So einen Haken oder eine Meldung hat er vermisst.
Schöne Frage in der letzten Excelschulung. Ich habe eine Übung erstellt: Dutzende von Fehlern: Bezugsfehler, Formatierungsfehler, falsch Zeichen („x“ statt „*“; „;“ statt „:“, …) ausgeblendete Zeilen, weiße Schriftfarbe, …
Danach erstellen wir eine Pivottabelle. Eine Teilnehmerin fragt, wo Quellen von Rechenfehlern liegen können. Man sieht das Ergebnis einer Summe – aber stimmt es auch?
Ich überlege:
Der Bereich kann falsch gewählt sein
Wird mit Bereichen gearbeitet, kann sich die Pivottabelle beispielsweise auf einen Bereich auf einem falschen Tabellenblatt beziehen
Wird mit intelligenten Tabellen gearbeitet, kann eine falsche Tabelle verwendet worden sein.
Das kann man über Pivottable-Analyse / Datenquelle ändern herausfinden.
Die Pivottabelle wurde nicht aktualisiert.
Die Beschriftung wurde sinnentstellt geändert.
Habe ich etwas vergessen? Sicherlich … Ich fand die Frage sehr interessant …
PowerQuery-Schulung. Ein Teilnehmer sagt, dass er nicht den gesamte Ordnernamen sehen kann und deshalb nicht den richtigen Ordner deselektieren kann.
„Dann schieben Sie halt die Bildlaufleiste nach rechts“, meine ich. „Geht nicht!“
Was ist pasiert?
Wir üben in der PowerQuery-Schulung den Zugriff auf Ordner:
Der Teilnehmer hat die Dateien (auf OneDrive) in einem sehr, sehr langen Ordnernamen abgelegt. Und wirklich: es ist dann leider nicht mehr möglich, die Bildlaufleiste so zu verschieben, dass ich das rechte Ende des Ordners sehen kann:
Die Lösung: Da ich den Text kenne, der am Ende steht, kann ich den gewünschten Ordner auch über „endet nicht mit“ filtern. DAS klappt.
Gestern PowerQuery-Schulung. Wir üben und probieren den Zugriff: Excelmappen, Textdateien, XML, json, die SQL-Datenbank, Ordner, Web, … alles klappt.
SharePoint?
Der Teilnehmer kopiert seinen Sharepoint-Pfad in das Eingabefeld:
Und noch bevor ich sagen kann, dass er sich über das Microsoft-Konto – drei Zeilen darunter – anmelden muss, erhält er eine Fehlermeldung:
Zweiter Versuch: erneute Anmeldung. Das Resultat: sofortige Fehlermeldung ohne die Möglichkeit sich über das „Microsoft-Konto“ anzumelden. Wie gelangt man wieder dort hinein?
Es dauert eine Weile, bis wir es gefunden haben:
Man muss über die Datenquelleneinstellungen den Pfad löschen:
… dann wird man bei der nächsten Anmeldung wieder nach ALLEN Einstellungsoptionen gefragt.
Und ich sage es noch deutlich. Aber er hört nicht.
Listen in Excel sollten eine Überschrift besitzen, wenn man die Listen sortiert und filtert; sie müssen eine Überschrift besitzen, wenn man mit einer Pivottabelle arbeitet.
Der Teilnehmer der Excelschulung hört nicht; erstellt einer Liste, bei der eine Spalte keine Überschrift besitzt:
Das Ergebnis: Excel geht davon aus, dass die Liste keine Überschrift hat und sortiert die erste Zeile ein:
Der Teilnehmer wundert sich.
Man kann es deutlich zeigen, wie Excel diese Liste interpretiert. Die benutzerdefinierte Sortierung zeigt auf, dass keine Überschrift identifiziert wurde:
Nachtrag: bei einer intelligenten Tabelle wäre das nicht passiert. Aber die lernen wir erst später …
Hallo in die Runde, Dies ist mein erster Post. Normalerweise, wenn ich einen Bereich als Tabelle formatiert habe, wurde eine die Formel mit dem Drücken der Enter-Taste automatisch bis zum Ende der Tabelle ergänzt. Seit kurzem funktioniert das nicht mehr. Ich muss wohl irgendwas verstellt haben. Könnt ihr mir bitte sagen, wie ich das zurück stelle? Unter einstellungen ist formel erweitern auf automatisch. Aber das löst das Problem nicht. Ich nutze Excel für Mac. Vielen dank für Eure Hilfe Gruß Stephen
ich hoffe es geht Dir gut! Ich betreue gerade ein ziemlich spannendes Projekt für ein Unternehmen in der Schweiz, dass mich in meinen VBA-Kenntnissen bisher schon ziemlich gefordert aber auch gefördert hat. Jetzt bin ich allerdings an einem Punkt wo ich mit Google und alleinigem überlegen nicht mehr weiterkomme und habe die Hoffnung, dass Du einen Tipp für mich hast.
[…]
Ich habe quasi jeweils eine Liste mit den nach Wunsch aufbereiteten Rohdaten. In dieser soll jetzt an Hand von Daten aus einem Konfigfile (wird wie die Rohdaten über PowerQuery vom Server eingelesen) der entsprechende Filter auf den Verkäufer gesetzt werden und die daraus resultierenden Tabellen in ein neues Dokument exportiert werden. Ist der Vorgang abgeschlossen, kommt der nächste Filter etc. etc. Ich brauche also meines Erachtens einen iterativen Filter der auf Grund eines Kriteriums aus dem Konfigfile erstellt wird.
Leider bekomme ich diesen Part nicht wirklich hin.
Ich hoffe dass meine Mail soweit erstmal nachvollziehbar ist
und dass Du vielleicht eine Idee hast, was ich hier noch machen oder an wen ich
mich noch wenden kann. Ich weiß leider nicht mehr weiter und der Kunde wartet
auf sein Reporttool.
Über eine Rückmeldung von Dir würde ich mich sehr freuen.
Danke Dir und liebe Grüße
Paul
Hallo Paul,
kennst du den
Spezialfilter? Hast du schon einmal den AdvancedFilter in VBA benutzt? Ist
nicht sehr schnell, aber nur eine Zeile Code um eine Liste durch eine
Filterkriteriumsliste zu ziehen.
Ich habe für meine Kollegen zur Budgetplanung 2022 je
Abteilung ein Excel auf Teams eingestellt.
Das Excel enthält eine Power Query Abfrage auf alle
Abteilungs-„Auftragsbücher“, und in PowerPivot ein Datenmodell für die
Beziehungen zwischen den Tabellen.
MAC Benutzer scheinen aber Probleme mit der Datei zu haben (s. Screenshots unten)
Wie kann man die volle Funktionalität der Datei auch für MAC
Benutzer herstellen?
Es wäre super, wenn Du hier einen Rat hast
Vielen Dank und beste Grüße Katrin
Hi Katrin,
1. Antwort: Mac ist nicht meine Welt – ich habe keinen.
2. Antwort: ich weiß, dass der mac lange Zeit nicht
PowerQuery unterstützt hat; soweit ich weiß, kann er das inzwischen.
3. Antwort: der Mac unterstützt (noch) nicht das Datenmodell von Excel.
Es ist unglaublich, aber ich habe wirklich das Gefühl, dass ich in jeden «Sche…sstopf» falle, welchen Microsoft zu bieten hat.
Seit 2 Tagen kämpfe ich mit dem Problem, dass in einer table in jeder Zelle scheinbar versteckte Tabs vorhanden sind. Dies hat natürlich die traurige Konsequenz, dass damit s- oder wverweise auf diese table kläglich scheitern und zu #NV Fehlern führen.
Zum Problem mit Tabs hast Du ja den Artikel tabulatoren | Excel nervt … (excel-nervt.de) geschrieben, doch in meinem Fall hilft mir dieser (wenigstens im Moment) nicht wirklich weiter.
Ich muss dazu vielleicht etwas ausholen und den Vorgang beschreiben, welcher mich zum Problem geführt hat. Am Anfang steht Excel File mit einer table. Diese table wird mittels Power Automate in eine SharePoint Online Liste geschrieben.
In einem anderen Excel File werden die Daten der SharePoint Liste wieder mit einer PowerQuery Abfrage eingelesen und stehen somit wieder in einer table, auf welche ich eben mit dem erwähnten wverweis zugreifen möchte. Der Befehl führt eben zu dem #NV und nach langem Suchen, habe ich letztendlich herausgefunden, dass in der abgefragten table in allen Zellen ein tab steht. Interessanterweise ist es aber so, dass in der table sämtliche Zellwerte linksbündig angezeigt werden. Klicke ich dann bei denjenigen Zellen welche eine Zahl enthalten nicht auf sondern in die Zelle, dann springen die Zahlen nach rechts (ohne dass ich ausser dem Klick in die Zelle etwas anderes mache) Noch verwirrender (wenigstens für mich) ist die Tatsache, dass die Zellformatierungen danach erhalten bleiben. Ich meine damit, dass diejenigen Zellen in welche ich wie beschrieben einmal reingeklickt habe, auch nach einem reload der Power Query Abfrage erhalten bleiben.
Hast Du vielleicht eine Erklärung für dieses Verhalten? Wieso und wann wurden die Tabs in die Zellen geschrieben und gibt keine Möglichkeit dies zu beeinflussen?
Bezugnehmend auf Deine vorherige Antwort ist es aber sicherlich schon so, dass man solche Phänomene auch mit der besten Schulung nicht abwenden kann ☹
Würde mich auf jeden Fall darüber freuen, wenn Du eine Idee zu meinem neuen Problem hättest
Lieber Gruss
Hallo Herby,
das Problem ist mir und vielen anderen bekannt – ich würde
es nicht als Anomalie, sondern als Bug von Excel bezeichnen.
– dort beschreibe ich mehrere Lösungen (mein Liebling ist Daten / Text in Spalten) und auch, wie dieses Phänomen zustande kommt.
Liebe Grüße
Rene
Hallo Rene
Danke für die abermals hilfreiche Unterstützung
Mein Problem schein aber irgendwie anders gelagert zu sein und entgegen meiner
vorherigen Problemschilderung ist es leider nicht so, dass der Fehler mit einem
Klick in eine der betroffenen Zellen «nachaltig» gelöst wird.
Zur besseren Veranschaulichung habe ich eine Kopie der Tabelle erstell, welche
auf der PQ Abfrage beruht. Am Bsp der Zelle B2 kannst Du sehen, dass der
Zellwert nach einem Klick in die Zelle, nach rechts gesprungen ist.
Sobald ich das bei irgend einer benötigten Zelle mache, welche einen Zahlenwert
enthält, springen die Werte nach rechts und die Formeln mit den darauf
referenzierenden Zellen, funktionieren.
Wenn ich hingegen die PQ Abfrage aktualisiere, springen die Zahlen wieder nach
links und die Formeln bringen den #NV
D.h die PQ Abfrage erzeugt die falschen Daten und dabei spielt es
überhaupt keine Rolle, wie die Zellen formatiert sind.
Die Spalten der Daten Quelle (ShareListe) sind ausnahmslos als standard
formatiert und dies lässt sich auch nicht ändern, da innerhalb einer Spalte
unterschiedliche Daten vorhanden sind.
Wie bei Excel gibt es beim PQ unter Transformieren/Bereinigen die Trim
Funktion, mit welcher eigentlich ein tab aus einer Zelle entfernt werden
sollte.
Aber bis dato ist mir dies damit nicht gelungen
Das Problem muss beim erzeugen der Tabelle gelöst werden, da die Daten
dynamisch sind und laufend aktualisiert werden. Oder anders ausgedrückt, eine
neue Abfrage würde die vormals vorgenommenen Korrekturen mir den Daten
überschreiben.
Das File Servicekatalog Quelldaten dient als Datenquelle, das heisst wenn sich
irgendwelche Daten vom Servicekatalog geändert haben, werden diese dort
eingepflegt. Eine Flow schreibt die Daten in die SharePoint Liste, welche dann
wie PQ Abfrage von überall in eine Servicekatalog.xlsx gelesen werden können.
Die Quelldatei hat das Problem auf jeden Fall nicht, d.h entweder auf dem
SharePoint oder bei anschliessenden PQ Abfrage wird ein problematischer tab
angehängt ☹
Vielleicht mache ich einen Denkfehler und/oder Du hast eine Idee, was ich
ändern muss
Lieber Gruss
Hallo Herby,
Das Problem ist Folgendes:
In einer Spalten stehen Zahlen und Texte.
Wird diese Liste nach PowerQuery „gezogen“ und dort der Typ nicht explizit angepasst, so bleiben die Zahlen Zahlen (rechtsbündig) und die Texte Texte.
Verwendet man in PowerQuery jedoch den Datentyp „Text“, dann „schiebt“ Excel unter diese Zahlen ein Textformat (das so nicht sichtbar ist).
Da die Zelle als Standard (oder Zahl) formatiert ist, verschwindet das Textformat beim Editieren (Doppelklick) der Zelle. Andererseits: Nach Aktualisierung von PowerQuery haben wir die gleiche Situation wie am Anfang.
Gegenfrage: Warum MÜSSEN in einer Spalte Zahlen und Texte
stehen? Das widerspricht einem Datenbankdenken.
Und: wenn schon Zahlen – dann sollten sie auch Texte bleiben
– als Informationen und nicht zum Rechnen verwendet werden.
Manchmal amüsieren und erstaunen mich Fragen in Excelschulungen. Beispielsweise folgende:
In einer Liste stehen Email-Adressen. Um herauszufinden, ob einige der Adressen doppelt vorkommen, wird eine Pivottabelle aufgesetzt, die Adressen werden gruppiert und gezählt:
Die Anzahlspalte wird absteigend sortiert.
Eine Teilnehmerin fragt, warum ein Doppelklick auf einen Eintrag (eine Mailadresse) die Möglichkeit bietet, weitere Details einzublenden, während ein Doppelklick auf die Anzahl diese aggregierte Zahl zu „entfalten“, also alle Datensätze anzuzeigen, die sich dahinter verbergen.
Clevere Frage. Und: ich weiß keine Antwort. Ich kann nur vermuten, warum Microsoft das SO eingerichtet hat.
Eine schöne Frage in der letzten PowerBI-Schulung:
Wie viele Funktionen kann man in DAX ineinander verschachteln. „Genug“ lautete meine Antwort. „Sehr viele“, um etwas präziser zu sein. Ich habe gesucht und nicht gefunden. Erstaunlich. Also habe ich ausprobiert. Aber 100 Ebenen habe ich aufgehört:
hoffe, Du hast einen schönen Urlaub ohne großen Regen, aber vielleicht Zeit für eine kleine Knobel-Aufgabe in VBA
Ich habe eine Tabelle, in der alles getan werden darf, d.h. auch gefiltert, aber nur nicht sortiert.
Schutz geht leider nicht, da sich dann leider die Tabelle nicht dynamisch erweitert.
In diesem Artikel steht, dass man mit Hilfe des Events „BeforeSort“ die Sortierroutinen abfangen kann.
Aber leider kriege ich das nicht hin und im Internet habe ich auch nichts gefunden. Weißt Du, wie man dieses Event in Excel platzieren kann? Würde mich freuen.
Hallo Johannes,
ich habe mal ein bisschen gewühlt und probiert:
1. Das SortObjekt existiert – allerdings besitzt es keine
Ereignisse (wie MS behauptet): Das sieht man, wenn man versucht in einem
Klassenmodul einzutragen:
Public WithEvents SO As so…
2. Ich habe überlegt, ob man die Symbole wegnehmen kann. Das
Problem: Man kann über die Registerkarte Start und Daten den Sortierbefehl
aufrufen; über das Kontextmenü oder über die Pfeilchen, die der Filter, die
intelligente Tabelle, die Pivottabelle filtern. Das heißt: es wird sehr mühsam,
dem Anwender die Symbole wegzunehmen.
3. Ich würde alle Zellen auf „nicht gesperrt“ setzen, das Blatt schützen – außer der Sortieroption. Dann kann der Anwender (fast) alles – was er nicht kann, ist beispielsweise einen AutoFilter einschalten.
Excelstammtisch. Hartmut zeigt, dass man das Datenmodell von Excel nach PowerBI importieren kann.
Ich frage, ob er wisse, wann das zu Problemen führt. Und zeige eine Datei:
Darin befinden sich Tabellen, die ins Datenmodell geladen wurden. Mit Hilfe des Datenmodells wurde eine Pivottabelle erstellt. Die Tabellen wurden mit Measures angereichert und sind untereinander verknüpft.
Nun will ich diese Datei (genauer: die Daten, Verknüpfungen und Measures) nach PowerBI importieren:
Ich erhalte eine Fehlermeldung – fast nichts wird importiert:
Wir machen uns auf die Suche – Hartmut wird fündig. Man darf nicht die Daten in Tabellen in der Arbeitsmappe halten und diese ins Datenmodell laden, sondern man muss sie mit PowerQuery importieren. So:
Diese Daten werden nun ins Datenmodell geladen – dort kann man sie verknüpfen
und mit Measures anreichern:
Das Ergebnis:
Ein erneuter Import nach PowerBI Desktop:
Klappt!
Ein Dankeschön an Hartmut Hilbich für das Suchen und Auffinden der Lösung des Importproblems. Hartmut schreibt dazu:
„Das Problem bestand hier (besteht!) darin, dass PBID das Datenmodell selbst sehr wohl importiert, aber nicht gleichzeitig auch die Quelltabellen!
Ich habe die
Quelltabelle mit PQ abgefragt und das PP-Modell exemplarisch mit 2 Measures
versehen. Der Import in PBID funktioniert dann einwandfrei!
Mein Fazit: Es ist nicht
ratsam, die Quelldaten physikalisch gemeinsam mit dem PP-Modell zu speichern.
Also entweder die Daten direkt mit PP abfragen, oder aber (besser) mit PQ
abfragen. Was also innerhalb von PP kein Problem ist, wird dann aber eines beim
Import in PBID.
PowerBI-Schulung. Wir greifen auf Excelmappen zu, die auf Sharepoint liegen. Es kommt die Frage, ob man auch auf einen Sharepoint-Ordner zugreifen kann. Klar kann man:
Man muss nur den Ordnerpfad kopieren und eintragen:
Und – erhält einen Fehler:
Ach, klar, natürlich: man muss sich natürlich noch anmelden. Ist ein bisschen versteckt:
Erstaunlicherweise kann man JETZT OHNE Anmedlung in Excel über PowerQuery auf einen Sharepoint-Ordner zugreifen. Muss ich das verstehen?
Amüsant: ich habe eine große Excelliste mit mehrere Tausend Datensätzen. Ich bearbeite sie in PowerQuery:
Ich importiere eine zweite Liste und verknüpfe sie mit einem Left outer Join:
Das Ergebnis sieht in PowerQuery gut aus:
Ich lade die Tabelle zurück nach Excel und erhalte einen Fehler:
Zurück zu PowerQuery versuche ich einen Right outer Join:
Die Ursache? PowerQuery zeigt nur 1.000 Datensätze. Wenn in der Liste DANACH eine Zelle mit einem fehlerhaften Wert steht, wird er bei einem Left Outer Join nicht angezeigt. Erst in Excel. Natürlich kann man sich in PowerQuery auf die Suche nach dem fehlerhaften Datensatz machen und ihn entfernen. Oder in Excel:
Danke an Christa für diesen Hinweis und danke für die Bemerkung, dass die Fehlermeldung in älteren PowerQuery-Versionen eine andere war:
Gestern habe ich für den Excelstammtisch einige Dateien vorbereitet, um zu zeigen, was PowerQuery verlangsamt. Ich habe eine Liste mit Dummy-Namen mit 20.000 Datensätzen:
Diese verknüpfe ich mit einer Liste, die zwei Zeilen lang ist:
Das Ergebnis: 28.877 Datensätze
Ein zweiter Blick auf die Liste zeigt, dass einige Zeilen (nicht alle!) nun zwei Mal in der Liste auftauchen:
Nein – an der Verwendung eines Primärschlüssels liegt es nicht – die Zeile
Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.
Seit ein paar Tagen heißt es nun „Vom Blatt“
Muss das sein? Ständiges Umbenennen?
Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):
Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:
Das funktioniert auch mit der Funktion SEQUENZ:
Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:
dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …
Gestern auf dem Excelstammtisch stellte Volker folgendes Problem bei/mit intelligenten (dynamischen; strukturierten) Tabellen vor.
Wir haben eine Liste, die zu einer intelligenten Tabelle verwandelt wurde:
Die Tabelle heißt tbl_Planeten.
Auf einem zweite Tabellenblatt wird Bezug auf diese Tabelle genommen; genauer: auf jede Spalte:
=WENNFEHLER(tbl_Planeten[@Planet];"")
Erstaunt stellt man fest, dass der erste Planet – Merkur – fehlt. Die Antwort ist simpel: „@“ bezieht sich auf die Informationen der aktuellen Zeile. Da die zweite Tabelle erst ab Zeile zwei beginnt (die erste fängt in der ersten Zeile an), ist der Bezug versetzt. Man muss also bei Tabellen gleich positionieren. Das birgt Gefahren.
Fazit: besser SO nicht Tabellen miteinander verknüpfen. Es gibt bessere Lösungen: PowerQuery sei an dieser Stelle genannt. Oder relative Bezüge.
Danke an Volker für diesen amüsanten und wichtigen Hinweis!
Ich suche jemanden, der fit in power query und power pivot
ist und dem ich ca. eine stunde lang fragen stellen kann.
Hintergrund: Ich habe einen größeren Auftrag, da geht es um mehrere Tools im Excel-Umfeld, da geht es bei einem Tool jetzt erst mal darum, ob power query da was bringen würde.
Ich hatte Schlimmes oder Schwieriges befürchtet. Aber die zentrale Frage war weder schlimm noch schwierig zu beantworten.
Gegeben sei eine Auftragstabelle mit Verkaufsdaten:
Diese Liste wird nach PowerQuery gezogen und dort bearbeitet. Das Ergebnis wird zurückgegeben:
Die erste Frage lautete: Wie kann man Anfang und Ende als Filter in PowerQuery einbauen?
Die Antwort:
Man muss die jeweils zwei Zellen in eine intelligente Tabelle konvertieren.
Man muss diese ebenso nach PowerQuery importieren.,
Dort den Datentyp in Datum ändern.
Und dort ein Drilldown durchführen. Das heißt: die Tabelle in einen Wert, besser: in eine Variable, verwandeln.
Diese Variable hat einen Namen – er kann verwendet werden.
Also so:
Man schaltet einen beliebigen Datumsfilter ein („Zwischen“):
Und ersetzt in M die beiden Werte durch die Variablennamen:
Fertig! Test in Excel:
Und natürlich kam danach die Frage:
ich möchte die berechnung lieber in PowerPivot vornehmen und mit einer Pivottabelle gruppieren und das Meassure verwenden.
Ich habe jetzt in DAX diesen ausdruck, der funktioniert:
Schon doof. Excel zeigt viele Dinge an, aber leider nicht die Liste der Pivottabellen. Mourad Louha schlägt ein kleines VBA-Skript vor, um die Liste aller Pivottabellen auszulesen:
Public Sub ListPivotTables() Dim c As Long Dim b As Worksheet Dim o As PivotTable Dim St As Worksheet On Error Resume Next Set b = ThisWorkbook.Worksheets.Add c = c + 1 b.Cells(c, 1).Value = "Name" b.Cells(c, 2).Value = "Quelle" b.Cells(c, 3).Value = "Aktualisierung" b.Cells(c, 4).Value = "Arbeitsblatt" b.Cells(c, 5).Value = "Bereich" b.Cells(c, 6).Value = "MDX" For Each St In ThisWorkbook.Worksheets For Each o In St.PivotTables c = c + 1 b.Cells(c, 1).Value = o.Name b.Cells(c, 2).Value = o.SourceData b.Cells(c, 3).Value = o.RefreshDate b.Cells(c, 4).Value = o.Name b.Cells(c, 5).Value = o.TableRange1.Address b.Cells(c, 6).Value = o.MDX Next Next End Sub
Ich habe es ein klein wenig überarbeitet. Das Ergebnis:
Oder man benennt die Datei mit der Endung ZIP um, entpackt das Archiv und wirft einen Blick in die beiden Ordner pivotCache und pivotTable. Darin verbergen sich sämtliche Informationen zu den Pivottabellen:
Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:
IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:
Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …
Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!
Excelschulung. Ich erkläre und zeige (intelligente, dynamische, formatierte, strukturierte) Tabellen:
Ein Teilnehmer behauptet, dass diese Tabellen seine Überschriften löschen würden. Ich bin irritiert. Ich habe eine Weile gebraucht, um zu verstehen, dass die weiße Schriftfarbe, die Excel als Standard einsetzt, nicht sehr clever ist bei einer gelben Hintergrundfarbe …
Natürlich ist die Überschrift noch vorhanden. Nur eben – sehr schlecht lesbar!
In der letzten Excelschulung haben wir über Listen gesprochen. Beim Sortieren von Daten kann man nach Zeilen und Spalten sortieren. Man findet diese Einstellung in den Sortieroptionen:
Prompt kam die Frage, ob man auch nach Spalten filtern könne.
Zuerst überlegte ich, ob dies überhaupt sinnvoll sei.
Die korrekte Antwort lautet: Nein! man kann den Autofilter nicht neben der ersten Spalte einschalten. Man muss die Liste transponieren:
Dann kann man den Filter für die erste Zeile einschalten, die in der ursprünglichen Tabelle die erste Spalte darstellte:
Und so ergeben sich sicherlich einige Anwendungsbereiche.
ich arbeite an einem VBA – Projekt, dass aus Power BI Dateien die
Metadaten rauslesen soll.
Das Auslesen geschieht über Power Query (what else….), aber ich
muss noch ein paar Prüfungen mit VBA erstellen und insbesondere die Power Query
Abfragen on the fly erstellen. Letzteres geht problemlos.
Der Ablauf:
Prüfe, ob User die pbix geöffnet hat.
Falls nicht, bitte freundlich darauf aufmerksam machen
Falls nein, Abbruch – falls ja, pbix öffnen.
Bis dahin klappt alles.
Nun kommt der Punkt, wo der Benutzer sich gegenüber der Power BI
Datei authentifizieren muss, nachdem er ja gesagt hat.
falls er aber den Dialog hier abbricht (…..DAU…….), kommt
eine „schöne“ Meldung:
Nun meine Frage:
Wie kann ich hier meine eigene Meldung einbauen und vor allem, wie
fange ich das ab?
Bin schon voller Zweifel…..
Merci, lieber René für deine Geduld mit mir
Freundliche Grüsse Hans Peter
########################################
die unwissenden erleuchten sich selber
habs gefunden. nach Drücken von „Senden“ fiel es mir wieder ein, da stand was im Buch von René
Erstaunlich! Auf dem letzten Excelstammtisch, den Frank Arendt-Theilen organisiert hat, hat er angemerkt, dass die PowerQuery-Funktion Date.WeekOfYear, die man über Spalte hinzufügen / Datum / Woche / Woche des Jahres nach dem US-amerikanischen Modell rechnet. Zwar verfügt Excel seit vielen Versionen über die Funktion ISOKALENDERWOCHE, welche die KW korrekt nach ISO 8601 berechnet. Auch Outlook unterscheidet bei den Kalenderwochen zwischen USA und Europa. Jedoch nicht PowerQuery. „Haben die das vergessen?“, fragt Frank. Also muss man diese Funktion in PQ nachbauen …
Vorgestern habe ich auf dem Excel-Meetup einen Vortrag über Zahlenformate in Excel gehalten. Nach dem Vortrag schreibt Tanja:
Hallo René
Danke für
deinen Vortrag. War ein sehr guter Überblick.
Wieso hast
du das Problem mit den Monaten über Hilfsspalten und nicht über die Gruppierung
des Datums gemacht?
Dann wäre
es ja ganz einfach die fehlenden Monate anzuzeigen.
Sonnige Grüsse aus der Schweiz
Hallo Tanja,
ich freue mich über Mails, die beginnen mit „warum hast du
nicht …?“ Es gibt immer drei Möglichkeiten:
* entweder ich habe etwas übersehen – und es gibt wirklich
elegantere Lösungen
* oder es gibt mehrere Lösungen; ich habe mich für die eine
entschieden, weil …
* oder meine Lösung ist bewusst gewählt, weil …
Ich habe gestern Abend noch einmal nachgedacht:
[und dann folgt eine lange Erklärung, warum ich es nicht so gemacht habe, wie sie es vorschlägt. Das soll an dieser Stelle nicht wiederholt werden.]
Liebe Grüße aus dem verregneten München
René
PS: Beim Durchschauen habe ich gerade bemerkt, dass die Option „Elemente ohne Daten anzeigen“ nicht aktivierbar ist, wenn das Datenmodell eingeschaltet ist. Wusste ich nicht …
Hallo Rene
Danke für deine Erläuterungen. […]
Ich kann mir vorstellen, warum das nicht aktiv sein kann. Beim Zusammenspiel mit dem Datenmodell kann ich ja auch nicht in der Pivot-Tabelle nach einem Datum gruppieren. Wenn ich es sauber aufbaue, brauche ich dazu im Datenmodell ja eine Kalendertabelle und dort kann das Problem mit fehlenden Jahren oder Monaten gar nicht auftauchen, da eine Kalendertabelle ja immer den kompletten «Datumsbereich» enthalten muss.
Übrigens: wer meinen Vortrag ansehen möchte – man findet ihn auf:
In einer großen Tabelle (~17.000 Zeilen) muss ich ein
einer Spalte eine Rechtschreibkontrolle durchführen. Hier scheinen „Alle
ignorieren“ und „Immer ändern“ nicht zu funktionieren – wenn ich
Fehler mit diesen Schaltflächen bearbeite (um z. B. ein bestimmtes Wort aus der
Schweizer Rechtschreibung in die deutsche Standard-Rechtschreibung
umzuwandeln), werden sie mir bei ihrem nächsten Vorkommen wieder angezeigt,
statt (wie erwartet) einmal komplett durch die Spalte zu gehen und alle
Vorkommnisse dieses Fehlers zu beheben. Dies bremst mich natürlich erheblich
aus. Gibt es einen Trick, damit die Excel-Rechtschreibkontrolle so effektiv
funktioniert wie in Word, oder muss ich mit diesem Problem leben? Und wurde das
Problem eventuell in Excel 2019 behoben?
Ich danke für eure Antworten!
Hallo Herr P.,
Ich habe es gerade bei einer Liste mit 300.000 Zeilen probiert – in meinem Excel in Microsoft 365 läuft [alles ignorieren] prima durch: der Fehler „Huerth“ wird in Zeile 299.991 gefunden, in der übernächsten Zeile übergangen.
Fehler wird gefunden – „Alle ignorieren“Dann wird der Fehler nicht mehr gefunden
Aber ich kann die Frage gerne mal posten – vielleicht hat jemand ein ähnliches Problem.
Liebe Grüße
René Martin
Hallo Herr Martin,
vielen Dank für Ihre Rückmeldung. Es könnte wirklich ein Problem meiner „älteren“ Version von MS Office sein.
In jedem Fall freue ich mich schon auf Ihre Rückmeldung.
Liebe Grüße
Liebe Leserin, lieber Leser,
gerne dürfen Sie einen Kommentar abgeben, wenn SIE dieses Problem in Excel haben / kennen. Wir würden uns freuen.
Und: Die Namensliste, die Sie oben sehen, habe ich mit dem fakename-Generator erzeugt:
ich bin durch Zufall auf Ihren Blog aufmerksam geworden.
Leider hat Excel immer wieder merkwürdige Verhaltensweisen.
Warum auch immer.
Ich habe auf einem Rechner ein Problem mit der Funktion Daten
„Abrufen und transformieren“
Ich kann auf dem PC keinerlei Daten über diese Funktion aufrufen.
Keine CSV noch eine Tabelle aus einem SQL Server.
Ich bekomme immer wieder die Fehlermeldung, dass die
Initialisierung der Daten fehlgeschlagen sei.
Lade ich die CSV auf einem anderen Rechner ein, funktioniert dies
Problemlos.
Gleiches gilt bei der SQL Abfrage.
Haben Sie noch eine Idee wo ich das Problem suchen kann?
In der PowerQuery kann ich die Daten sauber sehen.
Nur das Einfügen der Daten aus der Verbindung in das Tabellenblatt
funktioniert nicht.
Vielen Dank für Ihre Zeit. Mit besten Grüßen,
####
Ich habe es mir gerade angesehen: Excelversion 2016.
Ich erstelle eine intelligente Tabelle, ziehe sie in PQ, klappt; ich bearbeite, ich tue, ich mache, M-Code ist vorhanden – klappt.
Ich lade sie als
Verbindung. Klappt.
Aber sobald ich die Daten als Tabelle „sehen“ / „haben“ möchte – knallt es:
Kennt jemand diesen Fehler?
Initialisierung der Datenquelle schlug fehl. Überprüfen Sie den Datenbankserver oder kontaktieren Sie Ihren Datenbankadministrator. Vergewissern Sie sich, dass die externe Datenbank verfügbar ist, und wiederholen Sie den Vorgang. Sofern diese Nachricht wieder erscheint, erstellen Sie eine neue Datenbankquelle um mit der Datenbank Verbindung aufzunehmen.
Ich hätte da nochmal eine Frage zu den Matrixfunktionen.
Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der
auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den
Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn
zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.
Gruß
Hannes
Hallo Hannes,
du musst den Namen im INDIREKT in einen Bezug umwandeln.
Meinst du das?
Liebe Grüße :: Rene
Hallo Rene,
ja genau, da sind einige Haken drin.
Die Liste in der Datenüberprüfung übernimmt offensichtlich
nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine
Rückgabewerte aus Funktionen.
Aber wenn ich innerhalb einer „formatierten“ Tabelle einen
benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und
die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.
Wenn ich über die Sequenz-Funktion Werte ermittle und die in
einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben,
innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.
Die Problematik ist wahrscheinlich, dass die
Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher
auch die Bezugsherstellung mit der Indirekt-Funktion.
Keine Ahnung, ob man das irgendwie austricksen kann.
Gruß
Hannes
PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte
sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:
Und dann passiert:
Hallo Johannes,
eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also: =[@Umsatz]*19% oder: =SUMME(Tabelle1[Umsatz]) analog: =TEILERGEBNIS(109;[Umsatz])
DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.
Moin Rene,
Danke Dir für die ergänzenden Infos 🙂 Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂
also, es geht um die Funktion „Text in Spalten“. War
eigentlich eine Frage eines Kollegen, die ich selbst nicht beantworten konnte.
Ich habe eine CSV-Datei, in der die Werte auch wirklich durch Kommas getrennt sind:
Jetzt ist es so, dass auch innerhalb der Texte, die zu Zellinhalten werden sollen, Kommas drin sind, wie z.B. in Zeilen 1 und 2. Wenn ich jetzt „Text in Spalten“ mit der Option „Getrennt“ auswähle, dann packt Excel natürlich den Teil rechts vom Komma in eine neue Zelle:
Gibt’s ne Möglichkeit, das mit „Text in Spalten“ sauber zu
trennen? Mit PowerQuery krieg ich es hin, also saubere Trennung + korrektes
Anzeigen von Umlauten, ich frag mich nur, ob das „Text in Spalten“ nicht auch
irgendwie kann.
Danke dir und viele Grüße, Dominic
####
Hallo Dominic,
ich denke die Frage hast du schon selbst beantwortet:
* PowerQuery
* VBA
* Formeln (uff!)
Wenn der Anwender Zeichen in seinen Texten verwendet, die
als Trennzeichen vorgesehen sind; kann der Assistent Daten / Text in Spalten
das nicht abfangen. Wie wir wissen: die Grenzen von „Text in Spalten“ wurden ja
in PowerQuery erweitert – da geht mehr …
Liebe Grüße
Rene
PS: Man könnte die Blitzvorschau versuchen; ist aber mühsam und wahrscheinlich funktioniert auch das nicht.
Die Teilnehmerin der Excelschulung überlegt sich für jede Kategorie ein eigenes Tabellenblatt zu erstellen. So kann sie es leichter als PDF speichern und verschicken.
Ich überlege, ob die Berichtsfilterseiten ein geeignetes Werkzeug sein könnten. Ich erstelle eine Pivottabelle und achte darauf, dass die Daten NICHT zum Datenmodell hinzugefügt werden. Sonst kann man keine Berichtsfilterseiten erstellen:
Dennoch: der Befehl „Berichtsfilterseiten erstellen“ ist ausgegraut …
Ich habe eine Weile überlegt. Dann fiel mir ein: damit ich Berichtsfilterseiten erstellen kann, muss ich in der Pivottabelle einen Filter erstellen:
Die Daten, die auf den einzelnen Tabellenblättern dargestellt werden sollen, müssen als Filter vorliegen.
Tatsächlich! JETZT geht es!
Also: zwei Voraussetzungen, um Berichtsfilterseiten zu erstellen :
Beim Erstellen der Pivottabelle NICHT dem Datenmodell hinzufügen!
In der letzten Excelschulung waren wir erstaunt. Wir verknüpfen mehrere Tabellen miteinander:
Warum dauert das Verknüpfen der Daten in PowerQuery so lange?
Die Ursache war schnell gefunden: die Teilnehmerin hatte den Cursor nicht in die Liste gesetzt und so aus der Liste eine (intelligente) Tabelle erzeugt, sondern die ganzen Spalten markiert und dann diese (mit den leeren Zeilen) in eine Tabelle umgewandelt.
Der Anfang der Tabelle:
Und das Ende:
Als wir den Fehler entdeckt hatten, wollte die Teilnehmerin den Bereich „per Hand“ nach oben ziehen:
Ich werde nervös, wenn Aktion SOOO lange dauern. Ein kurzer Blick … das muss doch schneller gehen … und wirklich: es geht schneller. Das Werkzeug „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ bietet eine schnelle Möglichkeit Tabellen zu vergrößern und verkleinern. Man muss nur $A$1:$E$2156 tippen – und schon ist die Tabelle kleiner. Und PowerQuery schneller!
Grrrr. Eine intelligente Tabelle. Ich möchte eine Zeile löschen. Aus Gewohnheit setze ich den Cursor in eine Zelle:
drücke [Strg] + [-] (und normalerweise bewege ich die Auswahl mit der Pfeiltaste nach unten. Was passiert? Es wird gelöscht, der Bildschirm wird nach oben verschoben, so dass ich nicht sehe, was gelöscht wurde:
Und richtig: die Spalte wurde gelöscht! Grrrr … Muss man höllisch aufpassen …. Grrrrr
Verblüffend! Kennen Sie das? Ich habe ein Tabellenblatt mit einer intelligenten Tabelle. In der Arbeitsmappe wird ein Name oder mehrere Namen definiert, die Bezug auf diese intelligente (dynamische, formatierte, strukturierte) Tabelle nehmen:
Auf einem zweiten Tabellenblatt wird mit einer Formel (oder einer Datenüberprüfung) Bezug auf die Tabelle genommen:
Wird nun dieses Tabellenblatt dupliziert, werden auch die Namen dupliziert – sie liegen nun als Arbeitsmappennamen und Tabellenblattnamen vor:
Dupliziert man nun ein weiteres Mal das Duplikat, erhält man die Frage, „wohin mit den Namen“:
Der Name ist bereits vorhanden. Klicken Sie auf „Ja“, um diese Version des Namens zu verwenden, oder klicken Sie auf „Nein“, um die Version, die Sie verschieben oder kopieren möchten, umzubenennen.
Ich möchte den Namen nicht verschieben oder kopieren!
Und dann? Ist der Name drei Mal vorhanden … einmal als Arbeitsmappenname und zwei Mal als Tabellenblattname.
Erstaunlich. Ich programmiere eine Access-Datenbank, die Daten nach Excel exportiert und aufbereitet.
Die Excel-Datei liegt im XLS-Format vor und wird am Ende als XLSX gespeichert. Ich muss die Liste nach vier Kriterien sortieren. Deshlab verwende ich den „neuen“ Sortierbefehl, der seit Excel 2007 vorhanden ist:
i = xlBlatt.Range("I1").CurrentRegion.Rows.Count
xlBlatt.Sort.SortFields.Clear
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("I2:I" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("J2:J" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("M2:M" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
xlBlatt.Sort.SortFields.Add2 Key:=xlBlatt.Range("K2:K" & i), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With xlBlatt.Sort
.SetRange xlBlatt.Range("I1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' -- sortiere, damit man zählen kann
Klappt. Klappt bei meinem Excel in Microsoft 365. Allerdings beim Kunden, der Office 2016 einsetzt – leider nicht:
Schade! Also verwende ich den „alten“ Sortierbefehl. Da er jedoch nur drei Sortierkriterien zulässt muss ich zwei Mal sortieren:
Die Originaldatenquelle ändert sich – sie wird kleiner. Die Verbindung wird aktualisiert:
Die Folge: Die Formel wird angepasst, beispielsweise in:
=KKLEINSTE($F$2:$F$15;ZEILE(A1))
Fehlermeldungen sind die Folge.
Ändert sich die Liste erneut und wird nun länger, werde diese Bezugsfehler natürlich nicht korrigiert …
Fehler in der Berechnung sind die Folge.
Heißt: Wenn schon (intelligente/formatierte/dynamische) Tabellen – dann bitte die Bezüge auf diese Tabellen in Tabellenschreibweise und nicht in Bezugsschreibweise! Sonst gibt es Ärger!
Wer mit Datenbanken arbeitet, der würde in dieser Liste sofort eine n:m-Beziehung erkennen: einer Rebsorte sind mehrere Länder zugeordnet. In einem Land werden es mehrere Rebsorten angebaut. (zugegeben: das Original-Beispiel, das mir die Teilnehmerin zeigte waren Firmendaten aus dem Bereich firmeninterne Weiterbildungen – aber die Struktur war die gleiche)
Auch wenn die Länder durch einen Umbruch in eine Zelle eingefügt wurden, kann man sie mit dem Assistenten „Text in Spalten“ oder mit PowerQuery trennen.
Allerdings sollen nun Zuordnungen zu den Ländern getroffen werden. Das heißt: in einer Zeile steht ein Land oder mehrere Länder NEBENEINANDER.
Zum Glück stellt PowerQuery den Assistenten entpivotieren zur Verfügung:
Damit werden nicht nur die Zeilen n Mal wiederholt, sondern die zugehörigen Länder stehen auch UNTEREINANDER, was zur Weiterverarbeitung (SVERWEIS oder was auch immer) hervorragend geeignet ist:
Sind mehrere Texte in einer Zelle untereinander geschrieben, kann man sie mit dem Assistenten Daten / Text in Spalten trennen.
Und die Blitzvorschau? Ein Versuch ist es wert:
Sieht gut aus – jedoch:
Okay zu lang. Na ja! Und der zweite Wert? Versagt, weil in der zweiten Zeile nur ein Wert steht. Die Anzahl der Werte, die sich untereinander befinden, ist unterschiedlich groß:
Auf ein Neues: PowerQuery. Seit einigen Versionen stellt PowerQuery – anders als der Assistent „Text in Spalten“ als Trennzeichen den Zeilenumbruch zur Verfügung:
Splitter.SplitTextByDelimiter(„#(lf)“
Klappt! Klappt hervorragend!
Können Sie M? Da wir wissen, dass der Zeilenumbruch in Excel dem Wert 10 entspricht hätte man auch verwenden können:
Character.FromNumber(10)
Also:
„Split Column by Delimiter“ = Table.SplitColumn(Source, „Anbauländer“, Splitter.SplitTextByDelimiter(Character.FromNumber(10), QuoteStyle.Csv), …
Mit der Tastenkombination [Strg] + [J] kann man in den Dialogen „Suchen“ und „Text in Spalten“ ein Zeichen erzeugen, das den manuellen Zeilenumbruch repräsentiert. Ein blinkender Punkt ist die Folge. Allerdings lässt der Assistent „Text in Spalten“ nur ein Zeichen zu, so dass man nicht mehr in das Eingabefeld klicken kann und ein anderes Zeichen eintragen kann, wenn der Assistent ein zweites Mal Daten trennen soll:
Erste Lösung: in das Eingabefeld klicken. Das Zeichen befindet sich nicht vor dem Cursor, sondern dahinter. Also mit der Taste [entf] löschen und nicht mit Backspace (Rückschritt)
Zweite Lösung: „mit Anlauf“. Man kann das Kontrollkästchen „Andere“ aktivieren und dann mit der Tabulatortaste in das rechts neben stehende Textfeld springen.
Ich finde den blinkenden Punkt ein wenig unglücklich gewählt als Repräsentant des Zeilenumbruchs. Man sieht ihn schlecht oder gar nicht.
Neben dem Assistenten „Text in Spalten“ – gibt es noch weitere Möglichkeiten am Zeilenumbruch zu trennen? Morgen mehr …
In mehreren Zellen befinden sich durch einen Zeilenumbruch getrennte Daten. Sie sollen in eigene Spalten geschrieben werden. Man kann den Zeichenumbruch (ZEICHEN(10)) durch ein anderes Zeichen ersetzen, das dann im Assistenten Daten / Text in Spalten verwendet wird.
Oder – die Teilnehmerin, die mir diese Frage stellte, kannte die Antwort – man kann die Tastenkombination [Strg] + [J] nicht nur im Suchen-Dialog, sondern auch in diesem Assistenten verwenden.
Angezeigt wird (auch hier) ein blinkender Punkt. Er repräsentiert das Zeichen für den Zeilenumbruch. So kann man auch die Daten trennen:
Diese Lösung hat allerdings einen klitzekleinen Haken, den ich morgen verraten werde.
Es fing ganz harmlos an. Excelschulung. Genauer: vor der Excelschulung. Eine Teilnehmerin rief mich zu sich. Sie hat eine Liste, bei der Daten mit einer anderen Liste verknüpft werden sollen. Nichts leichter als !?!
Die „Anbauländer“ (Spalte H) sollte mit einer anderen Liste verknüpft werden. Ein genauer Blick auf die Daten lieferte folgendes Ergebnis: In Spalte H stand nicht nur ein Land, sondern manchmal eines, manchmal mehrere. Untereinander …
Mein erster Gedanke war: man kann den Zeilenumbruch durch ein anderes Zeichen ersetzen und dann damit trennen. Dass der Zeichenumbruch in Excel der Zahl 10 entspricht, weiß man, oder kann man leicht ermitteln mit:
=CODE(TEIL(H2;LÄNGE(„Deutschland“)+1;1))
Also wird gewechselt:
Anschließend markiert, kopiert und mit Daten / Text in Spalten getrennt. Voilà.
Die Teilnehmerin hatte eine bessere Idee. Ich verrate diesen Vorschlag morgen.
Hallöchen René, Ich habe eine Frage zu Excel, ich hatte eine adresstabelle mit Kunden aufgelistet und habe die schon sortiert und alle Kunden vor 2017 aussortiert, nur hat die Liste jetzt aber vorne die Zahlen nicht aktualisiert. Heisst an erster Stelle steht nicht wie gewohnt die „1“ sondern die „16“ gibt es eine Funktion um die durchnummerierung zu erneuern? Vielen Dank im Voraus Julie
Hallo Julie, du willst die Liste neu nummerieren lassen, oder? Die Antwort: Entweder die schreibst die erste Zelle eine 1, in die zweite eine 2, markierst beide und ziehst nach unten:
Oder du verwendest die Funktion ZEILE() – sie liefert die Zeilennummer. Damit nun in A2 die Zahl 1 steht, schreibst du: =ZEILE()-1
Dann kann man ganz links die Zahlen nicht ändern? Haben die Tabelle jetzt einfach kopiert und neu eingefügt in einer leeren Liste.
Ach – du willst die Zeilennummern ändern? – Das geht leider nicht! Gegenfrage: warum willst du die Nummern ändern? Jede Zelle hat einen festen Namen, beispielsweise C4, A3, R47 …
Nun – ich will wissen, wie viele Namen noch in der Liste stehen.
Ah, verstanden. Na – da kannst du entweder nach dem Filtern unten links in der Statuszeile nachschauen – dort wird die Anzahl der gefilterten Daten angezeigt:
oder setzt unter (oder neben oder über) die Tabelle die Funktion TEILERGEBNIS – sie zählt nur die sichtbaren Zellen, wenn du den Parameter 2 oder 3 verwendest:
Sieht wie eine intelligente (dynamische, formatierte, strukturierte) Tabelle aus. Ist es aber nicht. Nur die obere Hälfte. Die untere Hälfte ist „nur“ formatiert. Keine Tabelle, wie man leicht feststellen kann:
seit einiger Zeit habe ich ein Problem mit Pivot-Tabellen und wüsste gern, ob Du mir vielleicht helfen kannst.
Früher habe ich häufig die Funktion Berichtsfilterseiten anzeigen genutzt, aber irgendwie funktioniert es nicht mehr. Mir wird das Feld immer ausgegraut angezeigt.
Ich weiß nicht woran es liegt. Dr. Google konnte mir erstaunlicherweise auch nicht helfen, ich bin komplett ratlos.
Ich erzeuge regelmäßig Auswertungen für acht verschiedene Regionen und würde die Funktion dafür gerne verwenden, sodass jede Region ihren eigenen Reiter hat.
Ich hab mal eine Mini-Beispieldatei angehangen.
Es wäre toll, wenn Du das Brett von meinem Kopf entfernen könntest!
Liebe Grüße aus Köln,
Sabrina
Hallo Sabrina,
ich glaube, das ist eine einfache Frage:
Berichtsfilterseiten werden nur dann angeboten, wenn die Daten NICHT den Datenmodell hinzugefügt wurden!
Ich bereite gerade eine Excelschulung vor. Dabei bin ich über einen Artikel von Andreas Thehos gestolpert. Er beschreibt folgenden Bug in Excel:
Angenommen man hat eine Tabelle mit Berechnungen und wandelt sie in eine intelligente Tabelle um. Dabei werden in Bezügen der eigene Tabellenblattname genannt.
Sortiert man nun diese Tabelle, beispielsweise nach Spalte C (Radius), so wird die berechnete Spalte nicht mitsortiert.
Für „normale“ Listen habe ich diesen Bug hier auf excel-nervt.de schon mehrfach beschrieben. Das Erstaunlich ist, dass dieses Phänomen auch bei intelligenten Tabellen eintritt, die doch eigentlich die Aufgabe haben sollten, die Zeilen zusammenzuhalten.
Ist euch das schon aufgefallen? Wenn auf mehreren Tabellenblättern (intelligente, dynamische, strukturierte, formatierte) Tabellen liegen, kann man nicht mehrere Tabellenblätter gleichzeitig kopieren.
Zum einen finden sich dort verwirrende Denglish-Erklärungen. Zum andere erstaunen Aufzählungen. Die trigonometrische Kreisfunktion TAN wird den statistischen Funktionen zugeordnet. Ups!
Aber des Rätseln Lösung ist schnell gefunden: es gibt eine Schaltfläche, mit der man ausgeblendete Spalten und Tabellen anzeigen lassen kann oder „echt“ ausblenden lassen kann:
Übrigens: nicht nur in der Diagrammansicht, sondern auch in der Datenansicht!
Seltsam. Ich erstelle eine Pivottabelle, bei der ich mehrere Tabellen miteinander im Datenmodell verknüpft habe. Schon der erste Blick zeigt, dass das nicht korrekt sein kann:
Ein Blick in die Diagrammansicht von PowerPivot gibt Antwort: eine Verbindung wurde auf inaktiv gesetzt:
Man sieht es auch in der Feldliste der Pivottabelle:
Will man in einer Tabelle in PowerPivot mehrere Spalten ausblenden, muss man sie einzeln markieren., wenn sie nicht nebeneinander liegen. Man kann sie dort nicht mit gedrückter [Strg]-Taste markieren.
Wechselt man jedoch von der Datensicht in die Diagrammsicht, ist es ohne Weiteres möglich mit gedrückter [Strg]-Taste mehrere Spalten zu selektieren.
Kennst du den Spezialfilter (Daten / Sortieren und Filtern / Erweitert)? Er erlaubt beispielsweise Unikate aus einer Liste herauszufiltern:
Der dahinterliegende VBA-Befehl lautet AdvancedFilter. Er ist ein Befehl (eine Methode des Zellbereichs) und einfach zu programmieren. Okay – er ist – bei großen Datenmengen – nicht sehr schnell – aber für „Kleinigkeiten“ sehr gut geeignet:
Dim xlBlatt As Worksheet
Dim xlQuellbereich As Range
Dim xlZielZelle As Range
Set xlBlatt = ActiveSheet
Set xlQuellbereich = xlBlatt.Range("A1").CurrentRegion
Set xlZielZelle = xlBlatt.Range("E1")
xlQuellbereich.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=xlZielZelle, Unique:=True
Anschließend kann man Spalte A:D löschen; die Ergebnisse sortieren, weiter verarbeiten …
Was passiert allerdings, wenn mein System keine Daten liefert? Eine leere Datenquelle:
Die Antwort: DANN läuft der Spezialfilter an die Wand! Also: aufpassen! Vorher überprüfen, ob WIRKLICH Daten vorhanden sind (xlQuellbereich.Rows.Count > 1). Sonst passiert das:
Einmal links, einmal rechts – da muss ich jedesmal hinschauen. Kennt ihr das? Ich habe eine Datei erstellt oder geöffnet und modifiziert und möchte die Datei oder die Applikation schließen OHNE zu speichern. Sei es, weil ich etwas ausprobieren wollte, weil ich nur drucken wollte, weil die Änderungen falsch waren, weil Excel volatile Funktionen neu berechnet hat… Es gibt eine Reihe von Gründen. Also: schließen/beenden OHNE ZU SPEICHERN. Wohin klicken? „Nicht speichern“ – rechts:
Eben: „Nicht speichern“ – rechts:
Ups: noch ein Button mehr:
Hier auch:
Oder ich muss genau hinschauen, um zu verstehen, dass „NEIN“ „nicht speichern“ bedeutet:
In PowerQuery muss ich wieder rechts klicken – allerdings: „Verwerfen“:
Und hier: richtig: links klicken! Damit das Hirn wieder funktioniert! Die Aufmerksamkeit auf den Text gelenkt wird! Bloß keine Langeweile oder Gewohnheit aufkommt:
Unabhängig
davon möchte ich Ihnen nochmals für die sehr gelungene Onlineschulung am Freitag
danken.
Sehr
praxisnah und super erklärt! Ich freue ich schon auf PowerBI im September.
Bei dem File gibt es noch ein kleines Problem
Formula.Firewall: Abfrage ‚Tabelle 1‘ verweist auf andere Abfragen oder Schritte und kann daher nicht direkt auf eine Datenquelle zugreifen. Erstellen Sie diese Datenkombination neu.
Ich kann mir dies im Moment nicht im Detail ansehen (Pfad scheint korrekt, Files vorhanden), werde aber am Nachmittag nach Anleitung versuchen, dies „nachzubauen“.
Könnte ein Kompatibilitätsproblem sein (Excel-Version). Bekomme vor öffnen von Power Query diese Meldung:
Kompatibilitätswarnung: Die Abfragen in dieser Arbeitsmappe sind u.U. nicht mit Ihrer aktuellen Version von Excel kompatibel.
Hallo Herr Martin,
Ihre
Liste funktioniert doch – es waren meine Sicherheitseinstellungen.
#“Added Index“[Artikelname] ist die Spalte aus der die
Daten gruppiert werden.
[Index] – ab dieser Position wird gruppiert
],[#“Artikel (2).Anzahl“] – so viele Elemente werden
gruppiert
Steht ein Wert beispielsweise drei Mal in der Liste, können die letzten zwei Werte gelöscht werden. Dies erreicht man mit dem Befehl „Duplikate entfernen“, den Sie in Home / Zeilen verringern / Zeilen entfernen finden.
Amüsant. Während Inquire auch ausgeblendete Blätter anzeigt (sowohl hidden als auch very hidden)
zeigt PowerQuery nut die sichtbaren Blätter an:
Nein! Nicht ganz. Wenn man die Daten nun transformiert und zurück zur Source wechselt, so sieht man auch dort alle Blätter – egal ob sichtbar oder ausgeblendet:
Danke an Dominique Dauphin für diesen wertvollen Hinweis!
Ich habe eine Tabelle. Ich lege einen Druckbereich fest. Ich möchte, dass Menschen zwar die Tabel drucken dürfen, aber die Daten nicht stehlen, also nicht einfach rauskopieren.
Also wird ein Schutz auf das Tabellenblatt gelegt.
Wir wissen, dass PowerQuery von Dateien Tabellenblattnamen, Namen und intelligente Tabellen anzeigt. Da der Druckbereich als Name bespeichert wird, wird er angezeigt.
Kann geladen werden und nach Excel zurückgeschrieben werden.
Und schon hat man Zugriff auf die Faten. Ohne die Datei zu entzippen, das Protect-Element zu löschen, …
das ist sehr spannend, was da passiert. Ich habe die Ursache gefunden:
Die Überschrift Ihrer Tabelle ist länger als 255 Zeichen.
Sie speichern die Arbeismappe. Sie greifen mit PowerQuery auf diese Datei zu. PQ greift mit der Zeile
= Table.TransformColumnTypes(#“Höher gestufte Header“,{{„ID“, Int64.Type}, {„Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet.“, type number}})
darauf zu. Wir schreiben die Tabelle als intelligente Tabelle zurück nach Excel. Tabellen dürfen maximal 255 Zeichen in der Überschrift haben. Excel schneidet die restlichen Zeichen ab.
Soweit klappt de Workflow. Wenn ich allerdings diese Datei schließe und öffne, erhalte ich die Fehlermeldung.
Die Ursache: die Überschrift endet jetzt nicht mehr auf „dolor sit amet“.
Eine weitere Fehlermeldung ist die Folge:
Sehr spannend. Lösung: Mit einer intelligenten Tabelle arbeiten! Dann ist die maximale Anzahl der Buchstaben der Überschrift begrenzt.
Ich greife auf eine intelligente (formatierte/dynamische/strukturierte) Tabelle zu. Ich möchte in mehreren Zellen die gleiche Formel stehen haben, die ich danach leicht modifizieren kann. Also kann ich mehrere Zellen markieren und die Formel mit [Strg] beenden. Dann steht in allen Zellen die gleiche Formel, beispielsweise:
=SUMME(Tabelle1[Betrag]/Tabelle1[Prämienpunkte])
Wenn ich allerdings Formeln der Gestalt:
=[@[Quartal 1]]/@HP[[Gesamt]:[Gesamt]]
habe – also: die Spalte „Quartal 1“ wird verschoben, die Spalte „Gesamt“ bleibt fest – dann wüsste ich keine Möglichkeit, dies durch geschickte Tastatureingaben zu erzeugen. Die doppelte Klammer muss getippt werden. Oder? Wer kennt eine Lösung?
Amüsant. Ist Ihnen das schon aufgefallen? Wenn ich in einer intelligenten (dynamischen/formatierten/strukturierten) Tabelle eine Überschrift lösche, wird sie – je nach Spracheinstellung – durch eine andere (beispielsweise „Spalte1“) ersetzt:
Wenn Sie allerdings eine Überschrift eingeben, die länger ist als 255 Zeichen, wird dieser Text gelöscht. DFie maximale Anzahl der Zeichen einer Überschrift beträgt 255 Zeichen.
Das hat Auswirkungen auf PowerQuery, der Daten als intelligente Tabelle zurückgibt. Ist eine Überschrift länger, wird der restliche Text abgeschnitten. Werden gleiche Überschriften importiert, werden sie durchnummeriert.
Excelschulung. Wir üben Tabellen: sortieren, filtern und Pivottabellen. Wir erstellen die erste Pivottabelle. Wir gruppieren die Verkäufer. Ein Teilnehmer meldet sich und sagt, dass bei ihm keine Verkäufer angezeigt werden. Ich schaue mir seine Pivottabelle an:
Ich habe zwei Mal hinschauen müssen, dann habe ich entdeckt, dass in der Tabelle noch ein Filter eingeschaltet war. Dadurch sind die ersten Zeilen ausgeblendet und dadurch werden auch nicht Daten angezeigt. Perfide!
Eigentlich klar, logisch, verständlich, konsequent und einleuchtend – dennoch: auf den ersten Blick erstaunt es: Warum kann man in einer intelligenten (strukturierten, formatierten, dynamischen) Tabelle kein Teilergebnis erstellen?
Ich habe eine Excelliste, in der trage ich Informationen zu meinen Schulungen ein. Ich habe eine Spalte „Stunden außerhalb“, in der ich bislang Texte eingetragen habe wie „2 x 24“ oder „3 x 10“. Nun hätte ich gerne die Summe der Tage ermittelt, also die Zahlen, die vor dem „x“ stehen summiert. Nichts leichter als das:
=LINKS(I351;SUCHEN(„x“;I351)-2)
liefert die Zahlen. Allerdings: steht kein Text in der Zelle, so liefert SUCHEN(„x“;I351) einen Fehler. Der kann bequem mit WENNFEHLER abgefangen werden:
=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);““)
Das Ergebnis:
Darauf setzte ich eine Pivottabelle auf und ändere den Vorschlag „ANZAHL“ in „SUMME“:
Eine Fehlermeldung ist die Folge. Vielleicht hängt es mit dem „“ der Funktion WENNFEHLER zusammen? Ich ändere die Formel in:
=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);0)
Die leeren Zellen werden mit einer „0“ aufgefüllt. Sieht nicht schön aus. Also ändere ich das Zahlenformat in ein benutzerdefiniertes:
0;-0;;
Und wundere mich erneut. Alles ist weg!
Langsam dämmert es mir: klar – die Funktion LINKS liefert einen Text. Das heißt: der Text „2“ wird durch das benutzerdefinierte Zahlenformat weggeblendet. Und: Excel erkennt keine Zahlen für die Pivottabelle, die summiert werden könnten. Also noch einmal die Formel anpassen. Der Text muss in eine Zahl konvertiert werden: mit WERT, mit „–“ oder mit „*1“
Erstaunlich. Ich kopiere eine Liste von einer Internetseite. Und möchte die Informationen einer Spalte trennen. Ich verwende den Assistenten Daten / Text in Spalten und bin verblüfft, dass die Texte nicht (am Leerzeichen) getrennt werden:
Eine Analyse mit der Funktion =CODE(TEIL(A2;LÄNGE(„Argentinien“)+1;1)) liefert das Ergebnis, dass es sich nicht um ein Leerzeichen (32), sondern um ein geschütztes Leerzeichen (160) handelt. Es entspricht dem (non-breaking space). Zum Glück kann man es aus dem Text kopieren und daran trennen:
Gewundert habe ich mich schon: ich erstelle eine Formel mit einem Bezug auf eine (intelligente/formatierte/dynamische) Tabelle:
=XVERWEIS($F$2;tbl_Staaten[Staat];tbl_Staaten[Jahr der Unabhängigkeit];;;1)
Mein Erstaunen ist groß als ich die Formel nach rechts ziehe:
=XVERWEIS($F$2;tbl_Staaten[Jahr der Unabhängigkeit];tbl_Staaten[Staat];;;1)
Der absolute Bezug $F$2 bleibt. Die konstante Zahl 1 bleibt. Aber die beiden Spalten werden vertauscht. Klar – ein Bezug auf eine Spalte einer Tabelle ist immer relativ. Da die Formel beim nach Rechts-Ziehen nicht „weiterwandern“ kann, beginnen die Bezüge von vorne. Und wie macht man einen absoluten Bezug innerhalb einer Tabelle? Da war doch mal was?!
Richtig: Andreas Thehos hat es einmal gezeigt. Man muss die Formel folgendermaßen schreiben (mit doppelten eckigen Klammern).
Da habe ich eine Anfrage von Herrn Diedmann erhalten, ob er etwas auf meinem Blog veröffentlichen kann. Na klar kann er – nervige Sache mit einer hervorragenden Lösung. Es geht darum in einer Pivottabelle nur die Nullwerte mit den entsprechenden Daten herauszufiltern. Lesen Sie selbst:
Sehr geehrter Martin,
Hier die Geschichte, die den Stein ins Rollen brachte.
Durch ein Lernstudio habe ich vor Ort bei einer
Getränkefirma Excel Unterricht vor Ort gegeben.
Es waren mit verschiedenen Office Versionen gearbeitet, von
2010 bis 2019
Bei Pivot war die Grundlage eine Tabelle von YouTube vom
Andreas Thehos.
Es sind Automobilverkäufer, die verschiedene Modell anbieten. Einige der Verkäufer haben in einem Zeitraum aber nicht bestimmte Modelle verkauft.
Lösung für Herrn N.….. Also.. Pivot Tabelle erstellen aus den Daten der BMW-Verkäufern
Pivot Tools… Entwurfsansicht… Berichtslayout… in Tabellenformat…
Es erscheint…
Weiter… so wie angezeigt. Modell anklicken, rechte Maustaste, Feldeinstellungen, Elemente ohne Daten auswählen und OK
Wie unten, ABEL Ergebnis anklicken, rechte Maustaste, Teilergebnisse keine und OK…
Die Nullwerte auszufiltern war und ist ja auch kein Problem.
Nun kam die Frage, Kann ich alle Verkäufer mit Nullwerte untereinander
ausfiltern?
Das Feld „Summe vom Endpreis“ zeigte keine
Filtermöglichkeit.
Aber, die Office Version 2010 hatte diese Filtermöglichkeit.
Das war die Herausforderung. Eine Lösung ohne VBA, denn das
können sie den Wenigsten vermitteln.
Die Lösung war letzthin und endlich, dass ich aus lauter
Verzweiflung einfach den Spaltenname über die projizierte Überschrift
geschrieben habe und schwupps…. Die Spalte ließ sich filtern.
Und….. Ratatataaaaaaa…..
Wenn jetzt noch Leere Daten als Null anzeige gesetzt wird…
Evtl. ist das das erste Thema, welches ich bei ihnen veröffentlichen kann.
Ansonsten zunächst frohe Fest und guten Rutsch ins neue
Jahr.
Heute in der PowerQuery-Schulung kam die Frage, ob man ab einer bestimmten Spalten alle anderen Spalten bis zum Ende der Tabelle löschen könne:
Schöne Frage. Geht aber nicht mit den Hausmittel. Ein paar Zeilen Code M wären nötig.
Ich habe vorgeschlagen von der ersten bis zu der Spalte mit der [Umschalttaste] zu markieren und anschließend „Andere Spalten entfernen“. Ist ein Klick mehr. Geht aber auch …
Verständlich, aber dennoch erstaunlich: Wenn eine Liste Datumsangaben in der ersten Zeile hat und wenn man diese Liste in eine (intelligente/dynamische) Tabelle verwandelt, so werden diese Datumsangaben zu Text. Klaro – Feldnamen/Überschriften müssen Text sein.
Beim Zurückkonvertieren in einen Bereich bleiben natürlich die Datumsangaben als Text stehen:
Power Query bei Ken Puls zu lernen ist ein Genuss.
Teil II
Auch deshalb (aber nicht nur deshalb), weil er auch so gerne über Excel, Ungereimtheiten und Schwächen lästert. Hier eine kleine Auswahl seiner spöttischen Bemerkungen über Power Query & co:
Der Dialog „gruppieren nach“ ist klasse – aber er zeigt die drei Pünktchen erst dann, wenn man mit der Maus darüber fährt.
Importiert man einen Bereich nach Power Query wird daraus eine Tabelle!?! Der Name der Tabelle: Tabelle1!
Power Query bei Ken Puls zu lernen ist ein Genuss.
Auch deshalb (aber nicht nur deshalb), weil er auch so gerne über Excel, Ungereimtheiten und Schwächen lästert. Hier eine kleine Auswahl seiner spöttischen Bemerkungen über Power Query & co:
Warum ist bei Dialogen so häufig der Default-Button derjenige, der am unwichtigsten ist? Kens Tipp: meistens ist der Button links von der Abbrechen-Schaltfläche der wichtige:
„Gebietsschema“: Der langsamste Dialog in PQ:
Warum ist das „schnelle Laden von Daten“ nicht Standard?
Warum zeigt ein Doppelklick auf den Rand nicht den kompletten Inhalt?
Masterclass bei den Exceldays 2019 in Sofia bei Gasper Kamensek (http://excelunplugged.com/) zum Thema „Power Pivot“. Er zeigt uns, das nach dem Erstellen einer KPI in PowerPivot zuerstet nur Zahlen zu sehen sind:
Desaktiviert man allerdings den Status und aktiviert ihn anschließend wieder von Neuem, werden die Farben korrekt angezeigt:
