Как сделать относительную ссылку в excel для макроса?

Тема 9. Автоматизация работы в Excel.

Теоретические сведения

Запись и выполнение макросов

Для автоматизации трудоемких или часто повторяющихся задач в Excel используются макросы. Макрос — это последовательность команд и действий, сохраненная под одним именем. Макрос можно создать двумя способами: 1) записать действия автоматически; 2) разработать процедуру в редакторе VBA.

Можно записывать макрос с абсолютными ссылками и с относительными ссылками. Макрос с относительными ссылками выполняется, начиная с ячейки, которая была текущей перед началом выполнения макроса. Макрос с абсолютными ссылками выполняется в одних и тех же ячейках, которые использовались во время записи макроса.

Для работы с макросами и элементами управления используется закладка Разработчик (рис. 9.1). Если такой закладки на ленте инструментов нет, ее нужно включить следующим образом:

— нажать кнопку Office , щелкнуть по кнопке ;

— открыть категорию Основные;

— установить флажок Показывать вкладку “Разработчик” на ленте.

Записьмакроса начинается после нажатия кнопки Запись макроса.Способ записи определяется состоянием кнопки Относительные ссылки. Если кнопку включить, то будет записываться макрос с относительными ссылками, если выключить — с абсолютными.

Рис. 9.1. Закладка Разработчик ленты инструментов

Выполнение макроса можно задать комбинации клавиш, кнопке на панели инструментов, элементу управления формы или графическому объекту на рабочем листе.

Удалениемакроса производится в диалоге Макросы, который открывается кнопкой Макросы на закладке Разработчик.Нужно выбрать в списке требуемый макрос и нажать кнопку Удалить. В этом же диалоге можно запустить выбранный макрос на выполнение, если не задан другой способ выполнения макроса.

Для автоматизации заполнения шаблонов в Excel используются элементы управления формы и элементы ActiveX. Элемент управления — это графический объект, позволяющий пользователю управлять приложением. Чтобы создать элемент управления, нужно раскрыть кнопку Вставить(рис. 9.1), выбрать требуемый элемент и растянуть мышью до желаемого размера в нужном месте рабочего листа. Когда элемент управления выделен, с помощью кнопки Свойства на закладке Разработчик можно изменять его параметры (рис. 9.6), например, связать с какой-либо ячейкой листа.

Рис. 9.2. Элементы управления формы

1. Группа — рамка, которая используется для объединения переключателей.

2. Кнопка — используется для выполнения назначенного ей макроса.

3. Флажок — если установлен, то в связанной с ним ячейке выводится значение ИСТИНА, если снят — ЛОЖЬ.

4. Переключатель — используется всегда в группе. Когда переключатели объединены в группу, только один из них может установлен. Тогда в ячейке, связанной с этой группой переключателей, выводится порядковый номер выбранного переключателя.

5. Список (а) и Поле со списком (б) — отображают список значений, который нужно предварительно ввести в ячейки, а затем указать диапазон этих ячеек в параметрах элемента управления. Номер выбранного в списке значения помещается в ячейку, связанную со списком.

6. Полоса прокрутки (а) и Счетчик (б) — изменяют значение связанной с ними ячейки. В параметрах элемента управления можно задать диапазон и шаг изменения этого значения.

Пример 9.1.Записать макрос под именем «Первый_день_месяца», который начиная с текущей ячейки выводит даты первых дней шести месяцев, следующих за текущим, и форматирует их так, чтобы название месяца выводилось словом, выравнивание по левому краю, цвет текста — синий, шрифт — Courier New полужирный.Выполнение макроса назначить автофигуре.

1. Нажать кнопку Запись макросана закладке Разработчик.

2. В открывшемся диалоге задать имя макроса — Первый_день_месяца, можно задать комбинацию клавиш и затем нажать ОК. При этом включится запись и кнопка Запись макросабудет преобразована в кнопку Остановить запись (рис. 9.3).

3. Включить кнопку Относительные ссылки (рис. 9.3).

Рис. 9.3. Вид фрагмента закладки Разработчик во время записи макроса

4. Проделать действия, которые должен выполнять макрос:

— в текущую ячейку ввести формулу, которая будет возвращать дату первого числа месяца, следующего за текущим (текущей является дата 15.05.06): =ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ())+1;1)

— выделить 6 ячеек, начиная с введенной формулы, раскрыть кнопку Заполнить на закладке Главная и выбрать команду Прогрессия…;

— в открывшемся диалоге указать Тип à Даты, Единицы à Месяц.

— открыть диалог Формат ячеек…;

— на закладке Число задать формат ДД ММММ ГГГГ, на закладке Выравнивание à по горизонтали по левому краю, на закладке Шрифт à шрифт — Courier New полужирный, цвет — синий.

5. Нажать кнопку Остановить запись (рис. 9.3).

6. На рабочем листе нарисовать какую-либо автофигуру, выбрав ее на закладке Вставка. В контекстном меню автофигуры выбрать команду Назначить макрос. В открывшемся диалоге указать макрос «Первый­_день_месяца» и нажать ОК.

Рис. 9.4. Результат выполнения макроса «Первый_день_месяца»

7. Сделать текущей любую ячейку и проверить работу макроса щелкнув по автофигуре (рис. 9.4).

Пример 9.2. Создать бланк заказ-наряда (рис. 9.5), в котором наименование работы выводится в ячейке В2 с помощью списка, а количество часов в ячейке В3 заполняется с помощью полосы прокрутки.

Читать еще:  Как сделать список с плюсиком в excel?

Рис. 9.5. Создание бланка с элементами управления формы.

  1. Заполнить ячейки данными как на рис. 9.5, кроме ячеек В2, В3, Е1.
  2. Нарисовать список (рис. 9.2, 5-а) и полосу прокрутки (рис. 9.2, 6-а).
  3. Изменить параметры созданных элементов управления (рис. 9.6). Так как полоса прокрутки связана с ячейкой В3, то щелчок мышью по стрелке полосы прокрутки будет менять значение ячейки на один шаг — 1. Список связан с ячейкой Е1, поэтому при выборе значения «Побелка потолка» в ячейке будет выведен порядковый номер этого значения в списке — 3.

Рис. 9.6. Параметры полосы прокрутки (слева) и списка (справа).

  1. Чтобы вывести в В2 наименование работы, а не ее номер, нужно использовать функцию ВПР, которая в зависимости от номера выбранной из списка работы в ячейке Е1, будет возвращать соответствующее значение из второго столбца таблицы Е2:F5. Таким образом, формула в В2 будет следующей: =ВПР(E1;E2:F5;2).

Замечание. Полосу прокрутки и список можно создать с помощью элементов ActiveX и затем изменить их свойства, как показано на рисунке:

Практические задания

Лабораторная работа № 9

Цель работы:научиться создавать и использовать макросы и элементы управления формы на рабочем листе для автоматизации работы в Excel.

Задания:

I. Записать макросы:

  1. С относительными ссылками. Макрос должен выводить названия месяцев в столбце, начиная с текущей ячейки, со следующими элементами форматирования:

— цвет символов — красный,

— обрамление ячеек — тонкая линия,

— текст выровнен по центру,

— внешнее обрамление столбца — жирная линия.

На панели Элементы управления формы выбрать элемент Кнопка(см. рисунок 9.1), нарисовать его на рабочем листе, назвать МЕСЯЦЫ и назначить созданный макрос.

  1. С абсолютными ссылками. Макрос должен очищать весь рабочий лист. На рабочем листе нарисовать любую автофигуру и назначить созданный макрос.

II. Создать шаблон для заполнения бланка заказа авиабилетов с элементами управления формы:

1. Заполнить ячейки данными, кроме ячеек С2:С4, С6 и С13, и вставить элементы управления формы:

