ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: gouweicao78

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

    [复制链接]

TA的精华主题

TA的得分主题

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

gvntw版主的函数版:本页小计、累计 OR ONLY 本页小计公式解释
  

公式1、A列的序号公式及改写

=IF(MOD(ROW(),J$1+1)=2,"本页小计",(COUNT(A$2:A2)+1<=MAX(Data!A:A))*(COUNT(A$2:A2)+1))

解释:
  
1、  首先,J1单元格存放着每页要的行数(本例中为每20行一个小计)——这就是循环的周期。

2、  利用求余函数MOD来做循环:

公式从第3行开始ROW()取得当前行号即3,MOD(ROW(),J$1+1)èMOD(3,21)è得到余数3。

目标:在20行后的第1行,也就是第3+20行显示“本页小计”,第23行的MOD(23,21)=2,这就是我们要的条件。

修改:将Row()改为Row(1:1),则公式改为=IF(MOD(ROW(1:1),J$1+1)=0,……也是同样的目的,区别在于不拘束与公式从第3行开始。

3、  (COUNT(A$2:A2)+1<=MAX(Data!A:A))* (COUNT(A$2:A2)+1):Count只计算数值的个数,A$2:A2是一个变动的区域,下拖复制时变为A$2:A3、A$2:A4……(请先学习相对引用和绝对引用

当序号小于等于Data原有序号的最大值时,用这个公式得到1、2、3等序号,超过时得到0,然后工具〉选项〉视图〉零值设置不显示,隐藏了0。

修改
=IF(MOD(ROW(1:1),J$1+1),(MAX(I$2:I2)<MAX(Data!A:A))*(MAX(I$2:I2)+1),"本页小计"),用Max忽略文本值的方法也可以达到。

公式2、B列的查询公式及改写

=IF(N(A3),VLOOKUP($A3,Data!$A:$H,COLUMN(),0),"")——Vlookup根据序号查找姓名。

N(A3)——如果A3是文本,返回0,否则返回数值。

此处因Data的序号是升序排列,Vlookup最后一个参数可以不用0,即模糊查找即可。=IF(N(A3),VLOOKUP($A3,Data!$A:$H,COLUMN()),"")

修改:升序排列Lookup也可以。

=IF(N(A3),LOOKUP($A3,Data!$A:B),"")——注意$A:B——A列绝对引用,B列相对引用。

公式3、C列的小计公式及查询
 

=IF($A3="本页小计",SUM(INDIRECT("R[-"&$J$1&"]C:R[-1]C",0)),IF$A3,VLOOKUP($A3,Data!$A:$H,COLUMN(),0),))
如果A列是”本页小计”,则返回SUM求和,否则返回利用序号查询,同理Vlookup对升序的查询可改版为:LOOKUP($A3,Data!$A:C)

Indirect函数第2参数为1或默认,表示A1引用样式,为0或False表示R1C1引用样式(请先学习引用样式),R——表示行、C表示列。

将J1的数字(20行)代入"R[-"&$J$1&"]C:R[-1]C"è得到"R[-20]C:R[-1]C"è表示当前单元格往上20行至往上1行的区域。
也就是对当前行上面的20行求和——这就是小计。

[此贴子已经被作者于2006-11-24 22:45:36编辑过]

TA的精华主题

TA的得分主题

发表于 2006-11-25 09:17 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

老大,俺还是看不懂?流程图更是一窍不通?你能解释一下吗?麻烦了?

[此贴子已经被gouweicao78于2007-5-12 1:58:18编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-11-25 10:07 | 显示全部楼层
QUOTE:

再谈二分法

不要急,一开始我看了许久也没看懂的,后来查了一些“二分法”(也称“半分法”)方面的东西就简单了。

比如数据=1、2、3……、100——升序排列,则用二分法在数据里查找X=33,第一次先对半分数据(这就要找那个Posi点了)。

1、流程图往左右分

N=100个数据,姑且按左向右排列,最左边为第1个数据,最右边为第N=100个数据,Posi=INT((1+100)/2)==>得到50,即第一次找到的Posi点就是第50个(Posi位置)数据。——33(查找值X)与第50个(位置)数据50()相比,33<50,所以,流程图往左边走,也就是说查找的X在数据中的位置在第1~50数据之间,绝不可能在第50~100之间

于是新的循环开始:最左边的位置=1,最右边变成了Posi-1也就是上次找到的第50个(位置)=49

查对半新的Posi位置即=INT((1+49)/2)=25——第25个位置的数据,即25,与X=33相比,25<33,则流程图往右走,也就是说查找的X在数据第25~50个数据之间,绝不可能在第1~25个之间

……如此往复循环,(上面总共进行了2次比较运算)查找的数据都是对某个位置有目的地比较,而不是像“遍历法”(比如Countif(A1:A100,1)计算A1:A100中数值为1的单元格个数,它就需要从第1到第100个数据全部经历一遍,才能得到结果,用了100次比较运算)或者Match、Vlookup精确查找所用的类似“遍历法”一个个从头到尾比较过来,比到第1~33个终于找到X的时候才停下来(比较运算了33次)。

2、流程图往下走

比如查找值X=50,第一步一下子就找到了50,在比较一下posi<right——即看这个位置(第50个)是否在最右边一个(第100个)的左边(除了找到第100个的时候,当然都是Yes了),如果是(即找到的位置50<100),则再比较一下第Posi+1个位置也就是第51个数是否等于查找值X,如果还满足(即第51个数据的值也是50),则Posi变成了51,再比较第52个数是否等于查找值X……一直找到最后一个=X的位置。(比如说第A50:A55都是数值50,那么Lookup(50,A1:A100,row(1:100))或者Match(50,A1:A100)得到的都是55。

这也就是为什么Lookup、Match、Vlookup采用二分法时效率极高的原因。

 

为何常见用Lookup(1,0/……、Lookup( 9E+307,……、Lookup("座座",……等查找公式:看流程图

当第一参数X与第二参数数组的某一个二分点(比如100个数据的第50个、第25个、第13个等)的值相等——A(posi)=X,流程图往下走,否则流程图往左右分。

所以,用一个永远大于第二参数的值X作为第一参数时,二分法将一直往流程图的左右分,直到找到最后一个满足条件的值。当第一参数=Max(第二参数)时,如果第二参数数组中的MAX值恰好处于二分点位置,且其左右不是Max值,则将返回找到的第一个符合的二分点位置的值。

[此贴子已经被作者于2007-4-12 13:28:46编辑过]

TA的精华主题

TA的得分主题

发表于 2006-12-4 15:56 | 显示全部楼层

以下三个公式结果一样,对偶用处很大,希望版主们给予解释。

具体链接:http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=618920&id=204464&page=1&skin=0&Star=1

=LEFT(A2,LOOKUP(1,0/ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2)))))

=LEFT(A2,MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2))))))

=LEFT(A2,LEN(A2)+1-MATCH(,0/ISNUMBER(--LEFT(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))),))

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-12-4 17:22 | 显示全部楼层

解答CANNA:这些公式都是=Left(A2,A2中最后一个数字的位置)

=LEFT(A2,LOOKUP(1,0/ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2)))))

1、Row(INDIRECT("1:"&LEN(A2)))——比如A2字符个数为10,得到Row(1:10),即1、2、……10的等差序列。——以下简称为Row

2、Mid(A2,Row,1)——对A1从第1个字符开始逐一取得每1个字符,用--将MID得到的文本转化为数值型,非数字则返回#VALUE!错误,在用ISNUMBER判断得到True、False的数组。

3、0/{True,False……}得到{0,#DIV/0!……}组成的数组,因为要找到的数字最大为0,所以Lookup(1,……,

Lookup(1,0/(条件),数组)的解释见本帖前几楼有关Lookup的阐述。并由此定位最有一个True所在的位置。

=LEFT(A2,LOOKUP(10,--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2)))))

因为找到的数字最大是9,所以Lookup(10,……

——以上2个公式均为普通公式,利用Lookup第2、3参数对数组的支持。

=LEFT(A2,MAX(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2))))))

利用Max(IF(条件,返回数组))得到数字所对应位置的最大值。

=LEFT(A2,LEN(A2)+1-MATCH(,0/ISNUMBER(--LEFT(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2)))))),))

Left(Right(A2,ROW(INDIRECT("1:"&LEN(A2)))))——用Right由右边往左逐个截取尾巴段字符,比如"ABC123DE"——Right(A2,Row)得到的是,"E","DE","3DE"……再用Left(Right)得到"E","D","3"……

接下来就是Match(0,0/(条件),0)——头尾两个粗体的0省略,其中第3个0表示精确查找,第1个0表示要查找的值。也是利用Match函数忽略错误值的特性在0/(条件)得到的{0,#DIV/0!,……}数组中查找第一个0出现的位置(注意,这是从右往左开始算的,也就是从左往右最后一个数字的位置)

比如:"ABC123DE"共有8个字符,最后一个数字是3,在第6位。从右往左数是第3位,Len(A2)=8,即8+1-3=6

=LEFT(A2,MATCH(1,0/ISNUMBER(--(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))))

这个公式的核心在于Match(1,0/(条件))——注意,没有第3参数,相当于Match(1,0/(条件),1)大致匹配查找,其特性与Lookup的查找原理一致,具体请看[函数用法讨论系列10] LOOKUP的查找策略!之流程图。

因Match本身查找的就是位置,而不是数据本身,所以可以少用一个Row

[此贴子已经被作者于2006-12-4 17:23:52编辑过]

TA的精华主题

TA的得分主题

发表于 2006-12-6 14:05 | 显示全部楼层

楼主你好,我想请教一个问题,关于字符串裁取,如我现在一个有字符串,

 "44"*180g莱克布绿色+4mmK329黑+28GT/C黑"红色部份为字符串,如何裁取从开头到"+"之间的字符串,用函数如何实现,如果不用函数,用其它方法实现也可以,

谢谢哈

QUOTE:

补充一下,"44"*180g莱克布绿色+4mmK329黑+28GT/C黑"红色部份有三个"+"所以这个字符串要裁取三次,第二个问题.如何裁取两个加号之间的内容,谢谢

[此贴子已经被czzqb于2006-12-16 13:07:21编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-12-6 15:08 | 显示全部楼层

to beefo:欢迎提问,但请注意本帖1楼的要求!

1、本帖是[解惑],不是解题,这种问题完全可以在外面发帖求助。

2、补充问题,可以编辑自己曾经发过的帖子,方法是点击该楼层右下角的“编辑”按钮。不要占用楼层,以便其他朋友阅读。

3、针对补充的问题看,你是要得到44"*180g莱克布绿色、4mmK329黑、28GT/C黑这样3个字符串,用数据〉分列〉分隔符号选其他,输入+号〉点击完成即可。不必用函数就轻松实现

用函数截取的方法:假设字符在A1,供参考,方法还有其他的:

B1=LEFT(A1,FIND("+",A1)-1)截取第一段

C1=MID(A1,FIND("+",A1)+1,FIND("+",A1,FIND("+",A1)+1)-FIND("+",A1)-1)截取第2、3个+号之间

D1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,B1,),C1,),"+",)将A1中的+号和+号之间的字符串替换为空,即剩下的最后一段。

[此贴子已经被作者于2006-12-6 15:10:13编辑过]

TA的精华主题

TA的得分主题

发表于 2006-12-10 02:10 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

IF(ISERROR(FIND("-",A3,FIND("-",A3,FIND("-",A3)+1)+1)),IF(ISERROR(FIND("-",A3,FIND("-",A3)+1)),LEFT(A3,FIND("-",A3)-1),LEFT(A3,FIND("-",A3,FIND("-",A3)+1)-1)),LEFT(A3,FIND("-",A3,FIND("-",A3,FIND("-",A3)+1)+1)-1))

请问G先生这个公式的意思?

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-12-12 00:57 | 显示全部楼层
QUOTE:
以下是引用jingdian在2006-12-10 2:10:15的发言:

IF(ISERROR(FIND("-",A3,FIND("-",A3,FIND("-",A3)+1)+1)),IF(ISERROR(FIND("-",A3,FIND("-",A3)+1)),LEFT(A3,FIND("-",A3)-1),LEFT(A3,FIND("-",A3,FIND("-",A3)+1)-1)),LEFT(A3,FIND("-",A3,FIND("-",A3,FIND("-",A3)+1)+1)-1))

请问G先生这个公式的意思?

欢迎提问,请先阅读1楼规则。公式离开特定的附件要解释或理解比较枯燥,如果是本论坛的公式,可以给链接。

关于本公式:用“庖丁解牛”分段解读,只有繁琐没有难度。

FIND(要找的字符,所在的字符串,查找的起始位置),由此理解:FIND("-",A3)得到A3单元格字符串中,第一个负号-的位置。

FIND("-",A3,FIND("-",A3)+1))——在A3单元格字符串中,从第一个负号-的位置后面1个字符开始查找"-"出现的位置(也就是第二个负号的位置。

FIND("-",A3,FIND("-",A3,FIND("-",A3)+1))+1)——从第二个负号的位置后面1个字符开始查找"-"出现的位置(也就是第三个)……

ISERROR用于判断是否错误值,因为如果Find没有找到,返回#VALUE!错误。

LEFT(A3,FIND("-",A3)-1)——得到A3单元格第一个负号前面的字符……依此类推。

TA的精华主题

TA的得分主题

发表于 2006-12-17 22:35 | 显示全部楼层

E1到E18里是文本 下面这个公式向下拉就可以得到其中不重复的

这是个啥道理 望高人解释解释

{=INDEX($E$1:$E$18,MATCH(,COUNTIF($A$8:A8,$E$1:$E$18),))}

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

本版积分规则

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

GMT+8, 2024-3-29 13:41 , Processed in 0.043676 second(s), 6 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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