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

ДОМ КНИГИ, ООО

83048, Донецк, ул. Артема, 147 а

Тел: [phone]

E-Mail: komp@hbook/donetsk.ua

Тема. Табличний процесор MS Excel. Створення діаграм.

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

Завдання 1. Заповніть даними і розрахуйте таблицю, наведену нижче.

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

1. Створіть шапку таблиці, враховуючі об’єднання чарунок за допомогою команди Формат – Ячейки – закладка Выравнивание.

2. Створіть нумерацію стовпчиків і рядків, за допомогою маркера авто заповнення утримуючи клавішу CTRL.

3. Створіть автосписок Найменувань товарної групи (Сервис-Параметри-Список).

4. Введіть найменування товарної групи за допомогою автозаповнення у чарунки В5:В13.

5. Введіть числові данні.

6. Розрахуйте вміст чарунок С14 та Е14 за допомогою функції автосума

7. Введіть у чарунку D5 формулу (дивись таблицю), посилання на чарунки уведіть за допомогою миші, для створення абсолютного посилання на чарунку С14 використовуйте клавішу F4.

8. Скопіюйте формулу з чарунки D5 у чарунки D6:D13, за допомогою маркера автозаповнення.

9. Уведіть у чарунку F5 формулу (дивись таблицю), посилання на чарунки уведіть за допомогою миші, для створення абсолютного посилання на чарунку E14 використовуйте клавішу F4.

10. Скопіюйте формулу з чарунки F5 у чарунки F6:F13, за допомогою маркера автозаповнення.

11. Розрахуйте вміст чарунок G5: G13, та H5: H13, використовуючи набуті знання.

12. Скопіюйте формулу з чарунки С14 у чарунки D14:G14, за допомогою маркера автозаповнення.

Завдання 2. Створіть кругову діаграму “Аналіз товарообігу в розрізі товарних груп за базовий період” на одному листі з таблицею.

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

1. Виділіть у робочому листі дані для побудови діаграми, чарунки В5:В13 (данні цих чарунок являються категорією) і D5:D13 (несуміжні діапазони виділяти утримуючи клавішу CTRL).

2. Клацніть на кнопці — «Мастер диаграмм»на панелі інструментів «Стандартная» або виконайте команду «Вставка-Диаграмма…».

3. Виберіть потрібний вигляд діаграми (поля «Тип» та «Вид»), натисніть кнопку Далее.

4. Перевірте діапазон чарунок, що містять дані для побудови діаграми. При необхідності зазначте шляхом уведення з клавіатури (або виділення на робочому листі за допомогою миші) інший діапазон чарунок. Визначте, чи розташовані дані у рядках або стовпчиках, натисніть кнопку Далее.

5. На третьому кроці на вкладці Заголовки введіть назву для діаграми Аналіз товарообігу в розрізі товарних груп за базовий період. Натисніть кнопку Далее.

6. На четвертому кроці виберіть перемикач , натисніть копку ОК.

УВАГА!!! Створену діаграму можна у будь-який момент змінити та перемістити в іншу область листа, якщо діаграма створена не на окремому листі.

Існує можливість також виділити дані, для побудови діаграми на окремому робочому листі, натиснувши клавішу < F11 >. Excel додасть у робочу книгу лист діаграм і створить діаграму на основі формату, що використовується за умовчанням.

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

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

1. Виділіть у робочому листі дані для побудови діаграми, чарунки В5:В13 (данні цих чарунок являються категорією), а також С5:С13 і Е5:Е13 (несуміжні діапазони виділяти утримуючи клавішу CTRL).

2. Клацніть на кнопці — «Мастер диаграмм»на панелі інструментів «Стандартная» або виконайте команду «Вставка-Диаграмма…».

3. Виберіть потрібний вигляд діаграми (поля «Тип» та «Вид»), натисніть кнопку Далее.

4. Перевірте діапазон чарунок, що містять дані для побудови діаграми. При необхідності зазначте шляхом уведення з клавіатури (або виділення на робочому листі за допомогою миші) інший діапазон чарунок. Визначте, чи розташовані дані у рядках або стовпчиках, натисніть кнопку Далее.

5. На третьому кроці на вкладці Заголовки введіть назву для діаграми Порівняльний аналіз сум товарообігу за базовий і звітний періоди. Натисніть кнопку Далее.

6. На четвертому кроці виберіть перемикач , кнопка ОК.

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

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

2. Створіть нумерацію стовпчиків і рядків, за допомогою маркера автозаповнення і клавіши CTRL.

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

4. Проаналізуйте дані за допомогою діаграм.

5. Скопіюйте таблицю на Лист 2. Встановіть режим виводу формул.

6. Перейменуйте Лист 2 надавши йому ім’я Формули.

7. Підготуйте створені документи до друку у режимі Предварительный просмотр:

- орієнтація сторінок – альбомна;

- колонтитули: верхній - дата створення, нижній – прізвище студента і група.

6. Роздрукуйте створені документи.

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

Варіант №1

Відомість реалізації продукції за перший квартал (тис. грн.)

№ п/ч	Найменування продукції	Місяць	Всього за перший квартал (тис. грн.)
Січень (тис. грн.)	Питома вага %	Лютий (тис. грн.)	Питома вага %	Березень (тис. грн.)	Питома вага %
1.
1.	крупа вівсяна	23,3	*	12,36	*	54,32	*	*
2.	печиво піскове	5,36	*	56,33	*	96,56	*	*
3.	крупа гречана	123,36	*	78,12	*	8,12	*	*
4.	бісквіти	23,52	*	12,36	*	45,63	*	*
5.	крупа перлова	89,05	*	56,78	*	9,99	*	*
6.	кукурудзяні хлоп’я	12,12	*	12,89	*		*	*
7.	цукерки шоколадні	45,23	*	45,56	*	12,5	*	*
8.	цукор	89,12	*	89,45	*		*	*
9.	мед бджолиний	45,12	*	11,23	*	56,23	*	*
Разом:	*	*	*	*	*	*	*

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

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

Варіант №2

Виконання плану реалізації товару (тис. грн.)

Найменування товарної групи	Реалізація за півріччя	Всього за рік	Питома вага по факту виконання
І	ІІ	план	факт
план	факт	план	факт
Кондитерські вироби		56,33			*	*	*
Бакалія		12,3			*	*	*
Соки	23,3	25,63	25,6		*	*	*
Молочні продукти	56,5	23,5			*	*	*
Полуфабрикати	8,69	12,3	13,2		*	*	*
Макаронні вироби	23,8	45,6		46,9	*	*	*
Ковбасні вироби	23,1	8,69	12,3	14,5	*	*	*
Всього:	*	*	*	*	*	*	*

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

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

Варіант №3

Відомість реалізації товарів

№ п/ч	Найменування товару	Кількість упаковок	Ціна упаковки (грн.)	Сума товару	Сума націнки	Сума продажу з урахуванням торгової націнки
1.	Мандарини (Грузія)			*	*	*
2.	Мандарини (Іспанія)			*	*	*
3.	Мандарини (Єгипет)			*	*	*
4.	Мандарини (Ізраїль)			*	*	*
5.	Мандарини (Італія)			*	*	*
6.	Апельсини (Куба)			*	*	*
7.	Апельсини (Марокко)			*	*	*
8.	Апельсини (Греція)			*	*	*
Підсумок:	*		*	*	*
Торгова націнка	20%

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

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

Варіант №4

Аналіз сум контрактів з постачальниками

№ п/ч	Найменування постачальника	Сума контракту у грн.	Сума контракту у іншій валюті	Сума контракту з урахуванням ПДВ (грн.)
долари	євро
1.	Рупас Пак		*	*	*
2.	Регент		*	*	*
3.	СТК Лютич		*	*	*
4.	ФЛОРА Нест		*	*	*
5.	Прем’єр папір		*	*	*
6.	Поліграфічні системи		*	*	*
7.	Поліграф імпорт		*	*	*
8.	Папірус		*	*	*
Разом:	*	*	*
Курс		5,4	6,1
ПДВ	20%

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

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

Варіант №5

Данні про залишки товару

№ п/ч	Найменування товару	Залишок на початковий період (грн.)	Надійшло	Продано (грн.)	Залишок на кінцевий період (грн.)
Ціна (грн.)	Кількість (кг)	Питома вага	Сума (грн.)
1.	2.	3.	4.	5.	6.	7.	8.	9.
	Халва з горіхами	1200,3	6,50		*	*		*
	Печиво “Шик-шок”	354,6	6,30		*	*		*
	Бісквіт “Полуничний”	1254,36	3,2		*	*		*
	Карамель “Горішок”	212,36	7,30		*	*		*
	Цукерки шоколадні “Білочка з горіхами”	892,56	26,50		*	*		*
	Морозиво “Ласунка”	1222,36	1,50		*	*		*
	Бісквіт “Молочний”	323,56	14,60		*	*		*
Разом:	*		*		*	*	*

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

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

Варіант №6

Звіт про імпорт товарів

Найменування	Вартість одиниці товару ($)	Кіль-кість	Загальна сума	Загальна сума з урахуванням ставки мита
країни походження товару	країни, що торгує	товару	одиниць виміру
1.	2.	3.	4.	5.	6.	7.	8.
Іспанія	Україна	Апель­сини	кг	0,96		*	*
Туреччина	Україна	Ман­дарини	кг	0,99		*	*
Греція	Україна	Ман­дарини	кг	1,02		*	*
Марокко	Україна	Апельсини	кг	1,05		*	*
Грузія	Україна	Апельсини	кг	0,89		*	*
Греція	Україна	Апельсини	кг	0,80		*	*
Єгипет	Україна	Банани	кг	0,78		*	*
Куба	Україна	Апельсини	кг	0,76		*	*
Іспанія	Україна	Кава	кг	3,20		*	*
Підсумок:	*	*
Діюча ставка мита	25%

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

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

