4. Аналіз та обробка правових даних у Excel

Заняття 4. Аналіз та прогнозування правових даних у

Excel

Пл а н

1.Створення і редагування таблиць. Транспонування

таблиці.

2.Масиви та формули масивів. Робота з масивами.

3.Вибір і аналіз даних у списках і базах даних. Діапазон бази даних. Діапазон критеріїв. Діапазон для вивільнення. Правила завдання критеріїв.

4.Відбір за обчислювальним критерієм.

5.Прогнозування в Excel. Аналіз тенденцій. Прогнозування на основі трендів.

6.Відображення даних у вигляді графіків та діаграм.

Завдання

1.Створити таблицю за наведеним зразком. Створити список автозаповнення, що містить назви судів і використати його для заповнення клітинок А6-А11.

2.Транспонувати таблицю і розмістити її на третьому робочому листі. Повернутися на перший лист до вихідної таблиці.

3.Розташувати текст заголовків стовпців у декілька

30

рядків.

4.У клітинках G6-G11 виконати розрахунки з використанням автосуми.

5.У клітинках Н6-Н11 виконати розрахунки з використанням функції.

6.У клітинках І6-І11 виконати розрахунки з використанням формули.

7.У клітинках B12-G12 виконати розрахунки з використанням формули масиву.

8.Створити список, що містить дані по кожному суду

за 5 років.

9.Упорядкувати у списку назви судів та дані по кожному суду в абетковому порядку.

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

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

12.У таблиці виконати прогнозування росту кількості розгляду справ всіма судами на наступні 5 років.

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

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

Ключі до виконання завдань

1.Сервис/Параметры/Списки – виділити НОВЫЙ СПИСОК – в якості елементів списку ввести назви судів, розділяючи їх комами. У клітинку А6 ввести назву першого суду зі списку, встановити курсор на маркер буксирування і виконати буксирування по клітинках А7-А11.

2.Транспонування таблиці – це заміна місцями рядків і

31

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

меню – виконати Специальная вставка... – Транспонировать.

3.Виділити клітинки G5-I5, викликати контекстне меню

івиконати Формат ячеек/Выравнивание – встановити по горизонтали або по ширине і встановити переносить по словам.

4.Виділити клітинки В6-G11, в панелі інструментів на-

тиснути .

5.Виділити клітинку Н6 й виконати Вставка/Функция або натиснути кнопку в панелі інструментів, вказати потрібну функцію і задати аргумент. Для аргументів, представлених клітинками суміжного діапазону, виділити клітинки, що містять аргумент. Для клітинок несуміжного діапазону – виділяти клітинки, що містять аргумент, при натиснутій клавіші [Ctrl]. Так само виконати розрахунок по інших судах.

6.Виділити клітинку І6, ввести знак =, ввести формулу, встановити курсор на маркер буксирування і відбуксувати формулу по клітинках І7-І11.

7.Формула масиву обробляє декілька наборів значень, званих аргументами масиву. Елементами формули масиву є, як правило, діапазони клітинок. Формула масиву створюється так само, як і інші формули, але для введення такої формули використовуються клавіші [CTRL+SHIFT+ENTER].

Унашому випадку слід обчислити суму значень, що містяться в рядках. Виділяємо клітинки В12-F12, в яких буде розміщений результат, і вводимо формулу. Увага! Не намагайтеся вводити фігурні дужки – вони з’являться після натискання

[CTRL+SHIFT+ENTER]:

8.Списки Microsoft Excel надають засоби, які спрощують управління і аналіз груп зв’язаних даних на листі Excel. Якщо оголосити деякий діапазон клітинок списком, то даними цього списку можна управляти і аналізувати їх незалежно від даних, що не увійшли до списку.

32

Виділити діапазон А6-F11, виконати Данные/Список/Создать список. Вибраний діапазон даних виділяється індикатором списку, а на панелі інструментів Список стає доступним велика частина загальних функціональних можливостей, пов’язаних зі списком.

9.Клацнути по клітинці А6, натиснути кнопку і обрати умову по возрастанию. Для повернення таблиці в поперед-

ній вид виконати Данные/Список/Преобразовать в диапазон.

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

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

Перед використанням розширеного фільтра слід сформувати умови фільтрації (діапазон критеріїв). Для цього ство-

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

є>250, та В середньому за рік, для якого умовою пошуку

є>30. Заголовки стовпців в основній таблиці і таблиці умов (діапазон критеріїв) повинні повністю співпадати, тому заголовки стовпців краще копіювати з основної таблиці в таблицю умов. Копіюємо заголовки стовпців у перший рядок, клітинки А1 і В1. Критерії пошуку записуються під підготовленим рядком:

