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

gouweicao78

to 听风看雪： 已经短信回复2次，请在30楼“编辑”帖子就可以了。说清楚你要的是哪个结果，哪部分不理解。因为定义的名称实在多。（尽量少占用楼层来发帖，以便更多解释的帖子能让大家阅读）

谢谢配合，对30楼问题一：直接用row()也可以吧，这个你得问daziran，呵呵，有的时候意图只有作者清楚。 问题二、注意看$A4——设置单元格格式看看，自定义格式为"第"@"学期"，而实际输入的是"一"，和学期序表对应的。

重新做了一个：见附件 swoRSJPA.rar (28.46 KB, 下载次数: 1410)

2006-4-18 15:24 上传

点击文件名下载附件

复制方法： 选择A1：M10，一起下拉复制。 定义名称如下 序号 =总表!$A$3:$A$21 ——列表定义动态名称（局部作列表） 姓名 =总表!$B$3:$B$21 ——列表定义动态名称（局部作列表） 成绩 =OFFSET(姓名,,1,,COUNTA(总表!$2:$2)-2) 学期 =OFFSET(总表!$C$1,,,,COUNTA(总表!$1:$1)-1) 课程 =OFFSET(学期,1,) 解释： 1、定义名称的序号、姓名均采用“列表”动态定义法（可以参考下贴） http://club.excelhome.net/dispbbs.asp?boardid=3&id=152396 2、定义名称：成绩=OFFSET(姓名,,1,,COUNTA(总表!$2:$2)-2) 即将姓名对应的B3:B21区域平移0行1列，其宽度为counta($2:$2)-2——即第2行不为空的列数扣掉序号和姓名2列。 同理定义学期、课程（按下Ctrl+F3，点击学期等名称，均可看到引用的具体范围) 3、分表B2=INDEX(姓名,(ROW()-1)/10+1) 注意看规律，由于姓名都是在2、12、22……等单元格，相隔10行，所以这样做。 (row()-1)/10——判断第几个10行；+1——得到的是小数，Offset、index等都支持小数且相当于int((row()-1)/10) 支持小数的帖子见： http://club.excelhome.net/dispbbs.asp?boardID=3&ID=106085 由此得到第1、2……个姓名。 4、分表B1的序号=LOOKUP(1,0/(姓名=B2),序号) 1对1关系的典型查询用Lookup(1,0/（条件）,引用区域) 可以参考： 第12楼 http://club.excelhome.net/dispbbs.asp?boardid=3&id=160099 5、B4的数组公式=IF(ISNA(MATCH(MID($A4,2,1)&B$3,学期&课程,)),"",INDEX(成绩,MATCH(INDIRECT("B"&2+10*INT(ROW()/10)),姓名,),MATCH(MID($A4,2,1)&B$3,学期&课程,))&"") 庖丁解牛： Mid(A4,2,1)=“一”取得学期的序号； Mid(A4,2,1)&B3——就是“一语文”学期加课程的合并。 $A4——列绝对行相对引用，下拉行变化，右拉列不变。 B$3——行绝对列相对引用，下拉行不变，右拉列变化。 Mtach(MID($A4,2,1)&B$3,学期&课程,)——在学期和课程合并的字符串数组中找到“本学期、本科目”第一次出现的位置，即找到了某学期某课程在“成绩”表中的“列”的位置 indirect("B"&2+10*int(row()/10))——引用B2、B12、B22……即学生的姓名,在B4:B9单元格引用的都是B2 Match(B2,姓名,)——找到B2（B12……等）在“姓名”中的位置——取得该学生对应成绩的行号。 isna(……)即找不到某学期某课程（比如第2学期没开某课程）返回空。 index(成绩……)&""——将index取得的单元格与空文本合并得到新的文本。 备忘： 比如=A1、=offset(B1,,-1)、=index(1:1,,1)、=indirect("A1)等等多种方法引用A1单元格，当A1不为空的情况下都返回A1的值 当A1为空，以上引用都返回0。而在以上公式尾巴加上&""变成文本之后，返回的就是空文本，也可以不用这个而采用：工具〉选项〉视图〉零值的方法屏蔽0。

[此贴子已经被作者于2006-4-18 20:28:30编辑过]

gdliyy

以下是引用[I]听风看雪[/I]在2006-4-18 13:01:22的发言： 对于“姓名”的定义,不理解.其函数为 =INDIRECT("B"&SUM(ROW())-MATCH(分表!$A4,学期序表,0)-1) 问题一：如我在分表的B4中输入函数，ROW（）表示该行，应该为4，请问前面的SUM的作用是什么。 问题二：MATCH（分表！$A4，学期序表，0）-1）这部分中，分表$A4为“第一学期”，“学期序表”的定义 ={"一","二","三","四","五","六"}。连接起来即为MATCH（第一学期，{"一","二","三","四","五","六"}，0）按MATCH函数的意义，返回所在的地址位置，该返回什么呢？

问题1是这样的：由于在数组公式中Row()返回是单值数组：如{3}，这个值在有的情况下传递给Indirect、Offset、Index时会出错，那么在外套上Sum()可以直接将常量数组转换为常量值后，就不会出错了。 问题2：通过Match()函数来直接将中文序号转换为数字序号，当然我们还可以用Find函数也可以达到效果。

gouweicao78

谢谢gdliyy版主补充问题1。问题2楼主是没注意自定义格式引起的误解。呵呵。

7楼山菊花求部门不重复公式挺妙，忍不住想解释了，呵呵：

=INDEX(人员,MATCH(,COUNTIF($D$5:D5,人员)+(部门<>D$5),))

常见的Match(,countif,)的另一个变体，用21楼“多条件筛选单列不重复值”的解法就是：

Match(1,(COUNTIF($D$5:D5,人员)=0)*(部门=D$5),)——可以这么理解，条件1*条件2是“肯定型”的思维，即确认要找的是1的位置，是满足条件1与条件2的。（不等于and哦，见3楼关于运算符的链接）

而这个MATCH(,COUNTIF($D$5:D5,人员)+(部门<>D$5),)——是“否定型”思维，要找的是0的位置。公式简洁了很多。

用“独孤九剑”法分别“抹黑”countif($D$5:D5,人员)和（部门<>D$5）可以看到得到的是{0,1……}之类的数组和{False,True……}之类的数组，数组相加，只有2个数组同一位置都是0返回的才是0。用汉语理解就是D$5:D5已经出现过的人员或者部门不等于D$5，都不满足（否定型）。

Kelidai

gouweicao78:

请帮忙解释一下：Growth;trend;Forecast之间的区别吗？

danq 2006-4-19 12:08:16： get.document()有没有关于这个宏表函数的解释啊

Kelidai2006-4-19 13:09:16: 本论坛上有一个，不过如果你找不到的话可以到我的网络硬盘上下。 http://free5.ys168.com/?kelin

danq2006-4-20 8:15:33: 谢谢两位的帮助,我将会永记在心.

danq2006-4-20 16:11:19 有时公式里会有SUM(NUM_1)这样的形式出现,它主要代表的是什么?请解释来听听?谢谢!

yigepure2006-4-20 20:22:26 不错，浮出水面顶一下

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

gouweicao78

to 34楼：实话实说，这3个函数我还没用过，而且这个属于专业性应用比较强（统计数学方面的吧）的函数，帮助文件很多我也看不懂。不过看了一下函数帮助，Growth是根据“满足指数回归拟合曲线 y=b*m^x ”来的，而Trend则是根据“ y = mx + b ”，“函数 FORECAST 的计算公式为 a+bx”且a、b均有专门的计算式。我想这应该是其本质区别吧。

to 35楼：宏表4.0函数帮助文件链接

