excel 在列表中查找最近日期和上个月之间添加或删除的值

sr4lhrrt 于 2022-12-30 发布在其他

关注(0)|答案(1)|浏览(109)

对于列B中的每个组，我希望标识列A中最近一天添加的值或上个月删除的值。

结果：

我设法在一个简化的例子中做到了这一点，但没有在上面的格式中做到，在上面的格式中，数据按字段一个在另一个下面存储。

Sub CompareSheetsV2()

Dim wsA As Worksheet
Dim wsB As Worksheet
Dim rngA As Range
Dim rngB As Range
Dim cellA As Range
Dim cellB As Range
Dim found As Range

Set wsA = ThisWorkbook.Worksheets("a")
Set rngA = wsA.Range("A2:A" & wsA.Cells(wsA.Rows.Count, "A").End(xlUp).Row)
Set rngB = wsA.Range("B2:B" & wsA.Cells(wsA.Rows.Count, "B").End(xlUp).Row)

For Each cellA In rngA
    Set found = rngB.Find(cellA.Value, LookIn:=xlValues)
    If found Is Nothing Then
        cellA.Offset(0, 5).Value = cellA.Value
        cellA.Offset(0, 6).Value = "deleted"
    Else

    End If
Next cellA

For Each cellB In rngB

    Set found = rngA.Find(cellB.Value, LookIn:=xlValues)
    If found Is Nothing Then
        wsA.Cells(wsA.Rows.Count, "F").End(xlUp).Offset(1, 0).Value = cellB.Value
        wsA.Cells(wsA.Rows.Count, "G").End(xlUp).Offset(1, 0).Value = "added"
    Else

    End If
Next cellB
End Sub

excel

来源：https://stackoverflow.com/questions/74939020/find-the-values-added-or-deleted-in-a-list-between-the-most-recent-date-and-past

1条答案

按热度按时间

q43xntqr1#

这也可以使用Windows Excel 2010+和Excel 365（Windows或Mac）中提供的Power Query来完成
使用增强查询的步骤

选择数据表中的某个单元格
第一个月
PQ编辑器打开时：Home => Advanced Editor
记录第2行中的表名称
将下面的M代码粘贴到您看到的位置
将第2行中的Table名称改回最初生成的名称。
仔细阅读代码（我尝试将其编写为自文档）和注解，并探索Applied Steps以理解算法
注意：不确定我所展示的内容是否适合您的输出，但这很容易更改--例如，如果您想要行，请在PQ中转置 *
- M代码**

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table19"]}[Content],

//set data types
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Group", type text}, {"Date", type date}}),

//Group by "Group"
/* Most Recent Month = latest month in subgroup
   Previous month = month prior to Most Recent Month*/
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {
        {"added/deleted", (t)=>
            let 
                #"Most Recent Date" = List.Max(t[Date]),
                #"Most Recent Month Start" = Date.StartOfMonth(#"Most Recent Date"),
                #"Previous Month Start" = Date.AddMonths(#"Most Recent Month Start",-1),
                #"Countries in Previous Month" = Table.SelectRows(t, each [Date] >= #"Previous Month Start" and [Date] < #"Most Recent Month Start")[Country],
                #"Countries in Most Recent Month" =  Table.SelectRows(t, each [Date] >= #"Most Recent Month Start")[Country],
                added = List.RemoveMatchingItems(#"Countries in Most Recent Month",#"Countries in Previous Month"),
                deleted = List.RemoveMatchingItems(#"Countries in Previous Month",#"Countries in Most Recent Month")
            in 
                Table.FromColumns(
                    {added,deleted},
                    {
                        "Added","Deleted"
                    })
                
        }}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Group"}),
    #"Expanded added/deleted" = Table.ExpandTableColumn(#"Removed Columns", "added/deleted", {"Added", "Deleted"}),

//Delete nulls from each column
    #"Delete Nulls" = Table.FromColumns(
        {List.RemoveNulls(#"Expanded added/deleted"[Added]),
        List.RemoveNulls(#"Expanded added/deleted"[Deleted])},
        type table[Added=text, Deleted=text])
 in 
    #"Delete Nulls"

在PQ中转置后

赞(0）回复(0）举报 2022-12-30

我来回答

excel 在列表中查找最近日期和上个月之间添加或删除的值

1条答案

相关问题

热门标签

最新问答