Хід виконання роботи

Лабораторна робота № 11-12

з навчальної дисципліни «Інформаційні системи в економіці»

Тема: Створення комп’ютерної системи з нарахування заробітної плати на базі MS Excel або Open Office Calc.

Мета роботи: вміти створювати КСБО засобами MS Excel; використовувати логічні і статистичні функції; налагодження інформаційних зв’язків.

Тривалість заняття: 80 хвилин.

Матеріально-технічне оснащення робочого місця: комп'ютери, інструкційні карти.

Завдання

Розробити модуль «Нарахування та виплата заробітної плати» на базі MS Excel виконуючи наступні етапи:

1. розробити бланк «Розрахунково-платіжна відомість»; підготувати текстовий документ «Розрахунковий листок».

2. на бланку «Розрахунково-платіжна відомість» установлюють зовнішні інформаційні й формульні зв'язки з формою «Штатний перелік і оплата»;

3. у відповідні комірки бланка «Розрахунково-платіжна відомість» уводять формули для обчислення: доплати, фонд зайнятості, пенсійний фонд, прибутковий податок і т.д.

Хід виконання роботи

1. Знайдіть на комп’ютері викладача файл «Основний-Нарахування зарплати.xlsx», його копію збережіть в папці «КСБО зарплата» у власній папці.

Перейменуйте документ видалив слово Основний. Відтепер ваш файл називається «Нарахування зарплати.xlsx».

2. Відкрийте файл – «Нарахування зарплати.xlsx» з підготовленими бланками документів «Штатний перелік і оплата», «Вид нарахувань і утримань».

3. Створіть новий аркуш «Розрахунково-платіжна відомість». Заповніть перший рядок таблиці даними:

A1 - Табельний номер

B1 - ПІБ

C1 - Підрозділ

D1 - Посада

E1 - Посадовий оклад, грн.

F1 - Доплата, %

G1 - Доплата (премія), грн.

H1 - Аванс, грн.

I1 - Фонд зайнятості, грн.

J1 - Пенсійний фонд, грн.

K1 - Фонд соціального страхування, грн.

L1 - Податок на доходи фізичних осіб, грн.

M1 - Отримано на руки, грн.

N1 - Підпис отримуючого

4. Перейдіть на аркуш «Штатний перелік і оплата». Привласніть стовпцям таблиці імена: активізуйте стовпець та використайте опцію на вкладці Формули ® Привласнити ім’я (рис. 1).

Рис.1 Привласнення імені діапазону комірок

5. Перейдіть на аркуш «Розрахунково-платіжна відомість» і встановіть інформаційний зв’язок з аркушем «Штатний перелік і оплата»:

- активізувати аркуш «Розрахунково - платіжна відомість» і встановіть курсор в комірку А2;

- натиснути клавішу «=»,

- активізувати аркуш «Штатний перелік і оплата» і клацнути мишею на комірці A2 цієї форми;

- нажати клавішу «Enter».

При цьому в клітинці А2 буде автоматично встановлений інформаційний зв'язок: ='Штатний перелік і оплата'!A2.

Заповніть комірки А3:А14 використовуючи метод копіювання-протяжка.

6. Використовуючи функцію «ПРОСМОТР» заповніть діапазон комірок В2:Е2.

Довідка: ПРОСМОТР - повертає значення з рядка, з стовпця або з масиву. Функція ПРОСМОТР має дві синтаксичні форми: вектор і масив. Векторна форма функції ПРОСМОТР переглядає діапазон, у який входять значення тільки одного рядка або одного стовпця (так званий вектор) у пошуках певного значення й повертає значення з іншого стовпця або рядка. Форма масиву функції ПРОСМОТР переглядає перший рядок або перший стовпець масиву, знаходить зазначене значення й повертає значення з аналогічної позиції останнього рядка або стовпця масиву. Важливо: значення в аргументі просматриваемый_вектор повинні бути розташовані в порядку зростання. Замітки:

- Якщо ПРОСМОТР не може знайти искомое_значение, то підходящою вважається найбільше значення в аргументі просматриваемый_вектор, що менше, ніж искомое_значение.

- Якщо искомое_значение менше, ніж найменше значення в аргументі просматриваемый_вектор, то функція ПРОСМОТР повертає значення помилки #Н/Д.

6.1 Перейдіть на аркуш «Штатний перелік і оплата». Відсортуйте інформацію таблиці в колонці «Табельний номер» за зростанням: виділіть діапазон комірок A1:F14 і оберіть вкладку Данні ® Сортування.

6.2 Поверніться на аркуш «Розрахунково-платіжна відомість» і встановіть курсор в комірку В2. Встановіть функцію «ПРОСМОТР».

Крок 1: оберіть искомое_значение;просматриваемый_вектор;вектор_результатов.

Крок 2: заповніть аргументи функції:

- Искомое_значение – виділіть комірку А2

- Просматриваемый_вектор – активізуйте аркуш «Штатний перелік і оплата» і виділіть стовпець А (Табельний номер)

- Вектор_результатов - активізуйте аркуш «Штатний перелік і оплата» і виділіть стовпець В (ПІБ)

При цьому в клітинці В2 буде автоматично встановлений інформаційний зв'язок: =ПРОСМОТР(A2;Табельний_номер;ПІБ)

Аналогічно за допомогою функції ПРОСМОТР заповніть комірки С2, D2, Е2, F2. Автоматично створюються інформаційні зв'язки:

=ПРОСМОТР(A2;Табельний_номер;Підрозділ)

=ПРОСМОТР(A2;Табельний_номер;Посада)

=ПРОСМОТР(A2;Табельний_номер;Посадовий_оклад_грн.)

=ПРОСМОТР(A2;Табельний_номер;Доплата)

Заповніть комірки В3:F14 використовуючи метод копіювання-протяжка.

7. Встановіть в комірку G2 формулу для розрахунку стовпця Доплата (премія), грн.

Доплата грн. = Посадовий оклад * Доплата,%

8. Для розрахунку авансу (комірка H2), фонду зайнятості (комірка I2), пенсійного фонду (комірка J2), податку на доходи фізичних осіб (комірка L2) використайте формулу:

= (Посадовий оклад, грн. + Доплата (премія), грн.) * x