Лабораторная работа № 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;