Лабораторна робота 4 (4 год.)

1 2 3 4 5 6 7

Тема роботи: „Робота з таблицями баз даних”

Мета роботи: навчитись працювати зі списками бази даних, створювати їх та редагувати

Теоретична частина

При роботі зі списками в Excel необхідно дотримуватись наступних правил:

1. Кожен стовпчик таблиці мповинен містити інформацію одного типу.

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

3. У список не варто вводити порожніх рядків та стовпців.

4. Найкраще кожен список розміщувати на окремому робочому аркуші.

5. Не розміщуйте жодних даних праворуч та ліворуч від списку, оскільки вони можуть заховатися в процесі фільтрації даних.

9. Створення та редагування списку (бази даних).

Приклад бази даних, створеної у Excel, подано на рис. 3.1.

Мал. 3.1. Приклад створеного списку

Перед тим як приступати до створення списку на робочому аркуші Excel, потрібно добре продумати його структуру і визначити, які дані будуть входити у нього. Створення списку має починатися з формування заголовків стовпців. Для цього необхідно ввести назви полів у порожній рядок робочого аркуша електронної таблиці та вибрати ширину полів так, щоб у них поміщалась уся інформація, яка буде вводитись у комірки стовпця. Якщо заголовки оформлені іншим шрифтом, начерком, кольором, тоді програма автоматично визначатиме, що вводиться список.

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

Порядок введення записів в список ролі не відіграє. Не варто вводити рядків дефісів та символів підкреслення в таблицю.

Якщо Вам потрібно створити велику таблицю (яка містить багато записів), то зафіксувати заголовок таблиці можна виконавши команду Закрепить область меню Окно.

Таблиці-списки можна створювати та редагувати так само, як і звичайні таблиці, тобто за допомогою відповідних клавіш керування курсором. Якщо у списку містяться значення, які повторюються, то можна скористатися функцією Автозаповнення, яка активізується після встановлення опції Автозаполнение значений ячеек на вкладці Правка, діалогового вікна Параметры (воно відкриється після виконання команди меню Сервис Þ Параметры). При введенні даних у список програма перевірить вміст інших комірок даного стовпця. Якщо в цих комірках знаходиться текст, перші символи якого співпадають з символами, які ввів користувач, то введення даних завершиться автоматично. Після натискання клавіші [ Enter ] користувач підтверджує введення запропонованого програмою тексту. Якщо потрібно зігнорувати пропозицію програми, то необхідно продовжувати введення даних.

Функції списку створюють додаткову можливість для обробки даних за допомогою використання діалогового вікна форми даних (мал. 3.2).

Мал. 3.2. Діалогове вікно форми списку.

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

Для того щоб викликати вікно форми, активізуйте будь-яку комірку таблиці. У меню Данные виберіть пункт Форма. У діалоговому вікні наведено загальну кількість записів списку та вказаний порядковий номер відкритого запису. Кожен запис списку буде подано у вигляді формуляра з відповідними полями. За допомогою відповідних кнопок (Добавить, Удалить, Вернуть, Назад, Далее, Закрыть ) можна виконувати операції над записами списку.

При відкритті діалогового вікна форми (назва якого відповідає назві аркуша, з яким працює користувач), за замовчуванням у ньому з’являється інформація першого запису списку. Для того щоб відредагувати цей запис, потрібно внести зміни у відповідні поля діалогового вікна. Переміщатись полями діалогового вікна можна за допомогою клавіші [ Tab ] (для переміщення в наступне поле) та комбінації клавіш [ Shift+Tab ] (для повернення у попереднє). Зміни вносяться у запис при переході в наступний запис або після натискання клавіші [ Enter ].

Якщо Вам потрібно перейти на інший запис списку, то це можна здійснити одним із запропонованих нижче способів:

– за допомогою смуги прокрутки;

– за допомогою кнопок Назад та Далее;

– за допомогою клавіш [ PgUp ] та [ PgDn ];

– за допомогою клавіш керування курсором [↑] та [↓].

Кнопка Вернуть та клавіша [ Esc ] призначені для відміни внесених змін у запис.

Зауваження. Якщо запис, який видно у діалоговому вікні, не змінювати, то кнопка Вернуть недоступна, а клавіша [ Esc ] призведе до закриття діалогового вікна.

За допомогою кнопки Закрыть діалогове вікно можна закрити. При цьому всі зміни в записах зберігаються.

Натискання кнопки Удалить знищить запис, який видно у діалоговому вікні форми. При цьому програма повідомить про виконання знищення запису. Пам’ятайте, що знищений запис неможливо відновити.

Якщо Вам потрібно додати новий запис до списку, натисніть кнопку Добавить, і з’явиться порожня форма для заповнення інформацією кожне з полів таблиці.

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

10. Перевірка даних, які вводяться у список

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

Мал. 3.3. Діалогове вікно для задання умов на введення даних

На вкладці Параметры можна задати тип даних та допустимі значення. Для цього потрібно розкрити список Тип данных та вибрати один з таких типів: Любое значение, Целое число, Действительное, Список, Дата, Время, Длина текста, Другой. Після цього діалогове вікно зміниться і можна буде ввести додаткову інформацію для вибраного типу.

Якщо ви хочете задати список допустимих значень, то після вибору типу Список, вкажіть діапазон робочого листа, де знаходиться список допустимих значень.

Для того щоб при введенні відбувалась перевірка даних за якоюсь формулою, виберіть тип Другой та введіть потрібний вираз в поле Формула (наприклад, якщо потрібно, щоб при введенні даних в список, представлений на мал. 3.1, перевірялась умова на введення даних полів Дата народження та Дата прийняття на роботу за формулою Дата народження < Дата прийняття на роботу у поле Формула, необхідно ввести вираз G4<H4).

Для створення підказки для користувача при введенні необхідних даних виберіть вкладку Сообщение для ввода діалогового вікна Проверка вводимых значений. Тут можна ввести заголовок і текст повідомлення. Коли користувач виділить комірку, на яку накладена ця умова, повідомлення з’явиться поруч з нею.

Якщо потрібно створити повідомлення, яке буде інформувати користувача про введення неправильних даних (даних, які не відповідають заданим на вкладці Параметры умовам), відкрийте вкладку Сообщение об ошибке діалогового вікна Проверка вводимых значений та введіть заголовок повідомлення. Крім того, з випадного списку Вид можна вибрати тип повідомлення, які супроводжуються відповідними малюнками-значками та кнопками, що дозволяють вибрати той чи інший варіант дій для продовження роботи.

11. Пошук записів

Для пошуку відповідних записів за допомогою форми даних, використовуються критерії пошуку, тобто умови, які повинні задовольняти шукані записи. Якщо натиснути кнопку Критерии, то з’явиться діалогове вікно форми даних без записів (мал. 3.4), яке відрізняється від попереднього лише тим, що замість кнопки Критерии знаходиться кнопка Правка, а замість Удалить – Очистить. Кнопка Добавить у ньому завжди неактивна, кнопки Очистить та Вернуть використовуються для редагування критеріїв пошуку, за допомогою кнопок Назад, Далее та Правка відбувається перегляд записів, які задовольняють умови пошуку, кнопка Закрыть дозволяє завершити роботу з формами.

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

Мал. 3.4. Діалогове вікно для задання критеріїв пошуку записів.

У критеріях можна використовувати символи шаблонів (”*” – для заміни будь-якої кількості довільних символів та ”?” – для заміни не більше, ніж одного символу) (наприклад, для пошуку усіх працівників, прізвища, яких починаються з літери К, потрібно в поле Прізвище ввести критерій К*).

У випадку необхідності пошуку записів за числовими умовами використовують логічні оператори порівняння (наприклад, для пошуку працівників, у яких навантаження не перевищує 500 год, у полі Навантаження потрібно ввести критерій <=500).

Ввівши усі потрібні критерії пошуку, натисніть одну із кнопок Назад, Далее або Правка. Після цього в діалоговому вікні відобразиться перший запис, який задовольняє заданим умовам. Переглянути усі такі записи можна за допомогою кнопок Назад та Далее.

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

12. Сортування даних в таблиці

Для того щоб відсортувати рядки по одному (ключовому) полю, достатньо активізувати будь-яку комірку цього поля та натиснути одну з кнопок або панелі інструментів (вибір кнопки залежить від способу сортування).

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

Мал. 3.5. Діалогове вікно для задання умов сортування

Зауваження. При застосуванні функцій списку, виділення цілої таблиці відбувається автоматично. Якщо ж потрібно сортувати лише окремий діапазон рядків або стовпців, то перед сортуванням його потрібно виділити.

Якщо перший рядок діапазону містить назви полів, то потрібно ідентифікувати поля за підписами першого рядка, тобто задати опцію подписям (первая строка диапазона) області Идентифицировать поля по. Опція обозначениям столбцов листа цієї області дозволить сортувати записи з урахуванням даних першого запису виділеного діапазону комірок, тобто назви полів будуть розглядатися системою як дані першого запису. Назви полів, по яких має відбуватися сортування, вибирають з випадного списку поля. Для кожного з трьох можливих полів задають порядок сортування записів.

За замовчуванням Excel 2000 не враховує різницю між регістрами в полях. Якщо потрібно враховувати різницю між великими та малими літерами, натисніть кнопку Параметры…, в результаті чого відкриється діалогове вікно Параметры сортировки (мал. 3.6).

Мал. 3.6. Приклад сортування по трьох полях з урахуванням регістра

Діалогове вікно дозволяє встановити такі опції:

§ у полі Сортировка по первому ключу можна задати порядок сортування (звичайне або за списком, створеним користувачем);

§ поле Учитывать регистр дозволяє встановити при сортуванні різницю між великими та малими літерами;

§ розділ Сортировать містить два перемикачі, які дозволяють здійснювати сортування за записами або за полями.

Після встановлення усіх необхідних параметрів сортування і натискання кнопку ОК, користувач побачить у вікні програми відсортований список.

13. Застосування функції автоматичного фільтрування

Для того щоб викликати функцію автофільтра, необхідно помістити вказівник комірки всередину списку та виконати команду меню Данные Þ Фильтр Þ Автофильтр. При цьому поряд з назвами полів з’являться кнопки розкриття списку, який дозволяє встановити критерії фільтрування (мал. 3.7).

Мал. 3.7. Вигляд вікна програми зі списком після активізації функції автофільтра

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

За допомогою елемента (Первые 10) можна вивести на екран 10 записів з найбільшими або найменшими числовими значеннями. Після вибору цього елемента відкривається діалогове вікно (мал. 3.8), в якому можна змінити кількість записів, Які повинні відобразитися, задати, які записи повинні виводитись (максимальні чи мінімальні), а також встановити числове чи процентне обмеження на кількість виведених на екран записів.

Мал. 3.8. Діалогове вікно елемента (Первые 10)

Відбір записів можна продовжувати, додаючи критерії в інших стовпцях. При цьому усі вибрані умови будуть зв’язані між собою логічним ” І ”.

Функція автофільтр дозволяє створювати користувачеві свій автофільтр. Цю можливість надає програма при виборі елемента (Условие…). При цьому відкривається діалогове вікно (мал. 3.9), в якому можна створити комплексні умови, об’єднавши їх за допомогою логічних операторів ” І ” та ” АБО ”. Наприклад, якщо потрібно відобразити записи працівників, прізвища яких знаходяться в діапазоні літер А–Д, то потрібно задати умови: больше А и меньше Д. При цьому не має значення, в якому регістрі Ви введете початкові літери діапазону.

Мал. 3.9. Діалогове вікна для створення автофільтра користувача

Елемент випадного списку (Все) дозволяє знову відобразити усі значення поля (тобто відмінити фільтр). Для відміни фільтрів також можна використати команду меню Данные Þ Фильтр Þ Отобразить все.

14. Застосування розширеного фільтру

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

У комірки під відповідними заголовками, записують відповідні умови. Для об’єднання критеріїв за допомогою логічного оператора ” І ” їх потрібно записувати в одному рядку. Якщо ж умови повинні бути об’єднані логічним ” АБО ”, то критерії записуються у різних рядках.

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

При створенні текстових умов потрібно пам’ятати про таке:

· одна літера в умові означає, що належить вибрати записи, які починаються з цієї літери;

· символи порівняння > та < означають, що треба знайти всі записи, які знаходяться за алфавітом після (>) або перед (<) введеним текстовим значенням;

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

· при створенні умов можна використовувати символи шаблону.

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

При створенні обчислювальних полів потрібно пам’ятати такі правила:

