Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

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

  • полностью абсолютная $C$3:$C$8 =>
  • абсолютные строки и относительные столбцы C$3:C$8 =>
  • абсолютные столбцы и относительные строки $C3:$C8 =>
  • полностью относительная C3:C8

А теперь представим ситуацию: когда-то были созданы кучи формул в разных местах листа и вдруг понадобилось сменить ссылки на ячейки в формулах с относительных на абсолютные или наоборот, или закрепить только столбцы, а строки оставить "плавающими" или т.п.. Вручную это делать довольно долго и совсем не интересно. Вот в таких случаях и понадобится код, который поможет сделать данный процесс более приятным и более быстрым. Я бы сказал - два клика мышью и часы работы в пятницу сэкономлены :-)

Как работает:

  • копируем приведенный выше код, переходим в редактор VBA (Alt+F11)
  • создаем стандартный модуль (Insert -Module)
  • переходим на лист, формулы в котором надо преобразовать, жмем Alt+F9, выбираем макрос Change_Style_In_Formulas и жмем Выполнить

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

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

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

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

Примечание: данный код не учитывает формулы массива и после работы кода такие формулы станут обычными.


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

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

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 30 коммент.
  1. Дмитрий:

    @Денис? не подскажете, как соотносится Ваш код с вышеприведенным? Т.е. куда его вставлять?

  2. sfantom20:

    а если ячейки имеют нестандартную нумерацию? программа тогда не работает...

    • что значит нестандартная нумерация? Может стиль ссылок имеется ввиду? Поменяйте тогда

      на

  3. sfantom20:

    например имя ячейки z1000_001_06

    • Имя ячейки и нумерация - разные вещи. К именам данный код отношения не имеет - на то они и имена, чтобы их можно было изменять только по необходимости и только из Диспетчера имен.

  4. sfantom20:

    но в том то и дело что не работает... если проблема не в имени а в чем тогда? в моем файле не хочет а если с нуля создать файл то работает!

    • Вчитайтесь в мое сообщение предыдущее: К ИМЕНАМ КОД ОТНОШЕНИЯ НЕ ИМЕЕТ! Их изменять можно только из Диспетчера имен.
      Что такое у Вас "z1000_001_06" - я не в курсе. Имя это или еще что. В новом файле работает и изменяет такое имя? Или изменяет обычные адреса вида "B1"? Уверен, что второе.

  5. Вот как Вам еще объяснить, если читать не хотите? z1000_004_05 - ЭТО ИМЯ. Это не адрес ячейки и быть им не может. А значения имен данным макросом изменить НЕЛЬЗЯ. Значения имен изменяются через диспетчер имен.
    Советую почитать: Именованные диапазоны

    • sfantom20:

      так хорошо! а в чем тогда может причина? если просто взять и создать 3 листа и из двух сумму копировать в 3й то все работает а если взять мои листы и даже создать с нуля файл тот куда суммируем то не работает! там других то макросов никаких нет!!!! Где можно поискать ошибку?

      • Не обижайтесь, конечно, но у меня уже создалось впечатление, что русский язык для Вас не родной. Т.к. я уже не раз написал - ИМЕНА НЕЛЬЗЯ ТАК ИЗМЕНИТЬ. Даже ссылку дал, чтобы Вы поняли, что это такое. И где их можно изменить. Для изменения именно имен надо писать ОТДЕЛЬНЫЙ КОД, который будет менять ссылки в именах. Но это может сказаться на работе формул(т.к. на именах может быть много формул завязано).

        Либо Вы никак не можете пояснить, что у Вас происходит. Есть имена - они не будут этим макросом изменяться(потому что в него изначально это на заложено). Если имен нет - тогда неясно, что за адресацию Вы тут демонстрируете. В Excel такой нет.

        • sfantom20:

          все освоил)несмотря что в формулах стоят другие имена... я поставил вместо них родные имена ячеек и макрос сработал!!!!

  6. Евгения:

    Большое спасибо! Выручили :)

  7. Спасибо! Лет пять из своих 26 мучалась с вопросом, как сделать массив ячеек абсолютным без огромных трудозатрат! Вы решили эту проблему навсегда)

  8. Владимир:

    Спасибо !!!! Работает просто супер.

  9. Александр:

    Спасибо огромное!!!!! Вы спасли мое время.

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти