Excel — сводная таблица из нескольких таблиц с одинаковыми столбцами

Question

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

Я поместил их в Excel в 2 таблицы, вот так:

Table 1 from source1

Year    Month    InfoType    Source    Cost
2019     01       Type1      source1    20
...

Источником является формула, которая выглядит следующим образом: ="source1"

Table 2 from source2

Year    Month    InfoType    Source    Cost
2019     01       TypeD2     source2    10
...

Источником является формула, которая выглядит следующим образом: ="source2"

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

Rows          Sum of Cost
2019 
    source1       20
    source2       10
Total 2019        30

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

Я изучал отношения, но, похоже, они ведут себя подобно SQL Join, а это не то, чего я пытаюсь достичь здесь. Я начал изучать power pivot, но не смог найти ничего, что помогло бы мне достичь этой цели.

Я буду признателен за любую помощь.

Answer 1

Описанную выше идею можно реализовать с помощью Power Query.

Сначала вам нужно создать 2 объекта «Таблица», используя ваши данные, выбрав их и нажав «Форматировать как таблицу» или используя сочетание клавиш CTRL+T.

Затем вы можете открыть пустой запрос, нажав «Данные», затем «Извлечь данные», затем «Различные источники», а затем «Пустой запрос».

Должно открыться следующее окно:

Там вы добавляете следующую формулу в поле Текст:

=Excel.CurrentWorkbook()

После нажатия Enter вы должны увидеть что-то вроде этого:

Там вы можете нажать на кнопку рядом с «Содержимым» и снять флажок внизу, затем нажать «ОК».

Теперь вы должны увидеть таблицу:

Фактически это объединенная таблица всех таблиц в вашей рабочей книге. Поэтому, если вы добавите еще одну таблицу с таким же макетом, она будет автоматически добавлена ​​в эту таблицу.

Теперь вы можете нажать на раскрывающийся список под «закрыть и загрузить», а затем нажать «закрыть и загрузить в...»

Вы должны увидеть всплывающее окно с четырьмя вариантами:

Table
PivotTable-Report
Pivot Chart
Connection only

Там вы можете выбрать PivotTable-Report и нажать OK.

Это должно создать сводную таблицу. Туда вы можете добавить поля, которые хотите отобразить, и в итоге у вас должно получиться что-то вроде этого:

Answer 2

Очень полезно! Я бы добавил, что вам нужно убедиться, что ваши данные в правильном формате, прежде чем нажимать «закрыть» и «загрузить». Например, если вашей сводной таблице понадобится какая-либо метрика, кроме «Количество», вам нужно убедиться, что в используемых вами столбцах нет данных текстового типа ДО загрузки в сводную таблицу. SUM, AVG и т. д. требуют нетекстовых типов данных.