使用 INDEX MATCH 找到绝对最接近的值 [英] Use of INDEX MATCH to find absolute closest value
问题描述
我一直在寻找一种在 Excel 中使用 INDEX MATCH 返回数组中绝对最接近的数字而不重新组织我的数据的方法(因为 MATCH 需要 lookup_array 降序查找大于lookup_value 的最近值，升序查找小于lookup_value 的最近值).
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).
I found the answer in this post. XOR LX's solution:
=INDEX(B4:B10,MATCH(TRUE,INDEX(ABS(A4:A10-B1)=MIN(INDEX(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),,))
谁能解释一下这部分?
推荐答案
我想我应该解释一下，然后！
I guess it makes sense for me to explain it, then!
实际上，我采用了一种旨在避免必须将公式作为数组公式输入的技术，即使用 CSE，这并没有帮助.尽管某些帐户可能认为这是一个加分项，但我认为我在这里使用它是错误的，并且可能不会再这样做了.
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.
该技术涉及在公式中的适当位置插入额外的 INDEX 函数.这会强制其他函数，这些函数没有数组入口，通常只对传递给它们的任何数组的第一个元素起作用，而是对该数组中的所有元素进行操作.
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.
然而，虽然为了避免 CSE 插入单个 INDEX 函数，在我看来，完全没问题，但我认为当您使用两个或三个(甚至更多)这样的强制时，那么您可能应该重新考虑这一切是否值得(我所做的少数测试表明，在许多情况下，在非数组、INDEX-heavy 版本中性能实际上更糟比等效的 CSE 设置).此外，数组公式的使用是值得鼓励的，而不是避免的.
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.
抱歉我的乱说，但实际上有点切题，因为如果我给了你数组版本，那么你很可能不会回来寻找解释，因为那个版本看起来像:
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))
=INDEX(B4:B10,MATCH(TRUE,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-formulashttps://excelxor.com/2014/08/18/index-返回整个行列
问候
这篇关于使用 INDEX MATCH 找到绝对最接近的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
