double arrow

Короткі теоретичні відомості

Для того, щоб використати засоби сортування, фільтрація, зведені таблиці, консолідація та підсумки потрібно представити електронну таблицю у вигляді бази даних або списку.

Список - це один із засобів організації даних на робочому листі. Список створюється як безперервна прямокутна область клітин, що складається з рядківз однотипними даними. Наприклад, перелік працівників із приклада, наведеного в попередній лабораторній роботі, у якому стовпчики мають відповідно такі імена: № бригады, Фамилия, Сорт, Количество, Начислено (грн.) - являє собою список даних. Дані, організовані в список, у термінології Ехсеl називаються базою даних (БД). При цьому рядки таблиці - це записи бази даних, а стовпці - поля бази даних.

Щоб перетворити таблицю Ехсel у список, необхідно привласнити стовпцям імена, які будуть використовуватися як імена полів. Варто мати на увазі, що імена стовпців можуть складатися з декількох рядків заголовків, розміщених в одному рядку таблиці Ехcel, як це зроблено на рис 1.1. В прикладі список розташовано в діапазоні А2:Е9.

При створенні списку на робочому листі Ехсеl необхідно виконувати такі правила:

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

2. Варто відокремлювати список від інших даних листа хоча б одним порожнім стовпцем або одним порожнім рядком. Це допоможе Ехсеl автоматично виділити список при виконанні фільтрації або при сортуванні даних.

3. Імена стовпців повинні розташовуватися в першому рядку списку. Ехсеl використає ці імена при створенні звітів, у пошуку й сортуванні даних.

4. Для імен стовпців варто використати шрифт, тип даних, вирівнювання, формат, рамку або стиль прописних букв, відмінні від тих, які використовуються для даних списку.

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

2.1.1 Сортування даних

 
 

Для швидкого сортування на панелі інструментів Стандартная перебувають дві кнопки: сортувати по зростанню; сортувати по спаданню. Ключем сортування в цьому випадку є стовпець із поточною клітиною.

Рисунок 2.1 - Кнопки сортування на панелі інструментів

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

Команда Сортировка здійснюється також і через діалогове вікно пункту меню Данные/Сортировка. Тут можна вказати сортування списку по трьох полях. У трьох полях вікна Сортировка можна задати ключі – імена полів, по яких буде виконане сортування. Excel сортує список по першому обраному полю, а при збігу значень у першому полі, записи сортуються по другому обраному полю. Наприклад, дані в прикладі можна відсортувати по стовпчику № бригады. У другому полі діалогового вікна Сортировка можна задати наступний ключ сортування, наприклад Фамилия. Тоді список буде впорядкований по бригадах, а усередині бригад - по прізвищах (за алфавітом).Дія третього ключа сортування аналогічна.

2.1.2 Фільтрація даних у списку

За допомогою фільтрів можна виводити й переглядати тільки ті дані, які задовольняють певним умовам. Ехсel дозволяє швидко й зручно переглядати необхідні дані зі списку за допомогою простого засобу - автофільтру. Більш складні запити до бази даних можна реалізувати за допомогою команди Расширенный фильтр. Автофильтр. Щоб використати автофільтр, треба спочатку виділити область списку із заголовками полів. Потім виконатикоманду Автофильтр у меню Данные. За командою Автофильтр Ехсel розташовує список, що розкривається, безпосередньо в іменах стовпців списку. Клацнувши по стрілці, можна вивести на екран список всіх унікальних елементів відповідного стовпця. Якщо виділити деякий елемент стовпця, то будуть сховані всі рядки, крім тих, що містять виділене значення.

Наприклад, якщо вибрати значення № бригады рівне 1, то будуть обрані тільки ті робітники, які працюють у першій бригаді.

Елемент стовпця, що виділений у списку, що розкривається, називається критерієм фільтра. Можна продовжити фільтрацію списку за допомогою критерію з іншого стовпця. Наприклад, якщо теперу поле Сорт вибрати значення «Семеренко», то на екран буде виведено тільки один рядок, як показано на рис. 2.2.

Щоб видалити критерії фільтра для окремого стовпця, треба вибрати параметр Все в списку, що розкривається.


