Взвешенный SLA в Excel: почему среднее процентов отделов обманывает

В одном отделе за месяц обработали десять обращений и уложились в срок во всех десяти. В другом обработали девяносто, из них вовремя только сорок пять. Если усреднить проценты отделов, получится 75%. Но клиентам оказали вовремя лишь 55 из ста услуг. Чтобы отчёт отражал вероятность своевременного обслуживания обращения, нужно взвесить отделы по числу обязательств, а не дать каждому одинаковый голос.

Сначала выбрать, чьё качество измеряем

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

Создайте в Excel таблицу из трёх строк отделов. В A2:A4 названия, в B2:B4 — количество обязательств, срок которых уже наступил, в C2:C4 — выполнено вовремя. Запишите строки: «Первый», 10, 10; «Второй», 90, 45; «Третий», 0, 0. В D1 поставьте заголовок SLA. Для отдела без обязательств процент оставим пустым: отсутствие наблюдений не означает нулевое качество.

Посчитать долю через исходные количества

В D2 введите формулу и скопируйте до D4. В E2 посчитайте общую долю напрямую. В E3 воспроизведите её как взвешенное среднее процентов через SUMPRODUCT. Формулы показаны с английскими именами и запятыми; в русской локализации Excel это ЕСЛИ, СУММ, СУММПРОИЗВ и разделитель точка с запятой. Ячейки с долями отформатируйте как проценты, сохранив числовые значения.

D2: =IF(B2=0,"",C2/B2)
E2: =IF(SUM(B2:B4)=0,"Нет данных",SUM(C2:C4)/SUM(B2:B4))
E3: =IF(SUM(B2:B4)=0,"Нет данных",SUMPRODUCT(D2:D4,B2:B4)/SUM(B2:B4))

D2 получится 100%, D3 — 50%, D4 останется пустой. E2 и E3 дадут 55%. Равенство видно из арифметики: процент каждого отдела умножается на число его обязательств, и восстанавливается количество своевременных. Затем сумма делится на общий объём. Прямой расчёт по количествам предпочтительнее, если они доступны: он не зависит от округления процентов, присланных подразделениями.

Не дать пропускам превратиться в хорошие проценты

Пустая C3 при B3=90 — неизвестный результат, а не ноль своевременных обращений. До расчёта проверьте, что обе колонки содержат числа во всех трёх строках, значения неотрицательны, а C не больше B. Для контроля количества числовых ячеек можно использовать COUNT(B2:C4): ожидается шесть. Если это не так, отчёт должен показать статус неполноты и запросить исходные данные, а не тихо усреднять доступное.

SUMPRODUCT требует массивы одинакового размера. D2:D4 и B2:B4 сопоставляются по строкам, поэтому перед расчётом проценты и объёмы должны относиться к одному отделу и периоду. Нельзя отдельно отсортировать одну колонку или склеить проценты текущего месяца с объёмами прошлого. Лучше хранить исходные количества в одной таблице Excel и получать процент формулой в той же строке. Текстовые значения SUMPRODUCT может трактовать как нули, поэтому числовую проверку нельзя пропускать.

Зафиксировать, какие обязательства уже можно оценивать

Из знаменателя нельзя удалять просроченное, но ещё открытое обращение: срок уже наступил, обязательство пока нарушено. И наоборот, новая заявка со сроком завтра ещё не должна ухудшать сегодняшний SLA. Для примера B содержит обязательства с наступившим сроком на дату среза; закрытые вовремя входят в C, просроченные остаются только в B. Это правило позволяет сравнивать отделы, даже когда у них разная доля открытой работы.

Показывайте рядом общий процент, числитель и знаменатель: 55 из 100. Добавьте строки отделов, чтобы было видно, где сосредоточен объём нарушений. В примере второй отдел определяет общий результат и требует внимания первым, хотя формальное среднее 75% скрывало масштаб проблемы. Такой отчёт связывает процент с реальными обязательствами и помогает выбрать действие: изменить очередь, увеличить мощность или разобраться в причинах опозданий.

Продолжить работу с данными

В курсе «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов» вы разберёте сложные формулы, общий процент через SUMX и практикум план-факта подразделений. Эти материалы помогут перенести один расчёт SLA в обновляемый отчёт с корректной моделью, срезами и проверяемыми итогами. Откройте программу платного курса «Продвинутый Excel: Power Query, Power Pivot и автоматизация отчётов».