1с скд общие итоги по группировкам. Примеры применения функции скд - вычислитьвыражение

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

1. нарастающий итог в группировке;
2. нарастающий итог в кросс-таблице;
3. получение предыдущего значения;
4. вывод ТЧ в одной строке.

1. Получение показателя нарастающим итогом

Получим количество товара нарастающим итогом на уровне группировки. Для этого создаем вычисляемое поле (см. рисунок 1).
На вкладке "Ресурсы" задаем функцию для вычисляемого поля:
ВычислитьВыражение("Сумма(КоличествоОборот)", "Первая", "Текущая")
которая будет суммировать количество товаров от первой записи до текущей (см. рисунок 2).

Если количество товара нарастающим итогом требуется получать на уровне детальных записей, то функцию ВычислитьВыражение задаем для вычисляемого поля на вкладке "Вычисляемые поля" (см. рисунок 3).
В зависимости от уровня получения нарастающего итога, создаем группировку (см. рисунок 4): на уровне ресурсов - группировку по товарам, на уровне ДЗ - группировку детальных записей.

Рисунок 4. Группировки отчета с нарастающим итогом

2. Получение значения показателя из предыдущей строки

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

3. Получение показателя нарастающим итогом в кросс-таблице

Получим количество товара нарастающим итогом в разрезе периода. Для этого создаем вычисляемое поле (см. рисунок 1). На вкладке "Ресурсы" для вычисляемого поля укажем следующее выражение (см. рисунок 6):
ВычислитьВыражение("Сумма(КоличествоОборот)", "Период", "Первая", "Текущая")
которое на уровне группировки будет вычислять количество товара в промежутке от первой строки до текущей в разрезе периода по каждой номенклатуре.
На вкладке "Настройки" создаем таблицу с группировкой по номенклатуре в строке и группировкой по периоду в колонке (см. рисунок 7).

4. Вывод данных табличной части в одну строку

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

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

Группировка строк и столбцов в Excel

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

Как скрыть и показать группы

Подведение итогов в Excel

Команда Промежуточный итог позволяет автоматически создавать группы и использовать базовые функции, такие как СУММ, СЧЁТ и СРЗНАЧ, чтобы упростить подведение итогов. Например, команда Промежуточный итог способна вычислить стоимость канцтоваров по группам в большом заказе. Команда создаст иерархию групп, также называемую структурой, чтобы упорядочить информацию на листе.

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

Создание промежуточного итога

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

Просмотр групп по уровням

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

Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

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

Удаление промежуточных итогов в Excel

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

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

Вроде и нет ничего проще,а с четким определением проблема.
Итак.

Сгруппировать(группировка) - свернуть записи в группу по групповым полям (полям с одинаковым значением).
Пример . Нам необходимо узнать общее количество товаров на определенном складе (...всего 999 тонн..). В регистр записывается каждое движение и даже элементарный запрос из двух полей (Склад и Количество) сформирует выборку из множества записей с одинаковым значением поля Склад и разными значениями поля Количество.
Свернуть (сгруппировать) остатки по полю Склад- это получить общее количество товара на этом складе.
Отсюда вытекает необходимость (для нашего примера, но вообщем не обязательно) вычисления агрегатных функций (производят одиночное значение для определенной группы полей таблицы) по групповому полю (в терминах конструктора запросов- Суммируемое поле ).
В нашем примере - групповое поле Склад, а агрегатную функцию вычислим по полю Количество- получим ОБЩЕЕ КОЛИЧЕСТВО всех товаров на складе.
Т.к. настолько простые запросы очень редки, то во всех иных случаях при применении Группировки, группироваться будут все поля запроса (за исключением Суммируемых) и выборка будет сокращаться (сворачиваться) только на те записи , у которых все групповые поля одинаковы (одинаковый склад, одинаковый товар, одинаковый размер товара- эти записи свернуться, если хоть что-то разное- нет).

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

Итоги по группировкам - значения агрегатных функций по групповым полям (полям с одинаковым значением).

Отличия запроса с группировками (СГРУППИРОВАТЬ ПО) от запроса с итогами по группировкам .

В первом случае в результат запроса попадут только итоговые строки. В примере групповое поле- номенклатура:

Во втором- итоги и исходные записи. Причина- в определении (итоги не "сворачивают" данные, а добавляют строки)!В примере групповое поле- номенклатура:

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

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

Итоги по иерархии. Если групповое (группировочное) поле является ссылкой на справочник, то для расчета итогов по группам справочника (или родительским элементам, если справочник без групп) необходимо указать ключевое слово ИЕРАРХИЯ. В этом случае в результат будут добавлены записи с итогами для уровней иерархии справочника.

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

Синтаксис работы с итогами в выборке из результата запроса

Метод ТипЗаписи()
Пример:
Если Выборка . ТипЗаписи () = ТипЗаписиЗапроса . ИтогПоГруппировке Тогда

Продолжить;
КонецЕсли;

Они имеют ту же задачу — произвести математические или статистические операции над данными выборки. Но имеются и существенные различия:

1. Итоги добавляют строки к данным выборки, в то время как группировки сворачивают выборку и строк становится меньше.
2. Итоги можно рассчитывать по всем данным выборки или по отдельным полям, при этом, в отличие от группировок, могут оставаться поля, которые не являются ни итоговыми, ни группировочными.
3. Итоги могут учитывать иерархию.

Для начала немного теории. Секция итогов в запросах 1С состоит из двух разделов.

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

Существует 6 видов агрегатных функций, применяемых при группировках:

