Eine Liebeserklärung an MS Excel
Ob man auf nur einige Teilergebnisse in einer Pivottabelle anzeigen kann, möchte eine Teilnehmerin wissen, nachdem ich in der Excelschulung gezeigt habe, wie man alle Teilergebnisse ein- und ausblendet.
Mit Formeln ist so etwas möglich – aber mit Pivottabellen sicherlich nicht.
Excelschulung. Schwerpunkt: Pivottabellen.
Wir fassen das Alter zusammen und gruppieren es in 10er-Schritten, fassen Geschlecht zusammen und berechnen den Mittelwert der Einkommen:
Ein Teilnehmer will es wissen und rechnet nach:
der Mittelwert der oberen vier Mittelwerte entspricht nicht dem Mittelwert des Gesamtergebnisses:
Ich kläre seinen Denkfehler (Rechenfehler?) auf:
Der Mittelwert von 2 und 2 ist 2, von 4, 4, 4 und 4 ist 4. Der Mittelwert von 2 und 4 ist 3; der Mittelwert von 2, 2, 4, 4, 4, und 4 ist jedoch 3,33.
Man muss bei dem gewichteten Durchschnitt aufpassen.
Excelschulung. Ich zeige, wie man eine Pivottabelle erstellt:
Eine Teilnehmerin meldet sich und sagt, dass die Überschriften (die Feldnamen) bei ihr nicht angezeigt werden:
Ich musste drei Mal hinschauen, bis ich es verstanden hatte: sie hatte in den Optionen der Pivottabelle den Abschnitt für die Felder ausgeblendet …
Schöne Frage in der Excelschulung, als wir Pivottabellen üben: Eine Teilnehmerin hat eine Tabelle mit Kategorien, Datumsangaben und Preisen:
Sie möchte die Kategorien gruppieren – aber jeweils von dem neuesten Datum den zugehörigen Wert sehen. Also so:
Mit der Funktion EINDEUTIG kann man die Werte gruppieren, mit XVERWEIS den letzten Wert aus der sortierten Liste herausholen:
=XVERWEIS(A11;$A$2:$A$7;$C$2:$C$7;;;-1)Oder man kennzeichnet den letzten Wert der Liste – beispielsweise mit MAXWENNS:
=WENN(MAXWENNS($B$2:$B$7;$A$2:$A$7;A2)=B2;"x";"")
Und setzt auf diesen Bereich eine Pivottabelle auf:
Aber ohne Hilfsspalte? Ich wüsste nicht, wie man lösen könnte …
Excelschulung.
Ich zeige, wie man einen Text sucht:
Ups – stimmt – seit ich die Fokuszeile habe, markiert Excel nach der Suche Zeile und Spalte. Das haben die Teilnehmerinnen und Teilnehmer der Schulung noch nicht.
Wir erstellen eine Pivottabelle:
Ups, stimmt: bei mir werden die Zahlenformate (hier: Euro) mit in die Pivottabelle genommen. Das haben die Teilnehmerinnen und Teilnehmer der Schulung noch nicht.
Ich füge einen neuen Datensatz unter die Tabelle:
Dieser wird allerdings – auch nach Aktualisierung – nicht in die Pivottabelle aufgenommen.
Ich stutze. Wieder eine neue Funktion in Excel? Werden neue Daten nicht mehr übernommen? Wo bleibt die neue Kategorie „Black“? Muss ich einen Schalter betätigen? Gibt es wieder neue Features – denn: bei den Teilnehmerinnen und Teilnehmern funktioniert es!
Da entdecke ich es: ich habe einen Filter über die Pivottabelle aktiviert. Deshalb werden die Daten des neuen Datensatzes nicht angezeigt. Nicht an allem sind neue Befehle in Excel schuld …
Amüsant.
Excel-Schulung. Wir färben in einer Liste einige Zellen gelb ein – das sind die verhaltensauffälligen Personen:
Ich zeige, dass man über den Assistent „benutzerdefiniertes Sortieren“ nach Farbe sortieren kann:
Später filtern wir die Daten – ich zeige und erkläre den Autofilter.
Wir filtern die gelb formatierten Zellen:
Ein Teilnehmer meldet sich und sagt, dass bei ihm dieser Befehl ausgegraut ist:
Ich gehe auf die Suche und werde fündig: seine gelben Zellen befinden sich am Ende der 12.000-Zeilen langen Liste – er hatte sie nach unten sortiert:
Die Ursache: Excel prüft vor dem Sortieren und Filtern nur die oberen Zellen (die genaue Zahl konnte ich nicht ermitteln) – Position 12.000 ist „zu weit unten“ – deshalb werden diese Kriterien nicht mehr im Filter angezeigt.
Lieber Herr Martin,
besten Dank, dass ich mich an Sie wenden darf. Ich habe eine Frage zum Layout bei Pivot-Tabellen.
Und zwar möchte ich mehrere Zeilenbeschriftungen nebeneinander anzeigen. Wenn ich mehrere Parameter für die Zeilen auswähle, werden mir diese aber nur untereinander angezeigt (siehe auch Beispiel-Datei anbei). Mir ist bewusst, dass dies in der aktuellen Datei keinerlei Mehrwert bietet, diese ist aber nur ein vereinfachtes Exzerpt einer größeren Datenbasis.
Ich füge unten noch ein Beispiel einer dritten Partei an, in welcher die Pivot-Darstellung meiner Vorstellung entspricht.
Besten Dank für Ihre Unterstützung und beste Grüße
####
Hallo Frau Schreiber,
Schalten Sie über die Registerkarte Entwurf die Option „im Tabellenformat“ ein:
Und anschließend die Teilergebnisse aus:
Die Einstellung können Sie als Standard über Datei / Optionen / Daten festlegen:
Eine clevere Idee, die ein Teilnehmer in der Excelschulung hatte.
Wir erstellen einen Kalender, bei welchem die Namen von einem zentralen Blatt („Mitarbeiter“) gezogen werden:
=WENN(Mitarbeiter!A3="";"";Mitarbeiter!A3)
Warum ich nicht eine intelligente Tabelle nehmen würde, fragte der Teilnehmer. Mit einer Spalte?
Und klar – mit der Formel
=tbl_Mitarbeiter[Namen]spart man sich die WENN-Funktion und auch die Frage „wie weit ziehe ich die Formel nach unten“?
Ernst schickt mir eine Lösung zur korrekten Berechnung der Kalenderwoche nach ISO in Power Query:
Hallo Rene,
Du hast in letzter Zeit mehrere Beiträge zu Power Query gepostet.
Vor einiger Zeit habe ich eine Möglichkeit beschrieben, die Iso-Kalenderwoche mit WORD-Feldfunktionen zu berechnen. Nun habe ich den verwendeten Algorithmus auf Power Query „M“ übertragen.
Die benutzerdefinierte Funktion sieht sieht dann in der einfachen Variante wie folgt aus.
InputDaten as any) as any =>
let
Quelle = DateTime.Date(InputDaten),
Wochentag = Date.DayOfWeek(Quelle,Day.Monday)+1,
Jahr = Date.Year(Date.AddDays(Quelle, 4-Wochentag))-1
IsoKw = Number.IntegerDivide(Duration.Days(Quelle – #date(1901,1,1)) – Wochentag-Duration.Days(#date(Jahr,12,21) – #date(1901,1,1)),7)
in
IsoKw
In einer Version, in der eine Erläuterung mit angezeigt wird sieht sie wie folgt aus.
let
//Errechnet die Kalenderwoche nach ISO 8601 eines Datums.
ISOKW =
let
Function = (InputDaten as any) as any =>
let
Quelle = DateTime.Date(InputDaten),
Wochentag = Date.DayOfWeek(Quelle,Day.Monday)+1,
Jahr = Date.Year(Date.AddDays(Quelle, 4-Wochentag))-1,
FunctionResult = Number.IntegerDivide(Duration.Days(Quelle-#date(1901,1,1))-Wochentag-Duration.Days(#date(Jahr,12,21)-#date(1901,1,1)),7)
in
FunctionResult,
FunctionType = type function (InputDaten as any) as any
meta [
Documentation.Name = „ISOKW“,
Documentation.LongDescription = „Calculates the calendar week according to ISO 8601 of a date.“,
Documentation.Examples = {
[Description = „“, Code = „ISOKW(#date(2024,12,30)“, Result = „1“]
}
],
TypedFunction = Value.ReplaceType(Function, FunctionType)
in
TypedFunction
in
ISOKW
Was mir an dieser cleveren Lösung gut gefällt, ist der Teil der Metadaten. Diese Teile werden beim Selektieren der Funktion angezeigt. Große klasse!
Hallo Rene,
wie ich gesehen habe, hast Du meinen Beitrag zur Berechnung der Iso-Kalenderwoche veröffentlicht. Ich habe noch eine kurze Anmerkung zu dieser PQ-Funktion.
Die Zeile
FunctionResult = Number.IntegerDivide(Duration.Days(Quelle-#date(1901,1,1))-Wochentag-Duration.Days(#date(Jahr,12,21)-#date(1901,1,1)),7)
kann durch die kürzere Version
FunctionResult = Number.IntegerDivide(Duration.Days(Quelle-#date(Jahr,12,21))-Wochentag,7)
ersetzt werden.
Außerdem habe ich die Verwendung von Metadaten in eine weitere benutzerdefinierte Funktion (TrimAll) integriert.
Diese Funktion entfernt die führenden und nachfolgenden Leerzeichen aus einem Textwert und ersetzt alle Mehrfachleerzeichen durch ein einzelnes Leerzeichen.
___________________________
Let
// Erstellt von Ernst-A. Börgener
//Entfernt die führenden und nachfolgenden Leerzeichen aus einem Textwert und ersetzt alle mehrfachen Leerzeichen durch ein einzelnes Leerzeichen.
TrimAll =
let
Function = (InputDaten as any) as any =>
let
FunctionResult = try Text.Combine(List.RemoveItems(Text.Split(InputDaten, “ „),{„“}),“ „) otherwise InputDaten //Funktion TrimAll
in
FunctionResult,
FunctionType = type function (InputDaten as any) as any
meta [
Documentation.Name = „TrimAll“,
Documentation.LongDescription = „Removes the leading and trailing spaces from a text value and replaces all multiple spaces with a single space.“,
Documentation.Examples = {
[Description = „“, Code = „TrimAll(„“ Removes the leading and trailing spaces from a text value and replaces all multiple spaces with a single space. „“)“, Result = „““Removes the leading and trailing spaces from a text value and replaces all multiple spaces with a single space.“““]
}
],
TypedFunction = Value.ReplaceType(Function, FunctionType)
in
TypedFunction
in
TrimAll
_____________________
Um mit dieser Funktion eine ganze Tabelle zu bearbeiten reicht folgender Aufruf.
= Table.TransformColumns(Quelle,{}, TrimAll)
Durch die leere Liste als zweiten Parameter werden alle Spalten der Tabelle abgearbeitet.
Und schon wieder eine lustige Fehlermeldung in der Excelschulung.
Wir wandeln eine Liste in eine intelligente Tabelle um:
Der Vorgang, den Sie gerade ausführen möchten, wirkt sich auf eine große Anzahl von Zellen aus und kann viel Zeit in Anspruch nehmen. Möchten Sie den Vorgang wirklich fortsetzen?
Was hat sie gemacht?
Ich schaue ihr über die Schulter und sehe:
Sie hat das ganze Tabellenblatt markiert. Eine sehr große, intelligente Tabelle – nein – das wollen wir nicht!
Amüsant: ich greife mit Power Query in Excel (ein Programm aus dem Hause Microsoft) auf eine Seite von microsoft.com zu und werde gefragt, ob diese Seite wirklich vertrauenswürdig ist:
Traut Microsoft sich selbst nicht über den Weg?
Nein – ich glaube eher, dass sie im Vorfeld KEINE Ausnahmen implementieren wollten.
Manchmal schreibt Microsoft zu schnell für mein kleines Hirn. Ich finde auf einer Seite die Info:
Um die Sicherheit Ihres semantischen Modells zu verwalten, öffnen Sie den Arbeitsbereich, in dem Sie Ihr semantisches Modell in Fabric gespeichert haben, und führen Sie die folgenden Schritte aus:
https://learn.microsoft.com/de-de/fabric/security/service-admin-row-level-security
Ich habe eine Weile suchen müssen, bis ich den „Arbeitsbereich“ gefunden habe. Nun ja – vielleicht wäre ein kleiner Hinweis …
Hi René,
Du hast Recht, es kommt schon auf den Tag an.
Ich habe mich noch etwas mit dem „Altersproblem“ beschäftigt und dabei eine Alternative im Netz von Imke Feldmann entdeckt:
(Startdatum as date, Enddatum as date) =>
let
StartdatumINT = Date.Year(Startdatum) * 10000 + Date.Month(Startdatum) * 100 + Date.Day(Startdatum),
EnddatumINT = Date.Year(Enddatum) * 10000 + Date.Month(Enddatum) * 100 + Date.Day(Enddatum),
Alter = Number.IntegerDivide((EnddatumINT - StartdatumINT),10000)
in AlterGruß
Christian
Stimmt, Christian,
diese Lösung – den Monat mit einer sehr großen Zahl und den Tag mit einer kleineren zu multiplizieren, habe ich vergessen. Die Lösung habe ich auch vor vielen Jahren mal irgendwo gefunden. Auch clever.
Datumsprobleme, Power Query und kein Ende.
Ich habe gestern darauf hingewiesen, dass die Altersberechnung von Power Query (es wird die Anzahl der Tage durch 365 dividiert und damit die Schalttage übergangen) sehr unscharf ist.
Man kann durch 365,25 (geschrieben: 365.25) dividieren. Das stimmt.
Oder nicht?
Nein – leider nicht ganz. Das Jahr 2024 war ein Schaltjahr, das heiß: es gab einen 29.02.2024.
Heute ist der 11.09.2024
Die Anzahl der Tage zum 11.09.2023, 11.09.2022 und 11.09.2021 betragen 366, 731 und 1096 Tage. Teilt man diese Zahlen durch 365.25 erhält man 1,0020534 beziehungsweise 2,0013689 und 3,0006845
Abgerundet also die Zahlen 1, 2 und 3
Angenommen heute wäre der 11.09.2023 (also kein Schaltjahr). Dann beträgt die Differenz zum 11.09.2022, 11.09.2021 und 11.09.2020 als Ergebnis 0,9993155 beziehungsweise 1,9986311 und 2,9979466 – oder abgerundet:
0, 1 und 2
In den Nicht-Schaltjahren bleibt ein Tag Differenz: das bedeutet: Er oder sie wird erst „einen Tag später“ ein Jahr älter.
Wir erstellen in Power Query eine einfache Funktion:
(Anfangsdatum as date, Enddatum as date) =>
Number.RoundDown(Duration.Days(Enddatum - Anfangsdatum) / 365.25)Randbemerkung: Leider kann man nicht Enddatum – Anfangsdatum rechnen (wie in Excel), sondern muss das Ergebnis mit Duration.Days in eine (Tages-)Zahl konvertieren.
Und so zeigt sich die Unschärfe von einem Tag:
Richtig wäre folgende Berechnung: Jahr vom Ende minus Jahr vom Anfang.
Wenn der Monat des Enddatums kleiner als der Monat des Anfangsdatums, dann muss 1 abgezogen werden.
Wenn beide Monate gleich, allerdings der Tag des Enddatums kleiner als der Tag des Anfangsdatums, dann muss 1 abgezogen werden (umgangssprachlich: er oder sie hatte noch nicht in diesem Jahr Geburtstag). Oder als Formel:
(Anfangsdatum as date, Enddatum as date) =>
Date.Year(Enddatum) - Date.Year(Anfangsdatum) -
(if Date.Month(Enddatum) < Date.Month(Anfangsdatum) then 1 else
if Date.Month(Enddatum) = Date.Month(Anfangsdatum) and
Date.Day(Enddatum) < Date.Day(Anfangsdatum) then
1 else 0)Rechnet korrekt:
Oder – man kann auch anders rechnen. Man transformiert das Anfangsdatum ins Jahr des Enddatums. Also: man holt Tag und Monat des Anfangsdatums und Jahr des Enddatums und baut ein Datum daraus.
Man berechnet Jahr minus Jahr.
Wenn das transformierte Datum größer als das Enddatum ist, muss noch 1 abgezogen werden.
Umgangssprachlich bei Geburtstagen: sollte er oder sie in diesem Jahr noch nicht Geburtstag gehabt haben, muss man 1 abziehen. Als Formel:
(Anfangsdatum as date, Enddatum as date) =>
Date.Year(Enddatum) - Date.Year(Anfangsdatum) -
(if #date(Date.Year(Enddatum), Date.Month(Anfangsdatum), Date.Day(Anfangsdatum)) > Enddatum then
1 else 0)Klappt auch:
Natürlich sollte man das Enddatum optional setzen, beispielsweise so:
(Anfangsdatum as date, optional Enddatum as date) =>
let
EnddatumNeu = if
Enddatum is null then
Date.From(DateTime.LocalNow()) else
Enddatum,
Diff = Date.Year(EnddatumNeu) - Date.Year(Anfangsdatum),
Alter = Diff - (if
#date(Date.Year(EnddatumNeu), Date.Month(Anfangsdatum), Date.Day(Anfangsdatum)) > EnddatumNeu then
1 else 0)
in
AlterOh wie schön wäre eine Funktion DateDif oder DATEDIFF!
Gestern auf dem Excelstammtisch. Angelika (Angelika Meyer; https://www.asmeyer.de/) will es wissen:
Sie hat eine Liste mit Namen und Geburtstagsdaten. Diese werden in Power Query abgerufen:
Sie möchte das Alter berechnen und dann in einer Pivottabelle gruppieren, um einen Überblick über die Altersstruktur zu erhalten.
Es erstaunt:
Und Power Query? Nichts dergleichen. Also per Hand:
Über Spalte hinzufügen / Datum / Alter kann man eine berechnete Altersspalte erzeugen. Wirklich?
Das Ergebnis ist eine Dauer – genauer: die Differenz in Tagen zwischen dem aktuellen Datum und dem Geburtsdatum (hier: Spalte „Birthday“)
Im zweiten Schritt kann man über Transformieren / Dauer / Jahre gesamt diese Spalte in eine Jahreszahl verwandeln:
Das Ergebnis: Dezimalzahlen
Diese müssen abgerundet werden – hier hilft Transformieren / Runden / Abrunden:
Aber ist das Ergebnis korrekt? Ich stutze. Wir probieren es. Heute ist der 10. September. Ich trage einige Geburtsdaten ein – vom 01.09 bis zum 30.09:
Bis zum 25. September sind diese Personen 60 Jahre als. Das ist falsch. Warum?
Ein Blick in den Code hilft. Power Query berechnet das Alter:
= Table.TransformColumns(#"Eingefügtes Alter",{{"Alter", each Duration.TotalDays(_) / 365, type number}})Power Query teilt die Dauer durch 365. Dadurch werden Schaltjahre nicht berücksichtigt. Bei einem 60jährigen macht dies eine Differenz von 60/4 = 15 Tage aus. Wir versuchen den Code anzupassen:
= Table.TransformColumns(#"Eingefügtes Alter",{{"Alter", each Duration.TotalDays(_) / 365.25, type number}})Wir teilen durch 365.25
Das Ergebnis ist besser:
Oder man muss eine eigene Funktion für dieses Problem erstellen.
Danke an Angelika für diese gute Frage.
Geht das nicht zusammen?
Im Power Query kann man natürlich mit der Bildlaufleiste den Ausschnitt der Tabelle nach oben oder unten fahren. Markiert man eine Zelle, kann man ebenso mit der [Leertaste9 nach unten scrollen.
Umgekehrt scrollt die Tastenkombination [Shift] + [Leertaste] nach oben:
Beides zusammen scheint jedoch nicht zu funktionieren: erst runter dann rauf. Oder umgekehrt.
Ich war sehr erstaunt. Warum zeigt die Matrix in PowerBI keine korrekten Werte bei den Zeilenzwischensummen?
Die Antwort finde ich im Aufgabenbereich „Daten hinzufügen“:
PowerBI hat als Funktion „Erstes Datum“ (?!?) verwendet. Warum nicht Summe?
Die Antwort finde ich in den Transformationsschritten: Dort war die Spalte, die später aggregiert wird, von Zahlentyp „beliebig („123 ABC“) festgelegt. Sie muss natürlich Zahl (beispielsweise Dezimalzahl) sein.
Noch besser: man verwendet nicht das Feld, sondern erstellt ein Measure, beispielsweise mit SUMX. Dann ist man auf der sicheren Seite, dann verwendet PowerBI genau DIESE Funktion und wählt nicht selbst eine aus.
Ups, das ist mir noch gar nicht aufgefallen:
Hat man in Power Query zwei Spalten vom Datentyp Dezimalzahl oder Dezimalzahl und Prozentzahl und multipliziert sie und lässt dich das Ergebnis in einer benutzerdefinierten Spalte anzeigen, ist das Ergebnis – anders als Excel, wo das Zahlenformat Währung übernommen wird: Prozent * Währung -> Währung – nicht vom Zahlentyp Dezimalzahl, sondern vom Datentyp „beliebig“ (123 ABC). Fatal. Das sollte immer geändert werden.
In Excel und in PowerBI.
Erstaunlich. Mal so, mal so.
In Power Query in Excel heißt das Zahlenformat „Währung“.
In PowerBI jedoch „Feste Dezimalzahl“
Beide Begriffe sind etwas „schräg“. Allerdings: eine genaue Definition „Dezimalzahl mit exakt vier Nachkommastellen für die Verwendung als Währungsformat“ oder ähnliches, wäre zu lang geworden.
Dennoch: Einheitlichkeit wäre auch schön gewesen.
UND ist nicht UND; ODER ist nicht ODER.
Augen auf bei den logischen Konjunktoren in DAX – sie sind nicht konsistent!
Über die Funktion CALCULATE kann ein Filterkontext mitgegeben werden (oder man einen Filter auch aufheben). Allerdings leider nicht konsistent, was UND beziehungsweise ODER betrifft:
Beginnen wir mit ODER:
|| ist das Zeichen für das logische „ODER“ und kann für eine Spalte verwendet werden:
J Oder01 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Mitgliedschaft] = "Platinum"
|| tbl_Kunden[Mitgliedschaft] = "Gold"
)Platinum oder Gold – kein Problem! Auch die Alternative mit der Funktion OR klappt:
J Oder02 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
OR (
tbl_Kunden[Mitgliedschaft] = "Platinum";
tbl_Kunden[Mitgliedschaft] = "Gold"
)
)Filtert man jedoch über zwei verschiedene Spalten, scheitert ||:
J Oder03 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Mitgliedschaft] = "Platinum"
|| tbl_Kunden[Jahresbeitrag] > 180
)aber auch OR liefert einen Fehler:
J Oder04 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
OR ( tbl_Kunden[Mitgliedschaft] = "Platinum"; tbl_Kunden[Jahresbeitrag] > 180 )
)Also muss man die Funktion FILTER verwenden. Kein Problem bei ||:
J Oder05 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
FILTER (
tbl_Kunden;
tbl_Kunden[Mitgliedschaft] = "Platinum"
|| tbl_Kunden[Jahresbeitrag] > 180
)
)Allerdings versagt hier die Funktion OR:
J Oder06:=
CALCULATE(sum(tbl_Kunden[Jahresbeitrag]);FILTER(or(tbl_Kunden;tbl_Kunden[Mitgliedschaft]="Platinum" ; tbl_Kunden[Jahresbeitrag]>180 )))OR wird nicht in der Funktion FILTER akzeptiert!
Und wie sieht bei der Verkettung zweier logischer Bedingungen mit UND aus?
J Und01 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140
&& tbl_Kunden[Jahresbeitrag] < 180
)&& stellt kein Problem dar. Auch die Funktion AND nicht:
J Und02 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
AND ( tbl_Kunden[Jahresbeitrag] > 140; tbl_Kunden[Jahresbeitrag] < 180 )
)Die Funktion CALCULATE bietet die Möglichkeit mehrere getrennte Bedingungen mit einem Semikolon zu trennen. Auch das klappt problemlos:
J Und03 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140;
tbl_Kunden[Jahresbeitrag] < 180
)Und wie sieht es bei zwei verschiedenen Spalten aus? Ähnlich wie bei ODER liefern die beiden Varianten it && und AND einen Fehler:
J Und04 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140
&& tbl_Kunden[Mitgliedschaft] = "Gold"
)Und auch das führt zu einem Fehler:
J Und05 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
AND ( tbl_Kunden[Jahresbeitrag] > 140; tbl_Kunden[Mitgliedschaft] = "Gold" )
)Also bleibt die Variante mit dem Semiloka – sie funktioniert:
J Und06 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140;
tbl_Kunden[Mitgliedschaft] = "Gold"
)Und die Funktion FILTER? Analog zu ODER klappt &&:
J Und07 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
FILTER (
tbl_Kunden;
tbl_Kunden[Jahresbeitrag] > 140
&& tbl_Kunden[Mitgliedschaft] = "Gold"
)
)AND ist im Kontext von FILTER (ebenso wie OR) verboten:
J Und08:=
CALCULATE(sum(tbl_Kunden[Jahresbeitrag]);FILTER(tbl_Kunden;and(tbl_Kunden;tbl_Kunden[Jahresbeitrag] > 140 ; tbl_Kunden[Mitgliedschaft] ="Gold")))Und da FILTER nur ein Filterkriterium akzeptiert, führt auch die letzte Lösung zu einem Fehler:
J Und09:=
CALCULATE(sum(tbl_Kunden[Jahresbeitrag]);FILTER(tbl_Kunden;tbl_Kunden;tbl_Kunden[Jahresbeitrag] > 140 ; tbl_Kunden[Mitgliedschaft] ="Gold"))Fazit:Man muss bei der Und- beziehungsweise Oder-Filterung gut aufpassen, welche der Varianten man verwendet.
Hier noch einmal der Screenshot mit den sechs Oder-FIlterungen und den neun Und-Filtern:
Gut aufpassen:
Importiert man in Power Query aus einer Datenquelle, in der die Datentypen nicht festgelegt wurden (also beispielsweise aus Excel, einer Text- oder CSV-Datei), werden die Werte vom Datentyp beliebig festgelegt.
Das Symbol ABS 123 zeigt den Datentyp „beliebig“ an. Wandelt man ihn in ganze Zahlen um, werden die Werte verändert – klar!
Wendet man den Datentyp Währung an, werden zwei Nachkommastellen angezeigt:
jedoch nur vier gespeichert, wie man leicht feststellen kann, wenn man anschließend den Datentyp Text oder Dezimalzahl verwendet – die anderen Nachkommastellen werden so gelöscht!
In der letzten Power Query-Schulung erkläre ich, dass man in Power Query keine Zahlen formatieren kann. „Dezimal“ bedeutet die Fähigkeit Nachkommastellen zu verwenden, Währung bedeutet mit maximal vier Nachkommastellen. Ein Währungssymbol ist dagegen nicht möglich.
Ein Teilnehmer fragt, warum der Zahlentyp „Währung“ ein Tausendertrennzeichen und exakt zwei Nachkommastellen anzeigt; Dezimalzahl jedoch nicht.
Ich weiß es nicht.
Dennoch: formatiert wird in Excel respektive PowerBI.
Das ist mir ja noch gar nicht aufgefallen:
Excel verwendet für die Formelsprache DAX das Trennzeichen, das in der Systemsteuerung eingestellt ist.
Und PowerBI?
Bislang dachte ich, PowerBI würde IMMER das Komma verwenden – weit gefehlt – in den Optionen kann man es tatsächlich ändern:
Schöne Frage in der letzten PowerBi-Schulung:
Greift man mit Power Query auf eine Excel-Arbeitsmappe zu, kann man mehrere Tabellenblätter auswählen. Diese Option muss jedoch explizit aktiviert werden:
In Power Query in PowerBI ist dies jedoch nicht nötig:
Ich habe keine Ahnung, warum sich diese beiden Dialoge unterscheiden.
Diana Sperber hat zu Recht darauf hingewiesen: Fügt man in eine Excelmappe eine Tabelle ein, die auf einer Power Query-Abfrage beruht und schützt das Tabellenblatt, so kann man die Tabelle nicht mehr aktualisieren.
Ärgerlich!
Gestern in der PowerBI-Schulung. Ich zeige verschiedene Arten des Filtern, beispielsweise Datenschnitte.
Ein Teilnehmer will wissen, ob es ein Shortcut gibt, mit dem man alle gesetzten Filter schnell wieder zurücksetzen kann.
Meine Antwort:
Nein.
Aber: wenn man per Hand alle Filter ausschaltet und diese Ansicht als Lesezeichen speichert, kann man auf eine Schaltfläche oder eine Form oder auf ein Bild dieses Lesezeichen als Aktion einfügen:
Der Teilnehmer ist zufrieden.
Letzte Woche auf dem Excelstammtisch hat uns Diana gezeigt, wie man in den automatisch generierten Code von Power Query eingreifen kann. Beispielsweise: greift man auf eine Excelmappe zu, dann schreibt Power Query folgende Zeile:
Excel.Workbook(File.Contents("Pfad\Dateiname.xlsx"), null, true)Ändert man den zweiten Parameter useHeaders von null in true, wird gleich die erste Zeile als Überschrift erkannt.
Allerdings, so erwidert Christian, verschwindet dann auch das Zahnrad:
Das ist bedauerlich, denn über das Zahnrad-Symbol kann man leicht den Dateinamen oder den Pfad ändern:
Also: entweder – oder.
Lieber Herr Martin,
es ist schon eine Weile her, dass Sie uns bei der Entwicklung unserer Excel-Tabellen für die Kaufpreissammlung des Kommunalreferats geholfen haben. Ihr Ansatz, den Sie mit uns entwickelt haben, hatte die Arbeit der Fachabteilung maßgeblich beschleunigt. Mittlerweile habe ich den Arbeitgeber gewechselt und wollte nun das Einlesen des Pfades zu einer Datei auch hier so etablieren, dass wir kleine intelligente Tabellen haben, in welche der Pfad und in andere der Dateiname eingetragen werden kann. Allerdings scheint Excel ein Problem zu haben, wenn der Pfad auf einen SharePoint führt und nicht zum Explorer. Es erscheint die Fehlermeldung, dass es kein absoluter Pfad sei. Haben Sie evtl eine Idee, wie man Excel dazu bringen kann, einen SharePoint Pfad wie einen Explorer Pfad zu verwenden? Ich habe Ihnen die Datei, die wir damals entwickelt haben, angehängt, in der Hoffnung mein Problem damit erständlicher zu machen.
Ich würde mich freuen, wenn Sie mir einen Tipp geben könnten, der mich zur Problemlösung bring.
Herzliche Grüße,
Hallo Frau I.,
sorry, Ihre Mail ist etwas nach unten gerutscht. Das ist nicht meine Art, nicht zu antworten.
Zu Ihrer Frage:
ja, wenn eine Excelmappe auf SharePoint liegt, gelten wohl andere Regeln für den Zugriff:
* entweder Sie greifen mit
SharePoint.Files auf den Ordner zu:
* oder Sie verwenden den Befehl
SharePoint.Contents* oder Sie öffnen die Datei in der Desktop-Version:
und verwenden dann diesen Pfad:
Dann sieht der Pfad beispielsweise so aus:
https://meinPfad/personal/mehrPfad/Documents/JurassicPark.xlsxHilft das?
Liebe Grüße
Rene Martin
Fatal.
Gestern war Excelstammtisch. Diana Sperber erzählte uns einige spannende Dinge über Power Query. Sehr interessant!
Beim Thema „Schutz“ musste ich schlucken.
Wenn man in Excel in Microsoft 365 eine Arbeitsmappe schützt, kann man zwar die Tabelle aktualisieren, aber nicht mehr den Code verändern und einsehen:
DOCH!
Man kann den Code kopieren und in einem Editor eingefügt anzeigen lassen:
Code einsehen geht – Code manipulieren natürlich nicht.
Danke an Diana für den hervorragenden Vortrag.
Intelligente Tabellen, Anomalien und kein Ende:
Befindet sich der Cursor innerhalb einer intelligenten Tabelle, wird mit [Strg] + [-] eine Zeile gelöscht.
Befindet sich der Cursor jedoch in der letzten Zeile, wird mit [Strg] + [-] die Spalte gelöscht:
[Strg] + [-]
wird IMMER die Spalte gelöscht!
Fazit: Vielleicht sollte man doch markieren …
Manchmal stellen die Teilnehmer in Excelschulungen verblüffend einfache Fragen, auf die ich keine einfache Antwort weiß.
Beispielsweise: wie kann ich die Werte eine Pivottabelle FILTERN? Sortieren ist ja kein Problem, aber filtern? Natürlich denke ich an DAX und Power Query, denke an Cube-Funktionen – aber einen einfachen Autofilter für die WERTE einschalten – ich weiß nicht wie …
Nachtrag:
Danke an Martin und danke an Odo: Es geht doch. Es geht doch sehr einfach:
Mit einem Rechtsklick auf das Filtersymbol (auf das Pfeilchen der Dropdownliste bei den Zeilenbeschriftungen kann man einen Filter der Werte (hier: Frachtkosten) aktivieren:
Oder natürlich die Werte in den Filter ziehen – dann ist eine Mehrfachauswahl möglich (allerdings nicht größer als, kleiner als, …)
Guten Tag Herr Martin,
ich hatte am 15.05.204 mit viel Freude Ihren Kurs „Daten abrufen und vergleichen (Power-Query)“ besucht. Nun bastele ich gerade an einer PQ mit Web-Abruf und hätte folgende Rückfrage:
Ausgangslage: Auf einer Webseite werden stets aktuell für die letzten 5 Tage Zinssätze veröffentlicht. Wir möchten von einem der Zinssätze eine Zinssatzhistorie in Excel aufbauen.
Anbei mein PQ-File dazu, das jedoch tgl. nur Zinssätze für die letzten 5 Zinsstage anzeigt.
Gestern wurden z.B. folgende Werte ausgegeben:
Über Ihre Expertise zu meinen Fragen würde ich mich sehr freuen 😊. Gerne können wir auch TEAMSen.
Beste Grüße
###
Hallo Frau S.,
die Antwort zur ersten Frage lautet: das geht mit Power Query nicht. Der Gedanke von PC ist es, die Daten zu aktualisieren.
Wenn Sie eine Historie erstellen möchten, müssen Sie die Daten per Power Automate oder VBA an eine andere Stelle schreiben. Oder per Hand kopieren / Inhalte einfügen – als Werte einfügen.
Ein Kollege von mir wollte mal Benzinpreise von Tankstellen vergleichen (Preise stehen im Internet), um dann einen Trend festzustellen – er hat die Daten mit VBA gespeichert.
In den Eigenschaften findet sich die Option „Aktualisieren beim Öffnen“:
Auch hier: wenn Sie die Datei jeden Tag aktualisiert haben möchten, müssen Sie mit Power Automate oder VBA (oder einer anderen Programmiersprache) die Aktualisierung erzwingen.
Liebe Grüße
Rene Martin
Am Montag auf dem Excelstammtisch. Wir schauen intelligente Tabellen an.
Inga stellt folgende Frage: in einer intelligenten Tabelle, die nicht erweitert wird, wird eine Spalte nicht gesperrt. Das Blatt wird geschützt, aber filtern und sortieren sind erlaubt. Während das Filtern möglich ist, geht das Sortieren nicht.
Ja, leider – sehr schade.
Am Montag auf dem Excelstammtisch. Wir schauen intelligente Tabellen an.
Schade, dass man nicht Zellen der intelligenten Tabelle freigeben kann und das Tabellenblatt schützen kann. Man kann dann keine weiteren Daten zur Tabelle hinzufügen, also keine neuen Datensätze anfügen.
Schade
In einer Liste befinden sich sehr viele verbundene Zellen.
Der Zellverbund soll aufgehoben werden.
Dazu kann man das gesamte Tabellenblatt markieren – man kann allerdings nicht die verbundenen Zellen aufheben:
Die Ursache? Unter der Liste befindet sich eine intelligente Tabelle. Sie verhindert, dass Zellen verbunden werden oder umgekehrt: dass ein Zellverbund entfernt wird:
Manchmal verwundert mich Excel. Und seine Meldungen.
Auf einem Tabellenblatt befinden zwei Listen: eine oben, eine unten. Die obere Liste ist eine intelligente Tabelle, die unter ein „normaler“ Bereich.
Die untere Liste wird gefiltert:
Nun wird in der intelligenten Tabelle eine Zeile gelöscht oder hinzugefügt:
Hierdurch wird ein gefilterter Bereich in Ihrem Arbeitsblatt geändert. Um diesen Vorgang abzuschließen, entfernen Sie bitte den AutoFilter.
Bleibt nur die Frage: Warum?
Hallo Herr Martin, danke u. a. für Power Query aus der Praxis. Da sind ein paar Gamechanger dabei. Vielleicht können Sie mir ja weiterhelfen, da es bei Datenschnitten kein zusätzliches Suchfeld sondern nur die Werte des Feldes enthält. Kann man ohne VBA eine Lösung über einen Datenschnitt machen, wo ich auch eine Eingabe für den Filter machen kann? In einer anderen Mappe habe ich mit VBA den Filter beim Verlassen des Feldes gesetzt, aber ich möchte da gerne von weg. Rein theoretisch kann ich auch den Pivot-Filter bemühen, der blendet aber Zeilen aus, die vielleicht für benachbarte Pivots benötigt werden. Kann man mit der Übergabe eines in einer über der Zelle befindlichen Wertes an ein Measure oder anders filtern? Danke im Voraus.
Hallo Frau F.,
meines Wissens kann man beim Datenschnitt kein Suchfeld einbauen (hätte ich mir auch schon oft gewünscht) oder bei Zahlen ein Schieberegler von – bis. Es soll eine vereinfachte Alternative zur Auswahl beim Autofilter sein – so verstehe ich den Datenschnitt. Alles andere läuft auf eine Programmierung hinaus …
Liebe Grüße
Rene Martin
Excelschulung. Manchmal bin ich verblüfft, wenn Teilnehmer gute Ideen haben, auf die ich selbst nicht gekommen bin.
Ich halte das Symbol „Löschen“ in der Registerkarte „Daten“ für wichtig – damit kann man sehen, ob irgendwo ein (Auto-)Filter gesetzt wurde oder ob über einen Datenschnitt gefiltert wurde. Umgekehrt kann man damit alle gesetzten Filter ausschalten:
Eine Teilnehmerin fragt, wie man das Symbol in die Symbolleiste für den Schnellzugriff hinzufügen kann.
Ich suche:
Das Symbol heißt weder „Löschen“ noch „Filter löschen“ oder „Daten löschen“.
Dann fällt es mir wie Schuppen von den Augen: wenn der Filter aktiviert ist, kann man es über das Kontextmenü in die Symbolleiste für den Schnellzugriff einfügen:
Und so steht mir das Symbol immer zur Verfügung:
Damit Excel ein bisschen weniger nervt …
Übrigens: das Symbol heißt „Alle löschen“.
Schöne Frage gestern in der Excelschulung:
Eine Teilnehmerin erzählte, dass sie eine Liste mit Straßennamen hätte. Bei einiigen Adresse steht „Straße“, bei anderen „Str.“.:
Ihr Wunsch: sie hätte gerne konsequent nur „str.“ in der Liste stehen.
Variante I: Suchen und ersetzen. Okay – steht „Straße“ einmal großgeschrieben, einmal kleingeschrieben in der Zelle, muss man zwei Mal ersetzen:
Variante II: Die Funktion WECHSLEN kann diese Aufgabe ebenso erledigen:
=WECHSELN(WECHSELN(E3;"Straße";"Str.");"straße";"str.")
Variante III: Die Blitzvorschau
.. versagt GNADENLOS!
Wenn ich Power Query unterrichte, erzähle ich, dass es – anders als in Excel – keine Tastenkombinationen gibt, um Befehle aufzurufen.
Ich werde diesen Satz in den nächsten Schulungen modifizieren. In den letzten Tagen bin ich im Internet über mehrere Listen von Shortcuts in Power Query gestolpert. Allerdings: einige der Tastenkombinationen funktionieren nur in der englischen Oberfläche – in der deutschen gibt es andere. Und: einige funktionieren in DAX-Editoren oder in Power Query in Power BI, aber nicht in Power Query in Excel.
Ich habe hier einige der Tastenkombinationen für Power Query in Excel aufgelistet (und werde sicherlich weiter sammeln) und auf unserem Excelstammtisch im Juli vorstellen.
Viel Spaß damit, die Arbeit mit Power Query mit folgenden Kombis zu beschleunigen:
Power Query-Tipps & Tastenkombinationen
7.) Kommentare
[Strg] + [K] / [Strg] + [C] einzeiliger Kommentar einfügen
[Strg] + [K] / [Strg] + [U] einzeiliger Kommentar löschen
[Strg] + [#] Kommentar einfügen und löschen
[Alt] + [Umschalt] + [A] Mehrere Zeilen auskommentieren / Kommentar von mehreren Zeilen aufheben
8.) Bequeme Eingabe
Zwei Wörter ohne Punkt schreiben und anschließend mit [Tab] bestätigen („the devil is inside the dot“)
Wenn ich Power Query unterrichte, erzähle ich, dass es – anders als in Excel – keine Tastenkombinationen gibt, um Befehle aufzurufen.
Ich werde diesen Satz in den nächsten Schulungen modifizieren. In den letzten Tagen bin ich im Internet über mehrere Listen von Shortcuts in Power Query gestolpert. Allerdings: einige der Tastenkombinationen funktionieren nur in der englischen Oberfläche – in der deutschen gibt es andere. Und: einige funktionieren in DAX-Editoren oder in Power Query in Power BI, aber nicht in Power Query in Excel.
Ich habe hier einige der Tastenkombinationen für Power Query in Excel aufgelistet (und werde sicherlich weiter sammeln) und auf unserem Excelstammtisch im Juli vorstellen.
Viel Spaß damit, die Arbeit mit Power Query mit folgenden Kombis zu beschleunigen:
Power Query-Tipps & Tastenkombinationen
5.) Codeeingabe
[Strg] + [entf] lösche ab Cursorposition bis Ende des Wortes
[Alt] + [klick] Multicursor
6.) Bearbeitungsleiste
Vor dem Schreiben der Klammer den Befehl/die Befehle markieren – Klammer „ummantelt“ vorhandenen Befehl
Wenn ich Power Query unterrichte, erzähle ich, dass es – anders als in Excel – keine Tastenkombinationen gibt, um Befehle aufzurufen.
Ich werde diesen Satz in den nächsten Schulungen modifizieren. In den letzten Tagen bin ich im Internet über mehrere Listen von Shortcuts in Power Query gestolpert. Allerdings: einige der Tastenkombinationen funktionieren nur in der englischen Oberfläche – in der deutschen gibt es andere. Und: einige funktionieren in DAX-Editoren oder in Power Query in Power BI, aber nicht in Power Query in Excel.
Ich habe hier einige der Tastenkombinationen für Power Query in Excel aufgelistet (und werde sicherlich weiter sammeln) und auf unserem Excelstammtisch im Juli vorstellen.
Viel Spaß damit, die Arbeit mit Power Query mit folgenden Kombis zu beschleunigen:
3.) Erweiterter Editor
[Strg] + [Umschalt] + [+] Ansicht vergrößern
[Strg] + [Umschalt] + [-] Ansicht verkleinern
4.) Erweiterter Editor – Code
[Alt] + [Pfeil unten/oben] Zeile verschieben
[Alt] + [Umschalt] + [Pfeil unten/oben] Zeile duplizieren
[Strg] + [Umschalt] + [K] Ganze Zeile löschen
[Strg] + [Pfeil unten/oben] Bildschirm verschiebt sich, ohne dass Cursor seine Position ändert
[Strg] + [´] Zeileneinzug vergrößern
[Strg] + [ß] Zeileneinzug verringern
Wenn ich Power Query unterrichte, erzähle ich, dass es – anders als in Excel – keine Tastenkombinationen gibt, um Befehle aufzurufen.
Ich werde diesen Satz in den nächsten Schulungen modifizieren. In den letzten Tagen bin ich im Internet über mehrere Listen von Shortcuts in Power Query gestolpert. Allerdings: einige der Tastenkombinationen funktionieren nur in der englischen Oberfläche – in der deutschen gibt es andere. Und: einige funktionieren in DAX-Editoren oder in Power Query in Power BI, aber nicht in Power Query in Excel.
Ich habe hier einige der Tastenkombinationen für Power Query in Excel aufgelistet (und werde sicherlich weiter sammeln) und auf unserem Excelstammtisch im Juli vorstellen.
Viel Spaß damit, die Arbeit mit Power Query mit folgenden Kombis zu beschleunigen:
2.) Umgang mit Spalten im Editor
[Strg] + [A] alle Spalten markieren
Pos1/Ende wechsle zur ersten Spalte/letzten Spalte oder zum Anfang/Ende der Tabelle
Pfeiltaste wenn Spalte markiert ist: weitere Spalten auswählen
[Umschalt] + [Pfeiltaste] mehrere nebeneinander liegende Spalten auswählen
[Strg] + [Pfeiltaste] / [Strg] + [Leertaste]
mehrere nicht zusammenhängende Spalten markieren
[Alt] + [Pfeil unten] Filter
[Menütaste] Kontentmenü der Spalte
erste Spalte / [Pfeil links] / [Enter] öffnet Kontextmenü der Tabelle
[Leertaste] verschiebt den Bildschirm, ohne die Cursorposition zu verändern
[Strg] + [Leertaste] wechselt zwischen Zelle markieren und Spalte markieren
Wenn ich Power Query unterrichte, erzähle ich, dass es – anders als in Excel – keine Tastenkombinationen gibt, um Befehle aufzurufen.
Ich werde diesen Satz in den nächsten Schulungen modifizieren. In den letzten Tagen bin ich im Internet über mehrere Listen von Shortcuts in Power Query gestolpert. Allerdings: einige der Tastenkombinationen funktionieren nur in der englischen Oberfläche – in der deutschen gibt es andere. Und: einige funktionieren in DAX-Editoren oder in Power Query in Power BI, aber nicht in Power Query in Excel.
Ich habe hier einige der Tastenkombinationen für Power Query in Excel aufgelistet (und werde sicherlich weiter sammeln) und auf unserem Excelstammtisch im Juli vorstellen.
Viel Spaß damit, die Arbeit mit Power Query mit folgenden Kombis zu beschleunigen:
Power Query-Tipps & Tastenkombinationen
1.) Editor allgemein
[Alt] + [F12] Editor öffnen
[Alt] + [F4] Editor beenden
[F2] editieren/umbenennen: Abfrage, Spaltenüberschrift, Schritt
[Tab] Zwischen den einzelnen Elementen wechseln
Hallo Rene,
wie geht es dir? 🙂
Ich hätte eine Frage, du bist der Power Query Experte, macht es einen Unterschied von der Performance ob ich eine Abfrage in PQ zusammenführe oder ob ich eine Beziehung in PP herstelle?
LG
Hallo Christoph,
die Frage kann ich SO nicht ganz beantworten.
Wenn du in PQ verknüpfst, holst du eine Tabelle in eine andere Tabelle. Diese wird normalerweise extrahiert, um eine oder mehrere Spalteninformationen zu erhalten.
Bei PP erstellst du lediglich eine Beziehung zwischen beiden Tabellen, welche keine Zeit in Anspruch nimmt.
Jedoch: wenn du PP verwendest, lädst du die Daten ins Datenmodell – die Arbeitsmappe wird größer. Nur PQ muss man die Daten nicht ins Datenmodell laden. So bleibt die Datei kleiner.
Wenn du beispielsweise mit einer Pivottabelle Informationen aus mehreren Tabellen holen willst, musst du mit PQ, wenn du das Datenmodell nicht verwenden willst, die Daten nach Excel laden. Beim Aktualisieren werden die Daten nach Excel geschrieben und die Spaltenbreite neu berechnet – DAS kostet Zeit. Pivottabelle auf Basis der Daten im Modell ist schneller.
Ich habe letzteres mal getestet mit 300.000 Datensätze – hier das Ergebnis, das mit VB liefert. PP gewinnt gegenüber PQ, weil kein Schreiben in eine Tabelle nötig ist.
Mit Power Query greife ich von Excel auf eine andere Arbeitsmappe zu. Und erhalte folgende Meldung:
Etwas differenzierter wäre schön gewesen.
Die Datei wurde mit einem Kennwort (beim Öffnen) gespeichert. Daher die „beschädigten Daten“.
Eine ähnliche Meldung erhält man auch, wenn man mit Power Query auf eine andere Datei zugreift und diese Datei dann mit Kennwort geschützt wird:
[DataFormat.Error] Die Datei enthält beschädigte Daten.
Gestern in der Excelschulung.
Wir erstellen auf Basis einer Kundenliste eine intelligente Tabelle und setzen drei Datenschnitte auf (Geschlecht, Bundesland und Mitgliedschaft):
Wir filtern, beispielsweise Membership = „Gold“:
Ein Teilnehmer fragt, warum denn einige Bundesländer, beispielsweise „Ceuta“ verschwinden. Die Antwort: Sie verschwinden nicht – sie stehen nur „am Ende der Liste“, welche hier nicht sichtbar ist, weil die Liste zu lang für den Datenschnitt ist. Dort werden sie ausgegraut:
Diese Option kann man in den Einstellungen ändern:
„Elemente ohne Daten zuletzt anzeigen“
Der Teilnehmer ist zufrieden.
Die benutzerdefinierten Listen in Excel sind bekannt:
Umso erstaunlicher gestaltet sich das Objekt, wenn man mit VBA darauf zugreifen möchte:
Die Anzahl der Elemente erhält man mit dem Befehl CustomListCount :
MsgBox Application.CustomListCountEin neues Element wird hinzugefügt mit einem Array mit dem Befehl addCustomList:
Dim meineListe As Variant
meineListe = Array("a", "b", "c")
Application.AddCustomList meineListeSehr angenehm: existiert der Eintrag bereits, ist weder Fehlermeldung noch ein zweiter Listeneintrag die Folge – alles bleibt beim Alten.
Auf ein Element kann per Nummer zugegriffen werden. Das Element wird als Array ausgegeben. Die Elemente können per Schleife ermittelt werden (Achtung: das erste Element trägt die Nummer 1!):
meineListe = Application.GetCustomListContents(21)
MsgBox Join(meineListe, "/")
Ein Element kann mit der Methode deleteCustomList gelöscht werden:
Application.DeleteCustomList 21Wird eine Liste mit einer benutzerdefinierten Liste sortiert, lautet der Befehl:
ActiveWorkbook.Worksheets("Tabelle1").Sort.SortFields.Add2 Key:=Range( _
"F5:F16"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"Blue,Standard,Silber,Gold,Platinum", DataOption:=xlSortNormalWird jedoch in einer Pivottabelle nach dieser benutzerdefinierten Reihenfolge sortiert, lautet der Befehl:
ActiveSheet.Range("$I$5").Sort Order1:=xlAscending, Type:=xlSortLabels, _
OrderCustom:=12, Orientation:=xlTopToBottomAllerdings ist nicht 12 der Eintrag „Blue, Standard, Silber, Gold, Platinum“, sondern die Nummer 11:
Application.GetCustomListContents(11)Ein bisschen verwirrend!
Schöne Frage in der Excel-Schulung:
Warum umschließen zwei eckige Klammern die Spalte Kategorie-Nummer?
Die Antwort: Wegen des Trennzeichens. Würde man die Spalte in KategorieNr umbenennen, würde die Formel lauten:
=XVERWEIS([@KategorieNr];tbl_Kategorien[Kategorie-Nr];tbl_Kategorien[Kategoriename])
Mit Trennzeichen würde @Kategorie-Nr nicht „verstanden“ werden.
Wyn Hopkins ist genervt, dass der Zugriff auf SharePoint-Ordner mit Power Query so langsam dauert. Er schlägt vor, statt des Befehls SharePoint.Files den Befehl SharePoint.Contents zu verwenden.
Hier ist sein Video:
Clever!
Wyn Hopkins ist genervt. Greift man in Excel mit Power Query auf einen Ordner zu, wird die letzte Spalte „Folder Path“ sehr schmal angezeigt. Man kann sie nicht verbreitern und sieht auch nicht den gesamten Pfad.
Er schlägt vor, diese Spalte nach links zu verschieben und dort zu verbreitern:
Löscht man diesen Schritt ist die Spalte „Folder Path“ breiter. Cleverer Tipp – danke Wyn.
Gestern in der Excelschulung. Wir erstellen eine Pivottabelle. Ich wunder mich, warum die Städe alphabetisch sortiert werden, allerdings München oben steht:
Es fällt mir schnell ein: in einer der letzten Schulungen habe ich benutzerdefinierte Listen gezeigt und dort „München“ verwendet. Deshalb wird dieser Eintrag oben angezeigt.
Ich habe ein benutzerdefiniertes Format für meine Pivottabelle erstellt. Und wundere mich, warum ich das Format nicht ändern kann:
Die Antwort: die Quelltabelle liegt auf einem geschützten Tabellenblatt!
Eigentlich schade. In PowerBI kann man in den neuen Karten Bilder einfügen:
Klasse! Man kann jedoch nicht bei den neuen Datenschnitten Bilder einfügen – dort muss man in ein Feld einer Tabelle die URL eintragen – das Bild von einer Webseite geholt:
Chandeep Chhabra ärgert sich, dass es in Power Query keine Switch-Funktion gibt. Deshalb baut er sie mit M nach:
Eine sehr clevere Lösung, wie ich finde.
Allerdings: Ich habe sie an einer Datei mit 300.000 Zeilen getestet und festgestellt, dass sie zirka 40% mehr Zeit benötigt als mehrere verschachtelte If-Funktionen …
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!
Das neue Visual „Karte (neu)“ in PowerBI ist klasse. Jetzt kann man mehrere Karten mit mehreren Elementen darstellen.
Allerdings: verwendet man für die Verweisbezeichnungen Zahlen (beispielsweise das Ergebnis von Measures), können diese leider nicht formatiert werden. Vielleicht kommt es ja noch …
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.
Erstaunlich: Microsoft „puscht“ die intelligenten Tabellen. Jedoch in Word-Serienbriefe werden nur die Tabellenblätter angezeigt – nicht die Tabelle. Schade!
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.“
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.
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 …
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!
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:
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.
Wie man die Balken in umgekehrter Reihenfolge in einem gestapelten Balkendiagramm darstellen könne, wollte ein Teilnehmer in der PowerBI-Schulung wissen.
Erstaunlicherweise findet sich diese Option nicht in „Visual formatieren“, sondern hinter den „weiteren Optionen (…)“:
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:
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 …
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 …
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.
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!
Alan Murray empfiehlt [Strg] + [L] statt [Strg] + [T], um eine intelligente Tabelle zu erzeugen.
Warum? [Strg] + [L] funktioniert auch im Browser in Microsoft 365.
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 …
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:
| Artikel | Menge | Preis |
| Artikel | 1 | 20 |
| Artikel | 2 | 10 |
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?
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
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
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:
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!
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.
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.
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.
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!
Ach wie doof! Die Funktion OR (ebenso wie AND) kann in Power Pivot – anders als in Excel – nur zwei Argumente aufnehmen! Also genau so wie die Funktion CONCATENATE
Ob man die Funktionen in Power Pivot auf Deutsch anzeigen lassen könne, fragte eine Teilnehmerin.
Leider nein!
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.
Ein Teilnehmer in der Schulung erklärt mir, dass er Formeln nicht mein einem Gleichheitszeichen, sondern mit einem Pluszeichen beginne:
Dafür benötige man nur einen Finger. Excel schreibt das Gleichheitszeichen davor:
Warum das nicht in Power Pivot gehe, will er wissen:
Ich schmunzle amüsiert.
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 …
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
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!
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:
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!
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.Filesmacht 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 WithDas 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:
Dennoch: Meistens klappt es. Und schließlich: Excel muss halt ab und zu nerven …
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
=PIVOTDATENZUORDNENerzeugt 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.
Kennst ihr DPQ-Dateien? Das sind Textdateien, in denen ein Query auf eine Datenbank gespeichert sind. Diese Data-Query kann man in Excel einbinden.
Soweit so gut.
Nun wollte ich so eine Datei in Power BI einbinden.
„Einen direkten Konnektor über diese Datenverbindung gibt es zur Zeit in Power BI Desktop nicht.“ schreibt Frank Arendt-Theilen. Hans-Peter Pfister hat es bestätigt. Schade.
Ach wie doof! Wenn man in PowerBi in das Visual Tabelle oder Matrix Werte (oder besser: ein Measure) in die Werte zieht, kann man mit einem Klick auf den Spaltenkopf danach sortieren:
Verwendet man jedoch eine Kategorie in den Spalten, kann man damit nicht sortieren – auch nicht mit gedrückter [Strg]-Taste:
Mit einem Klick wird die Spalte selektiert (und damit die anderen Visuals gefiltert).
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:
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:
Eine Teilnehmerin in der PowerQuery-Schulung wundert sich, warum so merkwürdige Kästchen neben den Datumsinformationen stehen:
Die Antwort ist simpel: sie hat statt „Datentyp ändern“ auf das Symbol „Datentyp erstellen“ geklickt.
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.
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:
Amüsant, was manchen Teilnehmerinnen und Teilnehmern in Schulungen auffällt. Dinge, die ich noch nie beachtet habe oder denen ich keine Beachtung beigemessen habe.
Beispielsweise ist mir noch nie aufgefallen, dass Zahlen in PowerQuery kursiv stehen, während Texte immer „aufrecht“, also nicht kursiv im Editor dargestellt werden:
Amüsant. In der Excelschulung fragt mich ein Teilnehmer, warum manchmal der Filter bei der Auswahl „Textfilter“ / „Ist gleich“ die letzte Filterung anzeigt und manchmal nicht:
Oder so:
Nun – das hängt damit zusammen, wie man filtert. Wählt man den Befehl „Textfilter“ / „Ist gleich“ aus
und trägt dort mehrere Varianten ein, beispielsweise M?nchen oder M??nchen:
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 bin unterwegs und arbeite auf meinem Laptop.
Ich möchte mit Power BI auf eine Access-Datenbank zugreifen. Leider erhalte ich folgende Meldung:
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.
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:
Aber kein Problem – denn zum Glück kann man diesen Schritt wieder löschen:
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.
In Excel für Windows kann man über das entsprechende Symbol leicht die Tabellengröße verändern:
Leider fehlt diese Funktion in Excel auf Mac. Kann man sie dort gar nicht aktivieren?
Manchmal habe ich gar nichts zu meckern – manchmal freue ich mich!
Mit [Alt] + [F11] öffne ich den VBA-Editor.
Und nun gibt es eine neue Tastenkombination:
[Alt]+[F12] öffnet den PowerQuery-Editor. Klasse!
(danke an Frédéric Le Guen für den Tipp)
Dummerweise hat ein Teilnehmer eine intelligente Tabelle über das gesamte Tabellenblatt erstellt.
Ich möchte den Bereich auf die benötigte Größe verkleinern. Hierfür tut das Symbol „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ gute Dienste. Schneller als das grüne Eck nach oben zu ziehen ist sicherlich das Eintragen des Bereichs:
Allerdings: wir wundern uns, dass das Ergebnis nicht das gewünschte ist – die intelligente Tabelle ist verschwunden. Genauer: sie „hängt“ irgendwo oben:
Des Rätsels Lösung: der Bildschirm war so verschoben, dass die erste Ziffer der Zeilennummer nicht sichtbar war. Also auf ein Neues – beim zweiten Mal klappt es auch!
In ein Tabellenblatt wird eine Überschrift eingetragen. Sie wird formatiert:
Sie wird in eine (leere) intelligente Tabelle (mit einer Zeile) umgewandelt:
Fügt man nun eine Zeile ein, wird die Farbe der Überschrift übernommen:
Man darf also nicht, wenn man in einer intelligenten Tabelle die Überschrift per Hand formatiert (beispielsweise, wenn man unterschiedliche Farben für verschiedene Bereiche verwenden möchte) vor der ersten Datenzeile eine Zeile einfügen.
Für die leere Tabelle heißt das: man muss sie so anlegen, dass mehrere leere Zeilen vorhanden sind.
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
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!
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.
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.
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.pbixJedoch 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!
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.InsertUnd 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!
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.
Gelächter in der Excelschulung.
Wir lernen Pivottabellen:
Da Leute dies unterschiedlich aussprechen, wiederhole ich den Namen „Pivot“ in verschiedenen Aussprachevarianten. Auch in der „französischen“ – ohne „t“.
Eine Teilnehmerin aus der Slowakei schaut irritiert auf. Ihr Nachbar kommentiert: „Du denkst jetzt an Bier, oder?“
Nach der Excelschulung zeigt mir ein Teilnehmer eine Pivottabelle. Er möchte dort eine Berechnung durchführen. Eigentlich recht einfach: einen Wert durch 1.000 teilen. Kein Problem, oder?
Allerdings erhalten wir eine Fehlermeldung:
Es dauert eine Weile, bis ich dahinter komme, dass ich mir die Liste einmal anschauen sollte. Und tatsächlich: dort finde ich eine Spaltenüberschrift
in der sich nicht nur Leerzeichen und Sonderzeichen ([ ]) befinden, sondern auch noch Zeilenumbrüche ([Alt] + [Enter]).
Also noch ein Versuch: Da ich die unterste Zeile nicht sehe, drücke ich so lange die Taste [Pfeil unten] und [Pfeil rechts], bis ich glaube, das Ende erreicht zu haben. Dann füge ich die Rechnung (hier: / 1000) ein.
Geht doch:
Hallo Herr Martin,
können Sie mir als Excel-Virtuose beim Umgang mit intelligenten Tabellen helfen? Es handelt sich um folgende Sache:
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
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:
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!
Ach wie doof.
VBA-Schulung. Ein Teilnehmer wollte JSON-Datein in Excel haben. Kein Problem, sagte ich und zeigte ihm PowerQuery.
Allerdings – nach einigen fragenden Blicken – stellten wir fest, dass in seinem Excel 2016 (anders als meines in Microsoft 365) noch kein JSON-Zugriff vorhanden ist.- Ärgerlich!
Schöne Frage in der Excelschulung:
Mit PowerQuery wurde eine Abfrage erstellt. Diese enthält eine Liste. Wie kann man eine Dropdownliste mit einer Datenüberprüfung auf diese Liste erstellen, ohne dass die Daten nach Excel geladen werden?
Ich wusste keine Antwort …
Etwas irritiert bin ich schon. In einer PowerBI-Schulung erzählt mir ein Teilnehmer, dass er die Daten gerne in Excel hätte. Und dass er eigentlich gerne mit PowerQuery in Excel darauf zugreifen würde. Und nicht mit PowerBI. Aber die IT hätte ihm gesagt, dass es nicht gut ist, wenn mit PowerQuery so viele Abfragen auf die Datenbank abgesetzt werden. Besser wäre es, mit PowerBI auf die Datenbank zuzugreifen.
Ich bin mir nicht sicher, ob die Damen und Herren von der IT nicht wissen, dass PowerBI auch PowerQuery verwendet.
Schöne Frage in der Excelschulung: Wir haben auf SharePoint mehrere Excelmappen, in denen der Autofilter eingeschaltet ist. Kolleginnen und Kollegen laden die Dateien runter, filtern, vergessen den Filter auszuschalten und – nun ist für den nächsten Kollegen der „alte“ Filter aktiviert. Ob man das einstellen könne, dass beim Schließen der Datei zwar der Autofilter gesetzt bleibt, jedoch nicht eine mögliche Filterung.
Leider gibt es dafür keine Einstellung, lautete meine Antwort – das müsse man programmieren. Ein langes Gesicht war die Folge.
Schade. Ich hätte es gut brauchen können.
In intelligenten Tabellen kann man – anders als bei der Datenüberprüfung – leider nicht die Überschriften mit einer ausgelagerten Liste verknüpfen …
Lustige Frage eines Teilnehmers der letzten Excelschulung (ich habe hier schon darüber geschrieben):
Kann man eine intelligente Tabelle mit der Funktion SEQUENZ ausstatten, so dass ihre Größe sich dynamisch ändern kann:
Die Antwort:
Leider nein:
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:
Irre!
Wenn man mit PowerQuery auf eine „ältere“ XLS-Excelmappe zugreift, werden folgende Spalten angezeigt:
Die drei Spalten (mit ihren Informationen) Item, Kind und Hidden fehlen.
Würde man eine XLS-Datei in XLSX umbenennen, wäre das Ergebnis das Gleiche wie bei XLS:
Wer macht denn so etwas? Und: DAS würde man in Excel doch sofort bemerken.
Ich erhalte vorgestern die Frage, warum das PowerQuery-Tool, das ich für die Firma erstellt habe bei einer Datei nicht läuft. Ich stelle fest – obwohl die Datei vom Format XLSX ist, werden nur die beiden Spalte Name und Date angezeigt, nicht jedoch Item, Kind und Hidden. Warum?
Ich gehe auf die Suche.
Ich stelle fest, dass die im openXML-Format verwendete interne Datei app.xml (im Ordner docProps) folgendermaßen aussieht:
Wenn ich die Datei, die von Apache POI erstellt wurde, öffne, speichere und schließe, sieht diese XML-Datei so aus:
SO jetzt jede XLSX-Datei aus, die von Excel erzeugt und in Excel gespeichert wurde.
Das bedeutet: das (umstrittene) Werkzeug Apache POI produziert XLSX-Dateien, die nicht genau der Spezifikation von Microsoft entsprechen. Ist das schlimm?
Ja, weil mein Werkzeug auf die Spalte „Kind“ zugreift und diese nicht findet …
Ein Dankeschön an Mark Risner. Er hat die Fehlermeldung „Wir haben alle Daten neben Ihrer Auswahl untersucht und konnten kein Muster zum Ausfüllen mit Werten erkennen.“ erhalten.
Was hat er gemacht: mehrmals hintereinander das Ergebnis der Blitzvorschau gelöscht und erneut die Blitzvorschau aktiviert. Irgendwann funktionierte sie nicht mehr.
Ich konnte diesen Fehler und diese Fehlermeldung leider nicht reproduzieren …
Erstellt man in Pivottabellen ein Stripset, wird es nur angezeigt, wenn die Option „Gebänderte Zeilen“ aktiviert ist.
Erstaunlicherweise verschwinden die Farben auch dann, wenn keine Werte vorhanden ist – das heißt: man kann mit den Stripsets keine gruppierte Liste formatieren:
Danke an Christian Steckmann für diesen Hinweis.
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.
Hallo René,
Habs.
Allerdings funtkioniert die Berechnung nicht…
Hallo Nadine,
in PowerQuery musst du für „hoch“, also Potenz schreiben:
Number.Power
PowerQuery „spricht“ US-Amerikanisch, also 0.3 statt 0,3.
Und den natürlichen Logarithmus musst du schreiben:
Number.Ln
Liebe Grüße
Rene
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:
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!
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 BarwertIst 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
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 …
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
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]
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
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!
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.
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!
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.
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“.
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:
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!
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.
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.
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.
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.
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:
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.
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.
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:
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.
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“].
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.
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!
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
NextVier 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.
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 …
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.
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.
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.
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)
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?
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.
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:
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:
Excelschulung. Thema: Listen. Ich beginne mit der Antwort auf die Frage, wie eine Liste in Excel aufgebaut werden soll:
„EINE Überschriftszeile“ deklamiere ich: „EINE, genau EINE – jede Überschrift MUSS eine Überschrift haben.“
Wir ändern die Daten, verschieben, löschen, benennen um, … und: sortieren.
Ein Teilnehmer fragt, warum die Überschrift IN der Liste steht. Meine Antwort: „Weil Sie nicht tun, was ich Ihnen sage!“ Sie haben sicherlich eine Spalte OHNE Überschrift versehen. Machen Sie mal bitte den letzten Schritt zurück!“
„Tatsächlich“, lautet seine Antwort: „eine Spalte hatte keine Überschrift!“
Ich schiebe nach: Wenn Sie unsicher sind, ob Ihre Liste eine Überschrift hat, beziehungsweise die erste Zeile als Überschrift erkennt, dann verwenden Sie die „benutzerdefinierte Sortierung“. Dort ist deutlich zu erkennen: Überschrift oder keine Überschrift; dort kann man auch explizit einschalten: „Bitte mit Überschrift“. Das heißt: die erste Zeile bleibt beim Sortieren bitte oben stehen!
Es ist zum Haare-Raufen. Hätte ich welche auf meinem Kopf! Unglaublich! Excel ärgert mich, wo es nur kann. Wenn ich schon einen Fehler haben möchte – was passiert? – Richtig – natürlich kein Fehler! Es ist zum Haare-Raufen!
Was ist geschehen?
Excelschulung. Turboschulung: ich zeige in einer Stunde Listen: sortieren, filtern, intelligente Tabellen, Datenschnitt und Pivottabellen. Eine Teilnehmerin bedankt sich für die Infos zu den Pivottabellen – das hätte ihr sehr weitergeholfen; nun verstehe sie den Gedanken, der dahinter steht. Und: „so schwierig ist das gar nicht“:
Ich wiederhole. „Der Aufbau der Tabelle ist wichtig: Entweder Sie nehmen eine intelligente Tabelle oder Sie achten darauf, dass Ihre Liste keine Leerzeile und keine Leerspalte hat. Und: jede Spalte muss eine Überschrift haben.“
Ich demonstriere es, lösche eine Spaltenüberschrift raus
erstelle eine Pivottabelle – und: es klappt! Excel unterläuft meine Schulung. Jetzt, wo Excel einen Fehler erzeugen sollte tut Excel: NICHTS! Fügt den gelöschten Spaltennamen ein:
Der Gedanke: Klar – eine zweite Pivottabelle wird nicht auf der Liste aufgesetzt, sondern auf dem Pivotcache. Deshalb weiß Excel auch den Namen der fehlenden Spaltenüberschrift. Der Fehler käme beim Aktualisieren zum Tragen.
Oder – damit die Teilnehmerin mir glaubt – ich kopiere die Liste in eine andere Datei, erstelle dort die Pivottabelle und:
HURRA – die Fehlermeldung!
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!
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:
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!
„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:
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:
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:
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.
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.
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:
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.
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:
=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:
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 …
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.
Hm. Irgendwie doof. Ich habe einen Bereich in Excel, in dem eine Spalte mit dem Zahlenformat „Text“ formatiert ist:
Wandelt man die Liste in eine (intelligente) Tabelle um, wird beim Einfügen einer neuen Zeile das Textformat nicht in die neue Zeile übernommen …
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)
Gestern in der Excelschulung. Wir besprechen den Autofilter. Ich erkläre die Option „Daten haben Überschriften“ beim Assistenten „benutzerdefiniertes Sortieren“.
Ein Teilnehmer fragt, warum bei ihm diese Option ausgegraut, also inaktiv sei:
Die Antwort ist schnell gefunden: der Autofilter wurde eingeschaltet – dadurch wird die erste Zeile als Überschriftszeile definiert.
Gestern in der Excelschulung. Ich erkläre den Autofilter. Wir haben eine Liste mit zirka 12.000 Datensätzen. Wir filtern alle Hamburger und Hamburgerinnen:
Zu der gefilterten Liste fügen wir alle Personen hinzu, die in Flensburg wohnen:
Und so machen wir weiter mit Bremen, Husum, Kiel, Buxtehude, Uelzen, Itzehoe, …
Ein Teilnehmer meldet sich und fragt, wie und ob man denn erkennen könne, welche Orte gefiltert seien:
Ich habe eine Weile überlegen müssen. Fährt man mit der Maus über das Filtersymbol, werde alle Filterkriterien im Quickinfo angezeigt:
Sehr versteckt!
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.Contentsverwendet; 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!?!
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.
Schöne Frage in der letzten PowerQuery-Schulung: warum kann man eigentlich keine Duplikate ermitteln lassen? Oder – wie in Excel – Duplikate löschen lassen?
Stimmt – DAFÜR gibt es in PowerQuery leider keinen Assistenten. Muss man „per Hand“ machen.
Tanja Kuhn schreibt: „Das geht beides. Duplikate löschen per Rechtsklick. Duplikate anzeigen über Gruppierung.“
Danke für den Hinweis – zur Gruppierung hätte der Teilnehme, der sich so eine Option beim Import der Daten gewünscht hatte, sicherlich angemerkt, dass man es dann auch „Duplikatensuche“ nennen sollte. Das „Duplikate löschen“ habe ich glatt übersehen / vergessen … (ich schäme mich! *lach*)
Der Teilnehmer dachte übrigens beim Verknüpfen von zwei Tabellen in einer 1:n-Beziehung an Access, bei dem beim Aktivieren der referentiellen Integrität automatisch überprüft wird, ob alle n-Elemente auf der 1-Seite vorkommen. So einen Haken oder eine Meldung hat er vermisst.
Schöne Frage in der letzten Excelschulung. Ich habe eine Übung erstellt: Dutzende von Fehlern: Bezugsfehler, Formatierungsfehler, falsch Zeichen („x“ statt „*“; „;“ statt „:“, …) ausgeblendete Zeilen, weiße Schriftfarbe, …
Danach erstellen wir eine Pivottabelle. Eine Teilnehmerin fragt, wo Quellen von Rechenfehlern liegen können. Man sieht das Ergebnis einer Summe – aber stimmt es auch?
Ich überlege:
Das kann man über Pivottable-Analyse / Datenquelle ändern herausfinden.
Habe ich etwas vergessen? Sicherlich … Ich fand die Frage sehr interessant …
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!
PowerQuery-Schulung. Ein Teilnehmer sagt, dass er nicht den gesamte Ordnernamen sehen kann und deshalb nicht den richtigen Ordner deselektieren kann.
„Dann schieben Sie halt die Bildlaufleiste nach rechts“, meine ich. „Geht nicht!“
Was ist pasiert?
Wir üben in der PowerQuery-Schulung den Zugriff auf Ordner:
Der Teilnehmer hat die Dateien (auf OneDrive) in einem sehr, sehr langen Ordnernamen abgelegt. Und wirklich: es ist dann leider nicht mehr möglich, die Bildlaufleiste so zu verschieben, dass ich das rechte Ende des Ordners sehen kann:
Die Lösung: Da ich den Text kenne, der am Ende steht, kann ich den gewünschten Ordner auch über „endet nicht mit“ filtern. DAS klappt.
Gestern PowerQuery-Schulung. Wir üben und probieren den Zugriff: Excelmappen, Textdateien, XML, json, die SQL-Datenbank, Ordner, Web, … alles klappt.
SharePoint?
Der Teilnehmer kopiert seinen Sharepoint-Pfad in das Eingabefeld:
Und noch bevor ich sagen kann, dass er sich über das Microsoft-Konto – drei Zeilen darunter – anmelden muss, erhält er eine Fehlermeldung:
Zweiter Versuch: erneute Anmeldung. Das Resultat: sofortige Fehlermeldung ohne die Möglichkeit sich über das „Microsoft-Konto“ anzumelden. Wie gelangt man wieder dort hinein?
Es dauert eine Weile, bis wir es gefunden haben:
Man muss über die Datenquelleneinstellungen den Pfad löschen:
… dann wird man bei der nächsten Anmeldung wieder nach ALLEN Einstellungsoptionen gefragt.
Und ich sage es noch deutlich. Aber er hört nicht.
Listen in Excel sollten eine Überschrift besitzen, wenn man die Listen sortiert und filtert; sie müssen eine Überschrift besitzen, wenn man mit einer Pivottabelle arbeitet.
Der Teilnehmer der Excelschulung hört nicht; erstellt einer Liste, bei der eine Spalte keine Überschrift besitzt:
Das Ergebnis: Excel geht davon aus, dass die Liste keine Überschrift hat und sortiert die erste Zeile ein:
Der Teilnehmer wundert sich.
Man kann es deutlich zeigen, wie Excel diese Liste interpretiert. Die benutzerdefinierte Sortierung zeigt auf, dass keine Überschrift identifiziert wurde:
Nachtrag: bei einer intelligenten Tabelle wäre das nicht passiert. Aber die lernen wir erst später …
Hallo in die Runde, Dies ist mein erster Post. Normalerweise, wenn ich einen Bereich als Tabelle formatiert habe, wurde eine die Formel mit dem Drücken der Enter-Taste automatisch bis zum Ende der Tabelle ergänzt. Seit kurzem funktioniert das nicht mehr. Ich muss wohl irgendwas verstellt haben. Könnt ihr mir bitte sagen, wie ich das zurück stelle? Unter einstellungen ist formel erweitern auf automatisch. Aber das löst das Problem nicht. Ich nutze Excel für Mac. Vielen dank für Eure Hilfe Gruß Stephen
Hallo Stephen,
schau mal in der Autokorrektur nach.
LG :: Rene
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.
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:
Liebe Grüße
Rene
PS: Danke an Hans-Peter Pfister für den Link.
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.
Schöne Frage in der Excel-Schulung:
Wie kann man beim Spezialfilter nur einen Teil der Liste auswählen? Also beispielsweise Spalte A:F und Spalte R:U?
Antwort: geht leider nicht! Man muss vom Ergebnis den nicht benötigten Bereich löschen.
Schöne Frage gestern in der Excel-Schulung:
Wie kann man die benutzerdefinierte Liste, die beim Sortieren verwendet wird, auslagern? Um sie dynamisch zu halten?
Ich wüsste nicht, wie das funktionieren sollte …
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!
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
Manchmal amüsieren und erstaunen mich Fragen in Excelschulungen. Beispielsweise folgende:
In einer Liste stehen Email-Adressen. Um herauszufinden, ob einige der Adressen doppelt vorkommen, wird eine Pivottabelle aufgesetzt, die Adressen werden gruppiert und gezählt:
Die Anzahlspalte wird absteigend sortiert.
Eine Teilnehmerin fragt, warum ein Doppelklick auf einen Eintrag (eine Mailadresse) die Möglichkeit bietet, weitere Details einzublenden, während ein Doppelklick auf die Anzahl diese aggregierte Zahl zu „entfalten“, also alle Datensätze anzuzeigen, die sich dahinter verbergen.
Clevere Frage. Und: ich weiß keine Antwort. Ich kann nur vermuten, warum Microsoft das SO eingerichtet hat.
Eine schöne Frage in der letzten PowerBI-Schulung:
Wie viele Funktionen kann man in DAX ineinander verschachteln. „Genug“ lautete meine Antwort. „Sehr viele“, um etwas präziser zu sein. Ich habe gesucht und nicht gefunden. Erstaunlich. Also habe ich ausprobiert. Aber 100 Ebenen habe ich aufgehört:
Das dürfte genügen.
Fürs Erste.
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:
und auf:
https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-create-and-manage-relationships
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