Анализ данных в Excel

• В поле Изменяя значение ячейки укажите адрес ячейки, значение которой будет изменено для получения желаемого результата. Как мы ранее договорились, изменять мы будем количество работников, то есть значение ячейки СЗ.
• Щелкните на кнопке ОК, чтобы запустить поиск нужного значения. На экране появится диалоговое окно Результат подбора параметра с сообщением о результате поиска (рис. 5).

Рис. 5. Результат  подбора параметра.

Кроме того, что на экране появилось диалоговое окно, на рабочем  листе тут же отразился результат. У нас получилось, что требуется 12,5, то есть 13, человек. Очевидно, мы не сможем привлечь еще семь человек. После переговоров с начальством выяснилось, что нам выделят самое большее еще 3 рабочих места.

• Щелкните на кнопке Отмена, чтобы отказаться от полученного результата. Исходные данные будут восстановлены на рабочем листе.

Теперь выясним, насколько нужно увеличить рабочий день, чтобы максимальное число работников (6+3=9 человек) выполнили работу в срок.

• В свете последних событий необходимо изменить количество работников в таблице данных с 6 до 9.
• Снова выберите команду Сервис – Подбор параметра, чтобы вызвать диалоговое окно Подбор параметра (см. рис. 4) и ввести необходимые значения.
• В поле Изменяя значение ячейки укажите адрес ячейки, содержащей информацию о продолжительности рабочего дня, то есть ячейки С2.
• Щелкните на кнопке ОК, чтобы запустить поиск нужного значения. На экране появится диалоговое окно Результат подбора параметра с сообщением о результате поиска.
• Щелкните на кнопке ОК, чтобы полученный результат был сохранен.

Рис.6. Таблица  данных с результатом подбора параметра.

Как видно на рис. 6, если привлечь к  работе 9 человек, они выполнят работу в срок, только если будут работать немного дольше — 11 часов в день.

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

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

 

Таблица подстановки.

Мы научились отыскивать решение задачи методом подбора, однако возможны самые разнообразные задачи, и никто не гарантирует, что, приняв один из предложенных вариантов, вы не окажетесь всего в одном шаге от оптимального решения, но уже не узнаете об этом. Что же нужно, чтобы этого не произошло? Наверное, получить как можно больше информации для анализа и выбора. Средство, которое предоставит нам эти возможности, — Таблица подстановки.

Можно создавать два  типа таблиц подстановки: с одной  или двумя переменными. Значения переменных задаются в виде списков. При создании таблицы подстановки  список (списки) исходных значений задается в виде строки или (и) в виде столбца таблицы.

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

Сначала научимся создавать  таблицу подстановки с одной  переменной. Например, попробуем получить информацию о сроках выполнения работ, исходя из того, что участвуют в работе 9 человек, а максимальная продолжительность их рабочего дня   14 часов.

• На свободном месте рабочего листа создайте список значений подстановки для переменной — это можно сделать в отдельном столбце или строке. На рис. 7 представлен список значений подстановки, в котором длительность рабочего дня составляет от 8 до 14 часов с интервалом в 30 минут (ЕЗ:Е15). (Количество значений подстановки не ограничено.)

Рис. 7. Рабочий лист перед  созданием таблицы подстановки, с одной переменной

• Теперь нужно ввести формулу, в которую будут подставляться значения из списка. Введите ее в качестве заголовка следующего столбца справа. Как показано на рис. 7, вводимая формула должна буква в букву копировать соответствующую формулу (со ссылкой на ячейку С2) из таблицы с исходными данными, содержащуюся в ячейке С5.
• Выделите диапазон, содержащий значения подстановки и формулы, как это сделано на рис. 7.
• Выберите команду Данные – Таблица подстановки. На экране появится диалоговое окно Таблица подстановки (рис. 8).

Рис. 8. Указание ячейки подстановки.

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

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

• В поле Подставлять значения по строкам в укажите адрес ячейки в исходной таблице, содержащей данные о продолжительности рабочего дня, так как метки строк в таблице подстановки соответствуют возможным значениям продолжительности рабочей смены. В нашем примере адрес ячейки подстановки — С2.
• Щелкните на кнопке ОК, чтобы запустить процесс создания таблицы подстановки. Созданная таблица подстановки представлена на рис. 9.

Рис. 9. Таблица  подстановки с одной переменной

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

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

Снова поставим перед  собой задачу оптимизации: выполнить работу в срок, но при этом не сильно удлинять рабочий день и привлекать как можно меньше сторонней рабочей силы (сотрудников других отделов). Для решения такой задачи мы создадим таблицу подстановки, в которой автоматически будет подсчитан срок выполнения работ в зависимости от разных показателей продолжительности рабочего дня и количества персонала.

• На новом свободном месте рабочего листа создайте списки значений подстановки для двух переменных: значения одной переменной будут представлены в столбце (продолжительность рабочего дня — диапазон КЗ:К13 на рис. 10), а значения другой переменной — в строке заголовков столбцов справа (количество работников от 6 до 9 человек — диапазон L2:O2 на рис. 10).

Рис. 10. Рабочий лист перед созданием таблицы подстановки с двумя переменными.

• Формулу, в которую нужно подставлять значения столбца и строки, введите в ячейку на пересечении столбца и строки со значениями подстановки (К2 на рис. 10). Как и в случае с таблицей подстановки с одной переменной, вводимая формула должна буква в букву копировать соответствующую формулу из таблицы с исходными данными, содержащуюся в ячейке С5.
• Выделите диапазон, содержащий значения подстановки и формулу, как показано на рис. 10.
• Выберите команду Данные – Таблица подстановки. На «экране появится диалоговое окно Таблица подстановки (см. рис. 8).
• В поле Подставлять значения по столбцам в укажите адрес ячейки из таблицы данных, содержащий информацию о количестве работников (ячейка СЗ на рис. 10), так как в названиях столбцов у нас стоят значения о количестве работников.
• В поле Подставлять значения по строкам в укажите адрес ячейки в исходной таблице, содержащей данные о продолжительности рабочего дня (ячейка С2 на рис. 10), так как метки строк в таблице подстановки соответствуют возможным значениям продолжительности рабочей смены.
• Щелкните на кнопке ОК, чтобы запустить процесс создания таблицы подстановки. Созданная таблица подстановки представлена на рис. 11.

Рис. 11. Таблица  подстановки с двумя переменными.

 

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

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

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

Рассмотрим работу процедуры  поиска решения на примере. Составим план загрузки оборудования. Для этого  создадим таблицу, изображенную на рис. 12.

Рис. 12. Таблица-пример.

 

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

• на используемом оборудовании можно производить не более 35000 единиц товара;
• общие издержки не должны превышать 4,5 млн.

В качестве переменных будем  использовать значения количества единиц товаров разных видов. Нам необходимо найти такие значения общего количества единиц продукции и единиц продукции каждого вида, чтобы при издержках в 4,5 млн. предприятие выпускало не более 35000 единиц продукции.

• Запустите процедуру поиска решения. Для этого активизируйте команду Поиск решения из меню Сервис, вследствие чего откроется одноименное диалоговое окно (см. рис 13). Если эта команда отсутствует в меню, то активизируйте команду Надстройки из меню Сервис. В предложенном списке надстроек найдите Поиск решения и активизируйте опцию рядом с ним. Щелкните по кнопке ОК и данная надстройка будет установлена на ваш компьютер.

Рис. 13. Диалоговое окно Поиск решения

• В поле Установить целевую ячейку введите адрес ячейки (целевой) значение которой используется в качестве критерия оптимизации. В нашем примере таковой является ячейка D11, в которой представлены данные о суммарных издержках при текущей загрузке оборудования. Обратите внимание, что данная ячейка содержит формулу и связана с изменяемыми ячейками. Задайте значение для целевой ячейки. В нашем примере – это значение 4,5 млн. Активизируйте переключатель значению и введите в поле ввода значение 4500000.

Примечание: Остальные два переключателя используются в тех случаях, когда необходимо вычислить для целевой ячейки минимальное или максимально значение.

• И поле Изменяя ячейки укажите, в каких ячейках программа должна изменить значения для получения оптимального результата. Активизируйте указанное поле ввода и выделите на рабочем листе ячейки B4 – C9.

Примечание: При нажатии кнопки Предположить Excel 2000 выделяет диапазон ячеек на которые есть ссылка в целевой ячейке.

• Теперь необходимо указать два ограничения. Для этого выполните щелчок на кнопке Добавить в диалоговом окне Поиск решения. В результате откроется диалоговое окно Добавление ограничения (см. рис. 14).

Рис. 14. Диалоговое окно Добавление ограничения

• В поле Ссылка на ячейку этого окна введите адрес ячейки содержимое горой должно удовлетворять заданному ограничению. В поле ограничение укажите значение, выступающее в качестве ограничения или адрес ячейки с таким значением. Между этими двумя полями находится поле, в  котором  устанавливается оператор, определяющий отношения между значением ячейки и ограничением.
• Чтобы задать первое ограничение, активизируйте поле Ссылка на ячейку  диалогового окна Добавление ограничения и выделите ячейку C11, а в поле Добавление ограничение введите значение 35000. Выберите оператор <=, поскольку количество производимых единиц товара не должно превышать 35000. В завершение установки ограничения выполните щелчок на  кнопке Добавить. Диалоговое окно Добавление ограничения останется открытым, и вы приступите к определению второго ограничения, которое заключ<span class="dash041e_0431_044b_