Побудова й оформлення діаграм

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

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

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

• вибрати команду Диаграмма з меню Вставка або натиснути кнопку ;

• у діалогових вікнах Мастер диаграмм (рис. 3.11.) слід вибрати тип, вид і інші па-

раметри діаграми;

• для переходу до наступного кроку використовується кнопка “Далее”;

• для побудови діаграми на будь-якому кроці можна натиснути кнопку “Готово”.

Мастер диаграмм самостійно закінчить побудову діаграми;

• в останньому (4-му) вікні натиснути кнопку “Готово”.

Рис. 3.11. Вікно Майстра діаграм

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

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

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

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

Якщо треба додати до існуючих нові дані і потім відобразити їх на діаграмі, слід доданий

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

Щоб видалити діаграму, необхідно натиснути на ній мишею, щоб з’явилися маркери розміру, і натиснути на клавіатурі клавішу <Delete>.

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

Гістограми — застосовуються, коли треба показати динаміку змін за певний промі- жок часу. Данні приводяться у вигляді стовпчиків послідовно. Програма пропонує кілька підтипів гістограм.

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

Графіки — лінії, що відбивають зміну величини чи кількох залежних величин.

Діаграми з областями — нагадують кілька графіків, але дані відображаються товщи-

ною шару.

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

Крапкові діаграми — дають точками зв’язок між осями діаграми. Схожі з графіками.

Їх можна апроксимувати.

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

Біржові діаграми — можуть бути гістограмами чи точковими діаграмами, але для кожного значення показано інтервал коливання величини.

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

Серед стандартного набору діаграм майже завжди можна знайти необхідну, але Excel пропонує можливість створення нестандартних їх варіантів. Починати треба з вкладки “Не- стандартные” Майстра діаграм. Деякі з них роблять дані більш наочними.

В Excel діаграми створюються трьома способами: безпосередньо на поточному арку- ші, на окремому аркуші в поточній книзі та в окремій книзі. Перший спосіб має ту перевагу, що потім є можливість надрукувати діаграму і таблицю на одному аркуші. Якщо діаграма створюється на готовому діапазоні даних, програма встановлює динамічний зв’язок між даними та діаграмою — зміни в таблиці приводять до змін на діаграмі. Щоб створити діаг- раму на окремому аркуші, можна спочатку скористатися допомогою Майстра діаграм, але вже на четвертому кроці при виборі Поместить диаграмму на листе: поставити перемикач в положення отдельном. Новий аркуш автоматично буде названий Диаграмма1, якщо не внести в поле назви аркуша нову назву. Після натискування на кнопку “Готово” буде ство- рено новий аркуш, і він буде розміщений раніш усіх інших. При необхідності слід переміс- тити його в потрібне місце. Є ще один спосіб створення діаграми на окремому аркуші. Для цього необхідно виділити потрібний діапазон і натиснути на клавіатурі клавішу <F11 >. В цьому випадку будуть задіяні настройки з умовчання.

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

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

Заголовок діаграми дає змогу швидко орієнтуватися у великій кількості малюнків і таблиць. Звичайно він розміщується у верхній частині діаграми. Для створення заголовка слід виділити діаграму і викликати контекстне меню. В ньому вибрати пункт Параметры диаграммы, а потім вкладку Заголовки. В перше вікно “Название диаграммы” зліва вво- диться текст назви. Якщо після цього натиснути “OK”, то на діаграмі з’явиться її назва. Але можна ще добавити назви осей. Для цього в наступне віконце вводиться назва осі Х, а по- тім осі Y. Звичайно, що всі введені назви, а також позначення для різних рядів даних не завжди задовольняють своїм зовнішнім виглядом. Можна змінити розмір шрифту, його ко- лір. Все це робиться за допомогою контекстних меню, що викликаються на цих назвах. Такі контекстні меню складаються з двох пунктів: Формат... та Очистить. Пункт Очистить необхідний, якщо треба убрати назву. Вибір пункту Формат... дозволяє вибрати рамку, колір фону, параметри та колір шрифту, варіант розміщення тексту. Програма дає змогу провести форматування позначень на осях. Робиться це викликом контекстного меню на вибраній осі після появи напису “Ось Х (значений)”, наприклад, активізувавши пункт Формат оси, відкриваємо вікно з п’ятьма закладками, які дозволяють підібрати вигляд осі, змінити шка- лу встановлених на ній чисел, вибрати шрифт та колір для цифр, вибрати формат даних на цій

