План-конспект
Тема: “Сортування, фільтрація, розширений фільтр. Автофільтр”.
Мета: ознайомити учнів із такими поняттями як сортування, фільтрація та їхніми видами.
Методичне забезпечення: роздатковий матеріал.
Тип уроку: засвоєння нових знань.
СТРУКТУРА УРОКУ
1. Організаційний момент
- перевірка присутності учнів
- підготовка до уроку
2. Актуалізація опорних знань
- Яке призначення зведеної таблиці ?
- Як створити зведену таблицю ?
- Які звіти зведеної таблиці існують ?
- Яким чином можна змінити звіт зведеної таблиці ?
3. Викладення нового матеріалу
Сортування - це зміна відносного положення даних у списку відповідно до значень або типу даних.
Щоб почати сортування по яких-небудь елементах списку, виконайте наступні дії:
• клацніть по елементу таблиці або виділіть діапазон осередків - область сортировки;
• ввійдіть в меню Дані - Сортування;
• в діалозі Сортування діапазону, що відкрився, задайте стовпці для сортування (всього їх може бути три).
Якщо у стовпців вашої таблиці відсутні підписи, то для ідентифікації стовпців можна використовувати позначення стовпців на листі. Для цього в діалозі Сортування діапазону поставте перемикач Ідентифікувати поля по позначеннях стовпців листа.
Звичайно як поля сортування використовують стовпці, а не рядки, хоча процедура допускає сортування і по рядках. Останнє реалізується, якщо в діалозі сортування натискувати кнопку Параметри і в наступному діалозі Параметри сортування встановити перемикач Сортувати стовпці діапазону.
Параметром сортування є також прапорець Враховувати регістр. Його установка приведе до того, що слова, що починаються з прописної букви, сортуватимуться раніше слів, що починаються з рядкової букви.
Фільтрація - це виділення визначених записів, що задовольняють заданим критеріям.
Результатом фільтарції є створення списку з відфільтрованих записів. У Excel передбачено два режими фільтрації: Автофильтр і Расширенный фильтр.
Вже з назви засобу автофильтр слідує, що воно повинно бути простим і автоматизованим інструментом фільтрації даних. Цей засіб викликається командою Дані - Фільтр - автофильтр. В застосуванні автофильтра слід розрізняти два випадки:
Фільтрація без виділення діапазону
Якщо перед виконанням фільтрації не був виділений діапазон, то після команди автофильтр праворуч від назви кожного стовпця з'явиться кнопка із списком, що розкривається. Цей список відкривають позиції Всі, Перші 10... Умова... потім слідують елементи стовпця, розташовані в алфавітному порядку, і завершать список пункти Порожні і Непорожні. Прокрутивши список, ви можете вибрати необхідний вам елемент. Відразу після цього на екрані залишаться лише записи, що пройшли фільтрацію, а решта записів буде прихована. Заголовки відфільтрованих рядків забарвлені за умовчанням в синій колір. Ви можете продовжити фільтрацію, указуючи в списках інших стовпців необхідні елементи, а можете повернутися до початкового виду таблиці, відзначивши в списку пункт Все. Для повної відміни процедури фільтрації потрібно ввійти в меню Дані - Фільтр і прибрати галочку з команди автофильтр.
Фільтрація виділеного діапазону
Якщо перед викликом команди автофильтр був виділений діапазон осередків, то в списках елементів для фільтрації будуть вказані лише стовпці з цього діапазону. Відзначивши пункт Перші 10... в списках, що розкриваються, ви зможете вибрати перші десять рядків у формованій таблиці. Якщо вказати на цей пункт в стовпцях, що містять числа, то відкриється діалог Накладення умов за списком, в якому вам буде запропонований вибрати критерій для відбору 10 елементів списку: найбільших або якнайменших. Для стовпців, що містять текстові дані, критерію для відбору перші 10 записів не існує, тобто в цьому випадку виводяться дійсно перші десять записів початкової таблиці. До речі, кількість записів, що виводяться, ви можете змінити, задавши потрібне число за допомогою лічильника в діалозі Накладення умов за списком.
Призначений автофільтра
Точні критерії для фільтрації можна задати за допомогою пункту Умова, який є в списках автофильтра, що розкриваються. Якщо відзначити цей пункт, то з'явиться діалог Призначений для користувача автофильтр, в який потрібно ввести умову фільтрації по відповідному стовпцю. Назва цього стовпця відображається у верхній частині діалогового вікна над рядками введення умов.
В діалозі Призначений для користувача автофильтр можливо завдання двох умов, записаних через оператори порівняння = < >... і з'єднаних логічним І або АБО. Вибрати потрібний оператор порівняння можна із списку, що розкривається, а значення, з яким порівнюватимуться елементи, можна легко записати, якщо користуватися масками
В Excel на додаток до операторів порівняння є наступні критерії вибору: Починається (не починається) із заданого тексту, Закінчується (не закінчується) заданим текстом і Містить (не містить) заданий текст.
Для фільтрації списків програма Місгозой Ехсеl крім команди Автофильтр пропонує користувачам команду Расширенный фільтр, яку використовують для створення складних умов відбору даних.
Порядок роботи з розширеним фільтром такий:
• Спочатку слід створити таблицю-критерій. Для цього під початковою таблицею введіть назви стовпців, по яких ви збираєтеся провести фільтрацію. Назви стовпців краще всього вводити копіюванням з відповідного рядка початкової таблиці. Рядком нижче під назвами введіть з клавіатури критерії відбору. Як критерії можуть виступати текстові записи, співпадаючі із значеннями осередків (для стовпців з текстом), або числа або умови, оператори порівняння, що містять (для стовпців з числами). Таблиця-критерій може розміщуватися, взагалі кажучи, в будь-якому місці листа, але зручніше її поміщати все ж таки під початковою таблицею.
• Активізуйте будь-який елемент початкової таблиці і виконайте команду Данные – Фільтр – Розширений фільтр. В результаті з’явиться діалог Расширенный фільтр, в якому потрібно задати параметри розширеного фільтру.
• У верхнє поле введення вікна Розширений фільтр потрібно ввести діапазон фільтрованої таблиці (програма звичайно саму правильно встановлює початковий діапазон), а в середньому полі введення необхідно задати діапазон таблиці-критерію (в даному випадку $А$13:$Е$14).
Команда Расширенный фільтр вимагає визначення умов відбору рядків в окремому діапазоні комірок робочого аркуша. Діапазон умов рекомендується задавати в порожніх рядках над списком.
Копіювання відфільтрованих рядків в іншу частину робочого аркуша
За допомогою вікна діалогу команди Расширеный фільтр можна встановити режим копіювання відібраних рядків в іншу частину аркуша замість виведення відфільтрованого списку. Щоб скопіювати рядки, треба перемикач Обработка встановити в положення Скопировать результат в інше місце, а в полі Поместить результат в діапазон зазначити адресу діапазону, куди потрібно скопіювати інформацію.
• Режим Только уникальные записи у вікні команди Расширенный фільтр додає додатковий фільтр до заданих умов відбору. Він приховує рядки, що повторюються.
4. Закріплення вивченого матеріалу
1. Що таке сортування ?
2. Як створити фільтрація ?
3. Які є види фільтрації ?
5.Підсумок уроку
6.Домашнє завдання
II
Сортування - це зміна відносного положення даних у списку відповідно до значень або типу даних
Види сортування
1. Просте сортування - (по одному стовпчику). За допомогою кнопки на панелі інструментів Стандартна 
2. Складне сортування - (по 2 і більше стовпцях). Данные →Сортировка → в діалоговому вікні Сортировка диапазона→ в розкриваючомуся списку Сортировать, виберіть поле і вкажіть порядок сортування: потім зробіть те ж саме для другого поля → ОК
Задання додаткових параметрів сортування
Натиснути на кнопку Параметры і в діалоговому вікні Сортировка диапазона.
В діалоговому вікні Параметры сортировки можна задати наступні параметри:
Сортировать по первому ключу – дає можливість визначити особливий порядок сортування (Наприклад: назви місяця, дні тижня та ін.).
Учитывать регистр – при сортуванні буде враховуватись різниця між великими і малими літерами. (1 – великі; 2 - малі).
Сортировать – дає можливість виконувати сортування по стовпцях, а не по рядках.
Фільтрація списку – це процес приховування всіх рядків, окрім тих, які задовольняють визначеним критеріям.
Автофільтр:
1. Встановити курсор в одну з комірок списку.
2. Данные Фильтр Автофильтр. Excel проаналізує список і добавить в стрічку заголовків полів кнопки розкриваючих списків (кнопки автофільтра)
3. Список кнопки відкриває позиції Все, Первые 10, Условие…, за алфавітом розміщені елементи стовпця.
4. Вибрати потрібний елемент
Після того, як список буде відфільтрований, то в стрічці стану з’явиться повідомлення про те, скільки стрічок відібрано. Крім того, зміниться колір кнопки розкриваючого списку, щоб нагадати, що список відфільтрований позначенням.
Автоматична фільтрація має певні границі. В розкриваю чому списку з’явиться тільки 1000 перших різних значень. Якщо кількість елементів у списку переважає вказані границі, можна використати засіб розширеної фільтрації (вона описана нижче).
Крім всіх значень стовпчика, розкриваючийся список складає ще 5 пунктів.
Все. Відображає всі елементи стовпця. Використовується для відміни фільтрації стовпця.
Перші 10. Вибирає «найкращу десятку» елементів списку.
Умова. Фільтрує список, по декількох умовах.
Пусті. Фільтрує список, відображаючи тільки стрічки з пустими комірками в даному стовпчику. Цей пункт появляється в кінці списку тільки в тому випадку, якщо в стовпчику є одна або більше порожніх комірок.
Непорожні. Фільтрує список, вказуючи тільки стрічку з не порожніми комірками в даному стовпчику. Цей пункт з’являється вкінці списку тільки в тому випадку, якщо в стовпчику є одна або більше пустих комірок.
Щоб весь список був відображений знову клацніть на кнопці розкриття списку, або виконайте команду Данные > Фільтр > Автофильтр повторно. В результаті буде видалений прапорець в пункті меню Автофильтр і список набуде попереднього стану.
Часто для обробки списку буває достатньо автофільтра. Але, якщо необхідно виконати операцію, вихідну за рамки можливостей автофільтра, то доведеться звернутися до допомоги засобів розширеної фільтрації. Розширений фільтр набагато більше гнучкий, за автофільтр, однак при його використанні потрібно виконувати більше підготовчих дій. Розширений фільтр дозволяє виконувати наступне:
Визначити більш складний критерій фільтрації;
Встановити обрахунковий критерій фільтрації;
Відобразити тільки різні (неповторні) записи;
Переміщати копії стрічок, які відповідають визначеному критерію, в інше місце.
Розширений фільтр:
1. Задати діапазон критеріїв відбору
2. Данные ФильтрРасширеный фильтр
3. Задати вихідний діапазон даних
4. Задати діапазон умов
5. Вказати де буде виконуватись фільтрація:
Фільтрація списку на місці (за замовчуванням)
Скопіювати результат до іншого розташування
6. Якщо буде використовуватись 2 спосіб, то ввімкнути відповідну опцію.
Типи критеріїв відбору
текстові або числові (оператори - =, >, <, >=, <=, <>, символи підстановки - *, ?
критерії, що виконують обрахунки під час фільтрації – обраховують для списку нове поле
Правила для застосування критеріїв, що виконують обрахунки під час фільтрації:
Не використовувати заголовки полів списку в діапазоні критеріїв. Створіть новий заголовок або просто залиште пусту комірку.
Можна використовувати будь-яку кількість критерії, що виконують обрахунки, а також поєднання критеріїв, що проводять обрахунки і тих, що не проводять
4. Закріплення вивченого матеріалу
1. Фронтальна бесіда
1. Що таке сортування?
2. Які є види сортування?
3. Як виконується просте сортування?
4. Як виконується складне сортування?
5. Які додаткові параметри сортування можна задати?
6. Які засоби фільтрації пропонує нам програма Excel?
7. Яка послідовність використання автофільтра?
8. В яких випадках використовують розширений фільтр?
9. Яка послідовність використання розширеного фільтра?
|