§ 8. Упорядкування і фільтрація даних

В цьому параграфі ви вивчите:

• правила упорядкування даних у ЕТ;

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

• засоби фільтрації даних.

Упорядкування даних

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

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

Дані зазвичай сортуються за алфавітом, за числовим значенням або за датою. Опишемо порядок сортування, прийнятий у Excel. Числа сортуються у порядку зростання: від найменшого від’ємного до найбільшого додатного числа. При сортуванні тексту порівнюється зміст комірок за символами зліва направо. Наприклад, слово «зоологія» буде розташоване після слова «золото». Регістр символів при сортуванні не враховується.

У тексті, крім літер, можуть використовуватися цифри й інші символи. Упорядкована послідовність усіх можливих символів, що прийнята в Excel при сортуванні, така:

(пробіл) ! « # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `` { | } ~ + < = > 0 1 2 3 4 5 6 7 8 9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Є Ж З И І (укр.) Ї Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я

Сортування за одним параметром

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

- сортування за зростанням або

- сортування за спаданням.

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

Сортування здійснюється по крайньому лівому полю (у даному разі по стовпцю А).

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

Якщо сортування виявилося невдалим, можете його відразу скасувати, натиснувши кнопку Відмінити на панелі інструментів чи натиснувши Ctrl+Z.

Сортування за кількома параметрами

Мал. 8.1. Діалог Сортировка диапазона

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

Розглянемо як приклад сортування списку на мал. 7.1. Виконаємо спочатку сортування за жанром ігор, а потім – за назвами.

Клацніть по таблиці і ввійдіть у меню Дані→Сортування. У діалозі Сортування діапазону (мал. 8.1) задайте стовпці для сортування:

• Жанр – у полі Сортувати за;

• Назва гри – у полі Потім за.

Натисніть кнопку Ok, і ви одержите список, упорядкований за жанром ігор і за назвами. (мал. 8.2).

Фільтрація даних. Автофільтр

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

Фільтрація – це виділення визначених записів, що задовольняють заданим критеріям.

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

Розглянемо найпростіший режим фільтрації – Автофільтр на прикладі таблиці, показаної на мал. 7.1. Клацніть по будь-якій комірці списку, що підлягає фільтрації і виконайте команду меню Дані→Фільтр→Автофільтр. Праворуч від кожного стовпця з’явиться кнопка зі списком (мал. 8.3).

Список кнопки відкривають позиції Усі, (Перші 10…), (Умова), потім за алфавітом розташовуються елементи стовпця. Прокрутивши список, ви можете обрати необхідний вам критерій. Одразу після цього на екрані залишаться лише відфільтровані записи, а інші будуть сховані. Заголовки відфільтрованих рядків зафарбовано в синій колір. Ви можете продовжити фільтрацію, вказуючи в списках інших стовпців необхідні елементи, а можете повернутися до початкового вигляду таблиці, відмітивши в списку пункт Усі. Для повного скасування процедури фільтрації потрібно ввійти в меню Дані→Фільтр і зняти прапорець з команди Автофільтр.

Мал. 8.2. Список ігор після сортування за жанром і назвою

Мал. 8.3. Застосування автофільтра

Автофільтр користувача

Зауважимо, що у списку автофільтра (мал. 8.3) є позиція Умова. Вона дозволяє задати точні критерії для фільтрації. Припустимо, ви хочете з наведеного списку відібрати ті ігри, ціна яких не перевищує $40. Для цього в списку Ціна позначте пункт Умова, потім у діалозі Користувацький автофільтр задайте необхідну умову, як показано на мал. 8.4. Взагалі в даному діалозі можливе задання двох умов, записаних через оператори порівняння дорівнює, не дорівнює, більше… й об’єднаних логічним І або АБО.

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

Мал. 8.4. Задання автофільтра користувача

Розширений фільтр

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

Мал. 8.5. Застосування розширеного фільтру

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

• Активізуйте будь-яку комірку первинної таблиці і виконайте команду Дані→Фільтр→Розширений фільтр.

• Мал. 8.6. Задання параметрів розширеного фільтра

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

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

• Закрийте діалог Расширенный фильтр натиснувши кнопку Ok. Якщо ви правильно зазначили діапазон і критерії фільтрації, то на аркуші з’явиться таблиця-результат (мал. 8.5).

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

Вправи

1. Виконайте сортування списку комп’ютерних ігор (мал. 7.1) за ціною в порядку зростання.

2. відсортуйте список ігор за двома параметрами: ціною і найменуванням.

3. За допомогою автофільтра в списку комп’ютерних ігр виділіть ігри-стратегії.

4. У списку ігор встановіть фільтр користувача на ціну: більше $10 і менше $20.

5. Встановіть розширений фільтр для списку ігор, обравши умовою першу літеру найменування «З» і ціну <$15. Виконайте два варіанти виведення відфільтрованих записів: в зону первинної таблиці й в інше місце робочого аркуша.