Cum se înlocuiește numele intervalelor cu referințe de celule în Excel?

Când lucrați cu intervale denumite în Excel, le puteți găsi utile pentru lizibilitate și gestionarea formulelor. Cu toate acestea, există situații în care este posibil să doriți să eliminați numele intervalelor și să restaurați referințele reale de celule, mai ales când partajați fișiere, depanați sau standardizați șabloanele. În acest articol, vom parcurge diferite moduri de a înlocui intervalele denumite cu referințele de celule corespunzătoare în Excel.

Înlocuiți numele intervalelor cu referințe de celule cu codul VBA

Înlocuiți rapid și ușor numele intervalelor cu referințe de celule prin Kutools for Excel

De exemplu, în această foaie de lucru, voi denumi gama A2: A6 ca preț de vânzareși denumiți B2 ca reducere, și apoi folosind ambele nume ale intervalului într-o formulă pentru a calcula noul preț. Vezi capturi de ecran:

După urmărirea trucurilor rapide, numele intervalului va fi înlocuit cu referința celulei, așa cum se arată mai jos:

Înlocuiți numele intervalelor cu referințe de celule cu codul VBA

Folosind VBA pentru a înlocui numele intervalului formulei cu referințe de celulă, puteți face acest lucru pas cu pas după cum urmează:

1. Selectați intervalul care conține formulele pe care doriți să le înlocuiți numele cu referințe de celulă.

2. presa Alt + F11 pentru a deschide un nou Microsoft Visual Basic pentru aplicații se va afișa fereastra, apoi faceți clic Insera > Moduleși copiați și lipiți următoarele coduri în modul:

Cod VBA: Înlocuiți numele intervalelor cu referință absolută

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

Cod VBA: Înlocuiți numele intervalelor cu referințe relative

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. Apoi apăsați F5 pentru a rula codul, se afișează un dialog pentru selectarea intervalului de înlocuit. Vedeți captura de ecran:

4. Apoi apasa OK. și toate numele intervalului de formule selectate au fost înlocuite.

Înlocuiți rapid și ușor numele intervalelor cu referințe de celule prin Kutools for Excel

Deși efectuarea acestui lucru manual sau prin VBA poate consuma mult timp și poate fi predispusă la erori, Kutools for Excel oferă o soluție cu un singur clic pentru a simplifica întregul proces. Dacă aveți Kutools for Excel, ict Convertiți numele în interval de referință utilitarul vă va ajuta să înlocuiți rapid și ușor numele intervalelor cu referințe de celule.

1. Vă rugăm să aplicați acest utilitar făcând clic pe Kutools > Instrumente de nume > Convertiți numele în domeniul de referință. 

2. Se afișează o casetă de dialog, faceți clic Gamă fila și apoi selectați intervalul pe care doriți să îl înlocuiți cu referințe de celule din Gama de bază. Apoi apasa Înlocui buton. Vedeți captura de ecran:

3. Va fi afișată o altă casetă de prompt pentru a vă spune numărul de formule modificate și formule neschimbate, faceți clic pe OK, iar apoi numele intervalelor din intervalul selectat au fost înlocuite cu referințe de celule.

🔚 Concluzie

Indiferent dacă aveți de-a face cu câteva formule sau cu un întreg registru de lucru, înlocuirea intervalelor denumite cu referințe de celule reale este o modalitate excelentă de a îmbunătăți transparența, compatibilitatea și ușurința colaborării.

• Pentru utilizatorii avansați, VBA oferă o soluție flexibilă și programabilă pentru a automatiza procesul de înlocuire.
• Pentru toți ceilalți, Kutools for Excel oferă cea mai rapidă și mai ușor de utilizat modalitate de a finaliza sarcina în doar câteva clicuri - nu este necesară programarea sau editarea formulelor.

Alegând metoda care se potrivește cel mai bine fluxului dvs. de lucru, vă puteți curăța eficient formulele și vă puteți asigura că foile de calcul rămân ușor de înțeles și de întreținut. Dacă sunteți interesat să explorați mai multe sfaturi și trucuri Excel, site-ul nostru web oferă mii de tutoriale pentru a vă ajuta să stăpâniți Excel.

Articolul relativ:

Schimbați referința celulei în formule pentru a numi intervalul în Excel