Ранее я показал, как добавить линию на график. Аналогично может возникнуть потребность в добавлении контрольной (опорной) линии на гистограмму. Например, описывая распределение объема продаж по диапазонам прибыльности сделок, захочется добавить линию, показывающую предельно допустимую прибыльность (рис. 1):
Рис 1. Распределение выручки (по оси ординат) по диапазонам нормы валовой прибыли (по оси абсцисс).
Скачать заметку в формате Word, примеры в формате Excel
На рис.1 я провел эту линию путем вставки фигуры (линии):
Сейчас я покажу, как это сделать средствами построения диаграммы. Идея подсмотрена в книге:
Откроем пример в файле Excel. Наша задача — проанализировать платежную дисциплину клиента. Клиенту отгружают товары по накладным с отсрочкой платежа 30 дней. То есть, клиент должен оплатить не позднее тридцатого дня после отгрузки. Данные в файле представляют собой перечень накладных, с указанием на какой день они реально были оплачены клиентом:
Обобщим данные в сводной таблице
Представим данные в сводной таблице в виде долей. (Так мы сможем сравнивать разных клиентов, у которых абсолютное число накладных колеблется.) Для этого встанем на любую клеточку в столбце сводной таблицы «Количество по полю накладная», кликнем правой кнопкой мыши, выберем в контекстном меню пункт «Параметры полей значений». В окне «Параметры поля значений» перейдем на вкладку «Дополнительные вычисления» и выберем «Доля от суммы по столбцу»:
Скопируем данные из сводной таблицу в обычную таблицу (к сожалению манипуляции, которые мы хотим выполнить далее, невозможны в отношении сводной таблицы). Построим стандартную гистограмму на основе обычной таблицы и отформатируем её, как показано ниже. Важно! Вертикальная ось не удалена, а сделана невидимой. Для этого в формате оси выбран «Цвет линии оси» — «Нет линии», «Основные деления» — «Нет», «Подписи оси» — «Нет»:
Проведем вертикальную линию, отделяющую столбцы гистограммы, относящиеся к своевременной оплате — по 30-й день включительно. Для этого создадим дополнительный набор данных, смысл которых станет понятен чуть позже. Скопируйте в буфер памяти таблицу (ячейки L27:M29), выделите диаграмму и выберите «Специальная вставка»:
В появившемся окне установите «Добавить значение как» в положение «новые ряды», «Значения Y» в положение «в столбцах»; поставьте галочки в «Имена рядов в первой строке» и «Категории (подписи оси Х) в первом столбце:
Должно получиться так:
Выделите синий столбик и измените тип диаграммы для ряда на точечную без маркеров:
На диаграмме появились две вспомогательные оси: вертикальная и горизонтальная. Задайте для обеих минимальное и максимальное значения ноль и 100, а в формате осей выберите «Цвет линии» — «Нет линии», «Основные деления» — «Нет», «Подписи оси» — «Нет». Вот, что в итоге получилось:
Пояснение. Откуда взялись данные для построения контрольной линии. В точечных диаграммах использование значений 0–100 позволяет задавать расположение линии в процентах. В рассмотренном примере, значение 37,5 (расположенное в ячейках L28:L29) означает, что линия размещается в точке (по оси абсцисс), представляющей 37,5% длины оси категорий. Поскольку в нашей гистограмме было 8 столбцов, для отделения первых трех, линию нужно расположить на расстоянии 3/8 (или 37,5%) от левого края… ?
