Створення зведених таблиць.

Зведена таблиця є одним з інтерактивних інструментів аналізу даних. Зведені таблиці дають змогу виконати угрупування даних таблиць Microsoft Excel за одним або декількома критеріями і обробку інформації по групах, підгрупах.

Визначимо основні терміни.

Джерело даних – це список або база даних, на основі яких створюється зведена таблиця. Зведені таблиці можна створювати на основі відомостей, що знаходяться:

- у списку даних Excel;

- у декількох діапазонах консолідації;

- у інших зведених таблицях;

- у зовнішніх базах даних.

В якості зовнішніх баз даних можуть використовуватися бази даних MS SQL Server, MS FoxPro, MS Access та інші.

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

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

Поля сторінки дозволяють виводити дані по одному елементу. Це забезпечує можливість роботи з великим обсягом даних.

Областю даних називається частина зведеної таблиці, що містить підсумкові дані для елементів полів рядку або стовпця.

Позиції – це підкатегорії зведеної таблиці, що відображають спосіб підведення підсумків.

Розглянемо порядок створення зведеної таблиці нам основі списку Excel.

Щоб побудувати зведену таблицю слід подати команду меню Данные – Сводная таблица. У результаті буде відчинено вікно майстра зведених таблиць, що включає в себе 4 етапи.

На першому етапі роботи з майстром зведених таблиць потрібно обрати джерело даних для зведених таблиць - в списке или базе данных Microsoft Excel і тип звіту, що створюється, - сводная таблица. Натиснути на кнопці Далее.

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

На третьому етапі роботи з майстром слід визначити макет зведеної таблиці та її місце розташування. За допомогою кнопки Макет можна визначити порядок групування та обробки. Щоб згрупувати дані за певним показником слід перетягти назву показника в область Строка або Столбец. Якщо елемент показника поміщено в область Строка, то в зведеній таблиці буде стільки рядків, скільки значень даного показника існує в таблиці – джерелі даних. Відповідно можна згруповані дані вивести у стовпцях. В область Строка або Столбец можна помістити декілька показників. У цьому випадку перший показник буде створювати групу, другий підгрупу в групі і т.д. Для обробки даних відповідні елементи слід перетягти у область Данные. За замовчуванням застосовується операція сумування. Для зміни операції можна виконати подвійне клацання на елементі в області Данные.

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

Для редагування зведеної таблиці слід виділити будь-яку клітинку всередині таблиці, подати команду меню Данные – Сводная таблица.

2. Консолідація даних в середовищі Microsoft Excel.

Консолідація – це об’єднання даних з декількох таблиць, що можуть бути розташовані як на одному, так і на різних листах книги. В Excel існує декілька інструментів, що можуть консолідувати дані. Розглянемо 3 типу консолідації:

- по розташуванню – використовується для об’єднання даних, якщо дані вихідних областей знаходяться в одному і тому ж місці і розташовані в одному і тому ж порядку;

- по категоріям – використовується для об’єднання даних, якщо дані вихідних областей не упорядковані, але мають одні й ті ж заголовки;

- за допомогою трьохмірних посилань – використовується, коли відсутні обмеження по розташуванню даних на вихідних областях.

Розглянемо більш детально кожен з типів консолідації.

Консолідація по розташуванню

Для створення консолідації по розташуванню необхідно:

1. Скопіювати будь-яку таблицю на лист, на якому необхідно виконати об’єднання даних.

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

3. Не знімаючи виділення, виконати команду Данные – Консолидация.

4. У вікні, що з’явилося, обрати функцію, яку необхідно використовувати для обробки даних. Для підведення підсумків в таблиці консолідації можуть бути використані операції:

- сума значень;

- середнє значення;

- максимум;

- мінімум;

- кількість значень;

- кількість непустих рядків;

- середньоквадратичне відхилення вибірки з генеральної сукупності;

- дисперсія вибірки з генеральної сукупності.

5. В поле Ссылка ввести перший діапазон даних, що консолідуємо і натиснути кнопку Добавить. При цьому цей діапазон відобразиться у полі Список диапазонов.

6. Аналогічно введіть інші діапазони даних, що консолідуються.

7. Натиснути кнопку ОК.

В результаті отримаємо таблицю з консолідованими даними.

Консолідація по категоріям

Для створення консолідації по розташуванню необхідно:

1. Скопіювати шапку будь-якої таблиці на лист, на якому необхідно виконати об’єднання даних.

2. Поставити курсор в першу порожню клітинку.

3. Виконати команду Данные – Консолидация.

4. У вікні, що з’явилося, обрати функцію, яку необхідно використовувати для обробки даних.

5. В поле Ссылка ввести перший діапазон даних, що консолідуємо і натиснути кнопку Добавить. При цьому цей діапазон відобразиться у полі Список диапазонов.

6. Аналогічно введіть інші діапазони даних, що консолідуються.

7. Встановити параметри Использовать в качестве имен:

«Подписи верхней строки»;

«Значение левого столбца».

8. Натиснути кнопку ОК.

В результаті отримаємо консолідовану таблицю, без прив’язки до адресів клітинок.

Консолідація даних з використанням трьохмірних посилань

Для демонстрації цього типу консолідації наведемо приклад, у якому розраховується сума вміст клітинки В4 першого листа, F5 другого листа та В9 третього листа. Все це робиться за допомогою формули:

=СУММ(Лист1! В4; Лист2! F5; Лист3! В9)

Другий приклад. Формула =МАКС(Січень: Грудень! С: С) аналізує вміст 12 листів, яким присвоєні назви місяців року і знаходить максимальне значення у стовпці С.

ТЕМА 6

ПРОГНОЗУВАННЯ ОСНОВНИХ ПОКАЗНИКІВ ДІЯЛЬНОСТІ ПІДПРИЄМСТВ РЕСТОРАННОГО БІЗНЕСУ

Питання:

1. Прогнозування за допомогою ліній тренду.

2. Прогнозування з використанням статистичних функцій.

3. Прогнозування з використанням пакету аналізу.

Література:

1. Оліфіров О.В. Інформаційні системи і технології підприємства [Текст]: навчальний посібник для студентів екон. напрямку ден. і заоч. форм навчання/ О.В. Оліфіров, Н.М. Спіцина, Т.В. Шабельник; Донец. нац. ун-т економіки і торгівлі ім.. М. Туган-Барановського, каф. інформац. систем і технологій упр. – Донецьк: [ДонНУЕТ], 2010. – 312 с.

2. Апатова Н. В. Інформатика для економістів: підручник: затв. М-вом освіти і науки України для студ. вищ. навч. закл. / Н. В. Апатова, О. М. Гончарова та Ю. Ю. Дюлічева; М-во освіти і науки України. ─ К.: Центр учб. л-ри, 2011. ─ 456 с. ─ 978-611-01-0159-2.

1. Прогнозування за допомогою ліній тренду

Керівники підприємств ресторанного бізнесу вимушені приймати рішення в умовах постійного тиску обставин, досить часто не маючи повної і точної інформації. Статистичний аналіз допомагає вилучати інформацію з даних і оцінювати якість цієї інформації. Таким чином, статистичні методи слід розглядати як важливу частину процесу прийняття рішень, що дозволяє виробляти обґрунтовані стратегічні рішення, які поєднують інтуїцію фахівців з ретельним аналізом наявної інформації. Стратегічне планування визначає поточний стан підприємства задає напрям для майбутньої діяльності, координує зусилля різних підрозділів, оптимізує розподіл ресурсів.

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

Крім того, особливо важливим для вибору методів (моделей) прогнозування та вірогідності результатів, які одержуються, є наявність достатньої кількості даних і припустимий період прогнозу. Чим коротший період прогнозування, тим вище вірогідність прогнозованих величин.

Визначення тенденцій за допомогою додавання лінії тренду на діаграму

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

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

Прогноз можна виконувати по одному з шести видів рівнянь, запропонованих діалоговому вікні (рис.2.1 а).

За допомогою вкладки Параметри цього ж вікна (рис.2.2 б) можна задати період прогнозу вперед або назад, дати і’мя тренду, показати рівняння регресії на діаграмі, рівняння середньоквадратичного відхилення.

На діаграмі з’явиться відповідна лінія регресії (лінійна, логарифмічна, експоненційна, поліноміальна, ступенева, ковзної середньої).

Можна порекомендувати при значному розсіянні фактичних даних підбирати функції з більш високим порядком (наприклад, для ступеневої) або з більшим періодом згладжування (наприклад, для ковзної середньої).

Якщо повторювати виділення ряду й виклик діалогового вікна Лінії тренду вкладка Тип, можна нанести декілька видів ліній регресії на один рисунок.

Подвійне клацання по самій лінії тренду на діаграмі дає можливість задати різні типи та товщину ліній, колір для кожної лінії регресії.

Для додавання лінії тренду на діаграму потрібно зробити таке:

1.Оберіть ряд даних, до якого потрібно додати лінію тренду.

2. Оберіть команду Добавить линию тренда в меню Диаграмма.

3.На вкладці Тип оберіть потрібний тип регресійної лінії тренду. При виборі типу Полиномиальная введіть в поле Степень найбільший ступінь для незалежної змінної.

4. У полі Прогноз вперед на введіть кількість бажаних періодів, протягом яких лінія тренду буде простягатися уперед.

5. На вкладці Параметры установіть флажки Показывать уравнение на диаграмме і Поместить на диаграмму величину достоверности аппроксимации (R^2).

6. Клацніть по кнопці ОК.

На діаграмі чорною тонкою лінією буде відображена лінія тренду (рис. 3.23). За відповідною величиною достовірності апроксимації (R²®1) за допомогою отриманого рівняння можна виконувати прогнозування даних на наступні періоди.

2. Прогнозування з використанням статистичних функцій

Стандартні функції MS Excel можуть використовуватися для прогнозування даних.

Функція ПРЕДСКАЗ обчислює майбутнє значення по існуючим значенням з використанням лінійної регресії. Значення, що пронзується, є y-значенням, що відповідає заданому x-значенню. Цією функцією можна скористатися для прогнозування майбутніх продажів, потреб в обладнанні або тенденцій споживання.

Структура функції ПРЕДСКАЗ така:

= ПРЕДСКАЗ(Х, Відомі_значення_Y, Відомі_значення_Х) (3.1)

де Х – значення, для якого робиться прогнозування;

Відомі_значення_Y – незалежний масив даних;

Відомі_значення_Х – незалежний масив даних.

Функції ТЕНДЕНЦИЯ і РОСТ дозволяють екстраполювати y-значення, продовжують пряму лінію або е кспоненційну криву, що найкращим чином описують існуючі дані. Ці функції повертають y-значення, що відповідає заданим x-значенням. Структура функцій така:

= РОСТ() (3.2)

де Х – значення, для якого робиться прогнозування;

Відомі_значення_Y – незалежний масив даних;

Відомі_значення_Х – незалежний масив даних.

=ТЕНДЕНЦИЯ(Відомі_значення_Y, Відомі_значення_Х, Х) (3.3)

де Х – значення, для якого робиться прогнозування;

Відомі_значення_Y – незалежний масив даних;

Відомі_значення_Х – незалежний масив даних.

Функції ЛИНЕЙН і ЛГРФПРИБЛ дозволяють обчислити пряму лінію або експоненційну криву для існуючих даних. Ці функції повертають дані регресійного аналізу, включаючи наклон та зміщення графіку відносно осі Y.

Функція ЛИНЕЙН використовується для розрахунку коефіцієнтів прямої, яка найкращим чином апроксимує вихідні дані і має вигляд: y = mx + b. Діалогове вікно заповнюється за зразком (рис. 3.24).

Після заповнення потрібно натиснути одночасно клавіші Ctrl+Shift+Enter. На основі отриманих коефіцієнтів можна побудувати рівняння прямої:

Рисунок 3.24 – Мастер функції ЛИНЕЙН

Функція ЛГРФПРИБЛ використовується для розрахунку коефіцієнтів експоненціальної кривої, що апроксимує дані і має вигляд y = b*m^x. Діалогове вікно заповнюється за зразком:

Рисунок 3.25 – Майстер функції ЛГРФПРИБЛ

Після заповнення натиснути одночасно клавіші Ctrl+Shift+Enter. На основі отриманих коефіцієнтів побудувати рівняння експоненціальної кривої.

При необхідності виконання більш складного регресійного аналізу — включаючи обчислення і відображення залишків — можна використовувати засіб регресійного аналізу в надстройці «Пакет анализа».

3. Прогнозування з використанням пакету аналізу

Багатофакторна регресія

У реальній дійсності всі економічні процеси взаємозалежні і часто потребують визначення ступеня такого зв’язку та вплив його на майбутні періоди. Кореляційно-регресійний аналіз у системі обробки економічної інформації використовується для вивчення й моделювання зв'язків між економічними показниками.

Економічні показники можна розділити на два класи: факторні й результативні.

Результативний показник (y) – досліджуваний показник, що характеризує ефективність економічного процесу.

Факторний показник (x) – показник, що робить вплив на результативний показник.

По ступені або рівню детермінованості бувають стохастичні й функціональні зв'язки.

Стохастичні зв’язки між економічними показниками виникають у випадках ймовірних процесів.

Під детермінованістю в цьому випадку передбачається наявність твердого функціонального зв'язку між змінними.

Приватним випадком функціонального зв'язку є кореляційний зв’язок.

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

Для кількісної оцінки щільності зв'язку широко використовується лінійний коефіцієнт кореляції. Лінійний коефіцієнт кореляції змінюється в межах від -1 до +1. Інтерпретація вихідних значень коефіцієнта кореляції представлена в табл. 2.1.

Таблиця 2.1 - Оцінка лінійного коефіцієнта кореляції

Регресійна модель – запис виявленого зв'язку між результативним показником і факторами у вигляді рівняння, коли результуючий показник має випадкову складову, а фактори – детерміновані.

Теоретичною лінією регресії називається лінія, навколо якої групуються крапки кореляційного поля і яка вказує на основну тенденцію зв'язку.

По напрямку зв'язки розрізняють:

· пряма регресія - виникає за умови, якщо зі збільшенням або зменшенням незалежного показника значення залежного показника відповідно збільшується або зменшується;

· зворотна регресія - виникає за умови, якщо зі збільшенням або зменшенням незалежного показника значення залежного показника відповідно зменшується або збільшується.

Регресійний аналіз часто обмежується простим зв'язком між одним факторним і результативним показниками. Але у випадках, коли дуже важко встановити закономірний зв'язок стає необхідним використання множинної регресії.

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

Відбір факторів для кореляційно-регресійного аналізу

Між факторними показниками не повинно бути функціонально щільного кореляційного зв'язку. Це означає, що парний коефіцієнт кореляції не повинен перевищувати за модулем 0.7, 0.8:

(2.3)

де: - середнє квадратичне відхилення між факторним і результативним показниками,

- середнє квадратичне відхилення факторного показника,

- середнє квадратичне відхилення результативного показника.

Визначення регресійної функції. Оцінка якості регресійної моделі

Для визначення типу регресійної функції проводиться аналіз емпіричних даних по наступних напрямках:

Визначення напрямку зв'язку.

Визначення зміни напрямку зв'язку досліджуваної сукупності (чи є залежність монотонною).

Визначення характеру змін: рівномірно прискорюваних або що вповільнюються (лінійного або нелінійного характеру).

