17173

Ms Excel. Інструменти Пошук розв’язку. Таблиця підстановки

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

Информатика, кибернетика и программирование

Практична робота №18 Тема: Ms Excel. Інструменти Пошук розвязку. Таблиця підстановки. Мета: Вміти користуватися інструментами Пошук розв'язку і Таблиця підстановки для аналізу підприємницької діяльності і прийняття рішень. Обладнання: ПЕОМ. Табличний процесор MS Excel....

Украинкский

2013-06-29

165.5 KB

39 чел.

Практична робота №18

Тема: Ms Excel. Інструменти Пошук розв’язку. Таблиця підстановки.

Мета: Вміти користуватися інструментами Пошук розв'язку і Таблиця підстановки для аналізу підприємницької діяльності і прийняття рішень.

Обладнання: ПЕОМ. Табличний процесор MS Excel.

Хід виконання

  1.  Правила ТБ
  2.  Теоретичні відомості.
  3.  Індивідуальне завдання

Задача 1. Планування випуску продукції

Для виготовлення виробів х, у, z використовують три види сировини: I, II, III. У таблиці задано норми витрат сировини на один виріб кожного виду, ціна одного виробу, а також кількості сировини кожного виду, яку можна використати. Скільки виробів кожного виду потрібно виготовити, щоб прибуток був максимальний (п — номер варіанта)?

x

y

z

Загальна к-сть сировини

І

18

15

12

360 - п

II

6

4

8

192

III

5

3

3

180 + п

Ціна

9

10

16

Задача 2. Аналіз кредиту

Підприємець бере кредит на деяку суму під місячну ставку 6% і, зважаючи на свою щомісячну платоспроможність, повинен прийняти рішення, на скільки місяців його брати. Побудувати таблицю щомісячних виплат для різних термінів кредиту, наприклад, 4, 5, 6, 7 місяців і реальних сум, що повинні бути виплачені за кредит протягом усього терміну. Побудувати двовимірну таблицю щомісячних виплат з урахуванням двох параметрів: можливих сум позики і термінів позики.

1. Планування випуску продукції. Задача 1 є задачею лінійного програмування. Вона розв'язується за допомогою інструмента Пошук розв'язку (Solver).

Математична модель задачі. Позначимо через х, у, z шукані кількості виробів трьох видів. Потрібно визначити х, у, z  для яких досягається максимум функції прибутку f=9х+10у+16z за таких обмежень:

18х +15у + 12z<= 360 - n 

6х + 4у + 8z <= 192

5х +3у + 3z <= 180 + n 

х, у, z >= 0; x,y,zцілі.

Розв'язування. Потрібно виконати такий алгоритм:

1) клітинкам А1, B1, С1 присвоїти імена х, у, z командами Вставити > Ім'я > Присвоїти > Ввести  х = > ОК і т.д.;

2) у клітинку D1 ввести формулу =9*x+10*y+16*z;

3) запустити програму Пошук розв'язку з меню Сервіс;

4) задати адресу цільової клітинки D1 і зазначити дію досягнення максимуму функції (рис. 1);

5) задати клітинки, де має міститися розв'язок: х; у; z;

6) за допомогою кнопки Додати обмеження (рис. 2) у вигляді шести нерівностей (значення п підставити конкретне):

 х <= (360 - n - 15*y - 12*z)/18

 у <= (192 - 6*х - 8*z)/4

 z <= (180 + n - 5*х - 3*y)/3

 х >= 0; у >= 0; z >= 0

 х — ціле; у — ціле; z — ціле;

Рис. 1. Вікно програми Пошук розв'язку для задачі 8.

7) натиснути на кнопку Параметри і задати, що модель лінійна;

8) отримати розв'язок, натиснувши на кнопку Виконати. Для п = 0 відповідь (у клітинках Al, B1, C1, D1) така: х = 0, у = 8, z = 20, f = 400.

Рис. 2. Вікно для введення обмежень.

2. Аналіз кредиту таблицями підстановки. За допомогою таблиць підстановки можна оцінити вплив одного чи декількох параметрів на деяку величину чи декілька величин з метою прийняття рішень.

На прикладі розв'язування задачі 2 розглянемо вплив зміни одного параметра (кількості періодів позики) на дві величини: щомісячну і сумарну виплати за кредит.

Основною формулою розв'язування задачі є =ППЛАТ(ПС; КП; сума кредиту). Виконайте такий алгоритм (рис. 3).

1. У діапазон А1:АЗ введіть вхідні дані: ставку (6%), кількість періодів (4) і суму кредиту, наприклад 3000.

2. У діапазон А5:А8 введіть можливі терміни позики: 4, 5, 6, 7.

3. У клітинку В5 введіть формулу =ППЛАТ(А1; А2; A3). У клітинку С5 введіть формулу =В5*А2. Ці формули мають бути першими у своїх стовпцях.

4. Виокремте діапазон А5:С8 і застосуйте команду Дані > Таблиця підстановки (Table...). Параметром у цій задачі є кількість періодів з клітинки А2. Тому в отриманому діалоговому вікні у друге поле Підставляти значення по рядках введіть А2. Натисніть на кнопку ОК. Отримаєте таблицю, придатну для прийняття рішень. Який термін позики вам найбільше підходить?

Для аналізу щомісячних виплат, залежних від двох параметрів (можливих сум і термінів позики), таблицю будують так: у клітинку D1 вводять формулу =ППЛАТ(А1; А2; A3). Клітинки праворуч Е1:Н1 заповнюють деякими можливими сумами позики: 2000, 2500, 3000, 3500, а клітинки знизу (D2:D5) - можливими термінами 4, 5, 6, 7 місяців. Вибирають прямокутний діапазон D1:H5 і виконують команду Дані > Таблиця підстановки. В отриманому діалоговому вікні у перше поле вводять A3, а в друге — А2. Отримаємо таблицю, аналіз якої дає змогу вибрати суму і термін позики, враховуючи щомісячну платоспроможність підприємця.

Рис. 3. Зразок розв'язування задачі 2.

Хід роботи

1. Розв'яжіть задачу 1.

Переконайтесь, що засіб Solver доступний. Виконайте вказівки, описані в теоретичних відомостях. Який максимальний прибуток буде отримано?

2. Розв'яжіть задачу 2.

Виконайте вказівки, описані в теоретичних відомостях, а також додатково обчисліть, скільки всього грошей має повернути підприємець банку у випадку позики 3500 грн. на термін 6 або 7 місяців, а також у випадку позики 4000 грн. на 4 місяці.

3.   Розв'яжіть задачу 2 методом табулювання функції двох змінних ППЛАТ($А$1; термін; сума позики).

Для цього очистіть діапазон Е2:Н5, у клітинку Е2 введіть формулу =ППЛАТ($А$1; $D2; Е$1) і скопіюйте її у діапазон Е2:Н5. Порівняйте отримані результати з п.2.

  1.  Контрольні запитання:

  1.  Приклади використання матричних функцій?
  2.  Функції категорії : Дата и время.
  3.  Що таке засіб «Пошук розв'язку» (Solver)?
  4.  З яких частин складаються виплати за кредит?
  5.  Яке призначення функції БЗ (FV)?
  6.  Що таке зведена таблиця?

  1.  Оформлення звіту.

  1.  Захист роботи.


 

А также другие работы, которые могут Вас заинтересовать

43881. Организация управленческого учета на примере ООО «Фабрика мебели «Роникон» 840 KB
  Теоретические основы управленческого учета Сущность субъект и объект цели и задачи методы и принципы управленческого учета Пути совершенствования системы управленческого учета на ООО Фабрика мебели Роникон
43882. Визначення та наукове обгрунтування психолого-педагогічних умов подолання особистісної тривожності у дітей молодшого шкільного віку та пятикласників 959.5 KB
  Підходи науковців до реалізації наступності зорієнтовані передусім на інтеграцію двох ланок освіти, усунення суперечок між запитами школи і амбіційним завищеними вимогами окремих батьків щодо підготовки їхніх дітей; між непідготовленістю окремих учнів, які не відвідували дошкільних установ, і необхідністю враховувати специфіку дошкільної освіти.
43883. Экономика и управление на предприятии АПК». Методические указания 605 KB
  В методических указаниях рассматриваются вопросы подготовки написания процедуры защиты дипломных работ раскрыты требования по оформлению работы. ПОДГОТОВКА КВАЛИФИКАЦИОННОЙ РАБОТЫ Выбор темы дипломной работы Назначение руководителя дипломной работы и выдача дипломного задания.
43884. Створення когнітивно-семантичного підґрунтя вибору варіантів перекладу одиниць на позначення концепту “Кількість” 249 KB
  Кількість як узагальнений когнітивний зміст великий фрагмент кодованої засобами мови картини світу того чи іншого етносу. На першому етапі необхідно чітко визначити поняття “картина світу†“мовна картина світу†“концепт†для чого слід виконати критичний аналіз наукової літератури. Новизна одержаних результатів визначається тим що в ньому виявлено спільні і відмінні ознаки репрезентації концепту â€œКількість†в англійській та українській мовних картинах світу встановлені абсолютні і варіантні еквіваленти перекладу в...
43885. Проектування поліграфічного підприємства 3.18 MB
  Вибір напруги живлячої мережі. Вибір напруги розподільчої мережі. Характеристика джерела живлення Підприємство можна заживити від районних підстанцій що мають три рівні напруги.
43886. Изучение и систематизация теоретических аспектов организации финансов на ООО "Компания ОГАТ" 599 KB
  Финансы предприятий будучи частью общей системы финансовых отношений отражают процесс образования распределения и использования доходов на предприятиях различных отраслей народного хозяйства и тесно связаны с предпринимательством поскольку предприятие является формой предпринимательской деятельности. Целью данной дипломной работы является систематизация теоретических аспектов организации финансов на предприятиях различных форм собственности изучение аналитических сведений практической деятельности финансового состояния конкретного...
43887. Оценка уровня организации и планирования работы транспортного хозяйства на предприятии ТЧУП «Передовые технологии» на основе АВС-XYZ анализа 878 KB
  Структура и динамика грузов перевозимых предприятием и их характеристика Классификация грузов и грузовых перевозок предприятия на основе анализа АВС и XYZ Выбор рациональной структуры перевозимых грузов и видов грузовых перевозок на основе правила 80 20 и матрицы АВСXYZ анализа В третьей главе мы рассмотрим основные направления совершенствования транспортной деятельности предприятия за счет выбора рациональной структуры перевозимых грузов и видов грузовых перевозок на основе правила 80 20 и матрицы АВСXYZ анализа.
43888. Исследование взаимосвязей временных рядов курсов акций с помощью копула-функции и метода коинтеграции 5.8 MB
  Фондовый рынок - это составная часть финансового рынка на котором обращаются ценные бумаги. Для управления ценными бумагами важно знать их взаимосвязь между собой. В теоретической части подробно описываются сущность и содержание рынка ценных бумаг пакета акций методы определения взаимосвязи между временными рядами метод копулафункций и метод коинтеграции. Рынок ценных бумаг.
43889. РАЗРАБОТКА ПРОЕКТА АВТОМАТИЗАЦИИ КАДРОВОГО МЕНЕДЖМЕНТА НА ПРИМЕРЕ SRL 1.26 MB
  Основываясь на данных исследования проведенного агентством MR Reserch среди сотни быстро растущих компаний США можно утверждать что успешные и развивающиеся компании в текущем 2012 году не только не сократят а напротив увеличат свои расходы. Эксперты оценивают необходимость наличия данных качеств по семибалльной шкале которая количественно определяет не только степень необходимости но и недопустимости того или иного...