Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет стандартного отклонения и коэффициента вариации при отсутствующих данных в периодах
 
#1
01.09.2022 16:44:26
Уважаемые форумчане, добрый день! Выявилась потребность в расчете  коэффициента вариации. Как мы все знаем, это соотношение стандартного отклонения к среднему значению. В исходных данных детализация оборота - по дням,  требуемое значение вариации необходимо рассчитать с помесячной агрегацией.  Написал такой код (подсмотрел у surkenny, спасибо):

Код
КоэффВариации = 
var standOtkl = 
IF (
    NOT HASONEFILTER ( '_КалендарьAUTO'[Month] ),
    STDEVX.P (
        ADDCOLUMNS (
            SUMMARIZE ( FILTER ( 'Оборот', 'Оборот'[ОборотКолич] <> BLANK () ), '_КалендарьAUTO'[Month]),
            "w", CALCULATE ( SUM ( 'Оборот'[ОборотКолич] ) )
        ),
        [w]
    )
)
var sredZnach = 
IF (
    NOT HASONEFILTER ( '_КалендарьAUTO'[Month] ),
    AVERAGEX(
        ADDCOLUMNS (
            SUMMARIZE ( FILTER ( 'Оборот', 'Оборот'[ОборотКолич] <> BLANK () ), '_КалендарьAUTO'[Month]),
            "w", CALCULATE ( SUM ( 'Оборот'[ОборотКолич] ) )
        ),
        [w]
    )
)
return
 DIVIDE(standOtkl,sredZnach)

Код работает, но только в случае, когда во всех месяцах есть строки с данными по обороту. Если в каком-либо месяце оборота нет (нет строк в исходнике), код не воспринимает этот месяц. А для корректности требуемой задачи должен принимать исходные данные, как нулевые по этому месяцу и производить расчет стандартного отклонения, среднего значения и коэффициента вариации с учетом всех этих нулевых месяцев. Буду благодарен, если кто-нибудь сможет подсказать решение данной задачи.

Изменено: Sergey Chernichenko - 01.09.2022 16:44:56
 
 
 
#2
01.09.2022 17:43:11
Sergey Chernichenko, точно мой код? :) Не могу понять к чему тут filter :)
Так же сделайте немного по-другому (не нужен summarize):
Код
ADDCOLUMNS ( 
   VALUES ( '_КалендарьAUTO'[Month] ), 
   “@sum”, 
      VAR curSum = CALCULATE ( SUM ( 'Оборот'[ОборотКолич] ) ) 
      VAR resSum = IF ( ISBLANK ( curSum ); 0; curSUM )
      RETURN resSum
)

Это мы получим таблицу с суммой по месяцам. При этом, если в данных нет продаж за месяц, будет 0.

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

Посему код на мой не похож :) Либо я когда-то давно и пьяный писал :)

P.S. Нет компа сейчас под рукой, поэтому файл не смотрел. Ориентировался только на код Ваших мер.
P.P.S. Хз, что такое Month в Вашем календаре. Если это просто номер/наименование месяца, то нужно вместо этого использовать YearMonth, иначе данные за несколько лет у Вас будут попадать в сумму одного месяца.
Изменено: surkenny - 01.09.2022 18:12:17
 
 
 
#3
01.09.2022 23:57:28
surkenny, код ваш, а то, что он продублирован в коде - это уже моё творчество. Month - да, это столбец с наименованием месяца, принимаю, что нужно брать YearMonth, ценное замечание, спасибо.
Алгоритм расчета понятен, но попробовал по вашему примеру рассчитать среднее, не получилось:
Код
СреднЗначКорр =     
AVERAGE(
        ADDCOLUMNS ( 
                VALUES ( '_КалендарьAUTO'[Month] ), 
                    "sumkol", 
                VAR curSum = CALCULATE ( SUM ( 'Оборот'[ОборотКолич] ) ) 
                VAR resSum = IF ( ISBLANK ( curSum ), 0, curSUM )
         RETURN resSum
        )
    )  
Если использую AVERAGE, пишет, - "Функция "AVERAGE" принимает в качестве аргумента только ссылку на столбец". А если AVERAGEX - "Функции AVERAGEX передано слишком мало аргументов. Минимальное число аргументов для этой функции составляет 2". Никак не пойму, что необходимо добавить, чтобы он начал считать.
Изменено: Sergey Chernichenko - 02.09.2022 00:03:56
 
 
 
#4
02.09.2022 06:07:28
Sergey Chernichenko, наверное, тогда в filter другое условие было :) Для суммы незачем исключать пустые. Вообще, я имел в виду замену части addcolumns в Вашей мере:
Код
Коэффициент:=
VAR monthsValues = 
ADDCOLUMNS ( 
   VALUES ( '_КалендарьAUTO'[Month] ), 
   “@sum”, 
      VAR curSum = CALCULATE ( SUM ( 'Оборот'[ОборотКолич] ) ) 
      VAR resSum = IF ( ISBLANK ( curSum ); 0; curSUM )
      RETURN resSum
)
VAR standOtkl = 
IF (
    NOT HASONEFILTER ( '_КалендарьAUTO'[Month] ),
    STDEVX.P (
        monthsValues,
        [@sum]
    )
)
…
 
 
 
#5
02.09.2022 08:50:48
Даже усложнять не нужно :)
Код
Коэффициент:=
VAR monthsValues = 
ADDCOLUMNS ( 
   VALUES ( '_КалендарьAUTO'[Month] ), 
   “@sum”, CALCULATE ( SUM ( 'Оборот'[ОборотКолич] ) ) + 0
)
VAR standOtkl = 
IF (
    NOT HASONEFILTER ( '_КалендарьAUTO'[Month] ),
    STDEVX.P (
        monthsValues,
        [@sum]
    )
)
…
 
 
 
#6
02.09.2022 12:43:16
surkenny, понял, в чем была моя ошибка в предыдущем посте. Для функции AVERAGEX  требовалось добавить ссылку на столбец (квадратные скобки), а я вставлял без них. Конечный код выглядит так:
Код
КоэффВариацииСпуст = 
VAR monthsValues = 
ADDCOLUMNS ( 
   VALUES ( '_КалендарьAUTO'[Month] ), 
   "@sum", 
      VAR curSum = CALCULATE ( SUM ( 'Оборот'[ОборотКолич] ) ) 
      VAR resSum = IF ( ISBLANK ( curSum ), 0, curSUM )
      RETURN resSum
)
VAR sredZnach = 
IF (
    NOT HASONEFILTER ( '_КалендарьAUTO'[Month] ),
    AVERAGEX(
        monthsValues,
        [@sum]
    )
)
VAR standOtkl = 
IF (
    NOT HASONEFILTER ( '_КалендарьAUTO'[Month] ),
    STDEVX.P(
        monthsValues,
        [@sum]
    )
)
return
DIVIDE(standOtkl,sredZnach)

surkenny, спасибо! На всякий случай выкладываю файл с данным кодом, вдруг кому пригодится. И еще вопрос по теме. Этот код работает по внешним фильтрам дат. Как сделать так, чтобы он брал данные только за последние 6 месяцев вне зависимости от внешних фильтров дат на визуализации? Попытался это сделать, внедрив фильтрацию в момент формирования таблицы:
Код
VAR YearMonthsValues = ADDCOLUMNS ( 
   VALUES ( '_КалендарьAUTO'[ГодМесяц] ), 
   "@sum", 
      VAR curSum = CALCULATE ( SUM ( 'Оборот'[ОборотКолич]  ), 
                    DATESBETWEEN('_КалендарьAUTO'[Дата], StartData, EndData ) )
      VAR resSum = IF ( ISBLANK ( curSum ), 0, curSUM )
      RETURN resSum
)

Но расчет при этом сломался, он перестал видеть месяцы с нулевыми значениями.
Изменено: Sergey Chernichenko - 02.09.2022 13:46:11
 
 
 
#7
02.09.2022 15:13:02
понял. Не туда вносил фильтрацию по датам. Код должен выглядеть так:
Код
VAR YearMonthsValues = 
CALCULATETABLE(
    ADDCOLUMNS ( 
        VALUES ( '_КалендарьAUTO'[ГодМесяц] ), 
            "@sum", 
                VAR curSum = CALCULATE ( SUM ( 'Оборот'[ОборотКолич]) ) 
                VAR resSum = IF ( ISBLANK ( curSum ), 0, curSUM )
      RETURN resSum
                ), 
                FILTER(ALL('_КалендарьAUTO'[Дата]),'_КалендарьAUTO'[Дата] >= StartData && '_КалендарьAUTO'[Дата] <= EndData))

Теперь всё работает. surkenny, спасибо за помощь!
 
 
 
Страницы: 1
Читают тему
Наверх