gouweicao78

to danq：提点小要求

38楼——感谢帖可以不必。39楼求解释的应该附上附件或者原帖链接。楼层右下角有“编辑”，可以用这个功能减少发帖楼层。谢谢配合。

39楼的问题应该是NUM_1是一个定义名称，查看方法：菜单插入〉名称〉定义；或者Ctrl+F3；或者F3然后在空白处粘贴列表。均可看到定义名称的公式。前两种方法对“引用某些单元格区域”的定义名称点击名称公式时，能在excel里显示具体的范围，用虚线框起来。

再来一个公式解释：（原帖链接http://club.excelhome.net/viewthread.php?tid=163314&px=0）
=INDEX(A:A,SMALL(IF(A$1:A$19<>"",ROW($1:$19),65536),ROW()))&""

其实这个公式出去65536和&""两个部分的话大家并不陌生，是依次列出满足某条件的记录的数组公式。

解释：

1、关于if部分：如果A1:A19不为空，则返回A1:A19的行号（条件），否则返回65536（注意Excel2003及之前版本的最大行数是65536，Excel2007将采用1048576行，具体见有关介绍）；
if外部套用small()+row()进行排序，将if得到的满足条件的行号和不满足条件的65536进行排序

2、关于&""：首先说一个大家很熟悉的事情，那就是引用空单元格的情况，比如=A1、=index(1:1,,1)、=index(A:A,1)、=offset(B1,,-1)、=indirect("A1")等等各种各样的方法引用A1单元格，当A1为空的时候，这些引用都返回0，如果这个0时我们所不想看到的话，经常会采用工具〉视图〉零值勾选掉的方法。但比如我们想当A1=0的时候，引用A1就显示0，当A1为空时，引用A1显示空，那么我们就可以在以上引用公式的尾巴加上&""——将A1单元格的内容与""空合并成文本，则此时返回的将不是0而是空了。注意，返回的是“文本”，如果A1是日期，则返回的是日期序列号数字的“文本”，等等（可以再套用Text(引用,"yyyy-m-d")等来显示日期）。如果A1是数值型数字，返回的则是文本型数字，切记！

3、基于以上两点认识，我们不难理解，该公式的含义就是：
对A列取行列交叉值（index），其行号是small函数排好序（按记录先后顺序）的行号和65536组成的。而由于一般情况下，Excel最底下那一行是没有内容的（空），所以这个公式能达到=if(row(1:1)>满足条件记录个数,"",index(……))一样的显示效果。值得注意的是，除了这个公式缩短了长度和计算满足条件记录个数步骤带来的好处以外，要切记返回的是文本哦！

这个公式的用法还不少，喜欢的朋友们可以自己多试试。今天看到的一个：http://club.excelhome.net/viewthread.php?tid=163393&px=0 第3楼。（呵呵，这个用法我也是从gvntw版主那里学来的）

[此贴子已经被作者于2006-12-5 13:20:53编辑过]

gouweicao78

to 听风看雪：30楼再问

1、不好意思，今天有事出门了，这么晚才回复，实在抱歉。

2、楼主要善用论坛功能，比如，你可以通过短信息来找我。而不必重新发帖，而且重复发了3个，这个你可以发短信息给版主请求删除。

3、关于问题一、公式“=LOOKUP(1,0/(姓名=B12),序号)”改为“=VLOOKUP(B2,IF({1,0},姓名,序号),2,0)”

如果从运算速度的角度来看应该是改善的（原公式第2参数为数组运算，改后为普通运算，具体没研究）；如果只是说更好理解的话，你可以拿自己熟悉的东西来做是最好理解的。我觉得Lookup的公式也不难理解。或许链接里的帖子讨论有些过于深了造成你这种感觉吧。

关于问题二、“定义名称成绩=OFFSET(姓名,,1,,COUNTA(总表!$2:$2)-2)，如果定义为=$C$3:$S$21却发生了错误，不知是什么原因。我是这样理解的，您的定义成绩，所指的范围就是$C$3:$S$21。请指教。谢谢”

注意1、：我定义的名称区域在“总表”里，所以你定义为=总表!$C$3:$S$21也可以（我试了，没问题）。

注意2、：我定义的名称——姓名——是采用了“列表”功能定义的区域，是一个动态的区域，能够随着数据的增加而自动更新引用范围，所以用Offset直接利用“姓名”的偏移来得到另一格动态的名称。如果像注意1那样定义虽然可以，却不是动态引用了。

gouweicao78

z62196492006-4-25 18:35:09
我想知道循环引用的方法，望各位大侠赐教。

pk1314K13142006-4-25 20:39:44
版主好,请问gsjs函数怎么应用?

to 43楼：用循环引用搜一下论坛就有了。http://club.excelhome.net/viewthread.php?tid=55081

to 44楼：gsjs是啥，请在原楼层发附件看看。

数值按多个关键字排序
to http://club.excelhome.net/viewthread.php?tid=164072&px=0  第8楼：
“有些地方看不太明白”——那我就替hbhfgh4310兄倒卖一下，呵呵：（或者参考7楼山菊花的链接“关于前三名”，非常棒的解释！）

=INDEX(A$3:A$11,MAX(IF((($B$3:$B$11)*10000+($C$3:$C$11)*100+$D$3:$D$11)=LARGE(($B$3:$B$11)*10000+($C$3:$C$11)*100+$D$3:$D$11,ROW(1:1)),ROW($B$3:$B$11)-2)))

其实还是那招“庖丁解牛”，我想你不太明白的地方关键是那几个乘数，即
绿色部分（2部分都一样）：(($B$3:$B$11)*10000+($C$3:$C$11)*100+$D$3:$D$11)……就是B列*10000、C列*100、D列*1，意图就是给这3列数字“分权”——好比说“给张三有花10000块钱/次的权力，只给李四花100块钱/次的权力，王五可怜只能花1块钱/次”，这样在各自的权利范围内王五怎么花钱都不会超过李四的一次，李四一样……
也就是说：*上这些数（权）之后，B列的值之间比较，大小顺序还是原来B列的大小顺序，而如果B列一样大，C列原来比较大的这下子就比较大了，如果B、C两列都一样大，那么就要比比D列的值了，这就达到了“主要关键字、次要关键字、第3关键字”的作用了。
这个部分理解之后就不难了：假设绿色部分为X
则Max里面就是（if(X=large(X,row(1:1)),row(X)-2)——就是如果X是X中最大的、第2大的、第3……则返回其对应的行号（数组），然后用Max取得单值，以用于Index函数作第2参数。

实例参考：如何按顺序把第一、二……多的文字一个个排出来

[此贴子已经被作者于2007-4-24 0:47:31编辑过]

lzy853

=SUMPRODUCT(1/COUNTIF(A4:A15,A4:A15))

请教该公式的具体含义,为什么可以用他来计算同一列中的重复值.谢谢

gouweicao78

to lzy853：7楼山菊花前辈的链接有说明的。 给你看看“独孤九剑”法，看完你就可以知道为什么这个公式常被用来计算“不重复值”个数的原因了。 q0IFUi5D.rar (31.49 KB, 下载次数: 713)

2006-4-28 08:49 上传

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

不过：由于Excel浮点运算的误差（比如1/3表示为0.333333333之类的（如果数据量大，即A4:A15区域更大）等，容易造成运算误差而产生错误。而且由于countif函数的“高能低效”（采用“遍历法”，所以数据量大的话其运算速度会比较慢），可以采取别的方法来求，比如用“match(Range,Range,0)=row(Range)-Range上一行行号”判断来计算不重复个数和作为求不重复值的判断依据。

[此贴子已经被作者于2006-4-28 10:51:44编辑过]