Голоскоков К.П., Филиппова. И.Г. (сост.) Информационные технологии в экономике
Подождите немного. Документ загружается.
Справка:
Общая прибыль = Объем продаж * Размер прибыли в %.
Расход = Аренда + Услуги + Выплаты.
Чистая прибыль = Общая прибыль - Расход.
Показатели в столбцах C,D,E вычисляются по схеме:
Объем продаж 1998 г = Объем продаж 1997 г * (1 + % роста
объема продаж).
Размер прибыли 1998 г = Размер прибыли 1998 г. * (1 + %
роста размера прибыли).
и[т.[д.
4. Определить первый сценарий, выполнив команду
СЕРВИС/Сценарии:
- в диалоговом окне «Диспетчер сценариев» нажать кнопку
«Добавить»;
- в окне «Добавить сценарий» ввести в поле «Имя сценария» имя
(например, «Сценарий 1»);
- в поле «Изменяемые ячейки» ввести абсолютную ссылку на
ячейки, содержащие значения изменяемых параметров (B13:B17).
5. Щелкнув по кнопке «Добавить» создать аналогично
«Сценарийi2», изменив непосредственно в окне значения
процентов роста показателей в ячейках В13:В17, например, 3%,
2%, 6%, 4%, 4%.
6. Аналогично предыдущему пункту, изменяя значения отдельных
показателей, создать еще «Сценарий 3» и «Сценарий 4».
7. Щелкнув по кнопке «Отчет» в окне «Диспетчер сценариев»,
перейти к построению отчета.
- в окне «Отчет по сценарию» выбрать тип «структура» и ввести
в поле «Ячейки результата» ссылки на ячейки С11, D11, E11,
содержащие значения чистой прибыли.
Замечание: Ссылки должны разделятся символом «;» – «точка
с запятой».
Щелкнуть по кнопке «Ok». На экране появится рабочий лист
«Структура сценария» с таблицей примерно следующего вида:
21
8. Создать «Сводную таблицу по сценарию». Для этого перейти на
исходный рабочий лист и выполнить команду
СЕРВИС/Сценарии:
- в окне «Диспетчер сценариев», перейти к построению отчета.
- в окне «Отчет по сценарию» выбрать тип «сводная таблица» и
щелкнуть по кнопке «Ok».
Появится рабочий лист с таблицей примерно следующего вида:
22
ЛАБОРАТОРНАЯ РАБОТА №3
Цель работы: освоение следующих технологических приемов
работы:
- поиск оптимальных решений;
- работа с макросами.
Ознакомление с примером поиска оптимального решения
корпорации Microsoft:
1. Загрузить Microsoft Excel.
2. Открыть табличный файл Solvsamp.xls, находящийся в папке C:\
Program Files\Microsoft Office\Office\Examples\Solver.
3. Установить рабочий лист «Краткий обзор». Освоить технологию
решения на модели, связывающей затраты на рекламу с прибылью:
- найти затраты на рекламу в 1-м квартале, при которых
достигается максимальная прибыль;
- найти ежеквартальные затраты на рекламу, обеспечивающие
максимальную годовую прибыль без ограничений на затраты;
- найти ежеквартальные затраты на рекламу, обеспечивающие
максимальную годовую прибыль при наличии ограничений
на затраты.
Выполнить поиск оптимальных решений для экономико-
математических моделей, расположенных на других листах рабочей
книги:
- транспортная задача;
- график дежурств;
- управление капиталом;
- портфель ценных бумаг.
ПОИСК РЕШЕНИЙ
ЗАДАЧА 1
Найти оптимальные объемы выпуска трех видов продукции для
получения максимальной прибыли от их продажи.
23
A B C D
2 КОЛИЧЕСТВО
ПРИБЫЛЬ
НА 1 ШТ.
ДОХОД
3 ИЗДЕЛИЕ А
100 13 1300
4 ИЗДЕЛИЕ В
100 18 1800
5 ИЗДЕЛИЕ С
100 22 2200
6 ВСЕГО
300 5300
При решении данной задачи должны быть учтены следующие
ограничения:
общий объем производства – всего 300 изделий;
должно быть произведено не менее 50 изделий А;
должно быть произведено не менее 40 изделий В;
должно быть произведено не более 40 изделий С.
Технология:
1. Вести в новый рабочий лист данные для вычисления прибыли от
продажи трех видов продукции, причем в ячейки столбца D, и в
ячейку B6 должны быть введены формулы.
2. Запустить задачу поиска решений. Для этого: выполнить команду
Сервис/ Поиск решений … и в диалоге «Поиск решений» ввести
данные:
в поле «Установить целевую ячейку» указать адрес D6;
установить флажок «Равной максимальному значению»;
в поле «Изменяя ячейки» определить изменяемые ячейки (B3:B5);
в поле «Ограничения» по одному добавить каждое из следующих
четырех ограничений задачи (B6=300; B3>=50; B4>=40; B5<=40).
Для этого щелкнуть по кнопке «Добавить» и в появившемся окне
«Добавление ограничения» ввести ссылку на ячейку (B6),
оператор ограничения (=) и значение (300), для добавления
следующего ограничения щелкнуть кнопку «Добавить» и
повторить процедуру добавления ограничения; после ввода
последнего ограничения щелкнуть кнопку «ОК»;
в диалоговом окне «Поиск решения» щелкнуть кнопку
«Выполнить»;
24
в диалоге «Результаты поиска решения» установить
переключатель «Сохранить найденное решение», в окне «Тип
отчета» выбрать «Результаты» и нажать кнопку «Ok»;
ознакомиться с отчетом по результатам, помещенным на новом
листе.
ЗАДАЧА 2
С помощью средства «Поиск решения» решить задачу
минимизации расходов на перевозку грузов.
Постановка задачи:
Компания имеет 3 склада, которые расположены в разных
районах города. Заказы на перевозку грузов поступают из сети
розничных магазинов, распределенных по всей территории города.
Цель задачи – удовлетворить потребность всех шести розничных
магазинов в товарах, находящихся на трех складах и сохранить при
этом общие расходы на перевозку на минимальном уровне.
ИСХОДНЫЕ ДАННЫЕ:
A B C D E F
1
Таблица стоимости перевозок
2 СКЛАД 1 СКЛАД 2 СКЛАД 3
3 МАГАЗИН 1
58 47 108
4 МАГАЗИН 2
87 46 100
5 МАГАЗИН 3
121 30 57
6 МАГАЗИН 4
149 66 83
7 МАГАЗИН 5
62 115 164
8 МАГАЗИН 6
128 28 38
A B C D E F G
9
10
ПОТРЕБНОСТЬ
В ТОВАРЕ
КОЛИЧЕСТВО ПЕРЕВЕЗЕННОГО ТОВАРА СО СКЛАДА
11 СКЛАД 1 СКЛАД 2 СКЛАД 3 ВСЕГО
12 МАГАЗИН 1
150 25 25 25 75
13 МАГАЗИН 2
225 25 25 25 75
14 МАГАЗИН 3
100 25 25 25 75
15 МАГАЗИН 4
250 25 25 25 75
16 МАГАЗИН 5
120 25 25 25 75
17 МАГАЗИН 6
150 25 25 25 75
18 ВСЕГО
995 150 150 150 450
25
A B C D E F
19
20
Запасы на складе 400 350 500
21
Запасы после отпуска =D20-D18 ... ...
A B C D E F G
24
Стоимость перевозок =СУММПРОИЗВ (D3:D8;D12:D17) … … …
В ячейке G24 подводится общая стоимость перевозок для всех
заказов.
В результате поиска оптимального решения должны быть
найдены такие значения ячеек D12:F17, при которых каждый
розничный магазин будет получать желаемое количество товара и
общая стоимость перевозок будет минимальна. Нужно
минимизировать значение, находящееся в ячейке G24, изменяя
значение диапазона ячеек D12:D17 с учетом следующих
ограничений:
количество необходимого товара для каждого розничного магазина
должно быть равно количеству перевезенного (т.[е.[все заказы
должны быть выполнены);
значения в изменяемых ячейках не могут быть отрицательными;
количество запасов на каждом складе не должно быть
отрицательным.
ЗАДАЧА 3
Для изготовления пластмассовых втулок и шестеренок требуется
стеклоткань, эпоксидная смола и отвердитель. На изготовление одной
втулки затрачивается 4 ед. стеклоткани, 3 ед. – эпоксидной смолы и 2
ед. – отвердителя, а на изготовление одной шестеренки –
соответственно 3, 4 и 6 ед. материалов. Прибыль предприятия от
изготовления одной втулки составляет 20 руб., а шестеренки – 40 руб.
Сколько втулок и шестеренок должно изготовить предприятие для
получения наибольшей прибыли, если в его распоряжении имеется
480 ед. стеклоткани, 444 ед. эпоксидной смолы и 546 ед. отвердителя.
26
Для решения задачи можно использовать таблицу:
ВТУЛКИ ШЕСТЕРЕНКИ РАСХОД ИМЕЕТСЯ
СТЕКЛОТКАНЬ
4 3 603 480
ЭПОКСИДКА
3 4 1100 444
ОТВЕРДИТЕЛЬ
2 6 1400 546
ПРИБЫЛЬ ОТ 1 ШТ.
20 40
ВЫПУСК
100 200
ОБЩАЯ ПРИБЫЛЬ
10000
ЗАДАЧА 4
Прутки длиной 8 метров разрезаются на заготовки длиной 3
и 2.4 м, Заготовок первого типа нужно получить не менее 25 штук, а
второго – не менее 36 штук. Определить минимальное число
разрезаемых прутков. Допускаются лишь способы разрезки, при
которых длина остатка меньше любой заготовки.
Для решения задачи можно составить таблицу:
Длина прутка 8
Заготовка 1 3
Заготовка 2 2.4
Заготовка 1 Заготовка 2 Кол-во прутков
Способ 1 2 0 8
Способ 2 1 2 11
Способ 3 0 3 8
Всего прутков 27
Кол-во заготовок 1 27 25
Кол-во заготовок 2 38 36
Примечание. При вводе ограничений необходимо
предусмотреть условия целочисленности и положительности
искомых величин.
РАБОТА С МАКРОСАМИ
Макрос – это программа, представляющая собой
последовательность макрокоманд и макрофункций. Макросы
27
предназначены для автоматизации различных процедур работы с
таблицей, в частности, автоматизации часто повторяющихся
последовательностей рабочих шагов. В Excel 97 макросы
составляются на языке Visual Basic для приложений (Visual Basic for
application) – едином для всех компонентов Office 97.
Наиболее простым средством создания макроса является его
запись с помощью макрорекордера.
Для записи макроса с помощью макрорекордера необходимо
выполнить следующие шаги:
- запустить макрорекордер командой СЕРВИС/Макрос;
- присвоить имя макросу;
- выполнить необходимые операции с таблицей, которые будут
записаны макрорекордером в макропрограмму;
- после выполнения последней операции остановить запись макроса.
ЗАДАНИЕ 1
Записать макрос, который будет заносить в ячейки А1:С4
последовательность натуральных чисел, начиная с 1, по часовой
стрелке. Присвоить этому макросу имя «Макрос 1». Создать новое
меню «Макросы» и в нем пункт с именем «Макрос 1». Создать новую
панель инструментов и поместить на нее кнопку для запуска
созданного макроса.
Технология:
1. Установите на чистом рабочем листе курсор в ячейку Е10.
2. Для запуска макрорекордера выполните команду
СЕРВИС/Макрос/Начать запись.
3. В диалоговом окне «Запись макроса» введите имя «Макрос 1» и
щелкните по кнопке «Ok». На экране появится кнопка для
остановки записи макроса. Учтите, что с этого момента все Ваши
действия должны быть четкими, т.[к.[они записываются
макрорекордером.
4. Последовательно введите в ячейку А1 число 1, в ячейку В1 число
2, в ячейку С1 число 3, в ячейку С2 число 4, в ячейку С3 число 5,
28
в ячейку С4 число 6, в ячейку В4 число 7, в ячейку А4 число 8, в
ячейку А3 число 9, в ячейку А2 число 10.
5. Остановите запись макроса, щелкнув по кнопке «Остановить
запись».
6. Очистите рабочий лист, на котором производили запись и
установите курсор в произвольную ячейку рабочего листа.
7. Запустите только что созданный макрос. Для этого выполните
команду СЕРВИС/Макрос. Щелкните по имени макроса в окне
«Макрос», а затем по кнопке «Выполнить». Действия, ранее
выполненные Вами вручную, будут повторены макросом
автоматически.
8. Создайте меню с именем «Макросы». Для этого:
- выполните команду СЕРВИС/Настройка и в окне «Настройка»
на вкладке «Команды» выделите категорию «Новое меню»;
- перетащите мышью название этой категории на панель меню;
- щелкните правой кнопкой по перемещенному на панель
названию и поменяйте имя меню на «Макросы».
9. Создайте пункт с именем «Макрос 1» в меню «Макросы». Для
этого:
- выделите на вкладке «Команды» категорию «Макросы»;
- перетащите строку «Настраиваемая команда» из окна
«Команды» на панель меню в область, расположенную под
названием «Макросы»;
- щелкните правой кнопкой по вновь созданному пункту
«Настраиваемая команда меню» и поменяйте его имя на
«Макрос[1»;
- включите режим «только текст» и выполните пункт
«Назначить макрос»;
- в окне «Назначить макрос» выделите в списке строку с именем
макроса, который должен запускаться при щелчке по этому
пункту и щелкните по кнопке «Ok»;
- закройте окно «Настройка».
29
10. Проверьте работу макроса 1 при его запуске через меню
«Макросы». Проверьте работу макроса при запуске на другом
рабочем листе.
11. Просмотреть текст макроса на языке Visual Basic, выполнив
команду СЕРВИС/Макрос/Макросы и щелкнув по кнопке
«Войти». Вернуться в таблицу, выполнив команду редактора
макросов ФАЙЛ/Закрыть и вернуться в Microsoft Excel.
12. Создать новую панель инструментов и поместить на нее кнопку
для запуска Макроса 1. Для этого:
- выполнить команду СЕРВИС/Настройка и в окне
«Настройка» на вкладке «Панели» включить, например, панель
«Элементы управления»;
- в окне «Настройка» на вкладке «Команды» установить
категорию «Макросы»;
- перетащить строку «Настраиваемая кнопка» на панель
«Элементы управления»;
- не закрывая окна «Настройка», щелкнуть правой кнопкой
мыши по кнопке «Настраиваемая кнопка» выполнить пункты
меню «Выбрать значок для кнопки» и «Назначить макрос»;
- закрыть окно «Настройка» и проверить действие кнопки для
запуска макроса.
ЗАДАНИЕ 2
Составить макрос для формирования строки с итогами по
таблице.
Технология:
1. На новом рабочем листе Составить произвольную таблицу чисел,
располагающуюся в ячейках А1: D5. Установить курсор вне
таблицы, выделив ячейку вне таблицы.
2. Включить запись макроса.
3. Нажать комбинацию клавиш Ctrl+Home для установки курсора в
ячейку А1.
30