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

chrisfang

回复95楼：

解牛步骤一：

=INDIRECT("'"&LEFT(A2,3)&"'!"&ADDRESS(64,RANK(VALUE($D$1),'001'!$C$1:$BV$1,1)+2))

黄色部分是一个rank函数，rank函数的功能是进行排名计算，第一参数VALUE($D$1)指的是需要计算排名的某个数据；第二参数是一个数据区域，在sheet名为001的表上的C1:BV1这一行；第三参数为1，指的是排序方式为升序(数值越大名次数字越大)。黄色公式部分的完整含义就是计算D1单元格数值在001的表上的C1:BV1这一行中的排名名次，其中D1单元格的数值必须包含在C1:BV1区域之中。

解牛步骤二：

=INDIRECT("'"&LEFT(A2,3)&"'!"&ADDRESS(64,RANK(VALUE($D$1),'001'!$C$1:$BV$1,1)+2))

绿色区域公式可简化为Address(64,名次+2)，address函数的功能是取单元格的地址，第一参数为目标所在行，第二参数为目标所在列。这个公式取的就是第64行，第(名次+2)列的单元格地址。

解牛步骤三：

最后来看完整公式，可以简化成以下形式：

=INDIRECT"'"&LEFT(A2,3)&"'!"&ADDRESS(某单元格))

indirect是一个引用函数，从公式中可以看出，indirect函数的参数前半部分为"'"&LEFT(A2,3)&"'!"，取的是sheet表的表名，其中表名的具体名字由单元格A2中的字符串的左边三位得到。两边的单引号和感叹号是sheet表名在引用中的书写格式要求。ADDRESS(某单元格)就是指引用的单元格地址。

整个公式的含义就是利用A2单元格内容返回sheet表名，然后引用那个sheet表中的某个单元格，而那个单元格地址的所在行确定为64，所在列是一个变量，通过一个数字的排名名次所得。

wechane

=INDIRECT("数据2!R"&MATCH($A2,数据2!$A$1:$A$3,)&"C"&MATCH(K,数据2!$A$1:$D$1,),0)&K

请高手解释下上述函数意思,谢谢!

我不明白"R","K","C"的意思

原帖地址：http://club.excelhome.net/viewthread.php?tid=183351&px=0

[此贴子已经被chrisfang于2006-8-18 10:26:22编辑过]

chrisfang

回复97楼：

根据原帖提供的附件，首先来看一下K的意思，K是一个自定义的名称，可在定义名称中查看它的定义公式为：

=OFFSET(数据1!$A$1:$A$2,{1,2},1,,5)

根据offset函数的各项参数含义，可以发现K的值就是一个区域：数据1表中的B2:F3。

再来看一下前面的整个公式：

=INDIRECT("数据2!R"&MATCH($A2,数据2!$A$1:$A$3,)&"C"&MATCH(K,数据2!$A$1:$D$1,),0)&K

公式中有两个match函数部分，第一个match是查找首列124、125的编号对应在数据2表中的行号。第二个match函数的第一参数是前面的定义名称K，是一个内存数组。它通过match查询得到的是一个宽度高度与K一致的数组，数组的每个元素分别是K中每个元素对应在数据2表中的列号。

接下来，整个公式可以看成以下形式：=indirect("数据2!R"&查询到行号&"C"&查询到列号,0)&K

整个绿色部分是indirect函数的R1C1引用方式，R1C1引用方式就是R行号C列号引用表格中的单元格区域，其中列号在这个例子中为一个与K一样大小的数组。

整个indirect函数得到的就是一个数组：

张三	李四	#N/A	陈五	#N/A
#N/A	小六	李四	#N/A	王八

接下来&K进行字符串连接后的结果就是这样一个数组：

张三政治	李四语文	#N/A	陈五微机	#N/A
#N/A	小六微机	李四政治	#N/A	王八语文

其中的#N/A原附件中通过条件格式进行了隐藏。

lxp203

今天在excel视频里看到一个隔行用不同颜色底纹的公式，用在条件格式里，不知是什么意思，但经试验成功，请高手指点，先道声谢谢～～～～：

＝AND(LEN($A1)>0,MOD(ROW(),2)=1)

--------十分感谢chrisfang 与gouweicao78两位高手的讲解～～～～～～

QUOTE:

此条件格式的公式含义为："A列字符长度大于1"且"所在行为奇数行"时，采用相应的格式。

假设这个条件格式公式设置在A1单元格，那么判断当A1的字符长度大于0且A1所在行是奇数行时(第二条件已经满足)，采用相应的格式。

假设这个条件格式从A1单元格通过格式刷刷到了C4单元格，那么对于C4单元格来说，当前的条件格式公式变为：=AND(len($A4)>0,MOD(row(),2)=1)，这就是A前面的绝对引用符号所起的作用。此时判断的条件变成了A4单元格的字符长度是否大于0，且C4所在行(即A4所在行)是否为奇数行。

——chrisfang

[此贴子已经被作者于2006-8-19 13:09:14编辑过]

liuzhicai88

那大家告诉我countif(c:c,">=b2")我的意思是想统计c列中大于或等于表格b2中的数值,我那样写老是等于一,大家帮帮忙啊,该怎么写啊

gouweicao78

to lxp203: And(条件1，条件2，……)表示当条件1、2……同时成立时，返回True。

此处条件1是Len($A1)>0，即A列的单元格不为空，条件2 是MOD(ROW(),2)=1即行号对2求余数等于1也就是奇数行的意思。

to liuzhicai88: countif(c:c,">=b2")改为countif(c:c,">="&b2)就可以了。

hbhfgh4310

原地址：http://club.excelhome.net/viewthread.php?tid=184272&px=0

=SUMPRODUCT((MMULT(--(B3:G3=--$H$3:$H$498),{1;1;1;1;1;1})+MMULT(--(B3:G3=--$I$3:$I$498),{1;1;1;1;1;1})=0)*1)
B3:G3是行数据，$H$3:$H$498是列数据，用B3:G3=--$H$3:$H$498组建一个6*496的数据区域（见sheet2例子）。“--”的作用是把逻辑值转换成数值。Mmult是用来计算矩阵的（见sheet2例子）。将mmult的结果用sunproduct求和。

H列与I列的关系是，出现0次是指两列同时不包含B:G值，MMULT(--(B3:G3=--$H$3:$H$498),{1;1;1;1;1;1})计算的结果是H列中是否包含B:G的内容，MMULT(--(B3:G3=--$I$3:$I$498),{1;1;1;1;1;1})是计算I列中是否包含B:G的内容。包含结果为1，不包含结果为0。两个结果相加结果为0的说明两列值均不包含。所以公式又使用了一个“＝0”的判断。

K的公式与J列公式大体相似，是用两列的和是否等于1来判断H、I列中是否仅有一列值等于1，也就是说有一列内容包含B:G的值。

L公式是用两列的值相乘，只有在两列值同时为1时结果才等于，换句话说就是两列内容必须同时包含B:G的值。
再看看sheet3的例子。

  iEByHCz1.rar (20.76 KB, 下载次数: 149)

2006-8-23 09:13 上传

点击文件名下载附件
[答疑解惑] 函数公式解释专用帖

east065

各位高手，现有一个公式  {=INDEX(A:A,MAX(ROW(A1:A100)*(A1:A100<>"")))}这个公式所表的意思是：在A1:A100中找到最后一个（行序号最大的）不为空的数值并返回该值。

疑问：该公式一旦去掉  *(A1:A100<>"" 部分，该公式得到的答案即为A1(即选中区域内第一行对应的数据），请问为什么？该红色字体部分在公式中起到了什么作用？ 不胜感谢！！！

山菊花

首先告诉你，你测试的结果是错误的。去除红色部分，公式返回A100的值。

Row(a1:a100)返回的是{1;2;3……99;100}，从1到100的整数系列。

Max()求最大值，所以Max(Row(a1:a100))应该是100，自然公式最后结果是A100。

 

A1:A100<>""，返回的是一系列的逻辑值，如果单元格不为空，返回True，否则返回False。

可以选择B1:B100，输入公式A1:A100<>""，然后按Ctrl+Shift+Enter结束，观察结果。

在算式Row(a1:a100)*(a1:a100<>"")中，a1:a100<>""的结果自动把True和alse转换为数值1和0，相当于--(a1:a100<>"")。

相乘的结果，当单元格为空时，返回0，否则，返回行号，可以测试该结果：

选择C1:C100，输入公式=Row(a1:a100)*(a1:a100<>"")，按Ctrl+Shift+Enter结束。

 

hd7Avjj4.rar (4.62 KB, 下载次数: 170)

2006-8-23 14:53 上传

点击文件名下载附件
[答疑解惑] 函数公式解释专用帖

cq_qg

原贴地址：

http://club.excelhome.net/viewthread.php?tid=50402

4楼

Q3=RANK(N3,$N$3:$N$17)+SUM(($N$3:$N$17=$N3)*(TEXT($B$3:$B$17,"00")&TEXT($D$3:$D$17,"00")&TEXT($F$3:$F$17,"00")&TEXT($H$3:$H$17,"00")&TEXT($J$3:$J$17,"00")&TEXT($L$3:$L$17,"00")>TEXT($B3,"00")&TEXT($D3,"00")&TEXT($F3,"00")&TEXT($H3,"00")&TEXT($J3,"00")&TEXT($L3,"00")))

公式解决并列排名的问题，看不懂，能否解释一下？