Презентація на тему:
Застосування Microsoft Excel для оцінки ефективності економічної діяльності фармацевтичної фірми

Лекція №3 Застосування Microsoft Excel для оцінки ефективності економічної діяльності фармацевтичної фірми

Основні розділи лекції Виявлення статистичних залежностей за допомогою табличного процесора Microsoft Excel. Використання Microsoft Excel для вирішення задач оптимального керування.

1. Виявлення статистичних залежностей за допомогою табличного процесора Microsoft Excel Пакет аналізу даних. Види зв’язків між факторами Вибір методу перевірки статистичної залежності Шкали вимірювань

1.1.Пакет аналізу даних В поставку MS EXCEL входить пакет статистичного аналізу даних, доступ до якого можна одержати за допомогою команди Анализ данных меню Сервис. При відсутності даної команди, пакет можна встановити: командою Надстройки меню Сервис відкривається діалогове вікно Надстройки , в якому слід встановити відмітку навпроти пункуту Анализ данных. При цьому необхідний доступ до інсталяційного пакету MS OFFICE

1.1.Пакет аналізу даних

1.1.1.Призначення підпрограм Назва підпрограми Призначення Однофакторний дисперсійний аналіз Перевірка гіпотези про подібність внутрігрупової та міжгрупової дисперсій вибірок Двофакторний дисперсійний аналіз без повторів Теж, але наявно два впливаючих фактори Двофакторний дисперсійний аналіз з повторами Теж, але наявно кілька груп вибірок даних (наприклад кілька спроб) Кореляційний аналіз Виводить матрицю коефіцієнтів парної кореляції для вказаного діапозону даних Коваріаційний аналіз Обчислює середній добуток відхилень точок даних від відносних середніх значень

1.2.Види зв’язків між факторами Функціональні - кожному значенню однієї величини відповідає строго визначене значення іншої, тобто Y = F(X1 Х2, ..., Хn); Стохастичні - зміна значення однієї змінної веде до зміни закону розподілу іншої. Для дискретного випадку - кожному значенню однієї змінної відповідає набір значень іншої, причому кожне значення має свою імовірність реалізації. (приклад - марковські ланцюги). Статистичні - значення одній змінної змінюється в середньому в залежності від того, які значення приймає інша. Часто розглядається як функціональна залежність з випадковою помилкою, тобто: Y = F(X1, X2, ..., Xn) + ε

1.3.Вибір методу виявлення статистичних залежностей Визначення кількості змінних величин в задачі. Визначення впливаючих та залежних змінних. Встановлення шкал вимірювань змінних. Перевірка вибірок на відповідність нормальному закону розподілу. Вибір методу згідно наступної таблиці:

1.4.Шкали вимірювань Дискретні: Класифікації (найменування, номінальна) – можливі лише операції «рівно» і «не рівно». Нумерація чи найменування служить лише для ідентифікації об'єкта; Порядку - можливе порівняння об'єктів по величині — «більше» чи «менше». Інші операції неможливі. Прикладом можуть служити: ступінь тяжкості захворювання, стадія розвитку захворювання й ін. Неперервні: Інтервалів - можливо визначення «наскільки більше» (можливі операції «додавання» і «віднімання»). Прикладом можуть служити шкали виміру температури. Відношень - можливо визначити «у скільки разів» (припустимі всі операції: «порівняння», «додавання» і «віднімання», «множення» і «ділення»). Приклад — вага, довжина й ін.

2.Використання Microsoft Excel для вирішення задач оптимального керування Поняття транспортної задачі. Типи транспортних задач і методи їх вирішення. Формулювання транспортної задачі. Види транспортних задач. Підпрограма “Поиск решения”.

2.1.Поняття транспортної задачі В різноманітних економічних моделях в якості оптимального застосовується план виробництва, що забезпечує заданий виробничий результат при мінімальних затратах. Зокрема, в моделях транспортних задач здійснюється пошук оптимального по тому чи іншому критерію плану перевезень вантажів з пунктів відправлення в задані пункти споживання.

