Державна податкова адміністрація України

Національний університет ДПС України 

ЛЕКЦІЇ

З ДИСЦИПЛІНИ

«ІНФОРМАЦІЙНІ СИСТЕМИ І технології В МЕНЕДЖМЕНТІ»

Лекції -28 годин;  Лаб. роботи -28 годин; Консульт. -10 годин; Інд. роб. -16 годин;

2009 – 2010 навчальний рік

м. Ірпінь

ЗМІСТ ДИСЦИПЛІНИ

ВСТУП

1. Інформаційні системи і технології

1.1. Основні поняття теорії систем

1.2. Інформаційні ресурси управління

1.3. Автоматизовані інформаційні системи

1.4. Інформаційні технології

1.4.1. Електронна обробка документів

1.4.2. Системи підтримки прийняття рішень

1.4.3. Експертні системи

1.4.4. Автоматизовані робочі місця

Контрольні питання до теми 1

Список літератури до теми 1

2. Корпоративні програмні системи

2.1. Корпоративні системи управління підприємствами

2.1.1. Планування матеріальних ресурсів (MRP)

2.1.2. Планування виробничих ресурсів (MRP II)

2.1.3. Планування ресурсів підприємства (ERP)

2.1.4. Корпоративні програмні продукти

2.2. Корпоративна програмна система «Галактика»

2.3. Інтегрована корпоративна система «SAP R/3»

2.4. Упровадження корпоративних систем

Контрольні питання до теми 2

Список літератури до теми 2

3. Аналіз табличних даних

3.1. Табличний процесор Excel

3.2. Робота зі списками і підведення підсумків

3.2.1. Підведення проміжних підсумків і консолідація даних

3.2.2. Побудова зведених таблиць

3.3. Фінансові функції

3.3.1. Функція БЗ

3.3.2. Функція КПЕР

3.3.3. Функція ПЗ

3.3.4. Функція ППЛАТ

Контрольні питання до теми 3

Список літератури до теми 3

4. Статистичний аналіз і прогнозування

4.1. Статистичний аналіз

4.1.1. Регресійний аналіз

4.1.2. Кореляційний аналіз

4.1.3. Дисперсійний аналіз

4.1.4. Статистичні функції і передбачення даних

4.2. Підбір параметрів і пошук розв’язання

4.2.1. Підбір параметрів

4.2.2. Пошук розв’язку та оптимізація

Контрольні питання до теми 4

Список літератури до теми 4

ВСТУП

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

Для боротьби з інформаційною кризою вчені та інженери запропонували використовувати обчислювальну машину, що одержала назву «комп'ютер». З наукового погляду комп'ютер це універсальний перетворювач інформації. Універсальність комп'ютера як перетворювача інформації визначається, по-перше, його здатністю переробляти і зберігати будь-яку інформацію, якщо вона представлена в «зручній» для нього двійковому вигляді. Але головне, що власне і складає причину універсалізму, зв'язане з програмованістю комп'ютерів. Саме ця властивість робить комп'ютер багатофункціональним і потенційно здатним до розв’язування будь-яких задач, а також визначає воістину всюдисущий характер їхнього застосування в сучасному суспільстві. За частки секунди комп'ютер може переключитися з виконання однієї програми на іншу, і в результаті буде розв’язуватися зовсім інша прикладна задача. Така швидка зміна функцій також не має аналогів в історії машинобудування, хоча ідея програмованих автоматів була висунута інженерами ще в 19 столітті.

По мірі впровадження комп'ютерів процес економічного росту в передових країнах починає усе більше здійснюватися за рахунок випереджального розвитку інформаційних технологій, тобто технологій, пов'язаних зі способами використання комп'ютерів для розв’язання  прикладних задач. Якщо на початку сторіччя в інформаційному секторі США було зайнято 5% населення, у промисловості 37 % і в сільському господарстві 42 %, то до початку ХХІ сторіччя в інформаційному секторі вже працювало 50 % населення, у промисловості 13% і в сільському господарстві 2 %.

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

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

Основний зміст даного навчального курсу пов'язаний з викладом і описом інформаційних систем і технологій для цілей управління (менеджменту). При цьому розглядаються організаційно-методичні основи створення АІС підприємства, питання застосування інформаційних технологій і розв’яння практичних задач менеджменту.

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

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

Основним завданням даного курсу є теоретична і практична підготовка студентів за наступними напрямками:

• Сутність інформаційних систем і їхнє значення в керуванні підприємствами;

• Методологія розробки корпоративних програмних систем, оцінка їхньої якості й ефективності;

• Планування, прогнозування й оптимізація в бізнесі з використанням табличного процесора;

• Основні методи роботи з базами даних;

• Основні методи керування проектами.

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

1. Інформаційні системи і технології

1.1. Основні поняття теорії систем

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

• система;

• керування;

• інформація.

Розглянемо їх по черзі. Слово «система» походить від грецького слова «systema», що неформально можна визначити в такий спосіб:

Для систем характерні наступні основні властивості:

1) цілісність (цілеспрямованість);

2) стійкість;

3) структурованість;

4) різноманітність;

5) складність.

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

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

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

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

Складність системи пов'язана з різноманіттям і рівнем складності вхідних у неї компонент, динамікою їх взаємодії між собою і зовнішнім середовищем.

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

Системи в самому загальному змісті є предметом вивчення окремої науки - «системології» чи загальної теорії систем [1]. Для нас надалі найбільший інтерес будуть представляти економічні системи, частковим видом яких є підприємства.

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

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

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

Найважливішими функціями (етапами), що виконує система управління, є такі:

• аналіз і прогнозування;

• планування;

• регулювання;

• облік і контроль.

Управлінським діям повинно передувати аналіз і прогноз розвитоку ситуації та формулювання цілі управління. Після цього формується план досягнення цілі та управлінські впливи, які ведуть до цього досягнення. При цьому необхідно враховуватиі контролювати стан матеріальних і трудових ресурсів і знову аналізувати особливості поточної ситуації. Усі перераховані етапи управління доводиться циклічно виконувати доти, поки не будуть досягнені цілі управління. Тому систему, у якій виконується процес управління, можна схематично зобразити у вигляді сукупності суб'єкта та об'єкта управління, зв'язаних між собою прямими і зворотними зв'язками [2]. Ця схема називається в кібернетиці фундаментальним циклом управління зі зворотним зв'язком.

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

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

• Інформація - це властивість (здатність) матеріальних об'єктів відображати в собі події та явища навколишнього середовища.

Формою існування інформації є рух. Постійно переміщуючись, і переходячи з одного виду в інший, наприклад, у суспільстві, вона задовольняє потреби людей у спілкуванні і спільній роботі. Власне кажучи немає жодної ланки людської діяльності, де б не зустрічалося «перетворення інформації» [3].

В інформаційній взаємодії завжди можна виділити наступні три складові:

1) джерело інформації;

2) передавальне середовище;

3) приймач інформації.

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

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

Підсумовуючи різні підходи до поняття інформації, можна дати наступне описове визначення.

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

1) повнота;

2) актуальність (своєчасність);

3) достовірність;

4) корисність (цінність).

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

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

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

1.2. Інформаційні ресурси управління

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

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

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

• великі обсяги;

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

• різноманітнісь джерел і споживачів.

Економічну інформацію можна класифікувати по цілому ряду ознак, наприклад, по функціях управління (планова, нормативна, облікова, аналітична і т.д.). Але для будь-якого підприємства найбільше значення має поділ інформації на внутрішню (службову) і зовнішню. До внутрішньої економічної інформації можна віднести зведення про склад трудових, матеріальних і грошових ресурсів, а також зведення про стан об'єкта управління на визначений момент часу. Звичайно службова інформація відбиває діяльність підприємства за допомогою натуральних і вартісних показників. До зовнішньої економічної інформації в умовах ринкової економіки можна віднести різні види комерційної і ділової інформації (біржові і валютні курсы, котирування цінних паперів, дисконтні ставки банків, інформація про інвестиції, ціни і т.д.), а також інформацію про ділових партнерів (підприємства, фірми, продукції, ціни, керівниках і т.п.). Разом внутрішня і зовнішня інформація складають основу інформаційного ресурсу будь-якого підприємства.

Структура економічної інформації досить складна і може включати різні комбінації інформаційних одиниць. У її структурному складі можна виділити наступні складові [8]:

• реквізити;

• показники;

• документи.

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

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

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

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

1.3. Автоматизовані інформаційні системи

Отже, для управління підприємством потрібна систематизована заздалегідь підготовлена інформація. Поки інформаційні потоки всередині системи були невеликі,  керівники (менеджери) досить легко справлялися зі своїми обов'язками за допомогою традиційної паперової технології обробки інформації. Досить згадати комірні книги в Росії XIX століття. Але в середині XX століття відбулося стрімке зростання усіх видів інформації, що зробив практично неможливим традиційну паперову технологію управління підприємством. Тому основною метою створення автоматизованої інформаційної системи на будь-якім підприємстві є виробництво інформації за допомогою технічних засобів (комп'ютерів).

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

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

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

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

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

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

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

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

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

Ще одним досягненням архітектури клієнт-сервер став перехід до роботи з загальними програмами. У цьому випадку велика і складна програма, наприклад бухгалтерська чи програма управління деяким бізнес процесом, може працювати тільки на сервері, а в клієнта буде працювати лише невеликий модуль цієї програми, що і здійснює зв'язок з основною програмою. Таким чином, з'являється поняття сервера додатків, тобто сервера, на якому працюють загальні прикладні програми. Помітимо, що в локальній мережі може бути декілька серверів баз даних і серверів додатків. Крім того, той самий апаратний сервер може виступати і як сервер баз данных і як сервер додатків.

У цілому до складу автоматизованої інформаційної системи (АІС) входять наступні компоненты:

1) апаратне забезпечення;

2) програмне забезпечення;

3) інформаційне забезпечення (бази даних);

4) методичне і технологічне забезпечення;

5) персонал (користувачі й адміністратори).

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

Методичне і технологічне забезпечення регламентує методи і способи використання комп'ютерної системи на даному підприємстві з урахуванням його специфіки. Процес управління в умовах функціонування АІС повинен ґрунтуватися на економічних, організаційних і математичних моделях, що більш-менш адекватно відбивають структуру і динаміку роботи даного підприємства. Розробка таких моделей звичайно проводиться на етапі передпроектных досліджень і на етапі впровадження АІС, що вимагає залучення кваліфікованих фахівців з консалтингових фірм. Крім того, сюди ж може бути віднесене математичне забезпечення, основу якого складає сукупність математичних моделей, використаних при розробці програмного забезпечення. Найчастіше математичне забезпечення не постачається разом з комерційними продуктами і залишається комерційною таємницею розроблювача. Велика частина технологічного забезпечення приходиться на документацію, що регламентує роботу кінцевих користувачів. Сам термін «автоматизована» на відміну від «автоматична» припускає участь людини в процесі обробки інформації. Таким чином, ми приходимо до наступного трактування автоматизованих систем.

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

Впровадження АІС може змінити характер роботи не тільки виконавчого персоналу, але і привести до необхідності перегляду всього бізнесу (реінжиніринг бізнес процесів). Тому процес впровадження АІС є досить хворобливим у психологічному плані, до того ж він вимагає чималих витрат на оплату праці консультантів по системному аналізу, що займаються розробкою нових схем управління (менеджмент-консалтинг). Без перегляду бізнес процесів, інформаційна система може просто не заробити і замість прибутків можна одержати одні збитки.

У висновку цього розділу зробимо кілька термінологічних зауважень. Оскільки функція управління є основною на будь-якім підприємстві, то АІС називають також автоматизованими інформаційними системами управління (АІСУ) чи автоматизованими інформаційними системами менеджменту (АІСМ). Можна зустріти також абревіатури ІСУ і ІСМ, коли слово «автоматизована» опускають. Усі ці терміни можна вважати до визначеного ступеня синонімами поняття автоматизована інформаційна система, що, у той же час, крім процесу управління підтримує на сучасних підприємствах усі види обробки інформації для усіх виробничих процесів.

1.4. Інформаційні технології

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

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

З цього погляду «інформаційні технології» можна розуміти як способи застосування (використання) комп'ютерних систем на підприємствах, чи як уміння виділяти корисний інформаційний «сигнал» із загального інформаційного «шуму». ЮНЕСКО характеризує інформаційні технології таким чином:

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

Інформаційні технології (ІТ) можна класифікувати по ряду ознак, зокрема, у таблиці 1 дана їхня класифікація по ступеню підтримки задач управління в автоматизованих системах [4].

Таблиця 1. Класифікація інформаційних технологій

1.4.1. Електронна обробка документів

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

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

Визначеним кроком у розв’язанні цієї проблеми можна вважати появу спеціалізованих програмних серверів типу Microsoft Exchange і Lotus Notes, що призначені для централізованого збереження документів. До особливостей цих серверів можна віднести ієрархічну організацію даних, що складається з загальнодоступних папок різного рівня. У папках можуть зберігатися повідомлення, отримані по електронній пошті, електронні форми і звичайні файли документів. У ці сервери вбудовуються механізми захисту від несанкціонованого доступу до документів, поштові агенти, що обробляють повідомлення з поштової скриньки, служба обробки подій і т.д. З папками можна асоціювати спеціальні карти, у яких описуються різні сценарії обробки документів. Для взаємодії із сервером Microsoft Exchange у клієнтів на робочих станціях установлюється програма Microsoft Outlook. Усе це створює передумови для появи спеціалізованих систем управління документообігом, і такі програми уже з'явилися.

На базі сервера Microsoft Exchange російська фірма «Оптима» розробила комплексну систему управління потоками робіт і документообігу «Optima-Workflow”. Ця система забезпечує: маршрутизацію документів, засоби опису сценаріїв обробки документів, засоби шифрування й електронні підписи, контроль виконання робіт і виконання доручень, контроль версій документа і т.д.

1.4.2. Системи підтримки прийняття рішень

До наступного виду технологій можна віднести системи підтримки прийняття рішень (СППР). Ці системи виникли на початку 70-х років минулого століття, і їхня назва походить від англійського терміна “Decision Support Systems” (DSS). Можна дати наступне неформальне визначення СППР.

Для цих систем характерне широке використання економіко-математичних методів і моделей, програм аналізу і прогнозу, складання бізнес-планів, бази даних і бази моделей [5]. Основна мета в створенні СППР полягає в наданні допомоги менеджеру в розумінні й оцінці виниклої ситуації, структуризації проблеми, формування критеріїв і виявлення переваг. До задач, що розв’язуються за допомогою СППР, відносяться:

• підготовка стратегічних і тактичних планів;

• прогнозування й аналіз збуту;

• оцінка ситуації і розподіл ресурсів;

• розрахунки бюджетів.

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

Перелік комерційних СППР нараховує сотні назв. Прикладами СППР можуть служити: програма корпоративного планування «Симплан», маркетингова програма PIMS, маркетингова програма Marketing Expert, програма формування портфеля замовлень ISDS, програма фінансового планування IFPS, програма інвестиційного моделювання Project Expert [6]. Розглянемо коротко склад і структуру двох з них.

Система «Симплан» (Simplan) була створена в середині 70-х років минулого століття, щоб допомогти директорам підприємств у питаннях корпоративного планування. Вона містить три класи моделей: фінансові моделі, моделі маркетингу і моделі виробництва. Фінансові моделі служать для аналізу ефективності різних варіантів фінансового планування. Маркетингові моделі служать для оцінки майбутнього обсягу ринку, яким може заволодіти компанія. Моделі виробництва використовуються для оптимального планування майбутнього виробництва, оцінки пов'язаних з цим витрат, оцінки матеріальних запасів, формування кількісних і якісних вимог до робочої сили, оцінки вартості сировини, оцінці потужності виробничої бази й устаткування і т.д.

Пакет програм Project Expert був розроблений російською фірмою «Проинвест Консалтинг» наприкінці 80-х років минулого століття для аналізу ефективності інвестиційних проектів на базі імітаційних моделей грошових потоків. В даний час ця програма власне кажучи вийшла за рамки СППР і містить у собі деякі функції програм управління проектами, що будуть розглянуті в наступних розділах. Об'єднуючим у системі є модуль «Інвестиційний план», у якому складається сітковий графік проекту з описом всіх етапів роботи. Система також містить блок збуту продукції, блок оцінки виробничих витрат і блок фінансування (капіталу), у якому визначаються джерела зовнішнього і внутрішнього фінансування. Програма дозволяє провести детальний фінансовий аналіз проекту з урахуванням впливу на нього загальноекономічних факторів таких, як інфляція, курс валют, структура виробничих витрат, вартість робочої сили, вартість основних фондів, змін у податковій системі і т.д. Програма здійснює розрахунок фінансових показників ефективності інвестицій, що відповідають міжнародним стандартам. Крім того, виробляється розрахунок показників фінансового стану підприємства таких, як рентабельність, ліквідність, платоспроможність і т.д. [7].

1.4.3. Експертні системи

Експертні системи (ЕС) виникли в середині 70-х років минулого століття і призначалися для інформаційної підтримки роботи фахівців аналітиків [8]. Ці системи використовували останні досягнення в області інженерії знань і штучного інтелекту. Можна привести наступний неформальний опис цих систем:

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

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

Перші приклади успішного застосування експертних систем були пов'язані з медициною і хімією. Проект DENDRAL [8] був початий у 1965 році. У ньому була висунута фундаментальна ідея експертних систем – інженерія знань, тобто представлення великих обсягів спеціальних евристичних знань у формі, що була б зручна для їхньої автоматизованої обробки. Ця програма могла пророкувати молекулярну структуру хімічної речовини за даними мас-спектрографічного аналізу. За нею пішла програма MYCIN, призначена для діагностики інфекційних захворювань і ряд інших систем. 

У той же час, очевидно, що сфера управління істотно відрізняється від інших предметних областей тим, що тут набагато сутужніше добути евристичні знання і ще складніше їх формалізувати. Тому в цій області було розроблено порівняно небагато ЕС. Прикладами тут можуть служити програма XCOM, розроблена на замовленням фірми DEC, що допомагає вибрати конфігурацію комп'ютерної системи по заданій специфікації. Система PSY, розроблена російською фірмою «Сайнтекс», використовується на підприємствах і в кадрових агентствах для аналізу психологічної і професійної сумісності працівників. Психологічні знання, тобто знання експертів психологів, представляються в системі у вигляді логічних правил (близько 6 тисяч) і використовуються при тестових обстеженнях кандидатів на одержання роботи. Система демонструє високу точність при оцінці рівня здібностей кандидатів, тому вона широко застосовується в російських комерційних фірмах.

1.4.4. Автоматизовані робочі місця

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

Найбільш простою функцією, реалізованою в АРМ, було інформаційно-довідкове обслуговування. Крім того, на АРМах працюють різні прикладні програми, системи управління базами даних, СППР, експертні системи, і т.п. АРМи можуть підключатися як до головного сервера підприємства через локальну мережу, так і до зовнішніх інформаційних служб, наприклад, через мережу Інтернет. Іншими словами, програмне забезпечення АРМ перетворює звичайний комп'ютер чи робочу станцію в робоче місце фахівця. Таким чином, АРМ стає важливим елементом інфраструктури підприємства і складовою частиною його основних фондів і устаткування.

Як приклади можна привести АРМ «Управління цінними паперами» для банків, АРМ «Аналіз договорів, замовлень, контрактів» для юристів, АРМ-аналітик, Арм-статистик та ін. [4].

Контрольні питання до теми 1

1. Поясніть зміст понять «система», «управління », «інформація».

2. Які основні етапи процесу управління?

3. Чому інформація виступає в ролі управлінського ресурсу?

4. Що являє собою архітектура клієнт-сервер?

5. Опишіть основні компоненти АІС.

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

7. В чому полягає соціально-економічна ефективність АІС?

8. Як використовуються інформаційні технології в управлінні?

9. Що таке електронна обробка документів?

10. Для чого потрібні системи підтримки прийняття рішень?

11. Що можна робити за допомогою експертних систем?

12. Які задачі повинні розв’язуватися безпосередньо на робочому місці менеджера?

Список літератури до теми 1

1. Берталанфи Л. Общая теория систем – критический обзор. // В кн. Исследования по общей теории систем. – М.: Прогресс, 1969. С. 23 – 82.

2. Винер Н. Кибернетика или управление и связь в животном и машине. – М.: Советское радио, 1968. – 326 стр.

3. Глушков В.М. Кибернетика: вопросы теории и практики. – М.: Наука, 1986. – 477 стр.

4. Козырев А.А. Информационные технологии в экономике и управлении. – СПб., Изд-во Михайлова, 2000.

5. Ситник В.Ф. та ін. Системи підтримки прийняття рішень -  К.: Техніка, 1995.

6. Вовчак І.С. Інформаційні системи та комп'ютерні технології в менеджменті. - Тернопіль: “Карт-бланш”, 2001.

7. Гужва В.М. Інформаційні системи і технології на підприємствах. - К.: КНЕУ, 2001.

8. Уотермен Д. Руководство по экспертным системам.  – М.: Мир, 1989.

2. Корпоративні програмні системи

2.1. Корпоративні системи управління підприємствами

Як відмічалося в попередній лекції, корпоративні програмні системи відносяться до прикладного програмного забезпечення АІС і складають власне кажучи його ядро.

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

У 70-80-х роках минулого століття академік В. М. Глушков, увів поняття автоматизованої системи управління підприємством, і відмічав, що до впровадження АСУП підприємство повинне готуватися заздалегідь, оскільки не можна ефективно автоматизувати безладдя [1]. Очевидно, що упорядкованість робочих і управлінських процесів є найважливішою передумовою їхньої ефективної автоматизації і зменшує психологічне навантаження на персонал при переході на автоматизовані процедури управління.

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

Для західних компаній стало очевидним, що ядром КПС повинна бути нова методика управління виробництвом, регульована новими промисловими стандартами. І такі промислові стандарти були розроблені і прийняті до дії в багатьох західних компаніях, що почали впровадження інформаційних систем. Це стандарти MRP (Material Requirements Planning), MRP II (Manufacturing Resource Planning) і ERP (Enterprise Resource Planning) [2]. Розглянемо коротко кожний з них.

2.1.1. Планування матеріальних ресурсів (MRP)

На початку 60-х років минулого століття комп'ютери почали використовуватися для планування виробничих процесів. Необхідність автоматизації планування була викликана дисбалансом постачання комплектуючих, що приводило або до зайвих складських запасів, або до зупинки виробництва в зв'язку з їхньою відсутністю. Крім того, виникали труднощ з обліком і відстеженням комплектуючих деталей у готовій продукції, що згодом ускладнювало контроль її якості. Усе це сприяло появі методології MRP, що в дослівному перекладі означає планування потреб у матеріалах. Реалізація цієї методології передбачала автоматизоване оптимальне регулювання чисельності комплектуючих деталей у виробничому процесі за допомогою спеціальної MRP-програми. MRP-методологія дотримується двох важливих  принципів:

• Логіки «залежного попиту», тобто потреба у виробі визначає попит на компоненти;

• Поставляти необхідні компоненти як можна пізніше, щоб зменшити рівень запасів. 

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

Успіх методики MRP привів до появи тісно з нею пов'язаної методики CRP (Capacity Requirements Planning), у якій основна увага приділялася розподілу виробничих потужностей по циклах виробництва протягом планового періоду. У процесі роботи CRP-програми розроблявся план завантаження виробничого устаткування для виконання кожного виробничого циклу. Встановлювався також технологічний план послідовності виробничих процедур для одержання готової продукції в запланованому обсязі. Якщо програмі вдавалося виконати план, то дані з CRP-програми передавалися в MRP-систему для організації реального виробництва. 

Таким чином, MRP-системи стали прообразом сучасних систем управління підприємством. По суті MRP-методика вперше встановлювала (специфікувала) у вигляді промислового стандарту, які автоматизовані процеси управління й обліку повинні бути реалізовані на підприємстві й у якій послідовності вони повинні виконуватися.

2.1.2. Планування виробничих ресурсів (MRP II)

На початку 80-х років минулого століття з'явилася концепція MRP II (Manufacturing Resource Planning), у якій об'єктами планування стали вже всі виробничі ресурси підприємства, починаючи від закупівлі сировини, і закінчуючи відвантаженням готової продукції. У цілому ця методологія, і розроблений на її основі стандарт, були націлені на планування діяльності підприємства в натуральних показниках і фінансове планування у вартісних показниках (грошовому вираженні). Стандарт передбачає 16 груп функцій, яким повинна задовольняти MRPII-система - від планування продажів до оцінки результатів діяльності. Велика увага тут приділялася моделюванню виробничих і невиробничих процесів, що стало розвитком концепції CRP-систем.

Розглянемо коротко основні компоненты системи, побудованої за стандартом MRP II.

• Модуль бізнес планування дозволяв формувати план виробництва на верхньому рівні;

• Модуль планування попиту відповідав за прогнозування попиту на визначений період;

• Модуль планування продажів і виробництва перетворював бізнес план у план продажів і виробництва основних видів продукції і будувався план-графік випуску продукції;

• Модуль планування потреб у матеріальних ресурсах визначав кількість і терміни постачання матеріальних ресурсів, необхідних для випуску продукції;

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

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

Експлуатація MRP II-систем виявила їхні слабкі сторони, що зводяться до таких позицій:

• управління підприємством на основі тільки існуючих замовлень утруднювало середньострокове, а тим більше довгострокове планування. Хоча в системі був модуль прогнозування попиту, він не використовувався безпосередньо в плануванні виробництва;

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

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

Усе це привело до необхідності подальшого розвитку цієї концепції.

2.1.3. Планування ресурсів підприємства (ERP)

Згодом з'явилася нова концепція в управлінні підприємством ERP (Enterprise Resource Planning). Системи цього типу в значно більшій мірі орієнтовані на роботу з фінансовою інформацією для розв’язання задач управління великими корпораціями з територіально розподіленими ресурсами. Загалом ERP-систему можна розглядати як інтегровану сукупність наступних основних підсистем:

• Управління фінансами;

• Управління матеріальними потоками;

• Управління виробництвом;

• Управління проектами;

• Управління сервісним обслуговуванням;

• Управління якістю;

• Управління персоналом.

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

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

Подальшим розвитком методології ERP стала концепція CSRP (Customer Synchronized Resource Planning), що в дослівному перекладі означає планування ресурсів (підприємства), синхронізоване зі споживачем. Основа цієї концепції і її головна перевага полягає в інтеграції споживача в процес виробництва. І це логічно, адже виробництво існує для споживача й в умовах ринкової економіки треба, щоб хтось купив продукцію і спожив її. Тут споживач може втручатися навіть у виробничий ритм, якщо його вимоги до кількості чи якості продукції змінилися, оскільки в цих системах процес планування виробництва має щоденний цикл, і вимоги споживача враховуються в реальному часі. У цих системах, нарешті, був переборений споконвічний бар'єр класичних систем управління і планування, коли інформація про споживача була локалізована в невеликій кількості підрозділів компанії і не приймалася до уваги на виробничих ділянках. Крім того, відділ продажів одержав можливість впливати не тільки на виробничі ділянки, але і на роботу конструкторських відділів, у яких звичайно розробляється майбутня продукція.

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

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

Здавалося, що західні компанії знайшли те, що шукали, але пошук нових шляхів продовжується. У середині 90-х років минулого століття з'явилася ще одна концепція ARP (Advanced Resource Planning), з якою студенти можуть ознайомитися самостійно [2].

2.1.4. Корпоративні програмні продукти

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

Вартість сучасних КІС в основному визначається вартістю корпоративних програмних систем. Як образно виразився В.М. Глушков, вартість програмного й апаратного продукту будуть співвідноситися, як товар і упакування до нього. Досить помітити, що вартість апаратури для середньої комп'ютерної мережі в даний час складає кілька десятків тисяч доларів, а вартість КПС може доходити до мільйона доларів. Звичайно, настільки дорогі системи можуть собі дозволити купувати тільки великі корпорації. Тому постачальники КПС усіляко намагаються будувати свої системи по модульному принципу, що дозволяє здобувати і впроваджувати їх поступово (поетапно) навіть невеликим фірмам. В цілому нічого дивного у високій вартості КПС нема, оскільки управлінський і діловий інтелект схований саме в програмах.

Таким чином, основними характеристиками корпоративних програмних систем є:

• комплексна інформаційна підтримка усіх функцій управління;

• упорядкування ділових процесів;

• модульний принцип побудови програмної системи;

• адаптивність і гнучкість настроювання структури системи до особливостей підприємства;

• розвиток системи після її впровадження.

Три останні властивості стали обов'язковими для всіх сучасних КПС, розроблювачі яких ведуть гостру конкурентну боротьбу на ринку програмних систем. Ринок корпоративних програмних продуктів дуже різноманітний, і розібратися в ньому непросто. У наступній таблиці (табл. 2) продукти розділені на чотири класи у відповідності із широтою охоплення бізнес функцій, підтримуваних даним продуктом [2].

До першої групи віднесемо фінансово-управлінські (ФУ) системи, що власне кажучи є обліковими бухгалтерськими програмами. У цьому секторі на нашому ринку домінує пакет «1С Підприємство» російської фірми 1С в основному завдяки своїй дешевині і доступності. До його переваг варто віднести гнучкість настроювання до особливостей підприємства, зрозумілість інтерфейсу (для бухгалтера), швидкий прогрес версій. Зокрема, появу SQL версії для роботи з загальною базою даних можна розглядати як перший крок убік інтегрованих продуктів [3].

Таблиця  2. Класифікація корпоративних програмних продуктів

Український продукт «АБ Офіс 2000», розроблений львівською фірмою «АБ Система», по багатству і пропрацьованності інтерфейсу, а також по кількості реалізованих функцій (продаж, реалізація, послуги і т.д.) не поступається системі «1С» [4]. До недоліків цієї програми можна віднести відсутність бухгалтерських методик її використання, чим, очевидно, і пояснюється відставання по обсягах продажів. У цілому в таблиці 2 наведена лише невелика кількість продуктів з цієї самої численної групи (більш 100 найменувань). Термін упровадження програм цього класу досить короткий (від 1 до 3 місяців), а ціна знаходиться в діапазоні від 200 до 5000 доларів.

Другу групу малих інтегрованих систем (ІС) вже можна назвати виробничими системами. Продукти цієї групи звичайно відповідають стандарту MRP II і частково стандарту ERP. Цікаве майбутнє очікує, очевидно, систему «Axapta», що недавно придбала фірма Microsoft у датської фірми Navision, розроблювача цієї системи. Для систем цього класу характерне поетапне впровадження терміном до півроку і вартість у діапазоні від 5 до 30 тисяч доларів для російських і вітчизняних систем і від 15 до 100 тисяч доларів для західних систем у залежності від конфігурації (кількість ліцензій на робочі місця). Більш детально особливості цього класу будуть розглянуті в наступному розділі на прикладі системи «Галактика».

Третя група середніх інтегрованих систем найменше представлена на українському ринку, оскільки по вартості ці продукти наближаються до великих систем (від ста до двохсот тисяч доларів), а по функціях мало відрізняються від систем попередньої групи. Проте, система «Miracle 5» має декілька успішних впроваджень на наших підприємствах [5]. Продукти американської фірми Platinum Software також відомі в нас і використовуються для автоматизації фінансового й управлінського обліку, бюджетного планування, торгівлі, маркетингу і продажів. Існують версії цього продукту для малих і середніх підприємств. Продукти цієї групи звичайно відповідають стандарту ERP і впроваджуються поетапно терміном від напівроку до року.

Четверта група великих інтегрованих систем розрахована на великі холдингові компанії, фінансово-промислові групи і транснаціональні компанії.  Це не означає, звичайно, що одна програмна система може задовольнити потреби величезного підприємства, але, безумовно, ці продукти складають інформаційне ядро системи. Усі програмні продукти інших постачальників повинні бути сумісні з цим ядром, у противному випадку система перестає бути інтегрованою. Продукти цієї групи звичайно відповідають стандарту ERP і частково більш новим стандартам, зокрема, CSRP. Для цих систем характерне складне поетапне впровадження терміном до одного року і більше. Їхня вартість знаходитися в діапазоні від 100 тисяч до 1 мільйона доларів у залежності від конфігурації. Більш детально особливості цього класу будуть розглянуті в одному з наступних розділів на прикладі системи «R/3» німецької фірми SAP AG.

2.2. Корпоративна програмна система «Галактика»

Корпоративна система «Галактика» була розроблена однойменною російською фірмою в 1995 р. і дотепер інтенсивно розвивається, маючи у своєму активі більш 400 впроваджень. Програмна система забезпечує комплексну підтримку більшості задач, що виникають на різних стадіях управлінського циклу. Ця система реалізована в архітектурі клієнт-сервер і функціонує на базі сучасних серверів баз даних (Microsoft SQL і Oracle). Система може взаємодіяти зі спеціалізованим програмним забезпеченням, у тому числі і з програмами управління технологічними процесами.

Концепція системи «Галактика» заснована на наступних положеннях [6]:

• реалізація всіх типових виробничо-економічних функцій;

• настроювання на специфіку господарської діяльності даного підприємства;

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

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

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

Система «Галактика» відповідає стандарту MRP II і складається із шести основних  функціональних контурів (див. рис. 2 ):

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

• «Бухгалтерський облік». Складає функціонально повну підсистему ведення обліку від формування платіжних документів до розрахунку фактичних витрат;

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

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

• «Логістика». Автоматизує бізнес процедури, пов'язані з веденням договорів, контрактів, а також веде оперативний облік діяльності по постачанню і збуту;

• «Управління персоналом». Автоматизує управління штатним розкладом, веде особисті справи співробітників, планує і враховує робочий час, нараховує заробітну плату. 

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

У результаті впровадження й експлуатації системи «Галактика» на багатьох підприємствах вдається домогтися економічної ефективності. Наприклад, корпорацією «Галактика» зібрані наступні статистичні дані в процесі впровадження системи [6]:

• Зниження витрат підприємства в діапазоні 10–15 відсотків від річного обороту (у залежності від галузі й масштабів);

• Скорочення термінів руху оборотних коштів до 12 відсотків;

• Рівень неліквідних запасів на складі знижується на 15-20 відсотків.

За даними корпорації на Інгулецькому ГОКе система «Галактика» окупилася за півроку в основному за рахунок цінних відходів, що до цього просто не враховувалися.

2.3. Інтегрована корпоративна система «SAP R/3»

Інтегрована корпоративна програмна система «R/3» є світовим лідером по кількості продажів. На 2000 р. розроблювач системи «R/3» німецька фірма SAP AG мала по всьому світу близько 12 тисяч інсталяцій, з них близько 100 у СНД. Система відповідає стандарту ERP і продовжує інтенсивно розвиватися. В даний час фірма SAP збирається перемістити її з «вертикального» ринку на «горизонтальний», тобто зробити «операційною системою нового покоління», яка б установлювалась як стандартна система управління підприємством разом із системним програмним забезпеченням.

Система «R/3» також має модульну архітектуру і складається з таких основних модулів [7]:

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

• «Контролінг», що забезпечує облік витрат і прибутків підприємства по місяцях, по замовленнях, по проектах, контролінг економічної діяльності підприємства;

• «Управління основними засобами», призначений для бухгалтерського обліку основних засобів і управління їх технічним станом, нарахування амортизації, організації техобслуговування і ремонту устаткування, моніторингу інвестицій і продажів активів;

• «Планування виробництва», що забезпечує планування, організацію і контроль виробничої діяльності підприємства, специфікацію продукції, технологічні карти її виготовлення, планування збуту, планування потреб у матеріалах;

• «Управління матеріальними потоками», призначений для управління запасами, закупівлі матеріалів, сировини і комплектуючих, управління складами й оцінки запасів;

• «Управління якістю», що містить підсистему управління якістю. Він підтримує планування якості, перевірку і контроль якості виготовленої чи купленої продукції;

• «Управління проектами», що підтримує планування, управління і моніторинг довгострокових проектів. Він забезпечує контроль фінансових ресурсів, контроль якості і т.д.;

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

• «Управління інформаційними потоками», що здійснює зв'язок прикладних модулів із зовнішніми прикладними і системними програмами, у тому числі з модулями САПР (система автоматизованого проектування) і керує інформаційними потоками робіт (workflows). Цей модуль включає багатофункціональну офісну систему, з убудованою електронною поштою, і систему управління документообігом.

Як видно, по складу модулів система «R/3» не дуже відрізняється від попередньої системи, проте, при більш близькому знайомстві система вражає своїми розмірами. Вона доступна практично на всіх апаратних і програмних платформах, включаючи UNIX, AS/400, Windows NT і т.д. Як сервери баз даних з нею можуть працювати Oracle, Microsoft SQL, DB2 і т.д. Для зв'язку з іншими додатками в ній були розроблені спеціальні програмні інтерфейси BAPI (Business Application Programming Interface).

В даний час компанія SAP AG має спеціальні галузеві рішення в області авіаційного, автомобільного, машинобудівного, хімічного та інших видів промисловості, а також у невиробничій сфері (банки, страхування, державні органи, роздрібна торгівля і т.д.) [8].

Для користувачів і консультантів компанії був розроблений спеціальний модуль Business Engineer, що входить у стандартний комплект постачання системи і дозволяє проводити бізнес-інжиніринг ділових процесів одночасно з упровадженням системи [9]. У його склад входять:

• бізнес-конфігуратор, що дозволяє створювати моделі підприємства;

• модель упровадження системи «R/3», що містить моделі процесів, моделі даних і моделі бізнес процесів;

• репозитарій «R/3», що представляє собою базу даних, у якій зберігаються модель підприємства і модель упровадження системи.

Для скорочення термінів упровадження системи «R/3» була розроблена спеціальна програма прискореного впровадження, що одержала назву Accelerate SAP (ASAP). Ця програма містить у собі наступні компоненты:

• маршрутна карта (roadmap), що визначає послідовність дій по впровадженню системи;

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

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

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

2.4. Упровадження корпоративних систем

Для успішного впровадження корпоративної системи на підприємстві повинні бути створені відповідні умови. Зупинимося коротко тільки на самих принципових моментах.

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

Друге питання пов'язане з тим, за що і скільки платити. На жаль культурні (а вірніше некультурні) традиції вітчизняних бізнесменів і чиновників привели до того, що  консультації (а донедавна і ПО) не включаються до кошторису витрат при закупівлі інформаційної системи. Але саме консалтинг вимагає великих інтелектуальних зусиль для впровадження продукту. Схема фінансування великих проектів по автоматизації звичайно зводиться до того, що велика частина витрат йде на устаткування, небагато на ПО і нічого на консалтинг. У результаті підприємство залишається без програмного і методичного забезпечення, тобто без двох найважливіших компонентів інформаційної системи. У той же час, за оцінками західних експертів, середня західна фірма витрачає на корпоративну програмну систему близько 30% усієї суми, стільки ж складають витрати на її впровадження (в основному консалтинг і перенавчання персоналу) і 40%  що залишилися це устаткування, мережа і системне ПО. В західних компаніях «прийнято» витрачати на автоматизовану інформаційну систему до 10% свого річного обороту. Крім того, все більшу актуальність набуває розрахунок вартості володіння (вартості експлуатації) інформаційної системи, оскільки багато фірм, у тому числі і Microsoft, вимагають періодичних платежів за ліцензії і програмні продукти. Цілком можливо, що незабаром за користування програмами прийдеться платити так само, як за електроенергію.

Нарешті, третє питання пов'язане з економічним ефектом від впровадження інформаційної системи і терміном її окупності. Звичайно вважають, что інформаційні проекти носять витратний характер, оскільки вимагають великого вкладення коштів, а негайного ефекту не дають. Тому в нас систему купують не для того щоб краще працювати, а тому, що «так усі роблять». У західних компаніях строк окупності цих систем також виявився досить великим (у середньому 5-6 років), але, там йдуть на витрати тому, що «інакше зараз працювати не можна». З цього погляду вклади в інформаційну систему варто розглядати, перш за все, як вклади в інфраструктуру підприємства. Адже коли будується новий цех, ніхто не може відразу сказати, який прибуток він принесе. Крім чисто економічного ефекту впровадження інформаційної системи має і великий соціальний ефект, оскільки це вкладення у свій власний персонал, у підвищення його кваліфікації. Таким чином, впроваджувати інформаційну систему має сенс тоді, коли підприємство дозріло для переходу на якісно новий рівень роботи і цей якісний рівень не можна вимірювати тільки кількісними мірками. Дійсно, як виміряти в грошах зменшення часу прийняття рішень, одержання раніше недоступної інформації, оптимізацію бізнес процесів? Крім того, якщо мале підприємство може дозволити собі задовольнятися мінімальним прибутком без всякої автоматизації в надії на те, що завтра в нього все рівно буде «хліб з олією», то велике підприємство собі цього дозволити не може, оскільки завтра воно може залишитися взагалі без прибутку.

На закінчення розглянемо коротко основні етапи впровадження корпоративних систем. Фахівці компанії Sterling Group виділяють тут наступні етапи:

• Дослідження (інжиніринг) підприємства і створення його економічної моделі;

• Аналіз і алгоритмізація фінансових і господарських процесів;

• Створення прототипу інформаційної системи на базі моделі підприємства;

• Створення технічної інфраструктури (корпоративна обчислювальна мережа);

• Установка і запуск корпоративної програмної системи;

• Супровід системи.

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

І все-таки ключову роль при впровадженні КПС відіграє не інформаційний відділ, а вище керівництво компанії, оскільки тільки воно може бути «ініціатором» і «локомотивом» реформ, і тільки воно може забезпечити керованість процесу впровадження.

Контрольні питання до теми 2

1. Які функції виконують корпоративні програмні системи (КПС)?

2. Що означають скорочення MRP, MRP 1 і ERP?

3. Які основні варіанти постачання системи пропонує корпорація “Галактика”?

4. З яких основних модулів складається КПС “Галактика”?

5. Які контури управління підтримує система “Галактика”?

6. Які основні функції менеджменту підтримує система SAP «R/3»?

7. За допомогою чого виконується бізнес-інжиніринг у системі «R/3»?

8. Які основні етапи впровадження КПС на підприємстві?

9. Яка повинна бути структура витрат при впровадженні КПС?

10. Яка ефективність від упровадження КПС?

11. Яку роль відіграють інформаційні відділи при впровадженні корпоративних систем?

12. Яка роль топ менеджерів при впровадженні інформаційних систем?
    

Список літератури до теми 2

1. Глушков В.М. Основные принципы построения автоматизированных систем организационного управления // Избранные труды. – К.: Наукова думка, т. 3, 1990.

2. Гужва В.М. Інформаційні системи і технології на підприємствах. - К.: КНЕУ, 2001.

3. 1C: Предприятие, версия 7.7. Руководство пользователя. – М.: Фирма «1С», 2000.

4. ОФИС 2000. Руководство пользователя. – Львов: «АБ Система», 1997. – http://www.ab-system.com.

5. «Miracle-5». Руководство пользователя. – К.: «Миратех», 1999. - http://www.miratech.kiev.ua.

6. Система «Галактика». Концепция и основные компоненты. – М.:«Галактика», 2002. - http://www.galaktika.ru.

7. Вовчак І.С. Інформаційні системи та комп’ютерні технології в менеджменті. – Тернопіль: “Карт-бланш”, 2001.

8.    Ребшток М., Хильдебрант М. R3 – менеджмент. – Минск: Новое знамя, 2001.

9. Ойхман Е.Г., Попов Э.В. Реинжиниринг бизнеса: реинжиниринг организаций и информационные технологии. - М.: Финансы и статистика, 1997.

3. Аналіз табличних даних

Більшість менеджерів, що працюють у невеликих фірмах, не мають адекватних засобів автоматизації своєї роботи. Навіть якщо підприємство має корпоративну систему, то вона призначається як правило для оперативного управління. Тому для менеджерів-аналітиків, яким необхідно шукати оптимальні рішення, дуже актуальним є завдання автоматизації на основі «підручних» програмних засобів. В цій лекції буде показано, як можна розв’язувати задачі аналізу і прогнозу даних у табличному процесорі Excel, що входить у загальнодоступний пакет “Microsoft Office”.

3.1. Табличний процесор Excel

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

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

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

Таким чином, на кожному робочому аркуші є двовимірна система координат, що призначена для іменування комірок таблиць. Кожен рядок має адресу, що є числом від 1 до 65536, і аналогічно кожний з 256 стовпців має адресу, якою є латинська літера від A до Z чі сполучення латинських літер від AA до IV.

Кожна комірка на аркуші має адресу і вміст. Адреса комірки складається з адреси стовпця і рядка, на перетині яких вона знаходиться, наприклад, А1, С7 і т.д. З кожною коміркою на аркуші неявно пов'язується алгебраїчна змінна, назва якої збігається з адресою комірки, а значення збігається з вмістом комірки. Вмістом комірки може бути будь-який рядок символів, що має довжину  до 32768 символів, і який відноситься до одного з наступних типів:

- текстова константа;

- числова константа;

- формула.

Текстові константи (текстові рядки) починаються з літери і за замовчуванням вирівнюються в комірці вліво.

Числові константи (числа) починаються з цифри і за замовчуванням вирівнюються в комірці вправо. Якщо число в комірці вирівнюється вліво, то, швидше за все, відбулася помилка в числовому форматі. У програмі Excel є 9 числових форматів, включаючи формати дати і часу, які студенти повинні вивчити самостійно [2].

Вся сила Excel полягає в формулах. Без них ця програма дула б  просто калькулятором.

Знак рівності служить для відмінності формул і текстових констант. Всі арифметичні операції у формулах бінарні, тобто допускають два операнди. Наприклад, в комірці А1 може знаходитися формула =А2+1, у якій А2 є першим операндом (змінна), а константа 1 другим операндом операції додавання (+). Цю формулу можна сприймати як алгебраїчну рівність  А1=А2+1. Тому, якщо комірка А2 містить, наприклад, число 5,  то відповідно до законів арифметики комірка А1 буде містити число 6. В цьому випадку говорять, що в комірці А1 є посилання на комірку А2 чі, комірка А1 (інформаційно) залежить від А2. У програмі Excel у меню «Сервис» є спеціальна команда «Зависимости», яка дозволяє «побачити» всі комірки, від яких залежить дана комірка ( які впливають на дану комірку). Якщо у формулі є посилання на текстову, а не на числову константу, то у відповідній комірці з’являється повідомлення про помилку. Це найчастіше джерело помилок у формулах. Крім того, в комірці не може знаходитися формула, що посилається на ту ж комірку, в якій вона знаходиться. Це окремий випадок циклічних посилань, що заборонені.

З формулами пов'язані два фундаментальних механізми програми Excel:

- Автоматичне переобчислення;

- Автозаповнення при копіюванні формул.

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

Другий механізм включається при копіюванні формул. Наприклад, якщо формулу =А2+1 скопіювати з комірки А1 в комірку В1, то в комірці В1 з'явиться формула =В2+1. Загальне правило копіювання говорить: при копіюванні формули всі адреси модифікуються у відповідності до вектора зміщення формули.  Іншими словами, якщо формула копіюється зі зміщенням по горизонталі, то будуть відповідним чином змінюватися адреси стовпців, а якщо по вертикалі, то адреси рядків. Цей механізм також дозволяє заощаджувати багато часу, оскільки формули не потрібно модифікувати вручну.

Але, проте, іноді не потрібно змінювати формулу. Щоб більш гнучко керувати процесом копіювання вводиться поняття відносної й абсолютної адресації. Усі звичайні адреси у формулах розглядаються як відносні й модифікуються відповідно до загального правила. Якщо перед адресою рядка чи  стовпця стоїть знак долара $, то така адреса називається абсолютною і не модифікується при копіюванні. Таким чином, формула =$А$2+1 при копіюванні змінюватися не буде, оскільки адреса $А$2 є абсолютною. Можлива комбінована адресація, коли адреса рядка абсолютна, а адреса стовпця відносна, наприклад =А$2+1 чі навпаки =$А2+1. Такі формули будуть модифікуватися тільки при копіюванні по горизонталі чі, відповідно, тільки при копіюванні по вертикалі. Таким чином, загальне правило копіювання формул говорить:

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

Крім того, надалі, ми будемо припускати, що студенти вільно володіють майстром функцій Excel і можуть включити у формулу звернення до будь-якої функції. Помітимо, що аргументом функції часто виступає діапазон комірок, тобто одномірна чі двовимірна область таблиці, а не окрема комірка. Також будемо припускати, що студенти володіють майстром побудови діаграм, оскільки діаграми є основним інструментом  візуалізації даних [1], [2], [3].

3.2. Робота зі списками і підведення підсумків

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

Список у Excel це таблиця, що інтерпретується процесором як база даних. Тому списки в Excel також називаються «базами даних». Звичайно, до цієї назви потрібно відноситися з деякою обережністю, оскільки в наступній лекції ми познайомимося зі «справжніми» базами даних, в той же час певна аналогія тут, безумовно, є. Характерною ознакою (реляційної) бази даних є однорідність даних по стовпцях. Тому, щоб таблиця Excel могла розглядатися як список необхідно, насамперед, щоб у кожнім стовпці таблиці дані мали один тип, тобто або всі були текстовими, або всі числовими, або усі датами. Для більшості додатків, де використовуються списки, ця умова виконується. Крім того, щоб табличний процесор інтерпретував таблицю як список є необхідним виконання ряду формальних умов:

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

• перший рядок списку повинен містити заголовки стовпців.

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

Щоб викликати форму введення даних, введіть заголовки стовпців, виділіть їх жирним шрифтом, щоб вони відрізнялися від даних, поставте курсор у порожній рядок під заголовком одного зі стовпців, а потім виконайте команду «Данные →Форма». Якщо все в порядку, то з'явиться форма введення даних (див. рис. 3), у противному випадку, якщо порушена одна з вищезгаданих умов, Excel повідомить про помилку.

Цілком можливо, що після виконання команди «Данные ® Форма» Excel видасть повідомлення типу: «не вдається знайти рядок списку, щоб використовувати дані першого рядка як підписи натисніть кнопку «ОК». У цьому випадку треба просто натиснути кнопку «ОК» і форма з'явиться на екрані. Після цього можна бути впевненим, що ваша таблиця інтерпретується процесором як список, і з нею можна працювати як з базою даних. Звичайно робота з формою не викликає в користувачів труднощів, але тут варто врахувати, що форма може використовуватися не тільки для введення даних, але і для їх редагування і пошуку.  

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

Фільтрація це вибір даних зі списку за заданими критеріями відбору. У відфільтрованому списку можна бачити тільки ті рядки, які задовольняють заданим критеріям. При цьому відповідні номери рядків виділяються синім кольором (ознака фільтрації), а інші рядки залишаються прихованими, але не знищеними. Приховані рядки можуть бути в будь-який момент відновлені. Для простої фільтрації використовується Автофильтр, а для фільтрації за складними критеріями - Расширенный фильтр [1].

Звичайно фільтрація за допомогою автофільтру не викликає у користувачів труднощів, але при цьому використовуються далеко не всі надані можливості. По-перше, за допомогою елемента списку автофільтру «(Первые 10…)» можна вибрати рядки по декількох найбільших чи найменших елементах відповідного числового стовпця (поля). По-друге, за допомогою елемента «(Условие…)» можна викликати вікно користувальницького автофільтру, в якому задається складна логічна умова відбору за відповідним стовпцем, наприклад можна вибрати рядки, відповідні значення в яких знаходяться між двома заданими числовими значеннями. Нарешті, щоб відобразити всі дані, приховані в результаті фільтрації, потрібно виконати команду «Данные ® Фильтр ® Отобразить все».   

Розширений фільтр дозволяє власне кажучи створювати повноцінні запити (у термінології баз даних) до списку. Наприклад, за допомогою розширеного фільтру можна задати умови відбору по двох і більше стовпцях, які з'єднані логічним сполучником “ИЛИ”, що за допомогою автофільтру зробити не можна. Перша відмінність розширеного фільтру полягає в тому, що тут у явному вигляді потрібно задати диапазон условий. Для цього звичайно перед списком роблять кілька порожніх додаткових рядків і в першому рядку повторюють назви всіх стовпців  списку чи частини з них, якщо відбір буде йти не по всіх стовпцях. Краще вказувати тут назви всіх стовпців, тому що зайві стовпці завжди можна залишити порожніми. Потім порядково вводяться критерії відбору, причому умови, що знаходяться в одному рядку з'єднуються сполучником «И», а самі рядки умов з'єднуються сполучником «ИЛИ». Наприклад, у наступній таблиці показані умови відбору за розширеним фільтром. 

Таблиця 3. Умови відбору за розширеним фільтром

За цим критерієм будуть відбиратися рядки, в яких зазначений товар з артикулом 2001, і обсяг продажів якого перевищує 1000, або рядки, в яких зазначене прізвище менеджера «Иванов».

Після заповнення діапазону умов потрібно викликати розширений фільтр за допомогою команди «Данные ® Фильтр ® Расширенный фильтр». У вікні цієї команди уточнюється, де знаходиться список, де знаходиться діапазон умов і чи треба копіювати фільтровані дані в нове місце чи фільтрувати список на місці. Якщо дані треба скопіювати в нове місце, то варто вказати адресу діапазону, куди їх потрібно копіювати. Після виконання цієї команди відбувається фільтрація даних у списку за умовами, зазначеними у розширеному фільтрі. У тестових завданнях до цієї лекції студентам пропонується кілька задач на розширену фільтрацію.

3.2.1. Підведення проміжних підсумків і консолідація даних

Перейдемо тепер до обчислення підсумків у списках даних. Кожному менеджеру після збору оперативних даних доводиться в тому чи іншому вигляді підводити підсумки. Взагалі кажучи, це операція багатогранна. Іноді треба просто підрахувати суму  стовпця, а іноді потрібно побудувати складну зведену таблицю, щоб з'ясувати, хто і скільки заробив. Для більшості користувачів не складає труднощів підрахувати табличну суму по  рядку чи  стовпцю, оскільки для цього досить натиснути кнопку «S» на панелі інструментів. Але при підведенні проміжних підсумків по групах даних, наприклад, по прізвищах  продавців чи сортам товарів, у багатьох виникають труднощі. Тому розглянемо коротко основні способи підведення підсумків.

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

Оскільки Excel автоматично вставляє проміжні підсумки при кожній зміні даних у стовпці групування, то для одержання коректних результатів треба, насамперед, відсортувати список по стовпцю групування. Після цього треба виконати команду «Данные ® Итоги...» і у вікні, що відкрилося, вказати стовпець групування, підсумкову операцію і стовпець, по якому підводяться підсумки.

Можна в будь-який момент зняти проміжні підсумки зі списку даних, поставивши курсор на одне з проміжних значень і виконати команду «Данные ® Итоги...». Потім у вікні, що відкрилося, потрібно натиснути кнопку «Убрать все».

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

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

Перед виконанням консолідації даних потрібно виділити діапазон комірок на підсумковому аркуші, куди будуть записані результуючі дані, і виконати команду «Данные ® Консолидация...». У вікні цієї команди (див. рис. 5) потрібно вказати агрегуючу функцію (сума, середнє, добуток і т.д.) і список діапазонів з вихідними даними.

Для вказівки діапазонів у цьому вікні потрібно ввести адресу чергового діапазону в поле «Ссылка» і натиснути кнопку «Добавить» (чи «Удалить», якщо діапазон був зазначений невірно). Після додавання всіх діапазонів потрібно натиснути кнопку «ОК» і процесор обчислить результуючі значення. При вказівці діапазонів тут, як правило, використовуються тривимірні посилання, наприклад, посилання «Лист1!F5» вважається тривимірною, оскільки тут явно зазначений аркуш, на якому знаходиться комірка. Якщо комірки на вихідних аркушах розташовані однаково, то можна використовувати тривимірні діапазони. Наприклад, формула =СУММ (Лист1:Лист3!F5) підсумовує значення на трьох робочих аркушах в комірках F5.

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

3.2.2. Побудова зведених таблиць

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

Таких параметрів може бути багато, але, щоб зберегти видимість результуючої таблиці, звичайно використовують не більше чотирьох параметрів. Оскільки таблиці двовимірні, то значення одного узагальнюючого параметра розташовуються по горизонталі (вісь Х), значення другого по вертикалі (вісь У) і, нарешті, значення третього параметра (вісь Z) роблять зведену таблицю тривимірною. Четвертий підсумковий (агрегуючий) параметр використовується для заповнення зведеної таблиці числовими значеннями. Звичайно, і по горизонталі і по вертикалі можна розташовувати при необхідності кілька параметрів, а параметр Z використовується в основному для фільтрації двовимірних таблиць за значенням цього параметра. Відмітимо відразу, що зведені таблиці надають користувачу можливість фільтрації даних по будь-якій координаті і за значеннями будь-яких параметрів.

Наприклад, припустимо, що менеджеру треба довідатися, скільки і яких цукерок було продано за місяць у декількох магазинах. Тоді по горизонталі можна розташувати назви цукерок, по вертикалі - дати продажів і, нарешті, по третій координаті можна розташувати назви магазинів. Як підсумковий параметр тут можна використовувати обсяги продажів у вартісному чи кількісному виразі. У цьому випадку зведена таблиця буде показувати, скільки і коли було продано цукерок певного сорту у всіх магазинах. Якщо таблицю профільтрувати по осі Z, вибравши на ній значення «Центральный», то таблиця покаже, скільки і коли було продано цукерок певного сорту в центральному гастрономі і т.д. 

Для побудови зведеної таблиці треба поставити курсор в комірку, де буде розташовуватися зведена таблиця, чи на вихідний список даних і виконати команду «Данные ® Сводная таблица…». З'явиться вікно майстра зведених таблиць, що за три кроки будує таблицю.

• На першому кроці треба вказати тип джерела даних і, звичайно, тут треба залишити (чи вибрати) першу альтернативу «в списке или базе данных Microsoft Excel», оскільки, як правило, вихідні дані знаходяться в тому ж документі Excel, де будується зведена таблиця. При необхідності як джерело можна вказати зовнішню базу даних. Крім того, на першому кроці треба вказати вигляд створюваного звіту ( таблиця чи таблиця разом з діаграмою). Наприкінці кожного кроку треба натискати кнопку «Далее»;

• На другому кроці треба вказати місце розташування вихідного списку даних. Якщо курсор стояв на вихідному списку, то процесор сам може визначити адресу діапазону, де знаходиться список. У противному випадку треба явно вказати адресу списку, переключившись на робочий аркуш, де знаходиться вихідний список, і обвести мишею весь список. Потім натисніть кнопку «Далее»;

• На третьому (самому головному) кроці треба задати структуру (макет) зведеної таблиці. Тут, насамперед, потрібно натиснути кнопку «Макет...», щоб почати побудову структури таблиці. Не слід відразу натискати кнопку «Готово», тому що макет таблиці все рівно прийдеться будувати, але в інтерактивному режимі, коли процесор буде супроводжувати кожну дію користувача безпосередньою побудовою таблиці. Утім, тут користувач може діяти за власним смаком. Після натискання на кнопку «Макет» з'явиться вікно задання структури таблиці (див. рис. 6). Тут потрібно перетягнути назви полів вихідного списку даних на осі, зведеної таблиці, що будується  і вказати агреговане поле, по якому будуть обчислюватися підсумкові значення. Якщо назву поля перетягнути в зону «Столбец», то вона буде розташована в зведеній таблиці по горизонталі (вісь Х), якщо в зону «Строка», то по вертикалі (вісь У), якщо в зону «Страница», то по осі Z (третя координата). Якщо назву поля перетягнути в зону «Данные», то по ньому буде обчислюватися підсумкова функція. Звичайно, у цю зону варто перетягувати тільки числові поля. Підсумковою функцією за замовчуванням є сума, але її можна змінити, двічі клацнувши лівою кнопкою миші по полю в зоні «Данные». Щоб закінчити побудови макета таблиці, потрібно клацнути по кнопці «ОК». Після цього знову з'явиться третє вікно майстра зведених таблиць, у якому можна уточнити місце розташування зведеної таблиці. Крім того, тут можна натиснути на кнопку «Параметры» і задати назву зведеної  таблиці чи змінити її зовнішній вигляд. Після закінчення всіх підготовчих дій потрібно натиснути на кнопку «Готово» і зведена таблиця з'явиться на робочому аркуші, починаючи з зазначеної адреси.
  

Після створення зведеної таблиці з'являється панель інструментів «Сводные таблицы», за допомогою якої можна виконати наступні дії:

• Відформатувати таблицю, натиснувши кнопку «Формат отчета», і застосувати механізм авто форматування;

• Побудувати діаграму по зведеній таблиці, натиснувши на кнопку «Мастер диаграмм». При побудові діаграми варто видалити з таблиці загальні суми по рядках і по стовпцях і сховати в таблиці всі зайві елементи;

• Змінити макет таблиці, натиснувши на кнопку «Мастер сводных таблиц». Після цього знову з'явиться третє вікно майстра, в якому через кнопку «Макет» відкривається вікно, показане на рис. 6. Тут можна видалити поле з таблиці, забравши його за межі  макета чи ввести нове поле в макет, помістивши його у відповідну зону;

•  Показати чи сховати детальну інформацію про поля таблиці. Якщо двічі клацнути мишею по внутрішній комірці таблиці, то детальна інформація про цей елемент таблиці буде скопійована на новий робочий аркуш. Якщо двічі клацнути по заголовку  рядка чи стовпця зведеної таблиці, то з'явиться вікно «Показать детали», у якому можна додати до даного поля інші поля вихідного списку. Потім кнопками «Скрыть детали» чи «Показать детали» можна змінювати вигляд зведеної таблиці,  ховаючи чи розкриваючи детальну інформацію про це поле;

• Обновити дані в зведеній таблиці, натиснувши на кнопку «Обновить данные». Зведена таблиця зв'язується з вихідним списком, і після зміни вихідних даних необхідно  таким чином обновляти дані в зведеній таблиці;

• Змінити формат будь-якої комірки в таблиці, натиснувши на кнопку «Параметры поля», а потім на кнопку «Формат...»;

• Сховати чи відобразити поля вихідного списку на панелі інструментів «Сводные таблицы», натиснувши на кнопку «Скрыть поля» чи «Отобразить поля».

ЛАБОРАТОРНА РОБОТА № 1.

Використовуючи базу даних про постачаня галантерейних товарів менеджерами фірми Таблиця 1 дати відповіді на наступні питання:

1. Якого товару найбільш за все продав з мазагину Іван?

2. Яка кількість канви була продана усіма менеджерами зі складу?

3. Яка сумарна кількість наперстків була продана кожним менеджером з усіх точок постачання (магазину, бази, складу)?

4. Яке середє значення мала  кількість молній  які Олена продавала зі складу?

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

Розв'язок. Побудуємо зведену таблицю по вихідній інформації (таблиця 1) наступної структури:

- у зоні стовпців розмістимо показник Продавець;

- у зоні стрічок розмістимо показник Продукція;

- у зоні сторінок розмістимо показник Постачальник;

- в області даних розмістимо показник Обсяг, по якому виконаємо операцію Сума.

Відповідь на питання  a. можна отримати зі зведеної таблиці  (Таблиця 2) на перетині стрічки “Молнії” та стовпця “Іван” на сторінці “Магазин”. Відповідь на питання b. можна отримати з зведеної таблиці  (Таблиця 2) в стрічці “Канва” на сторінці  “Склад”. Відповідь на питання c. можна отримати з зведеної таблиці (Таблиця 2) на сторінці “все” у стрічці “Наперстки” Відповідь на питання d можна отримати зі зведеної таблиці (Таблиця 3).на сторінці “Склад” на перетині стовпця “Олена” та стрічці “Молнії”. Відповідь на питання e.можна отримати з зведеної таблиці (Таблиця 3) на сторінці “все” у стовпці “Нитки”.

3.3. Фінансові функції

Фінансовим менеджерам повсякденно доводиться аналізувати ефективність інвестицій, розраховувати терміни погашення кредитів, позик і позичок, визначати вигідність вкладень з урахуванням банківських відсотків і т.д. Процесор Excel містить багатий набір фінансових функцій, що дозволяють розв’язувати більшість з цих задач. Розглянемо основні фінансові функції в Excel і покажемо, як за їх допомогою можна розв’язувати стандартні задачі фінансового аналізу. З іншими фінансовими функціями, а їх більше 50, студенти можуть познайомитися в міру необхідності [4 ].

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

Таблиця 4. Аргументи фінансових функцій

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

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

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

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

3.3.1. Функція БЗ

Функція БЗ (майбутнє значення) повертає майбутню суму внеску, зробленого на основі постійної процентної ставки. Ця функція виражає значення «Бз» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується звичайно для оцінки доцільності проведення банківських вкладів (депозитів). Синтаксис виклику цієї функції наступний:

=БЗ(Норма, Кпер, Выплата, Нз, Тип);

Тут «Нз» означає одноразову виплату, що проводиться на початку операції. Хоча третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Зі співвідношення (1) видно, що значення функції буде позитивним, якщо третій чи четвертий аргументи негативні.

ЛАБОРАТОРНА РОБОТА № 2. Фірма вирішила відкладати на депозит по 10000 у.е. щорічно під 14% річних. Скільки буде на рахунку фірми через 3 роки?

Фірма вирішила покласти на депозит 10000 у.е. під 14% річних. Скільки буде на рахунку фірми через 3 роки?

Передбачається вкласти в банк 1000 у.е. під 6% річних і вкладати потім по 100 у.е. на початку кожного з наступних 12 місяців. Скільки грошей буде на рахунку наприкінці 12 місяців?

3.3.2. Функція КПЕР

Функція КПЕР (кількість періодів) повертає загальну тривалість виплати для даної позики чи депозиту на основі постійної процентної ставки. Ця функція виражає значення «Кпер» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується для оцінки термінів зворотності позик і тривалості депозитів. Синтаксис виклику цієї функції наступний:

КПЕР(Норма, Выплата, Нз, Бз, Тип);

Третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Використовувати цю функцію можна для оцінки тривалості позик, коли Нз > 0 і Бз = 0, чи для оцінки тривалості депозитів, коли Нз  0 і Бз > 0. Функція повертає звичайно дробове значення, тому варто встановити в комірці виклику формат цілих  чисел чи заокруглити результат до цілого числа, щоб одержати ціле число періодів.

ЛАБОРАТОРНА РОБОТА № 3. Скільки місяців знадобиться для виплати позики в 1000 у.е., взятої під 12% річних, якщо передбачається виплачувати по 100 у.е. щомісяця?

Передбачається вкласти в банк 1000 у.е. під 12% річних і вкладати потім по 100 у.е. щомісяця, поки на рахунку не стане 10000. Скільки місяців необхідно для цього? 

3.3.3. Функція ПЗ

Функція ПЗ (минуле значення) повертає поточний обсяг внеску, тобто суму, яку потрібно покласти, щоб одержати одноразовий еквівалент майбутніх періодичних  виплат чи щоб досягти в майбутньому заданого балансу. Ця функція виражає значення «Нз» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується для оцінки прибутковості депозитів та інвестицій. Ця функції має наступний синтаксис:

ПЗ(Норма, Кпер, Выплата, Бз, Тип);

Третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Якщо Виплата < 0 і Бз = 0, то функція ПЗ повертає одноразовий еквівалент періодичних виплат, якщо ж Виплати = 0 і Бз > 0, то вона показує, як досягти заданого балансу.

ЛАБОРАТОРНА РОБОТА № 4.

Банк пропонує Вам викупити приміщення відразу за 10000 у.е. чи платити по 600 у.е. щомісяця протягом двох років при ставці 8% річних. Визначите, який з цих варіантів вигідніший.

Яку суму потрібно покласти в банк на депозит, щоб через 4 роки вона досягла 100000 у.е. при нарахуванні 10% річних?

Банкпропонує Вам вкласти в проект 25000 у.е., що буде приносити щомісяця по 500 у.е. протягом 5 років при 8 відсотках річних. Визначте, чи варто інвестувати цей капітал.

3.3.4. Функція ППЛАТ

Функція ППЛАТ (періодична плата) обчислює величину періодичної виплати по позичці на основі постійної процентної ставки. Ця функція виражає значення «Выплата» через інші чотири аргументи, виходячи зі співвідношення (1). Вона використовується для оцінки обсягу періодичних виплат по позичках, позикам і кредитам. Ця функція має наступний синтаксис:

ППЛАТ(Норма, Кпер, Нз, Бз, Тип);

Третій і четвертий аргументи необов'язкові, але, принаймні, один з них повинен бути зазначений. Якщо Нз > 0 і Бз  0, то функція ППЛАТ повертає суму періодичної виплати по позиці, якщо ж Нз  0 і Бз > 0, то по депозиту.

ЛАБОРАТОРНА РОБОТА № 5. Банкдає в кредит 50000 у.е. на придбання житла під 10% річних з розстрочкою виплат на 20 років. При цьому 20% кредитної суми потрібно виплатити відразу. Оцініть суму щомісячних виплат по цьому кредиту.

Передбачається вкласти в банк 2000 у.е. під 12% річних і потім вкладати гроші щорічно протягом 5 років, поки на рахунку не буде 10000. Оцініть суму щорічних виплат по цьому депозиту.

Контрольні питання до теми 3

1. До якого класу програм відноситься Excel?

2. З яких елементів складаються формули в Excel?

3. Що таке відносна й абсолютна адресація?

4. Як по таблиці побудувати діаграму в Excel?

5. Що таке список (база даних) в Excel?

6. Як відсортувати список за значеннями одного чи декількох стовпців?

7. Які можливості відбору даних дають автофільтр і розширений фільтр?

8. Як за допомогою розширеного фільтру скопіювати фільтровані дані на іншу сторінку робочої книги Excel?

9. Які можливості підведення проміжних підсумків дає процесор Excel?

10. Що таке консолідація даних у Excel?

11. Що таке зведені таблиці і як їх використовувати?

12. Які основні фінансові функції надає Excel?

13. Як підрахувати періодичний внесок по кредиту при заданій процентній ставці?

14. Як підрахувати майбутню суму на депозиті при періодичних внесках?

15. Як підрахувати термін погашення кредиту при заданій процентній ставці?

ЛАБОРАТОРНА РОБОТА № 6.

 На першому аркуші книги Excel зробіть таблицю продажів мобільних телефонів, що містить стовпці «Продавець», «Дата», «Модель», «Ціна», «Кількість», «Сума». Перші п'ять стовпців заповніть довільними даними (5-10 рядків), а значення в шостому стовпці обчисліть за формулою. За допомогою розширеного фільтру відберіть рядки, в яких сума продажу перевищує 100  гривень чи не перевищує 50 гривень, і скопіюйте їх на другий аркуш.

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

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

3. Припустимо, що береться кредит на суму 10000 у.е. під 13% річних терміном на п'ять років. Оцініть розмір щомісячних виплат по цьому кредиту, якщо розрахунки проводяться наприкінці кожного місяця.

4. Підрахуйте яку суму необхідно покласти на депозит, щоб через 5 років вона досягла значения 10000 у.е. при нарахуванні 13% річних.

5. Припустимо, що береться кредит на суму 10000 у.е. під 13% річних з умовою, що кожен місяць буде погашатися 500 у.е. Визначте, скільки місяців знадобиться на погашення цього кредиту, якщо розрахунки проводяться на початку кожного місяця.

6. Підрахуйте, скільки буде на банківському рахунку через 5 років, якщо кожен місяць відкладати по 500 у.е. під 13% річних.
   

Список літератури до теми 3

1. Бахонський О.В. і др. Табличний процесор Microsoft Excel. Частина 1 і 2. - К.: МАУП, 2002.

2. Додж М., Стинсон К. Эффективная работа с Microsoft Excel 2000. – СПб.: Питер, 2000.

3. Рыжков В. Самоучитель Excel 2000. – СПб.: Питер, 1999.

4. Бухвалов А., Бухвалова В., Идельсон А. Финансовые вычисления для профессионалов. – СПб.: BHV, 2003.

4. Статистичний аналіз і прогнозування

4.1. Статистичний аналіз

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

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

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

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

Відзначимо відразу, що для роботи з пакетом аналізу необхідна команда «Анализ данных» з меню «Сервис». Якщо цієї команди немає в меню, то викличте команду «Сервис ® Надстройки…» (не плутати з командою «Настройка» з того ж меню), а потім у списку «Надстройки» встановіть прапорець у рядку «Пакет анализа» і натисніть кнопку «ОК». Якщо в списку «Надстройки» немає пакета аналізу, то натисніть кнопку «Обзор» і спробуйте знайти файл Analys32.xll у папці Program Files\Microsoft Office\Office\Library\Analysis, у якій звичайно містяться всі доповнення до програмної системи «Microsoft Office». Якщо і ця спроба закінчилася невдачею, то необхідно запустити програму Setup з інсталяційного диска системи «Microsoft Office» і додати пакет аналізу.

