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

hzm258

我遇到个帖子：

在EXCEL表格中自动计算某一列中在某一时间段的数量,如何设置函数.
举例:想在A1至A100中计算符合在10点至11点之间的值的总数量.

答案是：

个数和
=SUMPRODUCT((A1:A100>10)*(A1:A100<11)*1)

总数量
=SUMPRODUCT((A1:A100>10)*(A1:A100<11)*A1:A100)

能帮忙解释一下为什么这两个公式能算出10至11之间的值的个数和数量总数吗？好像SUMPRODUCT的作用只是计算几个数组之间相乘的总和。

QUOTE:

gouweicao78答复：这是最经典的多条件求和、多条件计数公式，您需要理解什么是数组以及多重计算的原理。比如A1、A2、A3分别是9、10.5、11，那么A1:A3>10返回的是{FALSE;TRUE;TRUE}，A1:A3<11返回的是{TRUE;TRUE;FALSE}，在四则运算中，FALSE=0,TRUE=1，则(A1:A3>10)*(A1:A3<11)得到{0;1;1}*{1;1;0}得到{0;1;0}，因此SUMPRODUCT((A1:A3>10)*(A1:A3<11))=SUMPRODUCT({0;1;0})=1，

同理=SUMPRODUCT((A1:A3>10)*(A1:A3<11)*A1:A3)=SUMPRODUCT({0;1;0}*{9;10.5;11})=10.5

上面的运算过程就是数组相乘，最后由SUMPRODUCT求相乘之后的总和。

[此贴子已经被gouweicao78于2008-9-2 10:24:19编辑过]

samica

要在附件sheet2中获取sheet1中O列为"是"或R列为"是",或O、R列同时为"是"……的数据,如两列都为"是",则要在sheet2中显示2行数据,一行的病名为"高血压",另一行病名为"糖尿病",而其他数据则相同显示 6WwK8hS3.rar (55.47 KB, 下载次数: 47)

2008-8-21 19:01 上传

点击文件名下载附件
[答疑解惑] 函数公式解释专用帖（20061125更新首页）

qq533520

=SUM(IF(COUNTIF($A$2:$A$16,C2:C16)=1,C2:C16))   这个函数该如何理解呢?我从别人那里看到的,好象是根据条件格式求和的

QUOTE:

gouweicao78答复：公式含义是：如果C2:C16区域中的记录在A2:A16中出现过且不重复（即只出现1次），则返回这些值，并求和。比如C2、C5与A2相同，则Countif(A2:A16,C2)=2，不是1，C2、C5就不会被加进去求和。

最好给出链接或附件，以便更好解释和验证。

[此贴子已经被gouweicao78于2008-9-2 10:17:24编辑过]

huangyun

找了一圈没找到关于条形码校验位计算过程的解释，就跟帖问一下公式的过程计算。

大体有二种算法，一是=RIGHT(SUM(LEFT($A$1,{0,1}+{1;3;5;7;9;11})*{9,7}))

二是=10-MOD(SUMPRODUCT(MID(A1,ROW(1:6)*2-1,1)*1)+SUMPRODUCT(MID(A1,ROW(1:6)*2,1)*3),10)。

第二种没问题，很容易读懂的，就是第一种,{0,1}+{1;3;5;7;9;11})*{9,7}这二个计算原理，以及为什么这个公式结果就能与第二种公式结果一样呢，它是什么个原理，9，7是如何得到的？

谢谢！

QUOTE:

gouweicao78：请给出链接或附件。计算原理好说，你把校验码的规则翻出来看看，呵呵。

[此贴子已经被gouweicao78于2008-9-2 11:00:13编辑过]

kuoma

A2=Madden.09.USA.PS2DVD-FATAL
公式
=LOOKUP(2,1/SEARCH({"@@@","XBOX360","PS3","WII","NDS","PSP","PS2"},"@@@"&A2),{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
=LOOKUP(2,1/SEARCH({"@@@","XBOX360","PS3","WII","NDS","PSP","PS2"},"@@@Madden.09.USA.PS2DVD-FATAL"),{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
=LOOKUP(2,1/{1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,18},{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
=LOOKUP(2,{1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,0.0555555555555556},{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
=PS2  ????????為什麼等於ps2呢？，且為什麼多引用了@@@ 我自己拿掉的話也是算的出來！引用@@@有什麼特別意思呢？

原帖:http://club.excelhome.net/viewthread.php?tid=349276&px=0

gouweicao78

QUOTE:

以下是引用kuoma在2008-8-27 23:30:28的发言：

=PS2  ????????為什麼等於ps2呢？，且為什麼多引用了@@@ 我自己拿掉的話也是算的出來！引用@@@有什麼特別意思呢？

原帖:http://club.excelhome.net/viewthread.php?tid=349276&px=0

已在原帖回复。

关于LOOKUP(2,1/(条件),查找区域)的解释，本帖第133、137楼以及其相关链接都已解释过了。请仔细研究。

kuoma

A1=Madden.09.USA.PS2DVD-FATAL

=LOOKUP(,-SEARCH({"","XBOX360","PS3","WII","NDS","PSP","PS2"},A1),{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
=LOOKUP(,{-1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,-15},{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
依照二分法先查(1+7)/2 第四個是 #VALUE!那他怎知要往左還往右--假設往右那答案就是PS2

A2=NBA_2K8_JPN_XBOX360-Caravan
=LOOKUP(,-SEARCH({"","XBOX360","PS3","WII","NDS","PSP","PS2"},A2),{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
=LOOKUP(,{-1,-13,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
依照二分法先查(1+7)/2 第四個是 #VALUE!那他怎知要往左還往右--假設如上的走法往右那答案就是 找不到

可是為什麼答案還是對的是XBOX360 ??? 難道他還會反查回去？
讀了二分法大致有一點了解，但對於這些值會忽略的說法跟實際好像沒多做說明，所以還是不懂，想再請教，或是那裡有資料我沒讀到的。

另外這篇條件格式的問題
=AND(COUNTA($A$1:$Z$100)>0,COUNTA(A1:$Z$100)>=1)
要以上二個都成立才會成立，可是明明沒資料在上面，儲存格還是會變色，不懂是為什麼？
原帖:
http://club.excelhome.net/viewthread.php?tid=350253&extra=&page=1#1509878

gouweicao78

QUOTE:

以下是引用kuoma在2008-8-31 22:28:53的发言：

讀了二分法大致有一點了解，但對於這些值會忽略的說法跟實際好像沒多做說明，所以還是不懂，想再請教，或是那裡有資料我沒讀到的。

另外這篇條件格式的問題
=AND(COUNTA($A$1:$Z$100)>0,COUNTA(A1:$Z$100)>=1)
要以上二個都成立才會成立，可是明明沒資料在上面，儲存格還是會變色，不懂是為什麼？
原帖:
http://club.excelhome.net/viewthread.php?tid=350253&extra=&page=1#1509878

LOOKUP对于错误、空值的“忽略”（事实证明不是忽略）在二分法讨论帖中仍在讨论，呵呵。你只需按照帮助说明的“如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。”这句话来理解就可以了。即，为什么我们要用LOOKUP(2,1/()……就是明知道第2参数没有大于1的，却要找2，最终只能一直找到最后一个数值的位置。

条件格式公式中：

1、COUNTA>0这个判断不必用>0，因为COUNTA得到的本身就是0或正整数，不可能是负数，当它等于0时，用于逻辑判断就相当于FALSE。同理，COUNTA不可能返回0~1之间的小数，也就是说要么就是0、要么就是>=1，因而，AND中的两个判断都可以直接用COUNTA代替：=AND(COUNTA($A$1:$Z$100),COUNTA(A1:$Z$100))

2、注意$A$1:$Z$100和A1:$Z$100的区别，看看本帖首页的“相对引用”解释。此处，第一个COUNTA的$A$1，也可只用A1，因为条件格式只对第1列和第1行的单元格设置。因而，整个公式可以简化为：

=COUNTA(A1:$Z$100)——即：在A1单元格设置条件格式，然后用格式刷刷A1:Z1、A1:A100区域。

这个公式在B1单元格时变为：=COUNTA(B1:$Z$100)，在A2单元格时变为=COUNTA(A2:$Z$100)

即：始终计算当前单元格到Z100这个区域内是否有非空单元格。

3、不明白你说的“明明沒資料在上面，儲存格還是會變色，不懂是為什麼？”是什么情况，在单元格里输入=""，即公式得出的空文本""，COUNTA也会返回1而不是0，可能就是你所说的“明明没资料在上面”情况。

kuoma

A2=NBA_2K8_JPN_XBOX360-Caravan
=LOOKUP(,-SEARCH({"","XBOX360","PS3","WII","NDS","PSP","PS2"},A2),{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
=LOOKUP(,{-1,-13,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},{"","XBOX360","PS3","WII","NDS","PSP","PS2"})
依照二分法先查(1+7)/2 第四個是 #VALUE!那他怎知要往左還往右--假設如上的走法往右那答案就是 找不到

如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。”

這話也讓我不解，找零的話 小於他的最大值是-1，但是他給我的答案是-13對印的XBOX360或許我唯一能想的解釋是，他遇到的第一個值是錯誤值且查到的結果是無之後，就會用反查，剛假設往右的話現在往左，用類似二分法去查左半邊，所以查到-13，我想這樣子就能說通了。

個人猜測LOOKUP查到錯誤值都會向右跑不然也抓不到-13的數字來交差

以這說法前面找尋PS2也就可以理解了，假設他先找左邊，遇到錯誤都往左跑，那就會找到-1的值了！所以會拿""來交差。

希望我個人的小心得，看能不能成立。沒版主這強還會做模擬器。

另版主所提的

COUNTA(A1:$Z$100)可以用，感謝了，只是不知怎對整張工作表套用較快的方法？只能用COUNTA(A1:$最後一欄$最後一列)這樣子嗎？

另前面我所提的

QUOTE:

gouweicao78回复：关于LOOKUP，请到那个讨论帖去谈吧，你的疑惑从该帖第72楼“惑起萧蔷”开始读起。

[此贴子已经被gouweicao78于2008-9-3 15:25:02编辑过]

ty_1029

=IF(ROW()-2>COUNTIF($F$2:$F$180,5),"",LARGE(ROW($F$2:$F$180)*($F$2:$F$180=5),COUNTIF($F$2:$F$180,5)-ROW()+2)-MAX(LARGE(ROW($F$2:$F$180)*($F$2:$F$180=5),COUNTIF($F$2:$F$180,5)-ROW()+3),1)-1)

此公式为论坛上一为大虾做的统计F列数字5出现的间隔,但是该公式放在第二行向下拉一切正常,但放到其他行就出错，小弟看不明白,现在想要放到第四行开始,如何修改公式,其实主要是ROW()+2和ROW()+2等不明白,希望解答一下~~~

QUOTE:

gouweicao78答复：如果您认真阅读了本帖首页，那么就知道（1）ROW()是返回当前行号，在第1行它是1，第4行它就是4；（2）要么给链接，要么给附件，以便解释。

[此贴子已经被gouweicao78于2008-9-4 9:17:15编辑过]