Daten zählen mit Pivot

Forumthread: Daten zählen mit Pivot

12.10.2020 20:05:19

HannesStein

Hallo
habe eine Liste mit den Spalten Tag, Stadt, Automarke und 14 Datenzeilen in angehängter Datei.
https://www.herber.de/bbs/user/140843.xlsx
Dies ist nur ein Beispiel. Meine Originalliste hat hunderte Zeilen.
Die Frage: In wie vielen Städten kommt jedes Auto vor? (Hier: Renault: 3, Audi und alle anderen: 1) Achtung Renault kommt in Hamburg 2 Mal vor, ist aber nur einmal zu zälen, weil die Anzahl der Städte gefragt ist.
Meine Frage nun an Euch: Wie kommt man am besten auf dieses Ergebnis ohne händisch zählen, ohne VBA, ohne PQ?
Kann man das mit einer Pivot-Tabelle bestimmen? (Habe einiges erfolglos herumprobiert)
Wenn das nicht geht: Kann man das mit einer intelligenten Tabelle bestimmen? Mit Filtern, Sortieren und ähnliches?
Kann man das mit Formeln bestimmen? (Mit Formeln geht es sicher, ist aber kompliziert zu erstellen. Daher sind mir Lösungen mit Pivot und Tabellen lieber).
Liebe Grüße, Hannes

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: dann eben Kombination von Pivot und Formel ...

13.10.2020 08:28:50

Pivot
AW: dann eben Kombination von Pivot und Formel ...

17.10.2020 00:13:23

Pivot
AW: dazu siehe ...

17.10.2020 13:07:47

neopa
AW: dazu siehe ...

17.10.2020 16:47:39

HannesStein
AW: wenn das so ist, dann ...

17.10.2020 17:18:38

neopa

AW: dann eben Kombination von Pivot und Formel ...

13.10.2020 08:28:50

Pivot

Hallo Hans,
... nur mit Pivotauswertung allein geht es mE nicht. Aber z.B. so:
In Deiner Pivotauswertung deaktiviere die Gesamtauswertung für Zeile und Spalte.
Dann folgende Formel für bis zu 25 verschiedene Städte:

=SUMMENPRODUKT((MMULT((B5:Z99&gt0)+0;ZEILE(A2:INDEX(A:A;SPALTE(Z4)))^0)=ANZAHL2(B4:Z4))+0)
Die Formel könnte zwar etwas kürzer definiert werden, so ist sie aber mE einfacher nachvollziehbar.
Anstelle dieser Formel kannst Du natürlich auch in einer Hilfsspalte die Anzahl (nicht Summe!) der jeweiligen Autos in den verschiedenen Städten auswerten und diese Hilfsspalt dann mit ZÄHLENWENN() für die jeweilige Anzahl der Städte auswerten.
Gruß Werner
.. , - ...

AW: dann eben Kombination von Pivot und Formel ...

17.10.2020 00:13:23

Pivot

Hi Werner,
wohin soll diese Formel geschrieben werden?
Ist das eine Array-Formel?
leider komme ich damit nicht klar. Muss wohl irgend wie angepasst werden.
Habe einen Weg gefudnen: Die Originaltabelle woanders hinkopieren. Weil jetzt kommt keine zerstörungsfreie Methode. Duplikate entfernen für Tabelle "Stadt" und "Automarke", dann PT mit "Automarke" in den Zeilen und "Anzahl Städte" im Datenfeld.
Dennoch Danke. Grüsse aus Wien.

AW: dazu siehe ...

17.10.2020 13:07:47

neopa

Hallo Hans,
... für eine von mir willkürlich erweiterte Datenquellliste und deren nachfolgten aufgezeigten Pivotauswertung kann die Formel E1 in einer beliebigen Zelle außerhalb der Pivotauswertung stehen:

Arbeitsblatt mit dem Namen 'MFF_201013_1'
	A	B	C	D	E
1					3
2
3	Anzahl von Stadt	in
4	AutoTyp	Berlin	Frankfurt	Hamburg
5	Audi	2	1	1
6	Daihatsu		1
7	Fiat			1
8	Ford	1
9	Honda		1
10	Mercedes	1	2
11	Porsche	1	1	2
12	Renault	1	1	1
13	Saab		1
14	Volvo	1
15	VW		1
16

Zelle	Formel
E1	=SUMMENPRODUKT((MMULT((B5:Z99&gt0)+0;ZEILE(A2:INDEX(A:A;SPALTE(Z4)))^0)=ANZAHL2(B4:Z4))+0)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...

AW: dazu siehe ...

17.10.2020 16:47:39

HannesStein

Hallo, Werner,
ich glaube, da liegt ein Missverständnis vor.
Deine Formel berechnet die Anzahl der Marken, die in 3 Städten vorkommt. Das sind eben 3 Automarken.
Was ich gesucht habe, ist die Anzahl der Städte, in der eine Automarke vorkommt. Und das für jede einzelne Automarke berechnet. Also einfach mit "=Anzahl2" neben jeder Zeile der Pivot-Tabelle. Das ist es, was ich wissen wollte.
Auf das bin ich aber erst gekommen, nachdem ich Deine Formel filetiert, jedes Zeichen umgedreht und alles untersucht habe. Eine komplizierte Konstruktion, Deine Formel, alle Achtung, Werner. Vielen Dank nochmals.
gruss hans

AW: wenn das so ist, dann ...

17.10.2020 17:18:38

neopa

Hallo Hans,
... hätte wirklich al Formel einfach in meiner Beiepielliste =ANZAHL(B5:D5) und nach unten kopiert gereicht. Diese ergibt das gleiche Ergebnis wie ANZAHL2(B5:D5).
Anders war es halt aber auch eine schönere Aufgabe.
Gruß Werner
.. , - ...

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Daten vorbereiten: Stelle sicher, dass deine Daten in tabellarischer Form vorliegen, mit klaren Überschriften für jede Spalte (z. B. Tag, Stadt, Automarke).
Pivot-Tabelle erstellen:
- Markiere deine Daten.
- Gehe zu Einfügen > PivotTable.
- Wähle den Ort, an dem die Pivot-Tabelle erstellt werden soll (neues oder bestehendes Arbeitsblatt).
Felder anordnen:
- Ziehe das Feld "Automarke" in den Bereich Zeilen.
- Ziehe das Feld "Stadt" in den Bereich Werte.
- Stelle sicher, dass die Werte als Anzahl von Stadt angezeigt werden. Das kannst du tun, indem du das Feld im Wertebereich bearbeitest.
Anzahl der Städte zählen: Um die Anzahl der Städte zu zählen, in denen jede Automarke vorkommt, kannst du die Funktion ZÄHLENWENN verwenden:
- Erstelle eine neue Spalte neben der Pivot-Tabelle und benutze die Formel:
```
=ANZAHL2(B5:D5)
```
- Kopiere die Formel nach unten, um die Anzahl für jede Automarke zu berechnen.

Häufige Fehler und Lösungen

Fehler: Die Pivot-Tabelle zeigt die falsche Anzahl an Städten an.
- Lösung: Überprüfe, ob die Werte im Feld korrekt als Anzahl eingestellt sind. Manchmal können die Einstellungen auf Summe stehen, was zu Verwirrungen führt.
Fehler: Die Formel funktioniert nicht wie erwartet.
- Lösung: Stelle sicher, dass die Zellreferenzen in deiner Formel korrekt sind und die Datenzeilen in der Pivot-Tabelle berücksichtigt werden.

Alternative Methoden

Verwendung von Excel-Funktionen: Du kannst auch die Kombination von SUMMENPRODUKT und MMULT verwenden, um die Anzahl der Städte zu zählen, in denen jede Automarke erscheint:
```
=SUMMENPRODUKT((MMULT((B5:Z99>0)+0;ZEILE(A2:INDEX(A:A;SPALTE(Z4)))^0)=ANZAHL2(B4:Z4))+0)
```
Dies kann in einer Zelle außerhalb der Pivot-Tabelle eingegeben werden.
Intelligente Tabellen: Anstatt Pivot-Tabellen zu verwenden, kannst du auch intelligente Tabellen nutzen, um die Daten zu filtern und zu sortieren.

Praktische Beispiele

Angenommen, du hast die folgende Datentabelle:

Tag	Stadt	Automarke
1	Hamburg	Renault
2	Hamburg	Renault
3	Frankfurt	Audi
4	Berlin	Audi
5	Hamburg	VW

Die Pivot-Tabelle würde dann folgendes anzeigen:

Automarke	Anzahl von Stadt
Audi	2
Renault	1
VW	1

Mit der Formel =ANZAHL2(B5:D5) kannst du die Anzahl der Städte für jede Automarke ermitteln.

Tipps für Profis

Pivot-Tabelle anpassen: Nutze die Funktion PivotTable-Analyse, um die Layout-Optionen deiner Pivot-Tabelle zu optimieren, z. B. durch Deaktivieren der Gesamtauswertung für Zeilen und Spalten.
Daten filtern: Verwende die Filteroptionen in der Pivot-Tabelle, um nur bestimmte Automarken oder Städte zu betrachten.
Dynamische Bereiche: Stelle sicher, dass du dynamische Bereiche für deine Daten verwendest, damit die Pivot-Tabelle automatisch aktualisiert wird, wenn neue Daten hinzugefügt werden.

FAQ: Häufige Fragen

1. Wie kann ich die Anzahl der Zeilen in einer Pivot-Tabelle zählen? Du kannst die Anzahl der Zeilen zählen, indem du das Feld in den Wertebereich ziehst und sicherstellst, dass die Zusammenfassungsfunktion auf Anzahl eingestellt ist.

2. Gibt es eine Möglichkeit, die Pivot-Tabelle automatisch zu aktualisieren? Ja, du kannst die Pivot-Tabelle so einstellen, dass sie sich automatisch aktualisiert, wenn du die Arbeitsmappe öffnest oder die Datenquelle änderst. Gehe dazu zu PivotTable-Analyse und aktiviere die Option Daten aktualisieren beim Öffnen der Datei.

Beliebteste Forumthreads (12 Monate)