Excel-从给定月份的最后一个随机日期获取值。

Question

我正在研究大约400家公司股票的每日市值数据。每家公司的数据从2000年到2016年不等。然而，我想在6月的最后一个日期和12月的最后一个日期选择市值。此外，这些最后的日期并不总是意味着月底。例如，12月份，有一家公司在12月14日有最后一次可用的市值。

为了再次总结我的问题，我想选择2000年至2016年12月份的最后一个“可用”市值(6月)和最后一个可用市值。

请帮助我，或者给我一个方向来解决这个问题。由于最近一天的可用值并不意味着月底值，我想我不能使用月底公式。查找函数看起来也不可靠。

我再次感谢你能提出的任何建议。提前谢谢你。

样本数据

Date   - Closing - Market Capitalization  
2-Jan  - 23.5    - 177,282,000    
1-Jan  - 22      - 177,282,000    
31-Dec - 33      - 177,282,000    
30-Dec - 33      - 177,282,000    
4-Jul  - 31      - 120,960,000  
2-Jul  - 31.1    - 113,400,000  
1-Jul  - 31      - 113,400,000  
28-Jun - 30      - 113,400,000  
27-Jun - 30      - 113,400,000  
26-Jun - 30.1    - 113,400,000 

​

​

Answer 1

您可以使用以下数组公式：

=INDEX($C$2:$C$11,MATCH(TRUE,(MIN(IF($A$2:$A$11<=EOMONTH(E6,0),EOMONTH(E6,0)-$A$2:$A$11))=IF($A$2:$A$11<=EOMONTH(E6,0),EOMONTH(E6,0)-$A$2:$A$11)),0))

它是一个数组公式，必须用Ctrl-Shift-Enter确认.如果操作正确，excel将把{}放在公式周围。

​

​

如果您能够按升序对日期进行排序，则可以使用以下非数组公式：

=INDEX($C$2:$C$11,MATCH(EOMONTH(E6,0),$A$2:$A$11,1))

​

​

Answer 2

潜在解决方案。对此我有个小小的警告，我会在最后讲到的。

让我们在E列中生成要搜索的目标日期列表。它们将是6月份和12月份的结束日期。每行将前一日期提前6个月。

在E2中，我们将播种机的日期放在：

=EOMONTH(DATE(2000,6,1),0)

在E3中，我们将列表生成器如下所示，并尽可能地复制：

=EOMONTH(E2,6)

在G2中，我们使用以下公式并尽可能地复制：

=IFERROR(INDEX($C$2:$C$11,AGGREGATE(15,6,ROW($A$2:$A$11)/((MONTH($A$2:$A$11)=MONTH(E2))*(YEAR($A$2:$A$11)=YEAR(E2))),1)-1),"NOT FOUND")

但要注意的是，市值至少必须在6月份或12月份发生。在这几个月之外，我们将不再关注这些问题。

如果您想显示您所提取的数字对应的日期，因为它实际上不是月份的最后一天，您可以将该日期放在F列中：

=IFERROR(INDEX($A$2:$A$11,AGGREGATE(15,6,ROW($A$2:$A$11)/((MONTH($A$2:$A$11)=MONTH(E2))*(YEAR($A$2:$A$11)=YEAR(E2))),1)-1),"NOT FOUND")

概念证明

​

​