17173

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

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

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

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

Украинкский

2013-06-29

165.5 KB

40 чел.

Практична робота №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.  Захист роботи.


 

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

78671. Прогрессивные формы организации инновационной деятельности :бизнес-инкубаторы, технопарки, технополисы 31 KB
  Инновационная деятельность это процесс направленный на реализацию результатов законченных научных исследований и разработку иных научно технических достижений интеллектуального продукта. Отличительные черты: комплексность по научнопроизводственному циклу научные учреждения вузы промышленные предприятия компактность расположения ограниченность площади расположение в экологически чистых районах. Технополис научнотехнический комплекс соединяющий научнотехническую деятельность с наукоемким производством с хорошо развитой...
78672. Система індукційного нагріву для нагрівання металів до температури оптимальної для пластичної деформації 1.51 MB
  В даному дипломному проекті розроблена система індукційного нагріву, яка призначений для нагрівання металів до температури оптимальної для пластичної деформації. Було визначено питому потужність системи для необхідного розміру деталі та оптимального часу нагрівання. Розглянуто аналоги та їх основні параметри. Розроблена структурна та принципова схеми системи.
78673. Предпринимательство как вид экономической деятельности. Виды предпринимательства 35 KB
  Виды предпринимательства. Рыночная экономика экономика свободного предпринимательства. В зависимости от содержания и направленности предпринимательской деятельности объекта приложения капитала и получения конкретных результатов связи предпринимательской деятельности с основными стадиями воспроизводственного процесса различают следующие виды предпринимательства. Коммерческое торговое предпринимательство Принцип организации торгового предпринимательства несколько отличается от производственного так как предприниматель выступает...
78674. Инфраструктурное обеспечение предпринимательской деятельности 25 KB
  система общих условий воспроизводства предпринимательского типа представляющая собой совокупность техникотехнологических организационноэкономических и социальных взаимосвязей тех элементов инфраструктуры которые обеспечивают обслуживание процесса предпринимательства на уровне макро мезо и микроэкономики. являются научность и системность в формировании и развитии предпринимательства и его инфраструктуры а также постепенность и многообразие моделей инфраструктурного обеспечения предпринимательства. Прежде всего нужна трансформация...
78675. Виды и формы предпринимательской деятельности 39 KB
  Рыночная экономика экономика свободного предпринимательства. В зависимости от содержания и направленности предпринимательской деятельности объекта приложения капитала и получения конкретных результатов связи предпринимательской деятельности с основными стадиями воспроизводственного процесса различают следующие виды предпринимательства: 1. Коммерческое торговое предпринимательство Принцип организации торгового предпринимательства несколько отличается от производственного так как предприниматель выступает непосредственно в роли...
78676. Государственное регулирование предпринимательской деятельности 28.5 KB
  Целью государственного регулирования предпринимательской деятельности является создание определенных условий обеспечивающих нормальное функционирование экономики в целом и стабильное участие предпринимателей страны в международном разделении труда и получение от этого оптимальных выгод. В обобщенном виде в задачи государственного регулирования входят: разработка принятие и контроль за законодательством обеспечивающим правовую основу и защиту интересов предпринимателей; повышение эффективности государственного регулирования и снижение...
78677. Малое предпринимательство, его фин-экон и организационные особенности 41.5 KB
  В современных условиях роль малого бизнеса в рыночной экономике растет. Функции малого бизнеса: Экономические функции малого бизнеса: Придает рыночной системе необходимую гибкость активизация структурных сдвигов процессов разгосударствления и приватизации. Социальные функции малого бизнеса: Обеспечивает рост занятости. Но все перечисленные достоинства малого бизнеса проявляются отнюдь не автоматически.
78678. Среднее и крупное предпринимательство 30.5 KB
  Формы взаимодействия малого среднего и крупного бизнеса в рамках сетевой экономики В современной развитой рыночной экономике малый бизнес оказывается подчинен крупному бизнесу через систему подрядов и субподрядов. Крупному бизнесу как правило обеспечивает гарантию возврата кредита и обеспечивает устойчивые долгосрочные отношения с подрядчиком или субподрядчиком . С помощью франчайзинга малый бизнес получает возможность стабилизировать внешнюю среду обеспечить конкурентные преимущества. Крупный бизнес расширяет сферу контроля над рынком и...
78679. Критерии выделения малого бизнеса в России 28.5 KB
  В реальный сектор экономики входят как отрасли материального производства так и сфера производства нематериальных форм богатства и услуг. В результате научнотехнической революции и роста производительности труда резко изменилось соотношение в нем между отраслями материального производства и сферой услуг. В индустриально развитых странах доля занятых в сфере услуг почти в два раза превышает эту долю в отраслях материального производства составляя более двух третей общей численности занятых. Информатизация экономики порождая дальнейшие...