5216

Робота з базами даних в MS Excel

Реферат

Информатика, кибернетика и программирование

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

Украинкский

2012-12-04

152.64 KB

79 чел.

Робота з базами даних в MS Excel

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

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

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

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

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

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

10   АлмазовО. М. 1968 Інженер Харків 86

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

Поле — певна характеристика об'єкта або окремий елемент даних у запису. Кожне поле має унікальне ім'я, йому відповідають дані одного стовпця. Для ефективного пошуку, селекції та сортування даних бази доцільно записи поділяти по полях, що містять найдрібніші елементи даних. Так, замість одного поля «Прізвище, ім'я, по батькові» краще задати три: «Прізвище», «Ім'я» і «По батькові». Єдину адресу клієнта можна розділити на поля «Поштовий індекс», «Місто», «Вулиця», «Номер будинку» і т. п.

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

Загальний вигляд найпростішої бази даних Excel з ім'ям «Замовлення» зображено на рис. 2.104.

Рис. 2.104. Загальний вигляд бази даних «Замовлення»

Загальні правила формування бази даних:

  1. у базі даних, як правило, розміщуються тільки поля початкових даних. Поля, що обчислюються, шапку документа і підсумкові рядки до бази не включають, їх формують на етапі створення документа-звіту;
    1. заголовний рядок має відрізнятися від рядків-записів кольором, шрифтом або обрамленням. У разі монохромного друку його краще взяти в рамку;
    2. після заголовного рядка мають іти рядки записів; розділяти їх навіть порожніми рядками не рекомендується;
    3. в однойменних полях записів розміщують дані тільки одного типу: числа, тексти або дати. Не треба розпочинати поля з пропусків;
    4. текстові дані краще розпочинати з великих літер, розширюючи таким чином можливості пошуку та сортування даних;
    5.  фон сусідніх записів доцільно чергувати: при цьому поліпшується сприйняття даних користувачем;
    6. на одному аркуші бажано розміщувати тільки одну базу даних. Інші дані краще розташувати на інших аркушах. Поєднання різнорідних баз даних можливе, якщо відокремити їх порожніми рядками і стовпцями.

Формати файлів баз даних

Базу даних, створену засобами Excel, можна зберегти не тільки у стандартному форматі книги (.xls), а й у звичному для баз даних форматі, наприклад, dbf-файлі. У цьому випадку програма коректно визначає типи полів (текст, числове, дата) та їхні довжини (для тексту) на підставі наявних даних. Для створення dbf-файлу достатньо вибрати значення DBF 4 (dBASE IV) (*.dbf) у полі Тип файлу діалогового вікна Збереження документа, яке викликається командою Файл/Зберегти як...

Excel дає змогу також працювати з dbf-файлами та файлами баз даних системи керування базами даних (СКБД) Microsoft Office Access. Для відкриття таких файлів у полі Тип файлу діалогового вікна Відкриття документа треба, відповідно, вибрати значення Файли dBase (*.dbf) або Бази даних Access (*.mdb; *.mdb). Іншим способом відкриття таких файлів є використання команди Дані/Імпорт зовнішніх даних/Імпорт даних....

Для побудови запитів до баз даних, створених спеціалізованими СКБД (наприклад, SQL Server, MS Access, Paradox, FoxPro), в Excel є спеціальний засіб - програма MS Querry, яку активізують командою Дані/Імпорт зовнішніх даних/Створити запит.... Перевага використання запитів полягає в тому, що в Excel переноситься лише потрібна (відфільтрована) частина записів з бази даних.

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

Базу даних, як і будь-яку книгу Excel, створюють командою Файл—Создать. Спочатку можна ввести назву таблиці, наприклад «Відомості про замовлення товарної продукції», потім — її заголовний рядок із однорядковими та короткими іменами полів: «Замовник», «Шифр товару» і т. ін. (рис. 2.104). Далі потрібно замінити системне ім'я бази «Книга1» на призначене для користувача, наприклад на «Замовлення», клацнувши мишею на кнопці Сохранить.

Після натиснення мишею на будь-якій із комірок таблиці командою Данные—Форма на екран викликається діалогове вікно, де відображається список уведених даних (рис. 2.105).

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

Попередження

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

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

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

Рис. 2.105. Вікно команди Форма (режим уведення записів)

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

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

3.Використання фільтра та сортування даних    

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

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

Рис. 7.35. Задання користувацького автофільтру

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

Складніші критерії фільтрування та пошук інформації в базі чи її частині реалізує команда Дані/Фільтр/Розширений фільтр.

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

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

Рис. 7.36. Налаштування сортування даних

4.Пошук записів бази даних    1, с. 416-418

В Excel процедура пошуку і селекції даних організовується за принципом збігу або селективним способом.

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

ПП «Маяк»; ТШ-45; 27.10.02; 22,95

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

ПП*;ШТ-4?;ТШ*;ЗАТ*;

И (Ціна, грн. >=20; Ціна, грн. <=60);

ИЛИ (Дата замовлення<=20.10.02; Дата замовлення >=28.10.02),

де * — ознака будь-якої групи символів; ? — ознака того, що в цій позиції може міститися будь-який символ. Критерій ПП* ідентифікує всі записи бази «Замовлення» (рис. 2.104), що стосуються приватних підприємств; критерій ПІТ-4? — всі записи з шифрами товарів від ПІТ-40 до ІПТ-49; а критерій И (Ціна, грн. >=20; Ціна, грн. <=60) — всі записи з цінами товарів від 20 до 60 грн. Аналогічні функції виконують інші критерії.

На етапі створення бази даних, щоб розпочати ручне введення критеріїв, пошук записів, їх послідовне переглядання і редагування, слід активізувати команду Критерии, яка трохи видозмінює початкове вікно (рис. 2.105) й очищає всі його поля (рис. 2.106).

Як критерії пошуку використовують значення полів початкової бази, що вводять до відповідних комірок нового вікна командою Добавить. Загалом керують критеріями так само, як і записами. Шукати дані можна за одним або кількома критеріями одночасно. У вікні на рис. 2.106 показано критерії пошуку та селекції замовників, які мають намір придбати товари з шифром ПІТ* ціною до 30 грн.

Рис. 2.106. Вікно команди Форма (режим уведення критеріїв)

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

Примітка

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

Повернення до поточного запису й ігнорування результатів пошуку за критерієм забезпечується кнопкою Правка. Для введення чергового критерію потрібно заздалегідь активізувати команду Очистить, після чого пошук відновлюється. Повертаються до початкової бази даних через команду Данные—Фильтр—Отобразить все.

5.Формування підсумкових даних    

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

Розглянемо роботу команди Підсумки на прикладі даних про продаж автомобілів (рис. 7.37).

Рис. 7.37. Дані про продаж автомобілів

Включення проміжних підсумків у список здійснюється так:

  1.  Список доцільно відсортувати за полем, що містить групи. Наприклад, за місяцем, прізвищем тощо.
  2.  Виконати команду Дані/Підсумки. Відкриється діалогове вікно Проміжні підсумки (рис. 7.38).

Рис. 7.38. Діалогове вікно Проміжні підсумки

  1.  Вибрати зі списку При кожній зміні в групу, для якої визначаються проміжні підсумки.
  2.  Вибрати зі списку Використовувати функцію одну з функцій (Сума -додавання чисел у групі, Кількість - підрахунок кількості заповнених клітинок у групі, Середнє — середнє арифметичне чисел у групі, Максимум - визначення найбільшого числа у групі, Мінімум - визначення найменшого числа у групі, Добуток - добуток чисел у групі, Кількість чисел - кількість клітинок, котрі містять числові дані у групі, Зсунене відхилення - розрахунок стандартного відхилення за генеральною сукупністю, Незсунене відхилення - розрахунок стандартного відхилення за групою, Зсунена дисперсія - розрахунок дисперсії за генеральною сукупністю, Незсунена дисперсія - розрахунок дисперсії за групою).
  3.  Вибрати зі списку Додати підсумки до стовпці для розрахунку проміжних підсумків. Для вибору стовпця необхідно поставити відмітку проти його назви.

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

Рис. 7.39. Дані про продаж автомобілів з проміжними та загальним підсумками

Кнопки у верхній частині поля визначають кількість рівнів даних, що відображаються і використовуються для виконання таких дій:

  1.  - Вивести тільки загальний підсумок;
  2.  - Вивести тільки загальний підсумок і проміжні підсумки;
  3.  - Вивести всі дані.

Кнопки з позначками (+) і (-) призначені для розгортання і згортання окремих груп.

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

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

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

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

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

Після виконання команди Дані/Зведена таблиця відкривається діалогове вікно майстра зведених таблиць і діаграм (рис. 7.40), котре складається з трьох кроків. Призначення кнопок в нижній частині майстра очевидне.

Рис. 7.40. Перший крок Майстра зведених таблиць і діаграм

Створимо зведену таблицю використовуючи дані продаж автомобілів з вибором в якості джерела даних аркуша Excel.

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

Рис. 7.41. Другий крок Майстра зведених таблиць і діаграм

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

Рис. 7.42. Третій крок Майстра зведених таблиць і діаграм

Структуру зведеної таблиці задають перетягуванням кнопок з назвами полів в області Рядок, Стовпець, Сторінка, Дані (рис. 7.43).

Рис. 7.43. Побудова макету зведеної таблиці

Вибравши опцію Параметри, можна замінити деякі параметри зведеної таблиці (рис. 7.44).

Рис. 7.44. Задавання параметрів зведеної таблиці

Результатом таких дій буде зведена таблиця щомісячної виручки від продажу автомобілів кожним з продавців (рис. 7.45).

Рис. 7.45. Зведена таблиця щомісячної виручки від продажу автомобілів


 

А также другие работы, которые могут Вас заинтересовать

35489. Экономические информационные системы 139.5 KB
  Наиболее распространенными формами такого рода моделей являются: диаграммы потоков данных сети Петри сети управления и планирования модели баз данных модели баз знаний и т. Большинство бизнеспроцессов воспроизводятся с помощью диаграмм потоков данных. В зависимости от целей моделирования внимание может быть сосредоточено либо на процессах бизнеспроцесса либо на объектах либо на потоках данных. Если необходимо воспроизвести объекты и связи между ними то пользуются стандартом IDEF1 а при необходимости моделирования потоков данных –...
35490. Информационные системы. Процесс информатизации 78.5 KB
  Информационный процесс. Характеристика его составляющих Информационный процесс процесс получения создания сбора обработки накопления хранения поиска распространения и использования информации. Базовыми фундаментальными понятиями экономической информатики являются: данные; информация и экономическая информация; информационный процесс; задача и экономическая задача; знания; Данные В повседневной жизни мы сталкиваемся с сообщениями об объектах событиях процессах от различных источников. Информационная система – это...
35491. Информационные системы. Шпаргалка 163 KB
  Для информационных систем характерно Многоаспектность Многофункциональность Различные сферы применения Поэтому классифицировать информационные системы сложно. Могут быть системы: автоматизированные слабо автоматизированные и не автоматизированные Уровень интеграции информационных процессов. Могут быть системы: интегрированные процессные информационные системы выполненные на единой информационной базе и обеспечивающие сквозную связь между всеми элементами ИС. Онги поддерживают управление бизнеспроцессами ...
35492. Информационные системы и информационные технологии 93.5 KB
  TPS – Транзакционные технологии TPS Trnsctions Processing Systems предназначены для ежедневной обработки поступающих в виде документов сообщений счета акты накладные и т. MIS – Технологии поддерживающие управленческие функции MIS Mngement Informtion Systems предназначены для автоматизации планирования деятельности предприятия организации а также для организации контроля над ходом выполнения планов производства и реализации продукции. DSS – Технологии аналитической обработки данных DSS Decision Support Systems...
35493. Автоматизированные системы управления (АСУ) 784 KB
  Основные компоненты АСУ ТП предназначена для выработки и реализации управляющего воздействия на ТОУ и представляют собой человекомашинную систему обеспечивающую автоматизированный сбор и обработку информации необходимой для оптимизации управления объектом в соответствии с принятым критерием. Основные компоненты: КТС – комплекс технических средств; СПО – системное программное обеспечение; ФАУ – функциональные алгоритмы управления. Информационное обеспечение информация характеризующая состояние системы управления системы классификации и...
35494. Моделирование информационных систем 702.5 KB
  Модели гидродинамики потоков в аппаратах. Модель идеального смешения Условия физической реализуемости этой модели выполняются если во всем потоке происходит полное смешение частиц потока. Модели идеального перемешивания соответствует апериодическое звено 1го порядка и имеет передаточную функцию. Математическое описание модели: где: с концентрация вещества; τ время пребывания частиц в реакторе; ω линейная скорость потока; х координата.
35495. Системы автоматизированной работы (САР) 5.7 MB
  Разомкнутые САР системы в которых входными воздействиями управляющего устройства являются только внешние задающие и возмущающие воздействия; при этом значение выходной величины ОУ может существенно отклоняться от его заданного значения в силу изменения внутренних свойств ОУ параметров САР. Устойчивость САР свойство системы возвращаться в исходное состояние равновесия после прекращения воздействия выведшего систему из этого состояния. уравнения частотные определяют связь между устойчивостью системы и формой частотных характеристик...
35496. Представление данных в электронных таблицах в виде диаграмм и графиков 1.25 MB
  Что нужно знать: что такое столбчатая линейчатая и круговая диаграмма какую информацию можно получить с каждой из них адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки например C15 формулы в электронных таблицах начинаются знаком = равно знаки – и ^ в формулах означают соответственно сложение вычитание умножение деление и возведение в степень в заданиях ЕГЭ могут использоваться стандартные функции СУММ сумма СРЗНАЧ среднее значение МИН минимальное...
35497. КУЛЬТУРА СОВЕТСКОЙ ПОВСЕДНЕВНОСТИ И ЕЕ ОТРАЖЕНИЕ В САТИРЕ 1920–х ГОДОВ 209.5 KB
  Анализ специфики репрезентации советской повседневности в сатире. Как известно, в этот период истории происходила, навязываемая сверху, смена отношений к повседневности: «борьба» старого и нового быта. Ключевым вопросом в нашей курсовой работе является осмысление противостояния традиционного уклада жизни и навязываемыми сверху принципами «новой жизни».