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

gouweicao78

QUOTE:

以下是引用blue_prince在2007-1-7 7:55:48的发言：

各位，有谁能告诉我以面的函数是什么意思呀？

{=IF(Q5="","",MAX(0,COUNT(1/(IF(MATCH(E5:E7,E5:E7,)={1;2;3},{1;2;3})*(A5:A7&B5:B7&D5:D7=A5&B5&D5))

请各位看附件中深红色部分函数，给个注好吗，谢谢！

E列是分店名，MATCH(E5:E7,E5:E7,0)={1;2;3}——每3行记录找，是否分店名有重复，如果没有重复则返回{1;2;3}，本例中前2个都是双桥店，返回{1;1;3}与{1;2;3}比较，得到{True;False;True}

A、B列是日期、D列是客户名称，A5:A7&B5:B7&D5:D7=A5&B5&D5——每3行记录找，是否有同一天给与当前行同一家客户送货的记录，如果有就返回True，例中，第5行记录与6、7行不同，返回{True;False;False}，在第6行则与第7行同、与第8行不同，返回{True;True;False}

所以，在第5行：IF({True;False;True},{1;2;3}*{True;False;False})——得到{1;0;0}。（备注：看不出返回{1;2;3}有什么实际意义，直接简化公式。）

1/{1;0;0}——得到{1;#DIV/0!;#DIV/0!}，Count函数可以忽略错误值，COUNT({1;#DIV/0!;#DIV/0!})得到1，Count-1=0

Max(0,0)=0

简化后的公式——=IF(P5="","",MAX(0,COUNT(1/(IF(MATCH(E5:E7,E5:E7,)={1;2;3},(A5:A7&B5:B7&D5:D7=A5&B5&D5))))-1))

再简化——=IF(P5="","",MAX(0,COUNT(1/((MATCH(E5:E7,E5:E7,)={1;2;3})*(A5:A7&B5:B7&D5:D7=A5&B5&D5)))-1))

IF部分的意思：如果从当前行起往下共3行的记录中，判断分店是否第一次出现（即非重复），如是则判断日期是否同一天、客户是否同一家，如果满足这3个条件，就按1个卸货点计算。

Count-1表示增加部分，不含本身。

以上是运算过程，可以用抹黑公式部分按F9键的方式来查看。下面以第10~12行的记录为例：

11	19	5213583	北京市世纪凯城科技有限公司	(正通顺小学)
11	19	5213583	北京市世纪凯城科技有限公司	(汪家拐小学)
11	19	5213583	北京市世纪凯城科技有限公司	(狮马路小学)

同一天给同一家客户的3个分店送货，那么卸货点是3个（即要增加2个）。

QUOTE:

以下是引用cawyj在2007-1-7 15:37:18的发言：
"sheet"&{2,3,4}&"!A:A"这是什么意思

这是公式中的一部分，问问题不应该断章让人家来取义。请参看本贴43楼 SUMif三维引用的经典用法的解释中关于Indirect函数的解释。即引用sheet2、sheet3、sheet4表的A列。

[此贴子已经被作者于2007-1-10 12:59:58编辑过]

lxm0109

发个链接,第五楼的解答http://club.excelhome.net/viewthread.php?tid=212672&px=0

http://club.excelhome.net/viewthread.php?tid=212209&px=0第七楼.

QUOTE:

已在链接中解答。为减少楼层占用，编辑2楼合并为1楼。——gouweicao78

[此贴子已经被gouweicao78于2007-5-11 18:37:05编辑过]

WTU7503

一个日记账自动取余额公式不明白,请帮忙解释一下

B3=INDEX(日记账1!E:E,MATCH(999999999999999,日记账1!E:E))

QUOTE:

to  楼主：请参考本帖首页山菊花关于9E+307的解释链接。

即：引用日记账E列最后一个数值。——gouweicao78

[此贴子已经被gouweicao78于2007-5-11 18:40:52编辑过]

YANJUNCHEN95

HYPERLINK("#"&"工作計劃"&"!i1",INDIRECT("工作計劃!a1"))

我現基本知道以上那個函數怎麼用，但是有一點還是不太明白，就是為什麼引用本工作簿的其它表格一定要加上“#”和後綴“!i1”，
這個是固定模式嗎，怎麼解釋。可以幫忙說明一下嗎，謝謝

gouweicao78

#表示同一工作簿路径，比如[Book1.xls]下面有3个sheet，分别为sheet1、sheet2、sheet3。

!i1与工作表名称连起来表示某工作表的I1单元格，比如sheet1的A1单元格表示为sheet!A1

HYPERLINK("#"&"工作計劃"&"!i1",INDIRECT("工作計劃!a1"))可以书写为：

HYPERLINK("#"&"工作計劃!I1",INDIRECT("工作計劃!a1"))——因为双引号内都是文本不是引用单元格。

此公式表示跳转到当前文件（同一工作簿）下的工作計劃工作表的I1单元格，显示的内容则是工作计划表的A1单元格的内容。

feta

[求助]请问设置了公式，为何不计算结果，而只显示公式本身？以前可以，是病毒引起的吗？

QUOTE:

可能：1、单元格格式为文本，请改为常规。

2、按下Ctrl＋`（ESC键下面那个）切换显示公式方式。

——gouweicao78

[此贴子已经被gouweicao78于2007-5-11 18:29:40编辑过]

gouweicao78

QUOTE:

以下是引用feta在2007-2-13 19:40:18的发言：
[求助]请问设置了公式，为何不计算结果，而只显示公式本身？以前可以，是病毒引起的吗？

可能：1、单元格格式为文本，请改为常规。

2、按下Ctrl＋`（ESC键下面那个）切换显示公式方式。

gouweicao78

QUOTE:

以下是引用zhou.ying在2007-2-14 8:26:42的发言：

=INDEX($B:$B,SMALL(IF((MATCH($A$2:$A$39&$B$2:$B$39,$A$2:$A$39&$B$2:$B$39,0)=ROW($A$2:$A$39)-1)*($A$2:$A$39=$D13),ROW($A$2:$BA$39),65536),COLUMN(A:A)))&""

请楼主帮忙解释其中if和match函数在组合函数中的结构及作用是怎样？  谢谢！！

这说一个非常常见的求不重复值的公式，可简称为Match＝Row法，原理是Match精确查找数据第一次出现的位置是否与row函数返回的1、2……序列相等，如果是则返回行号，由此控制第2次及之后出现的记录不返回行号。

其中Match查找记录第一次出现的位置（本公式中为A&B两列合并，即双条件），绿色部分又增加A列＝$D13的条件（应该是$D$13吧？），共计3个条件：A&B首次出现且A列＝$D$13。

此3个条件满足返回其行号以供INDEX取得单元格值，条件不满足返回B65536，并用&""显示为空文本——解释见本帖26楼。

hbhfgh4310

连接位置：http://club.excelhome.net/viewthread.php?tid=223370&px=0

原公式：=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$C$2:$C$94=$A2,ROW(Sheet1!$C$2:$C$94)),COUNTIF($A$2:$A2,$A2)))

从里往外逐层剖析：（sheet1暂命名为：资料表；sheet2命名为统计表）
1、先看Sheet1!$C$2:$C$94=$A2。Sheet1!$C$2:$C$94是资料表的姓名列；$A2是需要查找的姓名。用Sheet1!$C$2:$C$94=$A2做判断，得出都有那些行符合条件。得出的结果是93个值逻辑值（因为数据行有93行），即符合条件的结果是“TRUE”（真），不符合结果是“FALSE”（假）。下面是返回的结果：
{FALSE;……FALSE;TRUE;FALSE;……;FALSE}－－－TRUE对应的行号是57。

2、再往外来：IF(Sheet1!$C$2:$C$94=$A2,ROW(Sheet1!$C$2:$C$94))。如果判断式的结果为真，即Sheet1!$C$2:$C$94=$A2成立，则得到对应行的行号ROW(Sheet1!$C$2:$C$94)。Row（）的作用是用来返回引用单元格的行号。比如row（A2）＝2；row（F15）＝15。此段公式的结果是Sheet1!$C$2:$C$94=$A2的，返回对应的行号，不符合的返回“FALSE”。下面是返回的结果：
{FALSE;……;FALSE;57;FALSE;……;FALSE}

3、再来看看COUNTIF($A$2:$A2,$A2)的作用，用此段公式用来解决本题的重名问题。随着公式向下托拽，公式COUNTIF($A$2:$A2,$A2)中的$A2,$A2也随之变化，当公式托拽到第三行时变为COUNTIF($A$2:$A3,$A3)，依此类推。其结果是统计自A2行开始的至公式所在行为止的A列内容中等于当前行A列内容的个数。举例：78行的公式为：COUNTIF($A$2:$A78,$A78)，即统计$A$2:$A78内等于$A78的个数，实际结果是2。而58行的结果是1，这样便可以区分出想要找的“杨立”是第1个还是第2个了。

4、下面介绍small函数的作用：small（array，k）―――array指数组或数字型数据区域，简称数据区；k代笔需要返回的数据在数据区里的位置（从小到大）。比如k＝1，即代表返回数据区中第1小的数。Large作用与之刚好相反。
SMALL(IF(Sheet1!$C$2:$C$94=$A2,ROW(Sheet1!$C$2:$C$94)),COUNTIF($A$2:$A2,$A2))，if函数段IF(Sheet1!$C$2:$C$94=$A2,ROW(Sheet1!$C$2:$C$94))得到的结果是符合条件的行号，是一组数据（见步骤2的结果）。通过small和countif的共同作用，把符合条件的行号调出来。第2行公式的结果是57。

5、最外层：index函数的作用是返回数据内指定位置的内容，=INDEX(数据区,SMALL(IF(判断式,行号),统计结果))，用步骤4的结果在index中作为位置数，从数据区Sheet1!A:A中调出想要的结果。

另：在表中选中某含有公式的单元格，在编辑栏内用鼠标将上述各步骤的公式选中，按下F9键可以看到公式的结果，按ESC键取消。

鸟粥

哇,真是个学习的好园地,以后我会多多光顾!

有个难题想问问各位高手

可以详解下VLOOKUP、SUMIF和ROUND的用法及含义吗？