Примеры использования инструмента Поиск решения

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

Необходимо подобрать коэффициент пропорциональности, чтобы определить размер премии относительно оклада.

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

В окне Поиск решений необходимо указать параметры задачи:

Под «1» обозначена целевая ячейка, содержащая формулу критерия.

«2» — это возможные варианты решения задачи оптимизации.

«3» — изменяемых ячеек(переменных задачи). Перебирая допустимые значения этих переменных Excel находит решение.

«4» — Если понадобится задать ограничения, то стоит воспользоваться кнопкой «Добавить».

«5» — кнопка перехода к вычислениям.

Для добавления ограничений задачи используют знаки «=», «>=», «<=», а также варианты «цел» (от «целое»), «бин» («бинарное» или же «двоичное»), «раз» («все разные»), что следует из математической постановки задачи. Окно представлено на рисунке.

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

При изменении исходных данных (в данной задаче это зарплаты сотрудников, премиальный фонд) необходимо перезапустить «Поиск решений».

Пример 2. Владельцу мебельного предприятия необходимо наладить производство таким образом, чтобы получить максимально возможную прибыль. Предприятие производит книжные полки двух моделей – «А» и «В», производство которых ограничивается исключительно наличием (или отсутствием) высококачественных досок, а также машинным временем (обработка на станке).

Модель «А» требует 3 м3 досок, а модель «В» — на 1 м3 больше (то есть – 4). От своих поставщиков за неделю получают максимум 1700 м3 досок. При этом модель «А» создается за 12 минут работы станка, а «В» — за 30 минут. Всего в неделю станок может работать не более 160 часов.

Вопрос – сколько всего изделий (и какой модели), должна выпускать фирма за неделю, чтобы получить максимально возможную прибыль, если полка «А» дает 60 рублей прибыли, а «В» — 120?

Создадим таблицу с данными и формулами.

В целевой ячейке F7 содержится формула расчета прибыли. Параметр оптимизации устанавливаем на максимум. Среди изменяемых ячеек у нас значится «F3:G3». Ограничения – все обнаруженные значения должны быть целыми числами, неотрицательными, общее количество потраченного машинного времени не превышает отметку 160 (наша ячейка D9), количество сырья не превышает 1700 (ячейка D8).

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

В результате получаем:

Тесты и вопросы для закрепления материала

1. Количество ссылок для однократной сортировки в Excel

a) 64  

b) 1

c) 3

d) Нет ограничений

2. Какое имя по умолчанию имеет документ, открывающийся при запуске Microsoft Excel?

a) Книга1

b) Документ1

c) Таблица1

d) Лист1

3. Что представляет собой книга в Microsoft Excel?

a) Каталог, используемый для обработки и хранения данных

b) Файл, используемый для обработки и хранения данных

c) Программу, используемую для обработки и хранения данных

4. Изменяются или нет относительные адреса при копировании?

a) изменяются

b) не изменяются

5. Изменяются или нет абсолютные адреса при копировании?

a) изменяются

b) не изменяются

6.  Как называется ячейка, содержащая формулу в Microsoft Excel?

a) зависимой ячейкой

b) числовой ячейкой

c) постоянной ячейкой

d) влияющей ячейкой