Робота з базами даних в Мicrosoft Еxcel

КУРСОВА РОБОТА

"Робота з базами даних в Мicrosoft Еxcel"

Вступ

Завдяки структурі свого робочого листа Excel 2000 є ідеальним інструментом для організації зв'язаної інформації в список. На робочому листі Excel список являє собою набір послідовних рядків, що містять визначену інформацію. Чисто теоретично список Excel аналогічний таблиці Access (чи іншої програми управління базами даних). Кожен стовпець списку називається полем, а кожен рядок – записом даних. Заголовки у верхньому рядку являють собою назви полів. У будь-якім полі можна вводити текст, числа, дати, формули чи гіперзв'язки.

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

Створюючи новий список, потрібно дотримуватись наступних основних принципів.

• Створювати тільки один список на одному робочому листі. Результати багатьох засобів роботи зі списками в Excel (включаючи Автофильтр) залежать від здатності правильно виділити список на робочому листі. Якщо необхідно створити кілька списків в основній робочій книзі, потрібно розмістити їх на різних робочих аркушах.

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

• Не залишати в списку порожні рядки чи стовпці. У противному випадку при виконанні сортування чи пошуку Excel проігнорує всі дані, розташовані нижче порожніх рядків чи правіше порожнього стовпця. Наявність порожніх комірок допускається.

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

• Щоб спростити введення даних, слід закріпити на екрані рядок заголовків.

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

1. Створення баз даних в Мicrosoft Еxcel

База даних являє собою упорядковану інформацію, об'єднану в єдине ціле. На мал. 1 показаний приклад бази даних. Інформація в базі даних звичайно розміщається і зберігається в рядках і стовпцях, як у таблиці. На мал. 1 представлений список з адресами одержувачів у формі бази даних. У кожен рядок занесене ім'я, адреса, місто, штат і поштовий індекс. Оскільки список з адресами одержувачів являє собою інформацію, упорядковану певним чином (в один стовпець занесені імена, в іншій – адреси, у третій – назви міст і т.д.), він і є базою даних.

Мал. 1

Рядки в базі даних називаються записами, а стовпці – полями. Щоб було зрозуміліше, базу даних можна представити у виді картотеки, показаної на мал. 2. Кожна картка в шухляді – це запис у базі даних, а кожна категорія інформації на картці – це поле. На картках у шухляді інформація в кожен запис заноситься по шести полях: ім'я, прізвище, адреса, місто, штат і поштовий індекс. Оскільки кожна картка в шухляді містить інформацію того самого типу, картотеку можна назвати базою даних.

Мал. 2

У Excel база даних будується за аналогією з рядками і стовпцями, причому стовпець таблиці є полем, а кожен рядок – окремим записом. Інформація з кожного поля заноситься в окрему комірку таблиці. Наприклад, у таблиці, представленої на мал. 1, імена занесені в стовпець із заголовком Ім'я, прізвища занесені в стовпець із заголовком Прізвище і т.д. Для того щоб приступити до створення бази даних, помістіть курсор в комірку верхньої частини таблиці і введіть у рядку назви полів.

Створення бази даних

Щоб створити базу даних у робочій таблиці Excel, необхідно виконати наступне:

У порожній рядок таблиці ввести назви полів.

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

Для того щоб ввести в комірку цифри (наприклад, поштовий індекс), поставте спочатку апостроф.

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

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

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

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

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

2. Робота з записами в базі даних

Записи в базу даних додаються і редагуються шляхом введення даних безпосередньо в комірки, а знищуються з бази даних шляхом їхнього виділення і виконання команди Правка Ю Удалить. Однак більшість користувачів воліють знаходити і редагувати дані за допомогою форми, що надає Excel для введення і перегляду даних. Для того щоб переглянути дані на екрані за допомогою форми, необхідно помістити курсор у будь-яку комірку бази даних і виконати команду Данные Ю Форма. На екрані з'явиться форма з полями бази даних, показана на мал. 3.

Мал. 3

Додавання нових записів

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

Редагування записів

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

Видалення записів

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

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

Пошук даних за критерієм

Альтернативним способом пошуку даних у базі є визначення у формі даних критеріїв пошуку. Критерії пошуку визначають дані, які потрібно знайти. Наприклад, у великій базі даних, у якій зберігаються імена й адреси, необхідно знайти всі записи, у яких зазначене певне місто. Для добору потрібних даних використовується умова, що представляється у виді формули. За допомогою цього критерію при роботі з базою даних, у яку введені значення витрат, можна знайти всі записи про витрати, що перевищують 5000 грн. Для цього потрібно ввести >5000. Вказуючи критерій у формі даних, можна використовувати оператори порівняння (ті самі оператори, що використовуються у формулах, введених в комірки робочої таблиці). У табл. 1 перераховані оператори і їхнє значення.

Таблиця 1. Оператори порівняння

Оператор	Значення
=	Дорівнює
>	Більше
>=	Більше або дорівнює
<	Менше
<=	Менше або дорівнює
<>	Не дорівнює

Щоб знайти окремі записи, використовуючи критерій пошуку, необхідно виконати наступне:

1. Помістити курсор у будь-яку комірку бази даних.

2. Виконати команду Данные Ю Форма, щоб вивести на екран форму даних.

3. Клацнути на кнопці Критерии. У правому верхньому куті форми буде написане слово Критерии, а кнопка Критерии заміниться кнопкою Правка.

4. Ввести критерії пошуку у відповідні поля. Необхідно заповнити тільки ті поля, по яких буде здійснюватися пошук. Наприклад, якщо потрібно знайти всі записи, що мають відношення до Сан-Франциско, то слід ввести у поле Місто слово San – Francisco.

5. Натиснути клавішу <Enter> або клацнути на кнопці Правка, щоб повернутися до вихідної форми.

6. Для переходу між відібраними записами використовується кнопки Назад і Далее.

7. Завершивши перегляд записів, необхідно клацнути на кнопці Закрыть.

У критеріях можна використовувати символи підстановки * і?. Знак питання позначає будь-який символ, а зірочка – послідовність довільних символів. Наприклад, критерій Н? ll, введений у поле Ім'я, дозволить брати імена Hall, Hill і Hu // , а критерій *der – слова, що закінчуються на der (такі, як chowder і loader).

3. Сортування бази даних

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

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

При сортуванні Excel не враховує регістр символів. Порожні комірки розміщаються наприкінці (не важливо, як виконується сортування: по зростанню чи по спаданню). Для того щоб сортування виконувалося правильно, необхідно вказати поля, що будуть визначати новий порядок записів. Такі поля називаються ключовими. Іноді необхідно відсортувати базу даних відразу по декількох полях. Наприклад, якщо необхідно розмістити записи в базі даних в алфавітному порядку, використовуючи поле Прізвище в якості ключового, те після сортування всі прізвища будуть розміщені в алфавітному порядку, а імена – у довільному. Але можна відсортувати базу даних, використовуючи в якості першого ключового поля поле Прізвище, а в якості другого ключового поля – поле Ім'я. Щоб відсортувати базу даних потрібно виконати команду Данные Ю Сортировка.

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

Якщо потрібно відсортувати тільки частину записів у базі даних, слід виділити їх. Якщо ж необхідно відсортувати всю базу даних – помістити курсор у будь-якій її комірці.

Виконати команду Данные Ю Сортировка, щоб вивести на екран діалогове вікно Сортировка диапазона, як показано на мал. 4.

Мал. 4

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

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

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

6. Клацнути на кнопці ОК, щоб виконати сортування.

Іноді необхідно відсортувати базу даних по більш ніж трьох полях. Припустимо, що створено великий список з адресами в Excel і необхідно відсортувати цю базу даних по штатах, містам, прізвищам і, нарешті, по іменах. Оскільки в діалоговому вікні Сортировка диапазона передбачене сортування максимум по трьох полях, можна припустити, що таке сортування неможливе. Насправді його можна виконати поетапно. Для цього потрібно почати із сортування по тим полям, що мають найменший пріоритет, і послідовно виконувати сортування по більш важливих полях. Першим у кожній групі сортування слід вказати саме важливе поле. Наприклад, якщо спочатку потрібно виконати сортування по полю Прізвище, то його варто вказати в списку Сортировать по. Потім необхідно виконати сортування по полю Ім'я, виходить, його варто вказати в полі опції Затем по. Потім слід виконати сортування ще раз, вказавши в списку Сортировать по поле Штат, у списку Затем по-поле Місто і, нарешті, у списку В последнюю очередь по-поле Прізвище.

Команда Автофильтр

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

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

Для того щоб відібрати запису за допомогою команди Автофильтр, слід виконати наступне:

Розмістити курсор у будь-якій комірці бази даних.

Виконати команду ДанныеЮФильтрЮАвтофильтр.

Поруч з назвою кожного поля з'явиться кнопка розкриття списку.

Клацнути на кнопці розкриття списку потрібного поля, що містить усі значення цього поля. Вибрати значення, що повинне містити це поле. Також можна вибрати пункт (Условие…), щоб визначити більш складний критерій.

Після вибору всіх потрібних значень Excel покаже тільки ті записи, що містять у цьому полі обрані значення. Наприклад, на мал. 5 показано, що були відібрані тільки записи, в поле Штат яких зазначене МО, а в поле Місто – Kansas City. Для того щоб відібрати такі записи, не досить просто вибрати Kansas City зі списку поля Місто, оскільки місто з такою назвою є як у штаті Канзас, так і в штаті Міссурі. Якщо вказати кілька умов, яким повинні задовольняти значення у визначених полях записів, то після виконання команди Автофильтр залишаться тільки ті записи, що задовольняють заданим умовам.

Мал. 5

Друк звіту

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

Використання складних критеріїв

По команді Автофильтр можна виконувати відбір записів, що задовольняють більш складним, критеріям (наприклад записи тільки визначеного діапазону чи записи, що задовольняють критеріям з логічним АБО). Для того щоб визначити складний критерій, слід вибрати пункт (Условие…) зі списку, що розкривається, поруч з назвою поля. Після цього на екрані з'явиться діалогове вікно Пользовательский автофильтр, показане на мал. 6.

Мал. 6

Скористатися опціями цього діалогового вікна можна для того, щоб указати, які дані повинні бути відібрані, і визначити критерій з логічним АБО (наприклад, критерій для добору записів, у поле Штат яких міститься СА чи ТХ). Після цього потрібно вибрати потрібний оператор порівняння з першого списку, що розкривається, і ввести потрібну величину в поле праворуч від нього. Для того щоб використовувати ще один оператор порівняння, необхідно активізувати опцію И або ИЛИ, вибрати оператор із другого списку, що розкривається, і вказати величину в другому текстовому полі.

Відключення команди Автофильтр

Відібравши потрібні записи з бази даних, потрібно не забути відключити команду Автофильтр (виконати команду ДанныеЮФильтрЮАвтофильтр.)

Виконання злиття з даними Excel

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

1. Необхідно скористатися командою Excel Автофильтр для того, щоб відібрати записи, що будуть друкуватися на бланку.

2. Виділити всі дані, що містяться в записах.

3. Виконати команду ПравкаЮКопирование.

4. Перейти до Word для Windows і відкрити новий документ.

5. Виконати команду ПравкаЮВставить, і дані з Excel будуть представлені в Word у виді таблиці.

6. Зберегти цей документ під визначеним ім'ям.

7. Відкрити документ, що буде використовуватися в якості основного (чи створити новий документ із потрібним текстом).

8. Виконати команду СервисЮСлияние, щоб вивести на екран діалогове вікно Слияние.

9. Виконати інструкції, зазначені в цьому діалоговому вікні. Коли необхідно буде вказати документ із вихідними даними, слід вибрати той документ Word, у якому зберігаються дані з Excel.

Розробка баз даних

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

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

Дані й атрибути

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

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

Стадії розробки бази даних

Розробка бази даних у Excel здійснюється в двох стадіях:

визначення даних (аналіз існуючих даних);

уточнення даних (добір необхідних даних),

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

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

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

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

Висновок

Підсумовуючи вищесказане, можна з впевненістю твердити, що програма Мicrosoft Еxcel дозволяє користувачам ефективно створювати та обробляти бази даних. Однак для великих і складних баз даних слід використовувати СУБД Мicrosoft Access, так як вона спеціально призначена для таких цілей і містить у собі зручні функції для управління даними. В Мicrosoft Еxcel можна створювати лише невеликі бази даних. Для управління і сортування даних в Мicrosoft Еxcel також є досить зручні функції, зокрема Автофільтр, команди сортування і пошуку інформації, зручно організований ввід та знищення даних.

Створення баз даних вимагає від користувача певних знань та кваліфікації, зокрема:

знання самої програми Еxcel, її основних функцій та можливостей;

розуміння структури бази даних, знання способів організації даних;

практичні навички при роботі з базою даних.

Використана література

Ботт Эд, Леонард Вудди. Использование Microcoft Office 2000. специальное издание: Пер с англ.: Уч. пос. – М.: Издательский дом «Вильямс», 2000. – 1024 с.: ил. – Парал. тит. англ.

Руденко В.Д., Макарчик О.М., Паталанжоглу М.О. Практичний курс інформатики / За ред. Мадзігона В.М. – К.: Фенікс, 1997. – 304 с.

Гаевский А.Ю. Самоучитель работы на персональном компьютере. – К.: А.С.К., 2001. – 416 с.: ил.

Д. Тейлор, Дж. Мишель, Дж. Пенман. Microcoft Office 2000: Пер с англ.: В. Усманов, А. Сергиенко. – Издательство «Питер Ком», 2000. – 980 с.: ил.