- •Тема 7. Технология решения задач в среде табличного процессора
- •7.1 Методические указания и основные термины
- •7.1.1 Функциональные возможности и пользовательский интерфейс программы Excel
- •Упражнение 7.1
- •Упражнение 7.2
- •7.1.2. Создание и редактирование электронных таблиц
- •7.1.3. Форматирование электронных таблиц и операции над документами
- •Упражнение 7.3
- •Упражнение 7.4
- •Экзаменационная ведомость
- •Упражнение 7.5
- •Упражнение 7.6
- •Расчет стоимости продукции
- •7.1. 4. Построение, редактирование и форматирование диаграмм
- •Упражнение 7.7
- •Упражнение 7.8
- •Упражнение 7.9
- •Упражнение 7.10
- •Упражнение 7.11
- •Упражнение 7.12
- •Упражнение 7.13
- •Упражнение 7.14
- •Упражнение 7.15
- •Упражнение 7.16
- •7.1.5. Использование встроенных функций
- •Упражнение 7.16
- •Упражнение 7.17
- •Упражнение 7.18
- •Упражнение 7.19
- •Упражнение 7.20
- •Упражнение 7.21
- •Упражнение 7.22
- •Упражнение 7.23
- •Упражнение 7.24
- •Упражнение 7.25
- •Упражнение 7.26
- •Упражнение 7.27
- •Упражнение 7.28
- •Упражнение 7.29
- •Упражнение 7.30
- •Упражнение 7.31
- •Упражнение 7.32
- •Упражнение 7.33
- •Упражнение 7.34
- •Упражнение 7.35
- •7.1.6 Приемы автоматизации обработки данных и решения аналитических задач
- •Упражнение 7.36
- •Упражнение 7. 37
- •Упражнение 7.38
- •Упражнение 7.39
- •Упражнение 7.40
- •Упражнение 7.41
- •Упражнение 7.42
- •7.1.7. Работа со списками и сводными таблицами
- •Упражнение 7.43
- •Упражнение 7.44
- •Упражнение 7.45
- •Упражнение 7.46
- •7.2. Вопросы для самоконтроля
Упражнение 7.39
Рассмотрим задачу по выбору деловых партнеров для фирмы “Атлант”.
Условия задачи. Фирме “Атлант” необходимо выбрать деловых партнеров (из числа пяти организаций) для заключения контрактов на поставку товаров на сумму до 5 млн. руб., определив объем сделки с каждым из партнеров и обеспечив при этом для себя максимальную прибыль с учетом того, что ожидаемая сумма рисков от сделок не превысит суммы ожидаемой прибыли от выполнения всех контрактов.
Предлагаемые фирме “Атлант” возможными партнерами суммы контрактов, а также прогнозируемые сотрудниками фирмы “Атлант” показатели прибыльности и рисков при заключении контрактов с конкретными фирмами (в процентах) приведены в нижеследующей таблице.
Показатели |
Фирмы - партнеры |
||||
Атолл |
Алмаз |
Вега |
Лилия |
Гранат |
|
Сделки,руб |
500000 |
800000 |
1600000 |
2000000 |
1000000 |
Прибыль,% |
14 |
10 |
12 |
15 |
11 |
Риск,% |
8 |
8,5 |
10 |
10 |
7 |
Решение задачи. На листе Партнеры рабочей книги Свод создадим в табличной форме модель решаемой задачи, занеся в ячейки B4: F4 известные данные по прибыли, в ячейки B5 : F5 - по рискам, в ячейки B3 : F3 – предполагаемые суммы контрактов (хотя последние ячейки могут быть заполнены и произвольно).
Заполним расчетными формулами ячейки столбца Итого:
G3 = СУММ (B3 : F3)
G4 = B3*B4 + C3*C4 + D3*D4 + E3*E4 + F3*F4
G5 = B3*B5 + C3*C5 + D3*D5 + E3*E5 + F3*F5
Для непосредственного решения задачи активизируем ячейку G4 и командами Сервис\Поиск решения вызовем диалоговое окно Поиск решения. В этом окне нажмем кнопку <Равной максимальному значению>, в окне Изменяя ячейки вводим диапазон изменяемых ячеек (подбираемые значения сделок ) B3 : F3.
Далее вводим ограничения поиска. Для этого нажатием кнопки <Добавить > вызываем диалоговое окно Добавление ограничения и вводим первое ограничение (рис.7.46).
Рис.7.46. Окно Добавление ограничения
Для ввода следующего ограничения вновь нажимается кнопка <Добавить>. После завершения ввода всех ограничений нажатием кнопки <ОК> возвращаемся в окно Поиск решения (рис.7.47).
Рис. 7.47. ..Вид окна Поиск решения с введенными ограничениями для задачи выбора деловых партнеров
Нажатие кнопки <Выполнить> обеспечивает запуск программы поиска решения, после успешного завершения которой появляется диалоговое окно Результаты поиска решения с оптимальным результатом прибыли в размере 777000 руб. и оптимальными величинами контрактов с партнерами.
Рис. 7.48. Окончательный результат решения задачи
Нажатием на кнопку <Сохранить сценарий> можно сохранить результаты решения задачи с задаваемым именем сценария. В случае нажатия кнопки <Восстановить исходные данные> можно возвратиться к исходным данным, изменить ряд ограничений и вновь запустить программу поиска решения.