Многие знают, как изменить стиль ссылок в формуле с абсолютной на относительную и наоборот (за это отвечают знаки доллара внутри ссылки): выделяем ссылку внутри формулы и жмем 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. Для таких формул, если их преобразование завершилось ошибкой, изменения не будут применены и формула останется такой же как была
а вот на этой формуле не работает
=ЕСЛИОШИБКА(ЕСЛИ(ОСТАТ(ЕСЛИ(ЗНАЧЕН(ПСТР(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;"";"НКЗ");"")
Вполне возможно. VBA очень неохотно работает со сложными и длинными формулами.
Я думаю стоит сделать замечание к вашей статье, что это не работает для формул в именованных таблицах. У меня так всё слетело, всё прокопировалось значениями из первой строки. Сейчас 2 часа уйдет на перебивку всего заново... А так, спасибо, полезно
Владимир, спасибо. Попробую учесть этот момент в будущем.
Дмитрий, спасибо! Мне очень помогло!!!