Практична робота 4 на тему Пошук даних в списках за допомогою розширеного фільтру та побудова зведених таблиць у середовищі MS Excel

Практична  робота № 4.2 Тема: Пошук даних в списках за допомогою розширеного фільтру, побудова зведених таблиць та зведених діаграм у середовищі MS Excel

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

Теоретична довідка

ВИКОРИСТАННЯ РОЗШИРЕНОГО ФІЛЬТРА

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

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

Складний критерій містить у собі декілька простих крите­ріїв, сполучених або по вертикалі – декілька умов на одне по­ле, або по горизонталі – по одній умові на декілька полів.

Критерії, сполучені по горизонталі, поєднуються зв'язкою "И", тобто записи зі списку за таким критерієм вибираються у випадку, коли всі прості критерії, що входять у складний кри­терій, виконуються.

Критерії, сполучені по вертикалі, поєднуються зв'язкою "ИЛИ", тобто записи зі списку за таким критерієм вибираються, коли хоча б один із простих критеріїв, що входять у складний критерій, виконується. У таких критеріях допускається заголо­вок поля заносити тільки один раз.

Складений критерій – це сукупність декількох складних критеріїв – містить по декілька умов на декілька полів.

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

Після створення критеріїв можна використовувати Расши­ренный фильтр для організації запитів. Розширений фільтр ви­кликається на вкладціДанныев групіСортировка и фильтркомандоюДополнительно. З'явиться діалогове вікно розширено­го фільтру.

У цьому вікні автоматично позначиться діапазон списку (зона "Исходный диапазон"), якщо список був попередньо виді­лений або курсор мишки розміщувався на будь-якому із запи­сів списку.

У зону"Діапазон условий" треба занести адреси потрібного діапазону критерію і, якщо натиснути на [Ok], у списку будуть показані тільки ті записи, що відповідають уведеному критерію.

Якщо ж вибрати мишкою покажчик "Скопировать результат в другое место", то активізується зона "Поместить результат в диапазон", де треба вказати адресу лі­вої верхньої комірки майбутнього діапазону для вставляння вибраних зі списку записів.

При повторній фільтрації будуть проглядатися усі рядки, і приховані і відкриті.

Приклад

Нижче наведені приклади створення критеріїв для пошуку такої інформації:

1. Критерій 1 – вибрати зі списку всі записи про фірму "САМ". За правилами створення критеріїв діапазон для них буде розміщуватися у робочому аркуші по рядках.

Такий запит включає одну умову (назву фірми – "САМ"), тому створюється простий критерій для пошуку в полі Фірма/місто фірми "САМ".

У комірку G1 копіюється заголовок поля Фірма/місто, а в комірку G2 – умова для пошуку – "САМ" - Київ:

Таким чином, діапазон створеного простого критерію роз­міщується у комірках G1:G2.

2. Критерій 2 – вибрати зі списку всі записи, що стосуються або фірми "САМ", або "Вояж", або "Венея".

Такий запит включає три умови, що накладаються на поле Фірма/місто (фірма – або "САМ", або "Вояж", або "Венея"), тому створюється складний критерій із трьох простих, які поєднані зв'язкою "ИЛИ".

У комірку GЗ копіюється умова для пошуку – "Вояж"- Донецьк, а в комірку G4 – " Венея "- Донецьк:

Діапазоном створеного складного критерію будуть комірки G1:G4.

3. Критерій 3 – вибрати зі списку всі записи, де кількість груп більше ніж 10 і менше ніж 25.

Такий запит включає дві умови, тому створюється критерій для пошуку в полі Кількість груп, які поєднані зв'язкою "И".

У комірки G1 і H1 копіюється заголовок поля Кількість груп, а в комірку G2 – умова для пошуку  >10, а в комірку H2 – умова для пошуку <25:

Діапазоном створеного складного критерію будуть комірки G1:H2.

Нижче розміщені дані для фільтрації і результати запитів.

ЗВЕДЕНІ ТАБЛИЦІ

Зведені таблиці використовують для аналізу значної кількості даних у великих таблицях. Зведена таблиця містить всі або лише потрібні для аналізу дані основної таблиці, які відображені на екрані так, щоб залежності між ними проглядалися якнай­краще. Користувач залежно від умови задачі має лише зазначити, що відображати в заголовках рядків і стовпців, які дані відображати на їх перетині, а також по якому полю з основної таблиці групувати дані. Зведені таблиці  можна створити на вкладці Вставка командою Сводная таблица із групи Таблицы.

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

Завдання 2. Створити проміжні підсумки по полюНазва фірми.

Завдання 3. Створити зведену таблицю та зведену діаграму для вказаних у умов на окремому робочому аркуші.

Технологія виконання роботи

1. Завантажити табличний процесор Excel.

2. На робочому аркуші Лист1 побудувати таблицю моніторингу діяльності фірм. Виконати сортування даних за назвою фірми.

3. Скопіювати таблицю на Лист2, Лист3, Лист4, Лист5, Лист6.

4. На робочому аркуші Лист2 за допомогою розширеного фільтру відібрати дані з інвестиційними витратами більше 100 тис. грн. і терміном окупності більше півроку.

5. На робочому аркуші Лист3 за допомогою розширеного фільтру відібрати дані для фірми "Лідер", інвестиційні витрати яких перевищують 100 тис. грн.  і мають термін окупності більше 1 року.

6. На робочому аркуші Лист4 за допомогою розширеного фільтру відібрати дані для фірми "Лідер", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

7. На робочому аркуші Лист5 за допомогою розширеного фільтру відібрати дані для фірм "Лідер", "Термопласт", коли виручка від реалізації продукції є в межах від 150 тис. грн. до 200 тис. грн.

8. На робочому аркуші Лист6 за допомогою розширеного фільтру відібрати дані для фірм "Лідер", "Термопласт", коли податки більше 25 тис. грн. або   менше  20 тис. грн.

9. На робочому аркуші Лист1 створити проміжні підсумки по полю Назва фірми для обчислення  суми інвестиційних витрат, виручки від реалізації продукції, поточних витрат, податків, терміну окупності.

10. Для цього слід використати команду Промежуточный итог на вкладці Данные в групі  Структура.

11. На робочому аркуші Лист7 за допомогою зведеної таблиці визначити максимальну виручку від реалізації продукції кожною фірмою.

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

13. Побудувати зведену діаграму відсоткового відношення суми поля "Інвестиційні витрати" кожної фірми до загальних витрат.

14. Зберегти електронну таблицю під назвою Прізвище_Практична4_2 на сервері у власній папці.

15. Оформити звіт і здати роботу викладачу.

Контрольні питання

1) Що таке база даних?

2) Як виконати сортування записів у базі даних?

3) Що таке фільтрація даних і які різновиди фільтрів для виконання фільтрації даних існують у базі даних MS Excel?

4) Як створити автофільтр?

5) Як створити розширений фільтр?

6) Яке призначення мають проміжні підсумки?

7) Яка технологія створення проміжних підсумків?

8) Яке призначення мають зведені таблиці і зведені діаграми?

9) Яка технологія створення зведених таблиць і зведених діаграм?

З повагою ІЦ “KURSOVIKS”!