Verweis über eine ganze Spalte, ohne dass sich der Bereich des Nachschlagewerts verändert?
Wahrscheinlich sind es nur ein paar clicks, aber bis jetzt habe ich keine Lösung gefunden. Ich möchte eine Spalte einfügen die dem Begriff in der Spalte davor einem Wert zuordnet. Der Wert steht in einer separaten Tabelle auf einem anderen Blatt. Ich habe dafür Sverweis geschrieben, als Suchkrieterium die Zelle links, als Bereich die erste und letzte Zelle der Tabelle auf der anderen Seite, den Spaltenindex und false, und dann die eine Zelle bis nach unten gezogen.
Für die ersten Zellen hat das funktioniert, für die weiteren steht nur NV da, das liegt daran dass jede Zelle den Bereich der Matrix um 1 ändert. War der Bereich in der ersten Zelle, A1:B3 ist er in der zweiten A2:B4, in der dritten A3:B5. Das passiert auch wenn ich als Suchkriterium die gesamte linke Zeile auswähle. Natürlich lässt sich das manuell die Matrix in jeder Zeile bearbeiten, aber es würde mich wundern wenn es kein simple, schnelle Lösung gibt mit der Ich die Formel beliebig weit ziehen kann und sich nur das Kriterium anpasst.
LG Max
3 Antworten
Du Lösung ist einfach. Du bist den Bereich der Matrix unveränderlich an. Das macht man in Excel indem man der Bezeichnung ein Dollarzeichen voranstellt. $A$1:$B$3 verändert sich beim "Ziehen" nicht. A$1:B$3 als Beispiel würde sich in der Spalte verändern (hier fehlt das Dollarzeichen) in der Zeile (hier hat es eines) dagegen nicht.
Hallo,
die Antworten sind bisher ja richtig, nur leider (hatte zumindest ich bei den Antworten Verständnis Probleme wenn ich nicht selbst wüsste wie es funktioniert.) Deswegen versuche ich dir jetzt mal die Absolute Adressierung zu erläutern :)
Folgende Ausgangssituation :):
Du hast eine Tabelle mit einer =SVERWEIS() Funktion, mit der du aus einer nebenstehenden Tabelle Informationen abfragen möchtest.
Nehmen wir die SVERWEIS()-Funktion einmal auseinander:
Die SVERWEIS()-Funktion wird mit folgenden Parametern aufgerufen:
=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Sortierung)
Die Funktion =SVERWEIS() besteht aus 4 Teilen
- Das Suchkriterium Das Suchkriterium gibt an, nach was in der Angegebenen Matrix gesucht werden soll, sprich die 1. Spalte der Matrix wird nach diesem Wert durchsucht
- Die MatrixDie Matrix gibt den Zellbereich an, der für die Durchsuchung und die Auswertung der Daten bei einer Übereinstimmung genutzt wird.
Sie ist also der Bereich, der Innerhalb der SVERWEIS()-Funktion durchsucht und benutzt wird um Daten auszulesen. - Der SpaltenindexDer Spaltenindex gibt an, welche Spalte (von links nach rechts gezählt) bei einer Übereinstimmung des Suchkriteriums als Ausgabewert benutzt werden soll. So kann man ganz einfach sage, wenn man eine Übereinstimmung hat, will ich von dieser Suchabfrage den Wert aus der 3. Spalte haben.
- Die SortierungDie Sortierung beinhaltet 2 Werte, 0 und 1 oder WAHR und FALSCH.
Ist der Wert WAHR, muss die Suchspalte der Matrix aufsteigend Sortiert sein. Ist der Wert FALSCH muss er das nicht sein.
So weit zur Funktion :)...
Wie funktioniert das kopieren einer Formel/Funktion?
Eine Formel die Kopiert wird, passt im Normalfall die Spalten und Zellbezüge automatisch an( SUPER Feature von Excel ;) )
Was ist damit gemeint? Angenommen, du hast in der 1. Zeile deiner Tabelle Zahlen stehen,
A1=1, B1=, C1=3 ....
Jetzt schreibst du in A2 die Formel =A1+5, also ausgeschrieben 1+5.
Das willst Du nun für die ganze spalte machen, also!
Die Zelle ziehen oder einfach per Copy Paste die Formel einfügen.
Excel macht jetzt automatisch in B2 aus A1+5 ein B1+5! GENIAL oder?:p
So, aber was mache ich wenn ich das nicht möchte? z.B. wenn ich in A1 einen festen wert habe und den Wert, den ich dazurechnen möchte, per Zelle einlese?
Sprich:
A1=19, B1=12, C1=22
Vorhaben:
A1+B1 Danach A1+C1 ...
Wenn ich diese Formel kopiere hätte ich nach A1+B1, B1+C1, also das falsche Ergebnis!
Wie beuge ich das jetzt vor? Hier kommt ENDLICH die Absolute Adressierung ins Spiel. Das Wichtigste Zeichen bei der Adressierung ist das ... TROMMELWIRBEL BITTE!!!!
Das "$" Zeichen WOW! :)
Das $ bewirkt folgendes:
Durch das vor ansetzen eines $ Zeichens vor der Spaltenangabe oder Zeilenangabe, wird diese Absolut adressiert, sprich gespeichert.
Beim Kopieren oder ziehen einer Formel, wird hier der Wert nicht angepasst sondern durch das speichern 1:1 übernommen.
Der Aufbau ist dann wie folgt:
$A$1+B1
Die Formel kann nun kopiert werden und wird dann zu folgenden Formeln:
$A$1+C1, $A$1+D1, $A$1+E1 ...
Wenn das $ Zeichen vor der Spalte steht, wird diese nicht verändert, wird das $ Zeichen vor die Zeilenangabe geschrieben, ändert sich die Zeile nicht mehr!
So ändert sich z.B. bei $A$1 gar nichts wohingegen sich bei $A1 nur die Zeile ändert und bei A$1 nur die Spalte.
Also hätten wir in Deinem Fall folgendes zu tun, um das
A1:B3 wird zu A2:B4
vorzubeugen:
=SVERWEIS([Dein Suchkriterium];$A$1:$B$3;[Dein Spaltenindex];[Deine Sortierung])
Ich hoffe ich konnte das einigermaßen erklären und dir weiterhelfen ;)
Liebe Grüße,
iMPerFekTioN
Wenn der Bereich weiter nur A1:B3 sein soll, dann hilft $A$1:$B$3 zu verwenden.