ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 EH云课堂直播课程免费学 打造核心竞争力的职场宝典
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
楼主: gouweicao78

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

    [复制链接]

TA的精华主题

TA的得分主题

发表于 2007-4-24 22:52 | 显示全部楼层
本帖已被收录到知识树中,索引项:公式基础

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

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-4-25 11:46 | 显示全部楼层
QUOTE:

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

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

TA的精华主题

TA的得分主题

发表于 2007-4-26 23:41 | 显示全部楼层
{=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

TA的精华主题

TA的得分主题

发表于 2007-5-12 15:32 | 显示全部楼层

帮我解释一下

=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编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-5-21 10:12 | 显示全部楼层
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编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-5-25 11:12 | 显示全部楼层

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

许多朋友对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……}数组,直接参与乘积计算。

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

TA的精华主题

TA的得分主题

发表于 2007-6-29 06:29 | 显示全部楼层

请教解释公式

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-7-5 22:15 | 显示全部楼层
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列。

TA的精华主题

TA的得分主题

发表于 2007-7-14 11:27 | 显示全部楼层

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

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

TA的精华主题

TA的得分主题

 楼主| 发表于 2007-7-16 13:22 | 显示全部楼层
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升序查找。

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,每天学会一个新技能

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2019-10-22 07:21 , Processed in 0.059489 second(s), 11 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

沪公网安备 31011702000001号 沪ICP备11019229号

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:徐怀玉律师 李志群律师

快速回复 返回顶部 返回列表