模拟Lookup模糊匹配

例1:运费匹配


根据运费表,模糊匹配地址,得到最右边的绿色表的结果。如果地址完全一样,可以通过合并查询直接就出来了,但现在不完全一样怎么搞?
遇到这种题目,首先要理清楚逻辑。观察[地址]与[城市]的关系,发现"开头为..."和"包含..."两种好像都可以,但是这里为了防止有"北京市广州路"这种奇葩地址所以用开头为的关系更保险点。那么现在意思就是比如我要得到北京天安门的运费,我只要筛选出运费表里的城市是"北京天安门"这几个字以它为开头的行,那么这一行中的运费就是我要的。筛选的两种方法我在《自定义函数》这一篇里已经讲过,所以在此直接用较简洁但稍难理解的方法,如不懂请参考那一篇:

let
    运费 = Excel.CurrentWorkbook(){[Name="运费"]}[Content],
    地址 = Excel.CurrentWorkbook(){[Name="地址"]}[Content],
    结果 = Table.AddColumn(地址, "运费", each Table.SelectRows(运费,(x)=>Text.StartsWith([地址],x[城市]))[运费]{0})
in
    结果

 

例2:科目转换


这是会计朋友们经常会遇到的问题,题目乍一看好像无从下手,但仔细观察其实和上面一个意思。先理清楚思路,比如要得到"50010206"的科目全称,只要筛选出表中以[科目代码]开头的行,符合条件的有"5001"和"500102"以及它自己本身,得到一个包含三行的表,深化出[科目]列得到一个list,然后用Text.Combine合并起来即可。

let
    源 = Excel.CurrentWorkbook(){[Name="科目"]}[Content],
    文本 = Table.TransformColumnTypes(源,{{"科目代码", type text}}),
    全称 = Table.AddColumn(文本, "科目全称", each Text.Combine(Table.SelectRows(文本,(x)=>Text.StartsWith([科目代码],x[科目代码]))[科目],"/")),
    删除 = Table.RemoveColumns(全称,{"科目"}),
    排序 = Table.ReorderColumns(删除,{"科目代码", "科目全称", "等级", "年度"})
in
    排序

 

例3:累计求和


要求年累计,同样先理下思路,只要筛选表中[年]=当前行的年且[月]<=当前行的月,深化出[金额]列再求和即可。

let
    源 = Excel.CurrentWorkbook(){[Name="累计求和"]}[Content],
    年累计 = Table.AddColumn(源, "年累计", each List.Sum(Table.SelectRows(源,(x)=>[年]=x[年] and x[月]<=[月])[金额]))
in
    年累计

 

例4:区间匹配


需要根据指定日期,找到该日期区间对应的指导价。还是按照之前的思路,只要筛选出[调价日期]<=当前行的出售日期,得到的表中最大日期对应的指导价即为我们所要的,因为源数据已经是排序过的所以只要最后一行即可。

let
    调价表 = Excel.CurrentWorkbook(){[Name="调价表"]}[Content],
    查询表 = Excel.CurrentWorkbook(){[Name="查询表"]}[Content],
    结果 = Table.AddColumn(查询表, "指导价", each List.Last(Table.SelectRows(调价表,(x)=>[产品]=x[产品] and x[调价日期]<=[出售日期])[指导价]))
in
    结果

 

小结:

以上四个题目,看起来八竿子打不到一起,但解法却如出一辙。其中难点就是使用变量x来突破上下文,所以最好搞清楚上下文关系,搞清楚x代表的是哪张表。
详细语法说明可参考《深入理解函数》

练习:


现有零件用料表,请根据指定日期计算出零件还能用多久?题目见附件。
比如指定日期为5月17日,经过17和18日两天,到19日已经不够80了,所以答案为19日。

附件

