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

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

Сортування по полях, які містять дату та час, буде проводитись правильно лише в тому випадку, коли вони представлені у відповідному форматі прийнятому в Excel

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

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

Після цього вибираємо команду Sort... (Сортировка... | Сортування...) з пункту меню Data (Данные | Дані). У випадаючому списку Sort by (Сортировать по | Сортувати за) необхідно вибрати поле, по якому буде виконано сортування (ключове поле).

 

Якщо потрібно, щоб сортування відбувалось по декількох полях, то у випадаючому списку Then By (Затем по | Потім за) необхідно вибрати другорядне поле, а якщо і цього не досить, то в наступному списку Then By (В последеюю очередь по | Потім за) вибирають поле по якому буде відбуватись сортування в останню чергу. Опціями Ascending (по возрастанию | за зростанням) та Descending (по убыванию | за спаданням) необхідно вказати порядок сортування (відповідно по зростанню та спаданню елементів поля).

Після цього потрібно перевірити, чи активізована опція

Header Row (подписям | з рядком заголовка), оскільки в протилежному випадку (активізована опція No header row (обозначениям столбцов листа | без рядка заголовка)) назви полів також будуть брати участь в сортуванні.

За замовчуванням Excel не враховує різницю регістрів в полях списків (різницю між великими та малими літерами). Якщо потрібно встановити цю різницю або встановити інші додаткові параметри сортування, то необхідно натиснути командну кнопку Options... (Параметры... | Параметри...). Відкривається додаткове діалогове вікно, де можна вказати:

· First key sort order (Сортировка по первому ключу | Сортування за першим ключем) - в даному випадаючому списку можна вказати порядок сортування списку за основним ключем: Normal (Обычная | Немає) - звичайне сортування або сортування по списку, створеному користувачем; · Case sensitive (Учитывать регистр | Враховувати регістр) - встановити при сортуванні різницю між великими та малими літерами;

· розділ Orientation (Сортировать | Сортувати) - встановлює напрямок сортування: Sort top to bottom (строки диапазонов | рядки діапазону) - здійснювати сортування по колонках, Sort left to right (столбцы диапазонов | стовпці діапазону) - по рядках.

Вказавши всі необхідні опції сортування, потрібно натиснути кнопку Ok.

Для відбору даних на основі більш складних критеріїв, які неможливо застосувати при роботі з формами даних, а також для того, щоб роздрукувати звіти з певними вибраними даними, в Excel можна застосувати команду фільтрування, яка дозволяє виводити на робочому аркуші не весь список, а лише ту частину яка відповідає даному критерію.Одним із найзручніших та доступних способів фільтрування є використання функції автофільтрування (AutoFiltеr).Для того, щоб встановити автоматичне фільтрування списку, потрібно встановити курсор в будь-якій комірці БД. Після цього в пункті меню Data (Данные | Дані) вибрати підпункт Filter (Фильтр | Фільтр), а в ньому - команду AutoFiltеr (Автофильтр | Автофільтр). В результаті чого поряд із назвами полів з’являться кнопки розкриття списку Для вибору критерію фільтрування потрібно у відповідному полі натиснути ліву клавішу миші на кнопці автофільтрування або активувати комірку із назвою поля та натиснути комбінацію клавіш Alt+Ї, тоді вибрати значення, яке повинно містити дане поле. В результаті, в списку БД залишаться лише записи із вказаним вмістом поля.

Потрібно відмітити, що кількість елементів випадаючого списку автофільтрування не може первищувати 999 значень. Якщо кількість елементів у списку перевищує це значення, то слід скористатись розширеним фільтром. Якщо для поля із числовою інформацією в кнопці автофільтрування вибрати команду Top 10 (Первые 10... | Перші 10...), то відкривається вікно з трьох елементів. У випадаючому списку зліва можна вибрати: Top (наибольших | найбільших) - вивести найбільші за значенням поля N записів, Bottom (Наименьших | Найменших) - найменших N записів, де N - це кількість, яка вибирається в сусідньому полі ліворуч. При цьому, у випадаючому списку праворуч потрібно вибрати Items (элементов списка | елементів списку). Якщо в цьому списку вибрати Percent (% от количества элементов | % від кількості елементів), то N буде встановлювати відсоток записів (спочатку чи з кінця списку), що потрібно відобразити відносно їх загальної кількості. Потрібно також зазначити, що дана команда на поля із текстовою інформацією не поширюється. Для вибору більш складного критерію фільтрування потрібно вибрати в списку кнопки автофільтрування команду Custom… (Условаие... | Умова...). У випадаючих списках, що розміщені зліва, вказується команда логічної умови, а в тих що розміщені справа - значення критерію.

Рис.2.3 Вікно встановлення критерію

фільтрування

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

· And (И | І) - логічне “І”: записи відповідають критерію, якщо одночасно справджуються обидві умови, вказані у випадаючих списках вікна;

· Or (ИЛИ | АБО) - логічне “Або”: записи відповідають критерію, якщо виконується хоча б одна із умов, вказаних у випадаючих списках вікна;

· Or (ИЛИ | АБО) - логічне “Або”: записи відповідають критерію, якщо виконується хоча б одна із умов, вказаних у випадаючих списках вікна (іншими словами: даному критерію не відповідають лише записи, для яких не виконується жодна із умов).

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

· символ? - заміняє не більше одного невідомого символу в тексті (один або жодного);

· символ * - заміняє будь-яку кількість будь-яких символів.

У випадаючому списку зліва можна вибрати наступні оператори логічних умов:

· equals (равно | дорівнює) - умова виконується, якщо вміст поля дорівнює виразу справа;

· does not equal (не равно | не дорівнює) - умова виконується, якщо вміст поля не дорівнює виразу справа (тобто будь-який вираз, крім вказаного);

· is greater than (больше | більше) - умова виконується, якщо вміст поля більший за вираз справа. Якщо в ролі виразу виступає одна літера, то критерію буде відповідати будь-який текст, що починається з даної літери (але, якщо текст складається лише з цієї літери, то він не відповідає критерію), або з літер, які в алфавіті йдуть після неї. Якщо після літери у виразі вказати “я” або “z”, то текст що починається з цієї літери, не враховується, а виводиться лише текст, що починається з літер які в алфавіті ідуть за нею;

· is greater than or equal to (больше или равно | більше або дорівнює) - умова виконується, якщо вміст поля більший або дорівнює виразу справа. Якщо в ролі виразу вказати одну літеру, то критерію буде відповідати будь-який текст, що починається з даної літери (на відміну від попереднього оператора текст, що складається лише з однієї цієї літери також відповідає критерію), або з літер, які в алфавіті йдуть після неї;

· is less than (меньше | менше) - умова виконується, якщо вміст поля менший за вираз справа. Якщо в ролі виразу вказати одну літеру, то критерію буде відповідати будь-який текст, що починається з літери, яка в алфавіті знаходиться перед вказаною. Якщо після літери у виразі вказати “я” або “z”, то виводитись буде також текст, що починається з цієї літери;

· is less than or equal to (меньше или равно | менше або дорівнює) - умова виконується, якщо вміст поля менший або дорівнює виразу справа. Якщо замість виразу вказати одну літеру, то критерію буде відповідати будь-який текст, що починається з даної літери, або з літер, які в алфавіті йдуть перед нею;

· begins with (начинается с | починається з) - умова виконується, якщо текст поля починається із набору літер вказаних у виразі;

· does not begins with (не начинается с | не починається з) - умова виконується, якщо текст поля починається із набору літер відмінних від виразу вказаного справа;

· ends with (заканчивается на | закінчується на) - умова виконується, якщо текст поля закінчується набором літер вказаних у виразі;

· does not end with (не заканчивается | не закінчується на) - умова виконується, якщо текст поля закінчується набором літер відмінних від виразу вказаного справа;

· contains (содержит | містить) - умова виконується, якщо текст поля містить набір літер вказаних будь-де у виразі справа;

· does not contain (не содержит | не містить) - умова виконується, якщо в текст поля не входить набір літер вказаних у виразі справа.

Якщо необхідно відфільтрувати всі записи, для яких вміст поля починається на будь-яку літеру, що знаходиться в алфавіті від “Б” до “К”, то потрібно відмітити опцію AND (И | І), а тоді:

· у верхніх випадаючих списках вказати is greater than or equal to Б (або is greater than Ая);

· в нижніх випадаючих списках вказати is less than or equal to К (або is less than Кя).

Якщо необхідно відфільтрувати всі записи, для яких вміст поля починається “Ок”, а закінчується на “а”, то можна скористатись одним з варіантів.

1. У випадаючих списках зверху вказати: equals Ок*а.

2. Відмітити опцію AND (И | І) і у випадаючих списках зверху вказати “begins with Ок”, а в списках знизу “ends with а”.

Якщо необхідно відфільтрувати всі записи, для яких вміст числового поля лежить в межах від 100 до 300, то потрібно відмітити опцію AND (И | І), а тоді: · у верхніх випадаючих списках вказати is greater than or equal to 100;

· в нижніх випадаючих списках вказати is less than or equal to 300

Якщо необхідно відфільтрувати всі записи, для яких вміст поля починається на літери “А” або “Б”, то потрібно відмітити опцію OR (ИЛИ), а тоді:

· у верхніх випадаючих списках вказати equals А*;

· в нижніх випадаючих списках вказати equals Б*.

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

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

Для того, щоб зняти фільтрування з певного поля, потрібно вибрати його кнопку автофільтрування і виконати команду All (Все | Усе). Якщо потрібно відмінити будь-які фільтрування, то потрібно повторно викликати команду AutoFilter (Автофильтр | Автофільтр) з пункту меню Data (Данные | Дані), підпункту Filter (Фильтр | Фільтр).

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