So kopieren Sie Formeln mit Autofill in Excel 2016

Wenn Sie nur eine einzelne Formel in Excel 2016 kopieren müssen, verwenden Sie die AutoFill-Funktion oder die Befehle Kopieren und Einfügen. Diese Art der Formelkopie ist zwar weit verbreitet, kann aber nicht per Drag & Drop durchgeführt werden.

Vergessen Sie nicht die Option Summen im Schnellanalyse-Tool. Sie können damit im Handumdrehen eine Zeile oder Spalte mit Summen am unteren oder rechten Rand einer Datentabelle erstellen. Wählen Sie einfach die Tabelle als Zellbereich aus und klicken Sie auf die Schaltfläche Schnellanalyse gefolgt von Summen in der Palette. Klicken Sie dann auf die Schaltfläche Summe am Anfang der Palette, um Formeln zu erstellen, die die Spalten in einer neuen Zeile am unteren Rand der Tabelle summieren, und/oder auf die Schaltfläche Summe am Ende der Palette, um Formeln zu erstellen, die die Zeilen in einer neuen Summe summieren Spalte am rechten Ende.

So können Sie mit AutoFill eine Formel in einen Zellbereich kopieren. In dieser Abbildung sehen Sie das Arbeitsblatt Mother Goose Enterprises – 2016 Sales mit allen Unternehmen, diesmal jedoch mit nur einer Monatssumme in Zeile 12, die gerade durch Zelle E12 kopiert wird.

Kopieren einer Formel in einen Zellbereich mit AutoFill.

Die folgende Abbildung zeigt das Arbeitsblatt nach dem Ziehen des Ausfüllkästchens in Zelle B12, um den Zellbereich C12:E12 auszuwählen (wo diese Formel kopiert werden soll).

Das Arbeitsblatt nach dem Kopieren der Formel mit der Summe der monatlichen (und vierteljährlichen) Verkäufe.

Relativ gesehen

Die Abbildung zeigt das Arbeitsblatt, nachdem die Formel in einer Zelle in den Zellbereich C12:E12 kopiert wurde und Zelle B12 aktiv ist. Beachten Sie, wie Excel das Kopieren von Formeln handhabt. Die ursprüngliche Formel in Zelle B12 lautet wie folgt:

=SUMME(B3:B11)

Wenn die ursprüngliche Formel in Zelle C12 kopiert wird, ändert Excel die Formel geringfügig, sodass sie wie folgt aussieht:

=SUMME(C3:C11)

Excel passt den Spaltenverweis an und ändert ihn von B in C, da Sie von links nach rechts über die Zeilen kopiert haben.

Wenn Sie eine Formel in einen Zellbereich kopieren, der sich über die Zeilen erstreckt, passt Excel die Zeilennummern in den kopierten Formeln anstelle der Spaltenbuchstaben an die Position jeder Kopie an. Zelle E3 im Arbeitsblatt Mother Goose Enterprises – 2016 Sales enthält beispielsweise die folgende Formel:

=SUMME(B3:D3)

Wenn Sie diese Formel in Zelle E4 kopieren, ändert Excel die Kopie der Formel wie folgt:

=SUMME(B4:D4)

Excel passt die Zeilenreferenz an, um mit der neuen Zeile 4 Position aktuell zu bleiben. Da Excel die Zellbezüge in Kopien einer Formel relativ zur Kopierrichtung anpasst, werden die Zellbezüge als relative Zellbezüge bezeichnet .

Manche Dinge sind absolut!

Alle neuen Formeln, die Sie erstellen, enthalten natürlich relative Zellbezüge, es sei denn, Sie sagen etwas anderes. Da die meisten Kopien, die Sie von Formeln erstellen, Anpassungen der Zellbezüge erfordern, müssen Sie sich diese Anordnung nur selten überlegen. Dann stoßen Sie von Zeit zu Zeit auf eine Ausnahme, die eine Einschränkung erfordert, wann und wie Zellbezüge in Kopien angepasst werden.

Eine der häufigsten dieser Ausnahmen ist, wenn Sie einen Bereich verschiedener Werte mit einem einzigen Wert vergleichen möchten. Dies geschieht am häufigsten, wenn Sie berechnen möchten, wie viel Prozent jeder Teil an der Gesamtsumme beträgt. Im Arbeitsblatt Mother Goose Enterprises – 2016 Sales begegnen Sie beispielsweise dieser Situation, wenn Sie eine Formel erstellen und kopieren, die berechnet, wie viel Prozent jede Monatssumme (im Zellenbereich B14:D14) von der Quartalssumme in Zelle E12 ist.

