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

Mitglied: Florian86

Florian86 (Level 2) - Jetzt verbinden

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

Ups: Einfaches Nullzeichen hebelte den Anti-Malware-Schutzt in Windows 10 aus

Information von kgborn vor 12 StundenErkennung und -Abwehr

Windows 10 ist das sicherste Windows aller Zeiten, wie Microsoft betont. Insidern ist aber klar, das es da Lücken, ...

Windows 10

Windows 10 on ARM: von Microsoft entfernte Info - Klartext, was nicht geht

Information von kgborn vor 14 StundenWindows 10

Windows 10 on ARM ist ja eine neue Variante, die Microsoft im Verbund mit Geräteherstellern am Markt etablieren will. ...

Microsoft
TV-Tipp: Das Microsoft-Dilemma
Information von kgborn vor 14 StundenMicrosoft8 Kommentare

Aktuell gibt es in Behörden und in Firmen eine fatale Abhängigkeit von Microsoft und dessen Produkten. Planlos agieren die ...

Windows 10
Zero-Day-Lücke in Microsoft Edge
Information von kgborn vor 2 TagenWindows 10

In Microsofts Edge-Browser klafft wohl eine nicht geschlossene (0-Day) Sicherheitslücke im Just In Time Compiler (JIT Compiler) für Javascript. ...

Heiß diskutierte Inhalte
Windows 10
Windows 10 (1709) Tastur und Maus wieder einschalten?
Frage von LochkartenstanzerWindows 1026 Kommentare

Moin, Ich habe von einem Kunden einen Win10-Rechner bekommen, bei dem weder Tastatur noch Maus geht. Die Hardware funktioniert ...

Firewall
RB2011 Firewall Rule eine bestimmte Mac oder IP Adresse nicht zu blockieren
Frage von lightmanFirewall15 Kommentare

Hallo liebes Forum mit ihren Spezialisten. Ich habe meine Firewall so konfiguriert das kein Endgerät ohne meine Speziellen Erlaubnis ...

Humor (lol)
Was könnte man mit einem Server machen? Idee gesucht
Frage von 2SeitenHumor (lol)15 Kommentare

Hey Zusammen Ich habe einen alten HP G2 Rackserver zu Hause rumliegen. 28GB Ram, 1xAMD Prozi mit etwa 2GHz. ...

Webbrowser
Welcher Browser ist der Beste?
Frage von justtinWebbrowser13 Kommentare

Hallo Leute Ich habe eine interessante Frage. Mich wurde mal interessieren welcher Browser ist eure meinung nach der beste? ...