Clever-Excel-Forum

Hallo zusammen,

ich habe eine Tabelle mit Unterrichtsstunden
A: Datum
B: Name des Schülers

und möchte eine Statistik erstellen mit einer Tabelle
D: Name des Schülers
E: 2017
F: 2018
G: 2019
....
In den Zellen sollen die Anzahl der Unterrichtsstunden pro Schüler und pro Jahr stehen.

Mit ZÄHLENWENNS komme ich nicht weiter, weil ich im Spaltenkopf von E, F, G, ... nur das Jahr stehen habe, in Spalte A aber das Datum (bzw. habe ich keine Methode gefunden, um innerhalb von ZÄHLENWENNS aus dem Wert der Spalte A das Jahr zu machen und es dann mit dem Spaltenkopf von E, F, G, ... zu vergleichen).

Bisher habe ich mir damit geholfen, eine zusätzliche "Statistik-Spalte"
C: =JAHR(A)&B
zu machen und dann einfach in der Statistiktabelle mit ZÄHLENWENN(C:C;$D2&E$1) die Werte einzufügen. Das sollte aber doch auch ohne diese "Krücke" mit der Spalte C funktionieren, oder?

Hat jemand Tipps oder eine Lösung für mich? Ich bedanke mich aufs herzlichste für jede Anregung!
Stefan

Hallo Stefan,

mit etwas mehr Einsicht in den Aufbau deiner Datei sollte das kein Problem sein. Möglicherweise kann man auch mit Summenprodukt zu einem Ergebnis kommen. =Summenprodukt((Jahr(A2:A100)=E$1) … usw.

Hallo Klaus-Dieter,

danke für die schnelle Reaktion. Ich poste hier mal einen Screenshot von einer Beispieltabelle. In Anlage die Beispieltabelle.

Das Ziel ist der Verzicht auf die "Statistik-Spalte" C und das direkte Berechnen der Anzahl Stunden pro Schüler in den Zellen E2:G4 aus den Spalten A und B.

Vielen Dank!

Hallo Susi,

statt eines Bildschirmfotos wäre eine Beispieldatei besser gewesen, dann brauche ich die nicht nachbauen.

Hallo Klaus-Dieter, ich habe nicht gesehen, dass ich Attachments anfügen kann. Im obigen Post geändert/aktualisiert. Danke! Stefan

Hallo Stefan,

so sollte das gehen:

Tabelle1

	A	B	C	D	E	F	G
1	Datum	Name des Schülers		Name des Schülers	2017	2018	2019
2	05.04.2017	Maier, Susi		Maier, Susi	1	1	1
3	06.08.2017	Müller, Hans		Müller, Hans	1	2	0
4	23.11.2017	Schmitt, Rudi		Schmitt, Rudi	1	0	2
5	05.02.2018	Maier, Susi
6	10.04.2018	Müller, Hans
7	25.06.2018	Müller, Hans
8	06.01.2019	Schmitt, Rudi
9	04.02.2019	Maier, Susi
10	13.03.2019	Schmitt, Rudi
11	…	…	…

Formeln der Tabelle

Zelle Formel E2 =SUMMENPRODUKT((JAHR($A$2:$A$10)=E$1)*($B$2:$B$10=$D2)) F2 =SUMMENPRODUKT((JAHR($A$2:$A$10)=F$1)*($B$2:$B$10=$D2)) G2 =SUMMENPRODUKT((JAHR($A$2:$A$10)=G$1)*($B$2:$B$10=$D2)) E3 =SUMMENPRODUKT((JAHR($A$2:$A$10)=E$1)*($B$2:$B$10=$D3)) F3 =SUMMENPRODUKT((JAHR($A$2:$A$10)=F$1)*($B$2:$B$10=$D3)) G3 =SUMMENPRODUKT((JAHR($A$2:$A$10)=G$1)*($B$2:$B$10=$D3)) E4 =SUMMENPRODUKT((JAHR($A$2:$A$10)=E$1)*($B$2:$B$10=$D4)) F4 =SUMMENPRODUKT((JAHR($A$2:$A$10)=F$1)*($B$2:$B$10=$D4)) G4 =SUMMENPRODUKT((JAHR($A$2:$A$10)=G$1)*($B$2:$B$10=$D4))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Hallo Klaus-Dieter,

das ist genial. Herzlichen Dank für deine spontane und schnelle Hilfe!

Viele Grüße,
Stefan

Hallo Stefan,

du hast zwar eine Formellösung. Mit ein paar wenigen Mausklicks und völlig ohne Formeln und VBA: Pivottabelle

Dazu ziehst du "Datum" in Spalten, "Schüler" in Zeilen und "Datum" in Werte. Es werden dir von Excel im Feld Spalten automatisch noch Quartale und Jahre angelegt. Hier entfernst du einfach Quartale und Datum.

Gesamtergebnisse für Zeilen und Spalten kannst du unter Entwurf (siehst du in der Menüleiste, wenn der Cursor irgendwo in der PT steht) deaktivieren.

So siehts nach ein wenig Kosmetik aus:

[attachment=23464]

Ein Bild deshalb, weil das Tabellentool eine PT nicht richtig abbildet.

Hallo, das geht auch ohne SUMMENPRODUKT() - mit SUMME(). Auch alternativ zu PivotTable ("schöner") mit Power Query.