Excel-Zellreferenz (Inhaltsverzeichnis)

  • Zellreferenz in Excel
  • Arten von Zellreferenzen in Excel
    • # 1 - Relative Zellreferenz in Excel
    • # 2 - Absolute Zellreferenz in Excel
    • # 3- Gemischte Zellreferenz in Excel

Zellreferenz in Excel

Wenn Sie gefragt werden, welche Excel-Funktion die Erfahrung beim Arbeiten mit Formeln wirklich magisch macht, ist es möglicherweise nicht erforderlich, die Werte fest in Ihre Formeln zu codieren (dies wird sogar nicht empfohlen).

In Excel bestehen Kalkulationstabellen aus Spalten und Zeilen, die "Zellen" darstellen. Jede Zelle entspricht einer genauen Spalte und einer genauen Zeile. Um dies anhand eines Beispiels zu erläutern, verweist die Zelle B2 auf die zweite Spalte (B) und die zweite Zeile (2). Dementsprechend zeigt D16 auf die vierte Spalte (D) und die sechzehnte Zeile (16). Der eigentliche Vorteil von Excel liegt in der Verwendbarkeit der Zellreferenzen in anderen Zellen beim Erstellen von Formeln.

Arten von Zellreferenzen in Excel

Wir haben drei verschiedene Arten von Zellreferenzen in Excel -

  1. Relative Zellreferenz in Excel
  2. Absolute Zellreferenz in Excel
  3. Gemischte Zellreferenz in Excel

Die Verwendung des richtigen Typs von Cell Reference in Excel in einem bestimmten Szenario spart viel Zeit und Mühe und erleichtert die Arbeit erheblich.

# 1 - Relative Zellreferenz in Excel

Relative Zellreferenzen in Excel beziehen sich auf eine Zelle oder einen Zellbereich in Excel. Jedes Mal, wenn ein Wert in eine Formel wie SUMIFS eingegeben wird, kann in Excel eine „Zellreferenz“ als Ersatz für eine fest codierte Zahl eingegeben werden. Eine Zellreferenz kann in der Form B2 vorliegen, wobei B dem Spaltenbuchstaben der betreffenden Zelle entspricht und 2 die Zeilennummer darstellt. Immer wenn Excel auf einen Zellverweis stößt, wird die bestimmte Zelle besucht, ihr Wert extrahiert und dieser Wert in der von Ihnen geschriebenen Formel verwendet. Wenn dieser Zellbezug in Excel an einer anderen Stelle dupliziert wird, ändern sich auch die relativen Zellbezüge in Excel entsprechend automatisch.

Wenn wir Zellen wie diese referenzieren, können wir dies mit jedem der beiden Zellreferenztypen in Excel erreichen: absolut und relativ. Die Abgrenzung zwischen diesen beiden unterschiedlichen Referenztypen ist das unterschiedliche inhärente Verhalten beim Ziehen oder Kopieren und Einfügen in verschiedene Zellen. Relative Zellreferenzen können sich beim Kopieren und Einfügen ändern und anpassen. absolute referenzen dagegen nicht. Um in Excel erfolgreich Ergebnisse zu erzielen, ist es wichtig, relative und absolute Zellreferenzen richtig zu verwenden.

Wie effektiv verwende ich die relative Zellreferenz in Excel?

Um die Vielseitigkeit und Verwendbarkeit dieser erstaunlichen Funktion von Excel umfassend verstehen zu können, müssen wir uns einige praktische Beispiele ansehen, um ihren wahren Wert zu erfassen.

Sie können diese Excel-Vorlage für Zellreferenzen hier herunterladen - Excel-Vorlage für Zellreferenzen

Beispiel 1

Betrachten wir ein einfaches Beispiel, um die Mechanismen der relativen Zellreferenz in Excel zu erläutern. Wenn wir die Summe zweier Zahlen in zwei verschiedenen Zellen haben möchten - A1 und A2, und das Ergebnis in einer dritten Zelle A3.

Also wenden wir die Formel = A1 + A2 an

Welches würde das Ergebnis als 100 in A3 ergeben.

Nehmen wir nun an, wir haben ein ähnliches Szenario in der nächsten Spalte (B). Die Zellen B1 und B2 haben zwei Zahlen und wir möchten die Summe in B3 haben.

Dies können wir auf zwei verschiedene Arten erreichen:

Hier schreiben wir physikalisch die Formel, um die zwei Zellen B1 und B2 in B3 zu addieren.

Das Ergebnis als 30.

Oder wir kopieren einfach die Formel aus Zelle A3 und fügen sie in Zelle B3 ein (es würde funktionieren, wenn wir die Formel auch von A3 nach B3 ziehen).

Wenn wir also den Inhalt von Zelle A3 kopieren und in B3 einfügen oder den Inhalt von Zelle A3 ziehen und in B3 einfügen, wird die Formel kopiert, nicht das Ergebnis. Das gleiche Ergebnis erzielen wir, wenn Sie mit der rechten Maustaste auf Zelle A3 klicken und die Option Kopieren verwenden.

