Как заменить имя ячейки на ссылку на ячейку в Excel?
При работе с именованными диапазонами в Excel вы наверняка оцените, насколько они упрощают чтение и управление формулами. Однако иногда возникает необходимость удалить имена ячеек и вернуться к исходным ссылкам — особенно при обмене файлами, диагностике проблем или стандартизации шаблонов. В этой статье мы рассмотрим различные способы замены именованных диапазонов на соответствующие им ссылки на ячейки в Excel.
Замена Имя ячейки на ссылки на ячейки с помощью кода VBA
Быстрая и простая замена Имя ячейки на ссылки на ячейки с помощью Kutools для Excel
Например, на этом листе я назову диапазон A2:A6 как saleprice, а ячейку B2 — как discount, а затем использую оба имени в формуле для расчёта новой цены. См. скриншоты:
После применения описанных выше быстрых приёмов Имя ячейки будет заменён на ссылку на ячейку, как показано ниже:
Замена Имя ячейки на ссылки на ячейки с помощью кода 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 Sub3. Затем нажмите клавишу F5, чтобы запустить код. Появится диалоговое окно для выбора диапазона, который нужно заменить. См. скриншот:
4. Затем нажмите кнопку ОК, и все выбранные формулы с именами ячеек будут заменены.
Быстрая и простая замена Имя ячейки на ссылки на ячейки с помощью Kutools для Excel
Хотя выполнение этой задачи вручную или с помощью VBA может быть трудоёмким и чреватым ошибками, Kutools для Excel предлагает решение в один клик, которое значительно упрощает весь процесс. Если у вас установлен Kutools для Excel, его утилита Преобразовать имена в ссылки на диапазон поможет вам быстро и легко заменить имена ячеек на соответствующие ссылки.
1. Примените эту утилиту, последовательно выбрав Kutools > Инструменты имени > Преобразовать имена в ссылки на диапазон.
2. Появится диалоговое окно. Перейдите на вкладку Диапазон и выберите диапазон, который нужно заменить на ссылки на ячейки, из поля На основе диапазона. Затем нажмите кнопку Заменить. См. скриншот:
3. Появится ещё одно окно с информацией о количестве изменённых и неизменённых формул. Нажмите кнопку ОК, и имена ячеек в выбранном диапазоне будут заменены на ссылки на ячейки.
🔚 Заключение
Независимо от того, имеете ли вы дело с несколькими формулами или целой книгой, замена именованных диапазонов на фактические ссылки на ячейки — отличный способ повысить прозрачность, совместимость и упростить совместную работу.
- Для опытных пользователей VBA предоставляет гибкое и программируемое решение для автоматизации процесса замены.
- Для всех остальных Kutools для Excel предлагает самый быстрый и удобный способ выполнить задачу всего за несколько кликов — без написания кода или редактирования формул.
Выбрав метод, который лучше всего подходит вашему рабочему процессу, вы сможете эффективно упростить свои формулы и поддерживать таблицы чёткими и удобными в обслуживании. Если вы хотите узнать ещё больше полезных советов и приёмов по работе с Excel,на нашем сайте представлены тысячи обучающих материалов, которые помогут вам освоить Excel.
Смежная статья:
Замена ссылок на ячейки в формулах на Имя ячейки в Excel
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек