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

apolloh

以下是引用[I]gdliyy[/I]在2006-2-22 2:26:46的发言：
。。。4、最后我们再来看Apolloh兄的那段函数公式代码： COUNTIF(OFFSET(D6:L6,ROW(D6:L14)-ROW(D6:L6),,,COLUMN(D6:L6)-COLUMN(C6)),D6:L14) 当然，在这里我们也主要看其中的Offset()构造函数，其实在Offset()函数中，您只要对引用区域动态指定行高或列宽，那么对于基点的引用，你用1个单元格和1系列连续单元格作基点，对于系统来说都是一样的，系统自动会识别为“引用区域的左上角单元格”来作为基点的。 因此，Offset()简化为：OFFSET(D6,ROW(D6:L14)-6,0,1,COLUMN(D6:L6)-3) 1)从这个公式来看，按前面的几何概念可以理解为：系统首先应该是用动态列宽参数先构造（D6、D6:E6、D6:F6、D6:G6、D6:H6、D6:I6、D6:J6、D6:K6、D6:L6）9个区域－－（点->线、线->面）过程，我们可以通过转换为图形来看－－是否更象一个梯形。 2)接下来，Offset()再通过行偏移参数来产生引用区域的行偏移，即在立体空间上看成：在垂直方向上再次构造9个面－－（面->体），看似梯形体－－楼梯，有点象吧：） 3)从而产生新的（9*9）引用区域来实现分别统计计算。 但大家千万别忘记了在前面的Countif()中还有一个隐藏的交叉引用来实现对D6:L14各个值的统计。

红色部分有异议： 1、offset的第一参数使用一个单元格还是连续区域，能够影响到height或width参数的使用，如果是连续区域，一般可省略写height或width参数，也就是两个逗号都不必要用了。 2、直接使用常数的话，当表格上方或左方插入或删除行时公式如不作相应的修改就可能会出错。 3、多维引用不是在这里第一次提到，在[浅谈引用函数产生的多维引用及其应用]就已经讨论过了。理论上可以产生4维甚至5维以上的多维引用，但遗憾的是Excel工作表函数只能处理到4维。维是方向，只有一个单元格的引用不能算维数，因为其没有方向。所以维应该从线算起，而不是点，点只能算是元素。 一维为线，有行或列两个方向取其一 二维为面，由行和列两个方向构成 三维为体，由行、列、层三个方向构成，这里的体实际上已经是一个空间了。 四维为体的线状排列，有行或列两个方向取其一 五维为体的面状排列，由行和列两个方向构成 六维为体的体状排列，由行、列、层三个方向构成，是以空间体为元素再构造出一个空间。有点像把地球看作一个点，放在银河系中一样。 7，8，9维。。。。以此类推，道理相同。只可惜Excel只能处理到4维引用。 4、类似于公式结果同单元格区域之间的隐含交叉引用，在函数内，参数间也存在着隐含交叉计算，是数组参数之间一一对应的运算关系。 函数参数也可这样划分为两大类：其中一类只支持单元格引用，不能输入数组或常量；另一类只支持常量或数组，把单元格区域引用作为常量或数组看待。 交叉计算，实际上是同类或两类参数之间的数组对应运算。几乎每个函数都存在交叉计算的情况。例如，在SUM(A1:B10*C2:D11)这个公式里，就存在参数的交叉计算，是各行各列的单元格一一对应，属于第二类参数内部的隐含交叉计算。 又如，Rank(A1:F5,offset(A1:F1,ROW(A1:F5)-ROW(A1:F1),))，这个公式的情况是，第一类参数和第二类参数之间存在着交叉计算。其中offset的多维引用被看作是，多个连续引用区域组成的“区域数组”，这个概念在[浅谈引用函数产生的多维引用及其应用]中已有提及。 还有一些情况，我们以后再说。需要强调的是，交叉计算的实质是两个或多个“数组”的行列对应计算。这里的说的数组，可以是“区域数组”，也可以是“纯数组”。

[此贴子已经被作者于2006-5-2 17:31:51编辑过]

gdliyy

1、对你第1点我还是有异义，可能是黄兄没注意我说的前提：“您只要对引用区域动态指定行高或列宽”，确切点说，就是在Offset()函数中指定了行高或列宽，那么你的基点引用一点，还是引用一个区域，对系统来说都是一样的。
如：Countif(Offset(A1:A10,1,1,{1;2;3}),"<>") 与 Countif(Offset(A1,1,1,{1;2;3}),"<>")
这两个公式计算的结果是相同的，同理原公式段中：
OFFSET(D6:L6,ROW(D6:L14)-6,0,1,COLUMN(D6:L6)-3) 与 OFFSET(D6,ROW(D6:L14)-6,0,1,COLUMN(D6:L6)-3)
我们可以看到：你的公式中引用区域与区域的变化方向是相同的(同是列方向的变化)，那么基点就没有必要引用D6:L6了。
当然如果公式是这样就不同了：
OFFSET(D6:D7,ROW(D6:L14)-6,0,1,COLUMN(D6:L6)-3)，引用区域是两行，那么所动态产生的引用都是2行1列、2行2列。。。2行9列的范围了。

2、如果黄兄不同意“点到线、线到面”的说法，那么这个公式=Countif(Offset(A1,1,1,{1;2;3}),"<>")，我们就只能看成重新构造引用区域，我们视其为“三维引用”--我只是将其形象化，可能使大家好理解一些。

apolloh

关于1，可以把公式改造一下，可能会比较好理解我说的意思 Countif(Offset(A1:C10,1,1,{1;2;3}),"<>") 与 Countif(Offset(A1,1,1,{1;2;3},3),"<>")达到的效果是一样的，但后者必须制定width参数。所以我们不能说第一个参数分别使用区域和区域左上角的第一单元格， “对系统来说是一样的”。 关于2， 用“三维，四维，多维”来表示引用函数虚拟产生的区域引用集合，不只是形象化问题。引用函数产生的这些区域引用集合中，区域之间的排列存在着横向，纵向和层次等方向上的变化，所以称之为“多维引用”。

gdliyy

以下是引用[I]apolloh[/I]在2006-2-26 16:00:50的发言：
关于1，可以把公式改造一下，可能会比较好理解我说的意思 Countif(Offset(A1:C10,1,1,{1;2;3}),"<>") 与 Countif(Offset(A1,1,1,{1;2;3},3),"<>")达到的效果是一样的，但后者必须制定width参数。所以我们不能说第一个参数分别使用区域和区域左上角的谝坏ピ?瘢? “对系统来说是一样的”。 关于2， 用“三维，四维，多维”来表示引用函数虚拟产生的区域引用集合，不只是形象化问题。引用函数产生的这些区域引用集合中，区域之间的排列存在着横向，纵向和层次等方向上的变化，所以称之为“多维引用”。

第1点，这与我的解释一点都不矛盾，你的A1:C10，已经涉及到行与列，而且行高和列宽都通过数值指定，而且你在第5个参数中指定的列宽，刚好与实际引用相同，所以不能说明问题。如： Countif(Offset(A1:C10,1,1,{1;2;3}),"<>") 与 Countif(Offset(A1,1,1,{1;2;3}),"<>")肯定不同的，这样的效果是不一样的，因为你的公式中已经在行高和列宽上都有变化了； Countif(Offset(A1,1,1,{1;2;3},3),"<>")这只能相当于：Countif(Offset(A1:C1,1,1,{1;2;3}),"<>") 这个是较好的一个例子，请你解答：COUNTIF(OFFSET(A1:C10,1,1,{1;2;3},5),"<>")，这样的公式生成什么样的一个内存数组？当然我们一般不会这样写的：）

[此贴子已经被作者于2006-2-27 12:40:25编辑过]

apolloh

我再看一次，你原回复中 “对系统来说是一样的”前面有一大堆限定条件，而我只是想说明“对系统来说是不一样的”，在没有哪些限定条件的情况下。

你说的COUNTIF(OFFSET(A1:C10,1,1,{1;2;3},5),"")。大家一般不会这么写吧

可以写成

COUNTIF(OFFSET(A1:E10,1,1,{1;2;3}),"")

或

COUNTIF(OFFSET(A1,1,1,{1;2;3},5),"")

当然也可以写成

COUNTIF(OFFSET(A1:E1,1,1,{1;2;3}),"")

gdliyy

呵呵。。。当然是有条件的：）

当你的引用基点是多列，行高是动态变化的时候，一般建议公式写成：
COUNTIF(OFFSET(A1,1,1,{1;2;3},5),"")
或：
COUNTIF(OFFSET(A1:E1,1,1,{1;2;3}),"")

我们不建议这样写：COUNTIF(OFFSET(A1:E10,1,1,{1;2;3}),"")，前面的基点区域A1:E10会对使用者造成误解，因为实际用来产生偏移（或重新生成）区域的基点只是A1:E1。

apolloh

gdliyy兄的这种庖丁解牛式的钻研精神，值得我们大家来学习：）

[此贴子已经被作者于2006-2-27 12:50:03编辑过]

EXCEL-2005

头大的很！！

braverfang

太精彩了!!!!!我为你们喝彩

sahelo

看不懂！

惭愧！