Top-Themen

Aktuelle Themen (A bis Z)

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

Sverweis - mehrere Tabellen durchsuchen

Frage Microsoft Microsoft Office

Mitglied: Florian86

Florian86 (Level 2) - Jetzt verbinden

28.11.2014, aktualisiert 01.12.2014, 18921 Aufrufe, 5 Kommentare

Hallo,

kann man mit SVerweis mehrere Tabellen durchsuchen???
Ich habe das schon im Netz gefunden...

http://www.excelformeln.de/formeln.html?welcher=233

aber irgendwie werd ich da nicht Schlau draus

MfG

Florian86
Mitglied: colinardo
28.11.2014, aktualisiert um 16:35 Uhr
Hallo Florian86,
Zitat von Florian86:
aber irgendwie werd ich da nicht Schlau draus
dann vielleicht aus diesem Demo-Sheet: sverweis_multiple_tables_256193.xlsx

Grüße Uwe
Bitte warten ..
Mitglied: Florian86
01.12.2014 um 07:28 Uhr
Hallo Uwe,

2 Fragen:

1. kann ich die Tabellen auch so eingrenzen, das ich sage {Daten1:Daten200} ?

2. könntest du mir die Formel bissel genauer erklären

=SVERWEIS($B$7;INDIREKT("'"&INDEX({"Daten1";"Daten100";"Daten200"};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'"&{"Daten1";"Daten100";"Daten200"}&"'!A:A");$B$7)>0)+0;0))&"'!A:H");2;0)

Ich will ja nicht nur die Formel blind nutzen sondern auch verstehen.

Danke....

MfG

Florian86
Bitte warten ..
Mitglied: colinardo
LÖSUNG 01.12.2014, aktualisiert um 15:00 Uhr
Zitat von Florian86:
1. kann ich die Tabellen auch so eingrenzen, das ich sage {Daten1:Daten200} ?
Für so viele Blätter wäre ein Makro IMHO die bessere Lösung.
2. könntest du mir die Formel bissel genauer erklären
Die Methode zum verstehen ist immer gleich: Von innen nach außen auflösen. In Excel gibt es dafür auch die Formelauswertung auf dem Tab Formeln.

Weil du es bist ausnahmsweise noch einmal ausführlich:

1. Zuerst wäre da die Formel SVERWEIS:
SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])

2. In den ersten Parameter setzen wir die Zelle ein in der der Wert steht den wir suchen
$B$7

3. In den zweiten Parameter von SVerweis kommt nun eine Matrix. Diese erstellen wir mit der Formel Indirekt die einen Bezug aus einem "Text" zurückgibt den wir jetzt mit der Index-Formel zusammensetzen
INDEX(Matrix; Zeilennummer; [Spaltennummer])

Hier definieren wir als Matrix ein Array aus den Tabellennamen:
{"Daten1";"Daten100";"Daten200"}

Mit dem zweiten Parameter von Index definieren wir welches Sheet aus der Matrix später als Text in die Indirekt-Formel übernommen wird. Dies machen wir mit der Formel Vergleich. Wir prüfen also nun für jedes Sheet ob in der ersten Spalte der Wert von $B$7 vorkommt. Wenn ein Wert in einem Sheet gefunden wurde wird die Vergleich-Formel zu dem Index des Sheets aufgelöst und entsprechend in das richtige Sheet in der Indirekt-Formel ausgeben. Der Sheetname wird dann mit dem String "'!A:H" zusammengesetzt der die Spalten definiert von welchen der Wert zurückgegeben werden soll.

4. Der 3 Parameter von Sverweis kennst du ja, der Index der Spalte die zurückgegeben werden soll

5. Parameter 4 = 0 also genaue Übereinstimmung ist gesucht.

Wie gesagt nutze mal die Formelauswertung, dort kannst du die Formel Schritt-Für-Schritt auflösen und siehst welche Werte Excel jeweils einsetzt.

Grüße Uwe
Bitte warten ..
Mitglied: Biber
LÖSUNG 01.12.2014, aktualisiert um 15:01 Uhr
Moin Florian86 und colinardo,


Zitat von colinardo:

> Zitat von Florian86:
> 1. kann ich die Tabellen auch so eingrenzen, das ich sage {Daten1:Daten200} ?
Für so viele Blätter wäre ein Makro IMHO die bessere Lösung.

ja nee, nur nicht so bescheiden, das geht schon in endlicher Zeit auch mit Formeln.

Angenommen, es gäbe nicht nur Tabellenblätter "Daten1", "Daten100" und "Daten200", sondern x verschiedene im Bereich "Daten1"....bis "Daten200" (oder realistischer: im Bereich "KW1"... bis KW53")

Dann EINmalig als Zwischenergebnis berechnen und anzeigen, in welchem der x Tabellenblätter die Daten vorhanden sind, zB in dem Feld $B$8, direkt unter dem "Eingabe Suchtext"-Feld bei Uwes Lösung.

Dort dann die Formel
=INDEX("Daten"&ZEILE(A1:A200);VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'Daten"&ZEILE(A1:A200)&"'!A:A");$B$7)>0)+0;0))
... und das bitte als Matrixformel eingeben (mit Ctrl-Shift-Enter abschliessen).

Dann sollte, wenn "Kiwi" das Suchwort ist und diese Daten im Blatt "Daten100" stehen--> auch "Daten100" in dieser Zelle gezeigt werden.
Egal ist (zumindest bei Excel, nicht bei Calc von LO/AOO) ,ob es auch wirklich Tabellenblätter "Daten14", Daten87", "Daten124" gibt oder nicht ... der "Nummernkreis" "Daten1"...bis "Daten200" braucht nicht vollständig als Tabellenblätter existieren.

Okay, wenn wir jetzt wissen, in welchem Tabellenbaltt die Daten für den SVerweis stehen, dann vereinfachen sich die zusammengestrunkelten SVERWEIS-Formeln in den jetzigen Zellen Daten1!B10:H10 auf ein schlankes
=SVERWEIS($B$7;INDIREKT("'"&$B$8&"'!A:H");SPALTE(B$1);0)
...in Zelle B10, das nach rechts kopiert werden kann bis in Zelle H10.
Ginge auch kürzer, aber so bleibt es noch les- und wartbar.

P.S. Das relevante Tabellenblatt EINmalig berechnen statt in jedem SVERWEIS.... das würd ich ohnehin machen, auch wenn nicht über einen "Nummernkreis" gesucht wird, sondern über namentlich angegebene Blattnamen.

Grüße
Biber
Bitte warten ..
Mitglied: Florian86
22.12.2014 um 14:52 Uhr
Hallo,

ich habe diese Formel nun mal angewandt und Sie funktioniert auch.
Nur müssen dafür alle Excel Dateien die betroffen sind auch offen sein.

Bei einem SVerweis ja eigentlich nicht zwingend notwendig oder???

=SVERWEIS($C5;INDIREKT("'"&INDEX({"M:\Brunzel, Marco\Personal\Personalplanung\Personalbedarfsermittlung und Gruppenpläne (aktuell)\[Gruppenpläne.xls]Mitarbeiterliste_Laminat1";"M:\Brunzel, Marco\Personal\Personalplanung\Personalbedarfsermittlung und Gruppenpläne (aktuell)\[Gruppenpläne.xls]Mitarbeiterliste_Laminat2";"M:\Brunzel, Marco\Personal\Personalplanung\Personalbedarfsermittlung und Gruppenpläne (aktuell)\[Gruppenpläne.xls]Mitarbeiterliste_Laminat3"};VERGLEICH(1;(ZÄHLENWENN(INDIREKT("'"&{"M:\Brunzel, Marco\Personal\Personalplanung\Personalbedarfsermittlung und Gruppenpläne (aktuell)\[Gruppenpläne.xls]Mitarbeiterliste_Laminat1";"M:\Brunzel, Marco\Personal\Personalplanung\Personalbedarfsermittlung und Gruppenpläne (aktuell)\[Gruppenpläne.xls]Mitarbeiterliste_Laminat2";"M:\Brunzel, Marco\Personal\Personalplanung\Personalbedarfsermittlung und Gruppenpläne (aktuell)\[Gruppenpläne.xls]Mitarbeiterliste_Laminat3"}&"'!A:A");$C$5)>0)+0;0))&"'!A:H");4;0)

MfG

Florian86
Bitte warten ..
Ähnliche Inhalte
Microsoft Office
Formulare, Listboxen und durchsuchen von Tabellen
Frage von 94451Microsoft Office5 Kommentare

Hallo Ihr, dieses Script hat mir schon sehr weitergeholfen hab ich 'fast' so in den Tiefen des Internets gefunden: ...

VB for Applications
Mehrere Spalten Durchsuchen und Ergebnisse in neuer Tabelle Ausgeben
Frage von Mr.GreenVB for Applications3 Kommentare

Hallo liebe Community. Ich würde gerne mit einer VBA mehrere Spalten ("A:W") in einer Tabelle nach einem oder mehreren ...

Microsoft Office
Sverweis mit mehreren Kriterien
gelöst Frage von Florian86Microsoft Office8 Kommentare

Hallo, ich möchte aus einem 2. Tabellenblatt mit Sverweis und 2 Kriterien einen bestimmten Wert ziehen. Die folgende Formel ...

Microsoft Office
Excel SVERWEIS mit mehreren Ausgaben
Frage von PeronasMicrosoft Office

Hallo zusammen, hätte nochmal eine Frage zu einer Excelaufgabe. Und zwar habe ich 2 Tabellen. 1. Tabelle beinhaltet die ...

Neue Wissensbeiträge
Erkennung und -Abwehr

Necur-Botnet soll Erpressungstrojaner Scarab massenhaft verbreiten

Information von BassFishFox vor 13 StundenErkennung und -Abwehr

12,5 Millionen Spam-Mails aus einem Bot-Netz mit 6 Millionen Computern? Eigentlich eine schwache Leistung. Die Erpresser setzen dabei auf ...

Microsoft

Nadeldrucker-Problem unter Windows - Microsoft liefert Updates

Information von BassFishFox vor 14 StundenMicrosoft

Hat ja nicht lange gedauert. Nachdem die November-Updates für Windows 7, 8.1 und 10 zahlreiche Nadeldrucker lahmgelegt hatten, stellt ...

Linux

Limux-Ende in München: Wie ein Linux Projekt unter Ausschluss der Öffentlichkeit zerstört wurde

Information von Frank vor 21 StundenLinux14 Kommentare

Mein persönlicher Kommentar zum Thema "Limux-Ende". Die SPD-Politikerin Anne Hübner hat die Richtung von München ganz klar definiert: "Wir ...

Batch & Shell

Open Object Rexx: Eine mittlerweile fast vergessene Skriptsprache aus dem Mainframebereich

Information von Penny.Cilin vor 1 TagBatch & Shell9 Kommentare

Ich kann mich noch sehr gut an diese Skriptsprache erinnern und nutze diese auch heute ab und an noch. ...

Heiß diskutierte Inhalte
Windows Server
Kann man im KMS nachschauen , wieviele Clients den Key in Anspruch genommen haben
gelöst Frage von rainergugusWindows Server15 Kommentare

Hallo, wir haben einen KMS Windows 10 Key. Dieser ist ja W7 kompatibel. Aber unser Windows 7 Pool registriert ...

Router & Routing
Zwei Netzwerke erstellen
Frage von bunteblumeRouter & Routing14 Kommentare

Hallo Zusammen, Ich möchte gerne ein backup von einem bestimmten Folder welcher auf dem Server regelmässig synchronisiert wird auf ...

Linux
Limux-Ende in München: Wie ein Linux Projekt unter Ausschluss der Öffentlichkeit zerstört wurde
Information von FrankLinux14 Kommentare

Mein persönlicher Kommentar zum Thema "Limux-Ende". Die SPD-Politikerin Anne Hübner hat die Richtung von München ganz klar definiert: "Wir ...

Windows 10
Alle Programme mit bestimmtem Namen automatisch (per GPO) deinstallieren
gelöst Frage von lordofremixesWindows 1012 Kommentare

Hallo zusammen, gibt es eine Möglichkeit, alle Programme beginnend mit z.B. "Dell" im Namen per Script und somit per ...