ЗМІСТ
ВСТУП1. БАЗИ ДАНИХ У EXCEL.
1.1. Як створити базу даних.
1.2. Як виконати пошук, редагування та видалення записів.
1.3. Обробка даних в БД.
1.4. Обмін даними.
2.1. Проектування бази даних.
2.1.1. Структура бази даних
2.1.2. Визначення формул для обчислюється частини бази даних.
2.2. Створення бази даних.
2.2.1 Створення заголовка таблиці і першого рядка.
2.2. Заповнення таблиці за допомогою Майстра форм.
2.3. Ведення бази даних
2.3.1. Редагування полів.
2.2. Редагування записів
2.4.Начальная обробка даних.
2. 4.1. Додавання суми по стовпцях
2.4.2. Додавання підсумовування за критерієм.
2.5. Сортування бази даних.
2.5.1. Проста сортування по полю.
2.5.2 Сортування за кількома полями.
2.6. Форми подання інформації, що міститься в базі даних.
2.6.1. Додавання проміжних підсумків.
2.6.2. Робота зі структурою
2.7. Аналіз інформації, що міститься в базі даних.
2.7.1 Обчислення статистичних характеристик
2.7.2. Робота з функціями з розділу Бази даних
2.8. Вибіркове використання даних
2.8.1. Користувача автофільтр.
2.8.2 Розширений фільтр.
2.9. Графічне представлення даних.
ВСТУП
Термін електронні таблиці можна визначити як "програмний продукт, який широко використовується для опису безлічі пакетів моделювання, реалізованих для мікрокомп'ютерів, і який походить від схожості з" розворотом аркуша паперу ", розбитим на рядки і стовпці".Електронні таблиці використовуються для побудови моделей, тобто дозволяють визначати, яку інформацію слід подавати до моделі, як її представляти і як повинна маніпулювати даними відповідна програма. Модель представляє в математичних термінах відносини між важливими змінними, які характеризують певну ситуацію в бізнесі.
Прикладами пакетів ведення електронних таблиць є Supercalc, Lotus 1-2-3 і MS Excel. У даній роботі ми будемо розглядати роботу з базами даних в MS Excel.
Якщо виникає потреба у придбанні пакета ведення електронних таблиць, слід переконатися в тому, що вони, крім основних характеристик, які будуть детально розглянуті нижче, володіють наступними можливостями:
(А) засоби навчання, пропоновані основним розробником чи незалежної консультаційною компанією (доступність і ціна);
(Б) якість документації, що поставляється з пакетом;
(В) інтеграція з іншими функціями (наприклад, передачі даних з електронних таблиць в текстовий процесор або введення інформації з бази даних).
Для роботи з електронними таблицями необхідно, як правило, не менше 512 Кбайт оперативної пам'яті. Введення і виведення даних здійснюються через відеотермінал і клавіатуру з застосуванням для отримання твердих копій принтера. Сфери використання електронних таблиць обмежені тільки уявою користувача, а також кількістю "рядків і стовпців" таблиці.
1. БАЗИ ДАНИХ У EXCEL.
База даних (date base) - це сукупність збережених в пам'яті комп'ютера даних, які відображають стан деякої предметної області. Дані взаємопов'язані і спеціальним чином організовані.При такому інформаційному відображенні предметних середовищ упор робиться не на самі об'єкти і їх властивості, а на відносини між ними, що відповідає так званої реляційної точці зору на бази даних.
Excel вміє складати, віднімати, множити, ділити і виконувати безліч інших операцій. Excel дає можливість попередньо проаналізувати наслідки прийняття тих чи інших рішень при конкретних обставинах. Excel дозволяє автоматизувати не тільки розрахунки як такі, але дозволяє створювати і працювати з різноманітними картотеками, системами обліку, базами даних і т.п.
Питання збору даних, їх зберігання, обліку та обробки можна вирішити, маючи систему управління списками. Термін список використовується в Excel для позначення бази даних.
База даних - це особливий тип робочої таблиці, в якій не стільки обчислюються нові значення, скільки розміщуються великі обсяги інформації у зв'язаному вигляді.
Наприклад, можна створити базу даних із прізвищами, іменами, адресами та номерами телефонів ваших знайомих або список групи з усією інформацією про підсумки сесії і про розмір відповідної стипендії або її відсутності.
База даних являє собою послідовність записів, що містить однозначно визначену за категоріями та послідовності інформацію. Під кожну категорію даних у записі відводиться окреме поле, якому присвоюється ім'я і відводиться стовпець.
1.1. Як створити базу даних.
Створення бази даних забезпечує інтеграцію даних і можливість централізованого управління даними, забезпечуючи інформацією певних користувачів.
Створення бази даних (БД) починається з проектування БД, тобто з визначення її структури: кількості полів, їх імен, типу кожного поля (символьний, числовий, дата ...), довжини кожного поля (максимальної кількості символів), типу даних (вихідні, тобто незмінні, або обчислювані). Можливість використовувати обчислювані поля - основна особливість баз даних в Excel.
База даних створюється в звичайної електронної таблиці, але з виконанням таких правил:
ÿСтрока заголовків стовпців (верхній рядок списку) повинна бути заповнена іменами полів.
ÿКаждая запис повинен розміщуватися в окремому рядку.
ÿПервую запис необхідно розмістити в рядку, наступному безпосередньо за рядком заголовків.
ÿСледует уникати порожніх рядків між записами.
Створення та підтримка бази даних в Excel спрощується, якщо замість звичного для роботи в Excel переходу від осередку до осередку при введенні даних використовувати вбудовану форму (бланк) даних. Щоб створити форму даних для нової бази, слід ввести спочатку рядок (або стовпець) з іменами полів і помістити в наступному рядку запис ¾ зразок заповнення бази.
| Прізвище | Ім'я | По батькові | Вищ. матем. | Інформ. | Історія Укр. | Фізика | Ср.балл | Стипендія |
| Абдельгадір | Мусса | Ібрагімович | 4 | 5 | 3 | 3 | 3,75 | 0 |
При цьому необхідно відформатувати кожен елемент поля, пам'ятаючи, що таким же чином будуть відформатовані всі наступні елементи в цьому полі. У обчислювані поля вводяться розрахункові формули. Так у наведеному прикладі в стовпці «Ср.балл» і «Стипендія» введені формули:
| Ср.балл | Стипендія |
| = СРЗНАЧ (E2: H2) | = ЕСЛИ (I2 <4; 0; ЕСЛИ (I2 <4,5; 12; 15)) |
Потім необхідно відзначити обидва рядки (з іменами полів і формулами) і вибрати команду Форма з меню Дані. Після цього Excel аналізує введену в перші два рядки інформацію і створює форму з даними, у якої зверху вниз перераховані всі імена полів в лівій частині і елементи першого запису в текстових вікнах поруч з ними. Текстові вікна у обчислюваних полів відсутні.
Щоб ввести новий запис, потрібно клацнути на кнопці Додати і ввести дані в порожні поля. Для переходу до чергового полю слід натискати клавішу <Tab>. При заповненні БД необхідно дотримуватися таких правил:
ÿНельзя натискати клавішу Enter для переходу до наступного поля, так як при цьому додасться неповна запис!
ÿЧтоби відредагувати значення в пройденому полі, слід натискати клавіші <Shift + Tab> для повернення в потрібне поле.
ÿЧтоби замінити раніше введене значення, необхідно просто почати вводити потрібне в цьому полі.
ÿЧтоби відредагувати тільки деякі символи значення поля, слід натискати клавішу <¬ -> або клацнути I-образним покажчиком миші на тому місці, куди необхідно помістити курсор.
ÿПрі введення дати необхідно дотримуватися відомого програмі формату
ÿПрі введенні чисел з лідируючими нулями (крім поштових індексів), вводиться апостроф перед першим нулем, що сприймається Excel як текстова мітка (при цьому апостроф в БД не вводиться).
ÿПосле заповнення всього запису необхідно натиснути клавішу Enter або клацнути на кнопці Додати, в результаті чого Excel вставить новий запис останньої в БД і виведе на дисплей порожню форму для введення наступного запису.
ÿПосле завершення введення записів слід натиснути клавішу <Esc > Або клацнути на кнопці Закрити, щоб закрити форму даних.
ÿСохраніть робочу таблицю командою Зберегти з меню Файл або натиснути на інструменті Зберегти на стандартній панелі інструментів.
1.2. Як виконати пошук, редагування та видалення записів.
Доступ до даних забезпечує система управління даними. У її функції входять пошук, виправлення, додавання та видалення даних. Причому операція пошуку є головною.Форма даних служить не тільки для створення БД, а й для пошуку потрібного запису, для її редагування або для подальшого видалення непотрібної запису.
Якщо в БД порівняно небагато записів, пошук потрібного запису можна виконати наступним чином:
Ø встановити табличний курсор де-небудь в БД;
Ø виконати послідовність команд Дані - Форма;
Ø натискати кнопку Далі в формі, що з'явилася або клавішу Enter, поки не з'явиться потрібна запис, або скористатися смугою прокрутки праворуч від списку імен полів, клацаючи на кнопці зі стрілкою вниз у нижній її частині;
Ø для переміщення до попереднього запису натиснути <Shift + Enter> або клацнути на стрілці вгорі смуги прокрутки;
Ø для переміщення до першого запису натиснути <Ctrl + PgUp> або пересунути повзунок прокрутки в самий верх смуги прокрутки;
Ø для переходу на останній запис натиснути <Ctrl + PgDn> або пересунути повзунок смуги прокрутки в самий низ;
Ø для переходу по БД на 10 записів вперед натиснути <PgDn>, на 10 записів назад - <PgUp>.
Для пошуку потрібного запису у великій БД слід використовувати форму даних і спеціальні критерії пошуку. Excel, використовуючи задані критерії для знаходження необхідних записів у списку, знайде і відбере тільки ті записи, для яких виконані умови, задані критеріями.
Для завдання критеріїв і виконання пошуку задовольняють ним записів небхідно виконати такі дії:
Ø Клацнути на першого запису.
Ø Вибрати Дані - Форма.
Ø Клацнути на кнопці Критерії в формі, що з'явилася, після чого очистяться усі поля.
Ø Ввести необхідні критерії пошуку у відповідні поля форми.
Ø Натиснути кнопку Далі або клавішу Enter , Щоб почати пошук.
Ø Excel відобразить форму даних з першої знайденої записом, а щоб переглянути всі наступні, слід натискати кнопку Далі; при необхідності пройтися по знайденим записам у зворотному порядку слід клацати по кнопці Назад.
Щоб змінити критерій пошуку, якщо необхідно, потрібно спочатку очистити форму даних, клацнувши знову на кнопці Критерії. Потім - вибрати потрібні текстові вікна і очистити старий критерій перед введенням нового або просто замінити критерій за умови використання тих же полів.
Щоб повернутися до поточного запису, проігнорувавши результати пошуку за критерієм, слід клацнути на кнопці Правка, яка з'являється на місці кнопки Критерії після переходу в режим створення критерію.
Для редагування значення поля в поточному записі необхідно перейти в нього, натискаючи клавіші <Tab> або <Shift + Tab> (або за допомогою миші), і ввести нове значення. Для очищення поля цілком виділити його і натиснути клавішу <Del>.
Для видалення записи з БД клацнути на кнопці Видалити у вікні форми даних. При цьому, однак, слід пам'ятати, що неможливо відновити вилучену таким чином запис за допомогою команди Скасувати. Тому Excel видасть вікно попередження з таким повідомленням: «Запис, виведена на екран, буде вилучена». Можна підтвердити своє рішення про видалення запису, клацнувши на кнопці ОК, або скасувати, клацнувши на кнопці Скасувати.
Редагування полів здійснюється звичайними засобами Excel, призначеними для роботи зі стовпцями електронної таблиці з подальшим коректуванням форми даних і всіх записів.
Для додавання нових записів у вже сформовану БД при наявності рядків з підсумковими результатами (наприклад, середнє значення по полю або сума елементів стовпця) потрібно заздалегідь вставити звичайними засобами Excel (за допомогою меню Вставка) необхідну кількість нових рядків. В іншому випадку Excel повідомить, що «розширення БД неможливо». Після доповнення БД необхідно в обов'язковому порядку відкоригувати формули в підсумкових рядках, поширивши їх на нововведені дані, тому що інакше ці дані не будуть враховані і результати залишаться колишніми, тобто неправильними.
1.3. Обробка даних в БД.
Будь-яка інформація повинна бути впорядкована. Хороша БД - в тому числі. Кожна БД має деякий бажаний порядок підтримання та перегляду записів. Записи можна розташувати, наприклад, в алфавітному порядку прізвищ чи назв фірм. Для визначення рейтингу студентів список групи зручно розташувати за спаданням середнього балу. Однак при додаванні нових записів Excel включає їх в самий кінець БД, додаючи нові рядки і порушуючи колишній порядок. Це не єдина проблема з впорядкуванням записів, так як завжди може виникнути необхідність в іншому, особливому порядку. Іншими словами, при роботі з даними потрібна гнучкість упорядкування записів для різних цілей. Процес упорядкування БД називається сортуванням.Для сортування елементів у БД необхідно виконати такі дії:
Ø Клацнути на будь-якій комірці БД.
Ø Вибрати команду Сортування з меню Дані, в результаті чого Excel розкриє діалогове вікно Сортування діапазону.
Ø Клацнути на стрілці в групі «Сортувати за» і вибрати головний ключ сортування (поле, по якому повинна бути виконана сортування).
Ø Вибрати висхідний «за зростанням» або спадний «за спаданням» упорядкування, клацнувши по перемикачу справа.
Ø Якщо потрібно виконати ще одне упорядкування всередині першого, клацнути на стрілці в такій групі «Потім по» і вибрати друге поле сортування і потім - висхідний або низхідний порядок.
Ø При необхідності подальшого ієрархічного упорядкування вибрати поле і порядок упорядкування в останньому списку «В останню чергу, по».
Ø Для відмови від вибору полів і порядку сортування досить, не виходячи з цього діалогового вікна, знову відкрити список полів та вибрати «(не сортувати)».
Ø Клацнути на ОК або натиснути Enter.
Excel відсортує зазначені записи. При необхідності відмовитися від хибної сортування слід вибрати Правка - Скасувати Сортування або натиснути <Ctrl + Z> для відновлення записів БД в колишньому порядку.
| № | Прізвище | Ім'я | По батькові | Вищ. матем. | Інформ. | Історія Укр. | Фізика | Ср.балл | Стипендія |
| 4 | Грант | Анатолій | Семенович | 4 | 5 | 5 | 5 | 4,75 | 15 |
| 2 | Бірюкова | Галина | Олегівна | 5 | 4 | 4 | 5 | 4,5 | 15 |
| 3 | Вовченко | Олександра | Олександрівна | 3 | 4 | 5 | 4 | 4 | 12 |
| 1 | Абдельгадір | Мусса | Ібрагімович | 4 | 5 | 3 | 3 | 3,75 | 0 |
Для сортування записів в БД по одному полю простіше користуватися інструментом Сортувати за зростанням або Сортувати за зменьшенням на панелі інструментів. Для цього потрібно встановити курсор в клітці з ім'ям поля і клацнути на відповідному інструменті. При цьому необхідно пам'ятати, що сортується вся БД і по всіх елементах вибраного поля, в тому числі і за підсумковими рядками.
Форма даних є зручним засобом пошуку записів у БД, поки критерії пошуку прості. Однак набагато більш зручним інструментом цього призначення є фільтри. Фільтр обробляє весь список в електронній таблиці і відображає на екрані тільки ті дані, які потрібно. Перевага цього підходу полягає в тому, що з такою вибіркою можна працювати автономно як з новою таблицею, не завантажуючи екран надлишковою інформацією.
Ø Клацнути де-небудь у БД.
Ø Вибрати Дані - Фільтр - Автофільтр, в результаті чого Excel додасть список, що розкривається до кожної осередку з іменем поля в рядку заголовків.
Ø Клацнути на кнопці списку поля, по значенням якого потрібно виконати відбір записів, і вибрати один із запропонованих варіантів фільтра, клацнувши по ньому.
Після цього Excel покаже тільки записи, що містять у цьому полі вибране значення (всі інші будуть тимчасово приховані). Відфільтровані записи можна скопіювати на інший лист робочої книги або видати на друк. Для цього потрібно просто:
Ø відзначити клітини;
Ø вибрати команду Копіювати з меню Правка (або натиснути <Ctrl + C>);
Ø перемістити табличний курсор до першої комірки таблиці на новому аркуші, клацнувши по ярличку «Лист2» в нижній частині екрана;
Ø натиснути Enter.
Для виводу на друк відфільтрованих записів достатньо після їх виділення клацнути на кнопці Друк на інструментальної панелі або вибрати команду Друк в меню Файл.
| № | Прізвище | Ім'я | По батькові | Вищ. матем. | Інформ. | Історія Укр. | Фізика | Ср.балл | Стипендія |
| 2 | Бірюкова | Галина | Олегівна | 5 | 4 | 4 | 5 | 4,5 | 15 |
| 4 | Грант | Анатолій | Семенович | 4 | 5 | 5 | 5 | 4,75 | 15 |
Після копіювання відфільтрованих записів можна відновити відображення всіх записів БД, клацнувши на кнопці списку, за яким виконувалася фільтрація (кнопка виділена блакитним кольором), і вибравши в списку, пункт «(Усі)».
При необхідності звузити коло пошуку можна продовжити фільтрацію, вибравши значення зі списку, що іншого поля. Excel надає можливість переглянути лише перші 10 записів з допомогою опції Перші 10 команди Автофільтр. Цю опцію доцільно використовувати при роботі з числовими полями для виводу на екран тільки десяти записів (наприклад, з найбільшими або найменшими значеннями в даному полі). Більш того, за допомогою цієї опції можна вивести на екран будь-яку кількість записів, вказане користувачем у діалоговому вікні «Накладення умови за списком», яке з'являється у відповідь на вибір зазначеної опції.
| № | Прізвище | Ім'я | По батькові | Вищ матем. | Інформ. | Історія Укр. | Фізика | Ср.балл | Стипендія |
| 2 | Бірюкова | Галина | Олегівна | 5 | 4 | 4 | 5 | 4,5 | 15 |
| 3 | Вовченко | Олександра | Олександрівна | 3 | 4 | 5 | 4 | 4 | 12 |
| 4 | Грант | Анатолій | Семенович | 4 | 5 | 5 | 5 | 4,75 | 15 |
| 7 | Іванов | Іван | Іванович | 4 | 4 | 4 | 4 | 4 | 12 |
| 9 | Клочко | Георгій | Костянтинович | 5 | 4 | 3 | 4 | 4 | 12 |
Додатково до фільтрації БД по записах, що містить певне значення в полі, можна створювати власні Автофільтри, що дозволяють фільтрувати БД по записах з більш загальним критерієм, таким як, наприклад, прізвища, що починаються на літеру «А», або значення середніх балів у межах від 4 до 5. Для створення власного фільтру потрібно:
Ø клацнути на кнопці списку, що розкривається в назві поля;
Ø вибрати опцію Умова;
Ø у діалоговому вікні Користувацький автофільтр вибрати необхідний оператор порівняння в першому рядку або в обох рядках, якщо умова складене, тобто являє собою результат логічних операцій типу «і» та «або»; в текстові вікна праворуч ввести значення (текст або число), щодо яких має проводитися порівняння значень в записах БД.
Так для вибору списку студентів, прізвища яких починаються на літеру "А" необхідно в першому рядку діалогового вікна користувача автофільтр клацнути «рівно» і ввести в текстове вікно «А *» (без лапок). Отримаємо:
| № | Прізвище | Ім'я | По батькові | Вищ. матем. | Інформ. | Історія Укр. | Фізика | Ср.балл | Стипендія |
| 1 | Абдельгадір | Мусса | Ібрагімович | 4 | 5 | 3 | 3 | 3,75 | 0 |
| 5 | Антонова | Віра | Борисівна | 3 | 4 | 4 | 3 | 3,5 | 0 |
Отримаємо:
| № | Прізвище | Ім'я | По батькові | Вищ. матем | Інформ. | Історія Укр. | Фізика | Ср.балл | Стипендія |
| 2 | Бірюкова | Галина | Олегівна | 5 | 4 | 4 | 5 | 4,5 | 15 |
| 3 | Вовченко | Олександра | Олександрівна | 3 | 4 | 5 | 4 | 4 | 12 |
| 4 | Грант | Анатолій | Семенович | 4 | 5 | 5 | 5 | 4,75 | 15 |
| 7 | Іванов | Іван | Іванович | 4 | 4 | 4 | 4 | 4 | 12 |
| 9 | Клочко | Георгій | Костянтинович | 5 | 4 | 3 | 4 | 4 | 12 |
Зрозуміло, створювати зведені таблиці має сенс тільки по БД, що містить значний обсяг інформації. Розширимо первісну БД хоча б до 15 записів і введемо нове поле «Група». Впорядкуємо список за алфавітом і скорегуємо порядок, тобто дані в полі «№». Отримаємо таку БД:
| № | Прізвище | Ім'я | По батькові | Група | Вищ. матем. | Інформ. | Історія Укр. | Фізика | Ср.балл | Стипендія |
| 1 | Абдельгадір | Мусса | Ібрагімович | 219 | 4 | 5 | 3 | 3 | 3,75 | 0 |
| 2 | Антонова | Віра | Борисівна | 219 | 3 | 4 | 4 | 3 | 3,5 | 0 |
| 3 | Бірюкова | Галина | Олегівна | 219 | 5 | 4 | 4 | 5 | 4,5 | 15 |
| 4 | Борисова | Ніна | Павлівна | 221 | 5 | 5 | 4 | 5 | 4,75 | 15 |
| 5 | Вовченко | Олександра | Олександрівна | 221 | 3 | 4 | 5 | 4 | 4 | 12 |
| 6 | Горець | Анатолій | Володимирович | 221 | 3 | 3 | 5 | 4 | 3,75 | 0 |
| 7 | Грант | Анатолій | Семенович | 223 | 4 | 5 | 5 | 5 | 4,75 | 15 |
| 8 | Дмитренко | Петро | Павлович | 223 | 3 | 4 | 3 | 3 | 3,25 | 0 |
| 9 | Дмитренко | Віталій | Ігорович | 223 | 4 | 4 | 5 | 4 | 4,25 | 12 |
| 10 | Замовскій | Едуард | Федорович | 235 | 3 | 3 | 4 | 3 | 3,25 | 0 |
| 11 | Іванов | Іван | Іванович | 235 | 4 | 4 | 4 | 4 | 4 | 12 |
| 12 | Клочко | Георгій | Костянтинович | 235 | 5 | 4 | 3 | 4 | 4 | 12 |
| 13 | Новіков | Олег | Валентинович | 241 | 3 | 4 | 3 | 3 | 3,25 | 0 |
| 14 | Прокопенко | Віталій | Вікторович | 241 | 4 | 3 | 3 | 4 | 3,5 | 0 |
| 15 | Соловйов | Руслан | Анатолійович | 241 | 3 | 4 | 5 | 4 | 4 | 12 |
Ø Виконати команди Дані - Зведена таблиця.
Ø У першому діалоговому вікні «Майстер зведених таблиць - крок 1 з 4» встановити перемикач в положення, що визначає, де знаходяться дані для зведеної таблиці: у нашому випадку слід клацнути на першому положенні перемикача «У списку або базі даних Microsoft Excel».
Ø Натиснути кнопку Далі, в результаті чого з'явиться друге діалогове вікно «Майстер зведених таблиць - крок 2 з 4».
Ø У полі «Діапазон» вказати, в якому діапазоні знаходяться вихідні дані для зведеної таблиці (у нашому випадку це $ A $ 1: $ K $ 16) і натиснути кнопку Далі.
Ø У третьому діалоговому вікні «Майстер зведених таблиць - крок 3 з 4» необхідно вказати структуру зведеної таблиці, тобто визначити, дані якого поля повинні використовуватися в якості заголовків рядків і яких - як заголовки стовпців, для чого перетягнути їх назви, представлені у вікні у вигляді кнопок, у відповідні області «Рядок» (у нашому випадку - «Група») і «Стовпець »(у нашому випадку - нічого).
Ø У цьому ж діалоговому вікні в область «Дані» перетягнути назву поля (або полів), дані якого підлягають обробці (у нашому випадку - «Стипендія» або «Середній бал» або назви всіх предметів для визначення середнього балу за кожного іспиту).
Ø Поставити правило, за яким повинна здійснюватися обробка, клацнувши двічі по кнопці, переміщеної в область «Дані», і вибравши в діалоговому вікні «Обчислення поля зведеної таблиці» потрібну операцію (для прикладу про стипендії - «Сума», в інших прикладах - «Середнє»), в результаті чого в полі «Ім'я» з'являться назви операції і поля, за яким вона буде виконуватися.
Ø У діалоговому вікні «Майстер зведених таблиць - крок 4 з 4» потрібно задати деякі параметри, що визначають вид зведеної таблиці:
§ в полі вікна «Помістити таблицю в» вказати адресу лівої верхньої комірки таблиці на поточному аркуші або залишити поле порожнім, щоб таблиця розмістилася на початку нового робочого листа;
§ вказати назву зведеної таблиці в полі «Назва таблиці»;
§ встановити прапорці «Загальні підсумки по стовпцях» і «Загальні підсумки по рядках», якщо потрібно;
§ для створення додаткової копії даних встановити прапорець «Зберегти дані з макетом таблиці»;
§ встановити прапорець «Автоматично форматувати таблицю» для використання коштів автоформату Excel.
Ø Для виведення зведеної таблиці на екран після цього клацнути на кнопці Готово.
Для деталізації даних зведеної таблиці необхідно двічі клацнути на назві поля і в діалоговому вікні «Показати деталі» вказати, за яким полю необхідно виконати деталізацію.
| Сума по полю Стипендія | ||
| Група | Прізвище | Всього |
| 219 | 15 | |
| 221 | Абдельгадір | 0 |
| Вовченко | 12 | |
| Грант | 15 | |
| 221 Всього | 27 | |
| 223 | Горець | 0 |
| Іванов | 12 | |
| Клочко | 12 | |
| 223 Всього | 24 | |
| 235 | 12 | |
| 241 | 27 | |
| Загальний підсумок | 105 | |
Наступна зведена таблиця дає можливість проаналізувати результати екзаменів з кожного предмету та порівняти успіхи груп, деталізація тут не виконувалася, але при необхідності це може бути зроблено.
У Excel 2000 Майстер зведених таблиць пропонує виконання тих же дій щодо створення зведених таблиць, але тільки за допомогою трьох діалогових вікон. Створення структури і завдання параметрів зведеної таблиці виконується після натискання кнопок Макет і Параметри в діалоговому вікні «Майстер зведених таблиць - крок 3 з 3».
Excel надає можливість підбити проміжні і остаточні підсумки по полях БД, вставивши необхідні рядки для підрахованих сум. Крім того, Excel може відображати БД в режимі структури, що дозволяє виводити на екран лише проміжні підсумки.
Додавання підсумків здійснюється після попереднього сортування БД за потрібного поля (наприклад, за номером групи) наступною послідовністю дій:
| Група | Дані | Всього |
| 219 | Середнє по полю Висш.матем. | 4 |
| Середнє по полю Інформ. | 3,666666667 | |
| Середнє по полю Історія України | 3,333333333 | |
| Середнє по полю Фізика | 4 | |
| 221 | Середнє по полю Висш.матем. | 3,666666667 |
| Середнє по полю Інформ. | 4,666666667 | |
| Середнє по полю Історія України | 4,333333333 | |
| Середнє по полю Фізика | 4 | |
| 223 | Середнє по полю Висш.матем. | 4 |
| Середнє по полю Інформ. | 3,666666667 | |
| Середнє по полю Історія України | 4 | |
| Середнє по полю Фізика | 4 | |
| 235 | Середнє по полю Висш.матем. | 3,333333333 |
| Середнє по полю Інформ. | 3,666666667 | |
| Середнє по полю Історія України | 4,333333333 | |
| Середнє по полю Фізика | 3,333333333 | |
| 241 | Середнє по полю Висш.матем. | 3,666666667 |
| Середнє по полю Інформ. | 4,333333333 | |
| Середнє по полю Історія України | 4 | |
| Середнє по полю Фізика | 4 |
Ø Вибрати Дані - Підсумки, в результаті чого БД буде автоматично виділена і на екрані з'явиться діалогове вікно «Проміжні підсумки».
Ø У рядку «При кожній зміні в» вказати поле, при зміні значення якого слід підводити проміжні підсумки, для чого клацнути на стрілці праворуч і вибрати потрібну назву поля (наприклад, «Група» для нашої БД).
Ø У рядку «Операція», клацнувши на стрілці праворуч, вибрати в який з'явився переліку можливих операцій необхідну (наприклад, «Середнє»).
Ø Для виконання тієї ж операції з даними в інших полях необхідно зазначити їх назви, клацнувши в списку рядки «Додати підсумки по» (наприклад, назви всіх предметів і поля Ср.балл).
Ø Переконатися, що параметр «Замінити поточні підсумки» встановлений і клацнути на кнопці ОК.
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.
За допомогою БДФункцій ДМАКС і ДМІН можна знайти максимальний та мінімальний елемент стовпця для записів, які відповідають критерію. Функції БДСУММ і ДСРЗНАЧ дозволяють знайти суму та середнє значення елементів вказаного поля, відповідних записів, які відповідають критеріям.
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.Премія.
Відповідно до умов премія нараховується співробітникам, які пропрацювали певний час на фірмі. Для співробітників зі стажем від 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. До виплати. Значення визначається як різниця нарахованої суми і прибуткового податку.
К_вип = НС-ПН
В результаті проектування бази даних отримано ескіз таблиці такого вигляду.
Рис. 2.2. Ескіз таблиці для заповнення бази даних.
У таблиці вихідні дані відзначені синім кольором, а обчислювані значення - червоним.
Після форматування заголовків стролбцов приступаємо до заповнення 1-го рядка. Вводимо початкові дані і в обчислюваних полях записуємо розрахункові формули.
2.2. Заповнення таблиці за допомогою Майстра форм.
Подальше заповнення даних виконуємо за допомогою пунктів меню Дані Форма (у цей момент курсор повинен бути встановлений на комірці в області створюваної таблиці). При цьому відкривається вікно діалогу Майстра форм із заповненою першим записом вихідної бази даних.
Клацаючи на клавіші «Додати» у вікні діалогу і послідовно заповнюючи порожні поля вихідними даними, створюємо вихідну базу даних.
у клітинці збережеться відкоригована інформація. Додамо до вихідної базі рядок, що містить інформацію про робочий Васєчкіна і виправимо прізвище Іванов на Іванченко в 1-й запису. При редагуванні можна також користуватися вікном діалогу Майстра форм.
2.4.Начальная обробка даних.
Формули для обчислення сум в осередках F27, G27, H27, I27, J27 будуть виглядати відповідним чином.
F27 = СУММ (F7: F26); G27 = СУММ (G7: G26); H27 = СУММ (H7: H26);
I27 = СУММ (I7: I26) J27 = СУММ (J7: J26)
G27 = СУММЕСЛІ (E7: E26;> 10; G7: G26)
Результат використання формули наведено на Рис. 2.10.
Сортування за зростанням Сортування за спаданням
Ріс.2.11. Піктограми сортування.
При цьому курсор повинен бути встановлений в полі, яке будемо сортувати. Для сортування за зростанням за полем «Стаж» встановимо курсор на клітинку в цьому полі і виберемо напрямок сортування «за зростанням». Результат сортування представимо на рис.2.12.
Рис.2.12. Сортування по полю «Стаж» за зростанням.
Результат виконання операції по додаванню підсумків відобразимо на Ріс.2.16. Аналогічні дії виконаємо для додавання підсумків по полю «Спеціальність». Відсортуємо вихідну базу по полю «Спеціальність» по зростанню і при кожній зміні в цьому полі додамо проміжні підсумки. Визначимо сумарні значення нарахованої суми і прибуткового податку за спеціальностями. Для цього у вікні діалогу «Проміжні підсумки» виберемо поле, зміни в якому призведуть до додавання підсумків у таблицю. Це поле «Спеціальність», і встановимо операцію для виконання («Сума»). Встановимо у вікні діалогу, для яких полів буде виконуватися ця операція (Нараховано, По / нал). Результати відобразимо на Ріс.2.17.
Виберемо з вихідної бази записи, які відповідають наступним критерієм відбору: Спеціальність «зварювальник». Встановимо цей критерій у вікні діалогу на Рис. 2.23.
Рис. 2.23 Встановлення критерію фільтрації.
Далі встановимо фільтр для вибору інформації з вихідної бази по полю стаж. Необхідно відібрати робітників зі стажем від 5 до 10 років Встановимо фільтр використовуючи команди меню Дані Фільтр Автофільтр і розкриваємо список у полі, за яким передбачається виконувати фільтрацію. Виберемо зі списку параметр «Умова». У вікні діалогу, установимо критерії для фільтра .. Для поля стаж використовуємо оператори «більше або дорівнює» і менше або дорівнює »і задаємо числові значення відповідно 5 і 10.
Рис. 2.26. Результат роботи фільтру по полю стаж.
Для побудови наступного даграмми з переліку можливих видів у списку Майстра діаграм виберемо різновид «Графік». Для всіх співробітників фірми побудуємо графік розподілу грошових виплат.
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. Проектування бази даних.
На етапі проектування бази даних задаємо структуру бази, визначаємо кількість, найменування і типи полів бази, визначаємо для обчислюваних даних формули, за якими вони обчислюються.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.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. Сортування бази даних.
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.Для побудови наступного даграмми з переліку можливих видів у списку Майстра діаграм виберемо різновид «Графік». Для всіх співробітників фірми побудуємо графік розподілу грошових виплат.