Задача 11. Аналіз сценаріїв покупки

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

Задача 12. Побудова зведеної таблиці

Створити таблицю з 15 рядків, яка відображає облік прода­них фірмою товарів з такими назвами стовпців: Номер опе­рації, Назва товару, Ціна, Кількість, Вартість, Дата, Про­давець, Покупець. Назви товарів, фірм-покупців, прізвища продавців, дати повинні повторюватися по три-чотири рази. За­повнити таблицю даними на свій розсуд (див. зразок рис. 52). Побудувати зведену таблицю, яка характеризує ефективність роботи кожного продавця, тобто відображає, які товари він продав, скільки і на яку суму; модифікувати зведену табли­цю, відобразивши в ній дати проведення операцій продавцями. Побудувати зведену таблицю, яка характеризує уподобання клієнта-покупця, тобто відображає, які товари він купив, коли, скільки і на яку суму.

Теоретичні відомості

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

Розв'яжемо задачу 11. За зразком задачі № 1 про товар­ний чек створіть таблицю про купівлю шести видів товарів з такими назвами стовпців: Наз­ва, Ціна, Кількість, Вартість (рис. 49). Заповніть таблицю довільними даними: конкрет­ними назвами товарів, цінами за одиницю кожного товару, кількостями кожного товару.

Введіть формулу для обчислення вартості кожного товару = Ці на* КІЛЬКІСТЬ і скопіюйте її в діапазон Вартість. Виберіть клітинку під цим діапазоном і натисніть на кнопки Автосума і вводу — отримаєте шукану сумарну вартість покупки. Отже, ця клітинка міститиме результат, що залежить від параметрів.

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

Натисніть на кнопку Додати і у новому вікні введіть назву першого сценарію, наприклад varl, зазначте діапазон клітинок, що містять параметри, які досліджуються (у нашому випадку — це клітинки зі стовпця Кількість, наприклад С2:С7), і натисніть на кнопку ОК. Отримаєте вікно зі значеннями клітинок-пара-метрів стартового варіанта покупки. їх значення не змінюйте, натисніть ОК. Додайте новий сценарій з назвою var2, але зна­чення клітинок-параметрів тепер поміняйте довільним чином. Таким способом створіть усі чотири сценарії (рис.50).

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

Результати застосування всіх сценаріїв можна подати у вигляді звіту. Для цього у вікні Диспетчер Сценаріїв натисніть на кнопку Звіт і в новому вікні виберіть тип звіту: Структура, вкажіть клітинку-результат (клітинку зі значенням сумарної вартості покупки) і натисніть на кнопку ОК. Звіт отримаєте на окремій сторінці. Перегляньте його і поекспериментуйте з

 

180

181

 

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

Щоб розв'язати задачу 12, виберіть створену таблицю і вико­найте команди Дані ■=> Зведена таблиця (Pivot Table). Ви­конайте чотири кроки побудови зведеної таблиці. Двічі натисніть на кнопку Далі і перейдіть до найважливішого третього кроку майстра, де створюється структура зведеної таблиці (див. рис. 51).

Поле Продавець перемістіть на макеті структури на поле з назвою Сторінка, поле Назва — на поле Рядок (Строка), поля Кіль­кість і Вартість — на поле Дані. Переконайтеся, що в полі даних діє операція сума: на кнопці має бути напис «Сума по полю Кіль­кість». Якщо діє інша операція (середнє, максимум, мінімум, кіль­кість тощо), то двічі клацніть на назві поля і поміняйте операцію на суму. Перейдіть до четвертого кроку і зазначте, де розміщати зведену таблицю: на новій сторінці чи на поточній. Зазначте клітин­ку, яка буде верхнім лівим кутом зведеної таблиці. Після натискан­ня на кнопку ГОТОВО отримаєте зведену таблицю (рис. 52).

Рис. 52. Зразок розв'язування задачі 12 (основна і зведена таблиці). Щоб модифікувати зведену таблицю, знову виконайте ко­манди Дані О Зведена таблиця і перетягніть поле Дата в поле

 

182

183

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

Самостійно виконайте друге завдання задачі 12.

Деякі задачі аналізу (наприклад, ефективності роботи про­давця тощо) можна розв'язати іншим способом — за допомогою функції СУММЕСЛЩдіапазонІ; умова; діапазон2). Англійська назва функції SUMIF. Функція обчислює суму тих значень з діапазону2, для яких відповідні значення з діапазону 1 задоволь­няють умову. Наприклад, щоб визначити суму виторгу продавця Дацка, у деяку клітинку потрібно ввести формулу =СУММ-ЕСЛЩПродавець; "Дацко"; Вартість). Щоб дізнатися, скільки автомобілів купила фірма "Деол", потрібно ввести формулу =СУММЕСЛИ(Покупець; "Деол"; Кількість). Застосуйте подіб­ним способом цю функцію у своїй практичній роботі.

Хід роботи

Розв'яжіть задачу 11.

Розв'яжіть задачу 12.

Контрольна робота (ЗО хв)

Варіант 1

1. Що таке консолідація даних і як її виконують?

2. Яке значення виразу СРЗНАЧ(МАКС(8; 12); МИН(4; 16))?

3. Які є типи діаграм? Що таке булькова діаграма?

4. Що таке однофакторний регресійний аналіз?

5. Яке значення функції ЯКЩО(8>2; СУММ(2; 8); МАКС(2;8))?

6. Що таке засіб «Пошук розв'язку» (Solver)?

7. З яких частин складаються виплати за кредит?

8. Яке призначення функції БЗ (FV)?

9. Що таке зведена таблиця?

10. Що таке лінія тренду?
Варіант 2

1. Які типи даних опрацьовують за допомогою ЕТ?

2. Яке значення функції СРЗНАЧ(15; 20; МИН(25; 45))?

3. З чого складається діаграма? Що таке пелюсткова діаграма?

4. Що таке багатофакторний регресійний аналіз?

5. Яке значення функції ЯКЩО(АБО(3<5; 4<8); 8; 12)?

6. Яке призначення команди Підбір параметра?

7. Що таке принцип дисконтування?

8. Яке призначення таблиць підстановки?

9. Що таке сценарії?

10. Які є види ліній тренду?

Розділ IV

MS Office. СКБД MS Access

Практична робота № 26 Тема

MS Access. Створення бази даних у вигляді декількох таблиць.

Мета

Уміти створювати структуру таблиці, модифікувати її, вво­дити дані в таблицю.

План

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

2. Типи і властивості полів.

3. Створення таблиць в MS Access.

Задача «Успішність»

Створити базу даних з трьох таблиць: Оцінки (рис. 53), Теле­фони і Адреси з даними для десяти-п'ятнадцяти осіб (учнів, студентів чи співробітників фірми, які атестуються).

Теоретичні відомості

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

Робота з БД складається з таких етапів:

1) створення структури БД;

2) введення даних;

3) редагування структури і даних;

4) відшукання інформації в БД;

5) оформлення звітів.

Для виконання цих робіт є спеціальні програми, такі як MS Access, FoxPro, dBase-системи та інші. їх називають системами керування базами даних (СКБД).

В Access база даних — це файл з розширенням mdb, який містить дані у вигляді декількох таблиць, зазвичай пов'язаних

 

184

185

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

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

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

2. Типи і властивості полів. Нижче наведено приклади типів, назв і значень полів, які можуть траплятися в різних зада­чах.

Назва поля               Тип поля                             Значення поля

Номер                     Лічильник/Числовий       7

Прізвище                Текстовий                          Боженко

Телефон                  Текстовий/Числовий       2972216

Дата народження Дата                                        24.12.75

Меморандум         MEMO                                текст про хобі

Оклад                      Грошовий                          290 грн

Нагороди               Логічний                             має або немає

Біографія                Гіперпосилання               текстовий файл

Фотографія            Об'єкт OLE                         графічний файл

Посада                    Майстер підстановки     інженер

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

довге ціле, 4 байти, 8 байтів), формат (основний, грошовий, про­центний, експоненціальний тощо), кількість десяткових знаків після коми, підпис поля, значення за замовчуванням, умова на значення, повідомлення про помилку введення, обов'язкове поле, індексоване поле. Наприклад, у випадку оцінок умова на значення поля може бути такою: 2 or 3 or 4 or 5, а повідомлення у випадку помилки під час введення — "Помилка! Таких оцінок немає". Це повідомлення отримуватиме користувач, якщо він введе у таке поле як оцінку число 6 тощо. Індексованість зазначають для тих полів, в яких записи будуть упорядковувати чи відшукувати.

Отже, структура таблиці — це структура запису, тобто сукупність назв полів, їхніх типів та властивостей. Структуру визначає користувач під час аналізу конкретної задачі.

3. Створення таблиць в MS Access. Розглянемо, як створити базу даних, що складатиметься з однієї чи декількох таблиць. Для цього після запуску програми MS Access треба виконати таку послідовність команд: у вікні MS Access 97 увімкнути першу радіокнопку Нова (Blank) база даних о ОК ■=> Вибрати потрібну папку і надати назву файлові ■=> Створити о В отриманому головному вікні бази даних активізувати закладку Таблиці ■=> Створити. У MS Access 2003 слід виконати команду створення нової БД в області завдань. Отримаєте головне вікно БД (рис. 54).

 

186

187

 

Якщо з програмою вже працювали раніше, то послідовність команд інша: Файл о Створити БД => На закладці Загальні вибрати піктограму Нова база даних ■=> ОК і т.д... о Створити.

Надалі суттєво не плутати назву файлу БД і назви таблиць, які є в БД. Назва файлу, наприклад studenty, відображається у рядку заголовка головного вікна БД (див. рис. 54), а назви таблиць — на закладці Таблиці.

Є декілька способів створення таблиць:

♦ режим таблиці;

♦ конструктор таблиці;

♦ майстер таблиці;

♦ імпорт таблиці.

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

У режимі конструктора користувач задає:

Рис. 55. Вікно конструктора таблиці. Тут задають назви, типи, властивості полів, ключове поле.

♦ назви полів методом введення назв;

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

♦ описи полів, які є необов'язковими;

♦ властивості полів (лише у разі потреби) методом запов­нення таблиці властивостей (рис. 55).

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

Структуру будь-коли можна модифікувати (змінити). У раніше створену структуру нове поле вставляють командою Вставити ■=> Рядки. Виокремлене поле вилучають командою Редагувати •=> Вилу­чити рядки. Можна змінити порядок розташування полів, перетягуючи їхні назви вниз чи вгору.

Після створення структури вікно конструктора треба за­крити зі збереженням таблиці у файлі на диску з деякою назвою: Оцінки, Телефони тощо.

Щоб увести дані в таблицю, її потрібно відкрити в режимі таблиці з головного вікна бази даних: Закладка Таблиці ■=> Ви­беріть назву ■=> ВІДКрИТИ чи двічі клацніть на назві.

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

Для прискореного введення у поля даних, що є елементами деякого стандартного списку, наприклад, списку посад, назв міст тощо, у конструкторі таблиці для відповідного поля у типі даних потрібно увімкнути майстра підстановки і створити (ввести) спи­сок даних. Цим списком можна користуватися у режимі вве­дення даних у таблицю.

Стовпці можна ховати чи показувати командою Формат О Заховати стовпець/Показати стовпець.

Щоб деякі стовпці не зміщувались під час перегляду широкої таблиці, їх фіксують на екрані командою Формат о Зафіксувати чи вивільняють — Формат ■=> Вивільнити всі стовпці.

Словник

Database Create Tables Design View Field Name Data Type Description Field Properties AutoNumber View Lookup Wizard Validation Rule Validation Text

База даних Створити Таблиці Конструктор Ім'я поля Тип даних Опис Властивості поля Лічильник Вигляд Майстер підстановки Умова на значення

База данных

Создать

Таблицы

Конструктор

Имя поля

Тип данных

Описание

Свойства поля

Счетчик

Вид

Мастер подстановки

Повідомлення про помилку Сообщение об ошибке

Условие на значение

 

188

189

Хід роботи