Как поменять в формулах относительные ссылки на абсолютные и наоборот?
Многие знают, как изменить стиль ссылок в формуле с абсолютной на относительную и наоборот (за это отвечают знаки доллара внутри ссылки): выделяем ссылку внутри формулы и жмем 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 ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
@Денис? не подскажете, как соотносится Ваш код с вышеприведенным? Т.е. куда его вставлять?
а если ячейки имеют нестандартную нумерацию? программа тогда не работает...
что значит нестандартная нумерация? Может стиль ссылок имеется ввиду? Поменяйте тогда
на
например имя ячейки z1000_001_06
Имя ячейки и нумерация - разные вещи. К именам данный код отношения не имеет - на то они и имена, чтобы их можно было изменять только по необходимости и только из Диспетчера имен.
т е независимо от имени должен работать?
но в том то и дело что не работает... если проблема не в имени а в чем тогда? в моем файле не хочет а если с нуля создать файл то работает!
Вчитайтесь в мое сообщение предыдущее: К ИМЕНАМ КОД ОТНОШЕНИЯ НЕ ИМЕЕТ! Их изменять можно только из Диспетчера имен.
Что такое у Вас "z1000_001_06" - я не в курсе. Имя это или еще что. В новом файле работает и изменяет такое имя? Или изменяет обычные адреса вида "B1"? Уверен, что второе.
второе? вы про что?
=СУММ('[16vn1 пол-ка.xls]Лист1'!z1000_004_05;'[16vn1 стац.xls]Лист1'!z1000_004_05;'[16vn1 дневн.xls]Лист1'!z1000_004_05) вот пример суммирования по одной ячейке ...
НЕ ИЗМЕНЯЕТ К СОЖАЛЕНИЮ
Вот как Вам еще объяснить, если читать не хотите? z1000_004_05 - ЭТО ИМЯ. Это не адрес ячейки и быть им не может. А значения имен данным макросом изменить НЕЛЬЗЯ. Значения имен изменяются через диспетчер имен.Именованные диапазоны
Советую почитать:
так хорошо! а в чем тогда может причина? если просто взять и создать 3 листа и из двух сумму копировать в 3й то все работает а если взять мои листы и даже создать с нуля файл тот куда суммируем то не работает! там других то макросов никаких нет!!!! Где можно поискать ошибку?
Не обижайтесь, конечно, но у меня уже создалось впечатление, что русский язык для Вас не родной. Т.к. я уже не раз написал - ИМЕНА НЕЛЬЗЯ ТАК ИЗМЕНИТЬ. Даже ссылку дал, чтобы Вы поняли, что это такое. И где их можно изменить. Для изменения именно имен надо писать ОТДЕЛЬНЫЙ КОД, который будет менять ссылки в именах. Но это может сказаться на работе формул(т.к. на именах может быть много формул завязано).
Либо Вы никак не можете пояснить, что у Вас происходит. Есть имена - они не будут этим макросом изменяться(потому что в него изначально это на заложено). Если имен нет - тогда неясно, что за адресацию Вы тут демонстрируете. В Excel такой нет.
все освоил)несмотря что в формулах стоят другие имена... я поставил вместо них родные имена ячеек и макрос сработал!!!!
Большое спасибо! Выручили :)
Спасибо! Лет пять из своих 26 мучалась с вопросом, как сделать массив ячеек абсолютным без огромных трудозатрат! Вы решили эту проблему навсегда)
Спасибо !!!! Работает просто супер.
Спасибо огромное!!!!! Вы спасли мое время.
Александр, всегда пожалуйста! Рад, что статья помогла
Дмитрий, спасибо!!! А то я уже отчаялся искать!)))