Clever-Excel-Forum

Hallo,


ich habe ein Problem mit der Aufbereitung statistischer Daten. Da ich in meinem Statistik-Programm nicht weiterkomme, will ich Excel zu Hilfe nehmen und bin sicher, dass ihr mir hoch kompetent zur Seite stehen könnt.

Ich habe ein Datenblatt mit verschiedenen Variablen. Also ganz klassisch: Jede Spalte eine Variable, jede Zeile ein Fall. Jeweils die erste Spalte enthält eine Code-Variable. Aber nur die erste Zeile eines jeden neuen Codes enthält auch Daten in den anderen Spalten. Diese Werte muss ich in alle Zeilen kopieren, die die gleiche Code-Nummer haben.

Ich suche also eine Formel, die in etwa das tut:
IF (Wert in A1 bis A2071) equal (Wert in A1-1 bis A2071-1) 
THEN (Wert C1 bis C2071) = (C1-1 bis C2071-1) [und das ganze bis Spalte M]

IF (Wert in A1 bis A2071) unequal (Wert in A1-1 bis A2071-1) 
THEN (Wert = Wert in letzter Zeile, in der Wert in A gleich war)

Natürlich ist mir vollkommen bewusst, dass da so nichts draus wird
Also: Wenn es der gleiche Klassencode wie in der Zeile zuvor ist, dann sollen alle Werte kopiert werden. Wenn es nicht der gleiche Wert ist, sollen die Werte des neuen Codes genommen werden.
Ich erhalte dann also ganz viele gleiche Zeilen. Das ist aber notwendig, um die Daten dann zurück in mein Statistik-Programm zu kopieren. Rein inhaltlich gesprochen handelt es sich um Schüler- und aggregierte Lehrerdaten, die zusammengefügt werden. Jede Zeile steht also für einen Schüler. Andere Werte unterscheiden sich also. Da aber mehrere Schüler den gleichen Lehrer haben, sind diese Werte identisch.

Und um sicherzugehen noch einmal ein Beispiel:

      A      B      C      D      E      F      ....
1    15     2      2      4       5      9
2    15    [2]    [2]   [4]     [5]    [9]
...
23   5      5      6      8       9       4
24   7      2      4      8       6       7
25   7     [2]    [4]    [8]    [6]     [7]

In A steht also die Variable, die verglichen wird (Codevariable). In eckigen Klammern stehen in dem Fall die Werte, die eben automatisch ersetzt werden soll. In Zeile 2 die Werte von Zeile 1; in Zeile 23 und 24 passiert nichts; in Zeile 25 werden die Werte aus Zeile 24 ersetzt (bezieht sich jeweils nur auf Spalten A bis F, da A ja zum Abgleich dient und nicht verändert wird).

Wie gesagt, das mag erst einmal sinnlos klingen, ist aber für die Analysen, die ich damit machen will, unabdinglich. Diese Geschichte beschäftigt mich seit Jahren immer mal wieder. Bisher habe ich per Hand kopiert - langsam sind es dafür aber zu viele Zeilen (über 2.000) und das ganze muss einfacher gehen. ich bin mir ganz sicher, dass Excel das kann - und ich wäre unendlich dankbar, wenn ihr mir sagen könntet, wie.
Da ich ganz, ganz selten mit Excel-Makros arbeite (das letzte Mal vor 4 Jahren), nehme ich gern auch eine echt idiotensichere Antwort, wenn möglich

Danke!!!

Saskia

Hallo Saskia,

ist deine Liste nach Codenr sortiert, sodass sie untereinader stehen? Wenn ja, kann man es ganz einfach auf einem Hilfsblatt lösen.

Auf neuem Tabellenblatt  - Zeile1 hat Überschriften
In A2: =WENN(Tabelle1!A2="";"";Tabelle1!A2) - nach unten ziehen
In B2: =WENN(A2="";"";WENN(Tabelle1!$A2=Tabelle1!$A1;Tabelle1!B1;Tabelle1!B2)) - nach rechts und nach unten ziehen

Alternative (wenn nicht untereinander, sondern wild verstreut, geht natürlich auch bei untereinander)
Auf 2. Tabblatt B2 wieder nach rechts und unten

Tabelle3

	A	B	C	D	E
1	code	Hilf1	Hilf2	var1	var2
2	x1	1	x1;1	11	12
3	x2	1	x2;1	21	22
4	x3	1	x3;1	32	32
5	x4	1	x4;1	41	42
6	x3	2	x3;2

Formeln der Tabelle

Zelle Formel B2 =WENN(A2="";"";ZÄHLENWENN($A$2:A2;A2)) C2 =A2&";"&B2

Tabelle4

	A	B	C
1	code	var1	var2
2	x1	11	12
3	x2	21	22
4	x3	32	32
5	x4	41	42
6	x3	32	32

Formeln der Tabelle

