SVERWEIS:Mehrere Abfragekriterien
Juergen Schramm
gibt es die Möglichkeit bei der Sverweis-Funktion mehr als 1 Suchkriterium
zu berücksichtigen?
Sollte so aussehen:
Spalte A Spalte B Spalte C
4000 20 40
4000 30 10
Für das gesamte Tabellenblatt wurde der Name test vergeben, in einem neuen
Tabellenblatt steht soll er zuerst nach 4000 suchen gleichzeitig noch nach
20 schauen und das Ergebnis 40 (oder ähnliches) liefern.
Klingt etwas kompliziert, aber ich weiß nicht wie ich eine "Verschachtelte
Sverweis-Funktion" einbauen muß.
Martin Beck
verwende die folgende benutzerdefinierte Funktion:
Function ZweiSverweis(Bedingung1, Bedingung2, Suchbereich1, Suchbereich2,
Zuordnungsspalte)
Dim Erg As Object
Dim i As Integer
i = 1
Do
If Suchbereich1(i) = Bedingung1 And Suchbereich2(i) = Bedingung2 Then Exit
Do
i = i + 1
Loop
z = Suchbereich1(i).Row
s = Suchbereich1(i).Column
Set Erg = Cells(z, s + Zuordnungsspalte - 1)
ZweiSverweis = Erg.Value
End Function
Wenn in den Zellen D1:F11 folgende Zahlen stehen
4000 20 40
333 70 10
4000 40 -20
634 30 -50
4000 60 -80
424 111 -110
4000 80 -140
364 90 -170
4000 100 -200
4000 110 -230
4000 120 -260
liefert
=ZweiSverweis(D5;E5;D1:D11;E1:E11;3) oder
=ZweiSverweis(4000;60;D1:D11;E1:E11;3)
das Ergebnis -80
Wichtig ist, daß die beiden Suchbereiche beide von Zeile x bis Zeile y
gehen.
Gruß
Martin Beck
Juergen Schramm schrieb in Nachricht <7b702r$4pm$1...@news.online.de>...
Oliver Felsch
deine Funktion wollte ich nun für ein solches Problem ebenfalls verwenden.
Allerdings meldet mir Excel ('97) #WERT. Obwohl ich deine Funktion voll-
ständig kopiert habe. Allerdings mußte ich es auf
Function ZweiSverweis()
abändern. Denn sonst hat mir Excel einen Syntaxfehler gemeldet.
Könnte es vielleicht daran liegen, daß ich es selbständiges Modul
(aber in der gleichen Arbeitsmappe) abgespeichert habe ?!
Kannst Du mir vielleicht weiterhelfen ?
Mit besten Grüßen
Oliver Felsch
****************************************************************************
********
§§ Der kommerzielle Gebrauch meiner Email-Adresse ist verboten! §§
§§ The commercial use of my email-address is forbidden! §§
§§ LG Berlin AZ 16 O 201/98 und LG Berlin AZ 16 O 301/98 §§
****************************************************************************
********
[...]
>Function ZweiSverweis(Bedingung1, Bedingung2, Suchbereich1, Suchbereich2,
>Zuordnungsspalte)
>Dim Erg As Object
>Dim i As Integer
>i = 1
>Do
>If Suchbereich1(i) = Bedingung1 And Suchbereich2(i) = Bedingung2 Then Exit
>Do
>i = i + 1
>Loop
>z = Suchbereich1(i).Row
>s = Suchbereich1(i).Column
>Set Erg = Cells(z, s + Zuordnungsspalte - 1)
>ZweiSverweis = Erg.Value
>End Function
[...]
Martin Beck
leider kann ich das nicht nachvollziehen, da ich Excel 97 nicht zur Hand
habe. Ich sehe aber keinen Grund, warum die Funktion nicht unter Excel 97
funktionieren sollte. Vielleicht kann das ja jemand anderes aus der NG noch
einmal checken.
Mit privater Mail schicke ich Dir eine Excel 95 Demo-Mappe, mit der Du
selbst die Funktion noch einmal testen kannst.
Gruß
Martin Beck
Oliver Felsch schrieb in Nachricht <7bhmj8$gt2$1...@news2.kamp.net>...
>Hallo Martin und NG-Leser !
>
>deine Funktion wollte ich nun für ein solches Problem ebenfalls verwenden.
>Allerdings meldet mir Excel ('97) #WERT. Obwohl ich deine Funktion voll-
>ständig kopiert habe. Allerdings mußte ich es auf
>
>Function ZweiSverweis()
>
>abändern. Denn sonst hat mir Excel einen Syntaxfehler gemeldet.
>Könnte es vielleicht daran liegen, daß ich es selbständiges Modul
>(aber in der gleichen Arbeitsmappe) abgespeichert habe ?!
>
>
Andreas Steffens
ich habe es gerade mal getestet, die Funktion läuft! Wenn Du die Funktion
aus der Mail in Dein Modul kopierst, dürftest Du ein paar fehlerhafte Zeilen
erhalten, da die Mail ja einen Zeilenumbruch enthält. Diese mußt Du
natürlich entfernen (In der Function... und der Zeile, die mit Exit endet).
Bei mir werden die Zeilen mit Syntax-Fehlern hervorgehoben.
HTH,
Andreas
Oliver Felsch <oliver...@ob.kamp.net> schrieb in Nachricht
7bhmj8$gt2$1...@news2.kamp.net...///
>Hallo Martin und NG-Leser !
>
>deine Funktion wollte ich nun für ein solches Problem ebenfalls verwenden.
>Allerdings meldet mir Excel ('97) #WERT. Obwohl ich deine Funktion voll-
>ständig kopiert habe. Allerdings mußte ich es auf
>
>Function ZweiSverweis()
>
>abändern. Denn sonst hat mir Excel einen Syntaxfehler gemeldet.
>Könnte es vielleicht daran liegen, daß ich es selbständiges Modul
>(aber in der gleichen Arbeitsmappe) abgespeichert habe ?!
>
>Kannst Du mir vielleicht weiterhelfen ?
>
>Mit besten Grüßen
>
>Oliver Felsch
>
>***************************************************************************
*
>********
>§§ Der kommerzielle Gebrauch meiner Email-Adresse ist verboten! §§
>§§ The commercial use of my email-address is forbidden! §§
>§§ LG Berlin AZ 16 O 201/98 und LG Berlin AZ 16 O 301/98 §§
>***************************************************************************
*
>********
>
>
>[...]
>>Function ZweiSverweis(Bedingung1, Bedingung2, Suchbereich1, Suchbereich2,
>>Zuordnungsspalte)
>>Dim Erg As Object
>>Dim i As Integer
>>i = 1
>>Do
>>If Suchbereich1(i) = Bedingung1 And Suchbereich2(i) = Bedingung2 Then Exit
>>Do
>>i = i + 1
>>Loop
>>z = Suchbereich1(i).Row
>>s = Suchbereich1(i).Column
>>Set Erg = Cells(z, s + Zuordnungsspalte - 1)
>>ZweiSverweis = Erg.Value
>>End Function
>
>[...]
>
>
Martin Beck
Danke für's testen.
Gruß
Martin Beck
Andreas Steffens schrieb in Nachricht ...
>Hallo Oliver,
>
>ich habe es gerade mal getestet, die Funktion läuft!
>>
>>
joe...@hotmail.com
Herausfiltern nur, wenn ich die Funktion ZweiSverweis in dem gleichen
Tabellenblatt wie die einzelnen Suchbereiche eingebe.
Aber eigentlich verwendet man doch die Sverweis-Funktion in einem
anderen Tabellenblatt als in der die Daten stehen. Und genau dies geht
nicht mit der neuen Funktion. Wünschenswert wäre doch auch so eine
Eingabe zu ermöglichen( =ZweiSverweis(A1;B1;Tabelle1!A1;Tabelle1!D1;5)
). An der Lösung dieser Fragestellung bin ich aber bis dato
gescheitert.
Gruß,
Joachim
-----------------------------
> >>>Function ZweiSverweis(Bedingung1, Bedingung2, Suchbereich1,
Suchbereich2,
> >>>Zuordnungsspalte)
> >>>Dim Erg As Object
> >>>Dim i As Integer
> >>>i = 1
> >>>Do
> >>>If Suchbereich1(i) = Bedingung1 And Suchbereich2(i) = Bedingung2
Then
> Exit
> >>>Do
> >>>i = i + 1
> >>>Loop
> >>>z = Suchbereich1(i).Row
> >>>s = Suchbereich1(i).Column
> >>>Set Erg = Cells(z, s + Zuordnungsspalte - 1)
> >>>ZweiSverweis = Erg.Value
> >>>End Function
> >>
> >>[...]
> >>
> >>
> >
> >
>
>
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Martin Beck
joe...@hotmail.com schrieb in Nachricht
<7c67k7$u7a$1...@nnrp1.dejanews.com>...
>Ist eine sehr interessante Funktion,
Erstmal danke.
>nur leider funktioniert dieses
>Herausfiltern nur, wenn ich die Funktion ZweiSverweis in dem gleichen
>Tabellenblatt wie die einzelnen Suchbereiche eingebe.
>Aber eigentlich verwendet man doch die Sverweis-Funktion in einem
>anderen Tabellenblatt als in der die Daten stehen.
Wenn ich mich recht erinnere, war damals die Fragestellung so.
>Und genau dies geht
>nicht mit der neuen Funktion. Wünschenswert wäre doch auch so eine
>Eingabe zu ermöglichen( =ZweiSverweis(A1;B1;Tabelle1!A1;Tabelle1!D1;5)
>). An der Lösung dieser Fragestellung bin ich aber bis dato
>gescheitert.
Deshalb hier eine kleine Ergänzung (Achtung: Zeilenumbrüche des Newsreaders
beachten!):
Function ZweiSverweis_besser (Bedingung1, Bedingung2, Suchbereich1,
Suchbereich2, Zuordnungsspalte, Tabellenblattnummer)
Dim Erg As Object
Dim i As Integer
i = 1
Do
If Suchbereich1(i) = Bedingung1 And Suchbereich2(i) = Bedingung2 Then Exit
Do
i = i + 1
Loop
z = Suchbereich1(i).Row
s = Suchbereich1(i).Column
Set Erg = Worksheets(Tabellenblattnummer).Cells(z, s + Zuordnungsspalte - 1)
ZweiSverweis = Erg.Value
End Function
Als Wert für den zusätzlichen Parameter "Tabellenblattnummer" muß die Nr.
des Tabellenblattes angegeben werden, in denen die Suchbereiche und der zu
findende Wert stehen.
Gruß
Martin Beck
Werner Janz
Tabelle2 (Datenbank)
1. Zeile -> Spaltenüberschriften nebeneinander
2. und folgende Zeilen -> Daten untereinander
Im folgenden Beispiel in den Spalten A bis E
Tabelle1 (Auswertung)
1. Zeile -> 2 oder mehrere Spaltenüberschriften aus der Datenbank
2. Zeile -> Die gesuchten Inhalte für die betreffende Spalte
Im folgenden Beispiel in A1:B2
4. Zeile -> die Spaltenüberschriften in der gewünschten Reihenfolge und
Auswahl
5. Zeile =DBAUSZUG(Tabelle2!$A:$E;A$4;$A$1:$B$2) und rechts rüber kopieren
Ein Vorteil dieser Methode ist, daß man beliebig viele Kriterien kombinieren
kann.
Der absolute Nachteil ist die Online-Hilfe zu den Datenbank-Funktionen. Die
sind zwar schon uralt, aber teilweise immer noch sehr gut einsetzbar und im
Vergleich zu den neuerdings so hochgelobten Matrix-Funktionen auch noch
wesentlich schneller.
Tschüß
Werner
--
Bei Antwort bitte NOSPAM entfernen /
Please remove NOSPAM at response
--------------------------------------------------------------
joe...@hotmail.com schrieb in Nachricht
<7c67k7$u7a$1...@nnrp1.dejanews.com>...
>Ist eine sehr interessante Funktion, nur leider funktioniert dieses
>Herausfiltern nur, wenn ich die Funktion ZweiSverweis in dem gleichen
>Tabellenblatt wie die einzelnen Suchbereiche eingebe.
>Aber eigentlich verwendet man doch die Sverweis-Funktion in einem
>anderen Tabellenblatt als in der die Daten stehen. Und genau dies geht
>nicht mit der neuen Funktion. Wünschenswert wäre doch auch so eine
>Eingabe zu ermöglichen( =ZweiSverweis(A1;B1;Tabelle1!A1;Tabelle1!D1;5)
>). An der Lösung dieser Fragestellung bin ich aber bis dato
>gescheitert.
>
>Gruß,
>
>Joachim
>
>
>
>-----------------------------
>> >>>Function ZweiSverweis(Bedingung1, Bedingung2, Suchbereich1,
>Suchbereich2,
>> >>>Zuordnungsspalte)
>> >>>Dim Erg As Object
>> >>>Dim i As Integer
>> >>>i = 1
>> >>>Do
>> >>>If Suchbereich1(i) = Bedingung1 And Suchbereich2(i) = Bedingung2
>Then
>> Exit
>> >>>Do
>> >>>i = i + 1
>> >>>Loop
>> >>>z = Suchbereich1(i).Row
>> >>>s = Suchbereich1(i).Column
>> >>>Set Erg = Cells(z, s + Zuordnungsspalte - 1)
>> >>>ZweiSverweis = Erg.Value
>> >>>End Function
>> >>
joe...@hotmail.com
Vielen Dank für den Hinweis. Klappt auch sehr gut. Die andere Lösung
(siehe oben) ist in meinem speziellen Fall aber besser, da ich so
einen Tabellenaufbau, wie hier benötigt, nicht realisieren kann.
Gruß,
Joachim
us...@my-dejanews.com
Hallo NG!
Die neue Funktion klappt jetzt gut. Ich erhalte das gewünschte
Ergebnis.
Meine Idee für eine Verbesserung der Formel wäre folgende:
Man sollte die Formel unabhängig von der Positionierung des zu
durchsuchenden Tabellenblattes innerhalb der Arbeitsmappe machen. Bei
der normalen SVERWEIS-Funktion kann man im nachhinein das
Tabellenblatt umbenennen und an eine andere Position innerhalb der
Arbeitsmappe stellen und trotzdem erhält man das korrekte Ergebnis.
Kann man das nicht auch mit dieser Formel erreichen? Ich habe doch
durch die Eingabe des ersten Suchbereiches die Angabe in welchem
Tabellenblatt ich mich befinde, oder? Und wie kann ich nun diese
Angabe nutzen, damit ich trotz einer Umbenennung und Verschiebung des
Tabellenblattes ein korrektes Ergebnis erhalte?
Leider bin ich aber an der Realisierung gescheitert, da ich nicht
weiß, wie ich auch der Angabe des Suchbereiches1 die Blattnummer
innerhalb der Arbeitsmappe erhalte.