2018-01-08
624
Range("B1") = 24.6
End Sub
Эта процедура будет выполняться лишь после выхода Excel из режима конструктора, т.е. нужно щелкнуть кнопку 3 (рис. 9.1).
Используя элементы управления, вставленные в рабочий лист и ячейки электронной таблицы для ввода и вывода информации можно создавать сценарии. Сценарий — это набор значений, которые Microsoft Excel сохраняет и может автоматически подставлять на листе. Сценарии можно использовать для прогноза результатов моделей и систем расчетов.
Пример 9.1. Создание сценария для выбора поставщика сырья, с целью минимизации затрат производства при выпуске продукции. Исходными данными для сценария являются:
1. Три поставщика, поставляют три вида сырья для производства трех видов продукции (см. табл. 9.1).
2. Каждый поставщик за сырье 1, 2 и 3-е выставляет свою цену за кг. Отсюда затраты на производство продукции будут различными.
Обычно составляется ЭТ, в ячейки которой вводят цены за сырье от 1 поставщика и выписывают из таблицы полученные затраты, затем аналогично выполняют для 2-го и 3-го поставщика. Из полученных результатов выбирают поставщика с минимальными затратами. Этот процесс можно автоматизировать, создав сценарий, по которому к ЭТ на рабочем листе добавляются три кнопки, при нажатии на каждую из которых в таблицу программно вставляются цены, предлагаемые соответствующими поставщиками. При этом затраты по каждому поставщику автоматически заносятся в отдельные от ЭТ ячейки, среди которых выбирается минимальное значение, т.е. поставщик. Интерфейс сценария составляют 4 кнопки.
|
|
|
1. Процедуры отклика на каждую кнопку приведены ниже:
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
Таблица 9.1. - Исходная таблица для сценария
|
|
|
|
Электронная таблица с интерфейсом представлена на рис. 9.2.
|
Рис. 9.2. ЭТ и интерфейс пользователя
При составлении ЭТ предусматривается использование формул расчета:
1. Формула веса каждого вида сырья в % отношении от заданной выпускаемой продукции (ячейки D5:D7; G5:G7; J5:J7). Например, в ячейке D5 =B5*C5/100.
2. Затрат на каждое сырье, используя цену за 1кг. И сумму затрат по каждому сырью (ячейки E5:E8;H5:H8;K5:K8). Например, в ячейке Е5 =$E$3*D5
3. В ячейке L8 предусмотреть формулу суммы ячеек E8; H8; K8.
Для работы сценария нужно вывести Excel из режима конструктора (кнопка 3 на рис. 9.1) и щелкать последовательно первые три кнопки на рабочем листе. Результат работы сценария представлен на рис. 9.3.
|
Рис. 9.3. Результат работы сценария
