EXCEL - Bereich eingrenzen

N_O_K_I_A

Lt. Commander
Dabei seit
Jan. 2007
Beiträge
1.838
Hi,

ich habe folgendes Problem in Excel:

Ich habe eine sehr große Tabelle.
Ungefähr wie folgt aufgebaut:

1. Spalte sind die angesprochenen Berieche
2. Spalte die Daten, die ich mit dem sverweis suche
3. Spalte die Werte, die ich zurückgeben möchte

a x1 1
a x2 2
a x3 3
b y1 6
b y2 7
b y3 8
c z1 4
c z2 5
c x1 6


Wenn ich nun per sverweis nach der mittleren Spalte suche komme ich zu folgendem Problem:

Ich suche nach "x1" und er gibt mir natürlich eine "1" aus. Wenn ich nun aber zu "x1" die 6 haben möchte, klappt dies ja so nicht.
Die Daten aus der 1. Spalte habe ich aber immer gegeben.
Somit kann ich den Bereich "eingrenzen".
Die Logik ist relativ einfach, nur wie ich das am besten in Excel realisiere, da überlege ich noch...

EDIT:

Der Sverweis soll dann natürlich nur in diesem Bereich suchen
 
Zuletzt bearbeitet:

s3bbi

Cadet 3rd Year
Dabei seit
Feb. 2011
Beiträge
39
Wenn ich dich richtig verstehe versuchst du für einen Wert mehrere Ergebnisse zurück zu geben?
Das funktioniert mit Sverweis nicht, bzw. nicht so einfach.

Wenn du so etwas machen möchtest würde ich Access benutzen, die Abfragen in Access können das.

Oder du musst dir alternativ mal die Ergebnisse zu "Sverweis mehrere Ergebnisse" in Google anschauen.

Wie diese hier z.B. http://www.office-loesung.de/ftopic314496_0_0_asc.php
 

N_O_K_I_A

Lt. Commander
Ersteller dieses Themas
Dabei seit
Jan. 2007
Beiträge
1.838
Es würde ja auch reichen, wenn es einen Befehl gibt, der in einer großen Tabelle einen Bereich eingrenzt in dem der sverweis nur sucht. Wenn der sverweis nur in diesem Bereich sucht, ist sichergestellt, dass er nur EINEN Wert zurückgeben muss.
 

N_O_K_I_A

Lt. Commander
Ersteller dieses Themas
Dabei seit
Jan. 2007
Beiträge
1.838
Hi,

ich glaube ich habe es schon hinbekommen.

Ich laufe jetzt die Spalte solange durch, bis ich auf den ersten des gesuchten Wertes treffe.
Die Adresse dieser Zelle speicher ich ab.
Dann diesen gespeicherten Wert als neuen Startwert und dann solange bis der erste Wert kommt, der nicht mehr dem gesuchten ist und diese Adresse dann "-1".
So habe ich genau den Bereich, in dem nur der Wert vorkommt.

Ich hoffe, es ist verständlich :D



Jetzt tritt allerdings ein weiteres Problem auf..


Nun habe ich genau die Grenzen der Matrix, die ich nur durchsuchen will.

Jetzt wollte ich den sverweis bauen:

a = gesuchter Wert
b1 = Anfang Matrix
c1 = Ende Matrix

=sverweis(a, b:c, 1, Falsch)

Problem dabei ist, b1 und c1 müssen aus Zellen ausgelesen werden.
Wenn ich nun schreibe =sverweis(a, b1:c1, 1,Falsch) dann nimmt er die beiden Zellen B1 und C1 als Matrix und nicht deren Inhalt.
In Excel soll das ja mittel Indirekt() gehen.
Wie aber in VBA.
Wenn ich mit einem Makro aufzeichne, bekomme ich ja nur:

Range("G17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-15]C[1],C[-5]:C[-4],2,FALSE)"

Wie muss ich hier einsetzen, damit er aus den jeweiligen Zellen den Inhalt als Matrix nimmt?

EDIT:

Wenn die Matrix in G6 steht z.B: A4:C6 dann hab ich es so versucht:

Range("G17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-15]C[1],Indirect(G6),2,FALSE)"

Allerdings kommt dann immer: #NAME?
Und in der Funktionsleiste steht folgendes: =SVERWEIS(H2;INDIREKT('G6');2;FALSCH)
Die beiden ' ' stören.
Wenn man diese entfernt, läuft es.

ALSO: Wie kann ich machen, dass der Sverweis in VBA seine Parameter nicht fest hat, sondern aus Variablen ausliest?
 
Zuletzt bearbeitet:

Mirakel

Ensign
Dabei seit
Dez. 2010
Beiträge
218
Wenn du schon in VBA suchst und es in einen SVERWEIS schreiben willst, ginge es z.B. so (Beispiel anhand s3bbi's Tabelle):

Code:
Sub test()
With Range("a:a")
    Set c = .Find("b")
    If Not c Is Nothing Then
        firstAddress = c.Row
        'Debug.Print firstAddress
        Do
            'c.Value = 5
            'Debug.Print c.Row
            lastAddress = c.Row
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Row <> firstAddress
        
    End If
    Debug.Print firstAddress
    Debug.Print lastAddress
Range("g5").FormulaLocal = "=sverweis(h2;b" & firstAddress & ":c" & lastAddress & ";2;FALSCH)"

End With
End Sub
Sicherlich nicht elegant, aber es funktioniert.


Habe gerade auch noch eine Lösung ohne VBA zusammengebaut:
Suchkriterium 1: z.B. c in Zelle "E7"
Suchkriterium 2: z.B. a2 in Zelle "F7"

Formel: =SVERWEIS(F7;INDIREKT("B"&VERGLEICH(1;1/(A1:A100=E7);-1)):INDIREKT("C"&VERGLEICH(1;1/(A1:A100=E7);1));2)

Hinweis: Dies ist eine Matrix-Formel. Diese musst du nicht mit "Enter" sondern mit "Strg + Shift + Enter" bestätigen. Wenn du sie richtig bestätigt hast, erscheinen geschweifte Klammern um die Formel ({})
 
Zuletzt bearbeitet:

N_O_K_I_A

Lt. Commander
Ersteller dieses Themas
Dabei seit
Jan. 2007
Beiträge
1.838
Hi,

vielen Dank für die bisherige Hilfe.

Ein kleines Problem habe ich aktuell noch.

Ich bin auf Tabellenblatt 1. Hier stehen meine Grenzen und hier soll auch das Ergebnis vom Sverweis eingetragen werden.
Die Grenzen muss ich ja mit Indirekt auslesen, wie kann ich dann gleichzeitig, die mit Indirekt ausgelesenen Grenzen auf ein anderes Blatt beziehen?

So leider nicht...

=Sverweis(B1;Tabelle3!(INDIRECT(E1):INDIRECT(E2));3;Falsch)
 

Mirakel

Ensign
Dabei seit
Dez. 2010
Beiträge
218
Im Prinzip ganz einfach:
Du musst nur das "Tabellexy!" direkt vor die Zellbezüge schreiben:

=SVERWEIS(F7;INDIREKT("Tabelle3!B"&VERGLEICH(1;1/(Tabelle3!A1:A100=E7);-1)):INDIREKT("Tabelle3!C"&VERGLEICH(1;1/(Tabelle3!A1:A100=E7);1));2)

bzw. äquivalent in VBA

"=sverweis(h2;Tabelle3!b" & firstAddress & ":c" & lastAddress & ";2;FALSCH)"

Wobei ich mir bei der Benennung in VBA jetzt nicht sicher bin, ob das so funktioniert. An diese Stelle gehört es jedenfalls.
 
Top