☰

在 Excel 数据透视表中的某个组内查找今天之后的下一个截止日期

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 pivot-table
在 Excel 数据透视表中的某个组内查找今天之后的下一个截止日期

我在一张表中设置了一个包含“交易”的表格。每行包含供应商名称、欠款或已付款金额（取决于交易类型）以及到期日/交易日期。以下是一些简化的示例数据：

供应商日期发票付款
供应商 A 6/30 $200       
供应商 A 6/30 ($200)  
供应商 B 7/5 500 美元    
供应商 B 7/5（$500）
供应商 C 10/28 $50
供应商 A 10/30 100 美元
供应商 C 11/15 $50

我已经从该表构建了一个数据透视表，按供应商对这些交易进行分组，并计算剩余欠款总额。我想弄清楚的是，如何为每个供应商获取下一个到期日（该组的最小日期，不包括 < Today() 的日期），或者如果没有下一个到期日，那么我想查看该组的最大日期。

这是我的数据透视表的样子，加上我想要添加的日期列（假设 Today() = 10/23）：

供应商欠款日期
供应商 B 7/5 -    
供应商 C 10/28 $100
供应商 A 10/30 100 美元

我知道，如果我在该栏中填写付款日期，那么将其称为下一个到期日可能就不那么准确了，但我可以接受。

总结：我想找出每个组中的下一个最早日期或最后一个日期。我该怎么做？

答案1

好吧，也许这不是解决这个问题的最简单的方法，但我会使用 VBA 来构建一个返回下一个到期日期（或最大日期，如果下一个到期日期 < 今天）的函数。数组公式是另一种选择，但效率相当低。

  1. 将您的工作簿保存为 .xlsm（启用宏）文件。
  2. 选择 Vendor 列中的所有值并为其定义一个命名范围（下面的代码假设您已将命名范围称为“Vendor”）。我不建议选择整个列，因为这会导致代码运行非常缓慢。只需选择带有日期的单元格；如果插入新行，命名范围将动态扩展。为了安全起见，您可以在底部选择一些额外的单元格；包括空白单元格不会造成任何损害。
  3. 按 ALT+F11 打开 VBA 编辑器。
  4. 从“插入”菜单中选择“模块”。

  5. 粘贴此 VBA 函数：

    Public Function NextByVendor(ByVal rngVendor As Range)

Dim c As Range
Dim strVendorName
Dim lngToday As Long
Dim lngNextDate As Long

lngToday = Int(CDbl(Now()))
lngNextDate = 0

strVendorName = rngVendor.Value

'Get largest date value
For Each c In Range("Vendor")
    If c.Value = strVendorName And _
    c.Offset(0, 1).Value > lngNextDate Then
        lngNextDate = c.Offset(0, 1).Value
    End If
Next c

'If largest date is > today, get next available date
If lngNextDate > lngToday Then
    For Each c In Range("Vendor")
        If c.Value = strVendorName And _
        c.Offset(0, 1).Value < lngNextDate And _
        c.Offset(0, 1).Value > lngToday Then
            lngNextDate = c.Offset(0, 1).Value
        End If
    Next c
End If

NextByVendor = lngNextDate

End Function

  6. 保存并关闭 VBA 窗口。返回电子表格并在数据右侧添加一个新列。在第一个数据行（假设为 2）中，输入公式“=NextByVendor(A2)”。

  7. 将公式一直复制下去。最终结果应该类似如下：

    Vendor          Date            Invoice   Payment   Next Payment Due
Vendor A        6/30/2012       $200.00             10/30/2012
Vendor A        6/30/2012                 ($200)    10/30/2012
Vendor B        7/5/2012        $500.00             7/5/2012
Vendor B        7/5/2012                  ($500)    7/5/2012
Vendor C        10/28/2012      $50.00              10/28/2012
Vendor A        10/30/2012      $100.00             10/30/2012
Vendor C        11/15/2012      $50.00              10/28/2012

  8. 刷新数据透视表；现在新数据应该可用了。您可以将数据透视表中“下次付款到期”的值类型设置为“平均值”或“最小值”。

希望这可以帮助！

-- jm

相关内容