Kopieren von Formeln mit einem Muster (Microsoft Excel)
Angenommen, Sie haben ein Arbeitsblatt, das eine Reihe von Formeln in den Zellen A1: A3 enthält. Zelle A1 enthält die Formel = Blatt1! B4, Zelle A2 enthält = Blatt1! B18 und Zelle A3 enthält = Blatt1! B32. Möglicherweise müssen Sie dieses Muster in der Spalte fortsetzen, sodass A4 = Sheet1! B46 usw. enthält.
Das Problem ist, wenn Sie die Zellen einfach kopieren, wird das Muster nicht fortgesetzt. Stattdessen werden die Formeln basierend auf der Beziehung der Zielzelle zur Quellzelle angepasst. Wenn Sie also A1: A3 in A4: A6 einfügen, enthält A4 = Sheet1! B7, was nicht das ist, was Sie wollen. (Dies geschieht unabhängig davon, ob Sie die Zellen speziell kopieren und einfügen oder füllen, indem Sie den Füllgriff ziehen.)
Es gibt keine Möglichkeit, ein Muster fortzusetzen, während eine Formel kopiert wird. Stattdessen müssen Sie zunächst überlegen, wie Sie die Formel zusammensetzen.
Betrachten Sie zum Beispiel diese Formel:
=INDIRECT("Sheet1!B"&((ROW()-1)*14)+4)
Diese Formel erstellt eine Referenz basierend auf der Position der Zelle, in der die Formel platziert ist. Wenn diese Formel in Zelle A1 platziert wird, gibt die ROW-Funktion 1 zurück, die Zeile, in der die Formel platziert wird.
Somit lautet die Formel wie folgt:
=INDIRECT("Sheet1!B"&((1-1)*14)+4) =INDIRECT("Sheet1!B"&(0*14)+4) =INDIRECT("Sheet1!B"&0+4) =INDIRECT("Sheet1!B"&4) =INDIRECT("Sheet1!B4")
Was zurückgegeben wird, ist der Wert bei Sheet1! B4, genau wie ursprünglich gewünscht.
Wenn Sie diese Formel jedoch in die Spalte kopieren, gibt die ROW-Funktion in jeder Zeile etwas anderes zurück. Tatsächlich wird die ROW-Funktion zu einer Möglichkeit, den Versatz jeder Formel um 14 Zeilen gegenüber der vorherigen zu erhöhen – genau das, was Sie wollten.
Sie können auch einen etwas anderen Ansatz verwenden, diesmal mit der OFFSET-Funktion:
=OFFSET(Sheet1!$B$4,(ROW()-1)*14,0)
Diese Formel erfasst einen Wert basierend auf der Zeile, in der die Formel platziert wird (wiederum mit der ROW-Funktion) und versetzt von Zelle Sheet1! B4.
Die Formel wird in die erste Zeile einer Spalte eingefügt und dann in diese Spalte kopiert. Sie gibt Werte gemäß dem gewünschten Muster zurück.
Ein anderer Ansatz besteht darin, die gewünschten Formeln direkt zu erstellen. Sie können dies am besten tun, indem Sie die folgenden Schritte ausführen:
-
Beginnen Sie mit einem neuen, leeren Arbeitsblatt in einer Arbeitsmappe, die ein Arbeitsblatt mit dem Namen „Sheet1“ enthält.
-
Geben Sie in Zelle A1 die Formel = Sheet1! B4 ein.
-
Da das Formelmuster alle 14 Zeilen ist, markieren Sie den Bereich A1: A14. Nur die erste Zelle sollte die Formel haben; Die anderen 13 sind leer.
-
Ziehen Sie den Füllgriff (das kleine Quadrat in der unteren rechten Ecke der Auswahl für eine gute Anzahl von Zeilen nach unten – möglicherweise 1.000 oder so.
Der Bereich A1: A1000 sollte weiterhin hervorgehoben sein. |||| Wählen Sie Bearbeiten | Gehe zu | Spezial. Excel zeigt das Dialogfeld Gehe zu Spezial an. (Siehe Abbildung 1.)
-
Klicken Sie auf das Optionsfeld Leerzeichen und dann auf OK. Alle Leerzeichen (leer)
Zellen in der Auswahl werden ausgewählt.
-
Wählen Sie Bearbeiten | Löschen | Zellen nach oben verschieben /
Das Ergebnis ist, dass Sie nur die Formeln mit dem gewünschten Muster erhalten.
ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.
Dieser Tipp (3067) gilt für Microsoft Excel 97, 2000, 2002 und 2003.