Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

SVerweis findet nicht, wenn.....

SVerweis findet nicht, wenn.....
17.04.2014 12:07:20
Franz
Hallo Fachleute,
ich verwende eine SVerweis-Formel in Verbindung mit ISTZAHL(FINDEN......... Es soll immer der erste von mehreren passenden Werten, der beide Bedingungen erfüllt (SVerweis und Finden) gefunden und ausgegeben werden. Wenn allerdings der erste von SVerweis gefundene nicht mit der Bedingung für Finden zusammenpasst, wird nichts ausgegeben.
Bevor ich das alles umständlich so erkläre, das es verständlich ist, hier ein kleines Beispiel: https://www.herber.de/bbs/user/90235.xls
Womit lässt sich das denn lösen? Hab irgendwie mit INDEX probiert, krieg's aber nicht hin.
Danke schon mal und Grüße
Franz

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
als UDF mit VBA ? SVerweis findet nicht, wenn.....
17.04.2014 12:44:49
Klaus
Hallo Franz,
mit INDEX und Matrixfunktionalität ist sicherlich eine Formellösung möglich, aber ich komm grad nicht drauf ... darum lasse ich den Beitrag offen.
Was ich dir anbieten kann ist eine VBA-Lösung per UDF. In ein Modul der Datei folgenden Code:
Function SVWENN(Suchkriterium As Variant, Matrix_Kriterium As Range, SuchBedingung As String,  _
Matrix_Bedingung As Range) As String
Dim r As Range
SVWENN = "Nichts gefunden"
For Each r In Matrix_Bedingung
If Not Len(r) = Len(WorksheetFunction.Substitute(r, SuchBedingung, "")) Then
If Cells(r.Row, Matrix_Kriterium.Column).Value = Suchkriterium Then
SVWENN = r.Value
Exit For
End If
End If
Next r
End Function
und in C13 die Formel:
=SVWENN(B13;E13:E16;A12;F13:F16)
Achtung: die Bereiche E13:E16 und F13:F16 müssen gleich lang und einspaltig sein, und in den gleichen Zeilen anfangen + aufhören, sonst funktioniert es nicht richtig! Ich habe keine dementsprechende Fehlerüberprüfung eingebaut.
Grüße,
Klaus M.vdT.

Anzeige
AW: als UDF mit VBA ? SVerweis findet nicht, wenn.....
17.04.2014 12:56:25
Franz
Hallo Klaus,
danke, das ist wunderbar! Es klappt super. Jetzt muss ich noch versuchen, dass in meine Hauptdatei reinzukriegen und dann freu ich mich :-)))
Danke und Grüße
Franz

Danke für die Rückmeldung! mit Text
17.04.2014 13:25:42
Klaus
Hallo Franz,
sei bitte etwas vorsichtig mit der UDF, die Bereiche müssen immer ganz exakt stimmen - und Fehler werden nicht mit #NV quittiert, wie du es von "echten" Formeln gewohnt bist. Also Obacht!
Wenn du es in die Musterdatei bekommen hast, wirst du es auch in deinen Master bekommen! Einfach ALT+F11 für die VBA-Oberfläche, rechtsclick auf den Dateinamen und "neues Modul" und rein mit dem Code!
Grüße,
Klaus M.vdT.

Anzeige
AW: Danke für die Rückmeldung! mit Text
17.04.2014 13:39:24
Franz
Hallo Klaus,
danke ja, die UDF hab ich schon drin; (lediglich zu Deiner Formel kommt noch ne weitere Abfrage mit in die Zelle, das krieg ich schon hin). Und - JETZT - klappt's auch. Was in er Musterdatei funktioniert und in der Master nicht, ist ne Kleinigkeit, allerdings bin ich erstmal gestolpert:
wenn ich die Formel eintrage, bekomme ich erstmal "Nichts gefunden". Erst wenn ich im Quellbereich, DER BEI MIR IN EINEM ANDEREN BLATT LIEGT, in der rechten Spalte was eintrage oder mit F2 aktualisiere, dann werden die Werte übernommen (das passiert auch in der Beispieldatei, wenn ich den Quellbereich auf ein anderes Sheet verlege.
Das ist aber kein größeres Problem (oder auch überhaupt keines, denke ich mal), weil das Blatt mit den Formale fix ist, und es nur im Datenbereich (Quellbereich) Veränderungen gibt.
Das "Nichts gefunden" in Deiner UDF hab ich durch "" ersetzt, damit an den Tagen, an denen es keine ÜBereinstimmungen gibt, die Zelle einfach leer bleibt.
Danke nochmal und Grüße
Franz

Anzeige
Formel nicht "volatil"
17.04.2014 13:57:59
Klaus
Hallo Franz,
ich bin nicht richtig gut in dem Thema drin, betrachte das folgende bitte als gefährliches Halbwissen:
Die UDF ist nicht "volatil", das heißt nicht selbst berechnend. Das berechnen musst du anstoßen, zB. durch Zelle-Click-F2. Die Bereiche in anderen Blättern dürfte eigentlich kein Problem sein.
Um die Formel zum permanent-Berechnen zu zwingen, kannst du in die erste Zeile reinschreiben
Application.Volatil
aber damit handelts du dir am Ende des Tages mehr Probleme ein als es löst :-)
Grüße,
Klaus M.vdT.

AW: Formel nicht "volatil"
17.04.2014 15:12:05
Franz
Hallo Klaus,
hab Dir vorhin geantwortet, ist aber anscheinend untergegangen. Wollte sagen, dass ich's als Anfänger dann wohl lieber bleiben lasse mit "Volatile".
Inzwischen hat sich aber rausgestellt, das ichDeine UDF leider doch nicht werde verwenden können. Zum einen wegen der Volatilität: wenn das Blatt mit Deiner Formel (Deinen Formeln) aufgerufen wird, wird ein Activate-Makro ausgelöst, das einige Formatierungen entsprechend der Einträge vornimmt. Dadurch wird alles "zunichte gemacht" und in allen Zellen mit Deiner UDF steht nur noch "Nichts gefunden".
Zum zweiten ist der Zielbereich ein Ganzjahreskalender, und Deine Formel steht in jedem einzelnen Tag drin. Was zur Folge hat, dass nach einem Eintrag im QUELL-Bereich mehrere Sekunden vergehen, bis es weitergeht, wohl, weil 365 Mal die Formel berechnet werden muss.
Ich nutze bereits seit längerem eine VBA-Lösung, die erst die relevanten, dem Suchkriterium entsprchenden Daten aus dem Quellbereich in einer Liste auflistet, die dann per SVerweis abgefragt wird. Was auch gut funktioniert. Ich dachte nur, es gäbe vielleicht eine schnellere Formellösung. Drum bin ich das Ganze mal wieder angegangen. Aber dadruch wird die ganze Datei langsamer, nicht nur der Aufruf des relevanten Sheets. Ich glaub, ich bleib wohl bei der alten VBA-Variante :-)))
Trotzdem vielen Dank! Ich werd mir Deins auf jeden Fall aufheben, wer weiß, wo ich's mal brauchen kann...
Beste Grüße und frohe Ostern
Franz
Anzeige

309 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige