Інформаційні системи і технології в маркетингу - Навчальний посібник (Пінчук Н. С., Галузинський Г. П., Орленко Н. С.)

Розділ 2

ТЕХНОЛОГІЧНІ ЗАСОБИ ПІДТРИМКИ ПРИЙНЯТТЯ МАРКЕТИНГОВИХ РІШЕНЬ

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

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

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

Необхідність у засобах автоматизації такого стилю роботи спеціалістів і керівників для вирішення управлінських завдань була виявлена та обґрунтована Г. Саймоном (лауреатом Нобелівської премії 1978 р.). Його дослідження були однією з причин появи офісних програм, орієнтованих на комп’ютерну підтримку прийняття рішень методом безпосереднього аналізу спеціалістами складних проблемних ситуацій і прогнозування їх подальшого розвитку. У сучасному Excel є багато інструментів, з допомогою яких кінцеві користувачі можуть безпосередньо проводити оперативний аналіз проблемних ситуацій. Але для цього вони повинні в достатній мірі володіти технологією роботи з ними.

Ефективне відображення інформації — це один з основних напрямів підвищення ефективності сучасних інформаційних систем. У разі подання даних у вигляді масивів рядків і стовпців чисел зрозуміти процеси, які вони відображають, іноді буває надзвичайно важко. Тому при прийнятті маркетингових рішень у тих випадках, коли «сухі» цифри не дають належного ефекту, важливе значення має графічне зображення інформації. Воно не лише ілюструє явище, що вивчається, а відіграє вагому роль у процесі узагальнення та аналізу інфор­мації і є важливим й необхідним доповненням до її табличного подання. Різноманітність поглядів на ту саму інформацію допомагає ко­ристувачу спрогнозувати розвиток ситуації і дійти до збалансованих висновків. Для графічного зображення інформації характерне таке:

воно дає більш стислу, цілісну та узагальнену картину явища, що вивчається;

стає виразнішою порівняльна характеристика показників;

чіткіше виявляються тенденції розвитку явищ та основні взаємозв’язки.

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

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

Використовуючи діаграми, слід дотримуватися таких правил:

— діаграма має полегшувати розуміння зв’язків між даними. Якщо дані на діаграмі складно проаналізувати, то ніякої користі від неї немає і даним слід надати іншого вигляду;

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

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

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

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

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

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

Рис. 2.2.1. Діаграма із зонами

Рис. 2.2.2. Діаграма формату графік

Продаж за рік

Рис. 2.2.3. Лінійна діаграма

Рис. 2.2.4. Гістограма

Рис. 2.2.5. Об’єднана гістограма

Рис. 2.2.6. Гістограма розподілення даних

Рис. 2.2.7. Кругова діаграма

Рис. 2.2.8. Бульбашкова діаграма

 

Порівняння льотно-технічних характеристик вертольотів

Рис. 2.2.9. Використання пелюсткової діаграми для порівняльного аналізу конкуруючих виробів

Транспортний потенціал вертольотів

Рис. 2.2.10. Використання об’ємної гістограми для порівняння двох характеристик конкуруючих виробів

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

Графіки. Вони відображують дані через однакові проміжки часу. На одному графіку можна подавати кілька рядів даних. Дані для графіків мають бути послідовними і без пропусків. Якщо треба показати дані через неоднакові проміжки часу, то графіки не придатні. У такому разі слід використовувати точкову діаграму. Основне призначення графіків — відображення тенденції розвитку процесу чи явища.

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

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

Лінійні (смужкові) діаграми. Відображують ряди даних у горизонтальній послідовності (кількісні дані розташовуються вздовж горизонтальної осі). Основне призначення — акцентування уваги на відображенні порівняльної характеристики ок­ремих рядів даних. Горизонтальні лінійні діаграми (рис. 2.2.3) використовуються здебільшого тоді, коли потрібно показати, хто є лідером у тому чи іншому змаганні. Оскільки смуги спрямовані направо, найдовша смуга означає лідера. Лінійні діаграми — найдоцільніший спосіб відображення даних за результатами продажу. Наведена діаграма унаочнює «лідерство» ІІІ кварталу за обсягами продажу і внесок кожного товару у загальний підсумок продажу.

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

Лінійна діаграма — це гістограма з накопиченням, але повернута на 90°.

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

У лінійних діаграмах виділяються лідери, у гістограмах — кількість (або обсяг) окремих елементів та їх змінення. Відображаючи кілька схожих або зовсім різних наборів даних, мож­на легко порівняти їх елементи. На рис. 2.2.4 наведено гісто­граму обсягів продажу з щоквартальною розбивкою трьох видів товарів за рік.

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

Ще один вид гістограми — гістограма розподілення даних (частот або кількостей попадань елементів даних) за різними їх категоріями. Категорія даних визначається їх попаданням у відповідний інтервал певної ознаки. Кількість попадань відкладається на вертикальній осі, а інтервали — на горизонтальній. На рис. 2.2.6 наведено гістограму, яка показує розподілення працівників за розміром виплачених їм комісійних. На вертикальній осі відкладено кількість попадань у різні категорії виплат, а на горизонтальній — категорії виплат, що різняться на 200 грн. Гістограма такого виду будується з допомогою надбудови Пакет аналізу. Якщо пакет аналізу встановлено (команда Сервіс/Надбудова), доступ до нього здійснюється командою Сервіс/Аналіз даних.

Кругові діаграми. Відображують зв’язок окремих частин (секторів) даних з їх загальною сумою. Кругова діаграма може відоб­ражати лише один набір (ряд) даних. Маленькі сектори в основній діаграмі можна об’єднати в один сектор, а потім показати як окрему діаграму поруч з основною. Основне призначення діаграми — показ співвідношення між окремими частинами.

Це дуже поширені діаграми. Вони часто використовуються у газетах та журналах. Проте інколи некоректно. Здавалося б, кругову діаграму дуже легко тлумачити: всі її частини додаються одна до одної, а разом створюють одне ціле. Але часто буває важко визначити, чого саме. На рис. 2.2.7 відображено обсяги продажу за І квартал трьох товарів. Як бачимо, товар 3 за обсягами продажу посів перше місце. Проте незрозуміло, на підставі яких даних складено діаграму. З неї не видно, за яким показником порівнюються обсяги продажу: за кількістю проданих товарів, за грошовою масою чи ще за чимось.

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

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

Бульбашкова діаграма. Така діаграма показує на площині у вигляді кругів три набори (ряди) чисел. Вона подібна точковій діаграмі, де третя величина відображує розмір круга (бульбашки). За визначення наборів (рядів) даних на другому кроці роботи Подпись: Вироб-ник	Обсяги 
продажу, шт.	Обсяги 
продажу, грн.	Частка ринку, \%
А	14	13 000	20
Б	20	17 000	29
В	18	22 000	26

Майстра діаграм на вкладці Ряд з допомогою миші вказу­ються адреси розташування цих груп даних: Значення Х (горизонтальна вісь діаграми); Значення Y (вертикальна вісь діаграми); Розміри. Перші дві групи визначають координати центрів кругів, третя — розміри відповідних кругів. Для даних, наведених у таблиці, бульбашкова діаграма на рис. 2.2.8 відбиває стан на ринку провідних виробників товару. Як бачимо, фірма Б продає найбільшу кількість продукції, але її рівень продажу (у грн) не є найбільшим.

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

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

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

Побудова діаграми. В Excel, виконавши команду Вставка/Діаграма або натиснувши кнопку Майстер діаграм, можна побудувати різні діаграми. Інформація, необхідна для створення діаграми, вводиться через діалогові вікна. Залежно від версії Excel вигляд цих вікон дещо різниться, але основні принципи інтерфейсу такі самі.

На прикладі побудови графіків для трьох динамічних рядів: у — продаж товару (шт./1000 чол.), х1 — загальний обсяг товарообігу відповідних магазинів (грн/чол.), х2 — забезпеченість населення товаром (шт./1000 чол.), які займають рядки з 4 по 18 (15 періодів) колонок А, В і С (табл. 2.2.1), розглянемо, як будується діаграма в Excel. Для полегшення процесу узагальнення даних бажано відобразити їх у графічній формі.

 

Таблиця 2.2.1

РЕЗУЛЬТАТИ РОЗРАХУНКІВ ПОКАЗНИКІВ ПОПИТУ

 

A

B

C

D

E

F

G

H

I

J

1

№ періоду

Кількість продажу, шт./1000 чол.

Обсяг товарообігу, грн/чол.

Забезпеченість населення товаром, на 1000 чол.

Базисні темпи, \%

2

зростання

приросту

3

t

y

х1

х2

y

х1

х2

y

х1

х2

4

1

14

34,6

112

 

 

 

 

 

 

5

2

17

36,8

125

121,4

106,4

111,6

21,4

6,4

11,6

6

3

17

39,1

139

121,4

113,0

124,1

21,4

13,0

24,1

7

4

19

41,3

152

135,7

119,4

135,7

35,7

19,4

35,7

8

5

18

44,0

165

128,6

127,2

147,3

28,6

27,2

47,3

9

6

21

46,6

179

150,0

134,7

159,8

50,0

34,7

59,8

10

7

20

49,5

191

142,9

143,1

170,5

42,9

43,1

70,5

11

8

22

52,6

203

157,1

152,0

181,3

57,1

52,0

81,3

12

9

23

55,8

214

164,3

161,3

191,1

64,3

61,3

91,1

13

10

22

60,4

224

157,1

174,6

200,0

57,1

74,6

100,0

14

11

23

63,8

234

164,3

184,4

208,9

64,3

84,4

108,9

15

12

23

67,0

242

164,3

193,6

216,1

64,3

93,6

116,1

16

13

22

71,3

249

157,1

206,1

222,3

57,1

106,1

122,3

17

14

24

75,1

253

171,4

217,1

225,9

71,4

117,1

125,9

18

15

25

76,8

255

178,6

222,0

227,7

78,6

122,0

127,7

19

 

 

 

 

 

 

 

 

 

 

20

Середні

20,7

54,3

195,8

149,9

156,6

175,8

49,9

56,6

75,8

 

Побудову графіка продажу товарів в Excel можна виконати так:

Виділити будь-яку клітину в таблиці з даними.

У панелі інструментів клацнути по кнопці Майстер діаграм.

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

Подпись: 			
	Діапазон:	$A$2 : $J$18	
			
	Ряди в:	¡ рядках	
		¤ колонках	
			

У вікні Майстер діаграм (крок 2 з 4): джерело даних діаграми на вкладці Діапазон даних буде встановлено параметри:

Якщо вказаний діапазон має біль­ше рядків, ніж потрібно, їх можна ви­лучити, активізувавши вкладку Ряд. У розглядуваному прикладі треба залишити лише ряд В2:В18, який містить дані про кількість продажу.

5. У вікні Майстер діаграм (крок 3 з 4): параметри діаграми можна змінити параметри обраного типу діаграми. Будь-який з цих параметрів легко змінити і на готовій діаграмі, тому можна просто натиснути кнопку Далі.

6. На останньому, четвертому, кроці обирається спосіб розташування діаграми (на окремому або на будь-якому робочому аркуші разом із даними), після чого натискується кнопка Готово.

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

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

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

Рис. 2.2.11. Основні елементи діаграми

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

Оскільки мінімальна кількість продажу дорівнює 14, то для кращого використання області простору діаграми слід клацнути правою кнопкою миші на осі значень, вибрати пункт меню Формат осі, у діалоговому вікні — вкладку Шкала і встановити відповідну цифру мінімального значення на цій осі. В результаті графік матиме такий вигляд, як на рис. 2.2.12.

Рис. 2.2.12. Остаточний вигляд графіка

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

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

 

Залежність кількості продажу (Y) від забезпеченості населення товаром (Х2)

Залежність кількості продажу (Y) від обсягу товарообігу (Х1)

Рис. 2.2.13. Залежність кількості продажу від основних факторів

Карти даних

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

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

Необхідність використання картографії за оброблення маркетингових даних пояснюється тим, що понад 85 \% їх — географічні відомості: поштовий індекс, номер телефону, адреса і навіть координати. Отже, відображення даних на карті суттєво розширює можливості їх аналізу. Саме тому картографія зі своїми можливостями відображати на одній карті значну кількість різноманітних даних дає змогу приймати серйозні та обґрунтовані маркетин­гові рішення. Передусім, відображені на карті демографічні дані можуть суттєво допомогти у проведенні аналізу запланованих продажу і рекламних кампаній.

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

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

У поставку Excel входять декілька стандартних карт і значна кількість демографічних відомостей. Крім того, є можливість використовувати багато інших карт, які можна створювати самостійно (з допомогою прикладної програми MapInfo для Windows) або придбати в компанії MapInfo Corporation. Якщо потрібні карти, які не входять у комплект стандартної поставки, то їх слід зареєструвати в Excel з допомогою програми роботи з бібліотекою картографічних даних (Datainst.exe).

Вимоги до складання карт. Процес створення карти подібний до процесу створення діаграми. Для цього потрібен ряд даних у робочому аркуші, що складається з колонок. Одна з колонок мусить мати географічні дані певного типу (наприклад, назви країн). Для того щоб правильно записати ці назви у робочому аркуші, можна відкрити робочу книгу Mapstats.xls (шлях до неї: C:Program FilesCommon FilesMicrosoft SharedDatamapData Mapstats.xls). Вона містить велику кількість демографічної інформації. Необхідну частину цих даних можна скопіювати і вставити в будь-яку іншу робочу книгу.

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

У процесах створення карти і діаграми є багато і спільного, і відмінного:

на відміну від діаграми створення карти на окремому аркуші неможливе;

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

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

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

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

на відміну від діаграм змінна категорія (для карти це географічні дані) не обов’язково має бути у першій колонці.

Створення географічної карти. Створення географічної карти — процес, який передбачає такі кроки:

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

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

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

При утримуванні у натиснутому стані кнопки миші визначається місце і розмір карти на робочому аркуші. Карту можна розташувати на будь-якому робочому аркуші або навіть в іншій робочій книзі.

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

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

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

Панель інструментів. Панель інструментів Карта складається з таких кнопок: Вибір елемента, Перенесення, Вказівка центра карти, Виведення підписів об’єктів, Нанесення підписів, Нанесення прапорців, Карта в цілому, Освіжити карту, Оновити карту, Панель оформлення карти, Масштаб. Кожна кнопка має картку підказки, яка з’являється після наближення курсора до кнопки.

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

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

Рис. 2.2.14. Панель оформлення карти

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

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

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

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

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

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

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

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

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

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

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

кнопка заголовка колонки даних з верхньої частини панелі переноситься у ділянку Колонка робочого поля панелі;

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

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

2.2. Аналіз списків

