Вопрос или проблема
У меня есть таблица, с левой стороны таблицы есть около 10 колонок справочной информации, а затем, двигаясь вправо, каждый месяц имеет около четырех колонок данных.
Я хочу преобразовать это так, чтобы все данные были в одной таблице, чтобы я мог провести дальнейший анализ.
Начиная с таблицы ниже
Сгенерировать этот результат
Я хотел бы увидеть формулу Excel или запрос Power Query / M, который может это сделать, я не ищу макрос.
Вот один из способов достижения желаемого результата с использованием Power Query
Чтобы использовать Power Query, выполните следующие шаги:
- Сначала преобразуйте источники диапазонов в таблицу и назовите ее соответствующим образом, в этом примере я назвал ее
Table1и не забудьте снять отметкуУ моей таблицы есть заголовки
- Далее откройте пустой запрос из вкладки Данные –> Получить и преобразовать данные –> Получить данные –> Из других источников –> Пустой запрос
- Это откроет окно Power Query, теперь в вкладке Главная –> Расширенный редактор –> и вставьте следующий M-Code, удалив все, что вы видите, и нажмите Готово
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
First2Rows = Table.FirstN(Source,2),
Rows2Columns = Table.Transpose(First2Rows),
FillDown = Table.FillDown(Rows2Columns,{"Column1"}),
Merged = Table.CombineColumns(FillDown,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
Columns2Rows = Table.Transpose(Merged),
Combined = Table.Combine({Columns2Rows,Table.RemoveFirstN(Source,2)}),
PromotedHeaders = Table.PromoteHeaders(Combined, [PromoteAllScalars=true]),
AddedIndex = Table.AddIndexColumn(PromotedHeaders, "Index", 1, 1, Int64.Type),
UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"Reference|ID", "Reference|NAME", "Reference|ROLE", "Index"}, "MONTH", "Value"),
SplitColumnbyDelimiter = Table.SplitColumn(UnpivotedOtherColumns, "MONTH", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"MONTH.1", "MONTH.2"}),
PivotedColumn = Table.Pivot(SplitColumnbyDelimiter, List.Distinct(SplitColumnbyDelimiter[MONTH.2]), "MONTH.2", "Value"),
Sort = Table.Sort(PivotedColumn,{{"MONTH.1", Order.Ascending}, {"Index", Order.Ascending}}),
RemovedCol = Table.RemoveColumns(Sort,{"Index"}),
#"Renamed Columns" = Table.RenameColumns(RemovedCol,{{"Reference|ID", "ID"}, {"Reference|NAME", "NAME"}, {"Reference|ROLE", "ROLE"}, {"MONTH.1", "MONTH"}})
in
#"Renamed Columns"
- Наконец, чтобы импортировать это обратно в Excel –> нажмите на Закрыть и загрузить или Закрыть и загрузить в –> Первый вариант, на который вы нажмете, создаст Новый лист с нужным результатом, в то время как второй запросит окно, в котором вам будет предложено указать, куда разместить результат.
Ответ или решение
Чтобы преобразовать финансовые данные, представленные в виде ежемесячных таблиц, в единый упорядоченный формат в Excel, вы можете воспользоваться Power Query — мощным инструментом для обработки данных. В данном руководстве будет представлена пошаговая инструкция, как это сделать, а также соответствующий M-код для Power Query.
Шаги по преобразованию данных с использованием Power Query
-
Подготовьте исходные данные:
- Убедитесь, что ваши данные представлены в виде таблицы в Excel. Назовите эту таблицу, например,
Table1. При создании таблицы убедитесь, что опция "Моя таблица имеет заголовки" отключена.
- Убедитесь, что ваши данные представлены в виде таблицы в Excel. Назовите эту таблицу, например,
-
Открытие Power Query:
- Перейдите на вкладку Данные в Excel. Нажмите на Получить данные (Get Data) -> Из других источников (From Other Sources) -> Пустой запрос (Blank Query). Это откроет редактор Power Query.
-
Ввод M-кода:
- В редакторе Power Query выберите Главная (Home) -> Дополнительный редактор (Advanced Editor). Удалите любой существующий код и вставьте следующий M-код:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], First2Rows = Table.FirstN(Source,2), Rows2Columns = Table.Transpose(First2Rows), FillDown = Table.FillDown(Rows2Columns,{"Column1"}), Merged = Table.CombineColumns(FillDown,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"), Columns2Rows = Table.Transpose(Merged), Combined = Table.Combine({Columns2Rows,Table.RemoveFirstN(Source,2)}), PromotedHeaders = Table.PromoteHeaders(Combined, [PromoteAllScalars=true]), AddedIndex = Table.AddIndexColumn(PromotedHeaders, "Index", 1, 1, Int64.Type), UnpivotedOtherColumns = Table.UnpivotOtherColumns(AddedIndex, {"Reference|ID", "Reference|NAME", "Reference|ROLE", "Index"}, "MONTH", "Value"), SplitColumnbyDelimiter = Table.SplitColumn(UnpivotedOtherColumns, "MONTH", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"MONTH.1", "MONTH.2"}), PivotedColumn = Table.Pivot(SplitColumnbyDelimiter, List.Distinct(SplitColumnbyDelimiter[MONTH.2]), "MONTH.2", "Value"), Sort = Table.Sort(PivotedColumn,{{"MONTH.1", Order.Ascending}, {"Index", Order.Ascending}}), RemovedCol = Table.RemoveColumns(Sort,{"Index"}), #"Renamed Columns" = Table.RenameColumns(RemovedCol,{{"Reference|ID", "ID"}, {"Reference|NAME", "NAME"}, {"Reference|ROLE", "ROLE"}, {"MONTH.1", "MONTH"}}) in #"Renamed Columns" -
Загрузка данных в Excel:
- После того, как вы вставили M-код, нажмите Готово (Done) в редакторе. Затем вернитесь в Power Query и нажмите Закрыть и загрузить (Close & Load) или Закрыть и загрузить в (Close & Load To), чтобы выбрать способ загрузки данных обратно в Excel. Вы можете создать новую таблицу на новом листе или выбрать другое место для отображения данных.
Заключение
С помощью Power Query вы можете легко преобразовать данные с ежемесячной разбивкой в более удобный для анализа формат, что позволит вам производить дальнейшую обработку и анализ данных. Указанные шаги обеспечивают операции по транспонированию, объединению, расшированию и своду данных, благодаря чему вы получаете компактный и информативный отчет.
Для профессиональных пользователей Excel важно владеть навыками работы с Power Query, так как этот инструмент значительно упрощает процесс анализа данных и подготовки отчетности. С настойчивостью и практическим опытом, вы сможете освоить Power Query и эффективно работать с накопленными финансовыми данными, извлекая из них ценную информацию.