Методичні рекомендації до лабораторної роботи 5 на тему Програмне забезпечення АІС, ХНТУ
« НазадЛабораторна робота 5. Програмне забезпечення АІС
Мета. Оволодіння технологією побудови електронних таблиць для розрахунку заробітної платиКороткі теоретичні відомості Побудова зведених таблиць у програмі МЗ ЕхсеІ Зведена таблиця забезпечує формування зведеної (агрегованої) інформації і представлення табличних даних в структурованому вигляді, а також побудову пов'язаної із зведеною таблицею зведеної діаграми. Джерелами даних для формування зведеної таблиці є:
Примітка. Якщо джерело даних для побудови зведеної таблиці — список (база даних) Місгояо/і ЕхсеІ, зведена таблиця розміщується в тій же книзі. Якщо джерело — діапазон клітинок, що консолідуються, вони можуть знаходитися на різних листах однієї робочої книги і навіть в різних робочих книгах. Майстер зведених таблиць
Побудова зведеної таблиці здійснюється за допомогою Майстра зведених таблиць, який викликається командою меню Дані > Зведена таблиця. Робота майстра зведених таблиць виконується по кроках: • перший крок — вибір типу джерела даних і виду результату; • другий крок — вказівка діапазону клітинок для побудови зведеноїтаблиці. • третій крок — формування макета (структури) зведеної таблиці — кнопка Макет і настройка параметрів зведеної таблиці — кнопка Параметри. Макет зведеної таблиці включає області (рис. 17): • Сторінка — для розміщення полів фільтрації (відбору) записів, що відображаються в зведеній таблиці. • Рядок — для розміщення полів групування, враховується послідовність полів для створення вкладених груп, підгруп і т. д. • Стовпець — для розміщення полів групування, враховується послідовність полів для створення вкладених груп, підгруп і т.д. • Дані — для розміщення полів підсумків. При побудові макета зведеної таблиці використовуються наступні правила: • будь-яке поле розміщується одноразово в області групування (сторінка, рядок або стовпець); • в області Дані розміщуються тільки ті поля, які не ввійшли в область групування; • будь-яке поле з області Дані може багато разів розміщуватися в цій області для обчислення різних підсумків. Зведені таблиці є вельми могутнім і гнучким інструментом аналізу в середовищі МІСГ080Й ЕхсеІ. Основні обмеження для зведених таблиць представлені в табл. 21. Таблиця 21 Обмеження для зведених таблиць
Обчислювані поля і елементи зведеної таблиці
Крім «базових полів» з основної таблиці (списку), зведена таблиця допускає формування обчислюваних полів в області Дані, а також обчислюваних елементів для полів групування. Елементи полів групування можуть об'єднуватися в групи, для яких можна вказати тип підсумку, утаєння/відображення деталей підлеглих полів. Зведена таблиця має набір параметрів, що впливають на зовнішнє уявлення. Кнопка Параметри викликає діалогове вікно настройки формату зведеної таблиці і даних зовнішнього джерела (Рис. 18). Зведена таблиця, побудована на основі списку (бази даних), розміщується в тій же робочій книзі, що і список, на будь-якому листі, при цьому не допускається перекриття зведеної таблиці і заповнених клітинок на листі. Зведена таблиця, побудована на основі консолідації діапазону клітинок, може розміщуватися в будь-якій робочій книзі, у тому числі нової. Зведена таблиця, побудована на базі іншої зведеної таблиці, розміщується в тій же робочій книзі, що і початкова, може знаходитися на іншому листі. Панель інструментів Зведені таблиціКнопка Зведена таблиця викликає меню команд:
Додаткова обробка полів зведеної таблиці
Елементи поля угрупування в рядку або стовпці зведеної таблиці можна відсортувати. Для цього в зведеній таблиці курсором виділяється ім'я поля угрупування, виконується команда меню Дані > Сортування. Можна виділити певні елементи полів групування в області рядків або стовпців, у тому числі і несуміжні, для їх об'єднання в нові групи за допомогою команди меню Дані > Групи і структура > Групувати. В результаті створюється більш високий рівень ієрархії — нове поле зведеної таблиці, яке можна додатково набудувати. Наприклад, можна згрупувати стовпці зведеної таблиці в дві групи: НАРАХУВАННЯ (включає стовпці з номерами розрядів працюючих 2, 3, 4, 5 і стовпець Премія) і УТРИМАННЯ (включає тільки стовпець Вирахування). Кожне групове поле можна набудувати окремо: змінити ім'я, вибрати функцію підсумку за допомогою команди Параметри поля. Примітка. Якщо елементи мають числове значення або тип дати, автоматично визначається діапазон 'їх значень і пропонується крок угрупування, який можна змінити. Для поля дат крок угрупування: декілька днів, місяць, квартал, рік; для числових полів — число. Для відмови від угрупування виконується команда меню Дані > Група і структура > Розгрупувати. На додаток до групування елементів полів існує можливість відображення/утаєння окремих елементів. Можна змінювати, додатково настроювати підсумки і поля зведеної таблиці (перейменувати поля, групувати значення полів, формувати додаткові підсумки). В діалоговому вікні Обчислення поля зведеної таблиці, що викликається за допомогою команди контекстного меню Параметри поля, кнопка Додатково виводить список додаткових видів обробки поля (табл. 22). Таблиця 22 Види додаткової обробки поля
Завдання для лабораторних занять і самостійної роботи
1. Підготувати таблиці «Тарифна ставка» і «Картотека» для розрахунку відрядної зарплати. Створити іменовані блоки клітинок:
Примітка. Можна відразу виділити всі клітинки стовпців Розряд, Коефіцієнт і Ставка і виконати команду меню Вставка > Ім'я > Створити > По тексту в рядку вище.
Перед заповненням клітинок задати текстовий формат для клітинок стовпця Табельний №:
2. Задати умови перевірки даних в осередки стовпця, що вводяться, Розряд працюючого таблиці Картотека. Значення розряду працюючих, що вводяться, повинні відповідати тарифним розрядам: • Відкрити файл Приклади.ХЬЗ за допомогою команди меню Файл > Відкрити. • Вибрати лист Картотека. • Виділити всі осередки стовпця Розряд працюючого — клацнути лівою кнопкою миші на заголовку стовпця з ім'ям В. • Виконати команду меню Дані > Перевірка.• На вкладці Параметри встановити: тип даних — Список, джерело — Розряд (виклик списку іменованих блоків — Е3). • На вкладці Повідомлення для введення задати: Заголовок — Розряд працюючого, Повідомлення — Розряд вибирається з таблиці Тарифна ставка. • На вкладці Повідомлення про помилку задати: Вигляд — Останов, Заголовок - Невірне введення!, Повідомлення — Необхідно вказати число в діапазоні від 1 до 6!. • Натискувати кнопку ОК. • Заповнити осередки стовпця Розряд працюючого наступними даними 3, 3, 4, 3, 4, 2. • Закрити файл із збереженням за допомогою команди меню Файл >Закрити.
3. Заповнити список Картотека за допомогою екранної форми. • Відкрити файл Приклади.ХЬЗ за допомогою команди меню Файл > Відкрити. • Вибрати лист Картотека. • Встановити курсор в осередок Е2 і ввести формулу тарифу: =ПРОСМОТР(В2;Разряд; Ставка). • Скопіювати формулу осередку Е2 в осередки стовпця Тариф (для заповнених рядків списку).
4. Побудувати зведену таблицю для розрахунку місячної зарплати робітників при почасовій формі оплати праці (Встановлена премія по розрядах працюючих: 2 розряд 25 %, 3 розряд 32 %, 4 розряд 50 % до тарифу. Вирахування зі всіх видів нарахувань складають 13 %): • Відкрити файл Приклади.ХЬЗ за допомогою команди меню Файл > Відкрити. • Вибрати лист Картотека. • Встановити курсор в область даних на листі Картотека. • Виконати команду меню Дані > Зведена таблиця для виклику Майстра зведених таблиць і діаграм. • На кроці 1 вказати тип джерела — Створити таблицю на основі даних, що знаходяться: в списку або базі даних Місгояо/і ЕхсеІ. Вид створюваного звіту: зведена діаграма (із зведеною таблицею).
Примітка. Якщо буде потрібно змінити нормативи (кількість відпрацьованого годинника, відсоток премії, відсоток вирахувань), слід повторити редагування обчислюваних полів і об'єктів — вибрати команду меню Формули > Вивести формули, викликати поле або об'єкт, внести зміни.
Структуризація таблицьСтруктуризація таблиць, що містять значне число рядків і стовпців, полягає у виділенні рівнів ієрархії для представлення інформації. Кожному рівню ієрархії на листі робочої книги Місгозой ЕхсеІ відповідає своя спеціальна кнопка, за допомогою якої можна приховати або відобразити вміст рівня. Максимальний рівень вкладеності в структурній таблиці - 8. Для створення структури заздалегідь виділяється група рядків або стовпців, виконується команда меню Дані > Група і структура > Групувати. Якщо в таблиці знаходяться формули, що використовують як операнди посилання на осередки поточного рядка або стовпця, то команда меню Дані > Група і структура > Створити структуру створює структурну таблицю автоматично. Команда меню Дані > Група і структура > Настройки дозволяє виконати настройки структури:
Для видалення структури слід виконати повторне виділення рядків/стовпців і вьібрять команду Дані > Група і структура > Розгрупувати. Для автоматично створених структурних таблиць зворотне перетворення виконується командою Дані > Група і структура > Видалити структуру. Приклад Підготувати початкову таблицю, на її основі створити структурну таблицю.
• Ввести в елементи таблиці розрахункові формули: • Для контролю правильності обчислень створюються допоміжні стовпці: Сума 1 і Контроль. Формули перевірки даних: 04: =СУММ(Б4:Р4) Н4: =ЕСЛИ(04=С4;мм;мОшибкам) 014: =СУММ(Б14:Р14) Н14: =ЕСЛИ(04=С14;мм;мОшибкам) • Виконати команду меню Дані > Група і структури > Створення структури. Зовнішній вигляд структурної таблиці в закритому вигляді показаний на рис.
Консолідація даних
Консолідація — особливий спосіб обчислення підсумків для діапазону клітинок. Дані, що консолідуються, можуть знаходитися на одному і тому ж або декількох листах однієї робочої книги, а також різних робочих книг. При консолідації доступні всі функції статистичних підсумків (сума, максимум, мінімум, середнє і т. д.). Результат консолідації записується на лист робочої книги. На одному листі може бути представлено декілька результатів консолідації для одних і тих же діапазонів клітинок з різними функціями підсумків. Якщо діапазони клітинок розрізняються, результат консолідації повинен бути представлений на різних листах робочої книги. Розрізняють два види консолідації:
Консолідація може здійснюватися з підтримкою зв'язку з джерелами. В структурній таблиці при зміні початкових даних автоматично обчислюються нові підсумки. При виконанні команди меню Дані > Консолідація виводиться діалогове вікно Консолідація, в якому вибирається функція підсумку, указуються діапазони клітинок, що консолідуються. Для додавання чергового діапазону клітинок потрібно натискувати «червону кнопку». При цьому можливий перехід на інші листи книги, а за допомогою команди Вікно — в інші відкриті робочі книги для виділення клітинок. Діалогове вікно Консолідація ПрикладПідготувати консолідований звіт про прибутки і збитки за ряд періодів (рис). На листі робочої книги, починаючи з осередком А1, знаходиться таблиця
яка містить формули; С6: =С2-С3-С4-С5 С12: =Сб+С7-С8+С9+С 10-С11 С15: =С12+С13-С14 С16: =0,23*С15 С18: =С15-С16-С17 Б6: =Б2-03-04-05 Б12: =В6+В7-08+В9+Б10-011 Б15: =С12+С13-С14 Б16: =0,23*С15 Б18: =Б15-Б16-Б17
Контрольні питання
З повагою ІЦ “KURSOVIKS”! |