Коли проводиться аналіз даних в Excel, вони найчастіше розміщуються в робочих аркушах у вигляді списків. Тому значна частина інструментальних засобів Excel призначається саме для роботи зі списками. При цьому ефективність цих інструментів ана­лізу з кожною новою версією MS Office зростає. Так, в Excel-2000 реалізовано швидкіший пошук даних, швидкіші впорядку- вання записів списку (сортування) і підраховування підсумкових значень, додано режим розширення форматів і формул. Якщо останній режим включено, то за введення нових даних в кінці списку поля цього рядка форматуються так само, як і в попередніх рядках, а формули, що повторюються в попередніх рядках, копіюються в рядок уведення даних. Щоб формати і формули розширювалися, вони мають повторюватися, принаймні, в трьох з п’яти попередніх рядків списку. Включення і відключення режиму розширення форматів і формул виконуються на вкладці Правки діалогового вікна команди Сервіс/Параметри.

До основних засобів роботи зі списками належать: Форма даних, Функції для роботи з базами даних, Автофільтр, Розширений фільтр, Майстер підстановок, Сортування, Підсумки, Консолідація, Зведені таблиці.

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

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

Рис. 2.2.15. Діапазон даних списку

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

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

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

 

A

B

C

D

E

F

1

Товар

Місяць

Місто

Виторг

Прибуток

Виторг

2

Напої

 

 

>=500

 

<=8 000

3

Продукти

 

 

 

 

 

4

 

 

 

 

 

 

5

Товар

Місяць

Місто

Виторг

Прибуток

 

6

Напої

Січень

Київ

10 000

1 700

 

7

Напої

Січень

Одеса

2 000

340

 

8

Напої

Січень

Львів

1 000

170

 

9

Продукти

Лютий

Київ

5 000

900

 

10

Продукти

Лютий

Одеса

500

90

 

11

Продукти

Лютий

Львів

250

45

 

Рис. 2.2.16. Список з двома діапазонами (діапазоном критеріїв і діапазоном даних)

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

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

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

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

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

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

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

заголовки колонок мають знаходитися в першому рядку діапазону даних. Вони використовуються Excel у разі впорядкування, пошуку та структуризації даних. Тому для розмежування заголовків колонок (назв полів) і розташованих нижче даних не варто використовувати порожні рядки;

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

діапазон даних має бути організований так, щоб у всіх рядках в однакових колонках знаходилися однотипні дані;

у клітини з даними не варто вводити зайві пробіли, оскільки вони впливають на результати сортування (якщо треба змістити текстові дані, слід використовувати відповідні кнопки на панелях інструментів, у тому числі й кнопки Збільшити відступ і Зменшити відступ);

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

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

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

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

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

Наприклад, дані про продаж товарів за виміром Регіон можуть бути організовані з найвищого по найнижчий рівень абстрагування так: Країна-Область-Місто-Район (рис. 2.2.17). Вимір Час може мати навіть два напрями абстрагування. Перший — це Рік-Квартал-Місяць-День, другий — Тиждень-День. Наявність двох напрямів абстрагування за одним виміром пояснюється тим, що підраховування часу за місяцями і за тижнями несумісні.

 

Рис. 2.2.17. Напрями абстрагування даних

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

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

Але таку ж методологію можна застосовувати для вивчення даних під час роботи зі списками Excel, використовуючи такі інструментальні засоби Excel, як Сортування, Група і структура, Фільтри, Консолідація. Ще більше можливостей для вивчення даних за таким методом надає засіб Зведені таблиці, який дає змогу відбирати дані за різними критеріями як із внутрішніх, так і із зовнішніх джерел і створювати інтерактивні таблиці для відоб­раження різноманітних залежностей між цими даними.

Сортування списків

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

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

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

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

Із допомогою полів списків Сортувати за, Потім за та В ос­танню чергу за можна вибрати назву ключа сортування. Тут слід обрати і порядок сортування — за зростанням або за убуванням.

Хоча у діалоговому вікні Сортування діапазону можна ввести не більше трьох ключів, у дійсності проводити сортування можна більш як за трьома полями. Наприклад, щоб упорядкувати дані списку за шістьома ключами, розташованими в колонках, умовно позначених як А, Б, В, Г, Д, Е (де колонка А — ключ найвищого рівня), спочатку треба провести сортування за полями Г, Д, Е, а потім — за А, Б, В.

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

За умовчання список буде впорядковано за рядками. Проте можна також задати сортування за колонками. Це необхідно, наприклад, у випадку, коли перша колонка містить назви рядків, а записи однорідні за колонками. Для цього потрібно у діалоговому вікні Параметри сортування встановити параметр Сортувати/Колонки діапазону.

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

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

Для того щоб задати свій порядок, слід вибрати команду Сервіс/Параметри та активізувати у діалоговому вікні вкладку Списки.

На вкладці Списки представлено всі складені раніше списки. Щоб додати новий, треба на цій вкладці у полі Списки виділити елемент НОВИЙ СПИСОК. У полі Елементи списку з’явиться курсор уведення. Елементи списку вводяться у тій послідовності, в якій вони мають бути представлені за подальших сортувань. Після введення кожного елемента необхідно натискати клавішу Enter. По закінченні введення всіх елементів списку для внесення його у перелік існуючих списків натискується кнопка Додати.

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

Усі введені порядки сортування можуть використовуватись і для функції автоматичного заповнення.

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

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

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

Автофільтр. З допомогою автофільтра вибір окремих рядків таблиці можна здійснювати лише безпосередньо у робочому аркуші. Активізація автофільтра виконується командою Дані/Фільтр/Автофільтр. Для коректного виконання команди покажчик активної клітини (табличний курсор) повинен знаходитись у межах списку даних. Якщо перед виконанням команди було виділено певний діапазон таблиці, то у першому рядку цього діапазону для кожного поля (колонки) даних буде встановлено окрему кнопку для введення критерію фільтрації за цим полем. Дія критерію фільтрації поширюється на всі рядки таблиці, які знаходяться нижче кнопок. Якщо жодного діапазону таблиці не було виділено перед виконанням команди (але покажчик активної клітини знаходився у межах таблиці даних), то місцем розташування кнопок буде перший рядок таблиці, де звичайно знаходяться назви колонок. Після натискання на кнопку розкривається список елементів, які містить відповідна колонка і в якому можна вибрати необхідний елемент для визначення критерію відфільтровування даних. Це приведе до вилучення з екрана тих рядків списку даних, які не відповідають уведеному критерію, і до забарв­лення відповідної кнопки у синій колір. Критерії фільтрації мож- на задавати послідовно для кількох полів. Кожен додатковий критерій зв’язаний із попереднім критерієм умовним оператором «І», через що кожна наступна умова завжди належатиме до тих рядків даних, які будуть отримані за використання попередніх умов.

Критерій фільтрації, що складається з двох умов для однієї колонки, можна визначити після натискання на відповідну кнопку і вибору елемента Умова. На екрані з’явиться діалогове вікно Користувацький автофільтр, у якому можна задати два критерії, об’єднуючи їх умовними операторами «І» чи «АБО». В умовах відбору рядків є можливість задавати і шаблони, використовуючи знаки-замінники — «?» та «*». Знак запитання може використовуватися замість будь-якого одного символу поля, а зірочка — замість будь-якої кількості символів.

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

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

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

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

Подпись: Місто	Назва товару
Львів	ПК 486DX4*
Київ	ПК 486DX4*

Наприклад, якщо потрібно отримати інформацію про продаж у Києві та Львові комп’ютерів 486DX4, то умови відфільтровування даних можна записати так, як це показано праворуч.

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

Для запуску команди з меню Дані вибирається команда Фільтр/Розширений фільтр. На екрані з’явиться діалогове вікно Розширений фільтр, у якому можна вказати:

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

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

діапазон або інтервал критеріїв, тобто адресу місцезнаходження критеріїв фільтрації. Інтервал критеріїв уводиться аналогічно інтервалу списку даних, але слід стежити, щоб у цьому інтервалі не було порожніх рядків, інакше у відфільтрованому списку будуть представлені всі без винятку рядки списку даних. Це пояснюється тим, що відсутнім умовам порожнього рядка відповідають будь-які дані і він з’єднується з іншими рядками критеріїв умовним оператором «АБО»;

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

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

Обчислення групових характеристик

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

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

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

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

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

натиснути кнопку ОК.

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

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

Ще одна можливість, яку дає команда Дані/Підсумки, — це виведення у кожній групі даних кількох типів підсумків з використанням різних операцій. Наприклад, у таблицю з даними про збут комп’ютерів можна додати ще й інформацію про кількість продажу (замовлень) у кожному місті. Для цього ще раз потрібно виконати команду Дані/Підсумки. Табличний курсор при цьому має бути у межах таблиці. Це відкриє діалогове вікно Проміжні підсумки, в якому назва колонки Місто буде занесена у поле При кожній зміні в.

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

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

На рис. 2.2.18 ліворуч від заголовків колонок (A, B, C, ...) розташовано кнопки за номерами рівнів структури (1, 2, 3).

1

 

2

 

3

 

А

B

C

D

E

F

G

H

I

J

K

L

M

N

O

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Підсумки по 1-й групі рядків

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Підсумки по 2-й групі рядків

 

 

 

 

 

 

 

 

· · ·

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

·

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 Підсумки по n-й групі рядків

 

 

 

 

 

 Загальні підсумки

Рис. 2.2.18. Групування рядків таблиці

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

Натискання будь-якої кнопки з номером рівня структури веде до активізації групи її даних і всіх вищих рівнів, а також до приховування всіх груп даних, які належать до нижчих рівнів структури. Так, якщо натиснути (клацнути мишею) кнопку другого рівня структури (з цифрою 2 на рис. 2.2.18), то на екрані залишуться лише групові дані (тобто першого та другого рівнів). Дані третього рівня, тобто дані, що складаються з окремих записів таблиці, будуть сховані, а на кнопках, розташованих ліворуч від схованих даних, символ «–» буде замінено на символ «+». Для повернення детальних відомостей за всіма групами необхідно натиснути на кнопку третього рівня.

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

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

Якщо треба ліквідувати відображення структури даних, слід виконати команду Дані/Група і структура/Вилучити структуру.

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

Подпись: 	
Підсумки по 1-ій групі рядків
	
Підсумки по 2-й групі рядків
	• • •
	
Підсумки по n-й групі рядків
Загальні підсумки

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

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

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

