Ein absoluter Bezug hält eine Zelle fest, wenn du eine Formel kopierst. In =B2*$E$1 fixieren die Dollarzeichen E1: Füll die Formel nach unten aus, und jede Zeile multipliziert weiter mit E1, während B2 zu B3, B4 und so weiter wandert. Um die Dollarzeichen zu setzen, klickst du in der Formel auf den Bezug und drückst F4 (auf dem Mac Cmd+T). Formeln mit Funktionen kannst du in der Tabelle auch deutsch eingeben, mit Semikolons, etwa SUMME (englisch SUM): =SUMME($B$2:B2).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
=B2*$E$1C2 wurde einmal getippt und nach unten ausgefüllt. Klick auf C4: Die Formel dort lautet =B4*$E$1. Die Umsatzzelle ist in Zeile 4 gewandert, der Satz ist in E1 geblieben. Ändere den Satz in E1 in 8%, und jede Provision passt sich an.
Relative und absolute Bezüge
| Bezug | Name | Eine Zeile nach unten und eine Spalte nach rechts kopiert |
|---|---|---|
A1 | relativ | B2 |
$A$1 | absolut | $A$1 |
A$1 | gemischt: Zeile fixiert | B$1 |
$A1 | gemischt: Spalte fixiert | $A2 |
Ein einfacher Bezug wie B2 ist relativ: Excel speichert ihn als "die Zelle an dieser Position von mir aus", also zeigt eine Kopie eine Zeile tiefer auch eine Zeile tiefer. Genau das willst du für Daten pro Zeile, und es ist der Standard. Das $ vor einem Spaltenbuchstaben oder einer Zeilennummer fixiert diesen Teil.
Der klassische Fehler: nach unten ausfüllen ohne $
Hier ist die Provisionstabelle noch einmal, mit =B2*E1 in C2 und ohne Dollarzeichen. Die erste Zeile stimmt. Der Rest ist 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
=B3*E2C3 enthält =B3*E2: Der Bezug auf den Satz ist nach E2 gewandert, und E2 ist leer, und eine leere Zelle zählt als 0. Behebe es hier: Klick auf C2, ändere die Formel in =B2*$E$1 und drück die Eingabetaste. Die ganze Spalte zieht mit, weil C3:C6 Kopien von C2 sind. Ist die feste Zelle ein Divisor, wie in =B2/B7 für einen Anteil an einer Summe, zeigt derselbe Fehler #DIV/0! statt 0 (Prozent einer Summe ist der übliche Fall).
Mit F4 die Dollarzeichen setzen
Setz beim Tippen oder Bearbeiten einer Formel den Cursor in einen Bezug (oder direkt dahinter) und drück F4. Jeder Druck springt zur nächsten Form:
E1 -> $E$1 -> E$1 -> $E1 -> E1
Auf vielen Laptops steuert F4 den Bildschirm oder den Ton, dann drückst du Fn+F4. Auf dem Mac nimmst du Cmd+T oder Fn+F4. Du kannst die $-Zeichen auch selbst tippen.
Gemischte Bezüge: nur die Zeile oder nur die Spalte fixieren
Ein gemischter Bezug hat ein Dollarzeichen. $A2 liest immer Spalte A, lässt aber die Zeile wandern; B$1 liest immer Zeile 1, lässt aber die Spalte wandern. Mit beiden in einer Formel ergibt eine einzige Formel, über ein Raster ausgefüllt, eine Multiplikationstabelle:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
=$A2*B$1B2 enthält =$A2*B$1. Klick auf F6: Dort steht =$A6*F$1, die Zeilenzahl aus Spalte A mal die Spaltenzahl aus Zeile 1, also zeigt die Zelle 25. Entferne in B2 ein Dollarzeichen, und die Tabelle fällt auseinander, weil die Kopien dann benachbarte Zellen statt der Überschriften multiplizieren.
Nach demselben Muster berechnest du eine Preisliste zu mehreren Rabatten: =$A2*(1-B$1) mit den Preisen in Spalte A und den Rabattsätzen in Zeile 1.
Laufende Summe mit einem halb fixierten Bereich
Ein Bereich kann auch nur an einem Ende fixiert sein. =SUMME($B$2:B2) beginnt immer bei B2, während sein Ende beim Ausfüllen nach unten wandert, also addiert jede Zeile alles bis zu sich selbst:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
=SUMME($B$2:B2)C6 enthält =SUMME($B$2:B6) und zeigt 2230, die Summe aller fünf Monate. Derselbe halb fixierte Bereich lässt ZÄHLENWENN (englisch COUNTIF) mit =ZÄHLENWENN($A$2:A2;A2) zählen, wie oft ein Wert bisher vorgekommen ist, und so markierst du Duplikate nach dem ersten Auftreten.
Übung: eine Formel für die ganze Tabelle
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
Du bist dran: Schreib in B2 den Preis des ersten Produkts nach dem Rabatt in B1. Setz $ so, dass dieselbe Formel, nach rechts und unten bis D5 ausgefüllt, jeden Preis der Tabelle liefert.
Die Tabelle kopiert deine Formel in jede Zelle von B2:D5, so wie es das Ausfüllkästchen tun würde, und die Prüfung liest alle zwölf Ergebnisse. Ohne die richtigen Dollarzeichen lesen die Kopien in Zeile 3 oder Spalte C den falschen Preis oder den falschen Rabatt.
Absolute Bezüge auf ein anderes Blatt oder eine Suchtabelle
Die Dollarzeichen funktionieren genauso mit einem Blattnamen: =B2*Settings!$B$1. Am wichtigsten sind sie bei Suchen, wo die Tabelle stehen bleiben muss, während das Suchkriterium wandert: SVERWEIS (englisch VLOOKUP) mit =SVERWEIS(A2;$E$2:$F$10;2;FALSCH) sucht nach dem Ausfüllen weiter in E2:F10, während =SVERWEIS(A2;E2:F10;2;FALSCH) die Tabelle mit jeder Kopie eine Zeile nach unten schiebt und die ersten Zeilen nicht mehr findet (SVERWEIS). Wird eine feste Zelle in vielen Formeln verwendet, kannst du ihr mit Formeln > Namen definieren auch einen Namen geben und =B2*Rate schreiben; ein so definierter Name zeigt aus jeder Formel auf dieselbe Zelle, wie $E$1.
Häufig gestellte Fragen
Was bedeutet das $-Zeichen in einer Excel-Formel?
Es fixiert den Teil des Bezugs, der darauf folgt. In $E$1 sind die Spalte E und die Zeile 1 fixiert, also bleibt der Bezug E1, wohin die Formel auch kopiert wird. E$1 fixiert nur die Zeile, $E1 nur die Spalte.
Wie lautet die Tastenkombination für einen absoluten Bezug?
Klick beim Bearbeiten der Formel in den Bezug und drück F4 (Fn+F4 auf vielen Laptops). Jeder Druck wechselt zwischen $A$1, A$1, $A1 und A1. Auf dem Mac drückst du Cmd+T oder Fn+F4.
Was ist der Unterschied zwischen relativen und absoluten Bezügen?
Ein relativer Bezug wie B2 wandert mit, wenn du die Formel kopierst: eine Zeile tiefer wird er zu B3. Ein absoluter Bezug wie $B$2 bleibt $B$2. Absolute Bezüge nimmst du für eine einzelne Zelle, die jede Zeile braucht, etwa einen Satz oder eine Summe.
Was ist ein gemischter Bezug in Excel?
Ein Bezug mit einem Dollarzeichen: $A2 hält die Spalte fest und lässt die Zeile wandern, B$1 hält die Zeile fest und lässt die Spalte wandern. =$A2*B$1, über ein Raster nach rechts und unten ausgefüllt, ergibt eine Multiplikationstabelle.
Warum zeigt meine Formel nach dem Herunterziehen 0 oder #DIV/0!?
Ein Bezug, der fest bleiben sollte, ist mit der Kopie gewandert. Steht in Zeile 2 =B2/B7, bekommt Zeile 3 =B3/B8, und B8 ist leer. Fixiere die Summe mit =B2/$B$7 und füll erneut aus.
