超级有难度的查询,求大神帮忙

Power Query ksbor ⋅ 于 2018-12-23 14:52:02 ⋅ 最后回复由 eXcellent 2019-03-25 09:13:30 ⋅ 1846 阅读

file
Excel文件在网盘里https://pan.baidu.com/s/136GY27Gdnf4F8d2yyomtHw
查找条件:如2018年第1组中,B对I这行,首先看后三组,即第2组中O对B,第3组中B对C,第四组中G对B。然后查找O对B在2018年第1组之前的最近对比,即2017年第37组中的O对B,查找B对C在2018年第1组之前的最近对比,即2017年第25组C对B,查找G对B在2018年第1组之前的最近对比,即2017年第24组G对B,将查找后的三组结果返回到2018年B对I后面。

本帖已被设为精华帖!
本帖由 Alex 于 2年前 加精
回复数量: 10
  • klzb
    2018-12-24 08:35:51

    名称1和名称2合并,然后分组,每组取{0},是这个意思?

  • Ntt docomo
    2018-12-24 09:42:28

    file

  • ksbor
    2018-12-24 10:13:46

    @klzb 要根据未来三组对照情况,查找已经发生的最近对照结果,把结果列到该行的右面,这个照片看不出来,网盘里Excel表比较全
    假设2018第1组之后的对照结果还没发生,以B对I这行为例,我要查未来三组(第2组,第3组,第4组)中B跟谁相对(O对B,B对C,G对B),然后查询2018第1组之前已经发生的O对B,B对C,G对B最近对照结果,将结果放到B对I这行的后面,其他行的查询规则也是这样

  • 飞天篮球 Talk is cheap, show me your code.
    2018-12-24 14:14:55

    是这个意思吗?

    2017第37组,未来三组是38和2018的1,2,然后查找2017第37组之前的数据

    2018第1组,未来三组是2,3,4,然后查找2018第1组之前的数据,就是2017年的数据

    2018第2组,未来三组是3,4,5,然后查找2018第2组之前的数据,就是2018第1组+2017年的数据

    2018第3组,未来三组是4,5,6,然后查找2018第3组之前的数据,就是2018第1、2组+2017年的数据

    ......

  • ksbor
    2018-12-24 14:20:59

    @飞天篮球 是的,太对了

  • 飞天篮球 Talk is cheap, show me your code.
    2018-12-24 15:21:18

    @ksbor

    那你试试吧,看运行结果是不是你想要的。由于使用了Table.SelectRows,所以效率欠佳,加载到表有点慢。

    先把工作表中前五列所在的数据区域Ctrl+T变成表,表名为“表1”,导入PQ,进入高级编辑器,先清空,再把以下语句贴入:

    let
        源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
    
        变 = Table.SplitColumn(
                               Table.AddColumn( 源,"a",each let p = Table.PositionOf(源,_),
                                                                l = {Table.Buffer(Table.Range(源,0,p)),Table.Buffer(Table.Range(源,p+1))}
                                                             in List.Combine(
                                                                             Table.TransformRows(
                                                                                                 Table.Range(
                                                                                                             Table.SelectRows( l{1},
                                                                                                                               (x)=>List.Contains({x[名称1],x[名称2]},[名称1]))
                                                                                                             ,0,3),
                                                                                                 each Record.ToList(
                                                                                                                    Table.Last( 
                                                                                                                               Table.SelectRows( l{0},
                                                                                                                               (x)=> List.ContainsAll({x[名称1],x[名称2]},{[名称1],[名称2]}))
                                                                                                                               ,[] ) )))  )
                             ,"a",each _,15)
    in
        变
  • ksbor
    2018-12-29 12:40:41

    @飞天篮球 想来想去,最后用的if...then...else...,给每一行增加一个唯一对比编号,经过几次合并、拆分、再合并,过程比较麻烦,但运行速度很快,因为这个工作是重复性的,建立好模板后面就不用再费事了
    还是谢谢你了:)

  • eXcellent
    2019-03-24 05:29:45

    @ksbor
    1:你到底是要前三组 还是后三组? 你的表格 每行右边只留出三组的位置呀!
    2:你对于每年的每一组, 你到底想要从其它组里找 名称2 相同的 还是名称1相同的?看你前面的解释, 一会O对B,B对C,G对B, 一会你的表格里给出的又是O-B,C-B,G-B
    逻辑是不是有点混乱?

  • ksbor
    2019-03-24 21:34:08

    @eXcellent
    《一会O对B,B对C,G对B, 一会你的表格里给出的又是O-B,C-B,G-B》
    确实是这样的,有时O-B,有时B-O,顺序是不一定的,所以才有难度。蓝底色的例子只是一部分,整体还要更复杂。

  • eXcellent
    2019-03-25 09:13:30

    你的题 其实就是个反复筛选的过程, 根据不同条件分好步骤, 再不断将筛选函数加以组合。

    第1步, 根据当前行的名称1 去筛选比当前行的年份和分组 大的前3行;

    第2步, 对第1步取得的每一行, 根据其 名称1-名称2 的组合 去取得比当前行的年份和分组 小, 且 相同名称组合 的最后一行;

    第3步, 将第2步取得的每一行 与当前行合并。

    所以, 只要厘清条件和筛选的思绪, 又对DAX公式熟悉(M也如此), 本题不难.

    详见我的文章

暂无评论~~
  • 请务必阅读并严格遵守《社区管理规范与使用说明》
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 发送表情,可用的 Emoji 见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板粘贴上传, 格式限制 - jpg, png, gif
  • 不支持上传附件,请尽可能用文字和图片将问题描述清楚,如实在需要上传附件,可上传到 共享网盘 后分享链接
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!
Ctrl+Enter