Наступний крок полягає у виборі з меню Дані/Група і структура/Створення структури. Безпосередньо після виконання команди усі рівні структури будуть подані на екрані.

Крім того, користувач може виділити будь-яку частину таблиці й після виконання команди Дані/Група і структура/Групувати у діалоговому вікні вибрати спосіб групування: за рядками чи колонками. Це приведе не до обчислення якихось групових характеристик, а лише до появи лінійки відображення рівнів структури — ліворуч від таблиці (у разі групування за рядками) чи над нею (у разі групування за колонками).

Консолідація даних

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

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

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

відкрити новий аркуш і встановити курсор у клітину, з якої розпочинатиметься зона з підсумковими даними;

виконати команду Дані/Консолідация, що веде до відкриття одноіменного діалогу;

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

у поле Посилання ввести адресу одного з діапазонів даних, що консолідуються (наприклад, дані за січень). Цю адресу (вона задається в абсолютному вигляді) можна набрати на клавіатурі (наприклад, Січень!$A$3:$B$14) або ввести за допомогою миші, клацнувши на ярлику відповідного робочого аркуша, і разом із назвами рядків і колонок виділити діапазон, що консолідується. Після цього, щоб надіслати введену адресу у поле Список діапазонів, треба обов’язково клацнути на кнопці Додати;

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

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

натиснути кнопку ОК.

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

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

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

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

Зведені таблиці

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

Створення зведеної таблиці. До появи Ехсеl-2000 існував лише один спосіб створення зведеної таблиці — з допомогою макета у діалоговому вікні Майстра зведених таблиць. В Ехсеl-2000 з’явився ще один спосіб — з використанням макета в робочому аркуші.

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

для деяких типів зовнішніх джерел даних, включаючи великі бази даних, створення макета на аркуші з його багаторазовими звер­таннями до джерел даних може відібрати багато часу. Наприклад, звіт, заснований на кубі, створеному за допомогою Майстра куба OLAP у MS Query, повільно відображатиме зміни в макеті. Те саме відбуватиметься й у разі роботи з великими не-OLAP базами даних;

в Excel може виникнути дефіцит пам’яті або інших ресурсів, потрібних для створення звіту.

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

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

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

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

із зовнішнього джерела даних (параметр У зовнішньому джерелі даних);

з кількох інтервалів консолідації даних (параметр У кількох діапазонах консолідації). Цей параметр дає змогу будувати багато­рівневі зведені таблиці;

з іншої зведеної таблиці (параметр В іншій зведеній таблиці або діаграмі);

натискується кнопка Далі.

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

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

Цю зону з даними можна вказати в один з таких способів:

якщо вона була попередньо виділена, то її адреса буде автоматично уведена в поле Діапазон діалогового вікна Майстер зведених таблиць — крок 2 з 4;

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

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

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

Другий етап завершується натисканням кнопки Далі.

Рис. 2.2.20. Макет зведеної таблиці

На третьому етапі у діалозі Майстер зведених таблиць — крок 3 визначається зовнішній вигляд зведеної таблиці. Більшу частину діалогового вікна зай­має макет зведеної таблиці (рис. 2.2.20). Цей макет має чотири зони полів: рядок, колонка, дані і сторінка. У правій частині діалогу знаходяться кнопки із заголовками полів вхідних даних (саме тому ці дані обов’язково мусять мати назви колонок).

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

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

Дата

Товар 1

Товар 2

Товар 3

...

Загальні підсумки

 

 

 

 

 

 

 

 

 

 

 

 

Загальні підсумки

 

 

 

 

 

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

Рис. 2.2.21. Розмітка макета

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

у зону Рядок перетягується заголовок поля Дата;

у зону Колонка перетягується заголовок поля Назва товару;

оскільки нас цікавлять обсяги продажу, то в зону Дані перетягується заголовок поля Вартість.

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

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

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

В останньому діалозі визначення зведеної таблиці можна задати клітину, з якої розпочинатиметься зведена таблиця, та її назву. Тут можна також задати й деякі параметри таблиці. Для прискорення оброблення зведених таблиць Excel зберігає копію даних. Якщо копія вхідних даних не робиться, то за подальших змін полів з допомогою команди Дані/Поле зведеної таблиці слід спочатку переобчислювати зведену таблицю, використовуючи команду Дані/Об­новити дані. Це пов’язано з тим, що коли дані не зберігаються, на їх відображення у поточній зведеній таблиці може вплинути інша зведена таблиця поточної робочої книги. Але якщо на машині користувача виникають проблеми з дисковою пам’яттю, то можна вимкнути параметр Зберігати дані з макетом таблиці.

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

Якщо на четвертому кроці створення зведеної таблиці клацнути на кнопці Готово, то на екрані з’явиться зведена таблиця. У разі великої кількості вхідних даних це може відібрати багато часу. Для наведеного прикладу результат матиме такий вигляд, як на рис. 2.2.22.

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

Місто

(Все)

ê

 

Сума за полем Вартiсть

Назва товару

 

 

 

 

 

Дата

ПК486 DX2-66 PCI

ПК 486 DX2-80 PCI

...

ПК 486SX Super40

ПК i586 Pentium-166

Загальний підсумок

