Лабораторная работа № 4. Базы данных

Цель работы: рассмотрение вопросов, связанных с базой данных и взаимодействием данных, расположенных на различных рабочих листах. (4 часа.)

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

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

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

Введем в строку 1 название полей первого списка. В ячейках A1 – I1следующие названия:

Рис. 4.1

Название фирмы

Код

Контактная персона

Индекс

Город

Улица

Факс Телефон

Скидка (%)

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

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

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

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

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

С помощью диалогового окна формы данных можно выполнить поиск нужных записей, задавая различные критерии поиска. После нажатия кнопки Критериина экране появится незаполненная форма с текущими названиями полей. Укажите в соответствующем поле ввода значение, которое должно выступать в качестве критерия и запустите поиск нажатием кнопки Далее. При задании критерия можно также использовать символы подстановки, если точное написание значения неизвестно. Символ (*) служит для обозначения любого количества неизвестных символов, а символ (?) – для обозначения одного символа.

Чтобы завершить работу со списком, отсортируем его (по возрастанию) по коду фирмы-клиента. Кроме того, большое значение в последующей работе могут иметь имена, присваиваемые диапазонам ячеек. (С их помощью задавать аргументы функций гораздо проще, и в этом случае можно ввести любое количество данных). Поэтому выделим столбец A с помощью щелчка на заголовке столбца и в поле имени укажем имяФирма. Затем столбцуBприсвоим имя Код, а столбцуC– Скидка. Если при вводе имени была сделана ошибка и следует неправильно введенное имя удалить, то воспользуйтесь командойИмя/Присвоитьменю Вставка. В появившемся диалоговом окне выберите имя и нажмите кнопкуУдалить.

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

Рис. 4.2

Введем указанные названия полей в ячейки A1 – C1и сразу же присвоим имена ячейкам столбцов A, Bи C– Номер, Наименование товара и Цена соответственно. Затем вводим данные. При этом желательно, чтобы номера были расположены в порядке возрастания. В противном случае после ввода данных отсортируйте список по номеру товара. После ввода данных получаем таблицу, показанную на рис. 4.2.

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

Создадим структуру списка. Для этого в ячейках A1–L1 надлежит указать следующие названия полей:

Месяц

Дата

Номер заказа

Номер товара

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

Количество

Цена за ед.

Код заказчика

Название фирмы

Сумма заказа

Скидка

Уплачено

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

Рис. 4.3

C,D,E,F,G,H,I,J,K,Lи введем в поле имени имена: Дата, Заказ, Номер2, Товар, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата. До ввода данных в список определим нужные форматы и то, какие значения в каких полях должны быть указаны. В ячейках поля Месяц будем указывать названия месяцев. Добиться автоматического определения месяца на основе введенной даты с одной стороны достаточно просто, однако, часто вследствие применения различных форматов даты используемая для этого функция МЕСЯЦ будет возвращать не совсем корректный результат. Поэтому на этом этапе будем вводить название месяца. Столбец Bпредполагается использовать для даты выполнения заказов. До ввода дат выделим столбец Bс помощью команды Ячейкаменю Формат, в открывшемся на экране одноименном диалоговом окне активизируем раздел Число и выберем в категории Числовые форматы/Датажелаемый формат даты.

Третий столбец должен содержать номер заказа. Затем переходим в ячейку D2. Во всех ячейках четвертого столбца должен быть указан номер заказываемого товара в соответствии с нумерацией, используемой в рабочем листе Товары. В ячейкеH2следует указать код фирмы-заказчика. Код придется ввести с клавиатуры. Таблица, полученная после ввода данных и имеющая название Заказы, приведена на рис. 4.3.

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

= ЕСЛИ($D2=«»; «»; ПРОСМОТР($D2; Товары!A$2:A$12;Товары! B$2: B$12)).

Формулы целесообразно вводить в «русском регистре», а для ввода адресов ячеек следует указать мышкой на соответствующий рабочий лист и затем нужную ячейку. Для ввода знака доллара следует поставить указатель мышки в строке формул перед соответствующим адресом ячейки и нажать клавишу F4.

Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ячейкеD2данные отсутствуют, то ячейка E2также остается незаполненной. Если же в ячейке D2уже введен номер товара, то будет выполняться поиск номера товара в диапазоне Товары!A$2:A$12и в ячейку E2возвратится соответствующее значение из диапазона Товары!B$2:B$12. Для этого используется функция ПРОСМОТР. Вставим эту формулу в ячейку E2с помощью мастера функций. Заметим, что для ячейки D2задана комбинированная ссылка, при копировании формулы это приведет только к изменению номера строки.

В ячейки столбца Fследует ввести заказываемое клиентом количество того или иного товара. В столбцеG(поле Цена за ед.) надо указать цену единицы товара. Поскольку цена уже встречалась, то ее вставку можно задать с помощью формулы, аналогичной вставленной в ячейку E2. Формула в ячейке

G2должна иметь вид:

=ЕСЛИ($D2=«»; «»; ПРОСМОТР($D2; Товары!A$2:A$12; Товары! C$2: C$12)).

Зададим автоматическое заполнение ячеек полей Название фирмы и Скидкас помощью формул. Но теперь в качестве отправного пункта будет выступать значение в ячейке H2. Введем в ячейкуI2формулу:

= ЕСЛИ($H2 = «»; «»; ПРОСМОТР($H2; Клиенты!B$2:B$14;

Клиенты! A$2:A$14)).

В поле Сумма заказа укажите общую стоимость заказа без учета скидок. Для этого следует перемножить значения в полях Количество и Цена. Можно также с помощью логической функции ЕСЛИ задать не заполнение ячеек в том случае, когда запись не введена, что позволит избежать появления ошибки. Поэтому формула в ячейке J2должна иметь вид:

= ЕСЛИ(F2= «»; «»; F2*G2).

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

Рис. 4.4

Величину скидки (поле Скидка) также можно определить автоматически. Для этого достаточно ввести в ячейкуK2формулу:

= ЕСЛИ($H2= «»; «»; ПРОСМОТР($H2; Клиенты!B$2:B$14; Клиенты! I$2:I$14)).

В завершение осталось только определить сумму, подлежащую оплате. Для этого укажем в ячейке L2формулу:

= ЕСЛИ(J2= «»; «»; J2-J2*K2).

В результате получаем окончательную таблицу заказов, представленную на рисунке рис.4.4.

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

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

Поместим указатель ячейки на ячейку D3 и введем название бланка Заказ N. Номер заказа следует указать в ячейке Е3, при желании его можно подчеркнуть. Для этого в списке Линии рамкинадо установить обрамление ячейки линией снизу. Не забывайте во время работы при необходимости изменять ширину столбцов. В ячейке F3введем от и уменьшим ширину столбца. В ячейке G3 будет поставлена дата заказа, которую вставим с помощью формулы:

=ЕСЛИ($E$3= « »; « »;ПРОСМОТР($E$3;Заказ;Дата)).