Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Summenprodukt mit Bedingungen
#1
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


Angehängte Dateien
.xlsx   Notenblatt test.xlsx (Größe: 36,36 KB / Downloads: 5)
Antwortento top
#2
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.
Viele Grüße
Klaus-Dieter
Der Erfolg hat viele Väter, 
der Misserfolg ist ein Waisenkind
Richard Cobden
Antwortento top
#3
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
Antwortento top
#4
Hi Björn,

erweitere deine Formel:
Code:
SUMMENPRODUKT(D11:D16;E11:E16)/SUMMENPRODUKT((D11:D16)*(E11:E16<>"n.f.")))
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.zitate.net
[-] Folgende(r) 1 Benutzer sagt Danke an WillWissen für diesen Beitrag:
  • Bjoerndal
Antwortento top
#5
Hallo Will,

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

statt D10 als Teiler?

Bjoerndal
Antwortento top
#6
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.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.zitate.net
[-] Folgende(r) 1 Benutzer sagt Danke an WillWissen für diesen Beitrag:
  • Bjoerndal
Antwortento top
#7
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
Antwortento top
#8
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
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.zitate.net
[-] Folgende(r) 1 Benutzer sagt Danke an WillWissen für diesen Beitrag:
  • Bjoerndal
Antwortento top
#9
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
Antwortento top
#10
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.
Gruß Günter
Misserfolg ist eine Chance, es beim nächsten Mal besser zu machen.
(Henry Ford) www.zitate.net
[-] Folgende(r) 1 Benutzer sagt Danke an WillWissen für diesen Beitrag:
  • Bjoerndal
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste