МІЖРЕГІОНАЛЬНА АКАДЕМІЯ УПРАВЛІННЯ ПЕРСОНАЛОМ

МАУП

МЕТОДИЧНІ РЕКОМЕНДАЦІЇ ДО ВИВЧЕННЯ НАВЧАЛЬНОЇ ДИСЦИПЛІНИ

«ІНФОРМАЦІЙНІ СИСТЕМИ І ТЕХНОЛОГІЇ В МЕНЕДЖМЕНТІ»

(Навчальний посібник для студентів усіх форм навчання)

Київ 2005

Кузьмін А.В., Москалькова Н.М., Рисцов І.К., Сіницкий М.Є. Інформаційні системи і технології в менеджменті (під загальною редакцією І.К. Рисцова)

Навчальний посібник – К.: МАУП, 2004.

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

Затверджено на засіданні кафедри інформатики та інформаційних технологій МАУП (Протокол № __ від ___ _________ 2004 р.).

Схвалено Вченою радою Міжрегіональної Академії управління персоналом.

ЗМІСТ

Вступ

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. Математичне моделювання

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

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

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

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

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

3.4.1. Функція БС (БЗ)

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

3.4.3. Функція ПС (ПЗ)

3.4.4. Функція ПЛТ (ППЛАТ)

Контрольні питання до лекції 3

Задачі до теми 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

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

5. Основи управління базами даних

5.1. Основні поняття баз даних

5.1.1. Інформаційна модель предметної області

5.1.2. Бази даних

5.1.3. Системи управління базами даних

5.1.4. Моделі даних

5.1.5. Реляційна модель даних

5.2. Система управління базами даних MS Access

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

5.2.2. Зв’язування таблиць та формування схеми даних

5.3. Форми

5.3.1. Робота с формами даних

5.3.2. Пошук записів за допомогою форм

5.3.3. Фільтрація записів за допомогою форм

5.3.4. Підпорядковані форми

5.4. Створення та редагування форм даних

5.4.1. Створення форми за допомогою майстра

5.4.2. Елементи форми

5.4.3. Обчислювані поля

5.4.4. Створення підпорядкованих форм

5.5. Створення кнопочних та діалогових форм

5.5.1. Створення кнопочних форм

5.5.2. Створення диалогових форм

5.6. Запити до бази даних

5.6.1. Запити на вибірку даних

5.6.2. Запити на вибірку з параметрами

5.6.3. Запити на вибірку даних з декількох таблиць

5.6.4. Перехресні запити

5.6.5. Запит на пошук записів, що повторюються

5.6.6. Запит на пошук записів без підлеглих

5.6.7. Запити на зміну даних

5.7. Звіти

5.7.1. Побудова звітів за допомогою майстра

5.7.2. Групування та сортування даних у звіті

5.7.3. Редагування звітів в режимі конструктора

5.7.4. Обчислення у звітах

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

Задачі до теми 5

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

Вступ

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

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

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

Для аналізу господарської діяльності використовуються економіко-математичні моделі. В табл. 3 наведений кратний перелік цих моделей, аналітичних методів і програмних продуктів, які були створени для рішення економічних задач [5, 6].

Таблица 3. Економіко-математичні моделі

3.2. Табличний процесор 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.3. Робота зі списками і підведення підсумків

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.4.1. Функція БС (БЗ)

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

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

Задача 2. Фірма вирішила відкладати на депозит по 10000 у.е. щорічно під 14% річних. Скільки буде на рахунку фірми через 3 роки? Розв’язання. Викликати функцію БС з аргументами: Ставка = 14%, Кпер=3, Плт = -10000, (Пс = 0). Відповідь: 34396.

Задача 3. Фірма вирішила покласти на депозит 10000 у.е. під 14% річних. Скільки буде на рахунку фірми через 3 роки. Розв’язання. Викликати функцію БС з аргументами: Ставка = 14%, Кпер = 3, (Плт = 0), Пс = -10000. Відповідь: 14815,44.

Задача 4. Передбачається вкласти в банк 1000 у.е. під 6% річних і вкладати потім по 100 у.е. на початку кожного з наступних 12 місяців. Скільки грошей буде на рахунку наприкінці 12 місяців? Розв’язання. Викликати функцію БС з аргументами: Ставка = 6%/12 = 0,5%, Кпер = 12, Плт = -100, Пс = -1000, Тип = 1. Відповідь: 2301,40.

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

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

КПЕР(Ставка, Плт, Пс, Бс, Тип);

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

Задача 5. Скільки місяців знадобиться для виплати позики в 1000 у.е., взятої під 12% річних, якщо передбачається виплачувати по 100 у.е. щомісяця? Розв’язання. Викликати функцію КПЕР з аргументами: Ставка = 12%/12 = 1%, Плт = -100, Пс = 1000, (Бс = 0). Відповідь: 11.

Задача 6. Передбачається вкласти в банк 1000 у.е. під 12% річних і вкладати потім по 100 у.е. щомісяця, поки на рахунку не стане 10000. Скільки місяців необхідно для цього?  Розв’язання. Викликати функцію КПЕР з аргументами: Ставка = 12%/12, Плт = -100, Пс = -1000, Бс = 10000. Відповідь: 60.

3.4.3. Функція ПС (ПЗ)

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

ПС(Ставка, Кпер, Плт, Бс, Тип);

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

Задача 7. Банк пропонує Вам викупити приміщення відразу за 10000 у.е. чи платити по 600 у.е. щомісяця протягом двох років при ставці 8% річних. Визначите, який з цих варіантів вигідніший. Розв’язання. Викликати функцію ПС з аргументами Норма = 8%/12 = 0,6%, Кпер = 24, Плт = -600, (Бс = 0). Відповідь: Функція ПС повертає значення більше 13000, отже, перший варіант є більш вигідним.

Задача 8. Яку суму потрібно покласти в банк на депозит, щоб через 4 роки вона досягла 100000 у.е. при нарахуванні 10% річних? Розв’язання. Викликати функцію ПС з аргументами Ставка = 10%, Кпер = 4, (Плт = 0), Бс = 100000. Відповідь: -68301,35. Відповідь негативна, оскільки гроші потрібно віддати.

Задача 9. Банкпропонує Вам вкласти в проект 25000 у.е., що буде приносити щомісяця по 500 у.е. протягом 5 років при 8 відсотках річних. Визначте, чи варто інвестувати цей капітал. Розв’язання. Викликати функцію ПС з аргументами Ставка = 8%/12 = 0,6%, Кпер = 5*12 = 60, Плт = 500, (Бс = 0). Відповідь: -24659,22, отже, дана інвестиція недоцільна.

3.4.4. Функція ПЛТ (ППЛАТ)

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

ПЛТ(Ставка, Кпер, Пс, Бс, Тип);

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

Задача 10. Банкдає в кредит 50000 у.е. на придбання житла під 10% річних з розстрочкою виплат на 20 років. При цьому 20% кредитної суми потрібно виплатити відразу. Оцініть суму щомісячних виплат по цьому кредиту. Розв’язання. Викликати функцію ПЛТ з аргументами Ставка = 10%/12 = 0,8%, Кпер = 20*12 = 240, Пс = 50000, Бс = - 50000/5 = - 10000. Відповідь: - 469,34. Відповідь негативна, оскільки гроші потрібно віддавати.

Задача 11. Передбачається вкласти в банк 2000 у.е. під 12% річних і потім вкладати гроші щорічно протягом 5 років, поки на рахунку не буде 10000. Оцініть суму щорічних виплат по цьому депозиту. Розв’язання. Викликати функцію ПЛТ з аргументами Ставка = 12%, Кпер = 5, Пс = - 2000, Бз = 10000. Відповідь: - 1019,28.

Контрольні питання до лекції 3

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задачі до теми 3

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

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

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

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

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

5. Ильина О.П. Информационные технологии бухгалтерского учета. - СПб.: "Питер", 2002.

6. Карлберг К. Бизнес-анализ с помощью Excel. 2 изд. – К.: "Вильямс", 2004.

4. Статистичний аналіз і оптимізація

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задача 1. Виходячи з наведеної нижче статистичної таблиці, визначте вигляд лінійної залежності між кількістю раціоналізаторських пропозицій (Х) і доходом фірми (Y). Спрогнозуйте величину доходу при Х = 23.

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

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

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

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

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

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

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

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

Таблиця 9. Вихідний масив функції ЛИНЕЙН

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сума квадратів 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. Статистичні функції і передбачення даних

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

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

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

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

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

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

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

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

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

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

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

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

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

Задача 2. Ваш друг хоче взяти кредит у банку терміном на 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. Як приклад розв’язання задачі оптимізації розглянемо наступну задачу.

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

Розв’язання. Почнемо розв’язання зі створення, так званої, оптимізаційної моделі. Запишемо на робочому аркуші в діапазоні В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?

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

Задачі до теми 4

Використовуючи множинну лінійну регресію, на другому робочому аркуші книги 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.

5. Основи управління базами даних

5.1. Основні поняття баз даних

5.1.1. Інформаційна модель предметної області

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

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

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

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

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

5.1.2. Бази даних

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

Бази даних відрізняються від інших наборів даних такими властивостями:

• структурованість у відповідності з інформаційною моделлю предметної області;

• взаємопов’язаність;

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

• орієнтованість на вирішення спільних (колективних) задач.

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

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

5.1.3. Системи управління базами даних

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

СУБД є складним програмним комплексом, який забезпечує виконання функцій, пов’язаних зі створенням та експлуатацією бази даних, а саме:

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

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

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

5.1.4. Моделі даних

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

• Зовнішня інформаційна модель даних;

• Логічна модель даних (логічна схема збереження даних);

• Фізична модель даних (фізична схема збереження даних).

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

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

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

Протягом років становлення та розвитку інформаційних систем використовувались мережеві, ієрархічні та реляційні моделі даних на логічному (концептуальному) рівні [1,2,3]. У кожній з цих моделей використовуються той чи інший формальний апарат: у мереженій моделі – графи, у ієрархічній моделі – деревоподібні структури. Але більшість сучасних СУБД використовують реляційну модель даних, яку і буде розглянуто далі детальніше. Широке використання такого способу представлення даних пояснюється рядом переваг реляційної моделі даних, зокрема ефективність реалізації та простота використання.

5.1.5. Реляційна модель даних

Назва цієї моделі походить від англійського слова «relation» (відношення). Відношення представляє собою сукупність векторів, які мають однакову кількість компонент (але різні значення). Якщо такі вектори розташувати послідовно один за одним, то вони утворюють таблицю, яка називається реляційною таблицею [4].

В реляційній таблиці, стовпці називаються атрибутами (полями), оскільки вони характеризують одну із властивостей (аспектів) об’єктів. Список назв всіх стовпців (атрибутів) називається схемою відношення. Наприклад, відношення СТУДЕНТЫ може описуватись такою схемою:

СТУДЕНТ = (Номер зал. книжки, Прізвище, Ім.’я, Курс, Група, Дата народження).

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

Не кожна таблиця є реляційною таблицею. Реляційні таблиці задовольняють деяким умовам (аксіомам), зокрема

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

Наприклад, студента можна ідентифікувати за прізвищем, але якщо зустрічаються студенти з однаковим прізвищем, то до ключа слід додати як поле прізвище, так і поля Ім’я та По-батькові. Але може статись, що відомі студенти з однаковим прізвищем, ім’ям, по-батькові. Тоді ключ має містити ще і дату народження.

Підмножина полів таблиці є ключем тоді і тільки тоді, коли виконуються такі умови:

1. Рядки таблиці мають відрізнятись значенням хоча б одного ключового атрибута (унікальність значень ключових атрибутів);

2. Жоден з атрибутів не може бути виключений з підмножини ключових атрибутів без порушення умови унікальності (мінімальність).

Таким чином, прізвище, ім’я та по-батькові та дата народження студента може бути складеним ключем в таблиці СТУДЕНТ. У той же час, номер залікової книжки можна визначити як первинний ключ у цій таблиці, оскільки цей номер унікальний для кожного студента.

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

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

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

Таким чином, реляційну базу даних можна визначити так.

5.2. Система управління базами даних MS Access

Microsoft Access – це система управління базами даних реляційного типа, за допомогою якої можна швидко розробити програмне забезпечення для збереження та аналізу даних [5, 6]. MS Access відноситься до програмного забезпечення класу RAD (Rapid Application Development), що можна перевести як «швидка розробка аплікацій (програм)».

В файлі MS Access зберігаються дані разом з процедурами їх обробки у вигляді одного файлу з розширенням *.mdb[7, 8]. Основними об’єктами бази даних MS Access є:

• Таблиці, які використовуються для збереження даних;

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

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

• Звіти, які призначені для виведення даних на друк;

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

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

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

2. в режимі структури - створення та зміна об’єктів (таблиць, форм, запитів, звітів, модулів, макросів). Перехід в цей режим здійснюється за допомогою команди „Конструктор”. Зміна властивостей об’єкта в режимі „Конструктор” здійснюється за допомогою діалогового вікна „Свойства”.

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

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

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

У наступній таблиці викладені основні способи створення реляційних таблиць в СУБД MS Access. Для вибору методу створення таблиці необхідно натиснути відповідну кнопку у вікні бази даних на вкладці «Таблицы» або натиснути кнопку «Создать» та обрати у вікні метод.

 Таблиця 11. Методи створення таблиць

При визначенні значень атрибутів можна використовувати такі типи даних:

1. Текстовый – рядок до 255 символів;

2. Числовой - числові значення;

3. Дата/время – значення дати та часу;

4. Денежный – грошові значення;

5. Счетчик – унікальні автоматично зростаючі цілі значення;

6. Логический– два можливих значення (True/False, Да/Нет, 0/1);

7. Поле МЕМО – великі текстові фрагменти (до 64 Кбайт);

8. Поле OLE – об’єкти з інших застосувань (малюнки, таблиці і т.і.);

9. Гиперссылка – гіперпосилання на ресурси у мережі Інтернет;

10. Мастер подстановок – дозволяє здійснити підстановку списку значень.

Назва атрибуту визначається у вікні конструктора таблиць у стовпці „Имя поля”, а типа даних – у стовпці „Тип даннях”.

Кожне поле таблиці має набір властивостей, які впливають на спосіб введення та відображення даних в цьому полі. Перелік властивостей поля залежить від типу поля та представляється на вкладці „Общие” у вікні конструктора таблиць. Властивість „Формат поля” слугує для визначення формату представлення значень поля. Властивість „Маска ввода” дозволяє відображати дані у загальноприйнятій формі (наприклад, номер телефону відображати з символами тире). Властивість „Подпись” слугує для підпису, який буде відображатись у заголовку поля (за замовченням підпис поля співпадає з його назвою). Властивість „Значение по умолчанию” дозволяє задати значення, яке буде встановлено за замовченням для кожного нового запису таблиці. Властивість „Условие на значение” використовується для перевірки належності значення, що вводиться у поле, заданому діапазону. Якщо значення є помилковим, то видається повідомлення, текст якого задається у властивості „Сообщение об ошибке”. Поле можна визначити як обов’язкове (властивість „Обязательное поле”) або дозволити залишати його значення порожнім (властивість „Пустые строки”). За допомогою властивості „Индексированное поле” можна визначити індексоване поле, тобто поле, за яким система здійснює прискорений пошук записів. Можна також вимагати унікальність значень індексованого поля, встановивши цій властивості значення „Да (Совпадения не допускаются)”.

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

• у полі „Тип элемента управления” необхідно обрати значення „Список” або „Поле со списком” (в першому випадку можна обирати значення тільки зі списку, а в другому можна також вводити довільні значення);

• у полі зі списком «Тип источника строк» необхідно обрати значення «Таблица или запрос» або «Список значений» (у першому випадку набір значень списку буде відображати вміст бази даних, а у другому випадку – буде постійним);

• при виборі значення «Таблица или запрос» у властивості «Источник строк» необхідно вказати таблицю або запит, за допомогою яких буде сформовано список можливих значень. При цьому у властивості «Присоединенный столбец» визначають номер стовпця, значення якого буде вноситись до поля (тип обраного стовпця має співпадати з типом поля), а у властивості «Число столбцов» вказують кількість стовпців, які будуть відображені на екрані при відкритті списку з шириною, визначеною властивістю «Ширина столбцов»;

•  при виборі значення «Список значений» у властивості «Источник строк» необхідно задати набір значень списку.

