Tabellenübergreifende suche-mit SVERWEIS?

Forumthread: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 11:48:00

Andreas

Hallo zusammen,
ich habe hier eine Tabelle mit derzeit 8 Tabellenblättern. Im 9. Blatt soll eine Suche über alle diese Tabellenblätter möglich sein. Die Ergebnisse sollten dann auf dem Suchtabellenblatt 9 ausgegeben werden. Der gesuchte Wert steht in den 8 Tabellen in der Spalte D in Zahlenform wie z.B. 3216, 3216/8 oder 63157264 usw. Zur Info, da es auch unbekannte Werte hat, kann auch ein - in der Zelle stehen. Bei einem Suchtreffer, soll aber die ganze Zeile anzeigt werden, nicht nur den Zellenwert! Ich habe zwar gesehen, daß mit SVERWEIS so etwas anscheinend zu realisieren ist. Aber....wie? Ich habe im Archiv keinen Thread gefunden, der ansatzweise das macht was ich suche. Aber es ist natürlich möglich, daß mein Suchkriterium falsch war im Archiv. Wer kann mir helfen oder einen Thread posten, der dieses Thema mit meienr Anforderung behandelt?
mfG
Andreas Müller

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 12:41:00

Tino

Hallo,
hier mal ein Vorschlag, müsste noch auf mehrere Tabellen erweitert werden
vielleicht durch eine verschachtelte wenn Funktion.

	A	B	C	D
1	test7	<-- A1 = Suchewert
2
3	a10	b10	c10	test7

Formeln der Tabelle

Zelle	Formel
A3	=VERWEIS($A$1;Tabelle1!$D:$D;Tabelle1!A:A)
B3	=VERWEIS($A$1;Tabelle1!$D:$D;Tabelle1!B:B)
C3	=VERWEIS($A$1;Tabelle1!$D:$D;Tabelle1!C:C)
D3	=VERWEIS($A$1;Tabelle1!$D:$D;Tabelle1!D:D)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß
Tino

AW: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 19:07:20

Andreas

Hi Tino,
die Lösung von Daniel ist vom Aufwand und Ergebnis her gesehen die beste. Aber danke für den Vorschlag und Tip.
Gruß Andreas

AW: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 13:05:17

Daniel

Hallo
was der SVERWEIS macht und wie er funktioniert, kannst du hier nachlesen:
http://www.online-excel.de/excel/singsel.php?f=9
allerdings hast du das Problem, daß du ja 8 Blätter durchsuchen musst, der SVERWEIS ist immer nur für ein Blatt ausgelegt, dh, du müsstest dann immer die SVERWEISE für jedes Blatt in eine Formel packen, das gibt dann sehr komplexe Formeln
Daher hab ich mal diese selbstbeschriebenen Funktion "SuchenX" geschrieben, sie durchsucht alle Tabellenblätter nach dem Suchwert und gibt entweder den Blatt-Namen (1, Parameter = 1) oder die Zeilen-Nr (1. Parameter = 2) zurück, in der der Suchbegriff gefunden wurde.
mit diesen beiden Werten kannst du dir dann die restlichen Daten der Zeile über die INDIREKT-Funktion anzeigen lassen.
die weitern Parameter sind der Suchbegriff und die Suchspalte, in der gesucht werden soll.
der Code der Funktion sieht so aus:
Public Function SucheX(Art As Byte, Suchbegriff As Variant, Suchspalte As String) As Variant Dim Zelle As Range Dim sh As Worksheet If Suchbegriff = "" Then SucheX = "" Else For Each sh In ActiveWorkbook.Worksheets Set Zelle = sh.Columns(Suchspalte).Find(what:=Suchbegriff, LookIn:=xlValues, lookat:= _ xlWhole) If Not Zelle Is Nothing Then Exit For Next Select Case Zelle Is Nothing Case True SucheX = "nicht gefunden" Case False Select Case Art Case 1 SucheX = sh.Name Case 2 SucheX = Zelle.Row End Select End Select End If End Function

und hier die Beispielmappe:
https://www.herber.de/bbs/user/49790.xls
Gruß, Daniel

AW: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 18:08:00

Andreas

Hi Daniel,
das geht mal schon soweit ganz gut. Wie muss ich das erweitern, wenn er mir auch die Spalte C anzeigen soll? Geht es auch, daß er dann in einer seperaten Zeile (oder woanders) den Namen des Tabellblattes zeigt, in dem er den Wert gefunden hat?
Gruß Andreas

AW: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 18:57:17

Andreas

Hi Daniel,
das geht mal schon soweit ganz gut. Wie muss ich das erweitern, wenn er mir auch die Spalte C anzeigen soll? Die letzte Frage die ich eben wegeditiert habe war ja Schwachsinn, sorry. Viel besser wäre die Frage, ob man auch im Falle eines Doppelfundes zwei oder Drei Ergebnisse anzeigen lassen kann.
Gruß Andreas

AW: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 20:19:00

Daniel

Hi
für die Werte aus Spalte C musst du halt noch ne Zelle mit der Index-Funktion ergänzen:


=INDIREKT("'"&$B$6&"'!C"&$B$7)

zur Frage der Doppelfunde:
im Prinzip könnte man sowas gestalten, es wird dann aber deutlich aufwendiger, weil die Suchschleife nicht beim ersten Fund abbrechen darf, sondern die Erbebnisse erstmal sammeln muss.
desweiteren bräuchte man noch eine weitere Schleife, die die Tabellenblätter so oft durchsucht, wie ergebnisse vorhanden sind.
desweiteren müsste man für die Ausgestaltung wissen, wie die Ergebnisse dargestellt werden sollen.
Gruß, Daniel

AW: Tabellenübergreifende suche-mit SVERWEIS?

10.02.2008 22:45:04

Andreas

Hi Daniel,
naja so eine Nummer kann maximal 3x vorkommen. Die Ergebnisdarstellung kann so aussehen, wie die jetzt von dir erzeugte. Einfach zwei weitere Zeilen darunter. Ich weiss ich rede sicher wieder so einafch daher :-(.
Gruß Andreas

3D-SVERWEIS

10.02.2008 14:44:23

{Boris}

Hi Andreas,
ich hatte da seinerzeit auch mal eine UDF (Eigene Funktion) geschrieben. Binde sie in ein allgemeines Modul ein:


Option Explicit
Function SVERWEIS3D(Bezug As Variant, Suchkriterium As Variant, Spaltenindex As Integer) As  _
Variant
Dim s           As String       'Formelstring
Dim WsStart     As String       'Name Erstes Blatt
Dim WsEnde      As String       'Name Letztes Blatt
Dim iCount      As Integer      'Zähler vom 1. bis zum letzten Blatt
Dim i           As Integer      'Index des 1. Blattes
Dim j           As Integer      'Index des letzten Blattes
Dim B           As Boolean      'Prüfung, ob Mehrfachbezug vorhanden
Dim sBereich    As String       'Bereich auslesen bei Mehrfachmarkierung
Const MB        As String = "*:*!*,*" 'typische Syntax für Mehrfachbereich
s = Application.Caller.Formula 'Formel auslesen
'Prüfen, ob Mehrfachmarkierung vorliegt
B = s Like MB
If B Then 'Falls Mehrfachbezug vorhanden in der Form: Tabelle1:Tabelle3!A1:C100
'Formelstring innerhalb der Klammern auslesen und an s übergeben
s = Mid(s, InStr(1, s, "(") + 1, InStr(1, s, ")") - InStr(1, s, "(") - 1)
'Erstes Blatt auslesen
WsStart = Trim(Left(s, InStr(1, s, ":") - 1))
'Letztes Blatt auslesen
WsEnde = Trim(Mid(s, InStr(1, s, ":") + 1, InStr(1, s, "!") - InStr(1, s, ":") - 1))
'Zellbereich auslesen
sBereich = Trim(Mid(s, InStr(1, s, "!") + 1, InStr(1, s, ",") - InStr(1, s, "!") - 1))
'Index des 1. Blattes ermitteln
i = Worksheets(WsStart).Index
'Index des letzten Blattes ermitteln
j = Worksheets(WsEnde).Index
'Schleife über alle tangierten Blätter
For iCount = i To j
With WorksheetFunction
If .CountIf(Sheets(iCount).Range(sBereich).Columns(1), Suchkriterium) Then
SVERWEIS3D = .VLookup(Suchkriterium, Sheets(iCount).Range(sBereich),  _
Spaltenindex, 0)
Exit Function
Else
SVERWEIS3D = "Nix gefunden"
End If
End With
Next iCount
Else 'Falls kein Mehrfachbezug vorhanden: Einfacher SVERWEIS
SVERWEIS3D = WorksheetFunction.VLookup(Suchkriterium, Bezug, Spaltenindex, 0)
End If
End Function

In der Tabelle verwendest Du sie an mit der Syntax:


=SVERWEIS3D(Bezug;Suchkriterium;Spaltenindex)

Also z.B. (der Suchbegriff steht in E1):


=SVERWEIS3D(Tabelle1:Tabelle3!A:B;E1;2)

Es wird im Übrigen nur nach exakter Übereinstimmung gesucht.
Grüße Boris

AW: 3D-SVERWEIS

10.02.2008 19:09:00

Andreas

Hallo Boris,
danke für deine Lösung. Ich habe mich aber für die von Daniel entschieden. Die ist mir vom Aufwand her und für mein Verständnis etwas einfacher aber ebenso effektiv. Danke aber für deine Lösung, werde sie trotzdem abspeichern.
Gruß Andreas

Beliebteste Forumthreads (12 Monate)

Tabellenübergreifende suche-mit SVERWEIS?

Forumthread: Tabellenübergreifende suche-mit SVERWEIS?

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Tabellenübergreifende suche-mit SVERWEIS?

Forumthread: Tabellenübergreifende suche-mit SVERWEIS?

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread