Excel - SVERWEIS

Ich möchte mit dem SVERWEIS in Excel einen Wert, der in einer verbundenen Zelle steht, in einer Matrix aus mehreren anderen Zellen suchen.
Das Ansprechen der verbundenen Zelle ist nicht das Problem, sondern das Suchen und die variablen Größen, die Matrix der zu durchsuchenden Spalten ist nämlich jeweils unterschiedlich.

Wie kann ich Excel bzw. dem SVERWEIS erklären, ab wann Schluß ist, ohne eine feste Matrixangabe machen zu müssen?
Das möchte ich nämlich nicht, weil es zu viel Aufwand wäre, da, wie schon beschrieben, die Matrix immer wieder anders ist.

Ich möchte also einen Wert in einer Matrix von jeweils 5 Zellen suchen, den nächsten in 3 Zellen, wieder den Nächsten in vllt. einer und so weiter.
Der SVERWEIS soll dann jeweils zurückgeben können (mit ISTNV) ob der Wert gefunden werden konnte oder nicht.

Die Matrix besteht btw jeweils immer nur aus sovielen Zellen, wie die, in der der Suchwert steht.

Wurde die Suchzelle zum Beispiel mit 4 weiteren Zellen verbunden, hat die Suchmatrix 5 Zellen (4+1).

Ich denke das ist verständlich genug und ich bedanke mich im Voraus schon mal für die Hilfe.

Ich hoffe auf rege Beteiligung!^^

Spam und OT wird selbstverständlich gelöscht, aber das sollte eh klar sein. ;)

@bo
Sobald es Variabel wird, solltest du vielleicht überlegen Makros zu verwenden.
Käme das in Frage? VBA ist sehr einfach.

@Fennek

Welche Makros?
Ich hab grundsätzlich nichts gegen Makros, sie sollten sich aber aus Verständnisgründen gegenüber anderer in Grenzen halten.

@bo
VBA Makros in Excel.

Sollte recht einfach über ne Do Until Schleife und ne If Abfrage gelöst werden können, wenn ich es richtig verstanden habe.

Frag mal @A38O der sollte das wissen :D

@Fennek

Kannst du mir erklären, was ich in das Makro reinschreiben soll, damit mein Problem keines mehr ist?

Kann ich vielleicht irgendwie die verbundenen Zellen zählen?
Dann könnte ich die Anzahl dieser in eine Hilfspalte anzeigen lassen und diesen Wert dann benutzen um jeweils die Größe der Matrix anzugeben.
Das sollte mit INDIREKT(VERKETTEN()) gehen oder nicht?

@StUffz

StUffz schrieb:Frag mal @A38O der sollte das wissen

Ja, wenn er die Problemstellung verstanden hätte. ;)

@bo

Hast Du mal ein Beispiel für die variablen Zellbereiche, zum Beispiel in Form eines Screenshots?

Der SVERWEIS braucht einen festen Zellbereich, das geht nicht anders. Hat man den nicht, muss man an dieser Stelle andere Funktionen einsetzen, die diesen Bereich er- und an den SVERWEIS zurückgeben. Dazu wiederum braucht man aber bestimmte Erkenungsmerkmale, zum Beispiel Algorithmen, leere Zellen, Zellen mit bestimmten Inhalten, usw. Und hier liegt IMHO das Problem. Das betrifft auch VBA - auch hier werden solche Erkennungsmerkmale benötigt.

Also ist die Frage: Woran erkennt man die Zellbereiche, auf die die Funktion zugreifen soll - also wo sind die Zeilen und Spalten, aus denen sich die Bereiche zusammensetzen?

@A38O
Danke für die Antwort.

Das einzige Kriterium ist, daß die Zellen verbunden sind.

@bo

bo schrieb:Kann ich vielleicht irgendwie die verbundenen Zellen zählen?

Das wäre mittels benutzerdefinierter Funktion möglich:

- Mappe öffnen,
- Alt + F11,
- Einfügen > Modul
- Code einfügen:

Function verbunden_bereich(ByRef rngBereich As Range)
Application.Volatile
verbunden_bereich = rngBereich.MergeArea.Address(False, False)
End Function

In der Zelle kann die dann verwendet werden:

=verbunden_bereich(E14)

@A38O

Das ist cool!
Kann ich das dann einfach runterkopieren?

@bo

bo schrieb:Kann ich das dann einfach runterkopieren?

