在Excel 2007与LibreOffice Calc中匹配指定日期前最新汇率

要实现兼容Excel 2007和LibreOffice Calc的需求，可使用数组公式结合INDEX、MAX和IF函数完成，无需依赖FILTER这类高版本专属函数。

公式写法 #

在Sheet2的B1单元格输入以下公式，输入完成后按Ctrl+Shift+Enter（Excel）或Ctrl+Shift+Enter/Cmd+Shift+Enter（LibreOffice）确认数组公式：

=INDEX(Sheet1!$B$1:$B$100,MAX(IF(Sheet1!$A$1:$A$100<=Sheet2!A1,ROW(Sheet1!$A$1:$A$100)-ROW(Sheet1!$A$1)+1,0)))

公式说明 #

• IF(Sheet1!$A$1:$A$100<=Sheet2!A1,ROW(Sheet1!$A$1:$A$100)-ROW(Sheet1!$A$1)+1,0)：遍历Sheet1的日期列，筛选出小于等于目标日期的行，返回这些行在汇率列区域内的相对位置，不符合条件的返回0。
• MAX(...)：从上述结果中提取最大的相对位置，即目标日期之前最近的有效数据行。
• INDEX(Sheet1!$B$1:$B$100, ...)：根据找到的行位置，提取对应的汇率值。

注意事项 #

1. 将公式中的Sheet1!$B$1:$B$100和Sheet1!$A$1:$A$100替换为你实际的数据范围，确保覆盖所有日期和汇率记录。
2. 若Sheet1中没有任何小于等于目标日期的记录，公式会返回0，可添加IFERROR进行错误处理：

   =IFERROR(INDEX(Sheet1!$B$1:$B$100,MAX(IF(Sheet1!$A$1:$A$100<=Sheet2!A1,ROW(Sheet1!$A$1:$A$100)-ROW(Sheet1!$A$1)+1,0))),"无有效汇率")
   

3. 数组公式必须按组合键确认，直接按Enter无法正常计算。

示例验证 #

对应你给出的示例场景：

• Sheet2的A1为2022-08-03时，Sheet1中小于等于该日期的最近日期是2022-08-01，公式返回0.95。
• Sheet2的A2为2022-07-25时，Sheet1中存在该日期记录，公式返回0.91。

内容的提问来源于stack exchange，提问作者domaci_a_nas