Разработка и создание базы данных предметной области "Мебель" в программе MS Excel

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

ФГОУ ВПО "Саратовский  ГАУ"

 

 

Кафедра

"Информационные системы и  технологии"

 

 

 

 

 

 

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

 

На тему: Разработка и создание базы данных предметной области "Мебель" в программе MS Excel

 

 

 

 

 

 

 

 

 

 

 

 

                                                                                       Выполнила студентка

                                                                                       4 курса ФЗО

                                                                                       Группы БС-44

                                                                                       Никифорова Олеся

                                                                                       Сергеевна

                                                                                       Проверил:

 

 

 

 

 

 

 

Саратов 2012

Содержание

 

Введение.................................................................................................................. 3

 

1. Формирование базы данных.............................................................................. 5

 

2. Инструментарий для  работы с базой данных

 

2.1 Сортировка ........................................................................................................7

 

2.2 Фильтрация....................................................................................................... 9

 

2.3 Промежуточные итоги................................................................................... 12

 

2.4 Сводные таблицы........................................................................................... 14

 

2.5 Элементы управления.................................................................................... 16

 

Заключение ............................................................................................................20

 

Список использованной литературы ...... ...........................................................21

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Введение

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

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

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

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

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

1. Формирование базы  данных

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

Информация, хранящаяся в таблицах, организована в виде строк и столбцов. Каждая строка таблицы, называемая записью, содержит данные об одном объекте. В столбце, называемом полем, содержатся сведения о каком-либо свойстве всех объектов хранящихся в таблице.

Т.е., можно сказать, что База данных (БД)- это совокупность различных записей, обладающих определенными свойствами.

 В первой строке  любой базы данных обязательно  должны быть указаны имена полей. Максимальный размер базы данных в MS Excel определяется возможностями версии Excel (число строк и число столбцов в листе).

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

Для своего курсового  проекта я взяла предметную область "Мебель". В моей базе данных 10 полей и 29 записей. Имена полей: Наименование, Количество, Артикул, Цвет, Вид мебели, Заказчик, Производитель, Пункт продажи Номер, Цена. База данных создана для того, чтобы отследить продажу мебели в нескольких городах и пункта продажи, выяснить какая мебель самая дорогая и самая дешевая, вычислить среднее значение цены мебели и суммарное количество ресурсов, затраченных на ее покупку. А так же чтобы наглядно было видно кто из заказчиков какую мебель заказал и по какой цене. Все это нужно для экономического анализа продаж, с помощью инструментов Сортировка, Фильтрация, Промежуточные итоги, Сводные таблицы и Элементы управления мы сможет наглядно увидеть самые выгодные продажи, в каких точках мебели покупают больше и дороже, а так же выясним кто из заказчиков является наиболее перспективным клиентом для дальнейшей работы.

2. Инструментарий для  работы с базой данных

2.1 Сортировка

 

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

Можно выполнять сортировку данных по тексту (от А к Я или  от Я к А), числам (от наименьших к  наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых  к старым) в нескольких столбцах. Можно также выполнять сортировку по настраиваемым спискам (таким как состоящий из элементов «Большой», «Средний» и «Маленький») или по формату, включая цвет ячеек, цвет шрифта, а также по значкам. Большинство сортировок применяются к столбцам, но возможно также применить сортировку к строкам.

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

Краткий алгоритм сортировки: Данные →Сортировка→Появится диалоговое окно.

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

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

2.2 Фильтрация

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

• Автофильтр, включая фильтр по выделенному, для простых условий отбора;
• Расширенный фильтр для более сложных условий отбора.

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

Строки, отобранные при  фильтрации в Microsoft Excel, можно редактировать, форматировать, создавать на их основе диаграммы, выводить их на печать, не изменяя порядок строк и не перемещая их.

Краткий алгоритм фильтрации: Вкладка Главные→ Редактирование→  Сортировка и фильтрация→Меню Фильтр→Появится  диалоговое окно

Задача: Выявить диваны, находящиеся в ценовом диапазоне от 5 до 25 тысяч рублей. Найти среднее значение цены отфильтрованных диванов. Отсортировать списки  по критериям: Пункт продажи - Пенза, вид мебели  Мягкая. Выявить диваны, цена которых выше среднего.

Настраиваем пользовательский автофильтр: Цена "Больше или Равно" 5000 рублей и "Меньше или Равно" 25000 рублей. Так же фильтруем по Наименованию, выбрав только диваны. Выделив записи, в которых указаны цены мы видим что среднее значение цены отфильтрованных диванов 12200 рублей.

 

На данной картинке мы видим, что в пункте продажи "Пенза"самый дорогой диван с артикулом Д-10 стоимостью 30000 рублей.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2.3 Промежуточные итоги

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

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

Краткий алгоритм промежуточных  итогов: Данные → Структура →  Промежуточные итоги → Заполнить  окно Промежуточные итоги.

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

Среднее значение цены мебели составляет 8344,83 р. Суммарное значение ресурсов, затраченных на покупку  всей мебели , имеющей номер партии 140  345 000,00р.

2.4 Сводные таблицы

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

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

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

 

Краткий алгоритм сводных  таблиц: Выделить весь блок данных →  Вставка→   Таблицы → Сводная таблица→ Откроется диалоговое окно.

 

Задача: Определить какое количество мебели, какого артикула и на какую сумму должен получить тот или иной заказчик. Определить какой заказчик заказал мебель на самую большую сумму.

 

 

 

 

На диаграмме видно  что ООО "Миг" заказал мебель на сумму, на порядок выше чем остальные заказчики.

 

 

 

2.5 Элементы  управления

Microsoft Excel поддерживает  несколько элементов управления, которые можно использовать в  диалоговых листах, чтобы облегчить  выбор данных. Например, поля с раскрывающимся списком, списки, счетчики и полосы прокрутки позволяют легко выбирать элементы из приведенного перечня. 
 
Добавив элемент управления на лист и связав его с ячейкой, можно возвращать числовое значение текущего положения элемента управления. Это значение можно использовать вместе с функцией INDEX для выбора различных элементов списка. 
 
В приведенных ниже примерах демонстрируется использование полей с раскрывающимся списком, списков, счетчиков и полос прокрутки. Список, связь с ячейкой и функция INDEX в этих примерах одинаковые.