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

Міністерство освіти і науки України Полтавський національний технічний університет імені Юрія Кондратюка Факультет будівельний Кафедра будівельної механіки

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

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

на тему: «Робота з таблицями баз даних в MS Excel»

Індивідуальний план № 9 256

Виконала студентка групи 111-Б Веклич М.Ю.

Керівник: Мартьянов В.В.

Полтава 2010

Зміст

• Вступ
• 1 Розрахункова частина роботи
• 1.1 Алгоритм створення таблиць бази даних із наведенням відповідних таблиць книги MS Excel
• 1.1.1 Довідники
• 1.1.2 Облік робітників
• 1.1.3 Розподіл відрядного заробітку
• 1.2 Аналіз таблиць баз даних
• 1.2.1 Сортування за кількістю відпрацьованих годин
• 1.2.2 Вибір жінок, які не є членами профспілки, віком від 25 до 40 років за допомогою автофільтра
• 1.2.3 Вибір чоловіків старших 40 років, а також робітників 2-го розряду, які сплачують профспілкові внески більше 16 грн. за допомогою розширеного фільтра
• 1.2.4 Проміжні підсумки за статями
• 2 Графічна частина роботи
• 2.1 Побудова кільцевої діаграми прибуткового податку
• 2.2 Побудова кругової діаграми максимального прибуткового податку за розрядами
• Висновок
• Список використаних літературних джерел

Вступ

База даних — це сукупність даних, яка організована у спеціальний спосіб.

Список — це таблиця прямокутної конфігурації, в якій стовбці - поля, рядки — записи.

Існують певні вимоги до списку, які представлені нижче.

1. Максимальні розміри обмежуються 256*65 536, де перша цифра відповідає кількості стовбців, які відведені для полів, а друга — кількості рядків для записів.

2. Список має бути відокремлений від інших даних хоча б одними незаповне-ними стовбцем і рядком.

3. Імена стовбців в списку мають розташовуватись у першому рядку таблиці, але можуть бути багаторядковими.

4. Комірки мають містити однорідну інформацію.

Функції списку — це функції, які обробляють дані таблиць. Для роботи з ними достатньо помістити курсор в довільну комірку в середині списку. Маємо зауважити, що функції не застосовують для несуміжних комірок. Також не рекомендується створювати декілька списків на одному робочому аркушеві і розміщувати формули поруч із ними.

Дані впорядковуються за певними критеріями, за ключовими словами. Сортування зазнають записи таблиці за зростанням (спаданням) значень вибраного ревізиту-ключа або кількох ключів одночасно з урахуванням або без урахування регістра літер. Швидке сортування табличних даних забезпечується двома однойменними кнопками-піктограмами. При цьому текстові дані розміщуються за алфавітом або за зворотним йому порядку.

Фільтрація — відбір певних даних, що потрібні користувачеві за певними умовами. Це можливість бачити не всю таблицю, а тільки ту частину, яка нам потрібна. Фільтри бувають двох типів: Автофільтр і Розширений фільтр.

Автофільтр може працювати з простими критеріями (одна умова), складними критеріями (максимум дві умови за одним полем) і складеними критеріями, які можуть містити стільки умов, скільки полів у списку, але при цьому використовують максимум дві умови за одним полем. Критерії заносяться під час роботи Автофільтра, а результати запиту для подальшого збереження користувач може самостійно скопіювати в інше місце поточного робочого аркуша або на новий аркуш.

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

Автофільтр не взмозі виконати вибірку даних за умовою ИЛИ.

1 Розрахункова частина роботи

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

1.1.1 Довідники

Першу сторінку переіменовуємо в Довідники.

Для встановлення зв’язку з даними інших аркушів цієї книги, для наступних діапазонів комірок привласнюємо такі імена:

1) В3-В7 (перший діапазон) — Професія;

2) D3-D8 — Розряд;

3) D3-E8 — Тариф._коеф.

Для привласнення імен виконуємо команду: Меню Вставка — Имя — П рисвоить, попередньо виділивши потрібний діапазон комірок (В3-В7). У вікні, що з’явилося вводимо в графу Имя з клавіатури Професія, у графі Формула перевіряємо правильність діапазону, натискаємо ОK. Наступним двом діапазонам привласнюємо імена аналогічно. Ім'я повинно вводитися одним словом (напр. Тариф._коеф.).

