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

Forumthread: SVERWEIS und BEREICH.VERSCHIEBEN

SVERWEIS und BEREICH.VERSCHIEBEN
23.12.2005 15:37:55
Hans
Moin,
folgendes Problem stellt sich mir:
In einem Sheet (Tabelle_1) habe ich einen gegebenen Wert. Dieser Wert steht ebenfalls in einem anderen Sheet (Tabelle_2).
In Tabelle_1 soll nun der Wert per Formel eingetragen werden, der drei Spalten weiter rechts und eine Zeile über dem Suchwert in Tabelle_2 steht.
Für eine Lösung ohne VBA bis ich sehr dankbar.
mit vorweihnachtlichem Gruß
Hans
Anzeige

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SVERWEIS und BEREICH.VERSCHIEBEN
23.12.2005 16:06:09
Josef
Hallo Hans!
Vieleicht so?
Tabelle_1
 AB
1Wert 17Dummy 16/3
Formeln der Tabelle
B1 : =BEREICH.VERSCHIEBEN(Tabelle_2!A3;VERGLEICH(A1;Tabelle_2!A3:A40;0)-2;3;1;1)
 
Tabelle_2
 ABCDE
1     
2     
3Wert 1Dummy 1/1Dummy 1/2Dummy 1/3 
4Wert 2Dummy 2/1Dummy 2/2Dummy 2/3 
5Wert 3Dummy 3/1Dummy 3/2Dummy 3/3 
6Wert 4Dummy 4/1Dummy 4/2Dummy 4/3 
7Wert 5Dummy 5/1Dummy 5/2Dummy 5/3 
8Wert 6Dummy 6/1Dummy 6/2Dummy 6/3 
9Wert 7Dummy 7/1Dummy 7/2Dummy 7/3 
10Wert 8Dummy 8/1Dummy 8/2Dummy 8/3 
11Wert 9Dummy 9/1Dummy 9/2Dummy 9/3 
12Wert 10Dummy 10/1Dummy 10/2Dummy 10/3 
13Wert 11Dummy 11/1Dummy 11/2Dummy 11/3 
14Wert 12Dummy 12/1Dummy 12/2Dummy 12/3 
15Wert 13Dummy 13/1Dummy 13/2Dummy 13/3 
16Wert 14Dummy 14/1Dummy 14/2Dummy 14/3 
17Wert 15Dummy 15/1Dummy 15/2Dummy 15/3 
18Wert 16Dummy 16/1Dummy 16/2Dummy 16/3 
19Wert 17Dummy 17/1Dummy 17/2Dummy 17/3 
20Wert 18Dummy 18/1Dummy 18/2Dummy 18/3 
21Wert 19Dummy 19/1Dummy 19/2Dummy 19/3 
22Wert 20Dummy 20/1Dummy 20/2Dummy 20/3 
23Wert 21Dummy 21/1Dummy 21/2Dummy 21/3 
24Wert 22Dummy 22/1Dummy 22/2Dummy 22/3 
25Wert 23Dummy 23/1Dummy 23/2Dummy 23/3 
26Wert 24Dummy 24/1Dummy 24/2Dummy 24/3 
27Wert 25Dummy 25/1Dummy 25/2Dummy 25/3 
28Wert 26Dummy 26/1Dummy 26/2Dummy 26/3 
29Wert 27Dummy 27/1Dummy 27/2Dummy 27/3 
30Wert 28Dummy 28/1Dummy 28/2Dummy 28/3 
31Wert 29Dummy 29/1Dummy 29/2Dummy 29/3 
32Wert 30Dummy 30/1Dummy 30/2Dummy 30/3 
33Wert 31Dummy 31/1Dummy 31/2Dummy 31/3 
34Wert 32Dummy 32/1Dummy 32/2Dummy 32/3 
35Wert 33Dummy 33/1Dummy 33/2Dummy 33/3 
36Wert 34Dummy 34/1Dummy 34/2Dummy 34/3 
37Wert 35Dummy 35/1Dummy 35/2Dummy 35/3 
38Wert 36Dummy 36/1Dummy 36/2Dummy 36/3 
39Wert 37Dummy 37/1Dummy 37/2Dummy 37/3 
40Wert 38Dummy 38/1Dummy 38/2Dummy 38/3 
41     
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
'******************************
'* Gruß Sepp
'*
'* Rückmeldung wäre nett!
'******************************

Anzeige
AW: SVERWEIS und BEREICH.VERSCHIEBEN
23.12.2005 17:21:27
Hans
Hallo Sepp,
hat funktioniert - Danke!!!
Ein frohes Weihnachtsfest und alles Gute fürs neue Jahr!
Gruß
Hans
;

Forumthreads zu verwandten Themen

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 und BEREICH.VERSCHIEBEN in Excel


Schritt-für-Schritt-Anleitung

Um in Excel einen Wert aus einem anderen Sheet zu ziehen, der sich drei Spalten rechts und eine Zeile über dem Suchwert befindet, kannst du die Kombination von SVERWEIS und BEREICH.VERSCHIEBEN verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

  1. Öffne Deine Excel-Datei und gehe zu dem Sheet, in dem du den Wert abfragen möchtest (z.B. Tabelle_1).

  2. Wähle die Zelle aus, in der das Ergebnis erscheinen soll (z.B. B1).

  3. Gib die folgende Formel ein:

    =BEREICH.VERSCHIEBEN(Tabelle_2!A3;VERGLEICH(A1;Tabelle_2!A3:A40;0)-2;3;1;1)

    Diese Formel sucht den Wert in A1 von Tabelle_1, sucht ihn in der Spalte A von Tabelle_2 und gibt den Wert drei Spalten rechts und eine Zeile darüber zurück.

  4. Drücke Enter, um das Ergebnis zu sehen.


Häufige Fehler und Lösungen

  • Fehler: #NV

    • Lösung: Stelle sicher, dass der Suchwert in der angegebenen Range vorhanden ist. Überprüfe auch den Bereich, der im SVERWEIS verwendet wird.
  • Fehler: #BEZUG!

    • Lösung: Dies kann auftreten, wenn die Referenz in der BEREICH.VERSCHIEBEN-Formel ungültig ist. Überprüfe, ob die angegebenen Zeilen und Spalten korrekt sind.
  • Fehler: #WERT!

    • Lösung: Überprüfe die Eingaben in der Formel. Möglicherweise sind falsche Zellreferenzen oder Datentypen verwendet worden.

Alternative Methoden

Falls du eine andere Methode zum Abrufen von Daten aus einem anderen Bereich verwenden möchtest, kannst du auch folgende Optionen in Betracht ziehen:

  • INDEX und VERGLEICH: Diese Kombination erlaubt es dir, flexibler mit dem Zugriff auf Daten zu arbeiten. Ein Beispiel wäre:

    =INDEX(Tabelle_2!A1:E40;VERGLEICH(A1;Tabelle_2!A1:A40;0)-1;4)
  • XVERWEIS (in neueren Excel-Versionen verfügbar): Eine modernere Alternative zu SVERWEIS, die einfacher zu verwenden und flexibler ist.


Praktische Beispiele

Hier sind einige Beispiele, wie du die Formeln in verschiedenen Szenarien verwenden kannst:

  1. Beispiel für BEREICH.VERSCHIEBEN: Angenommen, du hast in Tabelle_2 folgende Daten:

    • A3: Wert 1
    • B3: Dummy 1/1
    • C3: Dummy 1/2
    • D3: Dummy 1/3

    Wenn du in Tabelle_1 in Zelle A1 "Wert 1" eingibst, gibt die Formel in B1 den Wert "Dummy 1/3" zurück.

  2. Beispiel für SVERWEIS: Wenn du den Wert "Wert 2" suchst und den entsprechenden Dummy-Wert aus Tabelle_2 zurückgeben möchtest:

    =SVERWEIS("Wert 2";Tabelle_2!A1:E40;4;FALSCH)

Tipps für Profis

  • Nutze benannte Bereiche, um die Formeln übersichtlicher zu gestalten. So kannst du anstelle von Zellreferenzen wie Tabelle_2!A3:A40 einfach Suchbereich verwenden.

  • Verwende die Funktion WENNFEHLER, um eine benutzerdefinierte Fehlermeldung anzuzeigen, falls die Suche keinen Treffer ergibt:

    =WENNFEHLER(BEREICH.VERSCHIEBEN(...); "Wert nicht gefunden")
  • Halte deine Daten sauber und gut strukturiert, um die Nutzung von SVERWEIS und BEREICH.VERSCHIEBEN zu optimieren.


FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen SVERWEIS und BEREICH.VERSCHIEBEN? SVERWEIS wird verwendet, um Werte basierend auf einer Suchbedingung abzurufen, während BEREICH.VERSCHIEBEN es dir ermöglicht, einen bestimmten Bereich relativ zu einer Referenz zu verschieben.

2. Kann ich BEREICH.VERSCHIEBEN auch ohne SVERWEIS verwenden? Ja, BEREICH.VERSCHIEBEN kann eigenständig verwendet werden, um Daten in einer Tabelle zu referenzieren, ohne eine Suchkriterium zu benötigen.

3. In welchen Excel-Versionen sind diese Funktionen verfügbar? Beide Funktionen sind in den meisten modernen Excel-Versionen verfügbar, einschließlich Excel 2010 und neuer.

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