Забыли пароль?


Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (64)
Разное (41)
Баги и глюки Excel (4)

Как поменять в формулах относительные ссылки на абсолютные и наоборот?

Многие знают, как изменить стиль ссылок в формуле с абсолютной на относительную и наоборот (за это отвечают знаки доллара внутри ссылки): выделяем ссылку внутри формулы и жмем F4. Ссылка последовательно меняется (на примере полностью относительной - C3:C8):

  • полностью абсолютная $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+F9, выбираем макрос Change_Style_In_Formulas и жмем Выполнить

Так же можно создать кнопку на листе для вызова макроса или поместить код в надстройку - Как создать свою надстройку?

Сразу после запуска сначала будет предложено выбрать тип ссылок:

  1. Все абсолютные;
  2. Абсолютная строка/Относительный столбец;
  3. Относительная строка/Абсолютный столбец;
  4. Все относительные

необходимо указать целое число от 1 до 4, в зависимости от того, в какой тип необходимо преобразовать все ссылки.
Далее будет предложено выбрать диапазон ячеек с формулами.
Все, после этого код все сделает и выдаст сообщение "Конвертация стилей ссылок завершена".

Примечание: данный код не учитывает формулы массива и после работы кода такие формулы станут обычными.
Так же код может некорректно работать с длинными и сложными формулами, и формулами внутри умных таблиц - особенность VBA. Для таких формул, если их преобразование завершилось ошибкой, изменения не будут применены и формула останется такой же как была


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Обсуждение: 35 комментариев
  1. Тимур:

    а вот на этой формуле не работает
    =ЕСЛИОШИБКА(ЕСЛИ(ОСТАТ(ЕСЛИ(ЗНАЧЕН(ПСТР(B4;1;1))<5;ЗНАЧЕН(ПСТР(B4;1;1))*2;(ЗНАЧЕН(ПСТР(B4;1;1))-5)*2+1)+ЗНАЧЕН(ПСТР(B4;2;1))+ЕСЛИ(ЗНАЧЕН(ПСТР(B4;3;1))<5;ЗНАЧЕН(ПСТР(B4;3;1))*2;(ЗНАЧЕН(ПСТР(B4;3;1))-5)*2+1)+ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;5));1;1))+ЕСЛИ(ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;4));1;1))<5;ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;4));1;1))*2;(ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;4));1;1))-5)*2+1)+ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;3));1;1))+ЕСЛИ(ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;2));1;1))<5;ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;2));1;1))*2;(ЗНАЧЕН(ПСТР((ПРАВСИМВ(B4;2));1;1))-5)*2+1)+ЗНАЧЕН(ПРАВСИМВ(B4;1));10)=0;"";"НКЗ");"")

  2. Владимир:

    Я думаю стоит сделать замечание к вашей статье, что это не работает для формул в именованных таблицах. У меня так всё слетело, всё прокопировалось значениями из первой строки. Сейчас 2 часа уйдет на перебивку всего заново... А так, спасибо, полезно

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти