Excel Задание 7. Лабораторная работа 7 Тема Анализ данных в Microsoft Excel. Метод подбора параметра Цель работы п

• Название: Лабораторная работа 7 Тема Анализ данных в Microsoft Excel. Метод подбора параметра Цель работы п
• Дата: 17.01.2022
• Размер: 115.32 Kb.
• Имя файла: Excel Задание 7.docx
• Тип: Лабораторная работа; #334090

MS EXCEL. ЛАБОРАТОРНАЯ РАБОТА № 7

Тема: Анализ данных в Microsoft Excel. Метод подбора параметра

Цель работы – получение навыков использования инструмента «Подбор параметра».

Подготовка к работе

Создайте файл с именем Лаб.7_ххх (№) на основе пустого файла-заготовки (в соответствии со своим номером по списку). Все дальнейшие действия выполняйте в этом файле.

Ознакомьтесь с теоретическим материалом для выполнения данной лабораторной работы, представленным в файле Excel Практика 7.

1. 
   Расчет прибыли от продаж
   

Порядок выполнения

1. 
   
   
   
   Рис. 1
   Откройте личный файл Microsoft Excel. На Листе 1 создайте таблицу, содержащую данные калькуляции цены книги, приведенные на Error: Reference source not found.
   

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

• 
  количество экземпляров;
  
• 
  проценты накладных расходов;
  
• 
  затраты на зарплату;
  
• 
  затраты на рекламу;
  
• 
  цена продукции;
  
• 
  себестоимость продукции
  

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

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

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

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

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

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

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

Введите формулы и сверьте результаты расчета по ним с данными, приведенными в таблице на Error: Reference source not found.

2. 
   Отформатируйте данные в соответствии с образцом.
   
3. 
   Переименуйте Лист1 в Калькуляция. Исследуйте информацию, представленную в таблице, проанализируйте, за счет чего может быть достигнуто увеличение прибыли.
   
4. 
   Скопируйте содержимое столбца B в три соседних столбца (C, D и E). Пронумеруйте их, задав номера от 1 до 4 в ячейках В1, С1, D1 и E1 соответственно.
   
5. 
   В столбце 1 подберите такую цену книги, чтобы прибыль от продукции составила 1 500 000 р. Для этого:
   

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

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

1. 
   в столбце 2 – определите, каким должен быть показатель процентов накладных расходов, чтобы прибыль составила 1 000 000 р.
   
2. 
   в столбце 3 – рассчитайте, каким должен быть тираж (количество экземпляров книги) для получения прибыли в размере 2 000 000 р.
   
3. 
   в столбце 4 – определите точку безубыточности, т.е. вычислите количество книг, при котором прибыль будет равна нулю.
   

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

8. 
   Выделите цветом ячейки, участвующие в подборе параметра, в каждом из четырех случаев.
   

Сохраните рабочую книгу Excel.

2. 
   Нахождение корней уравнения
   

Нахождение корней уравнения в среде Microsoft Excel выполняется в два этапа:

1. 
   приближенное определение корней графическим методом;
   
2. 
   получение точных значений с помощью инструмента Подбор параметра.
   

Задача: Найти корни полинома третьей степени x³–x²–x+0,5=0.

Решение

1. 
   В той же рабочей книге подготовьте новый рабочий лист (назовите его Корни) для построения графика функции y= x³–x²–x+0,5=0 на промежутке [х_нач, х_кон] для 10 точек (вариант оформления листа представлен на Рис. 2):
   

1. 
   в ячейках В2:В3 – промежуток построения графика, например, [-1, 2] (подбирается опытным путем);
   
2. 
   в ячейке В4 – количество точек для построения графика, например, 10 (также подбирается опытным путем);
   
3. 
   шаг вычисляется по формуле, зависящей от промежутка построения и количества точек:
   

=(В3–В2)/(В4–1)

4. 
   в ячейке E3 – ссылка на начальное значение х:
   

=В2

5. 
   в ячейку E4 ввести формулу вычисления следующего значения х и размножить (скопировать) в ячейки диапазона E5:E12:
   

=Е3+$В$5

6. 
   в ячейку F3 ввести формулу вычисления значения y и размножить (скопировать) в ячейки диапазона F4:F12:
   

=Е3^3-Е3^2–E3+0,5



Рис. 2. Вариант построения листа

2. 
   По данным диапазона E3:F12 построить график функции в виде точечной диаграммы (Рис. 3).
   

Рис. 3. График функции y= x³–x²–x+0,5 на промежутке [-1,2]

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

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

3. 
   Добавить на лист таблицу нахождения точных значений корней уравнения (Рис. 4):
   

• 
  в ячейки В10:В12 занести приближенные значения х, при которых график пересекает ось Ох, т.е. y=0; на графике (Рис. 3) видно, что это могут быть значения: –1; 0,5 и 1,5;
  
• 
  в ячейку С10 ввести формулу вычисления значения y и размножить (скопировать) в ячейки диапазона С11:С12:
  

=В10^3-В10^2-В10+0,5



Рис. 4. Поиск корней уравнения

4. 
   Для поиска более точного значения х₁применить инструмент Подбор параметра, для чего в диалоговом окне Подбор параметра заполнить соответствующие поля:
   

Установить в ячейке: $С$10

Значение: 0

Изменяя значение ячейки: $B$10

5. 
   Аналогично определить значение двух других корней. Сохраните рабочую книгу.
   

Ответы: х₁≈ –0,85485; х₂ ≈ 0,40304; х₃≈ 1,45170.

3. 
   Задача оптимизации с одним неизвестным
   

С помощью подбора параметров можно решать задачи оптимального распределения ресурсов следующего типа.

Задача: Требуется закупить составляющие (конфета карамельная, конфета шоколадная, упаковка печенья и мармелада) для комплектования подарочных наборов так, чтобы цена набора не превышала 150 р. При этом известны соотношения цен относительно одной из компонент (здесь карамели): цена шоколадной конфеты в 2,5 раза выше цены карамели, цена печенья на 10 р. больше карамели и цена мармелада в 8,5 раз выше цены карамели. В наборе должно быть 5-10 конфет карамель, 6-8 шоколадных конфет, 1-2 упаковки печенья и 1 упаковка мармелада. Рассчитать закупочные цены для разных комплектов.
Решение

1. 
   В той же рабочей книге на новом листе создайте таблицу, содержащую данные о комплектации подарочного набора в максимальном комплекте (вариант оформления таблицы представлен на Рис. 5. Назовите рабочий лист Подарочные наборы.
   

Рис. 5

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

• 
  в ячейке В6 =В5*2,5;
  
• 
  в ячейке В7 =В5+10;
  
• 
  в ячейке В8 =В5*8,5;
  
• 
  в ячейках D5:В8 произведение цены на количество =B5*C5 и т.д.;
  
• 
  в ячейке D9 общая сумма =СУММ(D5:D8).
  

3. 
   Пользуясь инструментом Подбор параметра, подберите такую цену конфеты карамель (базовой компоненты), при которой стоимость набора будет равна 150 р. Результат подбора представлен на Рис. 6.
   

Рис. 6

4. 
   На том же рабочем листе создайте копию полученной таблицы, в столбце Кол-во в наборе (шт.) замените максимальные количества составляющих на минимальные (см. условие задачи) и озаглавьте таблицу Минимальный комплект. Рассчитайте закупочные цены для минимального комплекта, выполнив Подбор параметра в новой таблице. Сохраните рабочую книгу.
   
1. 
   Задача распределения премии
   

Порядок работы

1. 
   Скопируйте в текущую рабочую книгу лист Сотрудники из рабочей книги «Сотрудники». Добавьте к таблице столбец Премия к 8 марта, заполните его по правилам: премия назначается только женщинам: если стаж больше 10 лет – в размере 20% оклада, иначе 15%. Значения процентов премий записать в отдельные ячейки и в формуле использовать ссылки на эти ячейки.
   
2. 
   Вычислите сумму столбца Премия к 8 марта.
   
3. 
   Используя метод Подбора параметра, найдите значение процента премии для сотрудниц со стажем, не превышающим 10 лет, при котором премиальный фонд (сумма столбца) будет равняться 150 000 р. Отобразите найденное значение с точностью до одного знака после запятой.
   

Сохраните рабочую книгу. Предъявите результат преподавателю.

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

1. 
   Какие задачи можно решить с помощью инструмента Подбор параметра?
   
2. 
   Как работает средство Подбор параметра?
   
3. 
   Каким образом должен быть подготовлен лист электронных таблиц перед применением средства Подбор параметра?
   
4. 
   Как вызвать инструмент Подбор параметра?
   
5. 
   Какая ячейка называется целевой?
   
6. 
   Может ли ячейка, влияющая на целевую, содержать формулу?
   
7. 
   Сколько решений находит команда Подбор параметра?