- •Раздел 1. Принцип работы электронных таблиц
- •Ввод и редактирование данных в ячейках
- •Форматирование данных в ячейках
- •Задание 1 Разработать таблицы Excel
- •Копирование данных в ячейках
- •Копирование и перемещение диапазонов ячеек
- •Ввод формул
- •Задание 2 Построить следующую таблицу Excel
- •Использования мастера диаграмм
- •Использование ссылок на диапазоны ячеек
- •Использование имен
- •Задание 3
- •Форматирование ячеек
- •Задание 4 Построить следующий бланк квитанции
- •Раздел 2. Редактирование созданных объектов Задание 5 Построить следующие диаграммы
- •Редактирование диаграмм
- •Задание 6
- •Использование встроенных функций Excel
- •Формулы массива
- •Раздел 3. Работа с группами таблиц Понятие списка в Excel
- •Работа со списками в Excel
- •Сводные таблицы Excel
- •Задание
- •Раздел 4. Решение задач оптимального управления в Excel
- •Постановка задачи и оптимизация модели с помощью процедуры поиска решения
- •Построение отчета по сценариям
- •Раздел 5. Шаблоны Понятие шаблона документа
- •Создание шаблона документа
- •Задание
Задание
Преобразовать таблицу, показанную ниже, в список или списки Excel. На основании сводных таблиц определить:
1. Площадь предприятий, подлежащих реконструкции по районам;
2. Число предриятий, подлежащих реконструкции по видам деятельности;
3. Суммарную площадь и число предприятий по направлениям;
4. Все вышеперечисленное в одной сводной таблице.
Список предприятий, подлежащих реконструкции |
|
||
Аэропорт |
|
|
|
Бытовое обслуживание |
|
|
|
№ |
Организация |
Вид деятельности |
Площадь |
1 |
ООО Успех |
Химчистка |
120 |
2 |
ЗАО Удача |
Парикмахерская |
200 |
3 |
ООО Игрок |
Химчистка |
300 |
Торговля |
|
|
|
1 |
ООО Фиалка |
Цветы |
30 |
2 |
ЗАО Бублик |
Хлеб |
100 |
3 |
ООО Ирис |
Цветы |
20 |
Сокол |
|
|
|
Бытовое обслуживание |
|
|
|
1 |
ООО Ботинок |
Ремонт обуви |
30 |
2 |
ООО Волос |
Парикмахерская |
50 |
Торговля |
|
|
|
1 |
ООО Мороз |
Бытовая техника |
300 |
2 |
ЗАО Калач |
Хлеб |
100 |
3 |
ООО Роман |
Книги |
200 |
4 |
ЗАО Селедка |
Рыба |
100 |
Раздел 4. Решение задач оптимального управления в Excel
Excel позволяет решать задачи оптимального управления, общий вид которых представлен формулами 1-5.
где xj может принимать либо действительные, либо целочисленные, либо булевы значения.
Формула 1 определяет целевую функцию, формула 2 определяет ограничения, а формула 3 определяет граничные условия. В зависимости от вида функции f в формуле 1 задача может относиться либо к классу задач линейного или нелинейного программирования. В зависимости от того, являются ли xj целыми и булевыми или действительными величинами задача может относиться к задачам целочисленного программирования или нет.
Для решения таких задач в Excel предусмотрена надстройка «Поиск решения», которую можно вызвать из меню «Сервис». Если пункта меню «Поиск решения» нет, то нужно вернуться к установке Excel и установить эту надстройку.
Рис.1.Окно диалога надстройки «Поиск решения»
После выбора пункта меню появится диалог надстройки «Поиск решения». Подробную справку по этому диалогу можно получить в справочной системе Excel по ключевым словам «надстройка поиска решения, прерывание»(см.рис.2). Появится следующее окно справки (см. рис. 3).
Рис.2. Поиск информации в Excel по ключевым словам
Постановка задачи и оптимизация модели с помощью процедуры поиска решения
1 В меню Сервис выберите команду Поиск решения. Если команда Поиск решения отсутствует в меню Сервис, установите соответствующую надстройку. Инструкции
2 В поле Установить целевую ячейку введите адрес или имя ячейки, в которой находится формула оптимизируемой модели.
3 Чтобы максимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение максимальному значению. Чтобы минимизировать значение целевой ячейки путем изменения значений влияющих ячеек, установите переключатель в положение минимальному значению. Чтобы установить значение в целевой ячейке равным некоторому числу путем изменения значений влияющих ячеек, установите переключатель в положение значению и введите в соответствующее поле требуемое число.
4 В поле Изменяя ячейки введите имена или адреса изменяемых ячеек, разделяя их запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Допускается установка до 200 изменяемых ячеек. Чтобы автоматически найти все ячейки, влияющие на формулу модели, нажмите кнопку Предположить.
5 В поле Ограничения введите все ограничения, накладываемые на поиск решения.
6 Нажмите кнопку Выполнить.
7 Чтобы сохранить найденное решение, установите переключатель в диалоговом окне Результаты поиска решения в положение Сохранить найденное решение. Чтобы восстановить исходные данные, установите переключатель в положение Восстановить исходные значения.
Советы
· Чтобы прервать поиск решения, нажмите клавишу ESC. Microsoft Excel пересчитает лист с учетом найденных значений влияющих ячеек.
· Чтобы получить более подробные сведения об элементах управления диалогового окна Поиск решения, нажмите кнопку .
· Чтобы получить более подробные сведения об элементах управления диалогового окна Результаты поиска решения, нажмите кнопку .
· Чтобы получить более подробные сведения о методах поиска, нажмите кнопку .
Рис.3.Окно справки Excel
Задание:
Определить с помощью надстройки поиск решения |
|
|
||||
в каком количестве следует выпускать продукцию четырех типов |
||||||
для изготовления которой требуются ресурсы трех типов |
|
|||||
для получения макс прибыли |
|
|
|
|
||
Таблица коэффициентов |
|
|
|
|
||
Ресурс |
Прод1 |
Прод2 |
Прод3 |
Прод4 |
знак |
Наличие |
Прибыль |
60 |
70 |
120 |
130 |
мах |
? |
Труд |
1 |
1 |
1 |
1 |
<= |
16 |
Сырье |
6 |
5 |
4 |
3 |
<= |
110 |
Финансы |
4 |
6 |
10 |
13 |
<= |
100 |
|
|
|
|
|
|
|
Получить три сценария для значения наличия финансов 100 120 140 |
||||||
Построить отчет по сценариям |
|
|
|
|
Указания:
Представленная задача относится к классу задач линейного программирования. В этом случае целевая функция и ограничения принимают вид, показанный в формуле (6).
(6)
В нашей задаче целевой функцией является максимум прибыли, объем производства каждого вида продукции – xj, весовые коэффициенты каждого вида продукции в прибыли –cj,, удельные затраты по каждому виду ресурсов для производства каждого вида продукции - aij., наличие ресурсов –bi. Граничные условия трансформируются в требование xj>0, так как выпустить отрицательное количество продукции невозможно.
При решении данной задачи целесообразно к имеющейся таблице (см. задание) добавить четыре ячейки, в которых записать формулы для вычисления значений, показанных в формуле (6) и четыре ячейки для значений объема выпуска каждого вида продукции. Вычисление сумм удобно производить по специальной формуле Excel «СУММПРОИЗВ». После этого можно выбрать пункт меню «Поиск решения».
Для задания ограничений нужно нажать на кнопку «Добавить» в окне диалога «Поиск решения» и в появившемся диалоге «Добавления ограничений» ввести ограничения по ресурсам. Далее, нужно нажать на кнопку параметры и появившемся диалоговом окне (см. рис. 3) отметить нужные опции, обязательно выбрав пункт «Неотрицательные значения».
Рис.3. Окно диалога «Параметры поиска решения»
После чего нужно нажать на кнопку «Выполнить» и появится диалоговое окно «Результаты поиска решения» (см. рис. 4).
Рис.4. Диалоговое окно «Результаты поиска решения»
Для построения отчета по различным значениям наличия финансов необходимо сохранить сценарии решения, нажав на кнопку «Сохранить сценарий».