[答疑解惑]函数公式解释专用帖

snowpeak · 发表于 2006-4-28 13:16

zxq11142006-4-28 12:12:48 求助：如果我想把A列和B列的位置对调一下，除了插入一列的方法还有别的方法吗？

to 48楼：

选中A列，按住shift，同时鼠标点中选中的A列的边缘托动，你会看到一个虚线的“工”字，托到需要的地方，松开鼠标就可以了；

这个操作对单个，多个单元格区域同样有效。

[此贴子已经被gouweicao78于2006-6-6 16:26:00编辑过]

gouweicao78 · 发表于 2006-4-29 16:43

to cwl75：

首先：这是一个“多条件计数”公式=sum（条件1*条件2）数组公式，即求满足两个条件的个数。

E35:E43>=96即语文成绩96分及以上。

I35:I43是班级——用Left公式得到的，属性：文本型数字。

B47也是班级——属性:数值型数字。

所以用Text(B47,0)将B47转化为文本。当然，也可以用*(--(I35:I43)=B47)即将I列的班级名转化为数值型数字。

或者用""&B47或者B47&""将B47转化为文本。

比如：=SUM(($E$35:$E$43>=96)*($I$35:$I$43=""&$B47))——数组公式。

完整理解就是：班级名=B47、语文成绩>=96分的人数。

关于文本、数值的阐述请参看本帖第4楼。

to 53楼：为节省空间在此楼回答 sum——求和函数，没错。条件1*条件2*…… “条件”——即逻辑判断，产生的是逻辑值，逻辑值的运算遵循True+True=2、True+False=1、True*True=1、True*False=0、True/False=#DIV/0！错误、False/True=0等。所以条件1*条件2*……得到的是有True和False组成的数组相*，如果多个条件都满足即True*True=1、有不满足的就是0 然后Sum就对{1,0,1,……}求和得到满足所有条件的个数——也就是计数了。如果=sum(条件1*条件2*……*求和区域)得到的就是多条件加总的公式了。

[此贴子已经被作者于2006-4-29 20:09:08编辑过]

chenhuafu · 发表于 2006-4-30 00:21

To cw175：

假定A1:A10有如下数值 1 2 3 4 5 6 7 8 9 10

那么，我想知道大于5的值有多少个？

可以输入公式 =sum(--(A1:A10>5)) 当然要以数组公式结束（Ctrl + Shift + Enter）

如果想知道 >2 且 <8 的值有几个？

输入： =sum((A1:A10>2)*(A1:A10<8)) 也是数组公式

gouweicao78 · 发表于 2006-4-30 09:24

在2006-4-29 22:28:49，cwl75给您发送的消息！

想问一下，此贴第21楼的一个问题，其中的I2=LOOKUP(1,0/(材料名称=H2),单位)这个怎么理解，谢谢！就是LOOKUP的查找机制，我没搜到。

为何不用=LOOKUP(H2，材料名称，单位)呢？ 1,0/(材料名称=H2)怎么理解呢？

首先，你要认真再看看Lookup查找策略一帖，尤其是8楼的流程图和47楼的流程图修正及模拟。我想，那边讲的会比这里更清楚。

第二个问题：为何不用=LOOKUP(H2，材料名称，单位)，这个是Lookup函数的向量语法。不管是向量语法或者数组语法都要求第2参数按升序排序，因为实际上表格里的“材料名称”并未排序，所以不能用这个简单、直接的公式来完成。

理解：首先看分子——(材料名称=H2)——这是一个“条件”即产生逻辑值数组{True,False……}之类的，0/True=0，0/False=#DIV/0！,也就是说0/（条件）返回的只有一些0和错误值组成的数组，0永远比1（第1参数）小，Lookup忽略错误值，它在0和错误值的数组中寻找1，如果数据是无穷多的话，它将永远地找下去，而数据当然是有限的，找呀找[em30]，找不着1只能认数组里出现的最后一个0了。——最后一个0意味着什么，意味着最后一个0/True，意味着最后一个“条件”返回True的情况。

所以=Lookup(1,0/（条件……），引用区域)返回的是最后一个满足条件的记录。

与Vlookup精确查找（最后参数为False）不同的是，Vlookup返回第一个满足条件的记录。（如果有多个满足条件的记录就知道区别了）

gouweicao78 · 发表于 2006-4-30 11:41

to 57楼：=INDIRECT("Sheet2!B4")，注意1楼的规则，这里是“解惑”不是“解题”，呵呵。请尽量少占用楼层，可以用前面你比较空的楼层来发出新的提问，并用短信息通知我或者他人来此解答。谢谢配合。这个问题的原因看Inidrect函数帮助：

INDIRECT(ref_text,a1)：Ref_text 为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文本字符串单元格的引用。

因为你的sheet2!B4单元格的字符串是C1，所以相当于=Inidrect("C1")=C1单元格的值，C1为空，所以返回0，若要返回空，加&""，前面41楼有说明。

to 57楼新补充：你sheet2!B4单元格存放的是"C1"字符，所以要返回sheet2!C1单元格的话，应该把sheet2!B4单元格的字符改为"sheet2!C1"，然后在sheet1用=indirect(sheet2!B4)才可以引用到那个200。

to 56楼等：注意1楼的规则，要感谢的话就把钱打到我卡里[em33]，呵呵。要感谢的话——请认真学习并在论坛帮助他人就可以了。

to 楼下的“折翼蚊子”——请看41楼。

[此贴子已经被作者于2006-4-30 15:53:44编辑过]

折翼蚊子 · 发表于 2006-4-30 15:15

=INDEX(年段!B:B,SMALL(IF(年段!$A$1:$A$550=1,ROW($1:$550),65536),ROW(1:1)))&""

帮我解释下，特别是最后的&""的含义

to “折翼蚊子”——请看本帖41楼。——删除部分楼层后在27楼

[此贴子已经被gouweicao78于2006-6-6 16:34:00编辑过]

四海飘零 · 发表于 2006-5-2 08:28

关于“隐含交叉” “交叉”有两种，一个是“显式交叉”，就是在公式本身就体现了引用区域的交叉。我们做个最简单的例子：在A1:C10中存放有数据（数值），下面这个公式：SUM(A1:C10)会计算所有的数值之和；而SUM(A1:B5 B2:C10)【注意这个公式中A1:B5与B2:C10之间有一个空格】却只计算B2:B5之和，这是因为A1:B5与B2:C10这两个区域之间只有B2:B5这一部分是交叉的，点击编辑栏里的公式，就可以看到这种交叉的情况，SUM计算的就是交叉范围内的数值之和。

这种情况就是公式本身指定了的，也是我们所期望得到的“显式交叉”。而另外一种交叉，在公式本身并不体现，它只和公式所在的行列位置有关。比如，令A1:A5={1,2,3,4,5}，在B1写公式=A1:A5，不按三键就会得到结果1,而同样的公式如果写在B3里，会得到结果3。这就是因为“隐含交叉”的原因：B1的公式与引用区域A1:A5在第一行交叉了，所以它只显示A1，B3同理。如果同样的公式按数组公式，按ctrl+shift+enter结束就不会这样。一般来说，这不是我们期望得到的结果，但也有人在特殊情况下利用这个特性来简化公式，很久以前的一个函数测试题好像就有人用过。现在再来看chenjun的OFFSET函数的帖子，就应该明白了：chenjun为什么把列公式用箭头引到M20:M25区域来显示，因为只有这个区域和数据源区域是有隐含交叉的。如果把这个公式放到其他的行上，比如N30:N35，由于不存在这种隐含交叉，显示结果也不是这样了。如果我们把这个公式按数组公式输入，就不存在隐含交叉，因而也能按我们的期望来显示了——不管公式放在哪里。

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

apolloh · 发表于 2006-5-2 17:25

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

gouweicao78 · 发表于 2006-5-4 23:37

to 稻草人367：原帖链接

这是SUMif三维引用的经典用法

=SUM(SUMIF(INDIRECT({"A","B","C","D"}&"!L4:L100"),$A4&$B4,INDIRECT({"A","B","C","D"}&"!R4C"&COLUMN()&":R100C"&COLUMN(),)))

主要在Sumif——第1参数黄色，第2参数绿色，第3参数红色：

我们知道=Sumif(A4:A100,B4,C4:C100)——这么一个最常见的Sumif的基本用法，表示对“A列中项目和B4单元格相同所对应的C列的数值”进行条件求和。

第一参数：Indirect("A!L4:L100")——引用的是A表的L4:L100区域，Indirect({"A","B","C","D"}&"!L4:L100")引用的是A、B、C、D表的L4:L100区域，是4个区域。注意：我在ABCD这4个表的L列设置了辅助列，将序号和颜色合并文本。

第二参数：A4&B4——序号和颜色合并文本。

第三参数:还是Inidrect引用，只不过引用样式改成了R1C1引用样式。比如Indirect("A!R4C3:R100C3",0)——表示引用A表的“第4行第3列到第100行第3列”即C4:C100区域。为了右拖复制的时候，第3列能够自动变为第4列、第5列……，则把R4C3改为"R4C"&column()——column()取得当前单元格所在列号。所以：第三参数引用的是A、B、C、D表的C4:C100，并且可以随着向右拖动复制变为D4:D100、E4:E100等区域。

这么一来，Sumif得到的就是4个表中每个表分别对应序号和颜色的数量总数，然后再用SUM对这4个表的总数（相当于每个表的小计）求和，就是4个表——满足序号和颜色与A4、B4单元格一致的数量汇总了。

结合gvntw版主的Lookup在内存数组中的处理，可以用Lookup和宏表函数取得无规律工作表名（除去汇总表）的其他工作表名数组，并由此替代上面的{"A","B","C","D"}之类的，则应用范围更广！

实例见http://club.excelhome.net/viewthread.php?tid=165500&px=0

[此贴子已经被作者于2007-5-10 8:36:27编辑过]

waqh6797 · 发表于 2006-5-5 17:12

楼主解释这个多单元格数组公式的运算过程:

选定a1:a4={small({1,2,3,6},{1,2,3,4})}我用公式审核——公式求值看了一下，在这四个单元格中，每一步的计算过程都相同，如={small({1,2,3,6},1）}，但结果为什么就不同呢？a1=1,a2=2,a3=3,a4=6.

waqh67972006-5-5 23:47:55 谢谢楼主,经过你的解释,对数组的理解清晰多了,辛苦你了.

[此贴子已经被gouweicao78于2006-6-6 16:39:16编辑过]

		自动登录	找回密码
密码			免费注册

[推荐] [答疑解惑]函数公式解释专用帖