Загальні положення та рекомендації по створенню списків

По суті список — це впорядкований набір даних. Звичайно список складається з рядка заголовка і додаткових рядків даних, які можуть бути числовими або текстовими. Список можна вважати табличною базою даних. Отже список (list) — це база даних (database), яка знаходиться у зовнішньому файлі і уявляється у робочій книзі як таблиця з інформацією.

По термінології Microsoft список (list) — це забезпечена мітками послідовність рядків робочого листа, що містять в однакових стовпцях дані одного типу.

Стовпчики списку називають полями (fields), а рядки — записами (records). Теоретично розмір списку обмежується розміром одного робочого листка (256 полів ´ 65 535 записів + 1 запис із заголовками полів).

Операції, які можна виконувати над списками:

· Введення даних у список.

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

· Сортування списку.

· Вставка формул для проміжного підсумовування.

· Створення формул для вираховування результатів в списку, відфільтрованому за певними критеріями.

· Створення списку за допомогою підсумкової таблиці даних.

При створенні списку слід керуватись такими рекомендаціями:

Розміщувати заголовки полів в першому рядку списку.

* В кожному стовпчику повинна бути однотипна інформація.

* Можна використовувати формули, в яких є значення з інших полів цього ж запису. Якщо формула посилається на комірку, яка розташована поза списком, треба зробити посилання на цю комірку абсолютним, інакше результат, наприклад, після впорядкування списку може бути непередбаченим.

* Не використовувати пустих рядків у списку, так як для Excel пустий рядок при виконанні операцій над списком — признак кінця списку.

* Розміщувати список на окремому листку. Якщо це неможливо — розташовувати іншу інформацію треба нижче або вище списку. Не використовувати комірок ліворуч та праворуч від списку.

* Щоб заголовки завжди були видні при прокручуванні списку, необхідно закріпити рядок заголовків командою “Вікно ð Закріпити області” (Window ð Freeze Panes).

* Попередньо відформатувати повністю увесь стовпчик, щоб дані у стовпчику завжди мали один і той же формат (вигляд).

 

Введення даних у список

 

Введення даних у список можна здійснити з використанням різних способів та прийомів:

* Вручну, використовуючи стандартні прийоми.

* Імпортувати або скопіювати дані з іншого файлу.

* Використати діалогове вікно.

 

При ручному введені можна використовувати такі засоби Excel:

® Автозаповнення (Auto Complete). При введенні даних у стовпчик, якщо Excel знаходить закономірність, то заповнює залишок комірок у стовпчику автоматично після натисканні клавіші < Enter >. Цей засіб можна включати / виключати, використовуючи відповідні опції вкладки “Правка” (Edit) діалогового вікна “Параметри” (Options).

® Вибір із списку (Pick Lists). Якщо при заповненні комірки клацнути правою кнопкою мишки, з’явиться контекстне меню з командою “Вибрати із списку” (Pick from list). За цією командою в списку, що з’являється, із всіма вже введеними в стовпчик елементами можна обрати потрібний — він з’явиться у комірці.

 

Для введення даних через діалогове вікно треба помістити табличний курсор у будь-яке місце заголовка списку і залучити команду “Дані ð Форма” (Data ð Form). Excel визначить розміри списку, створить і виведе діалогове вікно із всіма полями списку (обмеження — 32 поля), крім тих полів заголовків списку, що містять формули. Для пересування між полями списку використовуються клавіші < Tab > або < Shift+Tab >. Для пересування між записами списку використовується смуга прокрутки або певні кнопки вікна. Призначення кнопок вікна:

® “Додати” (New) — для введення нового запису, який додається в кінець списку;

® “Вилучити” (Delete) — для вилучення поточного запису;

® “Повернути” (Restore) — для відміни всіх внесених в поточний запис змін. Діє доки не натиснута кнопка “Додати”;

® “Назад” (Find Prev) — для здійснення переходу до попереднього запису списку. Якщо встановлено критерій відбору, то відбудеться перехід до попереднього запису, що задовольняє критерію;

® “Далі“ (Find Next) — для здійснення переходу до наступного запису списку. Якщо встановлено критерій відбору, то відбудеться перехід до наступного запису, що задовольняє критерію;

® “Критерії“ (Criteria) — для очищення полів в діалоговому вікні і введення критеріїв відбору записів. Після набору критеріїв використовуються кнопки “Далі“ і “Назад”, щоб знайти відповідний запис;

® “Закрити” (Close) — для запису введених даних в робочий листок і закриття діалогового вікна.

Якщо на комп’ютері встановлена програма Microsoft Access і завантажена надбудова Access Links, можна використовувати її форми для обробки записів робочого листка Excel. При вказаних умовах в меню команд буде нова команда “Дані ð Форма MS Access” (Data ð Access Form), залучивши яку, можна створити потрібні форми введення даних.

 

Доступ до файлів зовнішніх баз даних з Excel може бути корисним в таких випадках:

· база даних, з якою необхідно працювати, дуже велика;

· база даних використовується кількома користувачами одночасно;

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

· база даних має формат, який не підтримується Excel.

В більшості випадків використовуються не всі записи або поля бази даних, а тільки в ті, які задовольняють певному критерію. Щоб імпортувати саме ці дані, треба створити запит (query) до бази даних і, відповідно запиту, завантажити в робочий листок тільки частину зовнішньої бази даних.

Щоб здійснити запити до зовнішніх баз даних, необхідно мати на комп’ютері додаток Microsoft Query. Якщо додаток встановлений, то буде виконуватись команда “Дані ð Зовнішні дані ð Створити запит” (Data ð Get External Data ð Creat New Query). Загальний порядок дій для створення запиту такий:

Активізувати робочий листок.

Залучити команду “Дані ð Зовнішні дані ð Створити запит”. В результаті буде запущена програма MS Query.

Вказати, буде використовуватись MS Query безпосередньо чи засіб “Майстер Запитів” (Query Wizard) (для Excel 97).

Визначити зовнішню базу даних, яку необхідно використовувати і створити запит, що вміщує перелік критеріїв для визначення необхідних записів.

Вказати, в якому вигляді будуть передані дані — або просто скопійовані в робочий листок, або у вигляді зведеної таблиці.

Для багаторазового використання запиту його можна зберегти у файлі.

 

Фільтрація списку

 

Фільтрація списку — це сховування всіх рядків, крім тих, які задовольняють певним критеріям. В Excel для фільтрації списку існує два способи:

Автофільтр (AutoFilter) застосовується для фільтрації за простими критеріями.

Розширений фільтр (Advanced Filter) використовується для фільтрації за більш складними критеріями.

 

Щоб застосувати автофільтр, необхідно встановити табличний курсор на одну з комірок списку і залучити команду “Дані ð Фільтр ð Автофільтр” (Data ð Filter ð AutoFilter). Excel проаналізує список і додасть в рядок заголовків полів кнопки списків, що розкриваються. При клацанні на одній з таких кнопок розкриється список з елементами обраного стовпчика, які зустрічаються у ньому тільки один раз. Далі, якщо вибрати елементи в списку, що розкрився, то Excel сховає всі рядки у вихідному списку, крім тих, які включають відмічені елементи. Отже, критеріями відбору будуть вибрані елементи. Коли список буде відфільтрований, в рядку стану з’явиться повідомлення про те, скільки рядків відібрано. Крім того, зміниться колір кнопки використаного списку, що розкривається. Обмеження: в списку, що розкривається, з’являються тільки перші 999 унікальні елементи для Excel 97 і перші 250 унікальних елементів для попередніх версій Excel.

Крім унікальних елементів стовпчика, список, що розкривається, містить ще п’ять пунктів:

® Все (All) — відображує всі елементи стовпчика. Застосовується для відміни фільтрації стовпчика.

® Перші 10 (Top 10) — відбирає 10 або іншу кількість найменших або найбільших елементів списку.

® Умова (Custom) — дозволяє фільтрувати список за умовою, яка формулюється через логічний вираз у вікні діалогу “Автофільтр користувача”.

® Порожні (Blanks) — фільтрує список, відображуючи тільки рядки з порожніми комірками в даному стовпчику.

® Непорожні (NonBlanks) — фільтрує список, відображуючи тільки рядки з непорожніми комірками в даному стовпчику.

Для відміни режиму “Автофільтр” і вилучення кнопок розкриття списків в рядку заголовків полів треба повторно залучити команду “Дані ð Фільтр ð Автофільтр”. В результаті буде знятий прапорець у пункті меню “Автофільтр” і список повернеться у вихідний стан.

Фільтрація не діє на значення комірок, в яких є формули.

Режим “Автофільтр” може використовуватись для фільтрації за значеннями в кількох стовпчиках. Для цього послідовно виконується фільтрація по кожному стовпчику окремо.

 

Застосування розширеної фільтрації. Розширений фільтр більш гнучкий, ніж автофільтр, однак потребує більше підготовчих дій. Розширений фільтр дозволяє:

· Визначити більш складний критерій фільтрації.

· Встановити вираховуваний критерій фільтрації.

· Переміщувати копії рядків, що відповідають певному критерію, в інше місце.

Перед використанням засобу розширеної фільтрації необхідно задати діапазон критеріїв. Діапазон критеріїв (criteria range) — це спеціально відведена область робочого листка, що відповідає певним умовам, а саме:

* перший рядок — обов’язково рядок заголовку, який містить назви полів списку, що повинні залишитись після фільтрації, в тому числі і назви вираховуваних полів;

* інші рядки повинні містити критерії фільтрації.

Розміщувати діапазон критеріїв можна в будь-якому місці робочого листка, відокремлюючи від вихідного списку хоча б одним рядком або стовпчиком.

Поля в кожному рядку діапазону критеріїв (без першого рядка) з’єднані оператором “і“ (AND). Критерії в кількох рядках з’єднані оператором “або” (OR).

Щоб виконати фільтрацію, треба вказати на будь-яку комірку списку і залучити команду “Дані Þ Фільтр Þ Розширений фільтр” (Data Þ Filter Þ Advanced). В діалоговому вікні, що з’являється, слід визначити діапазон списку для фільтрації, діапазон залучених критеріїв, місце розташування результатів і клацнути на кнопці “ОК”. Список буде відфільтрований за заданими критеріями.

Критерії можна розділити на типи:

* Текстові або числові критерії. При фільтрації використовується порівняння з числом або рядком тексту через оператори порівняння: “=” (дорівнює — equal to), “>” (більше — greater than), “>=” (більше чи дорівнює — greater than or equal to), “<” (менше — less than), “<=” (менше чи дорівнює — less than or equal to), “<>” (не дорівнює — not equal to). Приклади:

 

Критерій	Дія	Критерій	Дія
>К	Слова, що починаються з букв відЛ до Я	См*	Слова, що починаються з букв “См”
<>С	Всі слова, крім тих, що починаються з букви С	с*с	Слова, що почи-наються з букви “с”і закінчуються буквою “с”
=“Січень”	Всі слова “Січень”	с?с	Слова з трьох букв, перша і остання —“с”

 

В текстових порівняннях не розрізнюються великі і прописні букви.

* Критерії, що обчисляються. При фільтрації може використовуватися значення порівняння, що вираховується за формулою. Формули розміщуються у комірках діапазону критеріїв. Приклад: у комірці А2 діапазону критеріїв міститься формула =С5 - В5 + 1 >= 30. Ця логічна формула посилається на комірки, що розташовані в першому рядку вихідного списку після рядка заголовків (посилання відносне) і дозволяє відобразити тільки ті рядки списку, через які формула повертає значення “істина”.

Рекомендації що-до використовування критеріїв, що обчисляються:

® При створенні формул критеріїв, що обчисляються, використовувати перший рядок даних списку і відносні посилання.

® Можна використовувати будь-яку кількість критеріїв, що обчисляються, а також сполучення критеріїв, що обчисляються, з невираховуваними.

® Не звертати увагу на значення, що повертають формули в діапазоні критеріїв.

® Якщо формула, що вираховується, посилається на значення поза списком, використовуйте абсолютні посилання.

 

Інші можливості розширеної фільтрації. В діалоговому вікні “Розширений фільтр” є ще дві опції:

· Опція “Скопіювати результати в інше місце” (Copy to Another Location) дозволяє відібрані через фільтрацію рядки скопіювати в будь-яке місце робочого листка, яке визначається у вікні “Помістити результат в діапазон” (Copy to).

· Опція “Тільки унікальні записи” (Unique Record Only) дозволяє сховати всі однакові рядки крім одного (унікального), які задовольняють певному критерію. Якщо критерії не встановлені, то будуть виведені тількиунікальні записи.

Аналіз даних через зведені таблиці

 

Основні елементи зведених таблиць

 

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

Зведена таблиця (pivot table) — це динамічний підсумок та систематизація даних, що містяться в списку або базі даних. Структура та критерії систематизації визначаються користувачем.

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

При створені зведеної таблиці звичайно необхідно підвести підсумок або кількість по одному чи кільком полях даних. Заголовки полів (заголовки стовпчиків) таблиці з’являться в зведеній таблиці у вигляді заголовків рядків, стовпчиків або сторінок в залежності від мети аналізу та бажання користувача (рис. 3).

 

Рис. 3. Основні елементи зведеної таблиці.

 

Термін “зведена таблиця” (pivot table) ввели співробітники фірми Microsoft. Інші терміни, які необхідно знати:

· Поле сторінки (page field) — це поле вихідного списку або таблиці, вміщене в область сторінкової орієнтації зведеної таблиці. На рис. 3 «Регион» є полем сторінки, через яке можна вибирати певний регіон для підсумовування даних по ньому. При виборі в спадаючому списку іншого значення поля сторінки відбувається перерахунок зведеної таблиці для відображення відповідних підсумків, пов’язаних саме з цим значенням.

· Поле даних (data field) — це поле зведеної таблиці, в якому відображується спосіб підсумовування даних (наприклад, сума, кількість, максимальне, середнє та інші характеристики даних вихідного списку). На рис. 3 поле «Сумма заказа» є полем даних, в якому вказується з яких саме підсумків складається зведена таблиця. За умовчанням в зведеній таблиці виконується підведення підсумків текстових даних за допомогою підсумкової функції «К-ть значень», а числових даних — за допомогою підсумкової функції «Сума».

· Поле рядка (row field) — це поле зведеної таблиці, якому відповідає сукупність окремих рядків — елементів поля. Структура поля рядків може бути вкладеною. На рис. 3 поля «Продукти» і «Заказчик» є полями рядка вкладеної структури. Внутрішнє поле рядка «Заказчик» в точності відповідає області даних. Зовнішнє поле рядка «Продукти» групує внутрішні поля.

· Поле стовпчика (Column Field) — це поле зведеної таблиці, якому відповідає сукупність окремих стовпчиків — елементів поля. Поле стовпчика також може мати вкладену структура.

· Елемент (Item) — це заголовок окремого рядка або стовпчика зведеної таблиці, який належить полю.

· Область даних (Data Area) — це комірки зведеної таблиці, в яких містяться підсумки. В Excel передбачені кілька способів підведення підсумку даних (сумування, визначення середнього значення, підрахунок кількості та інш.)

· Загальні підсумки (Grand Totals) — це рядок або стовпчик, в якому показані загальні підсумки для всіх комірок рядка або стовпчика зведеної таблиці. Завжди можна задати спосіб визначення загальних підсумків — для рядків, для стовпчиків, для рядків та стовпчиків, взагалі не підбивати підсумки.

· Група (Group) — це набір елементів, які оброблюються як один елемент. Елементи можна об’єднати в групу вручну або автоматично.

· Вихідні дані (Source Data) — це дані, які використовуються для створення зведеної таблиці. Вони можуть міститись в робочому листку або в зовнішній базі даних.

· Проміжні підсумки (Subtotals) — це рядок або стовпчик, в якому відображуються проміжні підсумки для поточних комірок рядка або стовпчика зведеної таблиці.

· Поновити (Refresh) — заново перерахувати зведену таблицю після внесення змін у вихідні дані.