Лабораторная работа № 7 Учет работы с клиентами в торговой фирме

Данная лабораторная работа посвящена возможностям ЭТ по работе со списками, что дает возможность квалифицированно осуществлять процесс управления. При управлении данными в фирмах составляются различные таблицы по работе с клиентами, товарами, заказами, поставщиками и т.д. Таблицы можно использовать в качестве БД, где строки соответствуют записям, а столбцы полям. Возможности ЭТ значительно уступают возможностям СУБД. Однако ЭТ – это удобный инструмент управления БД небольшого объёма, где можно совместить использование функций по обработке таблиц и списков.

Задание для подготовки к лабораторной работе

Изучите функции для работы со списками(ВЫБОР, ПОИСКПОЗ, ПРОСМОТР, ГИПЕРССЫЛКА). Опишите изученные функции в отчете к лабораторной работе. Ознакомьтесь с возможностями Excel формирования сводных таблиц.

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

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

Порядок выполнения лабораторной работы 1. Создание списка клиентов

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

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

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

1.3. Записи в нашем списке расположены хаотически, что значительно затрудняет быстрый поиск нужной информации. Поэтому для обеспечения обозримости списка отсортируйте данные в алфавитном порядке по названиям фирм. Пример полученной таблицы на рис.7.2.

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

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

Рис. 7.2. Пример списка клиентов

2.2. Введите данные. При этом желательно, чтобы номера были расположены в порядке возрастания. В противном случае после ввода данных отсортируйте список по номеру товара. Измените ширину столбцов и отформатируйте список. Пример смотрите на рис. 7.3

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

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

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

3.1. Создайте структуру списка. Для этого в ячейках A1-L1 укажите следующие названия полей: Месяц, Дата, Номер заказа, Номер товара, Наименование товара, Количество, Цена за ед., Код заказчика, Название фирмы, Сумма заказа, Скидка, Уплачено. Пример полученной таблицы смотрите на рис. 7.4.

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

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

3.3. Как и в предыдущих рабочих листах, присвойте ячейкам некоторых столбцов имена. Выделите по очереди столбцы В, С, D, Е, F, G, Н, I, J, К, L и введите в поле имени имена: Дата, Заказ, Номер2, Товар2, Количество, Цена2, Код2, Фирма2, Сумма, Скидка2 и Оплата.

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

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

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

= ЕСЛИ ($D2=””; ””; ПРОСМОТР ($D2; Номер; Товар)

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

В ячейки столбца F следует ввести заказываемое клиентом количество того или иного товара.

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

=ЕСЛИ($D2=" "; " "; ПРОСМОТР($D2; Номер; Цена)

Вполне целесообразно скопировать формулу из ячейки E2 в ячейку G2 и затем только изменить имя диапазона. Значение " пробел", представленное в ячейке в качестве результата применения формулы, убедит Вас в ее правильности.

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

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

=ЕСЛИ($Н2=" "; " "; ПРОСМОТР ($Н2; Код; Фирма)

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

=ЕСЛИ(F2=" "; " "; F2*G2)

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

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

= ЕСЛИ ($H2=" "; " "; ПРОСМОТР ($H2; Код; Скидка)

3.11. Определим сумму, подлежащую оплате. Для этого укажите в ячейке L2 следующую формулу

= ЕСЛИ (J2=" "; " "; J2-J2*K2)

3.12. Задайте некоторые параметры форматирования – центрирование значений, присвойте соответствующие стили (денежный и процентный) ячейкам нужных столбцов, а также закрасьте ячейки, в которых должны быть введены значения, голубым цветом, а ячейки, значения в которых будут определены на основе формул, – желтым цветом.

3.13. Выделите ячейки B2-L2 и выберите в меню Правка команду Заполнить/Вниз. Тем самым Вы зададите копирование значений ячеек строки 2 в остальные ячейки.

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

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

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

4.1. Подготовьте свой вариант бланка, начав с выбора шрифта. В списке Шрифт панели инструментов Форматирование выберите новый вид шрифта.

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

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

Подчеркните вставляемое с помощью формулы значение, проведя нижнюю линию обрамления. Значения в строке 3 должны иметь полужирное начертание и шрифт размером в 14 пунктов.

4.3. Перейдите к оформлению второй строки бланка. В ячейку С5 введите текст Название фирмы-заказчика. При этом старайтесь ввести текст таким образом, чтобы он заполнил ячейки С5 и D5. Для названия фирмы мы отвели ячейки Е5, F5, G5. Чтобы при заполнении заказа название фирмы вставлялось автоматически, поместите в ячейку E5 формулу

=ЕСЛИ($Е$3=" "; " "; ПРОСМОТР($Е$3; Заказ; Фирма2)

Если Вы помните, для удобства и упрощения работы диапазонам ячеек базы данных Заказы были присвоены имена. Подчеркните название фирмы и расположите его по центру диапазона из трех ячеек. В ячейку Н5 введите слово Код, а в ячейку I5 поместите формулу

=ЕСЛИ($Е$3=" "; " "; ПРОСМОТР($E$3; Заказ; Код2)

4.4. Теперь займитесь оформлением третьей строки бланка. В ячейку С7 введите текст Наименование товара, а для ячеек Е7, F7 и G7 примените подчеркивание и центрирование. Ячейка E7 должна содержать формулу

=ЕСЛИ ($Е$3=" "; " ", ПРОСМОТР ($E$3; Заказ; Товар2)

В ячейку H7 введите N (символ номера), а в ячейке I7 укажите формулу

=ЕСЛИ($Е$3=" "; " "; ПРОСМОТР ($Е$3; Заказ; Номер2)

и примените подчеркивание для помещаемого в ячейку I7 значения.

4.5. Четвертая строка бланка должна содержать сведения о количестве и цене заказываемого товара. В ячейку С9 введите текст Заказываемое количество. Для самого значения следует зарезервировать ячейку Е9. Значение будет вставляться автоматически, если в ячейку E9 ввести формулу

= ЕСЛИ ($E$3=" "; " "; ПРОСМОТР ($Е$3; Заказ; Количество)

Так же как и для всех полей бланка, предназначенных для ввода переменной информации, проведите под этой ячейкой нижнюю линию обрамления. В ячейку F9 введите с клавиатуры: ед. по цене, выровняйте введенный текст по центру столбцов F и G. Ячейка Н9 должна содержать формулу

=ЕСЛИ ($E$3" "; " "; ПРОСМОТР ($E$3; Заказ; Цена2)

К этой ячейке следует применить подчеркивание и денежный стиль. Над четвертой строкой бланка в ячейку I9 поместите текст за ед.

4.6. В ячейку C11 введите текст: Общая стоимость заказа, а в ячейку Е11 поместите формулу

= ЕСЛИ ($E$3=" "; " "; ПРОСМОТР($Е$3; Заказ; Сумма)

и задайте для ячейки параметры форматирования: нижняя линия обрамления и денежный стиль. В ячейку F11 введите: Скидка (%), выделите ячейки F11, G11 и H11 и выполните щелчок на кнопке Центрировать по столбцам. В ячейку I11 поместите формулу

= ЕСЛИ ($E$3=" "; " "; ПРОСМОТР ($E$3; Заказ; Скидка2)

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

4.7. Теперь приступим к работе над последней строкой бланка. Введите в ячейку С13 текст: К оплате, а в ячейку D13 поместите формулу

= ЕСЛИ ($E$3=" "; " "; ПРОСМОТР ($E$3; Заказ; Оплата)

и вновь задайте для ячейки следующие параметры форматирования: обрамление рамкой снизу и денежный стиль. В заключение укажите фамилию лица, оформлявшего заказ. В ячейке Е13 введите: Оформил, выделите ячейки Е13, F13, задайте центрирование текста по столбцам. Затем выделите ячейки G13, Н13 и I13, задайте для них центрирование текста по столбцам и обрамление рамкой снизу.

4.8. Закончите форматирование, улучшив внешний вид документа и обеспечив его однородное оформление. Установите ширину столбцов В и J, равную 1, 75, выделите диапазон ячеек B2-J14 и задайте обрамление всего диапазона.

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

Рис. 7.5. Созданный бланк заказа

5. Анализ данных с помощью сводной таблицы

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

5.1. Перейдите в пятый рабочий лист и присвойте ему имя Таблица. В этом рабочем листе и будет размещена сводная таблица.

5.2. Активизируйте меню Данные и выберите команду Сводная таблица. Поскольку после имени команды следует троеточие, то в результате ее активизации откроется диалоговое окно – первое окно мастера сводных таблиц. В этом окне Вам будет предложено выбрать один из четырех источников данных для сводной таблицы. Одобрите выбор, сделанный мастером: опцию В списке или базе данных Microsoft Excel и нажмите кнопку Далее.

Рис. 7.6. Окно мастера сводных таблиц – выбор источника

5.3. Укажите диапазон ячеек выбранного источника данных, который будет использован в сводной таблице. Если бы в момент вызова мастера сводных таблиц указатель ячейки находился внутри списка заказов (в рабочем листе Заказы), то Excel автоматически поместил бы нужный диапазон в поле ввода Диапазон. Однако поскольку мы уже перешли в рабочий лист Таблица, то теперь, поместив курсор ввода в поле Диапазон, нам следует возвратиться в рабочий лист Заказы и выделить диапазон A1-L21.

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

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

Рис. 7.7. Диалоговое окно создания сводной таблицы

В этом же окне Вы можете задать параметры форматирования ячеек (для этого следует нажать кнопку Формат), а также выполнение дополнительных операций обработки данных в текущем поле (нажав кнопку Дополнительно и указав нужные параметры). С помощью двойного щелчка на имени поля в диалоговом окне мастера таблиц можно изменить параметры для любого поля. В частности, после двойного щелчка на поле Количество выберите в появившемся диалоговом окне переключатель Нет, чтобы в таблице не отображались промежуточные итоги. Обращайте внимание на то, что при задании операций обработки для полей данных в области строки или столбца сводная таблица может получиться слишком громоздкой. Если структура таблицы Вас устраивает, нажмите кнопку Далее и перейдите в последнее окно мастера сводных таблиц. Изображение флага в этом окне информирует о том, что близок конец работы над созданием сводной таблицы. Выберите ячейку рабочего листа Таблица, с которой должна начинаться сводная таблица, присвойте таблице соответствующее имя и подтвердите установленные здесь остальные параметры нажатием кнопки Готово. Через несколько минут сводная таблица появится в рабочем листе.

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

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

5.6. Выбирая из списка Месяц (который выступает в качестве фильтра) нужное значение, можно отобразить данные о продажах для каждого отдельного месяца. В качестве фильтра можно использовать и несколько полей, для чего следует лишь поместить нужное поле в область страницы. Переместите поле Название в область страницы непосредственно под полем Месяц, таким образом, можно использовать значения двух этих полей в качестве фильтра показа.

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

5.8. На отдельном листе сделайте выводы на основе анализа сводных таблиц о работе с клиентами фирмы.

5.9. Напишите отчет по лабораторной работе.

Контрольные вопросы

1. Какие операции обработки списков возможно выполнить с помощью электронной таблицы?

2. Для чего используются сводные таблицы?

3. Опишите технологию построения и редактирования сводной таблицы.

4. Какие функции используются для выборки информации из списка?

5. Как сводная таблица помогает в анализе данных?