Рисунок 2.2 - Вікно із установленим автофильтром

За допомогою автофильтра можна для кожного стовпця задати потрібні критерії відбору записів, наприклад вивести на екран тільки ті записи, значення полів яких перебувають у границях заданого інтервалу. Щоб задати потрібний критерій, треба в списку, щорозкривається, вибратипараметр Условие..., а потім у діалоговому вікні Пользовательский автофильтр ввести потрібні критерії. На рисунку 2.3 показаний приклад завдання умов для поля Количество. Будуть вибиратися записи про співробітників, які зібрали більше 120 кг яблук і менше 180 кг.

 
 

Рисунок 2.3 - Установка умови у вікні

 
 

Складна фільтрація. Для фільтрації списку або бази даних за складним критерієм, що буде визначений нижче, а також для одержання частини списку, що задовольняє декільком заданим умовам, в Ехсel використається команда Расширенный фильтр меню Данные. Відмінність цієї команди від команди Автофильтр полягає в тому, що, крім перерахованих вище можливостей, відфільтровані записи можна винести в інше місце робочого листа Ехсel, не зіпсувавши початковий список.

Рисунок 2.4 - Таблиця критеріїв для розширеного фільтра

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

 
 

Якщо необхідно одержати список співробітників, яким нарахована сума в діапазоні від 20 до 23 гривень, в таблиці критеріїв кожна умова повинна бути задана окремо, але в одному рядку, тому що вони зв'язані оператором И. Таким чином, таблиця критеріїв буде мати вигляд, представлений на рисунку 2.5.

Рисунок 2.5 - Блок критеріїв з подвійною умовою

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

Кількість рядків у результаті Ехсel визначить самостійно. Таким чином, для виконання команди Расширенный фильтр треба виконати такі дії:

- сформувати у вільному місці робочого листа таблицю критеріїв (блок критеріїв);

- сформувати шапку діапазону результату (блок виведення);

- виділити область вхідного списку;

- виконати команду Данные/Фильтр/Расширенный фильтр та у вікні діалогу вказати необхідні параметри.


Для приклада, що ми розглядаємо, підготовлені для фільтрації таблиця критеріїв і блок виведення, а також список, що фільтрується, наведені на рисунку 2.6.

Рисунок 2.6 - Застосування розширеного фільтра

Тепер можна запускати команду Расширенный фильтр, по якій на екран буде виведене діалогове вікно. У цьому вікні треба задати область вхідного списку, визначити діапазон критеріїв (умов), задати рядок діапазону виведення.

В області Обработка треба вказати як буде виконуватися фільтрація. Якщо обрано режим - «фільтрувати список на місці», то Ехсеl сховає всі рядки вихідного списку, які не задовольняють заданим критеріям.

Якщо встановлено перемикач «Тільки унікальні записи», то повторювані рядки вхідного списку не будуть показані в області результату. Якщо умови пошуку задані в таблиці критеріїв в одному рядку, то ці умови зв'язані оператором И, якщо ж умови пошуку задані в різних рядках, то вони зв'язані оператором ИЛИ.

Використання критерію, що обчислюється

Завдання критеріїв, що обчислюються, вимагає виконання правил:

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

2. Формула повинна посилатися хоча б на один стовпець списку.

 
 

Наприклад, створимо таблицю критеріїв, що обчислюються, за якою ведеться пошук тих записів, де розмір заробітку (поле Начислено (грн.)) перевищує середнє значення по всіх працівниках. Формула для критерію, що обчислюється, використовує функцію СРЗНАЧ і має вигляд: =Е3>СРЗНАЧ ($Е$3:$Е$9).

Рисунок 2.7 - Використання критерію, що обчислюється

Використання критерію, що обчислюється, накладає обмеження на таблицю критеріїв. У цьому випадку ім'я стовпця в таблиці критеріїв, що містить значення критерію, що обчислюється, повинне відрізнятися від імені подібного стовпця у вихідному списку. Тому в прикладі ім'я поля Начислено (грн.) у таблиці критеріїв одержало ім'я Заработок.


Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:  



Сейчас читают про: