В електронних таблицях доступні такі засоби обробки даних, як сортування, фільтрація та формування підсумків. Використання цих коштів вимагає подання електронної таблиці у вигляді бази даних або списку. У цьому розділі показано, як представити електронну таблицю у вигляді бази даних і застосувати до неї апарат сортування, фільтрації та формування підсумкових даних, включаючи засоби зведених таблиць.
Список - це один із способів організації даних на робочому аркуші. Список створюється як позначений ряд, що складається з рядків з однотипними даними. Дані, організовані в список, в термінології Excel називаються базою даних (БД). При цьому рядки таблиці - це записи бази даних, а стовпці - поля записів БД. Щоб перетворити таблицю Excel в список або базу даних, необхідно присвоїти стовпцями однорядкові імена, які будуть використовуватися як імена полів записів БД. Слід мати на увазі, що однорядкові імена стовпців можуть складатися з декількох рядків заголовків, розміщених в одному рядку таблиці Excel.
При створенні списку або бази даних на робочому аркуші Excel необхідно виконувати наступні правила:
1. На одному робочому аркуші не слід поміщати більш одного списку, оскільки деякі операції, наприклад Фільтрація, працюють в певний момент тільки з одним списком.
2. Слід відокремлювати список від інших даних робочого листа хоча б одним незаповненим стовпцем або однієї незаповненим рядком. Це допоможе Excel автоматично виділити список при виконанні фільтрації або при сортуванні даних.
3. Список може займати весь робочий лист.
4. Імена стовпців повинні розташовуватися в першому рядку списку. Excel використовує ці імена при створенні звітів, в пошуку і сортування даних.
5. Для імен стовпців слід використовувати шрифт, тип даних, вирівнювання, формат, рамку або стиль прописних літер, • відмінних від тих, які використовувалися для даних списку.
6. Щоб відокремити імена стовпців від даних, слід розмістити рамку по нижньому краю осередків рядка з іменами стовпців. Не рекомендується використовувати порожні рядки або пунктирні лінії.
Щоб впорядкувати весь список, досить виділити одну комірку і вибрати в меню Дані команду Сортування. Excel автоматично виділить весь список. Якщо в першому рядку списку БД знаходяться імена стовпців, то вони не будуть включені в сортування. Необхідно мати на увазі, що в цьому випадку підсумковий рядок вихідного списку також буде включена в сортування, тому доцільніше самостійно виділяти область вихідного списку для сортування.
Команда Сортування здійснюється через діалогове вікно. У трьох вікнах введення вікна Сортування можна задати ключі, за якими вона буде виконана.
У діалоговому вікні Сортування є режим Параметри, який дозволяє встановити порядок сортування по першому ключу - звичайний або визначений користувачем, задати облік кодування малих і великих літер (облік регістра символів), а також напрямок сортування - за зростанням або за спаданням.
Для швидкого сортування на панелі інструментів Стандартна знаходяться дві кнопки:
Фільтрація даних у списку. За допомогою фільтрів можна виводити і переглядати тільки ті дані, які відповідають певним умовам.
Excel дозволяє швидко і зручно переглядати необхідні дані зі списку БД за допомогою простого засобу - автофильтра. Більш складні запити до бази даних можна реалізувати за допомогою команди Розширений фільтр.
Щоб використовувати автофильтр, треба спочатку виділити область БД для пошуку, з заголовками стовпців. При цьому імена стовпців списку Excel перетворює в імена полів записів БД.
Потім виконати команду Автофільтр в меню Дані.
За командою Автофільтр Excel поміщає розкриваються стрілки безпосередньо на імена стовпців списку. Клацнувши по стрілці, можна вивести на екран список всіх унікальних елементів відповідного стовпця. Якщо виділити певний елемент стовпця, то будуть приховані всі рядки, крім тих, які містять виділене значення.
Елемент стовпчика, який виділений в спадному вікні списку, називається критерієм фільтра. Можна продовжити фільтрацію списку за допомогою критерію з іншого шпальти. Щоб видалити критерії фільтра для окремого стовпця, треба вибрати параметр Усі в списку стовпця. Щоб показати всі приховані в списку рядки, треба вибрати в меню Дані команду Фільтр, а потім - команду Показати все.
За допомогою автофільтра можна для кожного стовпчика задати для користувача критерії відбору записів бази даних, наприклад, вивести на екран тільки ті записи, значення полів яких знаходяться в межах заданого інтервалу. Щоб задати користувальницький критерій, треба в списку стовпця вибрати параметр Налаштування. потім в діалоговому вікні користувача автофильтр ввести потрібні критерії.
Можлива фільтрація списків в Excel за складними критеріями з використанням команди Розширений фільтр.
Для фільтрації списку або бази даних по складному критерію, який буде визначений нижче, а також для отримання частини вихідного списку з кількох заданих стовпцях в Excel ис користується команда Розширений фільтр меню Дані. Відмінність цієї команди від команди Автофільтр полягає в тому, що крім перерахованих вище можливостей відфільтровані записи можна винести в інше місце робочого листа Excel, не зіпсувавши вихідний список.
Щоб використовувати команду Розширений фільтр, треба спочатку створити таблицю критеріїв, яку слід розмістити на; тому ж робочому аркуші, що і вихідний список, але так, щоб не приховувати його під час фільтрації.
Для формування таблиці критеріїв необхідно скопіювати імена полів записів БД (імена стовпців списку) в ту частину робочого листа, де буде розташовуватися таблиця критеріїв.
При цьому число рядків в цій таблиці визначається тільки числом критеріїв пошуку. Однак включення порожніх рядків в таблицю критеріїв неприпустимо, оскільки в цьому випадку будуть знайдені всі записи бази даних. Завдання критеріїв пошуку у вигляді констант вимагає точної копії імен тих стовпців вихідного списку, які задають умови фільтрації. Крім таблиці критеріїв для команди Розширений фільтр треба визначити вид вихідного документа. Це означає, що слід скопіювати в вільне місце робочого листа імена тільки тих стовпців списку, ко-1 торие визначають вид вихідного документа. Кількість рядків у вихідному документі Excel визначить сам.
Таким чином, для виконання команди Розширений фільтр треба виконати три дії:
сформувати у вільному місці робочого листа таблицю критеріїв;
сформувати шапку вихідного документа;
виділити область пошуку в вихідному списку.
Використання обчислювального критерію. Введення обчислюваних критеріїв пошуку в таблицю вимагає виконання наступних правил.
1. Формула повинна виводити логічне значення істина або
брехня. Після виконання пошуку на екран виводяться тільки ті буді
ки, для яких результатом обчислення формули буде істина.
2. Формула повинна посилатися хоча б на один стовпець в списку.
Використання обчислюваного критерію накладає істотне обмеження на таблицю критеріїв. У цьому випадку ім'я стовпця в таблиці критеріїв, що містить значення обчислюваного критерію, має відрізнятися від імені подібного стовпця в початковому списку.
Формування підсумків в електронній таблиці здійснюється командою Підсумки з меню Дані. Щоб команда Підсумки працювала, список повинен бути спочатку відсортований. Після сортування можна підрахувати підсумки за групами записів. Команда Підсумки дозволяє підводити підсумки більш ніж по одному полю з групи записів, а також додавати оброблювані поля і застосовувати для обчислення підсумків інші функції.
Зведена таблиця - це ще один інструмент обробки великих списків з даними. Оскільки в цьому випадку відразу підводяться підсумки, виконуються сортування і фільтрація списків, то зведена таблиця є більш потужним інструментом обробки даних, який називається «Майстер зведених таблиць». Для створення зведеної таблиці необхідно виконати наступні кроки:
виділити область списку;
в меню Дані вибрати команду Зведена таблиця, по якій на екран виводиться вікно Майстра зведених таблиць;
клацнувши по кнопці Далі, перейти до наступного кроку.
За допомогою миші можна перетягнути поля списку з правої частини вікна Майстра зведених таблиць в області рядків, стовпців і Даних в тому ж вікні. Таким чином формується вид вихідного документа.
Останній крок побудови зведеної таблиці визначає, де буде розміщена зведена таблиця. Рекомендується розміщувати її на окремому робочому аркуші. При необхідності можна зведену таблицю озаглавити.
Для друку електронної таблиці її слід попередньо підготувати. Для цього необхідно:
розбити ЕТ на сторінки;
встановити параметри сторінки;
створити необхідні колонтитули;
створити заголовки для друку, щоб шапка таблиці виводь
лась на кожному аркуші;
Щоб розбити ЕТ на сторінки, необхідно вставити горизонтальні роздільники кінця сторінки в необхідних місцях ЕТ. Для цього треба виконати наступне: виділити рядок, з якої треба почати нову сторінку; в меню Вставка виконати команду Розрив сторінки;
В результаті Excel вставить широку пунктирну лінію (кінець сторінки) зверху від виділеної рядки.
Аналогічно можна розбити на сторінки решту ЕТ.
Для установки параметрів сторінки в меню Файл слід вибрати команду Параметри сторінки, визначивши розміри полів, наявність і розташування колонтитулів, а також наявність заголовків таблиці і інші параметри.
Запитання і завдання
1. Що таке електронна таблиця і які її основні функції?
2. Вкажіть типи даних, що використовуються в електронних таблицях.
3. Який клас завдань вирішується за допомогою електронних таблиць?
5. Що таке робочий лист і робоча книга?
6. Які можливості надає Excel при роботі з діаграмами?
7. Якими засобами володіє Excel для сортування та вибірки даних?
8. Які обмеження має сортування в Excel?
9. Що таке фільтрація даних?
10.В чому відмінність автофильтра від розширеного фільтра?
11.Как автоматично підсумувати дані в рядках і стовпцях?
12.Что таке майстер формул?
13.Чем відрізняється розширений фільтр від автофильтра?
14.Что таке зведена таблиця і як її створити?
15.Что таке зв'язування об'єктів в Excel? Для чого використовуються зв'язку?
16.Як встановити зв'язок між даними різних робочих листів в
однієї робочої книзі?
17.Як встановити зв'язок між даними з різних робочих книг?
18.Какие проблему дозволяє вирішити встановлення зв'язків між різними таблицями в Excel?
19.Можно чи таблицю, створену в Excel, вставити в документ Word?
1. Технологія обробки електронних таблиць.
2. Організація обчислень засобами електронних таблиць.
3. Засоби ділової графіки електронних таблиць.
4. Інструментальні засоби електронних таблиць.
5. Обробка даних в Excel.
6. Підготовка та друк ЕТ.