11.03.96

0

0

...

775

0

3766

12.03.96

925

0

...

0

3856

4781

13.03.96

0

0

...

0

0

970

14.03.96

0

1862

...

0

0

1862

15.03.96

0

0

...

2295

0

3265

16.03.96

0

0

...

0

3856

3856

Загальний підсумок

925

1862

...

3070

7712

18500

Рис. 2.2.22. Результат побудови зведеної таблиці аналізу обсягів продажу

Між структурою макета (рис. 2.2.20) і структурою зведеної таблиці (рис. 2.2.23), створеною за цим макетом, є безпосередній зв’язок: у кожній з них є кнопки полів (Дата, Назва товару, Місто), розташовані на тих самих місцях. Кнопки полів — це заголовки колонок таблиці з даними. З окремих клітин списку даних під кнопками полів рядків і колонок зведеної таблиці створюються поля, які відіграють роль назв її рядків і колонок. Для даного прикладу назви рядків зведеної таблиці (11.03.96, ... , 16.03.96) формуються з полів колонки Дата списку даних, а назви колонок (ПК 486DX2-66 PCI, ... , ПК i586Pentium-166) — з полів колонки Назва товару списку даних. Клітини зони даних містять результати оброблення вхідних даних. Так, значення клітини зведеної таблиці, що знаходиться на перетині рядка з назвою 12.03.96 і колонки з назвою ПК 486DX2-66 PCI, отримано підсумовуванням значень поля Вартість за усіма рядками таблиці даних, що вміщують відомості про кількість проданих комп’ютерів ПК 486DX-66 PCI 12 березня 1996 р.

 

Зона сторінок (Кнопки полів)

 

Спосіб оброблення даних

Кнопки полів

 

 

 

Кнопки полів

Зона назв колонок (елементи полів)

 

Зона назв рядків (елементи полів)

Зона даних

 

Підсумки за рядками

 

Підсумки за колонками

Рис. 2.2.23. Структура зведеної таблиці

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

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

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

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

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

Поле показує поля зведеної таблиці. Це дає можливість після вибору будь-якої додаткової операції вибрати поле, яке надасть базові дані.

Елемент показує елементи обраного базового поля. Якщо потрібно, тут можна вибрати елемент, який буде базовим.

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

клацнути правою кнопкою миші по полю зведеної таблиці і у контекстному меню вибрати пункт Поле або клацнути лівою кнопкою миші по полю і потім в панелі інструментів Зведена таблиця по кнопці Поле зведеної таблиці. У результаті відкриється діалогове вікно Обчислення поля зведеної таблиці;

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

у списку Поле вибрати поле Дата, а у списку Елемент — базову дату (у даному випадку треба обрати елемент 11.03.96);

клацнути по кнопці ОК.

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

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

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

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

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

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

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

Поля рядків і колонок. Якщо клацнути на клітині поля рядків або колонок зведеної таблиці, а потім виконати команду Поле зведеної таблиці (через меню або використовуючи відповідну кнопку на панелі інструментів), то з’явиться діалогове вікно, у якому можна:

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

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

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

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

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

Ще один спосіб використання полів сторінок — клацнути по кнопці Відобразити сторінки інструментальної панелі Зведені таблиці. Відкриється діалог з переліком усіх полів сторінок, які були визначені у зведеній таблиці. У нашому випадку визначено лише одне поле — Місто. Якщо тепер клацнути по кнопці ОК, то для кожного значення у цьому полі відкриється новий робочий аркуш з відповідними даними, при цьому ім’я аркуша збігатиметься зі значенням поля.

2.3. Засоби для роботи з даними OLAP

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

OLAP-технологія

OLAP (On-Line Analytical Processing — оперативний аналіз даних) — це технологія швидкого аналізу нагромаджених даних, що базується на використанні сукупності засобів багатомірного аналізу та орієнтована на оброблення нерегламентованих, несподіваних запитів користувачів щодо даних. Робота користувача із системами OLAP полягає в інтерактивній послідовності формування запитів і вивчення їх результатів, кожний з яких може викликати потребу нової серії запитів. Хоча OLAP і не є необхідним атрибутом сховища даних, проте саме він найчастіше застосовується для аналізу відомостей, нагромаджених у сховищі. Дані (куби) OLAP формуються адміністратором бази чи сховища даних так, щоб вони були більш пристосованими для аналізу, що зменшує витрати часу і зусилля користувачів для отримання потрібної їм інформації.

За створення куба OLAP здійснюється об’єднання декількох структурних ієрархій, наведених на рис. 2.2.17, з різними напрямами уявлення про дані. В кубі представлено розмірності та поля даних. Поля даних визначають значення даних, що відслідковуються в базі, а розмірності — ієрархічні типи даних з різними рівнями подробиць. Кожна розмірність складається з сукупності рівнів, які охоплюють одну сторону даних (наприклад, Регіон на рис. 2.2.17).

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

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

У тривимірному кубі (рис. 2.2.24) як виміри використано товар, регіон і час. Ці виміри подано на певних рівнях узагальнення: товари згруповано за категоріями (напої, продукти, інші товари), регіони — за містами продажу (Київ, Одеса, Львів), час продажу — за місяцями (січень, лютий, березень). На перетині вимірів відображено лише одну міру — виторг (обсяги продажу у гривнях), але в дійсності на перетині вимірів куба може знаходитися будь-яка кількість мір.

Рис. 2.2.24. Тривимірний куб даних з продажу

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

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

Рис. 2.2.25. Двовимірне подання куба для однієї міри

У цій таблиці з простими заголовками рядків і колонок маємо одну міру — виторг (обсяги продажу в гривнях) і два виміри — регіон продажу (заголовки колонок) і час продажу (заголовки рядків).

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

 

Київ

Одеса

Львів

Виторг

 

 

 

Кількість

 

 

 

Прибуток

 

 

 

Рис. 2.2.26. Структура двовимірного подання куба з трьома мірами

У разі використання складних заголовків на осях таблиці (ряд­ках і колонках) можна розмістити два і більше вимірів куба, що «розрізується» (рис. 2.2.27).

 

Січень

Лютий

 

Київ

Одеса

Львів

Київ

Одеса

Львів

Виторг

 

 

 

 

 

 

Кількість

 

 

 

 

 

 

Прибуток

 

 

 

 

 

 

Рис. 2.2.27. Двовимірний зріз куба з декількома вимірами на одній осі

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

Клієнтське програмне забезпечення для роботи з даними OLAP

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

— програма MS Query. Вона використовується для настроювання джерел даних OLAP, для підключення до них і для створення запитів для вибірки даних;

— постачальник даних для куба OLAP. Для доступу до баз даних, створених із використанням OLAP продукту фірми Microsoft (MS SQL Server OLAP Services), в Excel включено відповідний драйвер джерела даних і програмне забезпечення, необхідне для доступу до баз даних. У разі використання інших продуктів, що забезпечують можливість роботи з даними OLAP, необхідно встановити додаткові драйвери та клієнтське програмне забезпечення. Але треба мати на увазі, що програмне забезпечення сторонніх розробників OLAP може виявитися несумісним із MS Office і не взаємодіятиме з функціями MS Query;

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

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

Командою Excel Дані/Отримати зовнішні дані/Створити запит запускається MS Query для створення джерела даних. Джерело даних надає відомості, необхідні Excel для підключення до бази даних OLAP: ім’я та місце розташування бази; драйвер, використовуваний для підключення; додаткову інформацію, необхідну для роботи з базою даних.

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

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

Зведена таблиця на основі даних OLAP може бути збережена в Excel як Шаблон звіту (тип файла xlt). Можуть бути створені й файли запитів OLAP (вони мають розширення oqy). При відкритті файла запитів Excel відображає порожній звіт зведеної таблиці, готовий для створення макета.

Створення власного куба OLAP. Excel дає можливість користувачам створювати власні куби з підмножин даних OLAP і працювати з ними. Для створення власних кубів необхідна наявність постачальника даних для куба, що підтримує таку можливість (наприклад, MS SQL Server OLAP Services).

Файли автономного куба дають змогу працювати з даними OLAP навіть у разі роботи поза мережею. Можна використовувати ці файли для забезпечення доступності певних даних OLAP у мережі чи в Іnternet, якщо сама вихідна база не повинна бути доcтупною для цих користувачів.

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

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

Для запуску Майстра автономного куба курсор заводиться у звіт зведеної таблиці і на панелі інструментів Зведені таблиці вибирається меню Зведена таблиця, а потім — пункт цього меню Настроювання «клієнт-сервер». Далі виконуються такі дії:

1. Якщо файл автономного куба ще не створювався, то вибирається режим Створити локальний файл даних, а якщо такий файл вже існує — режим Локальний файл даних і потім — Змінити файл.

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

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

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

5. У списку мір представлено вибрані виміри. Тепер слід визначити елементи даних, які потрібно одержувати з верхнього рів­ня кожного виміру. Для цього вибирається поле «плюс» поруч із виміром, що дає змогу переглянути всі елементи на верхньому рівні виміру. Наприклад, якщо верхнім рівнем географічного виміру є Країна, то це дасть змогу побачити перелік країн, в яких проводився продаж товарів. Для вилучення елемента з файла треба скинути відповідний прапорець. Якщо список не включає необхідних вимірів, треба повернутися до кроку 2 і вибрати потрібний вимір.

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

7. Після завершення збереження файла автономного куба звіт буде зв’язаний з цим файлом. Щоб не було проблем у разі відкриття звіту, побудованого за файлом автономного куба, останній бажано не переміщувати. Ознакою завершення процесу збереження файла буде поява діалогового вікна Настроювання «клієнт-сервер». Щоб повернутися у звіт, треба натиснути кнопку ОК.

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

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

Створення куба OLAP із записів реляційної бази даних виконується з допомогою MS Query і починається зі створення запиту або з майстра зведених таблиць чи зведених діаграм або запуском MS Query безпосередньо з MS Excel. Після створення запиту, що повинен включати всі поля, які потрібно використовувати в кубі OLAP, запускається майстер куба OLAP для створення самого куба.

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

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

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

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

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

Розділ 3