2.2.Типи ТЗ і методи рішення Існує 2 типи транспортних задач: ТЗ за критерієм мінімуму транспортних витрат. ТЗ за критерієм мінмальних строків доставки. При вирішенні ТЗ використовуються наступні методи лінійного програмування: Універсальні - симплекс-метод, метод розширених множників. Спеціальні, що враховують специфіку обмежень кожної конкретної ТЗ - розподільчий метод, метод потенціалів, венгерський, і інші.

2.3.Формулювання ТЗ Формулювання ТЗ за критерієм вартості: В m пунктах відправлення знаходиться, відповідно, a1 , a2 ,…am одиниць однорідного вантажу, який повинен бути доставлений в n заданих пунктів призначення (споживання), відповідно, в кількостях b1, b2,… bn одиниць. Нехай вартість перевезення одиниці вантажу з і – го пункту відправлення в j –й пункт призначення рівна Gij, а відповідна кількість одиниць перевезеного вантажу рівна xij (i=1, …m ; j = 1, … n).

2.4.Види транспортних задач Закрита ТЗ. Сумарні запаси в пунктах відправлення рівні сумарним потребам споживачів: Це канонічна форма ТЗ, всі методи рішення розроблені для неї. Відкрита ТЗ. Баланс між потребами і запасами порушено.

2.4.1.Рішення відкритих ТЗ (А) Сумарні ресурси переважають сумарні потреби: Слід ввести фіктивного споживача Вn+1 з попитом, що рівний різниці між наявними запасами і реальним попитом: Вартості перевезень вантажів з усіх пунктів відпралення у фіктивний пункт призначення рівні: c1(n+1)= c2(n+1) = … = cm(n+1) = 0

2.4.2.Рішення відкритих ТЗ (Б) Сумарні потреби переважають сумарні ресурси: Слід ввести фіктивного постачальника Аm+1 з запасом, що рівний різниці між наявними запасами і попитом: Вартість перевезень вантажів з фіктивного складу у всі пункти призначення рівні: c(m+1)1= c(m+1)2 = … = c(m+1)n = 0

2.5.Підпрограма “Поиск решения” Пошук рішення являється частиною блока задач, який називають “аналіз що-якщо”. В процесі пошуку рішення відбувається зміна значень впливаючих клітинок так, щоб знайти екстремальне значення (максимум, мінімум) формули, що знаходиться у цільовій клітинці. Підпрограма запускається командою меню «Сервис-Поиск решения». Якщо дана команда відсутня, необхідно підключити дану надбудову командою меню «Сервис-Надстройки»

2.5.1.Вирішення ТЗ Вирішення транспортної задачі за допомогою підпрограми “Поиск решения” проходить в такій послідовності: Підготовка вихідних даних. Формування цільової функції. Формування функцій обмежень. Програмування підпрограми “Поиск решения”: задання цільової функції; заданя діапозону клітинок, значення яких необхідно змінювати; встановлення обмежень на розрахунок.

2.5.3.Функції обмеженнь Цілоьва функція: добуток матриці ватрості перевезень на матрицю планів перевезень (СУММПРОИЗВ) Обмеження для постачальників: сумарна кількість перевезених товарів від кожного постачальника не повинна перевищувати наявних у нього запасів. Обмеження для споживачів: сумарна кількість товарів, що доставляються кожному споживачу, не повинна перевищувати його потреб.

2.5.Підпрограма “Поиск решения”

2.5.Підпрограма “Поиск решения”

2.5.2.Формування вихідних даних

Висновки У даній лекції висвітлено такі питання: Виявлення статистичних залежностей за допомогою табличного процесора Microsoft Excel. Використання Microsoft Excel для вирішення задач оптимального керування.

Джерела додаткової інформації Електронний посібник на локальній веб-сторінці кафедри: http://miserver/ http://10.21.0.49/ Microsoft Office 2000. Шаг за шагом: Практ пособие. / Пер. с англ. – М.: Изд-во ЭКОМ. Пасько В. Excel 2000 (руссифицированная версия):. – К.: Изд- группа BHV.

ЕКОНОМІЧНІ РОЗРАХУНКИ в MS EXCEL

ЗМІСТ Використання функцій в EXCEL Функція ЕСЛИ Функція СЧЕТЕСЛИ Функція СУММЕСЛИ Умовне форматування Стандартні фінансові функції Функція ПС Функція ЧПС Функція БС Функція ПЛТ Функція КПЕР Вихід

Функції — це програми, що за готовими формулами реалізують обчислення над аргументами, які задаються користувачем згідно з правилами запису (синтаксису) функції. Доступ до функції здійснюється за її іменем. Аргументи функцій записують у круглих дужках через крапку з комою. Ними, зокрема, можуть бути числа, посилання на комірки та діапазони, імена, текст, логічні значення, значення помилок, вкладені функції тощо. Використання ФУНКЦІЙ Меню

Програма Excel має приблизно 400 стандартних функцій, які умовно поділяються на категорії: математичні — SIN, ABS, КОРЕНЬ, СУММ та ін.; статистичні — МИН, МАКС, СРЗНАЧ, ТЕНДЕНЦИЯ та ін.; логічні — ИСТИНА, ЛОЖЬ, ЕСЛИ, ИЛИ, И та ін.; фінансові — БС, ПС, ЧПС, ПЛТ та ін.; дати і часу — ГОД, ДЕНЬ, СЕКУНДЫ та ін.; робота з базою даних — БДСУММ, БДПРОИЗВЕД та ін.; текстові — ЗНАЧЕН, ТЕКСТ, СТРОЧН та ін. перевірка властивостей і значень — ТИП.ОШИБКИ, ЯЧЕЙКА, ИНФОРМ та ін.; Меню

Виділити комірку і виконати команду меню Вставка / Функция Скористатися кнопкою Вставка функции рядочка Формул Якщо синтаксис функції Вам добре відомий, набирати її безпосередньо з клавіатури Введення функції до формули Меню

Крім того , Окремі статистичні функції, наприклад СУММ, МАКС, МИН, можна швидко обчислити без використання формул, виділивши потрібний діапазон комірок і ввімкнувши режим автообчислення клацанням правої кнопки миші в рядку стану на полі Автовычисление. Результат обчислень буде зображений у цьому ж полі. У подальшому значення останньої вибраної функції обчислюватиметься автоматично. Меню

Крім того , Підсумувати значення рядка (стовпчика) суміжних комірок можна також, виділивши комірку, що замикає рядок справа (стовпчик — знизу), і клацнувши кнопку Автосумма на панелі інструментів Стандартная. Якщо виділити діапазон комірок і натиснути кнопку Автосумма, функцію СУММ буде застосовано автоматично до всіх стовпців діапазону, а результати розташовано в наступних комірках. Меню

Реалізація умовних конструкцій функції ЕСЛИ( ), СЧЕТЕСЛИ( ), СУММЕСЛИ( ) та умовне форматування Excel дозволяє використовувати функції та режими форматування, за допомогою яких можна здійснювати вибір із множини значень комірок, виконувати операції лише над цими значеннями, а також здійснювати типові обчислювальні операції. Вибір із множини задається умовою, яка в термінології функцій Excel називається критерієм. Усі комірки (їх значення), які задовольняють критерій, включаються в обчислювальну операцію або в дію впливу на формат відповідних комірок. Вміння маніпулювати такими простими засобами дає змогу розробляти потужні сучасні бізнес - програми навіть без використання програмування. Меню

Функція ЕСЛИ повертає одне значення, якщо задана умова при обчисленні дає значення ІСТИНА, і друге значення, якщо НЕ ІСТИНА Функція ЕСЛИ Синтаксис функції: ЕСЛИ(умова; значення1; значення2) Меню

Функція ЕСЛИ Меню

Параметри функції ЕСЛИ Условие — будь-який вираз, значення якого буде або ИСТИНА, або ЛОЖЬ. Значение_если_Истина — це значення чи вираз, який поверне функція ЕСЛИ() як істинне значення параметра Условие. Якщо параметр не зазначити, то як результат ЕСЛИ() повернеться ИСТИНА. Виразом у параметрі, зокрема, може бути будь-яка інша функція, зокрема ЕСЛИ(). Допускається до семи вкладень функцій ЕСЛИ() одна в одну. Меню

Параметри функції ЕСЛИ Значение_если_НЕ_Истина — це значення чи вираз, який поверне функція ЕСЛИ() як хибне значення параметра Условие. Якщо параметр не зазначити, то як результат ЕСЛИ() повернеться ЛОЖЬ. Виразом у параметрі, зокрема, може бути будь-яка інша функція, зокрема ЕСЛИ. Меню

Приклади застосування функції ЕСЛИ ЕСЛИ(С7>J7-15,39;"У балансі є помилка!";"") ЕСЛИ(С7>J7-15,39;"У балансі є помилка!"; ЕСЛИ (С7=2002; "У балансі можлива помилка!"; "Баланс нормальний!")) Для складніших умовних виразів, коли задається багато значень, потрібно використовувати функції И(Рг;Рг;...) та ИЛИ(Рг;Рг;...), де Рг — умовний вираз. Ці функції мають бути вкладені у функцію ЕСЛИ() на місці Условие. Меню

Функція СЧЕТЕСЛИ() Синтаксис функції: СЧЕТЕСЛИ(Диапазон; Критерий) Функція СЧЕТЕСЛИ() обчислює кількість комірок у діапазоні, які задовольняють певний критерій. Диапазон задає прямокутну множину комірок, які функція бере до розгляду. Розривні діапазони не підтримуються. Значеннями в комірках можуть бути числові дані в усіх різновидах, логічні значення, слова і фрази (комірки можуть бути також порожні). Критерий — це вираз з умовою для значень у комірках діапазону. Меню

Приклади застосування функції СЧЕТЕСЛИ() СЧЕТЕСЛИ(D2:F9; 2009) СЧЕТЕСЛИ (D2:F9;"=2009") СЧЕТЕСЛИ(D2:F9; "samtron") СЧЕТЕСЛИ(A7:Е7; F8) СЧЕТЕСЛИ(С17:Е27; ">=2010") СЧЕТЕСЛИ(A1:D24; ">="&Е8) Меню

Функція СУММЕСЛИ() Синтаксис функції: СУММЕСЛИ (Диапазон; Критерий; Диапазон_Суммирования) Функція СУММЕСЛИ() використовується для розрахунку суми комірок діапазону, які задовольняють заданий критерій Меню

Функція СУММЕСЛИ() Синтаксис функції: СУММЕСЛИ (Диапазон; Критерий; Диапазон_Суммирования) Диапазон задає прямокутну множину комірок, які функція бере до розгляду. Розривні діапазони не підтримуються. Критерий — це вираз з умовою для значень у комірках діапазону. Диапазон_Суммирования визначає діапазон комірок, в якому виконується додавання значень комірок, для яких відповідні комірки у параметрі Диапазон задовольняють Критерий. Меню

Приклади застосування функції СУММЕСЛИ() СУММЕСЛИ(D2:D9; ”>300”;A2:A9) СУММЕСЛИ(D7:J7; 2009; D12:J12) СУММЕСЛИ(D7:J7;"" B17;D12:J12 ) СУММЕСЛИ(A7:Е7; ”=2009”;A20:E20) СУММЕСЛИ(С17:Е27; ">=2010") Меню

Умовне форматування На відміну від звичайного формату комірки або діапазону умовне форматування спрацьовує зміною формату на новий попередньо зазначений користувачем формат лише при виконанні певних умов. Типово цими умовами є значення в цій самій комірці або діапазоні. Меню

Для застосування до комірки/діапазону умовного форматування: виділіть комірку/діапазон; у меню Формат виберіть пункт Формат/Условное форматирование. у вікні діалогу групою керуючих елементів пропонується задати першу умову застосування форматування. задавши умову форматування, натисніть кнопку Формат. задайте формати шрифту, меж і колір тла комірки; якщо потрібна ще одна умова застосування для комірки/діапазону умовного форматування натисніть кнопку А также; якщо всі чи кілька умов (всього їх три) потрібно анулювати, натисніть кнопку Удалить. Меню

Умовне форматування На відміну від звичайного формату комірки або діапазону умовне форматування спрацьовує зміною формату на новий попередньо зазначений користувачем формат лише при виконанні певних умов. Типово цими умовами є значення в цій самій комірці або діапазоні. Меню

Стандартні фінансові функції Програма Excel містить функції для фінансового аналізу: функції аналізу інвестицій, обчислення швидкості обороту, розрахунку амортизації, аналізу цінних паперів. Меню

Розглянемо стандартні фінансові функції для аналізу інвестицій. У них використовують аргументи, призначення яких наведене в таблиці. Обов'язкові аргументи будемо виділяти в синтаксисі функції кольором. Меню Аргумент Призначення Ставка Процентна або облікова ставка Кпер Кількість періодів Выплата Постійні періодичні виплати Бз Майбутній обсяг внеску наприкінці терміну (за замовчуванням 0) Нз Поточна вартість внеску (за замовчуванням 0) Тип Число, що визначає, коли здійснюється виплата (за замовчуванням 0): 0 — наприкінці періоду, 1 — на початку Значение1; Значение2;... Змінні періодичні виплати

Функція ПС повертає поточне значення внеску, яке визначається дисконтуванням (зведенням до вартості в поточний момент) надходжень за цим внеском. Якщо поточна вартість надходжень перевищує внесок, то таке вкладення вважають вдалим. Функція ПС Синтаксис функції: ПС(Ставка;Кпер;Выплата;Бз,Тип) Меню

Функція ЧПС обчислює чисту поточну вартість внеску, використовуючи дисконтну ставку, а також обсяги майбутніх платежів(від'ємні значення) і надходжень (додатні значення). Внесок вважають вдалим, якщо його чиста поточна вартість перевищує нуль. Якщо вартість внеску виплачують авансом, її не включають у список аргументів Значение, а віднімають від значення функції НПЗ Функція ЧПС Синтаксис функції: ЧПС(Ставка; Значение1; 3начение2;...) Меню

Функція ЧПС має дві суттєві відмінності від функції ПС: ЧПС допускає використання виплат змінної величини, у той час як ПС припускає, що значення виплат постійні; ПС допускає, щоб платежі здійснювались як на початку, так і наприкінці періоду, тоді як ЧПС припускає, що всі платежі розподілені рівномірно і здійснюються наприкінці кожного періоду Різниця між функціями ЧПС і ПС Меню

Функція БС повертає майбутнє значення внеску на основі періодичних постійних платежів і постійної процентної ставки. Функція БС Синтаксис функції: БС(Ставка; Кпер; Выплата; Нз; Тип) Меню

Функція ПЛТ обчислює періодичні виплати, необхідні для погашення позики за зазначену кількість періодів. Функція ПЛТ Синтаксис функції: ПЛТ(Ставка;Кпер;Нз;Бз;Тип) Меню

Функція КПЕР обчислює кількість періодів, необхідних для погашення позики при заданій величині періодичних виплат Функція КПЕР Синтаксис функції: КПЕР(Ставка;Кпер;Нз;Бз;Тип) Меню

Дякую за Увагу.