Як замінити імена діапазонів на посилання на клітинки в Excel?

Деякі люди вважають за краще використовувати у своїх формулах безліч назв діапазонів, оскільки вони вважають, що вони зрозуміліші та легші для читання та розуміння. Але якщо ви хочете побачити фактичні посилання на клітинки, як це робити? Тут цей підручник представить два швидкі трюки для вирішення цієї проблеми.

Замініть назви діапазонів кодом VBA;

Швидко та легко замінюйте назви діапазонів за допомогою Kutools для Excel.

Наприклад, на цьому аркуші я буду називати діапазон A2: A6 як ціна продажу, а B2 назвіть як знижка, а потім використати назву діапазону у формулі для обчислення нової ціни. Дивіться скріншоти:

          

Після дотримання швидких прийомів ім’я діапазону буде замінено посиланням на клітинку, як показано нижче:

---

Замініть назви діапазонів кодом VBA

Використовуючи VBA для заміни імен діапазонів формул на посилання на клітинки, ви можете виконати це поетапно наступним чином:

1. Виберіть діапазон, що містить формули, які потрібно замінити їх іменами посиланнями на комірки.

2. клацання Розробник > Візуальний Бейсік or Alt + F11 відкрити нову Microsoft Visual Basic для програм відобразиться вікно, натисніть Insert>Модулі, а потім введіть наступні коди в модуль:

Код VBA: Замініть назви діапазонів абсолютним посиланням

Sub AbsoleteNamesWithRelativeRefs()
'Updateby20140603
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()
'Updateby20140603
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 Потім натисніть  для запуску коду, відображається діалогове вікно вибору діапазону для заміни. Дивіться знімок екрана:

4 Потім натисніть OK. і всі вибрані імена діапазонів формул замінено. Дивіться знімок екрана:

Замініть імена діапазонів на відносні посилання на комірки		Замініть імена діапазонів абсолютними посиланнями на комірки

---

Швидко та легко замінюйте назви діапазонів за допомогою Kutools для Excel

Якщо у вас є Kutools для Excel, його Замініть назви діапазонів Утиліта допоможе вам швидко та легко замінити імена діапазонів на посилання на комірки.

Kutools для Excel: з більш ніж 300 зручними надбудовами Excel, спробуйте безкоштовно без обмежень протягом 30 днів. Отримати зараз

Крок 1. Застосуйте цю утиліту, натиснувши Кутулс > Додаткові інструменти > Замініть назви діапазонів. Дивіться знімок екрана:

Крок 2. Спливаюче діалогове вікно з іменем Замініть назви діапазонів і натисніть Діапазон а потім виберіть діапазон, який ви хочете замінити посиланнями на клітинки, з Базовий діапазон. Дивіться знімок екрана:

Крок 3. Натисніть заміщати, Інший Замініть назви діапазонів з'явиться спливаюче діалогове вікно, щоб повідомити вам кількість змінених формул і незмінених формул, натисніть Ok, а потім імена діапазонів у вибраному дзвінку були замінені посиланнями на клітинки. Дивіться знімок екрана:

Для отримання більш детальної інформації про Замініть назви діапазонів, Будь ласка, відвідайте Замінити опис функції назв діапазонів.

---

Відносна стаття:

Змініть посилання на клітинки у формулах на назви діапазонів у Excel