Робота з табличним процесором Microsoft EXCEL.

Обчислення підсумків. Зведена таблиця. Графічне розв’язування задач прогнозування

 

Мета роботи: Ознайомитись з можливостямистворення і використання проміжних підсумків та зведених таблицьвExcel.

 

І. Теоретична частина (виконується письмово).

 

1. Призначення проміжних підсумків.

2. Послідовність створення проміжних підсумків.

3. Призначення зведених таблиць.

4. Підключення майстра зведених таблиць.

5. Особливості форматування зведеної таблиці.

 

ІІ. Практична частина.

Зміст роботи

1. Завантажити Excel і відкрити файл із створеною таблицею (практична робота №3).

2. Для даних таблиці створити проміжні підсумки.

3. На окремих листах робочої книги створити зведені таблиці за завданням викладача.

4. Побудувати лінії тренда за всіма назвами міст.

5. Створити на новому листі консолідацію даних.

6. Показати викладачу результати роботи, створивши файл практичної роботи №4.

1. Обчислення проміжних підсумків

 

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

1.1. Скопіюйте список (таблицю з роботи №3) на новий лист з ім’ям Підсумки.

1.2. Відсортуйте дані в списку по полю Назва міста. Активізуйте одну з комірок списку.

1.3. Виконайте команду Данные / Итоги. На екрані з'явиться діалогове вікно

 

 

1.4. В полі списку При каждом изменении в виберіть заголовок стовпця Назва міста, для якого необхідно обчислити проміжні підсумки після кожної зміни назви.

1.5. В полі списку Операция установіть функцію Сумма, для обчислення підсумкової суми.

1.6. В області Добавить итоги по активізуйте опцію Вартість загальна.

1.7. Клацніть на кнопці ОК.

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

Зверніть увагу, що нижче Поля имени, де відображається адреса виділеної комірки, з'являться числа 1,2,3. Вони відповідають різним рівням структуризації. Рівень 1 відповідає загальному підсумку по всім рядкам. Рівень 2 відповідає підсумкам по кожній окремій назві міста. Рівень 3 відповідає всьому списку.

1.8. Клацніть по цифрі 2. Excel сховає всі рядки з даними, залишивши тільки підсумки по кожному з найменувань, а також загальний підсумок.

1.9. Клацніть на знак плюс ліворуч від рядка з найменуванням маршруту, наприклад, Батурин. Excel покаже всі дані тільки по даному маршруту.

1.10. Клацніть на знак мінус, щоб сховати види маршрутів.

1.11. Клацніть на цифрі 3, що означає самий нижній рівень структуризації таблиці. Excel відобразить всі рядки.

1.12. Для видалення рядків з підсумковими значеннями клацніть на кнопці Убрать все, розташованій в діалоговому вікні Промежуточные итоги.

 

Зведена таблиця

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

2.1. Побудова зведеної таблиці

2.1.1 Виділіть будь-яку комірку списку.

2.1.2 Виконайте команду Данные / Сводная таблиця. На екрані з'явиться вікно першого кроку Мастера сводных таблиц. (Для Excel 2007: Вставка/Сводная таблиця).

2.1.3 В області Создать таблицу на основе данных, находящихся: вибрати перемикач в списке или базе данных Microsoft Excel (він за звичай активізований по умовчанню).

2.1.4. Натисніть кнопку Далее. На екрані з'явиться діалогове вікно другого кроку Мастера сводных таблиц.

2.1.5. В поле Диапазон: введіть діапазон комірок, дані з якого будуть включені в зведену таблицю (у нашому випадку – це весь список).

2.1.6. Натисніть кнопку Далее.

На екрані з'явиться діалогове вікно третього кроку Мастера сводных таблиц. Тут потрібно побудувати структуру зведеної таблиці із заголовка списку. Excel автоматично виводить у правій частині діалогового вікна поля з іменами стовпців списку або використати кнопку Макет…

2.1.7. Перетягніть мишкою поле Назва міста в область Строка.

2.1.8. Перетягніть мишкою поле Місяць продажу - в область Столбец.

2.1.9. Перетягніть мишкою поле Вартість загальна в область Данные. В області Данные з'явиться поле з назвою Сумма по полю Вартість загальна.

2.1.10. Двічі клацніть на поле Сумма по полю Вартість загальна. На екрані з'явиться діалогове вікно Вычисление поля Сводной таблицы.

