Formel runterziehen, Bezug um 2 Zeilen verschieben

[nerdprincess]

Hallo zusammen,

ich verusche gerade Daten auszuwerten, die etwas ungünstig erfasst sind. Die Daten sind in einzelne Reihen (1, 2 ...) unterteil und dann steht jeweils ein Buchstabe und eine dazugehörige Zahl. (Tabelle1)

     A   B   D   C
1   2    2   4   2

    B   A    B   C
2  3    2   5    1

Ich bauche die Reihenunterteilung nicht und möchte einfach die jeweiligen zugehörigen Zahlen zu den Buchstaben sehen, also es soll so aussehen (Tabelle2)
    A    B   C    D
    2    2    2   4
    2    3    1     
          5

Um das zu erreichen habe ich folgende noch simple Wenn-Formel erstellt.
{=WENN(Tabelle1!B4:E4=Tabelle2!$A$1;Zeiterfassung!B5:E5;"")}

Nun würde ich diese gerne einfach runterziehen, aber da die Daten ja immer über zwei Zeile gehen möchte ich als nächsten Bereich
nicht Tabelle1!B5:E5, sondern Tabelle1!B6:E6, dann als nächste B8:E8, B10:E10 usw. Natürlich auch immer um 2 verschoben im Dann-Bereich der Formel.

Wäre super, wenn jemand weiß ob bzw. wie so etwas geht! :)

Und ja es ist mir nun schon aufgefallen, dass in dem Beispiel bei B (das in Reihe 2 zweimal vorkommt, Excel nur den ersten Wert nimmt und aufschreibt). Hat jemand eine Idee wie man die Formel aufbereiten müsste, damit er immer die Summe abbildet, von allen zugehörigen Werten, die dazugehören? So würde ja nur die 3 stehen und da wo jetzt die 5 ist würde dann der Wert der nächsten Reihe stehen
Ansonsten würde ich das vorher manuell anpassen, und direkt auf 8 für B aufsummieren.

Danke
Jasmin

[neopa]

Hallo Jasmin

momentan ist (für mich) nicht ganz eindeutig, wo und wie Deine Daten in Tabelle1 angeordnet sind. Am besten Du stellst Deine Beispielmappe hier ein.

[BoskoBiati]

Hallo,

außerdem ist diese simple WENN-Formel schlichtweg falsch und nicht funktionierend!

[nerdprincess]

Hallo,

danke für die schnellen Antworten.
Habe jetzt eine Beispieltabelle hochgeladen (habe die Funktion vorher gar nicht gesehen )

Und die simple WENN-Formel hat nur für den ersten Wert funktioniert, also habe ich sie mal durch Index + Vergleich ersetzt.

Mein Problem ist aber das gleiche, das ich gerne die Formel automatisch ziehen können möchte und sich der Bezug um zwei Zeilen verschiebt. Da ja immer so eine leere Zeile zwischen steht.

Gruß
Jasmin

[neopa]

Hallo Jasmin

in Deiner Tabelle1 Zeile 6 steht "B" zweimal. Ist das korrekt ? Wenn die Buchstaben in einer Zeile mehrmals vorkommen können wird es komplizierter. Ohne dies berücksichtigen zu müssen, hätte ich momentan eine reine Formellösung. Meine Frage ist also, ist das für Dich schon ausreichend?

[BoskoBiati]

Hi,

ich denke das ist ein Fehler in der Tabelle. Ich würde das so angehen:

Code:

=WENNFEHLER(INDEX(Tabelle1!$B$5:$F$11;(ZEILE(A1)-1)*2+1;VERGLEICH(Tabelle2!A$3;INDEX(Tabelle1!$B$4:$F$10;(ZEILE(A1)-1)*2+1;);0));"")

[nerdprincess]

Hallo Werner,

das kommt leider öfter vor, das einen Buchstaben mehrfach gibt. Mein Datensatz ist aber noch relativ überschaubar, sodass ich so etwas im Zweifel auch vorher manuell korrigieren könnte. Wenn es mit der Formel gleich berücksichtigt werden könnte, das Werte mit dem gleichen Buchstaben aufsummiert werden, wäre das natürlich sensationell. ::)

Ich freue mich auch schon riesig über eine Lösung, um erstmal die jeweilige zweite Zeile herauszubekommen.

Danke und Grüße
Jasmin

[BoskoBiati]

Hallo,

dann würde ich das so machen:

Code:

=WENNFEHLER(SUMMENPRODUKT((INDEX(Tabelle1!$B$4:$F$10;(ZEILE(A1)-1)*2+1;)=A$3)*(INDEX(Tabelle1!$B$5:$F$11;(ZEILE(A1)-1)*2+1;)));"")

[neopa]

Hallo Jasmin,

das erleichtert natürlich die Auswertung enorm. Formel nach rechts kopieren:

	A	B	C	D	E
3	A	B	C	D	E
4	7	18	7	17	9

Formeln der Tabelle

Zelle Formel A4 {=SUMME(WENN(Tabelle1!$B$4:$F$14=A$3;Tabelle1!$B$5:$F$15))}

Enthält Matrixformel: Umrandende { } nicht miteingeben, sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[nerdprincess]

Hallo Werner,
hallo Edgar,

danke für eure Hilfe, das ist klasse! ::)

Vielen, vielen Dank, so funktioniert es prima.

Schöne Grüße
Jasmin