Овладение навыками импорта данных (Query) и обработка полученных данных средствами сводной таблицы в табличном процессоре Excel

Фёдоров И.В. ЭГР-05

Федеральное агентство по образованию Государственное образовательное учреждение высшего профессионального образования Санкт-Петербургский государственный горный институт им. Г.В. Плеханова

(технический университет)

КУРСОВОЙ ПРОЕКТ

По дисциплине: Информатика

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

Тема: Импорт данных с помощью приложения MS Query и обработка полученных таблично организованных средств сводной таблицы в табличном процессоре Excel

Автор:

студент группы ЭГР-05 _________________ /Симонов А. П./

Руководитель проекта

_зав. каф. ____________ /_Прудинский Г. А._/

Санкт-Петербург

Министерство общего и профессионального образования Российской Федерации Санкт-Петербургский государственный горный институт им. Г.В. Плеханова

(технический университет) УТВЕРЖДАЮ Заведующий кафедрой

________/______/

" ___" ноября 2006 г.

Кафедра информатики и компьютерных технологий

ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

По дисциплине Информатика

ЗАДАНИЕ

Студенту группы ЭГР-05 Симонову А.П.

1. Тема работы: овладение навыками импорта данных (Query) и обработка полученных данных средствами сводной таблицы в табличном процессоре Excel.

2. Содержание пояснительной записки: я использовал при написании данной курсовой работы средства приложения MS Query и табличного процессора MS Excel, с помощью которых я научился импортировать данные и обрабатывать таблично организованных данных средствами сводной таблицы.

3. Исходные данные к проекту: в процессе написания курсовой работы было использовано пять литературных источников.

4. Перечень графического материала: 3 таблицы, 18 рисунков

5. Срок сдачи законченной работы __1 декабря 2006 года.

Руководитель проекта: доцент /Прудинский Г. А./

Дата выдачи задания: ___12.09.2006___

Аннотация

query excel данные сводная таблица

Курсовая работа направлена на то, чтобы каждый студент овладел навыками импорта данных (Query) и обработки полученных таблично организованных данных средствами сводной таблицы в табличном процессоре Excel. Для выполнения задания требуются уверенные навыки работы на персональном компьютере, понимание основ построения баз данных, умение разрабатывать постановку задачи и алгоритм ее решения, а также знакомство с основными экономическими понятиями. Успешное выполнение курсовой работы служит еще одним элементом для успешной работы в будущем.

Данная курсовая работа изложена на 22 страницах, содержит 3 таблицы и 18 рисунков.

The summary

The course work is directed on, that each student has taken possession of skills of import of the data (Query) and processing received given tabular of the organized data by means of the summary table in the tabulated processor Excel. For performance of the task the sure skills of work on the personal computer, understanding of bases of construction of databases, skill are required to develop algorithm of the decision of a task and its statement, and also acquaintance to the basic economic concepts. The successful performance of course work serves one more element for successful work in the future.

The given course work is stated on 22 pages, contains 3 tables and 18 figures.

• Алгоритм решения задачи
• Работа с Query
• Работа со сводной таблицей
• Результаты
• Заключение
• Список литературы
• Введение
• Цель работы — овладеть навыками импорта данных (Query) и обработки полученных таблично организованных данных средствами сводной таблицы в табличном процессоре Excel. Средством для выполнения данной задачи является приложение Microsoft Query. Из MS Query выделяют по запросу необходимую информацию, которую можно отсортировать, отформатировать и проанализировать, то есть с помощью приложения MS Query извлекают информацию из варианта и помещают в Excel, а затем с помощью сводной таблицы обрабатывают полученные данные в соответствии с индивидуальным заданием.

С помощью MS Query можно обрабатывать данные различных форматов и передавать результаты в Excel.

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

База данных — совокупность связанных данных, организованных по определенным правилам, предусматривающим общие принципы описания, хранения и манипулирования, независимая от прикладных программ. База данных является информационной моделью предметной области. Обращение к базам данных осуществляется с помощью системы управления базами данных (СУБД).

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

СУБД характеризуется используемой моделью, средствами администрирования и разработки прикладных процессов.

СУБД обеспечивает:

— описание и сжатие данных;

— манипулирование данными;

— физическое размещение и сортировку записей;

— защиту от сбоев, поддержку целостности данных и их восстановление;

— работу с транзакциями и файлами;

— безопасность данных.

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

Условие - в программировании — логическое выражение, принимающее значение Истина или Ложь. Условия используются в конструкциях цикла и ветвления.

QBE (Query by example) — способ создания запросов к базе данных, с использованием образцов в виде текстовой строки, названия документа или списка документов. Система QBE преобразует пользовательский ввод в формальный запрос к базе данных, что позволяет пользователю делать сложные запросы без необходимости изучать более сложные языки запросов, таких как SQL.

Open Data Base Connectivity (ODBC) - стандарт Microsoft, который обеспечивает доступ к базам данных, созданных различными СУБД, с помощью интерфейса прикладного программирования, не зависящего от формата файлов. Для выполнения операций драйверы ODBC используют форму SQL-запросов.

Запрос (информационный запрос) в широком смысле — текст, выражающий информационную потребность.

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

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

Критерий — признак, являющийся основой классификации.

Запись — в реляционных базах данных — строка таблицы данных, состоящая из полей разного типа.

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

Сортировка — процесс перегруппировки заданного множества объектов в некотором определенном порядке.

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

Терминология сводной таблицы:

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

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

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

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

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

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

Суммирующая функция — функция, применяемая для вычислений в таблице

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

Основные экономические понятия:

Основные средства предприятия — денежные средства, вложенные в имущественные основные фонды.

Первоначальная стоимость основных фондов — это стоимость фондов в момент вступления их в сферу производства; это сумма денег, уплаченная за приобретение фондов.

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

Норма амортизации — установленный в процентах от балансовой стоимости размер амортизации за определенный период времени по конкретному объекту или виду (группе) основных фондов.

Износ — изнашивание (приход в негодность) хозяйственных товаров, постепенная утрата основными фондами первоначальной стоимости в результате перенесения ее на вновь созданный продукт в виде амортизационных отчислений.

Постановка задачи

Вариант задания

Таблица 1

Для решения задачи используется хранящаяся на сервере в виде двух таблиц данных (G:cursosfondsl1.dbf и G: cursosfondsl2. dbf) информация об основных фондах предприятия. В таблице sl1. dbf содержится информация о поставленных на учет основных фондах:

Таблица 2

В таблице sl2. dbf хранятся сведения об амортизации основных фондов предприятия:

Таблица 3

Требуется, согласно варианту, предоставить в виде таблицы информацию об основных средствах предприятия, поставленных на учет в период с 1.01.60 по 1.01.90 и по цехам 12 208 и 12 204, отсортировать полученные сведения по инвентарному номеру.

Для успешного выполнения работы требуется:

· Извлечь в приложение Query указанную в задании информацию из баз данных.

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

· Установить фильтры по полю CEH и по полю DDAT таким образом, чтобы получить информацию об основных средствах предприятия по подразделению 12 208 и 12 204, поставленных на учет с 1.01.60 по 1.01.90. Извлечь отфильтрованные записи по всем необходимым полям и отсортировать полученную информацию по полю DDAT по возрастанию.

Представить полученную информацию:

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

· Получить итоговые суммарные значения.

· Сгруппировать данные по инвентарному номеру.

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

На компьютере хранится информация об основных фондах предприятия в виде двух баз данных, созданных в формате dBASE: одна база содержит об инвентарных номерах, наименованиях, дате постановки на учет, первоначальной стоимости и шифре подразделения, вторая — норме амортизации, износе, инвентарных номерах и шифрах подразделения (цехах).

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

Алгоритм решения задачи

Работа с Query

На сервере хранится информация об основных фондах предприятия в виде двух баз данных, созданных в формате dBASE. С помощью приложения MS-Query можно выполнить обработку данных созданных в этом формате, и передать результаты в Excel.

Для этого необходимо открыть Excel. Установить курсор в верхний левый угол и с помощью строки меню вызвать MS-Query: ДДанныеДПолучить внешние данные ДСоздать запрос. При этом откроется диалоговое окно, в котором необходимо определить источник полученных данных (в данном случае это формат dBASE).

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

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

Рис. 2. Диалоговое окно Добавление таблиц

После определения источника информации на экране появится рабочее окно MS-Query. Оно разделено на две части: в верхней расположено два маленьких окна с именами полей, которые есть в исходной базе данных, а в нижней будут размещены поля, для которых определяется запрос. Для определения этих полей необходимо поставить указатель «мыши» на имя поля, нажать левую клавишу «мыши» и, не отпуская ее, перетащить в нижнюю часть окна. Если в запросе нужны все поля исходной базы, нужно щелкнуть 2 раза левой кнопкой «мыши» в качестве имя поля звездочку (*), которая находится в первой строке списка полей.

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

Рис. 3. Диалоговое окно MS-Query с двумя связанными таблицами Для выполнения поставленной задачи необходимо установить фильтры по полю CEH и по полю DDAT таким образом, чтобы получить информацию об основных средствах предприятия по подразделению 12 208 и 12 204, поставленных на учет с 01.01.60 по 01.01.90 года. Для этого выбираем меню ДУсловие ДДобавить условие и задаем критерий выбора. Логической связью «ИЛИ» объединяем критерий по цехам и " И" по дате.

Рис. 4. Диалоговое окно для выбора критерия Рис. 5. Диалоговое окно для выбора критерия Рис. 6. Добавление следующего критерия (по принципу «И»)

После выполненных операций получаем отобранные данные вместе с записью условий (критериев).

Затем необходимо осуществить сортировку полученной информации по возрастанию по полю INV (по инвентарным номерам). Для этого в окне MS-Query выделяем столбец INV и вызываем команду Записи Сортировать. Открывается диалоговое окно Сортировка, в котором определяется критерий сортировки.

Рис. 7. Диалоговое окно Сортировка

Отсортированные данные возвращаем в Excel с сохранением текста запроса. Для этого выполняем команду Файл Вернуть данные.

Переданные в Excel данные копируем на новый лист книги, устанавливаем необходимые форматы и заменяем названия колонок в таблице на русские заголовки следующим образом: NAIM — наименование, INV — инвентарный номер, DDAT — дата, CEH — цех, IZN — износ, SUM — первоначальная стоимость, NOR — норма амортизации.

Рис. 8. Результат импорта данных в Excel

Работа со сводной таблицей

Для создания сводной таблицы необходимо: установить курсор на первую строку имеющегося списка (А1); выбрать команду Данные Сводная таблица. Затем открывается первое из диалоговых окон Мастера сводных таблиц, в котором задается источник данных для сводной таблицы.

Рис. 9. Первое диалоговое окно Мастера сводных таблиц и диаграмм

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

Рис. 10. Второе диалоговое окно Мастера сводных таблиц и диаграмм

В третьем окне непосредственно и формируется сама таблица.

Рис. 11. Третье диалоговое окно Мастера сводных таблиц и диаграмм

В данном диалоговом окне, для создания таблицы, необходимо вызвать «макет».

Рис. 12. Диалоговое окно Мастера сводных таблиц и диаграмм — макет

В данном окне есть кнопки всех имеющихся полей. Эти кнопки надо с помощью мыши перетащить в область строки, страницы и функции (данные). Так, например, для выполнения поставленной задачи в область строки надо перетащить кнопку поля наименование. Для этого необходимо поставить на данную кнопку поля указатель мыши, нажать левую кнопку «мыши» и, не отпуская ее, перетащить в область строки. Затем в диалоговом окне Мастер сводных таблиц и диаграмм — шаг 3 из 3 определяем положение сводной таблицы и нажимаем кнопку «Готово».

Рис. 13. Диалоговое окно Мастера сводных таблиц и диаграмм — макет

В полученной сводной таблице дата содержит информацию о днях, месяцах и годах. В данной постановке задачи в таблице должна содержаться информация только о годах. Для этого необходимо использовать возможности группировки. Установим курсор в сводной таблице на ось Дата или на одну из ячеек с датой и с помощью контекстного меню (Группа и структура Группировать) вызовем диалоговое окно Группирование. Отметим группировку даты по месяцам и годам.

Рис. 14. Диалоговое окно Группирование

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

Рис. 15. Диалоговое окно Мастер сводных таблиц и диаграмм — макет

Теперь появилась возможность просматривать сводную таблицу по годам.

Результаты

Результатом работы являются таблицы с данными. На рис. 16. представлена итоговая сводная таблица, отражающая информацию о количестве наименований предприятия по подразделению 12 208 и12 204, поставленных на учет с 1.01.60 по 01.01.90 г.

Рис. 16. Итоговая сводная таблица

Результат работы характеризует окно «Запрос SQL» .

Рис. 17. Диалоговое окно Запрос SQL

Однако, воспользовавшись возможностью Мастера сводных таблиц просматривать таблицы по страницам, мы можем получить информацию о количестве наименований предприятия по подразделению 12 208 и 12 204 по этим цехам в отдельности. Например, на рис. 18. представлена информация по 12 204 цеху.

Рис. 18. Данные по подразделению 12 204.

Заключение

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

1. Методические указания.

2. Microsoft Office Excel 2003. Учебный курс / В. Кузьмин, — СПб.: Питер; Киев: Издательская группа BHV, 2004. — 493 с.

3. Лавренев С. М. Excel: Сборник примеров и задач. — М.: Финансы и статистика, 2003. — 336 с.

4. Коцюбинский А. О., Грошев С. В. Excel для бухгалтера в примерах. — М.: ЗАО «Издательский Дом «Главбух». — 2003. — 240 с.

5. Курс экономической теории / Под общ. Ред. М. Н. Чепурина, Е. А. Киселевой. Киров: АСА, 1994.