11 клас інформатика

Розширений фільтр

Способи фільтрування, розглянуті вище, дають змогу виконати фільтруван­ня не для всіх випадків. Так, наприклад, розглянутими способами не можна виконати фільтрування за умовою, яка є об’єднанням умов фільтрування двох стовпців логічною операцією АБО, наприклад (сума балів більше 35) АБО (бал з інформатики більше 8). Виконати фільтрування за такою та іншими складени­ми умовами можна з використанням так званого розширеного фільтра.

Для встановлення розширеного фільтра і виконання фільтрування за таким фільтром необхідно:

1. Скопіювати у вільні клітинки електронної таблиці назви тих стовпців, за дани­ми яких буде здійснюватися фільтрування.
2. Увести в клітинки під назвами стовпців умови фільтрування (якщо ці умови по­винні об’єднуватися логічною операцією І, то вони мають розташовуватися в одному рядку, якщо логічною операцією АБО - у різних, рис. 2.95).
3. Виконати Дзні=> Сортування й фільтр => Додатково.

4. У вікні Розширений фільтр:

1.        Вибрати один з перемикачів для вибору області розташування результату фільтрування.

2.        Увести в поле Вихідний діапазон адресу діапазону клітинок, дані в яких повинні фільтруватися (найпростіше це зробити з використанням кнопки Згорнути з подальшим виділенням потрібного діапазону клітинок).

3.        Увести в поле Діапазон умов адресу діапазону клітинок, у яких розташо­вані скопійовані назви стовпців і умови (доцільно також використовувати кнопку Згорнути).

4.         Якщо був вибраний перемикач скопіювати результат до іншого розта­шування, увести в поле Діапазон для результатів адресу діапазону кліти­нок, де має розміститися результат фільтрування.

5. Вибрати кнопку ОК.

На рисунку 2.96 представлено результат фільтрування, виконаного за умова­ми, наведеними на рисунку 2.95. Проаналізуйте результат цього фільтрування і порівняйте його з результатом фільтрування, наведеним на рисунку 2.94.

Умовне форматування

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

Умовне форматування автоматично змінює формат клітинки на заданий, якщо для значення в даній клітинці виконується за­дана умова.

Наприклад, можна задати таке умовне форматування: якщо значення в клітинці більше 10, установити колір тла клітинки - блідо-рожевий, ко­лір символів - зелений і розмір символів -12.

Звертаємо вашу увагу, на відміну від фільтрування, умовне формату­вання не приховує клітинки, значення в яких не задовольняють задану умову, а лише виділяє заданим чином ті клітинки, значення в яких задо­вольняють задану умову.

В Excel 2007 існує п’ять типів правил для умовного форматування (рис. 2.97):

• Виділити правила клітинок;
• Правила для визначення перших і останніх елементів;
• Гістограми;
• Кольорові шкали;
• Набори піктограм.

Для встановлення умовного форматування необхідно:

1. Виділити потрібний діапазон клітинок.
2. Виконати Основне => Стилі => Умовне форматування.
3. Вибрати у списку кнопки Умовне форматування необхідний тип правил (рис. 2.97).

Вибрати у списку правил вибраного типу потрібне правило.

4. Задати у вікні, що відкрилося, умову та вибрати зі списку форматів формат, який буде встановлений, якщо умова виконуватиметься, або команду Настроюваний формат.
5. Якщо була вибрана команда Настроюваний формат, то у вікні Формат клітинок задати необхідний формат і вибрати кнопку ОК.
6. Вибрати кнопку ОК.

На рисунку 2.98 наведено, як приклад, вікно Між, у якому встанов­лено правило Між 7 і 9, зі списком стандартних форматів, командою Настроюваний формат, а також попередній перегляд результату застосу­вання вибраного правила умовного форматування.

Встановлення одного з правил умовного форматування типу Гістогра­ми приводить до вставлення в клітинки виділеного діапазону гістограм, розмір горизонтальних стовпців яких пропорційний значенню в клітинці (рис. 2.99).

Встановлення одного з правил умовного форматування типу Кольорові шкали приводить до заливки клітинок виділеного діапазону таким чином, що клітинки з однаковими значеннями мають одну й ту саму за­ливку (2.100).

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

Для видалення умовного форматування потрібно виконати Основне => Стилі => Умовне форматування => Правила очищення і вибрати необ­хідне правило видалення умовних форматів.

---

6. Вивчення нового матеріалу.

Лекція вчителя з демонстрацією на комп'ютері

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

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

Правила сортування

1. При сортуванні порожні клітинки завжди переміщуються у кінець відсортованого списку.
2. Числові типи даних сортуються від найменшого від’ємного до найбільшого додатного.
3. Текстові типи даних сортуються познаково зліва направо.
4. Текстові дані сортуються у такому порядку: спочатку цифри, потім пробіл та символи цифрових клавіш верхнього регістра і  тільки після цього літери в алфавітному порядку.
5. Під час сортування логічних значень значення ЛОЖЬ ставиться перед значенням ИСТИНА.

Фільтрація даних. Автофільтр.

У MS Excel можна також поміщати величезну кількість за­писів (максимальне число рядків робочого аркуша —65536). Однак не завжди треба відображати всі ці записи.

Фільтрацією називається виділення підмножини набору записів.

У MS Excel виділяють такі способи фільтрації: автофільтр і роз­ширений фільтр. Увімкнення режиму фільтрації здійснюється коман­дою Дані -» Фільтр -> Автофільтр.

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

Демонстрація вчителем операцій сортування

Вчитель відкриває таблицю, яку було створено на попере­дніх уроках. Наприклад, таблицю «Нарахування заробітної платні» (рис. 13.1).

Для сортування таблиці потрібно клацнути на будь-якій заповненій клітинці і натиснути одну з кнопок на панелі інструментів — відбудеться упорядкування за зростанням.

Вчитель звертає увагу учнів на те, що рядки повністю пере­ставляються.

Сортування за спаданням: за кількістю відпрацьованих днів, і» алфавітом.

При сортуванні списків потрібно звертати увагу на клітинки

і формулами. Після сортування по рядках «горизонтальні» посилан­ня в межах одного рядка залишаться правильними, тоді як «пере­хресні» посилання на клітинки в інших рядках, можливо, стануть хибними. Аналогічно після сортування по стовпцях «вертикальні» посилання в межах одного стовпця залишаться правильними, а по­силання на клітинки в інших стовпцях, можливо, стануть хибними.

Учитель звертає увагу: щоб уникнути проблем із сортуван­ням списків і діапазонів, що містять формули, необхідно додержу­ватись таких правил:

• у формулах, що посилаються на клітинки поза списком, ви­користовуйте тільки абсолютні посилання (адреси);
• при сортуванні по рядках (по стовпцях) не застосовуйте форму­ли з посиланнями на клітинки в інших рядках (стовпцях).

Розширений фільтр

На відміну від Автофільтра, де критерії вводяться під час ро­боти фільтра, Розширений фільтр може працювати тільки тоді, коли критерії для пошуку даних попередньо створені користувачем і за­несені у визначений діапазон клітинок таблиці. Цей діапазон пови­нен міститися над списком і бути відокремленим від списку щонай­менше одним порожнім рядком.

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

• порівняльні — порівнюють вміст полів за заданою умовою (ана­логічно застосуванню автофільтра);
• обчислювальні — дозволяють записувати формули, що містять бібліотечні функції, та перевіряти складні умови. Наприклад, використовуючи обчислювальні критерії, можна легко виділи­ти у списку тільки тих працівників, у яких зарплата менша за середню мінімальну.

Під час роботи з відфільтрованими списками слід враховувати такі особливості:

• до друку будуть відправлені тільки відображені у робочо­му аркуші записи. При застосуванні автофільтра кнопки зі стрілками, розташовані поруч з іменами полів, не дру­куються;
• при сортуванні враховуються тільки відображені записи;
• при використанні функції Автосума, що викликається кноп­кою панелі інструментів Стандартна, при обчисленні сумибудуть враховані тільки відображені записи;
• при створенні діаграми також будуть враховані тільки відо­бражені на екрані дані. Якщо відібрані записи списку змі­нилися, діаграма автоматично оновлюється. Якщо діаграма не повинна оновлюватися кожного разу, коли відбувається приховування або відображення даних, на вкладці Діаграма вікна діалогу Параметри, що викликається командою Сервіс -» Параметри, необхідно вимкнути прапорець параметра Відо­бразити тільки видимі клітинки.

ПРАКТИЧНА РОБОТА -ТУТ