Danach gehen wir zur nächsten Zelle B3 und klicken mit der rechten Maustaste und wählen "Formeln (f)".

Dies bedeutet, dass die Zelle A3 = A1 + A2 ist. Wenn wir A3 kopieren und eine Zelle nach rechts verschieben und in Zelle B3 einfügen, passt sich die Formel automatisch an und ändert sich in B3 = B1 + B2. Stattdessen wird die Summierungsformel für B1- und B2-Zellen angewendet.

Beispiel # 2

Betrachten wir nun ein weiteres praktisches Szenario, das das Konzept recht deutlich machen würde. Nehmen wir an, wir haben einen Datensatz, der aus dem Stückpreis eines Produkts und der jeweils verkauften Menge besteht. Nun ist es unser Ziel, den Verkaufspreis zu berechnen, der durch die folgende Formel beschrieben werden kann:

Verkaufspreis = Stückpreis x Verkaufte Einheiten

Um den Verkaufspreis ermitteln zu können, müssen wir jetzt den Einheitspreis mit den verkauften Einheiten für jedes Produkt multiplizieren. Daher wenden wir diese Formel jetzt für die erste Zelle im Verkaufspreis an, dh für Produkt 1.

Wenn wir die Formel anwenden, erhalten wir das folgende Ergebnis für Produkt 1:

Es multiplizierte erfolgreich die Stückkosten mit den für Produkt 1 verkauften Einheiten, dh Zelle G2 * Zelle H2, dh 1826, 00 * 20, was das Ergebnis 36520, 00 ergibt.

Jetzt sehen wir, dass wir noch 9 weitere Produkte haben. In realen Szenarien kann dies bis zu Hunderten oder Tausenden oder Zeilen betragen. Es wird schwierig und fast unmöglich, einfach die Formel für jede Zeile zu schreiben.

Daher verwenden wir die Funktion "Relative Referenz" von Excel und kopieren einfach den Inhalt von Zelle I2 und fügen alle verbleibenden Zellen in der Tabelle für die Spalte "Verkaufspreis" ein oder ziehen die Formel einfach von Zelle I2 in die restlichen Zeilen in diese Spalte und erhalten Sie die Ergebnisse für die gesamte Tabelle in weniger als 5 Sekunden.

Hier drücken wir Strg + D. Die Ausgabe sieht also wie folgt aus:

# 2 - Absolute Zellreferenz in Excel

Der Großteil unserer täglichen Arbeit in Excel umfasst den Umgang mit Formeln. Aus diesem Grund sind gute Kenntnisse in Bezug auf relative, absolute oder gemischte Zellen in Excel von großer Bedeutung.

Sehen wir uns folgendes an:

= A1 ist eine relative Referenz, bei der sich sowohl die Zeile als auch die Spalte ändern, wenn wir die Formelzelle kopieren.

= $ A $ 1 ist eine absolute Zellreferenz, sowohl die Spalte als auch die Zeile sind gesperrt und ändern sich nicht, wenn wir die Formelzelle kopieren. Somit bleibt der Zellenwert konstant.

In = $ A1 ist die Spalte gesperrt, und die Zeile kann sich für diese bestimmte Spalte ständig ändern.

In = A $ 1 ist die Zeile gesperrt und die Spalte kann sich für diese bestimmte Zeile ständig ändern.

Im Gegensatz zur relativen Referenz, die sich beim Verschieben in verschiedene Zellen ändern kann, ändert sich die absolute Referenz nicht. Das Einzige, was hier erforderlich ist, ist das vollständige Sperren der jeweiligen Zelle.

Wenn Sie in der Formel ein Dollarzeichen verwenden, wird eine Zellenreferenz zu einer absoluten Zellenreferenz, da das Dollarzeichen die Zelle sperrt. Wir können entweder die Zeile oder die Spalte mit dem Dollarzeichen sperren. Befindet sich das "$" vor einem Alphabet, wird eine Spalte gesperrt. Befindet sich das "$" vor einer Zahl, wird eine Zeile gesperrt.

# 3- Gemischte Zellreferenz in Excel

Wie kann man die absolute Zellreferenz in Excel effektiv nutzen und auch die gemischte Zellreferenz in Excel verwenden?

Um ein umfassendes Verständnis der absoluten und gemischten Zellreferenz in Excel zu erhalten, schauen wir uns das folgende Beispiel an.

Wir haben die Verkaufsdaten für 4 Verkaufsleiter in verschiedenen Monaten, in denen Verkäufe mehrmals im Monat aufgetreten sind.

Unser Ziel ist es, die konsolidierte Umsatzübersicht aller 4 Vertriebsleiter zu berechnen. Wir werden die SUMIFS-Formel anwenden, um das gewünschte Ergebnis zu erhalten.

Das Ergebnis ist wie folgt:

Beobachten wir die Formel, um zu sehen, was passiert ist.

  • In der "sum_range" haben wir $ C $ 2: $ C $ 17. Sowohl vor den Buchstaben als auch vor den Zahlen befindet sich ein Dollarzeichen. Somit sind sowohl die Zeilen als auch die Spalten für den Zellbereich gesperrt. Dies ist eine absolute Zellreferenz.
  • Als nächstes haben wir "criter_range1". Auch hier haben wir absoluten Zellbezug.
  • Danach haben wir "Kriterien1" - $ F2. Hier sehen wir, dass beim Kopieren der Formelzelle nur die Spalte gesperrt wird, was bedeutet, dass sich nur die Zeile ändert, wenn wir die Formel in eine andere Zelle kopieren (nach unten verschieben). Dies ist eine gemischte Zellreferenz.
  • Als nächstes haben wir „criter_range2“, das auch eine absolute Zellreferenz ist.
  • Das letzte Segment der Formel lautet "Kriterien2" - G $ 1. Hier stellen wir fest, dass das Dollarzeichen vor der Zahl und nicht vor dem Alphabet steht. Somit ist nur die Zeile gesperrt, wenn wir die Formelzelle kopieren. Die Spalte kann sich ändern, wenn wir die Formelzelle in eine andere Zelle kopieren (nach rechts verschieben). Dies ist eine gemischte Zellreferenz.

Ziehen Sie die Formel über die Übersichtstabelle, indem Sie zuerst die Tastenkombination Strg + D und dann die Tastenkombination Strg + R drücken. Wir erhalten folgendes Ergebnis:

Ein gemischter Zellenverweis bezieht sich nur auf eine bestimmte Zeile oder Spalte, z. B. = $ A2 oder = A $ 2 . Wenn Sie eine gemischte Zellreferenz erstellen möchten, können Sie die F4-Taste in der Formel zwei- bis dreimal drücken, um sich auf eine Zeile oder Spalte zu beziehen. Durch erneutes Drücken von F4 wird die Zellenreferenz in eine relative Referenz geändert.

Dinge, an die man sich erinnern sollte

  • Beim Kopieren der Excel-Formel ist im Allgemeinen eine relative Referenzierung erwünscht. Dies ist der Grund, warum dies das Standardverhalten von Excel ist. Manchmal kann es jedoch das Ziel sein, eine absolute Referenz anstelle einer relativen Zellreferenz in Excel anzuwenden. Mit Absolute Reference wird eine Zellenreferenz auf eine absolute Zellenadresse festgelegt, wodurch die Formel beim Kopieren unverändert bleibt.
  • Bei der relativen Referenzierung sind absolut keine Dollarzeichen erforderlich. Wenn wir die Formel von einem Ort an einen anderen kopieren, wird sie entsprechend angepasst. Wenn wir also = B1 + B2 in die Zelle B3 eingeben und dann dieselbe Formel in die Zelle C3 ziehen oder kopieren oder einfügen, wird die Formel durch die relative Zellreferenz automatisch auf = C1 + C2 angepasst .
  • Bei der relativen Referenzierung passen sich die referenzierten Zellen in der Formel automatisch entsprechend Ihrer Bewegung nach rechts, links, oben oder unten an.
  • Wenn wir bei der relativen Referenzierung eine Referenz auf Zelle D10 geben und dann eine Zelle nach unten verschieben, ändert sich dies in D11 . Wenn wir stattdessen eine Zelle nach oben verschieben, ändert sich dies in D9 . Wenn wir jedoch eine Zelle nach rechts verschieben, ändert sich die Referenz in E10, und wenn wir stattdessen eine Zelle nach links verschieben, ändert sich die Referenz automatisch in C10.
  • Durch einmaliges Drücken von F4 wird die relative Zellreferenz in Excel in die absolute Zellreferenz geändert.
  • Durch zweimaliges Drücken von F4 wird die Zellenreferenz in eine gemischte Referenz geändert, in der die Zeile gesperrt ist.
  • Durch dreimaliges Drücken von F4 wird die Zellenreferenz in eine gemischte Referenz geändert, in der die Spalte gesperrt ist.
  • Wenn Sie F4 zum vierten Mal drücken, wird die Zellenreferenz auf die relative Referenz in Excel zurückgesetzt.

Empfohlene Artikel

Dies war eine Anleitung zur Zellreferenz in Excel. Hier werden drei Arten von Zellreferenzen in Excel besprochen, nämlich absolute, relative und gemischte Zellreferenzen, und wie sie verwendet werden, zusammen mit praktischen Beispielen und einer herunterladbaren Excel-Vorlage. Sie können auch unsere anderen Artikelvorschläge durchgehen -

  1. Grundlegendes zur relativen Referenz in Excel
  2. Excel Absolute Reference | Einfaches Excel-Tutorial
  3. Verwendung der SUMIF-Funktion in Excel
  4. Verwendung der SUM-Funktion mit Beispielen

Kategorie:

Excel-Tipps