Как да заменя имена на диапазони с препратки към клетки в Excel?

Когато работите с именувани диапазони в Excel, може да ги намерите полезни за четливост и управление на формули. Има обаче ситуации, в които може да искате да премахнете имена на диапазони и да възстановите действителните препратки към клетки, особено при споделяне на файлове, отстраняване на неизправности или стандартизиране на шаблони. В тази статия ще разгледаме различни начини за замяна на именувани диапазони със съответните им препратки към клетки в Excel.

Заменете имената на диапазони с препратки към клетки с VBA код

Бързо и лесно заменете имената на диапазони с препратки към клетки от Kutools за Excel

Например в този работен лист ще именувам диапазон A2:A6 като продажна ценаи наименувайте B2 като отстъпкаи след това да използвате името на диапазона във формула за изчисляване на новата цена. Вижте екранни снимки:

След като следвате бързите трикове, името на диапазона ще бъде заменено с препратка към клетка, както е показано по-долу:

Заменете имената на диапазони с препратки към клетки с VBA код

Използвайки VBA за замяна на имена на диапазони на формули с препратки към клетки, можете да го направите стъпка по стъпка, както следва:

1. Изберете диапазона, съдържащ формулите, чиито имена искате да замените с препратки към клетки.

2. Натиснете Alt + F11 за отваряне на нов Microsoft Visual Basic за приложения ще се покаже прозорец, след което щракнете Поставете > Модулии копирайте и поставете следните кодове в модула:

VBA код: Заменете имената на диапазони с Абсолютна препратка

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 код: Заменете имената на диапазони с относителна препратка

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. След това натиснете F5 за изпълнение на кода, се показва диалогов прозорец за избор на диапазон за замяна. Вижте екранна снимка:

4. След това кликнете OK. и всички имена на избрани диапазони на формули са заменени.

Бързо и лесно заменете имената на диапазони с препратки към клетки от Kutools за Excel

Въпреки че извършването на това ръчно или чрез VBA може да отнеме много време и да доведе до грешки, Kutools за Excel предоставя решение с едно щракване за опростяване на целия процес. Ако имате Kutools за Excel, неговата Преобразуване на име в референтен диапазон помощната програма ще ви помогне да замените имената на диапазони с препратки към клетки бързо и лесно.

1. Моля, приложете тази помощна програма, като щракнете Kutools > Инструменти за име > Преобразуване на име в референтен диапазон. 

2. Показва се диалогов прозорец, щракнете Обхват и след това изберете диапазона, който искате да замените с препратки към клетки от Базов диапазон, След това кликнете върху Сменете бутон. Вижте екранна снимка:

3. Ще се покаже друго поле за подкана, за да ви каже броя на модифицираните формули и непроменените формули, щракнете върху OK, а след това имената на диапазони в избрания ранг са заменени с препратки към клетки.

🔚 Заключение

Независимо дали работите с няколко формули или цяла работна книга, замяната на именувани диапазони с действителни препратки към клетки е чудесен начин за подобряване на прозрачността, съвместимостта и лекотата на сътрудничество.

• За напреднали потребители VBA предоставя гъвкаво и програмируемо решение за автоматизиране на процеса на подмяна.
• За всички останали Kutools за Excel предлага най-бързия и удобен за потребителя начин за изпълнение на задачата само с няколко щраквания – не е необходимо кодиране или редактиране на формули.

Избирайки метода, който най-добре отговаря на вашия работен процес, можете ефективно да изчистите вашите формули и да гарантирате, че вашите електронни таблици остават лесни за разбиране и поддръжка. Ако се интересувате от проучване на още съвети и трикове за Excel, нашият уебсайт предлага хиляди уроци, които да ви помогнат да овладеете Excel.

Относителна статия:

Променете препратката към клетка във формулите към имена на диапазони в Excel