vba 在最接近指定日期的列表中查找上一个日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21762647/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find a previous date on a list closest to a specified date
提问by John W
I would like to have VBA code or formula that will take a variable date value and find it on a list range of dates. If it can't find the date I want it to find the closest previous date value. VLOOKUPworks great for finding an exact match but I am struggling with the code to find the closest previous date.
我想要 VBA 代码或公式，它将采用可变日期值并在日期列表范围内找到它。如果它找不到日期，我希望它找到最接近的前一个日期值。 VLOOKUP非常适合查找精确匹配，但我正在努力使用代码来查找最接近的前一个日期。
My only thought is to create a loop where if it doesn't match it continues to subtract a day from the variable date until it can locate a match on the list. That does not seem like the best or most efficient method and I hope someone can recommend a simpler solution.
我唯一的想法是创建一个循环，如果它不匹配，它会继续从变量日期中减去一天，直到它可以在列表中找到匹配项。这似乎不是最好或最有效的方法，我希望有人可以推荐一个更简单的解决方案。
Example:
例子：
Variable Date = 01/26/2014
Date List
02/04/2014
01/27/2014
01/24/2014
01/13/2014
12/29/2013
Desired Result = 01/24/2014
Any help would be appreciated.
任何帮助，将不胜感激。
回答by John Bustos
Suppose your list of dates was in cells A1:A5and your check date was in cell C1, you could enter this function ENTERED AS AN ARRAY FORMULA:
假设您的日期列表在单元格中A1:A5并且您的检查日期在单元格中C1，您可以输入此函数作为数组公式输入：
=MAX(($A:$A<=C1)*A1:A5)
Rememeber, to enter it as an array formula, hit Ctrl+ Shift+ Enterwhen entering the formula.
Rememeber，将其输入作为数组公式，命中+ +输入公式时。CtrlShiftEnter
Hope this does the trick!!
希望这能解决问题！！
回答by Excel Ireland
I went about this a little differently, no arrays needed
Find how many numbers are bigger then the one you are looking for with CountIf()Then I used =Largethis will find the nth dates in a list we have the nth we are looking for in the countIF()
我对此略有不同，不需要数组查找比您要查找的数字大的数字CountIf()然后我使用=Large它会在列表中找到第 n 个日期，我们在列表中找到第 n 个日期countIF()
=LARGE(A:A,COUNTIF(A:A,">="&TODAY()))
回答by Anthony Bird
Vlookup can do this actually, if you set the final argument to true, which looks for an approximate match. You need your dates sorted from Oldest to newest and it will return the first date that is not after your search term.
Vlookup 实际上可以做到这一点，如果你将最后一个参数设置为true，它会寻找一个近似匹配。您需要将日期从最旧到最新排序，它会返回不在您的搜索词之后的第一个日期。
