两个不同的 Excel 表中的日期范围匹配

Question

如果我理解您想要正确执行的操作，则可以在 Windows Excel 2010+（和 Office 365）中提供的 Power Query 中完成此操作。

但你的描述中遗漏了一些我做出假设的事情

登陆日期不能早于航海日志日期（你怎么能登陆你还没捕捞到的鱼）
有时，您会在同一天针对同一 ID、同一物种和同一装备多次捕捞。
- 我选择只匹配最接近记录金额的着陆点
- 有时这会导致着陆记录和日志之间出现很大差异
- 不确定这是由于什么原因造成的（可能是其他日子的着陆合并）？
  - 如果是这样，可能需要手动检查。
PQ 需要很长时间才能处理您的数据（在我的机器上大约需要 20 秒）。这可能是由于多个表过滤操作造成的。如果其他方面都正常，也许可以加快速度。

对于 Excel 2010 中的 PQ，您需要下载并安装Microsoft Power Query 插件。

使用 Power Query

将两个数据表都变成表格
- 这将位于选项Insert卡上；或者位于选项Home卡上（格式化为表格）。
选择 Table1 中的某个单元格（或任何你为第一个表命名的单元格）
Data => Get&Transform => from Table/Range（*可能与插件不同）
当 PQ 编辑器打开时：Home => Advanced Editor
记下表格姓名在第 2 行
将下面的 M 代码粘贴到您所看到的位置
将第 2 行的表名改回最初生成的表名。
阅读评论并探索Applied Steps以了解算法

M 代码

let

//Read in table 1 and set data types
//Be sure to change `Name`  of table to the actual table name in your workbook
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

//Buffering this table seems to reduce speed of execution from 60 sec to about 20 sec
    Table1 = Table.Buffer(Table.TransformColumnTypes(Source,{
        {"ID", type text}, 
        {"DATE", type date}, 
        {"SPECIES", type text}, 
        {"GEAR", type text}, 
        {"LOGBOOK", Int64.Type}})),

//Read in table 2 and set data types
//Be sure to change `Name`  of table to the actual table name in your workbook
    Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Table2 = Table.Buffer(Table.TransformColumnTypes(Source2,{
        {"ID", type text}, 
        {"DATE", type date}, 
        {"SPECIES", type text}, 
        {"GEAR", type text}, 
        {"LANDING", Int64.Type}})),

//Join the two tables based on ID, SPECIES and GEAR
    Joined = Table.NestedJoin(Table1, {"ID", "SPECIES", "GEAR"}, Table2, {"ID","SPECIES","GEAR"},"Joined"),

//filter Joined subtable by minimum landing date that is >= logged date
// **I assumed that landing date MUST be AFTER log date.**
//If that is not the case, then code change will be needed
    filteredTbl = Table.AddColumn(Joined, "Filtered Table", each
         let 
            dt = [DATE],
            minDT = List.Min(List.Select([Joined][DATE],each _ >= dt))
        in 
            Table.SelectRows([Joined], each [DATE] = minDT)),
    #"Removed Columns" = Table.RemoveColumns(filteredTbl,{"Joined"}),
   
    //landing date will be any row in filtered table unless table is empty
    landingDT = Table.AddColumn(#"Removed Columns", "LANDING DATE", 
        each if Table.IsEmpty([Filtered Table]) then null 
        else [Filtered Table][DATE]{0}, Date.Type),

    //landings will be the value with the least difference from that logged on the landingDt
    landings = Table.AddColumn(landingDT, "LANDING", each
     if [LANDING DATE]=null then null 
     else   let 
                lb = [LOGBOOK],
                ld = List.Transform([Filtered Table][LANDING], each Number.Abs(_ - lb)),
                posMinDiff = List.PositionOf(ld,List.Min(ld))
            in 
                [Filtered Table][LANDING]{posMinDiff},Int64.Type),

    //Match if landing date LTE log date + 7 days
    match = Table.AddColumn(landings, "MATCH", each Duration.TotalDays([LANDING DATE]-[DATE])<=7,Logical.Type),

    //calculte difference between landings and logged, if there is a Match
    //sometimes the differences are quite large.
    //maybe an error, or consolidation of multiple logged valaues?
    //not sure what do with large differences
    landingsDiff = Table.AddColumn(match, "Landing Diff", each if [MATCH] = true then [LANDING] - [LOGBOOK] else null, Int64.Type),
    #"Removed Columns1" = Table.RemoveColumns(landingsDiff,{"Filtered Table"})
in
    #"Removed Columns1"

初始数据表 （部分的）

举个例子，关于登陆/日志金额严重不匹配的问题，你的 6 月 17 日和 6 月 23 日的日志记录ID **31/SBS/PS可能被合并为 6 月 24 日的一次登陆

结果 （部分的）

如果有任何评论或问题请回复。

Answer 1