Методи роботи з базами даних у Microsoft Excel

ЗМІСТ

2. Теоретичні положення.

2.2. Списки Excel як бази даних.

2.3. Перевірка даних під час введення.

2.4. Сортування даних.

2.5. Проміжні підсумки у базі даних.

2.7. Розширений фільтр.

3. Порядок виконання.

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

1. МЕТА РОБОТИ

Цілі:

  • підвищення рівня розуміння теми "Бази даних у програмі Microsoft Excel";
  • оволодіння спеціальними технічними вміннями конструювання та використання реляційної бази даних на рівні їх вільного використання;
  • розвиток навичок самостійної роботи та здатності застосувати отримані знання на практиці при розробці власної бази даних.

2. ТЕОРЕТИЧНІ ПОЛОЖЕННЯ

2.1. Загальні положення

Для обліку даних про співробітників на підприємствах використовують найрізноманітніші методи. У одних організаціях існують журнали обліку, куди інформація вноситься вручну, інших застосовуються класичні бази даних обліку кадрів, у третіх використовуються СУБД Access. Але здебільшого на невеликих підприємствах облік даних співробітників ведеться в електронних таблицях Microsoft Excel.

У запропонованому посібнику розглядаються основні можливості, що надаються Excel, для роботи з базами даних на прикладі списку "Співробітники", що містить інформацію про працівників певного умовного підприємства. Така база даних зручна з таких причин:

2.2. Списки Excel як база даних

Додаток Microsoft Excel має багаті вбудовані засоби для обробки та аналізу даних. Аналогом простої бази даних Excel служить список.

Список – це група рядків таблиці, що містить пов'язані дані.

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

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

2.3. Перевірка даних під час введення

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

  1. Виділити осередки стовпця, котрого встановлюється перевірка введення.
  2. На стрічціДані у групіРобота з даними вибрати командуПеревірка даних.
  3. На вкладціПараметри в областіУмови перевірки вибратиТип даних :Будь-яке значення (використовується для скасування перевірки введення),Ціло число,Справжня,Список,Дата,Час,Довжина тексту таІнший (формат, для якого можна задати власну формулу, наприклад, "м" або "ж"). При виборі значення внизу вікна з'являються додаткові поля для введення умов або обмежень – наприклад, мінімального та максимального допустимогозначення.
  4. На вкладціПовідомлення для введення можна встановити прапорецьВідображати підказку, якщо осередок є поточним і ввести повідомлення, щоб воно з'являлося на екрані при виділенні осередків.
  5. На вкладціПовідомлення про помилку можна встановити прапорецьВиводити повідомлення про помилку, щоб задати тип повідомлення про помилку, яке з'явиться при введенні в комірку неприпустимого значення.

2.4. Сортування даних

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

Щоб відсортувати список треба:

  1. Встановити курсор у комірку списку.
  2. Виконати командуСортування на стрічціДані у групіСортування та Фільтр.
  3. У діалоговому вікніСортування вибрати поле, яким буде відбуватися сортування; тип сортування (за значенням, колір комірки, колір шрифту, значок комірки) та порядок (за зростанням, спаданням, що налаштовується).

Примітка. Вибірнастроюваного порядку дозволяє задати нестандартний порядок сортування. Для цього треба в діалоговому вікніСписки (рис. 2) вибратиНОВИЙ СПИСОК, в поліЕлементисписку ввести значення, що утворюють користувальницький порядок сортування (наприклад, АОП, ФЕО, ІВЦ, ІТО, МПО), після чого послідовно вибрати кнопкиДодати таОК.

2.5. Проміжні підсумки у БД

Для організації списків використовують командуПроміжні підсумки на стрічціДані у групіСтруктура, яка дозволяє:

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

Перед викликом командиПідсумки список обов'язково треба відсортувати по полю, яке використовуватиметься для угруповання.

Режим структури, в якому надається список після виконання командиПідсумки, дозволяє переглядати різні частини списку за допомогою кнопок, розташованих на лівому полі (рис. 3).

роботи

Мал. 3. Перегляд списку у режимі структури

Кнопки, розташовані у верхньому лівому кутку, визначають кількість рівнів даних, що виводяться. Кнопки зі значками "+" і "-" призначені для згортання розгортання готельних груп.

Щоб видалити проміжний та остаточний результат, потрібно повторно виконати командуПроміжні підсумки, а потім клацнути по кнопціПрибрати все.

2.6. Автофільтр

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

Щоб встановити або прибрати автофільтр, потрібно на стрічціДані у групіСортування та фільтр вибрати командуФільтр. Після цього натиснути кнопку зі стрілкою біля назви будь-якого поля, щоб розкрити список його елементів і вибрати значення, що відображаються або задати умову відбору. На екрані з'являться лише записи, які відповідають заданій умові. У разі потреби можна продовжити фільтрацію, натискаючи кнопки зі стрілками на інших полях.

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

Показати всі записи по всіх полях, не прибираючи фільтр, командаОчистити.

Для даних різного типу існують додаткові автофільтри, які знаходяться у списку критеріївТекстові фільтри,Числові фільтри,Фільтри за датою і т.д.

Якщо виділити якесь числове поле (наприклад, Вік), а у списку критеріїв вибратиЧислові фільтри, то з'явиться список додаткових фільтрів (рис. 4), які дозволяють:

  • задати критерій у вигляді нерівності - критеріїрівно,не дорівнює,більше,більше аборівно,менше,меншеаборівно,між ;
  • вивести перші N значень – критерійПерші 10 : після вибору в спискуЧислових фільтрів командуПерші 10…, необхідно у вікні вказати число значень (N), а також спосіб обчислення: кількістьелементів списку,% від кількості елементів ;
  • визначити умову за середнім значенням у зазначеному стовпці - критеріїВище середнього,Нижче середнього ;
  • фільтр, що самостійно задається - критерійНастроюється фільтр.

даних

Мал. 4. Додаткові числові фільтри

Фільтр, що настроюється, дозволяє задати критерії з однієї або двох умов.

Проста умова складається з імені поля (атрибута); варіанти умови (рівно, не одно, більше, менше, більше або одно, менше або одно; починається з, не починається з, закінчується на або не закінчується на; містить, не містить); слова чи числа для порівняння.

Складна умова складається з двох простих, з'єднаних спілкамиІ абоАБО.

При написанні значень в умовах порівняння у фільтрах можна використовувати знаки підстановки (Таблиця 1).

Підстановочні знаки

Знак

Значення

один будь-який знак

будь-яку кількість символів

використовують, коли в тексті треба знайти знаки підстановки (символи «?», «*» або «

2.7. Розширений фільтр

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

Перед викликом командиРозширений фільтр, необхідно сформувати критерії. Для зручності краще формувати критерії на окремому аркуші (можна дати йому ім'я, наприклад, Критерії) та давати критеріям імена Кр1, Кр2 тощо.

Основне правило : якщо критерії пов'язані між собою операцієюІ, то вони повинні розташовуватися в одному рядку, а якщоАБО, то в різних.

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

Відновити вихідний список можна, вибравши на стрічціДані у групіСортування та фільтр командуОчистити.

3. ПОРЯДОК ВИКОНАННЯ РОБОТИ

1. Ознайомитись з теоретичними положеннями.

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

Примітка. Технологія створення бази даних конфігурації будь-якої складності та обсягу представлена ​​в додатку 3. Заповнити базу даних правдоподібними несуперечливими даними.

4. Використовуючи побудовану модель бази даних, виконайте:

  • сформувати структуру бази даних;
  • сформувати підсумки за одним або двома атрибутами;
  • побудувати діаграму;
  • сформулювати та реалізувати запити на відбір данихза допомогою автофільтра, при цьому використовувати простий автофільтр за значенням і автофільтр з додатковими критеріями для даних різних типів (числові, текстові, дата / час), а також настроюваний автофільтр;
  • сформулювати та реалізувати запити на пошук та відбір даних за допомогою розширеного фільтра таким чином, щоб створені критерії містили 2-3 умови, що відносяться, як мінімум до двох різних полів, і серед критеріїв були обчислювані.

5. Подати викладачеві звіт про виконану роботу в електронному чи друкованому вигляді. Робота розрахована на 6 академічних годин.

4. КОНТРОЛЬНІ ПИТАННЯ

5. СПИСОК РЕКОМЕНДУЄМОЇ ЛІТЕРАТУРИ

  1. Кошелєв В. Excel 2007. Ефективне використання. - М: Біном. Лабораторія знань, 2008 – 544 стор.
  2. Слєтова Л. Excel 2007 - М.: "ЕКСМО", 2007 - 336 стор.
  3. Сурядний А., Глушаков С. Microsoft Excel 2007: самовчитель, 2-ге видання. - М.: АСТ, 2008 - 416 стор.