Летние международные олимпиады для 1-11 классов Участвовать→
Конкурс разработок «Пять с плюсом» июнь 2021
Добавляйте свои материалы в библиотеку и получайте ценные подарки
Конкурс проводится с 1 июня по 30 июня

Практическая работа № 18

Раздел 3. Прикладные программные средства. Тема 3.3. Электронные таблицы. Тема занятия: Использование сценариев модели “что - если”, средств подбора параметра и поиска решения для анализа данных. Продолжительность работы 2 часа Цель: исследование информации, представленной в табл. 1 «Калькуляция» на основе формульных зависимостей с использованием средства Подбор параметра и последующим построением сценариев с помощью Диспетчера сценариев. Задачи: 1. Формировать умение создавать диаграммы разного типа в программе MS Excel. 2. Формирование и развития у обучающихся познавательных способностей; развитие интереса к предмету; развитие умения оперировать ранее полученными знаниями; развитие умения планировать свою деятельность. 3. Воспитание умения самостоятельно мыслить, ответственности за выполняемую работу, аккуратности при выполнении работы, воспитание культуры общения и поведения.
Просмотр
содержимого документа

Практическая работа № 18

 

Раздел 3. Прикладные программные средства.

Тема 3.3. Электронные таблицы.

Тема занятия: Использование сценариев модели “что - если”, средств подбора параметра и поиска решения для анализа данных.

Продолжительность работы 2 часа

Цель: исследование информации, представленной в табл. 1 «Калькуляция» на основе формульных зависимостей с использованием средства Подбор параметра и последующим построением сценариев с помощью Диспетчера сценариев.

Задачи:

  1. Формировать умение создавать диаграммы разного типа в программе MS Excel.
  2. Формирование и развития у обучающихся познавательных способностей; развитие интереса к предмету; развитие умения оперировать ранее полученными знаниями; развитие умения планировать свою деятельность.
  3. Воспитание умения самостоятельно мыслить, ответственности за выполняемую работу, аккуратности при выполнении работы, воспитание культуры общения и поведения.

 

Формирование ОК и ПК в соответствии с ФГОС:

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

ОК 08. Использовать средства физической культуры для сохранения и укрепления здоровья в

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

подготовленности.

ОК 09. Использовать информационные технологии в профессиональной деятельности.

ПК 6.1. Разрабатывать концепцию художественного образа на основании заказа.

 

После выполнения практической работы обучающийся должен:

Знать: назначение инструментов Фильтры для стилизации изображения цветоделения в программе Adobe Photoshop.

Уметь: редактирование изображений в программе Adobe Photoshop с помощью инструментов Фильтры для стилизации изображения цветоделения.

 

Внутридисциплинарные связи:

- тема 3.2 Тестовые процессоры;

- тема 3.4 Системы управления базами данных;

- тема 3.5 Компьютерные презентации.

 

Учебно-методическое оснащение занятия:

  • компьютеры на рабочих местах с системным программным обеспечением (для операционной системы Windows или операционной системы Linux);
  • мультимедийное оборудование;
  • учебная литература;
  • методические указания к практической работе №18.

 

Порядок проведения занятия:

  1. Организационный этап. (3 мин)
  2. Постанова цели и задач урока. (5 мин)
  3. Актуализация опорных знаний. (5 мин)
  4. Выполнение практической работы. (55 мин)
  5. Написание отчета по практической работе. (15 мин)
  6. Написание ответов на контрольные вопросы. (10 мин)
  7. Информация о домашнем задании. (2 мин)

 

 

Теоретическое обоснование работы:

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

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

Для проведения такого анализа «что-если» наоборот EXCEL имеет два средства: подбор параметра и поиск решения.

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

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

 

 

Практическая часть

Задание.  Подбор параметра

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

Примечание.   Средство подбора параметров поддерживает только одно входное значение переменной.

1. На Лист1 введите данные калькуляции цены книги, приведенные в табл. 1.

Таблица 1

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-dzl66w.png

Константами должны быть:

количество экземпляров;

проценты накладных расходов;

затраты на зарплату;

затраты на рекламу;

цена продукции;

себестоимость продукции.

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

Доход = Цена продукции x Количество экземпляров;

Себестоимость реализованной продукции = Себестоимость продукции x Количество экземпляров;

Валовая прибыль = Доход – Себестоимость реализованной продукции;

Накладные расходы = Доход x Проценты накладных расходов;

Валовые издержки = Накладные расходы + Затраты на зарплату + Затраты на рекламу;

Прибыль от продукции = Валовая прибыль – Валовые издержки.

Введите формулы и сверьте результаты расчета по ним с данными, приведенными в табл. 1.

 

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

 

3. Подберите такую цену книги, чтобы прибыль от продукции составила 1500 000 руб.

Для этого:

  • на вкладке Данные в группе Работа с данными выберите команду Анализ “что-если”, а затем выберите в списке пункт Подбор параметра;
  • в диалоговом окне Подбор параметра в поле Установить в ячейке с помощью мыши укажите целевую ячейку, содержащую значение прибыли от продукции ($B$11), в поле Значение укажите то значение, которое должно быть достигнуто (1 500 000) и в поле Изменяя ячейку введите абсолютную ссылку на ячейку, содержащую значение цены ($B$14);
  • нажмите кнопку ОК.

 

4. Ознакомьтесь с результатами выполнения операции подбора параметра в окне Результат подбора параметра и щелкните кнопку OK для изменения значений ячеек таблицы в соответствии с найденным решением.

 

