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

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

Товар	Количество
Валенки	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").SelectSelection.Consolidate Sources:= _"'E:\Магазинсувениров.xls'!Поставка", Function:=xlSum, _TopRow:=True, LeftColumn:=True, CreateLinks:=FalseRange("L4:M10").SelectSelection.Consolidate Sources:= _"'E:\Магазинсувениров.xls'!Продажа", Function:=xlSum, _TopRow:=True, LeftColumn:=True, CreateLinks:=FalseRange("J4").SelectEnd Sub- создадим кнопку «Остатки» и назначим ей только что созданный макрос;- скроем столбцы K, L и M.О кончательный интерфейс отчета может выглядеть следующим образом (рис. 5.16):Рис. 5.16. Окончательный интерфейс отчета об остаточных количествах товаров на складе

5.2.3.5.5. Использование элементов управленияВ Excel имеется возможность использовать две группы управляющих элементов.Первая – расположена на панели форм. Все вышеприведенные примеры построены на использовании именно этих элементов. Такие элементы представляют собой компоненты ActiveX и предназначены для запуска пользовательских макросов.Вторая группа – это «истинные» управляющие элементы Visual Basic for Application (VBA). Они обладают всеми свойствами элементов визуальных систем программирования. В целом – элементы группы VBA имеют больше возможностей и большую палитру свойств. Главная их особенность – это способность реагировать на события типа OnClick, OnChange и т.д. Однако их использование требует от пользователя определенной программистской квалификации.Исходя из этого, имеются различия в свойствах этих элементов и методах работы с ними. При этом может оказаться, что в одних случаях удобнее использовать одни элементы, а в других – другие.Покажем возможности элементов VBA на предыдущем примере формирования сводной таблицы.Недостатком сформированного там интерфейса является то, что пользователь должен знать об очередности нажатия кнопок «Сформировать» и «Очистить». В противном же случае произойдет сбой в работе макросов. Для того, чтобы исключить эту возможность модифицируем интерфейс расчетов.

1. 
   Командой Вид > Панели инструментов > Элементы управления вызовем панель «Элементы управления».
   
2. 
   Используя элемент «Кнопка» этой панели создадим две новых кнопки.
   
3. 
   Нажмем на панели «Элементы управления» кнопку «Режим конструктора»
   
4. 
   Щелчком ПКМ по первой кнопке вызовем ее контекстное меню.
   
5. 
   В меню выберем пункт «Свойства» и в появившейся таблице свойств в свойстве Caption (заголовок) поменяем значение – вместо CommandButton1 напечатаем «Сформировать».
   
6. 
   Аналогично поменять название второй кнопки – вместо CommandButton2 напечатаем «Очистить».
   
7. 
   На панели «Элементы управления» нажать кнопку «Исходный текст»
   
8. 
   Система перейдет в редактор Visual Basic и там будут две заготовки процедур для только что созданных кнопок
   

Private Sub CommandButton1_Click()End SubPrivate Sub CommandButton2_Click()End Sub

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

Private Sub CommandButton1_Click()Range("C13").SelectActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _"Данные_продаж").CreatePivotTable TableDestination:= _"[Пример.xls]Отчеты!R13C3", TableName:="СводнаяТаблица2", DefaultVersion:= _xlPivotTableVersion10ActiveSheet.PivotTables("СводнаяТаблица 2").AddFields RowFields:= _"Наименование"ActiveSheet.PivotTables("СводнаяТаблица 2").PivotFields("Количество"). _Orientation = xlDataFieldActiveWorkbook.ShowPivotTableFieldList = FalseEnd SubPrivate Sub CommandButton2_Click()Range("C13:D21").SelectRange("D21").ActivateSelection.ClearContentsEnd Sub

10. 
    Добавим в обе процедуры команды активации и дезактивации кнопок:
    

– в первуюCommandButton1.Enabled = FalseCommandButton2.Enabled = True– во вторуюCommandButton2.Enabled = FalseCommandButton1.Enabled = True

11. 
    Вернемся в Excel и на панели «Элементы управления» нажмем кнопку «Выход из режима конструктора»
    
12. 
    Если отчет еще не сформирован, то щелкаем по «Сформировать». Должна появиться сводная таблица и кнопка «Сформировать» станет не активной.
    

Если затем щелкнуть по кнопке «Очистить», то сводная таблица будет удалена, кнопка «Очистить» станет не активной, а кнопка «Сформировать» станет активной.Возникшая ситуация приведена на рис 5.17.Рис. 5.17. Организация интерфейса при расчетах с использованием сводных таблиц5.2.3.5.6. Создание собственных функций (прямое программирование)В общей случае Excel является уникальной системой. Благодаря наличию очень большого количества встроенных функций и специальных средств с его помощью можно решать очень широкий класс прикладных задач, связанных с обработкой управленческой информации.Однако практика создания информационных систем показывает