Frage zu sverweis

[Aufdienasefliegen]

Hallo zusammen
Ich habe in meinem Berechnungstool folgende Formel verwendet

=SVERWEIS(C$156;$Motordaten.A$9:Y$318;8)

Der Datenbereich in der Tabelle "Motordaten" beginnt mit der Zelle A9 endet aber bereits bei z.B. Y250.
so sollte ich doch 68 Zeilen einfügen können ohne an der Formel etwas ändern zu müssen.

1) Was bedeutet das $ Zeichen? Ich dachte, dass damit die 9 resp die 318 nicht selbstständig geändert werden wenn ich eben in der Tabelle Motordaten neue Zeilen einfüge .

Wenn ich nun aber in der Tabelle "Motordaten" neue Zeilen einfüge dann verändern sich eben genau diese beiden Werte. Meine Formel lautet dann z.B. plötzlich
=SVERWEIS(C$156;$Motordaten.A$68:Y$350;8)
Was mache ich falsch?

Vielen Dank und Grüsse Heinzi

[echo]

Hallo zusammen

Ich habe in meinem Berechnungstool folgende Formel verwendet
=SVERWEIS(C$156;$Motordaten.A$9:Y$318;8)

Beachte das Sverweis() einen 4. Parameter (Falsch/Wahr) hat. Das Ergebnis stimmt nicht immer, wenn
einerseits die Quelle nicht aufsteigend sortiert wird und
andererseits der Parameter nicht auf FALSCH oder 0 eingestellt wird!
Beispiel: =SVERWEIS(C$156;$Motordaten.A$9:Y$318;8;0)

1) Was bedeutet das $ Zeichen? Ich dachte, dass damit die 9 resp die 318 nicht selbstständig geändert werden wenn ich eben in der Tabelle Motordaten neue Zeilen einfüge .

Das $-Zeichen bedeutet das die Zeile einen absoluten Bezug hat der sich beim kopieren der Formel nach unten nicht ändert. Ohne $ wird beim kopieren / einfügen aus A9:Y318 in der nächsten Zeile A10:Y319
Das gleiche gilt für die Spalte ohne $ beim kopieren nach rechts
Markiere den Bezug mal in der Bearbeitungsleiste und betätige (mehrfach) F4 dann werden dir alle Versionen angezeigt

Wenn ich nun aber in der Tabelle "Motordaten" neue Zeilen einfüge dann verändern sich eben genau diese beiden Werte. Meine Formel lautet dann z.B. plötzlich
=SVERWEIS(C$156;$Motordaten.A$68:Y$350;8)
Was mache ich falsch?

Ist der 4. Parameter vom SVerreis auf FALSCH oder 0 eingestellt kannst du neue Daten einfach ans Ende der Liste hinzufügen.
Dann verändert sich der Bezug auch nicht

Gruß Holger

[Aufdienasefliegen]

Hallo Holger
Vielen Dank für deine Erklärungen.

zuerst muss ich mich ein wenig korrigieren.
Beim Einfügen neuer Zeilen im Blatt "Motordaten" ändert sich an der Formel: =SVERWEIS(C$156;$Motordaten.A$9:Y$318;8) nur der Beginn des Datenbereichs A$9. Die Formel lautete danach =SVERWEIS(C$156;$Motordaten.A$68:Y$318;8) Das Ende Y$318 blieb listigerweise unverändert.

Wenn ich dich richtig verstanden habe, dann sollte ich ;0 noch hinten anhängen damit ich neue Daten am Schluss einfügen kann. Wie ist das zu verstehen? Ich kann dann in Zeile 319 neue Daten aufnehmen und das wird dann automatisch in die Formel übernommen?

Ich müsste/möchte die neue Zeile aber irgendwo oberhalb einfügen, weil mein Blatt "Motordaten" quasi in drei Abschnitte unterteilt ist und ich von der Sortierfunktion Abstand nehmen möchte.

Und das $ Zeichen bezieht sich nur auf das Kopieren und Schieben innerhalb des eigenen Blattes, so kannte ich das bereits. Gibt es eine Möglichkeit in sverweis den Datenbereich A9:Y318 einzufrieren?

Danke und einen schönen Abend
wünscht Heinz

[echo]

Hallo Heinz
ich kann nicht nachvollziehen warum sich deine Formeln ändern.
Die Vermutung ist, das du etwas anders unter Einfügen verstehst wie ich.

Eine (Daten-)Liste hat Überschriften, und so viele Zeilen und Spalten wie eben nötig sind.
Es sollte sich unten ein leerer Bereich anschließen in dem weitere Daten eingefügt werden können.
Wenn das so ist, kann die Matrix der Formel getrost diverse Zeilen mehr haben.

=SVERWEIS(C$156;$Motordaten.A$9:Y$1000;8;0)

durch die 0 als 4. Parameter wird eine GENAUE Suche gestartet, wo die Übereinstimmung steht ist völlig egal.
durch die erweiterte Matrix werden auch neue Daten gefunden.

Auch die Abneigung gegen die Sortierfunktion erschließt sich mir nicht.
Möglicherweise solltest du uns ein Beispiel mit fiktiven Daten anhängen und noch mal beschreiben wie es an Hand der angehängten Liste zu der Änderung kommt.

Nachtrag:

Gibt es eine Möglichkeit in sverweis den Datenbereich A9:Y318 einzufrieren?

INDIREKT("Motordaten.A9:Y318") Als Matrix dann sollte sich nichts mehr ändern, auch nicht beim kopieren/einfügen

Gruß Holger

[karolus]

Hallo
INDIREKT ist nicht so toll, besser wäre wohl:

Code: Alles auswählen

=SVERWEIS($C156;INDEX(Motordaten.A:Y;9):INDEX(Motordaten.A:Y;318); 8;0)

[echo]

Hallo
INDIREKT ist nicht so toll, besser wäre wohl:

Ich vermute aber, das das Tabellenblatt unterhalb Zeile 318 nicht leer ist.

[karolus]

Hallo
INDIREKT ist nicht so toll, besser wäre wohl:

Ich vermute aber, das das Tabellenblatt unterhalb Zeile 318 nicht leer ist.

ja und?

Code: Alles auswählen

INDEX(Motordaten.A:Y;9):INDEX(Motordaten.A:Y;318)

»beobachtet« grundsätzlich nur den Bereich von Zeile 8 bis Zeile 318

dein INDIREKT muss jedesmal »neu berechnen« ganz egal wo sich etwas an den Daten ändert!

[redeagle56]

Hallo,
wie wäre es mit einer Beispiel-Tabelle?
Damit könnten viele offene Fragen geklärt werden.

MfG Günter