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

sun126

请版主解释一下：=LOOKUP(1,0/(条件等式),返回区域)是如何计算的?

其中1是什么意思？是指TRUE吗？和返回值的先后没有关系是吗？

[此贴子已经被作者于2007-4-24 22:53:39编辑过]

gouweicao78

QUOTE:

请参考本帖第139楼：[函数用法讨论系列10] LOOKUP的查找策略！之流程图再谈二分法

1，就是数字1，用来与0比较。返回最后一个满足条件等式的记录。

tinalei

{=IF(ROW(1:1)>IF(((SUM(x)+1)/3)=ROUNDUP(SUM(x)/3,),ROUNDUP((SUM(x)/3),),ROUNDDOWN((SUM(x)/3),)),"",INDEX(Sheet1!A:A,SMALL(IF(x,1+ROW(INDIRECT("1:"&ROWS(x)))),ROW(1:1)+INT((COLUMN()-1)/4)*IF(((SUM(x)+1)/3)=ROUNDUP(SUM(x)/3,),ROUNDUP((SUM(x)/3),),ROUNDDOWN((SUM(x)/3),)))))}这个长长的公式吓怕我了,X=subtotal(2,offset(sheet1!$A$1,row(indirect("1:"&(count(sheet1!$A:$A)+1))),,,))累死，定义名称里不让复制。http://club.excelhome.net/viewthread.php?tid=236485&px=0

山哥

帮我解释一下

=IF(ROW(14:14)>SUM(Cnt_Arr),"",IF(ISNUMBER(--CODE),LEFT(K14)&CODE,CODE))

谢谢！！！！！！！！

QUOTE:

请阅读本帖首页的要求，上传附件或者给出原帖链接。Cnt_Arr、CODE均为定义名称，按Ctrl+F3查看。

——gouweicao78注

[此贴子已经被gouweicao78于2007-5-14 8:40:38编辑过]

gouweicao78

QUOTE:

以下是引用tinalei在2007-4-26 23:41:58的发言：
……,X=subtotal(102,offset(sheet1!$A$1,row(indirect("1:"&(count(sheet1!$A:$A)+1))),,,))累死，定义名称里不让复制。http://club.excelhome.net/viewthread.php?tid=236485&px=0

定义名称可以复制，用鼠标选择公式部分，按Ctrl+C即可。

1、X——取得筛选后Sheet1表A列的记录个数。

1）ROW(INDIRECT("1:"&(COUNT(Sheet1!$A:$A)+1)))——假设Sheet1A列的数值个数是100，则此部分返回Row(1:101)即{1;2;3;……;101}数组。

2）Offset(Sheet1A1,Row(1:101),,,)——分别从Sheet1的A1单元格偏移1、2、3……101行，即分别引用Sheet1的A2、A3……A102单元格。

3）Subtotal(102,Ref)——第一参数102，相当于Count函数并忽略隐藏（被筛选掉的）计数，返回的是由1和0组成的{1;0;……}数组，0表示被隐藏的部分，由此可用Sum(X)得到筛选后所剩下的记录个数。

2、那么长的一串公式，多数是数学计算了。即把筛选后的Sum(X)条记录分配成3组。

最重要的部分是SMALL(IF(x,1+ROW(INDIRECT("1:"&ROWS(x))))，……

前面说了，X是1、0组成的数组，其行数=COUNT(Sheet1A:A)+1，而在逻辑判断中，0=False、非0数值=True，所以，IF(X,……则根据单元格是否被隐藏来返回，被隐藏就是False。并以此对应Row(indirect("1:"&Rows(X)))的行号——还原了原有行号。

可以进行简化，比如：

=IF(1+(ROW(1:1)-1)*3+INT((COLUMN(A:A)-1)/4)>SUM(x),"",OFFSET(Sheet1!$A$1,SMALL(IF(x,ROW(INDIRECT("1:"&ROWS(x)))),1+(ROW(1:1)-1)*3+INT((COLUMN(A:A)-1)/4)),MOD(COLUMN(A:A)-1,4)))——先行后列

(Row(1:1)-1)*3——每下1行相当于在原表的3个记录

INT((COLUMN(A:A)-1)/4)——每过4列相当于在原表的1个记录。以此作为跳转行数的依据。

Mod(Column(A:A)-1,4)——每4列一个循环，以此作为跳转列数的依据。

[此贴子已经被作者于2007-5-21 10:38:35编辑过]

gouweicao78

关于自动筛选或隐藏条件下的查找引用及计算

许多朋友对SUBTOTAL函数并不陌生，知道这是一个可以忽略因自动筛选或隐藏行列操作中被隐藏的数据，诸如=SUBTOTAL(109,A1:A10)来对A1:A10筛选后求和。

该函数支持三维引用，因而在筛选后的查找引用以及计算方面也有着高级应用，下面结合实例解释一下其核心公式SUBTOTAL(3,OFFSET($A$1,ROW(data)-1,)的工作原理。（请点击参考帖下载附件）

1、OFFSET(A1,ROW(Data)-1,)其中，Data是一个引用区域，假设为A2:A10，则ROW(Data)-1得到{1;2;3;4;5;6;7;8;9}即从1开始的行数与Data同的等差序列。由此Offset以A1单元格为原点，分别便宜1、2……9行，即分别引用A2、A3、A4……A10

2、SUBTOTAL(3,……)，第1参数用3或103相当于可以忽略隐藏的COUNTA函数，假设A2、A4、A6、A8行被隐藏，则核心部分返回{0;1;0;1;0;1;0;1;1}，即被隐藏行对应0。

3、select=IF(SUBTOTAL(3,OFFSET($A$1,ROW(data)-1,)),data,"")——根据Subtotal得到的{0;1……}数组，将Data中被隐藏的部分替换成""，形成新的内存数组select。

4、=IF(ROW(A1)>SUM((select<>"")*(MATCH(select,select,0)=ROW(data)-1)),"",INDEX($A:$A,SMALL(IF((select<>"")*(MATCH(select,select,0)=ROW(data)-1),ROW(data)),ROW(A1))))

经典的Match=Row求不重复值法，再加select<>""的条件，由此得到自动筛选后再筛选出不重复值的结果。

参考：
[显示自动筛选的筛选条件]　By gdliyy

统计计算更为简得，

即利用Subtotal返回{0;1……}数组，直接参与乘积计算。

请见实例：[请教筛选后的乘积求和公式]

glhdex

请教解释公式

OFFSET('1组1'!$A$7,INT(ROW()/22),0)

OFFSET('1组1'!$A$6,MATCH($B2,HUZ28,0),COLUMN(B:B)-1)

gouweicao78

QUOTE:

以下是引用glhdex在2007-6-29 6:29:25的发言：

请教解释公式

OFFSET('1组1'!$A$7,INT(ROW()/22),0)

OFFSET('1组1'!$A$6,MATCH($B2,HUZ28,0),COLUMN(B:B)-1)

请阅读本帖首页的规定，除非你认为下面这样的解释已经满意了。

第1个公式：从《1组1》表中A7单元格偏移Int(Row()/22)行。

第2个公式，从《1组1》表中A6单元格偏移Match(B2,HUZ28,0)行，偏移Column(B:B)-1列。

imljs

请帮忙解释一下C2中的公式

bYTWwcfs.zip (2.11 KB, 下载次数: 81)

2007-7-14 11:27 上传

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

gouweicao78

QUOTE:

以下是引用imljs在2007-7-14 11:27:59的发言：

请帮忙解释一下C2中的公式

1、C2公式仅IF、And和Lookup三个，难度都不大，Lookup可看函数帮助，为升序查找。

2、根据附件第三点可以解释此公式：

QUOTE:

三、当D＝900且0.6≤H0≤2.0时，盖板厚度h＝120；当D=900，且0.4≤H0＜0.6或2.0＜H0≤4.0时，h＝150；
当D＝1000～1100且0.6≤H0≤2.0时，盖板厚度h＝130；当D=1000～1100，且0.4≤H0＜0.6或2.0＜H0≤4.0时，h＝160；
当D＝1250～1350且0.6≤H0≤2.0时，盖板厚度h＝160；当D=1250～1350，且0.4≤H0＜0.6或2.0＜H0≤4.0时，h＝200；
当D＝1500～1650且0.6≤H0≤2.0时，盖板厚度h＝200；当D=1500～1650，且0.4≤H0＜0.6或2.0＜H0≤4.0时，h＝240；
当D＝1800～2000且0.6≤H0≤2.0时，盖板厚度h＝240；当D=1800～2000，且0.4≤H0＜0.6或2.0＜H0≤4.0时，h＝280。

换句话说也就是这样：

当0.6≤H0≤2.0时，根据D=900、1000~1100、1250~1350、1500~1650、1800~2000，分别得到h=120、130、160、200、240。即：=IF(AND(B2>=0.6,B2<=2),LOOKUP(A2,{900,1000,1250,1500,1800},{120,130,160,200,240}),……

省略号部分就是H0不在0.6~2.0这个范围的时候的值，也是Lookup升序查找。