Wie möchten Sie Ihren Kaffee trinken? Aus ihrem ausgehöhlten Schädel! Morgens bin ich die Frohnatur in Person.

Das neue Visual „Datenschnitt (neu)“ in PowerBI ist klasse. Dort kann man interessante und hilfreiche Filterwerkzeuge erstellen.

Allerdings irritiert mich, dass ich bei der Bezeichnung keine Felddaten hinzufügen kann:

Ich werde fündig: Nur beim Zustand „Standard“ ist dies möglich:

Bei den anderen drei Zuständen nicht!

Sehr versteckt!

Ich bin kein Wissenschaftler, aber ich bin mir ziemlich sicher, dass die Erdanziehungskraft am stärksten ist, wenn man am Montagmorgen im Bett liegt.

Nein, ganz so einfach ist es leider nicht.

Folgendes Problem: in einem Ordner befindet sich eine Datei.

Aus dieser Datei werden einige Spalten in einer anderen Datei benötigt. Dies kann prima mit Power Query umgesetzt werden.

Hinter dieser Datei werden weitere Informationen eingefügt:

Damit man die Originaldatei verändern kann, aber auch die in die Zieldatei neu eingetragenen Daten, geht man mit Power Query wie folgt vor:

Die intelligente Tabelle wird als zweite Abfrage in Power Query abgerufen:

Und mit der ersten Abfrage verknüpft:

Die eindeutige ID bildet hier die E-Mail-Adresse.

Die überflüssigen Spalten, die hier nun doppelt angezeigt werden, werden gelöscht

Klappt: die Originaldatei kann verändert werden (die Aktualisierungen werden in der Zieldatei angezeigt) und auch die Daten der neuen Spalten der Zieldatei können angepasst werden oder die Liste kann sortiert werden, ohne, dass die Aktualisierung etwas zerstört.

(danke an Hans-Peter Pfister für diesen Hinweis)

ABER:

Befinden sich die neuen Spalten INNERHALB der Datei, also beispielsweise so:

Wird nun diese intelligente Tabelle nach Power Query gezogen und dort mit der importierten Tabelle verknüpft:

Werden nach der Aktualisierung die Verknüpfungen zerstört:

Ich habe noch keinen Weg gefunden, dass die Zuordnungen korrekt bleiben – nach Sortieren und Ändern der Daten.

Eben dachte ich, jemand hätte mir beim Joggen nachgepfiffen. War aber nur die Lunge.

Das ist schon irgendwie doof.

Bevor man in PowerBI ein Flächenkartogramm aktivieren kann:

muss man in den Optionen in der Kategorie „Vorschaufeatures“ die Option „Visuelles Formzuordnungselement“ aktivieren:

Auch das Visual Azure-Landkarte muss aktiviert werden:

Allerdings nicht in den Optionen, sondern im Admincenter von Microsoft 365:

Dort in den Mandanteneinstellungen / in den Integrationseinstellungen:

Sonst erhält man die Fehlermeldung: „Ihr Mandantenadministrator hat die Verwendung von Azure Maps leider nicht aktiviert.“

Ich bin an Fasching zu einem Umzug eingeladen. Ich bin der einzige, der verkleidet ist. Die anderen schleppen Möbel.

Seltsam!

Ich erstelle in PowerBI ein (einfaches) Measure – hier: eine Summe. Und verwende es in einem Visual. Klappt.

Ich erstelle ein zweites Measure – diesmal mit AVERAGE statt SUM, das ich verwende. Klappt:

Ich ziehe das Zahlenfeld direkt in das Wertefeld. Die Summe funktioniert problemlos. Wechsele ich dagegen in „Visual formatieren“ bei Wert / Feld von Summe auf Mittelwert, versagt die Berechnung und Darstellung:

Fazit (war ja eigentlich schon klar): Für Daten und dahinterliegende Berechnungen sollten nicht die automatischen Berechnungen von PowerBI verwendet werden, sondern IMMER ein selbstgeschriebenes Measure.

Wir sollten endlich aufhören, weniger zu trinken.

Och nö! Wer macht den so etwas?

Eine Rechtsanwaltskanzlei zeigt mir ihre Excel-Vorlage, mit der sie arbeiten:

Darin befindet sich eine intelligente Tabelle mit dem Namen „Tabelle1“. So heißt auch das Tabellenblatt. Darin befinden sich Bereiche für Zahlen mit Zwischensummern (!), Text, Datum und Zahl wird in einer Spalte fröhlich durcheinander gewürfelt. Boah!

Ich vermute, dass sie die Idee der intelligenten Tabelle nicht ganz verstanden haben. Und diese nur für die lustigen Farben verwendet haben …

Brot trocknet nicht aus, wenn man es in einem Eimer mit Wasser lagert.

Verblüfft. Eine Teilnehmerin in einer Excelschulung sagt mir, dass eine intelligente Tabelle auf einem geschützten Arbeitsblatt liegt:

Sie möchte eine Pivottabelle erstellen, was mit Einfügen / Pivot-Tabelle nicht möglich ist.

Und umgekehrt:

Befindet sich der Cursor auf einem leeren, ungeschützten Tabellenblatt, klickt man dort auf Einfügen / Pivot-Tabelle , kann man den Namen der intelligenten Tabelle eintragen:

Padautz: SO kann man eine Pivottabelle erzeugen!

Falls jemand noch gute Vorsätze für 2024 benötigt: ich habe noch völlig unbenützte aus den Jahren 2023, 2022, 2021, 2020, 2019 und 2018. Antiquarische auf Anfrage.

Guten Tag Herr Martin

Ich kenne Power-Queri damit habe ich aber noch nie eine PDF hereingezogen. Geht denn das auch mit gescannten PDF’s? wie gesagt, es geht hier um das was die Kunden uns zu stellen. Und das ist wirklich sehr unterschiedlich.

Aber das schaue ich mir gerne an.

####

Hallo Herr S.,

1. Schauen Sie doch mal nach: Haben Sie in Excel Daten / Daten abrufen / aus Datei / Aus PDF?

Klicken Sie mal auf eine Rechnung? Bei meinen (alten) Telekom-Rechnungen hat es funktioniert. Vor Kurzem war ich in einer Firma – dort hatte auch jemand das Problem – Rechnungen als PDF … ging auch mit Power Query.

und dann muss ich (bei der Telekom) noch ein bisschen transformieren:

Relationship status: Made dinner for two. Ate both

Gestern in der Excelschulung fragte eine Teilnehmerin, ob man die Daten für die Datenüberprüfung auch auslagern können. So, dass man von jeder Excelmappe darauf zugreifen könne und sie verwenden könne. Das geht leider nicht, war meine Antwort. Die Daten müssen innerhalb der Mappe liegen.

Sie war enttäuscht.

Aber dann zeigte ich ihr, dass man Daten als benutzerdefinierte Liste (in den Optionen) ablegen kann. Diese werden in Excel gespeichert – nicht in einer Arbeitsmappe. Und damit kann man schnell die gewünschte Liste erzeugen, die man in jeder Datei verwenden kann. Beispielsweise für die Datenüberprüfung.

Die Teilnehmerin war glücklich.

Tragen Sie Ihre Hosen eher medium waist slim tube fit oder low raise normal waist regular straight leg? – Blau, bitte!

PowerBI-Schulung.

Warum bei ihm die Wert der Balken als Tsd angegeben werden, wollte der Teilnehmer wissen. Und nicht als Zahlen.

Wir haben eine Weile gesucht, wo in den Eigenschaften sich diese Einstellung verbirgt.

Schließlich sind wir fündig geworden: wenn man das Visual verkleinert, formatiert PowerBI die Zahl automatisch von 1.500 in 1,5 Tsd. um:

Ein Wort, in dem zwei Mal das Wort „Lauf“ vorkommt? – Nudelauflauf

Folgende Mail habe ich gestern erhalten:

„Wir werden Änderungen an den Funktionen zum Abrufen und Transformieren von Daten (auch bekannt als Power Query) in Excel vornehmen.Anmerkung: Wenn Sie Power Query nicht verwenden, können Sie diese Meldung schließen.

Wann das passieren wird:

Ab dem 11. März 2024 werden wir wichtige Änderungen an den Funktionen zum Abrufen und Transformieren von Daten (auch bekannt als Power Query) in Excel einführen.

Wie wirkt sich das auf Ihre Organisation aus:

Jeder Benutzer, der versucht, Power Query nach dem 11. März 2024 zu verwenden und unter eines der folgenden Szenarien fällt, erhält eine entsprechende Fehlermeldung. Da wir die Benutzeroberfläche zum Abrufen und Transformieren von Daten (auch bekannt als Power Query) in Excel für Windows modernisieren, erfordern einige der Funktionen zum Abrufen und Transformieren von Daten Microsoft Edge WebView2 ( Microsoft Edge WebView2 | Microsoft Edge Developer), die auf dem Clientcomputer installiert werden soll. In Zukunft erfordern alle Funktionen zum Abrufen und Transformieren von Daten in Excel für Windows Microsoft Edge WebView2, daher empfehlen wir, diese Bibliothek jetzt zu installieren. Um unseren Kunden die beste Verschlüsselung ihrer Klasse zu bieten, planen wir außerdem, die Unterstützung von TLS (Transport Layer Security) 1.1 oder niedriger in Get & Transform Data (auch bekannt als Power Query) auf allen Plattformen einzustellen. Wenn eine bestimmte externe Datenquelle, mit der ein Benutzer eine Verbindung herzustellen versucht, nur TLS Version 1.1 oder niedriger unterstützt, kann er nicht über die Tools zum Abrufen und Transformieren von Daten in Excel auf die relevante Datenquelle zugreifen.

Was Sie tun müssen, um sich vorzubereiten:

Sie müssen: Installieren Sie das Microsoft Edge WebView2-Framework für bevorstehende Features zum Abrufen von Daten. Stellen Sie sicher, dass alle externen Datenquellen , die Sie mit Power Query verwenden, TLS Version 1.2 oder höher unterstützen – für Power Query im Allgemeinen.Bitte informieren Sie Ihren Helpdesk und aktualisieren Sie die Dokumentation entsprechend.Weitere Informationen finden Sie unter:Informationen zum Abrufen und Transformieren (Power Query) in ExcelVorbereiten von TLS 1.2 in Office 365 und Office 365 GCC | Microsoft Learn
Aktivieren von Transport Layer Security (TLS) 1.2 auf Clients – Configuration Manager | Microsoft Learn

####

Ich verstehe es nicht ganz …

Kurios – je älter ich werde, umso mehr Männer interessieren sich für mich! – Die nennt man Ärzte, meine Liebe.

Einfach nicht aufgepasst!

Power Query-Schulung.

Ich möchte eine Liste sortieren:

Und erhalte einen Fehler:

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 …

Sohn: Jeder zweite hatte in dieser Mathearbeit eine 5 und die anderen 70 % waren auch nicht viel besser …

Hallo Renè!

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. :-(((

Wie immer wenn niemand lästig ist.

Liebe ist, wenn er sagt: Kann ich zu dir in die Badewanne oder hast du schon reingepinkelt?

Amüsant. Erstaunlich. Und erklärbar.

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!

Hier ein paar Schokoriegel, tolles Hexenkostüm, auch wenn du einen Tag zu früh kommst. – Schatz: Lass Mutter rein!

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 …

Du hörst mir nie richtig zu! – Ja, mach ich, Schatz!

Hallo Herr Martin,

wie heute Morgen gezeigt, machen mir aktuell zwei unterschiedliche Pivot Tabellen Probleme.

Die Spalte „Gesamt“ ist ein berechnetes Feld innerhalb der Pivot,

Für die einzelne Artikel wird die Gesamtstückzahl nicht korrekt berechnet (Stk./Pkg x Bestand).

###

Hallo Frau W.,

das erste Problem ist klar: Pivottabellen summieren erst, dann wird multipliziert.

Aus:

ArtikelMengePreis
Artikel120
Artikel210

Pivot rechnet NICHT: 1 x 20 + 2 x 10 = 40, sondern (1 + 2) x (20 + 10) = 3 x 30 = 90

Lösung 1: Hilfsspalten

Lösung 2: PowerPivot mit der Funktion SUMX

Hilft das?

Was sind Ihre Stärken? – Ordnung und Sauberkeit sind mir sehr wichtig! – Gut, und was sind Ihre Schwächen? – Ich hasse putzen.

Hallo Renè!

Da bin ich wieder 😉

Habe ein Filterproblem.

Vor längerer Zeit habe ich von dir die Formel in dem Arbeitsblatt erhalten.

Damals benötigte ich die Auflistung von Belegnummern.

Dieses mal brauche ich die Funktion um die Leistungsklassen bei der Medaillenbestellung anzugeben.

Komme beim gleichzeitigen Filtern von der Altersklasse und der Disziplin nicht weiter.

Die Formel aus dem Video über die Funktionen habe ich versucht.

Kein Erfolg. #Kalk war das Ergebnis.

Das Filter mit zwei Auswahlen in die frühere Formel einzubauen habe ich nach einiger Zeit aufgegeben.

Bitte um die bewährte Hilfe.

Danke für deine Hilfe,

mit freundlichen Grüßen

Peter

???

Hallo Peter,

Vertriebsmanager steht in der Spalte Position und Berlin steht in der Spalte Ort.

SO geht es:

=FILTER(tbl_Lieferanten;(tbl_Lieferanten[Position]=B35)*(tbl_Lieferanten[Ort]=C35))

Bitte markiere die KORREKTEN Spalten!

Liebe Grüße

Rene

Wie verkündet man den Tod eines großartigen Huhns? Eierlegende Eierlegende kam an ihr Eierlegende.

Guten Morgen Rene,

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

„Aktualisierung“

über


– Option1: Kontextmenü, rechte Maustaste
– Option 2: Ribbon: Daten / alle aktualisieren

noch möglich ist?.

Ich darf aber an dieser Stelle keinen VBA nutzten. Das ist (m)ein Problem.
Es gibt dann wohl keine Lösung dazu, oder?

Ein Dankeschön für deine Information & guter Tagesstart

Jürgen

#####

Hi Jürgen,

gute Frage.

Meines Wissens geht das nicht: Schutz bedeutet, dass keine Änderungen möglich sind.

Also: entweder Schutz oder Aktualisierung der Power Query-Tabelle. Oder eben doch ein paar Zeilen VBA.

Andere Lösung: die Daten, die geschützt werden sollen, auf ein anderes, geschütztes Blatt

Liebe Grüße

Rene

Machst du heute Abend noch irgend etwas? – Nein, ich altere nur so vor mich hin.

Hallo Herr Martin,

Aktuell habe ich ein Problem mit Power Query.

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.

Wir sind beide begeistert!

Ich hatte heute ein wirklich effektives Workout auf dem Laufband. Nur das Piepsen und das Schreien der Kassiererin haben genervt.

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.

7 Uhr: Nudelsalat machen. 10 Uhr: Schauen, ob Nudelsalat schon durchgezogen ist. 10.15: Neuen Plan für Abendessen machen.

Auf einem Tabellenblatt befindet sich eine intelligente Tabelle. Sie heißt „Tabelle1“.

Sie wird in das Datenmodell eingefügt. Die Tabelle in Power Pivot heißt „Tabelle1“:

Die Ursprungstabelle wird umbenannt.

Leider wird die Tabelle in PowerPivot NICHT automatisch umbenannt:

Sehr schade, findet das ein Teilnehmer der letzten Power Pivot-Schulung.

Ich kreuze Hunde. Die letzte Kreuzung wurde zwischen einem Bullterrier und einem Shih Tzu durchgeführt. Wie wohl die neue Rasse heißt?

Auf einem Tabellenblatt befindet sich eine intelligente Tabelle mit dem Namen „Tabelle1“.

Auf ihr wird eine Abfrage aufgesetzt und als Tabelle nach Excel zurückgegeben.

Ändert man nun den Namen der Ursprungstabelle …

… wird diese Namensänderung nicht in der Abfrage mitgenommen:

Sehr schade, findet eine Teilnehmerin der letzten Power Query-Schulung.

Aber Papa, wenn das Telefon früher in der Wand steckte, wie habt ihr dann im Garten Fotos machen können?

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

Und schließend die leeren Zeilen rausfiltern.

Geht doch!

Wusstest du, dass man drei Schafe braucht, um einen Wollpullover zu stricken? – Nein, ich wusste nicht, dass Schafe stricken können.

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.

Guck mal – dein Mann spricht gerade mit einer Anderen! – Lass ihn doch. Mal schauen, wie lange er den Bauch einziehen kann.

Amüsant.

PowerQuery-Schulung. Wir greifen auf eine (Währungskurs-)Tabelle im Internet zu:

Wir müssen die Daten transformieren, das heißt: an mehreren Stellen trennen:

Das Ergebnis

wird nach Excel als Tabelle geladen und dort weiterverarbeitet:

IN diesem Beispiel mit einem XVERWEIS (oder SVERWEIS würde auch funktionieren).

Eine Teilnehmerin meldet sich und sagt, dass ihr XVERWEIS Fehler liefern würde:

Die Ursache war schnell gefunden: sie hatte nicht Leerzeichen:Bindestrich als Trennteichen verwendet, sondern nur den Bindestrich:

Somit war noch ein Leerzeichen im Text, Und deshalb lief der XVERWEIS ins Leere …

Ich bin Hypochonder, Herr Doktor. – Ach was, das bilden Sie sich nur ein!

Hallo Herr Martin,

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.

Liebe Grüße und: ein schönes Wochenende

Rene

Die Würde des Mannes ist unten antastbar.

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:

Schade!

Kleine Brandlöcher von der Zigarette entfernt man am besten mit einer Nagelschere.

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:

Wir kochen Wasser immer nach Celsius, nicht nach Fahrenheit. Das spart 112 Grad.

Verständlich. Aber trotzdem doof.

Ich habe mehrere Tabellenblätter. Auf jedem Blatt befindet sich eine intelligente Tabelle mit einem identischen Aufbau:

Ich möchte nun alle Blätter auswählen (Gruppierungsmodus) und eine Information ändern (beispielsweise in der Überschrift):

Änderungen an einer Tabelleoder einer XML-Zuordnung können nicht vorgenommen werden, wenn mehrere Blätter ausgewählt wurden.

Schade!

Men are like mascara, they usually run on the first sign of emotion.

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:

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=qry_Sharepoint_Dateien;Extended Properties=""""", _
            Destination:=ActiveSheet.Range("$B$5")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [qry_Sharepoint_Dateien]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = strTabellenname
        .Refresh BackgroundQuery:=False
    End With

Das Ergebnis sieht dann so aus:

Und nun gestaltet sich der Zugriff auf die Dateien leicht:

Set xlDateiZugriff = Application.Workbooks.Open ...

Ändern, speichern (eigentlich nicht nötig) und schließen.

Geht doch!

Zugegeben: Sharepoint Zickt! Das hat auch Martin Weiß (https://www.tabellenexperte.de/) bestätigt:

  • Manchmal stürzt das Programm ab!
  • Manchmal öffnet er nicht (obwohl die Datei vorhanden)
  • Manchmal schließt er nicht. Macht einfach nicht mehr zu!

Dennoch: Meistens klappt es. Und schließlich: Excel muss halt ab und zu nerven …

Der Berg ruft. Es ist entweder die Wäsche oder der Abwasch.

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.

Danach klappte es wieder.

If you think your neighbor’s grass is greener, go smoke it.

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.

Welche Kosmetikartikel können Sie mir fürs Gesicht empfehlen? – Eine Tüte!

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).

Ich fühle mich heute so motiviert – ich könnte Wollmäuse dressieren!

Amüsiert!

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:

67 % der Frauen sagen, ihr Haustier hört ihnen besser zu als ihr Partner. 73 % der Haustiere sagen, diese verrückten Frauen machten ihnen Angst.

Irritiert:

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:

Ich bin heute so farbenfroh – ich habe fünf verschiedene Schwarztöne an.

Amüsant.

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“.

Hier macht der Wert null keine Probleme.

Quatsch. Natürlich gibt es heute noch Männer, die richtige Krieger sind. Das sind dann halt Nix-auf-die-Reihe-Krieger

PowerQuery-Schulung. Wir greifen auf einen Ordner zu.

Eine Teilnehmerin schreibt statt

=Excel.Workbook([Content])

den Befehl


=Excel.Workbook[Content]

Erstaunlicherweise wird kein Fehler angezeigt – es wurden keine Syntaxfehler erkannt:

Erst nach Bestätigung wird der Fehler (hier: die fehlende runde Klammer) quittiert:

Hinter jedem Laib Brot steckt auch die tragische Geschichte von Getreide, das es nicht geschafft hat, in Bier weiterverarbeitet zu werden.

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:

Manche Menschen leben so anständig und gesittet – die sterben fast wie neu.

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:

Seltsam.

Ich mag offene Menschen. Micha (38), Chirurg

Ich bin unterwegs und arbeite auf meinem Laptop.

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.

Was kann die Türkei uns schon anhaben? – Sie ernten 70% aller Haselnüsse! – … – Nutella? – Jemanden muss diesen Kerl stoppen!

Lustiger Fehler in der PowerQuery-Schulung.

Wir üben, wie man auf einen Ordner zugreift und dort Dateien eines bestimmten Typs, beispielsweise Textdateien ausliest und zusammenfasst.

Ein Teilnehmer meldet sich und fragt, warum es bei ihm nicht funktioniere. Warum bei ihm nur eine Datei bearbeitet wird.

Als ich es mir anschaue, stelle ich fest, dass er nicht auf das Symbol in der Überschrift, also neben dem Feldnamen geklickt hatte:

sondern auf den ersten „Binary“-Eintrag:
Und so wird nur diese eine Datei extrahiert:

Aber kein Problem – denn zum Glück kann man diesen Schritt wieder löschen:

Stand nackt da und betrachtete mich im Spiegel und dachte „dein Körper ist dein Tempel.“ – Frage mich, in welchem Jahrhundert der gebaut wurde und wer zum Teufel den Anbau genehmigt hat.

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.

Im nächsten Leben mache ich etwas ohne Wecker.

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!

Heute habe ich mein erstes graues Sackhaar entdeckt. Ich bin aber nicht so ausgerastet, wie die anderen Leute im Fahrstuhl.

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.

Woran ist eigentlich der Tote Winkel gestorben?

Excelstammtisch

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:

https://www.repairmsexcel.com/blog/excel-external-table-is-not-in-the-expected-format

Ich habe heute den Salzstreuer aufgefüllt. Das dauert bei den kleinen Löchern ja immer ewig.

Blöde! Nur blöde!

Eine intelligente Tabelle. Eine bedingte Formatierung:

Excel schreibt als Bereich $G$2:$G$32 ein. Auch ein Ändern in

=tbl_Fahrräder[Anschaffungspreis]

wird wieder zurückgesetzt.

Das ist doof: wird nun eine Zeile ausgeschnitten und eingefügt, lautet nun der Bereich:

=$G$15:$G$38;$G$2:$G$13

Wiederholt man nun diese Aktion, sieht die bedingte Formatierung folgendermaßen aus:

Blöde!

Advent ist, wenn der Dachboden entrümpelt ist und alles in der Wohnung verteilt ist.

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.

Männer warten mit den Weihnachtseinkäufen bis Heiligabend, aber die Fußballkarten besorgen sie sechs Monate im Voraus

Amüsant:

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.

Wann musst du morgen arbeiten? – Von dunkel bis dunkel.

Manchmal muss man andere Denkwege einschlagen …

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:

https://contoso.sharepoint.com/sites/contoso/Freigegebene Dokumente/VanArsdel.pbix

Jedoch scheitern sämtliche Versuche, diese Datei mit einem Befehl wie

Shell "https://contoso.sharepoint.com/sites/contoso/Freigegebene Dokumente/VanArsdel.pbix"

zu starten. Nach einer sehr langen Weile dämmert es mir: Man nehme den Originalpfad und rufe ihn mit

ThisWorkbook.FollowHyperlink "https://app.powerbi.com/groups/me/reports/385f2964-2ac8-5f56e1894f8b/ReportSection"

Geht doch! So einfach!

Leute, die fragten „Isst du das noch?“ und auf meine Teller griffen, sagten auch „aua“ und: „Zieh die Gabel wieder raus!“

Der Klassiker: bei mir läuft es – beim Kunden nicht.

Sehr hübsch. Ich füge per Programmierung einige Spalten in eine Liste ein:

xlBLatt.Cells(1, lngSpaltennummer + 1).EntireColumn.Insert

Und erhalte eine Fehlermeldung:

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!

Kein Mensch mag Mon Chérie. Es gibt überhaupt nur 17 Packungen Mon Chérie weltweit, die permanent verschenkt werden und so weltweit rotieren.

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.

Heute war ich eine Stunde auf dem Laufband. Morgen schalte ich es vielleicht sogar ein.

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.

Geht doch:

Ein echter Pirat weint nicht. Ein echter Pirat heult Rum.

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:
  1. 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
  2. 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.
  3. 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.

Mit freundlichen Grüßen

Marcel Gröschel

Bitte keine Anfragen für einen Dreier – wenn ich zwei Personen gleichzeitig enttäuschen will, gehe ich zu meinen Eltern.

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:

Ich habe im Homeoffice dem Paketboten aus Versehen nackt die Türe aufgemacht. Ich: „UPS.“ Er: „Nein, DHL.“

Menschen machen manchmal lustige Dinge.

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!

Dass du deinem Mann in seinem Alter noch Knutschflecke verpasst hast, finde ich toll! – Würgemale, meine Liebe – das sind Würgemale.

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.

Im Gegensatz zum Gehirn meldet sich der Magen, wenn er leer ist.

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.

Werbung von der Bank: „Legen Sie Ihr Geld in Immobilien an!“ – Prima Idee! Ich kaufe mir gleich am Montag mit meinem Ersparten ein Eigentumszelt.

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):

Also: dann grenzen wir den Bereich doch ein!

    Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C4" , _
        Version:=8)

Geht doch:

Habe meinen Wecker heute auf die Arbeit mitgenommen. Damit er sieht, wozu er mich jeden Tag nötigt.

Irre!

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 …

„Ich putze, bügle, koche und räume auf – ich komme mir vor wie Aschenputtel.“ – „Ich habe dir ja gesagt, dass das Leben mit mir wie im Märchen für dich sein wird.“

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 …

Mein Ex hat angerufen – Was wollte er? – Ich habe nicht abgenommen. – Das sehe ich, aber was wollte dein Ex?

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:

Danke an Christian Steckmann für diesen Hinweis.

Die gute Nachricht: ich bekomme den oberen Knopf meiner superengen Jeans endlich wieder zu. Die schlechte Nachricht: Ich habe sie nicht an.

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.

Heute habe ich gesehen, dass der Honig teurer geworden ist. Ich wusste gar nicht, dass Bienen mit Diesel fliegen.

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:

Rosi hat jetzt unter 32168 eine Rufumleitung. Zu Layla.

Perfide.

Warum funktioniert der Filter nicht? In einer Liste befinden sich Zahlen – allerdings bietet der Autofilter die Einstellung „Textfilter“ an:

Ich filtere „beginnt nicht mit ‚-‚“, um die negativen „Zahlen“ auszublenden. Das Ergebnis:

Die negativen Zahlen bleiben stehen. Ich filtere „größer oder gleich 0“ – es klappt:

Und die Ursache? Warum zeigt Excel „Textfilter“ an, obwohl es sich um Zahlen handelt? Obwohl die Spalte mit dem Zahlenformat „Zahl“ formatiert ist?

Die Antwort: in der Spalte befinden sich keine Zahlen, sondern Formeln, die manchmal einen Text (eine leere Zeichenkette) liefern:

=WENNFEHLER(O2-P2;"")

Das Ergebnis sind Texte („“) und Zahlen. Text gewinnt – obwohl die Spalte korrekt als Zahl formatiert ist. Perfide!

Der Computer sagt „Drucker suchen“ und ich stelle mir vor, wie die Daten rumlaufen und panisch rufen „Ein Drucker! Ist ein Drucker anwesend?“

Hallo Herr Martin,

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?

Liebe Grüße

Rene Martin

Findest du, dass ich zu wenig Busen habe. – Nein: zwei sind okay!

Lustige Frage in der letzten PowerQuery-Schulung:

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 …

Lust auf Vollkornnudeln? – Warum fängst du jetzt Streit an?

Lieber Herr Martin,

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] )

Klappt das?

Liebe Grüße

Rene Martin

Die Frau hat einen Beutel: „Heute Rettich ich die Welt.“ Ich lese das und zucchinerlich zusammen.

Lieber Herr Martin,

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

und rechnen damit weiter:

Brutto = [MWST] + [Netto]

Da ist ein Fehler auf der Speisekarte. Sie haben „Rucola“ geschrieben. Da fehlt das „m“.

Guten Tag Herr Martin,

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.


Hilft das?
Liebe Grüße

Rene Martin

Hab gerade zwei Männer im Partnerlook gesehen und sie höflich gefragt, ob sie schwul sind. Mich dafür verhaften, finde ich schon krass …

Irgendwie doof!

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?

Irgendwie doof!

„haha – nix schlafen gehen!“ brüllte mein verkorkster Schlafrhythmus und fuhr laut hupend auf einem Bobbicar durch mein Hirn.

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

= Table.Sort(#"Geänderter Typ",{{"Ort", Order.Ascending}, {"Plz", Order.Ascending}, {"Strasse", Order.Ascending}})

Ähnlich wie SQL:

SELECT *
FROM Kunden
ORDER BY Kunden.[Ort], Kunden.[Plz], Kunden.Strasse

Der Teilnehmer war zufrieden.

Herr Ober, können Sie bitte den Daumen von meinem Schnitzel nehmen! – Damit es mir schon wieder runterfällt, oder was?

Hallo Rene,

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!

Wenn euch jemand vor die Frage stellt: Sex oder Magnum – nehmt ihr dann Mandel oder Classic?

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:

= Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]

Ich erstelle eine Spalte mit diesem Wert:

= Table.Column(#"Geänderter Typ", Jahr)

Das Ergebnis:

Wird eine Spalte der Liste gefiltert, lautet der M-Befehl (beispielsweise für das Jahr 1950) folgendermaßen:

= Table.SelectRows(#"Gefilterte Zeilen", each ([1950] <> null))

Baue ich dort allerdings das Ergebnis der Abfrage ein, klappt die Filterung nicht mehr:

= Table.SelectRows(#"Gefilterte Zeilen", each (Jahr <> null))

Auch alle anderen Versuche greifen nicht – werde das Weiterverarbeiten der Tabelle oder Liste noch eine Hilfsspalte, bei der aus

= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if [1950] <> null then null else "x")

ändert in

= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if Jahr <> null then null else "x")

Schließlich – der Gedanke: [1950] muss durch

Record.Field(_, Jahr) 

ersetzt werden. Die ganze Zeile lautet laso:

= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Jahr) <> null))

oder komplett:

= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]) <> null))

Geht doch!

PS: Die rote Farbe im Kopf habe ich mit der bedingten Formatierung erzeugt. Man darf jetzt nur nicht die Aktualisierung vergessen.

Einerseits will man für jemanden eine Freude machen und einen Käsekuchen backen, anderseits ifft der sowiefo viel su ungefund.

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:

https://www.office-kompetenz.de/inhalte-aus-spalten-auf-zeilen-verteilen-mit-power-query/

Und genau DAS ist die Lösung:

Zuerst muss man den „rechten“ Teil entpivotieren:

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“.

Seit ich nicht mehr rauche, fast keinen Alkohol mehr trinke und mehr Sport treibe, lüge ich häufiger.

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:

Männer können einfach nicht richtig zuhören. – Sind wir gar nicht!

Ich erhalte eine Mail.

„Ich komme leider mit der Fehlermeldung  

     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:

Excel.CurrentWorkbook(){[Name="Schweinchen3"]}[Content]{0}[Column1]

In der Codezeile

= Excel.Workbook(File.Contents(Pfad & Schweinchen1), null, true)

müssen die beiden Variablen durch ihre Funktion ersetzt werden (was den Code nicht gerade lesbar macht):

= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true)

Klappt:

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:

#"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])

und lösche die Zeile „Navigation“. Klappt:

Der ganze Code (jetzt: völlig unverständlich!):

let
    Quelle = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true),
    #"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])
in
    #"Höher gestufte Header"

Die übrigen zwei Abfragen werden analog angepasst – nun klappt alles!

Hannibal Lecter hat unhöfliche Menschen einfach aufgegessen. Ich wollte es nur einmal gesagt haben.

Eine schöne Frage in der letzten Schulung.

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.

Wenn eine Schraube locker ist, hat das Leben etwas mehr Spiel

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:

Danke, Martin, für diesen wertvollen Tipp.

Sehr rücksichtsvoll vom Badespiegel, dass er beschlägt, wenn ich aus der Dusche komme.

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.

Danke, Martin, für diesen wertvollen Tipp.

Werdet ihr beim Saubermachen auch immer so abgelenkt von Dingen, die ihr findet?

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.“

Danke, Martin, für diesen wertvollen Tipp.

Lass deinen Drucker nie spüren, dass du in letzter Minute ausdrucken möchtest. Sie können deine Angst spüren.

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:

  1. 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:

Danke, Martin, für diesen wertvollen Tipp.

Gute Mütter lassen ihre Kinder die Rührstäbe abschlecken. Großartige Mütter schalten vorher das Gerät aus.

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:

= Folder.Files(Excel.CurrentWorkbook(){[Name="tblPfad"]}[Content]{0}[Pfad])

Danke, Martin, für diesen wertvollen Tipp.

Immer wenn ich ein paar Kilo verliere, finde ich sie kurz darauf im Kühlschrank wieder.

Guten Tag Herr Martin,

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:

https://blog.jamesbayley.com/2018/04/23/power-query-how-to-load-only-fresh-rows-and-create-an-excel-history-table/

Die Schritte sehen so aus:

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.

Ich würde mich über eine positive Antwort freuen.

Hab heute 20 Cent auf dem Parkplatz gefunden. Ich werde erst mal weiter so leben wie bisher, damit die Nachbarn nichts merken.

Hallo Rene,

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 neuerdings die Durchsage „Liebe Kunden, wir öffnen Kasse 2 für Sie“ als Klingelton auf meinem Handy. Einkaufen macht plötzlich viel mehr Spaß!

Hallo Rene,

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.

Ich mache im Moment drei Diäten. Von einer alleine wird doch kein Mensch satt!

PowerQuery-Schulung.

Eine Teilnehmerin fragt, wie sie den M-Code einer Abfrage dokumentieren, das heißt in Word speichern kann.

Ich antworte ihr, dass sie den „Erweiterten Editor“ öffnen kann und dort den Code einsehen und herauskopieren kann.

Eine andere Teilnehmerin weist mich darauf hin, wie man alle Codetexte aus allen Abfragen erhält:

„Du musst nach Excel wechseln:

Dort musst du die Abfragen markieren und kopieren:

Und das Ergebnis kann man nach Word einfügen. PowerQuery fügt nicht die Namen der Abfragen, sondern den M-Code ein:

Ich bin begeistert! Ein Dankeschön an Lydia Homann für diesen guten Tipp. Schon nervt Excel wieder ein bisschen weniger!

Kennst du das? Montag Morgen voller Freude aus dem Haus stürmen? – Ich kenne das auch nicht!

Reguläre Ausdrücke – sie sind so klasse – warum mag Microsoft sie nicht?

Die Aufgabe: Extrahieren Sie aus zirka 30.000 Zellen einer Exceltabelle die darin befindlichen Datumsinformationen. Es finden sich Texte wie:

Maistraße 17 (St. 29.03.2016), Flurnummer-alt: 47/11

Bahnhofstraße 1 – 17.3.2022 – Flurnummer-alt: 08/15

Hirtenweg 3A 05-2005 – Garagen, Flurnummer-alt: 00/77

Ich überlege: Formeln wären eine Option. Aber sehr umständlich.

VBA wäre gut.

PowerQuery auch – kennt aber keine regulären Ausdrücke.

Ich entscheide mich für VBA.

Dort muss man einen Verweis auf „Microsoft VBScript Regular Expressions“ einbinden. Oder diese Klasse mit

Set regex = CreateObject("vbscript.regexp")

aufrufen. Dann kann man definieren:

    ' -- Muster: ***01.01.2022***
    strMuster1 = ".*\d{1,2}\.\d{1,2}\.\d{2,4}.*"
    strMuster1_Raus = "\d{1,2}\.\d{1,2}\.\d{2,4}"

Und kann nun extrahieren:

    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:

Trotzdem: ich ziehe hier VBA vor.

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.

Als mit klar wurde, wie lange es dauern würde, „Nana Mouskouri“ in die Schulbank zu schnitzen, habe ich beschlossen, lieber „Kiss“ zu hören.

Böse!

Eine Funktionen der intelligenten Tabellen sind „ausgegraut“. Warum?

Auch sortieren und filtern funktioniert nicht …

Man muss schon ganz genau hinschauen, um zu entdecken, dass es sich hier um ZWEI Tabellen handelt, die nebeneinander eingefügt wurden …

Mein Arzt hat gesagt, ich soll mein Trinkverhalten beobachten. Ich bin jetzt auf der Suche nach einer Bar mit Spiegel.

Excelstammtisch letzte Woche.

Sabrina stelle eine interessante Frage:

In einer Liste – egal ob intelligente Tabelle oder Bereich – wurde ein Filter gesetzt:

Über diesen Autofilter wird in einer Spalte gefiltert:

Eine Zelle einer anderen Spalte wird markiert und kopiert:

Schaltet man den Filter über das Symbol „Alle löschen“ in der Registerkarte „Daten“ aus:

Wird leider der Kopiermodus deaktiviert. Der Zellinhalt kann nicht mehr in das Textfeld des Autofilters eingetragen werden:

Auch das Öffnen der Zwischenablage bringt keinen Erfolg.

Ich schlage vor, nicht die Zelle, sondern den Zellinhalt zu markieren und zu kopieren:

Klappt, stößt aber nicht auf Gegenliebe.

Josef meldet sich zu Wort. Man findet im Kontextmenü der Zelle den Befehl:

Nach dem Wert der ausgewählten Zelle filtern.

So erhält man die Schnittmenge beider Filter:

Deaktiviert man allerdings den ersten Filter:

hat man nur noch den zweiten Filter.

Sabrina ist begeistert und Excel nervt ein bisschen weniger.

Danke an Josef Feißt für diesen wertvollen Hinweis.

Du bist so süß, wenn du betrunken bist. Du bist auch süß, wenn ich betrunken bin.

Excelstammtisch letzte Woche.

Frank zeigt die unterschiedlichen Orte, an denen man Measures erstellen kann.

Wählt man Power Pivot / Measures / Measures verwalten, so kann man den Dialog an allen vier Seiten (und der Ecke) vergrößern:

Dagegen in einer Pivottabelle, deren Daten dem Datenmodell hinzugefügt wurden, kann der Dialog „Measure bearbeiten“ nur an der Ecke vergrößert werden:

Amüsant.

Danke an Frank Arendt-Theilen für den Hinweis.

Ich bin jetzt in einem Alter, in dem dir der Körper am nächsten Tag ganz leise ins Ohr flüstert: Mach das nie, nie wieder!

Excelstammtisch letzte Woche.

Volker präsentiert eine clevere Lösung für die Datenüberprüfung. Er weist auf folgendes Phänomen hin:

Liegt eine intelligente Tabelle auf dem gleichen Tabellenblatt, bewirkt eine Erweiterung, dass die Liste der Datenüberprüfung auch erweitert wird:

Liegt die intelligente Tabelle jedoch auf einem anderen Tabellenblatt, wird die Liste der Datenüberprüfung nicht erweitert.

Danke an Volker Pagel für den guten Hinweis.

Am kältesten ist es da, wo es am draußensten ist.

Hallo genervter Excel-Verliebter,

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.

Grüßle

Andreas (Thehos)

Früher haben wir uns gegenseitig eingecremt – da war es erotisch – heute ist es eher rheumatisch

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.

Hast du in den 15 Sekunden, die ich nicht im Raum war, die ganze Schokolade alleine gegessen? – Mach mich nicht für deine Fehler verantwortlich!

Excelschulung. Schwerpunkt: Listen.

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:

Vom Winterschlaf direkt in die Frühjahrsmüdigkeit. Ich führe ein Leben im Einklang mit der Natur.

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.

zeigen:

Du kochst gut. Sogar der Rauchmelder jubelt dir zu!

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!

Immer wenn ich sage, „ich bin auch nur ein Mensch“, meldet sich die Waage aus dem Hintergrund und ruft: „Anderthalb“.

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:

HURRA – die Fehlermeldung!

Alexa, stell den Wecker auf 4:30 Uhr. – Ich, wenn ich zu Besuch bei jemand bin, der eine Alexa hat.

Hi Rene

Wie geht es dir?

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:

"<h1>" & [Attribut] & "</h1>#(lf)<p>" & [Wert] & "<p>"

Die nicht mehr benötigten Spalten werden gelöscht, der Rest in Text konvertiert:

Und zurück nach Excel.

Man hätte die Zeilen in PowerQuery zu einem Wert zusammenfassen können – ich denke, es ist geschickter in Excel mit TEXTVERKETTEN zu erledigen.

Ihre Reaktion:

unglaublich, ich staune! Ich war mir sicher, dass du es kannst, wenn Excel es kann. Aber ich hab schon an Excel gezweifelt.

Danke vielmals!

Was für ein Gefühl muss der Tropfen haben, der das Fass zum Überlaufen bringt. (Nikolaus Cybinski)

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:

Schade!

Wenn man das Müsli mit Eierlikör anrührt, sieht der Tag gleich viel freundlicher aus.

„Guten Morgen,

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:

  1. 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.

Ich hab als Kind viel mit Autos gespielt, jede Barbie hatte eins!

Ohne zu spicken – kennst du die Antwort?

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:

Weihnachten ist vorbei – ich habe alle Cookies gelöscht!

Ein bisschen doof ist es schon:

Ich habe eine (intelligente) Tabelle.

Ich füge eine Ergebniszeile hinzu – dort wird gerechnet:

Ich entferne die Farben und wandle sie in eine Liste um:

Was passiert? Die Formeln
=TEILERGEBNIS(109;Tabelle1!$E$2:$E$10)

bleiben stehen. Ebenso die Beschriftung der ersten Zelle: „Ergebnis“:

Wandelt man diese Liste nun erneut in eine Tabelle um:

ist die Formelzeile nun Teil der Tabelle.

Man kann erneut eine Ergebniszeile hinzufügen – das Ganze ist nun recht verwirrend:

Ich nehme dieses Jahr an Weihnachten keine Pakete für die Nachbarn an. Letztes Jahr war nur Schrott drin.

Verblüfft. Ich erstelle eine Pivottabelle und möchte die Jahreszahlen gruppieren:

Eine Fehlermeldung ist die Folge:

Kann den markierten Bereich nicht gruppieren.

Okay? – und warum?

Ein Blick in die Daten liefert die Lösung: die Jahreszahlen sich als Text formatiert?!!?!

In Zahlen umwandeln – dann klappt es …

Hört bitte auf, euch an Weihnachten den perfekten Mann zu wünschen! Ich wurde schon drei Mal gekidnappt.

Christian ist irritiert. Ich auch.

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!

Danke an Christian Gröblacher für diesen Hinweis.

Advent ist, wenn der Dachboden entrümpelt und alles wieder in der Wohnung verteilt wird.

Boah, was ist denn das? Ich bin sehr irritiert!

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)

Wenn deine Freundin fragt: „Schatz, kann ich so rausgehen“ – ist „Klar, ist ja schon dunkel“ die falsche Antwort.

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.

Es gibt Leute, da denke ich mir im Gespräch plötzlich: Ach guck mal, die Evolution macht auch mal Pause.

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:

Sehr versteckt!

Wenn ich zwei Dinge gleichzeitig kann, dann ist es lächeln und dabei Mordgedanken hegen.

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!?!

Nehmen Sie Ihren Köter hier weg, ich spür schon einen Floh! – Komm, Rex, gehn wir. Die Frau hat Flöhe.

Das ist mir ja noch gar nicht aufgefallen. Volker hat darauf aufmerksam gemacht:

In einer Excelmappe befindet sich ein Tabellenblatt („Kontinente“) mit sechs intelligenten Tabellen: tbl_Europa, tbl_Antarktis, tbl_Afrika, …

Die Datei wird einmal als Excel-Arbeitsmappe (XLSX) und einmal Excel-Binärarbeitsmappe (XLSB) gespeichert.

Greift man mit PowerQuery auf die XLSX-Mappe zu, ist das Ergebnis bekannt: angezeigt wird das Tabellenblatt und die sechs intelligenten Tabellen:

Beim Zugriff auf die XLSB-Datei dauert der Zugriff nicht nur sehr, sehr lange – angezeigt wird nur das Tabellenblatt:

Danke an Volker Pagel für diesen Hinweis.

Sein Fazit: Don’t use xlsb!

Seine Kollegen kommentieren es:

Andreas:

Vielleicht ist xlsb dem alten Format xls zu ähnlich. Die xls aus 2003 lässt sich auch nicht einlesen.

Jens:
Volle Zustimmung!

Über Excel.Workbook ([Content]) werden xlsb auch nicht erkannt.

Ist echt nen Problem…xlsb ist bei uns relativ beliebt, da xlsm nicht per Mail versendet werden kann. Makro sind BÖSE 

Aber xlsb kann auch Makros enthalten.

Oberste Regel beim Putzen mit lauter Musik: die Klobürste ist nie, nie, nieeee das Mikrophon. Niemals!

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.

Neun von zehn Enten empfehlen Rindersteak zu Weihnachten.

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 …

Ich gehe mal raus. Habe gehört, bei dem Sturm fliegt alle 11 Minuten ein Sigle vorbei. Ich orkanshippe jetzt.

Einfach übersehen. PowerQuery-Schulung. Wir wollen auf einen Sharepoint-Ordner zugreifen:

Geht aber nicht. Ich frage meinen Freund und Kollegen Hans-Peter Pfister um Rat. Seine Antwort:

Hoi René

Nur kurz, ohne viel drum-herum, bin gerade unter Wasser.

Nimm den SharePoint Ordner Konnektoren, nicht den für SP-Liste.

Das Leben kann manchmal so einfach sein!

Heute kommt Mutti und bringt zehn Original Thüringer mit. Ich weiß gar nicht, wo die alle schlafen sollen.

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.

Superman und Chuck Norris hatten eine Wette. Der Wetteinsatz: der Verlierer muss in Strumpfhosen rum laufen. Der Ausgang ist bekannt.

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.

Ich bin jetzt in dem Alter, in dem ein Schneidersitz mit einer dreitägigen Ganzkörperlähmung bestraft wird.

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 …

Rapper rappen, Rockstars rocken, aber was machen eigentlich Popstars?

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

Hallo Stephen,

schau mal in der Autokorrektur nach.

LG :: Rene

Ich hab mich am Bahnhof durch ein mobiles Impfteam impfen lassen und hab jetzt eine Frage: Ist es normal, dass das Zeug mit einem Löffel heiß gemacht wird und warum wollten die 200 Euro, obwohl ich doch krankenversichert bin?

Hallo Rene,

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.

Verwende ich oft und gerne.

In meinem Horoskop stand, dass ein großer Reichtum auf mich zukommen wird. Heute bin ich fast von einem Geldtransporter überfahren worden.

Hallo Rene,

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.

4. Schau mal:

https://support.microsoft.com/de-de/office/wo-ist-power-pivot-enthalten-aa64e217-4b6e-410b-8337-20b87e1c2a4b

Liebe Grüße

Rene

PS: Danke an Hans-Peter Pfister für den Link.

Immer wenn ich Berichte über Haiattacken sehe, frage ich mich ,wie blöde muss man denn eigentlich sein? Ich meine – das hört man doch schon an der Musik, wenn der Hai näher kommt.

Schöne Frage gestern in der PowerQuery-Schulung:

Warum kann man bei vielen Befehlen, beispielsweise dem Filtern, den Dialog wieder anzeigen lassen, um dort schnell Änderungen vorzunehmen:

Jedoch nicht beim Ändern des Datentyps?

Man müsste die Korrekturen in der M-Codezeile vornehmen oder erneut in den entsprechenden Spalten.

Gestern mehrere Krimis im Internet bestellt. Heute Angebot für Messer und Müllsäcke bekommen. Die denken echt mit!

Das ist mir ja noch nie aufgefallen! Eine Teilnehmerin in der letzten Excelschulung hat mich darauf aufmerksam gemacht.

Setzt man in einer (intelligenten) Tabelle den Mauszeiger zwischen Überschrift und erste Zeile, kann man nur die Überschrift markieren:

Ist allerdings die Überschrift markiert …

… führt ein Klick auf die Unterkante dazu, dass die ganze Spalte markiert wird.

Aha – wieder etwas gelernt!

Eine der Nudeln schwimmt im Kochtopf oben. Das macht mich wahnsinnig: ist die tot oder was?

Hallo Rene

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.

Wirf mal einen Blick in das PDF in

https://www.compurem.de/buecherdownloads/Zahlenformate.zip

– 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.

Liebe Grüße

Rene



Wie groß können Frösche werden? – Das kommt darauf an, wie oft man mit dem Auto darüber fährt. – Schweigen. – Ich sollte Moderator einer Kindersendung 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.

Oh, ein neues Möbelstück. Lass uns darauf anstoßen! – Kleiner Zeh: o ja, gerne!

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:

Das dürfte genügen.

Fürs Erste.

Neue Ultraschall-Zahnbürste benutzt. Bin jetzt mit 127 Fledermäusen und mit Batman im Badezimmer.

Schöne Frage in der letzten PowerBI-Schulung, auf die ich keine Antwort wusste:

Warum steht bei den Beziehungen in PowerBI eine Kreuzfilterrichtung zur Verfügung:

im Datenmodell in Excel dagegen nicht?

Übrigens: Gute Erklärungen, was Kreizfilterrichtungen sind, finde ich auf:

https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-create-and-manage-relationships#understanding-additional-options

und auf:

https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-create-and-manage-relationships

Ich bin wie Batman. Ich muss oft nachts raus.

Hi Rene,

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.

https://docs.microsoft.com/en-us/office/vba/api/excel.sortfield

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.

Tja!

Hilft das?

Liebe Grüße

Rene

Solange mir niemand das Wasser reichen kann, nehme ich das Bier.

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.

Good to know!“

Verkaufe gebrauchte facebook-Freunde. Sie liken nicht, und haben noch nie kommentiert, aber sie wissen alles über mich.

Letzte Woche in der PowerBI-Schulung. Wir schauen uns einige der DAX-Funktionen an. Beispielsweise CONCATENATE.

Es folgt die Frage, warum man mit der Excel-Funktion VERKETTEN ( CONCATENATE) mehrere Texte verketten kann:

Dagegen in DAX:

Zwei Texte sind kein Problem. Jedoch bei drei Texten:

=CONCATENATE(tbl_ottos_mops[ottos];" ";tbl_ottos_mops[mops])

Also verketten wir in DAX mehrere Texte mit dem et-Zeichen „&“. Das funktioniert!

=tbl_ottos_mops[ottos]&" "&tbl_ottos_mops[mops]&" "&tbl_ottos_mops[Spalte1]&" "&tbl_ottos_mops[Spalte2]

An alle, die behaupten, sie wüssten, wie der Hase läuft: Er hoppelt!

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?

Boah – 38 Grad im Schatten – das hält ja keine Sau aus! – Meine Fresse – warum gehst du auch in den Schatten!

Eine schöne Frage, die gestern in der Excel-Schulung kam. Ich zeigte wie man Listen sortiert und dass man auch nach Farben sortieren kann.

Dann zeigte und erläuterte ich den Autofilter. Auch dort kann man nach Farben filtern:

Es kam die Frage, warum man nicht nach mehrere Farben filtern könne – schließlich könne man auch nach mehreren Farben sortieren:

Stimmt – warum eigentlich nicht? Ich weiß es nicht!

Mal den Teufel nicht an die Wand! – Aber das ist doch ein Spiegel, in den du hineinschaust …

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:

Ich habe jetzt genau das richtige Alter. Muss nur noch herausfinden für was.

Nicht aufgepasst. Nicht genau hingeschaut.

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

= Table.AddKey(#"Geänderter Typ",{"Geschlecht"}, true)

ist nicht der Grund!

Schließlich finde ich des Rätsels Lösung: ich habe beim Verknüpfen der beiden Listen aus Versehen die Fuzzy-Logik aktiviert.

Haken raus – und ich erhalte wieder die ursprünglichen 20.000 Zeilen.

Ich habe gerade versucht, den Mähroboter mit einem Grasbüschel vom Nachbarn an den Gartenzaun zu locken. War wohl schon satt.

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 …

Wie ist dein Verhältnis zu deinen Eltern? – 1:2

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!

Als Kind dachte ich, dass Spargel und Erdberen illegal seien, weil wir ihn aus Autos auf abgelegenen Parkplätzen gekauft haben.

Hallo Rene,

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:

MeineSumme:=
CALCULATE(SUM(tblBestellungen_pre[Frachtkosten]);
DATESBETWEEN(tblBestellungen_pre[Bestelldatum];
date(2019;2;1); date(2019;2;27)) )

Weißt du, wie ich hier jetzt das Datum aus den Zellen hineinbekomme?

Auch DAS war schnell geklärt:

Hi Claus,

wenn tblFrom und tblTo im Datenmodell liegen, dann mit:

MeineSumme:=CALCULATE( SUM(tblBestellungen_pre[Frachtkosten]);DATESBETWEEN(tblBestellungen_pre[Bestelldatum]; MAX(tblFrom[From]); MAX(tblTo[To])))

Und kann man Datenmodell und Meassure in einer Pivottabelle in Excel verwenden.

Hübsche Fingerübung!

Ich hasse es, wenn ich ein Lied laut mitsinge und der Interpret den Text nicht kann!

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:

Natürlich spreche ich mit mir selbst. Manchmal brauche ich eben eine kompetente Beratung.

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!

Und: während sich auf der Microsoft-Hilfeseite

https://docs.microsoft.com/de-de/powerquery-m/number-round

leider kein Hinweis dazu findet, hat Ken Pulse es sehr gut erläutert:

https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/

XLarium weist darauf hin, dass es noch weitere Artikel und Videos zu diesem Thema gibt:

MrExcel hat vor kurzem ein Video dazu gemacht:

Weitere Videos zum Thema Runden:

MROUND In Power Query

TRUNC In Power Quer

CEILING oder FLOOR in Power Query

Ich habe 25 Jeans im Schrank. Eine, die ich jeden Tag trage, eine hippe, die ich nie trage, weil ich eigentlich nicht hip bin und 23 ausrangierte aus den Jahren 2000 – 2020, die ich für potenzielle Maler-, Garten-, Renovierungs- oder Umzugsarbeiten aufgehoben habe.

Zugegeben: geschmunzelt habe ich schon:

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!

Ich sitze im Café und verbinde mich mit dem Netzwerk „Martin Router King“. Was soll man sagen? – „I have a stream.“

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.

Nach zwei Stunden Babysitten glaube ich, dass ich doch keine Kinder, sondern lieber etwas Harmloseres möchte. Krokodile zum Beispiel. Oder Löwen.

Hallo Excel-Meister

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:

  1. Prüfe, ob User die pbix geöffnet hat.
  2. Falls nicht, bitte freundlich darauf aufmerksam machen
  3. 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é

ich danke dir!

Hier der Code, falls es dich interessiert.

Sub GetData()

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚ Code erstellt durch: Pfister BI Consulting GmbH

‚ Zweck: Holt Metadaten aus der Power BI Datei

‚ Erstelldatum: 8.3.2021

‚ Aenderungsdatum:

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚Testen, ob pbix Datei geöffnet ist. Falls nicht, Mesagebox und fragen, ob sie geöffnet werden soll. Fall nicht, Abbruch

    If Dateigeoeffnet(Range(„Dateipfad_PBIX_Original“)) = False Then

        If MsgBox(„Die Datei muss geöffnet sein. “ & Chr(10) & “ Soll die Datei geöffnet werden?“, vbYesNo, „Power BI Datei öffnen?“) = vbNo Then

            Exit Sub

            Else: Call Open_PBIX

                Application.Wait (Now + TimeValue(„0:00:10“))

            End If

   End If

’notwendige Abfragen aktualisieren

Abfragen_starten:

On Error GoTo ErrHandler

    ActiveWorkbook.Connections(„Abfrage – Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Memory Usage Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Tabellenliste“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Liste nicht geladene Queries“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Abfragen – nicht geladen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

  Call Listen_befuellen

ErrHandler:

    ‚Fehler No. 1004 abfangen

    If Err = 1004 Then

        If MsgBox(„Soll der Prozess abgebrochen werden?“, vbYesNo, „Bitte Identifikation vornehmen“) = vbYes Then

            Exit Sub

        ’sonst Abfrage wieder aufnehmen

            Else: Resume Abfragen_starten

        End If

    End If

End Sub

Gruss Hp

Nachricht von den Nerven: „Falls du uns suchst – wir sind am Ende.“

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 …

Jeder sollte jemanden haben, der im entscheidenden Moment nachschenkt.

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:

https://www.youtube.com/watch?v=9UI9IwDVlGc

Ich brauche heute einen zweiten Kaffee, der nachguckt, warum der erste seine Arbeit nicht macht …

Ich habe ein Problem mit Excel 2016:

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:

https://www.fakenamegenerator.com/

Was sagt der Teig zum Bäcker? – Ich bin gerührt!

Guten Tag Herr Martin,

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.

Seit ich gesehen habe, wieviel schlanker ein Bär nach seinem Winterschlaf ist, kommt mir das Konzept noch viel attraktiver vor.

Hallo Rene,

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

Ich habe gerade „ach-da-brauchst-du-nichts-draufschreiben-man-sieht-ja-was-drin-ist“ aus der Gefriertruhe geholt und bin gespannt, was ich heute koche.

Hi Rene,

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 🙂

Laut ADAC sind 11 Winterreifen gut. Nach meiner Erfahrung sage ich: 4 genügen!

Guten Morgen,

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.

Mein Liebesleben 2020: Der Kühlschrank hat mich öfters nackt gesehen als irgend jemand anders!

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!
  • Filter einschalten!

Sweet dreams are made of cheese, who am I to dis a Brie?

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!

Ich nehme ja gerne mal Anhalter mit. So fangen zwar viele Horrorfilme an, aber auch viele Pornos.

Mourad Louha stellt die neuen Datentypen vor, die man über Powerquery erstellen kann:

http://www.excel-ticker.de/eigene-datentypen-mit-power-query-erstellen/

In der Registerkarte „Transformieren“ findet man das Symbol, um eigene Datentypen zu erstellen:

Gibt man die Daten zurück, erhält man das typische Symbol der Datentypen:

Mit einer Datenüberprüfung wird eine Dropdownliste geschaffen. Aus ihr wird das Attribut ausgewählt: =[Zelle].Attribut:

Mourad bemängelt, dass man die Datentypen leider nicht in Abfragen verwenden kann. Schade ist auch, dass sie nicht in der Registerkarte erscheinen:

Einige Dinge fehlen – aber ich zuversichtlich, dass Microsoft hier „nachrüsten“ wird.

79% der Abonnenten hat nicht gemerkt, dass ihr Fitnessstudio geschlossen ist.

Fast wäre ich reingefallen. Folge Mail erreichte mich:

Hallo René

Du hast mir kürzlich mit dem SVerweis geholfen.

Nun habe ich wiederum ein kurze Frage:

Im Anhang die Angaben von Personen. Ich möchte diese aufteilen in Anrede (Mr.), Vorname und Nachname.

Wie geht das?

Herzliche Grüsse

Andreas

Das kann doch kein Problem sein, dachte ich klickte auf den Assistenten „Daten / Text in Spalten“. Doch der belehrte mich eines besseren:

Ich schreib zurück:

Hallo Andreas,

die Funktion

=CODE(TEIL(A2;4;1))

liefert 160. Das ist der ASCII-Code des „Leerzeichens“ zwischen „Mr.“ und „Rüdiger“. Das Leerzeichen hat Code 32.

Woher hast du die Daten? Aus dem Internet? Aus Word? Von einem fremden System? DAS hat kein Mensch getippt!

Antwort: Wir brauchen Leerzeichen!

Markiere das Teilchen zwischen „Mr.“ und „Rüdiger“. Kopiere es.

Dann rufe den Ersetzen-Dialog auf (Strg + H).

Ersetze (Strg + V) durch Leerzeichen.

Das Ergebnis sieht aus wie vorher.

Dann kannst du die Spalte A markieren und mit dem Assistenten Daten / Text in Spalten trennen. Voilà.

Klappt das?

Liebe Grüße

René

Hoi René

Perfekt! Hat super geklappt!

LG Andreas

Deutsche stellen den Löwenanteil der Migranten Österreichs. Sollte sich die Entwicklung fortsetzen, muss damit gerechnet werden, dass in 50 Jahren alle Österreicher Deutsch sprechen.

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

Zum Glück war Halloween. So konnte ich das alte abgelaufene Naschzeugs an die dicken Nachbarkinder verteilen.

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.

Kann mann mit Männergrippe schon Pflegestufe 3 beantragen?

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:

xlBlatt.Range("A1").CurrentRegion.Sort Key1:=xlBlatt.Range("J1"), Order1:=xlAscending, Key2:=xlBlatt.Range("M1"), Order2:=xlAscending, Key3:=xlBlatt.Range("K1"), Order3:=xlAscending, Header:=xlYes

xlBlatt.Range("A1").CurrentRegion.Sort Key1:=xlBlatt.Range("I1"), Order1:=xlAscending, Header:=xlYes

Geht doch!

Hast du schon wieder mein Deo benutzt? – Ich bin Robin Hood! Ich stehle und verteile es unter den Armen!

Ups, das ist mir noch nie aufgefallen! Warum? Weil ich SO nicht arbeite.

Aus einer Datenquelle wird mit PowerQuery eine Abfrage gestartet. Auf diese Liste wird eine Formel aufgesetzt, allerdings nicht in der Schreibweise

=KKLEINSTE(Artikel[Einzelpreis];Artikel[@Einzelpreis])

rechnet, sondern in der Bezugsschreibweise:

=KKLEINSTE($F$2:$F$78;ZEILE(A1))

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!

Pflanze im Internet bestellt. Email erhalten: „Ihre Bestellung ist eingegangen.“ Neue bestellt.

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:

Tipps gegen Schimmel im Badezimmer: Langsam mit einer Karotte ins Bad gehen. Dann zum nächsten Pferdehof locken.

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), …

Klappt auch:

Wir müssen ja sowieso denken. Warum dann nicht gleich positiv?

Irgendwie doof.

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 …

Lange Rede. Gar kein Sinn.

Immer mehr Medien berichten, „wie ein Excel-Fehler 16 000 britische Corona-Fälle verschwinden ließ“:

https://www.heise.de/news/Excel-Datei-voll-16-000-Diagnosen-fuer-Covid-19-in-Grossbritannien-nachgemeldet-4920380.html?fbclid=IwAR2JItI853Rg41P4–X1A-0XZTPnUabpEdZ9z0nSYF-10B9F4VeLHnQuXso

oder:

https://www.sueddeutsche.de/digital/excel-microsoft-coronavirus-grossbritannien-1.5056482?fbclid=IwAR3NT0BO7GsqHtXgclkN2gb7jf3hZrqVV129VQKifRAG61CMx3DHsZhrFeI

oder:

https://t3n.de/news/excel-verursacht-corona-panne-1326375/?

oder:

https://www.faz.net/aktuell/feuilleton/pandemie-datenpanne-in-england-die-tabelle-ist-voll-16989088.html?fbclid=IwAR1W0MHXBshgE5vXZMv9UM-zuga68DVkfA2jm_qSA8TxxwGRCMeab9ve3BQ

Die Häme und den Spott brauche ich nicht zu wiederholen.

Ein Dankeschön an Bettina Berger und Andreas Thehos, die mich darauf aufmerksam gemacht haben.

Suche Disneyprinzen, dessen Gesang Kleintiere inspiriert meine Wohnung zu putzen.

Daten sollen verknüpft werden; Daten sollen getrennt werden:

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.

Alkoholtest gemacht – vertrage alle gängigen Sorten.

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.

Am Ende der Nerven ist oft noch zu viel Kind übrig.

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:

Herr Doktor, ich bin Hypochonder. – Ach was, das bilden Sie sich nur ein!

Lieber René,

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!

Liebe Grüße

Rene

Jahrelang hat man uns eingeredet, dass nach 18 Uhr gegessene Kohlenhydrate dick machen. Und jetzt kommt raus: Kohlehydrate wissen gar nicht, wie spät es ist.

Hallo Rene!

Nach längerer Zeit wieder eine Bitte!

Durch den Corona-Virus musste ich die Termine für meine ABO-Vorstellungen selbst zusammenstellen.

Die Auswahl der Stücke war in ‚ABO Stücke‘ bald erstellt. Mit Pivot habe ich mir die Theaterstücke zusätzlich unterschiedlich angeordnet. 

In diesen Tabellen wollte ich die Zellen färbig (Der Leuchtturm) markieren, die in ‚ABO Stücke‘ grün sind. Das war mir nicht möglich.

Im Internet habe ich entdeckt, dass eine Zellfärbung in der Pivot-Tabelle nur mit den dortigen Daten möglich ist.

Nachdem Excel so viele Möglichkeiten bietet die Daten aufzubereiten und darzustellen meine oben erwähnte Bitte

um eine Lösung.

Mit freundlichen Grüßen

Peter

Hallo Peter,

das geht so nicht. Excel kann nicht per Formel Formatierungen abfragen.

Du benötigst im Tabellenblatt „ABO Stücke“ eine Hilfsspalte (bspw. Premiere: ja/nein)

und kannst dann mit einer Formel in der bedingten Formatierung anzeigen lassen:

=UND(INDEX(‚ABO Stücke‘!$F:$F;VERGLEICH($A15;’ABO Stücke‘!$D:$D;1))=“ja“;
B$14=INDEX(‚ABO Stücke‘!$B:$B;VERGLEICH($A15;’ABO Stücke‘!$D:$D;1)))

wirf mal einen Blick auf die Datei – ich habe drei Mal „ja“ eingefügt.

Liebe Grüße Rene

Hallo Rene,

danke für die schnelle Hilfe und den Hinweis.

Da stand ich auf der Leitung.

Klar, dass Excel nur mit Zahlen und Buchstaben arbeiten kann. Die Auswahl mit farbiger

Zelle ist optisch leichter zu überblicken, darum habe ich sie angewendet aber nicht bedacht,

dass Pivot natürlich einen ‚echten‘ Wert benötigt.

Mit besten Grüßen Peter

gerne, Peter,

stimmt: EXCEL kann nur Zahlen und Texte verarbeiten. Mit VBA hätte man die Möglichkeit zu überprüfen:

If Zelle.Interior.Color = …

Liebe Grüße Rene

Ich hätte gerne den Schokoriegel mit dem Löwen. – Lion? – Nein, kaufen!

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.

Danke an Andreas Thehos für diesen Hinweis.

Ich war gestern doch gar nicht so betrunken. – Du hast den Duschkopf in den Arm genommen und gesagt, er soll aufhören zu weinen!

Diese Seite zu den DAX-Funktionen sollte Microsoft noch einmal überarbeiten.

https://docs.microsoft.com/de-de/dax/dax-overview

Zum einen finden sich dort verwirrende Denglish-Erklärungen. Zum andere erstaunen Aufzählungen. Die trigonometrische Kreisfunktion TAN wird den statistischen Funktionen zugeordnet. Ups!

Ich war reich und sexy. Und dann klingelte der Wecker.

Seltsam – da fehlen einige Spalten in PowerPivot.

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!

Ich diskutiere nicht. Ich erkläre nur, warum ich recht habe.

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:

Verbindung wird aktiv setzen und –

schon sieht das Ergebnis besser aus!

Papa, bekomme ich eine Delfintherapie? – Im Kühlschrank steht ein Glas Rollmöpse. Die kannst du aufwickeln und streicheln.

Nicht konsequent.

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.

Polizei: „Ihre Reifen sind aber ganz schön abgefahren.“ Ich: „danke. Ihr Blaulicht fetzt aber auch gut.“

Ich habe mal wieder nicht aufgepasst!

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:

Gib einem Mann einen Putzlappen und er wird ihn ratlos angucken. Gib ihm einen Hochdruckreiniger und er wird alle reinigen, soweit das Kabel reicht.

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:

Kühlschrank sauber gemacht. Der Käse hielt ein Referat über Vergänglichkeit und hatte die Haare schön.

Hallo Herr Martin,

herzlichen Dank für Ihre Mühe!.

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.

Nochmals vielen lieben Dank!

Brettspiel für eine Person? – Bügeln!

PowerQuery-Schulung. Frage eines Teilnehmers:

Von einer Liste sollen gleiche Werte gruppiert und deren Elemente durch Komma getrennt in einer Zelle zusammengefasst werden. Also, aus

A 1
B 2
B 3
C 4

Soll werden:

A 1
B 2, 3
C 4

Ich habe eine Weile überlegen müssen. Und hier die Lösung:

Schritt 1: Die Tabelle wird importiert. Die erste Spalte wird sortiert, damit die Elemente untereinander stehen. Diese Tabelle wird dupliziert:

Die zweite Tabelle wird gruppiert, so dass die Anzahl der Elemente ermittelt werden kann:

Diese beiden Tabellen werden mit einander verknüpft (Home / Kombinieren / Abfragen zusammenführen / Abfrage als neue Abfrage zusammenführen):

Die Tabelle wird „aufgeklappt“.

Eine Indexspalte, die mit 0 beginnt, wird eingefügt.

Die Funktion List.Range gruppiert die Werte:

List.Range(#“Added Index“[Artikelname],[Index],[#“Artikel (2).Anzahl“])

Zur Erklärung:

  • #“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.

Das Ergebnis sieht dann wir folgt aus:

Knifflige Aufgabe …

Ich habe einige Punkte von der ToDo-Liste auf die WasSolls-Liste gesetzt.

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!

Der Ernst des Lebens beginnt in dem Moment, wo du keine Gratikwurstscheibe mehr bekommst.

Ist das im Sinne des Erfinders?

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, …

1 2 3