13. Сохраните рабочую книгу book.Xls.

Вы научились создавать сводные таблицы для получения итоговых сведений на основе содержимого базы данных.

Упражнение 9. Построение сводной диаграммы

1. Запустите программу Excel (Пуск - Программы - Мiсrosоft Excel) и откройте рабо­чую книгу book.xls, созданную ранее.

2. Откройте рабочий лист Сводная таблица поставок.

3. Щелкните на кнопке Мастер диаграмм на панели инструментов Сводная таб­лица. Сводная диаграмма строится автоматически на новом рабочем листе.

4. Переименуйте этот рабочий лист, дав ему имя Сводная диаграмма.

5. Поэкспериментируйте со сводной диаграммой, чтобы выяснить, какие ее пара­метры можно изменять.

6. Измените тип диаграммы.

Вы научились создавать сводные диаграммы на основе сводных таблиц. При прямом создании сводной диаграммы соответствующая сводная таблица создается в любом случае, выяснили, что при использовании сводной диаграммы нам доступны как операции, соответствующие сводной таблице, так и те, которые при меняют при постро­ении диаграмм. Иными словами, сводные диаграммы объединяют средства анализа и средства графического представления информации, имеющейся в базе данных.

Упражнение 10. Решение задачи оптимизации расходов предприятия

Решите задачу по подбору графика работы для работников с пятидневной рабочей неделей и двумя выходными подряд, обеспечивающий требуемый уровень обслуживания при наименьших затратах на оплату труда, методами оптимизации пакета Excel используя надстройку “Поиск решения”.

Цель - минимизация расходов на оплату труда.

Изменяемые данные - число работников в группе.

Ограничения - число работников не может быть отрицательным;

- число работников должно быть целым числом;

- число ежедневно занятых работников не должно быть меньше ежедневной потребности.

Выберете следующую модель:

1. В первой строке объединяем ячейки от А до L и пишем заголовок: “График занятости персонала”.

2. В первом столбце набираем: А6- График, А7-А, А8-Б, А9-В, А10-Г, А11-Д, А12-Е, А13-Ж; А14:А18-пропускаем; А19, В19, С19-объединяем и пишем - Дневная оплата работника; А20, В20, С20-объединяем и пишем – Общая недельная зарплата.

3. Объединяем ячейки столбцов В и С с шестой по 18-ю строки и записываем: В6 – выходные дни, В7 – вск-пн, В8 – пн-вт, В9 – вт-ср, В10 – ср-чт, В11 – чт-пт, В12 – пт-сб, В13 – сб-вск; В14 – пропускаем; В15 объединяем с А15 – Всего; В16 – пропускаем; В17 объединяем с А17 – Всего требуется; В18 – пропускаем.

4. Заполняем столбец D: D6 – Работники, D7 – 4, D8 – 4, D9 – 4, D10 – 6, D11 – 6, D12 – 4, D13 – 4, D14 – пропускаем, D15 – набираем формулу =СУММ(D7:D13), D16:D18 – пропускаем, D19 – 40, D20 набираем формулу =D15*D19.

5. Столбец Е пропускаем.

6. Заполняем столбец F ( цифра 1 означает, что день рабочий, 0- выходной): F6 – вск, F7 – 0, F8 – 1, F9 – 1, F10 – 1, F11 – 1, F12 – 1, F13 – 0; F14 – пропускаем, F15 – набираем формулу =$D$7*F7+$D$8*F8+$D$9*F9+$D$10*F10+$D$11*F11+$D$12*F12+$D$13*F13 и распространяем её на все дни недели до столбца L включительно, F16 – пропускаем, F17 – 22, F18:F20 – пропускаем.

7. Заполняем столбец G: G6 – пн, G7 – 0, G8 – 0, G9 – 1, G10 – 1, G11 – 1, G12 – 1, G13 – 1, G14 и G16 – пропускаем, G17 – 19, G18:G20 – пропускаем.

8. Аналогично заполняем столбцы: H, I, J, K, L. До 14-й строки с учётом выходных дней.

9. В 17-й строке пишем: H17-15, I17-14, J1716, K17-18, L17-23.

Рекомендации к решению:

Запустите надстройку Поиск решения (Сервис - Поиск решения). Если пункт Поиск решения в меню отсутствует, значит, необходимо подключить эту над­стройку, дав команду Сервис - Надстройки и установив соответствующий фла­ жок.

В поле Установить целевую ячейку выберите ячейку $D$20, для переключателя Равной выберите вариант минимальному значению. Изменяемые ячейки: $D$7:$D$13. Щелкните на поле Ограничения и затем - на кнопке Добавить – добавляем ограничения: $D$7:$D$13>=0, $D$7:$D$13=целое, $F$15:$L$15>=$F$17:$L$17.

Во вкладке Параметры устанавливаем флажок- Линейная модель. Нажимаем ОК и анализируем ответ

Вы научились использовать программу Excel в задачах оптимального управлении,. научились формулировать условия задачи табличным образом, формировать ограни­чения, которым должно удовлетворять решение, выполнять поиск оптимального вари­анта, также поняли, что найти нужное решение подбором крайне сложно даже для самых простых задач.