小写转大写函数:
=IF(L7=0,"",IF(ABS(L7)<0.995,"",TEXT(INT(ROUND(ABS(L7),2)),"[DBNum2]")&"元")&IF(RIGHT(TEXT(L7,".00"),2)*1=0,IF(ABS(L7)<0.005,"","整"),TEXT(IF(ABS(L7)<0.095,"",LEFT(RIGHT(TEXT(L7, ".00"),2))),"[dbnum2]")&IF(LEFT(RIGHT(TEXT(L7,".00"),2))*1=0,"","角")&IF(RIGHT(TEXT(L7, ".00"))*1=0,"整",TEXT(RIGHT(TEXT(L7,".00")),"[dbnum2]")&"分")))
=VLOOKUP(B1,Q:R,2,FALSE)
括号里面:1、判断值;2、选定区域;3、判断选定区域的行数;4、取值
Round 函数
返回按指定位数进行四舍五入的数值。
举例:
=ROUND(2.15, 1) 将 2.15 四舍五入到一个小数位 (2.2)
=ROUND(2.149, 1) 将 2.149 四舍五入到一个小数位 (2.1)
=ROUND(-1.475, 2) 将 -1.475 四舍五入到两小数位 (-1.48)
=ROUND(21.5, -1) 将 21.5 四舍五入到小数点左侧一位 (20)
实例:
=ROUND(IF(K5-2000-L5<=0,0,IF(K5-2000-L5<=500,(K5-2000-L5)*0.05,IF(K5-2000-L5<=2000,(K5-2000-L5)*0.1-25,IF(K5-2000-L5<=5000,(K5-2000-L5)*0.15-125,IF(K5-2000-L5<=20000,(K5-2000-L5)*0.2-375,IF(K5-2000-L5<=40000,(K5-2000-L5)*0.25-1375,0)))))),1)
VALUE函数
该函数可以将代表数字的文本字符串转换成数字
right函数
right函数的功能是从字符串右端取指定个数字符。语法Right ( string, n ) 。参数string:string类型,指定要提取子串的字符串n:long类型,指定子串长度返回值String。函数执行成功时返回string字符串右边n个字符,发生错误时返回空字符串("")。如果任何参数的值为NULL,Right()函数返回NULL。如果n的值大于string字符串的长度,那么Right()函数返回整个string字符串,但并不增加其它字符。
CONCATENATE函数
功能:即将几个文本字符串合并为一个文本字符串,作用相当于“&”
举例:
N2=381527
=CONCATENATE("02",VALUE(RIGHT(N2,4))+41)
取N2的后4位,即1527,加41,得1568,取1568值与02合并,得021568
=IF(ISERROR(MATCH(D4,$B$2:$B$13,0)),0,1)
如果D4等于B2到B13列中的任意单元格,则等于1,否则等于0
=LEFT(RIGHT(" ¥"&$A2*100,13-COLUMN()))
功能:金额填充
注意:1、如果从B列开始填充,则为13减,若从C列开始填充则是14减
2、COLUMN()括号中空值,表示从该函数所在的列值计算,在A列为1,在B列为2
3、RIGHT(A,B)表示在A单元格中,从右往左取B个数值
4、LEFT()表示从左往右取值
=SUMIF($A8:$A254, $A6, F8:F254)column函数和vlookup函数
功能:区域求和
括号中表述为,条件判断的区域,条件单元格,求和区域
=IF(M1<=10000,5.5,IF(M1<=100000,10.5,IF(M1<=500000,15.5,IF(M1<=1000000,20.5,IF(1000000<M1,M1*0.00002+0.5)))))
功能:IF函数嵌套
注意:IF函数是逐层判断,所以当第一层判断M1<=10000后,第二层判断为M1<=100000,千万别输入10000<M1<=100000
=RANDBETWEEN(2,200)
功能:在2——200间取随机整数
=MOD(C1,1)
功能:除整取余,无余则等于零
举例:C1=107.99,运算结果为0.99;C1=107,运算结果为0
判断身份证号码位数
=IF(LEN(G2)=15,"15位身份证",IF(LEN(G2)=18,"18位身份证","身份证位数不正确"))
判断身份证性别
=IF(LEN(F3)=18,IF(MOD(MID(RIGHT(F3,2),1,1),2)=0,"女","男"),IF(MOD(MID(RIGHT(F3,1),1,1),2)=0, "女","男"))
提取身份证出生日期
=IF(LEN(G2)=18,RIGHT(LEFT(G2,14),8),RIGHT(LEFT(G2,12),6))
或者=IF(LEN(H2)=15,TEXT(MID(H2,7,6),"1900-00-00"),TEXT(MID(H2,7,8),"00-00-00"))
ISERROR
功能:去掉用公式后出现的#N/A之类的错误值
举例:=IF(ISERROR(C2-D2),””,C2-D2)
如果C2-D2是错误值,则显示空白,否则显示C2-D2
知道月份,显示月初和月末
A1=2011-6-2
月初=DATE(YEAR(A1),MONTH(A1),1)
月末=DATE(YEAR(A1),MONTH(A1)+1,)
9月新个税的公式
=ROUND(MAX((应税工资-3500)*{3,10,20,25,30,35,45}/100-{0,21,111,201,551,1101,2701}*5,0),2)
INDIRECT
功能:对工作簿的引用
注意:1、当工作表名称直接是数字的,在工作表名称两边必须添加上一对单引号。
在“2”工作表,计算“1”工作表B2:B11的成绩总和。公式为:=SUM(INDIRECT("'1'!B2:B11"))。解释:indirect(“’工作表名’!单元格区域”)
总结:如果工作表名为汉字,工作表名前后可以加上一对单引号,也可以不加。但是数字和一些特殊字符时,必须加单引号,否则不能得到正确结果。
总结:如果工作表名为汉字,工作表名前后可以加上一对单引号,也可以不加。但是数字和一些特殊字符时,必须加单引号,否则不能得到正确结果。
2、INDIRECT函数对工作簿引用的书写方式和细节正确写法
=INDIRECT("[工作簿名.xls]工作表表名!单元格地址")
=INDIRECT("[工作簿名.xls]工作表表名!单元格地址")
INDIRECT函数,如果是对另一个工作簿的引用(外部引用),则那个工作簿必须被打开。如果源工作簿没有打开,函数 INDIRECT 返回错误值 #REF!。
举例:=VLOOKUP(G4,INDIRECT("'[银行贷款.xls]"&MONTH($G$3)&"'!$D:$K"),4,FALSE)
'[银行贷款.xls]"&MONTH($G$3)&"'!$D:$K是得到一个工作簿的名字,其中的工作表编码是随着G3的月份变动的
=LEFT(A4,LENB(A4)-LEN(A4))
功能:分离汉字与数字组合中的汉字,汉字在左
举例:A4=临时工工资701457890,运算结果为临时工工资
=RIGHT(A4,LENB(A4)-LEN(A4))
功能:分离汉字与数字组合中的汉字,汉字在右
举例:A4=701457890临时工工资,运算结果为临时工工资
=MID(A4,1,2*LEN(A4)-LENB(A4))
功能:分离汉字与数字组合中的汉字,数字在左
举例:A4=701457890临时工工资,运算结果为701457890
=RIGHT(A4,2*LEN(A4)-LENB(A4))
功能:分离汉字与数字组合中的汉字,数字在右
举例:A4=临时工工资701457890,运算结果为701457890
自定义格式:"RMB"#,##0.00
功能:如果单元格中为100000,自定义格式后为RMB100,000.00
三个取随机文本的公式
1、=index({"是", "否","其他"},rand()*3+1)
2、=INDIRECT("A"&INT(RAND()*3+1))
注:A是表示引用的单元格,RAND()*3是表示需要引用几个文本值
3、=CHOOSE(INT(RAND()*3)+1,"是","否","其他")
下一个单元格表示
=OFFSET(A1,1,0)
功能:表示为A2
=MATCH(A1,A1:A17,0)
功能:查A1在A1到A17区域中的位置,返回值为1
=INDEX(A1:B17,3,2)
功能:查A1到B17区域中第三行第二列的值
举例:=INDEX($G$1:$G$45,MATCH(F1,$G$1:$G$45,0)+1)
查值等于F1的下一个单元格的值
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论