Оперування даними
Сортування дозволяє впорядковувати дані БД в алфавітному чи цифровому порядку за зростанням чи зменшенням. Ви заповнювали таблицю у порядку надходження товарів, а хотілося б мати список товарів за відділами, для цього застосуємо сортування рядків:
1. Збережіть робочу книгу за допомогою , Зберегти , щоб легше було повернутися до початкового порядку даних, якщо дані випадково виявляться перемішаними.
2. Виділіть таблицю без першого стовпця. Якщо ви виділите лише частину стовпців, то інформація виявиться перемішаною – поля записів поміняються один з одним місцями. За допомогою командиГоловна, Редагування, Сортування та фільтр, Настроюване сортуваннявідкрийте діалогове вікноСортування(рис.4.3.).
3. Виберіть перший ключ сортування: у спискуСортувати завиберіть перше поле, за яким слід упорядковувати дані- Відділі встановіть порядокВід А до Я(усі відділи в таблиці розташуються за абеткою).
4. Щоб усередині відділу всі товари розміщувалися за алфавітом, додайте ще один рівень сортування і виберіть другий ключ сортування: у спискуПотім повиберітьНайменування товарів, встановіть порядокВід А до Я. Друге поле сортування використовується, якщо виникає повторення першого поля.
5. Якщо потрібно відсортувати стовпці, перед виділенням полів для сортування натисніть кнопкуПараметри. У діалоговому вікніПараметри сортування виберіть перемикачСтовпці діапазону, а потім натисніть кнопкуОк.
Малюнок 4.3. Діалогове вікно для сортування даних
6. Результати сортування представлені нарис.4.4.
Малюнок 4.4. Результати сортування
Пошук, фільтрація, редагування у списках та базах даних
Пошук і аналіз даних – це операції, що найчастіше використовуються, що проводяться зі списком або базою даних. Пошук проводиться на основі критеріїв, що задаються користувачем. Критерій – це вимога, що накладається на інформацію. За допомогою іншої операції – фільтрації є можливість тимчасово приховати записи, що не відповідають заданим критеріям.
В Excel існує два засоби для пошуку та фільтрації даних:АвтофільтртаРозширений фільтр.Автофільтрзалишає на екрані тільки ті записи, які задовольняють критеріям.Розширений фільтрдозволяє накладати складні умови відбору такі, як обчислювані критерії. Розглянемо ці кошти на прикладі.
Автофільтрація
Так як щодня потрібно роздруковувати список товарів, що залишилися в магазині, а також з метою аналізу господарської діяльності групувати списки товарів за якимись іншими критеріями, наприклад вибрати всі товари, продані на даний день, розглянемо, як це можна зробити, використовуючи фільтрацію даних :
- Виділіть таблицю з другого рядка заголовка.
- Виберіть команду менюГоловна, Редагування, Сортування та Фільтр, Фільтр(рис.4.5).
Малюнок 4.5. Автофільтр по полю Ціна при вступі
- У кожної комірки заголовка таблиці з'явилася кнопка, що управляє. Вона дозволяє розкрити список та задати критерій фільтра для відповідного поля. Ми хочемо залишити всі записи, які відповідають проданому товару.
- Розкрийте список коміркиЗалишокі виберітькоманду Числові фільтри, Настроюваний фільтр. У діалоговому вікні, що з'явилося, можнавстановити одну або дві умови з використанням шаблонів, операцій порівняння та логічних операційІ, АБО. Встановіть відповідні параметри (рівно, І, 0).
- Замість повного списку товарів ви отримали список проданих на сьогоднішній день товарів.
- Для того щоб знову побачити перелік усіх товарів по всіх відділах, потрібно у спискуЗалишоквибрати командуЗняти фільтр із «Залишок».
- Виберемо список, сформований за складнішою умовою: «Показати всі товари,Сума залишкуяких менше 60 або більше 2000». Для цього розкриємо список поляСума залишкуі виберемо командуЧислові фільтри, Настроюваний фільтр. Діалогове вікно, що з'явилосяКористувачський автофільтріз заданою умовою відбору списку буде виглядати так (рис. 4.6). Результат роботи автофільтра представлено на рис. 4.7.
Малюнок 4.6. Користувальницький автофільтр
Малюнок 4.7. Результат роботи автофільтра
Завдання 4.1. Самостійно сформулюйте кілька завдань та вирішіть їх за допомогою автофільтра.
Розширений фільтр
Розширений фільтрдозволяє сформувати критерій фільтрації практично будь-якого ступеня складності, у тому числі і вигляді формули (обчислюваний критерій). Відфільтровані дані можуть бути скопійовані в будь-який діапазон осередків даного листа або на інший лист.
Завдання 4.2. Використовуючи розширений фільтр із загального списку товарів вибрати у відділах, що починаються на букву Р, всі товари крім оселедця, ціна яких при надходженні товару більша за 45 або вибрати всі товари з відділу М'ясної, ціна яких більша за 30.
На рис. 4.8. показані всі етапи створення такого списку та сам обраний список. Для отримання такого результатунеобхідно:
А) Задати критерій, у якому визначаються умови пошуку даних. У випадку критерій є таблицю, що містить рядок заголовка і записи з умовами фільтрації. Рядок заголовка критерію може містити всі поля або тільки частина полів заголовка вихідної таблиці, при цьому порядок полів може і не зберігатися. Скопіюйте другий рядок заголовка вихідної таблиці в комірку A24.
Б) У наступних рядках після заголовка записуються умови, тому вони мають бути вільними від інших даних. Якщо перед умовами буде порожній рядок, результатом фільтрації будуть всі записи вихідної таблиці. Введіть умови фільтрації, зазначені у завданні (див. рис.4.8).
Малюнок 4.8. Формування умов фільтрації
Всі умови одного рядка з таблиці критеріїв з'єднуються логічною операцієюІ, таким чином будуть показані ті рядки вихідної таблиці, які задовольняють всім умовам одночасно. Всі рядки таблиці критеріїв з'єднуються між собою логічною операцієюАБО, таким чином будуть показані записи вихідної таблиці, які задовольняють або всім умовам першого рядка з таблиці критеріїв, або всім умовам другого рядка, або всі умовам третього рядка і т.д. буд. У прикладі критерій відбору слід задати у двох рядках таблиці критеріїв. У випадку таблиця критеріїв може містити будь-які логічно вирази, зокрема і формули.
В) Для запуску механізму фільтрації необхідно:
- Виділити вихідну таблицю повністю, крім першого рядка заголовка.
- Виконати командуДані, Сортування та фільтр, Додатково, Розширений фільтр. З'явиться діалогове вікноРозширений фільтр(див. рис. 4.8).
- У групіОбробкавибратиперемикачСкопіювати результат в інше місце. У цьому випадку вихідна таблиця не зміниться, а відібрані записи будуть поміщені в діапазон, який ви вкажете в поліПомістити результат у діапазон.
- Встановіть курсор введення символів у полеДіапазон умові введіть посилання на таблицю критеріїв, у нашому випадку це будуть комірки з номерами $A$24:$J$26.
- Якщо вибрано перемикачСкопіювати результат в інше місце(у нашому випадку він вибраний), перейдіть до поляПомістити результат у діапазоні введіть посилання або ім'я діапазону призначення.
- НатиснітьОк. Результати повинні відразу відобразитись на робочому аркуші.