Наприклад, в учбовій базі даних «Борей», яка входить до пакету MS Office в якості демонстраційного приклада і викликається по команді „Справка ® Примеры баз даннях ® Учебная база даннях „Борей”” в полі «Код Типа» таблиці «Товары» використовується підстановка у вигляді поля зі списком, у якому відображається два стовпця „Код типа” і „Категория” таблиці «Типы» та здійснюється підстановка значення з поля „Код типа”. Але код типу не відображаються у списку («Ширина столбцов» становить 0 см), тому в списку відображається тільки назва категорії товару, а не його числовий код.

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

5.2.2. Зв’язування таблиць та формування схеми даних

Окремі таблиці не дозволяють представити структуру та взаємозв’язки об’єктів предметної області. Тому наступним етапом створення схеми даних є встановлення зв’язків. Зв’язок – це асоціація (посилання), що встановлюється між таблицями та дозволяє запобігти надлишковості даних. Зв’язок між таблицями встановлюється по полям, які мають однаковий тип даних, але ці стовпці можуть мати різни назви. Встановлення зв’язків у Access здійснюється у вікні «Схема данных» (див.рис. 19), для відкриття якого необхідно виконати команду „Сервис ® Схема данных”. У цьому вікні зв’язки між таблицями відображаються у вигляді ліній, які проведено між полями таблиці.

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

Зв’язок„один до багатьох” (1:М) використовується, коли кожному запису першої таблиці (головна таблиця) може відповідати декілька записів другої таблиці (підпорядкованої таблиці) з тим самим значенням пов’язаного поля. Наприклад, в базі даних «Борей» зв’язок „один до багатьох” встановлено між таблицями «Поставщики» та «Товары» по полю «Код поставщика» (рис. 19). Постачальник може поставляти декілька товарів, однак кожний товар може мати тільки одного постачальника. Поле «Код поставщика»в таблиці «Поставщики» и «Товары» має сумісні типи, а саме у першій таблиці воно має тип „Счетчик”, а в другій таблиці – числовий тип.

Між об’єктами предметної області може існувати співвідношення „багато до багатьох”, коли кожному екземпляру одного об’єкту відповідає багато об’єктів іншого типу та навпаки. Такий тип відношення моделюється за допомогою додаткової таблиці, яка пов’язується з першими двома зв’язками типу „один до багатьох”. Наприклад, в базі даних «Борей» зв’язок між об’єктами «Товары» та «Заказы» типу „багато до багатьох” реалізовано за допомогою проміжної таблиці «Заказано», в якій ключ складається з двох полів «Код заказа» та «Код товара». Ключове поле «Код заказа» таблиці «Заказы» пов’язано з одноіменим полем з таблиці «Заказано» зв’язком типу „один до багатьох”.

Для встановлення зв’язку між таблицями необхідно виконати такі дії:

1. закрити всі таблиці та виконати команду «Сервис ® Схема данных»;

2. додати необхідні таблиці до вікна схеми даних;

3. перетягнути мишею поле зв’язку з однієї таблиці в іншу;

4. у вікні „Изменение связей”, яке викликається подвійним клацанням мишею на зв’язку між таблицями (див.рис. 20), визначити властивості встановленого зв’язку.

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

При введенні даних та внесенні змін до таблиць існує необхідність підтримувати цілісність (несуперечливість) даних. Умовами цілісності даних називають набір правил, які використовуються для підтримки між табличних зв’язків та заборони на випадкове поновлення або знищення пов’язаних таблиць. Встановлення прапорця „Обеспечение целостности данных" дозволяє забезпечити виконання цих умов, а саме

• додавати запис у підпорядковану таблицю (зі сторони „багато”) можна лише при наявності відповідного запису у головній таблиці;

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

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

• встановлення прапорця "Каскадное обновление связанных полей" включає режим автоматичної зміни пов’язаних записів у підпорядкованій таблиці при зміні значень первинного ключа у головній таблиці;

• встановлення прапорця "Каскадное удаление связанных записей" включає режим автоматичного видалення пов’язаних записів з підпорядкованої таблиці при видаленні значення первинного ключа з головної таблиці.

5.3. Форми

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

• дозволяють вводити та редагувати дані в таблицях та запитах (форми даних);

• дозволяють здійснювати управління роботою програми (кнопочні та діалогові форми).

Перелік всіх форм розташовано на вкладниці «Формы» у головному вікні бази даних. Для кожної форми можна за допомогою відповідної кнопки на панелі інструментів форми виконати одну з таких операцій:

• Открыть – відкрити форму в режимі даних;

• Конструктор – відкрити форму в режимі конструктора для зміни структури форми.

5.3.1. Робота с формами даних

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

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

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

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

5.3.2. Пошук записів за допомогою форм

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

На вкладинці «Поиск» можна визначити параметри пошуку записів. Відшукуване значення визначається в полі «Образец», а назва поля, по якому здійснюється пошук, визначається у списку «Поиск в». Спосіб відбору записів та порівняння значень полів зі зразком обирається у списку «Совпадение»:

1. „С любой частью поля” – значенння поля має містити підрядок поля «Образец»;

2. „Поля целиком” - значенння поля має співпадати з рядком поля «Образец»;

3. „С начала поля” - значенння поля має починатися з рядка поля «Образец».

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

5.3.3. Фільтрація записів за допомогою форм

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

1. за виділеним значенням одного з полів (фильтр по выделенному);

2. за складеною умовою відбору записів.

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

• відкрити форму в режимі даних;

• розмістити курсор у поле зі значенням, за яким буде здійснено фільтрацію;

• натиснути кнопку «Фильтр по выделенному» на панелі інструментів;

• переглянути відібрані записи за допомогою кнопок переходу по записах;

• зняти фільтр за допомогою кнопки «Удалить фильтр» на панелі інструментів.

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

• відкрити форму в режимі даних;

• натиснути кнопку «Изменить фильтр» на панелі інструментів;

• сформувати умови відбору записів;

• натиснути кнопку «Применение фильтра» панелі інструментів „Режим формы”;

• переглянути відібрані записи за допомогою кнопок переходу по записах;

• зняти фільтр за допомогою кнопки «Удалить фильтр» на панелі інструментів.

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

Результати фільтрації можуть бути експортовані у файл Excel. Для цього необхідно виконати команду „Сервис ® Связи с Office ® Анализ в MS Excel”. Фільтр можна зберегти у вигляді запиту за допомогою кнопки „Сохранить как запрос”, яка з’являється на панелі інструментів у режимі зміни фільтру.

5.3.4. Підпорядковані форми

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

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

5.4. Створення та редагування форм даних

Для створення форми необхідно натиснути кнопку «Создать» на вкладинці «Формы» у головному вікні бази даних. У діалоговому вікні «Новая форма» необхідно визначити спосіб її побудови:

• за допомогою майстра побудови форм;

• у режимі «Конструктор»;

• за допомогою засобу «Автоформа» на основі визначеного джерела даних (таблиці чи запиту).

5.4.1. Створення форми за допомогою майстра

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

1. У вікні «Новая форма» обрати пункт «Мастер форм»;

2. У наступному вікні «Создание форм» обрати поля, які необхідно відобразити у формі:

- у списку «Таблицы и запросы» обрати таблиці або запити, які будуть виступати джерелом даних для форми;

- у списку «Доступные поля» обрати поля, які необхідно відобразити у формі, шляхом натиснення на кнопку «>» для вибору окремих полів або натиснення на кнопку «>>» для вибору всіх полів (для вилучення полів можна використати кнопки «<» або «<<»).

- після додавання полів з усіх необхідних таблиць або запитів натиснути кнопку «Далее»;

1. Обрати зовнішній вигляд форми: «в один столбец», «ленточный», «табличный», «выровненный»;

2. Обрати стиль оформлення зовнішнього вигляду форми;

3. визначити назву форми.

Для швидкого створення простої форми, які містить усі поля таблиці або запиту, можна скористатись засобом «Автоформа» у вікні «Новая форма». У цьому випадку майстер побудує просту форму, у якій поля будуть розташовані в залежності від обраного типу форми.

5.4.2. Елементи форми

Для створення форми «з нуля» та зміни довільних її елементів використовують режим «Конструктор». Структура форми складається з таких розділів (рис. 23):

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

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

3. область даних – використовується для відображення даних.

Для зміни зовнішнього вигляду форми використовуються:

• кнопка „Автоформат”, яка розташована на панелі інструментів конструктора форм і призначена для зміни стилю представлення форми;

• Панель інструментів „Формат” для зміни зовнішнього вигляду елементів керування;

• Діалогове вікно властивостей форми.

Зв’язок між формою та джерелом даних реалізується за допомогою елементів управління, які розміщують в області даних. Для додавання елементів до форми використовується „Панель элементов”, яка містить наступні кнопки.

Таблиця 12. Панель елементів форми

Додавання поля або елемента управління може бути здійснено наступним чином:

1.  Для додавання поля з таблици або запита треба відобразити список всіх полів за допомогою кнопки "Список полей" на панелі інструментів, обрати в списку поле та перетягти його в форму;

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

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

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

1. „Макет” – властивості, які встановлюються загальний вигляд елементу або об’єкту;

2. „Данные” - властивості, які стосуються даних, що пов’язані з елементом або об’єктом;

3. „События” – властивості, які дозволяють визначити процедури, що будуть виконанні при виникненні певної події;

4. „Другие” – додаткові властивості;

5. „Все” – на цій вкладниці  розташовані усі властивості.

Для більшості елементів управління визначені наступні властивості:

• „Имя” – використовується для ідентифікації елемента;

• „Вывод на экран” – визначає, чи буде елемент відображен у форми;

• „Формат поля” – визначає спосіб відображення даних у полі;

• „Данные” – визначає, які дані будуть відображені за допомогою елементу;

• „Значение по умолчанию” – значення, яке буде автоматично просвоюватись елементу при додаванні нового запису;

• „Условие на значение” – визначає умову, якій має задовольняти вводиме значення;

• „Сообщение об ошибке” – текст повідомлення, яке будо відображено у вікні повідомлення при порушенні умови на вводиме значення і т.д.

Але окрім наведених властивостей кожний елемент керування має деякі притаманні лише йому властивості. Наприклад, поле зі списком має наступні важливі властивості:

• „Присоединенный столбец” – вказує, який стовпець приєднано до базового поля. Дані цього стовпця зберігаються у полі при виборі рядка у списку;

• „Число столбцов” – задає кількість стовпців;

• „Ширина столбцов”  - визначає ширину кожного стовпця;

• „Источник строк” – визначає дані, які будуть використані як елементи списку.

Елементи керування можна додавати до форми і за допомогою майстра. У Access наявні наступні майстри побудови елементів керування: „Группа переключателей”, „Список” і „Поле со списком”, „Кнопка”, „Подчиненная форма/отчет”.

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

1. „Объект «поле со списком» будет использовать значения из таблицы или запроса”;

2. „Будет введен фиксированный набор значений”;

3. „Поиск записи в форме на основе значения, которое содержит поле со списком”.

Якщо форма має джерело даних, то обрати варіант обробки значення:

• „Запомнить значение”, тобто зберегти значення у визначеному полі бази даних;

• „Сохранить в поле”, тобто запам'ятати значення для подальшого використання як значення поточного поля;

• ввести назву списку.
  

5.4.3. Обчислювані поля

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

• ідентифікатори (назви) полів таблиць, запитів, форм, звітів беруть у квадратні дужки [ , ];

• константи, зокрема логічні константи „Истина”, „Ложь”, перед та після дат розміщується символ решітки «#», текстові константи беруть у лапки,

• арифметичні оператори: додавання, віднімання, множення та ділення чисел (+, -, *, /);

• оператори порівняння (=, <, >, <=, >=, <>), логічні оператори (and, or, not), оператор злиття (конкатенації) текстових рядків (&);

• стандартні функції Access.

Для побудови виразів може бути використаний „Построитель выражений”. Для виклику цього засобу треба натиснути відповідну кнопку на панелі інструментів. Вікно цього майстра складається з наступних розділів (рис. 24):

1. Зверху розташована зона побудови виразів. Вираз можна вводить безпосередньо з клавіатури або використовуючи нижні розділи;

2. Кнопки операцій – використовуються для додавання операцій та операторів до виразу;

3. Знизу розташований розділ елементів виразів, який містить три списки:

• Список об’єктів, ідентифікатори яких можуть бути використані у виразі: об’єкти бази даних, функції, константи, оператори, загальні вирази (лівий список),

• Список елементів або категорій елементів для об’єкту, який було обрано у першому списку (середній список),

• Список значень (якщо він існує) для елементів, що обрані у середньому списку (правий список).

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

• „Преобразование” – перетворення даних різних типів,

• „База даних” – функції роботи з базою даних,

• „Дата/время” – функції обробки даних, які представлені у форматі дата або час,

• „Математические” – основні математичні функції,

• „Управление” – функції умовного переходу та вибору,

• „Статистические” - основні статистичні функції,

• „Текстовые” - основні текстові функції.

В якості приклади обчислюваного поля розглянемо поле «Итого» форми «Заказы» бази даних «Борей» (див. Рис. 22). В цьому полі обчислюється значення суми замовлення з урахуванням вартості доставки, тому властивість «Данные» цього поля містить формулу:

= [Промежуточная сумма] + [Стоимость доставки] .

5.4.4. Створення підпорядкованих форм

Підпорядкована форма – це форма, яка пов’язана з іншою (головною) формою за деякою сукупністю полів. Підпорядковані форми використовуються для представлення даних з таблиць та запитів, що пов’язані відношенням “один до багатьох” так, що головна форма представляє сторону “один”, а підпорядкована форма сторону “багато”. Відображення даних у підпорядкованій формі синхронізовано з головною формою: у підпорядкованій формі з’являються тільки ті записи, які пов’язані з записами головної форми. Приклад підпорядкованої форми з бази даних «Борей».

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

Для створення підпорядкованої форми одночасно з головною необхідно на другому кроці роботи майстра побудови форм у полі «Таблицы и запроси» обрати один з наступних варіантів:

• запит, який об’єднує декілька зв’язаних таблиць, або;

• послідовно обрати декілька таблиць та поля, які необхідно представити у формі.

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

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

1. натиснути кнопку „подчиненная форма/отчет” на панелі елементів;

2. розмістити підпорядковану форму у головной формі;

3. У вікні „Мастер подчиненных форм” установити перемикач у пункті „Имеющиеся таблицы и запроси”;

4. натиснути кнопку „Далее”;

5. Обрати таблиці або запити та поля, які необхідно представити у підпорядкованій формі;

6. Визначити зв’язки між головною та підлеглою формами: обрати запропонований варіант зі списку або самостійно визначити зв’язки між формами за допомогою майстра;

7. Визначити ім’я підпорядкованої форми.

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

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

1. додати елемент типу „Поле” в область приміток підпорядкованої форми і надати йому назву, наприклад „Вартість”;

2. записати формулу в комірку „Данные” властивостей цього поля, використовуючи агрегатну функцію, наприклад, =Sum([Цена]);

3. додати елемент типу „Поле” у головну форму;

4. записати в комірку „Данные” властивостей цього поля посилання на значення поля в підпорядкованої форми, наприклад:

=[Подчиненная форма заказов].Форма![ Вартість] .

Прикладом обчислюваного поля, що призначене для обчислення ітогових значень за групою записів підпорядкованої форми, є поле «Сумма» форми «Заказы» бази даних «Борей». В цьому полі наявне посилання на поле «ПромежуточнаяСумма» підпорядкованої форми. В полі «ПромежуточнаяСумма» обчислюється відпускна вартість по всіх товарах поточного замовлення за допомогою формули: =Sum([ОтпускнаяЦена]).

5.5. Створення кнопочних та діалогових форм

5.5.1. Створення кнопочних форм

Кнопочна форма – це форма, яка містить кнопки, при натисненні яких здійснюється виконання визначених на етапі розробки дій, таких як виконання запиту, відкриття звіту, виконання макросу і т.і. Для створення кнопочних форм використовується „Диспетчер кнопочных форм”, який виклається по команді  „Сервис ®  Служебные программы ® Диспетчер кнопочных форм”.

У вікні диспетчера кнопочних форм можна здійснити наступні дії (рис. 27):

1. Створення нової кнопочної форми – команда „Создать”;

2. Зміну кнопочної форми – команда „Изменить”;

3. Знищення кнопочної форми – команда  „Удалить”;

4. Вибір форми, яка буде завантажуватись по замовчанню - команда „По умолчанию”.

Після створення кнопочної форми можна виконати такі операції:

1. Створити командну кнопку у формі за допомогою команди „Создать” та призначити дію, яка буде виконуватись при натисненні на цю кнопку;

2. змінити дію, яка буде виконуватись при натисненні на кнопку за допомогою команди „Изменить”;

3. Знищити кнопку за допомогою команди „Удалить”.

За допомогою командної кнопки у формі можна виконати наступні команди:

1. Відкрити форму даних, ім’я якої обирається у полі зі списком „Форма”;

2. Відкрити звіт, ім’я якого обирається у полі зі списком „Отчет”;

3. Завершити роботу з базой даних;

4. Виконати макрос, ім’я якого обирається у полі зі списком „Макрос”;

5. Виконати програму, ім’я якої вводиться у полі „Функція”.

5.5.2. Створення диалогових форм

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

Для створення діалогової форми необхідно:

1. створити порожню форму у режимі конструктора; 

2. встановити наступні значення властивостей форми:

- властивість«Допустимые режимы» = «Форма»;

- властивість«Всплывающее окно» = «Да»;

- властивість«Модальное окно» = «Да»;

- властивість«Тип границы» = «Окно диалога»;

- властивість«Полосы прокрутки» = «Отсутствуют»;

- властивість«Область выделения» = «нет»;

- властивість«Кнопки перехода» = «нет»;

- властивість«Разделительные линии» = «нет».

Розмістити у формі необхідні елементи керування:

- поля даних для введення кожного з параметрів;

- кнопки для виконання запиту та виходу із вікна.

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

5.6. Запити до бази даних

Важливим аспектом роботи з базою даних є аналіз та обробка даних. В СУБД MS Access аналіз даних здійснюєтсья за допомогою запитів на вибірку даних, а також за допомогою звітів, які використовуються для виведення на друк результатів запитів та  обчислення ітогових значень. Для обробки даних використовуються, так звані, запити на зміну даних (запити на створення, видалення, оновлення таблиць, додаваня записів до таблиць і т.і.).

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

Використовують декілька основних типів запитів:

1. запити на вибірку даних;

2. перехресні запити;

3. запити на зміну: створення таблиці, знищення, оновлення, додавання записів.

5.6.1. Запити на вибірку даних

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

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

Перелік запитів бази даних можна переглянути на вкладниці "Запросы" головного вікна бази даних. Запити на вибірку можуть бути переглянуті у таких режимах:

1. перегляду результатів запиту у режимі таблиці (основний режим перегляду даних);

2. у режимі конструктора, використовується для створення і зміни змісту запиту;

3. у режимі SQL (Structured Query Language), використовується для створення запитів.

Створення запиту на вибірку у режиме Конструктор

Для створення простого запиту на вибірку необхідно перейти на вкладинку "Запросы" та натиснути кнопку „Создать”. У вікні „Новый запрос” обрати пункт „Конструктор” та натиснути кнопку „OK”.

Після того буде відкрито вікно конструктора запитів та вікно „Добавление таблицы”, у якому необхідно обрати таблиці та запити, з яких буде здійснюватись вибірка даних. Кожна обрана таблиця буде відображена у верхній частині вікна конструктора побудови запитів. Вікно „Добавление таблицы” можна також відкрити у вікні конструктора побудови запитів за допомогою кнопки „Отобразить таблицу”, яка розташована на панелі інструментів конструктора запитів.

Потім треба визначити поля таблиць (стовпці), яки необхідно переглянути в запиту (селекція по вертикалі). Для цього у рядку „Имя таблицы” здійснюється вибірка таблиці, а в рядку „Поле” – назва поля із цієї таблиці, котре додається в запит (рис. 28). Кількисть полів в запиту не обмежується. За допомогою прапорців у рядку „Вывод на экран” можна тимчасово приховати деякі поля в запиту визначити ті поля. Для того, щоб додати поле у запит, можна також використати один із наступних варіантів дій:

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

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

У рядку "Сортировка" можна визначити спосіб впорядкування записів у запиті. Наприклад, у запиті на рис. 28 для поля "Категория" визначено порядок сортування за зростанням.

Для визначення умови відбору записів використовується рядок "Условие отбора" (селекція по горизонталі). Наприклад, у полі „Прізвище” можна вказати „Коваленко” (у рядку "Условие отбора") або записати в поле дати формулу "< 01.02.2005", тоді в результаті виконання запросу з’являться тільки записі, яки задовольняють вказаним умовам. На рис.28 визначено умову відбору для поля „ПоставкиПрекращены”, а саме до результату виконання запиту будуть включені лише ті записи, для яких поставки не закінчились (значення поля не дорівнює „Да”). Правила побудови умов відбору детальніше будуть розглянуті у наступному параграфі.

Для збереження запиту необхідно скористатись кнопкою „Сохранить” та визначити назву запиту.

Створеня запиту на вибірку за допомогою майстра 

Для створення простого запиту на вибірку за допомогою майстра необхідно у вікні "Новый запрос" обрати пункт "Простой запрос" або обрати пункт "Создание запроса с помощью мастера" на вкладинці "Запросы" бази даних. Після цього відкривається вікно мастера "Создание простых запросов".

На першому кроці майстра треба вибрати зі списку "Таблицы и запросы" таблиці або запити на базі яких буде створюватись даний запит. Потім зі списку "Доступные поля" за допомогою кнопок зі стрілками треба додати необхідні в запиті поля до списку „Выбранные поля” і натиснути кнопку „Далее”. 

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

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

Використання арифметичних та логічних виразів у запитах

У запитах часто використовуються арифметичні та логічні вирази для визначення обчислювальних полів та визначення умов відбору записів. Нагадаємо, що обчислювальне поле, це поле, в якому відображається результат обчислення деякого виразу. Для побудови виразів використовується засіб „Построитель выражений” (см. раздел 5.5.3). Наприклад, суму замовлення з урахуванням знижки можна визначити за допомогою виразу:

[Цена]*[Количество]*(1 - [Скидка]),

де у квадратних дужках визначені назви полів таблиці.

У рядку "Условие отбора"визначають деякий логічний вираз відбору записів. Якщо для деякого запису значення виразу – істина, то запис дадається до результуючого набору, у супротивному випадку – не додається. В логічному виразі можуть використовуватись оператори, константи, функції, назви полів, імена елементів управління. Логічна умова відбору даних за декількома полямі складається з декількох простих умов, які об’єднані за допомогою операторів "And" (и) або "Or" (или), причому тут діє настуне правило:

• якщо умови задані в одному рядку, то буде використано оператор „И”;

• якщо умови задані в різних рядках, то буде використано оператор „ИЛИ”.

Логічні оператори можуть використовуватись також для об’єднання логічних виразів для одного поля. До логічних операторів відносяться також: „Not” (Ні), „Between” (Між), „In” (В), „Like” (Подібний). Логічний оператор „Not” означає виконання протилежної умови. Оператор „Between”використовується для визначення належності значення вказаному діапазону. Наприклад, якщо в стовпці „Ціна” вказати вираз „Between 100 And 200”, то будут обрані записі ціна в яких знаходиться у діапазоні між 100 та 200.

За допомогою оператора „In” здійснюється перевірка, чи співпадає значення виразу з одним з елементів вказаного списку. Наприклад, якщо в стовпці „Кількість” вказати вираз „Not In(1, 5, 7, 8)”, то будут обрані записі кількість в яких не знаходиться у вказаному списку.  

За допомогою оператора „Like” можна задати шаблон, якому має відповідати значення текстового стовпця. Якщо в стовпці „Прізвище” задати наступний вираз:

Like “Ко*”,

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

• знак зірочка (*) або знак проценту (%) – позначає довільну послідовність символів;

• знак питання (?) або символ підкреслення (_) – позначає довільний один символ.

Групування даних та використання агрегатних функцій

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

Таблица 13. Ітогові (агрегатні) функції

Наприклад, у запиті "Продажи товаров в 1997" бази даних "Борей" використовується групування за полями "Категория", "Марка", "Квартал" з сумуванням за полем "ПродажиТоваров".

Для визначення способу групування записів та обчислення агрегатних функцій у режимі конструктора використовується рядок „Групповая операція”. Для відображення цього рядку необхідно натиснути кнопку „Групповые операции” на панелі інструментів конструкторазапитів і обрати зі списку одне із значень:

- „Группировка” – якщо за полем необхідно здійснити групування;

- „Выражение” – якщо необхідно здійснити обчислення виразу для групи записів;

- „Условие” – якщо необхідно визначити умову на обчислене значення для групи;

- назву агрегатної функції.

5.6.2. Запити на вибірку з параметрами

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

Ім’я параметра береться у квадратні дужки та використовується при побудові умовного виразу запиту. Ім’я параметра буде відображатись у запрошенні ввести значення параметру. Наприклад, запит „Продажи по сотрудникам и странам” бази „Борей” виконується з параметрами для початкової та кінцевої дати, які вводяться користувачем. Умова на значення поля „ДатаИсполнения” має наступний вигляд:

Between [Начальная дата] And [Конечная дата]

де „Начальная дата”, „Конечная дата” – імена параметрів. Запит „Продажи по сотрудникам и странам” є основой для однойменного звіту. Таким чином, стає можливим формування звіту про продажі товарів для різних звітних періодів.

Для введення значення параметра з форми даних або з діалогової форми необхідно при створенні  запиту в якості умови відбору вказати назву елемента форми, в яке введено значення параметру. Наприклад, запит „Выбор счета” бази даних „Борей” використовується для вибірки поточного рахунку форми „Заказы” .

5.6.3. Запити на вибірку даних з декількох таблиць

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

• з кожної таблиці здійснюється відбір визначених полів;

• здійснюється об’єднання сформованих динамічних наборів записів відповідно до зв’язків, які визначені між таблицями;

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

Операція об’єднання записів декількох таблиць є одною з найважливіших у реляційній алгебрі [4]. В залежності від типу зв’язків, які встановлено між таблицями, розрізняють декілька способів об’єднання:

• повне об’єднання  (декартовий добуток), коли між таблицями зв’язки не встановлено;

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

• зовнішнє об’єднання.

Повне об’єднання (декартовий добуток) таблиць (відношень) є найпростішим способом об’єднання, коли всі записи першої таблиці комбінуються з усіма записами з другої таблиці. Таким чином, якщо у першій таблиці було m записів, а у другій n записів, то до результуючий набір буде містити m*n записів, кожна з яких містить усі обрані поля вихідних таблиць. Наприклад, повне об’єднання таблиць „Поставники” та „Клиенты” бази „Борей” буде містити 2639 = 91*29 записів. Такій спосіб об’єднання використовують, якщо необхідно сформувати перелік можливих комбінацій, що задовольняють певній умові. Наприклад, визначити перелік можливих поставок у межах однієї країни, тобто перелік постачальників та клієнтів з однієї країни.

Найчастіше у запиті на вибірку необхідно отримати об’єднання таблиць, між якими існує зв’язок типу «один до багатьох». При цьому лівою таблицею називається та з таблиць, у якій поле зв’язку є ключовим, інша таблиця називається правою таблицею. Таким чином, ліва таблиця знаходиться на стороні „один” зв’язку, а права таблиця знаходиться на стороні „багато”. Внутрішнє об’єднання здійснюється з перевіркою умови на значення полів, за якими встановлено зв’язок: до результуючого набору додаються лише ті комбінації записів, для яких значення у пов’язаних полях і лівої і правої таблиць не є порожніми та співпадають.

Використовується також і зовнішнє об’єднання таблиць:

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

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

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

Наприклад, результат внутрішнього об’єднання таблиць „Клиенты” та „Заказы” буде містити 830 записів, тобто це буде перелік клієнтів, які дійсно здійснювали замовлення. Результат лівого зовнішнього об’єднання цих таблиць буде містити 832 запису, тому що в вихідний набір будуть включені в тому числі і два клієнти, які не здійснювали замовлення.

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

Зміна типу зв’язку здійснюється у діалоговому вікні "Параметры объединения", яке можна відкрити, натиснувши два раза ліву кнопку миші на зв’язку, або за допомогою команди "Параметры объединения" контекстного меню зв’язку. У цьому вікні можна визначити тип об’єднання:

1) Об’єднання тільки тих записів, у яких пов’язані поля обох таблиць співпадають (внутішнє об’єднання);

2) Об’єднання всіх записів з лівої таблиці та тільки тих записів з правої таблиці, у яких значення пов’язаних полів співпадають (ліве зовнішнє об’єднання);

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

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

1) На першому кроці майстра обрати поле "Цена" з таблиці "Товары", поле "Категория" з таблиці "Типы" та поле "Страна" з таблиці "Поставщики";

2) на другому кроці встановити перемикач у положення "итоговый", а у діалоговому вікні "Итоги" встановити прапорець для функції обчислення середнього значення "Avg".

5.6.4. Перехресні запити

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

На рис.35 зображено приклад перехресного запиту, за допомогою якого обчислюється середнє значення ціни товарів з бази даних "Борей". Інформація про ціни товарів відбирається з таблиці "Товары", групування здійснюється за країною виробника товарів (поле "Страна" таблиці "Поставщики") та по типам товарів (поле "Категория" таблиці "Типы"). Середнє значення за полем Цена обчислюється для кожної групи записів, у яких значення полів "Страна" та "Категория" співпадають. Наприклад, середня ціна кондитерських виробів з Австралії складає 785,25 грн. Із Бразилии кондитерські вироби не поставляються, тому на перерині відповідного стовпця та рядка середня ціна не вказана.

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

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

2. натиснути кнопку "Создать" на вкладинці "Запросы" та обрати пункт "Перекрестный запрос";

3. у вікні "Создание перекрестных таблиц" обрати створений на першому кроці запит або потрібну таблицю та натиснути кнопку "Далее";

4. обрати за допомогою кнопки ">" одне або декілька полів групування, значення яких будуть розташовані у назвах рядків та натиснути "Далее";

5. обрати одне поле, значення якого будуть розташовані як назви стовпців, та натиснути кнопку "Далее";

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

7. якщо необхідно обчислити ітогове значення для кожного з рядків, то слід встановити прапорець "Вычислять итоговое значение для каждой строки" та натиснути кнопку "Далее";

8. визначити ім’я запиту.

У режимі конструктора для перехресного запиту відображається додатковий рядок „Перекрестная таблица”, в якому для кожного поля запиту визначається спосіб його відображення зі списку: „Заголовки строк”, „Заголовки столбцов”, „Значение” або „(не отображается)”. Лише для одного поля може бути визначено значення „Заголовки столбцов” та лише для одного поля може бути визначено значення „Значение”.

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

5.6.5. Запит на пошук записів, що повторюються

Серед полів таблиці можна виділити поля, значення яких дозволяють однозначно ідентифікувати записи таблиці, тобто не існує наборів значень цих полів, які зустрічаються у декількох записах. Наприклад, для ідентифікації авіарейсу достатньо визначити номер рейсу та дату вильоту. Один з таких наборів полів визначається як первинний ключ таблиці. Однак, для інших полів таблиці або запитів допускаються повторення. Наприклад, фирма може має багато клієнтів з однієї країни та одного міста. Тоді в таблиці "Клиенты" можуть бути присутні наборі записів з однаковими значеннями в полях "Страна" та „Город”. Запит типу "Повторяющиеся записи" використовується для вибірки з таблиці тих записів, у яких значення визначених полів співпадають. Наприклад, на рис.37 відображено результат виконання запиту на пошук записів, що повторюються, у таблиці "Клиенты" за полями "Страна" та „Город” бази даних "Борей". За цими результатми можна визначити перелік клієнтів з однієї країни та одного міста.

Для того щоб побудувати запит на пошук записів, що повторюються, за допомогою майстра необхідно:

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

2. натиснути кнопку "Создать" на вкладинці "Запросы" та обрати пункт "Повторяющиеся записи";

3. у вікні "Поиск повторяющихся записей" обрати таблицю або створений запит та натиснути кнопку "Далее";

4. визначити поля, які містять значення, що повторюються, та натиснути кнопку "Далее";

5. визначити поля, значення  яких мають бути відображені у результуючому наборі записів та натиснути кнопку "Далее";

6. визначити ім’я запиту.

5.6.6. Запит на пошук записів без підлеглих

Для забезпечення цілісності даних Microsoft Accessавтоматично проводить аналіз з метою запобігання суперечливості даних у таблицях. Наприклад, в таблиці "Заказы" бази даних "Борей" в полі "Код клиента" не можна ввести значення, якого немає в такому самому полі в таблиці "Клиенты", тобто неіснуючий клієнт не може зробити замовлення. Однак,  в таблиці клієнтів можуть буть записи про клієнтів, які не робили замовлень. Для пошуку таких записів і використовується запити типу "Записи без подчиненных".

5.6.7. Запити на зміну даних

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

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

5.7. Звіти

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

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

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

Зміна структури звіту здійснюєтсья у режимі конструктора (рис. 39). У режимі конструктора звіт містить такі розділи:

• „Заголовок отчета” – вміст розділу виводиться лише на першій сторінці звіту;

• „Верхний колонтитул” - вміст розділу виводиться вверху кожної сторінки звіту;

• „Заголовок группы” – необов’язковий розділ, який використовується при групуванні даних, виводиться вверху кожної групи;

• „Примечание группы” – необов’язковий розділ, який використовується при групуванні даних, виводиться внизу кожної групи;

• „Область даннях” – у цьому (основному) розділі розміщують елементи, які забезпечують зв’язок звіту з джерелом даних;

• „Нижний колонтитул” – вміст розділу виводиться внизу кожної сторінки звіту;

• „Примечание отче та”– вміст розділу виводиться лише наприкінці звіту.

Для відображення заголовку та приміток звіту, колонтитулів необхідно у вікні конструктора у меню "Вид" ввімкнути перемикачі «Колонтитулы» та "Заголовок/примечание отчета".

5.7.1. Побудова звітів за допомогою майстра

Для створення звіту за допомогою майстра необхідно у вікні "Новый отчет" обрати пункт "Мастер отчетов" або обрати пункт "Создание отчета с помощью мастера" на вкладинці "Отчеты" головного вікна бази даних.

На першому кроці роботи майстра необхідно сформувати перелік полів, які мають бути розміщені у звіті. Формування переліку здійснюється шляхом вибору зі списку «Таблицы и запросы» таблиці або запиту, де вони розташовані, та додаванні полів за допомогою кнопок ">" та ">>".

На другому кроці необхідно визначити способи групування записів. Для цього можна обрати від одного до десяти полів, кожне з яких буде утворювати окремий рівень групування. Додавання рівнів групування здійснюється за допомогою кнопок ">" та "<", а зміна порядку групування здійснюються за допомогою кнопок з вертикальними стрілками. Для кожного обраних полів можна змінити спосіб групування у діалоговому вікні „Интервалы группировки”, яке викликається за допомогою кнопки „Группировка”.

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

5.7.2. Групування та сортування даних у звіті

Групування записів застосовується у тих випадках, коли дані у звіті необхідно об’єднати за деяким полем з подальшим обчисленням значень за групою записів (проміжні підсумки). Для зміни порядку сортування або групування використовують діалогове вікно „Сортировка и группировка”, яке можна відкрити за допомогою команди „Вид ® Сортировка и группировка” або кнопки „Сортировка и группировка” на панелі інструментів конструктора звітів.

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

На рис.40 наведено діалогове вікно „Сортировка и группировка” для звіту „Продажи по сотрудникам и странам” бази даних „Борей”. Групування здійснюється за полем „Страна” та за обчислювальним виразом  =[Фамилия] & ", " & [Имя], про що свідчать помітки зліва від назви поля. За полем „КодЗаказа” здійснюється лише сортування.

5.7.3. Редагування звітів в режимі конструктора

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

• зміна стиля звіту здійснюється за допомогою кнопки „Автоформат” панелі інструментів;

• для зміни зовнішнього вигляду елемента треба виділити його та використати кнопки панелі інструментів форматування конструктора звітів (панель„Формат”);

• для зміни формата представлення даних треба виділити елемент та в діалоговому вікні „Свойства” змінити властивість „Формат поля”.
  

5.7.4. Обчислення у звітах

Обчислювані поля у звітах можуть використовуватись для обчислень значень по окремих записах або по групі записів. Для створення обчислюваного поля по окремих записах чтобы необхідно:

• додати в область даних обчислюване поле (кнопка з позначкою "аб" на панелі елементів);

• побудувати алгебраїчний вираз у комірки „Данные” діалогового вікна „Свойства”.

Наприклад, поле „Продавец” звіту „Продажи по сотрудникам и странам” містить  вираз =[Фамилия] & ", " & [Имя], в якому прізвище продавця з’єднається з його ім’ям для кожного запису про співробітника. Для того, щоб виконати обчислення підсумкових значень для груп записів необхідно:

1. додати обчислюване поле до одного з розділів:

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

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

1. побудувати в комірці „Данные” вираз, що містить функцію, наприклад, сумування „Sum” або функцію обчислення середнього значення „Avg”.

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

• „Отсутствует” – сумування не здійснюється;

• „Для группы” – сумування здійснюється всередині групи та значення суми встановлюється нульовим при переході до нової групи записів;

• „Для всего” - сумування здійснюється для всіх записів звіту.

Наприклад, у звіті „Итоги продаж по объему” бази даних „Борей” використовується поле „Счетчик” з накопиченням для всіх записів.  Поле розташовано в області даних, воно відображається у звіту для кожного запису джерела даних, збільшуючись на одиницю (рис. 41). Таким чином реалізується наскрізна нумерація усіх рядків звіту.

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

1. Що таке реляційна база даних?

2. Що таке відношення, що таке ключове поле відношення (таблиці)?

3. Дайте характеристику об’єктам MS Access.

4. Для чого використовуються зв’язки між таблицями? Як визначити зв’язок між таблицями у MS Access?

5. Що таке цілісність даних в таблицях MS Access? Що таке каскадне відновлення та віддалення даних в таблицях MS Access?

6. Опишіть процедуру створення таблиці у MS Access.

7. Назвіть типи даних, які можна використовувати при визначенні типів полів таблиці у MS Access.

8. Для чого призначені форми? Опишіть способи створення форм у MS Access.

9. Як виконати пошук у формах MS Access?

10. Як здійснити фільтрацію у формах MS Access?

11. Що таке підпорядкована форма? Опишіть способи створення підпорядкованих форм.

12. Для чого призначені елементи управління? Які елементи управління використовуються при створенні форм в MS Access?

13. Що таке кнопочні форми? Як створити кнопочну форму у MS Access?

14. Що таке діалогові форми? Як створити діалогову форму у MS Access?

15. Що таке запит і які види запитів існують в MS Access?

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

17. Як створити запит в режимі конструктора?

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

19. Що таке вираз? Як побудувати вираз за допомогою засобу "Построитель выражений".

20. Як здійснюється побудова обчислювальних полів у запитах?

21. Назвіть правила визначення умов відбору записів у запитах?

22. Що таке запит з параметрами?

23. Назвіть способи об’єднання таблиць у запитах.

24. Дайте визначення внутрішнього об’єднання та зовнішнього об’єднання таблиць.

25. Для чого використовуються перехресні запити? Опишіть створення перехресного запиту за допомогою майстра.

26. Для чого використовуються запити-дії? Назвіть типи запитів-дій.

27. Назвіть розділи звіту та опишіть їх призначення.

28. Назвіть етапи створення звітів за допомогою майстра?

29. Як здійснюються обчислення та групування записів у звітах?

30. Створити базу даних обліку інформації про співробітників фірми. У базі даних має бути представлена наступна інформація про співробітника:

    • Прізвище, ім’я, по-батькові, посада, оклад для визначеної посади, персональна надбавка для даного працівника, дата найма співробітника, робочий телефон, номер паспорта, дата народження, місто проживання, адреса проживання, домашній телефон, фотографія;

    • Назва філіалу, в якому працює співробітник, ідентифікаційний код філіалу, місто розташування філіалу, адреса філіалу, телефон офісу.

Задачі до теми 5

Створити форми даних :

1. Форму „Співробітник” для представлення інформації про співробітників фірми (поля розмістити на трьох вкладках „Дані про співробітника”, „Особисті дані”, „Фото”);

2. Форму „Філіали” для представлення інформації про філіали фірми, у якої розмістити підпорядковану форму „Підпорядкована форма Співробітник” для представлення службової інформації про співробітників філіалу;

3. Форму „Посади” для представлення інформації про посади фірми;

4. Форму „Міста”  для представлення переліку міст.

5. У формі „Співробітник” та „Підпорядкована форма Співробітник” розмістити обчислювальне поле заробітна плата, яка складається з окладу посади, надбавки за вислугу років – 50 грн. за кожні 5 років служби та персональної надбавки.

6. У формі „Філіали” розмістити обчислювальне поле для представлення сукупного фонду заробітної плати філіалу.

7. Створити головну кнопочну форму, з якої здійснювати виклик форм „Філіали”, „Співробітник”, „Посади”, „Міста”.

8. Заповнити базу даних інформацією про 3-5 філіалів з різних міст, у кожному із яких працює 7-10 співробітників (на 5-7 посадах).

9. Створити звіт „Кадри”, в якому навести перелік співробітників по кожному із філіалів (Назва та ідентифікаційний код філіалу, фамілія, ім’я, по-батькові співробітника, посада, вік та стаж роботи, впорядкувати за прізвищем за зростанням) з нумерацією співробітників для кожного із філіалів. Визначити середній вік та стаж співробітників по кожному із філіалів та в цілому по фірмі.

10. Створити звіт „Зарплата” (параметром звіту виступає дата видачі), в якому навести перелік співробітників по кожному із філіалів (Фамілія, ім’я, по-батькові, номер паспорта, заробітна плата з урахуванням надбавок, податок 13% на всю суму, сума на руки, впорядкувати за прізвищем за зростанням) з нумерацією співробітників для кожного із філіалів. Визначити загальний фонд заробітної плати по кожному філіалу та по фірмі в цілому.

11. Створити базу даних обліку інформації про поставки товарів на склад. У базі даних має бути представлена наступна інформація про товар:

    • Назва, категорія товару, ціна товару, одиниця виміру, назва постачальника, країна, місто та адреса постачальника, телефон постачальника та адреса Web-сторінки;
    • Номер поставки, дата поставки товару, кількість товару у поставці, знижка при поставці, вартість доставки.

Створити форми даних :

1. Форму „Товари” для обліку інформації про товари;

2. Форму „Постачальник” для обліку інформації про постачальників;

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

4. Форму „Категорії” для представлення інформації про категорії товарів.

5. У формі „Поставлено” розмістити обчислювальне поле, в якому обчислювати ціну продажу товару з урахуванням знижки. У формі „Поставки” розмістити обчислювальне поле для представлення суми поставки з урахуванням знижки та вартості доставки товару.

6. Створити головну кнопочну форму, з якої здійснювати виклик форм „Товари”, „Постачальник”, „Поставки”, „Категорії”.

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

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

9. Створити звіт „Розрахунки” (параметром звіту виступає дата), в якому навести перелік поставок товарів по категоріях (назва, ціна, номер замовлення, дата, кількість, вартість, вартість з урахуванням знижки, вартість з урахуванням доставки, впорядкувати за вартістю за зростанням) з нумерацією товарів для кожної із категорій. Визначити загальну сплачену суму по кожній із категорій та по всім товарам в цілому.

10. Створити діалогову форму „Звіти” для друку звітів, в якій користувач обирає тип звіту „Постачальники” або „Розрахунки” та визначає дату. Якщо обрано тип звіту – „Розрахунки”, то користувач має визначити категорію товару, для якого формується звіт. Форма також містить кнопки „Просмотр” для перегляду звіту, „Друк” для друку звіту та „Отмена” для виходу з форми. Реалізувати виклик форми „Звіти” із головної кнопочної форми.

11. Створити базу даних обліку інформації про клієнтів фірми. У базі даних має бути представлена наступна інформація про клієнта:

    • назва клієнта, прізвище, ім’я, по-батькові директора, контактний телефон, номер рахунку клієнта,

    • країна, місто та юридична адреса клієнта, ідентифікаційний код клієнта,

    • вид робіт, виконання яких замовив клієнт, опис замовлення, сума замовлення, знижка, дата замовлення, дата виконання замовлення, номер акту прийняття робіт,

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

Самостійно спроектувати та створити форми в базі даних.

1. Створити звіт „Клієнти”, в якому навести перелік клієнтів по кожному із видів замовлених робіт (назва клієнта, ідентифікаційний код, номер рахунку, робочий телефон, сума замовлення, сума, яку вже сплачено, сума боргу, впорядкувати за назвою за зростанням) з нумерацією клієнтів для кожного із видів замовлених робіт. Визначити середній розмір замовлення по кожному із видів робіт та загальний розмір боргу.

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

3. Здійснити аналіз даних у навчальній бази даних „Борей” за допомогою фільтрації:

У формі „Заказы” здійснити фільтрацію даних: знайти всі замовлення, які виконані по пошті у 1998 році. Виконати експорт результатів в MS Excel.

У формі „Товары” здійснити фільтрацію даних: знайти всі товари, тип яких – "Кондитерские изделия" або "Мясо/птица". Виконати експорт результатів в MS Excel та зберегти фільтр як запит з назвою „Фільтр”.

У навчальній базі даних „Борей” створити наступні запити

Запит 1. Визначити, на яку суму (з урахуванням знижки) оформив замовлень товарів типу „молочные продукты” кожний із співробітників, що проробив на фірмі хоча б 10 років.

Запит 2. Визначити категорії товарів, що поставлялись у банках з США або з Канади.

Запит 3. Визначити десять найдорожчих замовлень, що були зроблені у 1998 році.

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

Запит 5. Визначити перелік товарів типу „Кондитерские изделия”, які повторно були поставлені клієнтам з Бразилії.

Запит 6. Знайти марки товарів типу „молочные продукты” або „Рыбопродукты”, які постачаються у пакетах.

Запит 7. Яку кількість замовлень обслужив кожний із співробітників, вік якого більше 35 років, та на яку суму (з урахуванням знижки).

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

Запит 9. Знайти постачальників та співробітників з однакових країн.

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

Запит 11. Знайти марки товарів типу „Кондитерские изделия” або „Мясо/птица”, які постачаються у коробках.

Запит 12. Для кожного типу товарів визначити розмір максимальної знижки.

Запит 13. Визначити перелік товарів типу „Молочные продукты”, які повторно були поставлені клієнтам з Австрії.

Запит 14. Визначитикількість та суму замовлень, які були розміщені у різні роки.

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

Запит 16. Визначитикількість та суму замовлень, які доставлені кожним із видів доставки.

Запит 17. Сформувати перелік клієнтів та категорій товарів, які вони не замовляли.

Запит 18. Сформувати перелік марок товарів, які не доставляли до США та Канади.

Запит 19. Знайти постачальників та клієнтів з однакових міст.

Запит 20. На яку загальну суму замовлено товарів кожного типу до кожної країни клієнту (перехресний запит).

Запит 21. Яка кількість товарів зберігається на складі для кожного типу товару та кожної країни постачальника (перехресний запит).

Запит 22. Створити таблицю Таблиця1, до якої додати записи про замовлення товарів (код замовлення, марка товару, категорія товару, назва постачальника та ціна товару), тип яких "Кондитерские изделия", ціна перевищує 2000 р., та замовлення на які були розміщені у 1997 році.

Запит 23. У всіх записах таблиці Таблица1 змінити назву постачальника Forots d'Erables на Forots Erables.

Запит 24. Створити таблицю Таблиця2, до якої розмістити інформацію про товари (марка товару, категорія товару, назва постачальника та ціна товару), тип яких "Фрукты", ціна перевищує 2000 р, та замовлення на які були розміщені у 1998 році.

Запит 25. З таблиці Таблиця2 видалити всі записи про товари, ціна яких більше 2300р.

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

1. Дейт К. Введение в системы баз данных. – К.:, Диалектика, 1998.

2. Куправа Т.А. Создание и программирование баз данных. – М.: Мир, 1991.

3. Когаловский М.Р. Технология баз данных на персональных ЭВМ. – М.: Финансы и статистика, 1992.

4. Мейер М. Теория реляционных баз данных. – М.: Мир, 1987.

5. Келли Дж. Самоучитель Access 97. – СПб.: Питер, 1999.

6. Пушкар О.І. та др. Інформатика: Комп’ютерна техніка, комп’ютерні технології. – К.: Академія (серія Альма Матер), 2001.

7. Стоицкий Ю. Самоучитель Office 2000. – СПб.: Питер, 1999.

8. Харитонова И.А. Microsoft Access 2000. – СПб.: БХВ Санкт-Петербург, 2001.

9. Бекаревич Ю., Пушкина Н. Самоучитель MicrosoftТ Access 2000, 1999 г.   480 с.

10. Боровиков Access 2002. Издательство: Солон-Р, 560c.

11. Коупстейк С. Access 97 шаг за шагом. - М.: Бином, 1998.

12. Послед Б.С. Access 2002. Приложения баз данных. Лекции и упражнения. Издательство: DiaSoft UP, 656c.

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