Справочник по работе с EXCEL. Часть 2

Справочник по работе с EXCEL. Часть 2

Николай Петрович Морозов

© Николай Петрович Морозов, 2025

ISBN 978-5-0068-6810-6 (т. 2)

ISBN 978-5-0068-6811-3

Создано в интеллектуальной издательской системе Ridero

Работа с вкладкой «Данные» в Excel: Примеры использования инструментов «Поиск решения», «Консолидация» и «Диспетчер сценариев»

Excel является мощным инструментом анализа данных, особенно благодаря инструментам, расположенным во вкладке «Данные». Рассмотрим три наиболее полезные функции этой вкладки – «Поиск решения», «Консолидация» и «Диспетчер сценариев» – на конкретных практических примерах.

1.Инструмент «Поиск решения»: Оптимизация решений

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

1.1.Пример использования инструмента «Поиск решения»

Представим, что компания производит два вида продукции: А и Б. Каждая единица продукции требует определённое количество сырья и рабочего времени, а также приносит разную прибыль. Задача состоит в максимизации прибыли компании при ограничениях на ресурсы.

| Продукция | Сырье (кг/ед.) | Время работы (часы/ед.) | Прибыль ($/ед.) |

| A | 4 | 5 | 100 |

| B | 6 | 3 | 80 |

Ограничения:

– всего доступно 24 кг сырья.

– всего доступно 20 часов рабочего времени.

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

Решение через инструмент «Поиск решения»

1. Заполняем таблицу исходных данных и вводим формулы для расчета общей прибыли и потребления ресурсов.

```

| | A | B | C | D | E |

| – -| – — – -| – — – — -| – — – — – — -| – — – — – | – — – — – — |

| 1 | | Продукт | Сырье (кг) | Время (ч) | Прибыль ($) |

| 2 | | A | 4 | 5 | 100 |

| 3 | | B | 6 | 3 | 80 |

| 4 | | | | | || 5 | | План | =B5*4+C5*6 | =B5*5+C5*3|=B5*100+C5*80|| 6 | | Кол-во |? |? | |

2. Открываем вкладку «Данные» → нажимаем «Поиск решения».

Кнопка «Поиск решения» по умолчанию не отображается в интерфейсе Excel – эту надстройку нужно активировать вручную.

1.2.Как включить «Поиск решения»

– Откройте вкладку «Файл» (в левом верхнем углу).

– Выберите «Параметры» (внизу левого меню, см. рис.1).

Рис.1.

– В окне «Параметры Excel» перейдите в раздел «Надстройки» (см. рис.2).

Рис.2.

– Внизу окна, в поле «Управление», выберите «Надстройки Excel» и нажмите «Перейти» (рис.3).

Рис.3.

– В открывшемся окне Надстройки найдите «Поиск решения», поставьте галочку рядом с ним и нажмите «ОК» (рис.4).

Рис.4.

Где теперь найти кнопку?

После активации надстройка появится на ленте Excel:

– Перейдите на вкладку «Данные».

– В правой части ленты, в группе «Анализ», вы увидите кнопку «Поиск решения».

Если надстройка не найдена

Если «Поиск решения» отсутствует в списке надстроек:

1. Нажмите «Обзор» в окне надстроек.

2. Если появится сообщение о том, что надстройка не установлена (см. рис.5), нажмите «Да» для установки.

Рис.5.

Теперь кнопка будет доступна на вкладке «Данные» в группе «Анализ» (см. рис.6).

Рис.6.

Далее:

3. Устанавливаем целевую ячейку (ячейка с формулой прибыли).

4. Выбираем режим «Максимизировать».

5. Указываем ячейки переменных (количество продукции А и Б).

6. Добавляем ограничения:

– ресурсы (сырье): `=B5*4+C5*6 <= 24`

– рабочий ресурс (время): `=B5*5+C5*3 <= 20`

7. Нажимаем «Найти решение».

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

Последовательность открытия окон с соответствующими настройками показана на рисунках 7 —11.

Рис.7.

Рис.8.

Рис.9.

Рис.10.

Рис.11.

1.3.Пример

Разберём порядок работы Поиска решения на простом примере

Предположим, что Вы начальник производственного отдела и Вам предстоит по-честному распределить премию в сумме 100 000 руб. между сотрудниками отдела пропорционально их должностным окладам. Другими словами Вам требуется подобрать коэффициент пропорциональности для вычисления размера премии по окладу.