使用INDEX MATCH找到绝对最接近的值 [英] Use of INDEX MATCH to find absolute closest value
问题描述
我在这篇文章。 XOR LX的解决方案：
= INDEX（B4：B10，MATCH（TRUE，INDEX（ABS（A4：A10-B1）= MIN INDEX（ABS（A4：A10-B1）,,））,,），0））
我不知道为什么我可以理性化大部分，但我无法弄清楚这一部分。
INDEX（ABS（A4：A10-B1）= MIN INDEX（ABS（A4：A10-B1）,,））
有人可以解释这部分吗？
我想这是有道理的，我解释一下，然后！ >
实际上，我没有帮助我使用一种技术，旨在规避必须输入公式作为数组公式，即使用CSE，虽然可以被认为是加上一些帐户，我想我在这里使用它是错误的，可能不会再这样做。
这项技术涉及在额外的INDEX函数中插入公式。这将强制其他函数，其中没有数组条目通常只会对传递给它们的任何数组的第一个元素起作用，而是对该数组中的所有元素进行操作。
然而，为了避免CSE而插入一个INDEX函数，在我看来是完全正确的，我想当它到达你'使用两三个（甚至更多）这样的胁迫，那么你应该重新考虑是否值得一试（我做的几个测试表明，在许多情况下，性能实际上更糟糕的是， em> off在非阵列中，INDEX-heavy版本比等效的CSE设置）。此外，使用数组公式是值得鼓励的，而不是值得避免的事情。
对不起，给予你数组版本的 ，那么你可能不会再回来寻找一个解释，因为这个版本会是这样的：
= INDEX（B4：B10，MATCH（TRUE，ABS（A4：A10-B1）= MIN（ABS（A4：A10-B1）），0））
<这在客观上比语言更容易理解。
让我知道，如果这有帮助和/或你还要我通过任何解决方案的细分，我很乐意做到这一点。
您还可以找到以下感兴趣的链接（我希望通过发布这些链接不会违反本网站的任何规则）：
http://excelxor.com/2014/08/23/index-an-alternative-to-array-cse-formulas/
http://excelxor.com/2014/08/ 23 / index-returns-whole-rowscolumns /
注意
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),,))
Can anyone explain this part? I hate blinding using it and know it'll be useful in the future.
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.
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.
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))
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):
http://excelxor.com/2014/08/23/index-an-alternative-to-array-cse-formulas/ http://excelxor.com/2014/08/23/index-returning-entire-rowscolumns/
Regards
这篇关于使用INDEX MATCH找到绝对最接近的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
