Приклад виконання лабораторної роботи

Список видів товарів

1. Хлібобулочні вироби
2. Молочні вироби
3. Взуття
4. Побутова хімія
5. Соки та мінеральні води
6. М’ясопродукти
7. Кондитерські вироби
8. Одяг
9. Канцелярські товари
10. Газети та журнали
11. Іграшки
12. Миючі засоби
13. Аптека
14. Головні убори
15. Чай та кава
16. Крупи
17. Побутова техніка
18. Морепродукти
19. Меблі
20. Будматеріали
21. Фрукти
22. Овочі

 

ЛАБОРАТОРНА РОБОТА №2

 

Тема: використання формул в MS Excel

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

Завдання:

Потрібно створити фінансовий звіт про продаж товарів деякого магазину. Звіт має форму таблиці, в якій представлена інформація про продані товари та обчислені прибуток і податки, які повинен сплатити магазин (рис. 2.2). За основу для виконання завдання взяти лабораторну роботу №1. Кількість проданих товарів повинна бути не менше 10.

Порядок виконання

1. Створити таблицю, як це показано на рис. 2.2. При створенні обов’язково вказати формат „денежный” для комірок з грошовими значеннями.

2. Занести до таблиці вихідні дані про продані товари: номер, назву, ціну закупки, ціну продажу, кількість проданого товару.

3. Вказати у відповідних комірках ставки податку на додану вартість (ПДВ) та податку на прибуток (ПП).

4. В стовпчик Витрати записати формулу для визначення витрачених на закупівлю товару коштів: .

5. В стовпчик Сплачений ПДВ записати формулу для розрахунку податку на додану вартість закупленого товару: .

6. В стовпчик Виручка записати формулу для розрахунку виручених від продажу кожного товару коштів:

7. В стовпчик Нарахований ПДВ записати формулу для розрахунку податку на додану вартість проданого товару:

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

9. Записати формулу для визначення решти податку на додану вартість, яку залишилось сплатити: .

10. Записати формулу для визначення отриманого від продажу товарів прибутку:

.

11. Записати формулу для визначення податку на отриманий прибуток:

.

12. Записати формулу для визначення суми податків, які магазин повинен сплатити державі:

.

13. Записати формулу для визначення чистого прибутку, який отримав магазин:

14. Записати формулу для визначення коштів, що залишаться в магазині:

або

.

 

Теоретичні відомості

Формула – це текстовий рядок, який записується в комірку і визначає алгоритм, за яким Excel знаходить значення цієї комірки. Цей рядок завжди починається зі знака „=”. Знак „=” вказує Excel, що в комірці записана саме формула а не звичайний текст.

Формула містить дані та операції над цими даними.

Дані – це константи або посилання на комірки, в яких зберігаються значення для обчислень. Під час обчислень Excel автоматично бере дані з комірок, на які вказують посилання, і підставляє їх в формулу. Якщо значення, що зберігаються в цих комірках, зміняться, то Excel автоматично перерахує значення формули.

Операції над даними визначаються операторами (+, –, /, * та ін.) та функціями (будуть вивчатися в лабораторній роботі №3). На рис. 2.1 представлено приклад формули для визначення площі прямокутника. Довжини сторін прямокутника вказані в окремих комірках, тому формула містить посилання на ці комірки. Для визначення площі використовується оператор * (зірочка), який відповідає арифметичній дії добутку.

 

 

Приклад виконання

ЛАБОРАТОРНА РОБОТА №3

 

Тема: робота з функціями.

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

Завдання:

Знайти значення функції на проміжку та побудувати графік цієї функції. Функцію вибрати з таблиці 3.1 за своїм номером у списку групи як і в лабор. роботі № 1.

Порядок виконання:

Для рішення завдання треба розбити проміжок на 100 рівних частин і знайти значення функції в крайніх точках проміжка та точках розбиття. Всього буде 101 точка. Потім за цими значеннями побудувати графік функції. Вся робота виконується за 8 кроків:

1. Створити таблицю для зберігання значень функції :

a) створити заголовок таблиці (комірки B2, C2, D2 на рис. 3.1);

b) заповнити значеннями 1 – 101 стовпчик №:

1) записати у комірку, що під назвою стовпчика № (комірка В2), число 1;

2) автоматично заповнити інші комірки стовпчика:

Правка à заполнить à прогрессияà встановити параметри як на рис. 3.1 à Ok

 

 

2. Створити таблицю параметрів (комірки F2:J3) і записати туди значення , , та . Якщо параметр містить константу , для його обчислення використати формулу.

3. За формулою знайти крок (комірка H3), з яким змінюється : .

4. За формулами у стовпчику X визначити всі значення змінної :

a) У першу комірку стовпчика X (комірка C3) записати початкове значення : .

b) у другу комірку стовпчика X (комірка C4) записати формулу для визначення через попереднє значення та крок : .

c) розтягнути цю формулу (з комірки C4) на всі нижчі комірки стовпчика X.

5. За формулами у стовпчику Y знайти значення функції на проміжку :

a) у першу комірку стовпчика Y (комірка D3) записати формулу для знаходження значення функції у точці .

b) розтягнути цю формулу (з комірки D3) на всі нижчі комірки стовпчика Y.

6. Відформатувати стовпчики №, X та Y так, як на рис. 3.3.

7. Створити графік функції :

Вставка à Диаграмма à Точечная à Далее à Ряд à Добавить à вказати параметри ряду даних як на рис. 3.2 à Готово

 

 

8. Настроїти елементи графіка: вісі, фон, розташування написів та їх розмір і ін. Для цього необхідно наводити мишку на кожен елемент і натискати праву клавішу мишки. У вікні, що з’являється, встановлювати необхідні параметри вибраного елемента.

 

Приклад виконання лабораторної роботи

Таблиця 3.1 Варіанти функцій до лабораторної роботи №3

функція	параметри	проміжок
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

ЛАБОРАТОРНА РОБОТА №4

 

Тема: логічні функції.

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

Завдання:

Банк видає невеликі споживчі кредити за спрощеною процедурою. Але завжди є ризик неповернення або несвоєчасного повернення кредиту. Щоб максимально зменшити цей ризик фахівці банку сформували кілька простих правил для оцінки можливостей клієнта вчасно повернути кредит. Клієнт оцінювався за такими параметрами: вік, сімейний стан, розмір середньомісячного доходу, чи має постійне місце роботи. Також враховувався розмір кредиту, який клієнт бажає отримати, і термін, за який він зобов’язується повернути кредит. Алгоритм оцінки представлений на рис. 4.1. а правила записані у вигляді таблиць 4.1 – 4.7.

 

 

 

Таблиця 4.1 Оцінка віку			Таблиця 4.2 Оцінка соціального стану
Вік (A)	оцінка		У шлюбі	Постійна робота	Соціальний стан
A ≤ 21	юний		ні	немає	нестабільний
21 < A ≤ 35	молодий		ні	є	змінний
A > 35	зрілий		так	немає	змінний
			так	є	стабільний
Таблиця 4.3 Оцінка терміну повернення			Таблиця 4.4 Оцінка розміру кредиту
Термін повернення кредиту (T) років	оцінка			Відносний розмір кредиту (R)	Оцінка
T ≤ 3	короткий			R ≤ 1	малий
3 < T ≤ 10	середній			1< R ≤ 5	середній
T >10	довгий			5< R ≤ 20	великий
				R >20	значний

 

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

 

 

Таблиця 4.5 Оцінка надійності клієнта		Таблиця 4.6 Оцінка ризику невчасного повернення
Вік	Соціальний стан	Надійність		Розмір кредиту	Термін повернення	Ризик невчасного повернення
юний	змінний	низька		малий	короткий	середній
юний	стабільний	середня		малий	середній	малий
молодий	змінний	середня		малий	довгий	малий
молодий	стабільний	висока		середній	короткий	великий
зрілий	змінний	висока		середній	середній	середній
зрілий	стабільний	висока		середній	довгий	малий
	нестабільний	низька		великий	короткий	значний
				великий	середній	великий
				великий	довгий	середній
				значний	короткий	значний
				значний	середній	значний
				значний	довгий	великий

 

Таблиця 4.7 Прийняття рішення
Кредит видається, коли:
Надійність	ризик невчасного повернення
низька	малий
середня	малий
середня	середній
висока	малий
висока	середній
висока	великий

 

Потрібно, використовуючи логічні функції, розробити в MS Excel таблицю для прийняття рішення про надання кредиту. На рис. 4.2 представлений приклад такої таблиці.