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

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 Sub
Private Sub CommandButton2_Click()

End Sub

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

Private Sub CommandButton1_Click()

Range("C13").Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _

"Данные_продаж").CreatePivotTable TableDestination:= _

"[Пример.xls]Отчеты!R13C3", TableName:="СводнаяТаблица2",

DefaultVersion:= _

xlPivotTableVersion10

ActiveSheet.PivotTables("СводнаяТаблица 2").AddFields RowFields:= _

"Наименование"

ActiveSheet.PivotTables("СводнаяТаблица 2").PivotFields("Количество"). _

Orientation = xlDataField

ActiveWorkbook.ShowPivotTableFieldList = False

End Sub
Private Sub CommandButton2_Click()

Range("C13:D21").Select

Range("D21").Activate

Selection.ClearContents

End Sub

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

– в первую

CommandButton1.Enabled = False

CommandButton2.Enabled = True

– во вторую

CommandButton2.Enabled = False

CommandButton1.Enabled = True

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

Если затем щелкнуть по кнопке «Очистить», то сводная таблица будет удалена, кнопка «Очистить» станет не активной, а кнопка «Сформировать» станет активной.

Возникшая ситуация приведена на рис 5.17.



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

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

Однако практика создания информационных систем показывает

---

, что обойтись только этими возможностями получается не всегда.

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

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

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

Пример 1.

Пусть у нас на листе «Прайс-лист» имеются сведения о поставщиках и поставляемых ими товарах (рис. 5.18):

	B	C	D	E	F
4		Код товара	Код поставщика	Оптовая цена
5		1	1	100
6		2	1	120
7		3	1	120
8		2	2	110
9		3	2	150
10		4	3	250
11		1	4	110
12		2	4	130
13		3	4	140
14		4	4	300
15		5	4	400
16		6	4	500
17

---

Рис. 5.18. Размещение данных на листе «Прайс-лист».
А на листе «План закупок» сформирован план следующего вида (рис. 5.19):

	B	C	D	E	F	G
3
4		Код товара	Код поставщика	Количество	Сумма
5		1	1	50
6		2	1	40
7		3	1	10
8		2	2	20
9		3	2	70
10		4	3	55
11		1	4	30
12		2	4	40
13		3	4	80
14		4	4	30
15		5	4	20
16		6	4	20
17

---

Рис. 5.19. Размещение данных о закупках на листе «План закупок».
Необходимо определить суммы, выплачиваемые каждому поставщику.

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

В то же время можно создать собственную функцию следующего вида:
Function ЦенаПоставки(КодТовара, КодПоставщика) AsVariant

For i = 5 To 16

x1 = Sheets("Прайс-лист").Cells(i, 3)

x2 = Sheets("Прайс-лист").Cells(i, 4)

If x1 = КодТовара And x2 = КодПоставщика Then

ЦенаПоставки = Sheets("Прайс-лист").Cells(i, 5)

Exit Function

End If

Next

ЦенаПоставки = "Неверен код товара или поставщика"

End Function

Для окончательного решения задачи в ячейку F5 вводится формула:

=E5*ЦенаПоставки(C5;D5)
Примечание

Функцию ЦенаПоставки можно написать и без привязки к конкретным адресам. В этом случае данным прайс-листа следует присвоить имя (например, «ПрайсЛист») и тогда параметры размещения данных можно определить автоматически. Единственное что потребуется при возможных модификациях данных это соблюдать правила:

- в первой колонке данных должны быть коды товаров;

- во второй – коды поставщиков;

- в третьей – оптовая цена.
Function ЦенаПоставки(КодТовара, КодПоставщика) AsVariant

Dim s As Range
Set s = Range("ПрайсЛист ")

r = s.Row 'Начальная строка диапазона

c = s.Column ' Начальный столбец диапазона

n = s.Rows.Count ' Количество строк в диапазоне

For i = r + 1 To n + r

x1 = Sheets("Прайс-лист").Cells(i, c)

x2 = Sheets("Прайс-лист").Cells(i, c + 1)

If x1 = КодТовара And x2 = КодПоставщика Then

ЦенаПоставки = Sheets("Прайс-лист").Cells(i, с+2)

ExitFunction

EndIf

Next

ЦенаПоставки= "Неверен код товара или поставщика"
End Function
Пример 2.

Одной из основных задач управления является контроль за выполнением решений.

При автоматизации этой задачи обычно фиксируется информация следующего вида (рис. 5.20).

	B	C	D	E	F	G	H
3						12.12.2011
4
5		Номер п/п	Дата приема	№ документа	Контрольный срок	Дата решения	Статус
6		1	05.11.2011	123/4-12	05.12.2011
7		2	05.11.2011	123/3-11	05.12.2011	27.11.2011
8		3	06.11.2011	123/4-11	06.12.2011	10.12.2011
9		4	14.11.2011	123/4-12	14.12.2011
10		5	15.11.2011	123/4-13	15.12.2011
11

---