Как сделать комбинированную диаграмму в Excel: план и факт
Как сделать комбинированную диаграмму в Excel для плана и факта: назначить столбцы и линию, проверить общую шкалу и понять, когда нужна вторая ось значений.
О
Ольга
Основатель малого бизнеса, партнёр
8 мин чтения
На четвёртой неделе мастерская подготовила 33 комплекта для выездных занятий вместо запланированных 30. Отличная цифра для отчёта. Только за все четыре недели получилось 104 комплекта при плане 108. Покажу, как сделать комбинированную диаграмму в Excel, чтобы видеть недельные отклонения и не выдавать удачный финал за выполнение всего плана. Факт станет столбцами, план — линией. И обоим хватит одной шкалы.
Выбираю форму по смыслу двух рядов
Я собрала учебную таблицу. В ней каждый комплект готов к отправке: материалы собраны и проверены. Незаконченные наборы сюда не входят. План означает, сколько таких же готовых комплектов требовалось подготовить к концу соответствующей недели. Поэтому сравнивать два ряда можно напрямую, без пересчёта единиц.
Столбцы показывают отдельные недельные результаты, линия — ориентир, с которым их сравнивают. На второй неделе столбец окажется выше плановой отметки, на третьей — ниже. Линия здесь соединяет четыре плановых значения по порядку недель. Она не означает, что внутри недели мы каждый день выпускали одинаковое количество комплектов.
Я бы выбрала разные варианты в зависимости от вопроса:
Две группы столбцов — если нужно одинаково подробно сравнить два равноправных ряда.
Столбцы факта и линия плана — если результат читается относительно заданной цели.
Две отдельные диаграммы — если показатели имеют разные единицы и наложение мешает понять каждый из них.
Для нашей мастерской подходит второй вариант. При этом комбинированная диаграмма не обязана иметь вторую ось. Комбинация означает разные типы отображения рядов; число шкал выбирается отдельно. Я прежде включала правую шкалу просто потому, что видела её в готовом шаблоне. Сравнение плана с фактом от этого становилось менее понятным.
Четыре недели здесь составляют выбранный отчётный период. Не называю его календарным месяцем: даты начала и окончания в примере не заданы. Если у тебя реальные даты, сначала проверь, что оба ряда относятся к одним и тем же промежуткам времени.
Как сделать комбинированную диаграмму в Excel с общей осью
Дальше — настольный Excel для Windows, версии Microsoft 365, 2024 и 2021. В них есть раздел комбинированных диаграмм. Для старого Excel 2010 эти названия и расположение команд не подходят как точная инструкция: сначала уточни версию программы.
Начни с диапазона A1:C5. В первой строке размести заголовки из нашей таблицы. В A2:A5 введи текстовые подписи «Неделя 1», «Неделя 2», «Неделя 3», «Неделя 4», а рядом — значения плана и факта. Так номера недель не будут ещё одним числовым показателем. Числа комплектов вводи отдельно от единицы: она уже указана в заголовке. Общий итог не включай. Он не является пятой неделей.
Построить нужную комбинацию можно через изменение типа уже созданной диаграммы:
Выдели A1:C5 и на вкладке «Вставка» создай обычную плоскую диаграмму со столбцами.
Щёлкни по диаграмме. Открой вкладку её конструктора (Chart Design).
Выбери изменение типа диаграммы (Change Chart Type).
В окне типов перейди к «Комбинированная» (Combo).
Для ряда «Факт, комплектов» назначь столбцы с группировкой (Clustered Column).
Для ряда «План, комплектов» назначь линию (Line). Вариант с маркерами поможет видеть четыре плановые точки.
Проверь, что флажок вспомогательной оси не установлен ни для плана, ни для факта. Подтверди выбор.
Если готовый вариант предлагает линию на вспомогательной оси, сними соответствующий флажок. В нашем примере обе величины измеряются комплектами, а диапазон значений близкий. Лишняя правая шкала усложнит прямое сравнение: одинаковая высота линии и столбца должна означать одинаковое количество.
Теперь сверь назначение рядов по их названиям. Порядок столбцов исходной таблицы сам по себе не гарантирует нужного оформления. Важен итог: факт отображается столбцами, план — линией. Если получилось наоборот, вернись в окно комбинации и поменяй типы у соответствующих названий.
Через контекстное меню диаграммы открой «Выбрать данные» (Select Data). Должно быть ровно два ряда: план со значениями B2:B5 и названием из B1, факт со значениями C2:C5 и названием из C1. Подписи категорий берутся из A2:A5. Если появился лишний ряд «Неделя», убери именно его из списка рядов диаграммы, сохранив исходные ячейки. Совпадения подписей 1–4 самого по себе недостаточно: проверь состав рядов.
Снизу должны читаться четыре недельные подписи. Если там стоят «План» и «Факт», Excel иначе прочитал строки и столбцы. Команда Switch Row/Column меняет способ их отображения. После неё снова сверь оба диапазона и категории; не заменяй подписи вручную поверх неверных данных.
Назови рисунок «Подготовка комплектов: план и факт за четыре недели». Вертикальную ось подпиши «Комплектов». Для столбцов сохрани нулевое начало шкалы. При максимальном значении 33 удобно оставить верхнюю границу, например 40. Это выбор для наших четырёх строк, а не универсальная настройка любого отчёта.
Не соединяй план плавной декоративной кривой ради красоты. Четыре недельных значения не описывают подробный ход работы между точками. Маркеры и обычные прямые отрезки сохраняют понятную связь с таблицей: одна точка — один недельный план.
Плановая линия у нас поднимается между второй и третьей неделей: цель выросла с 24 до 30 комплектов. Это не ошибка построения и не постоянная горизонтальная норма. Сохрани исходные недельные цели; замена всех четырёх значений одним средним уберёт изменение задания, которое и нужно увидеть рядом с фактом.
Вторая ось: другой показатель, другие правила чтения
Иногда вместо плана в комплектах требуется показать долю проверенных материалов в процентах. Доля сообщает, какая часть материалов прошла проверку; число комплектов описывает объём готового выпуска. Процент нельзя читать по шкале «Комплектов», даже если его числовое значение случайно близко к объёму выпуска.
В таком случае сначала реши, помогает ли совмещение. Если да, в окне Combo у процентного ряда можно поставить флажок Secondary Axis, оставив количество на основной оси. Укажи единицу у каждой шкалы и явно назови ряды. Правую ось читатель должен связывать именно с процентной линией, а не со столбцами.
Но один флажок не делает сравнение корректным. Высота на двух независимо настроенных шкалах имеет разный смысл. Точка пересечения линии и верхушки столбца не доказывает равенство показателей. Тем более она не объясняет, почему один показатель изменился вслед за другим. Для вывода о причинах нужны дополнительные данные.
Я задаю себе вопрос: что читатель должен сравнить одним взглядом? Если каждый раз приходится переводить взгляд с левой шкалы на правую и вспоминать их единицы, лучше разделить показатели на два рисунка с одинаковым порядком недель. В нашей исходной таблице такой трудности нет: план и факт уже измерены одинаково, поэтому вторую ось не включаем.
Разница в величине сама по себе тоже требует решения, а не автоматической правой шкалы. Например, недельный выпуск и накопленный выпуск — оба в комплектах, но смысл рядов различается. Прежде чем совмещать их, обозначь, где значение за отдельный интервал, а где сумма с начала периода.
Проверяю четыре отклонения и один общий результат
После оформления вернись к исходным числам. У меня есть две независимые проверки. Первая — положение каждого столбца относительно своей плановой точки: ниже, выше, ниже, выше. Вторая — арифметика всего периода.
Я проверила четыре отклонения вручную. Считаю каждое как факт минус план:
Неделя 1: 18 − 24 = −6 комплектов.
Неделя 2: 26 − 24 = +2 комплекта.
Неделя 3: 27 − 30 = −3 комплекта.
Неделя 4: 33 − 30 = +3 комплекта.
Я сделала отдельный итог периода. Сумма отклонений равна −4. Она согласуется с итогами 104 − 108 = −4. На последней неделе цель превышена на три комплекта, но этого не хватило, чтобы закрыть недобор всего периода. Вот чего не было видно в моей первой фразе про удачный финал.
Проверь мой план комбинированной диаграммы. За четыре недели план выпуска комплектов: 24, 24, 30, 30; факт: 18, 26, 27, 33. Факт хочу показать столбцами, план линией на одной оси. Проверь единицы, недельные отклонения и итог периода. Составь три вопроса, по которым я проверю готовый рисунок в Excel. Причины изменений и отсутствующие даты не придумывай.
Это предлагаемый запрос для разбора данных. Сам рисунок проверяй в Excel: правильность вычислений в ответе не подтверждает, что выбран нужный диапазон или отключена лишняя ось. Для подготовки исходника пригодится материал о работе нейросети с таблицами; условия регулярной работы можно посмотреть в тарифах LibraChat.
Попробуй прочитать свой рисунок двумя предложениями: что произошло на первой неделе и выполнен ли план всех четырёх. Если получаются ответы «18 из 24» и «не хватает четырёх комплектов», изображение согласуется с нашим исходником. Объяснение причин оставь следующему разговору, для которого понадобятся сведения о самой работе мастерской.
