Разработка базы данных "Магазин бытовой техники"

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

1. Работа с базами данных в табличном процессоре Microsoft Excel

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

Внутри базы данных информация может быть организована по разному. Наиболее распространенной в данное время является реляционная модель баз данных. В этой модели данные представлены в виде связанных между собой таблиц.

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

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

В списке Excel каждый столбец - это поле, а каждая строка - это запись.

В Excel предусмотрена функция создания списка с помощью формы:

1) Сформируйте заглавную строку. В каждом столбце этой строки введите название соответствующего поля записи.

2) Щелкните на любой из ячеек заглавной строки, а затем из меню Данные выберите команду Форма. На экране появится диалоговое окно, содержащее поля, названия и количество которых соответствует созданным заголовкам столбцов.

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

В Excel существуют понятия абсолютной и относительной ссылок.

Ссылка на ячейку типа =А1 является относительной. При копировании такая ссылка изменяется автоматически. Если вы в ячейку В1 ввели формулу =А1, а потом скопировали ячейку в С1, то в ячейке С1 будет уже формула =В1. Если же вы скопируете ячейку В1 в ячейку В2, то в ячейке В2 будет формула =А2. То есть при копировании по горизонтали изменяются номера столбцов, и при копировании по вертикали меняются номера строк.

Это очень удобно при вычислениях.

Абсолютные ссылки отличаются от относительных тем, что при копировании не изменяются. Записываются они со знаком «$». Например, =$A$1. Это иногда используется в задачах, если нужна ссылка на одну определенную ячейку, например в ячейке хранится курс рубля к доллару или минимальная заработная плата исходя из которой вы будете рассчитывать остальные показания. В формуле можно использовать абсолютные и относительные ссылки, комбинируя их таким образом, чтобы автозаполнение ячеек было как можно более удобным и результаты пересчитывались автоматически.

Например =А2*$F$1 - ссылка на F1 абсолютная, а на А2 - относительная.

1.1 Сортировка и фильтрация данных

Числовые или текстовые данные можно отсортировать по какому-нибудь критерию. Количество данных (в отличие от фильтрации) сохраняется.

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

Чтобы отсортировать список, выделите любую ячейку в списке, а затем из меню Данные выберите команду Сортировка. На экране появится диалоговое окно Сортировка диапазона.

1) Щелкнув на кнопке со стрелкой в группе Сортировать по, увидите список заглавных строк. Выберите соответствующий пункт из этого списка, и Excel расставит все записи в соответствии с порядком значений, помещенных в ячейки данного столбца.

2) Установки По возрастанию и По убыванию определяют порядок сортировки. Вариант По возрастанию означает сортировку по возрастающим числам (начиная с самого малого и заканчивая самым большим), по буквам от А до Я и по датам и времени - начиная с самых ранних значений и до самых поздних. При сортировке По убыванию значения располагаются в обратном порядке.

3) В поле Затем по выбирается установка для вторичной сортировки.

Отбор данных на основе фильтра.

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

Автофильтр.

- Выделить любую ячейку списка

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

- Если выбран пункт Условие, появиться окно диалога которое позволяет задать правила фильтрации для числовых значений в виде двух неравенств, объединенных логическими операциями И или ИЛИ. В задании условий допустимы символы * и?.

- После установки условий нужно щелкнуть на кнопке ОК.

Таблица 1.1 - Правила фильтрации

Пункт списка	Описание
Все	Отменяет действие фильтрации по этому полю
Первые 10	Отображает заданное пользователем количество записей с наибольшими или наименьшими значениями
Условие	Выводит окно для задания правил фильтрации в виде неравенств
Пустые	Выводит только те записи, у которых данное поле пустое
Непустые	Выводит только те записи, у которых данное поле не пустое

Расширенный фильтр

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

1) Куда поместить отфильтрованный список: фильтровать на месте или скопировать результат в другое место (в этом случае необходимо указать диапазон, в который следует поместить отфильтрованный список).

2) Исходный диапазон - диапазон списка.

3) Диапазон условий - диапазон ячеек, содержащий набор условий поиска. Диапазон условий состоит из строки подписей условий и одной или нескольких строк самих условий. Условия, перечисленные в одной строке диапазона условий должны выполняться одновременно, в разных - хотя бы одно из условий должно быть удовлетворено.

4) В случае если флажок Только уникальные записи установлен, из совпадающих по содержанию записей выводится только одна.

5) Щелкнуть по кнопке ОК.

Возможно создать и свое условие отбора с помощью Пользовательского автофильтра.

1.2 Встроенные функции Excel

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

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

Все функции разбиты по категориям.

Наиболее полезными являются:

«10 недавно использовавшихся» - это те функции, которые наиболее часто используются.

«Математические» - СУММ (число1; число2;…), КОРЕНЬ(число), СТЕПЕНЬ (число, степень), ОКРУГЛ(), ЦЕЛОЕ(), SIN(), COS()…

«Статистические» - СРЗНАЧ(), СТАНДОТКЛОН(), МАКС (число1; число2;…), МИН (число1; число2;…), СРЗНАЧ (число1; число2;…), ДИСП (число1; число2;…).

«Дата и время» - работа с датами, например, функция ТДАТА().

«Логические» - ЕСЛИ (условие; результат если условие истинно; результат если условие ложно).

СЧЁТЕСЛИ (интервал; критерий) - возвращает количество ячеек в интервале, которые удовлетворяют критерию;

СУММЕСЛИ (интервал; критерий; интервал суммирования) - возвращает сумму значений в ячейках из интервала суммирования, отфильтрованных в соответствии с критерием, применяемым к интервалу;

СРЗНАЧ (число1; число2;…) - возвращает среднее арифметическое своих аргументов, которые могут быть числами или именами;

МИН (число1; число2;…) - возвращает минимальное значение из списка аргументов;

ВПР{VLOOKUP} (ЗН; ИН; номер; ключ) - значение из столбца с заданным номером, находящегося в той строке ИН, где левый элемент совпадает с ЗН. Ключ=0 требует точного совпадения. Если совпадения нет, то выдается сообщение об ошибке #N/A.

1.3 Подведение промежуточных итогов в таблице

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

Для получения промежуточных итогов нужно:

- Выделить исходный список.

- Команда Данные, Итоги. Откроется окно диалога

Рисунок 1.3 - Промежуточные итоги

- В списке «При каждом изменении в»: выбрать группообразующий столбец.

- В списке «Операция»: выбирать итоговую функцию.

- В списке флажков «Добавить итоги по»: указываются столбцы, по которым подводятся итоги.



Три нижних флажка служат для:

- Заменить текущие итоги - обновление итоговых данных.

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

- Итоги под данными - обеспечивает размещение итоговых данных ниже исходных.

1.4 Создание сводных таблиц

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

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

Мастер сводных таблиц осуществляет построение в несколько этапов.

Первый этап: указания вида источника сводной таблицы.

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

Второй этап: указание интервала ячеек для построения сводной таблицы.

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

Рисунок 1.3 - Мастер сводной таблицы

Третий этап: построение макета сводной таблицы.

Для начала построения макета нужно щелкнуть на кнопке Макет. Появиться окно диалога.

Рисунок 1.5 - Макет сводной диаграммы

Структура сводной таблицы состоит из следующих областей, которые вы определяете в макете:

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

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

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

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

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

После создания макета нужно щелкнуть на кнопке ОК. Затем следует указать где будет расположен результат: на новом листе или на том же листе что и исходные данные. Результат появится после щелчка на кнопке Готово.

2. Создание и использование базы данных «Магазин бытовой техники»

2.1 Разработка и заполнение таблицы Производители

Таблица Производители содержит данные о производителях товара. Каждая запись имеет 2 поля. Структура записей представлена в таблице 2.1.

Таблица 2.1 - Структура записей таблицы Производители

Имя поля	Описание поля	Тип данных
Производитель	Наименование производителя	Текст, длиной не более 30 символов
Страна	Страна	Текст, длиной не более 30 символов

Порядок разработки и заполнения таблицы:

1) Лист 1 рабочей книги переименован в соответствии с названием таблицы - Производители.

2) В первую строку листа введены имена полей таблицы.

3) Установлена проверка на ввод значений в поля таблицы:

- выделен соответствующий столбец таблицы;

- на вкладке Данные, в группе Работа с данными выбрана команда Проверка данных…;

- в диалоговом окне Проверка вводимых значений на вкладке Параметры заданы требуемые условия проверки; условие проверки для поля Производитель показано на рис. 2.1.

- на вкладке Сообщение для ввода введен текс сообщения для ввода данных в поле таблицы (рис. 2.2);



Рисунок 2.1 - Условие проверки для поля Производитель

Рисунок 2.2 - Сообщение для ввода данных в поле Производитель

- на вкладке Сообщение об ошибке введен текст сообщения, которое появляется при введении данных, не соответствующих условиям проверки (рис. 2.3).

Второе поле заполняется аналогично.

В таблицу было внесено 15 записей (рис. 2.4).



Рисунок 2.3 - Сообщение об ошибке при вводе данных в поле Производитель

Рисунок 2.4 - Таблица Производители

2.2 Разработка и заполнение таблицы Номенклатура

Таблица Номенклатура содержит данные о товарах магазина. Каждая запись имеет 7 полей. Структура записей представлена в таблице 2.2.

Таблица 2.2 - Структура записей таблицы Номенклатура

Имя поля	Описание поля	Тип данных
Шифр	Шифр товара, уникальный 5-значный номер	Текст, длиной 5 символов
Товарная группа	Группа, к которой относится товар	Список значений: телевизоры, холодильники, стиральные машины, пылесосы
Товар	Наименование товара	Текст, длиной 50 символов
Производитель	Наименование производителя	Список значений из таблицы Производители
Количество	Количество товаров, штук	Целое положительное число
Цена закупки	Цена закупки товара, руб. за штуку товара	Действительное положительное число
Цена реализации	Цена реализации (продажи) товара, руб. за штуку товара	Действительное положительное число

Порядок разработки и заполнения таблицы:

1) Лист рабочей книги переименован в соответствии с названием таблицы - Номенклатура.

2) В первую строку листа введены имена полей таблицы.

3) Установлена проверка на ввод значений в поля таблицы:

- выделен соответствующий столбец таблицы;

- на вкладке Данные, в группе Работа с данными выбрана команда Проверка данных…;

- в диалоговом окне Проверка вводимых значений на вкладке Параметры заданы требуемые условия проверки; условие проверки для поля Шифр показано на рис. 2.5.



Рисунок 2.5 - Условие проверки для поля Шифр

- на вкладке Сообщение для ввода введен текс сообщения для ввода данных в поле таблицы (рис. 2.6);

Рисунок 2.6 - Сообщение для ввода данных в поле Шифр

- на вкладке Сообщение об ошибке введен текст сообщения, которое появляется при введении данных, не соответствующих условиям проверки (рис. 2.7).

Рисунок 2.7 - Сообщение об ошибке при вводе данных в поле Шифр

Рассмотрим технологию создания полей с выпадающим списком, например поле Товарная группа:

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

Рисунок 2.8 - Список значений для поля Товарная группа

2) Теперь нужно выделить список значений и на вкладке Формулы нажать кнопку Присвоить имя, далее в появившемся окне заполняем поля Имя и нажимаем Ок.

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

4) Далее на вкладке Данные нажимаем кнопку Проверка данных, в появившемся окне в поле Тип данных выбираем значение Список, в поле Источник вводим знак «=» и набираем имя списка назначенного нами ранее, жмем Ок.

5) Теперь попадая на одну из ячеек, для которых создавался список, то

6) справа от ячейки появится кнопка с раскрывающимся списком (рис. 2.9).

Рисунок 2.9 - Список значений для поля Товарная группа

Рисунок 2.10 - Таблица Номенклатура

2.3 Разработка и заполнение таблицы Книга продаж

Таблица Книга продаж содержит данные о реализации товаров. Каждая запись содержит 7 полей. Структура записей приведена в таблице 2.3.

Таблица 2.3 - Структура таблицы Книга продаж

Имя поля	Описание поля	Тип данных
Дата продажи	Дата продажи товара	Дата в формате ДД.ММ.ГГГГ
Шифр	Шифр товара	Список значений из таблицы Номенклатура
Товар	Наименование товара	Рассчитывается по значению поля Шифр и данным таблицы Номенклатура
Продано	Количество проданного товара, штук	Целое положительное число
Сумма	Стоимость проданного товара, штук	Рассчитывается
Оплата	Вид оплаты товара	Список значений: наличный расчет, безналичный расчет
Доставка	Доставка товара покупателю	Список значений: да, нет

Поля таблицы заполняются по технологиям, описанным в разделе.

В таблицу Журнал регистрации было внесено 30 записей (рис. 2.11).



Рисунок 2.11 - Таблица Книга продаж

2.4 Сортировка данных

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

Порядок выполнения сортировки:

1) Курсор установлен в область данных, т.е. в одной из ячеек таблицы Номенклатура.

2) На вкладке Данные, в группе Сортировка и фильтр выбрана команда Сортировка.

3) В диалоговом окне Сортировка заданы уровни сортировки: по количеству товара (рис. 2.12.).



Рисунок 2.12 - Условия для сортировки по количеству товара

Результат сортировки показан на рисунке 2.13.

Рисунок 2.13 - Результат сортировки по цене закупки

Второй вид сортировки по товарной группе и производителю выполняется аналогично. На рисунке 2.14 представлены этапы сортировки.



Рисунок 2.14 - Условия для второй сортировки

Результат сортировки показан на рисунке 2.15.

Рисунок 2.15 - Результат сортировки

2.5 Фильтрация данных

Фильтрация данных проводилась с использованием технологий автофильтр и расширенного фильтра.

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

Порядок выполнения фильтрации записей Производители:

1) Курсор устанавливается в область записей.

2) На вкладке данные, в группе Сортировка и фильтр выбираем команду фильтр.

3) В списке режимов фильтрации поля Расчет «да» (рис. 2.16).

excel база магазин таблица

Рисунок 2.16 - Пользовательский автофильтр

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



Рисунок 2.17 - Отфильтрованные записи

Заключение

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

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

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

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

При выполнении заданий я использовал многие технических приёмы работы с программой:

- использования множества встроенных функций Excel из разных категорий,

- использование фильтрации,

- функция копирования и вставки на другой лист.

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

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

Список источников

1. Степанов А.Н. Информатика: Учебник для вузов. 5-е изд. - СПб.: Питер, 2007. - 765 с.: ил.

2. Информатика / Под ред. Макаровой Н.В. - 3-е изд., перераб.-М.: Финансы и статистика, 2004.-768 с.

3. Самохина, М.И., Квирам С.А. Информатика: лабораторный практикум - Братск: ГОУ ВПО «Братский государственный университет», 2008. - 88 с.

4. Элисон Берроуз «Секреты Excel» - М.:Веста, 2003 -753 с.

Размещено на Allbest.ru