开始案例之前先补充几个简单的函数:
1)、求最大,最小
2)、求余数
3)、文本字符串指定的引用
INDIRECT(ref_text,a1),返回由文本字符串指定的引用,INDIRECT(单元格引用,引用样式),a1如果省略,表示A1的引用样式,如果为0表示R1C1引用样式。(单元格的两种引用方式,R1C1引用方式行和列全用数字表示,比如平常我们说B2,如果用R1C1表示,就是R2C2,即第2行第2列)
4)、将数值转换为按指定数字格式表示的文本。
TEXT(value,format_text) ,
大家如果一时搞不清或者记不住这些用法,可以在实例中边理解公式,边学函数的用法。一定要多看,多练习。
这是前几天一个朋友问的一个问题,她想提取最后一个数字,但是这个数字所在的列不确定,比如下图中,想提取这个区域中最后一个数值。
下面给出三种办法:
思路一:
若是能找出这个数字所在行数,那么这一行只有这个数字,对这一行求和即可得到这个数字。
于是用=IF(A1:E9<>'',ROW(1:9),0),所有非空单元格都显示行号,最后一个数字对应行号最大,如上图,最后一个数字在第九行,所以用=max(IF(A1:E9<>'',ROW(1:9),0))就得到了该数字所在的行数9,然后我们对该行数求和就可以。
对一行求和,我们可以用sum(行号:行号),如上图,可以用sum(9:9),但是用公式求出9后想引用成行数要这样表达=indirect(9&':'&9),然后用sum去求和。
整合公式就是下面:=SUM(INDIRECT(MAX(IF(A1:E9<>'',ROW(1:9),0))&':'&MAX(IF(A1:E9<>'',ROW(1:9),0)))),三键结束。
思路二:
若是能找出这个数字所对应的行和列,用TEXT将其转化为C1R1个格式,用indirect直接引用即可。
返回行数列数的办法:=IF(A1:E9<>0,ROW(A1:E9)*10+COLUMN(A1:E9),先将非空单元格所在的行数*10,然后加上对应的列数,当然这里是乘以10,是根据上面图中数据的实际情况决定的,因为图中数据行数列数全都是个位数,如果行数列数超过10,那要想表达行数列数,行数两位,列数两位,所以行数至少要乘以10000。
用=MAX(IF(A1:E9<>0,ROW(A1:E9)*10+COLUMN(A1:E9),)),这样就可以得出最大的数字所在的行数列数,比如上图中结果是93,即第九行第三列。
用=text(93,'r0c0')即得到了'r9c3',最后用indirect('r9c3',0)即得到300。
整合公式为:=INDIRECT(TEXT(MAX(IF(A1:E9<>0,ROW(A1:E9)*10+COLUMN(A1:E9),)),'r0c0'),)
怎么样,这函数用的牛吧!
思路三:
把值所在的行数扩大10^6倍,把列数扩到10^4倍,然后加上原始数值,这样得到值所在的行数加列数就是9030300,然后除以10^4,取余数即得到了原始数字。当然要扩大多少倍也是要看数据本身,你写大点无所谓,但除数的位数总得大于想要得到的值的位数吧!比如本例中数据全是三位数,想要取余数得到该值,至少要列数至少要扩大一千倍吧。
公式:
=MOD(MAX(IF(A2:E9<>0,ROW(A2:E9)*10^6+COLUMN(A2:E9)*10^4+A2:E9,)),10^4)
今天就分享到这里,大家慢慢体会!
本节就分享到这里,需要Office视频(收费)的请联系我微信:527240310,具体信息可至公众号菜单Office视频中了解,针对推送内容有不懂的也可以单独录制视频。
长期关注此公众号的人技术都提高了!
完
感谢每一位支持我的读者!!!
联系客服