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

Question

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

Желаемый результат - столбчатая диаграмма с осью 2 Y. Set1 и Set2 должны быть нанесены на Вторичную Ось, в то время как Set3, Set4 и Set5 должны быть нанесены на Первичную Ось.

Чтобы сделать это, я создал диаграмму столбцов после выбора данных, затем щелкнул правой кнопкой мыши по столбцам Set1 и Set2 и выбрал Secondary Axis из опций. В результате получился очень запутанный график со столбцами, наложенными друг на друга, как видно на скриншоте ниже.

Есть ли хитрость для построения графика данных, как описано выше, но без того, чтобы столбцы накладывались друг на друга?

Answer 1

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

Начните с создания кластеризованной столбчатой диаграммы (верхняя диаграмма ниже).

Переместите последние три серии на вторичную ось (средняя диаграмма ниже).

Отрегулируйте основную ось так, чтобы ее минимум оставался неизменным, но ее новый максимум был в два раза больше его старого максимума: это означает, что масштаб изменяется с {0 до 6000} на {0 до 12000}. Измените вторичную ось так, чтобы ее максимум не изменился, а ее новый минимум отрицателен по отношению к ее старому максимуму: масштаб изменяется от {0 до 10} до {-10 до 10}. (нижняя диаграмма ниже)

Используйте пользовательский числовой формат [<=6000]0;;; для основной оси, которая показывает только значения, меньшие или равные 6000. Используйте пользовательский числовой формат 0;;0; для вторичной оси, которая показывает только положительные и нулевые значения. (верхний график ниже)

Наконец, растяните график, чтобы его было легче читать (нижний график ниже).

Answer 2

Самый простой способ сделать это - создать дополнительные "вспомогательные" столбцы, чтобы освободить пространство для ваших данных. Так как Excel обрабатывает ось столбцов диаграммы Х как категориальные данные, вам потребуется одинаковое количество столбцов для каждой оси (в вашем примере). Тогда у вас есть 0 (или # N/A) значений в ваших столбцах помощника, чтобы создать визуальные пробелы, и ваши фактические значения могут просвечивать. Поэтому добавьте 3 вспомогательных столбца для вспомогательной оси с 0 значениями и добавьте 2 вспомогательных столбца к основной оси с 0 значениями. В итоге вы получите 5 перекрывающихся значений на графике, при этом значения 0 будут соответствовать положению других значений оси Y.

Вот пример: