Импорт внешних данных с помощью приложения MS Query

В соответствии с учебным планом курсовая работа по информатике выполняется в III семестре и является заключительным этапом в изучении курса.

В конце сентября мною было получено индивидуальное задание, которое ориентировано на работу с заранее подготовленными и хранящимися на компьютере базами данных. Выполнение данного задания предполагает уверенные практические навыки работы на персональном компьютере, понимание основ построения баз данных, умение разрабатывать алгоритм решения задачи и ее постановку.

Целью данной курсовой работы является овладение навыками импорта внешних данных с помощью приложения MS Query, а также обработка полученной информации средствами сводной таблицы в табличном процессоре MS Excel.

Microsoft Query представляет собой программу для переноса данных из внешних источников в программы Microsoft Office, в частности Microsoft Excel. Извлечение данных из корпоративной базы данных и файлов с помощью Microsoft Query позволяет избежать их повторного ввода для анализа в Microsoft Excel. Кроме того, имеется возможность автоматического обновления отчетов и итоговых значений в Microsoft Excel при изменении данных в исходной базе данных.

Таким образом, Microsoft Query — это приложение, операциями которого необходимо уметь пользоваться для успешной работы в будущем.

ОСНОВНЫЕ ТЕРМИНЫ И ПОНЯТИЯ

MS-Query — приложение, которое просматривает, отбирает и организует данные из базы данных. Это не база данных, а только инструмент для просмотра и отбора определённых данных. Полученные данные можно копировать в MS Excel и в приложения Windows.

С помощью приложения MS Query можно производить следующие операции:

обрабатывать данные различных форматов (dBase или Paradox, Microsoft Access, FoxPro), а также любых других форматов, доступ к которым возможен через интерфейс ODBC (Open Data Base Connectivity), и передавать результаты в MS Excel;

указывать записи или отдельные поля записей, для которых будет выполнен тот или иной запрос QBE (Query by Example), а также определять критерии выбора данных;

использовать для обработки запросов специальный структурированный язык запросов SQL (Structured Query Language).

SQL (Structured Query Language) — Структурированный язык запросов — был разработан фирмой IBM. Теперь он стандартизирован, но многие разработчики программных продуктов расширили его собственными элементами. Диалект языка SQL фирмы Microsoft называется MS Query. Основанный на реляционной алгебре язык манипулирования данными, позволяющий описывать условия поиска информации, не задавая для этого последовательность действий, нужных для получения ответа. SQL является стандартным средством доступа к серверу баз данных. Стандарт SQL содержит компоненты для определения, изменения, проверки и защиты данных.

База данных — объективная форма представления и организации совокупности данных, систематизированных таким образом, чтобы эти данные могли быть найдены и обработаны с помощью ЭВМ. Базы данных применяются во всех сферах человеческой деятельности, сопряжённых с учётом и хранением информации.

Разделяют плоские базы данных, в которых вся информация располагается в единственной таблице, каждая запись в которой содержит идентификатор конкретного объекта, и реляционные базы данных, состоящие из нескольких таблиц, связь между которыми устанавливается с помощью совпадающих значений одноимённых полей.

Диалоговое окно — в графическом пользовательском интерфейсе специальный элемент интерфейса, окно, предназначенное для вывода информации и (или) получения ответа от пользователя. Получил своё название потому, что осуществляет двустороннее взаимодействие компьютер-пользователь («диалог»): сообщая пользователю что-то и ожидая от него ответа.

Добавленная стоимость — стоимость проданного организацией продукта (оказанных услуг) за вычетом стоимости материалов, затраченных на производство; равна выручке, которая включает в себя эквивалент затрат на заработную плату, процент на капитал, ренту и прибыль.

Запрос — это формальное сообщение, поступающее на вход системы и содержащее условие на поиск данных и указание о том, что необходимо проделать с найденными данными.

Заголовок поля — название (метка), описывающее назначение поля; сводные таблицы реорганизуются при перемещении заголовков полей.

Информация — согласно Закону РФ «Об информации и защите информации», это сведения о людях вещах, фактах, событиях и процессах. В широком смысле, это абстрактное понятие, имеющее множество значений, в зависимости от контекста. В узком смысле этого слова — сведения (сообщения, данные) независимо от формы их представления.

Источник данных — исходный список или таблица, на основе которых строится сводная таблица.

Макет таблицы (сведение) — средство реорганизации сводной таблицы путем перемещения полей.

Налог на добавленную стоимость (НДС) — один из видов федеральных налогов в РФ, который представляет собой форму изъятия в бюджет части добавленной стоимости, создаваемой на всех стадиях производства и определяемой как разница между стоимостью реализованных товаров, работ и услуг и стоимостью материальных затрат, отнесенных на издержки производства и обращения.

Обновление — перерасчет сводной таблицы, после которого она отражает текущее состояние источника данных; обновить таблицу можно кнопкой Обновить данные на панели инструментов Сводная таблица.

Ось — одно из трех направлений в таблице: по столбцам, по строкам или по страницам.

Отчет сводной таблицы — интерактивный перекрестный отчет Microsoft Excel, содержащий итоговые данные и выполняющий анализ таких данных, как записи базы данных из разных источников, в том числе внешних по отношению к Microsoft Excel.

Поле — категория данных, соответствующая столбцу в списке.

Сводная таблица — это специальная таблица, в которой обобщается информация из других таблиц или списков. При создании сводных таблиц с помощью Мастера сводных таблиц задаются исходные данные, форма таблицы, а также вид вычислений.

Сортировка — процесс перегруппировки заданного множества объектов в некотором определенном порядке. Сортировка предпринимается для того, чтобы облегчить последующий поиск элементов в отсортированном множестве.

Стоимость — основа количественных соотношений при эквивалентном обмене.

Суммирующая функция — функция, применяемая для вычислений в таблице; по умолчанию для числовых полей Сумма, для текстовых полей Счёт; нужную функцию выбирают из списка.

Цена — денежное выражение стоимости.

Элемент данных — значение поля.

ЗАДАНИЕ

Таблица 1 — Вариант задания

Таблица 2 — Сводная таблица

ПОСТАНОВКА ЗАДАЧИ

В базе данных STORE. DBF формата dBASE хранится информация о поступлении товара на склад. База данных DESIGN. DBF, также формата dBASE, содержит информацию о наименовании изделий и их авторах. В соответствии с индивидуальным заданием, с помощью MS Query следует сформировать запрос на выборку, предварительно объединив таблицы данных STORE. DBF и DESIGN.DBF. Результат выборки направить в MS Excel, где обработать переданную информацию средствами сводной таблицы в соответствии с заданием.

Первая база — STORE. dbf

Таблица 3 — Структура базы STORE. dbf

Вторая база — DESIGN. dbf

Таблица 4 — Структура базы DESIGN. dbf

АЛГОРИТМ РЕШЕНИЯ ЗАДАЧИ

РАБОТА С MS-QUERY

1) Для импорта данных из внешних источников следует:

открыть Excel;

установить курсор в верхний левый угол листа;

с помощью строки меню вызвать MS Query:

Данные>Импорт внешних данных>Создать запрос. (рис. 1.)

Рис. 1 — Получение внешних данных в Ms Excel

После проделанных операций перед нами откроется диалоговое окно, в котором необходимо определить источник получения данных (в данном случае это формат dBase*) (рис. 2).

Рис. 2 — Диалоговое окно Выбор источника данных

3) После того, как указан формат файлы dBase, MS Query открывает диалоговое окно Добавление таблиц (рис. 3), в котором определяем файлы-источники данных.

Рис. 3 — Диалоговое окно Добавление таблиц После этого на экране появится рабочее окно MS-Query.

Оно разделено на две части:

в верхней расположено два маленьких окна с именами полей, которые есть в исходной базе данных;

в нижней будут размещены поля, для которых определяется запрос.

Определить эти поля можно с помощью двойного щелчка по соответствующему имени столбца в исходных базах данных.

В полученных из файла таблицах необходимо установить связи (STORE.naim=DESIGN.naim) (рис.4).

Для этого необходимо:

поставить указатель «мыши» на имя поля;

нажать левую клавишу «мыши» и, не отпуская ее, протащить в нижнюю часть окна;

указатель «мыши» переместить на имя соответствующего столбца в другой таблице;

между именами двух столбцов различных таблиц появится линия, которая отражает связь между ними.

Рис. 4 — Рабочее окно Ms Query.

5) Затем необходимо отсортировать нужные поля по возрастанию (сортировка по полю naim). Для этого в окне MS Query выделить столбец naim. Выполнить сортировку можно командой:

Записи> Сортировать (рис. 5).

Рис. 5 — Сортировка по полю naim

6) Затем необходимо определить критерии выборки.

По дизайнеру:

Берлин И.И.

Ахметова А.А.

Тарасова Н.А.

Воробьев М.М.

По дате — весь период.

Для установки критериев выборки используем команду:

Условия> Добавить условие (рис. 6, 7).

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

Рис. 6 — Выбор критерия по имени дизайнера Рис. 7 — Выбор критерия по имени дизайнера Рис. 8 — Выбор критерия по имени дизайнера Рис. 9 — Выбор критерия по имени дизайнера Рис. 10 — Выбор критерия по дате — весь период

8) После проделанных операций мы получим:

Рис. 11 — Диалоговое окно MS-Query с заполненным критерием выборки

9) Чтобы запомнить текущие параметры форматирования, а также для использования запроса в другое время и из другой книги, запрос можно сохранить. Для чего нужно выполнить следующие команды:

Сохранить Имя файла (в диалоговом окне Сохранить_как MS Query автоматически выбирает тип Файлы запросов (*.dqy) нажать кнопку Сохранить. Однако, для передачи данных в MS Excel НЕ требуется сохранение запроса.

После сохранения запроса (рис. 12).

Рис. 12 — Диалоговое окно сохранения запроса

10) Названия колонок можно заменить русскими заголовками следующим образом: DESIGNER — Фамилия дизайнера, DT — Дата поступления на склад, NAIM — Наименование изделия, KOLVO — количество рис. 13:

Рис. 13 — Диалоговое окно изменения столбца

11) Отформатированную информацию нужно возвратить в Excel с помощью команды Файл Вернуть данные в Microsoft Office Excel или нажать соответствующую кнопку на панели инструментов. При этом открывается диалоговое окно, где выбираем, куда поместить данные:

Рис. 14 — Диалоговое окно Импорт данных В результате получаем рис. 15.

Рис. 15 — Вид полученной таблицы в Ms Excel

РАБОТА СО СВОДНОЙ ТАБЛИЦЕЙ

Для создания сводной таблицы следует выполнить операции:

установить курсор в верхний левый угол имеющегося списка;

выбрать команду Данные>Сводная таблица, откроется первое из диалоговых окон Мастера сводных таблиц и диаграмм (рис. 17), в котором задается источник данных для сводных таблиц;

Рис. 16 — Диалоговое окно Мастера сводных таблиц диаграмм (шаг 1)

3. Во втором шаге Мастера сводных таблиц и диаграмм выбираем диапазон, содержащий исходные данные (рис. 17):

Рис. 17 -Диалоговое окно Мастера сводных таблиц диаграмм (шаг 2)

4. Щелкнуть Далее, после чего откроется третье диалоговое окно Мастера сводных таблиц (рис.17), в котором определяются:

место сводной таблицы параметры сводной таблицы макет сводной таблицы (рис. 18).

Рис. 18 — Диалоговое окно Мастера сводных таблиц (шаг 3)

5. Далее помещаем таблицу на новый лист (рис. 19) и с помощью «мыши» заносим элементы в сводную таблицу в соответствии с индивидуальным заданием (макет сводной таблицы) (рис. 20).

Рис. 19 — Макет сводной таблицы Рис. 20 — Вид сводной таблицы в Ms Excel

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

Необходимо провести группировку по полю «дата». Для этого нужно выделить поле «дата поступления на склад» и в контекстном меню выбрать Группа и структура Группировать. Затем, в появившемся диалоговом окне выбрать месяцы и годы (рис. 21).

Рис. 21 — Диалоговое окно Группировка данных по полю Дата Рис. 22 — Итоговая сводная таблица еxcel сводный таблица база

РЕЗУЛЬТАТЫ

Результат работы характеризует окно Запрос SQL (рис. 23). Можно просмотреть и отредактировать SQL-определение запроса в диалоге, который открывается при нажатии клавиши SQL на панели инструментов. Изменения в окне «Запрос SQL» немедленно отображаются в окне MS Query.

Рис. 23 — Текст запроса SQL

Результатом выполненной работы являются таблицы с данными.

Таблица, созданная с помощью запроса, на основе которой составляется сводная таблица (Таблица 5) (Фрагмент);

Таблица 5

Сводная таблица (итоговая таблица) (рис. 24).

Рис. 24 — Сводная таблица

ЗАКЛЮЧЕНИЕ

В процессе выполнения данной курсовой работы я ознакомился с возможностями приложения Microsoft Query, получил некоторые навыки создания сводных таблиц в табличном процессоре Microsoft Excel, а также закрепил ранее полученные знания и навыки по работе в текстовом редакторе Microsoft Word, и табличном процессоре Microsoft Excel. Также я научился импортировать данные с помощью MS-Query и обрабатывать их средствами этого приложения.

В результате проведенной работы получена сводная таблица с данными о единицах продукции по годам и месяцам в период с 01.01.1995 по 31.12.1997. Итоговая сводная таблица представляет собой интерактивную таблицу, которая позволяет быстро объединять и сравнивать большие объемы данных, менять местами строки и столбцы для получения различных итогов по исходным данным, а также получать подробные данные по нужным областям и осуществлять обновление и перерасчет сводной таблицы.

БИБЛИОГРАФИЧЕСКИЙ СПИСОК

1. Онушкина И. О. Правила оформления курсовых и квалификационных работ: Методические указания. / И. О. Онушкина, П. Г. Талалай. СПб, Санкт-Петербургский государственный горный институт (технический университет), 2005, 50 с.

2. Прудинский Г. А. Информатика. Подготовка данных для экономического анализа: Методические указания к курсовой работе./ Г. А. Прудинский, В. В. Беляев, Т. А. Виноградова. СПб, Санкт-Петербургский государственный горный институт (технический университет), 2010, 39 с.

3. Налог на добавленную стоимость // Википедия. Свободная энциклопедия. http://ru.wikipedia.org/wiki/Налог на добавленную стоимость 5.11.2010 17:20.