Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
24.04.2024 17:19:09
Formel mit Index-Funktion
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1352to1356
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1352to1356
1352to1356
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Schulnoten mit Gewichtung

Schulnoten mit Gewichtung
24.03.2014 16:00:09
Ralf
Hallo Forum,
ich erstelle gerade für die Klasse meines Sohnes einen Notenspiegel.
Dabei sind die Notengruppen gewichtet.
Ich habe eine Beispieldatei erstellt:
https://www.herber.de/bbs/user/89822.xlsx
Nun habe ich zwei Fragen:
1. Um die Datei variabel zu gestalten sollen in der Spalte C die Kennzeichnungen mit x in der Formel der beiden Zellen AT21 und AT22 berücksichtigt werden. Ich habe hier behelfsmäßig den Bezug einfach verkleinert. Der Formelbezug soll von Zeile 11 bis 19 gehen und nicht von 11 bis 16, wie bei mir.
2. Ich möchte den Notendurchschnitt der einzelnen Fächer immer im Blick haben, nicht erst wenn alle Notengruppen belegt sind. Wenn es Noten für Schulaufgaben und Stegreifaufgaben gibt, aber für Klasenaufgaben und mündl. Noten noch nicht, soll trotzdem ein Notendurchschnitt berechnet werden. In meiner derzeitigen Lösung müssen alle Gruppen belegt sein, damit der Notendurchschnitt mit der Gewichtung berechnet wird. Es werden auch nicht für jede Gruppe Noten vergeben. Löscht in meinem Beispiel in der Zelle M12 die 2 und Ihr seht was ich meine.
Kann mir bitte jemand bei den beiden offenen Punkten helfen?
Viele Grüße
Ralf

23
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Schulnoten mit Gewichtung
24.03.2014 16:40:01
Klaus
Hallo Ralf,
1:
in AT21 die Formel
=ZÄHLENWENNS(AT11:AT19;AS21;C11:C19;"x")
in AT22 die Formel
=ZÄHLENWENNS(AT11:AT19;AS22;C11:C19;"x")
2:
einfachst: ändere deine Mittelwert-Formeln in L S AC und AQ
von
=WENNFEHLER(MITTELWERT(E11:J11);"")
in
=WENNFEHLER(MITTELWERT(E11:J11);0)
mit der 0 kann, im Gegensatz zu "", gerechnet werden. Da in der Mittelwert-Formel jetzt "+0,333*0" vorkommt wenn es noch keine Klassenaufgaben gab, werden diese nur mit 0, also nicht, gewichtet.
3:
(das hast du nicht gefragt, aber ich antworte trotzdem)
Die lange WENN-Kette in AT11 kannst du so verkürzen:
=WAHL(AS11;"sehr gut";"gut";"befriedigend";"ausreichend";"mangelhaft";"ungenügend")
und runter kopieren
4:
statt verbundener Zellen: benutze "horizontal über Auswahl zentrieren".
Grüße,
Klaus M.vdT.

Anzeige
KorrekturAW: Schulnoten mit Gewichtung
24.03.2014 16:46:00
Klaus
in AR11
=WENN((L11+S11+AC11+AQ11)=0;"";(L11*$E$9+S11*$M$9+AC11*$T$9+AQ11*$AD$9)/(WENN(L11=0;0;$E$9) +WENN(S11=0;0;$M$9)+WENN(AC11=0;0;$T$9)+WENN(AQ11=0;0;$AD$9)))
und runter kopieren

AW: Schulnoten mit Gewichtung
24.03.2014 16:41:32
Jack_d
Das ist eigentlich einfacher wie du denkst.. du hast dir nur selbst ein Bein gestellt.
1. = 

WENNFEHLER(MITTELWERT(E12:J12);"")
die Crux ist hier das "" das ist nämlich nicht "0"
Also ich würde es an deiner stelle einfach weg lassen dann bleibt die Zelle auch leer also : 
=WENNFEHLER(MITTELWERT(E11:J11);)
oder 
=WENNFEHLER(MITTELWERT(E11:J11);0)
oder 
=WENNFEHLER(MITTELWERT(E11:J11);"0")

du hast dich also für die Variante entschieden welche nicht klappt :-D
2.würde ich die Formel in AR vereinfachen in 

=(L11*E9+S11*M9+AC11*T9+AQ11*AD9)/SUMME(E9:AQ9)
Grüße

Anzeige
Reingefallen, Jack!
24.03.2014 16:49:03
Klaus
Hallo Jack,
da bin ich grad auch voll drauf reingefallen. Mittelwert 0 bringt nichts, da ja immer über die volle Gewichtung von 2+0,33+1+0,5 gemittelt wird.
Probiers mal, eine 6 bei den Schulaufgaben und sonst keine Einträge ergibt eine Durchschnittsnote von 3,13!
Aufgrund des doofen Tabellenaufbaus (sorry Ralf) muss man sich hier wohl mit einer langen WENN-Krücke behelfen. Ansonsten währ das ein Fall fürs SUMMENPRODUKT.
Grüße,
Klaus M.vdT.

nicht ganz so einfach ...
24.03.2014 16:53:14
der
Hallo Ralf,
... in AR1 folgende Formel: 

=WENNFEHLER((L11*E$9+S11*M$9+AC11*T$9+AQ11*$AD$9)/($E$9*(K11>0)+$M$9*(R11>0)+T$9*(AB11>0) +$AD$9*(AP11>0));"")
und nach unten kopieren.
Zuvor natürlich die Einzeldurchschnitte mit =WENNFEHLER(MITTELWERT(E11:J11);0) etc. ermitteln, wie bereits Klaus und Jack schrieben.
Gruß Werner
.. , - ...

Anzeige
AW: nicht ganz so einfach ...
24.03.2014 20:39:43
Ralf
Hallo Klaus vdT.,
Hallo Jack_d,
Hallo Neopa,
zunächst mal vielen Dank für die konstruktiven Lösungen.
Folgende Fragen habe ich nun noch:
- "horizontal über Auswahl zentrieren" -
Wo kann ich diese Funktion finden? Das habe ich noch nie gehört.
Die Funktion "Wahl" kannte ich bis vorhin auch noch nicht. Die muss ich mir nochmal näher anschauen.
@Klaus: Warum ist der Tabellenaufbau "doof"? Ich habe die Noteninformation mit diesem Aufbau eigentlich nur abgebildet. Was ist ungünstig, was soll ich anders machen?
Eine Rückmeldung zu den offenen Punkten wäre nett.
Viele Grüße
Ralf

Anzeige
Über Auswahl zentrieren
25.03.2014 07:55:04
Klaus
Hallo Ralf,
über Auswahl zentrieren geht so: Inhalt in der Zelle ganz links, rechtsclick auf die selektierten Zellen, Zellen formatieren. In dem Fenster:
Userbild
Vorteile: sieht genauso aus, die Wert-Zelle ist eindeutiger ansprechbar (grad wenn später VBA ins Spiel kommt legt man sich sonst Steine in den Weg), Formeln über ganze Spalten sind einfacher einzugeben (da aus F:F nicht automatisch A:G oder sonstwas wird)
Nachteile: keine :-)
WAHL() ist die Funktion der Wahl (Wortspiel nicht beabsichtigt) um lange WENN-Ketten abzulösen.
Entschuldige bitte das Wort "doof", ist nicht böse gemeint. Ich nenne diese Art von Tabellenaufbau gerne "menschlich", zum Verständnis von Menschen für Menschen. Um eine gute Formellösung zu haben empfielt sich aber oft ein Aufbau, der eher für Maschinen geeignet ist - also weniger intuitiv zu verstehen und mehr logisch zu verstehen. Als erstes Beispiel: die Gewichtungen stehen in anderen Spalten als die dazu gehörigen Mittelwerte. Stände beides in der gleichen Spalte, könnte man warscheinlich die Formeln noch kürzen. So wie es ist müssen halt die WENN-Monster herhalten :-)
Mach dir keine Gedanken, ich bin mal wieder viel zu pedantisch. Für eine Notenübersicht ist das super.
Grüße,
Klaus M.vdT.

Anzeige
AW: Über Auswahl zentrieren
25.03.2014 07:59:35
Ralf
Hallo Klaus,
super Erklärung.
VIELEN DANK!!!
Mit "doof" ist schon ok. Ich spreche Probleme/Missstände auch lieber offen und ehrlich an. Das passt für mich.
Mit der Funktion Wahl, das finde ich irgendwie kurios. Die ist bis jetzt bei mir noch nie aufgetaucht. Das muss ich mir unbedingt ansehen.
Viele Grüße
Ralf

Gewichteter Mittelwert ....
25.03.2014 07:58:01
Klaus
... ich bin mir ziemlich sicher, dass all unsere Formelvorschläge falsch sind. Es wird ja ein Mittelwert der Fächer gezogen, und aus diesem Mittelwert wiederum ein Mittelwert genommen. Zwei mal Mittelwert ergibt immer ein falsches Ergebnis, oder?
Ich meine, man müsste jede Note einzeln mit ihrer Gewichtung multiplizieren (Summenprodukt) und dann durch die Anzahl Noten teilen, um die End-Note zu bekommen. Das Ergebnis weicht dann im Nachkommabereich ab.
Wer kann besser Mathe als ich, versteht den Wiki-Eintrag und kann das kontrollieren? Ich kanns nicht :-(
Grüße,
Klaus M.vdT.

Anzeige
AW: Gewichteter Mittelwert ....
25.03.2014 08:02:55
Ralf
Hallo Klaus,
ich bin der Meinung, dass ich den Wiki Eintrag in der Formel richtig umgesetzt habe. Was dann neu hinzugekommen ist, ist die Wenn Abfrage, das bei nicht vorhandenen Noten z. B. bei Klassen-/Gruppenarbeiten, die Gewichtung nicht berücksichtigt wird.
Hm, dann aktiviere ich das Thema wieder als offen.
Viele Grüße
Ralf

Klaus hat recht.
25.03.2014 09:01:10
Jack_d
daruber hab ich ehrlich gesagt gar nicht nachgedacht.
Zur erläuterung der Formel (Aus dem Wiki)
Xn beschreibt die Variable (hier die Note) von X1 bis Xn
Gn beschreibt das korespondierende Gewicht von G1 bis Gn
gefragt ist also die Summe von XiGi / Summe von Gi
Heisst soviel wie Alle Noten mal ihr Relativgewicht durch die Summe der Relativgewichte.
Sieht in Praxi dann so aus 
=(SUMMENPRODUKT(E11:J11;E9:J9)+SUMMENPRODUKT(M11:Q11;M9:Q9)+SUMMENPRODUKT(T11:AA11;T9:AA9) +SUMMENPRODUKT(AD11:AO11;AD9:AO9))/(SUMMEWENN(E11:J11;""&"";E9:J9)+SUMMEWENN(M11:Q11;""&"";M9:Q9) +SUMMEWENN(T11:AA11;""&"";T9:AA9)+SUMMEWENN(AD11:AO11;""&"";AD9:AO9))
Mir fiel jetzt grad keine Lösung ein wie ich das Summenprodukt verkürze
Zur Erläuterung des von Klaus identifizierten Fehlers. Hierbei werden die Anzahl der Noten vernachlässigt. Was tatsächlich zu einer Umverteilung führen kann.
Grüße

Anzeige
Hier die Formel nochmal schöner
25.03.2014 09:02:02
Jack_d 

=(SUMMENPRODUKT(E11:J11;E9:J9)+SUMMENPRODUKT(M11:Q11;M9:Q9)_
+SUMMENPRODUKT(T11:AA11;T9:AA9) +SUMMENPRODUKT(AD11:AO11;AD9:AO9))_
/(SUMMEWENN(E11:J11;""&"";E9:J9)+SUMMEWENN(M11:Q11;""&"";M9:Q9) _
+SUMMEWENN(T11:AA11;""&"";T9:AA9)+SUMMEWENN(AD11:AO11;""&"";AD9:AO9))

Ich sollte vielleicht erwähnen
25.03.2014 09:12:31
Jack_d
dass ich die Verbundenen Zellen "de-Mergt" hab und jeweils das Gewicht reingeschrieben hab.
Aber der geneigte Formelleser wird das mitbekommen haben :-D
Grüße

also wenn das so gewollt sein sollte, dass ...
25.03.2014 09:38:17
der
Hallo Jack,
... die Gewichtung je Zensur eingerechnet werden soll, dann reicht in AR11 aber einfach: 
=SUMMENPRODUKT(E11:AO11*E$9:AO$9)/SUMMENPRODUKT((E11:AO11>0)*E$9:AO$9)

und nach unten kopieren.
Ich war und bin aber der Meinung, dass immer nur die Teilzensurendurchschnitte in den Gesamtdurchschnitt eingehen sollen. Aber das kann nur Ralf endgültig klären.
Gruß Werner
.. , - ...

Anzeige
AW: also wenn das so gewollt sein sollte, dass ...
25.03.2014 09:41:02
Ralf
Hallo Werner,
ja, die Teilzensurendurchschnitte sollen mit entsprechender Gewichtung in die Berechnung der Gesamtzensur eingehen.
Viele Grüße
Ralf

dann wieder mein erster Formelvorschlag ...
25.03.2014 09:45:52
der
Hallo Ralf,
... mit der Präzisierung von heute 8:22.
Gruß Werner
.. , - ...

AW: also wenn das so gewollt sein sollte, dass ...
25.03.2014 09:41:20
Jack_d
Hallo Werner ..
das ist viel zu einfach :-D
Ist klar dass ich da drauf nicht gekommen bin :-)
Ansonsten gebe ich dir recht, dass es nur Ralf entgültig klären kann. Das hängt ja auch ein wenig von der "Aufgabenstellung" ab.
Add1. Mathematisch exakt ist die Wichtung JEDER Note.
Add2 aus Gründen der Vereinfachung ist die Wichtung des "Gruppen-Mittelwerts" denkbar
Grüße

