Excel Power query 提取多个工作簿指定单元格数据

显示全部楼层 · 2020-11-5 23:09:09

平时用VBA实现，每次原始数据模板和提取数据的要求发生变化，只改几下代码就好了。但由于这项统计工作要移交给不会VBA，只会点鼠标的人去做，因此想用power query来做好功能，教给新人。但没想到研究了一个多小时，仍然没搞明白。
特来求助大神，万分感谢！

原始数据：同一文件夹下，命名从1到N的Excel文件。同一次数据处理时，每个Excel文件的模板完全相同。但今天和明天两次数据处理时，所用Excel模板并不相同。今天需要提取的数为（标蓝）：价/数/利/税；明天的模板里，也许就变成要取的数（标蓝）：价/数/利/重/长/高/宽/税
请不要问为什么原始数据这么奇葩，就是这么奇葩。
Excel内的数据：需要提取每个Excel文件中的几个指定单元格内容，到新的Summary表中。
Summary表的结构：A列：每个原始文件的文件名；B列：每个文件中抓的第一个单元格；C列：每个文件中抓的第二个单元格…………

示例：

图1：汇总表与原始数据文件夹

图2：原始数据（以同一文件夹下的4个文件为例，命名有规律，但数量不定）

图3：每个Excel文件内的数据示例（以1.xlsx为例，每个Excel文件格式虽然相同，但文件并不是字段格式，而是报表式的格式。）

图4：汇总后效果(其中7.0x数据认为是无效数据，不进行汇总)

10602102303079.zip (30.05 KB, 下载次数: 0)

szxrszxr · 2020-11-5 23:14:09

你自己改一下数据源就行了，数据表里不能有多余的东西，必须是上面是标题，下面是数字，最后没用的列删了就行

let
源 = Folder.Files("E:\10590\Desktop\CMM\CMM Data"),
删除的其他列 = Table.SelectColumns(源,{"Content", "Name"}),
重排序的列 = Table.ReorderColumns(删除的其他列,{"Name", "Content"}),
取表格 = Table.TransformColumns(重排序的列,{"Content",each Excel.Workbook(_){[Name="Sheet1"]}[Data]}),
整理 = Table.TransformColumns(取表格,{"Content",each Table.PromoteHeaders(Table.FromColumns(List.Split(List.RemoveNulls(List.Combine(Table.ToColumns(_))),2)))}),
展开 = Table.ExpandTableColumn(整理, "Content", List.Distinct(List.Combine(List.Transform(整理[Content],each Table.ColumnNames(_)))))
in
展开

复制代码

106021023030710.zip (37.55 KB, 下载次数: 0)

zzm · 2020-11-5 23:21:10

你自己改一下数据源就行了，数据表里不能有多余的东西，必须是上面是标题，下面是数字，最后没用的列删了就 ...

经过测试，非常好用，非常感谢！
请问，以下部分的函数，是手工输入的，还是通过在power query功能区，鼠标点选生成的呢？

  整理 = Table.TransformColumns(取表格,{"Content",each Table.PromoteHeaders(Table.FromColumns(List.Split(List.RemoveNulls(List.Combine(Table.ToColumns(_))),2)))}),
  展开 = Table.ExpandTableColumn(整理, "Content", List.Distinct(List.Combine(List.Transform(整理[Content],each Table.ColumnNames(_)))))
in
  展开

kevin · 2020-11-5 23:26:10

let
  源 = Folder.Files("C:\Users\Administrator\Desktop\CMM\CMM Data"),
  自定义1 = Table.TransformColumns(源,{"Content",eachlet t=Excel.Workbook(_){0}[Data] in
#table({"价","数","利","税"},{{t{1}[Column1],t{3}[Column1],t{3}[Column2],t{4}[Column3] }    })
  }),
  删除的其他列 = Table.SelectColumns(自定义1,{"Content", "Name"}),
  重排序的列 = Table.ReorderColumns(删除的其他列,{"Name", "Content"}),
  #"展开的“Content”" = Table.ExpandTableColumn(重排序的列, "Content", {"价", "数", "利", "税"}, {"价", "数", "利", "税"})
in
  #"展开的“Content”"