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

星语心愿

关于根据身份证号码取出生年月的公式我懂了，谢谢版主的耐心指导。再有个问题呀。同样是这个文件里面的：

=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)

这个是把原来的身份证号码全部改为18位的公式。能抽空指点一下吗？谢谢啦？

cnfeng

可否解释一下：{=SUM(OFFSET(INDIRECT({"A2","B3","C1"}),2,1,3,3))}

           与｛=SUM(SUM(OFFSET(INDIRECT({"A2","B3","C1"}),2,1,3,3)))}的差别?

各位侠客有空请到:http://club.excelhome.net/viewthread.php?tid=186165&replyID=&skin=0 看看.

谢了!

QUOTE:

优秀会员czzqb兄的解释已经通俗易懂了。

[此贴子已经被山菊花于2006-9-7 10:08:17编辑过]

山菊花

A1="987654321012345"

身份证号码由15位改为18位，分两部分更改：

1、在原号码的第7位插入“19”，把2位数的年份改为4位数表示：REPLACE(A2,7,,19)

2、后面加一个识别码。

①把年份改为4位数成17位号码："98765419321012345"

②各位上的数字依次乘以2的17、16、15……1次方，并求和：

s=9*2^17+8*2^16+7*2^15+……+4*2^2+5*2^1

公式为：

各位上的数字：MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)

系数：2^(18-ROW(INDIRECT("1:17")))

求和：SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17"))))

③求上面结果除以11所得余数：mod(s,11)

④取字符串"10X98765432"中对应位置(余数+1)上的字符。

计算过程：

在编辑栏，一步一步用鼠标按下面所示效果选取公式，按F9：

1、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)

2、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID("98765419321012345",ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)

3、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM({"9";"8";"7";"6";"5";"4";"1";"9";"3";"2";"1";"0";"1";"2";"3";"4";"5"}*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)

4、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM({"9";"8";"7";"6";"5";"4";"1";"9";"3";"2";"1";"0";"1";"2";"3";"4";"5"}*{131072;65536;32768;16384;8192;4096;2048;1024;512;256;128;64;32;16;8;4;2}),11)+1,1),A2)

5、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM({1179648;524288;229376;98304;40960;16384;2048;9216;1536;512;128;0;32;32;24;16;10}),11)+1,1),A2)

6、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(2102514,11)+1,1),A2)

7、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",8,1),A2)

8、=IF(LEN(A2)=15,REPLACE(A2,7,,19)&"5",A2)

9、=IF(LEN(A2)=15,"987654193210123455",A2)

条件LEN(A2)=15成立，所以返回结果"987654193210123455"

xlbcxm8689

請朋友們幫我解釋一下這個公式：

｛ =IF(ROW(1:1)<=COUNTIF(x,">0"),LOOKUP(SMALL(IF(x>0,ROW(x)),ROW(1:1)),ROW(x),汇总表!B$6:B$10000),"")｝

謝謝。

主要是X這個東東，我不理解，是變量嗎？

LOOKUP(SMALL(IF(x>0,ROW(x)),ROW(1:1)),ROW(x),汇总表!B$6:B$10000),"")

又是什么意思？

z5ut33q6.rar (5.79 KB, 下载次数: 92)

2006-9-8 10:27 上传

点击文件名下载附件

QUOTE:

x这东东叫自定义名称，按Ctrl+F3可查看，它代表的是一个单元格区域，与公式中直使用“汇总表!$F$6:$F$10000”效果是一样的，这样可以缩短公式的长度。

关于第二个疑问，请阅读第一页gouweicao78关于如何解读公式的帖子，自己动手拆一拆、解一解。

IF(x>0,ROW(x))：返回符合条件的行号。

SMALL(IF(x>0,ROW(x)),ROW(1:1))：符合条件的第一个行号(从小到大排列)。

LOOKUP()：根据指定的行号，返回B列的值。

                                                                                     ——山菊花

[此贴子已经被山菊花于2006-9-8 11:19:56编辑过]

mqcwl

1、data=OFFSET(汇总!$B$3,,,SUM(Num_1)) 
2、iRow=IF(data=myName,ROW(data)-1) 
3、myName=SUBSTITUTE(GET.DOCUMENT(1),"["&GET.DOCUMENT(88)&"]",)&T(NOW()) 
4、myTotal=COUNTIF(data,myName) 
5、Num_1=COUNTIF(INDIRECT(sh&"!A3:A100"),">0") 
6、Num_2=MMULT(N(ROW(INDIRECT("1:"&ROWS(Num_1)))>COLUMN(INDIRECT("C1:C"&ROWS(Num_1),0))),Num_1) 
7、sh={1;2;3} 
特别是第3、4、5、6、7个名称定义，我真的搞不懂，请帮我解释一下，谢谢。 yRoZeB5n.rar (6.7 KB, 下载次数: 61)

2006-9-8 20:58 上传

点击文件名下载附件
[求助]名称定义解释

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

[此贴子已经被作者于2006-9-8 21:01:46编辑过]

sugar1353138

请问如何由一连串身份证号码转换成年龄

QUOTE:

A1=身份证号码

B1=DATEDIF(TEXT(MID(A1,7,IF(LEN(A1)=15,6,8)),"00-00-00"),TODAY(),"y")

 

——山菊花

[此贴子已经被山菊花于2006-9-19 0:12:36编辑过]

cq_qg

这是原来在这里下载的一个附件，好像是一个竞赛的题目，可是找不到是哪个了，我上传附件。

想问的是求行列和的公式，怎么与我理解的相反，我觉得里面求列的和的公式应该是求行的和，按F9键看到也是这样，可是偏偏得到的结果又是对的。

可能比较简单，可是我还是想不明白，希望能解答一下。

j7PqE4n8.rar (2.1 KB, 下载次数: 76)

2006-9-19 13:53 上传

点击文件名下载附件

[此贴子已经被作者于2006-9-19 13:53:20编辑过]

山菊花

是你理解有误。

B20:E20的公式：

=SUM(OFFSET($B$7:$E$16,,COLUMN($B$7:$E$16)-COLUMN(OFFSET($B$7:$E$16,,,1,1)),,1))

在编辑栏，照这样选择公式，按F9，得：

=SUM(OFFSET($B$7:$E$16,,{0,1,2,3},,1))

{0,1,2,3}表示列位移量，表示B7:E16，从左往右数第几列。

H7:H16的公式：

=SUM(OFFSET($B$7:$E$16,ROW($B$7:$E$16)-ROW(OFFSET($B$7:$E$16,,,1,1)),,1,))

在编辑栏，照这样选择公式，按F9，得：

=SUM(OFFSET($B$7:$E$16,{0;1;2;3;4;5;6;7;8;9},,1,))

{0;1;2;3;4;5;6;7;8;9}表示行位移量，表示B7:E16，从上往下数第几行。

用MMULT()求解：

B20:E20=MMULT(TRANSPOSE(ROW(B7:B16)^0),B7:E16)

H7:H16=MMULT(B7:E16,{1;1;1;1})

yingdsss

{=LOOKUP(SMALL(IF(MATCH(newdata,newdata,)=Row,Row),ROW(INDIRECT("1:"&SUM(N(MATCH(newdata,newdata,)=Row))))),Row,newdata)}

=IF(A3="","",SUM(SUMIF(INDIRECT(ROW($1:$3)&"!B3:B37"),B3,INDIRECT(ROW($1:$3)&"!c3:c37"))))

是什么意思啊，能帮我解答问题吗

公式源贴：http://club.excelhome.net/viewthread.php?tid=189883&replyID=&skin=0

[此贴子已经被chrisfang于2006-9-23 17:42:02编辑过]

chrisfang

回复yingdsss朋友：

A：对于第一个公式，这是提取不重复值的区域内存数组公式用法，这种用法之前在论坛上已经广为运用且较为成熟，可以看我的签名档里就有相关不重复值提取公式的链接：http://club.excelhome.net/viewthread.php?tid=172729

一、

一般，我们对不重复值提取的套路公式如下：

=INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(data)-X,ROW(data_arr)-X),ROW(1:1))))

其中data指数据源，可以是区域引用或区域引用的定义名称(如果是内存数组，公式稍有变化，后面会提到)；其中的X指的是data数据源首行相对于表格第一行的位置，比如data的首行是表格中的第五行，那么X值就取5-1=4，公式中把具体的数值放进去就可以。

关于这个提取不重复值的公式，可能其他地方也有比较详细的解释，我这里稍微再简单介绍一下。

1，首先看这一部分：

IF(MATCH(data,data,0)=ROW(data)-X,ROW(data_arr)-X),

match(data,data,0)返回的是一串行标，data中每一个元素去与data数据源作对应查找，找到第一个与自己相同的元素就返回它所在的相对行位置。我们假设data={3;2;4;2;4}，它的查找结果就是{1;2;3;2;3}，因为在查找后面的重复数2与4的时候，首先返回的是它们在第二行和第三行的位置。

2，然后通过if函数对返回的相对行标进行筛选：row(data)-x在刚才举的例子中就是五个数的相对行位置{1;2;3;4;5}，通过if({1;2;3;2;3}={1;2;3;4;5},{1;2;3;4;5})这样的公式操作，返回结果：{1;2;3;false;false}，这样就可以把包含重复的内容筛除。

3，最后一步index(data,small({1;2;3;false;false},row(1:1)))的作用就是把前面取出来的相对行标用index函数定位到data数据源的相应位置上，取出每一个对应的元素。

二、

这就是对上面那个套路公式的解释。楼主所引用的公式与这个公式思路基本相同，但因为是区域数组公式，所以在有些细节上稍有不同，下面就讲一下不同的地方。

=LOOKUP(SMALL(IF(MATCH(newdata,newdata,)=Row,Row),ROW(INDIRECT("1:"&SUM(N(MATCH(newdata,newdata,)=Row))))),Row,newdata)

1，其中的黄色部分结构与前面基本一致，所不同的是，这里的newdata是一个内存数组而不是对源数据的区域引用，它所引起的变化就是后面的Row，这个Row也是一个定义名称，定义名称的公式为：=ROW(INDIRECT("1:"&ROWS(newdata)))，就是以内存数组newdata的高度为元素个数生成一个1:N的数值数组，其中N就等于newdata的元素个数。因为对于内存数组来说，并不能用row函数来取得它的每一个元素的行标，所以要通过这种定义的方式来自己创造一个类似于行标形式的标竿出来，这就是我们定义的这个名称Row，它取代了上面公式里的ROW(data)-X这部分。

这部分内容是数据源从区域引用变化为内存数组后处理方式变化的关键，可能我的表达还不是很清楚，有不明白的请多看几遍并且自己试验一下看看中间过程。

2，绿色部分公式是为最后结果输出为区域数组公式而准备的，何谓区域数组公式，有不清楚的请见本贴第46楼。我们在最初的公式中，这部分对应的公式是small(....,row(1:1))，这部分公式在下拉过程中，row(1:1)会依次变为row(2:2)、row(3:3)。。。，使得small函数结构成为small(....,1)、small(....,2)、small(....,3)等等，依次取得每一个行标。现在因为结果为区域数组公式，我们需要把输出结果也构造成一个内存数组。这就需要row(1:1)这部分内容成为一个可以自适应的数组。绿色公式这长长的一串就是起的这个作用。其中SUM(N(MATCH(newdata,newdata,)=Row))这部分是计算不重复内容的个数，也就是small函数中第二参数的个数。ROW(INDIRECT("1:"&...))这部分就是生成一个1:N的递增数组，其中N就是前面计算出来的不重复个数。对于前面举过的那个例子来说，这部分的公式结果就是small({1;2;3;false;false},row(1:3))。

3，最外面的lookup(....,row,newdata)部分就是最后构造输出结果为内存数组的部分。前面已经把不重复数所对应的位置标竿取得，现在要做的就是根据这个位置标竿去newdata里面取具体的数据了。lookup(不重复数的标竿,所有数的标竿,newdata)的结构就可以把所有不重复数取出并生成一个新的可被后续引用的内存数组了。

关于第一个公式就解释到这里，在源贴中，我把最后公式做成了区域数组公式，当然这并非是必须的，也可以使用单元格数组公式的形式：

=INDEX(newdata,SMALL(IF(MATCH(newdata,newdata,)=Row,Row),ROW(1:1)))，下拉即可，这就比原来的就要简单许多。

B：下面来看第二个公式(问问题的太狠了，这个帖子要累死我了)

=IF(A3="","",SUM(SUMIF(INDIRECT(ROW($1:$3)&"!B3:B37"),B3,INDIRECT(ROW($1:$3)&"!c3:c37"))))

这是一个跨表单条件求和的公式，其中涉及到了indirect的三维引用(行、列、sheet)。

1，公式结构并不复杂，先来看INDIRECT(ROW($1:$3)&"!B3:B37")这部分。

row(1:3)生成的是一个数组{1;2;3}，与感叹号!结合后就是对三张sheet的一维引用(三张sheet的表名分别为1,2,3，所以这里构造这样一个数组来对其引用)。结合后面的B3:B37(以及公式后面部分的C3:C37)构成了对各个sheet的相关单元区域的三维引用。

2，然后外面是一个简单的条件求和公式sumif(条件区域,条件,求和区域)，这样就取得了指定区域的条件和。因为引用的源是一个三维区域，所以sumif的结果还是以sheet表为划分的一个数组，需要外面再套一层sum函数来取得三张sheet表的和值。这样的结果才是最终要取的多表条件和值。

第二个公式也就解释到这里。

说点题外话，草版主开的这个主题真是作孽啊，解释一个公式能打上半天字，累啊，还不知道对方能不能看明白。这个帖子里这么多口水都能合在一起出本书了。

呵呵，以上玩笑话，绝无贬义。

[此贴子已经被作者于2006-9-23 20:13:39编辑过]