осі.

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

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

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

Іноді буває доцільним на діаграмі чи на аркуші розташувати малюнок. Малюнок мо- жна вставити з файла чи намалювати. Останнє дозволяє панель Рисование, яку перш за все треба вивести на екран, якщо її немає. Крім того, ця панель дає змогу нанести для виділення якоїсь ділянки на діаграмі чи графіку кола, рамку, стрілки. Для отримання кола і квадрата слід утримувати на клавіатурі клавішу < Shift >.

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

— Объект та у вікні вибрати Microsoft Map.

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

 

сторінки задовольняє, то для друку треба натиснути кнопку “Печать”.

ЛЕКЦІЯ 4. ОБРОБКА СПИСКІВ

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

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

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

У полі прихованого переліку Сортировать по (рис. 3.12.) вибирається поле, за яким будуть відсортовані дані і тип сортування:

Рис. 3.12. Вікно "Сортировка діапазона"

• по возрастанию – цифри сортуються за зростанням, текст – за абеткою, логічні вирази – ЛОЖЬ передує ИСТИНА;

• по убыванию – сортування в зворотному порядку.

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

Для сортування даних також використовуються кнопки . Перед їх викорис-

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

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

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

них.

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

звертанні до команди Форма меню Данные Excel читає дані й створює діалогове вікно фо- рми даних (рис. 3.13.). У формі даних на екран виводиться один запис. При введенні або зміні даних у полях цього вікна змінюється вміст відповідних комірок у базі даних.

Рис. 3.13. Приклад форми для вводу даних

Для використання форм даних таблиця повинна мати імена стовпців. Імена стовпців стають іменами полів у формі даних. Поле відповідає кожному стовпцю в таблиці. Форма даних автоматично розгортається так, щоб вивести на екран відразу усі поля в даній табли- ці, до 32 полів за один раз. За допомогою смуги прокручування можна прокручувати записи в базі даних. Позиція виведеного запису вказується у верхньому правом куті. Пересуватись по полях форми можна за допомогою миші та клавіш < Tab > (униз), < Shift >+< Tab > (вгору). Праворуч розташовані такі кнопки:

Добавить – очищує поля для введення нового запису бази даних. Якщо знову натис-

нути кнопку Добавить, то уведені дані будуть додані як новий запис у кінець бази даних.

Удалить – видаляє виведений запис, інші записи бази даних зсуваються. Видалені записи не можуть бути відновлені.

Вернуть – відновлює відредаговані поля у виведеному запису, видаляючи зроблені зміни. Щоб відновити запис, необхідно зробити це перед натисканням клавіші < Enter > або перед переходом до іншого запису.

Назад – виводить попередній запис у переліку. Якщо був визначений критерій за до- помогою кнопки “Критерии”, то кнопка “Назад” виведе попередній запис із тих, що задово- льняють заданому критерію.

Далее – виводить наступний запис у базі даних.

Критерии – очищає поля для введення критеріїв порівняння з операторами порів-

няння для пошуку необхідної підмножини записів.

Правка – слугує для виходу з режиму введення критеріїв. Доступна тільки тоді, коли натиснута кнопка “Критерии”.

Очистить – видаляє існуючий критерій із вікна діалогу. Доступна тільки тоді, коли натиснута кнопка “Критерии”.

Закрыть – закриває форму даних.

Для додавання запису до бази даних необхідно:

— виділити комірку в таблиці, до якої слід додати запис;

— у меню Данные вибрати команду Форма;

— натиснути кнопку “Добавить ”;

— заповнити поля нового запису;

— для переміщення до наступного поля натиснути клавішу <Тab >;

— після введення даних натиснути клавішу < Enter > для додавання запису;

— після додавання всіх необхідних записів натиснути кнопку “Закрыть”.

Нові записи будуть додані в кінець бази даних.

Встановлення інтервалу критеріїв. Критерії бувають двох типів:

1. Критерії обчислення – це критерії, що є результатом обчислення формули. Напри- клад, інтервал критеріїв =F7>СРЗНАЧ($F$7:$F$21) виводить на екран рядки, що мають у стовпці F значення більше, ніж середнє значення розмірів у комірках F7:F21. Формула по- винна повертати логічне значення ЛОЖЬ або ИСТИНА. При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА.

2. Критерії порівняння – це набір умов для пошуку, що використовується для отри-

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

мволів (константою) або виразом (наприклад, Ціна > 700).

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

— виділити комірку в таблиці;

— у меню Данные вибрати команду Форма;

— натиснути кнопку “Критерии”;

— у полях редагування ввести критерії для пошуку даних;

— для виводу на екран першого запису, що відповідає критерію, натиснути кнопку

“Далее”;

— для виводу на екран попереднього запису, що відповідає критерію, натиснути кнопку “Назад”;

— для пошуку записів у переліку по іншому критерію натиснути кнопку “Крите-

рии” і ввести новий критерій;

— по закінченні натиснути кнопку “Закрыть”.

Щоб знову одержати доступ до всіх записів переліку, необхідно натиснути кнопку

“Критерии”, а потім натиснути кнопку “Правка”.

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

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

Рис.3.14. Кнопки зі стрілками в рядку полів

Якщо у прихованому переліку вибрати пункт Условие …, то з’явиться вікно “Поль-

зовательский автофильтр ” (рис. 3.15.). У верхньому правому переліку слід вибрати один з операторів (равно, больше, меньше та ін.), а у полі праворуч вибрати одне зі значень. У нижньому правому переліку можна вибрати іншій оператор, і у полі ліворуч – значення. Коли увімкнений перемикач И, то будуть виводитися тільки записи, які задовольняють оби- дві умови. При увімкненому перемикачу ИЛИ будуть виводитися записи, які задовольня- ють одну з умов. Наприклад, у вікні на рис. 3.15. введені умови для виведення записів по виробах з ціною більше 99 грн. і менше 187 грн.

Рис. 3.15. Вікно "Пользовательский автофильтр"

Для виведення декількох записів з найбільшими або найменшими значенням по будь- якому полю слід у прихованому переліку поля вибрати пункт Первые 10. У вікні “Наложе- ние условия по списку” у першому полі з лічильником необхідно вибрати кількість записів, а у полі праворуч вибрати наибольших або наименьших.

Щоб вивести усі дані переліку, необхідно викликати команду Отобразить все або скасувати команду Автофильтр меню Данные, підменю Фильтр.

Команда Расширенный фильтр дозволяє фільтрувати дані з використанням інтервалу

критеріїв для виводу тільки тих записів, що задовольняють визначеним критеріям (рис. 3.16.). При повторній фільтрації будуть проглядатися усі рядки і приховані і відкриті. Значення перемикачів і полів вікна “Расширенный фільтр” наступне:

Рис. 3.16. Вікно "Расширенный фильтр"

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

няють зазначеному критерію;

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

3. Исходный диапазон – поле, що визначає інтервал, якій містить перелік, що підля-

гає фільтрації;

4. Диапазон условий – поле, що визначає інтервал комірок на робочому аркуші, якій містить необхідні умови;

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

6. Только уникальные записи – перемикач, що виводить тільки рядки, що задоволь- няють критерію і не містять неповторюваних елементів. Якщо інтервал критеріїв не визна- чений, то в цьому випадку усі рядки переліку, що містять дублікати, будуть приховані.

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

1. вставити декілька рядків у верхній частині робочого аркуша;

2. в одному із вставлених порожніх рядків увести імена стовпців, за якими слід від-

фільтрувати перелік;

3. при використанні критеріїв порівняння, імена критеріїв повинні бути ідентичні іменам стовпців, що перевіряються;

4. у рядках, розташованих під рядком із іменами стовпців, що перевіряються, увести критерії, яким повинні відповідати комірки стовпців, що перевіряються;

5. вибрати в меню Данные підменю Фильтр, а потім команду Расширенный фильтр, і в діалоговому вікні увести умови фільтрації.

Для об'єднання критеріїв за допомогою умовного оператора И потрібно зазначити критерії в одному і тому ж рядку, а для об'єднання критеріїв за допомогою умовного опера- тора ИЛИ слід подати критерії в різних рядках. Наприклад, інтервал критеріїв на рис. 3.17. виводить на екран усі записи, що мають у стовпці Цена значення більше 50 і менше 100.

Рис. 3.17. Дані, що задовольняють інтервалу критеріїв

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

Щоб знову вивести усі записи, слід у меню Данные вибрати пункт Фильтр і потім команду Отобразить все.

ЛЕКЦІЯ 5.АНАЛІЗ ТАБЛИЦЬ

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

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

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

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

Рис. 3.18. Вікно "Консолідація"

У графі Функция слід вказати спосіб підбиття підсумків. Звичайно там встановлюєть- ся опція Сумма, але можна задати й інший спосіб підведення підсумків: Количество значе- ний, Среднее, Максимум, Минимум тощо.

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

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

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

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

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

Для полегшення роботи з вихідними областями задайте ім'я кожного діапазону і ви-

користовуйте в поле Ссылка тільки імена.

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

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

Якщо вихідні області і область призначення знаходяться на різних аркушах, то вико- ристовуйте ім'я аркуша і ім'я чи посилання на діапазон. Наприклад, щоб включити діапазон із заголовком “Витрата”, що знаходиться в книзі на аркуші “Квартал”, введіть: Квар- тал!Витрата.

Якщо вихідні області й область призначення знаходяться в різних книгах, то викорис- товуйте ім'я книги, ім'я аркуша, а потім — ім'я чи посилання на діапазон. Наприклад, щоб включити діапазон “Продажі” з аркуша “Київ” у книзі “2000”, що знаходиться в цій же па- пці, введіть: '[2000.xls]Київ!Продажу

Якщо вихідні області й область призначення знаходяться в різних книгах різних ката-

логів диска, використовуйте повний шлях до файлу книги, ім'я книги, ім'я аркуша, а потім

— ім'я чи посилання на діапазон. Наприклад, щоб включити діапазон “Оборот” аркуша

“Лютий” у книзі “Відділ продаж”, що знаходиться в папці “Бюджет”, уведіть:

'[C:\Бюджет\Відділ продаж.xls]Лютий'!Оборот

Підсумки (Итоги). У програмі Excel передбачене виконання багатьох дій з підрахун- ку і представлення даних, зібраних у таблиці. Зокрема, за допомогою команди Данные — Итоги для значень одного із стовпців можна провести добір і аналіз даних, розташованих в інших стовпцях таблиці. Наприклад, визначити суму виторгу для кожного дня продажу, визначити, на які дні тижня припадає максимум мінімум чи продажем визначити статисти- чні характеристики роботи. Підбиття підсумків можна виконувати тільки після виконання відповідного сортування тих даних, за якими проводиться аналіз. Сортування необхідне тому, що рядки з підсумками додаються там, де відбувається зміна значення даних. Напри- клад, підсумок по даті додається після всього списку даних для кожної дати в стовпці. То- му значення, за якими підбивається підсумок повинні групуватися разом, а не бути розки- даними по таблиці. Закінчивши сортування, і вибравши в меню Данные пункт Итоги, на екран виводиться вікно (рис. 3.19.), в якому варто поставити програмі завдання:

