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

gouweicao78

1、首先看单个成绩查询：
LOOKUP(J4,{0;60;70;85},10^{0;1;2;3})
即J4的成绩在大于等于0、大于等于60、大于等于70、大于等于85这几个分数段的话，分别对应10^0（也就是1）、10^1、10^2、10^3，因为J4是96分，所以返回10^3；
2、接着看多个查询：
LOOKUP(J4:M4,{0;60;70;85},10^{0;1;2;3})——就是J4:M4都按这个方法查对应值，因为4个成绩都是大于等于85，因而都返回10^3。
3、求和：SUM返回的4个10^3求和，得到4000
同理，第2个、第3个SUM(LOOKUP)也是这样的，得到总和7000。
4、TEXT格式化：
自定义单元格格式为："0我0你0他0大家"，则7000将变为"7我0你0他0大家"
把其中的我、你、他、大家换成ABCD，道理一样。但因为字母B、D在数字格式中分别表示佛历、日期，所以需要用!或\号强制显示为字母本身。


再给一个解法，普通公式：

1. 
   
2. =TEXT(SUM((FREQUENCY(J4:M4,{60;70;85}-1%)+FREQUENCY(E4:F4,{90;105;128}-1%)+FREQUENCY(D4,{90;105;120}-1%))*10^{0;1;2;3}),"0A0\B0C0\D")
   

复制代码

[ 本帖最后由 gouweicao78 于 2010-10-13 19:14 编辑 ]

cm110

狗尾草老师，你的解释我完全听明白，我是数学老师，我真佩服你有这么清晰的思路来讲解，你的公式更胜一筹，更简捷。不好意思，能否再就你的公式给出些许解释，心里有不解的东西，憋着实在难受。

gouweicao78

我的公式，主要在于FREQUENCY函数求各分数段人数，具体可以参考：
http://club.excelhome.net/viewthread.php?tid=467836
其中，{60;70;85}-1%是让3个分段点都减去0.01，才不会刚好等于60、70、85，则FREQUENCY得到的结果是4个值，比分段点多1个，即小于60-0.01，大于等于60-0.01小于70-0.01，大于等于70-0.01小于85-0.01，大于等于85-0.01的人数。
其他原理与前一个公式解释差不多。

careyfour

EXCEL中有函数公式求解微积分吗？

gouweicao78

http://club.excelhome.net/viewth ... ighlight=%2Bchenjun

fendouzhe

在昨天求助论坛解决条件取值，共有两个链接贴，虽然都解决了问题，但我依然迷惑，不明白意思，请帮助解答:
第一个链接两个公式，原贴链接：http://club.excelhome.net/thread-640297-1-1
公式1:    D2=IF(COUNT(FIND({"CE","CEC","SO","SH","SOS","SHS"},B2))-COUNT(FIND({"C","S"},B2)),A2,"")
             公式中的COUNT(FIND({"CE","CEC","SO","SH","SOS","SHS"},B2))-COUNT(FIND({"C","S"},B2)是什么意思
公式2:    D2=LOOKUP(SUM(ISNUMBER(FIND({"C","S"},B2))*{10,100})+SUM(ISNUMBER(FIND({"CE","CEC","SO","SH","SOS","SHS"},B2))*{2,2,10,10,10,10}),{0,10,12,14,100,110,112},{"","A","","A","A","","A"})
            这个就完全不懂
第二个链接也有两个公式，原贴链接：http://club.excelhome.net/thread-640214-1-1.html
公式3： =IF(C2="","",IF(LEFT(C2)<>"甲",B2,IF(LEFT(C2,4)="甲999",B2,"")))这个一知半解,为什么用=IF(C2="","",IF(C2<>"甲*",B2,IF(C2="甲999*",B2,"")))就错
公式4： =CHOOSE(MATCH(C2,{0,"0","甲1","甲999"}),"",B2,"",B2)这个完全不懂

gouweicao78

第1个链接无法打开。

第2个链接：
公式3中，LEFT(C2)是取C2的最左1个字符，但不能使用C2="甲*"来通配，因为=号比较运算不支持通配符。但COUNTIF、SEARCH函数可以支持通配符，比如：
=IF(COUNTIF(C2,"甲*"),B2,"")等。
根据你的附件，我写公式如下：

1. =IF(C2="","",IF(OR(LENB(C2)=LEN(C2),LEFT(C2,4)="甲999"),B2,""))

复制代码

意思是，如果C2为空返回空，如果C2没有双字节字符或者C2以“甲999”开头，则返回B2，否则返回空。

公式4是使用MATCH在{0,"0","甲1","甲999"}中升序查找C2的位置，并根据位置1~4返回对应后面的4个参数。

fendouzhe

我重新修正了第一个链接，第一个链接两个公式，原贴链接：http://club.excelhome.net/viewth ... p;page=1#pid4343088

公式1:    D2=IF(COUNT(FIND({"CE","CEC","SO","SH","SOS","SHS"},B2))-COUNT(FIND({"C","S"},B2)),A2,"")
             公式中的COUNT(FIND({"CE","CEC","SO","SH","SOS","SHS"},B2))-COUNT(FIND({"C","S"},B2)是什么意思
公式2:    D2=LOOKUP(SUM(ISNUMBER(FIND({"C","S"},B2))*{10,100})+SUM(ISNUMBER(FIND({"CE","CEC","SO","SH","SOS","SHS"},B2))*{2,2,10,10,10,10}),{0,10,12,14,100,110,112},{"","A","","A","A","","A"})
            这个就完全不懂

fendouzhe

另外有个关于动态更新数据问题，也试用了列表，列表确实好用简便，但如果我要同时既增加行数据，又增加列数据怎么办呢？
经试用，只能向下拓展，不能同时向右自动拓展，签于OFFSET是易失性函数，那有什么方法能完全取代下面这个公式呢，就是不管我增加多少行或列，名称都会动态更新：
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

gouweicao78答复：易失性函数对运算速度的影响，是相对的。如果数据量如你所言的10万行，那么使用函数来解决是不合适的。

[ 本帖最后由 gouweicao78 于 2010-10-25 08:38 编辑 ]

gouweicao78

1、FIND("C",B2)——在B2中查找大写字母C，有则返回数字代表的位置，无则返回#VALUE!错误。
2、COUNT(FIND("C",B2))——统计FIND返回结果中的数字个数，有则是1，无则是0（利用COUNT函数忽略错误值的特性）。
3、COUNT(FIND({"C","S"},B2))——统计FIND的数字的个数，假设B2中既有C又有S，则得到2，只有一个得到1，两个都没有得到0。

同理，COUNT(FIND({"CE","CEC","SO","SH","SOS","SHS"},B2))-COUNT(FIND({"C","S"},B2)就是第一个COUNT判断B2中是否包含那第1个数组中的字符串，统计出个数再减去第2个COUNT的值，因为包含CE、CEC肯定包含C，所以减去C的个数意思是要排除只有C而没有CE的情况。
但实际上，这个算法不太妥当，比如包含CO，那么不含CE，第1个COUNT得到0，第2个COUNT得到1，相减之后就是-1
IF(-1,……,……)，使用非0数值作为IF的条件，都会返回TRUE，也就是说，如果包含CO，将会误认为是包含CE而不是只有C。