2. Изменить свойства элементов управления:

2.1. Для поля со списком вывод на печать отключен, объемное затенение включено, список формируется по диапазону I3:I7, результат помещается в ячейку G3, т. е. в эту ячейку помещается номер элемента, который был выбран в списке.

2.2. Для счетчика вывод на печать отключен, объемное затенение включено, диапазон изменения от 1 до 10 с шагом 1, результат помещается в ячейку С6.

2.3. Для переключателей вывод на печать и объемное затенение включены, результат помещается в ячейку G4. Переключатели объединены в рамку.

2.4. Для кнопки вывод на печать отключен.

3. В ячейках С2:С4 написать формулы (используя функцию ВПР), которые выводят город, время вылета и цену билета в зависимости от выбранного значения списка.

4. В ячейке С13 написать формулу для расчета суммы к оплате в зависимости от количества билетов и типа билета (для льготного — скидка 30%).

5. Снять защиту с ячеек, с которыми связаны элементы управления.

6. Формулы скрыть.

7. Скрыть столбцы, содержащие вспомогательные данные.

8. Создать и назначить кнопке «Печать» макрос, который задает альбомную раскладку и размер бумаги А5 и отображает шаблон в режиме предварительного просмотра.

9. Защитить лист и сохранить файл как шаблон.

Контрольные вопросы к теме

1. Какими способами можно автоматизировать заполнение шаблона?

2. Зачем используются относительные ссылки при записи макроса?

3. Назовите элементы управления и их назначение.

4. Как создать элемент управления на рабочем листе и изменить его параметры?

Как сделать макрос в Excel

Простую последовательность действий, которую нужно повторить несколько раз, можно записать в виде программного кода и сохранить как макрос. Если последовательность действий записана в макрос, то выполнять её можно снова и снова, просто запуская этот макрос. Это гораздо эффективнее, чем выполнять раз за разом одни и те же действия вручную.

Чтобы записать макрос, нужно включить режим записи. Это можно сделать на вкладке Вид (View) в разделе Макросы (Macros) или в меню Сервис (Tools), если у Вас Excel 2003. Ниже на картинках показано, как выглядят эти меню.

  • Запись макроса в современных версиях Excel (2007 и новее):

Далее откроется диалоговое окно Запись макроса (Record Macro), как показано на картинке ниже:

Здесь, по желанию, можно ввести имя и описание для макроса. Рекомендуется давать макросу такое имя, чтобы, вернувшись к нему спустя некоторое время, можно было без труда понять, для чего этот макрос нужен. Так или иначе, если не ввести для макроса имя, то Excel автоматически назовёт его Макрос1, Макрос2 и так далее.

Здесь же можно назначить сочетание клавиш для запуска записанного макроса. Запускать макрос таким способом будет значительно проще. Однако будьте осторожны! Если случайно назначить для макроса одно из предустановленных клавиатурных сочетаний Excel (например, Ctrl+C), то в дальнейшем макрос может быть запущен случайно.

Когда макросу дано подходящее имя и (при желании) задано сочетание клавиш, нажмите ОК, чтобы запустить запись макроса. С этого момента каждое действие (ввод данных, выделение ячеек, изменение формата ячеек, пролистывание листа и так далее) будет записано в макрос и сохранено в виде кода VBA.

Читать еще:  Как сделать средний балл в excel?

При включении режима записи макроса в строке состояния (внизу слева) появляется кнопка Стоп. В Excel 2003 эта кнопка находится на плавающей панели инструментов.

    Кнопка Стоп в строке состояния в Excel 2007 и более новых версиях:

Нажмите Стоп, когда выполните все действия, которые должны быть записаны в макрос. Теперь код записанного макроса сохранён в модуле редактора Visual Basic.

Параметр «Относительные ссылки»

Если перед началом записи макроса включить параметр Относительные ссылки (Use Relative References), то все ссылки в записываемом макросе будут создаваться как относительные. Если же параметр выключен, то при записи макроса будут создаваться абсолютные ссылки (подробнее об этих двух типах ссылок можно узнать в статьях, посвящённых теме ссылок на ячейки в Excel).

Параметр Относительные ссылки (Use Relative References) находится в разделе Макросы (Macros) на вкладке Вид (View). В Excel 2003 этот параметр расположен на плавающей панели инструментов.

    Параметр Относительные ссылки (Use Relative References) в современных версиях Excel:

Просмотр кода VBA

Код VBA, записанный в макрос, размещается в модуле, который можно просмотреть в редакторе Visual Basic. Редактор можно запустить нажатием Alt+F11 (одновременное нажатие клавиш Alt и F11).

Код находится в одном из модулей, которые расположены в окне Project слева от области редактирования. Если дважды кликнуть по Module1 в окне Project, то справа появится код записанного макроса.

Запуск записанного макроса в Excel

Записывая макрос, Excel всегда создаёт процедуру Sub (не Function). Если при создании макроса к нему было прикреплено сочетание клавиш, то именно с его помощью запустить макрос будет проще всего. Существует и другой способ запустить макрос:

  • Нажмите Alt+F8 (одновременно нажмите клавиши Alt и F8);
  • В появившемся списке макросов выберите тот, который нужно запустить;
  • Нажмите кнопку Выполнить (Run).

Ограничения

Инструмент Excel для записи макросов — это очень простой способ создавать код VBA, но подходит он только для создания самых простых макросов. Дело в том, что этот инструмент не умеет использовать многие возможности VBA, такие как:

  • Константы, переменные и массивы;
  • Выражения IF;
  • Циклы;
  • Обращения к встроенным функциям или внешним процедурам.

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

Автоматическое создание кода VBA в Excel отлично работает для простых макросов, но если нужно построить более сложный макрос, то придётся научиться писать код VBA самостоятельно. Тем не менее, запись макроса в Excel — это отличны инструмент, при помощи которого можно создавать первоначальный код, а в дальнейшем исправлять или вставлять его в более сложные макросы.

Урок подготовлен для Вас командой сайта office-guru.ru Источник: http://www.excelfunctions.net/Record-A-Macro.html Перевел: Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel

Абсолютная ссылка в Excel фиксирует ячейку в формуле

Преимущества абсолютных ссылок сложно недооценить. Их часто приходится использовать в процессе работы с программой. Относительные ссылки на ячейки в Excel более популярные чем, абсолютные, но так же имеют свои плюсы и минусы.

В Excel существует несколько типов ссылок: абсолютные, относительные и смешанные. Сюда так же относятся «имена» на целые диапазоны ячеек. Рассмотрим их возможности и отличия при практическом применении в формулах.

Абсолютные и относительные ссылки в Excel

Абсолютные ссылки позволяют нам зафиксировать строку или столбец (или строку и столбец одновременно), на которые должна ссылаться формула. Относительные ссылки в Excel изменяются автоматически при копировании формулы вдоль диапазона ячеек, как по вертикали, так и по горизонтали. Простой пример относительных адресов ячеек:

  1. Заполните диапазон ячеек A2:A5 разными показателями радиусов.
  2. В ячейку B2 введите формулу вычисления объема сферы, которая будет ссылаться на значение A2. Формула будет выглядеть следующим образом: =(4/3)*3,14*A2^3
  3. Скопируйте формулу из B2 вдоль колонки A2:A5.

Как видите, относительные адреса помогают автоматически изменять адрес в каждой формуле.

Так же стоит отметить закономерность изменения ссылок в формулах. Данные в B3 ссылаются на A3, B4 на A4 и т.д. Все зависит од того куда будет ссылаться первая введенная формула, а ее копии будут изменять ссылки относительно своего положения в диапазоне ячеек на листе.

Использование абсолютных и относительных ссылок в Excel

Заполните табличку, так как показано на рисунке:

Описание исходной таблицы . В ячейке A2 находиться актуальный курс евро по отношению к доллару на сегодня. В диапазоне ячеек B2:B4 находятся суммы в долларах. В диапазоне C2:C4 будут находится суммы в евро после конвертации валют. Завтра курс измениться и задача таблички автоматически пересчитать диапазон C2:C4 в зависимости от изменения значения в ячейке A2 (то есть курса евро).

Для решения данной задачи нам нужно ввести формулу в C2: =B2/A2 и скопировать ее во все ячейки диапазона C2:C4. Но здесь возникает проблема. Из предыдущего примера мы знаем, что при копировании относительные ссылки автоматически меняют адреса относительно своего положения. Поэтому возникнет ошибка:

Читать еще:  Как в excel сделать выборку из таблицы по условию?

Относительно первого аргумента нас это вполне устраивает. Ведь формула автоматически ссылается на новое значение в столбце ячеек таблицы (суммы в долларах). А вот второй показатель нам нужно зафиксировать на адресе A2. Соответственно нужно менять в формуле относительную ссылку на абсолютную.

Как сделать абсолютную ссылку в Excel? Очень просто нужно поставить символ $ (доллар) перед номером строки или колонки. Или перед тем и тем. Ниже рассмотрим все 3 варианта и определим их отличия.

Наша новая формула должна содержать сразу 2 типа ссылок: абсолютные и относительные.

  1. В C2 введите уже другую формулу: =B2/A$2. Чтобы изменить ссылки в Excel сделайте двойной щелчок левой кнопкой мышки по ячейке или нажмите клавишу F2 на клавиатуре.
  2. Скопируйте ее в остальные ячейки диапазона C3:C4.

Описание новой формулы . Символ доллара ($) в адресе ссылок фиксирует адрес в новых скопированных формулах.

Абсолютные, относительные и смешанные ссылки в Excel:

  1. $A$2 – адрес абсолютной ссылки с фиксацией по колонкам и строкам, как по вертикали, так и по горизонтали.
  2. $A2 – смешанная ссылка. При копировании фиксируется колонка, а строка изменяется.
  3. A$2 – смешанная ссылка. При копировании фиксируется строка, а колонка изменяется.

Для сравнения: A2 – это адрес относительный, без фиксации. Во время копирования формул строка (2) и столбец (A) автоматически изменяются на новые адреса относительно расположения скопированной формулы, как по вертикали, так и по горизонтали.

Примечание. В данном примере формула может содержать не только смешанную ссылку, но и абсолютную: =B2/$A$2 результат будет одинаковый. Но в практике часто возникают случаи, когда без смешанных ссылок не обойтись.

Полезный совет. Чтобы не вводить символ доллара ($) вручную, после указания адреса периодически нажимайте клавишу F4 для выбора нужного типа: абсолютный или смешанный. Это быстро и удобно.

Преобразование формул Excel от относительных к абсолютным ссылкам, и наоборот

Ниже приведён код на VBA Excel, который может быть использован для преобразования всех Excel формул из абсолютных к относительным ссылкам или относительных к абсолютным.
Этот код поможет изменять область ячеек как по-горизонтали, так и по-вертикали, можно выделять сразу область, в которой выделены несколько столбцов и колонок. Просто выберите ячейки, которые хотите изменить, запустите код и выберите нужный Вам тип ссылки.

Ниже приведён код двух макросов Excel, первый работает быстро, но может вызвать проблемы с массивом формул. Второй выполняется медленнее, но реже вызывает вопросы. Перед запуском макросов необходимо сохранить книгу Excel.

СОВЕТ: Вы можете иcпользовать четыре типа ссылок. Выбрав ячейку, щелкните в строке формул, а затем нажимайте F4, ссылка будет меняться (например A1, $A1, A$1 и т.д.). Каждое нажатие F4 переключает тип ссылки.