У вікні “При каждом изменении в: ” необхідно вибрати один із пропонованих стовп- ців. Якщо, наприклад, вибрати Дата, то рядок підсумків буде з'являтися всякий раз перед зміною дати.

У вікні “Операция:” програма пропонує вибір для визначення суми, середнього ариф- метичного, максимального чи мінімального значення, підрахунок однієї зі статистичної величини.

У вікні “Добавить итоги по:” вибираються стовпці, у яких проводиться підрахунок. Тут слід зазначити найменування стовпців, в яких варто робити обчислення суми, серед- нього і т.ін.

Рис. 3.19. Встановлення завдань у вікні "Промежуточные итоги"

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

Натиснути мишкою на кнопці “ОК”.

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

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

Зведені таблиці (сводные таблицы) є ще одним засобом підбиття підсумків. Щоб по- будувати зведену таблицю, помістіть курсор в середині бази даних, виберіть у меню Дан- ные команду Сводные таблицы. У результаті відкривається вікно “Мастер сводных таб- лиц и диаграмм”. Майстер у три кроки проведе до повної побудови зведеної таблиці. Анг- лійською зведена таблиця називається “pilot Table”, що можна приблизно перекласти, як “керована таблиця”. Це зв'язано з тим, що після її створення нею можна як би вертіти, пере- творюючи стовпці в рядки і навпаки. Після відкриття вікна “Мастер сводных таблиц и диа- грамм” з'являється перше її вікно, де треба вказати розташування вихідних даних для по- будови зведеної таблиці:

ƒ у списку чи базі даних Microsoft Excel;

ƒ в зовнішньому джерелі даних;

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

ƒ в іншій зведеній таблиці чи діаграмі,

а також вказати вид створюваного звіту: зведена таблиця чи зведена діаграма.

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

У вікні третього кроку необхідно насамперед вибрати, куди помістити результат. Крім цього, програма дозволяє задати деякі параметри нової таблиці, натиснувши кнопку “Параметры”. Вікно “Параметры сводной таблицы” розділено на три зони:

— віконце завдання імені таблиці;

— діалогова зона формату, де задаються деякі параметри висновку;

— діалогова зона даних, де можна вказати умови виводу даних і їх відновлення.

Натиснувши “OK” чи “Отмена”, повертаємося у вікно третього кроку. Кнопка “Ма-

кет” відкриває ще одне вікно, що дає змогу настроїти макет зведеної таблиці.

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

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

При виконанні цієї команди на екран виводиться вікно “Диспетчер сценариев”

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

Рис. 3.20. Вікно "Диспетчер сценариев"

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

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

Надалі за допомогою “Диспетчер сценариев” можна переглядати і редагувати сценарії чи використовувати їх у звітах.

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

Лекція 6. ЗАГАЛЬНА ХАРАКТЕРИСТИКА Microsoft Access.

1. ПОНЯТТЯ БАЗИ ДАНИХ ТА СИСТЕМИ КЕРУВАННЯ БАЗОЮ ДАНИХ (СКБД)

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

Комп’ютерна СКБД — це програма, призначена для запису, пошуку, сортування,

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

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

Microsoft Access — це програма, призначена для роботи з реляційними базами даних

у середовищі Windows. В Access поняття “база даних” стосується одиничного файла, що вміщує всю інформацію. Це файл з розширенням mdb, який містить дані у вигляді однієї чи декількох таблиць. СУБД Access орієнтована на роботу з об’єктами бази даних, до яких відносяться: таблиці; запити; форми; звіти; сторінки; макроси; модулі.

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

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

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

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

Сторінка — забезпечує роботу з базами даних у вигляді WEB-сторінок, забезпечує роботу в комп’ютерній мережі Internet.

Макрос —використовують для автоматизації дій, які регулярно повторюються.

Дата добавления: 2014-01-07