Тема 6. Бази даних Excel. Графічний аналіз даних.

Тема 6. Бази даних Excel. Графічний аналіз даних.

1. Створення і редагування списку

2. Сортування даних в списку

3. Фільтрація даних в списку

4. Вставка і видалення проміжних підсумків

5. Використання зведених таблиць

6. Створення та редагування діаграм

 

Сортування даних

Програма Excel сортує дані по полями. Кожне ім'я поля в списку можна використовувати для сортування і реорганізації списку. Користувач може задати синхронно три умови сортування.

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

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

٧ виконати команду Дані > Сортування;

٧ у списках полів, що розгортаються, слід послідовно вибрати імена полів списку, по яких буде проводиться сортування, потім вибрати опцію За збільшенням або За зменшенням відповідно до мети сортування. Щоб запобігти міткам стовпців від сортування разом із списком слід в розділі Ідентифікуватиполя вибрати опцію Попідписам;

٧ клацнути по кнопці ОК або натиснути [Enter];

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

٧ для завдання власного порядку сортування у вікні діалогу Параметри сортування в полі Порядок сортування по першому ключу потрібно вибрати бажаний порядок сортування. Його можна задати за допомогою команди Сервіс > Параметри > Списки > у полі Елементи списку ввести елемент списку в потрібному порядку ввести кожний елемент списку, завершувати натисненням Enter), а потім натиснути кнопку Додати.

 

Фільтрація даних в списку

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

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

Критерії порівняння використовують для тих записів, дані в яких точно співпадають із заданим критерієм або потрапляють в певний інтервал. Якщо один або декілька символів критерію невідомі, тоді в записі критерію використовують символи-замінники: зірочка (*) і знак, питання (?).

Для заміщення в критерії одного символу використовують знак (?), питання, а для заміщення будь-якої послідовності символів використовують зірочку (*).

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

٧ = (рівно);

٧ > (більше);

٧ >= (більше або дорівнює);

٧ < (менше);

٧ <= (менше або дорівнює);

٧ < >(не рівно).

 

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

Для отримання записів, які відповідають заданим критеріям, в Excel передбачені три способи: Автофільтр, Форма і Розширений фільтр.

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

Критерій допускає використання умов з одним логічним оператором И або ИЛИ. Автофільтр допускає використання миші.

Форма показує вибрані рядки по одній. Це спрощує редагування даних в записах. Форма не дозволяє використовувати умови, сполучені оператором ИЛИ.

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

Використання автофільтру. Метод фільтрації за допомогою автофільтру є простим. Фільтрація в цьому випадку відбувається автоматично. Щоб скористатися автофільтром потрібно:

٧ встановити курсор в комірку списку або виділити діапазон комірок, які слід відфільтрувати;

٧ виконати команду Дані >Фільтр> Автофільтр.

 

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

Таблиця 1

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

 

Кнопки автофільтру будуть відображені в заголовках стовпців до тих пір, поки фільтр не буде вимкнений. Вимкнути автофільтр можна, повторно скориставшись командою Дані >Фільтр >Автофільтр.

Опції Порожні і Непорожні доступні лише тоді, коли у вибраному для фільтрації стовпці є порожні комірки.

 

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

Опція Умова в списку опцій кнопки автофільтру вибирається тоді, коли потрібно задати декілька умов, сполучених між собою логічним оператор ИЛИ або включити в умову пошуку знак підстановки. Після виконання команди Дані > Фільтр > Автофільтр клацнути мишею на кнопці автофільтру в стовпці, який містить дані для фільтрування, і з|із| розгорненого списку вибрати опцію Умова. Відкривається вікно діалогу Призначений для користувача автофільтр. У цьому вікні можна встановити наступні параметри фільтрування списку:

 

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

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

٧ Відповідність одному з двох критеріїв. Для установки цього параметра слід вибрати оператора порівняння і значення для першого критерію, потім встановити перемикач ИЛИ. Потім вибрати оператора і значення для другого критерію.

 

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

 

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

1). Виділити будь-який комірку списку і вибрати команду Дані>Зведена таблиця.

Майстер запрошує розташування даних, необхідних для створення зведеної таблиці:

Створювати таблицю на основі даних, що знаходяться:

· у списку або базі даних Microsoft Excel;

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

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

· у іншій зведеній таблиці.

 

Якщо передбачається використовувати список, розташований на робочому листі Excel, слід прийняти встановлюване за умовчанням положення перемикача " в списку або базі даних Microsoft Excel". Нажати кнопку Далі та вказати в полі Діапазон (опреділити) діапазон даних БД.

 

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

 

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

3,4). Перетягнути потрібні кнопки полів в область Стовпець Рядок.

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

6). Натиснути кнопку Далі. Встановити в цьому вікні перемикач Помістити таблицю в положення новий лист і натиснути кнопку Готово.

Окрім таблиці на екран буде виведена панель інструментів Зведені таблиці.

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

Створення діаграм

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

Microsoft Excel має широкі можливості для створення і побудови діаграм різноманітних типів. У комірках діапазону міститься безліч значень і «заголовки» для цих значень.

 

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

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

Перед побудовою діаграми необхідно:

· Визначити мету побудови діаграми.

· Виділити дані листа, які потрібно відобразити на діаграмі.

· Визначитися, де оформити діаграму - на окремому листі або листі з таблицею даних.

· Визначити тип діаграми для відображення даних.

Типи діаграм

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

 

 

Таблиця 8

діаграми	Характерні застосування
Діаграма з областями	Надання тенденцій сумарних значень даних у вигляді різноколірних областей за певний період. Серії даних відображаються у вигляді забарвлених областей. Особливість таких діаграм полягає в тому, що Excel розміщує ці забарвлені області одну поверх іншої. Область першої серії даних створюється шляхом нанесення на діаграму лінії, що відображає дані цієї серії, з подальшим закрашенням або затемненням області. Область другої серії даних будується інакше. Спочатку малюється лінія, що відображає сумарні дані першої і другої серії, а потім закрашується область між першою і другою лініями. Решта серій даних відображається на діаграмі подібним же чином. Для побудови лінії третьої серії даних використовується сума значень трьох перших серій даних, після чого закрашується область між другою і третьою лініями і т.д. Таким чином, ці діаграми краще всього відображають зміну в часі. Наприклад, якщо діаграма відображає об'єм продажів основних конкурентів в певному сегменті ринку, то діаграма з областями відобразить зміну в часі об'єму продажів у всій цій області бізнесу. Крім того, діаграми з областями відображають, хоч і не так добре зміна в часі щодо внеску кожної серії в їх загальну суму. Якщо повернутися наприклад діаграми доходів основних конкурентів в деякій області бізнесу, то діаграма з областями дозволить виявити, наприклад, наступну: «Об'єм продажів конкурента А як і раніше росте, але його внесок в загальний сумарний об'єм по всій галузі зменшується».
Лінійна діаграма	Горизонтальне порівняння різноманітних категорій даних. На лінійній діаграмі кожне значення даних відображається окремою горизонтальною лінією. Оскільки на лінійній діаграмі для кожного значення даних використовується окремий маркер, вони виявляють і дозволяють порівнювати окремі значення даних. Ці діаграми зручні тоді, коли категорією даних не є час.
Гістограма	Вертикальне порівняння різноманітних категорій даних. Гістограма має ті ж властивості і особливості, що і лінійні діаграми. Вони і виглядають майже також, за винятком того, що дані на них відображаються вертикальними, а не горизонтальними маркерами. Як і лінійні діаграми, гістограми використовують окремий маркер для відображення кожного елементу даних. Завдяки цьому вони виділяють певні значення даних, надаючи користувачу можливість їх порівнювати. Вони (гістограми) зручні для порівняння окремих значень, що змінюються в часі.
Графік	Відображення динаміки зміни ряду значень по даній категорії за певний період часу. На графіках елементи даних однієї серії відображаються у вигляді складної лінії.
Кільцева діаграма	Порівняння складових частин одного цілого по одній або декількох категоріях. Кільцеві діаграми схожі на кругові, але|та| дозволяють відобразити більше однієї серії даних, поміщаючи кожну з серій, що відображаються, в концентричні кільця. Кожен елемент даних представлений сегментом кільця. Сегмент, що представляє дуже мале значення, ставатиме все більше у міру того, як концентричне кільце віддалятиметься від центру. Візуально порівняти значення в різних серіях украй важко.
Кругова діаграма	Відображення відносин між складовими частинами одного цілого. На круговій діаграмі можна відобразити тільки одну серію даних. На такій діаграмі кожен елемент даних відображається сегментом круга відповідного розміру. Кругові діаграми не такі зручні, як інші типи діаграм. На ній складно відобразити серії даних більш ніж десяти значень, оскільки сегменти круга стануть дуже малі за розміром.
Пелюсткова діаграма	Відображення зміни даних або їх частота щодо центральної крапки. На пелюсткових діаграмах значення даних відрізняються на радіусах, що витікають із загального центру, причому така діаграма має стільки радіальних осей, скільки елементів даних налічується в одній серії. Точки значень даних кожної серії об'єднуються в одну лінію. На перший погляд, пелюсткові діаграми здаються досить складними для аналізу, але вони дозволяють точно визначити кожне значення даних в серії, оскільки кожна точка даних розташовується безпосередньо на осі. Ще одну гідність пелюсткових діаграм - можливість порівняти сукупні значення, тобто підсумкові значення всіх серій даних.
Точкова діаграма	Відображення типу зв'язку між двома і більш рядами даних. Дозволяє візуально аналізувати або одну і більш серії залежних даних, або, взаємозв'язок між двома і більш незалежними серіями даних.
Об’ємна	Відображення набору даних за допомогою тривимірних поверхонь. Ці діаграми дають можливість виявити взаємозалежність, що існує як між окремими серіями даних, так і серед даних однієї категорії.
Бульбашкова діаграма	Різновид точкових діаграм. Вони можуть відображати і залежні, і незалежні серії даних.
Біржова діаграма	Відображення зміни курсу біржових цін.
Циліндрові, конічні, пірамідальні діаграми	Тривимірні варіанти гістограми і лінійної діаграми.
Логарифмі-чні	Графік, побудований на логарифмічній осі.
Комбіновані	Комбінація графіка, гістограми або діаграми з|із| областями на одній діаграмі. Використовуються для зіставлення одного або декількох родів з іншими рядами даних.

 

Діаграму можна будувати на активному листі або на новому.

 

 

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

 

У Excel існує два способи швидкого створення діаграм:

٧ натиснути|натискувати| клавішу [F11]. У книзі створюється окремий лист з ім'ям Діаграма 1.

٧ виконати команду Вставка —> Діаграма і у вікні діалогу Майстер діаграм крок 1 з 4 натиснути кнопку Готово, або прийняти за умовчанням всі установки майстра діаграм.

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

٧ заголовки - це назва діаграми і заголовки осі значень і осі категорій;

٧ осі координат - вісь OY (вісь 1), вісь значень, вісь ОХ (вісь 2) - вісь категорій;

٧ діаграма - виділена прямокутна область для побудови діаграми;

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

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

٧ точка даних - елемент ряду даних, який відповідає значенню одного комірки в електронній таблиці:

٧ мітки даних - значення елементів ряду даних або назви категорій;

٧ шкала - числові ділення на осі значень;

٧ сітка - вертикальні і горизонтальні лінії, паралельні осям координат;

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

٧ стрілка - графічний об'єкт, створений за допомогою кнопки із стрілкою на панелі інструментів Малювання.

Перший етап.

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

2) Клацнути мишею по кнопці на панелі інструментів Стандартна для вибору типу діаграми в першому вікні діалогу Майстер діаграм (крок 1 з 4): тип діаграм.

3) Вибрати на вкладці Стандартні або Нестандартні потрібний тип діаграми і її вигляд і натиснути кнопку Далі для переходу до наступного вікна діалогу майстра діаграм.

Другий етап.

У вікні діалогу Майстер діаграм (крок 2 з 4) перевірити правильність завдання діапазону даних. Якщо діапазон в текстовому полі вказаний правильно, то клацніть по кнопці Далі, В протилежному випадку зміните значення цього поля безпосередньо редагуванням або виділенням за допомогою миші іншого діапазону. Після цього вкажіть спосіб розміщення в таблиці рядів даних: по рядках або стовпцях. На вкладці Ряд можна видалити що існує або додати новий ряд даних. Далі варто проглянути зразок діаграми і, якщо вона задовольняє поставленій меті, клацнути по кнопці Далі для переходу до наступного вікна діалогу.

 

Третій етап.

У третьому вікні діалогу Майстер діаграм (крок 3 з 4) належить встановити параметри діаграми. Ці параметри зібрані на вкладках Заголовки, Осі, Лінії сітки, Легенда, Підписи даних і Таблиця даних.

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

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

 

Четвертий етап.

На четвертому етапі Майстер діаграм (крок 4 з 4) визначають, де слід розмістити діаграму. Якщо встановити перемикач на окремому, то діаграма буде створена на окремому листі, для побудови діаграми на тому ж листі, де розміщена таблиця даних, потрібно встановити перемикач на тому, що є|. Після натиснення кнопки Готово діаграма переноситься на лист.

На кожному кроці роботи майстра діаграм можна повернутися на попередній етап побудови діаграми, натиснувши кнопку Назад або припинити побудову діаграми, натиснувши кнопку Відміна.

Редагування діаграми

Діаграма може знаходитися в трьох станах:

٧ перегляду, коли діаграма виділена по периметру прямокутником;

٧ переміщення, зміни розміру або видалення, коли діаграма по периметру выділена прямокутником з маленькими чорними квадратами (маркерами);

٧ редагування, коли діаграма виділена по периметру штриховою рамкою.

 

Таблиця 9

Параметр формату-вання|	Коротка характеристика параметра
Вигляд|вид|	Набір опцій залежить від вибраного елементу діаграми. Включає колір меж або осей, колір фону, його текстуру, ділення на осях і ін.
Вісь	Установка додаткової осі. Використовується для комбінованих діаграм.
Y- погрішність	Відображення планок погрішностей. Можна вибрати величину погрішності, включаючи фіксоване або відносне значення, стандартне відхилення, стандартну погрішність або задати своє значення.
Написи даних	Відображення напису даних, значень або відсотків від загальної суми.
Порядок рядів	Зміна порядку рядів. Особливо корисно у разі об'ємних діаграм, коли одні ряди закривають інші.
Параметри	Різноманітні опції певних елементів діаграми.
Шкала	Використовується для осі значень. Можна вибрати мінімальне і максимальне значення, ціну основних і проміжних ділення, значення в якому перетинається вісь категорій. Можна відобразити логарифмічну шкалу.
Шрифт	Задає тип, зображення, розмір, спеціальні ефекти шрифту, його колір|цвіт| і фон.
Число	Задає формат чисел: грошовий, процентний, дріб і ін.
Вирівнювання	Дозволяє обертати текст -90° до 90° або виводити тест вертикально.
Розміщення	Використовується для легенди. Дозволяє вибрати місце, де буде показана легенда.

 

Тема 6. Бази даних Excel. Графічний аналіз даних.

1. Створення і редагування списку

2. Сортування даних в списку

3. Фільтрація даних в списку

4. Вставка і видалення проміжних підсумків

5. Використання зведених таблиць

6. Створення та редагування діаграм