☰

Excel - 查找具有最接近截止日期的给定类型的文档

tag-icon • tag-icon microsoft-excel worksheet-function date array
Excel - 查找具有最接近截止日期的给定类型的文档

我正在对 2 个表进行操作。

“表 1”是我导入的、希望保持不变的当前文档列表。该文件没有任何格式。

表2是我操作的客户列表，是一个excel表。

现在我想做的是在表 2 中创建一个公式，该公式为每个客户在表 1 中查找他的发票，并选择最接近其各自到期日的发票（或者换句话说，到期日最接近今天的发票）。

我尝试的是：

Column F - Due dates
Column A - Customer number
Column C - Document type 

{=MIN(IF([Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY();[Documents.XLSX]Sheet1!$F$2:$F$30000))}

它给了我一个总体最近的日期，即相隔一天，但是它是从所有客户的所有文件中获取的，这并不令人满意。

然后我尝试添加第二个条件，只过滤掉发票

{=MIN(IF(AND([Documents.XLSX]Sheet1!$C$2:$C$30000="INVOICE";[Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY());[Documents.XLSX]Sheet1!$F$2:$F$30000))}

不幸的是，事情开始变得糟糕起来。

当然，我也可以使用客户号码来添加第三个条件，但这仍然不起作用。

{=MIN(IF(AND([Documents.XLSX]Sheet1!$C$2:$C$30000="INVOICE";[Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY();[Documents.XLSX]Sheet1!$F$2:$F$30000=[@CustomerNumber]);[Documents.XLSX]Sheet1!$F$2:$F$30000))}

我还尝试使用类似这样的函数：

=INDEX(AD3:AD9;MATCH(1;INDEX((AG3=AA3:AA9)*(AG4=AB3:AB9)*(AG5=AC3:AC9);0;1);0))

其中 AD 是值，AG 是输入，AA AB 和 AC 是不同的条件数组（例如文档类型、客户名称等）。只要前两个条件看起来像 AG3 和 AG4，我就会推测最后一个条件必须以不同的方式制定，其中输入数据范围也是输出范围。

样本： https://i.stack.imgur.com/S8HCs.png

现在，在这个例子中，Bob 的公式应该返回 30.10.2018 的值，因为这是今天最近的发票。而对于 John，它不应该返回任何内容，因为他的发票已经过期。

编辑：最后我设法自己解决了这个问题。将最终的公式发布给将来可能偶然发现此主题的任何人：

{=MIN(IF(([Documents.XLSX]Sheet1!$C$2:$C$30000="Invoice")*([Documents.XLSX]Sheet1!$F$2:$F$30000>TODAY())*(TEXT([@Customer];0)=[Documents.XLSX]Sheet1!$A$2:$A$30000);[Documents.XLSX]Sheet1!$F$2:$F$30000))}

这是一个数组公式，因此当然要按 ctrl+shift+enter

但是，如果有人知道如何通过索引匹配组合获得类似的结果，我会非常乐意学习。

答案1

您可以添加一个中间数据透视表......

  • “行标签”中的发票号码
  • “价值”区域中的到期日。

确保将值字段设置设为“最大”，以便您可以提取每个发票号的最迟到期日。

然后您可以通过感兴趣的表中的“vlookup”查找这些内容。

相关内容