Рис. 1.1. Присвоєння імені

Складаємо довідники професій і тарифних коефіцієнтів.

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

Рис. 1.2. Створення довідників

1.1.2 Облік робітників

Наступну сторінку переіменовуємо в Облік робітників.

Створюємо таблицю, яка містить наступні поля: № п.п., прізвище, ім'я, побатькові, стать, дата народження, вік, професія, розряд, членство у профспілці, дата прийому на роботу, стаж роботи (визначається в повних роках, місяцях, днях). Кількість записів в таблиці відповідає чисельності бригади, що складає 21 чол.

З клавіатури вводимо назви таблиці і назви робітників. Заповнюємо записами поля: № п.п., прізвище, ім'я, побатькові, дата народження, дата прийому на роботу. Решту полів оброблюємо за допомогою формул MS Excel.

Щоб розрахувати записи в полі Стать натискаємо ліву клавішу миші у комірці E4. Так як у чоловіків по батькові закінчується на літеру «ч», формула для автоматичного визначення статі буде виглядати так: =ЕСЛИ (ПРАВСИМВ (D4,1)="Ч" ," чол." ," жін.").

За допомогою Автозаповнення заповнюємо інші комірки у графі Стать.

У полі Вік застосовуємо функцію =РАЗНДАТ (дата початку;дата кінця;форма представлення). Форма представлення — роки (Y). Так як вік — це різниця між сьогоднішньою датою і датою народження, то його можна визначити через наступну формулу:

=РАЗНДАТ (F4,СЕГОДНЯ ()," Y").

Для розрахунку записів у полях Професія, Розряд, Членство у профспілці виділяємо діапазон комірок (H4:H24). Натискаємо Меню — Данные — Проверка.

Після цього з’являється діалогове вікно (Рис. 1.3). У графі Тип данных обираємо Список, а у графі Источник з клавіатури вводимо =Професія і натискаємо OK. З правого боку з’являється кнопка, що дозволяє відкривати список і вибирати відповідні записи. Натискаючи на кнопку зі стрілкою, бачимо список запропонованих професій (Рис. 1.4). У полі Розряд комірки заповнюємо аналогічно, лише у графі Источник вводимо =Розряд. У полі Членство у профспілці комірки заповнюються також аналогічно, але у графі Источник з клавіатури вводимо так, ні, не ставлячи знака «=», так як у нас немає відповідного діапазону з присвоєним ім'ям.

Рис. 1.3. Створення списку Рис. 1.4. Список професій Для розрахунку записів у полі Стаж роботи ми використали функцію =РАЗНДАТ (дата початку; дата кінця; форма представлення). Форма представлення — в повних роках, місяцях, днях (Y, YM, MD відповідно). Так як стаж роботи — це різниця між сьогоднішньою датою і датою прийому на роботу, то формула для визначення стажу роботи у повних роках буде виглядати так:

=РАЗНДАТ (K4,СЕГОДНЯ ()," Y"),

а для двох сусідніх полів Y змінюється на YM, MD відповідно:

=РАЗНДАТ (K4,СЕГОДНЯ ()," YM"); =РАЗНДАТ (K4,СЕГОДНЯ ()," MD").

1.1.3 Розподіл відрядного заробітку

Маємо розробити таблицю (Табл. 1.2) розподілу суми відрядного заробітку — 22 000 грн. між робітниками бригади та утримань з полями, які дані у завданні.

Щоб створити записи в полі Прізвище І.П. нам треба зчепити разом прізвище і ініціали. Ініціали — це літери ім'я і по батькові, які є першими зліва, тому використовуємо таку формулу:

=СЦЕПИТЬ ('Облік робітників'!B4," «, ЛЕВСИМВ ('Облік робітників'!C4,1),» .", ЛЕВСИМВ ('Облік робітників'!D4,1)," ."). Всі записи у полі Кількість відпрацьованого часу вводяться з клавіатури, але щоб за-безпечити введення даних між 152 і 184 годинами, натискаємо Меню — Данные — Проверка… Після цього з’являється діалогове вікно (Рис. 1.5). У графі Тип данных обираємо Действительное, у графі Минимум з клавіатури вводимо =152, а у графі Максимум — =184 і натискаємо OK.

Рис. 1.5. Перевірка значень, що вводяться Тарифний коефіцієнт визначаємо за функцією ВПР. Знаходячись у комірці D4, викликаємо вищезгадану функцію. В графі Искомое значение переходимо на попередній аркуш Облік робітників, відповідна комірка I4 (що відповідає розряду); Таблица — Тариф._коеф. (з Довідників); Номер столбца — 2; Интервальный просмотр — Истина (як це показано на рис. 1.6.).

Рис. 1.6. Функція ВПР Формула має такий вигляд:

=ВПР ('Облік робітників'!I4,Тариф_коеф., 2, ИСТИНА).

Відпрацьований час приведений до I-го розряду шукаємо за формулою:

=D4*C4 (для комірки Е4).

Відрядний заробіток на 1 люд.-год. І-розряду знайдений за формулою: =$C$ 2/$E$ 25 (комірка F2).

Розподілений відрядний заробіток знайдений за формулою:

=E4*$F$ 2 (комірка F4).

При відсутності інших нарахувань Відрядний заробіток співпадає з величиною суми «всього нараховано».

Для визначення прибуткового податку, відрахування до пенсійного фонду, відрахування на соцстрахування, збору на випадок безробіття, які залежать від розмірів процентів (13%, 2%, 1%, 0,5% відповідно), застосовуємо функцію =ОТБР (число;число_разрядов). Так як число відповідає всьому нарахованому (в даному випадку відрядному заробітку), а число_разрядов = 0, формула виглядає так: =ОТБР (F4;0)*відсоток (0,13; 0,02; 0,01; 0,005) відповідно для чотирьох полів, які розглядаються). Наприклад, для комірки G4 (Прибутковий податок):

=(ОТБР (F4,0))*0.13.

Щоб вирахувати Профспілкові внески все нараховане множать на коефіцієнт 0,01 або 0, якщо людина входить або не входить до профспілки відповідно. Формула така:

=ЕСЛИ ('Облік робітників'!J4="так", F4*0.01,0).

Всього утримано вираховується за формулою:

=СУММ (G4:K4) (для комірки L4).

Сума до видачі - це різниця Розподіленого відрядного заробітку і Всього утримано. Відповідно для комірки М4 формула має вигляд:

=(F4-L4).

Табл. 1.2. Створення таблиці розподілу