Варіант №7

Аналіз продажу з урахуванням знижки

№ п/ч	Найменування товару	Кількість	Ціна за одиницю товару	Сума покупки	Сума з урахуванням знижки
	Шафа для посуду			*	*
	Стіл комп’ютерний			*	*
	Стілець кухонний			*	*
	Стіл кухонний			*	*
	Шафа-купе			*	*
	Стілець дитячий			*	*
	Шафа для кухні			*	*
	Барна стійка			*	*
Разом:	*	*
Знижка (у відсотках):

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

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

Варіант №8

Відомість продажу товару

№ п/ч	Найменування товару	Закупівельна ціна	Акцизний збір	ПДВ	Торгівельна націнка	Ціна реалізації
1.	Шуба з хутра лисиці		*	*	*	*
2.	Шуба з хутра нутрії		*	*	*	*
3.	Шуба з хутра норки		*	*	*	*
4.	Ваза кришталева для квітів		*	*	*	*
5.	Бокал кришталевий		*	*	*	*
6.	Люстра кришталева		*	*	*	*
7.	Ваза кришталева для фруктів		*	*	*	*
8.	Килим 2х5		*	*	*	*
9.	Килим 4х5		*	*	*	*
Разом:		*	*	*	*
ПДВ	20%
Акцизний збір	50%
Торгівельна націнка	15%

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

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

Варіант №9

Аналіз прибутку підприємства від реалізації виробів

Найменування виробу	Оптова ціна за одиницю виробу (грн..)	Кількість реалізованих виробів	Прибуток	Питома вага прибутку	Сума відхилення прибутку
план	факт	план	факт
Чоботи шкіряні жіночі				*	*	*	*
Чоботи шкіряні для чоловіків				*	*	*	*
Чоботи шкіряні дитячі				*	*	*	*
Ботики дитячі				*	*	*	*
Унти для чоловіків				*	*	*	*
Туфлі жіночі				*	*	*	*
Туфлі для підлітків				*	*	*	*
Кросівки				*	*	*	*
Разом:		*	*	*	*	*	*

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

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

Варіант №10

Відомість нарахування премії співробітникам

№ п/ч	ПІБ	Обсяг продаж (грн.)	Питома вага від обсягу продаж	нарахування	До виплати
заробітна плата	премія (% від суми прибутку)
	Івакін І.І.		*		*	*
	Купенко М.Р.		*		*	*
	Панок О.Л.		*		*	*
	Пащенко П.Р.		*		*	*
	Романов О.О.		*		*	*
	Панько Л.Д.		*		*	*
	Шуліка Л.Г.		*		*	*
Разом	*	*	*	*	*
Процент премії	5%

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

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

Варіант №11

Відомість нарахування дивідендів

№ п/ч	Відомості про акціонера	Відсоток від прибутку	Дивіденди
ПІБ	Стаж
	Гусев В.Л.		10%	*
	Іванов А.Д.		12%	*
	Петров П.О.		18%	*
	Коломієць Н.Л.		15%	*
	Чубарева А.В.		10%	*
	Волкова О.В.		11%	*
	Дяченко А.А.		12,5%	*
	Соловей Д.О.		8%	*
Прибуток

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

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

Варіант №12

Амортизаційні розрахунки

Вихідний дані	Розрахункові дані
Станок	Первинна вартість (ПВ)	Ліквідна вартість (ЛВ)	Амортизаційна вартість (2ст.-3ст.)	Річна амортизація (4ст./ТКД)	Щомісячна амортизація (5ст./12)
№1			*	*	*
№2			*	*	*
№3			*	*	*
№4			*	*	*
№5			*	*	*
№6			*	*	*
№7			*	*	*
Термін корисної дії (ТКД)

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

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

Варіант №13

Відомість відрахувань

ПІБ	Нарахування	Відрахування
Пенсійний фонд	Соціальне страхування	Страхування на випадок безробіття
Макаров М.В.		*	*	*
Карнаухова В.М.		*	*	*
Петров О.О.		*	*	*
Ковальова Т.Н.		*	*	*
Сидоренко Ю.Д.		*	*	*
Харламов А.Б.		*	*	*
Котов Р.Д.		*	*	*
Пенсійний фонд	2%
Соціальне страхування	0,5%
Страхування на випадок безробіття	2,9%

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

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

Варіант №14

Інформація про вартість викупу

Вихідні дані	Інформація про викуп
Предмет застави	Вартість	Плата користування позикою за день	Вартість викупу (2ст.+3ст.*К)
Сережки		*	*
Каблучка		*	*
Кольє		*	*
Годинник		*	*
Браслет		*	*
Відсоток за користування позикою (за день)	0,05%
Кількість днів (К)

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

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

Варіант №15

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