Wie ersetzt man Bereichsnamen durch Zellbezüge in Excel?

Beim Arbeiten mit benannten Bereichen in Excel 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 von Bereichsnamen durch Zellbezüge mit VBA-Code

Schnelles und einfaches Ersetzen von Bereichsnamen durch Zellbezüge mit Kutools für Excel

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:

Ersetzen von Bereichsnamen durch Zellbezüge mit VBA-Code

Mit VBA können Sie die Bereichsnamen in Formeln durch Zellbezüge ersetzen. Sie können dies Schritt für Schritt 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. Klicken Sie dann auf Einfügen > Modul und kopieren Sie den folgenden Code in das Modul:

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 Sub

VBA-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 Sub

3. 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.

Schnelles und einfaches Ersetzen von Bereichsnamen durch Zellbezüge mit Kutools für Excel

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 dabei, 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, basierend auf dem Basisbereich. Klicken Sie anschließend 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.

🔚 Fazit

Ob Sie es mit einigen Formeln oder einer ganzen Arbeitsmappe zu tun haben, das Ersetzen von benannten Bereichen durch tatsächliche Zellbezüge ist eine großartige Möglichkeit, Transparenz, Kompatibilität und 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 mit nur wenigen Klicks abzuschließen – kein Codieren oder Bearbeiten von Formeln erforderlich.

Durch die Wahl der Methode, die am besten zu Ihrem Workflow passt, können Sie Ihre Formeln effizient aufräumen 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.

Verwandter Artikel:

Ändern von Zellbezügen in Formeln zu Bereichsnamen in Excel