Робота з таблицями баз даних в MS Excel

Міністерство освіти і науки України

Полтавський національний технічний університет

імені Юрія Кондратюка

Факультет будівельний

Кафедра будівельної механіки

РОЗРАХУНКОВО – ГРАФІЧНА РОБОТА №2

із дисципліни «Інформатика» на тему:

«Робота з таблицями баз даних

в MS Excel»

Залікова книжка

№ 07281

Виконав: студент гр. 110-Б

Смілик О.В.

Керівник:

Мартьянов В.В.

Полтава 2008

Завдання на розрахунково-графічну роботу № 2

Робота з таблицями баз даних в MS Excel

Строки виконання РГР № 2: початок 07.04.2008 р. закінчення 16.05.2008 р.

В табличному процесорові MS Excel розробити таблиці баз даних для розподілу відрядного заробітку між робітниками комплексної будівельної бригади та виконати аналіз даних.

Книга повинна містити наступні робочі аркуші.

1). Довідники. Скласти довідники професій і тарифних коефіцієнтів. Передбачити кількість будівельних професій 6. Розрядам 4, 5, 6, 7, 8, 9 відповідають тарифні коефіцієнти 1,39; 1,54; 1,70; 1,87; 2,06; 2,26.

2). Відомість про робітників. Розробити таблицю, яка має містити наступні поля: № п.п., прізвище, ім’я, по батькові, стать, дата народження (забезпечити введення даних між датами 01.01.1950р. і 01.01.1985р.), вік, професія, розряд, членство у профспілці, дата прийому на роботу, стаж роботи (визначається в повних роках, місяцях, днях). Кількість записів в таблиці відповідає чисельності бригади, що складає 20 чол.

3). Розподіл. Розробити таблицю розподілу суми відрядного заробітку – 25000 грн. між робітниками бригади та утримань з такими полями: № п.п., прізвище та ініціали, кількість відпрацьованого часу (забезпечити введення даних між 152 і 184 годинами), тарифний коефіцієнт, відпрацьований час приведений до робочого часу 1-го розряду, розподілений відрядний заробіток (при відсутності інших нарахувань співпадає з величиною суми “всього нараховано”); прибутковий податок (13% від суми “всього нараховано” без копійок), відрахування до пенсійного фонду (2% від суми “всього нараховано”), відрахування на соцстрахування (1% від суми “всього нараховано”), збір на випадок безробіття (0,5% від суми “всього нараховано”), профспілкові внески (1% від суми “всього нараховано”, в залежності від членства у профспілці), всього утримано, сума до видачі.

4). Сортування. За результатами розроблення відомості про робітників та таблиці розподілу створити таблицю з наступними полями: № п.п., прізвище та ініціали, стать, вік, професія, розряд, членство у профспілці, стаж роботи, кількість відпрацьованого часу, всього нараховано; прибутковий податок, відрахування до пенсійного фонду, відрахування на соцстрахування, збір на випадок безробіття, профспілкові внески, всього утримано, сума до видачі. Виконати сортування за сплаченим прибутковим податком.

5). Підсумки. На цьому аркушеві розмістити таблицю з аркуша Сортування. Виконати підведення проміжних підсумків за професією.

6). Автофільтр. На цьому аркушеві розмістити таблицю з аркуша Сортування. За допомогою автофільтру вибрати жінок, які є членами профспілки, віком від 27 до 42 років.

7). Розширений фільтр. На цьому аркушеві розмістити таблицю з аркуша Сортування. За допомогою розширеного фільтру вибрати чоловіків старших 40 років, а також робітників 2-го розряду, які сплачують до пенсійного фонду більше 23 грн.

8). Діаграма №1. Побудувати гістограму нарахованої суми.

9). Діаграма №2. Побудувати кільцеву діаграму середньої суми до видачі за членством у профспілці. Для цього необхідно створити допоміжну таблицю на окремому робочому аркушу.

У відповідних таблицях передбачити наявність робітників із даними, що задовольняють вимогам завдання щодо автофільтру та розширеного фільтру. З метою додаткової поінформованості, для комірок, в необхідних випадках (особливо для полів, що обчислюються), створити примітки.

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

За допомогою текстового редактора MS Word створити звіт про виконану розрахунково-графічну роботу, що повинен містити: титульну сторінку; бланк завдання; зміст; алгоритм створення таблиць бази даних із наведенням відповідних таблиць книги MS Excel; аналіз таблиць баз даних (сортування, автофільтр, розширений фільтр, проміжні підсумки); графічну частину роботи – задані діаграми та графіки і опис їх побудови; список використаних літературних джерел. Текст звіту оформлюється шрифтом типу Times New Roman розміром 14 кегель з полуторним інтервалом і повинен бути вирівняним по ширині з абзацним відступом 1,25 см. Всі сторінки повинні бути пронумеровані. Нумерація сторінок проставляється у правому верхньому куті аркуша. Титульна сторінка і бланк завдання включаються до нумерації сторінок, але номери на них не проставляються. Звіт повинен бути оформлений у відповідності з вимогами ДСТУ Б А.2.4–4–99 і роздрукований на аркушах формату А4. До звіту додаються електронні копії робочої книги з таблицями баз даних MS Excel та звіту підготовленому в текстовому редакторі MS Word.

При захисті роботи студент повинен відповісти на питання стосовно виконаної роботи та вміти користуватись інструментами MS Excel (форматування, обчислення, сортування, підсумки, фільтр та ін.).

Студент ______________________

Завдання видав ____________________

(підпис) (підпис)

Зміст

Вступ. 6

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

2. Аналіз таблиць баз даних. 13

2.1 Сортування. 13

2.2. Автофільтр. 13

2.3. Розширений фільтр. 14

2.4 Проміжні підсумки. 16

3. Графічна частина роботи. 18

Висновок. 23

Список використаних літературних джерел. 24

Вступ

Бухгалтер, економіст, інженер повсякденно має справу з інформацією, що представлена у вигляді таблиць як текстового, так і числового характеру. Швидке створення, заповнення, оформлення та друк таблиць у зручному вигляді, пошук сум чи середніх значень чисел, розміщених у стовпцях або рядках таблиці та більш складний аналіз даних – це ті завдання, які найкраще вирішувати за допомогою табличного процесора.

Мета: розробити книгу MS Excel для розподілу заробітної плати між окремими членами комплексної бригади та аналізу результатів розподілу згідно професії, розряду та відпрацьованого часу і т.д. Передбачити обчислення прибуткового податку та відрахувань в пенсійний фонд.

1. Алгоритм створення таблиць бази даних із наведенням відповідних таблиць книги 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).

2. Аналіз таблиць баз даних

2.1 Сортування

Для впорядкування таблиці розподілу потрібно скопіювати її на аркуш «Впорядкування» та виконати команду Данные → Сортировка і встановити задані параметри впорядкування.

Дані в таблиці можна упорядковувати за різними критеріями.

Поле «Кількість відпрацьованого часу» складається з текстових (символьних) даних. Їх впорядковують за їх кодами в таблиці символів в алфавіті відповідної мови (табл. 2.1).

Використовуючи списки полів «Сортировать по» та «Затем по» вибираємо поля по яким буде здійснюватися сортування. Потім встановлюється для кожного поля сортування необхідний перемикач «по возрастанию» або «по убыванию», перевіряємо правильність установки перемикача у розділі «Идентифицировать поля по» і натискаємо «ОК»

2.2 Автофільтр

Для застосування автофільтра копіюємо таблицю з аркушу «Сортування» на аркуш Автофільтр і виконуємо Данные → Фильтр → Автофильтр. Після цього встановлюємо задані умови фільтрації:

а) поле «Вік» відфільтроване з умовою «менше и равно 48» и «більше и равно 29» - відображає тільки ті записи, де вік менше 48 та більше 29.

б) поле «Розряд» відфільтроване за значенням «5» у списку значень у даному стовпці.

в) поле «Членство у профспілці» відфільтроване за значенням «ні» у списку значень у даному стовпця (табл. 2.2).

2.3. Розширений фільтр

Фільтрування даних за допомогою розширеного фільтра виконується у два етапи:

а) створення області критеріїв:

- Перший рядок: Стать – чол.; вік – >31;

- Другий рядок: розряд – 4-го; Соц. страх – >19

б) фільтрування даних:

Після створення області критеріїв виконуємо команди Данные→ Фильтр → Расширенный фильтр, які викликають вікно Расширенный

фильтр. У ньому вказуємо таблицю, з якої виконується відбір даних $A$7:$Q$33, визначається область критеріїв $A$2:$Q$4, діапазон у якому потрібно розмістити результати відбору $А$7:$О$33.

За допомогою фільтра в Excel можна вибирати значення, які відповідають заданим умовам, серед інформації, що збігаються у таблицях (табл. 2.3).

2.4 Проміжні підсумки

Проміжні підсумки розраховуються для полів, які мають значення, що повторюються. Копіюємо таблицю розподілу на аркуш «Підсумки» впорядковуємо за полем «Професія», виконуємо команду Данные → Итоги, встановлюємо необхідні параметри:

- у вікні діалогу Промежуточные итоги зі списку При каждом изменении вибираємо поле «Професія», по якому список розбитий на групи записів.

- у вікні Операция вибираємо функцію СУММ, яка використовується при визначенні проміжних підсумків.

- у полі Добавить итоги по вказуємо поле, за яким повинні бути визначені проміжні підсумки. Це поле «Відпрацьовано» (табл. 2.4).

3. Графічна частина роботи

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

Для побудови графічних відображень табличних даних використовуємо спеціальну графічну підсистему – майстра побудови діаграм, яка викликається командами Вставка [ Диаграмма. Майстер діаграм створює прототип діаграми за чотири кроки. Редагуємо цей прототип, використовуючи різноманітні можливості графічної підсистеми.

Крок 1. вибір типу діаграм.

Вибираємо з вкладок, в яких наведено перелік різноманітних стандартних і нестандартних типів діаграм та графіків тип діаграми, який відповідає умовам задачі і максимально спрощує сприйняття й аналіз даних.

Якщо натиснути на кнопку Просмотр результата, можна переглянути ескіз діаграми (рис. 3.1);

Рис. 3.1. Вибір типу діаграми

Крок 2. Джерело даних діаграми.

На цьому етапі визначаємо джерело даних (діапазон), якщо воно не було визначене раніше, та, за необхідності, видаляємо, додаємо ряди даних чи встановлюємо їх параметри. Параметри Имя та Подписи по оси Х можемо визначити як посилання на клітинки чи діапазони або текстовими константами (рис. 3.2);

Рис. 3.2. Діапазон діаграми

Крок 3. Параметри діаграм.

У наступному вікні є шість вкладок, у кожній із яких встановлюємо параметри певної складової діаграми:

- Заголовки – вводимо назву діаграми, підписи осі категорій, рядів та осі значень;

- Оси – вмикаємо чи вимикаємо відображення будь-якої осі;

- Линии сетки – відображаємо чи приховуємо сітку на відповідній координатній площині;

- Подписи данных – розміщуємо в області діаграми підписи даних у вигляді значень, імен чи відсотків;

- Таблица данных – розміщуємо поруч із діаграмою таблицю – джерело даних (рис. 3.3).

Рис. 3.3. Параметри діаграми

Крок 4. Розміщення діаграми.

У даному вікні вибираємо один з двох варіантів розміщення діаграми – на окремому аркуші діаграм чи на будь-якому з аркушів робочої книги і отримуємо побудовану діаграму (рис. 3.4).

Рис. 3.4. Розміщення діаграми

Лінійчасту діаграму будую із аркушу проміжні підсумки.

Висновок

Деякі види інформації необхідно відображати у вигляді таблиць. Особливо широко така структура даних застосовується у роботі з економічною інформацією. Саме для оброблення табличної інформації розроблені спеціальні програмні системи – табличні процесори. Глибоке засвоєння складової MS Office - табличного процесора MS Excel дає змогу:

· здійснювати оброблення табличних даних;

· відображати дані у графічному вигляді (як графіки та діаграми);

· працювати з базами даних, виконуючи сортування інформації, групування даних, відбір даних, що відповідають певним критеріям та ін.;

· здійснювати імпорт і експорт даних в інші програмні системи.

При роботі над темою курсової роботи «Робота з табличним процесором MS Excel» були опрацьовані такі розділи, як «Файл Excel і робоча книга», «Форматування таблиць MS Excel», «Введення і редагування даних», «Обчислення і використання формул в MS Excel», «Вбудовані функції Excel», «Діаграми і графіки в MS Excel», «Робота з таблицями Excel як із базою даних».

Список використаних літературних джерел

1. ДСТУ Б А.2.4-4-99 Основні вимоги до проектної та робочої документації.Київ,1999р. – 77с.

2. В.В.Браткевич, М.В.Бутов, І.О.Золотарьова, та ін. Інформатика. Комп’ютерна техніка. Комп’ютерні технології. К.: Академія, 2003

3. Экономическая информатика. Под редакцией П.В. Конюховского, Д.П. Колесова. Питер, 2001.

4. Информатика для юристов и экономистов. Под редакцией С.В. Симоновича. Питер, 2001.

5. Ильина О.П. Информационные технологии бухгалтерского учета. Спб, Питер, 2001.

6. В.Долженков, Ю. Колесников. Excel 2002. Спб. BHV, 2002.

7. В.А.Баженов, П.С.Венгерський, В.М.Горлач, та ін., К.: Каравела, 2003. Бернс П., Бэрроуз Э. Секреты Excel 97. К.: Диалектика, 1997.

8. Клименко В.И. Эффективный самоучитель работы на ПК. 2002.

9. Методичні вказівки для виконання курсової роботи "Використання Microsoft Office (текстового редактора Word97, табличного процесора Excel97). О.І. Корх,М.Є. Рогоза. 1999.

10. Методичні вказівки до виконання лабораторних робіт на тему: “Підготовка текстового документа у редакторі Microsoft Word”, О.А. Харченко, О.І. Сороковий. 2002.

11. Методичні вказівки до виконання лабораторних робіт з Інформатики і комп’ютерної техніки. Укладачі Харченко О.А., Сороковий О.І. Полтава, ПНТУ, 2003.

12. Самарский А.А., Гулин А.В. Численные методы: учебное пособие для студентов вузов. - М.:Наука, 1989. - 318с.

13. Турчак Л.И. Основы численных методов: Учеб. пособие для студентов вузов. - М.:Наука, 1987. - 318с.