Da dürfte es ein Problem geben, wenn Du die Bereiche senkrecht verbunden hast. In dem Fall werden ja auch die Zellen mitgenommen, die in den verbundenen Bereichen liegen - das wäre also vielleicht nicht falsch, aber unschön und überflüssig.

Wenn die Bereiche aber waagerecht verbunden sind, geht es.

@A38O

Das Makro hat mit schon sehr geholfen.
Meine Formel sieht nu so aus:
=ZEILEN(VERKETTEN("AB";(INDIREKT(VERKETTEN("AB";ZEILE())))))
Das Ergebnis ist 1.
Logisch, denn beim Zählen der Zeile AB;ZEILE = AB5, ist nur eine Zeile, ergibt 1.
In AB5 steht aus dem Makro ergeben B5:B13
Das ist meine Matrix, die gezählt werden muss.
Das bedeutet, ich will den Inhalt aus AB5, nicht die Zeile an sich.
Das ist wahrscheinlich ganz einfach, aber ich steh grad auf dem Schlauch und sehs einfach nicht.

Ok, vielen Dank für die Hilfe.
Das Problem ist gelöst.

=ZEILEN(INDIREKT(VERKETTEN(verbunden_bereich(B5))))

@bo

Falls Du ausgehend von dem verbundenen Bereich die Werte aus einem Nachbarbereich verwenden möchtest, kannst Du auch das nehmen:

Function verbunden_bereich(ByRef rngBereich As Range, lngVersatz As Long)
Dim arrTemp
Application.Volatile
If Range(rngBereich.MergeArea.Address(False, False)).Cells.Count = 1 Then
verbunden_bereich = Cells(rngBereich.Row, rngBereich.Column + lngVersatz).Address(False, False)
Else
arrTemp = Split(rngBereich.MergeArea.Address(False, False), ":")
verbunden_bereich = Range(Cells(Range(arrTemp(0)).Row, rngBereich.Column + lngVersatz), Cells(Range(arrTemp(1)).Row, rngBereich.Column + lngVersatz)).Address(False, False)
End If
End Function


Beispiel:

B5:B13 ist verbunden. Du brauchst die Werte aber aus der Spalte D neben dem verbundenen Bereich, also aus D5:D13. Dann kannst Du als Funktion verwenden:

=verbunden_bereich(B5;2)

Das guckt, welcher Bereich mit B5 verbunden ist und gibt die Adresse des Bereichs der zweiten Spalte daneben zurück.

Beispiel:

=SVERWEIS(B5;INDIREKT(verbunden_bereich(B5;2));1;FALSCH)

Oder halt:

=WENN(ISTNV(SVERWEIS(B5;INDIREKT(verbunden_bereich(B5;2));1;FALSCH));"Fehlt";"Vorhanden")

@A38O

Ich hab da noch mal ne Frage.
Geht das auch im Verbund über mehrere Tabellenblätter oder ist das auf ein Blatt beschränkt?

@bo

Das müsste schon gehen:

=ZEILEN(INDIREKT(verbunden_bereich(Tabelle3!B5)))

Aber ich sehe gerade: Brauchst Du wirklich nur die Anzahl der Zeilen? Dann kannst Du auch das nehmen:

Function verbundene_zaehlen(ByRef rngBereich As Range)
Application.Volatile
verbundene_zaehlen = Range(rngBereich.MergeArea.Address(False, False)).Cells.Count
End Function

=verbundene_zaehlen(B5)

@A38O

Wenn ich das richtig verstehe, muss der Folgebereich auch verbunden sein, in deinem Beispiel D.
Was ist aber, wenn ich die Werte aus einem nebenstehenden Bereich brauche, der aber in nicht verbundenen Spalten steht?
Zum Beispiel: Z5:Z13

@bo

Nein, der Folgebereich muss nicht verbunden sein.

Beispiel:

B5:B13 verbunden
Z5:Z13 nicht verbunden

Formel:

=WENN(ISTNV(SVERWEIS(B5;INDIREKT(verbunden_bereich(B5;24));1;FALSCH));"Fehlt";"Vorhanden")

Ich hab jetzt das Problem, dass das mit dem Runterkopieren so nicht geht.
Bei:
=WENN(ISTNV(SVERWEIS(ODER("Y043GGT";"Y043GGB");INDIREKT(ReportResult!verbunden_bereich(B5;22));1;FALSCH));"Fehlt";"Vorhanden")
zählt er die B5, dann auf B6 runter. Aufgrund des Zellenverbunds darf er das so aber nicht, sondern muss die zelle anpassen.