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

chrisfang · 发表于 2006-7-23 17:57

QUOTE:

以下是引用littleliuyy在2006-7-23 15:35:53的发言：

请问一下有什么方法能将每次筛选后的数据自动加总，而不用每次都要输入公式．除了ＳＵＢＴＯＴＡＬ这个公式外，还有没有其他？因为我的原数据是不断增加的，使用ＳＵＢＴＯＴＡＬ这个公式就得不断地改变数据区域．

subtotal函数就可以满足你的要求，请看附件：

3Q57pwcW.rar (1.9 KB, 下载次数: 237)

gpgpgp · 发表于 2006-7-27 14:40

请教这个公式是什么意思，尤其是E2*1000中的1000是什么含义。

MATCH(E2*1000+D2,LARGE(E$2:E$4*1000+D$2:D$4,ROW(E$2:E$4)-1),)

帖子链接：

http://club.excelhome.net/viewthread.php?tid=143960&px=0

[此贴子已经被山菊花于2006-8-5 17:00:26编辑过]

chrisfang · 发表于 2006-7-28 10:20

回复87楼gpgpgp朋友：

按照你提供的链接，原帖楼主的要求是对三人的分数进行排序。排序的准则是先以总分排名，总分相同时看“常识”所得分数。这里我们通常都把这种排名的方法称为权重排名，总分的权值最高，常识分的权值其次。排名时先看权值高的，再看权值低的。权重法也是我们平时生活中经常用到的一种比较方法，比如股票的指数，就是对一批股票的价格指数乘以各自的权值后的一个总值。又比如基金投资，也是对一批投资目标进行权重的分配，然后确定投资比例。

回到这个问题上，×1000的目的就是为了区分总分和常识分的权重：总分×1000＋常识分，因为常识分最大不超过100分，所以无论常识分多大，总分×1000＋常识分的值首先是由总分的大小所决定的；在总分大小一样的情况下，常识分才有了决定大小的价值。所以，我们在这里也可以把1000看成是总分的权值。

再来看整个公式：LARGE(E$2:E$4*1000+D$2:D$4,ROW(E$2:E$4)-1)，黄色部分返回的是数组{1,2,3}，作为large函数的第二参数。Large函数的第一参数就是刚才加权后的分值，所以整个large函数返回的结果就是加权后的分值从大到小排列的一个数组，数组中三个元素的名次就是按照1、2、3排列了。

MATCH(E2*1000+D2，large....这部分就是依次对当前行所对应的加权后分值去large后返回的数组里查找对应的名次了。

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

cq_qg · 发表于 2006-8-4 08:47

A1:INDEX(A:A,10)

这种用法怎么解释啊？INDEX都得到一个单元格值，这种怎么能形成一系列数据？

这个跟A1：A10是一样么？

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

在四楼的附件里面有这种用法。

QUOTE:

“INDEX都得到一个单元格值”的理解有误。——山菊花

QUOTE:

INDEX有两个本事，返回一个值或一个引用，什么时候返回值？什么时候返回引用？这个不用我们管，它是智能的，它知道该给你什么。你看，INDEX走在大街上，不停地跟人打招呼：“早上好。”，对面走来一个洋妞，看他有趣，向他眨眨眼，INDEX还了一个礼：“Good morning!”。原来INDEX还有这本事。

B1=Index(A:A,10)

这种情况下，它会返回一个值，不论A10是数字还是文本或其它，它返回的是这个单元格中的值。

B1=Sum(A1:Index(A:A,10))

这种情况下，Index(A:A,10)返回的是一个引用，即是返回A10。

Index(A:A,10)等效于A10，A1:INDEX(A:A,10)也就等效于A1:A10

顺理成章，Sum(A1:Index(A:A,10))也就是Sum(A1:A10)。

——山菊花

[此贴子已经被czzqb于2007-11-6 12:40:27编辑过]

cq_qg · 发表于 2006-8-7 12:51

=INDEX($C$123:$C$127,MATCH($F$123&"xyz"&$F$124,$A$123:$A$127&"xyz"&$B$123:$B$127,0))

这是一个多条件查找的公式，根据F123和F124的值，在A123：A127，B123：B127查找条件，返回C123：C127列的值。

公式里用“xyz”有何用处？

没有也能查找到啊！

山菊花 · 发表于 2006-8-7 21:56

F公式中的“xyz”起分隔符的作用，也可换成其它(在原数据中没有的字符)，如“|”、“-”、“☆”等。

F工作表中A列与B列大概是同一类数据，且有一种混淆的可能。

如：

"1231"&"23"="123123"

"12"&"3123"="123123"

虽然A与B不同了，但结果还是一样，如果中间加了分隔符，结果就不一样，你试试。

F如果一定没有这种混淆的可能，把&"xyz"删了。

lts8823 · 发表于 2006-8-10 14:21

本人超级菜鸟，麻烦楼主解释这句：
=MAX(0,MIN(LOOKUP(COLUMN(),COLUMN($F:$I),VLOOKUP($C5,{"A",690,100,200,2000;"B",420,200,2000,2000;"C",2000,2000,2000,2000},COLUMN($B:$E),1)),$D5-SUM($E5:E5)))

4qzvFkqU.rar (3.12 KB, 下载次数: 94)

[此贴子已经被作者于2006-8-10 14:32:30编辑过]

chrisfang · 发表于 2006-8-10 14:59

回复lts8823朋友：

原帖地址：http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=481151&id=181763&page=1&skin=0&Star=2

=MAX(0,MIN(LOOKUP(COLUMN(),COLUMN($F:$I),VLOOKUP($C5,{"A",690,100,200,2000;"B",420,200,2000,2000;"C",2000,2000,2000,2000},COLUMN($B:$E),1)),$D5-SUM($E5:E5)))

先看绿色部分Vlookup函数：第一个参数C5，是类别里面那些A、B、C、D等等；第二个参数是自己构造的一个三行五列的数组，其中第一列就是"A"，"B"，"C"，其他列的那些数字就是根据帖子楼主要求的a、b、c、d列(即F～I列)允许的最大值；第三参数是一个数组{2,3,4,5}，指的是查找到时返回数组的第2、3、4、5列的数据；第四个参数为1，指的是模糊查找，当C5不为A或B或C时，比如D、E、F等等时，可以返回与C一样的数值。这部分绿色的公式，可以通过下面一个附件里的辅助区域来表达，看这个附件能够更好的理解这段公式。

uKs9AFb0.rar (2.18 KB, 下载次数: 136)

最后来看最外面的max函数，这部分是根据帖子楼主最后的要求：如果不够减，不要返回负数而要返回零，根据这个要求，就用函数max()处理，如果前面公式部分返回的是负值，就取0，否则就取返回值。这部分比较简单，就不再加附件了。

说了这么多，也不知道大家有没有看懂。我也只能解释到这里了，不明白的地方还是多在论坛查找相关帖子进行学习吧。祝大家天天有收获！

汗，解释公式比写公式累多了，以后不太敢写这么繁复的公式了。

saintdai · 发表于 2006-8-11 11:26

=ROUND(D13*E13,0)这个公式怎么解释啊～～请指教～

QUOTE:

欢迎saintdai朋友来到ExcelHome！round函数是对数值实现四舍五入的函数，其中第二个参数为0时，表示取零位小数(即四舍五入取整数)。整个公式的意思就是将D13单元格和E13单元格的乘积进行四舍五入取整。

[此贴子已经被chrisfang于2006-8-11 11:49:39编辑过]

ministart · 发表于 2006-8-16 17:44

楼主麻烦帮忙解释下面的公式

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

谢谢!

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

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

公式里用“xyz”有何用处？