Слід враховувати, що виконання умови І (И) вимагає розташовувати критерії пошуку поряд, в одному рядку, а виконання умови АБО (ИЛИ) вимагає розташування критеріїв у різних рядках.

33

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

Виконуємо Данные/Фильтр/Расширенный фильтр….

У наступному вікні задаємо: Исходный диапазон – це, як правило, вся таблиця. Діапазон може буди заданий виділенням всієї таблиці.

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

34

Після натиснення [ОК] в таблиці залишаться тільки ті рядки, які відповідають умовам фільтрації.

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

Данные/Фильтр/Отобразить все.

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

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

Тож у клітинку А2 вводимо формулу

Виконуємо Данные/Фильтр/Расширенный фильтр….

У наступному вікні задаємо: Исходный диапазон – це, як правило, вся таблиця. Діапазон може буди заданий виділенням всієї таблиці.

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

Після натиснення [ОК] у таблиці залишаться тільки ті рядки, які відповідають умовам фільтрації.

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

Данные/Фильтр/Отобразить все.

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

35

Початкове значення Продовженнярядкагеометричноїпрогресії

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

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

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

ное приближение.

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

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

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

ділений діапазон;

– клацнути на кнопці на панелі інструментів або виконати: Вставка/Диаграмма. У діалоговому вікні, що з’явилося, обрати потрібний тип і вид діаграми. Для подальшого оформлення діаграми натиснути Далее.

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

Викличте контекстне меню першого стовпця діаграми,

37

виконайте Добавить линию тренда, задайте тип лінії тренда.

Матеріал для роздумів і осмислення

1.Що таке список автозаповнення? Наведіть приклади його використання.

2.Як виконується розрахунок за формулою? Назвіть засоби використання формул.

3.Що таке формула масиву? Які переваги формула масиву має порівняно зі звичайною формулою?

4.Як виконується розрахунок з використанням функції? Назвіть засоби вказування аргументу функції.

5.Що таке список в Excel? Чим елементи списку відрізняються від інших елементів таблиці?

6.Що таке розширений фільтр?

7.Якформуютьсяумовилогічногорозширеногофільтра?

8.Як формуються умови розширеного фільтра, що обчислюється?

9.Що таке прогнозування в Excel?

10.Що таке лінійна залежність і як вона прогнозується

вExcel?

11.Що таке експоненціальна залежність і як вона прогнозується в Excel?

12.Що таке лінії трендів? Як вони створюються?

13.Як формуються діаграми в Excel?

14.Які елементи оформлення можна використовувати в діаграмах Excel?

Заняття 5. Зведені таблицітазасоби статистичного

аналізуданих

Пл а н

1.Аналіз і узагальнення даних за допомогою зведених

таблиць.

2.Підготовка вхідних даних.

3.Майстер зведених таблиць.

38

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

5.Формування звітів з необхідними параметрами.

6.Групування даних.

7.Засоби статистичної обробки даних MS Excel.

8.Статистичні характеристики випадкових величин та їх обчислення.

9.Підсумкова статистика. Середнє, Медіана, Мода, Дисперсія тощо.

Завдання

1.Визначіть призначення засобу зведені таблиці в Excel,

атакож особливості його використання.

2.Створіть новий файл (нову робочу книгу Excel), яку назвіть Завдання 1. Збережіть цей файл в особистій папці (яка названа Вашим прізвищем) на диску Е:\.

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

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

5.За допомогою зведених таблиць визначіть кількість чоловіків і жінок у відділах.

6.Побудуйте гістограму кількості співробітників, які мають певну кількість дітей.

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

39

Ключі до виконання завдань

1. Зведена таблиця являє собою інструмент у наборі функціональних засобів Excel для інтерактивного керування наборами даних з метою розподілу даних за категоріями, підведення підсумку великої кількості даних, представлення даних у доступному вигляді, виконання розрахунків. Головна ідея використання зведених таблиць – динамічна зміна ракурсу аналізу даних у таблицях, перетаскування полів у звітах і перерахунок підсумків відповідно до потрібної точки зору. Зведені таблиці дають найшвидший і найефективніший спосіб розрахунку підсумкових значень і форматування великих наборів даних. Зведені таблиці мають чотири області: область даних, де виконують розрахунки. Тут повинно бути хоча б одне поле даних і од-

40

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

2.Створення особистої папки користувача виконується стандартними для Windows способами. У вікні диска Е:\ створіть нову папку, яку назвіть своїм прізвищем. Відкрийте вікно цієї папки, через контекстне меню вільної частини вікна папки оберіть Создать/Лист Microsoft Excel, задайте ім’я файлу (Завдання 1) та відкрийте його для подальшої роботи. У процесі роботи над завданням не забувайте час від часу виконувати збереження поточного змісту таблиці в цьому ж файлі за рахунок натискання на кнопку [Сохранить] панелі інструментів Стандартная (ця кнопка зображенау вигляді дискети).

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

4.Використаємо майстер зведених таблиць. Виділяємо одну з клітинок таблиці з даними. В меню вибираємо: Данные/Сводная таблица. Запускається майстер зведених таблиць. На першому кроці пропонується обрати джерело даних. Встановлюємо перемикач у положення в списке или базе данных Microsoft Excel (за замовчуванням цей пункт вже виділений). Тут також пропонується обрати вид створюваного звіту. Погодимося з встановленим перемикачем сводная таблица. Натискаємо кнопку [Далее>]. На другому кроці пропонується вказати

41

діапазон, який містить вхідні дані. У вікні введення Диапазон: буде запропоновано блок вхідних даних (завдяки попередньому вибору клітинки у цьому діапазоні). Натискаємо кнопку [Далее>]. На третьому кроці визначається структура таблиці. Натискаємо кнопку [Макет...].

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

У показаних областях (Страница, Столбец, Строка, Данные) необхідно розмістити назви полів вхідної таблиці. Ці поля перераховані праворуч від шаблону. Перетягніть мишею кнопку поля Відділ в область рядків, а кнопку поля Оклад в область даних. Зверніть увагу, що замість Оклад у полі тепер записано Сумма по полю Оклад. Майстер автоматично вибрав підсумкову функцію Сумма, тому що Оклад є числовим полем. Натиснемо [ОК]. Обираємо місце розміщення зведеної таблиці – встановимо значення перемикача новый лист. Натискаємо [Готово]. Отримаємо зведену таблицю, яка розміщена з третього рядка. Перші рядки можна видалити. Таблицю можна відформатувати, щоб було краще її використовувати у подальшому. Якщо необхідно у вхідній таблиці змінити дані, то для того, щоб вони були враховані у зведеній таблиці, потрібно на листі зведеної таблиці натиснути кнопку [Обновить данные].

42

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

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

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

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

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

6.В Excel є засіб Сводная диаграмма. Для її побудови достатньо на панелі інструментів Сводная таблица натиснути кнопку [Мастер диаграмм] або у контекстному меню будь-якої клітинки зведеної таблиці обрати команду Сводная диаграмма. Робота з діаграмою аналогічна роботі зі зведеною таблицею: можна переміщувати кнопки з назвами полів від осі категорій в область легенди і навпаки. Можна переміщувати кнопку в верхню частину діаграми – область сторінок. Закінчивши формування діаграми, можна прибрати кнопки полів. Для цього на панелі інструментів Сводная таблица клацніть по кнопці [Сводная диаграмма] і оберіть команду Скрыть кнопки полей сводной диаграммы. Також за допомогою контекстного меню діаграми можна оформити її легенду і необхідні позначення осей. Підпишіть осі діаграми відповідно до значень, що відображаються.

7.Статистичні характеристики інтегрально описують

43

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

–виділити клітинку, в якій буде розміщуватись значення функції;

–виконати команду Вставка/Функция, у вікні майстра функцій обрати категорію Статистические, у переліку функцій обрати потрібну і натиснути [ОК];

–у вікні Аргументы функции задати діапазон клітинок, що містить вхідні значення. Це краще зробити так: правіше по-

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

– натиснути [ОК] і у клітинці розміщення функції отримати результат.

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

–кількість чисел у множині – СЧЕТ();

–максимальне значення – МАКС();

–мінімальне значення – МИН();

–середнє значення – СРЗНАЧ();

–дисперсія – ДИСП() – показує ступінь розкиду величин рядка відносно середнього значення, обчислюється як середнє квадратичне відхилення значень у квадраті від середнього арифметичного;

–мода – МОДА() – значення, яке найчастіше зустрічається у заданій сукупності. Сукупність може не мати моди, а може мати більше однієї;

–медіана – МЕДИАНА() – значення, яке розділяє ранжовану низку значень на дві рівні частини: із значеннями менше медіани і із значеннями більше медіани, знаходиться на середині впорядкованого рядка.

44