Формули та функції. Типи адресації. Автозаповнення. Зв’язок між листами робочої книги

MICROSOFT EXCEL

Хід роботи:

1. Відкрити з персональної папки робочий файл.
2. Доповнити штатний розпис іншими посадами згідно наступної таблиці:

- - для вставки нових посад використовувати “ВставкаÞСтроки”;

- - для нумерації використовувати автозаповнення;

- - для стовпчика сума використовувати авто заповнення;

- - перевірити правильність формул та форматів даних.

Штатний розпис фірми "Хлібторг"
№ з/п	Посада	Кількість	Посадовий оклад	Сума
	директор		600,00 грн.	600,00 грн.
	бухгалтер		500,00 грн.	500,00 грн.
	продавець		450,00 грн.	900,00 грн.
	вантажник		350,00 грн.	700,00 грн.
	зам.директора		500,00 грн.	500,00 грн.
	касир		350,00 грн.	350,00 грн.
	водій		300,00 грн.	600,00 грн.
	прибиральник		150,00 грн.	300,00 грн.
	секретар-референт		270,00 грн.	270,00 грн.
	охоронець		200,00 грн.	600,00 грн.
	Всього:			5 320,00 грн.

 

1. Перейти на Лист 2 та перейменувати його на “Кадри”.
2. Використовуючи відомі методи вводу та форматування даних, створити таблицю кадрового забезпечення:

Кадрове забезпечення
№ з/п	ПІБ	Посада	Дата народження	Дата зарахування
	Головатчук Г.О.
	Віцин В.І.
	Валютенко В.А.
	Банковець Б.А.
	Клавіатуренко К.Л.
	Сидоренко С.Д.
	Махлевська М.А.
	Баранов Б.А.
	Колесник К.О.
	Тягло Т.Я.
	Кантоваленко К.А.
	Вінник В.І.
	Відров В.В.
	Собаченко С.О.
	Цепков П.К.
	Гавкало Г.А.

 

 

1. Заповнити стовпчик “Посада” використовуючи дані таблиці “Штатний розпис” (лист “Штат”):

- - виділяємо клітинку посади для першого прізвища в таблиці (С4);

- - натиснути знак “=” на клавіатурі або на панелі біля рядка формул;

- - при допомозі миші перейти на лист “Штат”;

- - виділити клітинку з вмістом “директор” (В4);

- - натиснути Enter;

- - пересвідчитись, що навпроти першого прізвища з’явилась посада – директор.

1. Аналогічно заповнити весь стовпчик “Посада” згідно зразка:

Кадрове забезпечення
№ з/п	ПІБ	Посада	Дата народження	Дата зарахування
	Головатчук Г.О.	директор	01.02.80	1 Лют 95
	Віцин В.І.	зам.директора	02.03.49	1 Лют 95
	Валютенко В.А.	бухгалтер	02.11.74	1 Лют 95
	Банковець Б.А.	касир	14.08.78	11 Тра 99
	Клавіатуренко К.Л.	секретар-референт	02.07.80	15 Лют 97
	Сидоренко С.Д.	продавець	30.12.77	7 Лис 99
	Махлевська М.А.	продавець	10.10.71	19 Грг 00
	Баранов Б.А.	водій	12.04.69	16 Кві 99
	Колесник К.О.	водій	23.06.81	16 Кві 99
	Тягло Т.Я.	вантажник	20.09.80	22 Бер 00
	Кантоваленко К.А.	вантажник	05.11.68	16 Кві 99
	Вінник В.І.	прибиральник	02.09.71	12 Сер 00
	Відров В.В.	прибиральник	24.10.77	29 Жов 01
	Собаченко С.О.	охоронець	30.07.76	18 Вер 97
	Цепков П.К.	охоронець	20.08.70	18 Вер 97
	Гавкало Г.А.	охоронець	14.07.68	24 Чер 02

 

1. Дати, які записані в стовпчиках “Дата народження” та “Дата зарахування” заповнити самостійно (дані можна підібрати довільні). Формати дат встановити такими, якими вони зображені в таблиці, що наведена вище.
2. Перейти на Лист 3 та перейменувати його на “Розрахунок ЗП”.
3. Створити наступну таблицю:

 

1. Заповнити стовпчик “№” при допомозі авто заповнення від 1 до 16.
2. Заповнити стовпчик ПІБ відповідними даними з листа “Кадри”:

- - перше прізвище вивести аналогічно п5. практичного завдання №3 або написати наступну формулу: “=Кадри!B4” (в результаті з’явиться прізвище директора);

- - виділити клітинку з прізвищем директора;

- - використовуючи прийом автозаповнення, вивести решту прізвищ.

1. Використовуючи автозаповнення по горизонталі, вивести першу посаду.
2. Використовуючи автозаповнення по вертикалі, вивести решту посад.

 

1. Заповнити стовпчик “Оклад”, використовуючи “Специальную вставку” для кожної з посад на листі “Штат”.

- - перейти на лист “Штат” та виділити оклад директора (D4);

- - скопіювати в буфер обміну вміст клітинки;

- - перейти на лист “Розрахунок ЗП” та виділити клітинку окладу директора;

- - виконати “ПравкаÞСпециальная вставка”

- - у вікні, що з’явиться – “Специальная всавка”, натиснути кнопку “Вставить связь”.

1. Заповнити стовпчик “Відпрацьовано годин”:

- - виділити клітинку цього стовпчика для першого працівника (директора);

- - вставити формулу “=8*D4” (D4 містить кількість відпрацьованих днів);

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

УВАГА!!!

При автозаповненні отримані неправильні значення як результат відносної адресації (=8*D4). Тому, при авто заповненні потрібно використовувати абсолютну адресацію! При цьому використовується знак $ для фіксації стовпчика (перед буквою), рядка (перед цифрою) або одночасно стовпчика і рядка.

1. В зв’язку з неправильною адресацією при авто заповненні, змінити формулу обрахунку кількості відпрацьованих годин з “=8*D4” на “=8*D$4” (або на “=8*$D$4”). Для зміни типу адресації виконати наступне:

- - виділити перше значення (для директора);

- - встановити курсор в рядок формул (на координатах клітинки);

- - натиснути клавішу F4 на клавіатурі для зміни типу адресації;

- - натисніть Enter;

- - автозаповненням заповнити решту клітинок цього стовпчика.

1. Заповнити стовпчик “Нараховано всього” використовуючи формулу “=D8*E8/($D$4*8)” (де D8 – оклад першого в списку співробітника (директор); E8 – відпрацьовано годин першим в списку співробітником (директором); D4 – кількість відпрацьованих днів) та прийом автозаповнення.
2. Розрахувати дані стовпчика “Прибутковий податок”, використовуючи наступний умовний розрахунок:

якщо нараховано менше 200 грн., тоді прибутковий податок = 10% від нарахованої суми;

якщо ж нараховано більше як 200 грн., то на 200 грн. нараховується 10%, а на решту суми – 20%

- - виділити першу клітинку стовпчика “Прибутковий податок” (для директора);

- - натиснути на панелі інструментів кнопку , або вибрати “ВставкаÞФункция”;

- - у вікні “Мастер функцій” вибрати категорію “Логические” та функцію “Если”

- - натиснути “Ок”;

- - у вікні редагування функції вписати необхідні вирази (див. наступний рисунок) відповідно до умовного розрахунку, прийнятого раніше;

- - натиснути “Ок”.

1. Використовуючи формули та автозаповнення розрахувати для кожного з співробітників значення відрахувань у пенсійний фонд (2% від нарахованого всього), страхування по безробіттю (0,5% від нарахованого всього), соціального страхування (0,5% від нарахованого всього).
2. Підрахувати значення “Утримано всього”, як суму всіх відрахувань, та “До видачі” як різницю між “Нараховано всього” та “Утримано всього”. Також підвести підсумки по рядку Всього для всіх нарахованих та утриманих сум.
3. Прізвище бухгалтера для підпису відомості взяти як посилання на відповідну клітинку листа “Кадри”.

1. Самостійно змінити значення окладів на листі “Штат” та проаналізувати зміни на листі “Розрахунок ЗП”.