2.1.11. В поле Имя введіть будь-яке нове ім’я для поля, наприклад, Сума по назві міста.

2.1.12. Натисніть кнопку ОК.

2.1.13. Натисніть кнопку Далее. На екрані з'явиться діалогове вікно четвертого кроку Мастера сводных таблиц.

2.1.14. В поле Поместить таблицю в установити прапорець новый лист.

2.1.15. Натиснути кнопку Параметры… На екрані з'явиться діалогове вікно Параметры сводной таблицы.

2.1.16. У поле Имя введіть назву таблиці - Зведена таблиця.

2.1.17. Установіть прапорець для пустых ячеек отображать, а у текстове поле праворуч введіть цифру 0.

2.1.18. Установіть прапорець общая сумма по столбцам, якщо він не встановлений.

2.1.20. Скиньте прапорець общая сумма по строкам.

2.1.21. Натиснути кнопку ОК.

2.1.22. Натиснути кнопку Готово. На екрані з'явиться таблиця, яку необхідно відредагувати.

 

2.2. Редагування зведеної таблиці

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

2.2.1. Відкрийте панель Сводная таблица, використовуючи команду Вид / Панели инструментов / Сводные таблицы (ця панель використовується при редагуванні даних).

2.2.2. Викличте Мастер сводных таблиц клацанням миші на його піктограмі на панелі інструментів Сводные таблицы.

2.2.3. Перетягніть поле Кількість путівок в область Данные в крайню верхню позицію та за допомогою діалогового вікна Вычисление поля Сводной таблицы виберіть операцію – Среднее.

2.2.4. Натисніть кнопку Готово. На екрані з'явиться зведена таблиця, що має іншу структуру.

Проаналізуйте отриманий результат.

2.2.5. Перетягніть поле Місяць продажу в зведеній таблиці в область Строка і опустіть відразу ж під полем Назва міста. Структура зведеної таблиці зміниться.

Проаналізуйте отриманий результат.

2.2.6. Відформатуйте зведену таблицю.

При форматуванні зведеної таблиці рекомендується застосовувати Автоформат.

2.2.7. Установіть курсор на будь-яку комірку таблиці.

2.2.8. Виконайте команди Формат/Автоформат.

2.2.9. У Списке форматов діалогового вікна Автоформат виберіть классический 2.

2.2.10. Клацніть на кнопці ОК.

3. Графічний розв’язок задач прогнозування

Побудова зведеної таблиці дає можливість виконати не тільки аналіз економічних показників, але й спрогнозувати їхній розвиток на найближчий період. Розв’язок такої задачі можливий графічним методом за допомогою Мастера диаграмм. У побудові графіка, крім кількісних показників, обов'язково повинні бути присутні і показники часу (місяць, квартал, рік і т.д.)

3.1. Створіть на новому листі ще одну зведену таблицю, в якій: поле Назва місту в області Столбец, поле Місяць продажу - в області Строка, поле Вартість загальна в області Данные. Виділіть або виберіть область зведеної таблиці, що відноситься до будь-якої назви міста, наприклад, Батурин. В інтервал повинні входити, крім вартості (або кількості), місяць продажу (січень … червень).

3.2. Викличте Мастер диаграмм.

3.3. Виберіть категорію - график і який-небудь з його видів.

3.4. Виконайте підписи і назву графіка.

3.5. Помітьте яку-небудь з крапок на графіку і викличте контекстне меню.

3.6. Виберіть в контекстному меню Добавить линию тренда, виберіть її тип.

 

3.7. У вікні Параметры установіть перемикач Прогноз вперед на __ периодов число 1.

3.8. Установіть опції: показывать уравнение на диаграмме та поместить на диаграмму величину достоверности аппроксимации.

3.9. Клацніть на кнопці ОК та проаналізуйте графік.

3.10. Побудуйте лінії тренда за всіма назвами міст.

Увага! Самостійно створить на новому листі консолідацію даних з полів:

Назва міста та Вартість загальна.

ІІІ. Індивідуальне завдання

1. Завантажити Excel і відкрити файл з індивідуальною таблицею (лабораторна робота №3).

2. Для даних таблиці на новому листі створити проміжні підсумки.

3. На окремих листах робочої книги створити зведені таблиці.

4. Побудувати лінії тренда за всіма назвами міст.

5. Створити на новому листі консолідацію даних.

6. Створити колонтитули та надрукувати результати роботи по пункту 4.

7. Оформити звіт з лабораторної роботи та зробити висновки.