Как создать сгруппированную стопку столбцов/гистограмму в Excel?

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

Создание сгруппированной стопки столбцов в Excel

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

✅ Что нужно знать сначала:

• Excel не предлагает встроенного типа «сгруппированная стопка столбцов». Результат достигается за счет хитростей в раскладке данных.
• Вы должны реструктурировать исходные данные для моделирования группировки кластеров.
• Пустые строки добавляются между группами категорий для визуального разделения каждого кластера.

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

1. Организуйте ваши сырые данные: В этом примере у нас есть названия продуктов в столбце A и данные о продажах (например, фактические против целевых для Q1 и Q2) в соседних столбцах. Цель состоит в том, чтобы сгруппировать данные каждого продукта бок о бок и показать фактические/целевые значения внутри каждой группы.

2. Переформатируйте данные: Вам необходимо скопировать каждую группу данных (например, каждую строку продукта) в новый макет и вставить пустую строку между каждой группой. Это поможет Excel интерпретировать каждую группу как отдельный кластер в стековой столбчатой диаграмме.

3. Создайте диаграмму: Выберите ваши новоиспеченные структурированные данные. Затем перейдите на вкладку Вставка > Столбчатая или Гистограмма > Стековая колонка.

4. Настройте серию: Щелкните правой кнопкой мыши любой столбец на диаграмме, выберите Формат ряда данных.

5. Уменьшите ширину зазора: В панели Формат ряда данных, перейдите к Параметры ряда и установите Ширина зазора = 0% для визуального сжатия каждой группы в одну стековую группу.

6. Настройте легенду и макет: Щелкните правой кнопкой мыши легенду > Формат легенды.

7. Выберите положение легенды: В панели Формат легенды, в разделе Параметры легенды, выберите предпочтительное положение легенды (справа, сверху, слева или снизу), чтобы лучше соответствовать вашей диаграмме и избежать перекрытия с данными.

✅ Результат: Теперь у вас есть сгруппированная стопка столбцов, где фактические/целевые данные каждого продукта сгруппированы и расположены бок о бок для быстрого сравнения.

⚠️ Ограничение: Этот метод хорошо работает для небольших наборов данных. Но для больших наборов данных или часто меняющихся данных ручная реструктуризация может быть подвержена ошибкам. Смотрите следующие разделы для автоматизации этого процесса с помощью VBA и формул.

Код VBA – Автоматизация реструктуризации данных и создания диаграмм

При работе с большими наборами данных или часто изменяемыми отчетами, ручная перестройка данных для создания сгруппированных стековых диаграмм может быть утомительной и подверженной ошибкам. Использование VBA (Visual Basic for Applications) позволяет автоматизировать весь процесс — от реструктуризации исходной таблицы до вставки диаграммы, экономя ваше время и усилия.

Этот метод наиболее подходит для пользователей, знакомых с макросами Excel или средами, где разрешено использование VBA. Особенно он эффективен, если структура диаграммы остается постоянной, но данные обновляются регулярно. Вот как это реализовать:

Шаг 1: Нажмите Alt + F11, чтобы открыть редактор VBA. В редакторе нажмите Вставка > Модуль.

Шаг 2: Вставьте следующий код VBA в окно модуля:

Sub CreateStackedClusteredChart()
    Dim ws As Worksheet
    Dim rngData As Range
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim xTitleId As String

    On Error Resume Next
    Set ws = ActiveSheet
    xTitleId = "KutoolsforExcel"

    ' Prompt user to select original data
    Set rngData = Application.InputBox("Select the original grouped data (including all headers):", xTitleId, Selection.Address, Type:=8)
    If rngData Is Nothing Then Exit Sub

    ' Create new worksheet for reshaped data
    Dim wsChartData As Worksheet
    Set wsChartData = Worksheets.Add
    wsChartData.Name = "ChartData_" & Format(Now(), "hhmmss")

    Dim numRows As Long, numCols As Long, i As Long, j As Long, outRow As Long
    numRows = rngData.Rows.Count
    numCols = rngData.Columns.Count
    outRow = 1

    ' Add headers
    wsChartData.Cells(outRow, 1).Value = "Category"
    For j = 2 To numCols
        wsChartData.Cells(outRow, j).Value = rngData.Cells(1, j).Value
    Next j
    outRow = outRow + 1

    ' Copy data and insert blank rows
    For i = 2 To numRows
        For j = 1 To numCols
            wsChartData.Cells(outRow, j).Value = rngData.Cells(i, j).Value
        Next j
        outRow = outRow + 1
        If i < numRows Then
            wsChartData.Cells(outRow, 1).Value = ""
            outRow = outRow + 1
        End If
    Next i

    ' Define chart data range
    Set chartRange = wsChartData.Range(wsChartData.Cells(1, 1), wsChartData.Cells(outRow - 1, numCols))

    ' Insert chart
    Set chartObj = wsChartData.ChartObjects.Add(Left:=100, Top:=30, Width:=500, Height:=350)
    With chartObj.Chart
        .SetSourceData Source:=chartRange
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "Stacked Clustered Column Chart"
        .Legend.Position = xlLegendPositionRight
        .ChartGroups(1).GapWidth = 0
    End With

    MsgBox "Chart generated successfully.", vbInformation, "KutoolsforExcel"
End Sub

Шаг 3: Нажмите Alt + F8, чтобы открыть диалоговое окно Макросы. Выберите CreateStackedClusteredChart и нажмите Выполнить.

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

📝 Советы:

• Убедитесь, что ваша исходная таблица имеет согласованные заголовки столбцов и форматирование.
• Вы можете повторно запускать макрос каждый раз, когда обновляется ваш набор данных — нет необходимости повторять ручные шаги.

✅ Преимущества: Экономит время, точный макет, идеально подходит для регулярных отчетов.
⚠️ Недостатки: Требует Excel с включенными макросами и базовое знание VBA.

Формулы Excel – Динамическая реструктуризация данных для сгруппированных стековых диаграмм

Если вы предпочитаете не использовать VBA или нуждаетесь в решении, которое позволяет динамически обновлять диаграммы при изменении исходных данных, формулы могут использоваться для изменения структуры исходных данных в правильный макет для сгруппированных стековых диаграмм. Используя встроенные функции, такие как ИНДЕКС, ТРАНСПОНИРОВАТЬ и вспомогательные столбцы, вы можете настроить область преобразования данных, которая всегда доставляет правильную структуру для вашей диаграммы с минимальными усилиями.

Этот подход особенно практичен, когда ваши исходные данные регулярно обновляются (новые периоды, категории и т.д.), и вы хотите, чтобы ваша диаграмма автоматически корректировалась без ручной реструктуризации. Основное требование заключается в создании «вспомогательного» раздела, который извлекает и организует блоки данных и пустые строки с помощью формул, так что источник ваших данных всегда актуален.

Вот пример того, как вы могли бы это настроить:

• Предположим, ваши исходные данные находятся в A1:D7 (где A1 - верхний левый заголовок), структурированы как регион/категория в столбце A и подкатегории (например, Q1, Q2, Q3) в столбцах B, C, D.
• Вы хотите отобразить каждую категорию как кластер со стековыми Q-значениями, используя пустые строки для разделения кластеров.

1. В новом листе или рядом области создайте вспомогательную структуру для извлечения каждой группы и вставки пустых строк. Например, чтобы скопировать первую строку данных в E2:G2:

=INDEX($A$2:$D$7,INT((ROW()-2)/2)+1,COLUMN()-4+1)

Протяните эту формулу вниз по мере необходимости. Чтобы вставить пустые строки между группами, установите формулу ЕСЛИ для возврата пустого значения ("") на альтернативных строках:

=IF(ISODD(ROW()), "", INDEX($A$2:$D$7,ROW()/2,COLUMN()-4+1))

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

2. После завершения вашего диапазона преобразования (со стеками и кластерами), выберите этот новый диапазон и создайте свою стековую столбчатую диаграмму, следуя исходному методу, указанному ранее (Вставка > Стековая колонка). Диаграмма теперь будет автоматически отражать любые изменения, которые вы внесете в исходную таблицу данных.

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

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

Устранение неполадок: Если ваша диаграмма не обновляется правильно, дважды проверьте наличие ошибок ссылок или несоответствий во вспомогательных формулах. Убедитесь, что пустые строки правильно вставлены, так как они являются ключом к достижению «сгруппированного» вида.

Больше статей о диаграммах:

---

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

• Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
• Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
• Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
• Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
• Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
• Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
• Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести... 

---

Office Tab - добавляет интерфейс с вкладками в Office и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
• Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести...