Обработка эт в среде vbа

Цель работы: разработка проектов по обработке электронной таблицы в среде VBА (подготовка ЭТ и создание сценариев). При подготовке к работе изучить теоретический материал (с.37 – 40).

Задание 1. Создания в Excel ЭТ обработки данных. Листов 3. При создании таблицы исходных данных использутся шаблон (рис. Л10.1), который заполняется данными из таблицы на рис. Л10.6. В качестве примера, для нулевого варианта составлена таблица исходных данных на рис. Л10.2 и составлена электронная таблица (ЭТ) на рис. Л10.3.

Продукция	Ед. изм	План	Сырье для производства
			Сырье 1	Сырье 2	Сырье 3
Продукт 1	кг	П1	C1	С2	С3
Продукт 2	кг	П2	С11	С21	С31
Продукт 3	кг	П3	С12	С22	С32
			Цены за 1 кг. сырья
Поставщик 1			Ц1	Ц2	Ц3
Поставщик 2			Ц11	Ц21	Ц31
Поставщик 3			Ц12	Ц22	Ц32

Рис. Л10.1. Шаблон для составления таблицы исходных данных

Продукция	Ед. изм	План	Сырье для производства
			Сырье 1	Сырье 2	Сырье 3
Продукт 1	кг	210	12%	23%	32%
Продукт 2	кг	234	43%	12%	32%
Продукт 3	кг	456	12%	11%	12%
			Цены за 1 кг. сырья
Поставщик 1			3,5	4,5	2,5
Поставщик 2			3,2	5,5	2,6
Поставщик 3			3,3	4,1	2,8

Рис. Л10.2. Таблица исходных данных для нулевого варианта

Рис. Л10.3. Исходная электронная таблица

В исходной ЭТ необходимо выполнить:

• Вычисление веса (в кг.) каждого вида сырья в % -ом отношении от выпуска продукции 1, 2, и 3 (ячейки D5:D7; G5:G7; J5:J7 ). Например, в ячейке D5 формула будет иметь вид =B5*C5/100 ;

• Затрат на каждое сырье, используя цену за 1кг. (ячейки E5:E7; H5:H7; K5:K7 ). Например, в ячейке Е5 формула будет иметь вид =$E$3*D5, а в ячейке Е8 соответственно =СУММ(E5:E7). Аналогично вычисляются суммы в ячейках H8; K8;

• В ячейке L8 предусмотреть формулу суммы ячеек E8; H8; K8;

• Добавить к ЭТ в ячейках А10, А11, А12 соответствующие надписи (рис. Л10.4).

Таким образом подготовлена ЭТ для определения затрат на выпуск продукции от 1-го поставщика.

Рис. Л10.4. ЭТ для определения затрат от 1 - го поставщика

Задание 2. Создание сценария. Для вставки элементов управления в ЭТ необходимо выполнить следующие действия:

1. Открыть панель инструментов ″Visual Basic″, выполнив команды меню Вид, Панели инструментов, Visual Basic .

2. Открыть панель элементов управления, щелкнув в панели инструментов Visual Basic по кнопке ″Элементы управления″.

3. Последовательно установить с помощью мыши четыре кнопки управления

и их свойства согласно таблице Л10.1. При этом активизируется Режим конструктора.

Таблица Л10.1 — Таблица свойств элементов

Элемент управления	Свойство	Значение свойства
Кнопка 1	Caption	Поставщик 1
Кнопка 2	Caption	Поставщик 2
Кнопка 3	Caption	Поставщик 3
Кнопка 4	Caption	Очистка ячеек

Двойным щелчком мыши по кнопке 1 открыть редактор кода Visual Basic и создать процедуру обработки события первой и т.д. кнопок.

Private Sub CommandButton1_Click() ' 1 поставщик

Range("E3") = 3.5 'Цена за кг. 1-го сырья 1-м поставщиком

Range("h3") = 4.5 'Цена за кг. 2-го сырья 1-м поставщиком

Range("K3") = 2.5 'Цена за кг. 3-го сырья 1-м поставщиком

Range("D10") = Range("L8") 'Передача затрат в ячейку D10

End Sub

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

Private Sub CommandButton2_Click() ' 2 поставщик

Range("E3") = 3.2 'Цена за кг. 1-го сырья 2-м поставщиком

Range("h3") = 5.5 'Цена за кг. 2-го сырья 2-м поставщиком

Range("K3") = 2.6 'Цена за кг. 3-го сырья 2-м поставщиком

Range("D11") = Range("L8") 'Передача затрат в ячейку D11

End Sub

Private Sub CommandButton3_Click() ' 3 поставщик

Range("E3") = 3.3 'Цена за кг. 1-го сырья 3-м поставщиком

Range("h3") = 4.1 'Цена за кг. 2-го сырья 3-м поставщиком

Range("K3") = 2.8 'Цена за кг. 3-го сырья 3-м поставщиком

Range("D12") = Range("L8") 'Передача затрат в ячейку D12

End Sub

Private Sub CommandButton4_Click() ' Процедура обнуления ячеек

Range("E3") = 0 ' Обнуление ячейки Е3

Range("h3") = 0

Range("K3") = 0

Range("D10") = 0

Range("D11") = 0

Range("D12") = 0

End Sub

Запуск сценария. Вывести Excel из режима конструктора, щелкнув в панели инструментов кнопку ″Выход из режима конструктора ″. Затем щелкать последовательно первые три кнопки на рабочем листе 1. В ячейках D11: D13 выбрать поставщика с минимальными затратами (рис. Л10.4). Кнопка 4 служит для обнуления исходных ячеек.

Рис. Л10.5. Результат работы сценария

На листе 2 представить ЭТ с выводом всех формул.