1. СУММА — суммирует значения группируемого столбца, применяется только для числовых значений.
2. СРЕДНЕЕ — вычисляет среднее арифметическое из значений группируемого столбца, применяется только для числовых значений.
3. МАКСИМУМ — может применяться для любых типов значений группируемого столбца, при этом возвращается максимальное значение из всех группируемых. Если значения не числовые, то возвращается последнее при сортировке по возрастанию.
4. МИНИМУМ — может применяться для любых типов значений группируемого столбца, при этом возвращается минимальное значение из всех группируемых. Если значения не числовые, то возвращается первое при сортировке по возрастанию.
5. КОЛИЧЕСТВО — возвращает количество значений в группируемом столбце, может использоваться для любых типов значений. В расчет не берутся значения типа NULL.
6. КОЛИЧЕСТВО РАЗЛИЧНЫЕ — возвращает количество различных значений в группируемом столбце, может использоваться для любых типов значений. В расчет не берутся значения типа NULL.

Второй раздел начинается с ключевого слова ПО и содержит группировочные поля в разрезе которых будет подсчитываться результат по итоговым полям. И/или слово ОБЩИЕ , если нужно подсчитать итоги по всей выборке. Здесь важен порядок, в котором будут располагаться поля, это этого зависит и порядок подсчета итогов в результате запроса.

Если в группировочном поле находятся данные, имеющие иерархическую структуру (иерархический справочник), то можно подсчитать итоги по всей иерархической цепочке. Для этого используется ключевое слово ИЕРАРХИЯ после имени группировочного поля. Если же итоги нужно подсчитывать по всей иерархической цепочке, кроме самого группировочного поля, то используется ключевое слово ТОЛЬКО ИЕРАРХИЯ .

Рассмотрим в качестве примера таблицу поставок товаров.

Задача 1. Подсчет итогов по нескольким полям.

Необходимо узнать, сколько всего товаров поставлено и сколько столов и стульев.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,


ИЗ
Поставки КАК Поставки
ИТОГИ
СУММА(Количество)
ПО
ОБЩИЕ,
Товар" ;

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

Задача 2. Группировка записей по полю.

Необходимо сгруппировать записи таблицы по виду товара.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,
Поставки.Поставщик КАК Поставщик,
Поставки.Количество КАК Количество,
ИЗ
Поставки КАК Поставки
ИТОГИ

ПО
Товар" ;

Задача 3. Подсчет итогов по полю с учетом иерархии.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,
Поставки.Поставщик КАК Поставщик,
Поставки.Количество КАК Количество,
ИЗ
Поставки КАК Поставки
ИТОГИ
СУММА(Количество)
ПО
Поставщик ИЕРАРХИЯ" ;

В результате получим следующую таблицу.

Задача 4. Подсчет итогов только по иерархии.

Запрос. Текст= "ВЫБРАТЬ
Поставки.Товар КАК Товар,
Поставки.Поставщик КАК Поставщик,
Поставки.Количество КАК Количество,
ИЗ
Поставки КАК Поставки
ИТОГИ
СУММА(Количество)
ПО
Поставщик ТОЛЬКО ИЕРАРХИЯ" ;

В результате получим следующую таблицу.

Как обойти результат запроса с итогами

Результат запроса с итогами можно обойти несколькими способами:

1. Как обычный запрос. В этом случае будут последовательно выведены группировочные и детальные записи. Пример такого обхода приведен в статье .
2. Отдельно обойти группировки и детальные записи.
   В качестве примера возьмем запрос из задачи 1. Но не будем учитывать общие итоги.

   ГруппировкиТовар = Запрос. Выполнить() . Выбрать(ОбходРезультатаЗапроса. ПоГруппировкам, "Товар" ) ;
   Пока ГруппировкиТовар. Следующий() Цикл
   //здесь обрабатываем группировочные строки результата запроса
   ДетальныеЗаписи = ГруппировкиТовар. Выбрать() ;
   Пока ДетальныеЗаписи. Следующий() Цикл
   //здесь обрабатываем строки детальных записей, принадлежащих группировкам
   КонецЦикла ;
   КонецЦикла ;

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

Вот примеры таких задач:

• Получение нарастающего объема продаж по месяцам, например, для вывода в виде диаграммы
• Расчет разницы продаж текущего и предыдущего периода – для всех строк отчета
• Сравнение объема продаж каждого менеджера с эталонным значением, которое вычисляется в ходе формирования этого отчета
• Получение нарастающего итога по горизонтали и вертикали в кросс-таблицах

И все эти задачи решаются элементарно с помощью функций СКД – буквально за пару минут . Без запросов в цикле и кривого кода.

Сегодня рассмотрим использование некоторых функций СКД на конкретных примерах.

Видео 1. Как получить сумму в отчете нарастающим итогом?

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

На самом деле эту задачу можно решить и с помощью запроса (без функций СКД), но есть 2 проблемы:

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

С помощью функций СКД эта задача решается одной строчкой кода, и отчет будет работать максимально быстро, смотрим:

Видео 2. Как получить значение из предыдущей строки отчета?

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

Приведем несколько примеров, когда это может быть полезно:

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

Такие задачи также трудоемко решить с помощью запроса, а в СКД это делается одной строкой:

Видео 3. Как сравнить итог по текущей строке с эталонным значением?

Пример, когда этот функционал может быть полезен:

• В системе ведется учет продаж менеджерами
• Руководитель отдела ведет личные продажи наряду с менеджерами
• Требуется вывести отчет по продажам в разрезе менеджеров
• В отчет также необходимо выводить дельту относительно объема продаж руководителя отдела.

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

А эти данные могут, например, использоваться для начисления премии.

И опять же с помощью СКД эта задача решается в одну строку:

Видео 4. Получение нарастающего итога в кросс-таблице

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

Мы покажем, как с помощью одного параметра СКД управлять направлением расчета итогов:

Видео 5. Вывод табличной части в отдельной ячейке отчета

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

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

В СКД эта задача вновь решается одной функцией.