Лабораторна робота №5

Розрахунок прибутку

Статті доходу	Перший квартал	Разом	Питома вага
січень	лютий	березень
Комп’ютери				*	*
Комплектуючі				*	*
Програмне забезпечення				*	*
Доход всього:	*	*	*	*
Статті витрат
Реклама				*	*
Оренда				*	*
Податки				*	*
Витрати всього:	*	*	*	*
Прибуток:	*	*	*	*

1. Побудувати колову діаграму аналізу доходу за перший квартал. Розташувати на одному листі з таблицею.

2. Побудувати гістограму порівняння витрат січень та березень. Розташувати на окремому листі.

Тема. Табличний процесор MS Excel. Аналіз даних за допомогою зведених таблиць.

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

Електронні таблиці можуть містити великі групи даних, що вимагають деякого узагальнення і аналізу. Для цих цілей застосовуються зведені таблиці, які дозволяють узагальнити дані і проглянути зрізи даних по деяких критеріях: по товарних групах, по найменуваннях товарів, по постачальникам, по датам, по споживачам, по П.І.Б., по виробникам і т. ін.

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

Завдання 1. Підготуйте початкові дані для побудови зведеної таблиці, або завантажте готовий файл общие_сводные.xls. Створіть зведену таблицю об'єму продажу по товарам і регіонам (використовуючи майстер зведених таблиць).

У таблиці приведені дані про об'єми продажу телевізорів різних виробників по регіонам за три місяці 2005 року.

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

Методичні вказівки.

1. Виберіть пункт меню Данные – Сводная таблица…. Буде запущений майстер.

2. На першому кроці запрошуються початкові дані для зведеної таблиці - виберіть перемикач в списке или базе данных Microsoft Excel і натисніть кнопку Далее>.

3. На другому кроці виділіть діапазон чарунок A2:D29. Натисніть Далее>.

Увага!!! При виділенні діапазону, захоплюємо заголовок таблиці, а нижній рядок з підсумками не беремо.

4. На третьому кроці, безпосередньо створюється зведена таблиця - на макет, показаний на рис. 1, перетягуємо заголовки таблиці в потрібні області. ОК.

Рисунок 1. Макет для створення зведених таблиць

Увага!!! Якщо необхідно вибрати операцію для поля, яке обчислюється клацніть на ньому двічі. Відкриється діалогове вікно Вычисление поля сводной таблицы. Виберіть потрібну операцію (рис.2).

Рис.2. Діалогове вікно Вычисление поля сводной таблицы.

Увага!!! Можна перетягувати кнопки (назв стовпців) як на макет, так і з макету на Панель инструментов Сводная таблица. Таким чином, зведена таблиця завжди може бути реорганізована. Поля із списками в зведеній таблиці дозволяють одержувати зрізи даних.

4. На третьому кроці вибираємо місце розташування таблиці.

Зведена таблиця може мати вигляд, як на рис. 3. Як видно, в ній стовпець Об'єм продажу підрахований і узагальнений по стовпцях Регіон і Товар по всім місяцях. Поля Період, Регіони і Товари є списки, які дозволяють вибирати і проглядати зрізи даних по деяких критеріях. У полі Сума по полю Об'єм продажу можна задавати інші операції.

Рисунок 3. - Зведена таблиця по товарах і регіонах.

Готова зведена таблиця легко реорганізується простим перетягуванням назв стовпців. Наприклад, поля Період і Регіон можна поміняти місцями і т.ін.

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

Увага!!! Якщо ви випадково закрили Панель інструментів Сводная таблица, то для її відображення клацніть правою кнопкою миші по Панелі інструментів Excel і в списку, що розкрився, виберіть еп1ёяґпотрібну панель.

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

- Формат отчета дозволяє додати зведеній таблиці "презентабельний" для друку вигляд;

- Мастер диаграмм дозволяє автоматично побудувати діаграму по зведеній таблиці;

- Отобразить детали дозволяє деталізувати зведену таблицю по деякому параметру. Наприклад, виділіть поле Товар, натисніть кнопку, далі в окремому вікні Показати деталі виберіть Період і натисніть ОК - товари будуть деталізовані по всіх періодах;

- Отключить детали відміняє деталізацію;

- Восстановить данные дозволяє відновити узагальнені дані в зведеній таблиці у разі зміни початкових даних. Змініть числові дані в стовпці Об'єми продажів початкової таблиці. У зведеній таблиці і на діаграмі зміни відбудуться тільки після оновлення даних, тобто натиснення кнопки;

- Параметры поля дозволяє встановити узагальнену операцію по вибраному полю. Наприклад, виділіть на зведеній таблиці поле Сума по полю Об'єм продажів і клацніть по кнопці. У вікні, що з'явилося, замість операції Сума задайте операцію Мінімум (або іншу). Натисніть ОК, проаналізуйте результат, а потім відмініть результат кнопкою.

Завдання 3. Для одержаної зведеної таблиці побудуйте об’ємну діаграму.

Методичні вказівки.

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

2. Змініть тип гістограми: ще раз натисніть кнопку Мастер диаграмм (можна клацнути правою кнопкою миші по самій гістограмі і із списку, що з'явився, вибрати Тип диаграммы, але тоді гістограма буде побудована на окремому листі). У вікні, що з'явилося, виберіть вигляд - об’ємна гістограма і натисніть Далее.

3. У наступному вікні Мастер диаграмм натисніть Далее.

4. У наступному, останньому вікні вкажіть, куди ви хочете помістити діаграму: виберіть перемикач окремому. Натисніть кнопку Готово. Гістограма буде перетворена в об’ємний вигляд, як на рис. 4. Списки на діаграмі дозволяють вибирати і проглядати зрізи даних так само, як на зведеній таблиці.

Рисунок 4 - Гістограма аналізу продажу товарів по зведеній таблиці.

Завдання 3. Виконайте фільтрацію даних у таблиці за допомогою авто фільтру.

Методичні вказівки.

1. Виділить будь-яку чарунку початкової таблиці та виконайте команди: Данные – фільтр – автофильтр.

2. Із списку, що розчиняється, наприклад, рядка «Період» виберіть «березень».

В результаті маємо таблицю, що містить дані продажів у березні.

Для відновлення всіх рядків початкової таблиці необхідно виконати команди: Данные – Фильтр – Отобразить все.

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

Індивідуальні завдання для виконання лабораторної роботи №5

1. Заповніть таблицю згідно варіанту.

2. Розрахуйте чарунки де містяться зірочки (*).

3. Створіть зведені таблиці згідно варіантам завдань.

4. Для третьої зведеної таблиці побудуйте об’ємну діаграму.

5. Виконайте фільтрацію даних у початковій таблиці за допомогою авто фільтру.

6. Роздрукуйте таблиці і діаграму.

7. Збережіть файл на диску, надавши йому ім’я (прізвище студента).

Варіант № 1 Відомість поставки продукції

Дата

Кількість

Товар

Країна постачальник

Ціна за одиницю

Сума

Січень

30 000

Ґудзики

Франція

*

Січень

240 000

Застібки

Італія

*

Лютий

121 000

Ґудзики

Франція

1,7

*

Січень

98 000

Ґудзики

Франція

1,7

*

Березень

345 000

Застібки

Італія

*

Лютий

215 000

Ґудзики

Канада

*

Березень

480 000

Застібки

Франція

6,8

*

Січень

212 000

Ґудзики

Італія

3,6

*

Квітень

420 000

Ґудзики

Канада

4,5

*

Травень

540 000

Ґудзики

Франція

1,7

*

Травень

311 000

Ґудзики

Італія

3,6

*

Червень

120 000

Застібки

Канада

*

Травень

98 000

Ґудзики

Франція

1,7

*

Травень

300 000

Ґудзики

Італія

3,6

*

Червень

120 000

Застібки

Італія

*

Разом:

*

Використовуючи майстер зведених таблиць створіть зведені таблиці:

a) Суми поставок товарів по країнам постачальницям у розрізі дат.

b) Поставок товарів з визначенням мінімальної ціни та середньої кількості.

c) Максимальної кількості продукції по товарах та країнах у розрізі найменувань товарів.

Варіант № 2 Данні про прибуток за рік

Місяць

Відділ

Дохід

Витрати

Рентабельність (гр.3/гр.4)

Прибуток (гр.3 - гр.4)

Січень

взуття дитяче

127 735

45 495

*

*

Лютий

взуття дитяче

127 246

47 710

*

*

Березень

взуття дитяче

127 289

48 402

*

*

Квітень

взуття дитяче

127 169

47 217

*

*

Травень

взуття дитяче

131 330

49 082

*

*

Червень

взуття дитяче

130 996

49 862

*

*

Липень

взуття дитяче

131 054

51 872

*

*

Серпень

взуття дитяче

135 284

61 427

*

*

Вересень

взуття дитяче

138 903

62 342

*

*

Жовтень

взуття дитяче

136 368

62 353

*

*

Листопад

взуття дитяче

135 199

60 571

*

*

Грудень

взуття дитяче

135 144

59 848

*

*

Січень

одяг жіночий

400 000

256 354

*

*

Лютий

одяг жіночий

558 654

425 862

*

*

Разом

*

Використовуючи майстер зведених таблиць створіть зведені таблиці:

a) Загального доходу та загальних витрат по відділам у розрізі місяців.

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

c) Мінімальних витрат та доходів по відділам у розрізі місяів.

Варіант № 3

Відомість обсягу продажу

Місяць	Обсяг продажу	Замовник	Найменування товару	Відсоток знижки	Сума знижки
Червень 2011	$47 926	Амстор	Апельсини		*
Липень 2011	$45 875	БУМ	Лимони		*
Серпень 2011	$28 800	БУМ	Мандарини		*
Вересень 2011	$60 000	Метро	Грейпфрут		*
Жовтень 2011	$58 051	Сокіл	Банани		*
Листопад 2011	$53 438	Атлант	Ананаси		*
Березень 2012	$72 938	Метро	Апельсини		*
Квітень 2012	$44 000	Сокіл	Лимони		*
Травень 2012	$85 900	Атлант	Мандарини		*
Червень 2012	$82 253	Амстор	Грейпфрут		*
Грудень 2012	$84 000	Амстор	Апельсини		*
Січень 2013	$62 900	БУМ	Лимони		*
Лютий 2013	$76 500	БУМ	Мандарини		*
Березень 2013	$67 100	Метро	Грейпфрут		*
Квітень 2013	$59 700	Сокіл	Банани		*
Травень 2013	$82 041	Атлант	Ананаси		*
Червень 2013	$61 300	Амстор	Папайя		*
Липень 2013	$80 700	БУМ	Авокадо		*
Разом:					*
Використовуючи майстер зведених таблиць створіть зведені таблиці:
a) Загального відсотку знижки та середнього відсотку знижки по замовникам у розрізі місяців.
b) Загальної суми знижки та мінімальної суми знижки по найменуванням товарів у розрізі місяців.
c) Максимального обсягу продажів та максимальної суми знижки по найменуванням товарів у розрізі замовників.

Варіант № 4

Дата добавления: 2014-11-06