Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
PAGE 7
Лабораторна робота 3
Тема
Програмне забезпечення АІС
Мета
Оволодіння технологією побудови електронних таблиць для розрахунку заробітної плати.
Лабораторне завдання:
1 Ознайомитися з короткими теоретичними відомостями по темі.
2 Ознайомитися з можливостями програми MS Excel для розрахунку заробітної плати.
3 Виконати завдання лабораторної роботи.
4 Роздрукувати звіт та оформити роботу.
Обладнання:
1 ПЕОМ ІВМРС
2 Програма MS Excel.
Короткі теоретичні відомості
Побудова зведених таблиць у програмі MS Excel
Зведена таблиця забезпечує формування зведеної (агрегованої) інформації і представлення табличних даних в структурованому вигляді, а також побудову пов'язаної із зведеною таблицею зведеної діаграми. Джерелами даних для формування зведеної таблиці є:
· список (база даних) Microsoft Excel;
· зовнішнє джерело даних (текстовий файл, що містить табличні дані,
реляційна база даних);
Примітка. Якщо джерело даних для побудови зведеної таблиці — список (база даних) Microsoft Excel, зведена таблиця розміщується в тій же книзі. Якщо джерело — діапазон клітинок, що консолідуються, вони можуть знаходитися на різних листах однієї робочої книги і навіть в різних робочих книгах.
Майстер зведених таблиць
Побудова зведеної таблиці здійснюється за допомогою Майстра зведених таблиць, який викликається командою меню Дані > Зведена таблиця. Робота майстра зведених таблиць виконується по кроках:
· перший крок — вибір типу джерела даних і виду результату;
· другий крок — вказівка діапазону клітинок для побудови зведеної
таблиці.
· третій крок — формування макета (структури) зведеної таблиці —кнопка
Макет і настройка параметрів зведеної таблиці — кнопка
Параметри.
Макет зведеної таблиці включає області (рис. 1):
Рис. 1. Макет зведеної таблиці
· Сторінка — для розміщення полів фільтрації (відбору) записів, що відображаються в зведеній таблиці.
· Рядок — для розміщення полів групування, враховується послідовність полів для створення вкладених груп, підгруп і т.д.
· Стовпець — для розміщення полів групування, враховується послідовність полів для створення вкладених груп, підгруп і т.д.
· Дані — для розміщення полів підсумків.
При побудові макета зведеної таблиці використовуються наступні правила:
· будь-яке поле розміщується одноразово в області групування (сторінка, рядок або стовпець);
· в області Дані розміщуються тільки ті поля, які не ввійшли в область групування;
· будь-яке поле з області Дані може багато разів розміщуватися в цій області для обчислення різних підсумків.
Зведені таблиці є вельми могутнім і гнучким інструментом аналізу в середовищі Microsoft Excel. Основні обмеження для зведених таблиць представлені в табл. 1.
Таблиця 1 Обмеження для зведених таблиць
|
Параметр |
Значения |
|
Число зведених таблиць на одному листі |
Обмежується об'ємом доступної оперативної пам'яті |
|
Число елементів зведеної таблиці |
8 000 |
|
Число полів рядків або стовпців в зведеній таблиці |
Обмежується об'ємом доступної оперативної пам'яті |
|
Число полів сторінки в зведеній таблиці |
256 (може обмежуватися об'ємом доступної оперативної пам'яті) |
|
Число полів даних в зведеній таблиці |
256 |
|
Число формул обчислюваних елементів оперативної пам'яті |
Обмежується об'ємом доступної в зведеній таблиці |
Обчислювані поля і елементи зведеної таблиці
Крім «базових полів» з основної таблиці (списку), зведена таблиця допускає формування обчислюваних полів в області Дані, а також обчислюваних елементів для полів групування. Елементи полів групування можуть об'єднуватися в групи, для яких можна вказати тип підсумку, утаєння /відображення деталей підлеглих полів.
Зведена таблиця має набір параметрів, що впливають на зовнішнє уявлення. Кнопка Параметри викликає діалогове вікно настройки формату зведеної таблиці і даних зовнішнього джерела (Рис. 2).
Рис. 2. Параметри зведеної таблиці
Зведена таблиця, побудована на основі списку (бази даних), розміщується в тій же робочій книзі, що і список, на будь-якому листі, при цьому не допускається перекриття зведеної таблиці і заповнених клітинок на листі. Зведена таблиця, побудована на основі консолідації діапазону клітинок, може розміщуватися в будь-якій робочій книзі, у тому числі нової. Зведена таблиця, побудована на базі іншої зведеної таблиці, розміщується в тій же робочій книзі, що і початкова, може знаходитися на іншому листі.
Панель інструментів Зведені таблиці
Для роботи із зведеними таблицями виводиться панель інструментів Зведені таблиці (Рис. 3).
Рис.3. Панель інструментів Зведені таблиці
Кнопка Зведена таблиця викликає меню команд:
· Формат звіту зведеної таблиці для вибору одного з 10 автоформатів
для звіту зведеної таблиці.
· Зведена діаграма для побудови діаграми для зведеної таблиці.
· Майстер для переходу до 3-му кроку побудови зведеної таблиці, який
забезпечує зміну макета, настройку параметрів, вибір місця розміщення
зведеної таблиці.
· Відновити дані зведеної таблиці при зміні інформації джерела.
· Настройка клієнт-серверу при використовуванні OLAP-куба для
побудови зведеної таблиці.
· Виділити окремі частини зведеної таблиці: заголовки, дані або зведену
таблицю цілком.
· Формули для створення обчислюваних полів в області даних або
обчислюваних об'єктів/елементів в області заголовків.
· Параметри поля для настройки параметрів поля зведеної таблиці.
· Параметри таблиці для настройки параметрів (аналог кнопки
Параметри).
· Відобразити сторінки зведеної таблиці на окремих листах книги.
Інші кнопки повторюють команди: Формат звіту, Майстер діаграм,Майстер зведених таблиць, Приховати/Відобразити деталі (для виділеного заголовка), Відновити дані, Параметри поля, Відобразити поля.
Додаткова обробка полів зведеної таблиці
Елементи поля угрупування в рядку або стовпці зведеної таблиці можна відсортувати. Для цього в зведеній таблиці курсором виділяється ім'я поля угрупування, виконується команда меню Дані > Сортування.
Можна виділити певні елементи полів групування в області рядків або стовпців, у тому числі і несуміжні, для їх об'єднання в нові групи за допомогою команди меню Дані > Групи і структура > Групувати. В результаті створюється більш високий рівень ієрархії — нове поле зведеної таблиці, яке можна додатково набудувати. Наприклад, можна згрупувати стовпці зведеної таблиці в дві групи: НАРАХУВАННЯ (включає стовпці з номерами розрядів працюючих 2, 3, 4, 5 і стовпець Премія) і УТРИМАННЯ (включає тільки стовпець Вирахування). Кожне групове поле можна набудувати окремо: змінити ім'я, вибрати функцію підсумку за допомогою команди Параметри поля.
Примітка. Якщо елементи мають числове значення або тип дати, автоматично визначається діапазон їх значень і пропонується крок угрупування, який можна змінити. Для поля дат крок угрупування: декілька днів, місяць, квартал, рік; для числових полів — число.
Для відмови від угрупування виконується команда меню Дані > Група і структура > Розгрупувати.
На додаток до групування елементів полів існує можливість відображення/утаєння окремих елементів. Можна змінювати, додатково настроювати підсумки і поля зведеної таблиці (перейменувати поля, групувати значення полів, формувати додаткові підсумки).
В діалоговому вікні Обчислення поля зведеної таблиці, що викликається за допомогою команди контекстного меню Параметри поля, кнопка Додатково виводить список додаткових видів обробки поля (табл. 2).
Таблиця 2 Види додаткової обробки поля
|
Вид обробки |
Зміст обробки |
|
Відмінність |
Різниця поточного значення поля з вказаним значенням поля |
|
Частка |
Відношення значень поля області даних до відповідних значень цього поля у вказаному рядку або стовпці зведеної таблиці, які задаються шляхом вибору поля групування і його окремого елемента |
|
Приведена відмінність |
Відношення різниці значень поля області даних і відповідних значень поля у вказаному рядку або стовпці зведеної таблиці |
|
З наростаючим підсумком в полі |
Наростаючий підсумок значень поля області даних у відповідних рядках або стовпцях зведеної таблиці, орієнтація задається шляхом вибору поля групування |
|
Частка від суми по рядку |
Значення поля області даних обчислюється у відсотках від підсумку рядка |
|
Частка від суми по стовпцю |
Значення поля області даних обчислюється у відсотках від підсумку cтовпця |
|
Частка від загальної суми |
Значення поля області даних обчислюється у відсотках від загального підсумку зведеної таблиці |
|
Індекс |
Значення поля області даних обчислюється але формулі: ((Значення поля) * (Загальний підсумок)) / ((Підсумок рядка) * (Підсумок стовпця)) |
Завдання для лабораторного заняття
1. Підготувати таблиці «Тарифна ставка» і «Картотека» для розрахунку відрядної зарплати. Створити іменовані блоки клітинок:
· Створити нову книгу.
· Зберегти її з ім'ям Примеры.xls.
· Перейменувати Лист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 за допомогою команди меню Файл
> Відкрити.
· Вибрати лист Картотека.
· Виділити всі осередки стовпця Розряд працюючого — клацнути
лівою кнопкою миші на заголовку стовпця з ім'ям D.
· Виконати команду меню Дані > Перевірка.
· На вкладці Параметри встановити: тип даних — Список, джерело — Розряд (виклик списку іменованих блоків — F3).
· На вкладці Повідомлення для введення задати: Заголовок —
Розряд працюючого, Повідомлення — Розряд вибирається з таблиці Тарифна ставка.
· На вкладці Повідомлення про помилку задати: Вигляд —Останов, Заголовок – Невірне введення!, Повідомлення —Необхідно вказати число в діапазоні від 1 до 6!.
· Натискувати кнопку ОК.
· Заповнити осередки стовпця Розряд працюючого наступними
даними 3, 3, 4, 3, 4, 2.
· Закрити файл із збереженням за допомогою команди меню Файл
> Закрити.
3. Заповнити список Картотека за допомогою екранної форми.
Розряд працюючого визначає його тарифну ставку:
· Відкрити файл Приклади.XLS за допомогою команди меню Файл
> Відкрити.
· Вибрати лист Картотека.
· Встановити курсор в осередок Е2 і ввести формулу тарифу: =ПРОСМОТР(D2;Разряд;Ставка).
· Скопіювати формулу осередку Е2 в осередки стовпця Тариф (для
заповнених рядків списку).
4. Побудувати зведену таблицю для розрахунку місячної зарплати
робітників при почасовій формі оплати праці (Встановлена премія по
розрядах працюючих: 2 розряд 25 %, 3 розряд 32 %, 4 розряд 50 % до
тарифу. Вирахування зі всіх видів нарахувань складають 13 %):
· Відкрити файл Приклади.XLS за допомогою команди меню Файл
> Відкрити.
· Вибрати лист Картотека.
· Встановити курсор в область даних на листі Картотека.
· Виконати команду меню Дані > Зведена таблиця для виклику
Майстра зведених таблиць і діаграм.
· На кроці 1 вказати тип джерела — Створити таблицю на основі
даних, що знаходяться: в списку або базі даних Microsoft Excel.
Вид створюваного звіту: зведена діаграма (із зведеною таблицею).
· На кроці 2 перевірити діапазон клітинок для побудови зведеної
таблиці для списку Картотека.
· На кроці 3 — натискувати кнопку Макет, розмістити поля в
макеті зведеної таблиці:
· Сторінка — Професія, рядок — Розряд працюючого, стовпець —
ФІО, дані — Тариф
· Натискувати кнопку ОК.
· Задати параметри зведеної таблиці за допомогою кнопки
Параметри: Загальна сума по стовпцях; Автоформат; Зберігати форматування.
· Помістити таблицю в: новий лист.
· Натискувати на кнопку Готово.
Зведена таблиця представлена на рис. 4.
Рис. 4. Зведена таблиця для списку Картотека
· Встановити курсор в область зведеної таблиці.
· На панелі інструментів Зведені таблиці натиснути кнопку Зведена таблиця, вибрати команду Формули > Обчислюване поле. В діалоговому вікні Вставка обчислюваного поля створити нове обчислюване поле (рис. 5):
· Ім'я поля — Зарплата, формула: =Тариф*168 (Ім'я Тариф вибирається із списку Поля: подвійним клацанням лівої кнопки миші).
· Натиснути кнопку Додати.
· Закрити вікно — кнопка ОК.
Рис. 5. Вікно настройки обчислюваного поля зведеної таблиці
· Встановити курсор в область зведеної таблиці.
· На панелі інструментів Зведені таблиці натискувати кнопку
Зведена таблиця > Майстер. У вікні майстра зведених таблиць
на 3-у кроці натискувати кнопку Макет:
· Прибрати з області Дані поле Сума по полю Тариф.
· Натискувати кнопку ОК.
· Натискувати кнопку Готово.
· Встановити курсор в області зведеної таблиці на полі Сума по
полю Зарплата.
· На панелі інструментів Зведена таблиця натискувати кнопку Зведена таблиця > Параметри поля (мал. 6):
· Змінити ім'я поля — Місячна зарплата.
· Натискувати кнопку Формат і вибрати формат поля — Грошовий, Число десяткових знаків: 2.
· Натискувати кнопку ОК.
Рис. 6. Вікно настройки поля зведеної таблиці
· Встановити курсор в області зведеної таблиці на полі Розряд
працюючого.
· На панелі інструментів Зведені таблиці натискувати кнопку
Зведена таблиця.
· Виконати команду Формули > Обчислюваний об'єкт:
· ввести ім'я поля — Премія;
· у вікно формули ввести формулу: ='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’ вибирати подвійним клацанням з поля Елементи діалогового вікна Вставка обчислюваного елемента);
· натискувати кнопку Додати;
· натискувати кнопку ОК.
· Для проглядання виразу обчислюваного поля і обчислюваних
об'єктів необхідно встановити курсор в область зведеної таблиці, на панелі інструментів Зведені таблиці натискувати кнопку Зведена таблиця, вибрати команду Формули > Вивести формули (рис. 7).
Рис. 7. Лист формул зведеної таблиці
Примітка. Якщо буде потрібно змінити нормативи (кількість відпрацьованих годин, відсоток премії, відсоток вирахувань), слід повторити редагування обчислюваних полів і об'єктів — вибрати команду меню Формули > Вивести формули, викликати поле або об'єкт, внести зміни.
· Встановити курсор в зведену таблицю.
· На панелі інструментів Зведені таблиці натискувати кнопку
Зведена таблиця > Майстер. У вікні третього кроку за допомогою кнопки Макет змінити положення полів (рис. 8): Рядок — ФІО, стовпець — Розряд працюючого. Натискувати кнопку ОК. Натискувати кнопку Готово.
Рис. 8. Зведена таблиця Зарплата робітників
меню Файл > Закрити.
Рис. 9. Діаграма зведеної таблиці Зарплата Структуризація таблиць
Структуризація таблиць, що містять значне число рядків і стовпців, полягає у виділенні рівнів ієрархії для представлення інформації. Кожному рівню ієрархії на листі робочої книги Microsoft Excel відповідає своя спеціальна кнопка, за допомогою якої можна приховати або відобразити вміст рівня. Максимальний рівень вкладеності в структурній таблиці - 8.
Для створення структури заздалегідь виділяється група рядків або стовпців, виконується команда меню Дані > Група і структура > Групувати. Якщо в таблиці знаходяться формули, що використовують як операнди посилання на осередки поточного рядка або стовпця, то команда меню Дані > Група і структура > Створити структуру створює структурну таблицю автоматично.
Команда меню Дані > Група і структура > Настройки дозволяє виконати настройки структури:
· вибрати місце вставки кнопок структури;
· використовувати автоматичні стилі структури;
· застосувати інші стилі.
Для видалення структури слід виконати повторне виділення рядків/стовпців і вы6рять команду Дані > Група і структура > Розгрупувати. Для автоматично створених структурних таблиць зворотне перетворення виконується командою Дані > Група і структура > Видалити структуру.
Контрольні питання
9.Де застосовуються електронні таблиці?
12. Які пакети відносяться до класу проблемно-орієнтованих?