Excel: Zellen mit Text nicht berechnen + Gewichtung verändern

[jtkrk]

Hallo,

ich bin Lehrer und lege mir gerade eine Tabelle zur Notengebung an. Leider sind meine Excel-Kenntnisse begrenzt.

Mein Plan ist es, eine Gesamtnote aus mehreren Einzelnoten zu berechnen. Die Einzelnoten haben jeweils eine unterschiedliche Gewichtung. Die Gewichtung trage ich in extra Felder ein. Im Anhang findet ihr eine Datei mit einem Beispiel.

Note1 = "4" (Gewicht1 = "70")
Note2 = "3" (Gewicht1 = "20")
Note3 = "1" (Gewicht2 = "10")

=SUMME(Note1*Gewicht1+Note2*Gewicht2+Note3*Gewicht3)/100

Mein Problem ist, dass manchmal Schüler nicht anwesend sind und als Note dann ein "FE" für "fehlt entschuldigt" eingetragen wird. Dadurch entstehen zwei Probleme.

1. Die Summe ist fehlerhaft "###".
2. Die Gewichtung stimmt nicht mehr. Denn wenn z. B. Note 2 wegfällt, dann sind ja nur noch 80% an Noten vorhanden, die nun aber 100% darstellen.

Kann mir jemand sagen, wie man das beheben kann? Bitte für Idioten erklären! ;-)

Danke im Voraus!

 

[G-Red]

ist die Gewichtung bei FE = 0 ?

 

[supastar]

FE kannst du ersetzen, z. B. einfach mit der "Ersetzen"-Funktion.
Wie du das dann aber berechnen willst, musst du schon selbst wissen.

Möglich wäre natürlich, dass du das durch die Gewichtung teilst, dann hast du halt das Problem, dass man mit nur 30% (bei Gewichtung 1 gefehlt), ein recht schlechtes Ergebnis erhältst.

Der Rest geht sicher eleganter z. B. mit der SVERWEIS-Funktion.
https://support.office.com/de-de/article/sverweis-funktion-0bbc8083-26fe-4963-8ab8-93a18ad188a1

 

[slrzo]

Google ist dein Freund, hier ganz gut erläutert auf den ersten Blick: https://www.ulrich-rapp.de/stoff/pc/tabkal/notenliste.htm

Feld F7 in deinem Beispiel:

=SUMMENPRODUKT(C7:C9;D7:D9)/SUMME(D7:D9)

der Eintrag mit FE ist natürlich sehr unglücklich, da du kompliziert abprüfen müsstest ob in den Feldern eine Zahl steht. Falls deine Noten immer gleich sind also 3 Noten mit Gewichtung XY und du die Note bei einem Fehlen leer lässt, so müsste man die Formel noch bisschen anpassen:

=SUMMENPRODUKT(C7:C9;D7:D9)/SUMMEWENN(C7:C9;">0";D7:D9)

Somit könntest du statt FE eine 0 eintragen und den Fehlgrund würde ich wenn wichtig in ein separates Feld daneben setzen.

 

[G-Red]

Ich habe es folgendermaßen gelöst.

Die Noten mit der jeweiligen Gewichtung sind feste Größen und zusätzlich fügst du noch eine Spalte hinzu, wo du die Anwesenheit einträgst. Dann kann man das wie auf dem Bild berechnen

und hier noch mal die Formel zum kopieren

=WENN(SUMMENPRODUKT((E7:E9<>"FE")*(D7:D9)) >0;SUMMENPRODUKT((E7:E9<>"FE")*(C7:C9)*(D7:D9))/SUMMENPRODUKT((E7:E9<>"FE")*(D7:D9));"keine bewertung")

 

[slrzo]

Note 0,7 ist gut
Du musst natürlich auch berücksichtigen, dass bei einem Fehlen nicht mehr durch 100 geteilt werden darf sondern nur durch den verbleibenden Rest

 

[G-Red]

Note 0,7 ist gut
Du musst natürlich auch berücksichtigen, dass bei einem Fehlen nicht mehr durch 100 geteilt werden darf sondern nur durch den verbleibenden Rest

Danke für den Hinweis, habs korrigiert

 

[jtkrk]

Danke für eure Anreize. Ich konnte mit meinen bescheidenen Kenntnissen jetzt nicht alles nachvollziehen. Ich habe daher mal die eigentliche Tabelle hochgeladen.

Es handelt sich um 3 Noten, die jeweils einen Durchschnitt bilden.

• Bei der Mündlichen Noten teile ich die Noten durch die Anzahl der Zahlenwerte (mittels ANZAHL-Funktion). Das "FE" ist hier also nicht störend.
• Bei der Fachspezifischen Note und Klassenarbeiten tritt das oben beschriebene Problem auf, dass bei einem entschuldigten Fehlen (FE) a) die Zelle nicht mehr berücksichtigt werden soll und b) die Gewichtung entsprechend geändert werden muss. Denn wenn 30% eines Durchschnitts (100%) fehlen, darf man nicht mehr durch 100 teilen, sondern nur noch durch 70, um den Durchschnitt zu berechnen.

Dasselbe Problem tritt dann auch bei Gesamtnote (ganz unten) auf.

Nun zu euren Vorschlägen:

Die Noten mit der jeweiligen Gewichtung sind feste Größen und zusätzlich fügst du noch eine Spalte hinzu, wo du die Anwesenheit einträgst. ]

• @G-Red: Danke für deine Mühe und Hilfe! Für eine separate Zelle für "FE" pro Note habe ich aber leider keinen Platz.
• Man könnte statt Wert "FE" das Feld leer lassen - ja. Nachteil ist, dass man dann nicht mehr auf den ersten Blick sieht, warum jemand gefehlt hat.
• Eigentlich soll zusätzlich auch der Wert "F" als (unentschuldigtes Fehlen) berücksichtigt werden und automatisch als "6" berücksichtigt werden. Das wäre quasi das Non-plus-ultra.

Helfen hier WENN-Funktionen weiter?

Ich bin deshalb an einer möglichst umfangreichen Lösung interessiert, weil die Tabelle für alle meine Fächer und meine gesamte Lehrerzeit Anwendung finden wird.

Über weitere Hilfe würde ich mich sehr freuen.

Falls ich hier auf etwas nicht eingegangen bin, dann bitte nicht böse sein. Habe es dann wahrscheinlich falsch oder gar nicht verstanden.

Beste Grüße!

 

[Scientist]

Die Aussage "kein Platz" ist etwas Seltsam ...
Deine Excel-Datei ist praktisch leer.

Man kann die Rechnungen auf ein anderes Blatt auslagern und auf die Ergebnisse und Zwischenergebnisse entsprechen verweisen.

Es ist fuer Anfaenger sinnvoll eher mit vielen einfachen Formeln und Zwischenschritten zu arbeiten, als mit kurzen, weniger intuitiven oder komplexen, Formeln.
Denn wenn du in einem Jahr noch mal ranmusst, siehst du nur noch Fragezeichen.

Deswegen:
Sieht vielleicht nicht Elegant aus (interessiert Excel aber auch nicht), aber mach deine Rechnung so, wie du sie auch auf dem Papier machen wuerdest.

Mit der Zeit, wenn du dich mit Excel weiter beschaeftigt haben solltest, kannst du sie vereinfachen.

 

[G-Red]

@jtkrk

Kannst du eventuell anhand vom Notenspiegel eines konkreten Schüllers, eine genaue Beispielrechnung zeigen? Die Zwischenergebnisse bitte auch irgendwo am Rand notieren, damit man sieht wie du auf welche Zahl gekommen bist.

 

[jtkrk]

Klar! Habe mich gerade echt reingekniet. Die Notenberechnung funktioniert nun. Ihr werdet mich allerdings für die Formeln umbringen. Habe alles mit WENN-Ketten gelöst. Das geht sicher alles schöner, aber ich weiß nicht wie. Anbei die Tabelle.

Die mündlichen Noten sind kein Problem.

Verwendete WENN-Ketten bei Fachnoten und Klassenarbeiten:

• Wenn "FE" -> dann Wert "0" und Reduzierung der Gewichtung
• Wenn "F" -> dann Wert "6"

Einziges Problem ist, wenn es z. B. nur eine Klassenarbeit gab und ein Schüler diese nicht mitgeschrieben hat. Die WENN-Kette reduziert den Divisor 100 (100%) dann um 100, wodurch dieser 0 ist. Da man nicht durch "0" teilen kann, gibt es einen Fehler. Dieser müsste irgendwie umgangen werden. Bei Klassenarbeit müsste im Durchschnitt dann der Wert "0" stehen (nicht nur die Zahl!).

Die entsprechenden Fehler/Felder habe ich gelb markiert.

 

[G-Red]

Hi, wenn das Ergebnis, abgesehen von der Fehlemeldung der division durch Null, dich zufriedenstellt und du selbst es nachvollziehen kannst, dann kannst du diese Fehlermeldung wie folgt elliminieren:

Hier der Artickel zu der WENNFEHLER Funktion in Excel

 

[jtkrk]

Hier der Artickel zu der WENNFEHLER Funktion in Excel

Hi!

Danke für die Verlinkung! Ich hatte das schon ausprobiert. Leider bekomme ich einen Fehler in der Gesamtnote, auch wenn ich bei der Klassenarbeit den Fehler durch eine "0" ersetze.

Weiß jemand, woran das liegt?

Danke im Voraus!

 

[G-Red]

hier die Formel für die Gesamtnote mit dem WENNFEHLER Konstrukt

=WENNFEHLER(SUMME(N33*$AJ$7+N40*$AJ$8+N44*$AJ$9)/(100-(WENN(N33=0;$AJ$7;0)+WENN(N40=0;$AJ$8;0)+WENN(N44=0;$AJ$9;0)));0)

Übrigens, du kannst statt der Null auch einen textuellen Wert eingeben, sowas wie das Kürzel NB für Nicht bewertet.
Du musst also die 0 mit dem Ausdruck "NB" ersetzen MIT DEN HOCHKOMMATA.

 

[jtkrk]

Danke dir! Das Problem ist jedoch ein anderes. Wenn ich mit WENNFEHLER die Note der Klassenarbeit behebe und durch eine "0" ersetze, sollte eigentliche eine Gesamtnote zustande kommen. Leider funktioniert das aber nicht.

Die Gesamtnote wird trotzdem als Fehler angezeigt, obwohl nun für alle Bereiche Noten vorliegen (im Falle der Klassenarbeit eben eine "0"). Das verstehe ich nicht.

 

[Janush]

Du hast da ein paar Klammern falsch gesetzt und somit kommt einfach nur Null raus. Denn sobald D37 zu WAHR validiert wird, werden die beiden nachfolgenden Arbeiten einfach ignoriert.

=(IF(D37="FE",0,(IF(D37="F",6,D37)*$AK$8)+(IF(D38="FE",0,(IF(D38="F",6,D38)*$AL$8))+(IF(D39="FE",0,(IF(D39="F",6,D39)*$AM$8))))))/(100-(IF(D37="FE",$AK$8,0)+IF(D38="FE",$AL$8,0)+IF(D39="FE",$AM$8,0)))

wird zu

=(IF(D37="FE",0,IF(D37="F",6,D37)*$AK$8)+(IF(D38="FE",0,IF(D38="F",6,D38)*$AL$8))+(IF(D39="FE",0,IF(D39="F",6,D39)*$AM$8)))/(100-(IF(D37="FE",$AK$8,0)+IF(D38="FE",$AL$8,0)+IF(D39="FE",$AM$8,0)))

Der Rest sieht ganz gut aus, denke ich :-)

Oh ich merke gerade, dass ich ja ein English sprachiges System benutze...lol....du musst das natürlich bei dir entsprechend anpassen. Und WennFehler kannst du auch weglassen, da du ja der einzige bist der dieses Tool benutzt, oder?

 

[jtkrk]

Du hast da ein paar Klammern falsch gesetzt und somit kommt einfach nur Null raus. Denn sobald D37 zu WAHR validiert wird, werden die beiden nachfolgenden Arbeiten einfach ignoriert.

VIELEN LIEBEN DANK! Nun funktioniert alles. Echt mega nett von dir! Anbei die fertige Tabelle.

Drei Fragen noch:

1. Kann man die Rahmen und Linien der Zellen irgendwie sperren, sodass beim Einfügen/Verschieben, etc. nicht immer die Rahmen kaputt gehen?
2. Kann man die Formeln der Zellen irgendwie schützen?
3. Gibt es eine Möglichkeit, bei der Tabelle "Gewichtung" eine Prüffunktion einzubauen, die überprüft, ob die Zellen AJ6 bis AJ8 sowie AK7 bis AM7 und AK8 bis AM8 zusammen 100% ergeben und ggf. einen Fehler ausgibt oder die Zellen rot einfärbt, wenn das mal nicht der Fall ist?

Danke im Voraus!

 

[G-Red]

Zu 1 und 2
Man kann das gesamte Blatt schützen oder bestimmte Bereiche, wie hier beschrieben.
Zu 3.
Such mal nach dem Begriff "bedingte Formatierung"

 

[jtkrk]

Danke für deine Hilfe!

Ich habe mich via Google an der bedingten Formatierung versucht, bekomme es aber einfach nicht hin. Folgende Zellen sollen rot eingefärbt werden, wenn die Summe der dort eingegebenen Zahlen ungleich 100 ist:

AJ6 bis AJ8
AK7 bis AM7
AK8 bis AM8

Kann mir jemand erklären, was ich genau machen muss?

Danke im Voraus!

 

[steve1da]

Hola,

für den ersten Bereich als Formel in der bedingten Formatierung:
=summe(AJ$6:AJ$8)<>100
Für die beiden anderen Bereiche:
=summe($AK7:$AM7)<>100

Gruß,
steve1da