I've long sought a method for using INDEX MATCH in Excel to return the absolute closest number in an array without reorganizing my data (since MATCH requires lookup_array to be in descending order to find the closest value greater than lookup_value, but ascending order to find the closest value less than lookup_value).
我一直寻求一种方法在Excel中使用索引匹配数组中返回最近的绝对数量没有重组我的数据(因为比赛需要按照降序排列lookup_array找到最接近的值大于lookup_value,但升序找到最接近的值小于lookup_value)。
I found the answer in this post. XOR LX's solution:
我在这篇文章中找到了答案。XOR LX的解决办法:
=INDEX(B4:B10,MATCH(TRUE,INDEX(ABS(A4:A10-B1)=MIN(INDEX(ABS(A4:A10-B1),,)),,),0))
=指数(B4:B10,匹配(真的,指数(ABS(A4:A10-B1)= MIN(索引(ABS(A4:A10-B1))),,),0))
worked perfectly for me, but I don't know why. I can rationalize most of it but I can't figure out this part
我做得很好，但我不知道为什么。我能合理地解释它的大部分，但我搞不懂这部分
INDEX(ABS(A4:A10-B1)=MIN(INDEX(ABS(A4:A10-B1),,))
指数(ABS(A4:A10-B1)= MIN(索引(ABS(A4:A10-B1)))
Can anyone explain this part? I hate blinding using it and know it'll be useful in the future.
有人能解释这部分吗?我讨厌盲目地使用它，我知道它将来会很有用。
1 个解决方案
#1
8
I guess it makes sense for me to explain it, then!
我想我解释一下是有道理的!
Actually, it didn't help that I was employing a technique which is designed to circumvent having to enter a formula as an array formula, i.e. with CSE. Although that could be considered a plus by some accounts, I think I was wrong to employ it here, and probably wouldn't do so again.
实际上，我使用了一种技术，这种技术可以避免输入一个公式作为数组公式，例如CSE。虽然这可能被一些人认为是一个优点，但我认为我在这里使用它是错误的，而且很可能不会再这样做了。
The technique involves inserting extra INDEX functions at appropriate places within the formula. This forces the other functions, which without array-entry would normally act upon only the first element of any array passed to them, to instead operate over all elements within that array.
该技术涉及在公式的适当位置插入额外的索引函数。这迫使其他函数(没有array-entry的函数通常只作用于传递给它们的任何数组的第一个元素)对该数组中的所有元素进行操作。
However, whilst inserting a single INDEX function for the purpose of avoiding CSE is, in my opinion, perfectly fine, I think when it gets to the point where you're using two or three (or even more) such coercions, then you should probably re-think whether it's worth it all (the few tests that I've done suggest that, in many cases, performance is actually worse off in the non-array, INDEX-heavy version than the equivalent CSE set-up). Besides, the use of array formulas is something to be encouraged, not something to be avoided.
然而,当插入一个指数函数避免CSE的目的是,在我看来,非常好,我想的时候,你使用两个或三个(或更多)这样的强迫,那么你应该重新思考是否值得这一切(我所做的一些测试表明,在许多情况下,性能非数组实际上是更糟,INDEX-heavy版本比等效CSE设置)。此外，使用数组公式是值得鼓励的，而不是要避免的。
Sorry for the ramble, but it's kind of to the point actually since, if I had given you the array version, then you may well not have come back looking for an explanation, since that version would look like:
不好意思，我讲的有点跑题了，因为，如果我给你数组的版本，你可能不会回来找解释，因为那个版本会是这样的:
=INDEX(B4:B10,MATCH(TRUE,ABS(A4:A10-B1)=MIN(ABS(A4:A10-B1)),0))
=指数(B4:B10,匹配(真的,ABS(A4:A10-B1)= MIN(ABS(A4:A10-B1)),0))
which is objectively far easier syntactically to understand than the other version.
从语法上讲，这比其他版本更容易理解。
Let me know if that helps and/or you still want me to go through a breakdown of either solution, which I'd be happy to do.
让我知道这是否有帮助和/或你仍然想让我经历任何一个解决方案的分解，我很乐意这样做。
You may also find the following links of interest (I hope that I'm not breaking any of this site's rules by posting these):
你也可以找到以下链接的兴趣(我希望我没有打破这个网站的任何规则张贴这些):
https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas https://excelxor.com/2014/08/18/index-returning-entire-rowscolumns
https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas https://excelxor.com/2014/08/18/index-returning-entire-rowscolumns
Regards
问候
#1
8
I guess it makes sense for me to explain it, then!
我想我解释一下是有道理的!
Actually, it didn't help that I was employing a technique which is designed to circumvent having to enter a formula as an array formula, i.e. with CSE. Although that could be considered a plus by some accounts, I think I was wrong to employ it here, and probably wouldn't do so again.
实际上，我使用了一种技术，这种技术可以避免输入一个公式作为数组公式，例如CSE。虽然这可能被一些人认为是一个优点，但我认为我在这里使用它是错误的，而且很可能不会再这样做了。
The technique involves inserting extra INDEX functions at appropriate places within the formula. This forces the other functions, which without array-entry would normally act upon only the first element of any array passed to them, to instead operate over all elements within that array.
该技术涉及在公式的适当位置插入额外的索引函数。这迫使其他函数(没有array-entry的函数通常只作用于传递给它们的任何数组的第一个元素)对该数组中的所有元素进行操作。
However, whilst inserting a single INDEX function for the purpose of avoiding CSE is, in my opinion, perfectly fine, I think when it gets to the point where you're using two or three (or even more) such coercions, then you should probably re-think whether it's worth it all (the few tests that I've done suggest that, in many cases, performance is actually worse off in the non-array, INDEX-heavy version than the equivalent CSE set-up). Besides, the use of array formulas is something to be encouraged, not something to be avoided.
然而,当插入一个指数函数避免CSE的目的是,在我看来,非常好,我想的时候,你使用两个或三个(或更多)这样的强迫,那么你应该重新思考是否值得这一切(我所做的一些测试表明,在许多情况下,性能非数组实际上是更糟,INDEX-heavy版本比等效CSE设置)。此外，使用数组公式是值得鼓励的，而不是要避免的。
Sorry for the ramble, but it's kind of to the point actually since, if I had given you the array version, then you may well not have come back looking for an explanation, since that version would look like:
不好意思，我讲的有点跑题了，因为，如果我给你数组的版本，你可能不会回来找解释，因为那个版本会是这样的:
=INDEX(B4:B10,MATCH(TRUE,ABS(A4:A10-B1)=MIN(ABS(A4:A10-B1)),0))
=指数(B4:B10,匹配(真的,ABS(A4:A10-B1)= MIN(ABS(A4:A10-B1)),0))
which is objectively far easier syntactically to understand than the other version.
从语法上讲，这比其他版本更容易理解。
Let me know if that helps and/or you still want me to go through a breakdown of either solution, which I'd be happy to do.
让我知道这是否有帮助和/或你仍然想让我经历任何一个解决方案的分解，我很乐意这样做。
You may also find the following links of interest (I hope that I'm not breaking any of this site's rules by posting these):
你也可以找到以下链接的兴趣(我希望我没有打破这个网站的任何规则张贴这些):
https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas https://excelxor.com/2014/08/18/index-returning-entire-rowscolumns
https://excelxor.com/2014/09/01/index-an-alternative-to-array-cse-formulas https://excelxor.com/2014/08/18/index-returning-entire-rowscolumns
Regards
问候