Теоретичний матеріал за
темами:
1.
Відносні й абсолютні посилання
2.
Змішані посилання. Форматування даних
3.
Дані у форматі дати і часу. Використання функцій у формулах
4.
Фільтрація даних
5.
Сортування даних. Підведення підсумків. Зведені таблиці
6.
Консолідація даних
7. Використання майстра шаблонів
8. Підстановка і добір значень. Захист, перевірка й приховування інформації
9. Побудова діаграм
Лабораторні роботи:
1. Відносні й абсолютні посилання
2.
Змішані посилання. Форматування даних
3.
Дані у форматі дати і часу. Використання функцій у формулах
4.
Фільтрація даних
5.
Сортування даних. Підведення підсумків. Зведені таблиці
6.
Консолідація даних
7. Використання майстра шаблонів
8. Підстановка і добір значень. Захист, перевірка й приховування інформації
9. Побудова діаграм
return_links();
?>
|
Короткі теоретичні відомості
Сортуванням називають перевпорядковування даних, при якому значення обраних вами полів (їх називають ключовими полями або ключами) розташовуються:
- числа – в порядку зростання або зменшення їх величин,
- тексти – в алфавітному порядку (прямому чи зворотному),
- дати і час - у хронологічному порядку (прямому або зворотному).
Перед сортуванням даних важливо правильно виділити область, у якій відбуватиметься сортування. Якщо перед вибором меню Данные / Сортировка помістити курсор усередину таблиці, то Excel автоматично виділить усі її рядки і стовпці. Якщо ж виділити тільки частину таблиці, то і сортування відбудеться тільки у межах виділеної області. Наприклад, якщо ви виділите тільки стовпець із прізвищами співробітників, але не виділите стовпець із сумами належних їм виплат, то прізвища будуть перевпорядковані, але суми виплат залишаться нерухомими в колишніх комірках. Так можна зіпсувати дані, на введення яких був витрачений значний час. Виправити помилку можна, якщо відразу після неправильного сортування натиснути кнопку скасування, або закрити файл, не зберігаючи на диску внесені зміни.
Якщо сортування відбувається за одним ключем, то достатньо просто встановити курсор у ключове поле і скористатися однією з кнопок  . При цьому Excel сам виділить всю таблицю і виконає сортування за обраним ключем. Щоб Excel зміг правлильно визначити межі таблиці, у ній не повинно бути пустих рядків чи стовпців.
Для сортування за декількома полями слід помістити курсор усередину таблиці або виділити діапазон комірок, що підлягає сортуванню, та вибрати в меню Данные опцію Сортировка. В результаті не екрані з'явиться вікно, показане на рис. 8.
Якщо ви бажаєте сортувати стовпці (а не рядки, як це робиться за замовчуванням), то сповістіть про це програмі, клацнувши у вікні, що з'явилося, на кнопці Параметры. Там же можна задати режим, що забезпечує сортування назв місяців, днів тижня тощо не в алфавітному, а в хронологічному порядку.
Звичайно Excel вважає перший рядок шапкою таблиці, що не бере участі в сортуванні. Якщо ж таблиця не має шапки, і її перший рядок підлягає сортуванню нарівні з іншими, то у вікні сортування слід вибрати: Идентифицировать поля по позначенням столбцов листа.
У графі Сортировать по виберіть заголовок чи координату стовпця, по якому буде виконуватися сортування, а також напрямок сортування: по возрастанию або по убыванию.
Записи, що містять однакові значення ключового поля, у свою чергу, можуть бути відсортовані по другому ключовому полю, якщо ви заповните графу Затем по. Наприклад, при сортуванні персоналу записи з однаковими прізвищами сортуються по іменах. Аналогічно, записи, що містять однакові значення в двох перших ключових полях, можна піддати сортуванню, указавши третє ключове поле в графі В последнюю очередь по. Наприклад, якщо збігаються і прізвище, і ім'я, то сортування здійснюється за полем по батькові.
Для виконання сортування з обраними параметрами, клацніть мишкою на кнопці ОК.
Підведення підсумків можна виконувати тільки після виконання відповідного сортування даних. Якщо, наприклад, вам потрібно підвести підсумки по датах із проміжними підсумками по товарах, то і сортування повинне виконуватися в першу чергу по датах, у другу - по товарах.
Сортування необхідне тому, що рядки з підсумками додаваються там, де відбувається зміна значення даних. Наприклад, підсумок по даті додавається там, де відбувається зміна дати. Отже значення, по яких підводиться підсумок, повинні групуватися разом, а не бути розкиданими по таблиці.
Закінчивши сортування, і обравши в меню Даные пункт Итоги, на екран виводиться вікно (рис. 9), у якому тртеба відповісти на наступні питання:
- Де треба вставляти рядки з підсумками? Якщо, наприклад, ми виберемо тут Дата або Товар, то рядок підсумків буде з'являтися там, де змінюється значення дати чи найменування товару відповідно.
- Як треба підбивати підсумок? Частіше усього підсумок - це просто сума значень у стовпці. Але замість суми можна вибрати, наприклад, середнє арифметичне, максимальне або мінімальне значення тощо.
- У яких стовпцях треба підбивати підсумок? Тут слід зазначити найменування стовпців, у яких виконується обчислення суми, середнього і т.п.
- Чи потрібно вилучати всі старі підсумки перед виводом нових? Якщо ви відмовитеся від вилучення, то нові підсумки будуть додаватися до раніше сформованого. Це дозволяє виводити декілька різних підсумків одночасно, наприклад, до раніше обчислених сум додати ще і середні значення тощо.
- Чи потрібно після виводу кожного рядка з підсумками переходити на нову сторінку?
- Де потрібно розміщати рядки з підсумками?(під відповідними групами рядків даних або над ними).
Вказавши потрібні параметри, клацніть мишкою на кнопці ОК, і у вашій таблиці додадуться рядки з підсумками.
Зліва від таблиці з підсумками відображаються символи структури - кнопки з зображеннями знаків плюс і мінус. Користуючись ними, можна сховати деталі даних, залишивши тільки їхні підсумки, або знову відновити відображення деталей.
Якщо таблиця містить проміжні підсумки різних рівнів деталізації, то зручно користуватися також розташованими в лівому верхньому куті кнопками з зображеннями цифр. Кнопка з цифрою 1 забезпечує виведення тільки загального підсумку, приховуючи всі дані таблиці і всі проміжні підсумки. Кнопка 2 додає проміжні підсумки наступного рівня деталізації і т.д. Кнопка з найбільшою цифрою забезпечує виведення усіх наявних у таблиці вхідних даних з усіма проміжними підсумками.
Зведені таблиці є ще одним засобом підведення підсумків. Щоб побудувати зведену таблицю, помістіть курсор усередині бази даних , виберіть у меню Даныеопцію Сводные таблицы і дайте відповіді на питання програми:
- Звідки взяти вхідні дані для побудови зведеної таблиці?Тут виберіть опцію В списке или базе данных Microsoft Excel.
- Який діапазон комірок містить вхідні дані для побудови зведеної таблиці? Тут ви або погоджуєтеся з діапазоном, запропонованим програмою, або уточнюєте його.
- Якою повинна бути структура зведеної таблиці? Тут треба за допомогою мишки перетягнути кнопки з зображенням заголовків стовпців вхідної бази даних на відповідні позиції зведеної таблиці. Наприклад, для виконання завдання 6, кнопку Товартреба перемістити в позицію Строка, кнопку Продавец- у позицію Столбец, кнопку Стоимость- у позицію Даные, а кнопкуДата- у позицію Страница (див. рис. 10). Звичайно в позиції Даныекнопка, що відповідає числовим даним вхідної таблиці, набуває вигляду Сумма по полю... Але крім суми, зведена таблиця дозволяє обчислювати і багато інших підсумків: середнє, мінімум, максимум тощо. Щоб змінити спосіб обчислення підсумків, треба двічі клацнути на кнопці в позиції Данные і вибрати потрібний спосіб підведення підсумку.
- Де треба розмісити побудовану зведену таблицю? Тут є можливість вибору між новим робочим аркушем і аркушем, що містить вхідну базу даних.
Вже після побудови таблиці, її структура, формат даних та інші параметри можуть бути скориговані за допомогою кнопок на панелі інструментів Сводные таблицы.
Самостійно вивчіть тему:
Створення і використання макросів.
Контрольні питання
- Як здійснюється сортування даних у Excel?
- Які параметри можна задати перед початком сортування?
- Як повинні бути відсортовані дані перед підведенням підсумків і чому?
- Як здійснюється підведення підсумків?
- Що таке символи структури і як ними користуватися?
- Що таке макроси? Як записати і виконати макрос?
- Що в Excel називають базою даних?
- Як здійснюється побудова зведеної таблиці?
Як змінити структуру зведеної таблиці після її створення?
Базою даних у Excel називають таблицю регулярної структури, в якій перший рядок містить заголовки полів (стовпців), а решта рядків заповнені однотипними даними. В таблиці не повинно бути пустих стовпців і об’єднаних комірок.
|
