Лабораторна робота №5, Програмне забезпечення АІС
Код роботи: 1285
Вид роботи: Лабораторна робота
Предмет: Інформаційні системи і технології в управлінні
Тема: №5, Програмне забезпечення АІС
Кількість сторінок: 1
Дата виконання: 2016
Мова написання: українська
Ціна: 150 грн (за Excel-файл)
Мета. Оволодіння технологією побудови електронних таблиць для розрахунку заробітної плати.
Завдання для лабораторних занять і самостійної роботи
1. Підготувати таблиці «Тарифна ставка» і «Картотека» для розрахунку відрядної зарплати. Створити іменовані блоки клітинок:
Створити нову книгу.
Зберегти її з ім'ям Приклад.хls.
Перейменувати Лист1 в Тарифи.
На листі Тарифи, починаючи з клітинки А1, створити таблицю:
|
Розряд |
Коефіцієнт |
Ставка |
Величина ставки 1 розряду |
|
1 |
1 |
|
3,67 |
|
2 |
1,35 |
|
|
|
3 |
1,57 |
|
|
|
4 |
1,85 |
|
|
|
5 |
2,12 |
|
|
|
6 |
2,57 |
|
|
Створити іменовані блоки клітинок для стовпців Розряд, Коефіцієнт, Ставка. Для кожного стовпця слідує:
Виділити всі клітинки стовпця, включаючи заголовок стовпця.
Виконати команду Вставка > Ім'я > Створити > По тексту в рядку вище.
Примітка. Можна відразу виділити всі клітинки стовпців Розряд, Коефіцієнт і Ставка і виконати команду меню Вставка > Ім'я > Створити > По тексту в рядку вище.
Розрахувати поле Ставу по формулі: =Кофіціент*Величина ставки 1 розряду
Вставити новий лист за допомогою команди меню Вставка > Лист.
Перейменувати новий лист за допомогою команди меню Формат > Лист > Перейменувати, вказати — Картотека.
На листі Картотека, починаючи з осередком А1, створити таблицю.
|
ФІО |
Табельний № |
Професія |
Розряд працюючого |
Тариф |
Пільги |
|
Іванов А.П. |
01234 |
Кладовщик |
|
|
1 |
|
Колесов В.И. |
02345 |
Грузчик |
|
|
1 |
|
Крилов А.Р. |
00127 |
Грузчик |
|
|
2 |
|
Михайлов П.Р. |
12980 |
Грузчик |
|
|
2 |
|
Смірнов И.А. |
13980 |
Кладовщик |
|
|
3 |
|
Соколов Р.В. |
21097 |
Прибиральник |
|
|
1 |
Перед заповненням клітинок задати текстовий формат для клітинок стовпця Табельний №:
Створити іменовані блоки клітинок для стовпців ФІО, Табельний №, Професія, Розряд працюючого, Тариф, Пільги — команда меню Вставка > Ім'я > Створити > По тексту в рядку вище.
Перевірити визначення блоків всіма способами:
Вибрати блоки в списку імен клітинок (зліва від рядка формул).
Виконати команду меню Правка > Перейти.
В діалоговому вікні Перехід вибрати будь-яке ім'я поля і натискувати ОК.
Зберегти підготовлені таблиці за допомогою команди меню Файл > Зберегти.
2. Задати умови перевірки даних в осередки стовпця, що вводяться, Розряд працюючого таблиці Картотека. Значення розряду працюючих, що вводяться, повинні відповідати тарифним розрядам:
Відкрити файл Приклади.xls за допомогою команди меню Файл > Відкрити.
Вибрати лист Картотека.
Виділити всі осередки стовпця Розряд працюючого — клацнути лівою кнопкою миші на заголовку стовпця з ім'ям В.
Виконати команду меню Дані > Перевірка.
На вкладці Параметри встановити: тип даних — Список, джерело — Розряд (виклик списку іменованих блоків — Е3).
На вкладці Повідомлення для введення задати: Заголовок — Розряд працюючого, Повідомлення — Розряд вибирається з таблиці Тарифна ставка.
На вкладці Повідомлення про помилку задати: Вигляд — Останов, Заголовок - Невірне введення!, Повідомлення — Необхідно вказати число в діапазоні від 1 до 6!.
Натискувати кнопку ОК.
Заповнити осередки стовпця Розряд працюючого наступними даними 3, 3, 4, 3, 4, 2.
Закрити файл із збереженням за допомогою команди меню Файл
Закрити.
3. Заповнити список Картотека за допомогою екранної форми. Розряд працюючого визначає його тарифну ставку:
Відкрити файл Приклади.xls за допомогою команди меню Файл > Відкрити.
Вибрати лист Картотека.
Встановити курсор в осередок Е2 і ввести формулу тарифу: =ПРОСМОТР(В2;Разряд; Ставка).
Скопіювати формулу осередку Е2 в осередки стовпця Тариф (для заповнених рядків списку).
4. Побудувати зведену таблицю для розрахунку місячної зарплати робітників при почасовій формі оплати праці (Встановлена премія по розрядах працюючих: 2 розряд 25 %, 3 розряд 32 %, 4 розряд 50 % до тарифу. Вирахування зі всіх видів нарахувань складають 3,6 % , 15%):
Відкрити файл Приклади.xls за допомогою команди меню Файл > Відкрити.
Вибрати лист Картотека.
Встановити курсор в область даних на листі Картотека.
Виконати команду меню Дані > Зведена таблиця для виклику Майстра зведених таблиць і діаграм.
На кроці 1 вказати тип джерела — Створити таблицю на основі даних, що знаходяться: в списку або базі даних MS Excel.
Вид створюваного звіту: зведена діаграма (із зведеною таблицею).
На кроці 2 перевірити діапазон клітинок для побудови зведеної таблиці для списку Картотека.
На кроці 3 — натискувати кнопку Макет, розмістити поля в макеті зведеної таблиці:
Сторінка — Професія, рядок — Розряд працюючого, стовпець — ФІО, дані — Тариф
Натискувати кнопку ОК.
Задати параметри зведеної таблиці за допомогою кнопки Параметри: Загальна сума по стовпцях; Автоформат; Зберігати форматування.
Помістити таблицю в: новий лист.
Натискувати на кнопку Готово.
Зведена таблиця представлена на рис. 1.
Рис. 1 - Зведена таблиця для списку Картотека
Встановити курсор в область зведеної таблиці.
На панелі інструментів Зведені таблиці натиснути кнопку Зведена таблиця, вибрати команду Формули > Обчислюване поле. В діалоговому вікні Вставка обчислюваного поля створити нове обчислюване поле (рис. 2):
Ім'я поля — Зарплата, формула: =Тариф*168 (Ім'я Тариф вибирається із списку Поля: подвійним клацанням лівої кнопки миші).
Натиснути кнопку Додати.
Закрити вікно — кнопка ОК.
Рис. 2 - Вікно настройки обчислюваного поля зведеної таблиці
Встановити курсор в область зведеної таблиці.
На панелі інструментів Зведені таблиці натискувати кнопку Зведена таблиця > Майстер. У вікні майстра зведених таблиць на 3-у кроці натискувати кнопку Макет:
Прибрати з області Дані поле Сума по полю Тариф.
Натискувати кнопку ОК.
Натискувати кнопку Готово.
Встановити курсор в області зведеної таблиці на полі Сума по полю Зарплата.
На панелі інструментів Зведена таблиця натискувати кнопку Зведена таблиця > Параметри поля (мал. 3):
Змінити ім'я поля — Місячна зарплата.
Натискувати кнопку Формат і вибрати формат поля — Грошовий, Число десяткових знаків: 2.
Натискувати кнопку ОК.
Рис. 3 - Вікно настройки поля зведеної таблиці
Встановити курсор в області зведеної таблиці на полі Розряд працюючого.
На панелі інструментів Зведені таблиці натискувати кнопку Зведена таблиця.
Виконати команду Формули > Обчислюваний об'єкт:
ввести ім'я поля — Премія;
у вікно формули ввести формулу: ='2'*0,25+'3'*0,32+' 4'*0,5; (значення '2', '3', '4' вибирати подвійним клацанням з поля Елементи діалогового вікна Вставка обчислюваного елемента);
натискувати кнопку Додати;
ввести ім'я поля — Вирахування;
вибрати поле Розряд працюючого, розкрити список елементів (подвійне клацання лівою кнопкою миші на полі);
ввести формулу: =-0,13*('2'*1,25+'3'*1,32+'4'*1,5); (значення '2', '3', '4' вибирати подвійним клацанням з поля Елементи діалогового вікна Вставка обчислюваного елемента);
натискувати кнопку Додати;
натискувати кнопку ОК.
Для перегляду виразу обчислюваного поля і обчислюваних об'єктів необхідно встановити курсор в область зведеної таблиці, на панелі інструментів Зведені таблиці натискувати кнопку Зведена таблиця, вибрати команду Формули > Вивести формули (рис. 4).
Рис. 4 - Лист формул зведеної таблиці
Примітка. Якщо буде потрібно змінити нормативи (кількість відпрацьованого годинника, відсоток премії, відсоток вирахувань), слід повторити редагування обчислюваних полів і об'єктів — вибрати команду меню Формули > Вивести формули, викликати поле або об'єкт, внести зміни.
Встановити курсор в зведену таблицю.
На панелі інструментів Зведені таблиці натискувати кнопку Зведена таблиця > Майстер. У вікні третього кроку за допомогою кнопки Макет змінити положення полів (рис. 5):
Рядок — ФІО, стовпець — Розряд працюючого.
Натискувати кнопку ОК.
Натискувати кнопку Готово.
Рис. 5 - Зведена таблиця Зарплата робітників
Встановити курсор в зведену таблицю.
На панелі інструментів Зведені таблиці натискувати кнопку Майстер діаграм для побудови діаграми (рис. 6).
Закрити робочу книгу із збереженням за допомогою команди меню Файл > Закрити.
Рис. 6 - Діаграма зведеної таблиці Зарплата
5. Консолідація даних
Відкрити файл Приклади.xls за допомогою команди меню Файл > Відкрити.
Додати новий лист, якщо листів в книзі недостатньо.
Заповнити дані таблиці.
Встановити курсор на новий лист в новій книзі.
Виконати команду меню Дані > Консолідація:
вибрати функцію підсумку — Середнє;
вказати в полі Посилання діапазон клітинок таблиць Таблиця 1 для кожної робочої книги;
використовувати як імена підпису верхнього рядка і значення лівого стовпця;
створювати зв'язки з початковими даними;
натискувати кнопку ОК.
Закрити робочу книгу із збереженням за допомогою команди меню Файл > Закрити.
Детальніше дивіться методичні рекомендації: