Если Вы уже записывали макросы обработки таблиц, то наверняка сталкивались с ситуацией, когда макросом в таблицу добавляется столбец с формулами, которые потом необходимо распространить на все строки. Но если количество строк в таблице изменяется, то макрос работает некорректно: если строк стало больше, то формулы проставляются не на все строки, а если строк стало меньше – то появляются строки с лишними формулами.
Если Вы еще не знаете что такое макрос и как его записывать и воспроизводить, то рекомендуется сначала ознакомиться со статьей: Что такое макрос и где его искать?
К примеру, возьмем таблицу такого вида:
Исходная таблица
В конце таблицы нам необходимо добавить столбец «Стоимость», прописав в нем нехитрую формулу перемножения количества на цену:
=F2*G2
Перед записью макроса выделяем ячейку H1. При обычной записи макроса наши шаги такие:
1. Выделили I1
2. Записали в неё заголовок «Стоимость»
3. Перешли в I2
4. Записали формулу: =F2*G2
5. Распространили формулу до конца таблицы (через автозаполнение или путем копирования ячейки с формулой и вставки в остальные ячейки)
Макрос работает отлично. Пока количество строк не изменится. Если при записи макроса в таблице было 319 строк, а потом добавилось еще 20, то записанный макрос создаст формулу только в первых 319 строках. Все дело в том, что при обычной записи макрос использует абсолютную адресацию ячеек. Т.е. в нем каждый наш шаг обозначает выделение ячеек с конкретно указанным адресом (I1, I2, I319 и т.д.):
Пример абсолютных ссылок
Как выйти из такой ситуации? Все не слишком сложно. В группе кнопок код на вкладке Разработчик есть кнопка «Относительные ссылки». Если нажать её до записи макроса(или во время), то ссылки на ячейки будут уже запоминаться не как конкретный адрес, а как смещение относительно последней выделенной ячейки.
Например, запишем два простых макроса, которые будут делать одно и то же действие – перемещение вниз таблицы и выделение ячеек от нижней до верхней. Только первый макрос будет записан обычным способом, а перед записью второго мы нажмем кнопку «Относительные ссылки». Наши действия будут следующими (одинаковыми для обоих макросов):
1. До записи макроса выделяем ячейку I2
2. Начинаем запись макроса
3. Выделяем ячейку H2
4. Комбинацией клавиш Ctrl+(стрелка вниз) перемещаемся вниз таблицы
5. Стрелка вправо (т.е. выделяем последнюю ячейку в столбце I)
6. Комбинацией клавиш Ctrl+Shift+(стрелка вверх) выделяем столбец I от последней ячейки до первой
7. Завершаем запись макроса
Теперь можно посмотреть на код обоих макросов:
Сравнение кодов
Отличия очевидны: в первом используется обращение к ячейкам по их конкретным адресам. Во втором же все действия происходят относительно последней выделенной ячейки(на Range("A1") не обращаем внимания – это из другой оперы и если их удалить ничего не изменится). Из этого можно сделать вывод, что для создания гибких универсальных макросов с использованием относительных ссылок необходимо как можно меньше использовать мышку и максимально стараться применять горячие клавиши. Попробую пояснить почему: когда мы применяем то же автозаполнение (наведение курсора мыши на нижний правый угол ячейки и протягивание вниз или двойной щелчок левой кнопкой мыши) – оно применяется к конкретно определенному количеству ячеек. Т.е. даже относительные ссылки не помогут заполнить его формулами, как того требует наша изначальная задача. Но если использовать горячие клавиши перемещения и выделения (Ctrl+стрелка и Ctrl+Shift+стрелка), то мы можем создать макрос, которому уже будет не важно сколько строк в нашей таблице. Чтобы в этом убедиться, запишем макрос из начала статьи, но уже с использованием относительных ссылок и исключительно клавиш для перемещения. Наши действия:
1. Перед записью макроса выделяем ячейку H1
2. Начали запись макроса
3. Нажимаем кнопку Относительные ссылки(если она еще не нажата)
4. Выделяем I1
5. Записываем в неё заголовок «Стоимость»
6. Переходим в I2
7. Записываем в I2 формулу: =F2*G2
8. Комбинацией клавиш Ctrl+C(или при помощи контекстного меню мыши) копируем ячейку с формулой
9. Стрелкой вправо перемещаемся в ячейку H2
10. Комбинацией клавиш Ctrl+(стрелка вниз) перемещаемся вниз таблицы
11. Стрелка вправо (т.е. выделяем последнюю ячейку в столбце I)
12. Комбинацией клавиш Ctrl+Shift+(стрелка вверх) выделяем столбец I от последней ячейки до первой
13. Комбинацией клавиш Ctrl+V вставляем скопированную формулу
14. Нажимаем Esc для сброса буфера обмена
15. Запись макроса можно завершить
Если теперь попробовать применить такой макрос к таблице, у которой строк больше или меньше, чем было при записи макроса – все пройдет идеально. Макрос создаст столбец и запишет в нем формулу только на нужное количество строк.
Более того. Если наша таблица находится уже в другом листе и даже начинается не с первой ячейки, а где-то в середине:
Хаотично расположенная таблица
Нам достаточно будет выделить ячейку заголовка последнего столбца(K5) и запустить наш макрос. Он без проблем добавит столбец с формулой в нужном месте и на все строки. Макрос же без использования относительных ссылок в такой ситуации спасует по полной: он создаст формулы начиная с ячейки I2 и до заголовка, только испортив таблицу и не сделав ничего полезного.
Так же хочу дополнить, что Относительные ссылки играют роль исключительно во время записи макроса. Во время воспроизведения совершенно не важно включены они или нет. Плюс можно(а иногда и нужно) комбинировать во время записи макросов режим относительных ссылок с обычным режимом. Например, когда столбцов в таблице у нас всегда одинаковое количество и таблица всегда в одном месте, и столбец мы добавляем всегда в столбец I. Но формулы при этом надо протягивать на разное количество строк. Тогда можно начать запись макроса обычным режимом, а после того, как записали название столбца - включить режим относительных ссылок, чтобы определение последней ячейки таблицы не зависело от количества строк в этой таблице.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.