Unterschätzen Sie nicht die Kraft des $-Zeichens in Excel-Formeln

Microsoft Excel denkt normalerweise in Richtungen: „schau eine Zelle nach links.“ Das ist in Ordnung, bis Sie die Formel verschieben und die Referenz verloren geht. Wenn Sie wissen, wie Sie das Dollarzeichen ($) verwenden, können Sie Excel sagen, wann es eine permanente Adresse und wann es ein bewegliches Ziel betrachten soll, sodass Sie stabilere Formeln erstellen.

Ich benutze einen Verkaufs-Tracker, bei dem Preise und Mengen im Hauptdatensatz stehen und Steuer- und Provisionssätze in separaten „Konstanten“-Zellen. Das Ziel ist es, Formeln zu erstellen, die korrekt bleiben, wenn ich sie über das Raster ziehe.

Relative Bezüge: Lassen Sie Ihre Formeln mit Ihnen mitbewegen

Wenden Sie die gleiche relative Logik die Spalte hinunter an

Bevor wir zur Magie des $-Zeichens kommen, müssen wir verstehen, wie Excel ohne dieses funktioniert. Ein standardmäßiger Zellbezug wie B6 ist tatsächlich nur eine Reihe von Anweisungen. Zum Beispiel, wenn man =B6*C6 in die Zelle D6 eingibt, denkt Excel: „Nimm die Zelle, die zwei Felder links von mir liegt, und multipliziere sie mit der Zelle, die ein Feld links von mir liegt.“

Diese relative Logik ist es, die Excel so leistungsfähig macht. Da die Formel auf ihrer Position und nicht auf einer festen Adresse basiert, müssen Sie die Berechnung nicht für jede Zeile neu schreiben:

1. Geben Sie in Zelle E6 die Formel =C6*D6 ein und drücken Sie Ctrl+Enter, um die Formel zu übernehmen, während die aktuelle Zelle ausgewählt bleibt. Dies berechnet die Zwischensumme für Widget A.
2. Klicken Sie auf das Ausfüllkästchen in der unteren rechten Ecke der Zelle E6 und ziehen Sie es nach unten bis zur Zelle E8.
3. Wählen Sie die Zelle E7 aus und beachten Sie, dass sich die Formel auf =C7*D7 angepasst hat.

Weil Sie die Formel um eine Zeile nach unten verschoben haben, hat Excel die Bezugnahmen ebenfalls um eine Zeile nach unten verschoben, um sie anzupassen. Wenn Sie die Zelle E6 kopieren und in die Zelle K15 einfügen würden, würde sich die Formel zu =I15*J15 ändern.

Absolute Referenzen: Erstellen Sie einen Anker für Ihre Referenz

Beziehe dich konsequent auf dieselbe Zelle

Relative Verweise sind großartig, bis Sie auf eine einzelne, feste Zelle verweisen müssen - wie unseren "Globalen Steuer"-Multiplikator in Zelle B3. Wenn Sie versuchen, hier eine relative Formel zu verwenden, bricht alles schnell zusammen.

Wenn Sie =E6*B3 in die Zelle F6 eingeben, funktioniert es für die erste Zeile. Wenn Sie es jedoch nach unten ziehen, wird die Formel in F7 zu =E7*B4. Da die Zelle B4 leer ist, ergibt Ihre Berechnung null.

Um dies zu beheben, müssen Sie das $-Zeichen verwenden, um die Formel an die Zelle B3 zu binden:

1. Geben Sie in Zelle F6 =E6*$B$3 ein und drücken Sie Strg+Enter. Das $ vor dem B sperrt die Spalte, und das $ vor der 3 sperrt die Zeile.
2. Klicken Sie auf das Ausfüllkästchen und ziehen Sie es nach unten bis F8.
3. Wählen Sie die Zelle F7 aus. Sie werden nun sehen, dass dort =E7*$B$3 steht. Die relativen Gesamtsummen-Koordinaten haben sich verschoben, aber die Steuerkoordinate blieb genau dort, wo Sie sie platziert haben.

Profi-Tipp: Der sauberere Weg, eine Konstante zu referenzieren

Während $B$3 die Aufgabe erfüllt, ziehen Profis es oft vor, der Zelle stattdessen einen Namen zu geben. Wenn Sie die Zelle B3 auswählen und Global_tax in das Namenfeld (das kleine Feld links neben der Bearbeitungsleiste) eingeben, kann Ihre Formel =E6*Global_tax lauten.

Benannte Bereiche sind standardmäßig absolut, was bedeutet, dass sie an ihrem Platz bleiben, ohne dass visuelle Unordnung durch $-Zeichen entsteht.

Gemischte Verweise: Beherrsche das halbgeschlossene Raster

Wählen Sie, wann Sie die Zeile oder die Spalte fixieren möchten

Gemischte Bezüge sind das Geheimnis, um hocheffiziente Tabellenkalkulationen in Excel zu erstellen. In unserem Tracker möchten wir zwei verschiedene Provisionsstufen (Stufe 1 in C3 und Stufe 2 in D3) basierend auf den Summen in Spalte F berechnen. Anstatt für jede Spalte unterschiedliche Formeln zu schreiben, können wir eine Formel schreiben und sie über das gesamte Raster ziehen:

1. Geben Sie in Zelle G6 =$F6*C$3 ein und drücken Sie Strg+Enter. Das $F sperrt die Spalte, sodass beim Ziehen der Formel in Spalte H weiterhin die Summen in Spalte F berücksichtigt werden. Das $3 sperrt die Zeile, sodass beim Herunterziehen weiterhin die Provisionssätze in Zeile 3 berücksichtigt werden.
2. Klicken und ziehen Sie den Füllgriff von G6 nach H6.
3. Während sowohl G6 als auch H6 noch ausgewählt sind, ziehen Sie das Ausfüllkästchen nach unten bis H8.
4. Überprüfen Sie Ihr Ergebnis in Zelle H8. Die Formel lautet =$F8*D$3.

Aufgrund der strategisch platzierten $-Zeichen wusste Excel genau, welche Teile der Formel verschoben und welche unverändert bleiben sollten.

Jenseits der Zelle: Nehmen Sie das $-Zeichen zu anderen Werkzeugen

Verwenden Sie verschiedene Bezugstypen in der bedingten Formatierung

Die Macht des $ist nicht auf Standard-Zellenformeln beschränkt - sie ist auch der Motor hinter fortgeschrittener bedingter Formatierung. Hier geraten viele Menschen in Schwierigkeiten, weil sie nicht erkennen, dass Excels standardmäßige „Drift“-Logik in Dialogformelfeldern genauso gilt wie im Raster.

Angenommen, Sie möchten jede Zelle in der Zeile grün färben, wenn der Gesamtbetrag inkl. Steuer in Spalte F über 100 $liegt. In diesem Fall müssen Sie eine gemischte Referenz verwenden. Ohne das $-Zeichen bewertet jede Zelle ihre eigene relative Referenz, was zu inkonsistenter Formatierung führt.

Hier ist, was du tun musst:

1. Wählen Sie Ihre Daten von B6 bis H8 aus (nicht die Kopfzeile).
2. Klicken Sie auf der Start-Registerkarte auf Bedingte Formatierung und wählen Sie Neue Regel aus.
3. Klicken Sie auf Eine Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
4. Geben Sie im Feld Werte formatieren, für die diese Formel wahr ist =$F6>100 ein.
5. Klicken Sie auf Format, wählen Sie eine Füllfarbe (wie Hellgrün) und klicken Sie auf OK.

Weil Sie die Spalte ($F) gesperrt haben, ist jede Zelle in der Zeile gezwungen, auf den Wert in Spalte F zurückzublicken, um zu entscheiden, ob sie grün werden soll.

Dieselbe Logik gilt, wann immer Sie ein Formelfeld in einem Excel-Dialog sehen, zum Beispiel wenn Sie Datenüberprüfungsregeln einrichten.

Das $-Zeichen fungiert als Lenkrad für Ihre Formeln und ermöglicht es Ihnen zu steuern, welche Bezüge fest bleiben und welche sich verschieben. Aber es ist nur eines von vielen Microsoft Excel-Symbolen, auf die Sie stoßen werden, wenn Ihre Arbeit komplexer wird. Das Erlernen der Nutzung dieser Zeichen stellt sicher, dass Ihre Tabellen stabiler bleiben, Ihre Mathematik genau bleibt und Ihr Arbeitsablauf unendlich effizienter wird.