Microsoft Excel

Herbers Excel/VBA-Archiv

Daten zählen mit Pivot

Betrifft: Daten zählen mit Pivot von: HannesStein
Geschrieben am: 12.10.2020 20:05:19

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

Betrifft: AW: dann eben Kombination von Pivot und Formel ...
von: neopa C
Geschrieben am: 13.10.2020 08:28:50

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>0)+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
.. , - ...

Betrifft: AW: dann eben Kombination von Pivot und Formel ...
von: HannesStein
Geschrieben am: 17.10.2020 00:13:23

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.

Betrifft: AW: dazu siehe ...
von: neopa C
Geschrieben am: 17.10.2020 13:07:47

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'
 ABCDE
1    3
2     
3Anzahl von Stadtin   
4AutoTypBerlinFrankfurtHamburg 
5Audi211 
6Daihatsu 1  
7Fiat  1 
8Ford1   
9Honda 1  
10Mercedes12  
11Porsche112 
12Renault111 
13Saab 1  
14Volvo1   
15VW 1  
16     

ZelleFormel
E1=SUMMENPRODUKT((MMULT((B5:Z99>0)+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
.. , - ...

Betrifft: AW: dazu siehe ...
von: HannesStein
Geschrieben am: 17.10.2020 16:47:39

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

Betrifft: AW: wenn das so ist, dann ...
von: neopa C
Geschrieben am: 17.10.2020 17:18:38

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