Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1580to1584
1580to1584
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Matrix mit Pivot erstellen

Forumthread: Matrix mit Pivot erstellen

Matrix mit Pivot erstellen
21.09.2017 11:46:19
Dani
Hallo Zusammen,
ich benötige Hilfe bei einer Excel-Auswertung.
Ich habe eine Excel-Liste mit 500.000 Datensätze. Sie beschreibt die Materialbewegungen von einem Ort (Quelle) zu einem anderen Ort (Senke). Die Daten beziehen sich auf 1 Jahr.
Die Liste sieht wie folgender Ausschnitt aus:
Userbild
Ich möchte jetzt gerne eine Von-Nach-Matrix erstellen wie dieses Bild zeigt:
Userbild
In die Tabelle soll nun die Durchschnittsmenge der Materialbewegung pro Tag für eine definierte Strecke (Quelle-Senke) berechnet werden.
Dafür muss zuerst die Summe der Menge pro Tag (Datumstag) zwischen der spez. Quelle & Senke berechnet werden und danach aus allen Ergebnissen den Mittelwert für die jeweilige Strecke.
Das gestaltet sich jedoch als sehr sehr zeit- und arbeitsaufwendig.
Deshalb möchte ich das gerne mit einer Pivot-Tabelle lösen. Die Matrix habe ich schonmal hinbekommen, siehe Bild:
Userbild
Aber: in dieser Tabelle wird die Menge lediglich summiert, unabhängig vom Datum und ohne Mittelwert.
Wie berechne ich die Durchschnittsmenge pro Tag je Strecke? Ich krieg leider keine Formel hin... Und wo/wie muss ich dann die Formel in die Pivot-Tabelle einbetten?
Vielen lieben Dank schonmal :)
Dani
Anzeige

21
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: nachgefragt ...
21.09.2017 13:30:13
...
Hallo Dani,
... wie viele "Quellen" und "Senken" gibt es denn bei Dir max?
Deine Bilddaten sind offensichtlich nur eine Ergebnis aus einem kleinen Teilauszug aus Deinen 500.000 Datensätzen.
Kannst Du diese Beispieldatei anstelle als Bild hier als Datei einstellen und dafür Dein Zielergebnis angeben?
Gruß Werner
.. , - ...
Anzeige
AW: nachgefragt ...
21.09.2017 16:32:05
Dani
Leider darf ich die Exceldatei nicht hochladen. Ich versuche es nochmal besser oder verständlicher zu erklären:
Beispiel: Am 1.9.2016 gehen 5 und 2 Artikel von AAB nach SUR. Das ist nun die Summe der Artikel die am 1.9.16 genau auf dieser Strecke von AAB nach SUR gebracht wurden. Vielleicht wird zb am 1.10.2016 ebenfalls 20 Artikel genau auf dieser Strecke bewegt.
Ich möchte nun einen Mittelwert für die durchschnittlich bewegten Artikel pro Tag in einem Zeitraum von einem Jahr genau auf dieser Strecke berechnen. So kann ich am Schluss sagen: Auf der Strecke von AAB nach SUR werden durchschnittlich täglich 20 Artikel transportiert.
Und das brauch ich für alle Quellen und Senken. Es sind insgesamt 124 Quellen und 146 Senken in 430.000 Zeilen. Was das einzelne Bearbeiten schier unmöglich macht.
Ich hoffe meine Erklärung reicht aus!
Anzeige
AW: dazu ...
21.09.2017 16:52:45
...
Hallo Dani,
... ich wollte nicht Deine Originaldatei hier eingestellt haben (geht auch wegen der Größe sowieso nicht). Aber das was Du hier zuerst als Bilddaten eingestellt hast, was offensichtlich ein Extrakt aus Deiner Datei darstellt, kannst Du doch als Datei einstellen.
Gruß Werner
.. , - ...
Anzeige
AW: nachgefragt ...
21.09.2017 18:08:33
Dani
Hallo Werner,
achso :D
Also ich habe das Ergebnis schonmal auf den darauffolgenden Tabellenblättern berechnet. Wie ihr seht ist das schon für diese kleine Tabelle sehr umständlich und aufwändig und man muss sehr viele Exceldateien anlegen (mehrere 100 für die große Datei).
Deshalb mein Versuch das über eine Pivot-Tabelle zu lösen, leider bin ich darin aber nicht so gut. Die Lösung wie es dann aussehen sollte ist im Tabellenblatt "Von-Nach-Matrix".
Vielleicht gibt es auch einen ganz anderen Ansatz?
https://www.herber.de/bbs/user/116434.xlsx
Vielen Dank schonmal für die Hilfe :)
Liebe Grüße, Dani
Anzeige
AW: nachgefragt ...
21.09.2017 18:32:08
Fennek
Hallo,
die Summe der Mengen kann berechnet werden mit

=SUMMEWENNS($C$2:$C$11;$D$2:$D$11;D2;$E$2:$E$11;E2)
Sheet "Haupttabelle", F2. Wenn man die Anzahl der Tage berechnet, ist der Quotient der Mittelwert.
Falsch gedacht?
mfg
AW: PIVOTausw. aber mit Hilfspalte +Hilfszelle ...
21.09.2017 20:21:20
...
Hallo Dani,
... bei Deiner Datenmenge kommt wirklich nur eine PIVOTauswertung in Frage. Ein Problem ist jedoch, dass das die Möglichkeit darin mit berechneten Feldern zu arbeiten mE leider zu rudimentär ausgelegt ist.
Deshalb würde ich in Deiner Haupttabelle folgende Hilfsspalte in Spalte F vorschlagen:
In F1 Menge_je_Arb_Tage in F2: =C2/H1 und in der Hilfszelle H1: 

=SUMME(N(HÄUFIGKEIT(A2:A500000;A2:A500000)>0))
Dann die Formel F2 (durch Doppelklick auf die rechte untere Ecke der Zelle) nach unten kopieren.
Da ich keinerlei Erfahrung mit derartigen Massendaten habe, kann ich Dir nicht sagen, wie lange Excel für Deine knapp 1/2 Mio Datensätze benötigt.
Nun eine neue Pivotauswertung für A1:F###### die Feldzuweisung wie bisher nur in Werte anstelle Menge nun die Menge_je_Arb_Tage schieben.
Für die Ergebniszellen das Zahlenformat auf 0,0;; definieren und ... es sollte geschafft sein.
Gruß Werner
.. , - ...
Anzeige
AW: PIVOTausw. aber mit Hilfspalte +Hilfszelle ...
22.09.2017 22:59:00
Dani
Wow super es funktioniert :)
Vielen lieben Dank für die schnelle und gute Hilfe!!! Echt klasse, ihr habt mir sehr geholfen :)
AW: interessehalber nachgefragt ...
23.09.2017 11:22:05
...
Hallo Dani,,
... wie lange rechnet Dein PC für die knapp 1/2 Mio Hilfszellen? Oder anders gefragt, wie viel Zeit braucht bei Dir Excel, wenn Du Deine so abgespeicherte Datei neu öffnest?
Gruß Werner
.. , - ...
Anzeige
AW: interessehalber nachgefragt ...
23.09.2017 18:17:08
Dani
Hallo Werner,
ganz einfach war es nicht. Zuerst habe ich die Formel durch Doppelklicken auf das Kästchen auf die gesamten Zeilen angewendet. Nach ca. 1h war die Berechnung bei 13%. Dann hat sich leider Excel aufgehängt und ist abgestürzt. Leider ohne Wiederherstellungskopie.
Aus diesem Grund habe ich die Formel dann immer für 10.000 Zeilen angewendet und ca 30s-1min gewartet bis diese berechnet waren. Damit war ich zwar 3h beschäftigt und der Laptop musste ziemlich schnaufen, aber dafür ist jetzt alles funktionstüchtig.
Die Datei ist nur 40MB groß und dauert zum öffnen nur 15-20sek.
Danke nochmal für die Hilfe! :)
Anzeige
AW: interessant wäre jetzt noch ...
23.09.2017 20:28:51
...
Hallo Dani,
... wie Dein "Laptop" ausgestattet ist. Aber schon jetzt vielen Dank für Deine Information, denn wie ich geschrieben hatte, hab ich lediglich Erfahrungen mit max. ca. 1000 Datensätzen.
Ich denke Morgen nochmal über eine andere Lösung nach, denn bei Datenänderung dürften sich die Probleme wieder einstellen. Evtl. ist ja auch ein Weg über "gestaffelte" Pivotauswertung, aber mit korrekten Ergebniswerten, schon möglich.
Gruß Werner
.. , - ...
Anzeige
AW: interessant wäre jetzt noch ...
26.09.2017 09:23:07
Dani
Hallo Werner,
mein Laptop hat folgende Ausstattung:
Prozessor: Intel Core i5-7440HQ CPU @ 2.80GHz und 16GB RAM
LG Dani
AW: dann ...
26.09.2017 09:37:30
...
Hallo Dani,
... sollte Dein Laptop mit meinem neuen Vorschlag (siehe meinen Beitrag von 24.09.2017 14:19) schneller zum Ergebnis gelangen.
Gruß Werner
.. , - ...
Anzeige
AW: mt Formel die PIVOTausw. schneller auswert ...
24.09.2017 14:19:19
...
Hallo Dani,
... das dürfte die Auswertung arg beschleunigen.
Nimm die Pivotauswertung nun doch nur für Menge vor.
Da Du nach Deinen Angaben nur 124 Quellen und 146 Senken hast, ergibt sich auch eine derartig große Pivotauswertungsmatrix. Sämtliche so ermittelten Werte kannst mit einer Formel durch meine bereits angegebene Hilfszellenformel in Haupttabelle!H1 dividieren und so in eine endgültige Ergebnismatrix wandeln. Die Bezeichnungen für die "Quellen" und "Senken" holst Du mit einem einfachen Zellbezug in die Ergebnistabelle.
Für diese Ergebnismatrix werden somit nur =124*146=18104+124+136 Formeln benötigt und somit nur ein Bruchteil der Formeln meiner ersten Lösungsangabe.
Und auch noch wichtig, bei Aktualisierung brauchst Du nach wie vor nur einen Mausklick.
Gruß Werner
.. , - ...
Anzeige
AW: mt Formel die PIVOTausw. schneller auswert ...
27.09.2017 08:31:17
Dani
Klasse, habe das auch mal ausprobiert. Es funktioniert und geht um einiges schneller!
Vielen lieben Dank :)
AW: freut mich auch, danke für die Info owT
27.09.2017 12:15:56
...
Gruß Werner
.. , - ...
AW: Matrix mit Pivot erstellen
23.09.2017 18:50:51
Daniel
HI
hierzu müsstest du in zwei Schritten vorgehen.
1. in einer Pivot-Tabelle die Summen pro Tag und Strecke bilden
2. auf Basis dieser Pivot-Tabelle dann die Mittelwerte pro Strecke
nur leider ist es nicht möglich (zumindest habe ich es nicht hinbekommen) eine Pivotabelle als Basis für eine zweite Pivottabelle zu verwenden.
Aber man kann die erste Pivottabelle kopieren und als Wert einfügen, und diese Liste kann dann als Basis für die zweite Pivot-Tabelle verwendet werden
hierzu muss man die erste Pivottabelle so einstellen, dass sie keine Gesamt- und Zwischenergebnisse ausgibt, und die Gruppierungswerte müssen sich wiederholen
(kann man alles im Pivot-Menü einstellen: Berichtstlayout: Tabellenformat + Alle Elementnamen)
schau dir mal das Beispiel an:
https://www.herber.de/bbs/user/116463.xlsx
Gruß Daniel
Anzeige
AW: Deine Beispieldatei ergibt jedoch nicht das ..
23.09.2017 20:28:57
...
Hallo Daniel,
... was von Dani als Zielvorgabe angeben war.
Aber vielleicht ist ja die Zielvorgabe prinzipiell so ähnlich doch ermittelbar. Allerdings heute such ich nicht mehr danach. Vielleicht aber Du.
Gruß Werner
.. , - ...
AW: kannst du die Unterschiede konkreter beschreib
23.09.2017 20:36:16
Daniel
en?
Gruß Daniel
AW: für Beisp. waren/sind Ergebn. vorgegeben ...
24.09.2017 11:30:35
...
Hallo Daniel,
... und wenn Du Deine Ergebnisse mit den von Dani in ihrer Datei https://www.herber.de/bbs/user/116434.xlsx und da in "VON-NACH-Matrix" angegebenen Werten vergleichst, wirst Du die Unterschiede erkennen.
Gruß Werner
.. , - ...
Anzeige
AW: einfach in einer Hilfsspalte die Werte durch
24.09.2017 12:45:17
Daniel
die Anzahl der Arbeitstage, über die der Mittelwert gebildet werden soll, teilen und mit Hilfe dier Pivottabelle die Summe über diese Hilfsspalte bilden.
Gruß Daniel
AW: und wo siehst Du nun den Unterschied ...
24.09.2017 13:51:41
...
Hallo
... zwischen Deiner jetzigen rein verbalen Angabe zu der Lösung die ich Dani vorgeschlagen habe?
Gruß Werner
.. , - ...
Anzeige
;

