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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Автофильтр.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

«Статистические» - СРЗНАЧ(), СТАНДОТКЛОН(), МАКС (число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-ого уровня и т.д.);

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

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