Методические указания по лабораторным работам По дисциплине (стр. 8 из 8)
В ячейки столбца 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. Как сводная таблица помогает в анализе данных? |
| Лабораторная работа № 8 |
| Анализ деятельности филиалов фирмы на основе консолидации данных и сводных таблиц |
| Любое предприятие со временем расширяется и распространяет свою деятельность на другие "города и веси". Если с умом поставить управление филиалами и наладить определенный контроль, то дело может стать очень полезным и прибыльным. Это должно подвести Вас к мысли о необходимости разумного "воспитания" и управления детьми – дочерними фирмами. Как осуществлять контроль за их деятельностью, какую помощь при этом может предложить Excel – этому вопросу посвящена данная лабораторная работа. Ранее Вы уже имели возможность частично соприкоснуться с предметом нашего разговора – сводными таблицами. Второй темой, рассматриваемой в этой работе, является консолидация данных. Именно с помощью этих двух механизмов можно свести данные воедино, проанализировать деятельность филиалов, определить скользкие места и на основе полученной информации предложить пути их устранения. Изложение в данной работе будет построено на основе лабораторной работы № 7. |
| Задание для подготовки к лабораторной работе |
| Ознакомьтесь с возможностями Excel по построению консолидированных таблиц и сводных таблиц на основе консолидированных данных. |
| Условие задачи |
| В нем мы пытались помочь Вам наладить управление заказами. В вашей фирме количество клиентов из других городов резко возросло, и количество заказов в этих городах сделало целесообразным открытие филиалов. Было принято решение о расширении деятельности фирмы. В этой работе необходимо создать таблицы с данными о работе филиалов фирмы. Для анализа работы всей компании необходимо создать консолидированную, а на ее основе сводную таблицы. Применение консолидации всегда имеет смысл при подведении итогов по нескольким структурным подразделениям, данные в которых хранятся в одинаковых формах. |
| Порядок выполнения лабораторной работы |
| 1. Ввод данных |
| 1.1. Введите данные в таблицу для каждого филиала, которые будут представлены в отдельных рабочих листах. Для этого скопируйте в буфер обмена из листа Товары (лабораторная работа № 7) данные о распространяемых продуктах, их номерах и ценах на них. 1.2. Перейдите в первый рабочий лист новой рабочей книги. Рабочим листам в новой книге целесообразно присвоить названия городов, в которых размещены филиалы. Присвойте, например, нашему первому рабочему листу имя Киев. 1.3. Введите название филиала в ячейку А1 и сформатируйте ее соответствующим образом. 1.4. Переведите указатель ячейки на ячейку A3 и вставьте скопированный фрагмент таблицы. 1.5. В строке 3 укажите также другие метки столбцов, а именно: в ячейках D3, ЕЗ, F3 введите соответственно Количество заказов, Проданное количество и Объем продаж. Задайте также центрирование текста в ячейках и разрешите перенос текста по строкам. 1.6. Поместите в ячейку F4 формулу =С4 *Е4 и скопируйте ее в ячейки F5-F14. Тем самым создание структуры таблицы будет завершено. 1.7. Перед вводом данных выделите всю таблицу и скопируйте ее в остальные рабочие листы, которые будут содержать информацию по другим филиалам. Можете сразу же отформатировать ячейки в листах для других филиалов, присвоить листам рабочей книги названия городов и изменить заголовки таблиц в ячейках A1 в каждом листе. 1.8. Введите данные для каждого филиала. Представьте данные для пяти филиалов, расположенных в разных городах. 1.9. В завершение нужно подбить итог по объему продаж каждого филиала, для этого введите в ячейку В15 слово Всего, а в ячейку F15 вставьте формулу суммы. |
| 2. Консолидация данных |
| 2.1. Перейдите в шестой лист рабочей книги и поместите указатель ячейки на крайнюю левую ячейку таблицы, которая будет содержать консолидированные данные – ячейку A3. Поскольку номер и название товара у нас остаются неизменными и, естественно, не должны быть подвергнуты операции сложения, то можно скопировать эту часть таблицы из рабочего листа, например Киев. Для этого выделите в рабочем листе Киев диапазон ячеек АЗ-В14, выберите команду Копировать меню Правка, перейдите в шестой рабочий лист (присвойте ему имя, например Итог), поместите указатель ячейки на ячейке A3 и выберите команду Вставка меню Правка. Скопированный фрагмент таблицы будет вставлен в новом месте. При желании можете сразу же адаптировать параметры ячеек к потребностям вставленных текстовых данных. 2.2. Приступим непосредственно к операции консолидации данных. 2.2.1. Поместите указатель ячейки на ячейку СЗ и выберите в меню Данные команду Консолидация. Через несколько мгновений на экране появится одноименное диалоговое окно. 2.2.2. В списке Функция выберите операцию, которая будет выполняться над консолидируемыми данными. Выберите операцию сложения (элемент Сумма). 2.2.3. Перейдите на поле ввода Ссылка. В нем следует указать диапазоны ячеек, данные из которых должны быть подвергнуты процессу консолидации. Диапазон ячеек проще всего можно указать, используя манипулятор. Поместите курсор ввода в поле Ссылка, выполните щелчок мышью на ярлычке листа Киев, выделите диапазон ячеек D3-F14 и нажмите в диалоговом окне Консолидация кнопку Добавить. Указанный диапазон тотчас же будет представлен в поле Список диапазонов. Нажатием кнопки Удалить ненужный диапазон может быть из этой области удален (перед нажатием кнопки ненужный диапазон следует выделить в поле Список диапазонов). 2.2.4. После включения в область консолидации первого диапазона ячеек можно приступить к выполнению аналогичной операции над другими диапазонами. С помощью щелчка мышью на ярлычке перейдите в рабочий лист с данными для следующего филиала. Excel автоматически выделит тот же диапазон ячеек, что и в предыдущем листе. Нажмите кнопку Добавить и аналогичным образом укажите в поле Список диапазонов диапазоны других рабочих листов файла, данные из которых должны быть консолидированы. 2.2.5. Установите необходимые опции, находящиеся в диалоговом окне Консолидация. Если верхняя строка и/или левый столбец содержат заголовки столбцов или строк, которые необходимо скопировать в итоговую таблицу, следует включить соответствующие опции в группе Использовать метки. Поскольку в нашем примере верхняя строка содержит заголовки столбцов, то нужно активизировать опцию В верхней строке. 2.2.6. Так как между данными консолидированной таблицы и исходными данными должна быть установлена динамическая связь, поэтому необходимо включить опцию Создать связи с исходными данными. Активизация этой опции представляется довольно целесообразной, поскольку при изменении данных в исходном диапазоне будут изменяться значения и в итоговой таблице, что позволяет всегда владеть текущей ситуацией. 2.2.7. Кнопку Обзор следует использовать для выбора файла, который содержит консолидируемые данные. Для начала выполнения операции нажмите кнопку ОК. Через некоторое время на экране появится итоговая таблица с консолидированными данными (рис. 8.1). Рис. 8.1. Консолидированная таблица 2.3. В ячейку А1 введите название новой таблицы, например Итоговые данные. В ней Вы имеете возможность проследить, какой товар больше всего поспособствовал росту объема продаж во всех вместе взятых филиалах. 2.4. Определим сумму по всем филиалам. Для этого укажите в ячейке В70 значение Всего, а в ячейке Е70 поместите формулу вычисления суммы. Excel сам выделит нужный диапазон и после нажатия клавиши Enter выдаст сумму значений выделенного диапазона. 2.5. Для более наглядного представления данных в столбце F уместно было бы привести значения долей отдельных продуктов в общем объеме продаж. В ячейку F9 введите формулу =Е9/$Е$70 и скопируйте ее в остальные ячейки столбца F (вплоть до ячейки F70). Полученные результаты позволят сделать недвузначные выводы о популярности того или иного товара. 2.6. При определении долей Вы уже, наверное, обратили внимание на странные адреса ячеек. Все дело в том, что при консолидации данных программа записывает в итоговой таблице каждый элемент и автоматически создает структуру документа. Структурирование (создание структуры) документа позволяет добиться представления на экране только необходимой информации и скрыть ненужные детали. Слева от таблицы представлены так называемые символы структуры. Цифрами обозначены уровни структуры. Нажатие кнопки со знаком плюс позволит расшифровать данные высшего уровня структуры. Нажмите, например, кнопку для ячейки А9, чтобы получить информацию об отдельных заказах. В качестве альтернативы для выполнения этой операции можно воспользоваться командой Структура/Показать детали меню Данные. 2.7. Поскольку ячейки с данными отдельных заказов для первого товара у нас не были учтены при определении долей, то имеет смысл после расшифровки с помощью функции автоматического заполнения скопировать формулу из ячейки F9 в ячейки F4-F8. Скрыть детали можно посредством щелчка на кнопке с изображением знака минус или путем выбора команды Структура/Скрыть детали меню Данные. Эта команда предназначена для создания структуры из выделенных строк или столбцов. 2.8. Структурирование листа пользователь может задать и сам, предварительно разбив данные рабочего листа на группы. Для создания вертикальной структуры (которая имеется и в нашем примере) следует выделить несколько строк (речь может идти только о смежных выделениях), а затем активизировать команду Структура/Сгруппировать меню Данные. Целесообразно сгруппировать данные о продажах товаров одного типа, например: компьютеров, принтеров и мониторов. Путем выделения столбцов можно создать горизонтальную структуру. Разгруппировываются данные с помощью выбора одноименной команды в меню Данные после выделения строк (столбцов), которые подлежат разгруппированию. Самым простым способом создания структуры является выбор команды Структура/Автоструктура меню Данные. В этом случае программа сама создаст структуру таблицы. На отдельном листе создайте горизонтальную структуру, объединив группы товаров по типу, например: техника, продукты, галантерея и т.д. 2.9. С помощью команды Структура/Параметры меню Данные в диалоговом окне, представленном на экране, укажите необходимые итоговые ячейки под деталями (в случае вертикальной структуры) и справа от деталей (при горизонтальной структуре). Задайте автоматическое форматирование строк и столбцов с итогами с помощью встроенных стилей. |
| 3. Сводная таблица на основе диапазонов консолидации |
| В этой части лабораторной работы рассмотрим особенности создания сводной таблицы, данные для которой хранятся в нескольких диапазонах консолидации. 3.1. Для продолжения нашего анализа объема продаж по филиалам в седьмом рабочем листе (присвойте ему, например, имя Таблица) создадим сводную таблицу, источники данных для которой находятся в нескольких диапазонах консолидации. 3.2. Поместите указатель ячейки на левой верхней ячейке создаваемой таблицы и активизируйте команду Сводная таблица меню Данные. На экране незамедлительно покажется первое диалоговое окно мастера сводных таблиц, здесь при задании источника данных следует выбрать элемент В нескольких диапазонах консолидации. 3.3. После нажатия кнопки Далее на экране появится окно Мастер сводных таблиц шаг 2а из 4. В нем выберите элемент Создать поля страницы, что позволит присвоить имя диапазонам для каждого филиала. 3.4. После нажатия кнопки Далее перейдите во вторую часть второго диалогового окна мастера сводных таблиц. Перед указанием диапазонов консолидации активизируйте опцию 1, расположенную под полем Список диапазонов. Эта опция задает число полей страницы сводной таблицы. После выполнения этой операции становится доступным поле ввода Первое поле. 3.5. Затем переведите курсор ввода в поле Диапазон и с помощью мыши выделите диапазон ячеек B3-F14 в первом рабочем листе (лист Киев). Нажмите кнопку Добавить и аналогичным образом представьте в поле Список диапазонов адреса диапазонов с данными из других рабочих листов. 3.6. После указания всех диапазонов займитесь присвоением им имен. Для этого выделите нужный диапазон в поле Список диапазонов и введите желаемое имя в поле Первое поле. Используйте в качестве имен названия соответствующих городов. Для сохранения имени за диапазоном достаточно выбрать другой диапазон из поля Список диапазонов. Однако при присвоении имен следует быть довольно внимательным. После введения всех значений смело нажимайте кнопку Далее. 3.7. Третье диалоговое окно при создании сводной таблицы на основе данных нескольких диапазонов консолидации не играет такого большого значения, как в случае сводной таблицы на основе списка. Excel принимает за основу структуру диапазонов консолидации, поэтому вмешиваться в его деятельность здесь не имеет особого смысла. Внести в предлагаемый программой вариант только одно небольшое изменение, а именно: выполнить двойной щелчок на поле Столбец для открытия диалогового окна Вычисление поля сводной таблицы и с помощью щелчка на элементе Цена в поле Скрыть элементы подавить отображение на экране данных о цене. Согласитесь, что суммировать цены товаров вряд ли имеет смысл. 3.8. Нажмите кнопку Далее, чтобы добавить несколько последних штрихов в создаваемую Вами сводную таблицу. Эти штрихи заключаются в отключении опции Общие итоги по строкам в группе Параметры сводной таблицы и вводе (при желании) названия таблицы и нажатии кнопки Готово. 3.9. Подождите несколько мгновений и, если все выполнено правильно, на экране появится результат Вашего труда (рис. 8.2). Рис. 8.2. Сводная таблица на основе консолидации Сделайте анализ ситуации с филиалами. В данный момент, используя созданную таблицу, довольно легко оценить, какой вид товара принес наибольший оборот по всем филиалам, а также представить данные для каждого отдельного филиала. Выберите название филиала из списка в области страницы. В этом случае название филиала будет выступать в качестве фильтра. 3.10. Однако сводная таблица не была бы сводной таблицей, если бы на этом ее возможности исчерпывались. Главной характеристикой сводной таблицы является ее интерактивность. И именно это свойство можно сейчас проверить. Если Вы все еще находитесь в сводной таблице, переместите поле Строка в область страницы, а поле Страница 1 – в область строк. Проанализируйте, какой филиал достиг лучших результатов работы и (при выборе соответствующего наименования товара из списка в области страницы) какой товар в каком городе пользовался самым большим спросом. Изменяя структуру сводной таблицы, Вы будете иметь под рукой информацию, которая послужит хорошей основой для принятия решений. 3.11. Запишите все сделанные выводы в отчете по лабораторной работе. |
| Контрольные вопросы |
| 1. Что такое консолидация? 2. Как строится консолидированная таблица? 3. Как строится сводная таблица на основе консолидированной? 4. Какой анализ можно провести на основе консолидированной таблицы? 5. Какие выводы может сделать руководитель, используя консолидированную сводную таблицу? |
| Список литературы |
| Фигурнов В.Э. IBM PC для пользователя. – М.: Финансы и статистика, 1995. Пробитюк А. Excel 7.0 для Windows 95 в бюро. – Киев: Торгово-издательское бюро BHV, 1996. Клименко Б.И. Практические советы пользователю. – М.: Финансы и статистика, 1996. Николь Н., Альбрехт Р. Электронные таблицы Excel 5.0 для квалифицированного пользователя. – М.: ЭКОМ, 1996. |