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

 

При створенні підсумкових звітів в Excel використовується структурування даних робочих листів. Це дозволяє показувати або приховувати рівні структурованих даних, виводячи на екран дані з більшими або меншими подробицями.

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

Опція головного меню Данные → Сортировка, у вікні Сортировка диапазона у рядку Сортировать по вибрати Табельный №.

Опція головного меню Данные → Итоги, у вікні Промежуточные итогои в рядку При каждом изменении в вибрати Фамилия, в рядку Операція вибрати Сумма, в рядку Добавить итоги по вибрати Начислено, натиснути Ok.

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

Їх два типи: кнопки з номерами рівнів (у вашій таблиці це кнопки 1 і 2, що знаходяться в лівому верхньому куточку екрану) і знаки + (плюс) і/або – (мінус), що дозволяють відповідно розкривати або приховувати деталі структурованого документа.

Клацніть на кнопці 2. Ваша підсумкова таблиця „ розкрилася ”, надавши можливість пересвідчитись, що об’єднуються дані за Табельними №. Клацнувши по кнопці 1, ви можете сховати вихідні дані. Перевірте це на своїй таблиці. Клацніть по якому-небудь зі значків + (плюс). Результатом буде відкриття однієї зі складової підсумкової таблиці. Клацнувши по значку – (мінус), ви сховаєте вихідні дані.

Для відміни групування записів – опція головного меню Данные → Итоги → Убрать все.

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

Виділити клітину за межами таблиці і ввести в неї текст „ Максимальна заробітна платня ”, перейти у клітину поруч справа від введеного тексту і натиснути кнопку – Майстер функцій на стандартній панелі інструментів. У вікні майстра вибрати функцію МАКС із категорії Статистические, у вікна діалогу цієї функції вибрати адреси клітин, з яких повинно бути вибрано максимальне значення, а саме – узагальнені нараховані суми заробітної платні. Цю операцію повторити для находження мінімальної заробітної платні, використовуючи функцію МИН.

 

ЛАБОРАТОРНА РОБОТА № 5

Опрацювання даних у Excel, сортування даних,

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

 

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

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

5.1.1 Уявлення ЕТ у виді списку

Список – це один із засобів організації даних на робочому листі. Список створюється як позначена область, що складається з рядківтаблиці зі зв’язаними даними. Перший рядок містить назви стовпців, що визначають структуру таблиці. Дані в кожному стовпчику мусять бути однотипними. Наприклад, перелік співробітників деякого відділу, у якому стовпчики мають відповідно такі імена: №, Прізвище, ім’я по батькові, Оклад, Кількість дітей, профс., пенсійний, на прибуток, Сума до видачі – це є список даних. Дані, організовані в список, у термінології Ехсеl називаються базою даних (БД). При цьому рядки таблиці – це записи бази даних, а стовпчики – поля БД.

Щоб перетворити таблицю Ехсel у список, необхідно надати стовпчикам імена, які будуть використовуватися як імена полів бази даних. Варто мати на увазі, що імена стовпчиків можуть складатися з декількох рядків заголовків, розміщених в одному рядку таблиці Ехcel, як це зроблено на рис. 5.1.

Створюючи список на робочому листі Ехсеl необхідно дотримуватись певних правил:

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

б) варто відокремлювати список від інших даних робочого листа хоча б одним незаповненим стовпчиком або одним незаповненим рядком. Це допоможе Ехсеl автоматично виділити список при виконанні фільтрації або при сортуванні даних;

в) список може займати весь робочий лист: 65536 рядків і 256 стовпчиків;

г) імена стовпчиків повинні розташовуватися в першому рядк у списку. Ехсеl використовує ці імена при створенні звітів, для пошуку і сортування даних;

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

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

Звертаємо увагу на зміни у структурі списку „ Відомість нарахувань ” з лабораторної роботи №!: заголовки таблиці розміщені не у два рядка, а в один, текст „ податки ” відокремлений від таблиці додатковим рядком 9, як показано на рис. 5.1.

 

 

 

Рисунок 5.1 - Приклад списку

 

 

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

Список можна відсортувати за алфавітом, за значенням або в хронологічному порядку відповідно до даних конкретного поля. Щоб відсортувати увесь список, достатньо виділити одну клітину і вибрати в меню Данные команду Сортировка. Ехсеl автоматично виділить увесь список. Якщо в першому рядку списку знаходяться імена полів, то вони не будуть сортуватися. Але майте на увазі, що в цьому випадку підсумковий рядок списку, якщо від є, також буде включено до сортування, тому більш доцільно самостійно виділяти необхідну область вихідного списку для сортування.

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

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

У діалоговому вікні Сортировка існує режим Параметры. Він дозволяє встановити порядок сортування за першим ключем – звичайний або вказаний користувачем, задати врахування великих та малих літер(урахування регістру символів), а також напрямок сортування – за зростанням або за спаданням.

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

 

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

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

 

 

Автофільтр

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

Елемент стовпчика, який виділений у списку, що розкривається, називається критерієм фільтру. Можна продовжити фільтрацію списку за допомогою критерію з іншого стовпчика. Наприклад, якщо після виділення елемента «1» у списку для поля К ількість дітей вибрати у списку Сума до видачі значення «2005,19 грн», то на екран буде виведений тільки один рядок для співробітника Іванов А.Ф.

Щоб видалити критерії фільтра для окремого стовпчика, треба вибрати параметр Все в списку, що розкривається. Щоб показати всі сховані в списку рядки, треба вибрати в меню Данные команду Фильтр, а потім – команду Отобразить все.

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

 

Складна фільтрація

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

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

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

Завдання критеріїв пошуку у видіконстант потребує точної копії імен тих стовпчиків списку, що задають умови фільтрації. Наприклад, для фільтрації списку „Відомість нарахувань” з метою відбору записів співробітників, у яких „Сума до видачі” більше 400, треба скласти таку таблицю критеріїв:

 

 

Якщо необхідно одержати список співробітників, у яких „Сума до видачі” знаходиться в діапазоні від 2500 до 3500, то в таблиці критеріїв кожна умова повинна бути задана окремо, але в одному рядку, тому що вони пов’язані оператором И. Таким чином, таблиця критеріїв матиме вигляд:

 

 

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

Наприклад, для таблиці „ Відомість нарахувань” необхідно одержати список співробітників, у яких Сума до видачі знаходиться в заданому вище діапазоні. Тоді заголовок вихідного документу повинен мати такий вигляд:

 

Прізвище, ім'я по батькові

Сума до видачі

 

Кількість рядків у вихідному документі Ехсel визначить самостійно.

Таким чином, для виконання команди Расширенный фильтр треба виконати три дії:

а) сформувати у вільному місці робочого листа таблицю критеріїв;

б) сформувати шапку вихідного документу;

в) виділити область пошуку в первісному списку.

 

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

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

 

 

Рисунок 5.2 – Заповнене діалогове вікно Расширенный фильтр

Далі треба визначити Диапазон условий, задавши область знаходження таблиці критеріїв, до якої обов’язково входять імена стовпчиків таблиці. В області діалогового вікна Обработка треба зазначити, буде фільтрація виконуватися на місці або результат буде записаний в іншій області робочого листа. Якщо був обраний режим – „ фильтровать список на месте”, то Ехсеl сховає всі рядки початкового списку, які не задовольняють заданим критеріям. Якщо встановлений перемикач „ только уникальные записи ”, то однакові рядки списку не будуть показані в області вихідних даних. Якщо умови пошуку задані в таблиці критеріїв в одному рядку, то ці умови зв’язані оператором И, якщо ж умови пошуку задані в різних рядках, то вони пов’язані оператором ИЛИ.

Виконайте фільтрацію й перевірте правильність відбору даних.

 

5.1.6 Використання критерію, що обчислюється

 

Використання критеріїв пошуку, що обчислюються, в таблиці критеріїв потребує виконання таких правил щодо формул:

а) формула повинна виводити логічне значення Істина або Хибність (Ложь). Після виконання пошуку на екран виводяться тільки ті рядки, для яких результатом обчислення формули буде Істина;

б) формула повинна посилатися хоча б на один стовпчик у списку;

в) формула, використана в критерії, повинна посилатися або на заголовок стовпця, або на відповідне поле першого запису списку(в наведеному нижче прикладі це може бути або „Сума до видачі”, або H11, або H12).

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

Формула для критерію обчислюється за допомогою функції СРЗНАЧ, а саме:

=H11>СРЗНАЧ($H$11:$H$20)

 

Результат наведений на рис. 5.3.

 

Рисунок 5.3 – Результат фільтрації по критерію, що обчислюється

 

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

Виконайте зазначену фільтрацію і порівняйте отримані результати.