Relative und absolute Zellreferenz und Formatierung

In dieser Lektion werden Zellreferenzen, das Kopieren oder Verschieben einer Formel und das Formatieren von Zellen erläutert. Lassen Sie uns zunächst klären, was wir unter Zellreferenzen verstehen, die einen Großteil der Leistungsfähigkeit und Vielseitigkeit von Formeln und Funktionen untermauern. Mit einem konkreten Verständnis der Funktionsweise von Zellreferenzen können Sie Ihre Excel-Tabellen optimal nutzen!

Wir gehen nur davon aus, dass Sie bereits wissen, dass eine Zelle eines der Quadrate in der Tabelle ist, die in Spalten und Zeilen angeordnet sind, auf die durch horizontal und vertikal verlaufende Buchstaben und Zahlen verwiesen wird.

Was ist eine Zellreferenz?

Eine «Zellreferenz» bezeichnet die Zelle, auf die sich eine andere Zelle bezieht. Wenn Sie beispielsweise in Zelle A1 = A2 haben. Dann bezieht sich A1 auf A2.

Sehen wir uns an, was wir in Lektion 2 über Zeilen und Spalten gesagt haben, damit wir die Zellreferenzen weiter untersuchen können.

Zellen in der Tabelle werden durch Zeilen und Spalten bezeichnet. Die Spalten sind vertikal und mit Buchstaben gekennzeichnet. Zeilen sind horizontal und mit Zahlen gekennzeichnet.

Die erste Zelle in der Tabelle ist A1, was bedeutet, dass Spalte A, Zeile 1, B3 sich auf die Zelle in der zweiten Spalte, dritten Zeile usw. bezieht.

Zu Lernzwecken über Zellreferenzen schreiben wir sie manchmal als Zeile, Spalte. Dies ist keine gültige Notation in der Tabelle und soll lediglich die Dinge klarer machen.

Arten von Zellreferenzen

Es gibt drei Arten von Zellreferenzen.

Absolut – Dies bedeutet, dass die Zellreferenz gleich bleibt, wenn Sie die Zelle kopieren oder in eine andere Zelle verschieben. Dies erfolgt durch Verankerung der Zeile und Spalte, sodass sich diese beim Kopieren oder Verschieben nicht ändert.

Relativ – Relative Referenzierung bedeutet, dass sich die Zellenadresse beim Kopieren oder Verschieben ändert. dh die Zellreferenz ist relativ zu ihrer Position.

Gemischt – Dies bedeutet, dass Sie beim Kopieren oder Verschieben der Zelle entweder die Zeile oder die Spalte verankern können, sodass sich eine ändert und die andere nicht. Sie können beispielsweise die Zeilenreferenz verankern und dann eine Zelle zwei Zeilen nach unten und über vier Spalten verschieben, wobei die Zeilenreferenz gleich bleibt. Wir werden dies weiter unten erklären.

Relative Referenzen

Nehmen wir Bezug auf dieses frühere Beispiel – nehmen wir an, wir haben in Zelle A1 eine Formel, die einfach = A2 lautet. Das bedeutet, dass Excel in Zelle A1 ausgegeben wird, was auch immer in Zelle A2 eingegeben wird. In Zelle A2 haben wir «A2» eingegeben, sodass Excel den Wert «A2» in Zelle A1 anzeigt.

Angenommen, wir müssen in unserer Tabelle Platz für weitere Daten schaffen. Wir müssen Spalten oben und Zeilen links hinzufügen, also müssen wir die Zelle nach unten und rechts verschieben, um Platz zu schaffen.

Wenn Sie die Zelle nach rechts verschieben, erhöht sich die Spaltennummer. Wenn Sie es nach unten bewegen, erhöht sich die Zeilennummer. Die Zelle, auf die sie zeigt, die Zellreferenz, ändert sich ebenfalls. Dies ist unten dargestellt:

Fahren Sie mit unserem Beispiel fort und sehen Sie sich die folgende Grafik an. Wenn Sie den Inhalt von Zelle A1 zwei nach rechts und vier nach unten kopieren, haben Sie ihn in Zelle C5 verschoben.

Wir haben die Zelle zwei Spalten nach rechts und vier nach unten kopiert. Dies bedeutet, dass wir die Zelle geändert haben, auf die zwei und vier nach unten verweisen. A1 = A2 ist jetzt C5 = C6. Anstatt sich auf A2 zu beziehen, bezieht sich Zelle C5 jetzt auf Zelle C6.

Der angezeigte Wert ist 0, da Zelle C6 leer ist. In Zelle C6 geben wir «Ich bin C6» ein und jetzt zeigt C5 «Ich bin C6» an.

Beispiel: Textformel

Versuchen wir ein anderes Beispiel. Erinnern Sie sich an Lektion 2, in der wir einen vollständigen Namen in Vor- und Nachnamen aufteilen mussten? Was passiert, wenn wir diese Formel kopieren?

Schreiben Sie die Formel = RECHTS (A3, LEN (A3) – FIND (“,”, A3) – 1) oder kopieren Sie den Text in Zelle C3. Kopieren Sie nicht die eigentliche Zelle, sondern nur den Text, kopieren Sie den Text, da sonst die Referenz aktualisiert wird.

Sie können den Inhalt einer Zelle oben in einer Tabelle im Feld neben «fx» bearbeiten. Dieses Feld ist länger als eine Zelle, sodass es einfacher zu bearbeiten ist.

Jetzt haben wir:

Nichts kompliziertes, wir haben gerade eine neue Formel in Zelle C3 geschrieben. Kopieren Sie nun C3 in die Zellen C2 und C4. Beachten Sie die folgenden Ergebnisse:

Jetzt haben wir die Vornamen von Alexander Hamilton und Thomas Jefferson.

Markieren Sie mit dem Cursor die Zellen C2, C3 und C4. Zeigen Sie mit dem Cursor auf Zelle B2 und fügen Sie den Inhalt ein. Schauen Sie sich an, was passiert ist – wir erhalten eine Fehlermeldung: «#REF». Warum ist das?

Beim Kopieren der Zellen von Spalte C in Spalte B wurde die Referenz eine Spalte nach links aktualisiert = RECHTS (A2, LEN (A2) – FIND («,», A2) – 1).

Jeder Verweis auf A2 wurde in die Spalte links von A geändert, aber links von Spalte A befindet sich keine Spalte. Der Computer weiß also nicht, was Sie meinen.

Die neue Formel in B2 lautet beispielsweise = RIGHT (#REF !, LEN (#REF!) – FIND («,», # REF!) – 1) und das Ergebnis ist #REF:

Kopieren einer Formel in einen Zellbereich

Das Kopieren von Zellen ist sehr praktisch, da Sie eine Formel schreiben und in einen großen Bereich kopieren können und die Referenz aktualisiert wird. Dadurch muss nicht jede Zelle bearbeitet werden, um sicherzustellen, dass sie auf die richtige Stelle zeigt.

Mit «Reichweite» meinen wir mehr als eine Zelle. Zum Beispiel bedeutet (C1: C10) alle Zellen von Zelle C1 bis Zelle C10. Es ist also eine Spalte von Zellen. Ein weiteres Beispiel (A1: AZ1) ist die oberste Zeile von Spalte A zu Spalte AZ.

Wenn ein Bereich fünf Spalten und zehn Zeilen überschreitet, geben Sie den Bereich an, indem Sie die obere linke Zelle und die untere rechte Zelle schreiben, z. B. A1: E10. Dies ist ein quadratischer Bereich, der Zeilen und Spalten kreuzt und nicht nur Teil einer Spalte oder eines Teils einer Zeile ist.

In diesem Beispiel wird veranschaulicht, wie eine Zelle an mehrere Speicherorte kopiert wird. Angenommen, wir möchten unsere prognostizierten Ausgaben für den Monat in einer Tabelle anzeigen, damit wir ein Budget erstellen können. Wir machen eine Tabelle wie folgt:

Kopieren Sie nun die Formel in Zelle C3 (= B3 + C2) in den Rest der Spalte, um einen laufenden Saldo für unser Budget zu erhalten. Excel aktualisiert die Zellreferenz beim Kopieren. Das Ergebnis ist unten dargestellt:

Wie Sie sehen können, wird jede neue Zelle aktualisiert relativ an den neuen Ort, also aktualisiert Zelle C4 ihre Formel auf = B4 + C3:

Zelle C5 wird auf = B5 + C4 aktualisiert und so weiter:

Absolute Referenzen

Eine absolute Referenz ändert sich nicht, wenn Sie eine Zelle verschieben oder kopieren. Wir verwenden das $ -Zeichen, um eine absolute Referenz zu erstellen. Um sich daran zu erinnern, stellen Sie sich ein Dollarzeichen als Anker vor.

Geben Sie beispielsweise die Formel = $ A $ 1 in eine beliebige Zelle ein. Das $ vor der Spalte A bedeutet, dass die Spalte nicht geändert wird. Das $ vor der Zeile 1 bedeutet, dass die Spalte nicht geändert wird, wenn Sie die Zelle kopieren oder in eine andere Zelle verschieben.

Wie Sie im folgenden Beispiel sehen können, haben wir in Zelle B1 eine relative Referenz = A1.Wenn wir B1 in die vier Zellen darunter kopieren, ändert sich die relative Referenz = A1 in die Zelle links, sodass B2 zu A2, B3 wird werden zu A3 usw. Diese Zellen haben offensichtlich keinen eingegebenen Wert, daher ist die Ausgabe Null.

Wenn wir jedoch = $ A1 $ 1 verwenden, wie in C1, und es in die vier Zellen darunter kopieren, ist die Referenz absolut, ändert sich also nie und die Ausgabe ist immer gleich dem Wert in Zelle A1.

Angenommen, Sie verfolgen Ihr Interesse, wie im folgenden Beispiel. Die Formel in C4 = B4 * B1 lautet «Zinssatz» * «Saldo» = «Zinsen pro Jahr».

Jetzt haben Sie Ihr Budget geändert und zusätzliche 2.000 USD für den Kauf eines Investmentfonds gespart. Angenommen, es handelt sich um einen festverzinslichen Fonds, der denselben Zinssatz zahlt. Geben Sie das neue Konto und den Saldo in die Tabelle ein und kopieren Sie die Formel = B4 * B1 von Zelle C4 in Zelle C5.

Das neue Budget sieht folgendermaßen aus:

Der neue Investmentfonds verdient 0 USD Zinsen pro Jahr, was nicht richtig sein kann, da der Zinssatz eindeutig 5 Prozent beträgt.

Excel hebt die Zellen hervor, auf die sich eine Formel bezieht. Sie können oben sehen, dass der Verweis auf den Zinssatz (B1) in die leere Zelle B2 verschoben wird. Wir hätten den Verweis auf B1 absolut machen sollen, indem wir $ B $ 1 mit dem Dollarzeichen geschrieben haben, um die Zeilen- und Spaltenreferenz zu verankern.

Schreiben Sie die erste Berechnung in C4 wie folgt um: = B4 * $ B $ 1:

Kopieren Sie dann diese Formel von C4 nach C5. Die Tabelle sieht nun folgendermaßen aus:

Da wir die Formel eine Zelle nach unten kopiert haben, dh die Zeile um eins erhöht haben, lautet die neue Formel = B5 * $ B $ 1. Der Zinssatz für Investmentfonds wird jetzt korrekt berechnet, da der Zinssatz in Zelle B1 verankert ist.

Dies ist ein gutes Beispiel dafür, wann Sie einen „Namen“ verwenden können, um auf eine Zelle zu verweisen. Ein Name ist eine absolute Referenz. Um beispielsweise der Zelle B1 den Namen «Zinssatz» zuzuweisen, klicken Sie mit der rechten Maustaste auf die Zelle und wählen Sie dann «Name definieren».

Namen können sich auf eine Zelle oder einen Bereich beziehen, und Sie können einen Namen in einer Formel verwenden, z. B. = Zinssatz * 8 ist dasselbe wie Schreiben = $ B $ 1 * 8.

Gemischte Referenzen

Gemischte Referenzen sind wann entweder die Reihe oder Säule ist verankert.

Angenommen, Sie sind ein Landwirt, der ein Budget erstellt. Sie besitzen auch einen Futterladen und verkaufen Samen. Sie werden Mais, Sojabohnen und Luzerne pflanzen. Die folgende Tabelle zeigt die Kosten pro Morgen. Die «Kosten pro Morgen» = «Preis pro Pfund» * «Pfund Samen pro Morgen» – das kostet es Sie, einen Morgen zu pflanzen.

Geben Sie die Kosten pro Morgen als = $ B2 * C2 in Zelle D2 ein. Sie sagen, Sie möchten den Preis pro Pfundspalte verankern. Kopieren Sie diese Formel dann in die anderen Zeilen in derselben Spalte:

Jetzt möchten Sie den Wert Ihres Saatgutbestands wissen. Sie benötigen den Preis pro Pfund und die Anzahl der Pfund im Inventar, um den Wert des Inventars zu ermitteln.

Wir fügen zwei Spalten hinzu: «Pfund Saatgut im Inventar» und dann «Wert des Inventars». Kopieren Sie nun die Zelle D2 nach F4 und beachten Sie, dass die Zeilenreferenz im ersten Teil der Originalformel ($ B2) auf Zeile 4 aktualisiert wird, die Spalte jedoch fest bleibt, da $ sie in „B“ verankert.

Dies ist eine gemischte Referenz, da die Spalte absolut und die Zeile relativ ist.

Rundschreiben

Ein Zirkelverweis ist, wenn sich eine Formel auf sich selbst bezieht.

Zum Beispiel können Sie nicht c3 = c3 + 1 schreiben. Diese Art der Berechnung wird als «Iteration» bezeichnet, was bedeutet, dass sie sich wiederholt. Excel unterstützt keine Iteration, da alles nur einmal berechnet wird.

Wenn Sie dies versuchen, geben Sie SUM (B1: B5) in Zelle B5 ein:

Ein Warnbildschirm wird angezeigt:

Excel zeigt Ihnen nur an, dass Sie am unteren Bildschirmrand einen Zirkelverweis haben, sodass Sie ihn möglicherweise nicht bemerken. Wenn Sie einen Zirkelverweis haben und eine Tabelle schließen und erneut öffnen, teilt Excel Ihnen in einem Popup-Fenster mit, dass Sie einen Zirkelverweis haben.

Wenn Sie einen Zirkelverweis haben, teilt Excel Ihnen bei jedem Öffnen der Tabelle mit diesem Popup-Fenster mit, dass Sie einen Zirkelverweis haben.

Verweise auf andere Arbeitsblätter

Eine «Arbeitsmappe» ist eine Sammlung von «Arbeitsblättern». Einfach ausgedrückt bedeutet dies, dass Sie mehrere Tabellenkalkulationen (Arbeitsblätter) in derselben Excel-Datei (Arbeitsmappe) haben können. Wie Sie im folgenden Beispiel sehen können, enthält unsere Beispielarbeitsmappe viele Arbeitsblätter (in rot).

Arbeitsblätter heißen standardmäßig Sheet1, Sheet2 usw. Sie erstellen eine neue, indem Sie auf das «+» am unteren Rand des Excel-Bildschirms klicken.

Sie können den Arbeitsblattnamen in einen nützlichen Namen wie «Darlehen» oder «Budget» ändern, indem Sie mit der rechten Maustaste auf die Arbeitsblattregisterkarte unten im Excel-Programmbildschirm klicken, Umbenennen auswählen und einen neuen Namen eingeben.

Oder Sie doppelklicken einfach auf die Registerkarte und benennen sie um.

Die Syntax für eine Arbeitsblattreferenz lautet = Arbeitsblatt! Zelle. Sie können diese Art von Referenz verwenden, wenn in zwei Arbeitsblättern derselbe Wert verwendet wird. Beispiele hierfür sind:

• Heutiges Datum
• Währungsumrechnungskurs von Dollar in Euro
• Alles, was für alle Arbeitsblätter in der Arbeitsmappe relevant ist

Unten finden Sie ein Beispiel für das Arbeitsblatt „Zinsen“ unter Bezugnahme auf das Arbeitsblatt „Darlehen“, Zelle B1.

Wenn wir uns das Arbeitsblatt „Darlehen“ ansehen, sehen wir den Verweis auf den Darlehensbetrag:

Als nächstes …

Wir hoffen, dass Sie jetzt ein sicheres Verständnis für Zellreferenzen haben, einschließlich relativer, absoluter und gemischter. Es gibt sicherlich viel.

Das war’s für die heutige Lektion. In Lektion 4 werden einige nützliche Funktionen besprochen, die Sie möglicherweise für den täglichen Gebrauch in Excel kennen möchten.