4.1.1. Регресійний аналіз

Поняття регресії з'явилося в середині XIX століття в роботах англійського статистика Ф. Гальтона, що вивчав залежність між ростом батьків та їхніх дітей. Слово «регресія» походить від латинського «regressio», що в дослівному перекладі означає «рух назад». Згодом статистичний зміст цього терміна значно розширився, і ним стали називати будь-які наближені методи, що дозволяли знайти функціональні залежності між випадковими величинами [3].

У регресійному аналізі розглядається залежність випадкової (залежної) змінної Y від однієї чи декількох невипадкових (незалежних) змінних X. Така залежність може бути представлена у вигляді модельного рівняння регресії Y = f(X), де f – деяка функція. Але в силу впливу випадкових (неврахованих) факторів окремі значення, що спостерігаються,  змінноїY можуть відхилятися від функції f(X). У цьому випадку рівняння взаємозв'язку можна представити у вигляді Y = f(X) + e, де e - випадкова змінна, що характеризує відхилення від функції регресії, яку називають також збуренням. Таким чином, у регресійній моделі залежна змінна Y є деяка функція  f(X) з точністю до випадкового  збурення e.

Для простоти ми розглянемо тут тільки задачі лінійного регресійного аналізу, в якому функція f(X) передбачається лінійною щодо незалежної змінної. В цьому випадку задача ставиться таким чином. Нехай існує набір точок на площині  («хмара»  точок чи діаграма розсіяння), що відбивають наші емпіричні спостереження над економічними показниками x і y, потрібно знайти пряму лінію на площині, яка проходить якнайближче до вихідних точок. Вірніше так поставлена задача називається простою (чи парною) задачею лінійної регресії, оскільки тут є тільки одна незалежна змінна, котра разом із залежною змінною утворює пару. Якщо незалежних змінних декілька, то така задача називається множинною задачею лінійної регресії.

Поставлена задача неточна, оскільки незрозуміло, що означає фраза «якнайближче». З геометрії нам відомо, що через дві різні точки на площині проходить єдина пряма, але яку пряму вибрати, якщо існує 100 точок, і вони не лежать на одній прямій? Нехай існує деяка пряма (2), тоді сумою квадратів відхилень цієї прямої від точок  назвемо наступну суму.

Уточнив поставлену задачу і дав її розв’язання великий математик Ф. Гаусс ще в XVIII столітті. Він запропонував вибирати пряму лінію (2) таким чином, щоб сума квадратів S була найменшою. Виявляється така пряма завжди існує і вона єдина, тому Гаусс назвав цей метод методом найменших квадратів.

В усіх підручниках наводяться формули, що виражають коефіцієнти  через координати вихідних точок  отримані за методом найменших квадратів [1], [2]. Корисно подивитися на ці формули, але запам'ятовувати їх не обов'язково, оскільки табличний процесор Excel «пам'ятає» їх краще. Треба лише вміти правильно поставити регресійну задачу, а всі обчислення по методу найменших квадратів процесор проведе сам. Покажемо на прикладі як можна вирішити просту лінійну регресійну задачу за допомогою табличного процесора.

ЛАБОРАТОРНА РОБОТА № 7. Виходячи з наведеної нижче статистичної таблиці, визначте вигляд лінійної залежності між кількістю раціоналізаторських пропозицій (Х) і доходом фірми (Y). Спрогнозуйте величину доходу при Х = 23.

Розв’язання. Записати вихідну таблицю на робочому аркуші Excel, наприклад, у діапазоні А1:В11. Варто розташовувати значення однієї змінної в одному стовпці, оскільки таблиця розміру M´N сприймається при регресійному аналізі як М спостережень над N змінними. Виконати команду «Сервис ® Анализ данных» і у списку, що відкрився, «Анализ данных» вибрати пункт «Регрессия», тоді відкриється вікно, показане на наступному малюнку.

У полі «Входной интервал Y» варто вказати область зміни залежної змінної (діапазон В1:В11), а в полі «Входной интервал Х» - область зміни незалежної змінної (діапазон А1:А11). Потрібно також включити кнопку «Метки», оскільки заголовки стовпців (Х и У) включені у вхідні інтервали. Можна залишити цю кнопку виключеною, але тоді не потрібно включати у вхідні діапазони заголовки стовпців і, крім того, у цьому випадку Excel привласнює незалежним змінним назви за замовчуванням Х1, Х2 і т.д. Якщо включити кнопку «Константа – ноль», то вільний член  шуканої прямої буде прирівняний до нуля й у цьому випадку регресійна пряма буде примусово проведена через початок координат. Група селекторних кнопок «Параметры вывода» вказує, куди помістити результат (на поточному аркуші, на новому аркуші, у новій книзі). Група опціональних кнопок «Остатки» впливає на форму подання результату. Нарешті, потрібно натиснути на кнопку «ОК» і на робочому аркуші з'являться результати аналізу, показані на рис. 8.

На регресійній статистиці і дисперсійному аналізі ми зупинимося в наступному параграфі, а поки зазначимо, що  параметри які нас цікавлять і  знаходяться під заголовком «Коэффициенты» в комірках Е17 і Е18. У даному випадку маємо = 800,7 і = 2,9. Таким чином, при збільшенні на одиницю кількості рацпропозицій доход фірми «у середньому» збільшується приблизно на три одиниці. Підставивши значення  і  разом зі значенням Х = 23 у рівняння (2) одержуємо прогнозну оцінку доходу Y = 868. Задача розв’язана.

Можна провести регресійний аналіз і графічним методом. Для цього треба побудувати точкову діаграму по вихідній таблиці, а потім додати на діаграму лінію тренду, клацнувши правою кнопкою миші по будь-якій з вихідних точок і вибравши з контекстного меню команду «Добавить линию тренда». У вікні цієї команди (рис. 12) на вкладці «Параметры» корисно включити кнопку «показывать уравнение на диаграмме», тоді на екрані з'явиться рівняння лінії тренду. На наступному малюнку показана діаграма з лінією тренду, побудована на основі нашого прикладу.

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

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

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

4.1.2. Кореляційний аналіз

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

Поняття кореляції було введене в середині XIX століття англійським статистиком К. Пірсоном. Цей термін походить від латинського слова «correlatio», що означає  співвідношення чи взаємозв'язок. Класичними прикладами статистичного взаємозв'язку (кореляції) є залежність врожайності від кількості внесених  добрив чи залежність продуктивності праці від енергооснащеності і т.д.

Перший крок при проведенні кореляційного аналізу полягає у візуальному перегляді діаграми розсіяння. Якщо хмара вихідних точок досить широко розташувалася над регресійною прямою, то здоровий глузд підказує, що лінійна залежність між змінними відсутня. Якщо ж точки групуються у вузькому шарі навколо прямої, то можна говорити про більш-менш сильну лінійну залежність. Другим (і основним) кроком є обчислення вибіркового коефіцієнта кореляції, де  і - середні значення змінних Х и Y відповідно. Цей коефіцієнт називається також «парним вибірковим», оскільки він обчислюється безпосередньо по вихідній вибірці точок і відноситься до пари змінних. Коефіцієнт кореляції приймає значення на відрізку [-1,1], тобто.

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

Коефіцієнт кореляції можна обчислювати до проведення регресійного аналізу безпосередньо за формулою (4) чи після його проведення, щоб переконатися в коректності отриманих результатів. Якщо використовується програма Excel, то другий шлях є цілком прийнятним, оскільки Excel видає коефіцієнт кореляції в якості «безкоштовного» додатка до регресійного аналізу. Цей коефіцієнт наводиться першим у розділі регресійної статистики під назвою «Множественный R», оскільки він використовується і при множинному регресійному аналізі і є в цьому випадку узагальненням парного коефіцієнта кореляції. У нашому прикладі він знаходиться в комірці Е4. Крім того, у цьому розділі видається також квадрат коефіцієнта кореляції, стандартна помилка залежної змінної і число спостережень (емпіричних даних).

4.1.3. Дисперсійний аналіз

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

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

Такий інтервал називається довірчим, а  - довірчою  імовірністю чи надійністю оцінки. Величина довірчого інтервалу істотно залежить від обсягу вибірки (зменшується з її ростом) і від довірчої імовірності (збільшується з її ростом). Звичайно довірчу імовірність вибирають не меншою ніж 0,95 і в цьому випадку говорять про 95-процентну надійність результату. Надійність результатів регресійного аналізу регулюється у вікні регресії (рис. 7) кнопкою «Уровень надежности». З рівнем надійності тісно пов'язаний «рівень значимості» . Таким чином, звичайно .

ЛАБОРАТОРНА РОБОТА № 8.

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

Таблиця 5. Дисперсійний аналіз для простої лінійної регресії

Сума квадратів SS₁ це дисперсія, пов'язана з регресійною прямою. Ця величина тісно пов'язана з регресійним коефіцієнтом, оскільки , де  це дисперсія змінної Х. Сума квадратів SS₂ , як видно з формули (3), просто збігається із сумою квадратів відхилень, а величина  MS₂  називається середнім квадратом відхилення від регресії. Сума квадратів SS₃ збігається з повною дисперсією  змінної Y. Величина, названа F-відношенням, є статистичним критерієм (статистикою), значення якого безпосередньо використовується для перевірки гіпотези про лінійну залежність і для побудови довірчих інтервалів. З подробицями цієї побудови, що включає велику статистичну теорію, студенти можуть познайомитися по спеціальним курсам математичної статистики [1], [4 ].

У нашому прикладі результати дисперсійного аналізу виводяться в діапазоні Е12:I14. Число спостережень у нас (див. комірку Е8), тому число залишкових степенів свободи дорівнює 8. Суми квадратів рівні відповідно 3393,8 , 56,6 і 3450,4. Середній квадрат MS₂ = 7,1 , а F-відношення дорівнює 479,5. В комірці I12 показаний рівень значимості F, рівний 2*10 ^{– 8}. Ця величина, яка називається також Р-значенням, відіграє основну роль у перевірці статистичної гіпотези.

Це правило можна вважати підсумком дисперсійного аналізу. В даному випадку Р-значенння настільки мізерне, що гіпотеза про лінійну залежність може бути прийнята з надійністю яка перевищує 99%. У діапазоні Е17:L18 виводяться докладні статистичні дані по кожному з регресійних коефіцієнтів і, зокрема, показані довірчі інтервали для кожного з них з рівнем надійності 95%.

4.1.4. Статистичні функції і передбачення даних

ЛАБОРАТОРНА РОБОТА № 9.  

Проведення лінійного регресійного аналізу з використанням функції ЛИНЕЙН, що має наступний синтаксис виклику:

ЛИНЕЙН(Значения_Y; Значения_Х; константа; статистика) .

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

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

Перед викликом векторної функції потрібно виділити весь діапазон значень, що повертаються, потім викликати майстра функцій і після введення всіх аргументів натиснути комбінацію клавіш <Ctrl+Shift+Enter>.

У випадку простої лінійної регресії функція ЛИНЕЙН повертає в стислій формі (якщо статистика = 0) масив розміру  (коефіцієнти регресійної прямої  і  відповідно), і масив  у повній формі. Наступна таблиця пояснює структуру цього масиву.

Таблиця 6. Масив, що повертається функцією ЛИНЕЙН

Тут у першому рядку видаються коефіцієнти регресії, у другому рядку - їхні стандартні помилки (Standard Errors), у третьому рядку - квадрат коефіцієнта кореляції (коефіцієнт детермінованості) і стандартна помилка залежної змінної, яка дорівнює  квадратному кореню із середнього квадрата відхилення MS₂, у четвертому рядку видається F-відношення і число спостережень і, нарешті, у п'ятому рядку - регресійна і залишкова суми квадратів.

У випадку множинної регресії функція ЛИНЕЙН у повній формі, коли статистика=1, повертає масив розміром , де N – число незалежних змінних. По кожній з цих змінних у першому рядку видається регресійний коефіцієнт і наприкінці рядка вільний член, а в другому рядку стандартна помилка для цього коефіцієнта. Вміст перших двох стовпців залишається таким самим, як і при простій регресії. Підтаблиця, розташована в нижньому правому кутку розміром , залишається вільною.

ЛАБОРАТОРНА РОБОТА № 10.

Використання функція ТЕНДЕНЦИЯ для передбачення значень відповідно до лінійного тренду. Вона апроксимує вхідні масиви Х і Y регресійною прямою лінією (за методом найменших квадратів) і повертає вектор значень Y, що відповідають цій прямій для заданого масиву нових значень Х. Ця функція має наступний синтаксис виклику:

ТЕНДЕНЦИЯ(Значения_Y; Значения_Х; Новые_Х; константа).

Якщо константа = 0, то регресійна пряма примусово проводиться через початок координат, у протилежному випадку вільний член обчислюється звичайним чином. На рис. 11 показане використання функції ТЕНДЕНЦИЯ для прогнозу курсу акцій компанії на півроку вперед. Ще раз звернемо увагу, що функція ТЕНДЕНЦИЯ є векторною і для неї справедливе вищенаведене правило виклику векторних функцій.

Скалярною формою функції ТЕНДЕНЦИЯ є функція ПРЕДСКАЗ, що передбачає лише одне значення залежної змінної для одного нового значення незалежної змінної. Ця функція має наступний синтаксис виклику:

ПРЕДСКАЗ(Новое_Х;Значения_У; Значения_Х) .

Наприклад, ПРЕДСКАЗ(30;{6;7;9;15;21};{20;28;31;38;40}) = 10,61.

Прогнозувати значення можна і графічним способом. Для цього треба побудувати точкову діаграму по вихідній таблиці, а потім виконати з контекстного меню команду «Добавить линию тренда». У вікні «Формат линии тренда», яке показане на наступному малюнку, на вкладці «Параметры» у зоні «Прогноз» потрібно виставити число одиниць, на які прогнозуються дані, і вказати напрямок прогнозу  вперед чи назад. Тут корисно включити кнопку «поместить на диаграмму величину достоверности аппроксимации», тоді на діаграмі з'явиться квадрат коефіцієнта кореляції R².

У процесорі Excel є і нелінійні регресійні функції. Наприклад, функція ЛГРФПРИБЛ проводить експонентну (логарифмічну) апроксимацію дослідних даних, тобто наближає їх за допомогою показової функції y = * . Цю функцію можна вважати експонентним аналогом функції ЛИНЕЙН. Аналогічно для прогнозування за експонентним законом використовується функція РОСТ, яку можна вважати експонентним аналогом функції ТЕНДЕНЦИЯ. Наприклад, функцію РОСТ можна використовувати для прогнозування ажіотажного попиту. З нелінійними регресійними функціями студенти можуть познайомитися самостійно за допомогою довідкової системи Excel. 

4.2. Підбір параметрів і пошук розв’язання

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

4.2.1. Підбір параметрів

Припустимо, що за допомогою обчислення значень деякої складної функції необхідно одержати заздалегідь заданий результат. Як у цьому випадку знайти відповідне значення аргументу? Для розв’язання таких «зворотних» задач призначена команда «Подбор параметра» з меню «Сервис».

ЛАБОРАТОРНА РОБОТА № 11. Ваш друг хоче взяти кредит у банку терміном на 30 років під 10% річних на придбання житла. Але в нього немає можливості повертати більше ніж 200 у.е. на місяць. Який максимальний кредит він може взяти, щоб укластися в цю суму?

Розв’язання. Розв’язуємо спочатку «пряму» задачу за допомогою функції ППЛАТ, починаючи з деякої початкової суми кредиту, наприклад, 50000. З рис. 13 видно, що виплати по цьому кредиту (комірка В4) перевищать $200.

Активізуємо комірку В4 і виконуємо команду «Сервис ® Подбор параметра». У вікні цієї команди (рис. 14) в першому полі за замовчуванням буде зазначена цільова адреса B4, яку залишаємо без зміни. В полі «Значение» вказуємо цільове значення 200, а в полі «Изменяя значение ячейки», вказуємо адресу B1, оскільки саме в цій комірці розташований шуканий аргумент.

Після натискання на кнопку «ОК» з'явиться вікно «Результат подбора параметра» з протоколом розв’язання задачі, а в комірці В1 з'явиться відповідь, яка дорівнює 22790. Задача розв’язана.

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

При розв’язанні задач цим методом варто звертати увагу на точність одержуваних результатів і на вибір початкових наближень, які можуть вплинути на результат, якщо в задачі є кілька розв’язків. Наприклад, спробуємо добути квадратний корінь і запишемо в комірку А1 формулу =А2^2, а потім виконаємо команду «Подбор параметра» для пошуку значення в комірці А2, при якому вміст комірки А1 дорівнює 4. В результаті отримаємо «неточне» значення кореня, що дорівнює 2,000023. Якщо як початкове наближення взяти -1, то отримаємо в результаті –1,99992. 

4.2.2. Пошук розв’язку та оптимізація

Підбір параметра зручний для пошуку значень однієї змінної. В більш складних випадках варто використовувати команду «Поиск решения» з меню «Сервис», що дає змогу розв’язувати задачі з багатьма невідомими і знаходити значення цих невідомих, при яких значення деякої функції досягає  максимуму чи мінімуму. Тут також можна задати обмеження, яким повинне задовольняти розв’язання задачі. В даному розділі ми лише торкнемося роботи з компонентою пошуку розв’язання. Більш докладно з цим могутнім інструментом студенти можуть познайомитися по спеціальній літературі [5 ].

Відзначимо, що компонента пошуку розв’язання (на англійській Solver) як і пакет аналізу є доповненням (надбудовою) програми Excel і для роботи з нею необхідна команда «Поиск решения» у меню «Сервис». Якщо цієї команди немає в меню, то установка надбудови здійснюється так само, як це було описано вище для пакета аналізу.

По-перше, процедуру пошуку розв’язання можна використовувати для розв’язання рівнянь від декількох змінних . Для цього у вікні команди «Сервис ® Поиск решения» (див. рис. 15) потрібно встановити селекторну кнопку «Равной» у позицію «значению 0». В цьому випадку в якості цільової вказується комірка, в якій записана функція f, а в полі «Изменяя ячейки» вказуються адреси, де розташовані значення змінних . Іншими словами, пошук розв’язання є узагальненням процедури підбору параметра для багатьох змінних. Аналогічно за допомогою цього інструменту можна розв’язувати системи рівнянь від декількох змінних. Для розв’язання рівнянь в Excel використовується алгоритм нелінійної оптимізації градієнтного типу (GRG2), розроблений американськими вченими.

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

Функція f називається цільовою, а функції  - функціями обмежень. Якщо функції f і  лінійні, то відповідна задача називається задачею лінійного програмування. Якщо потрібно, щоб значення змінних були цілими, то відповідна задача називається задачею цілочисельного програмування. Для розв’язання лінійних і цілочисельних задач оптимізації в Excel використовується відповідно симплекс метод і алгоритм спрямованого перебору (гілок і меж), розроблені фахівцями компанії Frontline Systems Inc.

Як приклад розв’язання задачі оптимізації розглянемо наступну задачу.

ЛАБОРАТОРНА РОБОТА № 12.

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

Розв’язання. Почнемо розв’язання зі створення, так званої, оптимізаційної моделі. Запишемо на робочому аркуші в діапазоні В3:Е11 наступну таблицю.

Далі, в комірці Е4 запишемо формулу =С4+D4, щоб визначити обсяг  усієї продукції, а в комірки С6 і D6 запишемо відповідно формули =C4*C5 і =D4*D5, щоб обчислити майбутню собівартість партії продукції кожного виду. В комірки С8 і D8 запишемо формули =С6+C7 і =D6+D7, щоб обчислити повну вартість кожної партії. В комірки С10 і D10 запишемо відповідно формули =C4*C9 і =D4*D9, щоб визначити повний виторг, що буде отриманий при продажі кожної партії. Нарешті, в комірки С11 і D11 запишемо формули =С10-C8 і =D10-D8, щоб визначити майбутній прибуток, а в комірку Е11 запишемо формулу =С11+D11 для визначення загального прибутку, що буде отриманий від продажу продукції. На цьому створення оптимізаційної моделі можна вважати завершеним.

Після цього виконуємо команду «Сервис ® Поиск решения» і у вікні цієї команди, що показане на рис. 15, вказуємо в якості цільової комірку Е11, в якій записана цільова функція. Селекторну кнопку «Равной» встановлюємо в положення «максимальному значению». В полі «Изменяя ячейки» вказуємо діапазон С4:D4, де розташовані невідомі значення, а потім вводимо обмеження.

Для введення обмеження потрібно натиснути кнопку «Добавить». Як обмеження вказуємо межі збуту продукції, що дорівніють відповідно 50000 і 400000, і верхню межу на загальний обсяг продукції, що дорівнює 500000. Натискаємо на кнопку «Выполнить» і програма видає результат С4 = 100000 і D4 = 400000. При цьому буде отриманий прибуток, що дорівнює  1297500. Задача розв’язана.

В загальному випадку оптимізаційна модель повинна містити інформацію про цільову комірку, вихідні дані, змінні й обмеження задачі, а також формули, що пов'язують основні параметри задачі. Формули обмежень можна записувати на робочому аркуші чи вказувати безпосередньо у вікні пошуку розв’язання. Для складних формул перший спосіб більш зручний, оскільки в цьому випадку у вікні обмежень можна вказати просто адресу комірки з формулою. Оскільки опис оптимізаційної моделі може забирати значний час програма Excel дозволяє зберігати і завантажувати їх. Для цього за допомогою кнопки «Параметры» у вікні пошуку розв’язання потрібно відкрити додаткове вікно й у ньому натиснути кнопку «Сохранить модель». При збереженні моделі варто вказати весь діапазон, у якому знаходяться вихідні дані і формули.

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

 Контрольні питання до теми 4

1. Що таке математична статистика?

2. Які статистичні функції використовуються в Excel?

3. Як виконати простий регресійний аналіз у Excel?

4. Як оцінити ступінь лінійної залежності двох змінних величин у Excel?

5. Як виконати множинну лінійну регресію в Excel?

6. Як спрогнозувати курс акцій у Excel?

7. Як підбираються параметри в Excel?

8. Як розв’язуються рівняння з декількома параметрами в Excel?

9. Як розв’язуються задачі оптимізації в Excel?

10. Як розв’язуються задачі лінійного програмування в Excel?

ЛАБОРАТОРНА РОБОТА № 13.

Використовуючи просту лінійну регресію, на першому аркуші Excel визначите залежність Y від X, що задана наступною таблицею. Спрогнозуйте значення функції при Х = 7,50.

Використовуючи множинну лінійну регресію, на другому робочому аркуші книги Excel визначите залежність Y від X1 і Х2, що задана наступною таблицею. Апроксимуйте значення функції при Х1 = 64 і Х2 = 40.

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

Фірма збирається взяти кредит на чотири роки в розмірі 8000 у.е. і може повертати не більш 200 у.е. щомісяця. На п'ятому аркуші книги Excel визначите максимальну річну процентну ставку, що може влаштувати фірму для реалізації такої позики. Спробуйте розв’язати цю задачу двома способами. Зверніть увагу на знаки аргументів.
Підприємство випускає два види продукції А и Б, що виготовляються на верстатах трьох типів I, II і III за час зазначений в наступній таблиці. Щотижнева норма роботи верстатів I, II і III типів складає відповідно 40, 36 і 30 годин. Прибуток від реалізації продукції А и Б складає відповідно 5 і 3 гривні. Визначите щотижневий обсяг виробництва кожного виду продукції, при якому прибуток буде максимальним.

Список літератури до теми 4

1. Афифи А., Эйзен С. Статистический анализ. Подход с использованием ЭВМ. – М.: Мир,   1982.

2. Кремер Н.Ш. Теория вероятностей и математическая статистика. – М.: Юнити, 2002.

3. Дрейпер Н., Смит Г. Прикладной регрессионный анализ. Т. 1,2. - М.: Финансы и статистика, 1987.

4. Елисеева Н.И., Юзбашев М.М. Общая теория статистики. - М.: Финансы и статистика, 1996.

5. Курицкий Б.Я. Поиск оптимальных решений средствами Excel. – СПб.: BHV-Санкт Петербург, 1997.

З повагою ІЦ "KURSOVIKS"!