Если Вы не знаете как запускать Excel макросы, то выполните следующие действия:

  1. Откройте редактор Visual Basic, Сервис>Макрос>Редактор Visual Basic (Alt+F11).
  2. Добавьте новый стандартный модуль, Insert>Module.
  3. Скопируйте код и вставьте в модуль, который Вы только что добавили.
  4. Вернитесь в Excel, закрыв редактор Visual Basic можно нажать Alt+Q).
  5. Сохраните книгу Excel. Выделите диапазон ячеек и запустите макрос.
  6. Чтобы запустить макрос, выберите Сервис>Макрос>Макросы (Alt + F8) и выберите имя макроса и нажмите кнопку «Выполнить».

‘ Ask whether Relative or Absolute
Reply = InputBox ( «Change formulas to?» & Chr ( 13 ) & Chr ( 13 ) & «Relative row/Absolute column = 1» &
Chr ( 13 ) & «Absolute row/Relative column = 2» & Chr ( 13 ) &
«Absolute all = 3» & Chr ( 13 ) &
«Relative all = 4» ,
«OzGrid Business Applications» )
‘They cancelled
If Reply = «» Then Exit Sub
On Error Resume Next
‘Set Range variable to formula cells only
Set RdoRange = Selection . SpecialCells ( Type: = xlFormulas )
‘determine the change type
Select Case Reply
Case 1 ‘Relative row / Absolute column
For i = 1 To RdoRange . Areas . Count
RdoRange . Areas ( i ) . Formula = Application . ConvertFormula (_
Formula: = RdoRange . Areas ( i ) . Formula , FromReferenceStyle: = xlA1 ,
ToReferenceStyle: = xlA1 , ToAbsolute: = xlRelRowAbsColumn )
Next i
Case 2 ‘Absolute row / Relative column
For i = 1 To RdoRange . Areas . Count
RdoRange . Areas ( i ) . Formula =
Application . ConvertFormula ( Formula: = RdoRange . Areas ( i ) . Formula ,
FromReferenceStyle: = xlA1 , ToReferenceStyle: = xlA1 , ToAbsolute: = xlAbsRowRelColumn )
Next i
Case 3 ‘Absolute all
For i = 1 To RdoRange . Areas . Count
RdoRange . Areas ( i ) . Formula =
Application . ConvertFormula ( Formula: = RdoRange . Areas ( i ) . Formula ,
FromReferenceStyle: = xlA1 , ToReferenceStyle: = xlA1 , ToAbsolute: = xlAbsolute )
Next i
Case 4 ‘Relative all
For i = 1 To RdoRange . Areas . Count
RdoRange . Areas ( i ) . Formula =
Application . ConvertFormula ( Formula: = RdoRange . Areas ( i ) . Formula ,
FromReferenceStyle: = xlA1 , ToReferenceStyle: = xlA1 , ToAbsolute: = xlRelative )
Next i
Case Else ‘Typo
MsgBox «Change type not recognised!» , vbCritical ,
«OzGrid Business Applications»
End Select
‘Clear memory
Set RdoRange = Nothing
End Sub

И второй макрос:

‘Ask whether Relative or Absolute
Reply = InputBox ( «Change formulas to?» & Chr ( 13 ) & Chr ( 13 ) &
«Relative row/Absolute column = 1» & Chr ( 13 ) & «Absolute row/Relative column = 2» &
Chr ( 13 ) &
«Absolute all = 3» & Chr ( 13 ) & «Relative all = 4» ,
«OzGrid Business Applications» )
‘They cancelled
If Reply = «» Then Exit Sub

On Error Resume Next
‘Set Range variable to formula cells only
Set RdoRange = Selection . SpecialCells ( Type: = xlFormulas )
‘determine the change type
Select Case Reply
Case 1 ‘Relative row/Absolute column
For Each rCell In RdoRange
If rCell . HasArray Then
If Len ( rCell . FormulaArray ) 03.12.2009 / 0 Отзывы / от softmaker

Ссылка на основную публикацию
Adblock
detector