Необхідні, для визначення типу функції, відомості одержують із емпіричного матеріалу. Висновок про напрямок і форму зв'язку (аналітичному вираженню) можна одержати шляхом паралельного порівняння рядів показників, а також із графічного аналізу.

У наступній таблиці наведені типи функцій, що використовуються найбільше часто для опису парних та багатофакторних залежностей.

Таблиця 2.2 - Типи функцій

Найменування	Функція	Випадок використання
Лінійна	,	X і Y зростають приблизно в арифметичній прогресії
Гіперболічна	,	Зв'язок між X і Y зворотний
Показова	,	Логарифми Х зростають приблизно в арифметичній прогресії
Параболічна		X збільшується в арифметичній прогресії, а Y значно швидше
Ступенева
Логістична		Відношення приростів логарифмів Х є постійною величиною

У таблиці прийняті наступні позначення:

а₀ – вільний член регресії,

а₁ – коефіцієнт регресії.

Чим більше величина коефіцієнта регресії, тим значніше вплив даного показника на результуючий. Особливе значення при цьому має знак перед коефіцієнтом регресії.

З наведених форм зв'язку вибирають за формальними критеріями апроксимації одну. Для цього рекомендується використовувати один із двох критеріїв, що характеризують середню помилку апроксимації функції:

1. Показник середньої помилки апроксимації:

,

де: n – число значень досліджуваного показника,

- i – е значення досліджуваного показника,

- вирівняне по функції i – е значення досліджуваного показника.

2. Показник середньої квадратичної помилки:

,

де: k – число факторних ознак.

Застосування зазначених критеріїв є досить надійним способом відбору адекватних математичних моделей, при цьому значення помилки апроксимації не повинне перевищувати 12% - 15%.

Вірогідність побудованої регресійної моделі можна перевірити за допомогою коефіцієнта детермінації (R²):

, , (2.4)

де - значення параметра Y, розраховане відповідно до регресійної моделі.

Коефіцієнт детермінації показує якою мірою варіація результативного показника обумовлена впливом факторних показників.

Якщо , то лінія регресії точно відповідає всім спостереженням.

Коли можна стверджувати, що видимий зв'язок між X і Y є відсутній.

Якщо R² має нечітко виражене граничне значення (наприклад, 0.5), то в таких випадках зручно використовувати критерій Фішера або F - Тест.

При проведенні F - Тесту будемо вважати нульовою гіпотезою те, що значення R² отримане випадково й між величинами немає зв'язку.

Порядок перевірки моделі на адекватність за критерієм Фішера такий:

1. Розрахунок величини F - відносини:

(2.5)

2. Вибір користувачем рівня значимості =0, 05 або =0, 01.

3. По статистичних таблицях F - розподілу знаходимо граничне значення F_кр при v₁=(k-1) і v₂=(n-k) ступенях свободи.

Якщо розраховане значення , то відкидається нульова гіпотеза з ризиком помилки не більше, ніж у випадків.

Потім провадитися оцінка значимості коефіцієнтів регресії за допомогою t-критерію Стьюдента:

. (2.6)

Параметр моделі зізнається статистично значимим, якщо ,

де - рівень значимості критерію перевірки гіпотези про рівність нулю параметрів, що вимірюють зв'язок (задається користувачем),

- число ступенів свободи, що характеризує число вільно варіюючих елементів сукупності.

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

Таким чином, при виборі кращої регресійної моделі варто керуватися такими принципами:

1. Модель повинна бути адекватною за F - критерієм Фішера.

2. Коефіцієнти регресії повинні бути статистично значимі (задовольняти t - критерію Стьюдента).

3. Обрана математична функція повинна мати мінімальну помилку апроксимації серед всіх розглянутих рівнянь регресії.

При цьому можливі наступні варіанти:

1. Побудована модель на основі перевірки за F - критерієм Фішера адекватна, і всі її коефіцієнти значимі. Така модель може бути використана для прийняття рішень і здійснення прогнозів.

2. Модель за F - критерієм Фішера адекватна, але частина коефіцієнтів регресії незначима. У цьому випадку модель придатна для прийняття рішень, але не повинна використовуватися для прогнозування. Використання моделі для прогнозування можливо після виключення факторів, коефіцієнти яких є незначущі.

3. Модель за F - критерієм Фішера адекватна, але всі коефіцієнти регресії є незначущі. Така модель повністю вважається неадекватною.

Параметри рівняння регресії розраховуються за методом найменших квадратів.

а _i - параметр, що показує на скільки одиниць свого виміру зміниться Y, якщо x_i збільшитися на одну одиницю свого виміру, за умови, що всі інші фактори, включені в модель, впливають на Y, але не варіюють, тобто зафіксовані на рівні свого середнього значення.

а ₀ – параметр, що виражає сукупний вплив всіх неврахованих факторів, його внесок у значення результуючого показника не залежить від зміни факторів.

По параметрах рівняння можна оцінити частку кожного з факторів у зміні рівня результативного показника. Це може бути зроблено шляхом прямої оцінки за величиною коефіцієнтів регресії, а так само і за коефіцієнтом еластичності:

. (2.7)

Коефіцієнт еластичності показує, на скільки відсотків зміниться результативний показник при зміні факторного показника на один відсоток при фіксованому положенні інших факторів.

Знаки говорять про характер впливу на результативний показник. Якщо факторний показник має знак плюс, то зі збільшенням даного фактору результативний показник зростає; якщо факторний показник має знак мінус, то з його збільшенням результативний показник зменшується.

ТЕМА 7 ОПТИМІЗАЦІЙНІ РОЗРАХУНКИ НА ПІДПРИЄМСТВАХ РЕСТОРАННОГО БІЗНЕСУ

Питання:

1. Загальне поняття оптимізаційної задачі.

2. Основні типи задач планування.

Література:

1. Оліфіров О.В. Інформаційні системи і технології підприємства [Текст]: навчальний посібник для студентів екон. напрямку ден. і заоч. форм навчання/ О.В. Оліфіров, Н.М. Спіцина, Т.В. Шабельник; Донец. нац. ун-т економіки і торгівлі ім.. М. Туган-Барановського, каф. інформац. систем і технологій упр. – Донецьк: [ДонНУЕТ], 2010. – 312 с.

2. Апатова Н. В. Інформатика для економістів: підручник: затв. М-вом освіти і науки України для студ. вищ. навч. закл. / Н. В. Апатова, О. М. Гончарова та Ю. Ю. Дюлічева; М-во освіти і науки України. ─ К.: Центр учб. л-ри, 2011. ─ 456 с. ─ 978-611-01-0159-2. Мур Д. и др. Экономическое моделирование в Microsoft Excel, 6-е изд.: Пер. с англ.. – М.: Издательский дом «Вильямс», 2004. – 1024с.

3. Орлова И.В., Половников В.А. Экономико-математические методы и модели: компьютерное моделирование: Учеб. Пособие. – М.: Вузовский учебник, 2008. – 365с.

4. Спіцина Н.М. Інформаційні системи і технології підприємства [Текст]: метод.вказівки та індивід. Завдання для проведення лаборатор. і самост. робіт з модуля «Використання програми Microsoft Excel для моделювання основних показників підприємства» для студ. ін.-ту економіки упр. спец. 6.050107 ден. і заоч. форм навчання/ Н.М. Спіцина, Т.В. Шабельник, Ю.І. Соломка. – Донецьк: [ДонНУЕТ], 2008. – 49с.

5. Статистика для менеджеров с использованием Microsoft Excel, 4-е изд.: Пер. с англ./ Левин Д. и др. - Издательский дом «Вильямс», 2004. – 1312с.

6. Ханк Д.Э., Уичерн Д.У., Райтс А.Дж. Бизнес-прогнозирование, 7-е изд.: Пер. с англ.. – М.: Издательский дом «Вильямс», 2003 – 656с.

7. Шабельник Т.В. Комп'ютерні системи обробки обліково-фінансової інформації [Текст]: навч. посібник для студентів спеціальності 7.050104 «Фінанси» рівня спеціаліст ден. і заоч. форм навчання / Т. В. Шабельник; Донець. нац. ун-т економіки і торгівлі ім. М. Туган-Барановського, Каф. комп'ютер. технологій. - Донецьк: ДонНУЕТ, 2008.- 125 с.

1. Загальне поняття оптимізаційної задачі.

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

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

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

1) наявність системи взаємозалежних факторів;

2) строго певний критерій оцінки оптимуму;

3) точне формулювання умов, які обмежують використання ресурсів або факторів.

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

Для чисельного вирішення рівнянь з декількома невідомими і обмеженнями в Excel існує інструмент Пошук рішення.

За замовченням інструмент не встановлений, тому його потрібно встановити: вставити дистрибутивний CD-диск і вибрати у вікні Сервис – Надстройка відповідний прапорець.

Якщо цільова функція і обмеження лінійна, то рішення полягає в знаходженні множини чисел (х₁, х₂.. х_n), які мінімізують (максимізують) лінійну цільову функцію f ( х_{1 ,} х₂ .. х_n) = c₁х₁+c₂х₂+. +c_nх_n при m< n лінійних обмеженнях-рівняннях а_i1х₁+а_i2х₂+. +а_inх_n ( де i=1, 2. m) і n лінійних нерівностях х_k> =0 (де k =1, 2... n).

Алгоритм рішення оптимізаційної задачі з декількома невідомими такий:

- економічна постановка (аналіз завдання, визначення властивостей, параметрів, обмежень);

- математична постановка (математичний опис моделі, що оптимізується, – введення позначень, обмежень, і побудова цільової функції);

- реалізація задачі в середовищі Microsoft Excel.

2. Основні типи задач планування.

Задача оптимізації використання ресурсів (задача планування виробництва). Для виготовлення двох видів продукції P₁ і P₂ використовують чотири види ресурсів S₁, S₂, S₃, S₄. Запаси ресурсів, число одиниць ресурсів, що витрачені на виготовлення одиниці продукції, наведені в табл. 1 (цифри умовні).

Таблиця 1

Ціна реалізації одиниці продукції P₁ і P₂ відповідно, складає 2 і 3 грн.

Необхідно скласти такий план виробництва продукції, при якому прибуток від реалізації буде максимальним.

Складемо оптимізаційну модель задачі.

Позначимо x₁, x₂ – число одиниць продукції відповідно P1 і P2, запланованих до виробництва. Для їх виготовлення (табл. 1) буде потрібно (1* x₁+3* x₂) одиниць ресурсу S₁, (2_* x₁+1* x₂) одиниць ресурсу S₂, (1* x₂) одиниць ресурсу S₃, (3* x₁ ) одиниць ресурсу S₄. Оскільки споживання ресурсів S₁, S₂, S₃, S₄ не повинне перевищувати їх запасів, відповідно 18, 16, 5 і 21 одиниці, то зв'язок між споживанням ресурсів і їх запасами виражається системою нерівностей:

Змінні ненегативні x1³ 0, x2³ 0.

Сумарний прибуток F від реалізації продукції складе:

.

В математичній постановці задача формулюється таким чином. Позначимо X_j (j=1, 2., n) – число одиниць продукції P_j, запланованих до виробництва; b_i (i=1, 2., m) – запас ресурсу S_i; a_ij – число одиниць ресурсу S_i, затрачуваного на виготовлення одиниці продукції P_j (числа a_ij – коефіцієнти прямих витрат, які називають технологічними коефіцієнтами); c_j – прибуток від реалізації одиниці продукції P_j. Тоді математична модель задачі про використання ресурсів в загальній постановці прийме вигляд: знайти такий план X=(x₁, x₂,.x_n) випуску продукції, що задовольняє системі

і умові

,

при якому функція

приймає максимальне значення.

Задача оптимізації складання раціону (задача про дієту, задача про суміші). Є два види корму I і II, поживні речовини (вітаміни) S₁, S₂, S₃, що містять. Вміст числа одиниць поживних речовин в 1 кг кожного виду корму, необхідний мінімум поживних речовин наведений в табл. 2 (цифри умовні).

Таблиця 2

Вартість 1 кг корму I і II відповідно рівна 4 і 6 грн.

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

Складемо оптимізаційну модель задачі.

Позначимо x_{1 ,} x₂ – кількість кормів I і II, що входять в денний раціон. Тоді цей раціон включатиме (3* x₁+1* x₂) одиниць поживної речовини S₁, (1* x₁+2* x₂) одиниць речовин S₂, (1* x₁+6* x₂) одиниць поживної речовини S₃. Оскільки вміст поживних речовин S₁, S₂, S₃ в раціоні повинно бути не менше, відповідно 9, 8 і 12 одиниць, то отримаємо систему нерівностей:

(1)

Змінні ненегативні x₁³ 0, x₂³ 0.

Загальна вартість раціону F складе:

. (2)

Позначимо x_j (j=1, 2., n) – число одиниць корму n- го виду; b_i (i=1, 2., m) – необхідний мінімум вміст в раціоні поживної речовини S_i; a_ij – число одиниць поживної речовини S_ij в одиниці корму j-го вид; c_j- вартість одиниці корму j-го виду. Математична модель задачі складання раціону в загальній постановці прикмет вигляд. Знайти такий раціон X=(x1, x2., x_j,., x_n), що задовольняє системі:

і умові

,

при якому функція

приймає мінімальне значення.

Задача оптимізації використання потужностей (задача про завантаження обладнання, складання розкладу). Підприємству заданий план виробництва продукції за часом і номенклатурі: потрібен за час Т випустити n₁, n₂., n_k одиниць продукції P₁, P₂., P_k. Продукція виробляється на верстатах S₁, S₂., S_m. Для кожного верстата відома продуктивність a_ij і витрати b_ij на виготовлення продукції P_j на верстаті S_i в одиницю часу.

Необхідно скласти такий план роботи верстатів (тобто так розподілити випуск продукції між верстатами), щоб витрати на виробництво всієї продукції були мінімальні.

Позначимо x_ij – час, протягом якого верстат S_i – буде зайнятий виготовленням продукції Pj. Оскільки час роботи кожного верстата обмежено і не перевищує Т, то справедливі нерівності:

(3)

Для реалізації плану випуску по номенклатурі необхідно, щоб виконувалися наступні нерівності:

(4)

При цьому

(5)

Витрати на виробництво продукції виражаються функцією:

. (6)

Тоді математична модель задачі про використання потужностей в загальній постановці має вигляд: знайти таке рішення X=(x₁₁, x₁₂., x_mk), що задовольняє системам (3-4) і умові (5), при якій функція (6) приймає мінімальне значення.

Задача оптимізації розкрою матеріалів. Для виготовлення брусів завдовжки 1, 2 м, 3 м і 5 м в співвідношенні 2: 1: 3 на розпил поступають 195 колод завдовжки 6 м. Визначити план розпила, що забезпечить максимальне число комплектів. Можливі способи розпила колод, число отриманих при цьому брусів наведений в табл. 3.

Таблиця 3

Складемо оптимізаційну модель задачі.

Позначимо x_i – число колод, розпиляних i-м способом; x – число комплектів брусів. Враховуючи, що всі колоди повинні бути розпиляні, а число брусів кожного розміру повинне задовольняти умові комплексності математична модель задачі прийме вигляд:

при обмеженнях:

Тоді математична модель задачі про розкрій матеріалів в загальній постановці має вигляд:

Нехай кожна одиниця j-го матеріалу (j=1, 2., m) може бути розкроєний n різними способами, причому використання i-го способу (i=1, 2., n) дає a_ijk одиниць к-го виробу (k=1, 2., l), а запас j-го матеріалу рівний aj одиниць. xij – число одиниць j-го матеріалу, що розкроєний i-м способом.

