Добавленные операторы выделены полужирным шрифтом

Application.ScreenUpdating = True - включить режим обновления экрана

Подведение итогов. Для автоматизации работы с итогами вначале следует зафиксировать уже созданные элементы управления листа Данные, чтобы их размеры не изменялись при выполнении промежуточных итогов. В контекстном меню каждой кнопки следует выбрать команду «Формат объекта» и на закладке «Свойства» установить переключатель «Привязка объекта» в положение «не перемещать и не изменять размеры».

Создание макроса Итоги_Эмитентов.

1. Сервис – Макрос – Начать запись…

2. В окне Запись макроса указать имя макроса Итоги_Эмитентов;

3. Выполнить запись:

· Выделить ячейку В1;

· Нажать кнопку Сортировка

· Выполнить команду меню Данные – Итоги;

· Указать в качестве поля группировки столбец «Эмитент ».

· Выбрать операцию Сумма.

· Установить «флажки» итогов по полям Стоимость предложения и Стоимость спроса, итоги размещать под данными - ОК.

· Скрыть промежуточные данные таблицы, щелкнув по кнопке второго уровня структуры ;

· выделить ячейку В1.

4. Завершить запись макроса, выполнив команду меню Сервис – Макрос – Остановить запись.

Создание макроса Удалить_Итоги.

1. Сервис – Макрос – Начать запись…

2. В окне Запись макроса указать имя макроса Удалить _ Итоги;

3. Выполнить запись:

· Выделить ячейку В1;

· Выполнить команду меню Данные – Итоги;

· В диалоговом окне «Промежуточные итоги» щелкнуть по кнопке «Убрать все»

4. Завершить запись макроса, выполнив команду меню Сервис – Макрос – Остановить запись.

Так как при работе с итогами должен выполняться либо макрос Итоги_Эмитентов, либо Удалить_Итоги, то в качестве элемента управления выберем флажок, который и будет выполнять то один, то другой макросы в зависимости от своего состояния (установлен или снят).

Откроем Visual Basic и запишем «вручную» код макроса для флажка в модуле Module1.

Модуль примет вид:

Sub Итоги()

Application.ScreenUpdating = False

If Range("R3").Value = True Then

Итоги_Эмитентов

Else

Удалить_Итоги

End If

Application.ScreenUpdating = True

End Sub

Для создания флажка следует выбрать соответствующую кнопку панели управления Формы. Нарисовать прямоугольник и изменить название на «Итоги по эмитентам», пользуясь контекстным меню. В окне «Формат элемента управления» выбрать закладку «Элемент управления» и установить переключатель Значение в положение «снят», в поле «Связь с ячейкой» указать, например R3; на закладке «Свойства» установить переключатель «Привязка объекта» в положение «не перемещать и не изменять размеры». Назначить флажку макрос Итоги.

Рисунок 7. Результат автоматизации работы со списком

Самостоятельно создать флажок для промежуточных итогов по видам ценных бумаг. Результат проделанной работы представлен на рисунке 7.

Элементы управления, оперирующие списком, объединим в группу. Для этого следует использовать элемент управления «Группа» .

Выбрав этот элемент, следует нарисовать прямоугольную область, в которую нужно включить элементы группы (см. рисунок 8). Изменить текст элемента, пользуясь контекстным меню.

Рисунок 8. Готовый лист Данные с группой элементов управления

В окне «Формат элемента управления» выбрать закладку «Свойства» установить переключатель «Привязка объекта» в положение «не перемещать и не изменять размеры», снять флажок «выводить объект на печать».

Автоматизация типовых операций на листе Анализ

Сводная таблица – это еще один инструмент обработки больших списков, который позволяет подводить итоги, выполнять сортировку и фильтрацию данных.

Перед построением сводной таблицы на основе списка следует убрать из него промежуточные итоги и наложенные фильтры.

На листе Анализ создадим три макроса получения сводных таблиц для разностороннего анализа спроса и предложения.

Имя макроса	Назначение
Сводная_1	Анализ спроса и предложения по эмитентам и видам ЦБ
Сводная_2	Расчет спроса и эмиссии видов ЦБ по учетным периодам
Сводная_3	Расчет объема спроса и предложения за весь учетный период

Создание макроса Сводная_1 (текущий лист Анализ)

1. Сервис – Макрос – Начать запись…

2. В окне Запись макроса указать имя макроса Сводная_1;

3. Выполнить запись:

· Выделить ячейку В1;

· Выполнить команду Данные – Сводная таблица.

· В качестве исходного диапазона указать лист Данные, выделить исходную таблицу, можно заполнить поле «Диапазон» с клавиатуры (см. рисунок).

Кнопка Далее.

· На третьем шаге построения выбран переключатель «существующий лист» и указана ячейка В1 листа Анализ.

Теперь следует нажать кнопку Макет. Расположить столбцы таблицы на макете следующим образом:

· Строка – Эмитент

· Столбец – Вид ЦБ

· Данные – Стоимость спроса и Стоимость предложения

· Щелкнуть по кнопке ОК и Готово.

· Закрыть окно «Список полей сводной таблицы»

· Выделить любую ячейку сводной таблицы. На панели инструментов Сводная таблица выбрать из раскрывающегося списка кнопки Сводная таблица команду Формулы – Вычисляемое поле.

· Заменить имя нового поля (Поле1) на Дефицит/Избыток.

· В поле Формула ввести формулу расчета, выбирая имена полей из списка, = Стоимость предложения – Стоимость спроса.

· Выделить столбцы D, E, F, Gи указать их ширину равной 12 ед.

· Выделить ячейку В1.

4. Завершить запись макроса, выполнив команду меню Сервис – Макрос – Остановить запись.

В результате на листе Анализ будет построена сводная таблица, а в макрос записаны команды по ее построению.

Для удаления сводной таблицы текст макроса следует набрать в окне VBA. Выполните команду Сервис – Макрос – Макросы. В окне «Макрос» выберите макрос Сводная_1 и щелкните по кнопке Войти. Установите курсор на новую строку после текста макроса Сводная_1 и наберите с клавиатуры код удаления сводной таблицы.

Sub Удалить_сводную()

Range("B1").PivotTable.PivotSelect "", xlDataAndLabel

Selection.Clear

End Sub

Закройте окно VBA. Выполните команду Сервис – Макрос – Макросы. В окне «Макрос» выберите макрос Удалить_Сводную и щелкните по кнопке Выполнить. Таблица будет удалена.

Выполните макрос Сводная_1 построения сводной таблицы. Обратите внимание, что видны промежуточные этапы построения. Чтобы пользователь их не видел, добавим в начало и конец кода макроса команду отмены режима обновления экрана - Application.ScreenUpdating = False

……………………………………….

и команду включения режима - Application.ScreenUpdating = True.

Запишем макрос Сводная_2 (на листе Анализ).

1. Сервис – Макрос – Начать запись…

2. В окне Запись макроса указать имя макроса Сводная_2;

3. Выполнить запись:

· Выделить ячейку В1;

· Выполнить команду Данные – Сводная таблица.

В качестве исходного диапазона указать лист Данные, выделить исходную таблицу. Кнопка Далее.

· На третьем шаге построения выбран переключатель «существующий лист» и указана ячейка В1 листа Анализ. Теперь нажать кнопку Макет.

· Страница - Дата

· Строка – Номинал

· Столбец – Курс

· Данные – Эмиссия и Спрос

· Закончить построение сводной таблицы.

· Закрыть окно «Список полей сводной таблицы»

· Щелкнуть правой кнопкой мыши любую ячейку столбца Номинал, выбрать в контекстном меню команду Группа и структура – Группировать. В окне Группирование указать шаг группировки или согласиться с предложенным по умолчанию. Щелкнуть ОК.

· Щелкнуть правой кнопкой мыши любую ячейку строки Курс ЦБ, выбрать в контекстном меню команду Группа и структура – Группировать. В окне Группирование указать шаг группировки 0,05. Щелкнуть ОК.

· В столбце Данные выделить ячейку «Сумма по полю Эмиссия ЦБ» и на панели инструментов «Сводная таблица» щелкнуть кнопку «Параметры поля». Переименовать поле «Сумма по полю Эмиссия ЦБ» в «Эмиссия ЦБ». Нажать ОК (см. рисунок). В имени нового поля нельзя использовать существующие имена, поэтому в конце имени просто добавим пробел.

· Выделить ячейку «Сумма по полю Спрос ЦБ», щелкнуть кнопку «Параметры поля». Переименовать поле в «Спрос ЦБ». Нажать ОК.

· Выделить ячейку В1.

4. Завершить запись макроса, выполнив команду меню Сервис – Макрос – Остановить запись.

Добавить в текст макроса команды отмены и включения режима обновления экрана. Выполнить макрос Удалить_сводную.

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

· Строка - Дата

· Данные - Стоимость предложения и Стоимость спроса

Выполнить группировку столбца Дата по месяцам. Переименовать поля «Стоимость спроса» и «Стоимость предложения» в «Спрос» и «Предложение» соответственно. Завершить запись макроса.

Для автоматизации выбора варианта сводной таблицы можно использовать элемент управления «Список» из панели инструментов Формы. Начать нужно с формирования перечня названий сводных таблиц в ячейках листа.

В ячейках А22-А24 создадим перечень названий сводных таблиц.

	Анализ спроса и предложения
	Расчет по учетным периодам
	Расчет за весь учетный период

Теперь следует щелкнуть по кнопке Список и нарисовать контуры элемента на рабочем листе

В контекстном меню свойств объекта выбрать закладку «Элемент управления» и указать в поле «Форматировать список по диапазону» ячейки А22:А24, в поле «Связь с ячейкой» указать N2 и установить переключатель «возможен выбор» в положение «одинарного значения». ОК. В ячейке N2 будут появляться цифры 1, 2 или 3 согласно выбранному элементу списка.

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

Запишем макрос Изменить_сводную вручную.

1. Сервис – Макрос – Макросы.

2. В окне Макрос указать имя макроса Сводная_3 и нажать кнопку Войти;

3. В окне кода после команды End Sub с новой строки набрать вручную код процедуры.

Sub Изменить_сводную()

Удалить_сводную

Select Case Range("N2").Value

Case 1

Сводная_1

Case 2

Сводная_2

Case 3

Сводная_3

End Select

End Sub

Закрыть окно VBA и назначить макрос Изменить_сводную() объекту «список». Проверить работу макроса.

Для графической иллюстрации данных, полученных в третьей сводной таблице, добавим к ней диаграмму.

Создание макроса построения диаграммы. Перед началом записи макроса на листе «Анализ» должна быть построена сводная таблица под номером 3.

Запишем макрос Диаграмма (на листе Анализ).

1. Сервис – Макрос – Начать запись…

2. В окне Запись макроса указать имя макроса Диаграмма;

3. Выполнить запись:

· Выделить любую ячейку сводной таблицы;

· Щелкнуть кнопку «Мастер диаграмм» на панели инструментов «Стандартная». Мастер добавит новый рабочий лист с диаграммой;

· Перетащите кнопку в область легенды диаграммы

· Выполните команду меню Диаграмма – Тип диаграммы. Выберите обычный вариант диаграммы (первый вариант);

· Выполните команду Диаграмма – Размещение. В окне «Размещение диаграммы» выберите переключатель «Имеющемся», а в раскрывающемся списке выберите лист «Анализ»;

· ОК. Не перемещайте диаграмму и не изменяйте ее размеры.

· Выделите ячейку В1;

4. Завершить запись макроса.

Код макроса Диаграмма будет иметь вид:

Теперь дополним код макроса Сводная_3 процедурой построения диаграммы, иллюстрирующей сводную таблицу. Для этого следует выполнить команду Сервис – Макрос –Макросы, выбрать макрос Сводная_3 и нажать кнопку Изменить. В текст макроса нужно добавить одно слово – Диаграмма – перед командой включения режима обновления экрана. Дальше приведен фрагмент кода с добавленной процедурой

Для удаления диаграммы при смене варианта сводной таблицы нужно добавить команду удаления в макрос Изменить_сводную. Ниже приведен фрагмент макроса, добавленная команда выделена полужирным шрифтом.

Sub Изменить_сводную()

Удалить_сводную