Файл: Контрольная работа по теме Базы данных в Excel 72 IV. Макросы в ms excel 78 Макросы для автоматизации работ 78.doc

Рис. 5.10. Интерфейс расчета выручки за определенный период времени
Вычисления производятся следующим образом:

– в D5 и D6 вводятся даты начала и конца отчетного периода, а ячейке D8 отражается результат вычислений.

Для организации вычислений:

– на этом же листе за пределами экрана создаем шаблон критерия отбора;

		P	Q	R	S
5
6
7			Дата продажи	Дата продажи
8			>=11.11.09	<=20.11.09
9

Рис. 5.11. Размещение критериев для расчета выручки за определенный

период времени
– в Q8 вводим формулу =">="&D6;

– в R8 вводим формулу ="<="&D7;

– в D11 вводим формулу:

=БДСУММ(Данные_продаж;Продажи!H11;Q7:R8).

---

5.2.3.5.2. Использование встроенных функций в макросах

В макросах можно использовать и имеющиеся в Excel функции. Но при этом имеется одно ограничение:

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

Например.

Пусть для отчета, рассмотренного в предыдущем разделе необходимо выбрать вариант расчета.

К примеру:

- общая сумма выручки (уже реализовано в разделе 5.2.3.5.1.);

- средняя выручка;

- максимальная выручка;

- минимальная выручка.

Можно конечно выполнить все эти расчеты сразу.

Т.е. в ячейку D12 (рис. 5.10) ввести формулу:

=ДСРЗНАЧ(Данные_продаж;Продажи!H11;Q7:R8);

в ячейку D13 (рис. 5.10) ввести формулу:

=ДМАКС(Данные_продаж;Продажи!H11;Q7:R8);

и т.д.

Но если сделать вариант с выбором вида расчета, то интерфейс отчета может быть следующим (рис. 5.12):



Рис. 5.12. Интерфейс расчета показателей продаж за определенный период времени
Из раскрывающегося списка выбирается вид расчета и затем щелчок по кнопке «Рассчитать».

Технология создания такого интерфейса уже описана в разделах 5.2.3.4.3. Сортировка, 5.2.3.4.4. Поиск.

Поэтому дадим только краткие комментарии:

- для выбора операции используется элемент «Поле со списком»;

- этот элемент связан со списком операций, который введен в ячейки U11:U14;

- с этим списком связана ячейка U15;

Макрос для кнопки «Рассчитать» может иметь вид:
Sub Рассчитать()

k = Range("U15")

Select Case k

Case 1

Range("F11") = "=DSUM(Данные_продаж,Продажи!H11,Q7:R8)"

Case 2

Range("F11")="=DAVERAGE(Данные_продаж,Продажи!H11,Q7:R8)"

Case 3

Range("F11") = "=DMAX(Данные_продаж,Продажи!H11,Q7:R8)"

Case 4

Range("F11") = "=DMIN(Данные_продаж,Продажи!H11,Q7:R8)"

End Select

End Sub

Для определения вида англоязычного варианта функции рекомендуется стандартная технология:

– записывается временный макрос, в котором вызывается нужная нам функция;

- получившаяся команда копируется в нужный нам макрос;

- временный макрос удаляется.

5.2.3.5.3. Использование сводных таблиц

---

Предположим, что нам периодически требуются данные о структуре продаж следующего вида:

Товар	Количество
Валенки	15
Галоши	25
….	….

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

Интерфейс вычислений может выглядеть следующим образом (рис. 5.13).

		B		C		D		E
16
17
18
19
20
21
22			Сумма по полю Количество
23			Наименование		Итог
24			Валенки		41
25			Галоши		22
26			Кепки a'la Lenin		6
27			Кепки a'la Luzkov		9
28			Лапти дубовые		16
29			Лапти липовые		25
30			Общий итог		119
31

---

Рис. 5.13. Интерфейс отчета по структуре продаж
При щелчке по кнопке «Сформировать» появляется соответствующая сводная таблица.

Для реализации расчетов сформируем сводную таблицу в режиме записи макроса:

Сервис > Макрос > Начать запись > Ввести имя макроса (например, «Структура_продаж») > Данные > Сводная таблица > Далее > В качестве диапазона указать Данные_продаж > Далее > Переключатель установить в положение «Существующий лист» и ввести адрес формируемой сводной таблицы (в данном случае C22) > Макет > В область макета «Строка» перетаскиваем поле «Наименование», а в область «Данные» перетаскиваем поле «Количество» > Ok > Готово > Сервис > Макрос > Остановить запись

Затем создаем кнопку «Сформировать» и связываем ее с созданным макросом.

При первичном щелчке по кнопке сводная таблица формируется без проблем. Но, если щелкнуть по ней еще раз, то выйдет сообщение об ошибке.

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

Для автоматизации очистки запишем макрос:

Сервис > Макрос > Начать запись > Ввести имя макроса (например, «Очистка») > Выделяем область B21:E31 > Нажимаем клавишу «Delete» > Сервис > Макрос > Остановить запись.

Создадим кнопку «Очистить» и свяжем ее с макросом «Очистка».

Окончательный интерфейс расчетов будет выглядеть следующим образом (рис. 5.14).

Р
ис. 5. 14. Окончательный интерфейс отчета по структуре продаж
5.2.3.5.4. Использование средства «Консолидация»

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

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

Для этого:

- подготовим шаблон следующего вида (рис. 5.15).

	I	J	K	L	M	N
4		Наименование	Количество	Наименование	Количество	Остаток
5		Лапти липовые		Лапти липовые
6		Лапти дубовые		Лапти дубовые
7		Галоши		Галоши
8		Валенки		Валенки
9		Кепка a ’la Lenin		Кепка a ’la Lenin
10		Кепка a 'la Luzkov		Кепка a 'la Luzkov

---

Рис. 5.15. Шаблон отчета об остаточных количествах товаров на складе
- в колонки J и L скопируем с листа «Товары» список товаров;

- в ячейку N5 введем формулу =K5-M5, которую скопируем до строки 10;

- на листе «Поставки» диапазону E11:F311 присвоим имя “Поставка»;

- на листе «Продажи» диапазону E11:F311 присвоим имя “Продажа»;

- выполним команды:

Сервис > Макрос > Зададим имя макроса «Остатки» > Выделяем диапазон J4:K10 > Данные > Консолидация > В поле функция укажем «Сумма» > В поле список диапазонов укажем «Поставка» > Установим переключатели «Подписи верхней строки» и «Подписи верхнего столбца» > Ok > Выделяем диапазон L4:M10 > Данные > Консолидация > В поле функция укажем «Сумма» > В поле список диапазонов укажем «Продажа» > Установим переключатели «Подписи верхней строки» и «Подписи верхнего столбца» > Ok > Сервис > Макрос > Остановить запись
В результате должен получиться макрос примерно следующего вида:
Sub Остатки()

Range("J4:K10").Select

Selection.Consolidate Sources:= _

"'E:\Магазинсувениров.xls'!Поставка", Function:=xlSum, _

TopRow:=True, LeftColumn:=True, CreateLinks:=False

Range("L4:M10").Select

Selection.Consolidate Sources:= _

"'E:\Магазинсувениров.xls'!Продажа", Function:=xlSum, _

TopRow:=True, LeftColumn:=True, CreateLinks:=False

Range("J4").Select

End Sub
- создадим кнопку «Остатки» и назначим ей только что созданный макрос;

- скроем столбцы K, L и M.

О
кончательный интерфейс отчета может выглядеть следующим образом (рис. 5.16):

Рис. 5.16. Окончательный интерфейс отчета об остаточных

количествах товаров на складе

---