Фирмы, Код, Контактная персона, Индекс, Город, Улица
Скачать 175.92 Kb.
|
 Лабораторная работа № 5 Функции EXCEL по управлению данными В данной лабораторной работе вам необходимо составить небольшую базу данных, в которой будут содержаться сведения о клиентах фирмы. предлагаемых товарах и о выполненных заказах. Первый рабочий лист будет содержать данные о клиентах, второй - данные о предлагаемых товарах, в третьем мы попытаемся организовать учет заказов, а в четвертом обеспечим возможность печати данных для каждого отдельного заказа (бланка заказа). В завершении мы попытаемся проанализировать данные нашего списка. Порядок выполнения лабораторной работы 1.Создание списка клиентов 1.1. Для создания списка следует использовать обычный рабочий лист. Сразу же присвойте ему подходящее имя. Это может быть Клиенты. Введите в первую строку названия полей первого списка. Укажите в ячейках .A1-I7 следующие названия: Название фирмы, Код, Контактная персона, Индекс, Город, Улица, Телефакс, Телефон, Скидка(%). После ввода названий полей измените ширину столбцов и отформатируйте последнее поле процентным форматом. Выделите цветом строку заголовка. 1.2. Для ввода данных воспользуйтесь специальным диалоговым окном - формой данных. Для этого необходимо в меню Данные выбрать команду Форма. В диалоговом окне формы данных рядом с названиями полей создаваемого списка находятся поля ввода, в которые нужно вводить данные (см рис.6.1). Введите в соответствующие поля данные о клиентах, завершая ввод каждой записи нажатием кнопки Добавить Переход между отдельными полями ввода осуществляется посредством щелчка мыши или нажатием клавиши Tab. После ввода последней записи щелкните на кнопке Закрыть.  1.3. Записи в нашем списке расположены хаотически, что значительно затрудняет быстрый поиск нужной информации. Поэтому для обеспечения обозримости списка отсортируйте данные в алфавитном порядке по названиям фирм. Для этого воспользуйтесь командой Сортировка меню Данные. Пример полученной таблицы см на рис.6.2.  2. Создание списка товаров 2.1. Второй список нашего примера будет содержать данные о предлагаемых фирмой товарах. Каждому товару следует присвоить определенный номер, что в последующем поможет нам автоматизировать выполнение определенных операций. Создаваемый список в рабочем листе с названием Товары должен состоять из трех полей: Номер, Наименование товара и Цена. Введите указанные названия полей в ячейки А1-С1 и сразу же присвойте имена ячейкам столбцов A, В, и С — Номер, Товар и Цена соответственно. Создание списка товаров  2.2. Введите данные. При этом желательно, чтобы номера были расположены в порядке возрастания. В противном случае после ввода данных отсортируйте список по номеру товара. Измените ширину столбцов и отформатируйте список 3. Создание списка заказов Итак, начнем с определения основных функций создаваемого списка. Список должен помочь в управлении данными обо всех выполненых с : счала текущего года заказах. При этом мы попытаемся максимально автоматизировать процедуру ввода данных. Впоследствии представленные в списке данные будут подвергнуты анализу с помощью мастера сводных таблиц. 3.1. Создайте структуру списка. Для этого в ячейках A1-L1 укажете следующие названия полей: Месяц, Дата, Номер заказа. Номер товара. Наименование товара. Количество, Цена за ед., Код заказчика. Название фирмы. Сумма заказа. Скидка, Уплачено. Пример заполненного списка
впоследствии Вы можете вводить данные о заказах как с помощью формы данных, так и непосредственно в самом рабочем листе. В ячейках поля Месяц мы будем указывать названия месяца. Столбец В предполагается использовать для ввода даты выполнения заказов. С вводом дат повремените, а пока выделите столбец В с помощью команды Ячейки меню Формат, в открывшемся на экране одноименном диалоговом окне активизируйте раздел Число и выберите в категории Числовые форматы/Дата желаемый формат даты. Третий столбец должен содержать номер заказа. Продумайте нумерацию заказов, учитывая необходимость последующей сортировки заказов по номеру заказа. Затем перейдите в ячейку D2. Здесь пока не следует выполнять никаких действий. Во всех ячейках четвертого столбца должен быть указан номер заказываемого товара в соответствии с нумерацией, используемой в рабочем листе Товары. 3.5. Теперь перейдем к заданию формул. В столбце Е должно быть представлено наименование товара, при этом он вставляется автоматически с помощью формулы. Для этого укажите в ячейке Е2 формулу: =ЕСЛИ ($D2= "", "", ПРОСМОТР (SD2; Номер; Товар) Данная формула требует небольших пояснений. Функция ЕСЛИ проверяет содержимое ячейки D2. Если в ячейке D2 данные отсутствуют, то ячейка Е2 также останется незаполненной. Если же в ячейке D2 уже введен номер товара, то будет выполняться поиск номера товара в диапазоне Номер и в ячейку D2 возвратится соответствующее значение диапазона Товар. Для этого мы и используем функцию ПРОСМОТР. Вставьте приведенную формулу в ячейку Е2 с помощью мастера функций, если Вы не уверены в том, что аргумент в ячейке указан верно. Обратите внимание, что для ячейки D2 задана комбинированная ссылка, при копировании формулы это приведет только к изменению номера строки. В ячейки столбца F следует ввести заказываемое клиентом количество того или иного товара. 3.6 В столбце G (поле Цена за ед.) укажите цену единицы товара). Поскольку цена у нас уже встречалась, то ее вставку можно задать с помощью формулы, аналогичной вставленной в ячейку Е2. Формула в ячейке F2 должна иметь вид: =ЕСЛИ($D2=""; ""; ПPOCMOTP($D2; Номер; Цена) Вполне целесообразно скопировать формулу из ячейки Е2 в ячейку G2 и затем только изменить имя диапазона. Значение "пробел", представленное в ячейке в качестве результата применения формулы, убедит Вас в ее правильности.
=ЕСЛИ($Н2=""; ""; ПРОСМОТР (SH2; Код; Фирма) 3.9. В поле Сумма заказа укажите общую стоимость заказа без учета скидок Для этого следует перемножить значения в полях Количество и Цена. Можно также с помощью логической функции ЕСЛИ задать незаполнение ячеек в том случае, когда запись не введена, что позволит избежать появления значений ошибки. Поэтому формула в ячейке J2 должна иметь вид: ЕСЛИ(F2="";""; F2*G2) Все довольно просто и понятно. Если в поле F2 указано количество единиц заказываемого товара, то в ячейке J2 должно отображаться произведение ячеек F2 и G2. В противном случае ячейка должна остаться незаполненной. 3.8. Величину скидки (поле Скидка) также можно определять автоматически. Для этого достаточно ввести в ячейку К2 формулу: ЕСЛИ ($H2="";""; ПРОСМОТР ($112; Код; Скидка) 3.9 Определим сумму, подлежащую оплате. Для этого укажите в ячейке L2 следующую формулу: ЕСЛИ(J2="";"";J2-J2*K2) 3.10. Задайте некоторые параметры форматирования — центрирование значений, присвойте соответствующие стили (денежный и процентный) ячейкам нужных столбцов, а также закрасьте ячейки, в которых должны быть введены значения, голубым цветом, а ячейки, значения в которых будут определены на основе формул, — желтым цветом. 3.11.Выделите ячейки B2-L2 и выберите в меню Правка команду Заполнить/Вниз. Тем самым Вы зададите копирование значений ячеек сроки 2 в остальные ячейки. 3.12. Теперь все готово к вводу записей. Введите 10 записей.После ввода х записей для первых трех месяцев (дней, недель, лет) может возникнуть необходимость подвергнуть данные списка фильтрации, чтобы легко можно было определить, насколько популярным был тот или иной товар, какой клиент обращался в течение этих месяцев чаше всего или выделить заказы, сумма которых превышает определенное значение. Используйте для этих целей функцию автоматической фильтрации. После вставки автофильтра выбор нужных записей не должен составить труда. 4.1. Создание Бланка заказа Поскольку безбумажный офис все еще является для многих несбыточной мечтой, а большинство людей больше доверяют бумажным, нежели электронным документам, вполне вероятно, что Вы захотите распечатать данные каждого конкретного заказа и вложить их в отдельную папку клиента. Для этого следует предусмотреть возможность печати бланка заказа, который к тому же можно заполнять автоматически. Конечно же бланк заказа должен отражать характер деятельности Вашей фирмы. 4.1. Подготовьте свой вариант бланка, начав с выбора шрифта. Для этого с помощью кнопки в верхнем левом углу листа, находящейся на пересечении заголовков строк и столбцов - ... панели инструментов Форматирование выберите новый вид шрифта. 4.2. Теперь можно приступить к созданию самого бланка. Обратите внимание, что все четные строки листа не заполняются. Поместите указатель ячейки на ячейку D3 и введите Заказ N. Номер заказа следует указать в ячейке ЕЗ, при желании его можно подчеркнуть. Для этого в списке Липни рамки установите обрамление ячейки рамкой снизу. Не забывайте во время работы при необходимости изменять ширину столбцов. В ячейке F3 введите от и уменьшите ширину столбца. В ячейке G3 будет представлена дата заказа, которую мы вставим с помощью формулы: =ЕСЛИ($Е$3="";""; ПРОСМОТР(SES3; Заказ; Дата) Вид этой формулы аналогичен, использовавшимся ранее. Подчеркните вставляемое с помощью формулы значение, проведя нижнюю линию обрамления. В дальнейшем при проектировании бланка все области, предназначенные для ввод переменного текста, следует подчеркивать, используя список Линии рамки панели инструментов Форматирование. Значения в строке 3 должны иметь полужирное начертание и шрифт размером в 14 пунктов. 4.2. Перейдите к оформлению второй строки бланка. В ячейку С5 введите текст Название фирмы-заказчика. При этом старайтесь ввести текст таким образом чтобы он заполнил ячейки С5и D5. Для названия фирмы мы отвели ячейки Е5, F5. G5. Чтобы при заполнении заказа название фирмы вставлялось автоматически поместите в ячейку Е5 формулу: =ЕСЛП($Е$3="";""; ПРОСМОТР($Е$3; Заказ; Фирма2) Если Вы помните, для удобства и упрощения работы диапазонам ячеек базы данных Заказы были присвоены имена. Подчеркните название фирмы и расположите его по центру диапазона из трех ячеек. Для этого выделите ячейки F5, G5 нажмите в панели инструментов Форматирование кнопку Центрирования по столбцам. В ячейку Н5 введите слово Коб, а в ячейку I5 поместите формулу: =ЕСЛИ($Е$3="",""; ПPOCMOTP($E$3; Заказ; Код2) 4.3. Теперь займитесь оформлением третьей строки бланка. В ячейку С7 введите текст Наименование товара, а для ячеек Е7, F7 и G7 примените подчеркивание и центрирование. Ячейка Е7 должна содержать формулу: =ЕСЛИ ($E$3 =""; "", ПРОСМОТР (SE$3; Заказ; Товар2) В ячейку Н7 введите N (символ номера), а в ячейке i7 укажите формулу: =ЕСЛИ($Е$3=" "; " "; ПРОСМОТР (SES3; Заказ; Номер2) и примените подчеркивание для помещаемого в ячейку I7 значения. 4.4. Четвертая строка бланка должна содержать сведения о количестве и цене заказываемого товара. В ячейку С9 введите текст: Заказываемое количество. Для самого значения следует зарезервировать ячейку Е9. Значение будет вставляться автоматически, если в ячейку Е9 ввести формулу: = ЕСЛИ ($Е$3=""; ""; ПРОСМОТР (SES3; Заказ; Количество) Так же как и для всех полей бланка, предназначенных для ввода переменной информации, проведите под этой ячейкой нижнюю линию обрамления. В ячейку F9 введите с клавиатуры: ед. по цене, выровняйте введенный текст по центру столбцов F и G. Ячейка Н9 должна содержать формулу: =ЕСЛИ ($Е$3"";""; ПРОСМОТР (SES3; Заказ; Цена2) К этой ячейке следует применить подчеркивание и денежный стиль. В заключение над четвертой строкой бланка в ячейку I9 поместите текст за ед. 4.5. В ячейку СП введите текст: Общая стоимость заказа, а в ячейку E4l поместите формулу: =ЕСЛИ ($Е$3=" "; " "; ПРОСМОТР($Е$3; Заказ; Сумма) и задайте для ячейки параметры форматирования: нижняя линия обрамления и денежный стиль. В ячейку F11 введите: Скидка (%), выделите ячейки Fl1, G11 и H11 и выполните щелчок на кнопке Центрировать по столбцам. В ячейку I11 поместите формулу: =ЕСЛИ($Е$3=" ";"";"; ПРОСМОТР (SES3; Заказ; Скидка2) и задайтe для ячейки параметры форматирования: обрамление рамкой снизу и процентный стиль. 4.6. Теперь приступим к работе над последней строкой бланка. Введите в ячейку CJ3 текст: К оплате, а в ячейку D13 поместите формулу: = ЕСЛИ ($Е$3=" ": " "; "; ПРОСМОТР (SES3; Заказ; Оплата) и вновь задайте для ячейки следующие параметры форматирования: обрамление рамкой снизу и денежный стиль. В заключение укажите фамилию лица. оформлявшего заказ. В ячейке Е13 введите: Оформил, выделите ячейки Е13, F13 задайте центрирование текста по столбцам. Затем выделите ячейки G13, Н13 и I13. задайте для них центрирование текста по столбцам и обрамление рамкой снизу. 4.7. Закончите форматирование, улучшив внешний вид документа и обеспечив его однородное оформление. Установите ширину столбцов В и J, равную 1,75, выделите диапазон ячеек B2-J14 и задайте обрамление всего диапазона (выберите последний вариант в "палитре" рамок панели инструментов Форматирование). 4.8. Проверьте процедуру автоматического заполнения бланка. Поместите в ячейке ЕЗ номер заказа — и Вам останется только ввести свою фамилию перед печатью бланка. Если программа не совсем корректно справилась с поставленной задачей, проверьте, отсортированы ли номера заказов в списке в рабочем листе Заказы в возрастающей последовательности или измените ширину столбцов (при возникновении значения ошибки). Пример бланка заказа показан на рис.6.5.  5. Анализ данных с помощью сводной таблицы Итак, наступило время поговорить о сводных таблицах. Тем более, что момент очень подходящий: наш список заказов без анализа представленных в нем данных выглядит не совсем полным. А поможет нам проанализировать данные списка мастер сводных таблиц. Оценить его пользу Вы сможете, продолжив работу над примером. Скажем только, что главное отличие сводных таблиц от таблиц обычных заключается в их интерактивности. Сводные таблицы создаются из отдельного списка или базы данных.
Окно мастера сводных таблиц — выбор источника 
Диалоговое окно создания сводной таблицы  в окне мастера сводных таблиц. На экране появится диалоговое окно Вычисление поля сводной таблицы, в котором следует выбрать желаемую операцию. В этом же окне Вы можете задать параметры форматирования ячеек (для этого следует нажать кнопку Формат), а также выполнение дополнительных операций обработки данных в текущем поле (нажав кнопку Дополнительно и указав нужные параметры). С помощью двойного щелчка на имени поля в диалоговом окне мастера таблиц можно изменить параметры для любого поля. В частности, после двойного щелчка на поле Количество выберите в появившемся диалоговом окне переключатель Нет, чтобы в таблице не отображались промежуточные итоги. Обращайте внимание на то, что при задании операций обработки для полей данных в области строки или столбца сводная таблица может получиться слишком громоздкой. Если структура таблицы Вас устраивает, нажмите кнопку Далее и перейдите в последнее окно мастера сводных таблиц. Изображение флага в этом окне информирует о том, что близок конец работы над созданием сводной таблицы. Выберите ячейку рабочего листа Таблица, с которой должна начинаться сводная таблица, присвойте таблице соответствующее имя и подтвердите установленные здесь остальные параметры нажатием кнопки Готово. Через несколько минут сводная таблица появится в рабочем листе (см. рис. 6.8). 5.5. В область страниц поместите поле данных Месяц, а в область строк — поля Наименование товара. Количество и Сумма заказа. В результате в строках после наименования товара будут отображены количество проданных единиц и общая сумма заказа на данный товар в указанном месяце. Теперь следует определить, по какому полю подсчитывать сумму. Поместите в область данных поле Сумма заказа, после этого в области данных появится кнопка Сумма по полю Сумма заказа. Если Вы захотите задать выполнение другой операции обработки данных в текущем поле, следует вьполнить двойной щелчок мышью на имени поля  Вместе с созданием сводной таблицы на экране появилась панель инструментов Запрос и сводная таблица. Нажав первую кнопку панели инструментов, можно быстро "переместиться" в третье диалоговое окно мастера сводных таблиц и при необходимости, изменить структуру таблицы. Хотя если Вы хотите оставаться в пределах полей, уже используемых в сводной таблице, то изменить структуру таблицы с помощью перетаскивания полей можно и непосредственно в рабочем листе. Последняя кнопка служит для обновления сводной таблицы после изменения данных в исходном диапазоне. Если Вы хотите изменить параметры для поля данных, то после помещения указателя ячейки на нужном поле нажмите вторую кнопку панели Запрос и сводная таблица или выберите команду Поле сводной таблицы меню Данные. 5.6. Выбирая из списка Месяц (который выступает в качестве фильтра) нужное значение, можно отобразить данные о продажах для каждого отдельно месяца. В качестве фильтра можно использовать и несколько полей, для чего следует лишь поместить нужное поле в область страницы. Переместите по Название в область страницы непосредственно под полем Месяц, таким образом можно использовать значения двух этих полей в качестве, фильтра показа, что иллюстрирует рисунок 6.9. Использование нескольких фильтров показа  5.6. Комбинирование полей, задание дополнительных операций обработки данных — все это и многое другое делает сводные таблицы, безусловно незаменимыми помощниками при анализе данных списка. Просмотрите таблицу определите, какой товар принес самый большой оборот. Поле Наименование товара вновь поместите в область строк, а из списка поля Месяц выберите значения Все. Определите какой из товаров является лидером продаж Согласитесь, что сводные таблицы предоставляют большие возможности для творчества. Поэтому подерзайте немного, представляя данные списка в самых неожиданных ракурсах. |
Похожие:
 | Если товар отправляется в Россию по поручению контрактодержателя третьей фирмой, то указывается название этой фирмы и делается приписка:... | | Волгоградская обл.,р п. Светлый яр, ул. Коммунистическая, 45/ 17 (индекс, область, город, улица,) |
 | | ... | |
| Индекс, страна, город, улица дом, квартира/ zip code, country, city, street, building, apartment | | Оао (далее по тексту искового заявления Ответчик, Общество) является акционерным обществом, созданным в результате приватизации,... |
| Указывается индекс, город, улица, дом, корпус (при наличии), квартира (при наличии) | | Овд района «Южное Дорогомилово» гор. Москвы, код подразделения 700-005, временно проживающая в Бельгии, зарегистрированная по месту... |
| Российской Федерации 6515 903687, выдан оуфмс россии по свердловской области в орджоникидзевской районе города екатеринбурга, код... | | ... |