编程技术网

关注微信公众号,定时推送前沿、专业、深度的编程技术资料。

 找回密码
 立即注册

QQ登录

只需一步,快速开始

极客时间

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

Magick 集合 2022-5-11 10:48 9人围观

腾讯云服务器
排名与Rank.EQ函数-数组引用错误的处理方法

背景:

关于在 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函数-数组引用错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程技术网(www.editcode.net)!

腾讯云服务器 阿里云服务器
关注微信
^