Excel виконає зазначену операцію і додасть проміжні підсумки в тих стовпцях з даними, на підставі яких підраховувалися результати. У кінець БД буде додано рядок із загальним підсумком по всій БД (див. Рис.1.1).
Excel можна перевести в
режим структури, щоб відобразити на екрані тільки частина підсумкової інформації, що дуже важливо при роботі з БД, особливо великими. Клацаннями на маленьких кнопках зі знаком «мінус» і з цифрами «1», «2», «3» можна ховати або виводити на екран різні рівні структури. БД. Щоб прибрати дані по групі 219 до наведеної БД, досить клацнути по кнопці зі знаком «мінус» зліва від рядка 33 з проміжними результатами по цій групі (див. Рис.1.1). Щоб прибрати з екрану все, крім проміжних і загальних підсумків, потрібно клацнути по кнопці другого рівня (з цифрою «2»). Щоб прибрати всі, крім загального підсумку, потрібно клацнути по кнопці «1». Щоб повернути на екран всі записи, потрібно клацнути по кнопці «3».
У Excel є 12 функцій, які використовуються для аналізу даних з баз даних. Кожна з цих функцій, мають узагальнену назву БДФункція, використовує три аргументи:
база_данних, поле і критерій
БДФункція (база_данних; поле; критерій) Ці три аргументи посилаються на інтервали комірок на робочому аркуші, які використовуються цією функцією.
База_данних - це інтервал осередків, що формують БД.
Поле визначає стовпець, використовуваний функцією. Аргумент
поле може бути заданий як текст з назвою стовпця в подвійних лапках (наприклад, «Інформ.») Або як число, що задає положення стовпця в БД (наприклад, 7 для того ж поля).
Критерій - це посилання на інтервал осередків, що задають умови для
функції.
Функція повертає дані зі списку, які задовольняють умови діапазоном критеріїв. Діапазон критеріїв включає копію назви стовпця, для якого виконується підведення підсумків. Посилання на критерій може бути введена як інтервал осередків (наприклад, B24: B25).
У наведеному прикладі підраховується кількість оцінок кожного виду за результатами іспиту з інформатики в розглянутій БД. В осередках G19: G22 використані БДФункціі БСЧЕТ, яка переглядає в БД в інтервалі комірок A1: K16 запису в 7-му стовпці з ім'ям «Інформ.» І підраховує кількість п'ятірок за критерієм в діапазоні B24: B25, четвірок - у C24: C25, трійок і двійок - в D24: D25 і E24: E25.
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
19
|
|
|
|
|
Інформатика
|
К-во 5
|
= БСЧЕТ (A1: K16; 7; B24: B25)
|
20
|
|
|
|
|
|
К-сть 4
|
= БСЧЕТ (A1: K16; 7; C24: C25)
|
21
|
|
|
|
|
|
К-во 3
|
= БСЧЕТ (A1: K16; 7; D24: D25)
|
22
|
|
|
|
|
|
К-у 2
|
= БСЧЕТ (A1: K16; "Інформ."; E24: E25)
|
23
|
|
|
|
|
|
|
|
24
|
|
Інформ.
|
Інформ.
|
Інформ.
|
Інформ.
|
|
|
25
|
|
= 5
|
= 4
|
= 3
|
= 2
|
|
|
26
|
|
|
|
|
|
|
|
За допомогою БДФункцій ДМАКС і ДМІН можна знайти максимальний та мінімальний елемент стовпця для записів, які
відповідають критерію.
Функції БДСУММ і ДСРЗНАЧ дозволяють знайти суму та середнє значення елементів вказаного поля,
відповідних записів, які відповідають критеріям.
1.4.
Обмін даними.
Кожного разу, коли дві програми
Windows працюють з загальними даними, використовується
Буфер обміну. Будь-який виділений об'єкт, наприклад блок тексту в Word-документі або інтервал в електронній таблиці або інший фрагмент даних цих програм, може бути
вирізаний або
скопійований клацанням миші на кнопці
Видалити в буфер або
Копіювати в буфер на панелі інструментів. Після цього Windows зберігає цю інформацію в пам'яті комп'ютера, поки користувач не вкаже місце вставки і не вставить копію в активний документ. При цьому неважливо, чи належать джерело копіювання і цільовий документ до одного типу або це різні документи, наприклад, таблиця Excel і текст
Word, - процедура виглядає абсолютно однаково. Слід пам'ятати, що в будь-який момент часу
Буфер обміну здатний зберігати лише один об'єкт.
При роботі з БД виникає необхідність створити нову БД, що містить якісь дані з однієї БД і частина з іншої або просто додаткові дані, які не потрібні були при роботі з вихідної БД. Але при цьому необхідно, щоб усі зміни в вихідної БД
автоматично враховувалися у новоствореній БД і не тільки в БД, але і в будь-якому іншому документі Microsoft Office, що використовує як складову частину дані електронної таблиці. Тоді мало скопіювати через буфер обміну дані з однієї БД в іншу, необхідно їх
зв'язати. Створення
зв'язку між документом і електронною таблицею починається з копіювання даних в
Буфер обміну. Однак замість використання команди
Вставити, щоб вставити з буфера обміну необхідно використовувати команду
Спеціальна вставка. Порядок дій:
Ø В електронній таблиці виділити інтервал, який необхідно скопіювати.
Ø Клацнути правою кнопкою миші на виділеній області і в контекстному меню вибрати
Копіювати (або клацнути на панелі інструментів кнопку копіювання).
Ø Переключитися в документ, до якого слід скопіювати дані виділеної області (це може бути нова БД або документ Word).
Ø Вибрати
Правка -
Спеціальна вставка, в результаті чого з'явиться діалогове вікно
«Спеціальна вставка». Ø Вибрати опцію
Зв'язати, клацнувши мишею на потрібному положенні перемикача.
Ø Переконавшись, що в полі
Як виділено підсвічуванням пункт
Лист Microsoft Excel Об'єкт, клацнути на кнопці OK.
У результаті на екрані з'явиться копія інтервалу з електронної таблиці. Над цим
матеріалом не можна виконувати операції Word, хоча він буде схожий на Word-таблицю після вставки в Word-документ.
Для прикладу створимо БД, що містить поля «№», «Прізвище», «Ім'я», «батькові», «Група» і «Стипендія», такі ж, як у нашій БД. Доповнимо нову БД полями «Дата народження» і «Вік», задавши
відповідну формулу для обчислення віку: (СЕГОДНЯ () - «Дата народження») / 365 (років).
Вік, таким чином, буде змінюватися, з кожним днем збільшуючись на 1 / 365, так як вираження у скобці визначає різницю між днем сьогоднішнім і датою народження в днях. Можна ввести в БД ще одне поле, що обчислюють вік у днях.
Ці всі операції виконуються за правилами дій над датами в Excel. Зв'яжемо нову БД з вихідною, щоб можна було враховувати будь-які зміни, що у вихідної БД в загальних полях, без додаткового коректування нової БД.
Тепер змінимо у вихідної БД оцінку
студента Горця з інформатики на «5», що підвищить його середній бал і забезпечить стипендією у розмірі 12гр., А також скорегуємо ім'я та по батькові студента Прокопенко: як бачимо, дані змінилися автоматично як в нової БД, так і в даному Word-документі.
Література:
1.
Методичні вказівки до проведення обчислювальної практики № 1
2.
Конспект лекцій з курсу «Інформатика та комп'ютерна техніка».
3. Руденко В.Д., Макарчук О.М., Патланжоглу М.О. Практичний курс інформатики. / За ред. Мадзігона В.М.-К.: Фенікс, 1997.-307 с.
На етапі
проектування бази даних задаємо структуру бази, визначаємо кількість, найменування і типи полів бази, визначаємо для обчислюваних даних формули, за якими вони обчислюються.
2.1.1. Структура бази даних
Відповідно до завдання база даних повинна містити такі поля:
№ поля
|
Ім'я поля
|
Тип поля
|
Тип даних
|
Довжина поля
|
1
|
ПІБ
|
Символьний
|
Вихідні
|
20 символів
|
2
|
Бригада
|
Символьний
|
Вихідні
|
12 символів
|
3
|
Спеціальність
|
Символьний
|
Вихідні
|
15 символів
|
4
|
Оклад
|
Числовий
|
Вихідні
|
4 символи
|
5
|
Премія
|
Числовий
|
Обчислювані
|
4 символи
|
6
|
Нараховано
|
Числовий
|
Обчислювані
|
4 символи
|
7
|
Податок
|
Числовий
|
Обчислювані
|
8 символів
|
8
|
До виплати
|
Числовий
|
Обчислювані
|
8 символів
|
Рис.2.1 Структура проектованої бази даних.
2.1.2. Визначення формул для обчислюється частини бази даних.
У створюваної базі кілька обчислюваних полів Визначимо залежності, за якими обчислюються значення в цих полях. Введемо умовні позначення, які будемо використовувати при складанні формул:
Премія - П;
Оклад - О;
Стаж-С;
Нарахована сума - НС;
Прибутковий податок - ПН;
1.Премія. Відповідно до умов премія нараховується співробітникам, які пропрацювали певний час на фірмі. Для співробітників зі стажем від 2-х до 5-ти років премія становитиме 15% окладу, зі стажем більше 5 років 25% окладу.
При використанні Майстра функції логічне вираз для обчислення премії набуде вигляду:
П = Якщо (С <= 2; 0; якщо (С> 5; 0.25 * O; 0.15 * O))
2.Начісленная сума. Значення нарахованої суми визначається як результат додавання значення окладу та премії.
НС = П + О.
3. Прибутковий податок. Прибутковий податок визначається в залежності від величини нарахованої суми: не обкладаються
податком суми до 70 грв. включно; при нарахованій сумі більше 250 грв. прибутковий податок становить 20% від суми; в інших випадках прибутковий податок дорівнює 10% від суми.
ПН =
При використанні Майстра функцій логічне вираз набуде вигляду:
ПН = Якщо (НС <= 70; 0; якщо (НС> 250; 0.2 * НС; 0,1 * НС))
4.
До виплати. Значення визначається як різниця нарахованої суми і прибуткового податку.
К_вип = НС-ПН
В результаті проектування бази даних отримано ескіз таблиці такого вигляду.
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
1
|
ПІБ
|
Бригада
|
Спеціальність
|
Оклад
|
Стаж
|
Премія
|
Нарахована сума
|
Прибутковий податок
|
До виплати
|
2
|
|
|
|
|
|
|
|
|
|
Рис. 2.2. Ескіз таблиці для заповнення бази даних.
У таблиці вихідні дані відзначені синім кольором, а обчислювані значення - червоним.
2.2. Створення бази даних.
2.2.1 Створення заголовка таблиці і першого рядка.
Для створення таблиці розкриваємо додаток Excel Microsoft Office. Після введення назви таблиці заповнюємо заголовки стовпців і форматуємо їх. Для цього вибираємо команди меню
Формат Стовпець Ширина і встановлюємо необхідні значення (
відповідно до кількості символів у кожному полі, обумовленому в структурі бази даних). Після введення заголовків стовпців вибираємо
відповідний вид форматування. Для цього використовуємо пункти меню
Формат Осередки і активізуємо відповідні вкладки: Число, Вирівнювання, Шрифт. У числових полях задаємо 2 знаки після крапки, вирівнювання в тестових полях встановлюємо по правому краю комірки, вирівнювання числових полів виконуємо по центру.
Після форматування заголовків стролбцов приступаємо до заповнення 1-го рядка. Вводимо початкові дані і в обчислюваних полях записуємо розрахункові формули.
2.2. Заповнення таблиці за допомогою Майстра форм.
Подальше заповнення даних виконуємо за допомогою пунктів меню
Дані Форма (у цей момент курсор повинен бути встановлений на комірці в області створюваної таблиці). При цьому відкривається вікно діалогу Майстра форм із заповненою першим записом вихідної бази даних.
Клацаючи на клавіші «Додати» у вікні діалогу і послідовно заповнюючи порожні поля вихідними даними, створюємо вихідну базу даних.
2.3. Ведення бази даних.
Ведення бази даних полягає у коригуванні існуючих даних, додаванні нових, видаленні полів, що містять непотрібну або помилкову інформацію.
Відповідно до завдання виконаємо операції з редагування, додавання, видалення інформації. При роботі в середовищі електронних таблиць для цього можуть бути використані різні засоби.
2.3.1. Редагування полів.
Додамо до існуючої бази поле, яке відображає порядковий номер записів у базі. Для цього
встановимо курсор у комірку першого стовпця і активізуємо пункт меню
Вставка Стовпці. У який з'явився порожньому стовпці запишемо назву поля і заповнимо його. Результат помістимо в новий
файл. Видалення полів здійснимо за допомогою команд меню
Правка Видалити Видалити стовпець. При цьому курсор необхідно встановити в поле, яке збираємося видалити. На Рис. 2.6 показано вигляд бази даних з доданим полем №, а на рис.2.7 показана база даних, з якої видалили поля № і «Бригада».
2.2. Редагування записів
Для видалення записів з бази даних необхідно ці записи виділити й активізувати команди меню
Правка Видалити Рядок. В результаті виконання цих дій рядок, у якій був встановлений курсор буде видалена. Для додавання рядків у базу даних необхідно виконати наступні дії: активізувати команди меню
Вставка Строки. У результаті в базу даних буде додана порожній рядок над рядком, в якій знаходився курсор. Далі заносимо потрібні відомості в додану рядок.
у клітинці збережеться відкоригована інформація. Додамо до вихідної базі рядок, що містить інформацію про робочий Васєчкіна і виправимо прізвище Іванов на Іванченко в 1-й запису. При редагуванні можна також користуватися вікном діалогу Майстра форм.
2.4.Начальная обробка даних.
2. 4.1. Додавання суми по стовпцях.
Додамо в числових полях підсумовування за стовпцями.
Формули для обчислення сум в осередках F27, G27, H27, I27, J27 будуть виглядати
відповідним чином.
F27 = СУММ (F7: F26); G27 = СУММ (G7: G26); H27 = СУММ (H7: H26);
I27 = СУММ (I7: I26) J27 = СУММ (J7: J26)
2.4.2. Додавання підсумовування за критерієм.
Використовуємо функцію СУММЕСЛІ для додавання суми по заданій умові. У відповідно до довідки Майстра формул
функція може бути представлена в загальному вигляді як СУММЕСЛІ (діапазон; умова; діапазон_суммірованія) У нашому випадку підсумуємо преміальний фонд ветеранам праці (
стаж повинен перевищувати 10 років).
Розрахункова формула прийме вигляд:
G27 = СУММЕСЛІ (E7: E26;> 10; G7: G26)
Результат використання формули наведено на Рис. 2.10.
2.5.1. Проста сортування по полю.
Для звичайної сортування бази даних по одному полю (за зростанням або за спаданням) необхідно скористатися пунктом меню
Дані Сортування або піктограмами на графічному меню.
Сортування за зростанням Сортування за спаданням
Ріс.2.11. Піктограми сортування.
При цьому курсор повинен бути встановлений в полі, яке будемо сортувати. Для сортування за зростанням за полем «Стаж»
встановимо курсор на клітинку в цьому полі і виберемо напрямок сортування «за зростанням». Результат сортування представимо на рис.2.12.
Рис.2.12. Сортування по полю «Стаж» за зростанням.
2.5.2 Сортування за кількома полями.
Для проведення більш складної сортування (за кількома полями) відкриємо вікно діалогу «Сортування діапазону» (див. рис.2.13). Для цього виконаємо команди меню
Дані Сортування. Щоб сортування виконувалася за двома або трьома полями
у вікні діалогу для кожного діапазону задаємо напрям сортування. Найменування діапазонів вибираємо у вікні діалогу, розкриваючи список найменувань (клацаємо послідовно по областях вікна діалогу «Сортувати за», «Потім по», «В останню чергу по»), і вказуємо напрям сортування по кожному полю (убування / зростання). У нашому прикладі виконаємо сортування за трьома критеріями: по полю «Стаж», потім по полю «Спеціальність» і в останню чергу по полю «Оклад». Для всіх трьох критеріїв задаємо напрям сортування «За зростанням». Результат виконання сортування за кількома критеріями помістимо на рис.2.14.
2.6. Форми подання інформації, що міститься в базі даних.
2.6.1. Додавання проміжних підсумків.
Додавання проміжних і остаточних підсумків виконується після сортування вихідної бази по обраному полю. Виконаємо сортування вихідної бази по полю «Бригада» і додамо проміжні і загальний підсумки. Щоб додати підсумків відкриємо вікно діалогу «Проміжні підсумки». Для цього виконуємо команди меню
Дані Підсумки. У вікні діалогу задамо поле, в якому буде відслідковуватися зміна значень (наприклад поле «Бригада»). Потім вкажемо в рядку «Операція» той вид операції, який потрібен для виконання завдання (Сума, Середнє і т.д.). У списку вікна діалогу «Додати підсумки по» зазначимо, яких ще полях бази даних необхідно виконати аналогічні дії. Вікно діалогу з встановленими параметрами показано на Ріс.2.15.
Результат виконання операції по додаванню підсумків відобразимо на Ріс.2.16.
Аналогічні дії виконаємо для додавання підсумків по полю «Спеціальність». Відсортуємо вихідну базу по полю «Спеціальність» по зростанню і при кожній зміні в цьому полі додамо проміжні підсумки. Визначимо сумарні значення нарахованої суми і прибуткового податку за спеціальностями. Для цього у вікні діалогу «Проміжні підсумки» виберемо поле, зміни в якому призведуть до додавання підсумків у таблицю. Це поле «Спеціальність», і встановимо операцію для виконання («Сума»).
Встановимо у вікні діалогу, для яких полів буде виконуватися ця
операція (Нараховано, По / нал). Результати відобразимо на Ріс.2.17.
2.7. Аналіз інформації, що міститься в базі даних.
2.7.1 Обчислення статистичних характеристик
Найпростіша математична обробка числових полів виконується за допомогою Майстра функцій (розділ статистичних функцій МАКС, СРЗНАЧ, МІН). Для більш зручної роботи представимо результати в числовому і формульному вигляді. Для представлення в формульному вигляді скористаємося меню MS EXCEL
Сервіс Параметри Вид Параметри вікна Формули. Результати виконання цього пункту звіту представлені на
2.7.2. Робота з функціями з розділу Бази даних
У розділі Бази даних з Майстра функцій представлено більше 10 спеціальних функцій для роботи з базами даних. У завданні виконувалося підсумовування грошових виплат для робітників спеціальності «Маляр» з стажесм більше 10 років. Для цього використовувалася
функція БДСУММ (база_данних; поле; критерій). В якості діапазону досліджуваних даних було обрано вихідна база. Підсумовування вироблялося по полю «До виплати», або по 10-му стовпцю бази, при цьому враховувалися обмеження за фахом і за стажем, які записані в осередках B28: С29. Результат вирполненія цієї операції показаний на Ріс.2.22.
2.8. Вибіркове використання даних
Вибіркове використання даних представляє систему відбору даних з вихідної бази з будь-якою ознакою і подальшу обробку цих даних. Зазвичай застосовують для цих цілей користувальницький автофільтр і розширений фільтр.
2.8.1. Користувача автофільтр.
Підключення користувацького автофільтра відбувається при активізації меню
Дані-Фільтр-користувача автофільтр. Потім встановлюється критерій для фільтрації даних (для вибраного поля розкриваємо список, який поміщений в рядку заголовків полів бази даних. Якщо необхідно прибрати користувальницький автофільтр активізуємо команду
Дані Фільтр Автофільтр. Виберемо з вихідної бази записи, які відповідають наступним критерієм відбору: Спеціальність «зварювальник». Встановимо цей критерій у вікні діалогу на Рис. 2.23.
Рис. 2.23 Встановлення критерію фільтрації.
Далі встановимо фільтр для вибору інформації з вихідної бази по полю стаж. Необхідно відібрати робітників зі стажем від 5 до 10 років Встановимо фільтр використовуючи команди меню
Дані Фільтр Автофільтр і розкриваємо список у полі, за яким передбачається виконувати фільтрацію. Виберемо зі списку параметр «Умова». У вікні діалогу, установимо критерії для фільтра .. Для поля стаж використовуємо оператори «більше або дорівнює» і менше або дорівнює »і задаємо числові значення відповідно 5 і 10.
Рис. 2.26. Результат роботи фільтру по полю стаж.
8.2 Розширений фільтр.
Можливості розширеного фільтра значно більше, ніж у користувача. Підключаємо його за допомогою команди меню Дані Фільтр рашіреніем
З помощьюрасшіренного фільтра виберемо з вихідної бази запису, у яких в полі «спеціальність» будуть значення «зварювальник» або «маляр» і стаж цих робочих повинен перевищувати 10 років.
Для створення кругової діаграми виберемо таблицю даних, для яких ця діаграма буде будуватися. З огляду на те, що вихідна база містить 20 рядків, побудуємо діаграму для попередньо відфільтрованої базі по полю № бригади (Бригада № 1). Зазначимо діапазони даних, які будемо використовувати. Для цього виділимо стовпці ПІБ і К виплаті. Виділяємо при натиснутій клавіші CTRL. Далі за допомогою майстра діаграм встановлюємо вид виведеної інформації на полі діаграми (легенда, заголовок, частки значень і т.д.) і місце розташування діаграми (на окремому аркуші або поруч із таблицею.
Кругова діаграма розподілу виплат по бригаді № 1.
Для побудови наступного даграмми з переліку можливих видів у списку Майстра діаграм виберемо різновид «Графік». Для всіх співробітників
фірми побудуємо графік розподілу грошових виплат.