Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: SVERWEIS mit mehreren Tabellenblättern/ Tabellen

SVERWEIS mit mehreren Tabellenblättern/ Tabellen
28.08.2018 15:22:11
Jaschman
Hallo zusammen,
ich habe derzeit ein kleines Excel-Problem, bei dem ich partout nicht weiterkommen.
Ich habe die entsprechende Datei unter folgendem Link einmal zur Verfügung gestellt: https://www.herber.de/bbs/user/123617.xlsx
Ich habe befüllte Tabellenblätter (bis 25.000 EUR, bis 50.000 EUR, bis 100.000 EUR, bis 150.000 EUR, bis 200.000 EUR, ab 200.000 EUR), in denen jeweils mehrere Tabellen für die verschiedenen Laufzeiten enthalten sind. Die einzelnen Tabellen wieder rum die Kriterien Rating und Besicherung.
Ich möchte nun auf dem Tabellenblatt "Tabelle1" durch einfache Angaben (Nominalbetrag (freier Eingabewert), Zinsbindung (Dropdown), Rating (Dropdown), Besicherung (Dropdown)) die entsprechende Kondition angezeigt bekommen.
Leider habe ich gar keinen Schimmer wie ich dieses anstellen soll, vll. kann einer von euch mich hierbei tatkräftig unterstützen.
Vorab schon mal vielen Dank!
Jaschman
Anzeige

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: realiserbar u.a. mit INDEX(), VERGLEICH() ...
29.08.2018 13:25:08
neopa
Hallo Jaschmann,
... und noch einigen anderen Funktionen.
Möglich wäre dies aber sicherlich nur mit einer Monster-Formel. Allerdings dies auch nur mit unnötig hohen Aufwand.
Einfacher wäre es eine Formel aufzustellen, wenn Deine Datenstruktur abgeändert würde. Allerdings bedarf es auch dazu Aufwand, um diese herzustellen. Aber wer will solchen schon freiwillig betreiben.
Gruß Werner
.. , - ...
Anzeige
AW: realiserbar u.a. mit INDEX(), VERGLEICH() ...
29.08.2018 13:49:14
Jaschman
Moin Werner,
danke für deinen Beitrag.
Ja, eine Monsterformel habe ich schon mal gebastelt, habe diese aber schnell wieder über den Haufen geworfen, da diese zu unübersichtlich wurde.
Alternativ hätte ich alle Daten noch in einer Tabelle, bringt einen das voran? Kann dann eine entsprechende Abfrage gestaltet werden. (Also alle Daten in einer Tabelle ohne Verteilung auf mehrere Tabellenblätter und Tabellen)?
Beste Grüße
Jaschman
Anzeige
AW: SVERWEIS mit mehreren Tabellenblättern/ Tab
29.08.2018 13:45:41
Jaschman
Niemand eine Idee =(?
Oder zu unkonkret beschrieben? Fällt mir gerade etwas schwer das Ganze zu beschrieben, sonst auch sehr gerne nachfrage.
Vielen Dank vorab.
AW: die Daten alle in einem Tabellenblatt ...
29.08.2018 13:59:35
neopa
Hallo Jaschman,
.. das wäre schon mal hilfreicher. Weiterhin wäre es sehr sinnvoll als Datenwerte in der Zeile "Rating/Besicherung" lediglich den "bis-"Wert zu schreiben. Also 1 4 7 ... Übrigens was ist/soll "15C"?
Auch die Laufzeiten sollten als Zahlenwerte vorhanden sein. Die Darstellung "bis" #"Jahre" könnte durch ein benutzerdefiniertes Zahlenformat erzeugt werden. Gleiches analog zu den Nominalbetragsangaben.
Gruß Werner
.. , - ...
Anzeige
AW: die Daten alle in einem Tabellenblatt ...
29.08.2018 14:25:10
Jaschman
Hallo Werner,
ich habe nun ein paar Anpassungen vorgenommen.
Kommt das in etwa mit deinen Vorstellungen hin?
Die 15 C ist eine Feingliederung eines Ratings, ist hier aber nicht weiter von Bedeutung und habe ich in der neuen Datei in die "bis-Werte" bei der 17 mit einfließen lassen.
Kann man aus deiner Sicht bei dem neuen Datenbestand eine Abfrage erstellen?
https://www.herber.de/bbs/user/123644.xlsx
Beste Grüße
Jaschman
Anzeige
AW: jetzt wäre u.a. noch zu klären ...
29.08.2018 14:48:03
neopa
Hallo Jaschman,
... welcher Wert zu ermitteln wäre, wenn die Zinsbindung nicht exakt den Tabellenwerten entspricht. Denn Du hast in Deinem ursprüngl. Tabelle1!B2 auch Jahre 2,3,4 ... auswählbar.
Allerdings sind Deine Zinsdaten momentan sowieso alle identisch und in C28:H28 stehen noch falsche Daten.
Gruß Werner
.. , - ...
Anzeige
AW: jetzt wäre u.a. noch zu klären ...
29.08.2018 14:57:32
Jaschman
Hallo Werner,
korrekt, C:28 - H:28 habe ich wohl übersehen, Schande über mein Haupt, habe ich angepasst:)
Genau, also gesucht wird immer der jeweilige Zinssatz. Die Daten die belegt werden können im Dropdown sind, wie du richtigerweise schon angemerkt hast, auch Daten die in den Tabellen nicht vorkommen. Deshalb hatte ich im ersten Entwurf immer "bis" geschrieben.
Also wenn der Nominalbetrag 27.000 EUR beträgt, Laufzeit 6 Jahre, Rating 3, Besicherung bis 60% des BLW, dann sollte der zugehörige Zinssatz L:21 sein.
Welchen Ansatz würdest du wählen?
Beste Grüße
Jascha
Anzeige
AW: dann z.B. mit folgender Formel ...
29.08.2018 15:51:43
neopa
Hallo Jascha,
.... wobei ich in Deiner neuen Datei den Tabellenblattnamen Tabelle1 umbenannt in "Daten" und Deine ursprüngliches Ermittlungstabellenblatt Tabelle1 in die neue Datei einkopiert habe. In dieser dann in B5:
=INDEX(Daten!A:AZ;WENN(B2="variabel";2;AGGREGAT(15;6;ZEILE(Daten!C4:C29)/
(Daten!B4:Daten!B29="Laufzeit ")/(Daten!C4:Daten!C29>=B2);1))+2+VERGLEICH(B4;Daten!B5:B8;0);
AGGREGAT(15;6;SPALTE(Daten!C1:AQ1)/ISTZAHL(Daten!C1:Daten!AQ1)/(Daten!C1:Daten!AQ1>=B1);1)
+AGGREGAT(15;6;SPALTE(Daten!C4:H4)/(Daten!C4:Daten!H4>=B3)-3;1))
Gruß Werner
.. , - ...
Anzeige
AW: ergänzende Anmerkung ...
29.08.2018 16:07:20
neopa
Hallo Jascha,
... in der Formel vergleiche ich mit "Laufzeit " (also einem dem maßgeblichen Text nachgestellten Leerzeichen. Besser wäre es, Du würdest in Daten!A:A nach "Laufzeit " suchen und dies durch "Laufzeit" ersetzen und in der Formel mit "Laufzeit" vergleichen.
Gruß Werner
.. , - ...
Anzeige
AW: jetzt wäre u.a. noch zu klären ...
30.08.2018 08:45:35
Jaschman
Hallo Werner,
ich kann die Formel und den Ansatz nun nachvollziehen, der ist in meinen Augen auch logisch. Allerdings bekomme ich noch keine Ausgabe nach Eingabe der oben genannten Formel.
Kannst du noch mal rüber schauen, was ich falsch gemacht habe:(
Vielen Dank für deine Unterstützung.
https://www.herber.de/bbs/user/123653.xlsx
Beste Grüße
Jaschman
Anzeige
AW: Formel einkopieren und % Format zuweisen ...
30.08.2018 09:33:19
neopa
Hallo Jascha,
... hast Du meine eingestellte Formel nicht kopiert und eingefügt? Dies so getan, ergibt dies jedenfalls das richtige Ergebnis.
Gruß Werner
.. , - ...
AW: Formel einkopieren und % Format zuweisen ...
30.08.2018 10:54:03
Jaschman
Hallo Werner,
da hat sich ein Kopierfehler eingeschlichen...
Ich bin begeistert, genau wie ich es mir erwünscht habe....überragend.
Vielen Dank für deine Unterstützung.
Beste Grüße
Jascha
Anzeige
AW: Formel einkopieren und % Format zuweisen ...
30.08.2018 11:29:38
Jaschman
Hallo Werner,
ich habe doch noch eine ergänzende Frage: Warum findet er keine Zinsbindungen größer 10 Jahre?
bis einschließlich 10 Jahre funktioniert die Formel einwandfrei, ab 11 Jahren ergibt sich der Fehler #Zahl.
Beste Grüße
Jaschman
AW: ersetze in der Formel alle 29 durch 40 owT
30.08.2018 11:55:32
neopa
Gruß Werner
.. , - ...
AW: Vielen Dank für alles!!!
30.08.2018 11:59:28
Jaschman
Beste Grüße
Jaschman
Anzeige
AW: bitteschön owT
30.08.2018 14:24:57
neopa
Gruß Werner
.. , - ...
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

SVERWEIS über mehrere Tabellenblätter in Excel


Schritt-für-Schritt-Anleitung

Um mit dem SVERWEIS über mehrere Tabellenblätter in Excel zu arbeiten, folge diesen Schritten:

  1. Datenstruktur vorbereiten: Stelle sicher, dass deine Daten in allen relevanten Tabellenblättern einheitlich strukturiert sind. Zum Beispiel sollten die Spaltenüberschriften übereinstimmen.

  2. SVERWEIS-Formel schreiben: Verwende die SVERWEIS-Formel, um Werte aus anderen Tabellenblättern zu beziehen. Die allgemeine Struktur lautet:

    =SVERWEIS(Suchkriterium; Bereich; Spaltenindex; [Bereich_Verweis])
  3. Bereiche definieren: Wenn du mehrere Tabellenblätter durchsuchen möchtest, musst du die Bereiche für jedes Blatt definieren. Ein Beispiel für eine Formel, die über mehrere Blätter sucht:

    =SVERWEIS(A1;Tabelle1!A:B;2;FALSCH)
  4. Verschachtelung verwenden: Um über mehrere Tabellenblätter zu suchen, kannst du die SVERWEIS-Funktion in andere SVERWEIS-Formeln einbetten. Zum Beispiel:

    =WENNFEHLER(SVERWEIS(A1;Tabelle1!A:B;2;FALSCH); SVERWEIS(A1;Tabelle2!A:B;2;FALSCH))
  5. Anpassungen vornehmen: Je nach deinen Anforderungen kannst du die Formel weiter anpassen, um spezifische Bedingungen zu berücksichtigen.


Häufige Fehler und Lösungen

  • Fehler #NV: Dieser Fehler tritt auf, wenn der gesuchte Wert nicht gefunden werden kann. Überprüfe, ob der Wert tatsächlich in der Tabelle vorhanden ist.

  • Falsche Bereichsangaben: Achte darauf, dass der Suchbereich korrekt angegeben ist. Es sollte die gesamte Spalte oder der entsprechende Zellbereich sein.

  • Formatierungsprobleme: Manchmal können Unterschiede in der Formatierung (z. B. Text vs. Zahl) zu Problemen führen. Stelle sicher, dass die Formate übereinstimmen.


Alternative Methoden

Falls SVERWEIS nicht die gewünschten Ergebnisse liefert, kannst du folgende Alternativen nutzen:

  • INDEX und VERGLEICH: Diese Funktionen bieten mehr Flexibilität und ermöglichen eine Suche auch in mehreren Tabellenblättern.

    Beispiel:

    =INDEX(Tabelle1!B:B;VERGLEICH(A1;Tabelle1!A:A;0))
  • SUMMEWENN oder SUMMEWENNS: Wenn du Summen aus mehreren Tabellenblättern berechnen möchtest, verwende:

    =SUMMEWENN(Tabelle1!A:A;Kriterium;Tabelle1!B:B) + SUMMEWENN(Tabelle2!A:A;Kriterium;Tabelle2!B:B)

Praktische Beispiele

  1. SVERWEIS mit zwei Tabellenblättern: Angenommen, du hast die Tabellenblätter "Tabelle1" und "Tabelle2" mit identischen Strukturen. Du kannst so suchen:

    =WENNFEHLER(SVERWEIS(A1;Tabelle1!A:B;2;FALSCH);SVERWEIS(A1;Tabelle2!A:B;2;FALSCH))
  2. Summe aus mehreren Tabellenblättern mit SVERWEIS: Um die Gesamtwerte aus verschiedenen Blättern zu summieren, kombiniere die SUMME- und SVERWEIS-Funktionen:

    =SUMME(SVERWEIS(A1;Tabelle1!A:B;2;FALSCH); SVERWEIS(A1;Tabelle2!A:B;2;FALSCH))

Tipps für Profis

  • Verwende benannte Bereiche: Das Erstellen von benannten Bereichen für deine Tabellenblätter kann die Lesbarkeit deiner Formeln verbessern.

  • Daten konsolidieren: Wenn möglich, konsolidiere deine Daten in einem einzigen Tabellenblatt, um die Handhabung zu erleichtern.

  • Dynamische Bereiche: Nutze die OFFSET-Funktion, um dynamische Bereiche zu erstellen, die sich anpassen, wenn neue Daten hinzugefügt werden.


FAQ: Häufige Fragen

1. Wie kann ich mehrere Tabellenblätter mit einer Formel durchsuchen? Du kannst die SVERWEIS-Funktion in Kombination mit WENNFEHLER verwenden, um mehrere Tabellenblätter zu durchsuchen.

2. Was tun, wenn ich einen Fehler bei der Ausgabe bekomme? Überprüfe die Eingabewerte und die Bereiche, die du in der SVERWEIS-Formel angegeben hast. Stelle sicher, dass die Daten korrekt formatiert sind und dass der gesuchte Wert vorhanden ist.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige