Wie ersetzt man in Excel Zellennamen durch Zellbezüge?
Beim Arbeiten mit benannten Bereichen in Excel können diese die Lesbarkeit und Verwaltung von Formeln verbessern. Es gibt jedoch Situationen, in denen Sie Zellenname entfernen und die tatsächlichen Zellbezüge wiederherstellen möchten – insbesondere beim Teilen von Dateien, bei der Fehlersuche oder beim Standardisieren von Vorlagen. In diesem Artikel zeigen wir verschiedene Möglichkeiten, wie Sie benannte Bereiche durch ihre entsprechenden Zellbezüge in Excel ersetzen können.
Ersetzen Sie Zellenname mithilfe von VBA-Code durch Zellbezüge
Ersetzen Sie Zellenname schnell und einfach durch Zellbezüge mithilfe von Kutools für Excel
Beispielsweise benenne ich in diesem Arbeitsblatt den Bereich A2:A6 als saleprice und B2 als discount und verwende dann beide Zellennamen in einer Formel zur Berechnung des neuen Preises. Siehe Screenshots:
Nachdem Sie die schnellen Tricks angewendet haben, wird das Zellenname durch einen Zellbezug ersetzt, wie unten dargestellt:
Ersetzen Sie Zellenname mithilfe von VBA-Code durch Zellbezüge
Mit VBA können Sie die Namen in einer Formel durch Zellbezüge ersetzen. Gehen Sie dazu wie folgt vor:
1. Wählen Sie den Bereich aus, der die Formeln enthält, deren Namen Sie durch Zellbezüge ersetzen möchten.
2. Drücken Sie Alt + F11, um ein neues Microsoft Visual Basic for Applications-Fenster zu öffnen. Klicken Sie anschließend auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modul ein:
VBA-Code: Ersetzen Sie Zellenname durch absolute Bezüge
Sub AbsoleteNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
For Each xName In ThisWorkbook.Names
If InStr(Rng.Formula, xName.Name) > 0 Then
Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
End If
Next
Next
End SubVBA-Code: Ersetzen Sie Zellenname durch relative Bezüge
Sub ReplaceNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
For Each xName In ThisWorkbook.Names
If InStr(Rng.Formula, xName.Name) > 0 Then
Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(xName.RefersTo, "=", ""))
End If
Next
Next
End Sub3. Drücken Sie anschließend die F5-Taste, um den Code auszuführen. Daraufhin erscheint ein Dialogfeld, in dem Sie den zu ersetzenden Bereich auswählen können. Siehe Screenshot:
4. Klicken Sie anschließend auf OK, und alle ausgewählten Formel-Zellennamen wurden ersetzt.
Ersetzen Sie Zellenname schnell und einfach durch Zellbezüge mithilfe von Kutools für Excel
Während eine manuelle Durchführung oder die Verwendung von VBA zeitaufwendig und fehleranfällig sein kann, bietet Kutools für Excel eine Ein-Klick-Lösung, um den gesamten Prozess zu vereinfachen. Falls Sie Kutools für Excel installiert haben, hilft Ihnen das Namen in Bereichsverweise umwandeln-Tool dabei, Zellennamen schnell und einfach durch Zellbezüge zu ersetzen.
1. Wenden Sie dieses Tool an, indem Sie auf Kutools > Namenswerkzeuge > Namen in Bereichsverweise umwandeln klicken.
2. Es wird ein Dialogfeld angezeigt. Klicken Sie auf die Registerkarte Bereich und wählen Sie anschließend aus dem Feld Basierend auf Bereich denjenigen Bereich aus, dessen Inhalte Sie durch Zellbezüge ersetzen möchten. Klicken Sie dann auf die Schaltfläche Ersetzen. Siehe Screenshot:
3. Es erscheint ein weiteres Hinweisfenster, das Ihnen die Anzahl der geänderten und unveränderten Formeln anzeigt. Klicken Sie auf OK, um den Vorgang abzuschließen – anschließend wurden die Zellennamen im ausgewählten Bereich durch Zellbezüge ersetzt.
🔚 Fazit
Egal, ob Sie mit wenigen Formeln oder einer ganzen Arbeitsmappe arbeiten – das Ersetzen benannter Bereiche durch tatsächliche Zellbezüge steigert Transparenz, Kompatibilität und Zusammenarbeit.
- Für fortgeschrittene Anwender bietet VBA eine flexible und programmierbare Lösung, um den Ersetzungsprozess zu automatisieren.
- Für alle anderen bietet Kutools für Excel die schnellste und benutzerfreundlichste Lösung, um die Aufgabe mit nur wenigen Klicks zu erledigen – ganz ohne Programmierkenntnisse oder das manuelle Bearbeiten von Formeln.
Wählen Sie die Methode, die am besten zu Ihrem Arbeitsablauf passt, um Ihre Formeln effizient zu bereinigen und sicherzustellen, dass Ihre Tabellenkalkulationen stets leicht verständlich und einfach wartbar bleiben. Wenn Sie noch mehr Excel-Tipps und -Tricks entdecken möchten,bietet unsere Website Tausende von Anleitungen, um Excel zu meistern.
Verwandter Artikel:
Ändern Sie Zellbezüge in Formeln zu Zellenname in Excel
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins