Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

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 1) - Jetzt verbinden

28.11.2014, aktualisiert 01.12.2014, 11987 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

Mit freundlichen Grüßen

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....

Mit freundlichen Grüßen

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)

Mit freundlichen Grüßen

Florian86
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(3)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
Heiß diskutierte Inhalte
LAN, WAN, Wireless
gelöst Server erkennt Client nicht wenn er ausserhalb des DHCP Pools liegt (28)

Frage von Mar-west zum Thema LAN, WAN, Wireless ...

Windows Server
Server 2008R2 startet nicht mehr (Bad Patch 0xa) (18)

Frage von Haures zum Thema Windows Server ...

Outlook & Mail
Outlook 2010 findet ost datei nicht (18)

Frage von Floh21 zum Thema Outlook & Mail ...