Forumthreads zu verwandten Themen

Duplikate filtern ohne Matrix und AutoFilter
Sascha

Hallöchen Ihr Helfer in der Not ;)
Ich mal wieder...
gibt es eine andere Möglichkeit als Autofilter und Matrix um Duplikate an anderer Stelle des selben Tabellenblatts zu filtern?
Ich habe die Zeilen B1:B2496 mit doppelten einträgen drin und möchte in der Spalte J:J im selben Tabell...
23.01.2009 15:02:00
Thread-ID: 1043021
12 Beiträge
Anzeige
Autofilter bei Pivot????
Julia

Hallo,
ich habe eine Excel Datei mit zig tausend Datensätzen.
Kann man bei eine Pivottabelle einen Autofilter setzten?
Beispiel.
In meiner Tabelle gibt es eine Spalte Projekte und Spalte Ansprechpartner.
Wähle ich in der Pivottabelle Projekt A aus zeigt er mir trotzdem alle...
23.02.2009 14:05:36
Thread-ID: 1053071
2 Beiträge
Autoformatieren Pivot abstellen
Wilfied

Hallo Leutz,
kann man das Autoformatieren der Pivottabellen standardmäßig abstellen?
Ich stelle dies nach dem Erstellen der Pivot eh immer ab und würde dies deshalb lieber als Voreinstellung deaktiviert haben.
Greeeetz Will
07.07.2010 14:04:12
Thread-ID: 1165397
2 Beiträge
Autoformatierung Pivot-Diagramm
Tomi

Hallo,
wie kann ich die Autoformatierung eines Pivot-Diagrammes ausschalten.
Wenn ich bspw. die Farbe einer Linie im Diagramm ändere, anschließend im Dropdown-Menue weitere Daten einblende und dann wieder auf die ursprüngliche Ansicht wechsele (mit den veränderten Farben) dann sind die...
07.09.2010 21:19:27
Thread-ID: 1176120
2 Beiträge
Datenquelle der Pivot öffnet sich beim Aktualisier
Galic

Hallo,
kann mir Einer sagen warum sich meine externe Datenquelle öffnen, wenn ich meine Pivottabelle aktualisiere?
Vielen Dank.
28.06.2012 10:38:00
Thread-ID: 1269303
3 Beiträge
Aus Spalte eine Matrix erstellen
Franky

Hallo zusammen,
ich hab ein Problem das ich nicht gelöst bekomme.
Es geht darum einen Lagerbereich im Excel abzubilden.
Ich habe z.B.: in Spalte C die Lagerplätze stehen und in Spalte D ob der Lagerplatz
belegt ist oder nicht. Der Lagerplatz liegt in folgenden Format vor:
Bs...
23.02.2015 13:39:57
Thread-ID: 1410960
8 Beiträge
Anzeige
Falsches Gesamtergebnis Pivotabelle
Martin

Hallo,
ich gruppiere in einer Pivottabelle Arbeitstunden und möchte wissen wieviel Mehr- oder Minderstunden pro Monat gearbeitet wurden. Dafür verwende ich eine berechnetes Feld in dem ich die Fixxtunden pro Monat(173)von den tatsächlichen abziehe. Das macht Excel auch prima. Nur leider gib...
21.08.2015 09:54:14
Thread-ID: 1443583
4 Beiträge
Anzeige
Makrofehler Pivottabelle
riesen

Ich habe ein Makro zum automatischen Versand v. Kundenberichte. Wenn keinen Bericht in der Pivottabelle für den Kunden gefunden wird, erhalte ich die beiliegende Meldung.
Das Element mit diesem Namen darf man nicht umbenennen und die Frage mit abbrechen beantworten. Jedesmal muss ich manu...
18.10.2015 12:36:18
Thread-ID: 1452892
3 Beiträge
Powerpivot Datei öffnen dauert ewig
Frank

Guten Morgen zusammen,
ich habe eine Exceldatei, die ein Powerpivot-Datenmodel mit 170.000 Datensätzen enthält. Die Datei (binär gespeichert mit Endung xlsb) ist trotz der Datenmenge moderate 7MB groß.
Leider dauert es circa zwei Minuten diese Datei zu öffnen, also wesentlich länger als...
05.12.2016 10:54:21
Thread-ID: 1528627
3 Beiträge
Aus Liste eine Impact Matrix erstellen
Peat

Hallo zusammen,
ich hänge an folgendem Problem. Aus einer Auflistung an Begriffen soll automatisch eine Impact Matrix generiert werden. Auch wenn neue Punkte in die Liste augenommen werden, sollen diese dann ebenfalls in der Matrix erscheinen. Zum besseren Verstndnis habe ich eine Bild mit...
25.03.2018 17:23:41
Thread-ID: 1616253
3 Beiträge
Anzeige
Pivottabelle Gesamtergebnis als % anzeigen
Dani

Ich brauche eure Hilfe....Ich habe eine Pivottabelle erstellt - Spalten: Planwerte / Istwerte / Zielerreichung.
Die Werte in der Spalte Zielerreichung sollen als Prozent % dargestellt werden.
In der Zeile vom Gesamtergebnis sollen diese Werte jedoch nicht summiert werden, sondern als P...
23.04.2018 09:43:51
Thread-ID: 1620929
5 Beiträge
Matrixformel und Autofill per VBA
Uwe

Hallo zusammen,
meine Herausforderung ist es in der Tabelle1 (Zelle G23) folgende Matrixformel mittels VBA einzutragen:
.Cells(23, 8).FormulaArray = IF(A23="","",SUM(IF((Tabelle3!$A$1:$A$10000=Tabelle1!A23)*(Tabelle3!$K$1:$K$10000=Tabelle1!$G$22),1)))
Dies funktioniert auch soweit.<...
07.08.2018 09:49:08
Thread-ID: 1637867
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Matrix mit Pivot erstellen

Schritt-für-Schritt-Anleitung

  1. Daten vorbereiten: Stelle sicher, dass deine Daten in einer Excel-Liste korrekt formatiert sind. Die Spalten sollten beispielsweise Datum, Quelle, Senke und Menge enthalten.

  2. Pivot-Tabelle erstellen:

    • Wähle deine Daten aus und gehe zu Einfügen > PivotTable.
    • Wähle den Speicherort für die Pivot-Tabelle (neues oder bestehendes Arbeitsblatt).

  3. Felder zur Pivot-Tabelle hinzufügen:

    • Ziehe das Datum in den Bereich "Zeilen".
    • Ziehe die Quelle und Senke in den Bereich "Spalten".
    • Ziehe die Menge in den Bereich "Werte". Stelle sicher, dass hier die Summe der Menge angezeigt wird.

  4. Pivot-Tabelle anpassen:

    • Klicke mit der rechten Maustaste auf die Summenwerte und wähle Wertfeldeinstellungen. Wähle hier Mittelwert, um die Durchschnittsmenge zu berechnen.
    • Um die Pivot-Elementnamen zu wiederholen, gehe zu PivotTable-Analyse > Berichtslayout und wähle Tabellenformat + Alle Elementnamen.

  5. Datum in Tagen anzeigen:

    • Um die Pivot-Tabelle Datum Tage anzuzeigen, kannst du die Datumsspalte in die Zeilen ziehen und die Datumsgruppierung nach Monaten oder Tagen anpassen.

Häufige Fehler und Lösungen

  • Fehler: Pivot-Tabelle zeigt nur Summen: Stelle sicher, dass du die Wertfeldeinstellungen auf Mittelwert geändert hast.
  • Problem: Excel stürzt ab bei großen Datenmengen: Teile deine Daten in kleinere Segmente oder verwende eine Hilfsspalte zur Berechnung der Durchschnittswerte, bevor du die Pivot-Tabelle erstellst.
  • Fehler: Daten werden nicht korrekt gruppiert: Überprüfe die Datumsformate und stelle sicher, dass sie einheitlich sind.

Alternative Methoden

  • Hilfsspalte verwenden: Berechne die Menge je Arbeitstag in einer neuen Spalte, indem du die Menge durch die Anzahl der Arbeitstage teilst. Dies kannst du anschließend in der Pivot-Tabelle verwenden.

    Beispiel für die Formel in F2:

    =C2/H1

    Hierbei ist H1 die Zelle, in der die Anzahl der Arbeitstage steht.

  • Datenbankfunktionen nutzen: Du kannst auch Excel-Datenbankfunktionen wie DBSUM oder DGET verwenden, um spezifische Berechnungen ohne Pivot-Tabellen durchzuführen.

Praktische Beispiele

  1. Materialflussmatrix erstellen: Erstelle eine Pivot-Tabelle, die die Materialbewegungen von verschiedenen Quellen zu Senken anzeigt. Verwende eine Matrix-Tabelle, um die Flüsse übersichtlich darzustellen.

  2. Berechnung der Durchschnittsmenge: Setze die Pivot-Tabelle so auf, dass sie die Durchschnittsmenge der Artikel pro Tag für eine definierte Strecke zwischen Quelle und Senke anzeigt.

Tipps für Profis

  • Verwende slicer: Mit Slicern kannst du deine Pivot-Tabelle interaktiv gestalten und die Daten nach Quelle oder Senke filtern.
  • Pivot-Tabellen aktualisieren: Denke daran, deine Pivot-Tabelle regelmäßig zu aktualisieren, um sicherzustellen, dass sie die neuesten Daten anzeigt (Rechtsklick in die Pivot-Tabelle und Aktualisieren wählen).
  • Namen vergeben: Vergib aussagekräftige Namen für deine Pivot-Tabellen und -Felder, um die Übersichtlichkeit zu erhöhen.

FAQ: Häufige Fragen

1. Wie erstelle ich eine Matrix mit einer Pivot-Tabelle? Um eine Matrix mit einer Pivot-Tabelle zu erstellen, musst du deine Daten in die Pivot-Tabelle einfügen und dann die gewünschten Felder als Zeilen und Spalten anordnen.

2. Kann ich mehrere Pivot-Tabellen aus einer einzigen Quelle erstellen? Ja, du kannst mehrere Pivot-Tabellen aus denselben Daten erstellen, indem du sie einfach in verschiedenen Bereichen oder Arbeitsblättern platzierst.

3. Wie kann ich die Pivot-Tabelle nach Datum gruppieren? Klicke mit der rechten Maustaste auf ein Datum in der Pivot-Tabelle und wähle Gruppieren, um die Daten nach Tagen, Monaten oder Jahren zu gruppieren.

4. Was ist der Unterschied zwischen einer Matrix-Tabelle und einer Pivot-Tabelle? Eine Matrix-Tabelle ist eine spezielle Form der Datenpräsentation, während eine Pivot-Tabelle eine flexible Möglichkeit bietet, Daten zu aggregieren und zu analysieren.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige