Excel - SVERWEIS

@bo

Ja, siehe Beitrag von A38O (Seite 1)

Da bräuchte man dann wirklich einen Algorithmus oder andere Erkennungsmerkmale. Zum Beispiel könnte man die Anzahlen der verbundenen Bereiche und deren Zeilenanzahlen addieren - aber das würde eine endlose Formel. Da wäre eine VBA-Routine sicher besser.

@A38O
Das ist meine Formel:
=WENN(ReportResult!verbunden_bereich2(VERKETTEN("B";SUMME(AA5;AC5)))>1;WENN(ISTNV(SVERWEIS(ODER("Y043GGT";"Y043GGB");INDIREKT((ReportResult!verbunden_bereich(VERKETTEN("B";SUMME(ReportResult!AA5;ReportResult!AC5)));24):
(ReportResult!verbunden_bereich(VERKETTEN("B";SUMME(ReportResult!AA5;ReportResult!AC5)));24)));1;FALSCH);"Fehlt";"Vorhanden");WENN(ISTNV(SVERWEIS(ODER("Y043GGT";"Y043GGB");VERKETTEN("Z";SUMME(AA5;AC5)):VERKETTEN("Z";SUMME(AA5;AC5));1;FALSCH));"Fehlt";"Vorhanden")

Das Problem ist der Bereich in der Mitte, bei "dann". Es gibt ein Problem, wenn der Index zb. "verbunden_bereich(B5,24)" keinen eindeutigen Index zurück gibt, also nicht B5:B13 sondern zb. nur B14. Dann beschwert sich Excel der Index wäre unvollstänig, es fehlt ein Argument.
Leider soll in so einem Fall aber nur diese eine Zelle betrachtet werden.

Gibt es da eine andere Funktion, die einzelne Spalten vergleicht, oder nur die, die mit einem Index arbeiten?

Ich hatte wohl etwas vom Code vergessen, deshalb der neue Post.

@bo

bo schrieb:Es gibt ein Problem, wenn der Index zb. "verbunden_bereich(B5,24)" keinen eindeutigen Index zurück gibt, also nicht B5:B13 sondern zb. nur B14.

Das sollte eigentlich kein Problem sein, denn zum Beispiel folgende einfache Formel funktioniert:

=SVERWEIS(D35;E35;1)

Das meintest Du doch - oder?

Ich stecke gerade selbst in einem Programm, da kann ich die Formel leider nicht nebenbei mal auseinandernehmen (sonst habe ich meine eigenen Sachen vergessen). Aber generell: Kannst Du mit (ausgeblendeten) Hilfszellen arbeiten?

Deine Formel besteht aus einigen identischen Teilen. Das macht es nicht nur länger und damit unübersichtlicher, sondern auch die Performance leidet darunter - die einzelnen Funktionen müssen ja immer wieder neu berechnen. Wenn Du aber eine mehrfach vorkommende Funktion in eine Hilfszelle auslagerst, kannst Du in der großen Formel darauf zugreifen und in der Hilfszelle muss nur einmal berechnet werden. Außerdem wird die Hauptformel dadurch kürzer und übersichtlicher.

@A38O

Ja, genau das ist mein Problem, aber er nimmt es nicht so an.
Er sagt, ich habe zu wenig Argumente und verweist mich mit dem Cursor auf die Position der Matrix.

@A38O

So langsam geht s voran.^^

Das ist meine Formel:
=WENN(ISTNV(SVERWEIS(ODER("Y043GGT";"Y043GGB");INDIREKT(ReportResult!verbunden_bereich(VERKETTEN("B";(ReportResult!AA5+ReportResult!AC5));24));1;FALSCH));"Fehlt";"Vorhanden")

Sie gibt das falsche Ergebnis zurück.
Sie sagt vorhanden, das stimmt aber nicht.

Es scheint so zu sein, daß Excel mit einem Namen nicht zurecht kommt, aber da ist kein Tippfehler.

@bo

Bei dem INDIREKT - setze am besten das Blatt in Gänsefüße, wenn ihm die Funktion folgt:

INDIREKT("ReportResult!"&verbunden_bereich( …

Der Blattname ist hier ja nur ein String.

Tritt das Problem generell auf oder nur bei einem Namen?

@A38O

Ja, jetzt funktionierts, aber das Problem ist noch nicht ganz behoben, daß Ergebnis ist immer noch falsch.

Edit:

Kann es sein, dass er das Zusammenführen von "B" und 14 nicht als Zellindex versteht, sondern als Text und deshalb Quatsch zurück gibt?
Wenn ich ihn das nämlich schrittweise durchgehen lasse, ergibt:

verbunden_bereich("B14";24) = #WERT!

@bo

Hast Du es schon mal mit nur einem Suchkriterium statt des ODER getestet? Ich bin mir jetzt nicht sicher, ob man da eine Funktion einsetzen darf - der SVERWEIS sucht ja nach einer Übereinstimmung.

bo schrieb:Kann es sein, dass er das Zusammenführen von "B" und 14 nicht als Zellindex versteht, sondern als Text und deshalb Quatsch zurück gibt?

Du kannst es ja mal in ein INDIREKT setzen.

@A38O
Wie ist das mit der Zahl bei verbunden_bereich(BEZUG;#ZAHL?) zu verstehen?
Worauf bezieht die sich?

@bo

Die Zahl sagt der Funktion, um wieviele Spalten nach rechts sie die Daten suchen soll.

Du beziehst Dich zum Beispiel auf B5. Wenn Du in D die Daten hast, in denen gesucht werden soll, muss da die 2 hin - zwei Spalten nach rechts. Es können auch negative Werte verwendet werden, wenn der zu durchsuchende Bereich links von der verbundenen Zelle liegt.

@A38O

Excel guckt sich aber nur die erste Zeile von verbunden_bereich(B5;24) an?

@bo

Wenn Du den Bereich B5:B10 verbunden hast, nimmst Du in der Funktion B5. Die Funktion guckt sich dann an, welche Zellen mit B5 verbunden sind und gibt - im Fall der 24 - Z5:Z10 im Ergebnis als String zurück.

@A38O

Die Formelauswertung gibt aber nur Z5 wieder und verbindet dann zu reportResult!Z5.
Also eine zeile?

@bo

Da muss der Fehler woanders liegen, so sieht es aus:



Die Funktion gibt nur den Bereich als String zurück, nicht mehr. Prüfe vielleicht nochmal, ob alle Gänsefüße richtig gesetzt sind.

@A38O

Ok, jetzt hab ich ein anderes Problem. :D

bei: =ZEILEN(verbunden_bereich2(B5))
Gibt Excel nur noch #WERT! zurück und nicht mehr die Anzahl der Zeilen.

@bo

Entweder: =ZEILEN(INDIREKT(verbunden_bereich(B5;24)))

Odre als eigene Funktion:

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

Und in einer Formel dann: =verbundene_zaehlen(B5)

Hallo

ich möchte gerne, dass Excel mir in einer Matrix nach einem Begriff sucht und mir dann jeweils die Zeile, in der der Begriff gefunden werden konnte zurück gibt. Also die Zeilennummer.
Jemand eine Idee wie das geht?

@A38O
vielleicht? :D

@yaacool

Vielleicht geht das auch mit einer integrierten Funktion, aber mal auf die Schnelle:

Function ZeileMitWert(ByVal strSuchBegriff As String, ByRef rngBereich As Range)
Application.Volatile
Dim objGefunden As Object
Set objGefunden = rngBereich.Find(strSuchBegriff, lookat:=xlWhole)
If Not objGefunden Is Nothing Then
ZeileMitWert = objGefunden.Row
Else
ZeileMitWert = ""
End If
Set objGefunden = Nothing
End Function

Und in der Zelle:

=ZeileMitWert(C3;D3:D14)

damit gibt er aber auch nur die nummer einer zeile wieder
das müsste man wohl noch anpassen

@yaacool

yaacool schrieb:mir dann jeweils die Zeile, in der der Begriff gefunden werden konnte zurück gibt. Also die Zeilennummer.

yaacool schrieb:damit gibt er aber auch nur die nummer einer zeile wieder

Ja. ;)