Z-score внутри выбранного сегмента

Используйте этот приём, когда единица наблюдения — наблюдение, сегмент или период, а данные приходят из источника вроде «аналитическая витрина или выгрузка». Для «Z-score внутри выбранного сегмента» до формулы запишите ожидаемый результат словами и укажите, какие поля его определяют. В примере ниже это «Наблюдение», «Сегмент», «Показатель X», «Показатель Y». Такой порядок особенно полезен при регулярном обновлении: изменение исходной выгрузки не должно незаметно менять смысл показателя.

Русская версия Excel:

=ЕСЛИОШИБКА((B2-СРЗНАЧ(ФИЛЬТР($B$2:$B$200;$C$2:$C$200=C2)))/СТАНДОТКЛОН.Г(ФИЛЬТР($B$2:$B$200;$C$2:$C$200=C2));0)

Английская версия Excel:

=IFERROR((B2-AVERAGE(FILTER($B$2:$B$200,$C$2:$C$200=C2)))/STDEV.P(FILTER($B$2:$B$200,$C$2:$C$200=C2)),0)

Для «Z-score внутри выбранного сегмента»: Разберите выражение на входные диапазоны, критерии и возвращаемое значение. Такой разбор сразу показывает, какая ссылка должна быть абсолютной, а какая — изменяться по строкам. Особенно важно проверить источник массива и условие попадания строки.

1. Найдите в формуле источник массива и сопоставьте этот элемент с ячейкой или диапазоном из примера.
2. Затем проверьте условие попадания строки; для функции СРЗНАЧ это определяет, какие данные реально участвуют в ответе.
3. Отдельно задайте правило для ситуации: свободную область для разлива результата.
4. Проверьте ссылки после копирования формулы на следующую строку: параметры и справочники не должны сдвигаться случайно.
5. Измените одно значение в поле «Наблюдение» и убедитесь, что меняется только ожидаемая часть результата.

Z-score полезнее считать внутри сопоставимого сегмента, а не по смешанной совокупности.

Для рабочей методики зафиксируйте это правило рядом с расчётом. Если другой сотрудник не может по двум-трём строкам восстановить, почему запись вошла в «Z-score внутри выбранного сегмента», значит формула пока документирована недостаточно.

Проверка «Z-score внутри выбранного сегмента» должна быть привязана к данным статьи, а не к абстрактному чек-листу. Начните с полей «Наблюдение», «Сегмент», «Показатель X», «Показатель Y». В этой области отдельно проверьте: сопоставимый уровень детализации; достаточный размер выборки; отсутствие текстовых чисел и пропусков.

Создайте одну строку, которая заведомо должна участвовать в расчёте, и одну строку, которая заведомо не должна. Для «Z-score внутри выбранного сегмента» такая пара тестов полезнее десятка визуальных просмотров итоговой таблицы.

В рабочем файле держите сырой источник, справочники и итоговый расчёт раздельно. После обновления достаточно проверить несколько контрольных показателей, а не просматривать все строки. Для «Z-score внутри выбранного сегмента» добавьте рядом минимум два контроля: число строк, участвующих в расчёте, и отдельный индикатор для ситуации «свободную область для разлива результата». Если показатель анализируется по продукту, храните этот разрез отдельным полем, а не кодируйте его ручным цветом или положением строки.

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

Русская версия Excel

=ЕСЛИОШИБКА((B2-СРЗНАЧ(ФИЛЬТР($B$2:$B$200;$C$2:$C$200=C2)))/СТАНДОТКЛОН.Г(ФИЛЬТР($B$2:$B$200;$C$2:$C$200=C2));0)

Английская версия Excel

=IFERROR((B2-AVERAGE(FILTER($B$2:$B$200,$C$2:$C$200=C2)))/STDEV.P(FILTER($B$2:$B$200,$C$2:$C$200=C2)),0)

• Добавить сортировку результата применительно к «Z-score внутри выбранного сегмента».
• Проверить пустую выборку на том же примере.
• Связать массив с автоматически растущей таблицей, если расчёт станет регулярным.
• Сравнить показатель по продукту и сегменту.