Как создать диаграмму панели в Excel

В этом руководстве будет показано, как создать панельную диаграмму во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

Панельная диаграмма (также называемая решетчатой ​​диаграммой или небольшой кратной) - это набор похожих меньших диаграмм, сравниваемых рядом и разделенных разделителями. Поскольку эти мини-диаграммы имеют одни и те же оси и измеряются в одном масштабе, по сути, панельная диаграмма объединяет их все в одном месте.

Диаграмма не только позволяет аккуратно отображать больше информации, но также помогает быстро сравнивать или анализировать взаимосвязь между несколькими наборами данных одновременно, экономя при этом много места на панели инструментов.

В качестве примера взгляните на панельную диаграмму ниже, которая объединяет четыре отдельных линейных диаграммы, сравнивающих годовой доход франчайзи Orange Ltd и Banana Ltd в четырех штатах за последние шесть лет.

Как видите, панельная диаграмма иллюстрирует бездонный колодец полезных данных, в отличие от беспорядочного набора строк, который вы в конечном итоге получили бы, если бы просто выбрали его встроенный аналог.

К сожалению, этот тип диаграммы не поддерживается в Excel, а это значит, что вам придется вручную построить его самостоятельно. Но прежде чем мы начнем, ознакомьтесь с надстройкой Chart Creator, универсальным инструментом для создания расширенных диаграмм и графиков Excel всего за несколько щелчков мышью.

В этом руководстве вы узнаете, как с нуля построить настраиваемую панельную диаграмму в Excel.

Начиная

Чтобы проиллюстрировать шаги, которым вы должны следовать, нам нужно начать с некоторых данных. Как вы, возможно, уже догадались, мы собираемся сравнить исторический послужной список франчайзи двух компаний, Orange Ltd и Banana Ltd, в четырех разных штатах - Айове, Миннесоте, Техасе и Юте.

С учетом всего сказанного, рассмотрите следующую таблицу:

Давайте рассмотрим каждую колонку более подробно.

• Состояние - В этом столбце представлены категории, по которым диаграмма будет разделена на более мелкие диаграммы (панели). В нашем случае каждая мини-линейная диаграмма иллюстрирует динамику производительности для каждого из состояний.
• Год - Этот столбец определяет масштаб горизонтальной оси. Значения и форматирование должны быть одинаковыми на всех панелях.
• Orange Ltd и Banana Ltd - Это ваши настоящие ценности. Мы собираемся использовать только два набора данных для иллюстративных целей, но для метода, показанного в этом уроке, нет предела.

В конце концов, мы хотим, чтобы наши данные выглядели так:

Технически вы можете манипулировать данными вручную (если это так, перейдите к Шаг # 5). Этот подход может быть предпочтительнее, если ваши данные просты. В противном случае мы покажем вам, как использовать сводную таблицу для преобразования данных в необходимый формат.

Итак, приступим.

Шаг №1: Добавьте разделители.

Прежде чем вы сможете создать панельную диаграмму, вам необходимо правильно организовать свои данные.

Во-первых, справа от ваших фактических данных (столбец E), настройте вспомогательный столбец под названием «Разделитель.Цель этого столбца - разделить данные на две чередующиеся категории, выраженные значениями 1 и 2, чтобы заложить основу для будущей сводной таблицы.

Тип "1”В каждую соответствующую ячейку столбца Разделитель принадлежащих к первой категории, Айова (E2: E7). Затем введите «2”Во все соответствующие ячейки, которые попадают во вторую категорию, Миннесота (E8: E13).

Таким же образом заполните оставшиеся пустые ячейки в столбце, чередуя значения двух разделителей при переходе от состояния к состоянию.

Шаг № 2: Добавьте сводную таблицу.

Как только вы закончите Шаг 1, создайте сводную таблицу.

1. Выделите любую ячейку в диапазоне набора данных (A1: E25).
2. Перейти к Вставлять таб.
3. Выбирать "Сводная таблица.”

Когда Создать сводную таблицу появится диалоговое окно, выберите «Существующий рабочий лист,»Выделите любую пустую ячейку рядом с вашими фактическими данными (G1) и нажмите «OK.”

Шаг № 3: Разработайте макет сводной таблицы.

Сразу после создания сводной таблицы Поля сводной таблицы появится панель задач. На этой панели задач переместите элементы в списке полей в следующем порядке (порядок важен), чтобы изменить макет вашей новой сводной таблицы:

1. Двигаться "Состояние" а также "Год" к "Рядов.”
2. Двигаться "Разделитель" к "Колонны.”
3. Двигаться "Orange Ltd" а также "Banana Ltd" к "Ценности.”

На этом этапе ваша сводная таблица должна выглядеть примерно так:

Теперь измените тип макета и удалите лишние элементы таблицы.

1. Выберите любую ячейку в сводной таблице (G4: L26).
2. Перейдите к Дизайн таб.
3. Нажмите «Макет отчета.”
4. Выбирать "Показать в табличной форме.”

Все еще в Дизайн вкладку, щелкните значок «Общие итоги"Значок" и выберите "Отключено для строк и столбцов»Из раскрывающегося меню.

Затем нажмите «Промежуточные итоги»И выберите«Не отображать промежуточные итоги.”

После того, как вы выполнили все шаги, ваша сводная таблица должна быть отформатирована следующим образом:

Шаг №4: Извлеките данные из сводной таблицы.

Поскольку вы не хотите строить сводную диаграмму (нет, не хотите), пора отделить данные от сводной таблицы. Скопируйте все значения из сводной таблицы (G4: L27) в пустые ячейки рядом с ним. (Пустой столбец между наборами данных поможет избежать путаницы.)

Строка заголовка (P3: S3) вашей вновь созданной таблицы отвечает за отображение элементов легенды диаграммы, поэтому давайте также быстро их добавим.

Поскольку сводная таблица эффективно разделяет данные на две квазилинейные диаграммы, строка заголовка должна выглядеть следующим образом:

Шаг № 5: Создайте линейный график.

Наконец, постройте обычный линейный график и посмотрите, что произойдет.

1. Выделите таблицу, содержащую данные, извлеченные из сводной таблицы (N3: S27).
2. Перейти к Вставлять таб.
3. Щелкните значок «Вставить линейную диаграмму или диаграмму с областями" кнопка.
4. Выбирать "Линия.”

Шаг № 6: Сделайте цвета линий согласованными.

Как видите, Excel создал линейную диаграмму с четырьмя различными рядами данных. Унифицируйте эти данные, используя одинаковые цвета для каждой компании.

Щелкните правой кнопкой мыши любую серию данных и выберите «Форматировать ряд данных.”

в Форматировать ряд данных область задач, сделайте следующее:

1. Переключитесь на Заливка и линия таб.
2. Под "Линия," Выбрать "Сплошная линия.”
3. Щелкните значок «Цвет контура»И выберите свой цвет из палитры. (Повторите для каждой строки.)
4. Дважды проверьте легенду диаграммы, чтобы убедиться, что вы правильно подобрали цвета.

Шаг № 7: Создайте разделительные линии.

Поставленная задача может показаться чем-то особенным, но это не так просто, как кажется. Да, вы можете просто рисовать линии с помощью встроенных фигур, но они будут теряться каждый раз, когда вы изменяете размер диаграммы.

Итак, мы собираемся использовать вместо этого вертикальные полосы ошибок, которые будут располагаться между каждой панелью, прямо там, где ось Y пересекает ось категорий.

Однако, чтобы обойти эту проблему, вам придется преодолеть несколько препятствий. Во-первых, давайте создадим данные вспомогательной диаграммы:

1. Установите отдельный фиктивный стол точно так же как вы видите на скриншоте ниже. Покинуть ячейку U4 пусто, чтобы все работало, и введите "Разделители" в V4.
2. Тип "0" в V5 и скопируйте его. Количество строк, содержащих значения в таблице, показывает, сколько полос погрешностей будет создано в конечном итоге. В этом случае нам нужно три планки погрешностей, поэтому будет три ячейки со знаком «0" в них.
3. Введите “=СЧЕТ (O4: O9) +0,5" в U5.

Формула вычисляет количество значений шкалы оси X, используемых для одной панели, и вычисляет ее среднюю точку, чтобы расположить линии на оси X прямо там, где встречаются две оси.

Теперь введите «=U5 + COUNTA (O4: O9) » в U6 и скопируйте его в U7.

После того, как вы настроили вспомогательную таблицу, вставьте эти данные в свою диаграмму панели.

1. Выделите все значения в вспомогательной таблице, кроме строки заголовка (U4: V7).
2. Выберите область диаграммы.
3. Перейти к Дом таб.
4. Нажмите «Вставить.”
5. Выбирать "Специальная вставка.”

В появившемся диалоговом окне выполните несколько простых шагов:

1. Под "Добавить ячейки как," Выбрать "Новая серия.”
2. Под "Значения (Y) в," выбирать "Колонны.”
3. Проверить "Имена серий в первой строке" а также "Категории (метки X) в первом столбце»Коробки.
4. Нажмите «OK.”

Шаг № 8: Измените тип диаграммы фиктивного ряда.

Щелкните правой кнопкой мыши недавно добавленную серию данных (Серия «Разделители») и выберите «Изменить тип диаграммы серии.”

Для Серия «Разделители», изменение «Тип диаграммы» к "Скаттер с прямыми линиями.”

Шаг № 9: Измените второстепенные оси.

Сначала удалите вторичную горизонтальную ось. Щелкните правой кнопкой мыши числа в верхней части диаграммы и выберите «Удалить.”

Затем щелкните правой кнопкой мыши дополнительную вертикальную ось в правой части диаграммы и выберите «Ось формата.”

в Ось формата панель задач, высеченная в камне диапазоны шкалы осей:

1. Переключитесь на Параметры оси таб.
2. Изменить Минимальные границы значение "0.”
3. Установить Максимальные границы значение "1.”

На той же вкладке прокрутите вниз до Этикетки раздел и измените "Положение ярлыка" к "Никто», Чтобы скрыть масштаб оси - если вы просто удалите его, это испортит график.

Шаг № 10: Добавьте планки погрешностей.

Фундамент уже заложен, а это значит, что вы, наконец, можете добавить планки погрешностей.

1. Выбирать Серия «Разделители».
2. Щелкните значок Элементы диаграммы значок.
3. Щелкните стрелку рядом с «Полосы ошибок».
4. Выбирать "Больше опций.”

в Полосы ошибок форматирования панель задач, измените разделители.

1. Переключитесь на Параметры панели ошибок таб.
2. Под "Направление," выбирать "Плюс.”
3. Под "Конец стиля," Выбрать "Нет крышки.”
4. Под "Сумма ошибки," изменение "Фиксированная стоимость" к "1.”

Шаг № 11: Скройте вспомогательные элементы диаграммы.

В качестве последнего шага удалите нижележащую вторичную диаграмму рассеяния, горизонтальные полосы ошибок и повторяющиеся элементы легенды.

Давайте сначала разберемся с фиктивной серией. Выбирать Серия «Разделители», перейдите к Форматировать ряд данных панель задач и в Варианты серий вкладка, выберите «Нет линии.”

Теперь удалите горизонтальные полосы ошибок и все элементы легенды, за исключением «Orange Ltd" а также "Banana Ltd», Выбирая каждый элемент, щелкая правой кнопкой мыши и выбирая«Удалить. » К концу этого шага диаграмма вашей панели должна выглядеть так:

Измените заголовок диаграммы, и у вас будет потрясающая панель-диаграмма, готовая к работе!