Алгоритм створення таблиць бази даних із наведенням відповідних таблиць книги MS Excel

Робоча книга – файл, створений табличним процесором MS Excel. Вона складається з окремих аркушів – листів, кількість яких визначається шаблоном. Вставити чи видалити аркуші у поточній книзі можна за допомогою команд Вставка → Лист та Правка → Удалить лист. Листи можна переміщувати та копіювати з одної книги в іншу. А також змінювати їх порядок в межах однієї книги.

Для того, щоб змінити ім’я аркуша на більш інформативні для користувача необхідно викликати контекстне меню на назві Лист 1 і виконати команду Переименовать. Ім’я кожного листа виводиться на ярличок у нижній частині робочої книги: «Лист1» – Довідники, «Лист2» – Відомість про робітників, «Лист3» – Розподіл, «Лист4» – Сортування, «Лист5» – Підсумки, «Лист6» – Автофільтр, «Лист7» – Розширений фільтр, «Лист8-Лист9» – Діаграми.

На аркуші «Довідники» створюємо два довідники: Довідник професій та Довідник тарифних коефіціентів (табл. 1.1).

Формат комірок і параметри даних задаються командою Формат → Ячейки:

· у вкладці Число вибираємо формат даних: текстовий, числовий (з потрібною кількістю десяткових знаків), і т.д.;

· у вкладці Выравнивание змінюємо орієнтацію тексту, вирівнюємо по горизонталі та вертикалі, об’єднуємо елементи таблиці, переносимо слова у межах комірки;

· у вкладці Шрифт урізноманітнюємо шрифтове оформлення робочого аркуша;

· у вкладках Границі та Вид створюємо контури і заливку комірок.

Таблиця 1.1

Поля «Довідник тарифних коефіціентів», «Довідник професії», заповнюються довільними значеннями. Значення у полі «№ п.п.» заповнюємо з клавіатури у зростанні від 1 до 6.

Захищаємо від змін аркуш «Довідники», знявши захист в таблиці «Довідник складу бригади»: Сервис→ Защита→ Защитить лист

На аркуші «Відомість про робітників» заповнюємо такі поля «Прізвище», «Ім’я», «Побатькові» з клавіатури.

Поля «Дата прийняття на роботу», «Дата народження» заповнюємо довільними значеннями (табл. 1.2).

Поле «стать» заповнюємо за допомогою функції =ЕСЛИ(ПРАВСИМВ(D4;1)="Ч";"чол.";"жін."), яка автоматично поставить стать згідно наших умов. Для визначення віку працівника застосовуємо функцію =РАЗНДАТ(F4;СЕГОДНЯ();"Y"). Заповнюємо «Стаж роботи повних років» функцією =РАЗНДАТ(K4;СЕГОДНЯ();"Y"), «Стаж роботи повних місяців» - =РАЗНДАТ(K4;СЕГОДНЯ();"YM"), «Стаж роботи повних місяців» - =РАЗНДАТ(K4;СЕГОДНЯ();"MD").

Поля «Розряд» та «Професія» заповнюються Данные → Проверкау віні «Проверка вводимых значений», закладка «Параметры» → «Тип данных»(Список), а в «Источник» можна казати діапазон даних або ввести текст (рис. 1.1).

Рис. 1.1. Заповнення таблиці

На аркуші «Розподіл» створюємо таблицю «Розподіл відрядного заробітку, нарахування заробітної плати та утримання із заробітної плати».

Значення у полі «№ п.п.» автоматично заповнюються відповідними номерами тільки при наявності значення у полі праворуч за допомогою формули з використанням функції ='Відомість про робітників'!В4.

Значення у полі «Прізвище та ініціали» формуємо із значень полів «Прізвище», «Ім’я» та «По батькові». Використовуємо функцію =СЦЕПИТЬ('Відомість про робітників'!B4;" ";ЛЕВСИМВ('Відомість про робітників'!C4;1);".";ЛЕВСИМВ('Відомість про робітників'!D4;1);".").

Поле «Кількість відпрацьованого часу» заповнюємо довільними значеннями.

«Тарифний коефіцієнт» обчислюється за функцією =ВПР('Відомість про робітників'!I4;тарифний_коефіцієнт;2;ИСТИНА). Для обрахування «Відпрацьований час приведений до 1-го розряду» ми множимо «Тарифний коефіцієнт» на «Кількість відпрацьованого часу» =D4*C4. Підбивши суму «Відпрацьований час приведений до 1-го розряду» та поділивши на «Відрядний заробіток», який даний по умові, ми одержимо «Відрядний заробіток на 1 люд.-год. 1-го розряду» =$C$2/$E$30. Перемноживши «Відрядний заробіток на 1 люд.-год. 1-го розриду» на «Відпрацьований час приведений до 1-го розряду» ми отримаємо «Розподілений відрядний заробіток» =$C$2/$E$30, сума якого дорівнює «Відрядний заробіток» Оскільки йдеться про грошові суми, то результат обчислень потрібно заокруглюємо до копійок, а також встановлюємо грошовий знак (грн.). Для перевірки правильності обчислень підбиваємо суму по цьому полю =СУММ(F4:F29).

Для обчислення поля «Всього нараховано» додаємо «Розподілений відрядний заробіток» до попередньо визначаємо значення у полі «Надбавка за стаж роботи» =ЕСЛИ('Облік працівників'!L4<5;0;ЕСЛИ('Облік працівників'!L4<=10;F4*0,1;F4*0,2)).

Прибутковий податок обчислюємо як 13% від „ Всього нараховано ”. Записуємо таку формулу =(ОТБР(H4;0))*0,13. Значення прибуткового податку повинні визначатися у грошових одиницях, тому у вікні Формат ячеек у вкладці Числовибираємо Финансовый форматз 2 десятичними знаками і встановлюємо позначення «грн. Украинский».

Відрахування в пенсійний фонд в сумі 2% від «Всього нараховано» (=H4*0,02), «Соц. страх.» − 1% від «Всього нараховано» (=H4*0,01), «Збір на

випадок безробіття» − 0,5% від «Всього нараховано» (=H4*0,005). «Проф-спілкові внески» − 1% від «Всього нараховано», в залежності від членства у профспілці =ЕСЛИ('Облік працівників'!J4="так";H4*0,01;0). Для того щоб отримати скільки «Всього утримано», додаємо всі відрахування =СУММ(I4:M4).

Зарплата робітника дорівнює різниці «Всього нараховано» та «Всього утримано» =H4-N4 (табл. 1.3).

Поиск по сайту: