Um genaue Berechnungen in Excel durchzuführen, ist es wichtig zu verstehen, wie die verschiedenen Arten von Zellbezügen funktionieren.
A1 vs. R1C1 Referenzen
Excel-Arbeitsblätter enthalten viele Zellen und (standardmäßig) wird jede Zelle durch ihre . identifiziert Spaltenbuchstabe gefolgt von seiner Zeilennummer. Dies wird als Referenzierung im A1-Stil bezeichnet. Beispiele: A1, B4, C6
A1 Referenzstil
Optional können Sie zu . wechseln R1C1 Referenzmodus auf die Zeile einer Zelle verweisen & Spaltennummer. Anstatt auf Zelle A1 zu verweisen, würden Sie sich auf R1C1 (Zeile 1, Spalte 1) beziehen. Zelle C4 würde auf R4C3 bezogen werden.
R1C1-Referenzstil
Verweise im R1C1-Stil sind in Excel äußerst ungewöhnlich. Sofern Sie keinen triftigen Grund haben, sollten Sie sich wahrscheinlich an den Standard-Referenzmodus im A1-Stil halten. Wenn Sie jedoch VBA verwenden, werden Sie wahrscheinlich auf diesen Referenzstil stoßen.
Wechseln Sie zum R1C1-Referenzstil
Um den Referenzstil zu wechseln, gehen Sie zu Datei > Option > Formel. Aktivieren Sie das Kontrollkästchen neben R1C1-Referenzstil.
Benannte Bereiche
Eine der am wenigsten genutzten Funktionen von Excel ist die Funktion für benannte Bereiche. Anstatt auf eine Zelle (oder eine Gruppe von Zellen) durch ihre Zellposition (z. B. B3 oder R3C2) zu verweisen, können Sie diesen Bereich benennen und einfach in Ihren Formeln auf den Bereichsnamen verweisen.
So benennen Sie einen Bereich:
- Wählen Sie die Zelle oder Zellen aus, die Sie benennen möchten
- Klicken Sie in das Feld für den Bereichsnamen
- Geben Sie Ihren Wunschnamen ein
- Drücke Enter
Jetzt können Sie auf Zelle A1 verweisen, indem Sie stattdessen =range_name1 eingeben. Dies ist sehr nützlich, wenn Sie mit großen Arbeitsmappen mit mehreren Arbeitsblättern arbeiten.
Zellbereich
Wenn Sie die integrierten Funktionen von Excel verwenden, müssen Sie möglicherweise auf Zellbereiche verweisen. Zellbereiche werden wie folgt angezeigt „A1:B3“. Diese Referenz bezieht sich auf alle Zellen zwischen A1 und B3: Zellen A1,A2,A3, B1, B2, B3.
So wählen Sie bei der Eingabe einer Formel einen Zellbereich aus:
- Geben Sie den Bereich ein (trennen Sie den Anfangs- und Endbereich durch ein Semikolon)
- Klicken Sie mit der Maus auf den ersten Zellbezug, halten Sie die Maustaste gedrückt und ziehen Sie den gewünschten Bereich.
- Halten Sie die Umschalttaste gedrückt und navigieren Sie mit den Pfeiltasten, um Ihren Bereich auszuwählen
Absolute (eingefrorene) und relative Referenzen
Bei der Eingabe von Zellbezügen in Formeln können Sie relative oder absolute (eingefrorene) Bezüge verwenden. Relative Bezüge werden proportional verschoben, wenn Sie die Formel in eine neue Zelle kopieren. Absolute Referenzen bleiben unverändert. Schauen wir uns einige Beispiele an:
Relative Referenz
Eine relative Referenz in Excel sieht so aus
=A1
Wenn Sie eine Formel mit relativen Bezügen kopieren und einfügen, werden die relativen Bezüge proportional verschoben. Sofern Sie nichts anderes angeben, sind Ihre Zellbezüge standardmäßig relativ (nicht eingefroren).
Beispiel: Wenn Sie ‚=A1‘ eine Zeile nach unten kopieren, ändert sich die Referenz zu ‚=A2‘.
Absolute (eingefrorene) Zellreferenzen
Wenn Sie nicht möchten, dass sich Ihre Zellbezüge beim Kopieren einer Formel verschieben, können Sie Ihre Zellbezüge "einfrieren", indem Sie Dollarzeichen ($s) vor der Referenz hinzufügen, die eingefroren werden soll. Wenn Sie nun die Formel kopieren und einfügen, bleibt der Zellbezug unverändert. Sie können den Zeilenverweis, den Spaltenverweis oder beides einfrieren.
A1: Nichts ist eingefroren
$A1: Die Spalte ist eingefroren, aber die Zeile ist nicht eingefroren
A$1: Die Zeile ist eingefroren, aber die Spalte ist nicht eingefroren
$A$1: Sowohl die Zeile als auch die Spalte sind eingefroren
Absolute Referenz-Verknüpfung
Das manuelle Hinzufügen von Dollarzeichen ($s) zu Ihren Formeln ist nicht sehr praktisch. Verwenden Sie stattdessen beim Erstellen Ihrer Formel die Taste F4, um zwischen absoluten/relativen Zellbezügen zu wechseln.
Beispiel für einen absoluten Zellbezug
Wann müssten Sie eigentlich einen Zellbezug einfrieren? Ein häufiges Beispiel sind Eingabezellen, auf die häufig verwiesen wird. Im folgenden Beispiel möchten wir die Umsatzsteuer für jede Menge an Menüpunkten berechnen. Die Mehrwertsteuer ist für alle Artikel konstant, daher werden wir wiederholt auf die Mehrwertsteuerzelle verweisen.
Um die Gesamtumsatzsteuer zu ermitteln, geben Sie die Formel „=(B3*C3)*$C$1“ in Spalte D ein und kopieren Sie die Formel nach unten.
Gemischte Referenz
Sie haben vielleicht schon von gemischten Zellreferenzen gehört. Eine gemischte Referenz liegt vor, wenn entweder die Zeilen- oder Spaltenreferenz gesperrt ist (aber nicht beide).
Gemischte Referenz
Denken Sie daran, dass Sie mit der Taste „F4“ durch Ihre relativen, absoluten Zellbezüge blättern können.
Zellreferenzen - Einfügen und Löschen von Zeilen/Spalten
Sie fragen sich vielleicht, was mit Ihren Zellbezügen passiert, wenn Sie Zeilen/Spalten einfügen oder löschen?
Der Zellbezug wird automatisch aktualisiert, um auf die ursprüngliche Zelle zu verweisen. Dies ist unabhängig davon der Fall, ob der Zellbezug eingefroren ist.
3D-Referenzen
Manchmal müssen Sie möglicherweise mit mehreren Arbeitsblättern mit identischen Datenmustern arbeiten. Excel ermöglicht es Ihnen, auf mehrere Blätter gleichzeitig zu verweisen, ohne jedes Arbeitsblatt manuell eingeben zu müssen. Sie können auf einen Bereich von Blättern ähnlich wie auf einen Zellbereich verweisen. Beispiel 'Sheet1:Sheet5!A1' würde auf die Zellen A1 auf allen Blättern von Sheet1 bis Sheet5 verweisen.
Gehen wir ein Beispiel durch:
Sie möchten die Gesamtzahl der verkauften Einheiten für jedes Produkt in allen Geschäften addieren. Jedes Geschäft hat sein eigenes Arbeitsblatt und alle Arbeitsblätter haben ein identisches Format. Sie könnten eine Formel ähnlich dieser erstellen:
Das ist mit nur vier Arbeitsblättern nicht allzu schwierig, aber was wäre, wenn Sie 40 Arbeitsblätter hätten? Möchten Sie wirklich jeden Zellbezug manuell hinzufügen?
Stattdessen können Sie eine 3D-Referenz verwenden, um problemlos mehrere Blätter gleichzeitig zu referenzieren (ähnlich wie Sie einen Zellbereich referenzieren können).
Vorsichtig sein! Die Reihenfolge Ihrer Arbeitsblätter ist wichtig. Wenn Sie ein weiteres Blatt zwischen die referenzierten Blätter (StoreA und StoreD) verschieben, wird dieses Blatt eingeschlossen. Umgekehrt, wenn Sie ein Blatt außerhalb des Blattbereichs verschieben (vor StoreA oder nach StoreD), wird es nicht mehr eingeschlossen.
Kreisförmige Zellreferenz
Ein zirkulärer Zellbezug liegt vor, wenn eine Zelle auf sich selbst verweist. Wenn beispielsweise das Ergebnis von Zelle B1 als Eingabe für Zelle B1 verwendet wird, wird ein Zirkelbezug erstellt. Die Zelle muss nicht direkt auf sich selbst verweisen. Es kann Zwischenschritte geben.
Beispiel:
In diesem Fall lautet die Formel für Zelle B2 „A2+A3+B2“. Da Sie sich in Zelle B2 befinden, können Sie B2 in der Gleichung nicht verwenden. Dies würde einen Zirkelbezug auslösen und der Wert in Zelle „B2“ wird automatisch auf „0“ gesetzt.
Normalerweise sind Zirkelverweise das Ergebnis von Benutzerfehlern, aber unter Umständen möchten Sie Zirkelverweise verwenden. Das wichtigste Beispiel für die Verwendung eines Zirkelbezugs ist die iterative Berechnung von Werten. Um dies zu tun, müssen Sie gehen zu Datei > Optionen > Formeln und Iterative Berechnung aktiviert:
Externe Referenzen
Manchmal müssen Sie beim Berechnen von Daten möglicherweise auf Daten außerhalb Ihrer Arbeitsmappe verweisen. Dies wird als externer Verweis (Link) bezeichnet.
Um beim Erstellen einer Formel eine externe Referenz auszuwählen, navigieren Sie zur externen Arbeitsmappe und wählen Sie Ihre Referenz wie gewohnt aus.
Um zur anderen Arbeitsmappe zu navigieren, können Sie die Tastenkombination STRG + TAB verwenden oder zu gehen Ansicht > Fenster wechseln.
Nachdem Sie die Zelle ausgewählt haben, sehen Sie eine externe Referenz, die wie folgt aussieht:
Beachten Sie, dass der Name der Arbeitsmappe in Klammern [] eingeschlossen ist.
Sobald Sie die referenzierte Arbeitsmappe schließen, zeigt die Referenz den Dateispeicherort an:
Wenn Sie die Arbeitsmappe mit dem externen Link erneut öffnen, werden Sie aufgefordert, die automatische Aktualisierung von Links zu aktivieren. Wenn Sie dies tun, öffnet Excel den Referenzwert mit dem aktuellen Wert der Arbeitsmappe. Auch wenn es geschlossen ist! Vorsichtig sein! Sie können dies wollen oder nicht.
Benannte Bereiche und externe Referenzen
Was passiert mit Ihrem externen Zellbezug, wenn Zeilen/Spalten in der Referenzarbeitsmappe hinzugefügt oder daraus gelöscht werden? Wenn beide Arbeitsmappen geöffnet sind, werden die Zellbezüge automatisch aktualisiert. Wenn jedoch beide Arbeitsmappen nicht geöffnet sind, werden die Zellbezüge nicht aktualisiert und sind nicht mehr gültig. Dies ist ein großes Problem beim Verknüpfen mit externen Arbeitsmappen. Viele Fehler werden dadurch verursacht.
Wenn Sie auf externe Arbeitsmappen verlinken, sollten Sie den Zellbezug mit einem benannten Bereich benennen (Weitere Informationen finden Sie im vorherigen Abschnitt). Jetzt bezieht sich Ihre Formel auf den benannten Bereich, unabhängig davon, welche Änderungen in der externen Arbeitsmappe auftreten.
