Relative und absolute Zellreferenz und Formatierung

In dieser Lektion werden Zellbezüge beschrieben, wie Sie eine Formel kopieren oder verschieben und Zellen formatieren. Lassen Sie uns zunächst klarstellen, was wir unter Zellbezügen verstehen, die einen Großteil der Leistungsfähigkeit und Vielseitigkeit von Formeln und Funktionen untermauern. Mit einem konkreten Überblick über die Funktionsweise von Zellbezügen können Sie Ihre Excel-Tabellenkalkulationen optimal nutzen!

SCHULNAVIGATION

1. Warum benötigen Sie Formeln und Funktionen??
2. Formel definieren und erstellen
3. Relative und absolute Zellreferenz und Formatierung
4. Nützliche Funktionen, die Sie kennenlernen sollten
5. Suchvorgänge, Diagramme, Statistiken und Pivot-Tabellen

Hinweis: Wir gehen davon aus, dass Sie bereits wissen, dass eine Zelle eines der Quadrate in der Kalkulationstabelle ist, angeordnet in Spalten und Zeilen, die durch horizontal und vertikal verlaufende Buchstaben und Zahlen referenziert werden.

Was ist eine Zellreferenz?

Eine "Zellreferenz" bedeutet die Zelle, auf die sich eine andere Zelle bezieht. Wenn Sie in der Zelle A1 beispielsweise = A2 haben. Dann bezieht sich A1 auf A2.

Lassen Sie uns das, was wir in Lektion 2 über Zeilen und Spalten gesagt haben, überprüfen, damit wir die Zellverweise weiter untersuchen können.

Zellen in der Kalkulationstabelle werden durch Zeilen und Spalten bezeichnet. Spalten sind vertikal und mit Buchstaben beschriftet. Zeilen sind horizontal und mit Zahlen beschriftet.

Die erste Zelle in der Kalkulationstabelle ist A1, dh Spalte A, Zeile 1, B3 bezieht sich auf die Zelle in der zweiten Spalte, dritten Zeile usw..

Zu Lernzwecken über Zellbezüge schreiben wir sie gelegentlich als Zeile, Spalte. Dies ist keine gültige Notation in der Kalkulationstabelle und soll lediglich die Dinge klarer machen.

Arten von Zellbezügen

Es gibt drei Arten von Zellbezügen.

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

Relativ - Relative Referenzierung bedeutet, dass sich die Zellenadresse ändert, wenn Sie sie kopieren oder verschieben. die Zellreferenz ist relativ zu ihrem Ort.

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

Relative Referenzen

Lassen Sie uns auf dieses frühere Beispiel verweisen - in Zelle A1 haben wir eine Formel, die einfach = A2 sagt. Das heißt, Excel-Ausgabe in Zelle A1, was auch immer in Zelle A2 eingegeben wird. In Zelle A2 haben wir "A2" eingegeben, sodass Excel in Zelle A1 den Wert "A2" anzeigt.

Nehmen wir an, wir müssen Platz für mehr Daten in unserer Tabelle schaffen. Wir müssen Spalten über und Zeilen nach links hinzufügen, also müssen wir die Zelle nach unten und nach 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 es zeigt, die Zellreferenz, ändert sich ebenfalls. Dies ist unten dargestellt:

Wenn Sie mit unserem Beispiel fortfahren und die untenstehende Grafik betrachten, haben Sie den Inhalt der Zelle A1 zwei nach rechts und vier nach unten in die Zelle C5 verschoben.

Wir haben die Zelle zwei Spalten nach rechts und vier nach unten kopiert. Dies bedeutet, dass wir die Zelle, auf die sie verweist, zwei über und vier nach unten geändert hat. A1 = A2 ist jetzt C5 = C6. Anstatt auf A2 zu verweisen, 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".

Beispiel: Textformel

Versuchen wir ein anderes Beispiel. Erinnern Sie sich an Lektion 2, wo 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. Andernfalls wird die Referenz aktualisiert.

Sie können den Inhalt einer Zelle oben in einer Tabelle in dem Kästchen neben "fx" bearbeiten. Dieses Feld ist länger als eine Zelle breit und daher einfacher zu bearbeiten.

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 Ergebnisse unten:

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

Verwenden Sie den Cursor, um die Zellen C2, C3 und C4 hervorzuheben. 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 so??

Wenn wir die Zellen von Spalte C in Spalte B kopiert haben, wurde die Referenzspalte nach links aktualisiert = RECHTS (A2, LEN (A2) - FIND (",", A2) - 1).

Es wurde jeder Verweis auf A2 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 = RECHTS (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) und das Ergebnis ist #REF:

Eine Formel in einen Zellbereich kopieren

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 vermieden werden, dass jede Zelle bearbeitet werden muss, 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 anderes Beispiel (A1: AZ1) ist die oberste Reihe von Spalte A bis Spalte AZ.

Wenn ein Bereich fünf Spalten und zehn Zeilen kreuzt, geben Sie den Bereich an, indem Sie die obere linke Zelle und die rechte untere 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.

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

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

Wie Sie sehen, wird jede neue Zelle aktualisiert relativ an der neuen Position, so aktualisiert Zelle C4 ihre Formel auf = B4 + C3:

Zelle C5 wird aktualisiert auf = B5 + C4 usw.

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 dies zu berücksichtigen, stellen Sie sich ein Dollarzeichen als Anker vor.

Geben Sie zum Beispiel die Formel = $ A $ 1 in eine beliebige Zelle ein. Das $ vor der Spalte A bedeutet, dass die Spalte nicht geändert wird. Die $ 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 Beispiel unten sehen können, haben wir in Zelle B1 eine relative Referenz = A1. Wenn wir B1 in die vier darunter befindlichen Zellen kopieren, ändert sich die relative Referenz = A1 in die Zelle links, sodass B2 zu A2, B3 wird werden A3 usw. Diese Zellen haben offensichtlich keinen eingegebenen Wert, daher ist die Ausgabe Null.

Wenn wir jedoch = $ A1 $ 1 verwenden, z. B. in C1, und wir es in die vier darunterliegenden Zellen kopieren, ist der Bezug absolut, daher ändert er sich nie und der Ausgang ist immer gleich dem Wert in Zelle A1.

Angenommen, Sie verfolgen Ihr Interesse, wie im folgenden Beispiel. Die Formel in C4 = B4 * B1 ist der "Zinssatz" * "Saldo" = "Zinssatz pro Jahr".

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

Das neue Budget sieht so aus:

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

Excel markiert die Zellen, auf die eine Formel verweist. Sie sehen oben, dass der Bezug 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 schreiben, um die Zeilen- und Spaltenreferenz zu verankern.

Schreibe die erste Berechnung in C4 so um, dass sie = B4 * $ B $ 1 liest:

Dann kopieren Sie diese Formel von C4 nach C5. Die Tabelle sieht jetzt so aus:

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

Dies ist ein gutes Beispiel, wenn Sie einen "Namen" verwenden könnten, um sich auf eine Zelle zu beziehen. 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 "Name definieren".

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

Gemischte Referenzen

Gemischte Referenzen sind wann entweder die Reihe oder Spalte ist verankert.

Angenommen, Sie sind ein Landwirt, der ein Budget erstellt. Sie besitzen auch einen Futterladen und verkaufen Saatgut. Sie werden Mais, Sojabohnen und Luzerne anpflanzen. Die folgende Tabelle zeigt die Kosten pro Hektar. Die "Kosten pro Morgen" = "Preis pro Pfund" * "Pfund Samen pro Morgen" - das ist es, was es kostet, einen Morgen anzubauen.

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

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

Wir fügen zwei Spalten hinzu: "Pfund Samen in Inventar" und dann "Inventarwert". Kopieren Sie nun die Zelle D2 nach F4 und beachten Sie, dass der Zeilenverweis im ersten Teil der ursprünglichen Formel ($ B2) in Zeile aktualisiert wird 4, aber die Spalte bleibt fixiert, weil der $ -Status auf "B" verankert.

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

Zirkuläre Referenzen

Eine Zirkelreferenz ist, wenn sich eine Formel auf sich selbst bezieht.

Sie können beispielsweise nicht c3 = c3 + 1 schreiben. Diese Art der Berechnung wird als "Iteration" bezeichnet, dh sie wiederholt sich. 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 teilt Ihnen nur mit, dass Sie am unteren Rand des Bildschirms einen Zirkelverweis haben, sodass Sie ihn möglicherweise nicht bemerken. Wenn Sie eine Zirkelreferenz haben und eine Tabelle schließen und erneut öffnen, teilt Excel Ihnen in einem Popup-Fenster mit, dass Sie eine Zirkelreferenz haben.

Wenn Sie eine Zirkelreferenz haben, wird Ihnen Excel bei jedem Öffnen der Tabelle mit diesem Popup-Fenster mitteilen, dass Sie eine Zirkelreferenz haben.

Verweise auf andere Arbeitsblätter

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

Arbeitsblätter werden standardmäßig als Sheet1, Sheet2 usw. bezeichnet. Sie erstellen eine neue, indem Sie auf das "+" unten im Excel-Bildschirm klicken.

Sie können den Namen des Arbeitsblatts in etwas Nützliches wie "Darlehen" oder "Budget" ändern, indem Sie mit der rechten Maustaste auf die Registerkarte des Arbeitsblatts klicken, die am unteren Rand des Excel-Programmbildschirms angezeigt wird, Umbenennen auswählen und einen neuen Namen eingeben.

Oder Sie können einfach auf die Registerkarte doppelklicken und sie umbenennen.

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

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

Im Folgenden finden Sie ein Beispiel für ein Arbeitsblatt "Interesse", das sich auf das Arbeitsblatt "Darlehen" bezieht, Zelle B1.

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

Im Anschluss…

Wir hoffen, Sie haben jetzt einen festen Überblick über Zellbezüge, einschließlich relativer, absoluter und gemischter. Es gibt sicherlich viel.

Das war es für die heutige Lektion. In Lektion 4 werden wir einige nützliche Funktionen besprechen, die Sie für die tägliche Verwendung von Excel wissen möchten.