A: заголовок над обчислювальною умовою повинен відрізнятися від будь-якого заголовку стовпця списку; він може бути порожнім або містити будь-який текст;

B: посилання на комірки, які знаходяться за межами списку мають бути абсолютними;

C: посилання на комірки, які знаходяться в списку повинні бути відносними.

Якщо користувач часто використовує фільтрування відповідно до створених критеріїв, то діапазону умов варто присвоїти якесь ім’я.

Після того як створено таблицю критеріїв, необхідно виконати команду меню Данные Þ Фильтр Þ Расширенный фильтр. При цьому відкриється діалогове вікно Расширенный фильтр (мал. 3.10).

Мал. 3.10. Діалогове вікно розширеного фільтра

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

Зауваження. При створенні нової таблиці з вибраних результатів фільтрування записів необхідно пам’ятати, що вся інформація, яка може опинитися в діапазоні нової таблиці, буде знищена і її не можна буде вже відновити.

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

Так само можна задати діапазон умов у відповідному полі діалогового вікна. Якщо ж умови знаходяться в діапазоні, якому присвоєно ім’я, то це значно полегшить встановлення параметрів функції фільтрування.

Зауваження. При заданні діапазону критеріїв потрібно виділяти лише непорожні рядки, оскільки незаповнений рядок інтерпретується програмою як критерій, пов’язаний з іншими логічним ”АБО”. Якщо діапазон критеріїв містить порожній рядок (який відповідає будь-якому значенню), то у результаті буде виведений увесь список.

Ввімкнена опція Только уникальные записи діалогового вікна Расширенный фильтр дозволяє усунути з відфільтрованого списку всі записи, які мають повторення. Дія цієї опції полягає в тому, що на відфільтрований список накладається ще один фільтр, який ховає рядки, що повторюються (ця опція є доступна лише у випадку, коли результат пошуку повинен розміститися у іншому місці аркуша).

Наприклад, для того щоб вивести на екран записи доцентів кафедри ММЕКТ та асистенів кафедри ГН (див. табл. на мал. 3.11), необхідно створити таблицю критеріїв у вигляді, запропонованому на мал. 3.11. Така таблиця може знаходитись безпосередньо після списку або над ним.

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

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

Мал. 3.12. Діалогове вікно розширеного фільтра з заповненими діапазонами

Мал. 3.13. Вигляд екрану після виконання фільтрації

15. Створення обчислювальних умов для пошуку даних

Приклад 1. Знайдемо записів списку, показаного на мал. 3.1, які задовольняють такій умові: оклад працівника має перевищувати середню величину окладів усіх працівників організації.

Дане завдання можна виконати, використовуючи функцію Розширений фільтр. Проте перед створенням діапазону умов уведемо в якусь комірку (наприклад комірку C18) формулу:

= СРЗНАЧ(I4:I16).

У комірку B20 введемо обчислювальну умову = I4>$C$18.

При виконанні пошуку відповідно до цієї умови буде відбуватися порівняння кожного із значень діапазону I4:I16 зі значенням, отриманим у комірці С18. Діапазоном умов будуть комірки B19:B20.

У результаті виконання цього фільтру отримаємо таблицю, показану на мал. 3.14.

Мал. 3.14. Вигляд фрагмента екрану після виконання функції Розширений фільтр.

Зауважимо, що значення, яке з’явилося в комірці з формулою умови, говорить про те, що перший запис задовольняє встановленій умові. Якби у першому записі списку містилась інформація про працівника, оклад якого менший за середній, то в комірці С18 знаходилося б значення ЛОЖЬ.

Такий самий результат можна одержати і іншим способом, який полягає в наступному.

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

=I4>СРЗНАЧ($I$4:$I$16)

у будь-якій комірці таблиці (наприклад в комірці B20). Ця формула безпосередньо посилається на стовпчик Оклад, а не на комірку за межами списку.

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

Задавши необхідні діапазони в діалоговому вікні розширеного фільтру, отримаємо результат, аналогічний до показаного на мал. 3.14.

Приклад 2. Виберемо зі списку (мал. 3.1) записи працівників, які поступили на роботу у віці до 20 років.

Для знаходження таких записів створимо умову у вигляді:

=H4-G4<20*365.

Ця умова віднімає від дати прийому на роботу дату народження та порівнює отриманий результат з кількістю днів, які пройшло за 20 років.

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

16. Створення бази даних на основі таблиці-шаблону

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

Нехай бланк накладної матиме вигляд (мал. 3.15)

Мал. 3.15. Вікно робочого аркуша з бланком накладної.

Запишемо цю накладну у робочу книгу під назвою Накладна-бланк.

Для створення бази даних, в яку будуть входити дані з накладної перебуваючи у вікні відкритої книги Накладна-бланк викличемо програму Мастер шаблонов (Данные Þ Мастер шаблонов).

Зауваження. Якщо цієї команди немає, то потрібно активізувати команду Надстройки меню Сервис і у списку, який з’явиться на екрані, активізувати опцію Мастер шаблонов.

Після того як Ви викликали майстер шаблонів, на екрані з’явиться діалогове вікно першого кроку програми (мал. 3.16), в якому потрібно вибрати зі списку всіх відкритих на даний час робочих книг ту, на основі якої буде створено шаблон (якщо потрібної книги немає, то завершіть роботу з Майстром, завантажте потрібну книгу в оперативну пам’ять системи і знову завантажте майстер шаблонів). У полі Укажите имя шаблона необхідно вказати назву створюваного шаблону та задати повний шлях до місця файлової системи комп’ютера, де він буде знаходитись (за замовчуванням встановлюється шлях до папки Шаблоны, де програма зберігає усі шаблони, а назва шаблону співпадає з назвою робочої книги, на основі якої створюється шаблон).

Мал. 3.16. Діалогове вікно першого кроку Майстра шаблонів

У нашому випадку назвемо шаблон Накладна, а робочою папкою буде кореневий каталог диска D:. Після натискання кнопки Готово відкриється наступне вікно майстра, в якому потрібно задати назву бази даних та повний шлях до неї. Створювана база даних може бути лише у тому форматі, конвертори програм яких встановлені на Вашому комп’ютері (мал. 3.17).

Мал. 3.17. Діалогове вікно другого кроку майстра шаблонів.

Назвемо нашу базу Надходження та розмістимо її у папці Мои документы. Новий документ бази даних буде містити лише одну таблицю або робочий аркуш.

У наступному діалоговому вікні (мал. 3.18) необхідно вказати, для яких комірок потрібно відкрити поля в документі бази даних. Для створення нового поля потрібно вказати адресу комірки, дані з якої будуть копіюватись в базу даних. Для цього клацніть по кнопці мінімізації вікна поля Ячейка, після згортання вікна клацніть в комірці, адреса якої повинна з’явитись у цьому полі. У результаті цього адреса з’явиться у вигляді абсолютного посилання.

Мал. 3.18. Діалогове вікно, в якому створюється структура бази даних.

Якщо комірка, дані з якої повинні опинитися в базі даних, знаходиться на іншому аркуші робочої книги, то необхідно вказати повну адресу цієї комірки (наприклад, Лист1!$С$21).

Після вводу адреси комірки перейдіть в поле Имя поля, в якому задається назва поля бази даних. Програма може сама запропонувати назву, яку, при бажанні, можна змінити.

У наступному вікні майстра можна передбачити можливість додавання даних в документ бази даних з інших робочих книг. Якщо такої потреби немає, то активізуйте опцію Нет, пропустить этот шаг та перейдіть до наступного вікна.

Останнє вікно майстра шаблонів показує шлях до створеного шаблону та бази даних, а також передбачає можливість пересилки шаблону по електронній пошті. Якщо немає такої потреби, натисніть кнопку Готово. Цим Ви завершите процес створення шаблону та структури бази даних.

Після завершення роботи Майстра шаблонів потрібно викликати форму, активізувавши шаблон таблиці. Після цього достатньо заповнити бланк накладної потрібними даними та закрити робочу книгу, вказавши у діалоговому вікні, яке відкриється при закритті таблиці (мал. 3.19 а), бажання створити новий запис у базі даних. Якщо Ви змінювали дані в робочій книзі, яка вже зберігалася раніше, то необхідно вибрати одну з опцій діалогового вікна, яке відкриється після повторного її збереження (мал. 3.19 б):створити новий запис, змінити існуючий, чи закрити книгу без збереження змін.


а	Б
Мал. 3.19. Різні види діалогового вікна, за допомогою якого можна вносити зміни в базу даних

У результаті заповнення трьох бланків накладної утвориться база даних яка знаходиться в папці Мои документы. Зі створеною таблицею можна виконувати всі операції, які дозволяє програма Excel 2000: форматувати дані, комірки, змінювати розміри рядків та стовпців тощо. Приклад створеної таблиці подано на мал. 3.20.

Мал. 3.20. Екранний вигляд бази даних,
яка заповнена за допомогою шаблону Накладна

Послідовність виконання лабораторної роботи

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

2. Задайте умови на введення даних у поле Навантаження так, щоб можна було вводити лише значення між 200 і 1000.

3. Задайте список допустимих значень поля Посада, який складатиметься з чотирьох елементів: асистент, доцент, старший викладач, професор.

4. Передбачте виведення підказки при введенні даних у поле Категорія у вигляді тексту:

п – постійний працівник, с – сумісник.

5. Створіть можливість появи повідомлення при введенні помилкового значення.

6. Заповніть таблицю 10-15 записами.

7. У таблицю ”Облік кадрів” додайте ще дві графи (Вік та Стаж роботи). Розрахуйте вік, стаж для кожного працівника за допомогою функції СЕГОДНЯ(), задавши попередньо відповідним коміркам формат, який відображатиме лише дві цифри року.

8. Розрахуйте оклад, який залежить від посади і навантаження, відповідно до табл. 3.1:

9. Організуйте перегляд записів доцентів, у яких навантаження перевищує 800 год.

10. Перегляньте записи працівників за кафедрами; працівників, які працюють за сумісництвом; працівників, які старші за певний вік; працівників, у яких невеликий (до 2 років) стаж (введіть у відповідні поля форми потрібні відношення > або <).

Таблиця 3.1.

Шкала для розрахунку навантаження

Посада	Кількість годин на одну ставку	Оклад (1 ставка)
асистент
старший викладач
доцент
професор

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

12. Відсортуйте створену таблицю за кафедрами.

13. Відсортуйте записи за декількома полями одночасно (наприклад по кафедрі та по прізвищу або по прізвищу, кафедрі та окладу, тощо), встановлюючи при цьому різний порядок сортування.

14. Відфільтруйте список за допомогою функції автофільтру так, щоб на екран виводились лише записи, що відповідають такій умові: стаж роботи більший за 17 років і менший за 27.

15. Виведіть на екран записи працівників кафедри фінансів, які працюють за сумісництвом (функція автофільтр).

16. Використовуючи розширений фільтр, знайдіть записи працівників, які працюють на посаді доцента, та виведіть ці дані в окреме місце аркуша.

17. Посильте встановлений фільтр, вивівши на екран список тільки тих доцентів, у яких прізвища починаються з літер Б, Д або К та вік більший за 40 років. Виведіть результати на нове місце аркуша.

18. На інший аркуш виведіть дані про працівників кафедри ММЕКТ, які старші за 35 років і стаж яких перевищує 5 років.

19. Відфільтруйте список так, щоб на екран виводився список співробітників, які народились не пізніше 01.01.1956 року і були прийняті на роботу не раніше 01.01.1979 року. Запишіть відфільтровану таблицю на новий аркуш.

20. За даними таблиці обчисліть кількість постійних працівників і сумісників на кожній з кафедр та загальне навантаження викладачів кожної кафедри (за допомогою функцій СУММЕСЛИ та СЧЕТЕСЛИ).

21. Обчисліть середню заробітну плату для певної групи працівників (функція ДСРЗНАЧ), мінімальне та максимальне навантаження (ДМАКС та ДМИН).

Контрольні запитання

1) Що таке список у таблицях Excel 2000?

2) Що таке поле та запис таблиці Excel 2000?

3) Що визначає закінчення списку?

4) Як створити базу даних, використовуючи команду Форма?

5) Які поля знаходяться у діалоговому вікні Форма?

6) Які операції можна виконати за допомогою кнопок діалогового вікна Форма?

7) Для чого служать критерії?

8) Яким чином можна сортувати бази даних в Excel 2000?

9) Як посортувати базу даних по одному полю?

10) Як присвоїти імена полям бази даних?

11) Як переглянути записи, які відповідають простому критерію?