Как поменять в формулах относительные ссылки на абсолютные и наоборот?
Многие знают, как изменить стиль ссылок в формуле с абсолютной на относительную и наоборот (за это отвечают знаки доллара внутри ссылки): выделяем ссылку внутри формулы и жмем F4. Ссылка последовательно меняется (на примере полностью относительной -
- полностью абсолютная
$C$3:$C$8 => - абсолютные строки и относительные столбцы
C$3:C$8 => - абсолютные столбцы и относительные строки
$C3:$C8 => - полностью относительная
C3:C8
А теперь представим ситуацию: когда-то были созданы кучи формул в разных местах листа и вдруг понадобилось сменить ссылки на ячейки в формулах с относительных на абсолютные или наоборот, или закрепить только столбцы, а строки оставить "плавающими" или т.п.. Вручную это делать довольно долго и совсем не интересно. Вот в таких случаях и понадобится код, который поможет сделать данный процесс более приятным и более быстрым. Я бы сказал - два клика мышью и часы работы в пятницу сэкономлены :-)
Sub Change_Style_In_Formulas() Dim rR As Range, rFormulasRng As Range, rA As Range Dim lType As String Dim aF_Source, aF_Res Dim lr As Long, lc As Long lType = InputBox("Изменить тип ссылок у формул?" & Chr(10) & Chr(10) _ & "1 - Все абсолютные" & Chr(10) _ & "2 - Абсолютная строка/Относительный столбец" & Chr(10) _ & "3 - Относительная строка/Абсолютный столбец" & Chr(10) _ & "4 - Все относительные", "www.excel-vba.ru") If StrPtr(lType) = 0 Then Exit Sub If Val(lType) < 1 Or Val(lType) > 4 Then MsgBox "Неверно указан тип преобразования!", vbCritical Exit Sub End If On Error Resume Next Set rR = Application.InputBox("Выделите диапазон с формулами", "www.excel-vba.ru", , , , , , Type:=8) If rR Is Nothing Then Exit Sub On Error Resume Next Set rFormulasRng = rR.SpecialCells(xlFormulas) If rFormulasRng Is Nothing Then MsgBox "Выбранный диапазон не содержит формул", 64, "www.excel-vba.ru" Exit Sub End If On Error GoTo 0 For Each rA In rFormulasRng.Areas aF_Source = rA.Formula aF_Res = Application.ConvertFormula(aF_Source, xlA1, xlA1, Val(lType)) If IsArray(aF_Res) Then For lr = LBound(aF_Res, 1) To UBound(aF_Res, 1) For lc = LBound(aF_Res, 2) To UBound(aF_Res, 2) If IsError(aF_Res(lr, lc)) Then aF_Res(lr, lc) = aF_Source(lr, lc) End If Next Next Else If IsError(aF_Res) Then aF_Res = aF_Source End If End If rA.Formula = aF_Res Next Set rFormulasRng = Nothing MsgBox "Конвертация стилей ссылок завершена!", 64, "www.excel-vba.ru" End Sub |
- копируем приведенный выше код, переходим в редактор VBA (
Alt +F11 ) - создаем стандартный модуль (Insert -Module)
- переходим на лист, формулы в котором надо преобразовать, жмем
Alt +F8 , выбираем макрос Change_Style_In_Formulas и жмем Выполнить
Так же можно создать кнопку на листе для вызова макроса или поместить код в надстройку - Как создать свою надстройку?
Сразу после запуска сначала будет предложено выбрать тип ссылок:
- Все абсолютные;
- Абсолютная строка/Относительный столбец;
- Относительная строка/Абсолютный столбец;
- Все относительные
необходимо указать целое число от 1 до 4, в зависимости от того, в какой тип необходимо преобразовать все ссылки.
Далее будет предложено выбрать диапазон ячеек с формулами.
Все, после этого код все сделает и выдаст сообщение "Конвертация стилей ссылок завершена".
Примечание: данный код не учитывает формулы массива и после работы кода такие формулы станут обычными.
Так же код может некорректно работать с длинными и сложными формулами, и формулами внутри умных таблиц - особенность VBA. Для таких формул, если их преобразование завершилось ошибкой, изменения не будут применены и формула останется такой же как была
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Супер макрос. А можно сделать, чтобы макрос копировал значение (относительная, или абсолютная ячейка) в аналогичной формуле? То есть закрепить ячейки аналогично образцу.
Спасибо, хороший скрипт.
Спасибо огромное. Я думала, что до утра буду жать на клавишу F4.
БлагоДарю.
Спасибо, работает!
Макрос отличный, но есть проблема. В некоторых диапозонах превращает содержимое ячейки в #ЗНАЧ!. Именно в диапозонах, т.к. одна и таже формула, находящаяся в разных ячейка может быть обработана верно, а может превращена в #ЗНАЧ!
Денис, возможно на листе есть формулы массива, которые при замене преобразоываются в обычные, вследствии чего возвращают #ЗНАЧ!
Есть один ньюанс: если Вы перед использованием функции скопировали что-то в буфер обмена из ячеек, то указанный в InputBox диапазон ячеек не работает, а функция производит замену формул в гораздо более широком диапазоне, возможно и с ошибками как у Вас (сам столкнулся).
Решение:
Я люблю тебя, друг! От такого гемора спас...
Очень классно! Думала, не разберусь, разобралась, была такая счастливая, что все получилось)! Спасибо! Это мой первый макрос!!!
Благодарю, полезная вещь =)