Wie ersetzt man Bereichsnamen durch Zellbezüge in Excel?
Wenn Sie in Excel mit benannten Bereichen arbeiten, können diese für die Lesbarkeit und die Formelverwaltung nützlich sein. Es gibt jedoch Situationen, in denen Sie Bereichsnamen entfernen und tatsächliche Zellbezüge wiederherstellen möchten, insbesondere beim Teilen von Dateien, bei der Fehlersuche oder bei der Standardisierung von Vorlagen. In diesem Artikel werden wir verschiedene Möglichkeiten durchgehen, um benannte Bereiche durch ihre entsprechenden Zellbezüge in Excel zu ersetzen.
Ersetzen Sie Bereichsnamen durch Zellbezüge mit VBA-Code
Schnell und einfach Bereichsnamen durch Zellbezüge mit Kutools für Excel ersetzen
Zum Beispiel werde ich in diesem Arbeitsblatt den Bereich A2:A6 als saleprice benennen und B2 als discount, und dann beide Bereichsnamen in einer Formel verwenden, um den neuen Preis zu berechnen. Siehe Screenshots:
Nachdem Sie die schnellen Tricks angewendet haben, wird der Bereichsname durch den Zellbezug ersetzt, wie unten gezeigt:
Bereichsnamen durch Zellbezüge mit VBA-Code ersetzen
Mit VBA können Sie die Bereichsnamen in Formeln durch Zellbezüge ersetzen. Schritt für Schritt können Sie dies wie folgt erledigen:
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, dann klicken Sie auf Einfügen > Modul und kopieren und fügen Sie den folgenden Code in das Modul ein:
VBA-Code: Bereichsnamen durch absolute Bezüge ersetzen
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: Bereichsnamen durch relative Bezüge ersetzen
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 dann F5 , um den Code auszuführen. Ein Dialogfeld wird angezeigt, in dem Sie einen Bereich zum Ersetzen auswählen können. Siehe Screenshot:
4. Klicken Sie dann auf OK, und alle ausgewählten Formelbereichsnamen wurden ersetzt.
Schnell und einfach Bereichsnamen durch Zellbezüge mit Kutools für Excel ersetzen
Während das manuelle Ersetzen oder das Durchführen über VBA zeitaufwendig und fehleranfällig sein kann, bietet Kutools für Excel eine Ein-Klick-Lösung, um den gesamten Prozess zu vereinfachen. Wenn Sie Kutools für Excel haben, hilft Ihnen das Hilfsprogramm 'Namen in Bereichsverweise umwandeln', Bereichsnamen schnell und einfach durch Zellbezüge zu ersetzen.
1. Wenden Sie dieses Hilfsprogramm an, indem Sie auf Kutools > Namenswerkzeuge > Namen in Bereichsverweise umwandeln klicken.
2. Ein Dialogfeld wird angezeigt. Klicken Sie auf den Bereich-Tab und wählen Sie dann den Bereich aus, den Sie durch Zellbezüge ersetzen möchten, aus dem Basierend auf Bereich. Klicken Sie dann auf die Schaltfläche Ersetzen. Siehe Screenshot:
3. Eine weitere Meldungsbox wird angezeigt, die Ihnen die Anzahl der geänderten und unveränderten Formeln mitteilt. Klicken Sie auf OK, und die Bereichsnamen im ausgewählten Bereich wurden durch Zellbezüge ersetzt.
🔚 Schlussfolgerung
Ob Sie nun mit ein paar Formeln oder einer ganzen Arbeitsmappe arbeiten – das Ersetzen von benannten Bereichen durch tatsächliche Zellbezüge ist eine großartige Möglichkeit, Transparenz, Kompatibilität und die Zusammenarbeit zu verbessern.
- Für fortgeschrittene Benutzer 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 Möglichkeit, die Aufgabe in nur wenigen Klicks abzuschließen – kein Codieren oder Bearbeiten von Formeln erforderlich.
Indem Sie die Methode wählen, die am besten zu Ihrem Workflow passt, können Sie Ihre Formeln effizient bereinigen und sicherstellen, dass Ihre Tabellenkalkulationen leicht verständlich und wartbar bleiben. Wenn Sie mehr über Excel-Tipps und -Tricks erfahren möchten, bietet unsere Website Tausende von Tutorials, um Ihnen zu helfen, Excel zu beherrschen.
Ähnlicher Artikel:
Zellbezüge in Formeln in Bereichsnamen in Excel ändern
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins