Relative und absolute Zellreferenz und Formatierung

Inhaltsverzeichnis:

Video: Relative und absolute Zellreferenz und Formatierung

Video: Relative und absolute Zellreferenz und Formatierung
Video: Mobile Hotspot und Tethering: Internetzugang für weitere Geräte | Samsung 2024, März
Relative und absolute Zellreferenz und Formatierung
Relative und absolute Zellreferenz und Formatierung
Anonim
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-Tabellen optimal nutzen!
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-Tabellen 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 nehmen an, dass Sie bereits wissen, dass eine Zelle eines der Quadrate in der Kalkulationstabelle ist, angeordnet in Spalten und Zeilen, auf die durch horizontal und vertikal verlaufende Buchstaben und Zahlen verwiesen wird.

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.

Sehen wir uns an, was wir in Lektion 2 über Zeilen und Spalten gesagt haben, damit wir die Zellverweise genauer untersuchen können.
Sehen wir uns an, was wir in Lektion 2 über Zeilen und Spalten gesagt haben, damit wir die Zellverweise genauer 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 - nehmen wir an, wir haben in der Zelle A1 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, damit Excel den Wert "A2" in Zelle A1 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.
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 verweist, die Zellreferenz, ändert sich ebenfalls. Dies ist unten dargestellt:

Wenn Sie mit unserem Beispiel fortfahren und die folgende Grafik betrachten, haben Sie den Inhalt von Zelle A1, zwei nach rechts und vier nach unten, in Zelle C5 verschoben.
Wenn Sie mit unserem Beispiel fortfahren und die folgende Grafik betrachten, haben Sie den Inhalt von Zelle A1, zwei nach rechts und vier nach unten, in 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 die Zelle C6 leer ist. In Zelle C6 geben wir "Ich bin C6" ein und jetzt zeigt C5 "Ich bin C6".
Der angezeigte Wert ist 0, da die Zelle C6 leer ist. In Zelle C6 geben wir "Ich bin C6" ein und jetzt zeigt C5 "Ich bin C6".
Image
Image

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.
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 Feld neben dem Feld "fx" bearbeiten. Dieses Feld ist länger als eine Zelle breit und daher einfacher zu bearbeiten.

Jetzt haben wir:

Image
Image

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.
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 kopierten, wurde die Referenzspalte nach links aktualisiert = RECHTS (A2, LEN (A2) - FIND (",", A2) - 1).
Wenn wir die Zellen von Spalte C in Spalte B kopierten, 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:

Image
Image

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 weiteres Beispiel (A1: AZ1) ist die oberste Zeile 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 mehreren Orten kopiert wird. Angenommen, wir möchten unsere voraussichtlichen Ausgaben für den Monat in einer Tabelle darstellen, damit wir ein Budget erstellen können. Wir erstellen eine Tabelle wie folgt:
Hier ein Beispiel, das veranschaulicht, wie eine Zelle an mehreren Orten kopiert wird. Angenommen, wir möchten unsere voraussichtlichen Ausgaben für den Monat in einer Tabelle darstellen, 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:
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.
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. 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 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, wie in C1, und wir es in die vier darunter liegenden Zellen kopieren, ist die Referenz absolut, sie ä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 ist der "Zinssatz" * "Saldo" = "Zinssatz pro Jahr".
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.
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.
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:
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.
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 auf eine Zelle zu verweisen. Ein Name ist eine absolute Referenz. Um beispielsweise 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.
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 Morgen in der Zelle D2 als $ B2 * C2 ein. Sie wollen 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.
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 fest, weil der $ -Status auf "B" verankert.

Dies ist eine gemischte Referenz, da die Spalte absolut und die Zeile relativ ist.
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 die Iteration nicht, da alles nur einmal berechnet wird.

Versuchen Sie dies, indem Sie in Zelle B5 SUM (B1: B5) eingeben:

Ein Warnbildschirm wird angezeigt:
Ein Warnbildschirm wird angezeigt:
Excel teilt Ihnen nur mit, dass Sie am unteren Rand des Bildschirms einen Zirkelverweis haben, so dass 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.
Excel teilt Ihnen nur mit, dass Sie am unteren Rand des Bildschirms einen Zirkelverweis haben, so dass 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.
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.
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.
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.
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 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:
Wenn wir uns das Arbeitsblatt „Darlehen“ansehen, können wir den Verweis auf den Darlehensbetrag sehen:
Image
Image

Im Anschluss …

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

Das ist 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.

Empfohlen: