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

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

Рубрика Программирование, компьютеры и кибернетика
Вид курсовая работа
Язык русский
Дата добавления 20.11.2011
Размер файла 2,4 M

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

Студенты, аспиранты, молодые ученые, использующие базу знаний в своей учебе и работе, будут вам очень благодарны.

2

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

Размещено на http://www.allbest.ru/

Федеральное агентство по образованию

Государственное образовательное учреждение высшего профессионального образования

Санкт-Петербургский государственный горный институт им. Г.В. Плеханова

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

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

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

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

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

Автор:

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

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

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

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

2006

Размещено на http://www.allbest.ru/

Министерство общего и профессионального образования Российской Федерации

Санкт-Петербургский государственный горный институт им. Г.В. Плеханова

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

УТВЕРЖДАЮ

Заведующий кафедрой

________/______/

"___"ноября 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

№ варианта

Query

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

108

Критерий по дате

Критерий по цеху (ceh)

Сортировать по полю

Страница

Столбец

Строка

Функция

c 1.01.60 по 1.01.90

012208 и 012204

inv

цех

год

Наиме-нование

Сумм. (износ)

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

Таблица 2

Имя поля

Информация

NAIM

Наименование

INV

Инвентарный номер

SUM

Первоначальная стоимость в рублях

DDAT

Дата постановки на учет

CEH

Цех (шифр подразделения)

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

Таблица 3

Имя поля

Информация

NOR

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

IZN

Износ на начало 1996 года (величина физического износа, руб.)

CEH

Цех (шифр подразделения)

INV

Инвентарный номер

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

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

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

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

· Установить фильтры по полю CEH и по полю DDAT таким образом, чтобы получить информацию об основных средствах предприятия по подразделению 012208 и 012204, поставленных на учет с 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 таким образом, чтобы получить информацию об основных средствах предприятия по подразделению 012208 и 012204, поставленных на учет с 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. представлена итоговая сводная таблица, отражающая информацию о количестве наименований предприятия по подразделению 012208 и012204, поставленных на учет с 1.01.60 по 01.01.90г.

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

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

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

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

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

Заключение

Выполнив курсовую работу, я освоил приложение 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.

Размещено на www.allbest.ru


Подобные документы

  • Формирование и расчет таблиц в табличном процессоре Excel. Расчет таблицы с использованием "Мастера функций". Построение диаграмм на основе табличных данных. Работа с базой данных "Книжный магазин" в Excel. Выручка по книгам, относящимся к одному типу.

    контрольная работа [329,2 K], добавлен 26.09.2012

  • Работа с базами данных в табличном процессоре Microsoft Excel. Сортировка и фильтрация данных. Встроенные функции Excel. Подведение промежуточных итогов в таблице. Макет сводной диаграммы. Условие проверки для поля. Сообщение об ошибке при вводе.

    курсовая работа [1,0 M], добавлен 19.05.2014

  • Определение корней алгебраического уравнения и экстремумов функции с помощью процедуры Поиск решения. Расчет суммы и срока вклада в накопительный фонд для обучения. Создание базы данных и сводной таблицы в MS Excel, построение круговой диаграммы.

    контрольная работа [1,3 M], добавлен 02.05.2013

  • Анализ программы Microsoft Excel. Способы оформления элементов таблицы различными цветами. Этапы подготовки табличных документов. Характеристика табличного процессора EXCEL. Особенности проведения однотипных расчетов над большими наборами данных.

    реферат [565,9 K], добавлен 14.09.2012

  • Основные функции и методы работы в табличном процессоре Microsoft Excel. Создание и редактирование простейших таблиц и диаграмм. Характеристика встроенных функций программы. Использование формул и правил введения, их комбинирование и редактирование.

    курсовая работа [2,2 M], добавлен 08.06.2014

  • Структура базы Store.dbf, Design.dbf. Получение внешних данных в Ms Excel. Сортировка по полю naim. Диалоговое окно MS-Query с заполненным критерием выборки. Работа со сводной таблицей, особенности её создания. Итоговая сводная таблица, текст запроса SQL.

    курсовая работа [1,9 M], добавлен 23.11.2011

  • Практические навыки по заполнению рабочих листов исходной информацией, вводу и копированию формул в табличном редакторе Excel. Использование Автофильтра и Мастера функции, Сводной таблицы, вкладки Конструктор. Создание рабочего листа базы данных.

    лабораторная работа [1,7 M], добавлен 11.06.2023

  • Создание круговой диаграммы в табличном процессоре Microsoft Office Excel. Построение графиков математических функций. Назначение и алгоритм построение диаграммы с помощью Мастера диаграмм. Типы диаграмм в Excel. Метки строк и столбцов диаграммы.

    лабораторная работа [1,6 M], добавлен 15.11.2010

  • Использование различных программ Microsoft Office для создания таблиц. Системы управления базами данных (СУБД) как специальные программные средства, предназначенные для работы с файлами баз данных. Возможности работы с табличными данными в Excel.

    контрольная работа [21,6 K], добавлен 20.02.2010

  • Составление таблицы согласно образцу в программе MS Excel. Создание данных таблицы базы данных. Введение формул в программе MS Excel. Установление связи между таблицами. Создание запроса на выборку данных из одной таблицы с помощью мастер запросов.

    контрольная работа [4,0 M], добавлен 17.04.2016

Работы в архивах красиво оформлены согласно требованиям ВУЗов и содержат рисунки, диаграммы, формулы и т.д.
PPT, PPTX и PDF-файлы представлены только в архивах.
Рекомендуем скачать работу.