Основные сведения по выполнению работы

Microsoft Excel. Работа № 3 - Работа с макросами.

1. Цель занятия:

Изучить основные возможности MS Excel по работе с макросами. Научиться использовать макросы для обработки данных в электронных таблицах.

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

Обработку данных в электронных таблицах Excel можно автоматизировать с помощью макросов.

Макросы

Макрос – это последовательность команд (программный код), которые написаны на языке VBA и хранятся в стандартном модуле среды разработки. При создании макроса пользователь только описывает эту последовательность, а соответствующий программный код на языке VBA генерируется самой средой программирования с помощью макрорекодера, который преобразует действия пользователя в макрокоманды. Таким образом, использование макросов позволяет автоматизировать обработку данных в электронных таблицах, не прибегая к написанию соответствующих программ самим пользователем.

Условное форматирование

Условное форматирование – это способ визуального выделения данных, отвечающих определенным требованиям (критериям).

Задание.

В данной работе необходимо реализовать возможность обновления информации о динамике цен на ценные бумаги российских компаний, работающих в разных секторах экономики, и сравнение ее с динамикой индекса MMBБ (отражающего поведение рынка ценных бумаг в целом) на различных интервалах времени (1, 2 и 3 месяца). Для автоматизации процесса обновления информации необходимо создать соответствующий макрос.

3.1. Скопируйте с сетевого диска к себе на диск файл Excel_lab_3_данные, содержащий данные по котировкам ценных бумаг российских компаний.

3.2. Удалите (например, с помощью Формы данных) с листа «Цены» вскопированном файле информацию по котировкам ценных бумаг, не входящим в Ваш вариант задания.

Возможный вид результирующей таблицы на листе «Цены» в файле Excel_lab_3_данные представлен на рис.3.1.

Рис.3.1. Вариант окончательного вида листа «Цены» в файле Excel_lab_3_данные.

3.3. Создайте в файле Excel_lab_3_данные еще один лист с именем «Данные за месяц».

3.4. Скопируйте на лист «Данные за месяц» первые два столбца таблицы с листа «Цены» (см. рис.3.2).

Рис.3.2. Возможный вид таблицы на листе «Данные за месяц» в файле Excel_lab_3_данные.

3.5. Создайте на диске новый Excel -файл с именем Excel_lab_3.

3.6. Во вновь созданном файле создайте 6 листов с названиями: «Содержание», «Данные», «Нефтегаз», «Банки», «Металлургия» и «Энергетика».

3.7. На лист «Содержание» скопируйте свой вариант задания, а также включите в него свои персональные данные (фамилия, имя, номер группы).

3.8. На лист «Данные» скопируйтетаблицус листа «Данные за месяц» в файле Excel_lab_3_данные и установите связь между данными, расположенными во втором столбце каждой из таблиц (см. рис.3.3).

Рис.3.3. Возможный вид связанных таблиц в файлах Excel_lab_3_данные и Excel_lab_3.

3.9. Скопируйте (без установления связи) данные по ценам на акции компаний различных отраслей за первые 4 месяца с листа «Цены» файла Excel_lab_3_данные на соответствующие листы в файле Excel_lab_3 (цены по акциям нефтегазовых компаний – на лист «Нефтегаз», цены по акциям банков – на лист «Банки» и т.д.). Добавьте на каждый из листов значения индекса ММВБ на соответствующие даты (см. рис.3.4).

Рис.3.4. Возможный начальный вид листов по отраслям в файле Excel_lab_3.

3.10. Вставьте после первого столбца во всех таблицах добавьте три новых столбца с названиями 1 мес, 2 мес и 3 мес.

3.11. Вставьте в ячейки вновь созданных столбцов формулы по расчету изменения (в %) цен соответствующих акций за 1, 2 и 3 месяца (см. рис.3.5).

Пример. Для расчета изменения цены акций Газпрома за 1 месяц в ячейку B2 необходимо вставить следующую формулу:

= (F$2 - $E$2) / $E$2 * 100,

а затем скопировать («протянуть») ее в ячейки C2 и D2.

Рис.3.5. Возможный промежуточный вид листа «Нефтегаз» в файле Excel_lab_3.

3.12. Используя пункт меню Условное форматирование отформатируйтеячейки с формулами на всех листах таким образом, чтобы отрицательные значения выделялись красным цветом (см. рис.3.6).

Рис. 3.6. Возможный вид отформатированного листа «Металлургия» в файле Excel_lab_3.

3.13. На каждом из листов по отраслям вставьте диаграмму, отображающую динамику изменения цен на соответствующие акции, а также динамику индекса ММВБ за 1, 2 и 3 месяца. Отформатируйте созданные диаграммы (см. рис.3.7).

Рис. 3.7. Возможный окончательный вид листа «Металлургия».

3.14. В файле Excel_lab_3_данные скопируйте с листа «Цены» на лист «Данные за месяц» данные по котировкам всех акций по состоянию на конец следующего месяца (в данном случае на 30.04.2014). При правильно установленной связи между файлами Excel_lab_3_данные и Excel_lab_3 данные на листе «Данные» в файле Excel_lab_3 должны измениться соответствующим образом (см. рис.3.8).

Рис. 3.8. Возможный вид связанных таблиц в файлах Excel_lab_3_данные и Excel_lab_3.

3.15. Запишите макрос с именем Обновление, который будет обновлять данные в файле Excel_lab_3 на всех листах по отраслям путем добавления в соответствующие таблицы обновленных данных с листа «Данные». Для записи макроса сделайте следующие действия:

включите режим записи макроса;
с помощью операции копирования переместите три последних столбца в таблицах на листах «Нефтегаз», «Банки», «Металлургия» и «Энергетика» на один столбец влево (выделите столбцы F, G, H, скопируйте выделенный фрагмент и вставьте его в ячейку E1);

скопируйте в ячейку H1 на всех четырех листах новую дату с листа «Данные» (в данном случае 30.04.2013);
скопируйте в ячейку H5 на всех четырех листах новое значение индекса ММВБ с листа «Данные» (в данном случае − значение индекса на 30.04.2013);
скопируйте в ячейки H2:H4 на листе «Нефтегаз» новые значения цен на акции нефтегазового сектора с листа «Данные» (в данном случае – цены на акции Газпром, Сургут и Роснефть по состоянию на 30.04.2014);

Рис.3.9. Обновленная таблица на листе «Нефтегаз».

скопируйте в ячейки H2:H4 на листе «Банки» новые значения цен на акции банковского сектора с листа «Данные» (в данном случае – цены на акции Сбербанк об, Сбербанк прив и ВТБ по состоянию на 30.04.2014);

Рис.3.10. Обновленная таблица на листе «Банки».

скопируйте в ячейки H2:H4 на листе «Металлургия» новые значения цен на акции металлургического сектора с листа «Данные» (в данном случае – цены на акции ГМКНорНикель, НЛМК и Северсталь по состоянию на 30.04.2014);

Рис.3.11. Обновленная таблица на листе «Металлургия».

скопируйте в ячейки H2:H4 на листе «Энергетика» новые значения цен на акции энергетического сектора с листа «Данные» (в данном случае – цены на акции Русгидро, Мосэнерго и Иркутскэнерго по состоянию на 30.04.2014);

Рис.3.12. Обновленная таблица на листе «Энергетика».

остановите запись макроса.

3.16. Создайте на листе «Данные» в файле Excel_lab_3 элемент управления «Кнопка», дайте ему имя Обновление и свяжите его с созданным макросом.

Рис.3.13. Окончательный вид листа «Данные» в файле Excel_lab_3.

В результате нажатия на кнопку «Обновление» должен запускаться одноименный макрос, который будет обновлять содержимое всех таблиц на листах «Нефтегаз», «Банки», «Металлургия» и «Энергетика» данными за новый месяц с листа «Данные».

После этого в результате пересчета по формулам в ячейках B2:D5 должны измениться данные по динамике цен на соответствующие акции за 1, 2 и 3 месяца.

Затем измененные данные по динамике цен на акции должны отразиться на соответствующих диаграммах.

3.17. Протестируйте работу макроса. Для этого в файле Excel_lab_3_данные скопируйте с листа «Цены» на лист «Данные за месяц» данные по котировкам акций по состоянию на конец следующего месяца (в данном случае на 30.05.2014), а затем нажмите на кнопку «Обновление» на листе «Данные» в файле Excel_lab_3. Результат работы макроса для листа «Металлургия» представлен на рис.3.14.