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

amiewk

平时用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数据认为是无效数据，不进行汇总)

0星剑0

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

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

复制代码

amiewk

0星剑0 发表于 2020-3-21 12:04
你自己改一下数据源就行了，数据表里不能有多余的东西，必须是上面是标题，下面是数字，最后没用的列删了就 ...

经过测试，非常好用，非常感谢！
请问，以下部分的函数，是手工输入的，还是通过在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
    展开

shijiexiansheng

let
    源 = Folder.Files("C:\Users\Administrator\Desktop\CMM\CMM Data"),
    自定义1 = Table.TransformColumns(源,{"Content",each  let 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”"

kissyui

0星剑0 发表于 2020-3-21 12:04
你自己改一下数据源就行了，数据表里不能有多余的东西，必须是上面是标题，下面是数字，最后没用的列删了就 ...

老师帮忙看一下这个怎么弄http://club.excelhome.net/thread-1527684-1-1.html

0星剑0

kissyui 发表于 2020-3-23 20:54
老师帮忙看一下这个怎么弄http://club.excelhome.net/thread-1527684-1-1.html

帮不到你了，BI我也没用过，只是对PQ有点了解

0星剑0

amiewk 发表于 2020-3-22 11:32
经过测试，非常好用，非常感谢！
请问，以下部分的函数，是手工输入的，还是通过在power query功能区， ...

取表格、整理、展开是手动输的，展开本来是直接展开的，不过是固定的{"价", "数", "利", "税"}，我给改成自动获取了

Ricardo_h_ja

0星剑0 发表于 2020-3-21 12:04
你自己改一下数据源就行了，数据表里不能有多余的东西，必须是上面是标题，下面是数字，最后没用的列删了就 ...

大佬请教一下，修改完成合适数据源后运行，程序报错“Expression.Error: 导入 List.Split 没有匹配的导出。是否缺少模块引用?” 请问是什么原因？该如何解决？谢谢您！

Ricardo_h_ja

Ricardo_h_ja 发表于 2020-7-28 10:00
大佬请教一下，修改完成合适数据源后运行，程序报错“Expression.Error: 导入 List.Split 没有匹配的导出 ...

详情如下，编辑器截图