Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1084to1088
1084to1088
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Brett vorm Kopp? Bereich.verschieben mit Sverweis

Forumthread: Brett vorm Kopp? Bereich.verschieben mit Sverweis

Brett vorm Kopp? Bereich.verschieben mit Sverweis
Jörg-HH
Hi Leute
vielleicht is die Sonne heut bißchen dolle....?
mit
=BEREICH.VERSCHIEBEN(P16;3;0)
bekomme ich das angezeigt, was ich erwarte: Die Zelle 3 Zeilen unterhalb P16
Die Formel =SVERWEIS(UmfgBog1;FG_Werte;14;0) ergibt den Wert aus P16. Auch ok.
Ersetze ich aber P16 durch den Sverweis, krieg ich ne Fehlermeldung:
=BEREICH.VERSCHIEBEN(SVERWEIS(UmfgBog1;FG_Werte;14;0);3;0)
Die Sverweis-Formel allein ergibt aber genau den Wert in P16, wie erwartet.
Was is'n Böses daran, wenn ich 'n Sverweis mit Bereich.verschieben kombiniere ;-) ?
Danke für'n Wegweiser zum nächsten Brillenhändler...
Jörg
Anzeige
AW: Brett vorm Kopp? Bereich.verschieben mit Sverweis
01.07.2009 12:14:04
Rudi
Hallo,

ergibt den Wert aus P16


Aber eben nicht die Zelle P16.
Gruß
Rudi

ADRESSE oder INDIREKT
01.07.2009 12:20:35
Jörg-HH
Hi Rudi
habs mir Adresse oder indirekt versucht - geht auch nicht. Was liefert mir denn aus einem Verweis den Bezug?
Grüße Jörg
INDIREKT ist doch die gesuchte Lösung, oder ?
01.07.2009 12:27:37
NoNet
Hallo Jörg,
m.E. hast Du Dir das "Brett vor'm Kopf" schon selbst entfernt :
=BEREICH.VERSCHIEBEN(INDIREKT(SVERWEIS(UmfgBog1;FG_Werte;14;0));3;0)
klappt doch, oder ?
Gruß, NoNet
Anzeige
ergibt #BEZUG
01.07.2009 12:53:53
Jörg-HH
Hi NoNet, das ergibt 'n Fehler. Kann mir aber auch nicht recht erklären, wieso.
AW: ADRESSE oder INDIREKT
01.07.2009 12:31:32
Rudi
Hallo,
=Index(Ergebnisspalte;Vergleich(Kriterium;Suchspalte;0)+Verschiebung)
Gruß
Rudi
das klappt...
01.07.2009 12:56:10
Jörg-HH
Hi Rudi, ganz anderer Ansatz, aber geht.
Was war denn falsch bei der Version mit Bereich.verschieben - gibts da ne Lösung mit Indirekt oder so?
Anzeige
AW: das klappt...
01.07.2009 13:05:51
Rudi
Hallo,
nein, keine Lösung mit INDIREKT.
Sverweis gibt einen Wert und keinen Bereich zurück. und für Bereich.Verschieben brauchst du, wie der Name schon sagt, einen Bereich.
Bereich.Verschieben(Indirekt(Sverweis(....));3;;;) würde nur dann keinen Fehler bringen, wenn der Sverweis eine Zellbezeichnung (A1...IV65533) als Ergebnis hätte.
Gruß
Rudi
Anzeige
hmm... doch noch 'n Problem...
01.07.2009 13:07:45
Jörg-HH
Vergleich geht doch nur mit einspaltiger Matrix, oder? Ich will mich aber nicht auf die Spalte festlegen, sondern die erste Spalte in einem rechteckigen Bereich. Da krieg ich mit Vergleich n Fehler, Rudi...
:-(
AW: hmm... doch noch 'n Problem...
01.07.2009 13:20:30
David
Hallo Jörg,
wie wäre es mit einer Beispiel-Tabelle, an der man die Formel testen könnte?
Gruß
David
Anzeige
AW: hmm... doch noch 'n Problem...
01.07.2009 13:20:32
Rudi
Hallo,
dann musst du die Matrix per Bereich.verschieben auf eine Spalte reduzieren.
=Index(Bereich.Verschieben(FG_Werte;;13;;1);Vergleich(UmfgBog1;Bereich.Verschieben(FG_Werte;;;;1);0))
Gruß
Rudi
genial...
01.07.2009 14:03:30
Jörg-HH
...also, Rudi - daß man per Bereich.verschieben auch Höhe und Breite verändern kann, hab ich ja auch irgendwann mal mitbekommen - aber das verwendet man so selten, daß man es im entscheidenden Moment nicht auf der Pfanne hat...
Danke für die Gehhilfe!
;-)
ach so - noch 'ne Frage: hier gibt's ja einige witzige Pseudonyme, denke da zB an D.Saster
heißt du eigentlich wirklich Rudi Maintaire oder ist das eine Verballhornung der Vokabel "rudimentär"?
Anzeige
AW: genial... und dann gibt es...
01.07.2009 14:23:45
Rudi 

Danke für die Gehhilfe!


manchmal noch K.Rola, L.Vira, Klaus Trofobie, N.Riko, D.Menz, G.Hilfe;-), ...
Aber eigentlich heiße ich Jaques-Yves.
Gruß
Rudi

AW: genial... und dann gibt es...
01.07.2009 14:27:58
selli
hallo rudi,
ich hätte da auch noch einen zum besten zu geben: CLAUDE ECKEL
gruß selli
Anzeige
@Rudi : Und was ist mit all den anderen...
01.07.2009 14:36:34
NoNet
...netten Menschen ? - Sollen die alle unerwähnt bleiben ?
Karl Auer
Dennis Ball
Penny Bell
Anna Bolika
Theo Dohr
Mel Dung
Klara Fall
Martin Gans
Ernst Haft
Niko Laus
Vita Mine
Anna Nass
Mario Nette
Heinz Null
Martha Pfahl
Frank Reich
Hella Stern
Bonni Tät
Moni Tor
Hans Wurst
Reiner Zufall
Gruß, No Net ;-)
Anzeige
AW: @Rudi : Und was ist mit all den anderen...
01.07.2009 14:57:13
Rudi
stimmt!
Axel Schweiß und Rosa Slip nicht zu vergessen.
Gruß
Rudi
Den Letzten kenne ich,...
02.07.2009 15:05:41
Luc:-?
...NoNet;
der schreibt sich allerdings Zoufal... ;-)
Gruß Luc :-?
PS...
01.07.2009 13:17:49
Jörg-HH
Rudi, ich kann mich deshalb nicht auf Spalten beziehen, weil ich die Zahlen von einem anderen Blatt aus holen will. Dafür könnte ich natürlich sagen ...Tabelle2!B:B... usw - dummerweise ändert das Blatt Tabelle2 mit jedem Vorgang seinen Namen. Und bevor ich nun den Klimmzug mache, das in eine Formel einzubauen, dachte ich, es müsse einen einfacheren Weg geben...
Grüße Jörg
Anzeige
Anzeige
Anzeige

Infobox / Tutorial

Bereich verschieben mit SVERWEIS und INDIREKT in Excel

Schritt-für-Schritt-Anleitung

  1. Verwende die Formel BEREICH.VERSCHIEBEN: Um einen Bereich in Excel zu verschieben, kannst du die Funktion BEREICH.VERSCHIEBEN verwenden. Die Grundstruktur sieht so aus:

    =BEREICH.VERSCHIEBEN(Startzelle; Zeilenverschiebung; Spaltenverschiebung)

    Beispiel:

    =BEREICH.VERSCHIEBEN(P16; 3; 0)

    Diese Formel gibt dir den Wert aus der Zelle, die drei Zeilen unter P16 liegt.

  2. Kombinieren mit SVERWEIS: Wenn du die Funktion SVERWEIS verwenden möchtest, um einen Wert zu erhalten, der dann in BEREICH.VERSCHIEBEN genutzt werden kann, musst du sicherstellen, dass die Rückgabe von SVERWEIS ein Zellbezug ist. Beispiel:

    =BEREICH.VERSCHIEBEN(INDIREKT(SVERWEIS(UmfgBog1; FG_Werte; 14; 0)); 3; 0)

  3. Verwendung von INDIREKT: INDIREKT ist nötig, weil SVERWEIS einen Wert zurückgibt und nicht direkt einen Bereich. Achte darauf, dass der Rückgabewert von SVERWEIS eine Zelle in Form eines Textes ist (z.B. "P16").

Häufige Fehler und Lösungen

  • Fehler: #BEZUG!
    Wenn du einen #BEZUG!-Fehler erhältst, liegt das daran, dass BEREICH.VERSCHIEBEN nicht auf einen gültigen Bereich zugreift. Stelle sicher, dass der Rückgabewert von SVERWEIS ein gültiger Zellbezug ist.

  • Fehler bei der Verwendung von SVERWEIS:
    Wenn SVERWEIS nicht den erwarteten Wert zurückgibt, überprüfe die Syntax:

    =SVERWEIS(Suchkriterium; Suchmatrix; Spaltenindex; [Bereich_Verweis])

Alternative Methoden

Wenn die Kombination von BEREICH.VERSCHIEBEN und SVERWEIS nicht funktioniert, gibt es alternative Methoden:

  • INDEX und VERGLEICH: Eine häufige Alternative ist die Verwendung von INDEX in Kombination mit VERGLEICH, um einen Wert zu finden und dann den gewünschten Bereich zu verschieben:

    =INDEX(Ergebnisspalte; VERGLEICH(Kriterium; Suchspalte; 0) + Verschiebung)

  • Verwendung von ADRESSE: Du kannst auch die Funktion ADRESSE verwenden, um einen Zellbezug zu erstellen, wenn du mit dynamischen Bereichen arbeitest.

Praktische Beispiele

  1. Beispiel mit BEREICH.VERSCHIEBEN und SVERWEIS: Angenommen, du hast eine Tabelle mit Verkaufszahlen und möchtest die Verkäufe für das nächste Quartal prognostizieren:

    =BEREICH.VERSCHIEBEN(INDIREKT(SVERWEIS(A1; Verkäufe; 2; FALSCH)); 3; 0)

  2. Beispiel mit INDEX und VERGLEICH: Du kannst auch die Verkaufszahlen wie folgt abrufen:

    =INDEX(Verkäufe; VERGLEICH(A1; Produkte; 0) + 3)

Tipps für Profis

  • Dynamische Bereiche: Nutze BEREICH.VERSCHIEBEN, um dynamische Bereiche zu erstellen, die sich automatisch anpassen, wenn Daten hinzugefügt oder entfernt werden.
  • Namen für Bereiche: Benenne deine Bereiche in Excel, um die Lesbarkeit deiner Formeln zu verbessern und Fehler zu vermeiden.
  • Vermeide zu viele verschachtelte Funktionen: Halte deine Formeln einfach und vermeide tief verschachtelte Funktionen, um die Übersichtlichkeit zu wahren.

FAQ: Häufige Fragen

1. Kann ich BEREICH.VERSCHIEBEN mit mehr als zwei Funktionen kombinieren?
Ja, du kannst BEREICH.VERSCHIEBEN mit mehreren Funktionen kombinieren, solange die Rückgabewerte korrekt sind.

2. Was ist der Unterschied zwischen SVERWEIS und INDEX?
SVERWEIS sucht den Wert in der ersten Spalte einer Matrix und gibt einen Wert aus der gleichen Zeile zurück. INDEX kann beliebige Zeilen und Spalten ansprechen, was es flexibler macht.

3. Funktioniert INDIREKT auch mit Namen für Bereiche?
Ja, INDIREKT kann verwendet werden, um auf benannte Bereiche zuzugreifen, was die Handhabung von dynamischen Daten erleichtert.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige