Лабораторная работа 4.Учет заказов в Excel
Цель работы: постановка и решение задачи "Учет заказов"
Используемые средства: функции списка Excel, имена диапазонов, консолидация данных, сводные таблицы
Использование Excel в качестве базы данных может значительно облегчить работу отделов сбыта, управление кадрами, логистическими процессами и т.п. В качестве базы данных можно использовать список Excel, где строки соответствуют записям в базе данных, а столбцы — полям. При выполнении основных операций с базами данных список автоматически обрабатывается как база данных. Удобство работы со списками заключается еще и в том, что список можно создавать непосредственно в рабочем листе. Естественно, возможности Excel по управлению данными значительно уступают возможностям систем управления базами данных. Однако, если Вы хотите иметь под рукой удобный инструмент управления базами данных небольшого объема и при этом не углубляться в "дебри" других программ, функции списка Excel — лучшее средство. Неоспоримым преимуществом их использования является возможность комбинирования функций списка с другими функциями программы.
Выполнив эту работу, Вы составите небольшую базу данных, в которой будут представлены наиболее важные и необходимые для работы сведения о клиентах фирмы, предлагаемых товарах и о выполненных заказах. Затем Вы проанализируете данные с помощью сводной таблицы.
Создание списков
Перед тем как приступить к созданию списка, следует продумать структуру: определить, какие данные Вы хотели бы видеть в списке и какие поля необходимо для этого создать? Для создания списка следует использовать обычный рабочий лист, сразу же присвоив ему подходящее имя (которое затем будет использоваться в формулах). Первый рабочий лист (Клиенты) будет содержать общие данные о клиентах, второй (Товары)— данные о предлагаемых товарах, в третьем (Заказы) Вы организуете учет заказов.
Создание списка клиентов
На листе Клиенты создайте список следующей структуры:
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Название фирмы |
Код фирмы |
Контактное лицо |
Индекс |
Город |
Улица |
Телефон |
Скидка (%) |
2 |
Альфа |
1001 |
Иванов |
|
Москва |
|
|
5% |
3 |
Бета |
1002 |
Петров |
|
Москва |
|
|
8% |
4 |
Гамма |
2002 |
Соколов |
|
С-Петербург |
|
|
5% |
5 |
Старт |
2001 |
Филин |
|
С-Петербург |
|
|
10% |
6 |
Финиш |
3001 |
Федоров |
|
В. Новгород |
|
|
10% |
Во втором поле с целью автоматизации выполнения в будущем некоторых операций указывается код клиента, который может иметь произвольный вид, например 101, 102, 103 и т.д.; R-01, R-02 и т.д. Однако он должен быть уникальным.
В нашем примере код четырехзначный: 1-я цифра определяет регион, 3 последние - порядковый номер фирмы в регионе.
Некоторым постоянным клиентам предоставляются определенные скидки за большие объемы заказов. Процент скидки указан в последнем поле списка.
Ввод данных
Вы можете начать вводить данные непосредственно в ячейки таблицы, однако лучше воспользоваться специальным диалоговым окном — формой данных, в котором легко можно выполнить все операции над записями: ввод, редактирование, поиск. Чтобы открыть на экране диалоговое окно формы данных, необходимо выбрать в меню Данные команду Форма.
В диалоговом окне формы данных рядом с названиями полей создаваемого списка находятся поля ввода. Ввод каждой записи завершается нажатием кнопки Добавить. Переход между отдельными полями ввода диалогового окна осуществляется посредством щелчка кнопкой мыши или с помощью нажатия клавиши [Tab].
Диалоговое окно формы данных можно использовать и для обработки записей. Функциональные кнопки Назад и Далее позволяют просмотреть все записи. С помощью кнопки Удалить ненужная запись может быть удалена. После редактирования записи становится доступной кнопка Вернуть. Ее нажатие приводит к восстановлению прежнего вида записи. С помощью диалогового окна формы данных можно выполнить поиск нужных записей, задавая различные критерии поиска. После нажатия кнопки Критерии на экране появится незаполненная форма с текущими названиями полей. Укажите в соответствующем поле ввода значение, которое должно выступать в качестве критерия и запустите поиск нажатием кнопки Далее.
Сортировка данных
Записи в наших списках расположены хаотически, что значительно затрудняет быстрый поиск нужной информации. Для обеспечения лучшей обозримости списков отсортируйте данные в определенном порядке. Для этого воспользуйтесь командой Сортировка меню Данные.
Список Клиенты целесообразно отсортировать (по возрастанию) по коду фирмы-клиента. По мере добавления новых записей в конец списка записи данных можно снова отсортировать, чтобы учесть новые записи. Список можно сортировать не более чем по трем столбцам одновременно.
Нажатием кнопки Параметры можно открыть диалоговое окно, в котором Вам будет предоставлена возможность задать дополнительные параметры сортировки. Если при сортировке следует учитывать различие между строчными и прописными буквами, уместно активизировать опцию Учитывать регистр. По умолчанию Excel выполняет сортировку строк (активизирована опция Строки диапазона). Еще одной интересной возможностью программы является сортировка столбцов в соответствии со значениями ячеек отдельной строки (или строк). Задать ее можно с помощью включения опции Столбцы диапазона группы Сортировать. После нажатия кнопки ОК и возвращения в диалоговое окно Сортировка диапазона в качестве критерия можно выбрать строки, по которым будет происходить сортировка столбцов.
Создание списка товаров
Второй список нашего примера будет содержать данные о предлагаемых фирмой товарах. Каждому товару следует присвоить определенный номер, что в последующем поможет автоматизировать выполнение определенных операций
Создайте список в рабочем листе с названием Товары.
-
A
B
C
1
Номер
Наименование товара
Цена
2
101
Компьютер PC-166
999
3
102
Компьютер PC-100
1150
4
103
Компьютер 486-66
780
5
104
Компьютер 486-100
819
6
201
Принтер лазерный ОХ
1300
7
202
Принтер лазерный ПХ
740
8
203
Принтер струйный ЧБ
299
9
204
Принтер струйный Ц
460
10
301
Монитор14"
220
11
302
Монитор 15"
337
12
303
Монитор 17"
518
Отсортируйте список по коду товара (по возрастанию).
Имена диапазонов
Большое значение в последующей работе будут иметь имена, присваиваемые диапазонам ячеек. С их помощью задавать аргументы функций гораздо проще, и Вы сможете ввести любое количество данных. Присвойте имена столбцам таблицы Товары. Выделите столбец А с помощью щелчка на заголовке столбца и в поле имени укажите имя Код. Затем столбцу В присвойте имя Товар, а столбцу С — имя Цена.
Если Вы ввели какое-то имя неправильно и хотели бы удалить его, воспользуйтесь командой Имя/Присвоить меню Вставка. В появившемся на экране диалоговом окне выберите имя и нажмите кнопку Удалить.
Создание списка заказов
Список должен помочь в управлении данными обо всех выполненных с начала текущего года заказах. При этом желательно максимально автоматизировать процедуру ввода данных. Впоследствии представленные в списке данные будут подвергнуты анализу с помощью мастера сводных таблиц.
При интерактивной работе с документом целесообразно использовать выделение цветом части документа. Если документ предполагается печатать на монохромном принтере, при оформлении следует применять светлые тона. В дальнейшем ячейки, которые предназначены для ввода исходных данных, можно выделить, например, голубым цветом, а ячейки, которые можно не заполнять (их содержимое должно вычисляться автоматически на основе формул), — желтым цветом.
Создайте структуру списка. Для этого в ячейках A1:L1 укажите следующие названия полей:
Ме-сяц |
Дата |
Номер заказа |
Код товара |
Наим. товара |
Коли- чест- во |
Цена за ед. |
Код заказ-чика |
Назва- ние фирмы |
Стоимость
|
Скид-ка
|
Сумма к оплате
|
|
|
|
|
|
|
|
|
|
|
|
|
Выделите строку с названиями полей, выберите нужные параметры шрифта, центрируйте и разрешите перенос по словам в пределах одной ячейки (выберите команду Ячейки меню Формат и активизируйте в разделе Выравнивание появившегося на экране диалогового окна опцию Переносить по словам).
Сами данные в список вводить пока не будем, а только определим нужные форматы и то, какие значения в каких полях должны быть указаны. Впоследствии Вы можете вводить данные о заказах как с помощью формы данных, так и непосредственно в самом рабочем листе.
Поясним назначение некоторых столбцов:
Месяц - номер месяца, вводится автоматически на основе введенной даты.
Дата - дата выполнения заказа.
Номер заказа. Продумайте нумерацию заказов, учитывая необходимость последующей сортировки заказов по номеру заказа.
Код товара - код заказываемого товара в соответствии с кодировкой, используемой в рабочем листе Товары.
Наим. товара.
Автоматизируем вставку наименования товара. Для этого введите в ячейку Е2 формулу:
=ECЛИ($D2=""; ""; ПPOCMOTP($D2; Код; Товар))
Функция ЕСЛИ() проверяет содержимое ячейки D2. Если в ячейке D2 данные отсутствуют, то ячейка Е2 также останется незаполненной. Если же в ячейке D2 уже введен номер товара, то 6yдет выполняться поиск номера товара в диапазоне Код справочника Товары и в ячейку D2 возвратится соответствующее значение диапазона Товар. Для этого используется функция ПРОСМОТР(). Обратите внимание, что для ячейки D2 задана комбинированная ссылка, при копировании формулы это приведет только к изменению номера строки.
Аналогично в ячейку G2 введите формулу для вставки цены товара. Можно скопировать формулу из ячейки Е2 в ячейку G2 и затем только изменить имя диапазона.
Функция ПРОСМОТР() имеет две синтаксические формы: вектор и массив.
Форма массива функции ПРОСМОТР() просматривает первую строку или первый столбец массива, находит указанное значение и возвращает значение из соответствующей позиции последней строки или последнего столбца маcсива.. Если требуется указать положение столбца или строки результата, лучше использовать функции ГПР или ВПР.
Примечание. Функция ПРОСМОТР() «вложена» в функцию ЕСЛИ(). Такое вложение можно осуществить из окна Имя строки формул при развернутом диалоговом окне функции ЕСЛИ(). Причем значение параметра, содержащего вложенную функцию, вводится последним.
Используйте функцию ВПР() вместо ПРОСМОТР() для ввода названия фирмы и скидки по коду заказчика.
Завершите заполнение первой строки, определив, какие поля будут заполняться вручную, а какие автоматически (с помощью формул).
После заполнения первой строки скопируйте формулы в остальные строки.
После ввода записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть данные списка фильтрации, чтобы легко можно было определить, насколько популярным был тот или иной товар, какой клиент обращался в течение этих месяцев чаще всего или выделить заказы, сумма которых превышает определенное значение. Для этих целей могут использоваться функции (средства) фильтрации – автофильтр или расширенный фильтр (пункт меню Данные).