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, …)
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.
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:
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.
Eine Teilnehmerin in der Excelschulung fragte mich, warum auf einem Rechner folgendes funktioniert, auf einem anderen nicht:
Normalerweise bewirkt ein Klick auf eine Zelle in der Pivottabelle, dass die Formel
=PIVOTDATENZUORDNEN
erzeugt wird:
Bei ihr jedoch nicht:
Auch das Eintippen der Formel hilft nicht – Intellisense versagt:
Des Rätsels Lösung war schnell gefunden: sie hatte in den Optionen / Formeln die Option „GetPivotData-Funktionen für PivotTable-Bezüge verwenden“ ausgeschaltet.
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:
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.
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):
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:
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.
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:
In der letzten Excelschulung wurde ich gefragt, ob man Pivottabellen auf einem geschützten Tabellenblatt erzeugen, ändern und aktualisieren kann?
Ich gebe zu – ich war ein bisschen unsicher.
Und das sind die Antworten:
Auf ein schreibgeschütztes Blatt kann keine Pivottabelle eingefügt werden. Auch dann nicht, wenn alle Optionen zum Zulassen aktiviert sind. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.
Eine Aktualisierung ist nicht möglich, wenn das Blatt geschützt ist. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.
Wurde beim Blattschutz die Option “ PivotTable und PivotChart verwenden“ aktiviert wurde, kann man die Felder in die Zeilen, Spalten, Filter, … ziehen und von dort wieder entfernen – auch wenn die Zellen gesperrt sind.
Wurde das Tabellenblatt mit der Datenquelle geschützt, kann man keine Pivottabelle erstellen:
Schöne Frage in der letzten Excelschulung. Ich habe eine Übung erstellt: Dutzende von Fehlern: Bezugsfehler, Formatierungsfehler, falsch Zeichen („x“ statt „*“; „;“ statt „:“, …) ausgeblendete Zeilen, weiße Schriftfarbe, …
Danach erstellen wir eine Pivottabelle. Eine Teilnehmerin fragt, wo Quellen von Rechenfehlern liegen können. Man sieht das Ergebnis einer Summe – aber stimmt es auch?
Ich überlege:
Der Bereich kann falsch gewählt sein
Wird mit Bereichen gearbeitet, kann sich die Pivottabelle beispielsweise auf einen Bereich auf einem falschen Tabellenblatt beziehen
Wird mit intelligenten Tabellen gearbeitet, kann eine falsche Tabelle verwendet worden sein.
Das kann man über Pivottable-Analyse / Datenquelle ändern herausfinden.
Die Pivottabelle wurde nicht aktualisiert.
Die Beschriftung wurde sinnentstellt geändert.
Habe ich etwas vergessen? Sicherlich … Ich fand die Frage sehr interessant …
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.
Schon doof. Excel zeigt viele Dinge an, aber leider nicht die Liste der Pivottabellen. Mourad Louha schlägt ein kleines VBA-Skript vor, um die Liste aller Pivottabellen auszulesen:
Public Sub ListPivotTables() Dim c As Long Dim b As Worksheet Dim o As PivotTable Dim St As Worksheet On Error Resume Next Set b = ThisWorkbook.Worksheets.Add c = c + 1 b.Cells(c, 1).Value = "Name" b.Cells(c, 2).Value = "Quelle" b.Cells(c, 3).Value = "Aktualisierung" b.Cells(c, 4).Value = "Arbeitsblatt" b.Cells(c, 5).Value = "Bereich" b.Cells(c, 6).Value = "MDX" For Each St In ThisWorkbook.Worksheets For Each o In St.PivotTables c = c + 1 b.Cells(c, 1).Value = o.Name b.Cells(c, 2).Value = o.SourceData b.Cells(c, 3).Value = o.RefreshDate b.Cells(c, 4).Value = o.Name b.Cells(c, 5).Value = o.TableRange1.Address b.Cells(c, 6).Value = o.MDX Next Next End Sub
Ich habe es ein klein wenig überarbeitet. Das Ergebnis:
Oder man benennt die Datei mit der Endung ZIP um, entpackt das Archiv und wirft einen Blick in die beiden Ordner pivotCache und pivotTable. Darin verbergen sich sämtliche Informationen zu den Pivottabellen:
Vorgestern habe ich auf dem Excel-Meetup einen Vortrag über Zahlenformate in Excel gehalten. Nach dem Vortrag schreibt Tanja:
Hallo René
Danke für
deinen Vortrag. War ein sehr guter Überblick.
Wieso hast
du das Problem mit den Monaten über Hilfsspalten und nicht über die Gruppierung
des Datums gemacht?
Dann wäre
es ja ganz einfach die fehlenden Monate anzuzeigen.
Sonnige Grüsse aus der Schweiz
Hallo Tanja,
ich freue mich über Mails, die beginnen mit „warum hast du
nicht …?“ Es gibt immer drei Möglichkeiten:
* entweder ich habe etwas übersehen – und es gibt wirklich
elegantere Lösungen
* oder es gibt mehrere Lösungen; ich habe mich für die eine
entschieden, weil …
* oder meine Lösung ist bewusst gewählt, weil …
Ich habe gestern Abend noch einmal nachgedacht:
[und dann folgt eine lange Erklärung, warum ich es nicht so gemacht habe, wie sie es vorschlägt. Das soll an dieser Stelle nicht wiederholt werden.]
Liebe Grüße aus dem verregneten München
René
PS: Beim Durchschauen habe ich gerade bemerkt, dass die Option „Elemente ohne Daten anzeigen“ nicht aktivierbar ist, wenn das Datenmodell eingeschaltet ist. Wusste ich nicht …
Hallo Rene
Danke für deine Erläuterungen. […]
Ich kann mir vorstellen, warum das nicht aktiv sein kann. Beim Zusammenspiel mit dem Datenmodell kann ich ja auch nicht in der Pivot-Tabelle nach einem Datum gruppieren. Wenn ich es sauber aufbaue, brauche ich dazu im Datenmodell ja eine Kalendertabelle und dort kann das Problem mit fehlenden Jahren oder Monaten gar nicht auftauchen, da eine Kalendertabelle ja immer den kompletten «Datumsbereich» enthalten muss.
Übrigens: wer meinen Vortrag ansehen möchte – man findet ihn auf:
Die Teilnehmerin der Excelschulung überlegt sich für jede Kategorie ein eigenes Tabellenblatt zu erstellen. So kann sie es leichter als PDF speichern und verschicken.
Ich überlege, ob die Berichtsfilterseiten ein geeignetes Werkzeug sein könnten. Ich erstelle eine Pivottabelle und achte darauf, dass die Daten NICHT zum Datenmodell hinzugefügt werden. Sonst kann man keine Berichtsfilterseiten erstellen:
Dennoch: der Befehl „Berichtsfilterseiten erstellen“ ist ausgegraut …
Ich habe eine Weile überlegt. Dann fiel mir ein: damit ich Berichtsfilterseiten erstellen kann, muss ich in der Pivottabelle einen Filter erstellen:
Die Daten, die auf den einzelnen Tabellenblättern dargestellt werden sollen, müssen als Filter vorliegen.
Tatsächlich! JETZT geht es!
Also: zwei Voraussetzungen, um Berichtsfilterseiten zu erstellen :
Beim Erstellen der Pivottabelle NICHT dem Datenmodell hinzufügen!
seit einiger Zeit habe ich ein Problem mit Pivot-Tabellen und wüsste gern, ob Du mir vielleicht helfen kannst.
Früher habe ich häufig die Funktion Berichtsfilterseiten anzeigen genutzt, aber irgendwie funktioniert es nicht mehr. Mir wird das Feld immer ausgegraut angezeigt.
Ich weiß nicht woran es liegt. Dr. Google konnte mir erstaunlicherweise auch nicht helfen, ich bin komplett ratlos.
Ich erzeuge regelmäßig Auswertungen für acht verschiedene Regionen und würde die Funktion dafür gerne verwenden, sodass jede Region ihren eigenen Reiter hat.
Ich hab mal eine Mini-Beispieldatei angehangen.
Es wäre toll, wenn Du das Brett von meinem Kopf entfernen könntest!
Liebe Grüße aus Köln,
Sabrina
Hallo Sabrina,
ich glaube, das ist eine einfache Frage:
Berichtsfilterseiten werden nur dann angeboten, wenn die Daten NICHT den Datenmodell hinzugefügt wurden!
Seltsam. Ich erstelle eine Pivottabelle, bei der ich mehrere Tabellen miteinander im Datenmodell verknüpft habe. Schon der erste Blick zeigt, dass das nicht korrekt sein kann:
Ein Blick in die Diagrammansicht von PowerPivot gibt Antwort: eine Verbindung wurde auf inaktiv gesetzt:
Man sieht es auch in der Feldliste der Pivottabelle:
Excelschulung. Wir üben Tabellen: sortieren, filtern und Pivottabellen. Wir erstellen die erste Pivottabelle. Wir gruppieren die Verkäufer. Ein Teilnehmer meldet sich und sagt, dass bei ihm keine Verkäufer angezeigt werden. Ich schaue mir seine Pivottabelle an:
Ich habe zwei Mal hinschauen müssen, dann habe ich entdeckt, dass in der Tabelle noch ein Filter eingeschaltet war. Dadurch sind die ersten Zeilen ausgeblendet und dadurch werden auch nicht Daten angezeigt. Perfide!
Ich habe eine Excelliste, in der trage ich Informationen zu meinen Schulungen ein. Ich habe eine Spalte „Stunden außerhalb“, in der ich bislang Texte eingetragen habe wie „2 x 24“ oder „3 x 10“. Nun hätte ich gerne die Summe der Tage ermittelt, also die Zahlen, die vor dem „x“ stehen summiert. Nichts leichter als das:
=LINKS(I351;SUCHEN(„x“;I351)-2)
liefert die Zahlen. Allerdings: steht kein Text in der Zelle, so liefert SUCHEN(„x“;I351) einen Fehler. Der kann bequem mit WENNFEHLER abgefangen werden:
=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);““)
Das Ergebnis:
Darauf setzte ich eine Pivottabelle auf und ändere den Vorschlag „ANZAHL“ in „SUMME“:
Eine Fehlermeldung ist die Folge. Vielleicht hängt es mit dem „“ der Funktion WENNFEHLER zusammen? Ich ändere die Formel in:
=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);0)
Die leeren Zellen werden mit einer „0“ aufgefüllt. Sieht nicht schön aus. Also ändere ich das Zahlenformat in ein benutzerdefiniertes:
0;-0;;
Und wundere mich erneut. Alles ist weg!
Langsam dämmert es mir: klar – die Funktion LINKS liefert einen Text. Das heißt: der Text „2“ wird durch das benutzerdefinierte Zahlenformat weggeblendet. Und: Excel erkennt keine Zahlen für die Pivottabelle, die summiert werden könnten. Also noch einmal die Formel anpassen. Der Text muss in eine Zahl konvertiert werden: mit WERT, mit „–“ oder mit „*1“
Da habe ich eine Anfrage von Herrn Diedmann erhalten, ob er etwas auf meinem Blog veröffentlichen kann. Na klar kann er – nervige Sache mit einer hervorragenden Lösung. Es geht darum in einer Pivottabelle nur die Nullwerte mit den entsprechenden Daten herauszufiltern. Lesen Sie selbst:
Sehr geehrter Martin,
Hier die Geschichte, die den Stein ins Rollen brachte.
Durch ein Lernstudio habe ich vor Ort bei einer
Getränkefirma Excel Unterricht vor Ort gegeben.
Es waren mit verschiedenen Office Versionen gearbeitet, von
2010 bis 2019
Bei Pivot war die Grundlage eine Tabelle von YouTube vom
Andreas Thehos.
Es sind Automobilverkäufer, die verschiedene Modell anbieten. Einige der Verkäufer haben in einem Zeitraum aber nicht bestimmte Modelle verkauft.
Lösung für Herrn N.….. Also.. Pivot Tabelle erstellen aus den Daten der BMW-Verkäufern
Pivot Tools… Entwurfsansicht… Berichtslayout… in Tabellenformat…
Es erscheint…
Weiter… so wie angezeigt. Modell anklicken, rechte Maustaste, Feldeinstellungen, Elemente ohne Daten auswählen und OK
Wie unten, ABEL Ergebnis anklicken, rechte Maustaste, Teilergebnisse keine und OK…
Die Nullwerte auszufiltern war und ist ja auch kein Problem.
Nun kam die Frage, Kann ich alle Verkäufer mit Nullwerte untereinander
ausfiltern?
Das Feld „Summe vom Endpreis“ zeigte keine
Filtermöglichkeit.
Aber, die Office Version 2010 hatte diese Filtermöglichkeit.
Das war die Herausforderung. Eine Lösung ohne VBA, denn das
können sie den Wenigsten vermitteln.
Die Lösung war letzthin und endlich, dass ich aus lauter
Verzweiflung einfach den Spaltenname über die projizierte Überschrift
geschrieben habe und schwupps…. Die Spalte ließ sich filtern.
Und….. Ratatataaaaaaa…..
Wenn jetzt noch Leere Daten als Null anzeige gesetzt wird…
Evtl. ist das das erste Thema, welches ich bei ihnen veröffentlichen kann.
Ansonsten zunächst frohe Fest und guten Rutsch ins neue
Jahr.
Warum beginnt Excel mit der Nummer 3 und hängt die Nummern 1 und 2 hinten an???
Über jeden Hinweis wäre ich dankbar.
Mit freundlichen Grüßen
Hallo Herr K.,
und des Rätsels Lösung finden Sie nicht? Nun – dann will ich
es Ihnen „verraten“:
Ein Blick auf Ihre Liste „Rohdaten“ liefert die Antwort:
Dort befindet sich neben den Zellen mit einer „1“ und einer „2“ ein Smarttag
mit dem Hinweis, dass die Zelle als Text formatiert ist oder ein Apostroph
vorangestellt wurde. Da beides nicht der Fall ist liegt die Vermutung nahe,
dass Sie diese Daten aus einem System erhalten haben, das diesen Text
„darunter“ geschoben hat:
Da es sich um zehn Zahlen handelt, habe ich sie noch einmal
eingetragen.
Achtung: Aktualisieren der Pivottabelle auf dem Blatt
„Nutzung“ genügt nicht; ich habe die Daten über Entwurf / Berichtslayout „im
Tabellenformat anzeigen“ lassen und dann sortiert:
Zur Info: Excel sortiert: 7 < 14 < 104 <
hundertvier < sieben < vierzehn. Zuerst Zahl, dann Text
Vorgestern in der Excelschulung haben wir über den Mittelwert in Pivottabellen diskutiert: Pünktchen verkauft 200 und 300 Streichholzschachteln; Anton 10, 20 und 30. Die Summe der fünf Verläufe beträgt 560, die Anzahl 5, also der Mittelwert liegt bei 112. Anton hat im Durchschnitt 20 verkauft, Pünktchen 250. Ich darf nun – um den Gesamtmittelwert zu berechnen – nicht 270 durch 2 teilen, sondern muss die Gesamtsumme (560) durch die Gesamtanzahl (5) teilen – sonst erhalte ich einen „schiefen“ Mittelwert. Excel rechnet hier korrekt; wenn etwas anderes gewünscht ist, muss man mit Hilfsspalten arbeiten.
Amüsant: In einer Liste befinden sich ganze Zahlen. Setzt man eine Pivottabelle auf die Liste auf und gruppiert sie, erscheinen die Kategorien, beispielsweise 0 – 4999, 5000 – 9999, 10000 – 14999, …
Werden allerdings Dezimalzahlen verwenden sieht die Gruppierung wie folgt aus: 0 – 5000, 5000 – 10000, 10000 – 15000, …
vielen Dank für deine Antwort. Das ist ja sehr freundlich, danke!
Und kannst du mir sagen, wie man die Gesamtkosten pro Bankverbindung (Bspw ABC Privatkunden) noch berechnen kann? Aber ohne die Teilergebnisse der Mitgliedschaft. Das wäre ganz toll. Liebe Grüße, Louise
Hallo Louise,
ich fürchte das geht nicht. Du gruppierst drei Kategorien. Entweder du lässt dir die Teilergebnisse für alle Kategorien anzeigen oder für keine. Lösung: ich würde eine zweite Pivottabelle erzeugen.
Lieber Rene,
Als ich die Excel öffnete, war ich nicht mehr zu halten. Ganz großartig, genau
das habe ich gebraucht. Vielen vielen Dank, du hast mich unglaublich
unterstützt. Davon kann ich nur weiterlernen. Du hast meine höchsten
anerkennenden Worte verdient. Danke danke danke.
Ich wünsche dir alles Gute,
Mit lieben Grüßen,
Louise
Zwei Pivottabellen – eine rechnet richtig, eine nicht. Gerechnet werden soll nicht:
(10+20)x(10+20) = 900
sondern
10×10 + 20×20 = 500
Wenn Sie genau hinschauen, sehen Sie den Unterschied:
In der ersten Pivottabelle wurde mit einem Berechneten Feld gearbeitet, das nicht so rechnet, wie ich es gerne hätte.
Im unteren Beispiel wurde die (formatierte/intelligente) Tabelle ins PowerPivot-Datenmodell gezogen – dort wurde gerechnet und das Ergebnis in Excel in einer Pivottabelle ausgegeben.
Ich öffne eine Excelmappe und versuche die Pivottabelle zu ändern. Ich erhalte eine Fehlermeldung:
„Der PivotTable-Bericht wurde ohne die zugrunde liegenden Quelldaten gespeichert. Wählen Sie den Befehl ‚Daten aktualisieren‘, um den Bericht zu aktualisieren.“
Verstanden! – ich versuche den Bericht zu aktualisieren und erhalte erneut eine Fehlermeldung:
„Der Bezug ist ungültig.“
Verstanden – ich schaue nach in den PivotTable-Tools in Analysieren / Daten / Datenquelle ändern – dort steht die Datenquelle (der Name der intelligenten Tabelle), auf der die Pivottabelle aufsetzt.
Was ist passiert: man erstelle eine intelligente/formatierte Tabelle; nenne sie beispielsweise „Mitarbeiter“. Man setze eine Pivottabelle auf, wobei die Option „Dem Datenmodell diese Daten hinzufügen“ nicht aktiviert sein darf.
Man lösche die Datenquelle, also das Tabellenblatt, auf dem sich die Tabelle „Mitarbeiter“ befindet.
Deaktiviert man in den Optionen der Pivottabelle (Registerkarte „Daten“) die Option „‚Details anzeigen‘ aktivieren, so ist kein Drilldown mehr möglich – also ein Doppelklick auf einen Zahlenwert führt zu einer Fehlermeldung:
„Sie können Datenzellen für Felder im Zeilen-, Spalten- oder Seitenbereich nicht bearbeiten.“
Noch perfider wird es, wenn man in den Optionen die Einstellung „Quelldaten mit Datei speichern“ deaktiviert. Speichert man die Excel-Arbeitsmappe, schließt sie und öffnet sie erneut, hat man etwas, das aussieht wie eine Pivottabelle, aber leider keine ist. Überall Fehlermeldungen!
Böse! Und nirgends ein Hinweis darauf, dass die Quelldaten entfernt wurde UND der PivotCache geleert wurde.
In einer Liste befinden sich eine Reihe von Spalten, die alle in einer Pivottabelle verwendet werden. Da es sich um getrennte Spalten handelt, wird keine Gesamtsumme zur Verfügung gestellt. Für DIESEN Fall stellen Pivottabellen keine Funktion(en) zur Verfügung. Man muss ein berechnetes Feld hinzufügen, beispielsweise:
Diese Lösung ist möglicherweise für andere Anwender auch hilfreich:
Ich habe in unserem in Excel abgebildeten Berichts- und Planungssystem eine Umlage gerechnet die die Hilfskostenstellen auf 0 setzt.
Da Excel nicht mit endlos vielen Stellen rechnet ergaben sich „echte“ Nuller und Ergebnisse die in der 10. oder 11. Nachkommastelle nicht 0 sind.
Prinzipiell blende ich Nullwerte aus, die echten Nuller verschwinden dann aus der Tabelle, die „unechten“ nicht (was unschön aussieht, die Unterscheidung zwischen echten und unechten Nullern ist mir allerdings bei diesen Mini-Beträgen egal).
Gelöst habe ich es letztlich mit einer bedingten Formatierung für den gesamten Zahlenbereich: =Runden(C12;4)=0 und als benutzerdefiniertes Zahlenformat ;;;
Obiges Zahlenformat ist besser als Schriftfarbe weiß da ich 1. verschiedenfarbige Zellhintergründe verwende und 2. diese Nuller bei Markierung der Zelle „sichtbar“ werden
habe dies im März 2018 in der xing-Gruppe „Access und Excel“ veröffentlicht
Ich erstelle eine Pivottabelle. In der zweiten Spalte befindet sich eine ID. Diese ID soll mit INDEX- und VERGLEICHS-Funktionen in einer anderen Tabellen gefunden werden und weitere Informationen geliefert werden, beispielsweise die Gesellschafterin und die Geschäftsbezeichnung Spalte A und B):
Filtert man die Pivottabelle werden jedoch die Zeilen außerhalb der Pivottabelle nicht ausgeblendet. Das bedeutet: ich erhalte eine Reihe Fehlerwerte:
Und nein – ich kann und will die anderen Daten nicht zu den Daten der Pivottabelle hinzufügen. Und ja: natürlich habe ich anschließend diesen Fehler mit WENNFEHLER abgefangen.
Schon irgendwie seltsam: ein Filtern der Pivottabelle wirkt sich nicht auf die Zeilen außerhalb aus. Anders als beim „normalen“ Filtern mit Autofilter.
Wenn man eine Pivottabelle erstellt, auf die Pivottabelle eine bedingte Formatierung aufsetzt – genauer: eine Bedingung: „alle Zellen mit Summe der Werte UND den Kategorien“ und eine Bedingung „nur Werte über oder unter dem Durchschnitt formatieren“, beziehungsweise „nur obere oder untere Werte formatieren“, dann stellt Excel die Option (in einem Dropdownfeld) zur Verfügung: für den ausgewählten Bereich für: alle Werte / jede Spaltengruppe / jede Zeilengruppe:
Das Ergebnis irritiert: Ich wähle „Spaltengruppe“ und erhalte mehrere Werte pro Spalte. Excel geht in der Pivottabelle davon aus, dass Werte spaltenweise nebeneinander gruppiert werden – das heißt: Spaltengruppe bedeutet „in jeder Zelle einer Zeile in jeder Spalte“. Ein bisschen verwirrend die Logik – vielleicht wird es deutlich, wenn man sich darüber im Klaren wird, dass Werte in der Spalte gruppiert werden, das heißt NEBENEINANDER (und nicht untereinander dargestellt werden):
Ich erstelle eine Pivottabelle und wundere mich, warum ich kein berechnetes Feld erstellen darf:
Mein Blick fällt in den Aufgabenbereich „Feldliste“ – dort erkenne ich, dass ich die Daten „dem Datenmodell hinzugefügt“ habe:
Warum? Weil ich diese Option als Standard aktiviert habe. Wo? – In Excel für Office 365 gibt es in den Excel-Optionen in der Kategorie „Daten“ eine Option dafür:
Also: zweiter Anlauf – ohne Datenmodell – und schon darf ich Felder berechnen …
Aus einer (intelligenten/formatierten) Tabelle wird eine Pivottabelle erzeugt, die ins Datenmodell hinzugefügt wird. So kann man über die PivotTable-Tools über die Schaltfläche „Beziehungen“ eine 1:n Beziehung zwischen dieser Tabelle und einer anderen herstellen. Zieht man allerdings ein Feld aus der einen Tabelle und ein anderes Feld aus der anderen Tabelle heraus, so ist das Erstaunen groß: die Beziehung greift nicht – jede Zeile wird mit jeder kombiniert:
Erst wenn man Daten in die „Werte“ zieht, funktioniert die Beziehung zwischen beiden Tabellen:
Gestern in der Excelschulung. Wir üben das Formatieren einer Pivottabelle: Eine neue Vorlage wird erstellt; Stripeset der ersten Spalte erhält eine Farbe, der zweiten Spalte ebenso; die gebänderten Spalten werden eingeschaltet. Ein Teilnehmer beschwert sich, weil es nicht funktioniert.
Die Ursache war schnell gefunden: er hatte die Musterfarbe verwendet und nicht die Hintergrundfarbe.
Man zwingt mich, nächste Woche Excel auf dem Mac zu unterrichten. Auch wenn „Version 2016“ draufsteht – es ist nciht Version 2016 (von Windows) drin – es fehlt so viel!
Schmerzlich vermisse ich bei den Pivottabellen die Option „Dem Datenmodell diese Daten hinzufügen“:
Oder auch viele der kleinen liebgewonnenen Helferleins, beispielsweise die Ansichtsänderung im Aufgabenbereich der Pivottabellen:
Der sagte mir letzte Woche, dass eine Beschwerde bei Microsoft quittiert wurde mit einem: „na, dann verwenden Sie halt die Windows-Version!“
Darf ich Sie wegen einem aktuellen Problem kurz was fragen. Ich habe eine Spalte C, in welcher in der Pivot Tabelle eigentlich ein Standard Format als Zahl ohne Nachkomma stehen soll.
Immer wenn ich die Pivot aktualisiere, wirft er mir hier EUR Werte aus. An was kann dies liegen? Der Haken Zellformatierung beibehalten ist auch gesetzt.
Hallo Herr Wieser,
ich habe noch etwas gefunden:
ändern Sie die Feldeinstellungen in den Pivottable-Feldern (rechts im Aufgabenbereich) von Währung auf Standard. Bei der Datei, die Sie mir geschickt haben, verschwindet dann das Euro-Symbol dauerhaft.
Darf ich Sie wegen einem aktuellen Problem kurz was fragen. Ich habe eine Spalte B, in welcher in der Pivot Tabelle eigentlich ein Standard Format als Zahl ohne Nachkomma stehen soll.
Immer wenn ich die Pivot aktualisiere, wirft er mir hier formatierte Werte aus. An was kann dies liegen? Der Haken Zellformatierung beibehalten ist auch gesetzt.
Hallo Herr Dr. W.,
klar dürfen Sie fragen – ich helfe gerne weiter:
Die Option „Zellformatierung bei Aktualisierung beibehalten“ bezieht sich nur auf die „Werte“, nicht auf die Informationen in den Zeilen (oder Spalten).
Schauen Sie mal bitte in der Spalte „Abteilung“ (Spalte B) nach, ob diese Spalte formatiert ist.
Ich habe mal einen Dummy erstellt – tatsächlich – beim Auf- und Zuklappen wird mein Format durch das ursprüngliche zurückgesetzt.
Heute im Excel-Coaching in einer Rechtsanwaltskanzlei.
Die Aufgabe: Aus einer Liste von Prozesskosten soll eine Übersicht erstellt werden, wir oft Kosten im Bereich 0 – 10.000 Euro, 10.000 – 20.000 Euro 20.000 – 30.000 Euro und so weiter vorhanden sind. Ich überlege: ZÄHLENWENN oder HÄUFIGKEIT? Ich entscheide mich für eine Pivottabelle. Schnell erstellt, schnell gruppiert – aber: padautz!
„Kann den markierten Bereich nicht gruppieren.“
Dieser Satz hat kein Subjekt. Und auch keine Begründung. Versuche es mehrmals, ziehe, schiebe, lösche … geht nicht. Bis ich ans Ende der Pivottabelle schaue:
Da hat doch tatsächlich jemand Zahlen falsch eingegeben: 49,240.8 oder 11,593.00. Und hat auch noch einige Texte eingetragen: „to be added“, bzw. „t.b.a.“ Also doch ZÄHLENWENN …
Ich erstelle eine PivotTabelle und möchte dort die Werte zusammenfassen.
Warum hat mein Kollege am Ende der Liste der Funktionen die Funktion „Diskrete Anzahl“ – ich dagegen nicht:
seins
meins
Die Antwort: die letzte Funktion „Diskrete Anzahl“ erscheint nur, wenn Sie beim erstellen der Pivottabelle „den Datenmodell diese Daten hinzufügen“ aktivieren.
Amüsante Fehlermeldung. Dabei wollte ich doch nur ein weiteres Feld in eine Pivottabelle einfügen:
Aber die Ursache ist schnell gefunden: Neben der Pivottabelle befand sich eine Formel. Excel kann nicht eine Spalte einfügen, verschiebt also nicht die Tabelle, sondern überschreibt die Formel:
Die Frage bleibt: Hätte man nicht „Tabelle2 enthält bereits Daten“ etwas anders formulieren können?
Ich nenne eine Spalte „Name“. Eine zweite Werte. Ich trage Phantasienamen und -werte ein. Setze eine Pivottabelle auf. Nö – „Werte“ wird nicht akzeptiert – das muss schon Werte2 heißen!
und hier sende ich Dir die Datei mit meinen Hinweisen.
Aber warum nimmt Excel die neuen Zielumsätze (denen kein Umsatz vorausgegangen war) nicht ins Gesamtergebnis auf?
Ich berechne: Zielumsatz = WENN(Umsatz>0;Umsatz*110%;500)
Viele Grüße
Angelika
Hallo Angelika,
Ich weiß wo der Denkfehler – oder der Pivotfehler steckt:
Pivot rechet nicht Summe der einzelnen berechneten Werte, sondern: berechnet die Summe:
Also nicht (in deinem Beispiel 3.045,90 + 500,00 + 594,83 + 2.692,80), die ja berechnet sind: = WENN(Umsatz>0;Umsatz*110%;500)
sondern Pivot rechnet: 2.769,00 + 0,00 + 540,75 + 2.448,00. Wenn diese Summe (5.757,75) > 0;Umsatz*110%;500) -> also 5.757,75 + 1,1 = 6.333,53
Excel-Schulung. Thema Pivot. Frage einer Teilnehmerin: „Warum sieht Ihre Pivottabelle anders aus als unsere? Wir verwenden doch beide Excel 2016 und die gleichen Daten.“
Die Antwort war schnell gefunden: Ich hatte auf meinem Laptop noch eine alte *.xls-Version der Beispieldatei erwischt – während ich den Teilnehmern die Datei als *.xlsx zur Verfügung gestellt hatte.
Heute in der Excel-Schulung haben wir uns schon ein bisschen gewundert. Hand aufs Herz – hätten Sie das gewusst?
Wir erstellen eine Pivottabelle:
Mit [F2] kann man eine Zelle editieren und den Text ändern. Ich ändere den Text „GROSS“ in „klein“. Konsequenterweise wird nun „klein“ in „GROSS“ umbenannt:
Wenn ich nun aber „klein“ in „mittel“, „GROSS“ in „klein“ und „mittel“ in „GROSS“ umbenenne – so habe ich die Texte vertauscht. verblüffend!
Amüsant. Einer der Verkäufer – Herr Sonntag – wird in einer Pivottabelle nach oben sortiert:
Die Vermutung liegt nahe, dass Excel zuerst Monatsnamen und Wochentage sortiert und dann den „Rest“ alphabetisch. Schön und gut – aber wie bekommt man den Sonntag einsortiert?
Die Antwort findet sich in den „weiteren Sortieroptionen“: Dort muss „Sortiert nach“ – der richtigen Spalte eingeschaltet sein. Und anschließend schaltet man über den Befehl „weitere Optionen“ „bei jeder Berichtsaktualisierung automatisch sortieren“ aus.
Ein weiteres Dankeschön an Pia Bork, die nicht nur das Problem kannte, sondern auch seine Lösung.
Eine Pivottabelle gruppiert alle Daten und summiert bei manchen den Wert 0. Diese Zeilen möchte man nun löschen (heißt: filtern). Wenn man den Filter in der Feldliste auswählt passiert – NICHTS!
so nicht!
Man muss den Filter der Pivottabelle bemühen, also den Wertefilter der Zeilenbeschriftungen. dann klappt es.
Heute: eine Schulung „umsteigen von Office 2007 auf Office 2016“. In 3,5 Stunden. Viel zu wenig Zeit für vier Programme (natürlich kamen auch noch Fragen zu OneNote). Also die wichtigsten Dinge ansprechen.
Beispielsweise die Datenschnitte in Excel.
Eine Teilnehmerin meldete sich und fragte, warum bei ihr die Datenschnitte nicht funktionieren:
Merkwürdiger Datenschnitt
Die Antwort war schnell gefunden: Die Tabelle wurde nicht als „intelligente Tabelle“ formatiert. Das kann man schnell an der fehlenden Registerkarte „Tabellentools“ erkennen.
Wahrscheinlich hatte sie die Option Einfügen / Tabelle betätigt und dann die Tabelle wieder über Tabellentools / Entwurf / In Bereich konvertieren zurück verwandelt. Sieht man nicht auf den ersten Blick, weil die Formatierungen bleiben.
Wir lernen heute: Datenschnitte können nur auf Pivottabellen oder intelligenten Tabellen aufgesetzt werden.
Habe unterschiedliche Mitarbeiter, die unterschiedliche Bonisätze bekommen sollen.
Die Bonitabelle liegt in anderem Tabellenblatt.
Damit ich in Pivot für jeden MA den richtigen Bonusbetrag anzeigen kann, habe ich mir mit SVerweis die Sätze in meine Ausgangstabelle geholt.
Und ein Feld berechnet. Soweit alles schön…
…nur, der Bonusbetrag ist um den Faktor 10 zu hoch!
Wer macht da was falsch : ich oder Excel?
Hiielfe! Kannst Du helfen?
Viele Grüße – Angelika
#####
Hallo Angelika,
der Knackpunkt in der Pivottabelle liegt im berechneten Feld. Das kann man leicht zeigen. Bau mal folgende Tabelle auf:
Setze eine Pivottabelle auf, gruppiere die Namen, summiere die Beträge:
Wenn du nun ein berechnetes Feld einfügst – Betrag * Provision – stellst du fest, dass die berechnete Provision doppelt so hoch ist, wie sie sein sollte:
Der Grund: die beiden Provisionssätze für Rene werden summiert (5% + 5% = 10%) und diese Zahl mit der Summe der Beträge multipliziert. Wenn du die Summe mit 5% multiplizieren möchtest, musst du den Provisionssatz (über einen SVERWEIS) reinholen und damit multiplizieren. Dann klappt es:
Ich öffne eine PivotTabelle. Erstaunlich: Neben den Monatsnamen befinden sich auch die Zahlen der Monate als Vorschlag. Aber beim Einblenden wird nichts angezeigt:
Auch in der Ursprungsliste befinden sich diese Werte nicht. Ein Blick auf die Datenherkunft zeigt, dass in der Liste eine Formel steht:
Wahrscheinlich wurde eine alte Formel, wie beispielsweise:
=WENN(ISTZAHL(A2);MONAT(A2);““)
durch diese Formel ersetzt.
Ein Aktualisieren der Pivottabelle nützt nichts, um die alten, nicht mehr vorhandenen Werte, zu löschen. Auch ein Entfernen der Spalte „Monat“ nützt nichts. Man muss beides machen: das Feld entfernen, aktualisieren und erneut einfügen. Dann sind die „überflüssigen“ Werte weg:
Ich weiß – man sollte es nicht. Aber ich bin sicher, dass es Leute gibt, die so etwas tun.
In einer Pivottabelle werden die Werte (beispielsweise Summe der Umsätze) formatiert. Danach formatiert man beispielsweise die Spalte C. Wer gewinnt? Der letzte.
Das Amüsante ist, dass sich die Pivottabelle die Formateinstellungen „gemerkt“ hat. Das kann verwirrend sein.
Der letzte gewinnt.
Übrigens: Wenn Sie mehrmals „hart“ – das heißt über das Excel-Zahlenformat und über die Wertfeldeinstellungen / Zahlenformat formatieren, passieren seltsame Dinge. Aber so etwas tut man ja auch nicht 😉
auf der Suche meines Excel-Problems bin ich über Ihre Seite gestolpert und würd mich freuen, wenn Sie mir ggf. helfen könnten: Ich habe hier eine ziemlich umfangreiche Pivot Tabelle und vermisse die Funktion, die es mir erlaubt anstatt des Min. oder Max-Wertes bei den Wertfeldeinstellungen Quartile auszugeben. Aber auch bei den Rohdaten tue ich mir schwer. Ich schaffe es zwar für eine vorgegebene Auswahl die Quartile zu berechnen, aber was ich benötige ist eine dynamische Ausgabe, z.B. bei Auswahl über den Auto-Filter. Auch mit Excel 2016 und Power-Pivot habe ich es bisher nicht geschafft. Haben Sie eine Idee wie so was gehen könnte?
Viele Grüße
#####
Hallo Herr B.,
das ist eine interessante Frage – danke dafür.
Ich habe heute auf dem Nachhauseweg geknobelt, wie man so etwas lösen wollte. Dachte an PowerPivot – aber wenn Sie schreiben, dass es DORT kein Quartil gibt, muss ich dort auch nicht mehr auf die Suche geben.
Wenn Sie eine einfache Pivottabelle hätten, könnten Sie Sie mit Matrixfunktionen oder der Funktion AGGREGAT nachbauen. Ich habe es mal versucht – siehe Anhang. Allerdings schreiben Sie „umfangreiche“ Pivottabelle – ich fürchte das Schlimmste.
Übrigens hat Andreas Thehos ein Video dazu gemacht:
Obwohl ich nach dem Ortsnamen sortiere, weigert sich Excel zu sortieren. Warum?
Geht nicht!
Die Ursache der vermeintlich falschen Sortierung finden Sie, wenn Sie einen Blick in Benutzerdefinierten Listen werfen. Befindet sich dort diese Liste hat sie Vorrang gegenüber der Standardsortierung. Anders als beim „gewöhnlichen“ Sortieren kann hier nicht umgestellt werden.
Lösung 1: Löschen Sie diese Liste
Lösung 2: Wenn Sie nicht auf Ihre Liste verzichten möchten, erstellen Sie diese Liste neu, sortieren diese Liste und importieren nun die sortierte Liste. Dann wird Excel auf diese sortierte Liste zugreifen.
Einen Haken hat diese Lösung: Wenn nun ein Begriff in der Pivottabelle auftaucht, der nicht in der benutzerdefinierten Liste vorhanden ist, steht er am Ende der Pivottabelle … Also doch Lösung 1 ?!?
Danke an Andreas Thehos für diesen Hinweis. Er hat auf
ich würde gerne in einer Pivottabelle eine Spalte sortieren, aber es klappt nicht. Warum?
Sortieren – geht nicht!
Die Antwort: Sie haben in Ihrem Beispiel eine Abhängigkeit zwischen dem Ort und dem Firmenname. Eine Firma ist in einem Ort. Deutlicher wird es, wenn Sie Daten verwenden, bei denen n Firmen in m Orten auftauchen. Beispielsweise Artikel und Verkäufer:
Hier geht es!
Hier wird deutlich, was sortieren bedeutet. Innerhalb der ersten Kategorie wird die zweite Kategorie sortiert. Aber pro Gruppe wird erneut sortiert. Da im ersten Beispiel nur ein Wert pro erste Kategorie vorhanden ist, „sieht man die Sortierung“ nicht.
Der Makrorekorder zeichnet manchmal erstaunlich Dinge auf. Vor allem ist unverständlich, warum er für den gleichen Befehl völlig unterschiedlichen Code aufzeichnet. In der letzten VBA-Schulung stellten wir fest, dass Einfügen / PivotTable in Excel 2010 entweder folgendes Makro aufzeichnet:
Sub Pivot01()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
„Datenbank!R1C1:R76C6″, Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:=“Tabelle1!R3C1″, TableName:=“PivotTable1“, _
DefaultVersion:=xlPivotTableVersion14
End Sub
Oder folgendes:
Sub Pivot2()
Sheets.Add
ActiveWorkbook.Worksheets(„Tabelle1“).PivotTables(„PivotTable1″).PivotCache. _
CreatePivotTable TableDestination:=“Tabelle2!R3C1“, TableName:= _
„PivotTable2“, DefaultVersion:=xlPivotTableVersion14
End Sub
Je nachdem, ob man auf das obere Symbol oder das untere klickt …
Übrigens: In Excel 2013/2016 gibt es nur noch ein Symbol für Pivottabelle.
Vor Kurzem habe ich den Datenschnitt in Excel entdeckt. Tolle Sache!
Allerdings habe ich eine Frage: Kann ich nur ein Fenster öffnen? Wenn ich ein zweites Fenster aufmache, ist das erste Fenster weg.
nur ein Fenster?
Die Antwort: Schieben Sie mal das Fenster zur Seite – mit Sicherheit hat sich das andere Fenster hinter dem zuletzt geöffneten verborgen. Denn: Natürlich können Sie mehrere Datenschnitt-Fenster öffnen.
Ein bisschen nervig ist es schon: Jedes Mal, wenn ich eine Pivottabelle erstelle, muss ich das Berichtslayout im Tabellenformat anzeigen lassen, anschließend alle Elementnamen wiederholen lassen, die Gesamtergebnisse und Teilergebnisse deaktivieren und die Schaltfläche ausschalten. Nur so können die Daten weiter gegeben werden. Kann man leider nicht abspeichern.
Kann man die Standardeinstellungen der Pivottabellen ändern?
Ich habe eine Liste, in der sich Leerzellen befinden. Auf diese Liste setze ich eine Pivottabelle auf. Leider verwendet Excel nun die Funktion Anzahl statt Summe. Muss ich jede Spalte einzeln ändern?
Liste und Ergebnis
Die Antwort: Leider ja!
G
M
T
Text-to-speech function is limited to 100 characters
Ich weiß nicht warum, aber wenn ich eine Pivottabelle erstelle sieht sie anders aus als bei meiner Kollegin. Und: Ich hätte auch gerne die Informationen (Jan, Feb, Mrz, …) neben den Ortsnamen und nicht darunter.
Pivot bei mir
Pivot bei ihr
Die Antwort: In den einzelnen Excel-versionen, werden verschiedene Layouts zugrunde gelegt. Sie können diese aber umschalten über die Pivottable-Tools / Entwurf / Layout / Berichtslayout. Wählen Sie dort die Option „Tabellenformat“. Übrigens: Ist Ihnen aufgefallen, dass Excel in der Version 2016 Datumsangaben gleich gruppiert?
Die Antwort: Setzen Sie den Cursor in die Tabelle oder markieren Sie nur die Spalten der Tabelle. Auf keinen Fall die leeren Spalten. Diese haben keine Überschriften (klar) und können deshalb nicht in der Pivottabelle verarbeitet werden (eigentlich auch klar).
Eigentlich wollte ich nur die Teilergebnisse anzeigen lassen. Aber Excel zeigt leider nur eine Option an. Ein bisschen wenig, nicht wahr?
Teilergebnis
Klar – Sie haben auch nur eine Spalte markiert. Entweder Sie markieren die gesamte Liste oder setzen den Cursor in die Liste ohne etwas zu markieren. Dann wird Excel alle Spaltenüberschriften als Gruppierungsoptionen bei den Teilergebnissen vorschlagen.
Was läuft hier schief? Obwohl ich die Pivottabelle aktualisiert habe, wird sie nicht aktualisiert. Oder nimmt einige Daten nicht auf.
Auch nach der Aktualisierung: dieser Datensatz fehlt!
Die Antwort: Schauen Sie mal in Pivottable-Tools / Analysieren / Daten / Datenquelle ändern nach, auf welchen Bereich, bzw. auf welches Tabellenblatt sich Excel bezieht. Vielleicht haben sie den Bereich zu klein gewählt.
Warum zeigt die Feldliste nur eine Spalte zur Auswahl an und nicht – wie sonst auch – alle Spalten der Tabelle?
Heute: Pivot light?!
Die Antwort: Sicherlich haben Sie in der Tabelle einige Zellen markiert. Sie können es mit Pivottable-Tools / Analysieren / Daten / Datenquelle ändern herausfinden: Dort zeigt Excel an, auf welchen Bereich die Pivottabelle zugreift:
In der Liste wurden zwei Zellen markiert.
Wahrscheinlich haben Sie beim Erstellen der Pivottabelle einige Zellen markiert – das hätte Ihnen auffallen können. Also: Entweder NICHTS markieren oder die gesamte Liste!
Warum darf ich keine Pivottabelle erstellen? Irgend etwas klappt hier nicht!
Keine Pivottabelle
Die Antwort: Richtig: Sie müssen in der Feldliste über das Symbol „Extras“ von der Option „Nur Abschnitte für Bereiche“ wechseln zur Einstellung „Abschnitte für Felder und Abschnitt für Bereiche“:
Manchmal schlägt die Pivottabelle bei der Zusammenfassung von Zahlen die Funktion ANZAHL statt der Funktion SUMME vor. Natürlich kann man die Funktion über die Wertfeldeinstellungen umschalten – dennoch erstaunt das Verhalten. Wahrscheinlich liegt es daran, dass in den Daten leere Zellen gefunden werden und diese als Text interpretiert werden.
Wie hat mein Kollege denn das gemacht? Er hat eine Pivottabelle erstellt (klar – kann ich auch). In einer Spalte summiert er die Umsatzzahlen (auch klar) und daneben lässt er sich den Rang anzeigen. Das geht aber nicht bei mir! Warum?
Wo ist der Rang?
Die Antwort: Ihr Kollege hat die Pivottabelle mit Excel 2013 erstellt. Dort gibt es im Kombinationsfeld die Einstellungsmöglichkeit „Rang“. In Excel 2007 war sie leider noch nicht vorhanden.
Lösung: Sie könnten die Daten sortieren – dann sehen Sie auch die größte Zahl oben, die kleinste unten oder umgekehrt.
Die Antwort: In der Pivottabelle können Sie nicht beliebig markieren. Wenn Sie markieren möchten, kann es passieren, dass Sie ein Element aus der Pivottabelle löschen. Die Lösung: Entweder die verwenden die Rückgängig-Funktion oder Sie ziehen die entsprechenden Elemente im Aufgabenbereich PivotTable-Felder wieder dorthin, wo sie hingehören.
Manchmal erhalte ich eine seltsame Meldung, wenn ich Pivottabellen erstelle:
Das Feld, das Sie in den Spaltenbereich ziehen, enthält mehr als 256 Elemente. Nur die ersten 256 Elemente werden als Spalten angezeigt.“
Seltsame Meldung
Die Antwort: Sie verwenden eine Excel-2003-Datei (*.xls). Diese Datei stellt pro Tabellenblatt nur 256 Spalten zur Verfügung. Wenn Sie nun ein Feld in die Spalten ziehen und Excel beim gruppieren der Daten mehr als 256 Elemente ermittelt, hat er ein Problem. Deshalb die Fehlermeldung.
Warum taucht in der Pivottabelle noch die Kategorie „Forschung“ auf? Die haben wir doch längst mit der Abteilung „Forschung und Entwicklung“ zusammengelegt.
Pivottabelle nicht korrekt
Wenn der Bereich der Datenquelle korrekt ist, so wurde die Pivottabelle möglicherweise nicht aktualisiert. Sie können (und müssen!) den Refresh erzeugen über das Kontextmenü, über Analysieren / Daten / Aktualisieren. Oder indem Sie in Analysieren / PivotTable / Optionen / Optionen / Daten die Option einschalten „Aktualisieren beim Öffnen der Datei“.
Zuerst war alles wunderbar. Aber irgendwann hat die Pivottabelle nicht mehr richtig gerechnet.
Die Pivottabelle liefert falsche Daten.
Die Antwort finden Sie, wenn Sie sich die Datenquelle ansehen (Analysieren / Daten / Datenquelle ändern). Dann stellen Sie fest, dass wahrscheinlich die Liste erweitert wurde – die Pivottabelle jedoch noch auf den „alten“ Bereich zugreift.
Zwei Lösungen: Entweder Sie vergrößern den Bereich per Hand im Doalogfeld.
Oder Sie wählen beim Erstellen der Pivottabelle die ganze Spalte aus. Dann können Sie die Liste beliebig erweitern.
Excel benötigt für die Erstellung einer Pivottabelle einen zusammenhängenden und rechteckigen Bereich. Jede Spalte dieses Bereichs muss eine Überschrift (selbstredend in der ersten Zeile) haben. In diesem Beispiel werden die Spalten A bis D als Überschriften identifiziert. Spalten e bis Q haben keine Überschrift in Zeile 1. Dass der Bereich eine Überschrift in Zeile 7 hat, kann Excel nicht erkennen.
Die Lösung: Trennen Sie die den Kopf von dem Datenbereich durch eine Leerzeile. Oder markieren Sie nur den unteren Bereich. Besser: zwei getrennte Bereiche!
Dieser Befehl setzt mindestens zwei Zeilen voraus, die Quelldaten enthalten. Der Befehl kann nicht auf die Auswahl von nur einer Zelle angewendet werden.
… behauptet Excel. Er lügt doch – ich habe eine Liste auf der ich eine Pivottabelle aufsetzen möchte. Warum klappt das nicht?
Pivot geht nicht.
Den Grund kann man HIER sehr schnell sehen – in der die Spalte D hat keine Überschrift. Pivottabellen verlangen unbedingt, dass jede Spalte immer eine Überschrift haben müssen. Hier sieht man es – schwieriger wird es, wenn die Tabelle sehr groß ist, das heißt, wenn die Tabelle sehr viele Spalten besitzt. Sie können mit der Tastenkombination [Strg]+[→], beziehungsweise [Strg]+[←] überprüfen, ob jede Spalte eine Überschrift besitzt.
Wenn Sie schon mit Excel 2003 (oder noch älteren Versionen) gearbeitet haben, haben Sie sich vielleicht gefragt, wo denn der alte Pivottabellen-Assistent geblieben ist. Damit konnte man mehrere Excel-Tabellen zusammenfassen.
Nun die Antwort ist einfach: Er ist seit Office 2007 nicht verschwunden, sondern nur sehr versteckt: Man muss ihn als Symbol in die Symbolleiste für den Schnellzugriff hinzufügen. Er befindet sich beispielsweise in der Kategorie „Nicht im Menüband enthaltene Befehle“ und heißt „PivotTable- und PivotChart-Assistent“.
Der „alte“ Pivottabellen-Assistent, mit dem man mehrere Bereiche zusammenfassen konnte.
Haben Sie sich auch gefragt, warum Excel das Ziel der Pivottabelle „Quelle“ nennt? Nun, das ist eine schräge Übersetzung aus dem englischen. Dort wurde der Dialog der Pivottable mit „Location“ übersetzt, also Zielort und nicht Quellbereich …
