模拟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
    能请教一下哪里出错了吗？

    回复

  5. 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
    最后

    回复

  6. 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

    回复

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

    源 = 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
    源

    回复

发表回复

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