Перейти к основному содержанию

Как изменить / преобразовать абсолютную ссылку в относительную ссылку в Excel?

Обычно в формуле можно использовать несколько типов ссылок на ячейки, и каждый тип ссылок на ячейки может помочь вам достичь различных целей в формуле. У вас могут быть следующие типы ссылок на ячейки, такие как абсолютная ссылка на ячейку, относительная ссылка на ячейку, относительная ссылка на строку, абсолютная ссылка на столбец, абсолютная ссылка на строку и относительная ссылка на столбец. Но иногда вам может потребоваться изменить назначение формулы, изменив ссылки на ячейки в формуле. Следующие хитрые методы расскажут, как изменить абсолютную ссылку на относительную в Excel.

Измените абсолютную ссылку на относительную с помощью клавиши F4

Измените абсолютную ссылку на относительную ссылку с кодом VBA

Быстро изменить абсолютную ссылку на относительную с помощью Kutools for Excel

Абсолютная ссылка

Относительная ссылка


стрелка синий правый пузырь Измените абсолютную ссылку на относительную с помощью клавиши F4

С помощью сочетания клавиш F4 мы можем легко переключить абсолютную ссылку на относительную. Для этого выполните следующие действия:

Поместите курсор за $ A $ 1, затем нажмите F4 три раза, он последовательно станет A $ 1, $ A1, A1. Смотрите скриншот:

Согласно этому шагу поместите курсор за $ B $ 1, чтобы получить B1.

И эта ссылка на ячейку станет относительной ссылкой из абсолютной ссылки.

Если необходимо изменить ссылки на ячейки нескольких формул, этот способ будет утомительным и трудоемким.


стрелка синий правый пузырь Измените абсолютную ссылку на относительную ссылку с кодом VBA

С помощью кода VBA вы можете быстро изменить диапазон ссылок на ячейки формул с абсолютных ссылок на относительные за один раз.

1. Выберите диапазон, который вы хотите изменить.

2. Нажмите Застройщик > Визуальный Бейсик или вы можете нажать Alt + F11, откроется новое окно Microsoft Visual Basic для приложений, щелкните Вставить > Модули, и введите следующий код в Модули:
VBA: преобразование абсолютной ссылки в относительную.

Sub ConverFormulaReferences()
'Updateby20140603
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
Dim xIndex As Integer
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)
xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _
& "Absolute = 1" & Chr(13) _
& "Row absolute = 2" & Chr(13) _
& "Column absolute = 3" & Chr(13) _
& "Relative = 4", xTitleId, 1, Type:=1)
For Each Rng In WorkRng
    Rng.Formula = Application.ConvertFormula(Rng.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex)
Next
End Sub

3. Затем нажмите документ-абсолютный-относительный-6 Нажмите кнопку, чтобы запустить код, и появится окно подсказки для выбора диапазона для преобразования, затем нажмите OK, и откроется другое диалоговое окно с запросом, какой тип вы хотите использовать. Тогда вы сможете выбрать нужный вам тип. В этом примере я вставлю 4. См. Скриншоты:

4. Затем нажмите OK. Все выбранные абсолютные ссылки были заменены на относительные ссылки в формуле.

Это многофункциональный код VBA с этим кодом; вы также можете изменить абсолютную ссылку, изменить абсолютную строку или изменить абсолютный столбец.


стрелка синий правый пузырь Быстро изменить абсолютную ссылку на относительную с помощью Kutools for Excel

Kutools for Excel: с более чем 100 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас

Kutools for Excel позволяют быстро и легко изменить абсолютную ссылку на относительную или наоборот. Пожалуйста, сделайте следующее:

Шаг 1. Выберите диапазон, содержащий формулы, ссылки на ячейки которых вы хотите изменить на листе.

Шаг 2. Нажмите Кутулс > Преобразовать ссылки. Он будет отображать Преобразование ссылок на формулы диалоговое окно. Смотрите скриншот:

Шаг 3. Проверьте К относительной и нажмите Ok or Применить в Справочник по формулам преобразования диалог. Это изменит абсолютную ссылку на относительную.

Если вы хотите изменить ссылки на ячейки на абсолютные столбцы или абсолютные строки, установите флажок В столбец абсолютный Вариант или Гребать абсолютное опцию.

Для получения более подробной информации о Преобразовать ссылку, Пожалуйста, посетите Описание функции Convert Reference.


Относительная статья:Заменить относительную ссылку на абсолютную

Лучшие инструменты для офисной работы

Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

вкладка kte 201905


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (7)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Function ConvertMyAddress(strRng As String) As String
Dim rngSelection As Range

Set rngSelection = Application.Range(strRng)
'// Test if empty
If rngSelection Is Nothing Then Exit Function

' MsgBox rngSelection.Address(0, 0) 'A1
' MsgBox rngSelection.Address(1, 0) '$A1
' MsgBox rngSelection.Address(0, 1) 'A$1
' MsgBox rngSelection.Address(1, 1) '$A$1
' MsgBox rngSelection.Address '$A$1
'
' MsgBox rngSelection.Parent.Name & "!" & rngSelection.Address(0, 0)
' MsgBox "[" & rngSelection.Parent.Parent.Name & "]" & rngSelection.Parent.Name & "!" & rngSelection.Address(0, 0)

ConvertMyAddress = rngSelection.Address(1, 1)

End Function
This comment was minimized by the moderator on the site
Wao Excellent teh VBA Code Works Perfect, Thank You very Much
This comment was minimized by the moderator on the site
Excel 2010. I clicked on cancel when the prompt comes up, but excel still ran the script and it froze my computer for several hours. I only had 1 cell highlighted anyway, so even if the script did run, why did it take so long to run? [i realize now that despite pressing cancel, the code ran and made every cell in the worksheet relative ! :( ] I was assigning it to a form button vs pressing play within VBA window. I've used other scripts from KuTools and never experienced this nonsense.
This comment was minimized by the moderator on the site
The F4 toggle works in all versions. but you MUST be in edit mode first. I always press F2 (puts you into edit mode) then F4 to toggle between the four options.
This comment was minimized by the moderator on the site
With the shortcut key F4, we can easily toggle the absolute reference to relative reference, please do //as// the following steps. Forgot to mention F4 didn't work with my excel (windows 7) but your macro and invite to Kutools is great. Sorry about repeat sends, I thought the code wasn't working.
This comment was minimized by the moderator on the site
Thank you very much. Descriptive, easy to follow information.
This comment was minimized by the moderator on the site
Thank you very much, descriptive, easy to follow information.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations