Clever-Excel-Forum

Normale Version: Summenprodukt mit Bedingungen
Du siehst gerade eine vereinfachte Darstellung unserer Inhalte. Normale Ansicht mit richtiger Formatierung.
Seiten: 1 2
Hallo,

folgende Situation. Ich möchte in der Tabelle ("Schüler") den gewichteten Durchschnitt verschiedener Teilfächer berechnen (Gewichtung=Stundenfaktor, in den Zellen D11:D16; Teilnoten in den Zellen E11:E16). Solange in allen Teilfächern eine Note vergeben wurde funktioniert das ganze mit Summenprodukt geteilt durch die Summe der Gewichtungen ("Schüler!D10"). Die Formel lautet dann:

SUMMENPRODUKT(D11:D16;E11:E16)/D10

Wenn aber wie in der Tabelle "Schüler" eine Teilnote nicht feststellbar war und deshalb statt einer Note die Bemerkung "n.f." eingetragen wird bzw. die Zelle leer bleibt ("Schüler!E11") müsste ich statt durch die aufsummierten Stundenfaktoren aller Teilfächer ("Schüler!D10") nur durch die benoteten Teilfächer teilen. In der Tabelle also durch 10 statt durch 12. Statt in E11 kann das auch in einer der anderen Zellen (E11:E16) vorkommen.
Die Originaldatei umfasst ca. 50 Schüler-Notenblätter, so dass ein Anpassen der Formel im Bedarfsfall zwar möglich, aber nicht die eleganteste Lösung wäre. Habt Ihr eine Idee, wie ich das einfacher lösen kann?

Ich hänge die Datei mit der Beispieltabelle mit an den Post an. Kurze Erklärung zur Datei. Im Master werden die Namen der Fächer, die alle Schüler besuchen, und die Stundenfaktoren eingetragen. Bei Gewichtungen werden die Faktoren für die Gewichtung des 1. und des 2. Halbjahres eingetragen, diese werden in verschiedenen Formeln benötigt. Die Tabelle "Wahlpflichtfächer der Klassen" sorgt dafür, dass in den Notenblättern der Schüler die richtigen Wahlpflichtfächer eingetragen werden.

Für Lösungsvorschläge wäre ich dankbar Smile

Viele Grüße und schöne Pfingsten

Bjoerndal
Hallo,

eine der Grundregeln für Excel besagt, das es innerhalb der einzelnen Spalten nur gleichartige Daten geben soll. An deinem Problem siehst du, warum das so ist.
Hallo Klaus-Dieter,

muss ich aus Deiner Antwort schließen dass es keine elegante Lösung dafür gibt? Denn in unserer Schule kann es schon mal vorkommen dass Schüler ein gesamtes Halbjahr entschuldigt fehlen und man deshalb keine Note feststellen kann... Könnte das ganze funktionieren wenn man die Zelle leer lässt? dann wären es jedenfalls keine ungleichartigen Daten... oder?

Wenn ich in die Zellen für den Stundenfaktor der Teilfächer eine Bedingung einfüge, so dass der Stundenfaktor aus dem Master nur übernommen wird, wenn in der Notenzelle ein Wert zwischen 1 und 6 steht, dann müsste ich das Problem doch lösen können, oder?

=wenn(E11 zwischen 1 und 6;Stundenfaktor aus dem Master;"")

Aber Danke für die schnelle Antwort!

Bjoerndal
Hi Björn,

erweitere deine Formel:
Code:
SUMMENPRODUKT(D11:D16;E11:E16)/SUMMENPRODUKT((D11:D16)*(E11:E16<>"n.f.")))
Hallo Will,

den Teil :
SUMMENPRODUKT((D11:D16)*(E11:E16<>"n.f.")

statt D10 als Teiler?

Bjoerndal
Ja, das ist richtig. Teste einfach mal die Formel alleine, indem du mal das n.f. drin lässt und einmal statt des n.f. eine Zahl einträgst.
Hallo,

dann kommt als Ausgabe #WERT!

Aber danke erst mal. Ich versuch für die einzelnen Gewichtungen ne Übernahmebedingung zu basteln (siehe post #3)

Bjoerndal
Hi,

dann hast du irgendwo einen Knoten reingebracht. Hier der Divisor 10 wegen des Eintrags n.f.:

Arbeitsblatt mit dem Namen 'Schüler'
DEF
10123,2510
112n.f.
1222,00
1322,10
1433,60
15
1634,50

ZelleFormel
F10=SUMMENPRODUKT((D11:D16)*(E11:E16<>"n.f."))
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

und hier der Divisor 12 durch Auswechseln von n.f. mit einer Zahl:

Arbeitsblatt mit dem Namen 'Schüler'
DEF
10123,0412
1122,00
1222,00
1322,10
1433,60
15
1634,50

ZelleFormel
F10=SUMMENPRODUKT((D11:D16)*(E11:E16<>"n.f."))
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg
hi,

also müsste die ganze Formel in E10

=WENN(ODER(D10="";UND(E11="";E12="";E13="";E14="";E15="";E16=""));"";SUMMENPRODUKT(D11:D16;E11:E16)/SUMMENPRODUKT((D11:D16)*(E11:E16<>"n.f.")))

lauten...

ich habs ausprobiert... gleiche Ausgabe...

Kannst Du das in der Datei mal bitte so einbasteln dass es klappt und die anhängen?

Vielen Dank

Bjoerndal

Hallo,

ich hab das ganze jetzt so gelöst, dass ich in die Zelle D11 folgende Formel eingegeben hab:

=WENN(Master!D11="";"";WENN(ODER(E11<1;E11>6);0;Master!D11))

Die werde ich auch in den Zellen D12:D16 einbauen. Dadurch erhalte ich zum einen die richtigen Ergebnisse und kann zum anderen verhindern dass Werte außerhalb des Notenspektrums in die Berechnung mit einbezogen werden.

Ohne den Gedankenaustausch mit Euch wäre ich da nicht drauf gekommen!

Vielen Dank für die Rückmeldungen!

Euch allen schöne Pfingsten

Bjoerndal
Hi Björn,

es sollte umgekehrt sein: zeig uns, wo du was wie eingetragen hast. Dass kein #WERT herauskommt, siehst du am Tabellenausschnitt:

Arbeitsblatt mit dem Namen 'Schüler'
DE
10122,85
112n.f.
122
1322,10
1433,60
15
1634,50

ZelleFormel
D10=WENN(ANZAHL(D11:D16)=0;"";SUMME(D11:D16))
E10=WENN(ODER(D10="";UND(ANZAHL(D11:D16)=0));"";SUMMENPRODUKT(D11:D16;E11:E16)/SUMMENPRODUKT((D11:D16)*(E11:E16<>"n.f.")))
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Deine beiden Formeln in D10 und E10 habe ich verkürzt.
Seiten: 1 2