CFA LogoCFA Logo Computer
Новости Статьи Магазин Драйвера Контакты
Новости
RSS канал новостей
В конце марта компания ASRock анонсировала фирменную линейку графических ускорителей Phantom Gaming. ...
Компания Huawei продолжает заниматься расширением фирменной линейки смартфонов Y Series. Очередное ...
Компания Antec в своем очередном пресс-релизе анонсировала поставки фирменной серии блоков питания ...
Компания Thermalright отчиталась о готовности нового высокопроизводительного процессорного кулера ...
Компания Biostar сообщает в официальном пресс-релизе о готовности флагманской материнской платы ...
Самое интересное
Программаторы 25 SPI FLASH Адаптеры Optibay HDD Caddy Драйвера nVidia GeForce Драйвера AMD Radeon HD Игры на DVD Сравнение видеокарт Сравнение процессоров

АРХИВ СТАТЕЙ ЖУРНАЛА «МОЙ КОМПЬЮТЕР» ЗА 2003 ГОД

Express-отчет

Надежда БАЛОВСЯК nadia123@yandex.ru

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

Допустим, наша компания занимается продажей компьютерной и оргтехники. Причем у нее есть филиалы в разных областях. Кроме того, филиалы могут иметь несколько торговых точек, в которых реализуется продукция фирмы. Имеется информация о динамике сбыта принтеров на протяжении 2002 года, а именно известно количество проданных единиц каждого наименования в соответствующем магазине филиала, а также выручка от реализации, полученная в результате сбыта. Эти данные размещены на одном рабочем листе, который назовем, к примеру, Сбыт. Список с исходными данными структурирован по следующим полям: Область, Модель, Дата, Магазин, Количествопроданных, Выручка от реализации (рис. 1). Для удобства последующей обработки имеющейся информации, исходные данные нужно представить в соответствии с правилами оформления списков в Excel (см. статью «Удобный прайс-лист», МК, №5 (228)).

Кроме этого, в рабочей книге существует информация о поставках каждой марки принтеров поквартально в областные филиалы, размещены эти данные на отдельных рабочих листах в соответствии с маркой устройства. То есть в рабочей книге есть листы Cannon-1 кв, Cannon-2 кв, HP-1 кв и т.д., на каждом из них находятся списки областных филиалов и объем поставки Рис. 1. Список с исходными даннымипринтеров за указанный период.

Для анализа деятельности компании необходимо решить перечисленные ниже задачи.

І. Определить значения следующих показателей:

количество проданных принтеров в каждой из областей;

среднюю величину выручки от реализации в каждом из областных филиалов.

II. Сравнить суммарные объемы поставок и сбыта для будущей корректировки сроков и объемов поставок.

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

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

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

Итоги… Итоги? Итоги!

Для решения задачи I используется возможности, предоставляемые Excel по вычислению промежуточных итогов. Можно определить итоговые значения (сумму, среднее значение) для записей списка с одинаковыми значениями определенного поля. Например, для вычисления количества проданных единиц моделей принтеров в каждом из областных филиалов необходимо вывести сумму поля Количество проданных для одинаковых значений поля Область.

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

Для сортировки списка выбираем Данные > Сортировка. В поле Сортировать по окна Cортировка диапазона указывается поле, по которому следует упорядочить список, рядом задается способ упорядочивания — по возрастанию либо по убыванию.

Для вычисления промежуточных итогов находим Данные > Итоги. В окне Промежуточные итоги заполняются следующие поля:

в поле При каждом изменении в указывается имя поля, для одинаковых значений которого будут проводиться вычисления;

в поле Операция указывается итоговая функция;

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

Для решения первой из поставленных задач заполним поля окна Промежуточные итоги следующим образом: При каждом изменении в — поле Область, Операция —Cумма, Добавить итоги по —Количество проданных.

После вычисления промежуточных итогов в исходной таблице появятся строки с суммарными значениями поля Количество проданных после каждой группы записей с одинаковыми значениями поля Область. В конце таблицы вы увидите строку с общим итогом по всем записям. Кроме этого, Рис. 2. Исходная таблицаисходная таблица отобразится в виде структуры, как показано на рисунке 2.

В левой части рабочего листа появятся кнопки, используя которые, можно управлять режимом отображения промежуточных итогов. Третий уровень (кнопка с цифрой 3) отвечает за отображение итоговых и промежуточных данных. Второй уровень воспроизведет на рабочем листе лишь промежуточные и общие итоги. А первый — оставит на рабочем листе лишь общий итог. Кроме управления отображением итоговых значений для всех групп данных, кнопки + и - дают возможность показать либо скрыть итоги для определенной группы значений.

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

Консолидировать — значит собрать вместе

Для сравнения суммарных объемов поставок и сбыта принтеров (задача ІІ) необходимо вначале получить эти цифры, так как в исходном списке их попросту нет.

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

Для консолидации выбираем Данные > Консолидация, в поле Функция определяем итоговую функцию, которая будет вычисляться для данных разных рабочих листов. В поле Ссылка указывается адрес консолидируемого диапазона, после выделения каждого диапазона нажимаем Добавить. После того, как работа по выделению диапазонов из всех необходимых рабочих листов завершена, в поле Использовать в качестве имен отметим переключатель Значения левого столбца, что будет означать консолидацию по именам. При этом значения в строках с одинаковыми именами просуммируются. В результате на рабочем листе Суммарная поставка получим таблицу, состоящую из двух столбцов. Первый содержит названия областных филиалов, второй — общее количество поставленных принтеров всех марок.

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

Сводить можно не только дебет с кредитом

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

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

Формирование сводной таблицы невозможно на основании списка с добавленными промежуточными итогами. Здесь нам и пригодится копия исходного списка. Для построения сводной таблицы используется мастер сводных таблиц, вызвать который можно, выбрав Данные > Сводная таблица. На первом шаге работы мастера задается источник данных, а также указывается, что именно необходимо создать —сводную таблицу или сводную диаграмму вместе со сводной таблицей. После вводится диапазон, содержащий исходные данные, на последнем шаге выбирается место расположения сводной таблицы.

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

Сводная таблица состоит из следующих областей:

область полей строк и столбцов — поля, которые служат в качестве заголовков строк и столбцов. Можно управлять данными, отображаемыми в этих областях, для чего используется раскрывающийся список возле названий полей строк и столбцов. Этот список позволяет либо оставить в таблице необходимые значения поля, либо отобразить все значения поля в сводной таблице;

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

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

Параметры сводной таблицы можно изменить, выбрав Сводная таблица > Параметры. По умолчанию в окне параметров задано отображение общей суммы по строкам и столбцам. Также есть возможность задать характеристики печати сводной таблицы — печати заголовков, Рис. 3. Поле Количество проданныхповторение подписей на каждой странице и т.д.

Для удобства отображения исходных данных разместим их следующим образом:

в области полей страниц — поле Область исходного списка;

в области полей строк — поле Модель;

в области полей столбцов — поле Дата;

в области полей данных — поле Количество проданных (рис. 3).

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

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

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

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

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

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

Данные, размещенные в области полей строк или столбцов, можно группировать. Например, сгруппировав значения первых двух кварталов, создать группу «І полугодие» и соответственно «ІІ полугодие». После группировки данных у вас появится возможность либо отображать в таблице итоговые значения сразу по всей группе, либо задавать детализацию данных по элементам группы. Для группировки нужно выделить элементы, которые будут объединяться, и выбрать Сводные таблицы > Группа и структура —Группировать (рис. 5). Изменить название группы, присвоенное по умолчанию, можно, выделив соответствующую ячейку и введя новое имя в строке формул. Детализация и скрытие данных созданных групп напоминает работу с данными обыкновенных элементов области полей строк или столбцов.

Параметры обновления данных задаются в окне свойств сводной таблицы. Можно указать, чтобы обновление происходило каждый раз при открытии рабочей книги, в которой содержится сводная таблица (переключатель Обновить при открытии окна Параметры сводной таблицы). Если в процессе работы изменились исходные данные, то для обновления сводной таблицы необходимо воспользоваться кнопкой Обновить данные панели инструментов Сводные таблицы.

Рис. 4. Отображение детализации   Рис. 5. Группирование

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

И сводим, и считаем, и графически отображаем

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

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

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

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

Если наша задача — отследить динамику сбыта принтеров, вычислите разность между значениями области полей столбцов (данные за четвертый квартал минус данные за первый квартал). Для этого в сводной таблице Рис. 6. Работа с вычисляемым объектомиспользуется вычисляемый объект.

Вычисляемый объект поля создается в области полей строк, столбцов или страниц. Добавить его в таблицу можно, воспользовавшись Сводная таблица > Формулы > Вычисляемый объект (рис. 6). Когда вычисляемый объект добавлен, определять итоговые значения по строкам не имеет смысла, убрать их можно с помощью окна параметров таблицы.

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

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

Итак, все задачи решены, числа получены, наглядность обеспечена, начальство довольно. И возможно, теперь на решение подобных задач в дальнейшем у вас уйдет намного меньше времени :-).

Рекомендуем ещё прочитать:






Данную страницу никто не комментировал. Вы можете стать первым.

Ваше имя:
Ваша почта:

RSS
Комментарий:
Введите символы или вычислите пример: *
captcha
Обновить





Хостинг на серверах в Украине, США и Германии. © sector.biz.ua 2006-2015 design by Vadim Popov