Создание базы данных в Excel

 

Аннотация

В данной курсовой работе рассматривается  работа с базами данных в редакторе  таблиц Microsoft Excel. В курсовой работе отображено создание базы данных, содержащей информацию об ассортименте обуви в магазине и формирование на основе исходной таблицы различных выходных документов. Базы данных в настоящее время широко используются при решении задач учета товаров. Выходные документы формируются с помощью встроенных инструментальных средств Microsoft Excel, а также с использованием языка программирования высокого уровня Visual Basic for Application. Для создания выходных отчетов используются формулы и операции поиска записей по заданному критерию. Данные методы эффективно и качественно решают поставленные задачи.

Курсовая работа имеет объем 15 страниц, содержит 3 приложения, 3 рисунка, 7 таблиц.

 

 

 

 

 

Annotation

In this course work, the work with databases in the editor of tables Microsoft Excel is considered. In course work is displayed the creation of a database, containing the information on assortment of footwear in shop and formation of the various target documents on the basis of the initial table. The databases now are widely used at the decision of tasks of the account of the goods. The target documents are formed with use of the built-in tool means of Microsoft Excel, and also with use of the high-level programming language - Visual Basic for Application. For creation of the target reports, the formulas and operations of search the records by the given criterion are used. These methods effectively and qualitatively decide the given tasks.

The course work has volume - 15 pages, contains 3 applications, 3 figures, 7 tables.

 

 

 

Оглавление

ПРИЛОЖЕНИЕ 1. ИСХОДНЫЕ ДАННЫЕ

ПРИЛОЖЕНИЕ 2. РЕЗУЛЬТАТЫ РАСЧЕТОВ

ПРИЛОЖЕНИЕ 3. ТЕКСТЫ ПРОГРАММ

 

Введение

 

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

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

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

Одним из средств для реализации работы с базами данных является редактор электронных таблиц Microsoft Excel. Данный редактор обеспечивает достаточный набор операций работы с базами данных, таких как создание, внесение изменений в таблицы баз данных, просмотр записей и формирование всевозможных отчетов и выходных документов. Кроме того, Microsoft Excel поддерживает использование языка высокого уровня Visual Basic for Application (VBA) при ведении таблиц и формировании отчетов, что обеспечивает гибкость и высокую скорость работы, а значит и эффективность работы с базами данных в целом.

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

 

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

 

В данной курсовой работе необходимо сформировать и обработать базу данных, содержащую информацию об ассортименте обуви в магазине с помощью редактора электронных таблиц Microsoft Excel и средств Visual Basic for Application (VBA).

 

1.1. Входные данные

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

1. Наименование;
2. Артикул (м - мужская , д - дамская , п - детская обувь, далее цифра);
3. Цена;
4. Количество пар;
5. Размер.

Поля 1, 2 имеют текстовый  формат данных, поля 3, 4, 5 – числовой формат данных.

Также входными данными  являются: переменная Y (артикул), имеющая  текстовый формат данных, а также  переменные Z (размер обуви – для задания 1), Z (цена обуви – для задания 3), W и V (размеры обуви), имеющие числовой формат данных.

 

1.2. Выходные данные

На основе исходной таблицы  необходимо с помощью инструментов редактора таблиц Excel и средств Visual Basic for Application (VBA) сформировать документы:

1. сведения об обуви Y артикула и Z размера;
2. ассортиментный список мужской обуви;
3. сведения о детской обуви не дороже Z рублей, размеры от W до V.

 

2. Математическое  описание алгоритма

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

Пусть имеется непустое множество M = {A_i , B_{i ,}  C_{i ,}  D_{i ,}  E_i }, 

где  i Î [1..I], где I – кол-во элементов множества M; 

A_i – Наименование обуви; B_i – Артикул; C_i – Цена; D_i – Количество пар; E_i – Размер.

 

1). Нужно сформировать некоторое  множество P, состоящее и элементов множества M, причем, только из тех элементов, которые удовлетворяют условию, что артикул B_i равен заданному значению переменной Y, а размер E_i равен заданному значению переменной Z.

Определим множество P Ì M следующего вида:

P = {A_j , B_{j ,}  C_{j ,}  D_{j ,}  E_j },

где j Î [1..J], где J – кол-во элементов множества P;

Зададим условие, что  для каждого i верно, что m_i Î M и m_i Î P, если выполняется условие:  

             и              (1.1)

где m_i – i-й элемент множества M;

i Î [1..I], где I – кол-во элементов множества M;

Y – заданный артикул;

. Z – заданный размер обуви.

Если для i-го элемента условие (1.1) не выполняется, то m_i Ï P. Если для всех i условие (1.1) не выполняется, то P =Æ – пустое множество. Т.о. полученное множество P будет содержать сведения об обуви артикула Y и размера Z.

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

Так как переменные Y и Z не определены и задается пользователем непосредственно во время работы с базой данных, то задачу лучше решать с использованием элементов управления  и средств языка Visual Basic for Application.

 

 

 

2). Нужно сформировать  некоторое множество R, состоящее и элементов множества M, причем, только из тех элементов, которые удовлетворяют условию, что артикул B_i начинается с буквы «м» (мужская обувь).

Определим множество R Î M следующего вида:

R={A_k , B_{k ,}  C_{k ,}  D_{k ,}  E_k },

где k Î [1..K], где K – кол-во элементов множества R.

Зададим условие, что  для каждого i верно, что m_i Î M и m_i Î R, если выполняется условие:  

     M{B_i}= « м* » (1.2)

где m_i – i-й элемент множества M;

i Î [1..I], где I – кол-во элементов множества M;

знак «*» означает любые  символы.

Если для i-го элемента условие (1.2) не выполняется, то m_i Ï R. Если для всех i условие (1.2) не выполняется, то R =Æ – пустое множество. Т.о. полученное множество R будет содержать сведения о мужской обуви.

В редакторе электронных  таблиц Microsoft Excel выборку элементов, удовлетворяющих некоторому заданному условию, из некоторого множества элементов можно реализовать с помощью команды фильтрации списков «Расширенный фильтр». Задача будет решаться с использованием средств языка Visual Basic for Application.

 

3. Нужно сформировать  некоторое множество Q, состоящее  и элементов множества M, причем, только из тех элементов, которые  удовлетворяют условию, что артикул B_i начинается с буквы «п» (детская обувь), цена C_i не превышает заданного значения переменной Z, а размер обуви C_i находится в пределах от W до V.

Определим множество Q Î M следующего вида:

Q={A_g , B_{g ,}  C_{g ,}  D_{g ,}  E_g , F_g },

где g Î [1..G], где G – кол-во элементов множества Q.

Зададим условие, что для каждого i верно, что m_i Î M и m_i Î Q, если выполняется условие:  

 и              (1.3)

где m_i – i-й элемент множества M;

i Î [1..I], где I – кол-во элементов множества M;

знак «*» означает любые  символы;

Z – заданная цена обуви;

W,V – заданные размеры обуви.

Если для i-го элемента условие (1.3) не выполняется, то m_i Ï Q. Если для всех i условие (1.3) не выполняется, то Q = Æ – пустое множество. Т.о. полученное множество Q будет содержать сведения о туристических маршрутах на турбазы Z и W.

В редакторе электронных  таблиц Microsoft Excel выборку элементов, удовлетворяющих некоторому заданному условию, из некоторого множества элементов можно реализовать с помощью команды фильтрации списков «Расширенный фильтр». Так как переменные Z, W и V не определены и задаются пользователем непосредственно во время работы с базой данных, то задачу лучше решать с использованием элементов управления и средств языка Visual Basic for Application.

 

3. Описание исходных  данных

В редакторе электронных таблиц Microsoft Excel была создана таблица, состоящая из пяти столбцов:

1. «Наименование» – наименование обуви (текстовый формат данных);
2. «Артикул» – «м»-мужская, «д»-дамская, «п»-детская обувь, далее цифра (текстовый формат данных);
3. «Цена» – цена за пару в рублях (формат данных «Денежный» в рублях с двумя десятичными знаками в дробной части, обозначающих копейки);
4. «Количество пар» – количество пар данного типа обуви в магазине (числовой формат данных, неотрицательное число);
5. «Размер» – размер обуви (числовой формат данных, двузначное положительное число, целое, либо с одним знаком после запятой).

Таблица содержит 21 запись. Названия столбцов таблицы расположены  в ячейках A3–E3. Первая запись таблицы соответствует строке 4 листа Excel. Последняя запись таблицы соответствует строке 24 листа Excel. Записи в таблице отсортированы с помощью команды «Данные – Сортировка» по возрастанию по полям: 1 – «Наименование», 2 – «Артикул», 3 – «Размер».

Исходные данные для  решения задачи представлены в таблице П.1.1 ПРИЛОЖЕНИЯ 1.

4. Описание решения  задачи

1) Документ, содержащий  сведения об обуви Y артикула и Z размера можно получить с помощью команды фильтрации списков в Excel «Расширенный фильтр».

Для этого необходимо задать критерии фильтрации. Если рассматривать исходную таблицу в качестве множества M, в котором i-й элемент соответствует i-й записи таблицы, (где i Î [1..I], где I – количество записей исходной таблицы (в нашем случае I=21)), то критерием фильтрации является выполнение условия (1.1). При выполнении этого условия для i-й записи, эта запись включается в результирующую выборку, иначе - не включается.

Скопируем заголовок  исходной таблицы данных в произвольную свободную часть листа Excel. Пусть это будет область A27 – E27.

Далее необходимо задать значения переменных Y и Z в соответствующих окнах ввода элементов управления TextBox и нажать кнопку «Получить документ». При нажатии кнопки «Получить документ» значения введенных переменных Y и Z копируются в ячейки В28 (под заголовком столбца «Артикул») и Е28 (под заголовком столбца «Размер») соответственно.

При выполнении команды  «Расширенный фильтр» в Excel, условия в одной строке интервала критериев объединяются по правилу «И», таким образом будет реализовано условие (1.1).

Диапазон критериев фильтрации для Y = м8 и Z = 43 представлен в таблице П.2.1  ПРИЛОЖЕНИЯ 2.

2) Документ «Ассортиментный  список мужской обуви» можно  получить с помощью команды  фильтрации списков в Excel «Расширенный  фильтр». 

Для этого необходимо задать критерии фильтрации. Если рассматривать исходную таблицу в качестве множества M, в котором i-й элемент соответствует i-й записи таблицы, (где i Î [1..I], где I – количество записей исходной таблицы (в нашем случае I=21)), то критерием фильтрации является выполнение условия (1.2). При выполнении этого условия для i-й записи, эта запись включается в результирующую выборку, иначе - не включается.

Скопируем заголовок исходной таблицы  данных в произвольную свободную  часть листа Excel. Пусть это будет область A27 – E27. Далее необходимо нажать кнопку «Получить документ». При нажатии кнопки «Получить документ» в ячейку В28 (под заголовком столбца «Артикул») заносится значение «м». При выполнении команды  «Расширенный фильтр» условие фильтрации для любой записи списка считается выполненным, если для этой записи значение проверяемого поля начинается с заданного в диапазоне условий набора символов.  Т.е. при задании условия поиска в поле «Артикул» в виде «м», в результате выполнения команды «Расширенный фильтр» будут выбраны все записи, значение поля «Артикул» которых начинается с буквы «м». Таким образом, реализуется условие (1.2).