15 Replies to “模拟Lookup模糊匹配”

  1. 老师,练习题“根据指定日期计算出零件还能用多久?”,我做了一下,感觉过程很是反复折腾。先做了两个Table出来,然后再跟那个指定日期的表去匹配用料,深化出日期。
    请老师有空的时候帮忙指点一下:
    let
    用料= Excel.CurrentWorkbook(){[Name="用料"]}[Content],
    库存 = Excel.CurrentWorkbook(){[Name="库存"]}[Content],
    筛选日期 = Table.AddColumn(库存,"a",each Table.SelectRows(用料,(x)=>x[日期]>=[日期])){0}[a],
    累计用料 = Table.AddColumn(筛选日期,"acc",each List.Sum(Table.SelectRows(筛选日期,(y)=>y[日期]y[acc]>=[数量]){0}[日期])
    in
    最迟使用日期

    1. 不好意思,以下为准:
      let
      用料= Excel.CurrentWorkbook(){[Name="用料"]}[Content],
      库存 = Excel.CurrentWorkbook(){[Name="库存"]}[Content],
      筛选日期 = Table.AddColumn(库存,"a",each Table.SelectRows(用料,(x)=>x[日期]>=[日期])){0}[a],
      累计用料 = Table.AddColumn(筛选日期,"acc",each List.Sum(Table.SelectRows(筛选日期,(y)=>y[日期]y[acc]>=[数量]){0}[日期])
      in
      最迟使用日期

      1. 思路应该是对的,但你发的好像不完整。
        let
        用料= Excel.CurrentWorkbook(){[Name="用料"]}[Content],
        库存 = Excel.CurrentWorkbook(){[Name="库存"]}[Content],
        筛选日期 = Table.SelectRows(库存, each [日期]>=用料[日期]{0}),
        累计用料 = Table.AddColumn(筛选日期, "acc", each List.Sum(Table.SelectRows(筛选日期,(x)=>x[日期]<=[日期])[数量])), 最迟使用日期 = Table.SelectRows(累计用料,each [acc]>用料[数量]{0}){0}[日期]
        in
        最迟使用日期

        1. 我把整个代码复制粘贴进来,发表出来后,后边的几行自动就没了。
          Anyway,谢谢老师指点!!

        2. let
          用料 = Excel.CurrentWorkbook(){[Name="用料"]}[Content],
          库存 = Excel.CurrentWorkbook(){[Name="库存"]}[Content],
          筛选=Table.SelectRows(用料,each [日期]>=库存[日期]{0}),
          累计=Table.AddColumn(筛选,"累计",each List.Sum(Table.SelectRows(筛选,(x)=>x[日期]<=[日期])[数量])),
          截止=List.Last(Table.SelectRows(累计,each [累计]<=库存[数量]{0})[日期])

          in
          截止
          -------------------------------
          疑似楼上的公式中把库存和用料表的名字命名错了。多日期的应该是用量,单一日期的应该是库存。修改了下求最后一日满足用量的日期。如果错了,还请大佬批评指正。

  2. 新手提问,函数什么的都还刚开始学……
    例4里面如果多一个条件,比如不同的包装价格也不一样,应该怎么写呢?

    结果 = Table.AddColumn(查询表, "指导价", each List.Last(Table.SelectRows(调价表, (x)=>[产品]=x[产品] and (x)=>[包装]=x[包装] and x[调价日期]<=[出售日期])[指导价]))

    直接这样会运行出错
    谢谢解答!

    1. 我用傻办法合并出来一个产品&包装的列,结果是没问题了,但是更新查询非常慢,一秒钟100来行的样子,应该怎么优化一下呢?

  3. 如果是包含于的关系呢? 比如表1有一列“ABBCC”格式的数据,表2有一列“BBC”格式的数据,而且表1表2并不是严格对应的,现在希望得到一个表3,内有两列,数据来自表1表2存在对应关系的数据。

    结果 = Table.AddColumn(表2, "ABBCC", each Table.SelectRows(表2,(x)=>Text.Contains([ABBCC],x[BBC]))[BBC]{0})

    执行完这个步骤 我的表3“ABBCC”列全是error
    能请教一下哪里出错了吗?

  4. let
    源 = Excel.CurrentWorkbook(){[Name="领用"]}[Content],
    领用 = Table.TransformColumnTypes(源,{{"零件", type text}, {"日期", Int64.Type}, {"数量", Int64.Type}}),
    源1 = Excel.CurrentWorkbook(){[Name="库存"]}[Content],
    库存 = Table.TransformColumnTypes(源1,{{"零件", type text}, {"日期", Int64.Type}, {"数量", Int64.Type}}),
    最后 = Table.AddColumn(库存,"结束日期",each
    [a=Table.SelectRows(领用,(x)=>x[日期]>=[日期]),
    b=Table.AddColumn(a,"累计",(x)=>List.Sum(Table.SelectRows(a,(y)=>y[日期]x[累计]>[数量]))[日期]
    ][c])
    in
    最后

  5. let
    aa = Excel.CurrentWorkbook(){[Name="表2"]}[Content],
    bb = Excel.CurrentWorkbook(){[Name="表6"]}[Content],
    cc =Table.SelectRows(aa,each [日期]>=bb[日期]{0}),
    Running= Table.AddColumn(cc,"Running Total", each List.Sum(Table.SelectRows(cc,(x)=> x[日期]x[Running Total]<[数量])[日期]))
    in
    dd

  6. 感觉我这个有点复杂。。。

    源 = Table.AddColumn(
    表19,
    "Wearout Date",
    each
    List.Last(
    let
    List=
    Table.ToRows(
    Table.SelectRows(
    表17,
    (x)=>
    x[日期]>=_[日期]
    )
    )
    in
    List.Generate(
    ()=>
    [
    a = _[数量],
    b = List,
    c = 0,
    d = b{c}
    ],
    each
    [a]>0,
    each
    [
    a = [a]-[d]{2},
    b = [b],
    c = [c]+1,
    d = b{c}
    ],
    each
    [d]
    )
    ){1}
    )

    in

发表回复

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