Практическая работа "Поиск решения. MS Excel".
методическая разработка

Применение команды «Поиск решений»

в работе с профессионально-ориентированными задачами

Постановка задачи

В ресторане установлены 7 графиков работы официантов:

Количество работающих по ним соответственно – №1, №2, №3, №4, №5, №6, №7. Опытным путём было определено требуемое  количество официантов на каждый день недели:

Необходимо распределить работающих официантов по каждому графику таким образом, чтобы затраты на еженедельную зарплату всем официантам были минимальными (найти N1, N2, N3, N4, N5, N6, N7). При этом нужно учитывать следующие факторы:

1. Ежедневно фактическое количество работающих официантов должно быть не менее ежедневной потребности.
2. Число работников, работающих по каждому графику, не может быть отрицательным.
3. Число работников, работающих по каждому графику, должно быть целочисленным.

Рассмотреть ситуацию, когда по графику 1 обязательно должно работать 3 человека.

Создание таблицы для анализа данных

Исходя из условий постановки задачи, создадим в табличном процессоре Excel таблицу, где будем потом рассматривать и анализировать ситуации.

1. Открываем программу Excel, например, щёлкнув два раза её ярлык на рабочем столе или через Пуск – Программы – Microsoft Excel.
2. Устанавливаем поля документа через Файл - Параметры страницы:
3. Сохраняем книгу (файл в Excel) через Файл – Сохранить как под именем Анализ графиков работы.xls.
4. Начиная с ячейки A1, оформляем таблицу (см. следующий лист). В те дни недели, где предусмотрен по графику рабочий день, ставим 1, а где  выходной – 0. при этом формат данных в этих  ячейках оставляем по умолчанию – Общий.
5. После заполнения таблицы данными, отформатируем ячейки так, чтобы текст и числа в них располагались по горизонтали и вертикали По центру. Для этого выделим мышкой всю таблицу и используем меню Формат – Ячейки – вкладка Выравнивание (или по правой клавише мыши).
6. Оформим  внешние и внутренние границы ячеек.
7. Получим таблицу следующего вида:

Здесь A2…G2 – количество официантов, работающих в соответствующий день недели, A1..G1 – требуемое (определённое опытным путём, меньше нельзя).

Всего работников – N.

K – дневная заработная плата одного официанта, она составляет, к примеру, 200 рублей.

8. Поскольку файл в программе Excel называется книга, а каждая книга состоит из листов, переименуем ярлычок первого листа книги, где создана таблица, и назовём его Заготовка. Переименовать можно щелчком правой клавишей по ярлычку листа, выбрать пункт Переименовать или просто два раза щёлкнуть по ярлычку листа, название Лист1 выделиться, можно вводить новое название.
9. Мышкой выделим всю таблицу и скопируем её в Буфер обмена.
10. Перейдём на Лист2 книги, щёлкнув ярлычок этого листа.
11. Вставим из буфера скопированную таблицу: щёлкнем значок  на панели Стандартная.
12. В ячейках C2:C8 удалим набранный текст. В этих ячейках будут вычисляться значения при применении команды Поиск решения.
13. В ячейки D10:J10 введём определённое опытном путём минимальное количество официантов по дням недели.

Цвет шрифта этих ячеек для лучшей наглядности сделаем синим.    

14. В ячейках D9:J9 также удалим текст. Сюда необходимо вписать формулы.
15. В ячейку C12 запишем 200, изменим формат данных в ячейке на Денежный в рублях, получим 200р.
16. Переименуем Лист2 в Ситуация 1.

Таблица для проведения анализа готова.

Ввод формул для получения решения

Чтобы в дальнейшем проанализировать ситуации, необходимо в ячейки D9:J9 ввести формулы. рассчитывающие фактическое количество официантов по дням недели и по графикам работы.

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

Ввод формулы в Excel начинается со знака =. За ним записывается функция, потом в скобках () аргументы. Некоторые функции, например, многие статистические, финансовые используют несколько аргументов. Тогда аргументы отделяются друг от друга запятыми.

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

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

Введём в ячейку D9 формулу =D2*C2+D5*C5+D6*C6+D7*C7+C8.

 Она рассчитывает сколько официантов должно работать в понедельник с учётом графиков их работы. Там, где в таблице 0, т.е. выходной, ячейки не обсчитываем.

Для подсчёта работников во вторник в ячейку E9 запишем формулу

=C2*E2+E3*C3+E6*C6+E7*C7+E8*C8.

Для ячейки F9 – =F2*C2+F3*C3+F4*C4+F7*C7+F8*C8. Среда.

Для ячейки G9 – =G2*C2+G3*C3+G4*C4+G5*C5+G8*C8. Четверг.

Для ячейки H9 – =H2*C2+H3*C3+H4*C4+H5*C5+H6*C6. Пятница.

Для ячейки I9 – =I3*C3+I4*C4+I5*C5+I6*C6+I7*C7. Суббота.

Для ячейки J9 – =J4*C4+J5*C5+J6*C6+J7*C7+J8*C8. Воскресенье.

В ячейку C11 нужно записать формулу подсчёта общего количества официантов с учётом всех семи графиков работы: =СУММ(C2:C8), т.е. просуммировать всех официантов, работающих по графикам 1…7.

В ячейку C13 введём формулу подсчёта недельной зарплаты всех официантов:

=(C11*C12).

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

Поиск решения

Команда Поиск решения из меню Сервис анализирует ситуацию с учётом ограничений, накладываемых на отдельные ячейки, рассчитывает значение целевой ячейки, изменяя значения указанных нами ячеек.

При поиски решения  целевая ячейка должна содержать формулу, но ячейки, которые будут изменяться, должны содержать значения, а не формулу.

Для решения нашей задачи выполним следующие действия:

1. Выделим ячейку C13. (Еженедельная зарплата, она должна быть минимальной.)
2. Сервис - Поиск решения.
3. Установим целевую ячейку $C$13 равной минимальному значению.

4. Укажем, что будут меняться значения ячеек $C$2:$C$8. Это можно сделать, щёлкнув по красной стрелке в правом углу окошечка, перейдя в таблицу и выделив нужные ячейки.
5. Введём ограничения на значения отдельных ячеек: в ячейках C2…C8 должно быть целое число; ячейка D9>=11, E9>=10, F9>=9, G9>=10, H9>=12, I9>=12, J9>=13. Ввод ограничений проводим через кнопку.
6. Через кнопку Параметры установим, что значения в изменяемых ячейках должны быть неотрицательными. Другие параметры в открывшемся окне Параметры поиска решения оставляем внесёнными по умолчанию.

7. Нажимаем кнопку Выполнить. Получаем решение задачи для первой ситуации, когда определяем минимальное количество официантов с учётом всех графиков их работы при условии, что ежедневно фактическое количество работающих официантов должно быть не менее ежедневной потребности. Решение представлено в Приложении.
8. В ситуации 2 официантов, работающих по графику №1, должно быть обязательно 3. Для учёта этого обстоятельства при новом поиске решения введём ещё одно ограничение: $C$2 =3.  Тогда после нажатия кнопки Выполнить получим решение для этой ситуации. Это решение представлено также в Приложении.
9. Проанализировав обе ситуации видим, что общее количество официантов одинаково (16). Это минимальное количество при заданных условиях. Соответственно подсчитана и еженедельная зарплата всех официантов. Но в ситуации 2 распределение официантов по графикам работы другое. Фактическое количество работающих по дням недели  также изменилось.

Заключение

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

Приложение

Решение задачи, найденное с помощью программы MS Excel

Ситуация 1

Ситуация 2