Forumthread: Problem mit Sverweis durch #Bezug

Problem mit Sverweis durch #Bezug

Burghard

Hallo,
ich habe diese Funktion:
=WENN(ISTFEHLER(SVERWEIS($C2;Tabelle1!$A$1:Tabelle1!$B$300;2;FALSCH));"";SVERWEIS($C2; Tabelle1!$A$1:Tabelle1!$B$300;2;FALSCH))
Wenn ich in der Tabelle1 etwas lösche, bekomme ich dann im Tabellenblatt 2 diesen Fehler:
=WENN(ISTFEHLER(SVERWEIS($C2;Tabelle1!#BEZUG!:Tabelle1!#BEZUG!;2;FALSCH));"";SVERWEIS($C2; Tabelle1!#BEZUG!:Tabelle1!#BEZUG!;2;FALSCH))
Wie kann man #Bezug vermeiden (geht glaube ich mit Index oder Indirekt)?
Hilfe wäre nett.
Schönen Gruß
Burghard

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Problem mit Sverweis durch #Bezug

13.11.2011 01:56:36

kstorz

Hallo Burghard!
Was willst Du mit dieser Formel anstellen?
Wenn ich mich nicht täusche, dann willst Du, wenn in C2 etwas steht (was in Tabelle1 in A1 bis B300 steht) die Zelle leer ist. Im zweiten Teil soll dann nochmal das gleiche berechnet werden ...
Wenn Du #NV vermeiden willst, dann kannst Du folgendes eingeben:
=WENN(ISTFEHLER(SVERWEIS($C2;Tabelle1!$A$1:Tabelle1!$B$300;2;FALSCH));""; ...
Wenn Du willst, dass bei einer richtigen Eingabe etwas angezeigt wird - z.B. "stimmt überein!" und wenn es nicht übereinstimmt "stimmt leider nicht!" steht, dann probier folgendes
=WENN(ISTNV(SVERWEIS($C2;Tabelle1!$A$1:$B$300;2;0));"stimmt leider nicht!";"stimmt überein!")
Ich hoffe, es war das was Du wolltest!
Viele Grüße
Klaus

AW: Problem mit Sverweis durch #Bezug

13.11.2011 02:04:29

fcs

Hallo Burghard,
du hast eine etwas komplizierte Schreibweise in deiner Formel gewählt.
Folgende Alternativen sind gebräuchlicher.
Tabelle2

	A	B	C
1	Suchwert	Spalten	Bereich
2	A001	1234	1234

Formeln der Tabelle

Zelle	Formel
B2	=WENN(ISTFEHLER(SVERWEIS($A2;Tabelle1!$A:$B;2;FALSCH)); "";SVERWEIS($A2; Tabelle1!$A:$B;2;FALSCH))
C2	=WENN(ISTFEHLER(SVERWEIS($A2;Tabelle1!$A$1:$B$300;2;FALSCH)); "";SVERWEIS($A2; Tabelle1!$A$1:$B$300;2;FALSCH))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Wenn deine Liste in Tabelle1 immer wieder geändert wird (Zeilen löschen/hinzufügen) dann solltest du die Variante mit der Spaltenschreibweise (Tabelle1!$A:$B) wählen. Diese ist robust gegen Zeilen-Löschen/-Einfügen.
Du hast scheinbar den kompletten Verweis-Zellenbereich der Formel ausgeschnitten/gelöscht. Dann kommt es natürlich zwangsläufig zum Bezug-Fehler. Statt die Zellen in Tabelle1 zu löschen solltest du nur die Werte im Zellbereich löschen.
Mit INDIREKT("Tabelle1!A1:B100") statt Tabelle1!$A$1:$B$300 könntest du das Problem zwar umgehen, aber das ist hier keine wirklich gute Lösung und mit anderen Nachteilen verbunden. Mit INDIREKT sollte man dann arbeiten, wenn man mit den gleichen Formeln Daten aus unterschiedlichen/variablen Zellbereichen auswerten möchte.
Du solltest in Zukunft etwas mehr darauf achten, dass du keine Zellen/Zellbereiche löscht, die in Formeln verwendet werden.
Gruß
Franz

AW: Problem mit Sverweis durch #Bezug

13.11.2011 09:12:29

silex1

Hallo,
=WENN(ISTNV(SVERWEIS($C2;INDEX(Tabelle1!A:A;1):INDEX(Tabelle1!B:B;300);2;FALSCH));"";SVERWEIS($C2; INDEX(Tabelle1!A:A;1):INDEX(Tabelle1!B:B;300);2;FALSCH))
VG, Rene

AW: Gelöst

14.11.2011 15:05:31

Burghard

Hallo,
vielen Dank an alle Schreiber. Ich konnte das Problem jetzt lösen! Vielen Dank!
Schönen Gruß
Burghard

Infobox / Tutorial

Lösung für das Problem mit Sverweis und #Bezug

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Formel anpassen: Um den #Bezug-Fehler zu vermeiden, solltest Du die Formel anpassen. Statt den spezifischen Bereich zu verwenden, nutze die gesamte Spalte:
```
=WENN(ISTFEHLER(SVERWEIS($C2;Tabelle1!$A:$B;2;FALSCH));"";SVERWEIS($C2;Tabelle1!$A:$B;2;FALSCH))
```
INDIREKT verwenden: Wenn Du nur bestimmte Bereiche anvisieren möchtest, kannst Du die Funktion INDIREKT nutzen. Beachte jedoch, dass dies nicht die beste Lösung ist, wenn Du häufig Zellen löschen musst:
```
=WENN(ISTFEHLER(SVERWEIS($C2;INDIREKT("Tabelle1!A1:B300");2;FALSCH));"";SVERWEIS($C2;INDIREKT("Tabelle1!A1:B300");2;FALSCH))
```
Vermeide Löschungen: Achte darauf, keine Zellen oder Zellbereiche zu löschen, die in Formeln verwendet werden. Das führt häufig zu einem ungültigen Zellbezug oder einem #Bezug-Fehler.

Häufige Fehler und Lösungen

#BEZUG! Fehler: Dieser Fehler tritt auf, wenn Du Zellen löscht, die in einer Formel referenziert werden. Um dies zu vermeiden, vermeide das Löschen von Zellen in den verwendeten Bereichen. Stattdessen solltest Du nur die Inhalte der Zellen löschen.
Ungültiger Zellbezug: Dies passiert oft, wenn die referenzierten Zellen nicht mehr existieren. Überprüfe die Formel und stelle sicher, dass die Bereiche korrekt sind.

Alternative Methoden

INDEX-Funktion: Du kannst die INDEX-Funktion benutzen, um Bereiche dynamisch zu definieren. Dies ist besonders nützlich, wenn Du mit einer variablen Anzahl von Zeilen arbeitest:
```
=WENN(ISTNV(SVERWEIS($C2;INDEX(Tabelle1!A:A;1):INDEX(Tabelle1!B:B;300);2;FALSCH));"";SVERWEIS($C2;INDEX(Tabelle1!A:A;1):INDEX(Tabelle1!B:B;300);2;FALSCH))
```
Datenüberprüfung: Setze Datenüberprüfung ein, um sicherzustellen, dass nur gültige Werte in die Zelle eingegeben werden, die Du mit SVERWEIS abgleichst.

Praktische Beispiele

Wenn in Tabelle1 etwas gelöscht wird:

=WENN(ISTFEHLER(SVERWEIS($C2;Tabelle1!$A:$B;2;FALSCH));"Nicht gefunden";SVERWEIS($C2;Tabelle1!$A:$B;2;FALSCH))

Mit einer robusten Formel:

=WENN(ISTFEHLER(SVERWEIS($C2;Tabelle1!$A:$B;2;FALSCH));"Fehler";SVERWEIS($C2;Tabelle1!$A:$B;2;FALSCH))

Tipps für Profis

Nutze die Spaltenschreibweise: Wenn Du mit SVERWEIS arbeitest, ist es oft besser, die gesamte Spalte zu referenzieren (z.B. Tabelle1!$A:$B). Dies macht Deine Formel robuster gegenüber Änderungen.
Fehlerbehandlung verbessern: Verwende ISTFEHLER oder ISTNV, um Deine Formeln gegen Fehler abzusichern und klare Fehlermeldungen anzuzeigen.

FAQ: Häufige Fragen

1. Was bedeutet #BEZUG! in Excel?

BEZUG! tritt auf, wenn eine Formel auf einen Zellbereich verweist, der nicht mehr vorhanden ist. Dies geschieht häufig, wenn Zellen gelöscht werden.

2. Wie kann ich den #BEZUG! Fehler in SVERWEIS vermeiden?
Um den #BEZUG! Fehler zu vermeiden, stelle sicher, dass die Zellen, die Du in Deinen Formeln verwendest, nicht gelöscht werden. Verwende stattdessen die gesamte Spalte oder INDEX-Funktionen für dynamische Bereiche.

Beliebteste Forumthreads (12 Monate)

Problem mit Sverweis durch #Bezug

Forumthread: Problem mit Sverweis durch #Bezug

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Lösung für das Problem mit Sverweis und #Bezug

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

BEZUG! tritt auf, wenn eine Formel auf einen Zellbereich verweist, der nicht mehr vorhanden ist. Dies geschieht häufig, wenn Zellen gelöscht werden.

Beliebteste Forumthreads (12 Monate)

Problem mit Sverweis durch #Bezug

Forumthread: Problem mit Sverweis durch #Bezug

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Lösung für das Problem mit Sverweis und #Bezug

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

BEZUG! tritt auf, wenn eine Formel auf einen Zellbereich verweist, der nicht mehr vorhanden ist. Dies geschieht häufig, wenn Zellen gelöscht werden.

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread