Конспект уроку №2 (Excel) Соменко Дмитро

Тема:

Сортування та фільтрування даних

---

Мета: Ввести поняття сортування та фільтрування даних. Формувати в учнів навики та вміння сортувати та фільтрувати дані. Виховувати в них увагу та зібраність.

Тип уроку: комбінований урок.

Хід уроку:

І. Перевірка домашнього завдання. Фронтальне опитування учнів по матеріалу попереднього уроку.

ІІ. Вивчення нового матеріалу; формування навиків та вмінь.

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

Сортування за зростанням (за спаданням)

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

• порожні комірки завжди вміщуються у кінець відсортованого списку (як у варіанті сортування за зростанням, так і за спаданням);

• числові типи даних сортуються від найменшого від'ємного до найбільшого додатного (у варіанті сортування за спаданням — навпаки);

• текстові типи даних сортуються познаково зліва направо (але тут слід мати на увазі таку особливість: запис учень 101 буде вміщено програмою під час сортування перед записом учень 11, але після запису учень 10;

• текстові дані сортуються за таким порядком: спочатку цифри, потім пробіл та символи цифрових клавіш верхнього регістра, і тільки після цього літери у алфавітному порядку;

• під час сортування логічних значень значення ЛОЖЬ ставиться перед значенням ИСТИНА. Розглянемо сортування на конкретному прикладі.

Вправа 1.

Відкрити файл вправи «Вікоі групи» яку виконували на попередньому уроці.

та виконати впорядкування імен за алфавітним порядком.

Порядок виконання роботи:

• виділити діапазон даних А1:В10;

• натиснути на панелі інструментів на кнопку Сортировать по возрастанию. Після відпускання кнопки діапазон комірок набуде вигляду, як на рис.

Зберегти дані. Закрити файл.

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

Програма дає змогу виконувати сортування навіть за кількома критеріями відбору.

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

• виділити діапазон комірок, де слід виконати сортування;

• виконати команду меню Данные / Сортировка;

• у діалоговому вікні Сортировка данных у полях Сортировать по та Затем по вказати стовпчики у тому порядку, який потрібен (спочатку за стовпчиком «Вік», а потім за стовпчиком «Особа»). Натиснути ОК, після чого таблиця набуде вигляду як на рис.

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

Нестандартне сортування

Навіть деякі введені дані можна використовувати як ключ для сортування.

Вправа 2.

Виконати сортування за статтю.

Порядок виконання роботи.

До вищенаведеної таблиці додамо стовпчик «Стать» та запишемо навпроти осіб чоловічої статі слово «чол.», жіночої — «жін.». Виконаємо сортування:

• виділити 2 комірки, де поруч є слова «чол.», «жін.»;

• виконати команду Сервис/ Параметры/ Списки/Импортировать та натиснути кнопку ОК;

• виділити діапазон комірок, дані в яких слід відсортувати;

• виконати команду меню Данные/ Сортировка встановити в полі Сортировать по курсор та натиснути кнопку Параметры;

• у діалоговому вікні Сортировка по первому ключу обрати но-востворений список, натиснути ОК;

• у полі Сортировать по обрати стовпчик «Стать», вибрати інші параметри сортування та натиснути ОК. Таблиця набуде вигляду, як на рис.

Зверніть увагу, що тільки для поля Сортировать по можна використовувати нестандартні списки. Нестандартні списки можуть містити тільки тексти или Сортировать по убиванню.

Фільтрування даних

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

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

Найзручнішим інструментом фільтрування є Автофильтр. Цей інструмент містить такі фільтри:

• Первые 10 — для відображення 10 перших записів даних;

• Сортировка по возрастанию (сортировка по убиванню) — відображає всі записи впорядковані за зростанням (за спаданням);

• Условие ... — інструмент для відображення на екрані записів, які задовольняють певні критерії, що можна задати у діалоговому вікні Пользовательский автофильтр. Тут можна задавати як прості, так і складені умови фільтрування. Розглянемо застосування автофільтра на прикладі.

Вправа 3. Використовуючи дані з попередньої таблиці , показати всіх осіб, молодших за 27 років. Порядок виконання роботи:

• виділити стовпчики з даними;

• виконати команду меню Данные/Фильтр/Автофильтр;

• після виконання команди праворуч у назвах виділених стовпчиків з'являться кнопки зі стрілками [V];

• натиснути на кнопку [V] стовпчика з назвою «Вік» та обрати з переліку можливих варіантів фільтрування Условие;

• у діалоговому вікні Пользовательский автофильтр у полі Вік обрати умову Менше или равно, у полі поруч вказати «27»;

• після натискання ОК таблиця набуде вигляду, як на рис., а кнопка у стовпчику Вік стане синього кольору.

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

Умови можуть бути такими:

1) кілька умов для одного стовпчика даних;

2) одна умова для кількох стовпчиків даних;

3) одна умова для одного стовпчика та інша для іншого;

4) набір умов для кількох стовпчиків;

5) пошук за умовою у вигляді формули. Розглянемо на прикладі роботу розширеного фільтра

ІІІ. Самостійна робота.

Завдання 1. За даними таблиці на рис. 3 виконати пошук осіб жіночої статі, молодших за 25 років. Зберегти дані в окремому файлі.

Завдання 2. За даними таблиці виконати пошук всіх співробітників, почасова тарифна ставка яких більша 25 грн, а нарахована сума за місяць перевищує 3000 грн. Зберегти дані в окремому файлі.

Завдання 3. Створить таблицю «Моя бібліотека», структуру якої показано на рис.

Заповніть таблицю даними, причому рядків з даними має бути не менше 30.

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

Завдання 4. Створить таблицю «Успішність учнів класу», структуру якої показано на рис. Заповніть таблицю даними, причому рядків з даними має бути стільки, скільки учнів навчається у класі.

На окремому аркуші виконайте кілька таких пошуків:

а) показати в алфавітному порядку учнів, які мають тільки оцінки високого рівня з усіх предметів;

б) показати у алфавітному порядку учнів, які мають оцінки високого рівня з фізики та історії;

в) показати учнів, які мають незадовільні оцінки з будь-якого предмета.

Зберегти та закрити файл.

ІV. Підсумок уроку.

Запитання:

1. Що таке сортування ?

2. Поясніть, для чого використовується нестандартне сортування ?

3. ДЛЯ чого використовують фільтрування?

4. Чим відрізняється сортування від фільтрування ?

5. У яких випадках доцільно застосовувати розширений фільтр ?

V.Домашнє завдання.