Знайти таке рішення X=(x11, x12., x_nm), що задовольняє системі:

і умові xij ≥ 0, при якому функція F=x приймає максимальне значення.

Задача оптимізації транспортних витрат. Є три постачальники і чотири споживачі. Потужність постачальників і попит споживачів, а також витрати на перевезення одиниці вантажу для кожної пари «постачальник - споживач» зведені в таблицю постачань (табл. 4).

Таблиця 4

В лівому верхньому кутку довільної (i, j) клітки стоїть коефіцієнт витрат – витрати на перевезення одиниці вантажу від i -го постачальника до j-го споживача. Задача формулюється таким чином: знайти обсяги перевезень для кожної пари «постачальник - споживач» так, щоб: потужності всіх постачальників були реалізовані, попити всіх споживачів були задоволені, сумарні витрати на перевезення були би мінімальні. Позначимо через x_ij обсяг перевезення від i -го постачальника до j-го споживача. Задані потужності постачальників і попити споживачів накладають обмеження на значення невідомих x_{ij .} Щоб потужність кожного з постачальників була реалізована, необхідно скласти рівняння балансу для кожного рядка таблиці постачань:

Аналогічно, щоб попит кожного із споживачів був задоволений, подібні рівняння балансу складаються для кожного стовпця таблиці постачань:

Очевидно, що обсяг вантажу, що перевозиться, не може бути негативним, тому слід ввести обмеження невід’ємності змінних:

x_ij ≥ 0.

Сумарні витрати F на перевезення виражаються через коефіцієнти витрат таким чином:

Для математичної постановки транспортної задачі в загальній постановці позначимо через с_ij коефіцієнти витрат, через M_i – потужності постачальників, через N_j – потужності споживачів (i=1, 2., m)., (j=1, 2., n), m – число постачальників, n – число споживачів. Тоді система обмежень приймає вигляд:

(7)

Система (7) включає рівняння балансу по рядках і по стовпцях.

При це сумарна потужність постачальників рівна сумарній потужності споживачів, тобто

Цільова функція в даному випадку наступна:

(8)

Таким чином, на безлічі ненегативних рішень системи обмежень (7) знайти таке рішення, при якому значення цільової функції (8) буде мінімальне.

ТЕМА 8

БАЗИ ДАНИХ. ОСНОВНІ ПОНЯТТЯ. ХАРАКТЕРИСТИКА СУБД MS ACCESS. СТВОРЕННЯ ТАБЛИЦЬ У СУБД MS ACCESS.

Питання:

1. Основні поняття

2. Основи теорії реляційних баз даних

3. Створення баз даних. Етапи проектування.

4.Створення таблиць

Література:

1. Михеева В. Microsoft Access 2003// В. Михеева, И. Харитонова. – СПб: БХВ-Петербург, 2004. – 1072 с.

2. Гринченко Н.Н. Проектирование баз данных. СУБД Microsoft Access // Н.Н. Гринченко, Е. В. Гусев, Н. П. Макаров. – «Горячая Линия – Телеком», 2004. – 240 с.

3. Войтюшенко Н. М. Інформатика і комп’ютерна техніка. - Видавництво: Центр учбової літератури, 2009. – 564 с.

4. Хабрейкен Дж. Microsoft Office 2003: Word, Excel, Access, PowerPoint, Publisher, Outlook. Все в одном. – Изд-во «Вильямс», 2006. – 864 с.

5. Office 2003 for Dummies. - Издательство: Вильямс, серия: Для " чайников", 2007. - 336 с.

1. Основні поняття

Сучасні інформаційні системи характеризуються великими об'ємами даних, що зберігаються, їх складною організацією, а також високими вимогами до швидкості і ефективності обробки цих даних. Це стає можливим при використанні спеціальних програмних засобів - систем управління базами даних (СУБД).

Бази даних (БД) — це сукупність відомостей про реальні об'єкти, процеси, події або явища, що відносяться до певної теми або задачі, організована таким чином, щоб забезпечити зручне представлення цієї сукупності як в цілому, так і будь-якої її частини.

Система управління базами даннях – це комплекс програмних і мовних засобів, необхідних для структуризації інформації, розміщення її в таблицях, обробки баз даних і підтримки їх в актуальному стані.

Основні функції СУБД — це визначення даних (опис структури баз даних), обробка даних і управління даними. В даний час налічується більше 50 типів СУБД для персональних комп'ютерів. До найпоширеніших типів СУБД відносяться: MS SQL Server, Oracle, Informix, Sybase, DB2, MS Access тощо.

2. Основи теорії реляційних баз даних

Існують 4 основні моделі даних – списки (плоскі таблиці), реляційні бази даних, ієрархічні і мережні структури.

Протягом багатьох років переважно використовувалися плоскі таблиці типу списків в Excel, вся інформація яких містилася в одній таблиці, ніяк не зв’язаною з іншими таблицями, що розміщені в інших базах даних.

В ієрархічних моделях будь-який об’єкт може підпорядковуватися лише одному об’єкту вищого рівня, тобто описується як деревовидний граф.

У мережних моделях безпосередній доступ можливий до будь-якого об’єкту незалежно від рівня, на якому він знаходиться в моделі.