Anzeige
AW: Hier die Formel nochmal schöner
26.03.2014 08:44:07
Ralf
Hallo Jack_D,
ich habe Deine Formel ausprobiert.
Das Ergebnis ist für mich nicht wirklich logisch.
Folgende Situation:
Ich habe bei den Stegreifaufgaben Zeile 11 mit Gewichtung 1,0 die beiden Noten 3 und 5 eingetragen. Also Durchschnitt/Mittelwert also unstrittig 3,0.
In AR11 habe ich nun diese beiden Formeln probiert:
=WENNFEHLER((L11*E$9+S11*M$9+AC11*T$9+AQ11*$AD$9)/($E$9*(K11>0)+$M$9*(R11>0)+T$9*(AB11>0) +$AD$9*(AP11>0));0)
Ergebnis: 3,0
=(SUMMENPRODUKT(E11:J11;E9:J9)+SUMMENPRODUKT(M11:Q11;M9:Q9)+SUMMENPRODUKT(T11:AA11;T9:AA9) +SUMMENPRODUKT(AD11:AO11;AD9:AO9))/(SUMMEWENN(E11:J11;"<>"&"";E9:J9)+SUMMEWENN(M11:Q11;"< >"&"";M9:Q9)+SUMMEWENN(T11:AA11;"<>"&"";T9:AA9)+SUMMEWENN(AD11:AO11;"<>"&"";AD9:AO9))
Ergebnis: 4,0
Ich habe jetzt die obere Formel mit Ergebnis 3,0 drinstehen und werde das auch erstmal so lassen.
Ich habe die anderen Hinweise von Werner und Neopa eingefügt.
Viele Grüße
Ralf

Bei mir auf der Baumschule ..
26.03.2014 10:22:13
Jack_d
Ich habe bei den Stegreifaufgaben Zeile 11 mit Gewichtung 1,0 die beiden Noten 3 und 5 eingetragen. Also Durchschnitt/Mittelwert also unstrittig 3,0.
Ich find das dass schon strittig ist . Aber das war in meiner Schule bestimmt nur anders.
Grüße

AW: Bei mir auf der Baumschule ..
26.03.2014 10:29:09
Ralf
Hallo Jack_D,
ich wollte nur mal schauen, ob Du auch aufpasst. ;-)
Natürlich mein Fehler. Schreibfehler.
Neuer Versuch:
Ich habe bei den Stegreifaufgaben Zeile 11 mit Gewichtung 1,0 die beiden Noten 3 und 5 eingetragen. Durchschnitt/Mittelwert also unstrittig 4,0.
In AR11 habe ich nun diese beiden Formeln probiert:
=WENNFEHLER((L11*E$9+S11*M$9+AC11*T$9+AQ11*$AD$9)/($E$9*(K11>0)+$M$9*(R11>0)+T$9*(AB11>0) +$AD$9*(AP11>0));0)
Ergebnis: 4,0
=(SUMMENPRODUKT(E11:J11;E9:J9)+SUMMENPRODUKT(M11:Q11;M9:Q9)+SUMMENPRODUKT(T11:AA11;T9:AA9) +SUMMENPRODUKT(AD11:AO11;AD9:AO9))/(SUMMEWENN(E11:J11;"<>"&"";E9:J9)+SUMMEWENN(M11:Q11;"< >"&"";M9:Q9)+SUMMEWENN(T11:AA11;"<>"&"";T9:AA9)+SUMMEWENN(AD11:AO11;"<>"&"";AD9:AO9))
Ergebnis: 3,0
Jetzt sollte meine Formulierung/Beschreibung passen.
Entschuldigung.
Viele Grüße

AW: Bei mir auf der Baumschule ..
26.03.2014 10:37:46
Jack_d
Hallo Ralf
kein Problem.
Ich hab es nur kurz mal ausprobiert. Und konnte den Fehler mit meiner Formel reproduzieren.
Mir fehlt nur grad bissl die Zeit auf die Fehlersuche zu gehen.
Aner du hast ja jetzt eine adäquate Lösung
Grüße

weitere Anmerkungen dazu ...
25.03.2014 08:22:17
der
Hallo Ralf,
... auch wenn es sicher eine günstigere Tabellenstruktur gibt, in diesem Fall würde ich es an Deiner Stelle bei der vorhandenen belassen, weil Du ja nun dafür bereits einen prinzipiellen Lösungsansatz hast.
Aber Klaus hat Recht, wenn er Dich darauf verweist, dass verbundene Zellen sehr oft zu komplizierteren Formelauswertungen führen (kann) als ohne. Allerdings können auch Zellen, die "horizontal über Auswahl zentriert sind" gerade auch nicht so erfahrene Excelnutzer manchmal ganz schön "narren".
Die Funktion WAHL() ist eine sehr hilfreiche Funktion, mit der man gerade oft unübersichtliche _ WENN()-Formel-Konstrukte ersetzen kann. In Deinem Fall würde ich diese jedoch in AT11 so: 

=WAHL(AS11+1;"";"sehr gut";"gut";"befriedigend";"ausreichend";"mangelhaft";"ungenügend")
schreiben. Dies setzt jedoch voraus, dass Du zuvor in AS11 die Formel 
 =RUNDEN(AR11;)
einschreibst und in AR11 (bin momentan durchaus noch der Meinung, dass die Gewichtung damit richtig ermittelt wird, lass mich allerdings gern eines besseren belehren).
=WENNFEHLER((L11*E$9+S11*M$9+AC11*T$9+AQ11*$AD$9)/($E$9*(K11>0)+$M$9*(R11>0)+T$9*(AB11>0) +$AD$9*(AP11>0));0)
(also zuletzt: ...; 0 anstelle ..;"") . Die 0,0 bzw. 0 Ergebnisse kannst Du durch eine benutzerdefiniertes Zahlenformat =0,0;; bzw. 0;; einfach ausblenden. Die gilt auch für die anderen Spaltenergebnisse für Anzahl und Teil-Durchschnitte. In diesen sollte die Formel z.B. in L11 =WENNFEHLER(MITTELWERT(E11:J11) ;0) lauten, in S11, AC11 und AO11 analog.
Alle Formeln nach unten kopieren. Nun solltest Du nur noch in AU11 die Formel 

=WENN(AT11="";"";AS11)
nach unten kopieren, damit Du keine grüne Ampel für keine Zensuren erhältst.
Gruß Werner
.. , - ...

AW: weitere Anmerkungen dazu ...
25.03.2014 08:29:51
Ralf
Hallo werner,
danke für die ausführliche Antwort.
Ich werde Deine Anregungen heute Abend ausprobieren/einarbeiten.
Viele Grüße
Ralf

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige