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

nectar

请教:

1. 公式的意思

附件中的H16=

{=INDEX(C$4:C$20,MATCH(SMALL(IF($C$4:$C$20<--"2008-8-1",$C$4:$C$20),ROW(C12)),$C$4:$C$20,))}

附件中的H17=

{=INDEX(C$4:C$20,MATCH(SMALL(IF($C$4:$C$20<--"2008-8-1",$C$4:$C$20),ROW(C13)),$C$4:$C$20,))}

2. 上面两个公式看上去是一样的,显示结果为什么不一样(H16是日期,H17是数值)

请看附件.

ao9Elcoa.rar (4.62 KB, 下载次数: 35)

2008-7-7 21:53 上传

点击文件名下载附件

[此贴子已经被作者于2008-7-7 21:53:46编辑过]

gouweicao78

QUOTE:

以下是引用nectar在2008-7-7 21:36:49的发言：

请教:

1. 公式的意思

附件中的H16=

{=INDEX(C$4:C$20,MATCH(SMALL(IF($C$4:$C$20<--"2008-8-1",$C$4:$C$20),ROW(C12)),$C$4:$C$20,))}

附件中的H17=

{=INDEX(C$4:C$20,MATCH(SMALL(IF($C$4:$C$20<--"2008-8-1",$C$4:$C$20),ROW(C13)),$C$4:$C$20,))}

2. 上面两个公式看上去是一样的,显示结果为什么不一样(H16是日期,H17是数值)

请看附件.

看了附件，猜测出意图是：将满足C列日期早于2008-8-1的记录按时间顺序从小到大列出来。实际上这里的MATCH用法错了，因为日期可能重复，而Match只能找到第一条记录，所以有附件中的疑问。

问题2、H16、H17格式不一样。日期是数值的一种特殊格式。把H16公式下拖即可。如用G列公式右拖，则会改变H列格式，出现类似问题。

根据意图重新设计公式：

=INDEX(A:A,MOD(SMALL(IF($C$4:$C$20<--"2008-8-1",$C$4:$C$20+ROW($4:$20)%%),ROW(1:1)),1)/1%%)

数组公式，按Ctrl+Shift+Enter结束，右拖下拖复制。即：将满足条件的日期加上“万分之行号”，排序后相同日期的记录则行号在前的排在前面，再对排序后的日期+万分之行号对1求余得到小数部分即万分之行号，并除以万分之一，得到行号。如果C4:C20最大行数不超过99，用一个百分号即可，如果超过10000行，则用%%%代替。

nectar

谢谢解答!

再请教一个:

为什么计算房间总数的公式:

{=SUM(N(--LEFT($A$3:$A$38,1)=G3))}要用N函数,

而计算出租房间个数的公式:

{=SUM((--LEFT($A$3:$A$38,1)=G3)*($B$3:$B$38="出租"))}不用N函数?

请看附件.

MYCzKoM2.rar (2.4 KB, 下载次数: 38)

2008-7-8 19:32 上传

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

gouweicao78

QUOTE:

以下是引用nectar在2008-7-8 19:33:02的发言：

为什么{=SUM(N(--LEFT($A$3:$A$38,1)=G3))}要用N函数,

而{=SUM((--LEFT($A$3:$A$38,1)=G3)*($B$3:$B$38="出租"))}不用N函数?

公式中，LEFT(A3:A38)是将该区域首个字符提取出来，因为是文本所以用--转换为数值再与G3比较

而(--LEFT($A$3:$A$38,1)=G3)得到的是一组逻辑值TRUE、FALSE，因而用N函数转换为1、0，方可用于SUM求和。

第2个公式中，得到的是两组逻辑值相乘，在四则运算中，TRUE=1、FLASE=0。因而就不需要再用N函数了。

这里涉及到转换文本型数字为数值、转换逻辑值为数值的方法，你可阅读本帖首页有关基础知识。

独立董事

楼主好猛啊，我来求救！谢谢！

=INDEX(XINDIAN,26-VALUE(IF(MID(J5,2,1)<>"-",LEFT(J5,2),LEFT(J5,1))),VALUE(RIGHT(J5,(LEN(J5)-FIND("-",J5)))))

帮我解一下这个公式，如果方便的话请帮我分解一下，我知道它的作用，是从一个名字叫XINDIAN的表格里取数字，但是看了好久没分解开，我想学会，谢谢楼主！

yanmeizhou

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

8楼中的公式=SUM(INDIRECT("'"&$A2&"'!C"&COLUMN(A:A),))其中的“c”代表什么。

QUOTE:

gouweicao78：请阅读本帖首页相关基础知识。R1C1引用样式。

[此贴子已经被gouweicao78于2008-7-15 12:36:36编辑过]

nectar

请教:

=IF(ROW(1:1)>SUMPRODUCT(($C$4:$C$50<$G$4)*($C$4:$C$50>0)),"",INDEX(C:C,SMALL(IF($C$4:$C$50<$G$4,ROW($4:$50)),ROW(1:1))))

请看附件(G6) wVzuzulK.rar (5.98 KB, 下载次数: 34)

2008-7-15 20:18 上传

点击文件名下载附件

[此贴子已经被作者于2008-7-15 20:18:16编辑过]

gouweicao78

QUOTE:

以下是引用nectar在2008-7-15 15:19:59的发言：

请教:

=IF(ROW(1:1)>SUMPRODUCT(($C$4:$C$50<$G$4)*($C$4:$C$50>0)),"",INDEX(C:C,SMALL(IF($C$4:$C$50<$G$4,ROW($4:$50)),ROW(1:1))))

这个类型的公式，在【答疑解惑】前几页都有。其中，SUMPRODUCT(($C$4:$C$50<$G$4)*($C$4:$C$50>0))统计C列日期小于G4且大于0（空单元格可被识别为0）的个数，IF(ROW>SUMPRODUCT,""表示公式下拉行数超过sumproduct的个数，返回空文本。

IF($C$4:$C$50<$G$4,ROW($4:$50))——如果C列日期小于G4，返回日期所在的行号。注意此处的IF条件少了一个>0或者<>""的判断，如果C列日期为空（空单元格可被识别为0），会返回1900-1-0

SMALL(IF(),ROW(1:1)——将IF得到的满足条件的日期所在的行号从小到达排列出来

INDEX(C:C,small排列的行号)——取得C列对应行号的值，也就是满足条件的日期。

[此贴子已经被作者于2008-7-15 20:28:37编辑过]

nectar

请教日期函数:

=DATE(YEAR(NOW()),IF(MOD(ROW(),2)=1,A1,A1+1),1)-IF(MOD(ROW(),2)=1,0,1)

[此贴子已经被作者于2008-7-20 11:41:56编辑过]

gouweicao78

QUOTE:

以下是引用nectar在2008-7-18 21:08:35的发言：

请教日期函数:

=DATE(YEAR(NOW()),IF(MOD(ROW(),2)=1,A1,A1+1),1)-IF(MOD(ROW(),2)=1,0,1)

本帖首页的“规矩”还不明白吗？附件或者链接，结合实际来解释你才更容易明白。

根据这个公式MOD(ROW(),2)是将当前行号对2求余，也就是奇数、偶数行的判断。因为一个整数对2求余，只有两种结果即1和0，余数为1就是奇数，余数为0表示整除即偶数。因而这公式中的2个IF都可以不要。

比如=IF(MOD(ROW(),2)=1,A1,A1+1)，因为MOD只可能=1、0，而要得到的结果却是A1+0、+1，可变通为：

=A1+MOD(ROW()-1,2)——比如：row()是奇数行，按IF公式，就是判断MOD=1，并返回A1；按这个公式就是=A1+MOD(奇数-1,2)也就是偶数，=A1+0，同样返回A1。

因而：整改公式就是在奇数行得到当前年份、A1月份的1号；偶数行得到A1+1月的1号再减1，也就是A1月份的下个月倒回来1天，就是A1月份的最后一天。