排名与Rank.EQ函数-数组引用错误 [英] Rank & Rank.EQ function - Array reference error

问题描述

_背景:

关于在 RANK 或 RANK.EQ 函数中使用数组引用的简短问题.

Short question on the use of array references within RANK or RANK.EQ function.

两个函数都具有以下参数: ....(number，ref，[order])

Both function got the following parameters: ....(number,ref,[order])

Ms.Documentation RANK 和 RANK.EQ 中的 Ref 参数的a>:

Ms.Documentation about the Ref parameter within RANK and RANK.EQ:

"引用必需.数字列表的数组或对数字列表的引用.ref中的非数字值将被忽略."

"Ref Required. An array of, or a reference to, a list of numbers. Nonnumeric values in ref are ignored."

此外，我通常会强烈建议其他网站声明在 Ref 参数中可以使用数组或列表.

Also, other websites I would normally highly recommend, would state that the use of an array or list is possible within the Ref parameter.

_{样本数据:}

| Rank1 | Rank2 | Overall |
|-------|-------|---------|
| 1     | 3     |         |
| 1     | 1     |         |
| 3     | 2     |         |

---

_{尝试过的功能:}

根据文档， C2 中的以下内容应该起作用:

According to the documentation, the following in C2 should work:

=RANK(A2+B2,INDEX(A$2:A$4+B$2:B$4,))

这计算为(使用内置函数):

This evaluates to (using the build-in function):

=RANK(4,{4,2,5})

但是返回，错误 #VALUE .

_问题:

我们能否声明仅范围引用作为 Ref 参数起作用，而数组根本不起作用?还是我在这里错过了一些重要的信息?任何规范的回答都值得欢迎，我只想确保我的假设正确无误.

Can we state that only range references work as the Ref parameter and arrays simply don't work? Or am I missing some important piece of information here? Any canonical answer is welcome, I just want to make sure my hypothesis is correct.

对于这种特定情况，要复制 RANK ，我将不得不使用类似的内容:

For this specific case, to replicate the RANK, I would instead have to use something like:

=SUMPRODUCT(--(($A$2:$A$4+$B$2:$B$4)<(A2+B2)))+1

推荐答案

数组不起作用.据我所知，他们从来没有.该文档是错误的.

Arrays don't work. They never have either, to the best of my recollection; the documentation is just wrong.

这篇关于排名与Rank.EQ函数-数组引用错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！