иксель. Информатика (1). Пример xls, лист Заказы
 Скачать 3.11 Mb.
|
|
Создание отчетов при помощи сводных таблиц (работаем с файлом пример.xls, лист Заказы): Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в Excel заполняется примерно такая таблица  В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии): кто из наших менеджеров заключил сделку с каким из заказчиков какого именно товара и на какую сумму продано с какого из наших складов была отгрузка когда (месяц и день месяца) Для получения ответов на подобные вопросы используется такой инструмент Excel как сводные таблицы. Ставим активную ячейку в таблицу с данными (в любое место списка) и переходим на вкладку Вставка - Сводная таблица. Откроется диалоговое окно «Создание сводной таблицы.  Необходимо выбрать, откуда будут взяты данные для сводной таблицы. В нашем случае выделяется диапазон с данными о продажах. Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel "понимает" практически все существующие типы баз данных, поэтому с совместимостью больших проблем, скорее всего не будет. Работа с макетом То, что Вы увидите, нажав кнопку Готово называется макет (layout) сводной таблицы:  Работать с ним несложно - надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы в области строк, столбцов, страниц и данных макета.  В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет. Останется его только достойно отформатировать:  Единственный недостаток сводных таблиц - отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить. Группировка данных в сводных таблицах После создания сводной таблицы иногда бывает удобно и полезно сгруппировать данные в интересующем нас диапазоне и с нужным шагом. При группировке возможны несколько вариантов. Одна строка в нашей базе данных - одна продажа, т.е. полностью описывает одну сделку (что, когда и по какой цене). Пример 1. Группировка чисел Необходимо построить отчет, из которого было бы понятно - на каких именно товарах (дешевых или дорогих) мы делаем основной оборот. Для начала строим обычную сводную таблицу, поместив в область строк поле Цена, в область данных – Продано:  Теперь щелкаем правой кнопкой мыши по цене (область строк) и выбираем из контекстного меню команду Группировать:  В итоге появится наш основной инструмент - диалоговое окно группировки данных в сводной таблице:  После нажатия на ОК Excel сгруппирует товары с ценами в заданном интервале и посчитает суммарную выручку по каждому интервалу:  Пример 2. Группировка дат Необходимо подсчитать суммарную выручку за каждый месяц (квартал) 2006 года, используя данные из нашей исходной таблицы. Строим простую сводную, поместив Дату в область строк, а Продано в область данных:  Даты в сводной таблице получаются по умолчанию с тем же шагом, что и был в исходной, т.е. с шагом в один день. Щелкаем по любой дате правой кнопкой мыши и выбираем команду Группировать. В появившемся окне группировки задаем следующие параметры:  После нажатия на ОК Excel сгруппирует даты с шагом месяцы и кварталы и мы получим вот такой красивый отчет:  Пример 3. Произвольная группировка На самом деле группировать в сводных таблицах можно практически все, а не только числа или текст, как это может показаться после двух предыдущих примеров. Группировка может быть совершенно произвольной и производиться даже с текстовыми данными. Если выделить в сводной таблице несколько значений в области строк или столбцов (несмежные данные выделяем щелчком левой кнопкой мыши при нажатой кнопке CTRL) и щелкнуть по ним правой кнопкой мыши, то можно воспользоваться командой группировки так же, как и в предыдущих примерах:   Созданной группе можно легко присвоить любое имя, просто введя его с клавиатуры в строку формул:  Для добавления итогов для каждой группы можно щелчком правой кнопкой мыши по любой ячейке первого столбца таблицы вызвать контекстное меню и выбрать команду Промежуточный итог «Наименование2»:  Настройка вычислений в сводных таблицах Допустим, у нас есть построенная сводная таблица с результатами анализа продаж по месяцам для разных заказчиков:  Нам хочется слегка изменить ее внешний вид, чтобы она отображала нужные вам данные более наглядно, а не просто вываливала кучу чисел на экран. Что для этого можно сделать? Другие функции расчета вместо суммы Если щелкнуть правой кнопкой мыши по расчетному полю в области данных и выбрать из контекстного меню команду Параметры полей значений, то откроется очень полезное окно, используя которое можно задать кучу интересных настроек:  В частности, можно легко изменить функцию расчета поля на среднее, минимум, максимум и т.д. Например, если поменять в нашей сводной таблице сумму на количество, то мы увидим не суммарную выручку, а количество сделок по каждому заказчику:  По умолчанию, для числовых данных Excel всегда автоматически выбирает суммирование, а для нечисловых (даже если из тысячи ячеек с числами попадется хотя бы одна пустая или с текстом или с числом в текстовом формате) – функцию подсчета количества значений (Count). Если же захочется увидеть в одной сводной таблице сразу и среднее, и сумму, и количество, т.е. несколько функций расчета для одного и того же поля, то смело забрасывайте мышкой в область данных нужное вам поле несколько раз подряд, чтобы получилось что-то похожее:  …а потом задавайте разные функции для каждого из полей, щелкая по очереди по ним мышью и выбирая команду Параметры полей значений, чтобы в итоге получить желаемое:  Долевые проценты Если в этом же окне Параметры полей значений перейти на вкладку Дополнительные вычисления , то окно развернется, и станет доступен выпадающий список Дополнительные вычисления .  В этом списке, например, можно выбрать варианты Доля от суммы по строке, Доля от суммы по столбцу или Доля от общей суммы, чтобы автоматически подсчитать проценты для каждого товара или города. Вот так, например, будет выглядеть наша сводная таблица с включенной функцией Доля от суммы по столбцу:  В версии Excel 2010 к этому набору добавились несколько новых функций: % от суммы по родительской строке (столбцу) - позволяет посчитать долю относительно промежуточного итога по строке или столбцу:  В прошлых версиях можно было вычислять долю только относительно общего итога. % от суммы нарастающим итогом - работает аналогично функции суммирования нарастающим итогом, но отображает результат в виде доли, т.е. в процентах. Удобно считать, например, процент выполнения плана или исполнения бюджета: Сортировка от минимального к максимальному и наоборот - немного странное название для функции ранжирования (РАНГ), вычисляющей порядковый номер (позицию) элемента в общем списке значений. Например, с ее помощью удобно ранжировать менеджеров по их суммарной выручке, определяя кто на каком месте в общем зачете:  Динамика продаж Если в выпадающем списке Дополнительные вычисления выбрать вариант Отличие, а в нижних окнах Поле и Элемент выбрать Месяц и Назад:  ...то получим сводную таблицу, в которой показаны отличия продаж каждого следующего месяца от предыдущего, т.е. – динамика продаж:  А если заменить Отличие на Приведенное отличие и добавить условное форматирование для выделения отрицательных значений красным цветом - то получим то же самое, но не в рублях, а в процентах:  Сезонность продаж (работаем с файлом пример.xls, лист Данные продаж) Во многих сферах бизнеса, связанных с продажами товаров или услуг, приходится учитывать сезонность. Даже если вы не продаете санки и коньки, то предновогодний бум продаж или летнее затишье поры отпусков - факторы, которыми нельзя пренебрегать. Как же высчитать коэффициенты сезонности, чтобы прогнозировать продажи для каждого месяца года? Естественно, нам понадобятся данные о продажах за один или несколько прошлых лет, например, вот в таком виде:  При помощи сводной таблицы можно легко и красиво высчитать нужные коэффициенты сезонности. Для построения сводной таблицы установите активную ячейку в список с данными и перейдите на вкладку Вставка - Сводная таблица. В открывшемся окне можно сразу нажать кнопку Готово, чтобы не терять время, и перейти к конструированию сводной таблицы с помощью макета:  Перетащите поле Дата в область строк, а поле Стоимость продажи в область элементов данных. Excel просуммирует все стоимости продаж по дням:  Теперь необходимо отобрать данные только за один последний год и сгруппировать их по месяцам. Для этого щелкаем правой кнопкой мыши по серому полю Дата в сводной таблице и выбираем из контекстного меню Группировать: В открывшемся окне вводим начальную и конечные даты для отбора и шаг группировки:  Теперь в сводной таблице будут вычисляться суммы продаж за каждый месяц года:  Превратим долларовые помесячные суммы в долевые коэффициенты. Для этого щелкнем правой кнопкой мыши по любому значению в столбце Итог и выберем команду Параметры поля.  В открывшемся окне нажмем кнопку Дополнительные вычисления и выберем из выпадающего списка Дополнительные вычисления вариант % от общей суммы:  Вот вам и коэффициенты сезонности для каждого месяца:  Для полноты ощущений можно еще щелкнуть правой кнопкой мыши по сводной таблице и построить сводную диаграмму:  |