5. Вернитесь к исходному состоянию таблицы, используя описанный в пунктах 3, 4 способ подбора параметра.

 

6. Самостоятельно определите, каков должен быть показатель процентов накладных расходов, чтобы прибыль за продукцию составила 1000 000 рублей.

 

Построение сценариев

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

7. По данным рабочего листа Лист2 постройте сценарии решения задачи расчета значения прибыли за продукцию путем изменения параметров «Цена» и «Проценты накладных расходов».

 

8. Для построения каждого сценария необходимо:

  • на вкладке Данные в группе Работа с данными выбрать команду Анализ “что-если”, а затем выбрать в списке пункт Диспетчер сценариев;
  • в диалоговом окне Диспетчер сценариев нажать кнопку Добавить;
  • в окне Добавления сценария ввести в поле Название сценария имя (например, «Изменение цены 1»);
  • в поле Изменяемые ячейки ввести абсолютную ссылку на ячейку, содержащую значение изменяемого параметра (например, цены);
  • нажать кнопку OK;
  • в окне Значения ячеек сценария ввести значение изменяемого параметра (например, для цены ввести 175);
  • нажать кнопку OK.

 

9. Повторите указанные в пункте 8 действия для добавления в список сценариев еще трех сценариев расчета прибыли, изменяя параметры «Цена» (200) и «Проценты накладных расходов» (20% и 40%);

 

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

 

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

 

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

 

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

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

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-KaDVga.png

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

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-rPVHC0.png

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

  • присвойте имена ячейкам В13-В17 в соответствии с названиями показателей в столбце А. Для этого последовательно устанавливайте курсор на каждую ячейку диапазона В13-В17, на вкладке Формулы в группе Определенные имена выбирайте команду Присвоить имя и в окне Создание имени нажимайте ОК.
  • присвойте имена ячейкам результата С11, D11, E11 – «Прибыль_2010», «Прибыль_2011», «Прибыль_2012»;
  • введите расчетные формулы для вычисления показателей в ячейках С2:Е11:

 

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

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

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

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

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

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

и т.д;

  • определите первый сценарий, выполнив команду Данные/ Работа с данными/Анализ “что-если”/Диспетчер сценариев;
  • аналогично создайте еще три сценария («Изменение показателей 1» и т. п.), щелкая в диалоговом окне Диспетчера сценариев кнопку Добавить и меняя непосредственно в окне значения процентов роста показателей в ячейках B13:B17;
  • создайте отчет по сценарию, выбрав тип отчета – структура и введя в поле Ячейки результата ссылки на диапазон ячеек C11:E11, содержащие значения чистой прибыли;
  • создайте отчет по сценарию, выбрав тип отчета – сводная таблица;
  • проанализируйте полученные результаты решения задачи.

 

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

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

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

  • Выберите команду Файл/Параметры/Надстройки, а затем в окне Управление выберите пункт Надстройки Excel.
  • Нажмите кнопку Перейти.
  • В окне Доступные надстройки установите флажок Поиск решения и нажмите кнопку ОК.

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

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

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

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

Таблица 2

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-AlGYhq.png

Изменяя значения ячеек В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;
  • ознакомьтесь с отчетом по результатам, помещенным на новом листе.

 

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

 

По окончании практической работы студент должен:

  1. Написать отчет, который должен содержать (см. Приложение 1):
  • Тема занятия.
  • Цель работы.
  • Задание и его решение.
  • Ответы на контрольные вопросы.

 

 

Критерии оценки практической работы:

«5» (отлично) – Задания 1-2 + Контрольные вопросы

«4» (хорошо) – Задание 1+ Контрольные вопросы

«3» (удовлетворительно) – 1 задание или контрольные вопросы

«1» (плохо) – работа не сделана или не сдана

 

 

Вопросы для самоконтроля:

1. Как защитить ячейки от изменений в них?

2.  В чем суть автоматического перерасчета в MS EXCEL?

3.  Что происходит во время копирования формул в MS EXCEL?

4.  Что такое диапазон ячеек?

5.  Как выделить смежные и несмежные диапазоны ячеек?

 

 

Список литературы:

 

Основной:

  1. Михеева Е.В. Практикум по информатике: учеб. пособие для сред. Проф. Образования/Е.В. Михеева. -3-е изд., стер. - М.: Издательский центр «Академия», 2014. -192 с.

 

Дополнительной:

  1. Филимонова Е.В. Информационные технологии в профессиональной деятельности: Учебник. – Ростов н/Д: Феникс, 2004. – 352с. (серия «СПО».)

 

Интернет-источники:

  1. https://drive.google.com/file/d/1RgKVzpgf-fTfuGiTML_S0M7KhVM3Le57/view

 

 

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

 

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

 

Таблица 1

https://studfile.net/html/2706/252/html_Oz5ycuooAr.gpDy/img-dzl66w.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Приложение 1

 

Практическая работа №18

 

Тема занятия: Использование сценариев модели “что - если”, средств подбора параметра и поиска решения для анализа данных.

Цель: исследование информации, представленной в табл. 1 «Калькуляция» на основе формульных зависимостей с использованием средства Подбор параметра и последующим построением сценариев с помощью Диспетчера сценариев.

 

 

Контрольные вопросы:

1……

2……

3…….

4……

5……

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Информация о публикации
Загружено: 16 марта
Просмотров: 217
Скачиваний: 6
Здобнова Екатерина Олеговна
Информатика, СУЗ, Уроки

Проверьте знания своих учеников интересными заданиями

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

Скачать материал