Links in Excel – absolut, relativ und gemischt. Fehler beim Arbeiten mit relativen Links in Excel

Um Berechnungen mit Formeln im automatischen Modus zu erleichtern, werden Verweise auf Zellen verwendet. Je nach Art des Schreibens werden sie in drei Haupttypen unterteilt:

1. Relative Verknüpfungen. Wird für einfache Berechnungen verwendet. Beim Kopieren einer Formel müssen die Koordinaten geändert werden.
2. Absolute Verbindungen. Wenn Sie komplexere Berechnungen erstellen müssen, ist diese Option geeignet. Verwenden Sie zum Fixieren das Symbol „$“. Beispiel: $A$1.
3. gemischte Verbindungen. Diese Art der Adressierung wird in Berechnungen verwendet, wenn es notwendig ist, eine Spalte oder Zeile separat zu fixieren. Beispiel: $A1 oder A$1.

Wenn es notwendig ist, die Daten der eingegebenen Formel zu kopieren, werden Referenzen mit absoluter und gemischter Adressierung verwendet. Der Artikel zeigt anhand von Beispielen, wie Berechnungen mit verschiedenen Arten von Links durchgeführt werden.

Relativer Zellbezug in Excel

Dies ist eine Reihe von Zeichen, die die Position einer Zelle definieren. Links im Programm werden automatisch mit relativer Adressierung geschrieben. Zum Beispiel: A1, A2, B1, B2. Wenn Sie in eine andere Zeile oder Spalte wechseln, werden die Zeichen in der Formel geändert. Zum Beispiel Startposition A1. Die horizontale Bewegung ändert den Buchstaben zu B1, C1, D1 usw. Auf die gleiche Weise treten Änderungen auf, wenn Sie sich entlang einer vertikalen Linie bewegen, nur ändert sich in diesem Fall die Nummer – A2, A3, A4 usw. Wenn es notwendig ist, zu duplizieren B. einer gleichartigen Berechnung in einer benachbarten Zelle, erfolgt eine Berechnung mit einem relativen Bezug. Um diese Funktion zu verwenden, befolgen Sie einige Schritte:

1. Sobald die Daten in die Zelle eingegeben wurden, bewegen Sie den Cursor und klicken Sie mit der Maus. Die Hervorhebung durch ein grünes Rechteck zeigt die Aktivierung der Zelle und die Bereitschaft zur weiteren Arbeit an.
2. Durch Drücken der Tastenkombination Strg + C kopieren wir den Inhalt in die Zwischenablage.
3. Wir aktivieren die Zelle, in die Sie Daten oder eine zuvor geschriebene Formel übertragen möchten.
4. Durch Drücken der Kombination Strg + V übertragen wir die in der Zwischenablage des Systems gespeicherten Daten.

Fachberatung! Um die gleiche Art von Berechnungen in der Tabelle durchzuführen, verwenden Sie einen Lifehack. Wählen Sie die Zelle aus, die die zuvor eingegebene Formel enthält. Bewegen Sie den Mauszeiger über ein kleines Quadrat, das in der unteren rechten Ecke erscheint, und halten Sie die linke Maustaste gedrückt, um je nach durchgeführter Aktion in die unterste Zeile oder äußerste Spalte zu ziehen. Beim Loslassen der Maustaste wird die Berechnung automatisch durchgeführt. Dieses Werkzeug wird Autofill-Marker genannt.

Beispiel für einen relativen Link

Betrachten Sie zur Verdeutlichung ein Beispiel für eine Berechnung mit einer Formel mit einem relativen Bezug. Angenommen, der Besitzer eines Sportgeschäfts muss nach einem Jahr Arbeit den Gewinn aus dem Verkauf berechnen.

Die Reihenfolge der Aktionen:

1. Das Beispiel zeigt, dass die Spalten B und C verwendet wurden, um die Menge der verkauften Waren und deren Preis einzutragen. Um die Formel zu schreiben und die Antwort zu erhalten, wählen Sie dementsprechend Spalte D aus. Die Formel sieht folgendermaßen aus: = B2 *C

Passt auf! Verwenden Sie einen kleinen Trick, um das Schreiben einer Formel zu erleichtern. Setzen Sie das Zeichen „=“, klicken Sie auf die verkaufte Warenmenge, setzen Sie das Zeichen „*“ und klicken Sie auf den Preis des Produkts. Die Formel nach dem Gleichheitszeichen wird automatisch geschrieben.

2. Für die endgültige Antwort drücken Sie „Enter“. Als nächstes müssen Sie den Gesamtbetrag des Gewinns berechnen, den Sie von anderen Arten von Produkten erhalten haben. Nun, wenn die Anzahl der Zeilen nicht groß ist, können alle Manipulationen manuell durchgeführt werden. Um in Excel viele Zeilen gleichzeitig auszufüllen, gibt es eine nützliche Funktion, die es ermöglicht, die Formel auf andere Zellen zu übertragen.
3. Bewegen Sie den Mauszeiger über die untere rechte Ecke des Rechtecks ​​mit der Formel oder dem fertigen Ergebnis. Das Erscheinen eines schwarzen Kreuzes dient als Signal dafür, dass der Cursor nach unten gezogen werden kann. Somit wird eine automatische Berechnung des erhaltenen Gewinns für jedes Produkt separat durchgeführt.
4. Durch Loslassen der gedrückten Maustaste erhalten wir in allen Zeilen die richtigen Ergebnisse.

Wenn Sie auf Zelle D3 klicken, können Sie sehen, dass die Zellenkoordinaten automatisch geändert wurden und jetzt so aussehen: =B3 *C3 Daraus folgt, dass die Links relativ waren.

Mögliche Fehler beim Arbeiten mit relativen Links

Zweifellos vereinfacht diese Excel-Funktion die Berechnungen erheblich, aber in einigen Fällen können Schwierigkeiten auftreten. Betrachten wir ein einfaches Beispiel für die Berechnung des Gewinnkoeffizienten jeder Ware:

1. Erstellen Sie eine Tabelle und füllen Sie aus: A – Produktname; B – verkaufte Menge; C – Kosten; D ist der erhaltene Betrag. Angenommen, es gibt nur 11 Artikel im Sortiment. Daher werden unter Berücksichtigung der Beschreibung der Spalten 12 Zeilen ausgefüllt und der Gesamtgewinn beträgt D
2. Klicken Sie auf Zelle E2 und geben Sie ein =D2 /D13.
3. Nach dem Drücken der „Enter“-Taste erscheint der Koeffizient des relativen Umsatzanteils des ersten Artikels.
4. Strecken Sie die Spalte nach unten und warten Sie auf das Ergebnis. Das System gibt jedoch den Fehler „#DIV/0!“

Der Grund für den Fehler liegt in der Verwendung einer relativen Referenz für Berechnungen. Durch das Kopieren der Formel ändern sich die Koordinaten. Das heißt, für E3 sieht die Formel so aus =D3 /D13. Da die Zelle D13 nicht gefüllt ist und theoretisch einen Nullwert hat, gibt das Programm einen Fehler mit der Information aus, dass eine Division durch Null nicht möglich ist.

Wichtig! Um den Fehler zu korrigieren, ist es notwendig, die Formel so zu schreiben, dass die D13-Koordinaten fest sind. Relative Adressierung hat keine solche Funktionalität. Dazu gibt es eine andere Art von Links – absolute. 

Wie erstellt man einen absoluten Link in Excel

Dank der Verwendung des $-Symbols wurde es möglich, die Zellkoordinaten festzulegen. Wie das funktioniert, werden wir weiter betrachten. Da das Programm standardmäßig eine relative Adressierung verwendet, müssen Sie eine Reihe von Aktionen ausführen, um sie absolut zu machen. Analysieren wir die Lösung für den Fehler „So finden Sie den Koeffizienten aus dem Verkauf mehrerer Warenartikel“, indem Sie die Berechnung mit absoluter Adressierung durchführen:

1. Klicken Sie auf E2 und geben Sie die Koordinaten des Links ein =D2 /D13. Da der Link relativ ist, muss ein Symbol gesetzt werden, um die Daten zu fixieren.
2. Korrigieren Sie die Koordinaten von Zelle D Um diese Aktion auszuführen, stellen Sie dem Buchstaben, der die Spalten- und Zeilennummer angibt, ein „$“-Zeichen voran.

Fachberatung! Um die Eingabe zu erleichtern, genügt es, die Zelle zum Bearbeiten der Formel zu aktivieren und mehrmals die Taste F4 zu drücken. Bis Sie zufriedenstellende Werte erhalten. Die richtige Formel lautet wie folgt: =D2/$D$13.

3. Drücken Sie die „Enter“-Taste. Als Ergebnis der durchgeführten Aktionen sollte das richtige Ergebnis angezeigt werden.
4. Ziehen Sie die Markierung in die untere Zeile, um die Formel zu kopieren.

Aufgrund der Verwendung absoluter Adressierung in den Berechnungen werden die endgültigen Ergebnisse in den verbleibenden Zeilen korrekt sein.

Wie man einen gemischten Link in Excel einfügt

Für Formelberechnungen werden nicht nur relative und absolute Bezüge verwendet, sondern auch gemischte. Ihre Besonderheit ist, dass sie eine der Koordinaten fixieren.

• Um beispielsweise die Position einer Zeile zu ändern, müssen Sie das $-Zeichen vor die Buchstabenbezeichnung schreiben.
• Wenn im Gegensatz dazu das Dollarzeichen nach der Buchstabenbezeichnung geschrieben wird, bleiben die Anzeigen in der Zeile unverändert.

Daraus folgt, dass zur Lösung des vorigen Problems bei der Bestimmung des Warenverkaufskoeffizienten mit gemischter Adressierung die Zeilennummer festgelegt werden muss. Das heißt, das $-Zeichen steht hinter dem Spaltenbuchstaben, da sich dessen Koordinaten auch bei einem relativen Bezug nicht ändern. Nehmen wir ein Beispiel:

1. Geben Sie für genaue Berechnungen ein =D1/$D$3 und drücken Sie „Eingabe“. Das Programm gibt die genaue Antwort.
2. Um die Formel in nachfolgende Zellen in der Spalte zu verschieben und genaue Ergebnisse zu erhalten, ziehen Sie den Ziehpunkt in die unterste Zelle.
3. Als Ergebnis liefert das Programm die korrekten Berechnungen.

Achtung! Wenn Sie das $-Zeichen vor den Buchstaben setzen, gibt Excel einen Fehler „#DIV/0!“ aus, was bedeutet, dass diese Operation nicht ausgeführt werden kann.

„SuperAbsolute“-Adressierung

Betrachten wir zum Schluss ein weiteres Beispiel für einen absoluten Link – die „SuperAbsolute“-Adressierung. Was sind seine Merkmale und Unterschiede. Nehmen Sie die ungefähre Zahl 30 und geben Sie sie in Zelle B2 ein. Es ist diese Zahl, die die Hauptzahl sein wird, es ist notwendig, eine Reihe von Aktionen damit auszuführen, um sie beispielsweise zu einer Macht zu erheben.

1. Für die korrekte Ausführung aller Aktionen geben Sie in Spalte C folgende Formel ein: =$B$2^$D2. In Spalte D tragen wir den Gradwert ein.

2. Nachdem wir die „Enter“-Taste gedrückt und die Formel aktiviert haben, ziehen wir die Markierung in der Spalte nach unten.
3. Wir bekommen die richtigen Ergebnisse.

Unter dem Strich beziehen sich alle durchgeführten Aktionen auf eine feste Zelle B2, daher:

• Das Kopieren einer Formel aus Zelle C3 in Zelle E3, F3 oder H3 ändert das Ergebnis nicht. Sie bleibt unverändert – 900.
• Wenn Sie eine neue Spalte einfügen müssen, ändern sich die Koordinaten der Zelle mit der Formel, aber das Ergebnis bleibt ebenfalls unverändert.

Dies ist die Besonderheit des „SuperAbsolute“-Links: Wenn Sie verschieben müssen, ändert sich das Ergebnis nicht. Es gibt jedoch Situationen, in denen Daten aus Drittquellen eingefügt werden. Somit werden die Spalten zur Seite verschoben und die Daten werden auf die alte Weise in Spalte B2 gesetzt. Was passiert in diesem Fall? Beim Mischen ändert sich die Formel entsprechend der durchgeführten Aktion, dh sie zeigt nicht mehr auf B2, sondern auf C2. Aber da die Einfügung in B2 vorgenommen wurde, wird das Endergebnis falsch sein.

Referenz! Um Makros aus Drittanbieterquellen einfügen zu können, müssen Sie die Entwicklereinstellungen aktivieren (sie sind standardmäßig deaktiviert). Gehen Sie dazu zu Optionen, öffnen Sie die Ribbon-Einstellungen und aktivieren Sie das Kontrollkästchen in der rechten Spalte gegenüber „Entwickler“. Danach öffnet sich der Zugang zu vielen Funktionen, die dem durchschnittlichen Benutzer zuvor verborgen waren.

Dies wirft die Frage auf: Ist es möglich, die Formel aus Zelle C2 so zu ändern, dass trotz des Einfügens neuer Datenspalten die ursprüngliche Zahl aus Zelle B erfasst wird? Damit sich Änderungen in der Tabelle bei der Installation von Daten aus Drittquellen nicht auf die Gesamtermittlung auswirken, müssen Sie folgende Schritte durchführen:

1. Geben Sie anstelle der Koordinaten der Zelle B2 die folgenden Indikatoren ein: =INDIREKT(“B2”). Infolgedessen sieht die Formulierungszusammensetzung nach dem Verschieben folgendermaßen aus: =INDIREKT(“B2”)^$E2.
2. Dank dieser Funktion zeigt der Link immer auf das Quadrat mit den Koordinaten B2, egal ob Spalten in der Tabelle hinzugefügt oder entfernt werden.

Es muss verstanden werden, dass eine Zelle, die keine Daten enthält, immer den Wert „0“ anzeigt.

Zusammenfassung

Dank der Verwendung von drei Varianten der beschriebenen Verknüpfungen ergeben sich viele Möglichkeiten, die das Arbeiten mit Berechnungen in Excel erleichtern. Bevor Sie mit Formeln arbeiten, lesen Sie daher zuerst die Links und die Regeln für deren Installation.