MATCH在Excel公式中的使用 [英] usage of MATCH in Excel formula
问题描述
当我想要找出我的值范围内有多少个不同/唯一的项目时，这一切都开始了。经过一番研究，这个公式显然做得很好：
= SUM（IF（FREQUENCY（MATCH B10，B2：B10,0），MATCH（B2：B10，B2：B10,0））> 0,1））
其中计算单元格B2中唯一的文本和数字值的数量：B10（不能包含空白单元格）
我想要理解这个公式好一点，所以我复制了这部分并粘贴在别的地方：
MATCH（B2：B10， B2：B10,0）
这个评估为#Value！。
在这种情况下，excel如何完成工作？搜索MATCH的作品没有告诉我很多，因为例子需要一个价值而不是一个范围放在第一个参数...
对此的任何见解将非常感谢！
FYI - 对于那些有兴趣获得第一个公式的人：
https://support.office.com/en-us/article/Count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0- a929fbc1e273？CorrelationId = 4331e8f6-e4d5-4210-9b21-3ed53ee45a6d& ui = en-US& rs = en-US& ad = US
真的 - 但是绝大多数的功能都可以运行，而不仅仅是一个功能，而且还有一系列的价值。
几乎所有的情况这涉及将公式提交为数组公式（即使用CTRL + SHIFT） + ENTER），虽然包括FREQUENCY在内的一些功能具有先天的能力，但没有这种按键组合就会强制执行一系列的返回。
这就解释了你的#VALUE！错误：MATCH构造不会自行，评估传递给它的数组中的所有元素：它需要一些外部函数来强制并处理一个返回数组的结果，这是通过FREQUENCY函数实现的。
实际上，构造不是最有效的：重复MATCH子句for bins_array 没有必要，意味着施工资源过重。比较器（> 0）也不是严格必要的。更好的是：
= SUM（IF（FREQUENCY（MATCH（B2：B10，B2：B10,0）），ROW（B2：B10 ）-MIN（ROW（B2：B10））+ 1），1））
因为 - 特别是在较大的范围 - 尽管额外的函数调用，构造：
ROW（B2：B10）-MIN（ROW（B2：B10））+ 1
可能比一个涉及线性搜索的算法要快得多。
/ p>
PS如果你想要解释这个建筑是如何工作的，只需让我知道。
I have a question on how MATCH is used.
This initially all started when I want to find out "How many distinct/unique items are in my range of values". After a bit of research, this formula apparently does the job quite well:
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),MATCH(B2:B10,B2:B10,0))>0,1))
Which "Counts the number of unique text and number values in cells B2:B10 (which must not contain blank cells)"
I want to understand this formula a bit better, so I copied out this part and paste it somewhere else:
MATCH(B2:B10,B2:B10,0)
And this evaluates to "#Value!".
In this case, how is excel getting the job done in the first place? Searching up how MATCH works didn't tell me much, as the examples require a "value" and not a "range" to be put in the first argument...
Any insights into this would be much appreciated!
FYI - for those interested in got the first formula here: https://support.office.com/en-us/article/Count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0-a929fbc1e273?CorrelationId=4331e8f6-e4d5-4210-9b21-3ed53ee45a6d&ui=en-US&rs=en-US&ad=US
True - but the vast majority of functions can operate over, not just one, but also a range of values.
In almost all cases this involves committing the formula as an array formula (i.e. with CTRL+SHIFT+ENTER), though some functions, including FREQUENCY, have the innate ability to coerce an array of returns without this keystroke combination.
And that explains your #VALUE! error: the MATCH construction will not, on its own, evaluate over all elements in the array being passed to it: it requires some external function to both coerce, and process the results of, an array of returns, which is here achieved by the FREQUENCY function.
Actually that construction is not the most efficient: the repetition of the MATCH clause for the bins_array is not necessary, and means that the construction is unduly resource-heavy. Neither is the comparator (>0) strictly necessary. Better is simply:
=SUM(IF(FREQUENCY(MATCH(B2:B10,B2:B10,0),ROW(B2:B10)-MIN(ROW(B2:B10))+1),1))
since - and especially over larger ranges - despite the additional function calls, the construction:
ROW(B2:B10)-MIN(ROW(B2:B10))+1
is likely to be far quicker to calculate than one which involves linear searching.
Regards
P.S. If in addition you want an explanation as to how this construction works, just let me know.
这篇关于MATCH在Excel公式中的使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
