Справочник по работе с 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 руб. между сотрудниками отдела пропорционально их должностным окладам. Другими словами Вам требуется подобрать коэффициент пропорциональности для вычисления размера премии по окладу.

Конец ознакомительного фрагмента.

Текст предоставлен ООО «Литрес».

Прочитайте эту книгу целиком, купив полную легальную версию на Литрес.

Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.