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.  Захист роботи.


 

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

80881. Муниципальное регулирование занятости и трудовых отношений 45.99 KB
  Проблема муниципального регулирования занятости населения. Сложность муниципального регулирования вопросов занятости состоит в том что основное правовое регулирование этих вопросов относится к сфере федерального и регионального законодательства и реализуется через территориальные структуры федеральной службы занятости. Государственная политика и разграничение полномочий в сфере занятости.
80882. Муниципальная жилищная политика 45.91 KB
  Отсутствие жилья и плохие жилищные условия одна из главных причин снижения рождаемости семейных конфликтов детской беспризорности. в которой указаны основные задачи в области обеспечения доступности жилья и жилищного строительства разграничены функции федерального центра регионов и муниципалитетов в жилищной сфере. С начала перехода к рыночным отношениям основная часть государственного жилья в нашей стране была передана в муниципальную собственность включая ведомственный жилищный фонд передававшийся в процессе приватизации...
80883. Критерии и показатели эффективности муниципального управления 44.98 KB
  Поскольку генеральной целью муниципальной деятельности является повышение качества жизни населения на территории муниципального образования данный показатель в динамике мог бы выступать в качестве обобщающего критерия эффективности муниципального управления. Сложность выработки и измерения достаточно объективных показателей эффективности муниципального управления определяется:спецификой муниципального образования как сложного объекта управления имеющего иерархическую структуру;трудностями формализованного описания социальноэкономических...
80884. Муниципальное управление образованием 45.16 KB
  Муниципальная политика в сфере образования строится на основе гос. политики базирующейся на принципах: гуманистический характер образования приоритет общечеловеческих ценностей жизни и здоровья человека свободного развития личности; общедоступность образования адаптивность системы образования к уровням и особенностям развития и подготовки обучающихся воспитанников; светский характер образования в госных и муных образовательных учреждениях; свобода и плюрализм в образовании. актами определяющими задачи ОМС в области образования...
80885. Основы муниципальной молодежной политики 45.24 KB
  Цели и задачи государственной и муниципальной молодежной политики Муниципальная молодежная политика совокупность целей и мер по их реализации принимаемых ОМС в целях создания и обеспечения условий и гарантий для самореализации личности молодого человека и развития молодежных объединений движений и инициатив. Эта политика осуществляется на основе нормативных правовых актов представительных ОМС и в русле госной молодежной политики придавая ей логическую стройность системный и целостный характер и делая демократичными механизмы ее...
80886. Муниципальная экономика и модели муниципального хозяйства 43.46 KB
  Это объясняется тем что объекты муниципальной собственности могут быть бюджето-наполняющими приносящими доходы в бюджет и бюджето-поглощающими не приносящими доходов в бюджет или требующими бюджетных средств на их содержание в размере превышающем получаемый доход. Поэтому ключевая задача муниципальной экономической политики состоит в оптимизации соотношений между объемом бюджетных услуг и потребностью в имуществе и в финансовых средствах. Конкурентный рынок муниципальных услуг настолько развит что задачей муниципальной власти является...
80887. Порядок формирования и организация работы представительного органа местного самоуправления 43.11 KB
  ПО МС может осуществлять свои полномочия в случае избрания не менее двух третей от установленной численности депутатов. Заседание его не может считаться правомочным если на нем присутствует менее 50 от числа избранных депутатов. ПО поселения состоит из депутатов избираемых на муниципальных выборах. ПО муниципального района: 1 может состоять из глав поселений входящих в состав муниципального района и из депутатов ПО указанных поселений избираемых ПО поселений из своего состава в соответствии с равной независимо от численности населения...
80888. Организационная структура местной администрации 46.1 KB
  Организационная структура местной администрации. В современной муниципальной практике типичными звеньями организационной структуры местной администрации являются: глава администрации; его заместители по сферам муниципальной деятельности среди которых могут быть один или два первых заместителя; структурные подразделения различных типов которые могут находиться в подчинении главы администрации одного из его заместителей или в соподчинении между собой например отдел в составе управления; коллегиальные совещательные органы: коллегия...
80889. Статус, полномочия Главы Муниципального Образовния и основания прекращения его полномочий 41.75 KB
  выборах либо входит в состав ПО МО с правом решающего голоса и исполняет полномочия его председателя либо возглавляет местную администрацию; 3 в случае избрания ПО МО исполняет полномочия его председателя; 4 не может одновременно исполнять полномочия председателя ПО МО и полномочия главы местной администрации; Глава МО в пределах полномочий: 1 представляет МО в отношениях с ОМС других МО ОГВ гражданами и организациями без доверенности действует от имени МО; 2 подписывает и обнародует в порядке установленном уставом МО нормативные...