Excel 97 у якості бази даних

ЗМІСТ

Введення

Microsoft Excel - найпоширеніша у світі програма електронних таблиць. Програма Excel 97 розширена багатьма новими корисними можливостями і повністю інтегрована з іншими програмами Microsoft Office 97.

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

Програму Excel 97 можна використовувати для зберігання даних. Ця програма володіє багатьма можливостями, властивими сучасним програмам для роботи з базами даних. Так, наприклад, Excel 97 має засоби для обробки даних та здійснення їх фільтрації. Однак по своїм можливостям Excel 97 поступається спеціалізованим програм, призначених для роботи з базами, таким як Microsoft Access, Oracle, SQL. Зокрема, Excel 97 не може працювати з дуже великими базами даних. Це пов'язано з тим, що програма Excel 97 завантажує в пам'ять всю базу даних, незалежно від її обсягу. Спеціалізовані ж програми завантажують тільки ті дані, які необхідні.

Створення та Фільтрація Списків

Р
ісунок 1. Звичайний список в Excel

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

Відповідно до термінології, прийнятої в базах даних, поняття полі еквівалентно прийнятому в Excel 97понятію стовпець. Мітки, що описують поля, називаються іменами полів. Поняття запис еквівалентно прийнятому в Excel 97 поняттю рядок. На малюнку 1 показаний приклад бази даних, що має чотири поля: Студенти, Тест1, Тест2 і Середній показник. У ній імется також сім записів, по одній на кожного студента.

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

Фільтрація даних у списках Excel здійснюється за допомогою двох інструментів: Автофільтр і Розширений фільтр.

Використання інструменту Автофільтр.

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

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

• Усі записи

• Перші десять записів

• Фільтрація даних згідно певній умові.

Фільтрація за певним значенням.

Параметри фільтрації визначаються типом даних, з яким ведеться робота. Якщо вибрати з меню Автофільтр деяке значення, то в списку залишаться тільки ті записи, в осередках яких записано саме це значення. Розглянемо конкретний приклад. Якщо клацнути на стрілці Автофільтр F3, яка містить ім'я поля Середній показник, і з меню, що вибрати 71%, то в представленому списку залишаться видно тільки студенти Лепін і Якушев, оскільки тільки у них середні показники рівні 71%.

Якщо вибрати ім'я одного зі студентів зі списку Автофільтр (Малюнок 2), то в списку залишиться тільки запис з результатами цього студента, а інші записи виявляться прихованими.

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

Використання фільтра Первие10

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

Малюнок 2 Список зі стрілками і відкритим меню інструмента Автофільтр

Також можна визначити, за яким параметром буде проводитися відбір елементів: найбільші або найменші значення. Найбільше значення у списку - це найбільше числове значення, записане в одній з комірок стовпчика, найменше - це найменше з числових значень, що міститься в осередках вибраного стовпця.

У третьому полі діалогового вікна Накладення умови за списком можна визначити, чи буде виведено вказану кількість елементів або ж частка елементів списку (відсоток).

При здійсненні операції фільтрації адреси вічок не змінюються. Наприклад, якщо будуть відфільтровані рядки 6 і 7, то після рядка 5 буде видно рядок 8.

Для відключення інструменту Автофільтр випливає з меню Дані вибрати команду Фільтр і клацнути на команді Автофільтр.

Вибіркова фільтрація.

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

• Так само

• Не дорівнює

• Більше

• Більше або дорівнює

• Менше

• Менше або дорівнює

• Починається з

• Не починається з

• Закінчується на

• Не закінчується

• Містить

• Не містить

П
рімененіе двох параметрів, об'єднаних функцією І / АБО дозволяє звузити область пошуку (Малюнок 3).

Малюнок 3. Вибіркова фільтрація з використанням двох параметрів.

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

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

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

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

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

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

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

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

Створення власного списку автозаповнення

У Excel є можливість для створення своїх списків автозаповнення та збереження їх для подальшого використання. Наприклад, можна створити список держав, а потім зберегти його як список для автозаповнення. Тепер, після того як введені дві перші держави, інструмент Автозаповнення заповнить весь список. Можна також створити список місяців в році або список днів тижня.

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

1. Створити список, який буде використовуватися надалі. Потім слід розташувати створюваний список у послідовному ряду осередків у рядку або стовпці.

2. Виділити весь список.

3. Вибрати з меню Сервіс команду Параметри. У діалоговому вікні Параметри перейти на вкладку Списки.

4. Клацнути на кнопці Імпорт.

5. Клацнути на кнопці Ok

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

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

Сортування за допомогою списку автозаповнення.

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

1. Виділити клітинки, що підлягають сортуванню.

2. Вибрати з меню Дані команду Сортування. У діалоговому вікні Сортування діапазону клацнути на кнопці Параметри. З'явиться діалогове вікно Параметри сортування

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

Сортування "зліва направо"

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

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

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

2. Вибрати з меню Дані команду Сортування. У діалоговому вікні Сортування діапазону клацнути на кнопці Параметри. З'явиться діалогове вікно Параметри сортування.

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

4. Для закриття діалогового вікна Параметри сортування клацнути на кнопці Ok

5. У діалоговому вікні Сортування діапазону в полі Сортувати за вибрати номер рядка, що підлягає сортуванню.

Для того, щоб під час сортування враховувалися рядкові або прописні символи в діалоговому вікні Параметри сортування поставте позначку в полі Враховувати регістр.

структурування аркуша.

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

Автоматичне структурування листа

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

1. Виділити діапазон комірок, для яких потрібно створити структуру. Для структурування листа цілком вказати будь-яку клітинку.

2. Вибрати команду Структура в меню Дані, а потім - Автоструктура.

Структурування аркуша вручну

1. Виділити рядки або стовпці, які містять детальні дані. Детальні рядки або стовпці зазвичай підпорядковані рядку або стовпцю, який містить відповідні підсумкові дані. Наприклад, якщо рядок 6 містить суми для рядків з 3 по 5, то виділіть рядка 3 - 5.

2. Вибрати команду Структура в меню Дані, а потім - Сгруппіровать.Рядом з групою на екрані повинні з'явитися символи структури.

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

Структура може мати до 8 рівнів детальних даних, де кожен "внутрішній" рівень надає детальні дані для попереднього "зовнішнього".

За замовчуванням, підсумкові рядки і стовпці розташовані нижче або праворуч відповідно від детальних рядків і стовпців. Якщо підсумкові рядки вище детальних або підсумкові стовпці лівіше детальних даних, то необхідно зняти відповідні прапорці у групі прапорців Розміщення підсумків діалогового вікна Структура документа. Щоб переглянути діалогове вікно Структура документа, треба вибрати команду Структура в меню Дані, а потім - Параметри.

Зведені таблиці.

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

Малюнок 4. Частини зведеної таблиці

На малюнку 4 представлений приклад зведеної таблиці:

Поле сторінки - це поле вихідного списку або таблиці, поміщене в область сторінкової орієнтації зведеної таблиці. У цьому прикладі «Область» є полем сторінки, яку можна використовувати для підведення підсумків по регіонах. При вказівці іншого елемента поля сторінки відбувається перерахунок зведеної таблиці для відображення підсумків, пов'язаних з цим елементом.

Елементи поля сторінки об'єднують записи або значення поля або стовпця вихідного списку (таблиці). У цьому прикладі, елементу «Схід», псевдонімом у полі сторінки «Область», наведені у відповідність всі дані по східному регіону.

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

Елементи поля - це підкатегорії поля зведеної таблиці. У даному прикладі значення «М'ясо» і «Дари моря» є елементами поля в полі «Продукти». Елементи поля представляють записи в полі або стовпці вихідних данних.Елементи поля з'являються у вигляді заголовків рядків або стовпців, а також у спадному списку для полів сторінок.

Поля рядки - це поля вихідного списку або таблиці, поміщені в область рядкової орієнтації зведеної таблиці. У цьому прикладі «Продукти» і «Продавець» є полями рядка. Внутрішні поля рядка (наприклад «Продавець») в точності відповідають області даних; зовнішні поля рядка (наприклад «Продукти») групують внутрішні.

Поле стовпця - це поле вихідного списку або таблиці, поміщене в область стовпців. У цьому прикладі «Квартали» є полем стовпця, що включає два елементи поля «КВ2» і «КВ3». Внутрішні поля стовпців містять елементи, відповідні області даних; зовнішні поля стовпців розташовуються вище внутрішніх (у прикладі показано тільки одне поле стовпця).

Областю даних називається частина зведеної таблиці, яка містить підсумкові дані. У осередках області даних відображаються підсумки для елементів полів рядка або стовпця. Значення в кожному осередку області даних відповідають вихідним даним. У цьому прикладі в осередку C6 підсумовуються всі записи вихідних даних, що містять однакову назву продукту, розповсюджувача і певний квартал («М'ясо», «ТОВ Мясторг» і «КВ2»).

Зведену таблицю можна створити на основі даних, що перебувають у списку або базі даних Microsoft Excel, кількох аркушах Microsoft Excel, у зовнішній базі даних, а також в іншій зведеній таблиці.

Створення зведеної таблиці

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

1. Відкрити книгу, в якій необхідно створити зведену табліцу.Еслі зведена таблиця створюється на основі даних, що перебувають у списку або базі даних Microsoft Excel, виділити клітинку списку або бази даних.

2. Вибрати команду Зведена таблиця в меню Дані.

3. Далі слідувати інструкціям майстра зведених таблиць.

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

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

Отримання зовнішніх даних за допомогою Microsoft Query

Запит дозволяє отримати дані, що містяться в зовнішній базі даних. Для прикладу припустимо, що в базі даних Microsoft Access потрібно знайти дані з продажу продукту в регіоне.Чтоби знайти відповідь на це питання, потрібно отримати відомості з бази даних, яка може бути організована у вигляді різних таблиць. Кожна таблиця містить свої дані. Наприклад, одна таблиця може містити відомості за обсягом продажів та категорії продукту. Інша таблиця може містити відомості про регіон. Microsoft Query дозволяє отримати дані з усіх цих таблиць.

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

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

Створення діаграми

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

Діаграми можуть використовувати дані несуміжних клітинок. Діаграма може також використовувати дані зведеної таблиці.

Можна створити або впроваджену діаграму, або лист діаграми.

Впроваджена діаграма-об'єкт, розташований на листі і зберігається разом з листом при збереженні книги. Впроваджені діаграми також пов'язані з даними і оновлюються при зміні вихідних даних.

Лист діаграми - лист книги, який містить лише діаграму. Листи діаграми пов'язані з даними таблиць і оновлюються при змін даних в таблиці.

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

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

2. Натиснути кнопку Майстер діаграм.

3. Далі слідувати інструкціям майстра.

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

Н
а малюнку 5 зображено діаграма, отримана на основі списку, представленого на рисунку 1 даного реферату.

Малюнок 5. Діаграма, побудована на основі списку, зображеного на рисунку 1.

Висновок.

Виходячи з усього вищесказаного, можна зробити висновок, що програму Microsoft Excel цілком можна використовувати як простий бази даних. Дані тут організовані у вигляді рядків і стовпців, що відповідає полях і записам у "справжньої" бази даних, є засоби для фільтрації, сортування та навіть створення і виконання запитів до зовнішніх джерел даних. Крім того, застосування Visual Basic for Application в якості мови для розробки макросів і програм в Excel робить застосування цієї програми в якості бази даних ще привабливіше. Але, хотілося б ще раз підкреслити, що для баз даних великих розмірів, а також таких баз даних, які пред'являють підвищені вимоги до захисту і цілісності своїх даних Excel не застосуємо.

Література.

1. Льюїс Паттерсон. Microsoft Excel 97. Освой самостійно за 24 години: Пер. з англ. - М.: ЗАТ "Видавництво БИНОМ", 1998.

2. Електронні таблиці Excel 4.0: Практ. Пособ. / Пер. з нім. - М.: ЕКОМ., 1994

3. 
   

3. Нелсон Стефен. Путівник по Microsoft Excel 97 / Пер. з НГЛ. -М.: Видавничий відділ "Російська Редакція" ТзОВ "Channel Trading Ltd.", 1994.