• 1.2 Аналіз таблиць баз даних
• 1.2.1 Сортування за кількістю відпрацьованих годин
• Ми маємо згідно завдання за результатами розроблення відомості про робітників та таблиці розподілу створити нову таблицю (Табл. 1.3.).
• Записи в полях № п.п., прізвище та ініціали, стать, вік, стаж роботи, кількість відпрацьованого часу, всього нараховано, прибутковий податок, відрахування до пенсійного фонду, відрахування на соцстрахування, збір на випадок безробіття, профспілкові внески, всього утримано, сума до видачі і їх назви ми копіюємо з відомості про робітників та таблиці розподілу і вставляємо на потрібне нам місце таким чином:
• Натискаємо правою клавішею миші на місці вставки і у списку, що з’явився обираємо Специальная вставка. У вікні, що з’явилося ставимо прапорець навпроти Значения и форматы чисел і натискаємо OK (Рис. 1.7.).
• Копіювання записів в інших полях не потребують зміни параметрів.
• Рис. 1.7. Параметри спеціальної вставки
• Тепер ми маємо виконати сортування за кількістю відпрацьованих годин.
• 1. Виділяємо діапазон комірок (A2:Q23), так як до нього мають ввійти назви полів.
• 2. Натискаємо Меню — Данные — Сортировка.
• 3. У графі Сортировать по вибираємо Кількість відпрацьованих годин і натискаємо OK (Рис. 1.8)
• Рис. 1.8. Параметри сортування
• Рис. 1.9. Параметри автофільтра
• 1.2.2 Вибір жінок, які не є членами профспілки, віком від 25 до 40 років за допомогою автофільтра
• Створюємо новий аркуш MS Excel і називаємо його Автофільтр. Розміщуємо на ньому таблицю з аркуша Сортування.
• Виділяємо діапазон комірок (A2:Q23). В меню вибираємо Данные — Фильтр — Автофильтр. Натискаємо на кнопку зі стрілкою біля запису Стать і зі списку обираємо «жін.». Аналогічно з записом Членство у профспілці і зі списку обираємо ні. Натиснувши кнопку біля запису Вік зі списку вибираємо Условие. Для вибору людей віком від 25 до 40 років застосуємо Логічне зв’язування даних И. У графі Показать только те строки значения которых: нам треба вибрати або вписати больше 25 И меньше 40. Тому заповнюємо графи, як показано на Рис. 1.9.
• Після натиснення OK отримуємо Табл.1.4.
• 1.2.3 Вибір чоловіків старших 40 років, а також робітників 2-го розряду, які сплачують профспілкові внески більше 16 грн. за допомогою розширеного фільтра
• Створюємо новий аркуш MS Excel і називаємо його Розширений фільтр. Розміщуємо на ньому таблицю з аркуша Сортування, залишивши 4 пустих полів між нею і назвою. В перше пусте поле ми копіюємо заголовки всіх записів. В друге вписуємо під заголовком Стать ="чол.", а під заголовком Вік >40. В третє вписуємо під заголовком Розряд ="2″; під заголовком Профспілкові внески >16.
• Виділяємо діапазон (A6:Q27), в меню натискаємо Данные — Фильтр — Расширенный фильтр. У вікні, що з’явилося (Рис. 1.10), вибираємо Фильтровать список на месте, навпроти графи Диапазон условий натискаємо на кнопку згортання і виділяємо діапазон (A2:Q4). Після натиснення OK
• Рис. 1.10. Параметри розширеного
• Рис. 1.11. Параметри проміжних фільтра підсумків
• 1.2.4 Проміжні підсумки за статями
• Створюємо новий аркуш MS Excel і називаємо його Підсумки. Розміщуємо на ньому таблицю з аркуша Сортування.
• Алгоритм підведення проміжних підсумків за статями.
• 1. Виділяємо діапазон комірок (A2:Q23), так як до нього мають ввійти назви полів.
• 2. Виконуємо сортування за ключовим словом Стать.
• 3. В Меню натискаємо Данные і обираємо Итоги.
• 4. У вікні, що з’явилося (Рис. 1.11) у графі При каждом изменении в вибираємо зі списку Стать (підсумки мають бути після кожної зміни статі); у графі Операция — Количество; у графі Добавить итоги по ставимо галочку навпроти Стать, інші знімаємо.
• 5. Ставимо галочку навпроти Заменить текущие итоги і натискаємо OK.
• В результаті отримуємо Табл. 1.6.
• Табл. 1.6. Підведення проміжних підсумків за статями
• 2 Графічна частина роботи
• 2.1 Побудова кільцевої діаграми прибуткового податку
• Створюємо новий аркуш з назвою Для діаграми 1 — допоміжна таблиця для кільцевої діаграми. На ній розташовуємо таблицю з аркуша Сортування, виконуємо сортування за прізвищем.
• Рис. 2.1.Вибір типу діаграми
• Виділяємо назву поля «Прізвище І.П.» (діапазон B2: B23) з записами, натиснувши і утримуючи Ctrl, виділяємо також поле «Прибутковий податок» (К2:К23) з записами. На панелі інструментів натискаємо на значок Мастер диаграмм, який допоможе побудувати діаграму за чотири кроки. На першому кроці вибираємо тип діаграми — Кольцевая (Розрізана) (Рис. 2.1.).Натиснувши Просмотр результата, можна переглянути діаграму.
• Рис. 2.2.Настройка параметрів діаграми
• На наступному кроці нічого не змінюємо, перевіряємо правильність діапазону і натискаємо Далее.
• На третьому кроці на вкладинці Заголовки у графі Название диаграммы вводимо Кільцева діаграма прибуткового податку; на вкладинці Подписи данных ставимо відмітку біля підпису Значения (Рис. 2.2.)
• На четвертому кроці розміщуємо діаграму на окремому аркушеві під назвою Діаграма 1. Маємо кільцеву діаграму прибуткового податку (Рис. 2.3.).
• Рис. 2.3.Кільцева діаграма прибуткового податку
• 2.2 Побудова кругової діаграми максимального прибуткового податку за розрядами
• Створюємо новий аркуш MS Excel і називаємо його Для діаграми 2. Розміщуємо на ньому таблицю з аркуша Сортування.
• Таблицю називаємо Проміжні підсумки для кругової діаграми максимального прибуткового податку за розрядами. Виділяємо діапазон комірок (A2:Q23). В меню Данные вибираємо Сортировать. У вікні, що з’явилося, у списку Сортировать по обираємо Розряд і натискаємо OK (Рис. 2.4.). Не знімаючи виділення, виконуємо команду: Меню — Данные — Итоги. У вікні (Рис. 2.5) у графі При каждом изменении в обираємо Розряд, у графі Операция — Максимум, у графі Добавить итоги по — Прибутковий податок і натискаємо OK. Отримуємо Табл. 2.1.
• Рис. 2.4. Сортування за розрядом
• Рис. 2.5. Настройка проміжних підсумків
• Табл. 2.1. Підведення проміжних підсумків для кругової діаграми максимального прибуткового податку за розрядами
• Виділяємо комірку F9 і, натиснувши клавішу Ctrl, виділяємо комірки К9, F13, К13, F16, K16, F19, K19, F26, K26, F29, K29. Натискаємо на значок Мастер диаграмм, який допоможе побудувати діаграму за чотири кроки.
• Рис. 2.6. Вибір діаграми
• Рис. 2.7. Настройка параметрів діаграми
• Шаг 1 из 4. Зі списку діаграм вибираємо Круговая (об'ємний варіант) і натискаємо Далее (Рис. 2.6).
• Шаг 2 из 4. Нічого не змінюємо, перевіряємо правильність діапазону і натискаємо Далее.
• Шаг 3 из 4. На третьому кроці на вкладинці Заголовки у графі Название диаграммы вводимо Кругова діаграма максимального прибуткового податку за розрядами; на вкладинці Подписи данных ставимо відмітку біля підписів Значения і Долі; Разделитель — (новая строка) (Рис. 2.7.).
• Шаг 4 из 4. На останньому кроці розташовуємо діаграму на окремому аркуші і підписуємо її Діаграма 2. Маємо кругову діаграму (Рис. 2.8.)
• Рис. 2.8. Кругова діаграма максимального прибуткового податку за розрядами
• Висновки
• Під час виконання розрахунково-графічної роботи в табличному процесорові MS Excel ми розробили таблиці баз даних для розподілу відрядного заробітку між робітниками комплексної будівельної бригади та виконали аналіз даних.
• Список використаних літературних джерел
• 1). Бронштейн И. Н., Семендяев К. А. Справочник по математике для инженеров и учащихся вузов. — М.: Гостехиздат, 1956. — 608с.
• 2). ДСТУ Б А.2.4−4-99. Система проектної документації для будівництва. Основні вимоги до робочої документації.— Введений на заміну ДСТУ Б А.2.4−4.95 наказом Держбуду України № 117 від 11.05.99р.; Чинний від 01.10.99.—Київ, 1999. — 59
• 3). Методичні вказівки до виконання лабораторних робіт на тему «Робота в табличному процесорі MS Excel 97», О.І. Корх, Л. Ф. Крещенко, М.Є. Рогоза. — 1999.
• 4). Методичні вказівки до виконання лабораторних робіт на тему «Підготовка текстового документа у редакторі Microsoft Word», О. А. Харченко, О.І. Сороковий. — 2002.
• 5). Турчак Л. И. Основы численных методов: Учеб. пособие для студентов вузов. — М.: наука, 1987. — 318с.
• 6). Microsoft Excel. Шаг за шагом. — М.: ЭКОМ, 2001. — 472с.
• 7). Билл Камарда Использование Microsoft Word 97 — К.М.С-П., Вильямс, 1999. — 799с.