3. Підведення проміжних підсумків. Консолідація. зведені таблиці

Самостійна робота 3
Підведення проміжних підсумків. Консолідація. зведені таблиці

 Мета роботи: Вивчити прийоми консолідації даних і підведення підсумків за допомогою табличного процесора MS Excel. Вивчити прийоми створення зведених таблиць.

Завдання самостійної роботи:

1. Використання проміжних підсумків.
2. Використання консолідації даних.
3. Побудова зведеної таблиці.

Опис лабораторного обладнання:

• Персональний комп’ютер;
• ОС Windows;
• Програма MS Excel

Вимоги до змісту та оформлення звіту

• Тема і мета самостійної роботи.
• Короткі теоретичні відомості до самостійної роботи.
• Висновки щодо виконання самостійної роботи.
• В результаті виконання самостійної роботи повинно вийти 2 файли. Файли необхідно назвати наступним чином: <Прізвище-3_1>,<Прізвище-3_2>.

Практичне завдання

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

Створити таблицю з даними наведеними на рис. 1.

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

Рекомендації до виконання.

1. Для підведення підсумків список повинен бути відсортований по полю «Назва кафе», рис.1.

Рис. 1. Інформація по фірмі «Золота квітка»

Підрахувати Прибуток (Кількість проданих страв помножити на Ціну)

2. Виділити набрану таблицю (діапазон А2:Е20);
3. Перейти на вкладку Данные і обрати команду Промежуточный итог;
4. У діалоговому вікні Промежуточный итог у полі При каждом изменении в вказати Назва кафе, у полі Операция – Сума, а в полі Добавить итоги по поставити прапорець Прибуток (рис. 2).

Рис. 2. Вибір параметрів у вікні «Проміжні підсумки»

Після натискання на кнопку Ок, зовнішній вигляд таблиця буде наступним: (рис. 3).

Рис. 3. Таблиця після підведення проміжних підсумків

При цьому, зліва автоматично буде створена структура документа, в якій можна згортати та розгортати асортимент і передивлятися підсумки по кожному кафе. Для приховування деталей треба натиснути «–». Для відображення – «+», рис.4.

Рис.4. Відображення загального підсумку

6. Зберегти документ у файлі під назвою <Прізвище-3_1>.

Завдання 2

1. Перейти на Лист 2 і створити таблицю «Торгові агенти» (рис. 5);

Рис.5. Таблиця «Торгові агенти»

2. Додати до таблиці стовпці з назвами Прибуток і Борг (вартість книг, які залишилися непроданими) та підрахувати значення в цих стовпцях.

3.Скопіювати Лист 2, для цього:

• стати на назву Лист 2 і викликати контекстне меню;
• обрати команду Переместить или скопировать, рис 6;

Рис.6. Контекстне меню

• у діалоговому вікні Переместить или скопировать, у полі Перед листом обрати Лист 3 та встановити прапорець біля команди Создать копию. (Повинен з’явитися лист під назвою Лист 2(2) зі змістом ідентичним до Листа 2).

3. Стати на Лист 2 і підвести проміжні підсумки, що показують кількість проданих книг кожного найменування і суму виручених грошей.
4. Перейти на Лист 2(2) і підвести:

• проміжні підсумки, які показують, скільки грошей отримав кожен торговий агент. Попередньо впорядкувати список по полю Продавець (вкладка Данные / Сортировка).
• проміжні підсумки, які показують, який борг у кожного продавця.

5. Зберегти документ у файлі під назвою <Прізвище-3_1>.

Завдання 3. Консолідація даних

1. Перейти на Лист 1 з інформацією про фірму «Золота квітка»
2. Перенести дані по кожному кафе на окремий лист і перетворити їх відповідно до зразка (рис. 7).

Рис.7. Зразок оформлення листа по одному з кафе

3. Додати ще один чистий лист і перейменувати його на Звіт.
4. На цьому аркуші підготувати консолідуючий звіт в наступній послідовності:

– Внести заголовок Звіт про роботу кафе: «Ромашка», «Бузок», «Тюльпан».

– Скопіювати з будь-якого листа на лист Звіт рядок 2.

– На аркуші Звіт зробити активною клітинку A2 (Страва).

– У вкладці Данные, вибрати команду Консолидация. У діалоговому вікні Консолидация в поле Функция вибрати Сумма. Встановити курсор в рядок Ссылка та додати дані для консолідації. Для цього виділіть всю таблицю з даними для першого кафе. Клацнути по кнопці Добавить в діалоговому вікні Консолидация. Аналогічно вибрати дані для другого і третього кафе. У групі Использовать в качестве имен встановити прапорці біля команд подписи верхней строки та значения левого столбца, рис.8.

Рис. 8. Вибір даних для проведення консолідації

– Після натискання кнопки Ок, таблиця Звіт повинна виглядати наступним чином, рис.9.

Рис. 9. Результат проведення консолідації

5. Зберегти документ у файлі під назвою <Прізвище-3_1>.

Завдання 4

Побудова зведеної таблиці. Зведені таблиці використовують для вибіркового аналізу даних у великих таблицях.

Створити таблицю з 10 рядків, що відображають облік проданих фірмою товарів з такими назвами стовпців: Номер, Назва товару, Ціна, Кількість, Вартість, Дата, Продавець, Покупець. Назви товарів, фірм-покупців, прізвища продавців, дати повинні повторюватися по три-чотири рази. Заповнити таблицю даними на свій розсуд. Побудувати зведену таблицю, що характеризує ефективність роботи кожного продавця, тобто що відображає, які товари він продав, скільки і на яку суму (Рис. 10).

Рис. 10. Приклад таблиці з даними

Рекомендації до виконання.

1. Виділіть створену таблицю і виконайте команди вкладка Вставка / Сводная таблица…
2. У діалоговому вікні Создание сводной таблицы встановіть прапорець біля команди На новый лист і натисніть Ок.
3. На листі, що з’явився в полі Список полей сводной таблицы встановити прапорці біля команд: Назва, Кількість, Вартість, Продавець.
4. Зовнішній вигляд таблиці повинен бути наступним, рис.11.

Щоб модифікувати зведену таблицю, необхідно в полі Список полей сводной таблицы установити прапорці для інших категорій або побудувати її знов але з іншими категоріями.

Рис.11. Створення зведеної таблиці

5. Зберегти документ у файлі під назвою <Прізвище-3_2>.

Контрольні питання

1. Які засоби Excel дозволяють здійснювати автоматичне підведення підсумків?
2. Чим відрізняються проміжні підсумки від загальних?
3. Які параметки можна застосувати для підсумків?
4. Що таке консолідуючий звіт? Як його підготувати за допомогою Excel
5. 5. Як повинні бути організовані дані, щоб до них можна було застосувати команду вкладка Данные / Промежуточный итог?
6. 6. Яке призначення структури електронної таблиці? Як працювати зі структурою, які можливості надає структура робочому аркушу?