В даний час найбільше розповсюдження при розробці баз даних отримали реляційні моделі даних. Реляційна модель даних є сукупністю найпростіших двовимірних таблиць, що мають назву відношення (англ. relation) і забезпечують простоту, гнучкість структури, зручність реалізації. Цей спосіб організації даних дозволяє пов'язувати дані в одній таблиці з даними в інших таблицях через унікальні ідентифікатори (ключі) або ключові поля.

Реляційна база даних є безліччю взаємозв'язаних таблиць, кожна з яких містить інформацію про об'єкти певного типу.

Таблиця – регулярна структура, що складається із кінцевого набору одно типових записів.

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

Клас об'єктів - сукупність об'єктів, що володіють однаковим набором властивостей. Для кожного окремого об'єкту з даного класу об'єктів в таблиці створюється окремий запис.

Властивість (атрибут) - певна частина інформації про деякий об'єкт. Зберігається у вигляді стовпця (поля) таблиці.

Зв'язок (відношення) - спосіб, яким зв'язана інформація про різні об'єкти.

Кожний рядок таблиці включає дані про один об'єкт (наприклад, клієнті, автомобілі, документі), а стовпці таблиці містять різні характеристики цих об'єктів — атрибути (наприклад, найменування і адреси клієнтів, марки і ціни автомобілів). Рядки таблиці називаються записами; всі записи мають однакову структуру — вони складаються з полів, в яких зберігаються атрибути об'єкту. Кожне поле запису містить одну характеристику об'єкту і має строго певний тип даних (наприклад, текстовий рядок, число, дата). Всі записи мають одні і ті ж поля, тільки в них містяться різні значення атрибутів (рис. 9.1).

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

Рис.9.1 Структура таблиці реляційної бази даних

- в кожній таблиці БД не повинно бути полів, що повторюються;

- в кожній таблиці повинен бути унікальний ідентифікатор (первинний ключ);

- кожному значенню первинного ключа повинна відповідати достатня інформація про тип сутності або про об’єкт таблиці (наприклад, інформація про успішність, про групу або студентів);

- зміна значень в полях таблиці не повинна впливати на інформацію в інших полях (крім змін в полях ключа).

Ключ – це поле (або декілька полів), що додаються до таблиці і дозволяють встановити зв’язок з записами в іншій таблиці та однозначно визначають будь-який запис в таблиці реляційної бази даних.

Існують ключі двох типів: первинні та вторинні (або зовнішні).

Первиннийключ – це одно або декілька полів, комбінація значень яких однозначно визначають кожний запис в таблиці. Первинний ключ не допускає значень Null та завжди повинен мати унікальний індекс. Первинний ключ використовується для зв’язування таблиці з зовнішніми ключами і інших таблицях.

Зовнішній (вторинний) ключ - це одно або декілька полів (стовбців) в таблиці, що містить посилання на поле або поля первинного ключа в іншій таблиці. Зовнішній ключ визначає спосіб об’єднання таблиць.

З двох логічно зв’язаних таблиць одну називають таблицею первинного ключа або головною таблицею, а іншу таблицею вторинного (зовнішнього) ключа або підлеглою таблицею. СУБД дозволяють зіставити споріднені записи з обох таблиць і спільно вивести їх у формі, звіті або запиті.

Існує три типи первинних ключів: ключові поля лічильника, простий ключ або складовий ключ.

Поле лічильника (Тип даних «Лічильник»). Тип даних поля в базі даних, в якому для кожного запису, що додається таблицю, в поле автоматично заноситься унікальне числове значення.

Простий ключ. Якщо поле містить унікальні значення, такі як коди або інвентарні номери, то це поле можна визначити як первинний ключ. У якості ключа можна визначити будь-яке поле, що містить дані, якщо це поле не містить значення Null або значення, що повторюються.

Складовий ключ. У випадках, коли неможливо гарантувати унікальність значень кожного поля, існує можливість створити ключ, що складається з декількох полів. Частіше за все така ситуація виникає для таблиці, що використовується для скріплення двох таблиць «багато-до-багатьох».

Зв’язок встановлюється між двома загальними полями (стовбцями) двох таблиць. Існуючі зв’язки з відношенням «один-до-одного», «один-до-багатьох» та «багато-до-багатьох».

Відносини, що можуть існувати між записами двох таблиць:

1. один–до–одного, кожному запису з однієї таблиці відповідає однин запис в іншій таблиці;

2. один–до–багатьох, кожному запису з однієї таблиці відповідає декілька записів іншої таблиці;

3. багато–до–одного, множині записів з однієї таблиці відповідає один запис в іншій таблиці;

4. багато-до-багатьох, множині записів з однієї таблиці відповідає декілька записів в іншій таблиці.

Тип відношень у зв’язку, що створюється, залежить від способу визначення полів, що зв’язуються:

Відношення «один-до-багатьох» створюється в тому випадку, коли тільки одне з полів є полем первинного ключа або унікального індексу.

Відношення «один-до-одного» створюється в тому випадку, коли обидва полів, що зв’язуються, є ключовими або мають унікальні індекси.

Відношення «багато-до-багатьох» фактично є двома відношеннями «один-до-багатьох» з третьою таблицею, первинний ключ якої складається з полів зовнішнього ключа двох інших таблиць.