Как создать сводную таблицу Excel из таблицы с иерархическими столбцами?

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

• Выберите стол
• нажмите F5 или используйте Ctrl G или Goto
• нажмите Специальный
• Отметьте «Пробелы» и нажмите «ОК».
• введите знак = и нажмите стрелку вверх. Это создает формулу, которая ссылается на ячейку выше.
• удерживайте клавишу Ctrl и нажмите Enter. Теперь эта формула находится во всех ранее пустых ячейках.
• Скопируйте таблицу еще раз и вставьте ее в качестве значений.

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

Теперь вы можете построить сводную таблицу на основе этой исходной таблицы, суммировать часы и агрегировать по Epic.

В наши дни существует множество способов достижения желаемых результатов. Если вы используете Modern Versions Excel, один из таких способов — использование Power Query или новых функций Excel, что делает его относительно простым.

• Использование GROUPBY(), если применимо -> работает с MS365 Exclusively^{Beta Version}

• Формула, используемая в ячейке G1

=VSTACK({"Code","Epic","Hours"},
 GROUPBY(HSTACK(TEXTBEFORE(A2:A10&".","."),
 SCAN(,B2:B10,LAMBDA(x,y,IF(y = "",x,y)))),E2:E10,SUM,,0))

Или, если у кого-то нет доступа к GROUPBY() во время написания формулы, необходимо включить Office Insiders, если вы не хотите включать, то можно также попробовать следующее:

=LET(
     _Data, A2:E10,
     _Code, TEXTBEFORE(TAKE(_Data,,1)&".","."),
     _Epic, SCAN(,INDEX(_Data,,2),LAMBDA(x,y,IF(y = "",x,y))),
     _Uniq, UNIQUE(HSTACK(_Code,_Epic)),
     HSTACK(_Uniq, TAKE(MAP(_Uniq,LAMBDA(x,SUM(--(x=HSTACK(_Code,_Epic))*TAKE(_Data,,-1)))),,1)))

• Или используйте POWER QUERY, доступный в Windows Excel 2010+ и Excel 365 (Windows) --> Это однократный подход, поэтому вам не нужно увеличивать диапазон или диапазоны, только при добавлении новых данных в исходный источник необходимо обновить импортированную таблицу, и она обновится. в пределах нескольких.

Чтобы использовать Power Query, выполните следующие действия:

• Сначала преобразуйте исходный диапазон в таблицу и назовите ее соответствующим образом. Для этого примера я назвал ее Table1.

• Затем откройте пустой запрос на вкладке Data ​​--> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query.

• Приведенное выше позволяет открыть окно Power Query, теперь из вкладки Home --> Advanced Editor --> И вставьте следующее M-Code, удалив все, что вы видите, и нажмите Done

let
    Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Epic"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filled Down", {{"Code", each Text.BeforeDelimiter(Text.From(_, "en-US"), "."), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Code", "Epic"}, {{"Hours", each List.Sum([Hour]), type nullable number}})
in
    #"Grouped Rows"

• Наконец, чтобы импортировать его обратно в Excel --> Нажмите Close & Load или Close & Load To --> Первый клик создаст New Sheet с требуемым результатом, а второй предложит вам окно с вопросом, куда поместить результат.