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

山菊花

按名次顺序把“人次”连接起来，得到一个整数，数值大的表示前面名次的人数多，同等名次(Rank结果，名次1)之间依此得到一个排列(名次2)，名次1+名次2得到一个不重复的名次排列。

公式二：

P3=RANK(N3,$N$3:$N$17)+SUM(($N$3:$N$17=$N3)*(MMULT((MOD(COLUMN($B$3:$M$17),2)=0)*$B$3:$M$17,10^(12-ROW($1:$12)))>SUM((MOD(COLUMN($B3:$M3),2)=0)*$B3:$M3*10^(13-COLUMN($B:$M)))))

数组公式，按Ctrl+Shift+Enter结束(见工作表2)。

QZRUpbYn.rar (6.11 KB, 下载次数: 216)

2006-8-26 07:51 上传

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

cq_qg

谢谢山菊花的解释！并给出了另一个公式。

还有一个，再次请教，谢谢！

原贴地址

http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=88180&id=15309&page=1&skin=0&Star=1

请解释公式：

=REPLACE(REPLACE($M$1,MIN(IF(ROW()<{9,16,22,27,31,34,36,37},{1,2,3,4,5,6,7,8})),1,""),ROW()+9-MIN(IF(ROW()<{9,16,22,27,31,34,36,37},{9,16,22,27,31,34,36,37})),1,"")

山菊花

公式写出来了，不难理解。要写出它，不容易。

一个字符串有九个字符，用两个replace()函数，把指定位置上的字符替换成空字符，得到结果为7个字符的文本。

为什么用数组{9,16,22,27,31,34,36,37}？这是根据结果需要量身定做的，见原帖解释。

Z9qW9rEo.rar (4.73 KB, 下载次数: 138)

2006-8-27 10:41 上传

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

my204

请解释一下这2个公式：

‘=LOOKUP(2,1/(A2:A65536<>""),A2:A65536)

{=INDEX(A1:A65535,LARGE(IF(A1:A65535<>"",ROW(A1:A65535)),2))}

谢谢！

山菊花

第一个公式参见：

http://club.excelhome.net/viewthread.php?tid=184297&extra=&page=2#507861

第二个公式争取自己把它搞定。

星语心愿

我看到这样一个根据身份证号码求年龄的公式：如果身份证号码在A2：

=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,l)

请帮忙解释一下这个公式好吗？

kuPMgymC.rar (90.49 KB, 下载次数: 117)

2006-9-3 17:16 上传

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

chrisfang

回复星语心愿

首先，你提供的公式不是计算年龄而是显示出生日期，公式如下：

=IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)

第一个黄色区域：(LEN(A2)=15)*19：身份证目前有两种号码，15位和18位的，其中18位在前面的年份是显示四位的数字，如1969，而15位身份证号只显示两位年份，如69。此段公式的意思就是判断A2中身份证号是否为15位，如果是15位，那么前面的等式运算返回值就是true，与19相乘的结果就是19，也就是补上了年份的前两位数字。如果身份证是18位的，那么前面的等式运算返回值就是false，与19相乘结果为0。

第二个黄色区域：MID(A2,7,6+(LEN(A2)=18)*2)与上面类似，这部分取的是身份证号中的出生年月日字段。15位和18位的身份证的出生日期字段都是从第7个字符开始，由于18位的身份证年份数字多两位，所以在取字符时要再加两位。(LEN(A2)=18)*2就是起这个判断作用。

两部分黄色区域相&：两部分黄色区域字符连接后，就是一个完整的yyyymmdd形式的出生日期字符串。要把这个字符串转化成系统承认的日期格式，可通过Text(字符串,"#-00-00")+0来完成，前面的text部分是把字符串转换成一个日期的字符形式：yyyy-mm-dd。这样输入的字符串会被系统认成日期。后面一个+0的作用是把字符串转换成数值，也就是日期的系列数。也就是="yyyy-mm-dd"+0的意思，所以前面公式也可以是这样的形式：--Text(字符串,"#-00-00")。

[此贴子已经被作者于2006-9-4 11:08:24编辑过]

mqcwl

请山菊花老师帮我解释一下下面定义名称的公式。 x =IF(件数,IF(MATCH(品名,品名,)=ROW(品名)-13,ROW(品名))) 件数 =OFFSET(品名,,1) 　 　 　 　 品名 =装箱!$B$14:INDEX(装箱!$B:$B,MATCH("合计：",装箱!$A:$A)-1)                                                                                   名称定义具体的报表在附件中.

UzrqRlGC.rar (11.83 KB, 下载次数: 81)

2006-9-4 12:43 上传

点击文件名下载附件

[此贴子已经被作者于2006-9-6 14:53:35编辑过]

山菊花

在单元格中编写公式，有些函数或比较复杂的表达式，要反复调用，这种情况下，可以把这部分定义为一个名称，在公式中用一个名称代替长长的表达式，缩短公式的长度。

如下面的公式，使用了名称x。

=IF(ROW(1:1)>COUNT(x),"",INDEX(品名,SMALL(x,ROW(1:1))-13))

因为x=IF(件数,IF(MATCH(品名,品名,)=ROW(品名)-13,ROW(品名)))

如果不使用名称x，则公式为：

=IF(ROW(1:1)>COUNT(IF(件数,IF(MATCH(品名,品名,)=ROW(品名)-13,ROW(品名)))),"",INDEX(品名,SMALL(IF(件数,IF(MATCH(品名,品名,)=ROW(品名)-13,ROW(品名))),ROW(1:1))-13))

(数组公式)

其中品名与件数也是名称，是对工作表区域的引用，如果不使用该名称，也可在公式中直接使用单元格地址。

使用名称不仅仅是为了缩短公式的长度，公式中的“品名”、“件数”是对工作表区域的动态引用，把这个功能从公式中分离出来，使公式具有通用性：

x=IF(件数,IF(MATCH(品名,品名,)=ROW(品名)-13,ROW(品名)))

无论引用的区域如何变化，公式保持不变，不会因为区域变了，而影响公式的使用。

动态引用要根据实际情况而定。本例中定义一个“品名”，区域从B14开始，向下至A列出现“合计：”为止，公式为：

=装箱!$B$14:INDEX(装箱!$B:$B,MATCH("合计：",装箱!$A:$A)-1)

其中MATCH("合计：",装箱!$A:$A)-1是“合计：”的前一行。关于B14:index(B:B,n)的引用方式，见本帖89楼的解释。

本例中，品名引用的区域实际是B14:B45，“件数”是根据品名的变化而变化，OFFSET(品名,,1)表示品名的右一列，起始行与行数与品名相同。

关于定义名称，可在论坛上搜索一下，好帖子不少，如：

hxhgxy：定义名称的妙处

筛选不重复值的方法不少，“Match+Row”形式是其中的一个经典用法：

x=IF(件数,IF(MATCH(品名,品名,)=ROW(品名)-13,ROW(品名)))

本帖7楼有一个链接，可看看。

cnfeng

这个贴子太好了！

想知道剑魔三式的公式祥细解释可以吗？

具体链接：

http://club.excelhome.net/viewthread.php?tid=630&replyID=&skin=0

具体哪个公式？

索引值部分：

=IF($D$1="","",IF(COUNTIF(OFFSET(INDIRECT($B$1),,,$G$1),$D$1)=0,"",IF(ROW()=3,MATCH($D$1,OFFSET(INDIRECT($B$1),,,$G$1),0),IF(ISERROR(MATCH($D$1,OFFSET(INDIRECT($B$1),A2,0),0)+A2),"",MATCH($D$1,OFFSET(INDIRECT($B$1),A2,0),0)+A2))))

先谢了！

[此贴子已经被作者于2006-9-5 17:59:43编辑过]