Angenommen, Sie möchten diese Formeln in Zeile 14 des Arbeitsblatts Mother Goose Enterprises – 2016 Sales eingeben, beginnend in Zelle B14. Die Formel in Zelle B14 zur Berechnung des Prozentsatzes des Januar-Umsatzes zum ersten Quartal ist sehr einfach:

=B12/E12

Diese Formel teilt die Januar-Umsatzsumme in Zelle B12 durch die vierteljährliche Gesamtsumme in E12 (was könnte einfacher sein?). Sehen Sie sich jedoch an, was passieren würde, wenn Sie den Ausfüllpunkt eine Zelle nach rechts ziehen, um diese Formel in Zelle C14 zu kopieren:

=C12/F12

Die Anpassung der ersten Zellreferenz von B12 auf C12 ist genau das, was der Arzt verordnet hat. Allerdings ist die Anpassung der zweiten Zellreferenz von E12 auf F12 eine Katastrophe. Sie berechnen nicht nur nicht, wie viel Prozent die Februarverkäufe in Zelle C12 von den Verkäufen des ersten Quartals in E12 ausmachen, sondern Sie erhalten auch eine dieser schrecklichen #DIV/0! Fehler in Zelle C14.

Um zu verhindern, dass Excel einen Zellbezug in einer Formel in allen von Ihnen erstellten Kopien anpasst, konvertieren Sie den Zellbezug von relativ in absolut. Sie tun dies, indem Sie die Funktionstaste F4 drücken, nachdem Sie Excel in den Bearbeitungsmodus (F2) versetzt haben. Excel gibt an, dass Sie den Zellbezug absolut machen, indem Sie Dollarzeichen vor dem Spaltenbuchstaben und der Zeilennummer platzieren. In dieser Abbildung enthält beispielsweise Zelle B14 die richtige Formel, die in den Zellbereich C14:D14 kopiert werden soll:

Kopieren der Formel zur Berechnung des Verhältnisses von monatlichem zu vierteljährlichem Umsatz mit absolutem Zellbezug.

=B12/$E$12

Sehen Sie sich das Arbeitsblatt an, nachdem diese Formel mit dem Füllpunkt in den Bereich C14:D14 kopiert und Zelle C14 ausgewählt wurde (siehe folgende Abbildung). Beachten Sie, dass die Formelleiste anzeigt, dass diese Zelle die folgende Formel enthält:

Das Arbeitsblatt nach dem Kopieren der Formel mit dem absoluten Zellbezug.

=C12/$E$12

Da E12 in der ursprünglichen Formel in $E$12 geändert wurde, haben alle Kopien denselben absoluten (unveränderlichen) Bezug.

Wenn Sie eine Formel vermasseln und kopieren, bei der eine oder mehrere Zellbezüge absolut hätten sein sollen, Sie aber alle relativ belassen haben, bearbeiten Sie die ursprüngliche Formel wie folgt:

Doppelklicken Sie auf die Zelle mit der Formel oder drücken Sie F2, um sie zu bearbeiten.

Positionieren Sie die Einfügemarke irgendwo auf der Referenz, die Sie in absolut umwandeln möchten.

Drücken Sie F4.

Wenn Sie mit der Bearbeitung fertig sind, klicken Sie in der Formelleiste auf die Schaltfläche Eingabe und kopieren Sie dann die Formel mit dem Ausfüllkästchen in den durcheinandergebrachten Zellbereich.

Stellen Sie sicher, dass Sie F4 nur einmal drücken, um einen Zellbezug vollständig absolut zu ändern, wie ich zuvor beschrieben habe. Wenn Sie die Funktionstaste F4 ein zweites Mal drücken, erhalten Sie eine sogenannte gemischte Referenz, bei der nur der Zeilenteil absolut und der Spaltenteil relativ ist (wie in E$12). Wenn Sie dann erneut F4 drücken, zeigt Excel eine andere Art von gemischter Referenz an, bei der der Spaltenteil absolut und der Zeilenteil relativ ist (wie in $E12). Wenn Sie fortfahren und erneut F4 drücken, ändert Excel den Zellbezug wieder auf vollständig relativ (wie in E12).

Nachdem Sie wieder am Ausgangspunkt angelangt sind, können Sie mit F4 weiterhin dieselben Zellbezugsänderungen durchlaufen.

Wenn Sie Excel 2016 auf einem Gerät ohne Zugriff auf eine physische Tastatur mit Funktionstasten (z. B. ein Touchscreen-Tablet) verwenden, können Sie Zellenadressen in Ihren Formeln nur durch Öffnen von relativ in absolute oder eine gemischte Adresse konvertieren die Touch-Tastatur und verwenden Sie sie, um die Dollarzeichen vor dem Spaltenbuchstaben und/oder der Zeilennummer in der entsprechenden Zellenadresse in der Formelleiste hinzuzufügen.