多维转一维

题目:


现一个文件夹下有N张如上图所示格式统一的档案表,需要汇总得到如下图的一维表。

解法:

遇到这种多维度又有合并单元格的表格,什么透视逆透视的都已经没用了,仅靠转换表是不可能得到想要的一维数据的。
我们的需求是对于每张表获取一行标题和一行数据,又因为每张表格式统一,所以每一个标题和数据的位置是固定的,那么我们就可以先根据位置,把每一个值取出来,然后再重新构建成表。
动手之前,先在草稿纸上把要取的值的位置记录下来:

而因为PQ的索引是从0开始,所以以上的行列号都要-1。

首先第一步,使用Table.ToColumns将表拆成一列一列的,整个表就变成了list套list。

那么比如要得到"姓名",就是先用{0}得到第1列所有行,再用{2}得到第1列的第3行。

有了这个思路,后面只需要对刚才草稿纸上的每一个行列号构建为一个list,然后用List.Transform遍历取值就行了。
公式中我使用了List.Zip对行列号压缩,比如把两个数据分别在第1列第3行和第2列第3行,= List.Zip({{0,1},{2,2}}),返回{{0,2},{1,2}}。
遍历完所有行列号,得到两个list分别为标题和数据,再用Table.FromColumns还原成表即可。

let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    转为列表 = Table.ToColumns(源),
    标题 = List.Transform(List.Zip({{0,3,0,3,0,2,4,0,2,4,0,3,0,3,0,1,2,4,5,0,1,2,4,5,0,3,0},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,9,9,9,9,9,19,19,19,19,19,26,26,27}}),each 转为列表{_{0}}{_{1}}),
    数据 = List.Transform(List.Zip({{1,3,1,3,1,3,5,1,3,5,1,4,1,4,0,1,2,4,5,0,1,2,4,5,1,4,2},{2,2,3,3,4,4,4,5,5,5,6,6,7,7,10,10,10,10,10,20,20,20,20,20,26,26,27}}),each {转为列表{_{0}}{_{1}}}),
    结果 = Table.FromColumns(数据,标题)
in
    结果

 
这是对于单文件,那么如果是多文件汇总,就把上面的公式做成自定义函数,然后对文件夹下每一个文件遍历调用函数,最后再合并即可。
以上的方法是分别对每张表取一次标题,再取一次数据,要取两次。但因为每张表的标题都是一样的,所以在实际应用中可以只取其中一张表的标题,对于其他表只取数据,这样能减少运算量,在数据量大的情况下能节省刷新时间。

附件的案例中偷了个小懒,不同文件只改了姓名,其他数据都没改,所以如果看到数据都一样不要觉得奇怪。。。
下载附件后请自行修改数据源路径,否则会报错。

附件

28 Replies to “多维转一维”

  1. 施老师的方法我学习了,确实很简单,不过如果合并多个表的话,调用自定义函数可能对于初学者有些困难。我是先自定义函数,然后在另一个查询中通过菜单添加列调用,过程虽然复杂了一些,但是菜单操作会更易于我们初学者理解一点。

    1. 两个思路,看你最后想要什么样的形式展现:
      1、一张表一行,多篇文章数据横着排
      可以同样的方法把其他文章取出来,唯一的麻烦在于字段名重复会报错。所以可以把文章那里的字段名用List.Transform转换下,变成文章1、文章2、文章3这样,比较麻烦。
      2、一张表多行,多篇文章分多行
      因为本身文章那部分的表就是一个一维表,所以可以按案例做完后,添加一列用Table.Range把文章部分的一维表取出来,再展开就行了,这个方法比较简单。
      不知道看懂了没?

      1. 我还是个初学者,能不能请老师示范一下“一张表多行,多篇文章分多行”这种做法?
        谢谢!

        1. 下载附件-单文件,打开PQ,在我做的结果后面继续操作,点击添加列-自定义列,输入=Table.Range(源,10,5),后面两个参数就是从多少行开始取,取多少行,根据你自己的需求改下,完成后确定,展开就行了。还不会的话可以加首页右侧QQ群,在群里提问

  2. 请问老师,如果在合并的总表中,不列出文章和科研成果的具体明细,而只要给出文章数量和科研成果数量,要怎么操作呢?
    此外,当文章和科研成果部分的行数可以无限量自有添加的时候,用“=Table.Range(源,10,5)”是不是会出错?因为不能确定从多少行开始取、取多少行。

    1. 第一步先找一维表下面的第一行,也就是本案例中"计算机(科目数量)"在列1中的位置,用List.PositionOf(源[列1],"计算机(科目数量)")得到结果26,减去一维表第一行的位置19再减1,得到6,就是数量了

  3. 那老师我有个小小要求,在多文件汇总下,我在汇总结果里需要多加一个字段,字段内容就是每个文件的文件名,那需要怎么处理,谢谢!

  4. 请问一下:草稿纸上把要取的值的位置记录下来的那张图上,
    第二个标题是{3,4}第二个内容应该是是{3,5}啊,为什么例子中给的是{3,4}与{3,4}呢?

  5. 感谢施阳老师精彩的讲解,这篇文章内容,我投入了近一周的学习。终于领会了其中的奥妙。

  6. 我有一个案例,和这个表格结构差不多,也涉及到多个相同表格结构转换的!不过我的多张表都在一个工作表下,每页一组数据!一共250页左右,用这种list结构去遍历时,除非加Buffer,速度特别慢,大约刷新得一分钟左右。但加一个辅助索引列后,再把索引列除以17(每页17行),然后根据辅助列分组,添加列用Record取得各交叉位置的值,然后Tabe.FromRecords,这样速度快的多,大约不到两秒时间。

  7. 最近也在研究这个,思路也是用一个excel来确定数据行、列值,然后读取;
    但现在有个小问题:在此类表格中,嵌套着动态多行表,即通过Table.Range来获取,但下限是动态的,如何解决呢?

  8. let
    源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    更改的类型 = Table.TransformColumnTypes(源,{{"列1", type text}, {"列2", type any}, {"列3", type text}, {"列4", type text}, {"列5", type any}, {"列6", type any}}),
    已删除的空行 = Table.SelectRows(更改的类型, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    a = Table.AddIndexColumn(已删除的空行, "索引",1, 1),
    横 = Table.Combine( List.Transform(Table.ToRows( Table.SelectRows(a, each ([索引] 1 and [索引] 9 and [索引] 10 and [索引] 11 and [索引] 12 and [索引] 13 and [索引] 16 and [索引] 8 ))),each Table.FromRows((List.Split(List.Select(List.RemoveLastN(_,1),(n)=>nnull),2))))),
    筛选的行 = Table.SelectRows(a, each ([索引] = 8 or [索引] = 9 or [索引] = 10 or [索引] = 11 or [索引] = 12 or [索引] = 13)),
    向下填充 = Table.FillDown(Table.RemoveLastN(Table.Transpose(筛选的行),1),{"Column1", "Column4"}),
    合并的列1 = Table.CombineColumns(向下填充,{"Column1", "Column2"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"已合并"),
    合并的列2 = Table.CombineColumns(合并的列1,{"Column4", "Column5"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"已合并.1"),
    竖= Table.Combine( List.Transform(Table.ToRows(合并的列2),each Table.FromRows(List.Split(_,2))))
    in
    Table.Combine({横,竖})

    老师的套路还要再理解一下,我用了个投机的办法,先将表里横向排数据的行选出来,将这个表转为一维表,再把竖向排数据的行选出来,将这个表转为一维表,再把两个一维表合并就行了

  9. 用的例子中的 多文件汇总

    老是提示如下:

    Expression.Error: 列参数必须为 Null,或者指定列数、列名的列表或表类型。
    详细信息:
    [List]

  10. 求教一下老师:
    标题是:each 转为列表{_{0}}{_{1}}
    数据是:each {转为列表{_{0}}{_{1}}}
    为什么多了一对{ }?
    里面的深化是怎样理解的?
    ______菜鸟

  11. 用的例子中的 文件汇总

    老是提示如下:

    Expression.Error: 无法将值 "样品名称/样品编号:钛晶-01A0101" 转换为类型 List。
    详细信息:
    Value=样品名称/样品编号:钛晶-01A0101
    Type=[Type]

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注