КУРСОВАРОБОТА
«Роботаз базами даних в Мicrosoft Еxcel»
Вступ
Завдякиструктурі свого робочого листа Excel 2000 є ідеальним інструментом дляорганізації зв'язаної інформації в список. На робочому листі Excelсписок являє собою набір послідовних рядків, що містять визначену інформацію.Чисто теоретично список Excel аналогічний таблиці Access (чи іншоїпрограми управління базами даних). Кожен стовпець списку називається полем, акожен рядок – записом даних. Заголовки у верхньому рядку являють собоюназви полів. У будь-якім полі можна вводити текст, числа, дати, формули чигіперзв'язки.
Уразі потреби список можна відсортувати або організувати усередині нього пошуквизначеної інформації і використовувати фільтри для виділення групиінформації, що відповідає заданому критерію. Для складних списків Excelавтоматично створює структури, що дозволяють підсумовувати й аналізувати групизаписів. Великі і складні списки – відмінна відправна точка для записівзведених таблиць і зведених діаграм, за допомогою яких можна перетягувати поляна структурну сторінку для виконання складних задач аналізу даних без побудовиформул.
Створюючиновий список, потрібно дотримуватись наступних основних принципів.
• Створюватитільки один список на одному робочому листі. Результати багатьох засобів роботизі списками в Excel (включаючи Автофильтр) залежать від здатностіправильно виділити список на робочому листі. Якщо необхідно створити кількасписків в основній робочій книзі, потрібно розмістити їх на різних робочихаркушах.
• Створюватиодин рядок заголовків з унікальною назвою кожного стовпця. Використовуючи опціїформатування, слід виділити рядок підписів в області даних. Можна, наприклад,установити великий розмір шрифту і відокремити рядок заголовків границею. Excelпроаналізує це форматування, відокремивши заголовки від іншої частини спискупід час сортування чи підготовки звіту.
• Незалишати в списку порожні рядки чи стовпці. У противному випадку при виконанні сортуваннячи пошуку Excel проігнорує всі дані, розташовані нижче порожніх рядківчи правіше порожнього стовпця. Наявність порожніх комірок допускається.
• Починативведення списку можна з будь-якої комірки, однак не рекомендується залишатипорожні стовпці ліворуч від списку. Можна залишити порожніми кілька рядківугорі списку, щоб згодом створити діапазон умов чи додати заголовок. Не слідзабувати залишити як мінімум один порожній рядок між областю умов і власнесписком.
• Щобспростити введення даних, слід закріпити на екрані рядок заголовків.
При введенні новихданих в існуючий список Excel автоматично застосовує до них форматування іформули попередніх рядків, що звільняє користувача від необхідності зановоформатувати новий рядок. Наприклад, якщо перша комірка попереднього рядкавідформатована напівжирним курсивом, Excel автоматично застосує цей шрифт допершої комірки нового рядка, якщо користувач почне вводити в неї дані.
1. Створення баз даних в Мicrosoft Еxcel
Базаданих являє собою упорядковану інформацію, об'єднану в єдине ціле. На мал. 1показаний приклад бази даних. Інформація в базі даних звичайно розміщається ізберігається в рядках і стовпцях, як у таблиці. На мал. 1 представлений списокз адресами одержувачів у формі бази даних. У кожен рядок занесене ім'я, адреса,місто, штат і поштовий індекс. Оскільки список з адресами одержувачів являєсобою інформацію, упорядковану певним чином (в один стовпець занесені імена, віншій – адреси, у третій – назви міст і т.д.), він і є базою даних.
/>
Мал.1
Рядкив базі даних називаються записами, а стовпці – полями. Щоб булозрозуміліше, базу даних можна представити у виді картотеки, показаної на мал.2. Кожна картка в шухляді – це запис у базі даних, а кожна категорія інформаціїна картці – це поле. На картках у шухляді інформація в кожен запис заноситьсяпо шести полях: ім'я, прізвище, адреса, місто, штат і поштовий індекс. Оскількикожна картка в шухляді містить інформацію того самого типу, картотеку можнаназвати базою даних.
/>
Мал.2
УExcel база даних будується за аналогією з рядками і стовпцями, причому стовпецьтаблиці є полем, а кожен рядок – окремим записом. Інформація з кожного полязаноситься в окрему комірку таблиці. Наприклад, у таблиці, представленої намал. 1, імена занесені в стовпець із заголовком Ім'я, прізвища занесенів стовпець із заголовком Прізвище і т.д. Для того щоб приступити достворення бази даних, помістіть курсор в комірку верхньої частини таблиці івведіть у рядку назви полів.Створення бази даних
Щобстворити базу даних у робочій таблиці Excel, необхідно виконати наступне:
1. У порожнійрядок таблиці ввести назви полів.
2. У кожнукомірку у рядку, що знаходиться безпосередньо під рядком з назвами полів,ввести фактичні дані по кожному полю. Не потрібно залишати порожніх рядків міжрядком з назвами полів і наступними даними, оскільки в Excel можуть виникнутипроблеми з розпізнаванням початку бази даних.
3. Для того щобввести в комірку цифри (наприклад, поштовий індекс), поставте спочаткуапостроф.
Післядодавання записів у базу даних отримується набір даних, упорядкованих по рядкахі стовпцям, причому його формат буде таким же, як формат даних, представленихна мал. 1.
Якщо необхідно ввести великий масив даних, що представляютьсобою числа, що повинні бути збережені як текст (наприклад, поштові коди чиномери телефонів), необхідно відформатувати стовпець, у який буде занесенаінформація цього типу, як текст. Для цього потрібно виділити стовпець (клацнутина його заголовку), клацнути на ньому правою кнопкою миші і вибрати пунктконтекстного меню Формат ячеек. Вибрати вкладку Число вдіалоговому вікні Формат ячеек. Після цього вибрати пункт Текстовыйзі списку Числовые форматы і клацнути на кнопці ОК. Якщо попередньоне виконати ці дії чи якщо не ставити апостроф перед поштовим індексом, тоіндекси, що починаються з нулів (наприклад, 00742), будуть представлені безнулів.
Працюватиз базою даних буде набагато простіше, якщо розмістити її на окремому листі. Длятого щоб зберегти кілька баз даних на одному робочому листі, потрібно вказатидіапазон комірок для кожної бази даних, що значно ускладнить роботу з базоюданих. Оскільки кожна робоча книга в Excel може складатися з будь-якоїкількості аркушів (аж до 255), то легко можна керувати численними базамиданими, розміщаючи кожну з них на окремому листі. Якщо не розміщати інші дані натім же листі, що і база даних, то можна уникнути організаційних проблем. Вміру збільшення бази даних нові рядки будуть додаватися в кінець таблиці. Якщож у таблицю будуть введені інші дані, то може виникнути небезпека їхньогознищення.
Прироботі з базами даних у Excel придасться інформація про назви полів. Рядок зназвами полів повинний передувати списку. Залишати порожній рядок між рядком зназвами полів і фактичними даними неприпустимо. Назва кожного поля повинна бутиунікальною. У верхній частині списку може бути кілька рядків із введеною в нихінформацією, однак тільки один рядок буде сприйматися як такий, що міститьназви полів. Назва поля не повинне перевищувати 255 символів (для зручностікраще вибирати короткі назви).
Такожслід уникати розміщення іншої важливої інформації (наприклад, формул) праворучі ліворуч від бази даних. Якщо надалі скористатися автоматичною фільтрацією,щоб відфільтрувати дані в базі даних, то інші дані можуть зникнути.2. Робота з записами в базі даних
Записи в базу данихдодаються і редагуються шляхом введення даних безпосередньо в комірки, азнищуються з бази даних шляхом їхнього виділення і виконання команди Правка ÞУдалить.Однак більшість користувачів воліють знаходити і редагувати дані за допомогоюформи, що надає Excel для введення і перегляду даних. Для того щоб переглянутидані на екрані за допомогою форми, необхідно помістити курсор у будь-яку коміркубази даних і виконати команду Данные ÞФорма.На екрані з'явиться форма з полями бази даних, показана на мал. 3.
/>
Мал. 3
Додавання нових записів
Длятого щоб додати новий запис, потрібно клацнути у формі на кнопці Добавить.На екрані з'явиться порожня форма і можна буде вводити дані в кожне поле. Цідії потрібно повторити для кожного запису, що необхідно додати в базу даних.
Редагуваннязаписів
Редагуватизаписи можна за допомогою форми. Спочатку потрібно клацнути на кнопках Назаді Далее, щоб знайти потрібний запис. Крім того, можна скористатисялінійкою прокручування чи клавішами керування курсором. При роботі з великоюбазою даних для пошуку можна використовувати кнопку Критерии. Після тогояк запис буде знайдено, потрібно клацнути на потрібному полі і внести зміни.
Видаленнязаписів
Длятого щоб видалити запис за допомогою форми, спочатку потрібно знайти його,скориставшись кнопками Назад і Далее. Після того як записз'явиться на екрані, необхідно клацнути на кнопці Удалить.
Якщо необхідновидалити багато записів, то форму краще не використовувати. Замість цього слідскористатися автоматичною фільтрацією, щоб вивести на екран усі записи, якіпотрібно знищити. Потім слід виділити рядки з цими записами і вибрати з меню Правкакоманду Удалить.Пошук даних за критерієм
Альтернативнимспособом пошуку даних у базі є визначення у формі даних критеріїв пошуку.Критерії пошуку визначають дані, які потрібно знайти. Наприклад, у великій базіданих, у якій зберігаються імена й адреси, необхідно знайти всі записи, у якихзазначене певне місто. Для добору потрібних даних використовується умова, щопредставляється у виді формули. За допомогою цього критерію при роботі з базоюданих, у яку введені значення витрат, можна знайти всі записи про витрати, щоперевищують 5000 грн. Для цього потрібно ввести >5000. Вказуючикритерій у формі даних, можна використовувати оператори порівняння (ті саміоператори, що використовуються у формулах, введених в комірки робочої таблиці).У табл. 1 перераховані оператори і їхнє значення.
Таблиця1. Операторипорівняння
Оператор
Значення
= Дорівнює > Більше >= Більше або дорівнює Менше або дорівнює Не дорівнює
Щобзнайти окремі записи, використовуючи критерій пошуку, необхідно виконатинаступне:
1. Поміститикурсор у будь-яку комірку бази даних.
2. Виконатикоманду Данные ÞФорма, щоб вивести на екран форму даних.
3. Клацнутина кнопці Критерии. У правому верхньому куті форми буде написане слово Критерии,а кнопка Критерии заміниться кнопкою Правка.
4. Ввестикритерії пошуку у відповідні поля. Необхідно заповнити тільки ті поля, по якихбуде здійснюватися пошук. Наприклад, якщо потрібно знайти всі записи, що маютьвідношення до Сан-Франциско, то слід ввести у поле Місто слово San– Francisco.
5. Натиснутиклавішу або клацнути на кнопці Правка, щобповернутися до вихідної форми.
6. Дляпереходу між відібраними записами використовується кнопки Назад і Далее.
7. Завершившиперегляд записів, необхідно клацнути на кнопці Закрыть.
Укритеріях можна використовувати символи підстановки * і?.. Знак питання позначаєбудь-який символ, а зірочка – послідовність довільних символів. Наприклад,критерій Н? ll, введений у поле Ім'я, дозволить брати імена Hall, Hill і Hu //, а критерій *der – слова,що закінчуються на der (такі, як chowder і loader).
3.Сортування бази даних
Післятого як створена база даних, її можна перетворити. Наприклад, можна розташуватизаписи в будь-якому порядку, виконуючи сортування бази даних. Коли Excel сортуєбазу даних, усі записи в ній розміщаються в тім порядку, що задаються длясортування. Якщо сортується база даних так, щоб усі записи розміщалися валфавітному порядкую по іменах, то після сортування нова база даних будемістити ті ж записи, що і стара, але розміщені вони будуть в алфавітномупорядку по іменах.
Сортуванняпо полях, що містять дату чи час, буде виконане правильно тільки в томувипадку, якщо дата і час будуть представлені у відповідному для цих елементівформаті. Якщо користувач сам відокремив формат для дати і часу, і Excel нерозпізнає його, то дати будуть сортуватися як текст, що, цілком ймовірно, недасть потрібних результатів.
Присортуванні Excel не враховує регістр символів. Порожні комірки розміщаютьсянаприкінці (не важливо, як виконується сортування: по зростанню чи поспаданню). Для того щоб сортування виконувалося правильно, необхідно вказатиполя, що будуть визначати новий порядок записів. Такі поля називаються ключовими.Іноді необхідно відсортувати базу даних відразу по декількох полях.Наприклад, якщо необхідно розмістити записи в базі даних в алфавітному порядку,використовуючи поле Прізвище в якості ключового, те після сортування всіпрізвища будуть розміщені в алфавітному порядку, а імена – у довільному. Але можнавідсортувати базу даних, використовуючи в якості першого ключового поля полеПрізвище, а в якості другого ключового поля – поле Ім'я. Щоб відсортувати базуданих потрібно виконати команду Данные ÞСортировка.
Для того щобвідсортувати базу даних, потрібно виконати наступне:
1. Якщо потрібновідсортувати тільки частину записів у базі даних, слід виділити їх. Якщо жнеобхідно відсортувати всю базу даних – помістити курсор у будь-якій їїкомірці.
2. Виконатикоманду Данные ÞСортировка, щоб вивести на екран діалогове вікно Сортировкадиапазона, як показано на мал. 4.
/>
Мал. 4
3. Усписку Сортировать по вибрати поле, по якому буде виконуватися сортування,і активізувати опцію по зростанню чи по спаданню, визначивши тимсамим порядок сортування.
4. Якщопотрібно, щоб сортування виконувалося по декількох полях, необхідно вибратиїхні назви в списках Затем по і В последнюю очередь по. Спочаткусортування виконується по полю, зазначеному в списку Сортировать по,після цього – по полю, зазначеному в Затем по і зрештою – по полю,зазначеному в списку В последнюю очередь по.
5. Необхіднопереконатися, що активізовано опцію Идентифицировать поля по подписям (перваястрока диапазона) у нижній частині діалогового вікна. У результатірядок, у який занесені назви полів, не буде брати участь у сортуванні.
6. Клацнутина кнопці ОК, щоб виконати сортування.
Інодінеобхідно відсортувати базу даних по більш ніж трьох полях. Припустимо, що створеновеликий список з адресами в Excel і необхідно відсортувати цю базу даних поштатах, містам, прізвищам і, нарешті, по іменах. Оскільки в діалоговому вікні Сортировкадиапазона передбачене сортування максимум по трьох полях, можна припустити,що таке сортування неможливе. Насправді його можна виконати поетапно. Для цьогопотрібно почати із сортування по тим полям, що мають найменший пріоритет, іпослідовно виконувати сортування по більш важливих полях. Першим у кожній групісортування слід вказати саме важливе поле. Наприклад, якщо спочатку потрібновиконати сортування по полю Прізвище, то його варто вказати в списку Сортироватьпо. Потім необхідно виконати сортування по полю Ім'я, виходить, його вартовказати в полі опції Затем по. Потім слід виконати сортування ще раз,вказавши в списку Сортировать по поле Штат, у списку Затем по-полеМісто і, нарешті, у списку В последнюю очередь по-поле Прізвище.Команда Автофильтр
Длядобору даних на основі більш складних критеріїв, використання яких непередбачено при роботі з формою даних (і для того, щоб роздруковувати звітитільки з визначеними даними), Excel має у своєму розпорядженні команду Автофильтр.За допомогою цієї команди визначаються критерії, по яких будуть відображатисятільки ті записи, що відповідають цьому критерію.
Діякоманди Автофильтр продовжується доти, поки вона не буде відключена.Якщо біля назви поля є кнопки розгортання списку чи поруч з командою Автофильтру меню стоїть галочка, то команда Автофильтр усе ще активізована.
Длятого щоб відібрати запису за допомогою команди Автофильтр, слід виконатинаступне:
1. Розміститикурсор у будь-якій комірці бази даних.
2. Виконатикоманду ДанныеÞФильтрÞАвтофильтр.
3. Поруч з назвоюкожного поля з'явиться кнопка розкриття списку.
4. Клацнути накнопці розкриття списку потрібного поля, що містить усі значення цього поля.Вибрати значення, що повинне містити це поле. Також можна вибрати пункт (Условие…),щоб визначити більш складний критерій.
Післявибору всіх потрібних значень 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,її основних функцій та можливостей;
- розуміння структури бази даних,знання способів організації даних;
- практичні навички при роботі збазою даних.
Використаналітература
1. Ботт Эд, ЛеонардВудди. Использование Microcoft Office 2000. специальное издание: Пер с англ.: Уч. пос. – М.: Издательскийдом «Вильямс», 2000. – 1024 с.: ил. – Парал. тит. англ.
2. Руденко В.Д., Макарчик О.М., Паталанжоглу М.О. Практичнийкурс інформатики / За ред. Мадзігона В.М. – К.: Фенікс, 1997. – 304 с.
3. Гаевский А.Ю. Самоучитель работы на персональномкомпьютере. – К.: А.С.К., 2001. – 416 с.: ил.
4. Д. Тейлор, Дж. Мишель, Дж. Пенман. Microcoft Office 2000: Пер с англ.: В. Усманов, А. Сергиенко. –Издательство «Питер Ком», 2000. – 980 с.: ил.