Табличний процессор - Завдання 2
"Табличний процесор Microsoft Excel. Пошук даних. Зведені таблиці "
Мета: формування умінь сортувати та фільтрувати дані в таблицях, робити потрібні вибірки, підбивати підсумки й створювати зведені таблиці.
Питання для вивчення
1. Сортування даних у таблицях.
2. Пошук даних.
3. Підведення підсумків за допомогою інструмента ИТОГИ.
4. Створення зведених таблиць.
Розширений фільтр
Використання розширеного фільтра дає змогу:
1) для кожного поля вводити більш ніж два значення для пошуку;
2) використовувати обчислення при введенні критеріїв пошуку; 3) вводити з клавіатури значення для пошуку даних.
При використанні розширеного фільтра критерії відбору задаються безпосередньо в робочому листі (діапазон критеріїв). Користувач завжди може контролювати та швидко змінювати критерії. Але головне, можна задавати як завгодно складні критерії, що зв’язують елементарні умови в одному полі або різноманітних полях логічними операторами «И» та «ИЛИ». При копіюванні можна не тільки враховувати складні критерії, але і відбирати окремі потрібні поля з відфільтрованих записів.
Діапазон критеріїв (інтервал критеріїв) можна оформити у будь-якому вільному місці робочого листа. Обираючи місце під діапазон, виходять із того, щоб він не заважав. У діапазоні критеріїв можна вводити та сполучати два типи критеріїв:
Порівняльні критерії – порівнюють вміст полів за заданою умовою (аналогічно застосуванню автофільтра).
Обчислювальні критерії використовують для записувати формули, що містять стандартні функції, та перевіряти складні умови. Наприклад, використовуючи обчислювальні критерії, легко можна виділити у списку тільки тих робітників, у яких зарплата не менше ніж на 25% перевищує середню зарплату.
Діапазон критеріїв повинен складатися не менше ніж із двох рядків. У першому рядку для порівняльних критеріїв необхідно зазначити імена полів. Причому можна ввести тільки ті імена полів, для яких указуються обмеження. З урахуванням необхідності точного збігу імен полів, їх простіше усього одержати шляхом копіювання. У рядку (рядках), розташованих безпосередньо під рядком з іменами полів, потрібно зазначити обмеження, що мають традиційний вигляд (наприклад: >=5000, або <=30) та інтерпретуються наступним чином: обмеження розташовані в одному рядку зв’язуються логічним оператором «И»; обмеження у різних рядках зв’язуються логічним оператором «ИЛИ»; ім’я поля може повторюватися у рядку з іменами полів.
Пошук даних у таблиці засобами Розширеного фільтра
1. Для того, щоб відфільтровувати дані таблиці за допомогою розширеного фільтра, необхідно, щоб поля таблиці мали заголовки, тобто стовпчики мали назви. На листі електронної таблиці також повинно бути не менше трьох порожніх рядків вище від списку даних, які потім використовуватимуться як діапазон умов відбору – блок критеріїв.
2. Скопіювати із списку заголовки стовпчиків, в яких здійснюється пошук.
3. Вставити скопійовані назви стовпчиків до першого порожнього рядка блоку критерію – діапазону умов відбору.
4. Увести в рядки під заголовками умов блоку критеріїв необхідні критерії відбору. Переконатися, щоб між значеннями умов і списком таблиці знаходиться, як мінімум, один порожній рядок.
5. Встановити курсор на будь-яку клітину таблиці з даними.
6. Вибрати команду Данные - Фильтр - Расширенный фильтр….
7. Для того, щоб відобразити результати фільтрування, приховавши при цьому непотрібні рядки, слід встановити перемикач в положення Фильтровать список на месте.
8. Увести в поле Диапазон условий посилання на діапазон умов відбору, що включає заголовки стовпчиків. Натиснути кнопку Ok.
Обчислювальні критерії дають змогу виконувати більш складні операції, ніж просте порівняння значення поля з зазначеною константою. Наступні правила допоможуть уникнути помилок:
- Заголовок стовпчика над обчислювальним критерієм (перший рядок діапазону критеріїв) не повинний збігатися з іменем деякого поля в аналізованому списку. Він може бути порожнім або містити будь-який інший текст.
- Формула повинна повертати значення логічного типу.
- Задавати посилання на поле списку можна шляхом указівки відносної адреси першої клітинки поля.
- Посилання на клітинки поза списком повинні бути абсолютними.
- Посилання на клітинки усередині списку повинні бути відносними.
- Посилання на постійний діапазон клітинок усередині списку потрібно задавати в абсолютному вигляді.
Наприклад, обчислювальний критерій може мати вигляд: =J3/H3<10; =C8–D8<18*365 – таким чином, за даними про дату прийому на навчання та дату народження, можна відібрати тих, хто поступив у віці до 18 років; =F3>=СРЗНАЧ($F$3:$F$325)*1,25 – таким чином, за даними про зарплату, можна відібрати тих, чия зарплата не менш ніж на 25% перевищує середню.
Приклад використання розширеного фільтра
