如何直接对数组进行RANK?我想避免在单元格中创建更多中间数据只是为了引用它们。
Excel RANK.AVG 公式表明它接受数组和引用:
Syntax
RANK.AVG(number,ref,[order])
The RANK.AVG function syntax has the following arguments:
Number Required. The number whose rank you want to find.
Ref Required. **An array of, or a reference to**, a list of numbers. Nonnumeric values in Ref are ignored.
Order Optional. A number specifying how to rank number.
但是Excel一直拒绝下面的公式。
=RANK.AVG(5, {3,1,7,10,5})
如果数字放入单元格中,例如 B1:B5,Excel 会接受
=RANK.AVG(5, B1:B5}
最终,我想对动态数组进行排名
=RANK.AVG(value, TOCOL(VSTACK(array1, array2))
e.g. =RANK.AVG(5, TOCOL(VSTACK(B1:B5,C1:C10))