Как дополнить данные отчета всеми данными в группировке по периоду

Дополнение периодов в системе компоновки данных

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

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

Данные будем получать при помощи следующего запроса:

Для отчета будем использовать следующие настройки:

Т.е. в отчет будем выдавать группировку по периоду и диаграмму группировкой по периоду в сериях.

Если мы будем получать отчет с группировкой по периоду без дополнения, то результат отчета будет выглядеть следующим образом:

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

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

Результат отчета с этой настройкой будет выглядеть следующим образом:

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

При необходимости, для поля группировки можно указать интервал, в котором нужно дополнять периоды. Для этого следует ввести даты в колонки «Начальная дата периода» и «Конечная дата периода» поля группировки. При этом дополнение будет происходить не только в интервале дат, полученных из набора данных, но с начальной даты до конечной даты.

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

Для примеров будем рассматривать вывод в отчет одной группировки по полю Период.

Результат отчета без дополнения будет выглядеть так:

Результат с дополнением по дням без указания интервала будет выглядеть так:

Т.е. дополнение произошло в интервале, дат, которые были получены из набора данных.

Если у поля группировки установить начальную и конечную дату периода следующим образом:

То дополнение по дням произойдет в указанном интервале и результат отчета будет выглядеть так:

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

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

Как видно в данном примере, дополнение произошло в интервале, указанном в параметрах данных.

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

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

Рассмотрим следующую настройку:

В отчет будут выдаваться периоды, сгруппированные по месяцам.

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

При дополнении в периоде 01.01.2002 — 31.03.2002 результат может выглядеть следующим образом:

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

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

Результат будет выглядеть так:

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

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

Для простоты понимая пример будем строить на одном простом оборотном регистре накопления.

В моем случае это регистр накопления «Незавершенное производство бухгалтерский учет».

Его параметры для примера укажем жестко (не через мягкое накладывание параметров на СКД):

Обратим внимание, периодичность виртуальной таблицы — «Запись».

Далее выбираем период из виртуальной таблицы и нужные нам поля.

Но, как было замечаено выше, период нам нужен в разрезе периодичности, поэтому поле «Период» я предлагаю вычислить следующим путем (несовсем красиво, но лучше вариантов я не видел):

Как видно из скриншота, в запрос передается параметр, который пользователь указывает на форме: Значение перечисления «Периодичность» — данное перечисление есть практически во всех типовых решениях.

Его доустпные типы укажем на вкладке «Параметры»:

Далее, в настройках СКД создаем таблицу, в ней — колонку «Период» (вертикальная группировка):

Далее следует важный момент настройки условного оформления:

Этой настройкой мы форматируем наш период, чтобы все было красиво и радовало глаз)

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

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

Покажу 2 варианта, но существуют, конечно же, и другие. Можно, к примеру, в процедуре

Я хотел сделать всё исключительно в СКД, и такой способ тоже есть, более того, он, вероятно, даже проще. Я покажу два очень похожих варианта, смысл в двух — просто показать некоторые возможности СКД, которые кто-то, может быть, не знает.

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

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

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

Естественно, можно настроить формат так, как хотите. Можно и не настраивать.

Осталось только добавить наше поле в структуру варианта и вынести параметр Периодичность в быстрые настройки, для удобства.

В общем-то, всё, почти динамическая группировка готова. Почему почти? Ну мы же должны заранее задать и описать необходимые периоды!

Этот вариант очень похож на первый, я тут просто покажу пару возможностей СКД. Тут мы, вместо одного поля Период, сделаем несколько полей. Месяц, Квартал, Полугодие, Год и т.д.

В запросе опишем эти поля вот таким образом

NULL обязателен, чтобы использовать одну из настроек СКД — «Игнорировать NULL». Если не хотите использовать NULL, то никто не мешает для каждой из 4 группировок создать свой собственный отбор на параметр Периодичность. Я это описывать не буду, думаю и так всё очевидно.

И создаем 4 группировки с этими полями.

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

Так что в СКД избавляться от NULL нужно с умом 🙂 иногда оно бывает полезно.

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

Специальные предложения

(1) Yashazz, можно и полями, собственно какая разница? 🙂

Самым правильным вариантом считаю составление текста запроса с учетом переданного параметра (задавать периодичность виртуальной таблицы) с последующей передачей внешнего набора данных (таблицы) в макет СКД.
Как-то так:

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

У меня только один вопрос. На каком основании Вы считаете, что этот метод «самый правильный»? Я вот так не считаю. К тому же далеко не всегда у нас виртуальная таблица оборотов используется в отчете.

(6) zqzq, да можно, но всё равно это не так удобно, однако как вариант почему нет. Я же написал — это один из вариантов. Мне удобнее делать так, кому-то удобнее иначе 🙂

(7) Не считаете — обоснуйте, почему. Правильный — значит быстродейственный. При его использовании нет необходимости вычисления периода для каждой строчки отчета.
К тому же далеко не всегда у нас виртуальная таблица оборотов используется в отчете.
Я стесняюсь спросить, ЧТО вы еще собираетесь в отчете с вертикальной группировкой по периоду? Регистр сведений? Может вообще дату документов? Опять же возвращаемся к разговору про быстродействие. Правильный — значит быстродейственный. При его использовании нет необходимости вычисления периода для каждой строчки отчета.

зато есть необходимость менять текст запроса, создавать ТЗ, загружать его в СКД и прочее. А посчитать case для строчки дело не сложное, к тому же в моем примере идет сравнение чисел, что в общем-то вряд ли сильно затруднит обработку.

Но я так же могу поспорить и с фразой «правильный — значит быстродейственный.» Мы пишем не на С++ и 1С сама по себе довольно медлительна и много где теряет производительность. Вообще код в 1С должен соблюдать баланс между скоростью работы и скоростью восприятия этого кода другим программистом. По моему сугубо личному мнению все эти обработки при компановке, передача ТЗ как внешний источник и прочие извращения это от криворукости, когда человек не может в СКД сделать нормальный запрос. И анализировать это разбирая процедуры, которые наваял автор бывает довольно проблемно. И если вы ради вычисления одного поля будете всё переносить в модуль, то. ну даже не знаю. Бывают ситуации, когда без этого не обойтись, но они бывают редко.

однако вариант с переносом расчета периода из запроса в вычисляемые поля он и правда с этой точки зрения лучше, тк всё воспринимается ещё легче.

Зачем? Это Вы должны обосновывать на каких таких основаниях вы считаете свой метод лучше и быстрее 🙂 я ничего не брался доказывать, просто рассказал более удобный способ.
Более того, я в начале сказал, что мы можем что-то сделать с СКД при компановке, но это совершенно не интересно.

Я только что специально протестировал этот механизм с использованием планировщика(только сам запрос), чтобы я ни делал выше 0% общая стоимость выполнения comptute scalar не превышала, там что-то в районе 0.000000014 общая стоимость. Поэтому истории про то, что тяжело рассчитать case для каждой строчки несколько надуманы.
Кстати что интересно в SQL запрос попадает не весь case а только верный вариант, не знаю с чем это связано пока.

Я стесняюсь спросить, ЧТО вы еще собираетесь в отчете с вертикальной группировкой по периоду? Регистр сведений? Может вообще дату документов? Опять же возвращаемся к разговору про быстродействие.
Стесняться не надо, в этом нет необходимости. Дело в том, что мой метод универсален и конкретно я его придумал когда делал разворот по датам, которые брались из документа(но не дата документа) довольно сложная аналитическая самописная конфа для финансового планирования и ради одного отчета делать регистр начальство не посчитало нужным. Но в целом да, это своего рода обороты. Но дело ведь не в этом, правда? 🙂 моя маленькая заметка находится в разделе «практика программирования» и она довольно универсальна, плюс раскрывает некоторые довольно интересные методы работы с СКД.
Я вообще не понимаю откуда этот спор. Я просто предложил ещё один метод(о чем и написал) как это сделать. На мой личный взгляд это самый красивый способ, хотя способов конечно много. Плюс, я уверен, он работает с не худшей производительностью, чем предложенный Вами способ. Мы пишем не на С++ и 1С сама по себе довольно медлительна и много где теряет производительность

А вам сказать, благодаря кому она такая медленная? или Сами догадаетесь? А тестировали вы на каких данных? Сколько строк в результирующем запросе? 10?
Программист пишет не так, чтобы это было легко сделать (очень близко к определению понятия «говнокод»), а так, чтобы это работа правильно и максимально быстро.
Да извольте анализировать процедуры, написанные автором. Все правильное реализуется непросто, а что реализуется просто — то поделка школьника. Возьмите к примеру запросы вычисления страховых взносов в ЗУПе — их тупо листать устанешь, не то что разбирать. А на первый взгляд, там сложного ничего нет — есть база и есть процент))

Разворот по датам, которые брались из документа(но не дата документа) довольно сложная аналитическая самописная конфа для финансового планирования и ради одного отчета делать регистр начальство не посчитало нужным

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

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

А теперь по делу. Сделал регистр сведений, добавил туда пару полей и одно из полей Дата, добавил в него 510к строк. Судя по плану запроса вычисление периода тратит 1% от общего времени(ориентировочно)

Да и, к слову, в 1С есть стандарты разработки, которые ставят качество восприятия кода на одну ступень с его быстродействием, за редчайшим исключением вроде процедур проведения, где каждая секунда важна.
И если по Вашему стоит из-за любого чиха обработку переносить в модуль, вместо СКД и считаете это правильным, то мне надо Вас огорчить. Сказать почему? Ну например СКД динамически формирует запрос и отборы в СКД автоматически переносятся в запрос тоже. Может случиться такая ситуация, что пользователю будет нужна одна строка, а вы создадите свою таблицу из миллиона записей в модуле и СКД всё равно отберет одну строку. поспорим о производительности? Или будете все допустимые отборы выносить на форму и обрабатывать в своем запросе? СКД автоматически работает с характеристиками. Но главная причина спора и производительность это тема тоже сложная. Я например абсолютно убежден, что сделать запрос на миллион строк и выгрузить его в ТЗ, а потом передать как внешний источник данных в СКД — нереально медленная операция, тут потери производительности на создание ТЗ просто громадны и они даже близко не сопоставимы с мизерными потерями на расчет периода в строке.
Да и просто анализировать единый запрос в СКД куда правильнее чем раскидывать код по модулям без причин.

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

(10), я надеюсь на этом мы закончим нашу беседу, мне она не очень интересна, извините уж 🙂

Ну например СКД динамически формирует запрос и отборы в СКД автоматически переносятся в запрос тоже.

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

(10) Lyns_owner, я тоже считаю, что ваш вариант с программным составлением явно не для СКД. Может вы вообще все за СКД сделаете и отдадите ей только те поля в запросе, которые в конкретном варианте настроек будут, да еще и отборы сразу наложите?

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

Хороший вопрос 🙂 нет тут дело немного в другом, в моем примере я именно «выбираю все поля», но те, где NULL, автоматически отсекаются. То есть если бы там не было NULL, они бы вывелись.
Вы же говорите про другое, то есть я гипотетически могу создать 4 поля(для 4-х периодов) и выводить только одно из них, а остальные сами отсекутся. Да, это так, но для этого надо вручную менять структуру отчета, чтобы включить только нужные поля. Я же хотел именно чтобы к структуре отчета пользователь даже не подходил. Но, справедливости ради, это и правда довольно нетипичный метод и в данном случае он излишне сложный 🙂 я его показал просто как пример работы с NULL.

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

В целях демонстрации решения задачи разработана простая конфигурация, состоящая из двух документов, одного справочника, одного регистра накопления вида «Остатки» и отчета.

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

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

Кратко о конфигурации:

В справочник заносятся организации, документом «Поступление (списание) денежных средств» отражается факт поступления или списания денежных средств на [условный] счет организации, документом «Черная прибыль» дополнительно вносятся к учету некоторые средства.

Итак, имеется таблица регистра накопления со следующим содержимым:

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

Необходимо сформировать отчет, который будет включать следующие данные:

Организация	Начальный остаток средств	Приход средств	Расход средств	Конечный остаток средств	Черная прибыль
Месяц

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

Создаем отчет с использованием системы компоновки данных и конструируем во внутренностях такой запрос:

В конструкторе СКД выбираем необходимые поля для вывода в отчет, создаем группировки по полям Организация и Месяц, для группировки по месяцам устанавливаем дополнение периода Месяц.

Вроде бы, всё просто. Некоторые спрашивают, можно ли в таких случаях в запросе делать левое соединение. Ответ: не запрещается, но если сделать соединение к таблице, в которой нет данных (а такое может быть), получим пустой отчет.

Смотрим, что получилось в нашем случае:

(в условном оформлении задан формат поля «ПериодМесяц»: ДФ=’MMMM yyyy’)

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

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

Получаем такой запрос (пояснения ниже):

Пояснения к запросу:

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

Пояснения к временным таблицам:

• ДанныеИзРегистра — данные из регистра накопления (остатки и обороты)
• ДанныеИзДокументов — данные из документов (черная прибыль)
• ВсеМесяцы — таблица в которой содержатся все месяцы из интервала Январь 2000 — (Конечная дата отчета + 10 лет); этот интервал можно менять под конкретные нужды
• ВсеИнтервалы — интервалы [в количестве месяцев] между периодами в результате запроса к регистру накопления
• ИнтервалыБольшеМесяца — интервалы [в количестве месяцев] между периодами в результате запроса к регистру накопления, где количество месяцев в интервале больше 1
• НедостающиеПериоды — недостающие периоды в данных из регистра накопления и конечное сальдо по этим периодам (оно будет равно начальному в отчете)
• НедостающиеПериодыИСальдоПоДокументам — периоды (и сальдо по ним), которых нет в регистре, но есть в документах
• НедостающиеПериодыБезКонОст — свернутая таблица с недостающими периодами по документам без остатков
• НедостающееСальдоПоДокументам — свернутая таблица с недостающими периодами по документам и остатками
• СальдоВсеПериоды — таблица в которой содержатся все недостающие периоды (и по документам и «разрывы» в регистре) с остатками
• Данные — объединение данных из регистра (с дополненными периодами) с данными из документов

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

Результат формирования отчета:

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

Читайте также:

• Какие программы называют файловыми менеджерами
• Получить значение перечисление 1с 7
• Что такое sh x в visual studio
• Заполнение шаблона excel c
• В этом браузере не поддерживается просмотр панорамных видео перейдите в приложение youtube перейти

Как дополнить данные отчета всеми данными в группировке по периоду

Как дополнить данные отчета всеми данными в группировке по периоду

Сдать отчет в налоговую там.

1с. СКД. Дополнение отчета датами.
Иногда в отчет СКД 1с надо вывести периоды (например дни) все, а не только те по которым были движения. Для однозначности восприятия или чтобы построить по данным отчета график без дыр.

Как это сделать?
1. Запрос надо строить к виртуальной таблице остатки и обороты.
2. В параметрах установить Периодичность. Если не знаете какую поставьте Авто.
3. В полях группировки по периоду(добавить если нет), двойной клик мышью, выбрать «Тип дополнения», «Начальную дату периода». и «Конечную дату периода». Для последних установить тип (например, параметра СКД и выбрать НачалоПериода и КонецПериода).

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

Вирус скуки

Дать возможность пользователю самостоятельно комбинировать любые поля.

Дополнение от пользователя efi про ресурсы:
Под ресурсами в системе компоновки данных подразумеваются поля, значения
которых рассчитываются на основании детальных записей, входящих в группировку. По сути ресурсы являются групповыми или общими итогами отчета. Итоговые данные формируются на закладке Ресурсы.

10 комментариев:

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

Отчеты. Использование вычисляемого поля в отчете

Система компоновки данных предназначена для создания произ­вольных отчетов в системе 1С: Предприятие и состоит из нескольких основных частей.
Исходные данные для компоновки отчета содержит в себе схема компоновки данных.
Разработчик создает схему компоновки данных, в которой описывает текст запроса, наборы данных, связи между ними, доступные поля, параметры получения данных, и задает первоначальные настройки компоновки — структуру отчета, макет оформления данных и др.
Отчет системы компоновки имеет сложную иерархическую структуру и может состоять из различных элементов, таких как группировки, таблицы и диаграммы.
При этом пользователь может изменить существующую структуру отчета или вообще создать совершенно новую структуру отчета. Может настроить необходимый ему отбор, оформление элементов структуры отчета, получить расшифровку по каждому элементу и т. д.
Например, может быть задана такая структура отчета, состоящая из одной таблицы и одной диаграммы.

В этом случае сформированный отчет будет иметь следующий вид.
В представленном отчете таблица будет состоять из записей регистра накопления ПродажиОбороты о клиентах и оказанных им услугах. Эти записи сгруппированы по мастерам, которые выполняли заказы. А в группировке будет выведен список услуг, оказанных данным мастером, и затраченных на это материалов.
Как мы уже говорили в начале раздела, система компоновки данных представляет собой совокупность нескольких объектов. При фор­мировании и исполнении отчета происходит последовательная передача данных от одного объекта системы компоновки данных к другому, до получения конечного результата — документа, показан­ного пользователю.
Алгоритм взаимодействия этих объектов выглядит следующим образом:
Разработчик создает схему компоновки данных и настройки по умолчанию. В общем случае на основе одной схемы компоновки данных может быть создано большое количество различных отчетов. Настройки компоновки данных, создаваемые разработчиком или изменяемые пользователем, определяют, какой именно отчет будет получен в конкретном случае.

• На основе схемы компоновки и имеющихся настроек компонов­щик макета создает макет. Это этап подготовки к исполнению отчета. Макет компоновки данных является уже готовым заданием для выполнения процессором компоновки. Он содержит необходимые запросы, макеты областей отчета и др.
• Процессор компоновки данных выбирает данные из информационной базы согласно макету компоновки, агрегирует и оформляет эти данные.

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

ПРАКТИЧЕСКАЯ ЧАСТЬ

Использование вычисляемого поля в отчете

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

Добавим новый объект конфигурации Отчет. Назовем его РейтингКлиентов и запустим конструктор схемы компоновки данных.
Создадим новый Набор данных — запрос и вызовем конструктор запроса.

Запрос для набора данных

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

• ПродажиОбороты. Клиент,
• ПродажиОбороты.ВыручкаОборот,
• ПродажиОбороты.СтоимостьОборот.

— На закладке Объединения/Псевдонимы укажем, что поле ВыручкаОборот будет иметь псевдоним Выручка, а поле СтоимостьОборот -Стоимость.

—НажмемОК–
Перейдем к редактированию схемы компоновки данных.

Вычисляемые поля

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

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

Листинг 13.2.Выражение для расчета вычисляемого поля «Доход»

Выручка – Стоимость

Ресурсы

— На закладке Ресурсы нажатием кнопки » выберем все доступные ресурсы отчета:

Рис. 13.21. Ресурсы схемы компоновки данных

Настройки

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

Структура отчета должна принять следующий вид:

Рис. 13.22. Структура отчета и настройки диаграммы

— На закладке Другие настройкивыберем тип диаграммы Круговая объемная и зададим заголовок отчета — Рейтинг клиентов.
После этого в окне редактирования объекта РейтингКлиентов перейдем на закладку Подсистемы. Отметим в списке подсистем конфигурации подсистемы Оказание услуг и Бухгалтерия.

Запустим 1С:Предприятие в режиме отладки и выполним команду Рейтинг клиентов в панели действий раздела Бухгалтерия. Нажмем Сформировать.
Мы видим данные о доходе от оказания услуг по каждому из клиентов, представленные в виде круговой диаграммы. Если вернуться в конфигуратор и изменить тип диаграммы на Гистограмма объемная, то наша диаграмма поменяет вид.

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

• Как создать отчет, содержащий диаграмму.
• Как использовать параметры в системе компоновки данных.
• Что такое ресурсы в системе компоновки данных.
• Что такое вычисляемые поля в системе компоновки данных.
• Как дополнить данные отчета всеми датами в группировке по периоду.

— Перейти на страницу контрольного тестового задания

Лабораторная работа № 18

Дата добавления: 2020-04-25 ; просмотров: 234 ; Мы поможем в написании вашей работы!

Создание отчета с группировкой или сводного отчета

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

Access упрощает работу над отчетами с группировкой. Вы можете создать простой отчет с группировкой при помощи мастера отчетов, добавить группировку или сортировку в уже созданный отчет, а также изменить уже заданные параметры группировки или сортировки.

Примечание: Статья неприменима к веб-приложениям Access — новому типу баз данных, которые создаются в Access и публикуются в Интернете.

В этой статье

• Создание краткого отчета с группировкой или сортировкой
• Создание нового отчета с группировкой при помощи мастера отчетов
• Добавление или изменение группировки и сортировки в существующем отчете

Создание краткого отчета с группировкой или сортировкой

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

1. в области навигации выберите таблицу или запрос с записями, которые вы хотите поместить в отчет;
2. на вкладке Создание щелкните Отчет.

Создание отчета с группировкой при помощи мастера отчетов

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

Запуск мастера отчетов

1. На вкладке Создание в группе Отчеты выберите Мастер отчетов. Access запустит мастер отчетов.
2. Щелкните раскрывающийся список Таблицы и запросы и выберите таблицу или запрос с полями, которые вы хотите добавить в отчет.
3. Чтобы выбрать поля, дважды щелкните их в списке Доступные поля. Access переместит их в список Выбранные поля. Вы также можете нажать кнопки, находящиеся между полями Доступные поля и Выбранные поля, чтобы добавить или удалить выбранное поле или сразу все поля.
4. Если вы хотите добавить в отчет также поля из другой таблицы или из другого запроса, щелкните еще раз раскрывающийся список Таблицы и запросы, выберите нужное и продолжайте добавлять поля.
5. После того, как вы завершите добавление полей, нажмите Далее.

Группировка записей при помощи мастера отчетов

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

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

1. На странице мастера отчетов с вопросом Добавить уровни группировки? щелкните одно из полей в списке и нажмите Далее.
2. Чтобы добавить уровни группировки в отчет, дважды щелкните любое из добавляемых в отчет полей в списке. Вы также можете удалить уровень группировки, дважды щелкнув его на странице в правой части диалогового окна. С помощью кнопок со стрелками можно добавлять и удалять уровни группировки, а также настраивать приоритет уровня группировки, выбирая его и нажимая кнопки «Вверх» и «Вниз». Access добавит каждый уровень группировки и покажет, что он вложен в родительский уровень группировки.
3. Нажмите кнопку Параметры группировки, чтобы открыть диалоговое окно Интервалы группировки.
4. При желании для каждого поля группировки выберите интервал группировки. Интервал группировки позволяет настраивать способ группировки записей. На предыдущем рисунке записи группируются по полю «ДатаПоставки» с типом данных «Дата и время». Мастер отчетов предлагает варианты, соответствующие типу поля из списка Интервалы группировки. Так как тип поля «ДатаПоставки» — «Дата и время», вы можете группировать по действительному значению (обычный), по годам, кварталам, месяцам, неделям, дням, часам и минутам. Если бы поле имело текстовый тип, вы могли бы группировать по всему полю (обычный) или, к примеру, по первым пяти символам. В случае числового типа данных вы можете группировать по значению (обычный) или по возрастанию в выбранном диапазоне. Выбрав интервал группировки, нажмите ОК.
5. Чтобы перейти к следующей странице мастера, нажмите Далее.

Сортировка записей и подведение итогов по ним

Вы можете сортировать записи в порядке возрастания или убывания по 1–4 полям.

1. Щелкните первый раскрывающийся список и выберите поля для сортировки. Вы можете нажать кнопку в правой части списка, чтобы выбрать сортировку по возрастанию или убыванию (сортировка по возрастанию используется по умолчанию). При необходимости щелкните второй, третий и четвертый раскрывающийся список, чтобы выбрать дополнительные поля для сортировки.
2. Нажмите кнопку Итоги, если вам нужно вычислить итоговые значения числовых полей. Обратите внимание, что кнопка Итоги будет доступной лишь в случае, если в разделе подробностей отчета содержится одно или несколько числовых полей. Мастер отображает доступные числовые поля.
3. Выберите один из вариантов: «Сумма»Avg, Min или Максимум, чтобы включить эти вычисления в прил. группу. Вы также можете выбрать для отображения подробности и сводку или только сводку. В последнем случае итоги отображаются для каждого значения поля «ДатаПоставки» (например, если вы установили флажок Sum), но подробности о заказе опускаются. Вы также можете выбрать для сумм отображение итоговых расчетов в процентах.
4. Нажмите кнопку ОК.
5. Следуйте инструкциям на остальных страницах мастера отчетов. На последней странице вы можете изменить заголовок отчета. Этот заголовок будет отображаться на первой странице отчета, и Access сохранит отчет, используя этот заголовок в качестве имени документа. В дальнейшем вы сможете изменить как заголовок, так и имя документа.
6. Нажмите кнопку Готово. Access автоматически сохранит отчет и отобразит его в в режиме предварительного просмотра, в котором вы сможете посмотреть, как будет выглядеть распечатанный отчет.

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

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

Добавление или изменение группировки и сортировки в существующем отчете

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

Добавление группировки, сортировки и итогов

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

Примечание: Хотя в инструкциях этого раздела не говорится прямо об области Группировка, сортировка и итоги, мы советуем открыть ее и отслеживать изменения на ней в процессе работы. Вы получите более полное представление о действиях Access, а освоив работу с областью Группировка, сортировка и итоги, вы с ее помощью сможете дополнительно корректировать отчет. Чтобы отобразить панель Группировка, сортировка и итоги:

• на вкладке Конструктор в группе Группировка и итоги щелкните Группировка и сортировка.

Сортировка по одному полю

• Щелкните правой кнопкой любое значение в поле, по которому требуется выполнить сортировку.
• В контекстном меню выберите нужный вариант сортировки. Например, чтобы выполнить сортировку текстового поля по возрастанию, щелкните Сортировка от А до Я. Чтобы выполнить сортировку числового поля в убывающем порядке, щелкните Сортировка по убыванию.

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

Сортировка по нескольким полям

Примечание: Когда вы применяете сортировку, щелкая поле в режиме макета, вы можете проводить сортировку лишь одного поля за раз. Применение сортировки к другому полю удаляет сортировку по первому полю. Это отличается от сортировки в формах, где можно установить несколько порядков сортировки, щелкнув правой кнопкой мыши каждое поле по очереди и выбрав нужный порядок сортировки. Чтобы создать уровни сортировки для нескольких полей, см. раздел Добавление группировки, сортировки и итоговых расчетов с помощью области «Группировка, сортировка и итоги».

Группировка по полю

• Щелкните правой кнопкой любое значение в поле, по которому требуется выполнить группировку.
• В контекстном меню выберите Группировка.

Access добавит уровень группировки и создаст заголовок группы. Если область Группировка, сортировка и итоги уже открыта, будет видно, что к полю добавилась новая строка Группировка.

Добавление итогового значения в поле

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

• Щелкните правой кнопкой любое значение в поле, по которому требуется рассчитать итоговое значение.
• Щелкните Итог.
• Выберите операцию, которую нужно выполнить: Сумма, Среднее, Число записей (чтобы посчитать все записи), Количество значений (чтобы посчитать только записи со значением данного поля), Максимум, Минимум, Стандартное отклонение или Дисперсия.

Access добавит в колонтитул отчета такой элемент управления, как вычисляемый текст, в котором подводится общий итог. Если в отчете содержатся уровни группировки, Access добавит колонтитулы групп (если их нет) и поместит итоговое значение в каждый колонтитул.

Примечание: Вы также можете добавить итоги, щелкнув поле, по которому их необходимо рассчитать, и на вкладке Конструктор в группе Группировка и итоги щелкнув Итоги.

Добавление группировки, сортировки и итогов с помощью области «Группировка, сортировка и итоги»

Работа с областью Группировка, сортировка и итоги предоставляет максимальные возможности, когда вам нужно добавить или изменить группы, порядки сортировки или параметры итогов в отчете. При этом режим макета наиболее удобен в работе, так как в нем гораздо проще просматривать, как внесенные изменения влияют на отображение данных.

Отображение области «Сортировка, группировка и итоги»

• На вкладке Конструктор в группе Группировка и итоги щелкните Группировка и сортировка.

Чтобы добавить уровень группировки или сортировки, щелкнитеДобавить группировку или Добавить сортировку.

В область Группировка, сортировка и итоги будет добавлена новая строка и отобразится список доступных полей.

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

Дополнительные сведения о создании и использовании выражений см. в статье Создание выражений.

• Если уже существует несколько уровней сортировки или группировки возможно, потребуется прокрутить страницу вниз в области «Группировка, сортировка и итоги», чтобы увидеть кнопки «Добавить группу» и «Добавить сортировку».
• Вы можете назначить до десяти уровней сортировки и группировки в отчете.

Изменение параметров группировки

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

• Чтобы отобразить все параметры для уровня сортировки или группировки, щелкните Больше на нужном уровне.
• Чтобы скрыть параметры, щелкните Меньше.

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

Интервал группировки. Эта настройка определяет способ группировки записей. Например, текстовые поля можно сгруппировать вместе по первому символу (если они начинаются на «А», «Б» и т. д.). Поля даты можно сгруппировать по дню, неделе, месяцу, кварталу или ввести свой интервал.

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

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

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

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

Чтобы добавить или изменить заголовок:

• щелкните голубой текст после надписи с заголовком; появится диалоговое окно Масштаб;
• введите новый заголовок в диалоговом окне, а затем нажмите ОК.

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

С разделом/без раздела примечания. Используйте этот параметр, чтобы добавить или удалить раздел колонтитулов после каждой группы. Прежде чем удалить раздел колонтитулов, содержащий элементы управления, Access попросит вас о подтверждении.

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

• Не удерживать группу на одной странице. Используйте этот параметр, если вам не важно расположение групп при разрыве страниц. Например, 10 элементов группы из 30 элементов могут располагаться внизу одной страницы и остальные 20 вверху следующей страницы.
• Удерживать группу на одной странице. Этот параметр помогает свести к минимуму число разрывов страниц в группе. Если группа не помещается на оставшемся месте одной страницы, Access оставляет это место пустым и размещает группу на следующей странице. Большие группы все же могут располагаться на нескольких страницах, но этот параметр сводит такие случаи к минимуму.
• Удерживать заголовок и первую запись на одной странице. Следит за тем, чтобы заголовок группы не распечатывался отдельно от самой группы в нижней части страницы. Если Access определит, что места недостаточно для печати хотя бы одной строки после заголовка, группа будет распечатана со следующей страницы.

Изменение приоритета уровней группировки и сортировки

Чтобы изменить приоритет, щелкните строку в области Группировка, сортировка и итоги, а затем стрелку вверх или вниз справа строки.

Удаление уровней группировки и сортировки

Чтобы удалить уровень, в области Группировка, сортировка и итоги выберите строку, которую вы хотите удалить, а затем нажмите клавишу DELETE или кнопку Удалить справа от строки. При удалении уровня группировки, если заголовок группы или колонтитул содержали поле группировки, Access переместит его в раздел подробностей отчета. Все другие элементы управления удаляются.

Создание сводного отчета (без сведений о записях)

Если вы хотите показать только итоги (данные в строках заголовка и колонтитулов), на вкладке Конструктор в группе Группировка и итоги щелкните Скрыть подробности. Тем самым вы скроете записи следующего нижнего уровня группировки, и итоговые данные будут отображаться более компактно. Несмотря на то, что записи скрыты, элементы управления в скрытом разделе не удаляются. Щелкните Скрыть подробности еще раз, чтобы вернуть строки подробностей в отчет.

Группировка данных сводной таблицы в Excel 2013

Несмотря на то что большая часть операций подведения итогов и вычисления показателей выполняется с использованием стандартных команд сводных таблиц, иногда возникают ситуации, в которых требуется дополнительная группировка данных. [1] Например, исходные финансовые данные, как правило, фиксируются с указанием даты сделки (рис. 1), в отчете желательно представить их не по каждой дате отдельно (рис. 2а), а сгруппированными по месяцам (рис. 2б). Команда Группировать вкладки Анализ позволяет легко и быстро консолидировать даты сделок в группы, основанные на месяцах или кварталах. Впоследствии вы сможете суммировать данные в этих группах таким же образом, как это выполняется в полях сводной таблицы.

Рис. 1. Исходные данные содержат список сделок с указанием конкретной даты

Скачать заметку в формате Word или pdf, примеры в формате Excel

Рис. 2. Отчет, сведенный по датам: (а) данные двух лет продаж занимают более 500 столбцов; (б) после группировки по месяцам отчет существенно удобнее

Группировка полей дат

Для группировки полей дат выделите заголовок поля дат или любую ячейку с датой. Например, на рис. 2а выделите одну из ячеек: В3, В4, С4, D4… На контекстной вкладке ленты Анализ в области Группировать щелкните на кнопке Группировка по полю. Если поле содержит информацию о датах, откроется диалоговое окно Группирование (рис. 3). Обратите внимание, что исходные данные (см. рис. 1) в колонке Дата заказа должны содержать только даты; даже одна текстовая или незаполненная ячейка (пустая) в исходных данных в столбце Дата заказа не позволит сделать группировку по датам. Если перед группированием вы выделили только одну ячейку, то кнопки Группировка по полю и Группировка по выделенному работают одинаково. Различие проявится только если перед группированием вы выделите несколько ячеек.

Рис. 3. Окно Группирование

Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. По умолчанию выделен вариант Месяцы. Выберите также Дни и Годы и нажмите Ok. Обратите внимание на некоторые особенности группировки данных в итоговой сводной таблице. Во-первых, поля Месяцы и Годы добавлены в список полей (рис. 4). Не позволяйте себя одурачить — ваш источник данных не изменился и никаких новых полей не содержит. Эти поля теперь являются частью кеша сводной таблицы в памяти (подробнее о кеше см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Во-вторых, по умолчанию поля Месяцы и Годы автоматически добавляются в макет сводной таблицы. Вы можете работать с ними, как с обычными полями: перетаскивать в другие области или делать неактивными. В-третьих, включайте данные по Дням, чтобы иметь возможность добавить это поле в сводную таблицу. Если вы в окне Группирование оставите только Месяцы, то поле Даты вы будете видеть в списке, вот только оно эквивалентно месяцам, а поле Месяцы не появится. И наконец, в окне Группирование добавляйте поле Годы. Если это сделать, то вы корректно сможете разделить данные по годам (рис. 5а), если же этого не сделать, то данные двух лет наблюдений объединяться в одном столбце (рис. 5б).

Рис. 4. Добавление полей Месяцы и Годы в список полей и макет сводной таблицы

Рис. 5. Группирование: (а) по дням, месяцам и годам; (б) по дням и месяцам

Группировка полей дат по неделям

Диалоговое окно Группирование предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям или иным промежуткам времени? Это вполне реально.

Прежде всего следует свериться с календарем, чтобы решить, с какого дня должна начинаться неделя: с воскресенья, понедельника или любого на ваш выбор. Например, первый понедельник в 2014-м году – 6 января. Если вы хотите, чтобы данные за первую неделю также отражались, вам следует выбрать в качестве начала отсчета последний понедельник 2013-го года – 30 декабря. Чтобы сгруппировать даты по неделям выделите заголовок или ячейку с датой. Например, на рис. 2 ячейки В3 или В4. Перейдите на контекстную вкладку ленты Анализ и в разделе Группировать щелкните на кнопке Группировка по полю. В диалоговом окне Группирование (рис. 6) выделите только параметр Дни. В результате станет доступным счетчик количество дней. Чтобы создать недельный отчет, установите значение 7. Установить в поле начиная с требуемую дату (в нашем примере – 30.12.13). Нажмите Ok. В результате сгенерируется отчет, отображающий еженедельные объемы продаж, как показано на левой части рис. 6.

Рис. 6. Группировка дат по неделям

Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Вы не сможете сгруппировать текущее или любое другое поле по месяцам или кварталам. Чтобы преодолеть это ограничение вы должны создавать сводные таблицы так, чтобы каждая была основана на своем кеше (см. Excel 2013. Создание нескольких сводных таблиц на основе одного источника данных: один кеш или несколько?). Именно так я и поступал в файле с примерами, чтобы сохранить вид примеров в первозданном виде.

Группирование двух полей дат в одной сводной таблице

При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат. Если вам нужно создать отчет с двумя полями дат (например, дата заказа и дата оплаты) и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы, а второму — имя Годы2. Чтобы не возникло путаницы, переименуйте поле Годы, прежде чем запустить группирование по второму полу дат.

Группировка числовых полей

Диалоговое окно Группирование, применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Это может быть полезно при проведении частотного анализа. Сводная таблица на рис. 7 сформирована необычным образом. Здесь в область СТРОКИ помещено поле Доход, а в область ЗНАЧЕНИЕ – поле Заказчик. Поскольку поле Заказчик является текстовым, сводная таблица автоматически подсчитывает их количество, а не сумму.

Рис. 7. Сводная таблица, «заготовка» для проведения частотного анализа

Выделите в столбце А любое число, а затем на вкладке Анализ щелкните на кнопке Группировка по полю. В диалоговом окне Группирование выберите параметры группирования (рис. 8). В рассматриваемом случае группирование начинается с 0 и завершается величиной 25 350 при шаге группирования 5000.

Рис. 8. Частотное распределение на основе группировки заказов в группы по $5000 по полю Доход

Разгруппировка. Создав группу, вы можете разгруппировать её с помощью кнопки Разгруппировать, находящейся на вкладке Анализ. Достаточно выделить ячейку со сгруппированными данными и щелкнуть на этой кнопке. Эта команда также доступна в контекстном меню: выделите ячейку со сгруппированными данными и щелкните правой кнопкой мыши.

Группировка текстовых полей

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

Для начала создайте отчет, отображающий доход по рынкам сбыта. Держа нажатой клавишу Ctrl, выделите рынки сбыта, на основе которых будет создана новая зона (рис. 9). Перейдите на вкладку Анализ, и щелкните на кнопке Группировка по выделенному.

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

Excel добавит новое поле с именем Рынок сбыта2 (рис. 10). Выделенные на первом шаге рынки сбыта объединились в Группа1. Выделите оставшиеся рынки сбыта и повторно щелкните на кнопке Группировка по выделенному.

Рис. 10. Первая зона создана

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

Рис. 11. Итоговый отчет по новым зонам продаж

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.