Formel für SVerweis

[2cool]

Hallo zusammen, habe folgendes Problem:

Ich habe eine Tabelle mit "Artikeln" und "Werten".
Dann habe ich eine 2. Tabelle erstellt, mit "Artikel" und "Anzahl Werten". Nun möchte hätte ich gern ein Formel die aus der ersten Tabelle die Zeile mit dem Artikel raussucht und dann Aufgrund der Zelle Anzahlwerte die Summe der entsprechenden Zellen aus Tabelle 1 zurück gibt.

 

[mj10]

Hallo

Ich gehe davon aus, dass Du pro Wert in Sheet 1 "nebenan" eine zusätzliche Spalte für den Verweis erstellen möchtest (da bin ich nicht sicher). Dafür brauchen wir zuerst in Sheet 2 einen eindeutigen Schlüssel, diesen generieren wir mittels einer zusätzlichen Spalte und der Funktion VERKETTEN (Bild1). Dann ebenfalls in Sheet 1 mittels VERKETTEN in der SVERWEIS-Formel diesen Schlüssel generieren (Bild2).

 

[2cool]

Hallo Mj10
Glaube Du hast mich nicht ganz richtig verstanden. Wenn in Tabell2 neben dem Artikel eine 2 steht, soll er die Summen aus Wert1 + Wert2 bringen. Steht da eine 4, dann soll er die Summe von Wert1 + Wert2 + Wert3 + Wert4 bringen. Etc.

Danke und Gruss

 

[-ian]

Rückfrage:

Wieviele Spalten und Zeilen brauchst Du etwa in Tabelle 1? Im Prinzip hab ich das Problem gelöst-
es gibt aber etwas Arbeit für Dich. Darum Die Frage nach der Grösse.
-ian

 

[Officer]

Hallo 2cool

In den Spalten Wert1 -Wert8 stehen pro Zeile immer die gleichen Zahlen drin. Ist das Zufall oder ist das immer so? Dann kannst Du ja einfach "Wert1" * "Anzahl Werte" rechnen!?!

 

[mj10]

Ich glaube, die immer gleichen Werte dienen als Beispiel. So müsste es gehen:

Sub SummeSverweis()
zeilea = 2
spaltea = 1
sheeta = "Tabelle2"
sheetb = "Tabelle1"
Sheets(sheeta).Select
    Do While Cells(zeilea, spaltea) <> ""
        kriterium = Cells(zeilea, spaltea + 1)
        anzahl = Cells(zeilea, spaltea)
        summe = 0
        zeileb = 2
        spalteb = 1
        Sheets(sheetb).Select
            Do While Cells(zeileb, spalteb) <> ""
                If Cells(zeileb, spalteb) = kriterium Then
                    Do While spalteb <= anzahl And Cells(zeileb, spalteb) <> ""
                        summe = summe + Cells(zeileb, spalteb + 1)
                        spalteb = spalteb + 1
                    Loop
                Else
                End If
                zeileb = zeileb + 1
            Loop
            Sheets(sheeta).Select
            Cells(zeilea, spaltea + 2) = summe
        zeilea = zeilea + 1
    Loop
End Sub

Irgend ein Makro erstellen, dieses dann bearbeiten und gesamten Inhalt durch obigen Code ersetzen. Dann Makro "SummeVerweis" ausführen.

Achtung: Oben im Code diesen Teil anpassen,

sheeta = "Tabelle2"
sheetb = "Tabelle1"

so dass Deine "Endergebnistabelle" (die mit der Summe) bei sheeta steht und die "Datentabelle" bei sheetb. Ich gehe davon aus, dass Du immer eine Titelzeile hast, ebenso wird die Summe in sheeta immer in die dritte Spalte geschrieben.

Ansonsten hier:

Cells(zeilea, spaltea + 2) = summe

"Spaltea + 2" einfach das "2" durch eine andere Zahl ersetzen (3 = Spalte 4 etc).

UND: Sobald in einem der beiden Sheets eine Leerzeile vorkommt, funktioniert es nicht.

 

[2cool]

Wieviele Spalten und Zeilen brauchst Du etwa in Tabelle 1? Im Prinzip hab ich das Problem gelöst-
es gibt aber etwas Arbeit für Dich. Darum Die Frage nach der Grösse.
-ian

Es sind 25 Spalten und etwa 200 Zeilen.
2cool

 

[mj10]

Versuch das Makro, habs getestet. Es funktioniert. Du brauchst natürlich 2 verschiedene "Arbeitsblätter" (Sheets).

 

[2cool]

Hallo mj10
Das Makro funktioniert perfekt. Leider bin ich kein VBA-Crack.
Die echten Tabelle1 hat den Schlüssel in Spalte B und den ersten Wert in Spalte H. Könntest Du mir das Makro entsprechend anpassen?
Danke und Gruss 2cool

 

[Thomas Ramel]

Grüezi 2cool

Das Ganze kannst Du mit eine einzigen Formel erreichen.

Bei der Konstellation der Daten wie sie in deinem ersten Screenshot gezeigt sind kannst Du in C10 die folgende Formel verwenden und nach unten kopieren:

=SUMME(BEREICH.VERSCHIEBEN($A$2;VERGLEICH(B10;$A$3:$A$6;0);1;1;A10))

 

[2cool]

Hallo zusammen
die Formel von Thomas ist genau das was ich gesucht habe.
Vielen Dank an alle.
Gruss 2cool