[原创]RANK的三维引用和COUNTIF的四维引用

Bodhidharma

apolloh 发表于 2006-2-26 12:47
红色部分有异议：
1、offset的第一参数使用一个单元格还是连续区域，能够影响到height或width参数的使用 ...

這篇實在太經典了，但是能夠真正理解的人恐怕不多。

我最近正在寫多維引用與內存數組的教學文件，希望將本篇的內容納入其中一節

以下為部分內容，請大家多多指教： 多維引用(二).rar (15.62 KB, 下载次数: 88)

2013-8-12 07:07 上传

点击文件名下载附件

4、类似于公式结果同单元格区域之间的隐含交叉引用，在函数内，参数间也存在着隐含交叉计算，是数组参数之间一一对应的运算关系。 函数参数也可这样划分为两大类：其中一类只支持单元格引用，不能输入数组或常量；另一类只支持常量或数组，把单元格区域引用作为常量或数组看待。 交叉计算，实际上是同类或两类参数之间的数组对应运算。几乎每个函数都存在交叉计算的情况。例如，在SUM(A1:B10*C2:D11)这个公式里，就存在参数的交叉计算，是各行各列的单元格一一对应，属于第二类参数内部的隐含交叉计算。 又如，Rank(A1:F5,offset(A1:F1,ROW(A1:F5)-ROW(A1:F1),))，这个公式的情况是，第一类参数和第二类参数之间存在着交叉计算。其中offset的多维引用被看作是，多个连续引用区域组成的“区域数组”，这个概念在[浅谈引用函数产生的多维引用及其应用]中已有提及。 还有一些情况，我们以后再说。需要强调的是，交叉计算的实质是两个或多个“数组”的行列对应计算。这里的说的数组，可以是“区域数组”，也可以是“纯数组”。

這段話字字珠璣，讓我們仔細分析一下

函数参数也可这样划分为两大类：其中一类只支持单元格引用，不能输入数组或常量；另一类只支持常量或数组，把单元格区域引用作为常量或数组看待。

以RANK(number,ref)為例，其第二個參數為ref，只支持單元格引用，不能輸入數組或常量；第一個參數number則支持常量或數組

至於COUNTIF(range,criteria)，其第一個參數為range，只支持單元格引用；第二個參數criteria數支持常量或數組

这里的说的数组，可以是“区域数组”，也可以是“纯数组”。

純數組很好理解，就是一般的數組。至於"區域數組"，則是由多個平面所組成的數組

比方說=RANK(C14:K22,OFFSET(C14:K14,ROW(C14:K22)-ROW(C14:K14),,,))的第二個參數

就是由九個平面所構成的9*1區域數組：{C14:K14;C15:K15;C16:K16;C17:K17;C18:K18;C19:K19;C20:K20;C21:K21;C22:K22}

至於一般的用法，比方說=RANK(C14,C14:K22)，其第二個參數是由C14:K22構成的1*1區域數組

RANK中的這兩個參數依其維度不同，同樣會有行列擴充以及隱含交叉的出現，以下舉幾個例子：

RANK(C14,C14:K22)，兩個參數都是1*1，因此就直接計算C14在C14:K22之中的排名

RANK(C14:K14,C14:K22)，第一個參數是1*9，第二個參數是1*1(一個平面)，在這個情況下，第二個參數會自動擴充為1*9個相同的平面

因此會得到{RANK(C14,C14:K22),RANK(D14,C14:K22),RANK(E14,C14:K22)…,RANK(K14,C14:K22)}這個1*9的數組

RANK(C14:K22,C14:K22)，第一個參數是9*9，第二個參數是1*1，在這個情況下，第二個參數會自動擴充為9*9的相同平面

以上的情形應該都很好理解，接下來進到第二個參數是多維引用(區域數組)的情形

RANK(C14,OFFSET(C14:K14,ROW(C14:K22)-ROW(C14:K14),,,))，第一個參數是1*1，第二個參數是9*1(9個平面)

因此第一個參數自動擴充為9*1個相同的數，分別對應9個平面

RANK(C14:C22,OFFSET(C14:K14,ROW(C14:K22)-ROW(C14:K14),,,))，兩個參數都是9*1，因此一一對應

RANK(C14:K14,OFFSET(C14:K14,ROW(C14:K22)-ROW(C14:K14),,,))，第一個參數是1*9，第二個參數是9*1

因此第一個參數會向下擴充成9*9；第二個參數會向右擴充成9*9，之後再一一對應

如果以上都能夠理解，原公式RANK(C14:K22,OFFSET(C14:K14,ROW(C14:K22)-ROW(C14:K14),,,))應該就很好理解了

第一個參數是9*9，第二個參數是9*1，因此第二個參數會向右擴充成9*9，之後再一一對應

至此，應該就可以理解apolloh板主所說的，「第一個參數和第二個參數內部的隱含交叉計算」的意思了

有了以上概念，對於COUNTIF的四維引用及隱含交叉，應該就不難理解了

COUNTIF(OFFSET(C14:K14,ROW(C14:K22)-ROW(C14:K14),,,COLUMN(C14:K14)-COLUMN(B14)),C14:K22)

當OFFSET的第三、四個參數有值的時候，第一個參數只會取最左上方，因此上式等同於(這裡可參考apolloh與gdliyy的討論)

COUNTIF(OFFSET(C14,ROW(C14:K22)-ROW(C14:K14),,,COLUMN(C14:K14)-COLUMN(B14)),C14:K22)

在OFFSET的部分，原式等同於OFFSET(C14,{0;1;2;3;4;5;6;7;8},,,{1,2,3,4,5,6,7,8,9})，行列會自行擴充，成為

{OFFSET(C14,0,,,1),OFFSET(C14,0,,,2),…,OFFSET(C14,0,,,9);OFFSET(C14,1,,,1),…,OFFSET(C14,1,,,9);…;OFFSET(C14,8,,,1),…OFFSET(C14,8…9})

這個9*9的區域數組，成為COUNTIF的第一個參數，而COUNTIF的第二個參數(C14:K14)又是9*9，因此剛好能夠一一對應

當RANK(...)+COUNTIF(…)小於5的時候，就代表該位置是前三高的數字，因此再用MMULT取其和後，取最大值即為所求

經過以上的說明，希望大家能夠理解RANK與COUNTIF函數，其兩個參數間的一一對應關係("區域數組"與"純數組"之間的對應)

最後以apolloh板主在[函数用法讨论系列16] Countif函数内部参数隐含交叉计算问题  的一段話作結：

http://club.excelhome.net/forum.php?mod=redirect&goto=findpost&ptid=145007&pid=995177

如果一个函数有两个以上的参数可以为区域引用，且其中一个参数是多维引用，另一个参数的引用行列中一个方向与此多维引用产生的区域排列方向一致，那么就会缠产生隐含交叉计算问题。

这类似于直接存入单元格中的两个引用的隐含交叉，只是它直接在函数内部计算完成。

目前发现，支持在函数内部产生隐含交叉计算的有Rank，Countif，Sumif等。

dodo2029

没能理解其中奥秘,先留名.

chenjiawei5000

好贴，先收藏了再说。

Bodhidharma

chenjiawei5000 发表于 2013-8-30 16:22
好贴，先收藏了再说。

這個問題問得很好，這是出自apolloh的另一篇的文字
[原创] 浅谈引用函数产生的多维引用及其应用
http://club.excelhome.net/thread-98081-1-1.html

在OFFSET函数的参数中使用数组，需要注意以下规则：
（1） 参数rows、cols、height和width既可单独，也可部分或全部使用数组。
（2） 在两个以上参数中使用数组，所有数组的维数必须一致。
（3） 在两个以上参数中使用相同维度的数组，则数组的尺寸必须相同。
（4） 在两个以上参数中使用一维数组，若数组的维度不同，则尺寸可以不同。
（5） 所有参数中的数组都呈纵向或横向排列，则函数返回的以各区域为元素的“数组”也呈纵向或横向排列。
（6） 所有参数中的数组都呈二维排列，则函数返回的以各区域为元素的“数组”也呈二维排列。
以上规则中，如果符合第（5）条，则函数结果生成三维引用；如果符合第（4）或（6）条，则函数结果生成四维引用。

這邊我第一次讀的時候也是頭昏腦脹，不過其實這只是在談數組的行列擴充(或是自適應性)的原理
OFFSET(A1,{1,2,3},{4;5})，會自動擴充成OFFSET(A1,{1,2,3;1,2,3},{4,4,4;5,5,5})，這個是(4)的內容
OFFSET(A1,{1,2,3;4,5,6},{7,8})，會自動擴充成OFFSET(A1,{1,2,3;4,5,6},{7,8,#N/A;7,8,#N/A})，因為{7,8}無法向右擴充，因此會出錯
不過OFFSET(A1,{1,2,3;4,5,6},{7;8})，就可以擴充成OFFSET(A1,{1,2,3;4,5,6},{7,7,7;8,8,8})
如果以上能夠了解，這六條規則應該不難理解…當然(2)的說法並不完全正確，你的這個說法是沒錯的

-------------------------------------------------------------------------
咦，你怎麼把原文修掉了？

sky0112050

以我现在的功力，实在是看不懂

xLivfe

只知道一个字:强

龙城飞将III

深深拜服，看两位版主激烈交锋，好似读一本武侠小说，引人入胜。

willin2000

lgcmeli 发表于 2010-3-6 14:08
再次领略神奇的函数。

不知道当初的背景,貌似很简单的:

1. =MAX(MMULT(MOD(SMALL(D6:L14+ROW(1:9)*200,ROW(1:9)*9-{0,1,2}),200),{1;1;1}))

复制代码

zhuang0598

留下以后有空学习。

zhxj1983

看不懂，太厉害了