Міністерствоосвіти і науки України
Полтавськийнаціональний технічний університет
іменіЮрія Кондратюка
Факультетбудівельний
Кафедрабудівельної механіки
РОЗРАХУНКОВО– ГРАФІЧНА РОБОТА №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 та звіту підготовленому в текстовому редакторі MSWord.
При захистіроботи студент повинен відповісти на питання стосовно виконаної роботи та вмітикористуватись інструментами 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
/>електрик
/>/>4 1,39 2 столяр 5 1,54 3 муляр 6 1,7 4 штукатур 7 1,87 5 сантехнік 8 2,06 6 маляр 9 2,26
Поля «Довідниктарифних коефіціентів», «Довідник професії», заповнюються довільнимизначеннями.Значення у полі «№ п.п.» заповнюємо з клавіатури у зростанні від 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
Прибутковийподаток обчислюємо як 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с.