Организация учета данных о клиентах в торговой фирме

Цель работы: приобрести навыки работы использования электронных таблиц как хранилищ информации на основе обработки списков и организации выборки данных.

Условие задачи

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

Этапы выполнения работы:

Шаг 1. Создание списка клиентов

1.1. Для создания списка следует использовать обычный рабочий лист Excel. Присвойте листу имя Клиенты. Введите в первую строку названия полей первого списка. Укажите в ячейках А1-I1 следующие названия: Название фирмы, Код, Контактная персона, Индекс, Город, Улица, Телефакс, Телефон, Скидка (%). После ввода названия полей измените ширину столбцов и отформатируйте последнее поле процентным форматом. Выделите светло-синим цветом строку заголовка.

1.2. Присвойте имена ячейкам столбцов: столбцу А – Фирма, столбцу В – Код, I – Скидка. Для этого выделите полностью столбец А и в поле имени вместо А1 введите название Фирма (рис. 1), для столбца В и I аналогично.

Рис. 2.198. Изменение имени столбца

1.3. Для ввода данных воспользуйтесь специальным диалоговым окном – формой данных.

Если кнопки Форма нет на ленте, то её можно добавить на панель быстрого доступа, щелкнув стрелку на панели быстрого доступа и выбрав элемент Другие команды. В поле Выбрать команды из щелкните элемент Все команды. В списке выберите кнопку Форма и щелкните элемент Добавить.

Далее необходимо поставить курсор в ячейку А1 и щелкнуть на кнопку Форма. В окне запроса нажмите ОК. В диалоговом окне формы данных рядом с названиями полей создаваемого списка находятся поля ввода, в которые нужно вводить данные (рис. 2.199). Введите в соответствующие поля данные о клиентах (5–6 записей), завершая ввод каждой записи нажатием кнопки Добавить. После ввода последней записи щелкните на кнопке Закрыть.

Рис. 2.199. Диалоговое окно формы данных

Для обеспечения обозримости списка отсортируйте данные в алфавитном порядке по названиям фирм (Данные/Сортировка).

Шаг 2. Создание списка товаров

2.1. Второй список будет содержать данные о предлагаемых фирмой товарах. Каждому товару следует присвоить определенный номер.

Переименуйте второй лист в Товары. Создайте в нем список товаров, состоящий из полей: Номер, Наименование товара, Цена. Введите ячейки А1:С1 перечисленные названия полей по порядку. Измените ширину столбцов, выделите светло-синим цветом строку заголовка, сделайте формат поля Цена – денежный.

2.2. Присвойте имена ячейкам столбцов: столбцу А – Номер, столбцу В – Товар, С – Цена. Для этого выделите полностью столбец А и в поле имени вместо А1 введите название Номер, для столбца В и С аналогично (см. п. 1.2).

2.3. Введите данные (10–13 записей). При этом номера товаров должны быть расположены в порядке возрастания. Данные поля Цена отформатируйте денежным форматом.

Пример списка представлен на рис. 2.200.

Рис. 2.200. Список товаров

Шаг 3. Создание списка заказов

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

3.1. Создайте структуру списка. Для этого переименуйте третий лист в Заказы, в ячейках А1:L1 укажите следующие названия полей:

Месяц, Дата, Номер заказа, Номер товара, Наименование товара, Количество, Цена за ед., Код заказчика, Название фирмы, Сумма заказа, Скидка, Уплачено. Измените ширину столбцов с названиями полей, закрасьте их светло-синим цветом, сделайте выравнивание по центру.

3.2. Присвойте столбцам В, C, D, E, F, G, H, I, J, K, L имена Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата соответственно (см. п. 1.2).

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

3.4. Теперь задайте для списка заказов формулы. В столбце Е должно быть представлено наименование товара, при этом оно вставляется автоматически с помощью формулы. Для этого укажите в ячейке Е2 формулу = ЕСЛИ($D2="";"";ПРОСМОТР($D2;Номер;Товар)). Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ячейке D2 данные отсутствуют, то ячейка Е2 также останется незаполненной.

Если в ячейке D2 введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку Е2 возвратится соответствующее значение диапазона Товар. Для этого используется функция ПРОСМОТР.

В ячейки столбца F будет вводиться заказываемое клиентом количество товара. В столбце G (поле Цена за ед.) вставьте формулу = ЕСЛИ($D2="";"";ПРОСМОТР($D2;Номер;Цена)) Задайте столбцу формат Денежный.

3.5. В ячейке Н2 будет указываться код фирмы-заказчика. Код будет вводиться с клавиатуры.

3.6. Задайте автоматическое заполнение ячеек поля Название фирмы с помощью формулы для ячейки I2 = ЕСЛИ($H2="";"";ПРОСМОТР ($H2;Код;Фирма)).

3.7. В поле Сумма заказа укажите общую стоимость заказа без учета скидок. Для этого перемножьте значения в полях Количество и Цена. Также с помощью логической функции ЕСЛИ задайте незаполнение ячеек, когда запись не введена. В ячейку J2 введите формулу = ЕСЛИ (F2="";"";F2*G2) Задайте столбцу формат Денежный.

3.8. Величину скидки (поле Скидка) определите автоматически. Для этого введите в ячейку К2 формулу = ЕСЛИ($H2="";"";ПРОСМОТР ($H2;Код;Скидка)) Задайте столбцу формат Процентный.

3.9. Для определения суммы, подлежащей оплате (поле Уплачено), в ячейку L2 введите следующую формулу = ЕСЛИ(J2="";"";J2-J2*K2) Задайте столбцу формат Денежный.

3.10. Скопируйте формулы из ячеек строки 2 в остальные строки. Введите 10 записей в список заказов.

Пример заполненного списка приведен на рис. 2.201.

Рис. 2.201 Пример заполненного списка заказов

Шаг 4. Создание бланка заказа

4.1. Создайте бланк заказа на четвертом листе (лист назовите Бланк). Обратите внимание, что все четные строки листа не заполняются. Шаблон бланка заказа представлен на рис. 2.202.

Рис.2.202. Шаблон бланка заказа

4.2. В ячейке Е3 будет указываться номер заказа. В ячейке G3 задайте формат – Дата и для автоматического заполнения даты заказа введите формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Дата)).

В ячейку Е5 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Фирма2))

В ячейку I5 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Код2))

В ячейку Е7 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Товар2))

В ячейку I7 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Номер2))

В ячейку Е9 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Количество))

В ячейку I9 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Цена2))

В ячейку Е11 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Сумма))

В ячейку I11 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Скидка2))

В ячейку D13 вставьте формулу =ЕСЛИ($E$3="";"";ПРОСМОТР($E$3;Заказ;Оплата))

4.3. Закончите форматирование бланка, улучшив внешний вид документа и обеспечив его однородное оформление. Проверьте процедуру автоматического заполнения бланка. Введите в ячейку Е3 номер заказа. Введите свою фамилию рядом с записью Оформил. Пример оформленного и заполненного бланка см. на рис. 2.203.

Рис.2.203. Пример созданного бланка заказа

Поиск по сайту: