Відбір даних засобами автофільтра

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

Excel надає дві команди для фільтрації даних: для простих критеріїв – Автофильтр; для складних критеріїв – Расширенний фильтр.

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

Можливостями автофільтра можна скористатися, розмістивши активну комірку всередині списку і скориставшись командою Данные→Фильтр→Автофильтр.

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

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

Елемент Все списку дає змогу зняти обмеження щодо поля.

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

Можна задавати й складніші критерії, ніж перевірка рівності. Скориставшись елементом списку Условие…, можна відкрити вікно діалогу Пользовательский автофильтр і з’єднати за допомогою логічних операторів окремі умови відбору записів за одним полем (з будь-якими відношеннями порівняння).

При визначенні критеріїв за текстовими полями можна використовувати також символи шаблона “? ” та “ * ”.

Застосувати автофільтр можна тільки до одного списку на робочому аркуші.

Для відображення всіх записів списку можна скористатись командою Данные→Фильтр→Отобразить все.

Для відміни (деактивізації) автофільтра потрібно повторно скористатись командою Данные→Фильтр→Автофильтр.

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

Хід роботи

1. Створити таблицю за зразком таблиці 1

2. Створити макрос з ім’ям “Додати” для створення форми для заповнення таблиці. Для цього виконати наступні дії:

— Установити курсор у поле таблиці. Виконати команду
Сервис→Макрос→Начать запись. У вікні Запись макроса, що з’явилось, в текстовому полі Имя макроса ввести назву макроса “Додати” и натиснути ОК.

— Виконати команду Данные→Форма….

— Закрити вікно форми, зупинити запис макросу командою
Сервис→Макрос→Остановить запись.

3. Винести на аркуш кнопку з надписом “ Додати ” і присвоїти кнопці макрос з ім’ям “ Додати ”.

4. Заповнити до кінця таблицю інформацією з табл. 2 використовуючи кнопку “Додати”.

5. Включити Автофильтр.

6. Користуючись Автофильтром вивести на екран всі автомобілі моделі ВАЗ.

7. Скопіювати отриману інформацію на другий аркуш. Перейменувати Лист2 на “ВАЗ”.

8. Повернутися на Лист1 і користуючись автофильтром вивести на екран всі автомобілі моделі ГАЗ.

9. Скопіювати отриману інформацію на третій аркуш. Перейменувати Лист3 на “ГАЗ”.

10. Повернутися на Лист1 і користуючись Автофильтром вивести на екран всі автомобілі моделі Аudi.

11. Скопіювати отриману інформацію в буфер обміну. Додати четвертий аркуш. Перейменувати Лист4 на “Аudi” і вставити з буфера інформацію.

12. Аналогічно додати три аркуша і відповідно перейменувати ці аркуші на “BMW”, “Ford”, “Mersedes”. Вмістити на ці аркуші інформацію про відповідні моделі.

13. Додати новий аркуш. Перейменувати Лист8 на “МЕНЮ”.

14. Створити макрос з ім’ям “На_ВАЗ” переходу з аркуша “МЕНЮ” на аркуш “ВАЗ”.

15. Створити макрос з ім’ям “Из_ВАЗ” зворотного переходу з аркуша “ВАЗ” на аркуш “МЕНЮ”.

16. Винести на аркуш “МЕНЮ” кнопку з надписом “ВАЗ” і присвоїти кнопці макрос з ім’ям “На_ВАЗ”.

17. Винести на аркуш “ВАЗ” кнопку з надписом “Перехід в меню” і присвоїти кнопці макрос з ім’ям “Из_ВАЗ”.

18. Створити макрос з ім’ям “На_ГАЗ” переходу з листа “МЕНЮ” на лист “ГАЗ”.

19. Створити макрос з ім’ям “Из_ГАЗ” зворотного переходу з листа “ГАЗ” на лист “МЕНЮ”.

20. Винести на аркуш “МЕНЮ” кнопку з надписом “ГАЗ” і присвоїти кнопці макрос з ім’ям “На_ГАЗ”.

21. Винести на аркуш “ГАЗ” кнопку з надписом “Перехід в меню” і присвоїти кнопці макрос з ім’ям “Из_ГАЗ”.

22. Створити аналогічні кнопки і макроси для інших моделей автомобілів (BMW, Ford, Mersedes).

23. Оформить аркуш “МЕНЮ” (розташувати усі кнопки у рядок, створити рамку навколо кнопок, змінити колір заливки, у середині рамки, видалити сітку, заголовки стовпців та строк командою Сервис→Параметры…).

24. Виконати збереження створеної книги у файлі з ім’ям EXL_15.

Контрольні питання

1. Що таке список в Excel?

2. Як створити потрібні списки?

3. Як здійснити фіксацію діапазону з іменами полів?

4. Як поділити вікна на підвікна?

5. Чим відрізняються фіксація діапазону і поділ на підвікна?

6. Які можливості надає користувачу форма даних?

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

8. Як скасувати внесені в запис зміни?

9. Як відсортувати списки за власним порядком сортування?

 

10. Як відсортувати записи списку за більш як трьома полями?

11. Як забезпечити можливість відновлення початкового порядку записів списку?

12. Яких вимог потрібно дотримуватись при введенні формул у поля списку, щоб уникнути проблем із сортуванням?

13. Можливість автофільтра.

14. Як за допомогою автофільтра відібрати 5% записів, що мають найбільше значення за певним числовим полем?

15. Як за допомогою автофільтра відібрати записи, де значення деякого числового поля належать до певного інтервалу?

16. Як за допомогою автофільтра відібрати записи, де слова певного текстового поля містять “в” на третій позиції?

Лабораторна робота № 16
Робота з даними.

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