Anne Marie hat eine Reihe von Zellen, die Formeln enthalten, die relative Referenzen verwenden. Sie möchte die zweite Referenz in jeder Formel von relativ zu absolut ändern und fragt sich, ob es eine Möglichkeit gibt, dies zu tun, ohne die Formel jeder Zelle einzeln bearbeiten zu müssen.

Die Antwort auf diese Frage hängt ganz von den Eigenschaften der Formeln ab, mit denen Sie arbeiten. Wenn Ihre Daten wie die meisten Daten angeordnet sind, die ich gesehen habe, haben Sie normalerweise eine Spalte, die Formeln enthält, die auf Werten in anderen Spalten und / oder Zellen basieren. Normalerweise geben Sie die Formel oben in der Spalte ein und kopieren diese Formel dann für so viele Zeilen, wie Sie benötigen, in die Spalte. (Dies kann natürlich um 90 Grad gedreht werden, sodass die Formeln über Spalten statt über Spalten verteilt sind.)

Wenn dies die Art und Weise beschreibt, in der Ihre Daten angeordnet sind, müssen Sie lediglich die Formel in der oberen Zelle der Spalte so bearbeiten, dass sie die gewünschte absolute Referenz enthält, und dann die Eingabetaste drücken. Wählen Sie die Zelle erneut aus und doppelklicken Sie dann auf den Füllpunkt unten rechts in der Zelle. Am Ende wird die geänderte Formel in die Spalte kopiert. Das Fazit ist, dass Sie wirklich nur eine einzige Formel bearbeiten mussten, nicht alle Formeln einzeln.

Wenn Ihre Daten etwas anders angeordnet sind (nicht in einer Spalte in der beschriebenen Weise), können Sie versuchen, die Bearbeitung mithilfe von Suchen und Ersetzen für Sie durchzuführen. Zunächst müssen Sie jedoch herausfinden, ob die zweite Referenz in den Formeln, die Sie ändern möchten, ein konsistentes Muster enthält. Wenn Sie beispielsweise alle Verweise auf Spalte D so ändern möchten, dass sie absolut sind, können Sie folgendermaßen vorgehen:

  1. Wählen Sie den Zellenbereich aus, den Sie bearbeiten möchten.

  2. Drücken Sie Strg + H, um die Registerkarte Ersetzen des Dialogfelds Suchen und Ersetzen anzuzeigen.

  3. Klicken Sie auf die Schaltfläche Optionen, falls verfügbar. (Siehe Abbildung 1.)

  4. Geben Sie im Feld Suchen nach einen Großbuchstaben D ein.

  5. Geben Sie im Feld Ersetzen durch „$ D“ ein (ohne Anführungszeichen).

  6. Aktivieren Sie das Kontrollkästchen Match Case.

  7. Klicken Sie auf Alle ersetzen.

Dies sollte alle Verweise auf Spalte D durch $ D ersetzen, was anzeigt, dass der Verweis absolut ist. Sie sollten jedoch wissen, dass es auch alle Instanzen eines Großbuchstaben D in Textwerten ersetzt. Wenn in einer Ihrer Formeln bereits $ D enthalten ist, erhalten Sie $$ D.

(Sie können dieses letzte potenzielle Problem umgehen, indem Sie ein zweites Suchen und Ersetzen durchführen, in dem Sie $$ D finden, und es durch $ D ersetzen.) Bei diesem Ansatz werden auch alle Verweise auf Spalte D geändert, unabhängig davon, ob sie sich in der zweiten befinden Referenz oder nicht.

Wenn Sie unbedingt nur die zweite Referenz in einer Formel ändern möchten, müssen Sie ein Makro erstellen, um die Arbeit zu erledigen. Das Makro müsste Zellen untersuchen, die Formeln enthalten, die zweite Zellreferenz auswählen und dann sicherstellen, dass sich vor der Zeile und Spalte in der Referenz Dollarzeichen befinden. Dies ist jedoch leichter gesagt als getan; Der schwierige Teil besteht darin, die zweite Referenz in der Formel auszuwählen. Wie könnte ein Makro die zweite Referenz aus einer Formel wie der folgenden herausfinden?

=A23+SUM(B7:B19)/C15

Ist die zweite Referenz B7, B19, B7: B19 oder überspringen Sie diese Referenz vollständig, weil sie technisch ein Parameter für eine Funktion ist und stattdessen C15 verwenden? Solche Bestimmungen sollten in der Regel von Fall zu Fall untersucht werden. Dies bedeutet, dass Sie jede Zelle ohne Hilfe eines Makros wieder einzeln betrachten und möglicherweise bearbeiten können.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9897) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.