SVERWEIS automatische Anpassung

[Michael999]

Hallo ,
 
ich wäre für eur Unterstützung sehr dankbar.
Es geht um folgende Anpassung:
Ich habe eine Excel Tabelle mit 20 Schüler. Die Tabelle enthält zwei Spalten: Schülername und Anzahl nicht erledigte Hausaufgaben. Diese Berechnung wird monatlich in der gleichen Tabelle gemacht.
Beispiel:
Monat Januar 
Schülername   Anzahl nicht erledigte Aufgaben
Schüler 1                  1
Schüler 2                 6
Schüler 3                 3
Monat Februar
Schüler 1                 2
Schüler 2                 1
Schüler 3                 5
Mit einer anderen Excel datei (über sverweis) mache ich eine Berechnungen, indem ich von allen Schüler Anzahl nicht erledigte Hausaufgaben zusammenrechne.
Monat Januar                               Monat Februar
Schüler 1                 1                                2
Schüler 2                 6                                1
Schüler 3                 3                                5
Summe                   10                               8
 
Hier habe ich das Problem, dass ich den Zellberiech in der sverweis – Funktion für jeden Monat immer anpassen muss, Weil in der ersten Excel Datei Beispielsweise die Schüler  für den Monat Januar sich von Zeile (3 bis 5) befinden. Für Monat Februar befinden sich  die in der Zeilennummer (7 bis 9). Deswegen muss der Zellbereich in sverweis immer je nach Monat angepasst werden.
Am liebsten würde ich aber eine dritte Excel-Liste haben. In dieser Liste würde ich gerne 3 Spalten haben (Monat, Zeile von, Zeile bis) haben. Durch das Befüllen der Liste (z.B: (Feburar, 7, 9)) soll die sverweis in der zweiten Excel-Datei automatisch angepasst werden.

Vielen Dank für eure Vorschläge

[Peter]

Hallo,

ich würde die Datei ein klein wenig anders aufbauen

Tabelle1

	A	B	C
1	Monat	Name	Aufgabe
2	Januar	Schüler 1	1
3	Januar	Schüler 2	6
4	Januar	Schüler 3	3
5	Februar	Schüler 1	2
6	Februar	Schüler 2	1
7	Februar	Schüler 3	5

und dann kannst du das ganz bequem mit einer Pivottabelle auswerten:

Tabelle1

	F	G	H	I
3	Summe von Aufgabe	Spaltenbeschriftungen
4	Zeilenbeschriftungen	Januar	Februar	Gesamtergebnis
5	Schüler 1	1	2	3
6	Schüler 2	6	1	7
7	Schüler 3	3	5	8
8	Gesamtergebnis	10	8	18

Und die Spalte A könnte man mit einer Bedingten Formatierung noch etwas aufhübschen, so dass die Monatsnamen nur angezeigt werden wenn der Monat wechselt - das ist in der Beispielsdatei deutlicher zu sehen wie hier in der Vorschau.

[Michael999]

Vielen Dank Peter. Ich darf leider der Aufbau der Datei nicht ändern. Ich muss die Ergänzung über eine dritte Datei wie beschrieben machen. Ich habe gelesen, dass man dafür "indirekte Funktion" nutzen kann. Ich kann aber leider damit nicht anfangen und hoffe, dass ich hier eine Unterstüzung bekommen werden.

Viele Grüße
Michael

[Rabe]

Hi,

Vielen Dank Peter. Ich darf leider der Aufbau der Datei nicht ändern. Ich muss die Ergänzung über eine dritte Datei wie beschrieben machen. Ich habe gelesen, dass man dafür "indirekte Funktion" nutzen kann. Ich kann aber leider damit nicht anfangen und hoffe, dass ich hier eine Unterstüzung bekommen werden.

Du könntest ein Hilfsblatt einfügen und dort die Daten, so wie vorgeschlagen, aggregieren und danach aus diesem Hilfsblatt die Auswertung mit Pivot erstellen.

Zur Begründung für Deinen Auftraggeber:
Gleichartige Daten(sätze) gehören in EIN und nur EIN Tabellenblatt.
Auswertung dann per Filter iVm Teilergebnis() oder Aggregat().
Oder per Spezialfilter oder Pivottabelle.
Oder per VBA-Datenmaske.
Oder per Formular-Tabellenblatt.
Stichworte: Dropdown, SVERWEIS(), Summewenn(s) und die diversen Ableitungen.

Schau mal hier zum Tehma (Daten-)Listen.

[BoskoBiati]

Hallo,

unabhängig von allen berechtigten Einwänden, verstehe ich nicht, warum man hier den SVERWEIS ändern muß????

Arbeitsblatt mit dem Namen 'Tabelle3'
	A	B
1
2
3
4	Schüler1	5
5	Schüler2	2
6	Schüler3	3
7	Schüler4	4

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Arbeitsblatt mit dem Namen 'Tabelle2'
	A	B
11	Schüler1	8
12	Schüler2	1
13	Schüler3	2
14	Schüler4	4

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Arbeitsblatt mit dem Namen 'Tabelle1'
	M	N	O
7	Schüler1	5	8
8	Schüler2	2	1
9	Schüler3	3	2
10	Schüler4	4	4

Zelle	Formel
N7	=SVERWEIS($M7;Tabelle3!A:B;2;0)
O7	=SVERWEIS($M7;Tabelle2!A:B;2;0)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Dem SVERWEIS ist es egal, in welcher Zeile die Namen stehen, solange sie nicht doppelt vorhanden sind!