10. Для просмотра сценариев в окне Диспетчер сценариев поочередно выбирайте сценарии из списка и щелкайте по кнопке Вывести. Excel заменит содержимое ячеек листа значениями из сценария и отобразит результаты на листе.

11. Для создания отчета по сценарию в диалоговом окне Диспетчер сценариев нажмите кнопку Отчет.

12. В окне Отчет по сценарию выберите тип отчета Структура, установите абсолютную ссылку на ячейку со значением результата (Прибыль за продукцию) и нажмите ОК.

13. Перейдите на новый рабочий лист и введите таблицу с упрощенным бюджетом предприятия на 2015 год и выполните прогнозирование бюджета на 2016, 2017 и 2018 годы, манипулируя темпами роста различных показателей. Подготовьте 4 сценария с различными прогнозами роста и создайте итоговый сравнительный отчет.

Бюджет предприятия на 2015 г. приведен в таблице:

Прогнозируемый ежегодный рост отдельных показателей приведен в таблице:

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

• присвойте имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливайте курсор на каждую ячейку диапазона В13-В17, на вкладке Формулы в группе Определенные имена выбирайте команду Присвоить имя и в окне Создание имени нажимайте ОК.

• присвойте имена ячейкам результата С11, D11, E11 – «Прибыль_2016», «Прибыль_2017», «Прибыль_2018»;

• введите расчетные формулы для вычисления показателей в ячейках С2:Е11:

Общая прибыль= Объем продаж * Размер прибыли в %

Расход=Аренда + Услуги + Выплаты

Чистая прибыль=Общая прибыль-Расход

Показатели в столбцах C,D,E вычисляются по схеме:

Объем продаж 2016 г = Объем продаж 2015 г *(1+% роста объема продаж)

Размер прибыли 2016 г = Размер прибыли 2015 г *(1+% роста размера прибыли)

и т.д;

• определите первый сценарий «Прогноз роста 1», выполнив команду Данные/ Работа с данными/Анализ “что-если”/ Диспетчер сценариев и введя в поле Изменяемые ячейки абсолютную ссылку на ячейки B13:B17;

• измените в окне Значения ячеек сценария проценты роста показателей в ячейках B13:B17;

• аналогично создайте еще три сценария («Прогноз роста 2» и т. п.), щелкая в диалоговом окне Диспетчера сценариев кнопку Добавить и меняя непосредственно в окне Значения ячеек сценария проценты роста показателей в ячейках B13:B17;

• создайте отчет по сценарию, выбрав тип отчета – структура и введя в поле Ячейки результата ссылки на диапазон ячеек C11:E11, содержащие значения чистой прибыли;

• создайте отчет по сценарию, выбрав тип отчета – сводная таблица;

• проанализируйте полученные результаты решения задачи.

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

Основывается на методе линейной оптимизации и используется для решения задач со многими неизвестными и ограничениями.

Средство поиска решения является надстройкой² Microsoft Office Excel, которая доступна при установке Microsoft Office или Microsoft Excel. Чтобы использовать эту надстройку в Excel, необходимо сначала загрузить ее. Для этого:

• Выполните Файл/Параметры.

• Выберите команду Надстройки, а затем в окне Управление выберите пункт Надстройки Excel и нажмите кнопку Перейти.

• В окне Доступные надстройки установите флажки Пакет анализа (пригодится в дальнейшем) и Поиск решения. Нажмите ОК.

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

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

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

В табл. 2 приведены данные для вычисления прибыли от продажи трех видов продукции.

Таблица 2

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

• общий объем производства – всего 300 изделий в день;

• должно быть произведено не менее 50 изделий А;

• должно быть произведено не менее 40 изделий В;

• должно быть произведено не более 40 изделий С.

14. Введите на новый рабочий лист данные табл. 2 для вычисления прибыли от продажи трех видов продукции, причем в ячейки столбца D, и в ячейку B6 должны быть введены формулы.

15. Запустите задачу поиска решений. Для этого на вкладке Данные в группе Анализ нажмите кнопку Поиск решения и в диалоговом окне Поиск решения введите данные:

• в поле Оптимизировать целевую функцию укажите адрес $D$6, щелкнув мышью по соответствующей ячейке;

• в поле До установите переключатель Максимум;

• в поле Изменяя ячейки переменных определите изменяемые ячейки ($B$3:$B$5);

• в поле В соответствии с ограничениями по одному добавьте каждое из следующих четырех ограничений задачи ($B$6=300; $B$3>=50; $B$4>=40; $B$5<=40), для чего:

• щелкните кнопку Добавить и в появившемся окне Добавление ограничения введите ссылку на ячейку $B$6 (щелкая по ней мышью), оператор ограничения (=) и значение (300);

• для добавления следующего ограничения щелкните кнопку Добавить и повторите процедуру добавления ограничения;

• после ввода последнего ограничения щелкните кнопку ОК;

• в диалоговом окне Поиск решения щелкните кнопку Найти решение;

• в диалоговом окне Результаты поиска решения установите переключатель Сохранить найденное решение, в окне Отчеты выберите Результаты и нажмите кнопку OK;

• ознакомьтесь с отчетом по результатам, помещенным на новом листе.

16. С помощью средства Поиск решения решите следующую задачу минимизации расходов на перевозку:

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

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

Исходные таблицы с данными для решения поставленной задачи представлены на рис.1.

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

Для осуществления поиска решения введите в новый рабочий лист все таблицы, представленные на рис. 1, причем в выделенные ячейки введите следующие формулы:

• в ячейку F12 введите формулу автосуммирования для вычисления суммы товаров, перевезенных в магазин 1 со всех трех складов:

=СУММ(С12:Е12);

• размножьте формулу на диапазон ячеек F13:F17;

• в ячейку В18 введите формулу для подсчета суммарной потребности в товаре:

=СУММ(В12:В17);

• размножьте формулу на диапазон ячеек С18:F18 для вычисления сумм товаров, перевезенных с каждого из трех складов в шесть магазинов и суммарного количества перевезенного товара;

• в ячейку С21 введите формулу для вычисления запасов товаров на складе 1 после отпуска товаров:

= С20-С18;

• размножьте формулу на диапазон ячеек D21:E21;

• в ячейку С23 введите формулу для вычисления суммы произведений стоимостей перевозок на количество перевозок во все магазины по каждому складу:

=СУММПРОИЗВ(С3:С8;С12:С17);

• размножьте формулу на диапазон ячеек D23:E23;

• в ячейку F23 введите формулу для подсчета общей стоимости перевозок для всех заказов:

=СУММ(С23:Е23)

Рис. 1. Исходные данные для решения транспортной задачи

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

• количества необходимого товара для каждого розничного магазина, представленные в ячейках с B12 по B17, должны быть равны количествам перевезенного товара (ячейки с F12 по F17 соответственно), т.е. все заказы должны быть выполнены;

• значения в изменяемых ячейках (диапазон С12:Е17), соответствующие количествам перевезенных товаров с каждого из трех складов в каждый из шести магазинов, не могут быть отрицательными;

• количество запасов после отпуска на каждом складе (диапазон С21:Е21) не должно быть отрицательным.

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

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

Для изготовления пластмассовых втулок и шестеренок требуется стеклоткань, эпоксидная смола и отвердитель. На изготовление одной втулки затрачивается 4 ед. стеклоткани, 3 ед. - эпоксидной смолы и 2 ед. – отвердителя, а на изготовление одной шестеренки – соответственно 3, 4 и 6 ед. материалов. Прибыль предприятия от изготовления одной втулки составляет 20 руб., а шестеренки – 40 руб. Сколько втулок и шестеренок должно изготовить предприятие для получения наибольшей прибыли, если в его распоряжении имеется 480 ед. стеклоткани, 444 ед. эпоксидной смолы и 546 ед. отвердителя.

18. Сохраните результаты лабораторной работы в файле с именем lab4.xlsm.

ЛАБОРАТОРНАЯ РАБОТА № 5

Создание, редактирование и использование шаблонов

Цель лабораторной работы

Лабораторная работа служит для получения практических навыков по созданию и использованию нового шаблона рабочей книги.

Основные сведения о шаблонах

Шаблон – это особый документ, используемый для создания других документов по своему образцу. Шаблоны избавляют от большей части работы по расстановке элементов и оформлению, что позволяет полностью сконцентрироваться на данных. При от-крытии Excel 2013 отображаются шаблоны для планирования бюджета, создания календарей, форм и отчетов, а также многих других задач. Отличиями шаблона от обычной рабочей книги являются:

1. Расширение xltx в отличие от расширения обыкновенных книг xlsx.

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

3. Иконка (значок кнопки) шаблона имеет сверху желтую полоску в отличие от иконки обычной книги Excel:

Содержание лабораторной работы

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

Выполнение лабораторной работы

1. Откройте новую рабочую MS Excel 2013.

2. Используя имеющиеся в Excel средства форматирования, подготовьте рабочий лист в качестве шаблона для факса:

• перед вводом данных уменьшите ширину столбцов A, C, E, G.

• введите данные на основании факса, приведенного на рис.1. Для назначения шрифтов и линий используйте кнопки группы Шрифт на вкладке Главная;

• в ячейку H10 введите формулу для вывода текущей даты =ТДАТА() (см. рис. 1).

Рис 1. Шаблон для факса

3. Создайте новые стили и примените их при вводе данных.

Стиль ячейки – это определенный набор параметров форматирования, таких как шрифты и размеры шрифтов, форматы чисел, границы и заливка ячеек.

Создайте новый стиль и примените его для ввода данных «Кому» и «Фирма». Для этого:

• выполните команду Главная/Стили/Стили ячеек/Соз­дать стиль ячейки;

• в диалоговом окне Стиль в поле Имя стиля введите Мой стиль 1 и нажмите кнопку Формат;

• в окне Формат ячеек на вкладке Шрифт выберите Шрифт – Calibri (Основной текст), Начертание – курсив, Размер – 12, Цвет – синий;

• два раза нажмите ОК;

• выделите строки 5, 7, 8;

• выполните команду Главная/Стили/Стили ячеек и выберите пользовательский формат «Стиль 1».

4. Самостоятельно создайте еще один стиль (выберите новый шрифт, измените начертание и размер, выберите цвет, вид рамки и заливки) и примените его к ячейкам с 17 по 27 строку.

5. Сохраните подготовленный рабочий лист в качестве шаблона. Для этого:

• выполните команду Файл/Со­хранить как…;

• в диалоговом окне Сохранение документа выберите тип файла Шаблон Excel, введите имя файла «FAX» и сохраните шаблон в папке C:\Users\<Имя пользователя>\Documents. При этом Excel создаст в папке Мои документы папку Пользовательские шаблоны Office и сохраните там шаблон FAX. xltx.

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