Табличний процессор - Завдання 2

"Табличний процесор Microsoft Excel. Пошук даних. Зведені таблиці "
Мета: формування умінь сортувати та фільтрувати дані в таблицях, робити потрібні вибірки, підбивати підсумки й створювати зведені таблиці.
Питання для вивчення
1. Сортування даних у таблицях.
2. Пошук даних.
3. Підведення підсумків за допомогою інструмента ИТОГИ.
4. Створення зведених таблиць.

Розширений фільтр Використання розширеного фільтра дає змогу:
1) для кожного поля вводити більш ніж два значення для пошуку;
2) використовувати обчислення при введенні критеріїв пошуку;
3) вводити з клавіатури значення для пошуку даних.

При використанні розширеного фільтра критерії відбору задаються безпосередньо в робочому листі (діапазон критеріїв). Користувач завжди може контролювати та швидко змінювати критерії. Але головне, можна задавати як завгодно складні критерії, що зв’язують елементарні умови в одному полі або різноманітних полях логічними операторами «И» та «ИЛИ». При копіюванні можна не тільки враховувати складні критерії, але і відбирати окремі потрібні поля з відфільтрованих записів. Діапазон критеріїв (інтервал критеріїв) можна оформити у будь-якому вільному місці робочого листа. Обираючи місце під діапазон, виходять із того, щоб він не заважав. У діапазоні критеріїв можна вводити та сполучати два типи критеріїв: Порівняльні критерії – порівнюють вміст полів за заданою умовою (аналогічно застосуванню автофільтра). Обчислювальні критерії використовують для записувати формули, що містять стандартні функції, та перевіряти складні умови. Наприклад, використовуючи обчислювальні критерії, легко можна виділити у списку тільки тих робітників, у яких зарплата не менше ніж на 25% перевищує середню зарплату. Діапазон критеріїв повинен складатися не менше ніж із двох рядків. У першому рядку для порівняльних критеріїв необхідно зазначити імена полів. Причому можна ввести тільки ті імена полів, для яких указуються обмеження. З урахуванням необхідності точного збігу імен полів, їх простіше усього одержати шляхом копіювання. У рядку (рядках), розташованих безпосередньо під рядком з іменами полів, потрібно зазначити обмеження, що мають традиційний вигляд (наприклад: >=5000, або <=30) та інтерпретуються наступним чином: обмеження розташовані в одному рядку зв’язуються логічним оператором «И»; обмеження у різних рядках зв’язуються логічним оператором «ИЛИ»; ім’я поля може повторюватися у рядку з іменами полів.

Пошук даних у таблиці засобами Розширеного фільтра
1. Для того, щоб відфільтровувати дані таблиці за допомогою розширеного фільтра, необхідно, щоб поля таблиці мали заголовки, тобто стовпчики мали назви. На листі електронної таблиці також повинно бути не менше трьох порожніх рядків вище від списку даних, які потім використовуватимуться як діапазон умов відбору – блок критеріїв.
2. Скопіювати із списку заголовки стовпчиків, в яких здійснюється пошук.
3. Вставити скопійовані назви стовпчиків до першого порожнього рядка блоку критерію – діапазону умов відбору.
4. Увести в рядки під заголовками умов блоку критеріїв необхідні критерії відбору. Переконатися, щоб між значеннями умов і списком таблиці знаходиться, як мінімум, один порожній рядок.
5. Встановити курсор на будь-яку клітину таблиці з даними.
6. Вибрати команду Данные - Фильтр - Расширенный фильтр….
7. Для того, щоб відобразити результати фільтрування, приховавши при цьому непотрібні рядки, слід встановити перемикач в положення Фильтровать список на месте.
8. Увести в поле Диапазон условий посилання на діапазон умов відбору, що включає заголовки стовпчиків. Натиснути кнопку Ok.
Обчислювальні критерії дають змогу виконувати більш складні операції, ніж просте порівняння значення поля з зазначеною константою. Наступні правила допоможуть уникнути помилок:

• Заголовок стовпчика над обчислювальним критерієм (перший рядок діапазону критеріїв) не повинний збігатися з іменем деякого поля в аналізованому списку. Він може бути порожнім або містити будь-який інший текст.
• Формула повинна повертати значення логічного типу.
• Задавати посилання на поле списку можна шляхом указівки відносної адреси першої клітинки поля.
• Посилання на клітинки поза списком повинні бути абсолютними.
• Посилання на клітинки усередині списку повинні бути відносними.
• Посилання на постійний діапазон клітинок усередині списку потрібно задавати в абсолютному вигляді.

Наприклад, обчислювальний критерій може мати вигляд: =J3/H3<10; =C8–D8<18*365 – таким чином, за даними про дату прийому на навчання та дату народження, можна відібрати тих, хто поступив у віці до 18 років; =F3>=СРЗНАЧ($F$3:$F$325)*1,25 – таким чином, за даними про зарплату, можна відібрати тих, чия зарплата не менш ніж на 25% перевищує середню.

Приклад використання розширеного фільтра

Табличний процессор - Завдання 2(Самостійна робота)

1. Щоб задати для окремого поля (стовпчика) три або більше умов відбору, слід ввести умови в клітини, розташовані в суміжних рядках. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять або “Іванов”, або “Галушко” або “Січкар” в стовпчику “Прізвище”, а саме:

2. Щоб накласти умови відбору на кілька стовпчиків одночасно, слід ввести умови в клітини, розташовані в одному рядку діапазону критеріїв. Наприклад, для наступного діапазону умов будуть відібрані записи, що містять «хлопчик» в стовпчику «Стать», «сірі» в стовпчику «Колір очей», і вік більше 12.

3. Щоб вибрати рядки електронної таблиці, що задовольняють одній з кількох умов, накладених на різні поля (стовпчики), необхідно ввести умови в клітини, розташовані в різних рядках діапазону критеріїв. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять або «хлопчик» в стовпчику «Стать», або «сірі» в стовпчику «Колір очей», або маючи вік більше 12 років.

4. Щоб накласти складну умову відбору, необхідно ввести його складові частини в окремі рядки діапазону критеріїв. Наприклад, для наступного діапазону умов будуть відібрані рядки, що містять «хлопчик» в стовпчику «Стать» і “вік” більше 15, та рядки, що містять «дівчинка» в стовпчику «Стать» і “вік” більше 12.

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

на екрані будуть відображатися рядки, в яких значення в стовпчику В, починаючи з п’ятого запису, перевищує середнє значення в клітинах С5: С24. Як видно, заголовок умови не використовується. Слід також звернути увагу, що формула, яка використовується в критерієві, повинна посилатися або на заголовок стовпчика (наприклад, «Стать»), або на відповідне поле в першому запису таблиці, з якого слід відбирати дані. У наведеному прикладі посилання здійснюється на відповідне поле (стовпчик В) першого запису (рядок 5) таблиці з даними.

Підведення підсумків за допомогою інструмента ИТОГИ Відсортувавши список та відфільтрувавши у ньому тільки потрібні записи, можна скористатися командою Данные - Итоги для одержання різноманітних підсумкових даних. Для створення проміжних підсумків необхідно:
1. Відсортувати список за полем (стовпчиком), за яким окремі записи повинні бути розбиті на групи.
2. Виконати команду Данные - Итоги.
3. У вікні Промежуточные итоги (мал 30) зі списку При каждом изменении в виберіть поле, по якому список буде розбитий на групи записів.

4. У списку Операция оберіть функцію, що повинна використовуватися при визначенні проміжних підсумків.
5. У полі Добавить итоги по вкажіть поля, у яких повинні бути визначені проміжні підсумки. 6. У разі потреби, скиньте прапорець Итоги под данными, щоб відобразити рядки з підсумками над відповідними даними.
7. Якщо окремі групи записів повинні бути розташовані та виведені до друку на різних сторінках, встановіть прапорець параметра Конец страницы между группами.
8. Натисніть кнопку Ok.

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

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

Для побудови зведеної таблиці потрібно:
1. Виділити таблицю, за даними якої треба побудувати зведену
2. Вибрати команду меню Данные - Сводная таблица…
3. Завантажується вікно «Мастер сводных таблиц и диаграмм – шаг 1 из 3», у якому всі потрібні параметри встановлені за замовчанням. Натиснути кнопку Далее.
4. У вікні «Мастер сводных таблиц и диаграмм – шаг 2 из 3» треба вказати діапазон з даними, за якими буде побудована зведена таблиця. Якщо вихідна таблиця була зразу виділена, то у цьому вікні достатньо натиснути кнопку Далее.
5. У вікні «Мастер сводных таблиц и диаграмм – шаг 2 из 3» в розділі Поместить таблицу в: обрати потрібний пункт і натиснути кнопку Макет.
6. Заповнити макет і натиснути кнопку Ok, а у наступному вікні натиснути кнопку Готово.

Для прикладу розглянемо побудову зведеної таблиці до таблиці «Магазини». При побудові зведеної таблиці заповнимо макет як показано на малюнку 31 і отримаємо зведену таблицю як на малюнку 32. Розглянемо основні структурні елементи зведеної таблиці:

Поле рядка – це поле вихідного списку чи таблиці, яке розміщується до області рядкової орієнтації зведеної таблиці. У цьому прикладі «Магазини» (мал. 32) є полем рядка. Значення цього поля у вихідній таблиці повторюються, тобто в таблиці існують кілька записів з однаковою назвою магазинів: Всесвіт (повторюється 3 рази), Сатурн (повторюється 3 рази), Континент (повторюється 5 разів) та ін. При створенні зведеної таблиці ці данні групуються за значеннями даних, що повторюються, для даного прикладу, за назвами магазинів.

Поле стовпчика — це поле вихідного списку, яке розміщується до області стовпчиків. У цьому прикладі (мал. 32) – «Жанр фільму» є полем стовпчика. Значення цього поля у вихідній таблиці також повторюються, в таблиці існують записи з однаковими жанрами, наприклад, жанр фантастика повторюється 4 рази, жанр комедія повторюється 2 рази та ін.
Областю даних називається частина зведеної таблиці, що містить підсумкові дані. У клітинах області даних відображаються підсумки для елементів полів рядка чи стовпчика, за якими відбулося групування даних. Значення в кожній клітині області даних відповідають вихідним даним. У цьому прикладі (мал. 32) в клітинах G5-G8 зберігаються загальні підсумки виручених сум від продажу фільмів усіх жанрів для кожного окремого магазину; в клітинах В9-F9 – загальні підсумки виручених сум від продажу фільмів у всіх магазинах за кожним конкретним жанром; клітина G9 містить загальний підсумок від продажу фільмів всіх жанрів по всіх магазинах.

Поле даних — це поле вихідного списку чи таблиці, що містить дані. У цьому прикладі поле “Сума в полі “Ціна” є полем даних, в якому підводяться підсумки вихідних даних у полі чи стовпчику «Ціна». У полі даних, як правило, підводяться підсумки для чисел, що відповідають згрупованим даним, хоча поточні дані можуть бути і текстовими. За замовчуванням у зведеній таблиці підведення підсумків для текстових даних провадиться за допомогою підсумкової функції «Кількість значень», а числових даних – за допомогою підсумкової функції «Сума». Зрозуміло, що таку функцію можна змінити.

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

Поле сторінки — це поле вихідного списку чи таблиці, яке розміщується у верхній частині зведеної таблиці. У цьому прикладі «Назва фільму» є полем сторінки, яке можна використовувати для підведення підсумків за назвами фільму. При виборі із списку іншого конкретного елемента поля сторінки в зведеній таблиці перераховуються дані у відповідних полях даних для відображення підсумків, які зв'язані з вказаним елементом. Якщо, в розглядуваному прикладі в полі сторінки “Назва фільму” вибрати значення одного з фільмів, наприклад “Кобра”, то зведена таблиця буде мати вже інший вигляд.

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