За допомогою автофільтра вибір окремих рядків таблиці можна здійснювати лише безпосередньо у робочому аркуші. Активізація автофільтра виконується командою Дані/Фільтр/Автофільтр. Для коректного виконання команди покажчик активної клітини (табличний курсор) повинен знаходитись у межах списку даних. Якщо перед виконанням команди було виділено певний діапазон таблиці, то у першому рядку цього діапазону для кожного поля (колонки) даних буде встановлена окрема кнопка для введення критерію фільтрації за цим полем. Дія критерію фільтрації поширюється на всі рядки таблиці, які знаходяться нижче кнопок. Якщо жодного діапазону таблиці не було виділено перед виконанням команди (але покажчик активної клітини знаходився у межах таблиці даних), то місцем розташування кнопок буде перший рядок таблиці, де звичайно знаходяться назви колонок. Після натискання на кнопку розкривається список елементів, які містить відповідна колонка і в якому можна вибрати необхідний елемент для визначення критерію відфільтровування даних. Це приведе до вилучення з екрана тих рядків списку даних, які не відповідають уведеному критерію, і до забарвлення відповідної кнопки у синій колір. Критерії фільтрації можна задавати послідовно для кількох полів. Кожен додатковий критерій зв'язаний із попереднім умовним оператором «І», через що кожна наступна умова завжди буде належати до тих рядків даних, які будуть отримані при використанні попередніх умов.
Критерій фільтрації, що складається з двох умов для однієї колонки, можна визначити після натискання на відповідну кнопку і вибору елемента Умова.На екрані з'явиться діалогове вікно Користувацький автофільтр,у якому можна задати два критерії, об'єднуючи їх умовними операторами «І» чи «Або».В умовах відбору рядків є можливість задавати також і шаблони, використовуючи знаки-замінники — «?» та «*». Знак запитання може використовуватися замість будь-якого одного символу поля, а зірочка — замість будь-якої кількості символів.
Для відновлення рядків таблиці можна натиснути на відповідну кнопку фільтра, розкрити список елементів і вибрати елемент Все.Це дає змогу скасувати дію критерію фільтрації за відповідною колонкою. Другий спосіб — це вибір з меню Данікоманди Фільтр/Показати все.Він використовується, коли потрібно відновити відображення всіх рядків даних за рахунок відміни дії критеріїв фільтрації за усіма колонками.
Для того, щоб вилучити з таблиці кнопки фільтрування, треба ще раз виконати команду Дані/Автофільтр,тобто зняти позначку з цієї команди.
При використанні розширеного фільтра критерії фільтрування можна задавати у робочому аркуші. В такому разі у діалоговому вікні при активізації функції розширеного фільтра вказується інтервал (діапазон) клітин робочого аркуша, якій містить критерії фільтрації даних. Перевага цього способу полягає у тому, що користувач завжди має чітке уявлення про критерії, які він застосовує, і може швидко змінити їх залежно від своїх потреб.
Основою посиленого фільтра є зона критеріїв. Перший рядок цієї зони має мати назви колонок списку (таблиці) даних. У ньому можуть знаходитись або назви всіх колонок списку даних (для цього їх можна просто скопіювати), або назви лише тих колонок, для яких будуть визначатись умови відбору рядків даних. У клітинах зони критеріїв, що знаходяться під назвами колонок, записуються умови відфільтровування рядків даних. Умови, які знаходяться в одному рядку критеріїв, з'єднуються оператором «І». Умови, які знаходяться у різних рядках області критеріїв, з'єднуються оператором «Або».
Після запуску команди на виконання кожен рядок зі списку даних перевіряється на відповідність умовам кожного рядка зони критеріїв. Якщо рядок зі списку даних відповідає умовам хоча б одного рядка із зони критеріїв, то цей рядок даних з'являється у вихідному списку.
Для запуску команди з меню Данівибирається команда Фільтр/ Розширений фільтр. На екрані з'явиться діалогове вікно, у якому можна вказати:
- у ділянці Обробкаодну з двох дій з даними: фільтрувати список даних на місці або скопіювати відфільтрований результат в інше місце робочої книги;
- вихідний діапазон або інтервал списку даних, тобто адресу даних, які мають бути відфільтровані. Інтервал списку даних можна ввести або з клавіатури, або виділити необхідний діапазон даних за допомогою миші. В останньому випадку адреса даних буде перенесена у поле введення інтервалу списку автоматично;
- діапазон або інтервал критеріїв, тобто адресу місцезнаходження
критеріїв фільтрації. Інтервал критеріїв уводиться аналогічно інтервалу списку даних, але слід стежити, щоб у цьому інтервалі не було порожніх рядків, інакше у відфільтрованому списку будуть представлені всі без винятку рядки списку даних. Це пояснюється тим, що відсутнім умовам порожнього рядка відповідають будь-які дані і він з'єднується з іншими рядками критеріїв умовним оператором «Або»;
- місце копіювання відфільтрованого списку даних у полі Розташувати результат у діапазоні.Адреса місцезнаходження відфільтрованого списку даних вводиться аналогічно попереднім адресам, але це можна зробити лише у тому разі, якщо було включено індикатор Скопіювати результат в інше місце.Крім того, треба мати на увазі, що результат фільтрації не може бути скопійовано на інший робочий аркуш, оскільки він має знаходитися на одному аркуші зі списком, що фільтрується.
7.2 Обчислення групових характеристик
«Згущення» інформації — одна з основних функцій будь-якої інформаційної системи. Припустімо, що за наявності даних по кожному продажу потрібно виявити обсяг продажів у різних містах. Для вирішення таких завдань Ехсеl має функцію автоматичного визначення характеристик вказаних груп даних. Для її виконання необхідно здійснити таке:
- відсортувати таблицю за колонкою, однакові значення полів якої мають визначати належність рядка таблиці до певної групи даних. Якщо ж цього не зробити і записи якоїсь групи даних не будуть розташовані поряд, то отримати загальні характеристики цієї групи буде неможливо;
- завести курсор у зону списку, з даних якого можна отримати
необхідну інформацію. Ехсеl спробує автоматично визначити її розміри. Якщо є бажання заборонити автоматичне її визначення, користувач може самостійно виділити потрібний діапазон клітин;
- вибрати команду Дані/Підсумки, що відкриє діалог Проміжні
підсумки з трьома полями: 3 кожною зміною в, Операція,
Додати підсумок з.З правого боку кожного поля є кнопка, при натисканні якої розкривається список можливих значень відповідного поля;
- занести параметри, які забезпечують отримання необхідних
характеристик груп. Поле 3 кожною зміною впризначається для
представлення колонки, значення полів якої буде визначати належність кожного рядка до певної групи даних (рядки таблиці мають
бути упорядковані за значенням полів цієї колонки). Оскільки необхідно обчислити дані по кожному місту, слід активізувати у цьому
полі елемент Місто.Для підсумування відповідних даних зі списку
поля Операціяпотрібно вибрати функцію Сума. У полі Додати під
сумкизаувімкненням індикатора необхідно вказати колонку, клітини якої будуть використовуватися для обчислення підсумків. У даному разі це Вартість.Якщо одночасно треба обчислити й кількість проданих комп'ютерів у кожному місті, то біля назви колонки Кількістьтеж треба увімкнути індикатор. Індикатори біля назв усіх інших колонок мають бути вимкнені;
- натиснути кнопку ОК.
У результаті виконання команди таблиця буде доповнена рядками, в яких будуть представлені обсяги продажів для кожного міста окремо. Останній доданий рядок матиме підсумкову інформацію щодо усіх міст.
З кожною групою даних може бути виконана одна з операцій, зазначених у полі Операція. Операції, які можна вказати (або вибрати, клацнувши на стрілці цього поля), наведені у табл. 1.
Ще одна можливість, яку дає команда Дані/Підсумки,— це виведення у кожній групі даних кількох типів підсумків з використанням різних операцій. Таблиця. 1
Вбудовані функції для визначення характеристик групи даних
| Назва функції | Результат |
| Сума | Сума значень у полі групи аналізованих даних |
| Кількість значень | Кількість елементів у групі |
| Середнє | Середнє арифметичне значення групи |
| Максимум | Найбільше значення групи |
| Мінімум | Найменше значення групи |
| Добуток | Добуток усіх значень групи |
| Кількість чисел | Кількість клітин у групі, що містять числові значення |
| Незміщене відхилення | Оцінка стандартного відхилення за генеральною сукупністю |
| Зміщене відхилення | Оцінка стандартного відхилення за вибіркою |
| Незміщена дисперсія | Оцінка дисперсії за генеральною сукупністю |
| Зміщена дисперсія | Визначає значення дисперсії за вибіркою |
Для визначення кількості продажів у полі Операціяпотрібно вибрати функцію Кількість значень.У третьому полі для занесення результату можна вибрати назву будь-якої колонки, оскільки вибрана функція не використовує значення полів, а тільки підраховує кількість рядків у групі. Але для того, щоб групові значення за попередніми критеріями були теж представлені в таблиці, перед натисканням кнопки ОКобов'язково треба вимкнути параметр Замінити поточні підсумки.
При визначенні групових значень з лівого краю таблиці проставляються рівні структури, що забезпечує кращий візуальний контроль даних. Групування даних може виконуватись як за рядками, так і за колонками. Далі за допомогою операцій приховування і показу окремих груп і рівнів можна вивести на екран лише потрібну інформацію.