Иногда требуется обозначить на графике некоторые контрольные значения или коридор, например, среднее значение и сигму-окрестность. Однажды, выступая на конференции с докладом «Как повысить качество управленческих решений» мне потребовалось проиллюстрировать разброс числа рекламаций в течение года (рис. 1). Наряду со значениями (красные точки), я вывел на графике линию среднего значения (зеленая линия) и верхнюю границу диапазона, соответствующую отклонению 3s от среднего значения (синяя пунктирная линия):
Рис. 1. График со средним и границей
Скачать заметку в формате Word, примеры в формате Excel
Построение такого графика не должно вызвать затруднений. Рассмотрим лист «Коридор» Excel-файла:
Во второй колонке указаны значения средней дебиторской задолженности по месяцам, в третьей – среднее значение по году, вычисленное по формуле =СРЗНАЧ($B$2:$B$13), в четвертой – нижняя граница, соответствующая значениям на s меньше среднего =C2-СТАНДОТКЛОН($B$2:$B$13), в пятой – верхняя граница, соответствующая значениям на s больше среднего =C2+СТАНДОТКЛОН($B$2:$B$13).
Примечание. Чтобы отразить данные в миллионах рублей, я использовал специальный (пользовательский) формат:
Обратите внимание, что после # ##0,0 имеются два пробела.
Строим стандартный график:
Форматируем линии: убираем маркеры на опорных линиях, меняем цвет и тип линий, убираем линию на основных данных. Удаляем линии сетки, чтобы наши контрольные линии лучше выделялись. Добавляем релевантный заголовок нашему графику:
В принципе, график готов. Если ваш эстетический вкус 🙂 не удовлетворен тем, что контрольные линии не касаются границ области построения, можете добиться этого, потратив некоторое время.
Выделите одну из контрольных линий, например, линию среднего, и правой кнопкой мыши вызовите контекстное меню; выберите пункт «Формат ряда данных»:
Поставьте переключатель в положение «По вспомогательной оси»:
Повторите процедуру для линий, обозначающих верхний и нижний коридоры. Должно получиться следующее:
Перейдите на вкладку Макет и пройдите по меню Оси — Вспомогательная горизонтальная ось — Слева направо
Выделите вспомогательную горизонтальную ось и переключите «Положение оси» в позицию «по делениям»:
Диаграмма должна выглядеть так:
Выделите и удалите вспомогательную вертикальную ось. При этом контрольные линии расположатся в соответствии с основной вертикальной осью. А вот горизонтальную вспомогательную ось удалить нельзя, так как настройки контрольных линий (вспомогательная горизонтальная ось в положении «по делениям») пропадут. Поэтому нужно выделить горизонтальную вспомогательную ось и отключить основные деления и подписи оси:
Вот что у нас получилось:
Небольшое домашнее задание. Попробуйте сделать невидимой вспомогательную горизонтальную ось.