Office: (Office 2013) Text in einer Zelle suchen und je nach Kriterium einen Wert addieren

Hi Experten

Ich habe in einer Arbeitsmappe Auswertungen von Tests von Schülern
Da steht zb in A1 der Name des Schülers und in B1 eine Antwort.

Die Antwort in B1 setzt sich aus zB MultipleChoice Antworten zusammen
Beispiel: Frage lautet : Welche Programme gehören zum MS Office Paket? (Antwortmöglichkeiten: Word, Excel, Opera, PowerPoint, Editor)
Antwort Schüler 1 in B1: Word, Excel, Opera
Schüler 2 in B2: Word, Excel
Schüler 3 in B3: Opera

Nun soll folgende Punktevergabe gemacht werden.
Richtig: +0,5
Falsch: -0,5
Nicht angekreuzt: 0

Das bedeutet also Schüler 1 hätte 0,5 Punkte, Schüler 2 hätte 1 Punkt und Schüler 3 hätte -0,5 Punkte.

Nach einem einfachen Text suchen wäre leicht - das hätte ich so gelöst:
=WENN(ISTZAHL(FINDEN("Word";B1));0,5;"")

Nun soll Excel aber nach 5 Worten suchen und vergleichen, ob diese in der Antwort vorkommen, wenn ja, dann die richtigen mit +0,5 bewerten, die falschen mit -0,5 und dann das Ergebnis für diese Frage zusammenzählen.

Ich bin da vollkommen ratlos und bitte um Eure Hilfe
Vielen Dank

 

hallo, kannst du uns bitte (d)eine (beispiel)datei vorstellen..?

 

Kann ich schon, aber ich befürchte, dann wird's noch komlizierter zu verstehen :-)
Naja versuchen wir es mal:
Bitte schaut euch erstmal einzeln folgende 3 Dateien an:

1.) Die Den Anhang Student_1.pdf betrachten Datei zeigt den Test eines einzelnen Schülers mit den Testfragen und wie er geantwortet hat. Nur zum besseren Verständnis - die Datei wird nicht zur Auswertung benötigt.

2.) Die Datei Den Anhang Class.xlsx betrachten ist die vom Onlinetest generierte Auswertung aller Schüler. Die hat immer die gleiche Struktur und ist die Basisdatei für die Punktevergabe.
Da stehen z.B. in der Spalte J die Antworten aller Schüler (jede Zeile ein Schüler) auf die von mir im Beitrag erwähnte Office-Frage (rot: irgendwas falsch; grün alles richtig)

3.) Die Datei Den Anhang Gesamtergebnis CG.xlsx betrachten ist die von mir erstellte und bisher manuell auszufüllende Punktedatei.
Ich hab mal 3 Testschüler angelegt und im Tabellenblatt HW fiktiv Werte eingegeben, damit man sieht wie es dann nach dem HW Test aussehen kann. (Student_1 ist aber schon mit den korrekten Punkten eingegeben)

So, und nun wäre es natürlich endgeil, wenn man es schaffen könnte, dass Excel im Tabellenblatt SW Zelle C6 die Frage aus dem Antwortblatt "Class.xlsx" herausfiltert und die Punkte selbst errechnet. Natürlich könnte ich das Tabellenblatt aus Class.xlsx auch ganz einfach in ein neues Tabellenblatt in Gesamtergebni.xlsx kopieren, dann braucht man keine Verweise auf eine 2. Arbeitsmappe.

Den richtigen Schüler mit seinen Fragen zu finden, hätte ich mit einem SVERWEIS gemacht.

Wer das alles versteht, ist echt super :-)

Danke Leute!!!!