Zelle Formel A2 =WENN(Tabelle3!A2="";"";Tabelle3!A2) B2 =WENN(A2="";"";WENN(Tabelle3!$B2=1;Tabelle3!D2;SVERWEIS($A2&";1";Tabelle3!$C$1:$E$8;SPALTE(B$1);0)))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Hallo Andreas,

hab vielen Dank für deine schnelle Antwort!
Ich bin leider erst heute dazu gekommen mich wieder damit zu beschäftigen und bitte daher, meine späte Antwort zu entschuldigen.

Ich habe es wie von dir vorgeschlagen gemacht - neue Tabelle, erste Formel in A2 und dann nach unten gezogen, zweite Formel in B2 und nach rechts und unten gezogen. Die Daten liegen sortiert nach der Schlüsselvariablen vor, ja.
Jeweils die ersten Zeilen jeder neuen Schlüsselvariable werden tadellos aus Tabelle 1 übernommen. Auch die jeweils zweite Zeile wird korrekt ausgefüllt. Danach funktioniert es aber nicht mehr. Das heißt, ich habe nun pro Schlüsselvariable eine statt zwei Zeilen, aber in allen anderen Zeilen wird eine 0 übernommen

Es ist ganz bestimmt der richtige Weg - kannst du mir nochmal helfen, um es ganz hinzubekommen?

Ich hänge mal zwei Bilder an, einmal die Tabelle mit den "Original"-Daten und einmal die Tabelle 2 mit der Hilfsdatei inkl. der Formel in B4 (der ersten Zelle, in der die Kopie nicht funktioniert).

Vielen Dank!

Saskia

Hallo Sascha,

stimmt, da habe ich zu schnell geschossen.
Bei Variante 1 muss es in B2 heißen
=WENN($A2="";"";WENN(Tabelle1!$A2=Tabelle1!$A1;B1;Tabelle1!B2)) und nicht Tabelle1!B1, d.h. er nicmmt in der Hilfstabelle solange den Wert der in der Hilfstabelle darüber steht, bis ein neuer Code besginnt

Tabelle1

	A	B	C
1	code	var1	var2
2	x1	11	12
3	x1
4	x2	21	22
5	x2
6	x2
7	x3	31	32

Tabelle2

	A	B	C
2	x1	11	12
3	x1	11	12
4	x2	21	22
5	x2	21	22
6	x2	21	22
7	x3	31	32

Formeln der Tabelle

Zelle Formel A2 =WENN(Tabelle1!A2="";"";Tabelle1!A2) B2 =WENN($A2="";"";WENN(Tabelle1!$A2=Tabelle1!$A1;B1;Tabelle1!B2)) C2 =WENN($A2="";"";WENN(Tabelle1!$A2=Tabelle1!$A1;C1;Tabelle1!C2))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4


Variante2 Tabblatt 3/4 hätte übrigens funktioniert. ;)
=WENN($A2="";"";WENN(Tabelle3!$B2=1;Tabelle3!D2;SVERWEIS($A2&";1";Tabelle3!$C$1:$E$8;SPALTE(B$1);0)))

Hallo Andreas,

jetzt funktioniert es. Das ist großartig.

Ich bin dir wahnsinnig dankbar dafür. Das spart mir ungemein viel Zeit und erleichtert meine Analysen (und damit meine Publikationen) sehr. Du hast mir damit auf dem Weg zu meiner Promotion geholfen

Danke!!

Viele Grüße
Saskia

Hallo Sascha,

in was für einem Fach arbitest du und welche Auswertungssoftware benutzt du eigentlich? In SPSS gäbe es dafür nämlich die Lag-Fkt.

Hallo Andreas,

ich arbeite in der Psychologie, Schnittstelle zur Bildungswissenschaft. Normalerweise arbeite ich in der Tat mit SPSS (und in MPlus, wofür ich den Datensatz aber in SPSS aufbereite). 
Mit den Menü-Funktionen habe ich eben noch nie eine Lösung finden können, die alle Zeilen ausfüllt und in der Syntax fehlte mir immer der entsprechende Hinweis. Das ist natürlich toll, wenn du den auch gleich noch hast, vielleicht kann ich mir dann den Umweg über Excel auch ganz sparen
Die Lag-Funktion werde ich mir mal ansehen und es dann damit versuchen. 

Leider haben alle Kollegen, die ich sonst treffe, das Problem nicht, weil sie die Art von Analysen nicht machen, oder arbeiten mit anderen Programmen (sie sind R-affin, was ich leider nicht bin )...

Viele Grüße
Saskia

Hallo Saskia,
wie kam ich eigentlich auf Sascha - sorry

Ich habe dir eine Mail und eine private Nachricht geschrieben, wo du Hinweise zu SPSS findest. Können die Diskussion ja dort fortsetzen, wenn du magst. MPlus kenne ich nicht.