Excel一些函数的简单使用

发表于 更新于 分类于 ， 阅读次数： Waline： 阅读次数： 本文字数： 2.6k 阅读时长 ≈ 5 分钟
Excel一些函数的简单使用记录

财务常用

财务中转换数字为大写元角分

1
=SUBSTITUTE(SUBSTITUTE(IF(A3>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A3))),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(FIXED(A3),2),"[dbnum2]0角0分;;"&IF(ABS(A3)>1%,"整",)),"零角",IF(ABS(A3)<1,,"零")),"零分","整")

将数字填到数位中

1
2
3
=LEFT(RIGHT(TEXT($A3*100," ￥0;;"),COLUMNS(E:$K)))
或者
=LEFT(RIGHT(" ￥"&$A3*100,COLUMNS(E:$K)))

最终效果如：
数字到指定单元格

转换大小写

将时间转换为大写

1
="现在是:"&TEXT(NOW(),"[DBNum2][$-804]"&"h时m分s秒")

时间转换为大写

提取内容

混排提取汉字

1
=IFERROR(MID(MID(A2,19,520),MATCH(2,LENB(MID(MID(A2,19,520),ROW(INDIRECT("1:"&LEN(MID(A2,19,520)))),1)),0),LENB(MID(A2,19,520))-LEN(MID(A2,19,520))),"")

提取地址

数组公式一些应用

计算非空两列单元格两两相除后和的平均数

1
{=SUM(INDIRECT("C3:C"&MAX((B3:B20<>"")*(ROW(B3:B20))))/INDIRECT("B3:B"&COUNT(B3:B20,"<>")+2))/COUNT(B3:B20,"<>")}

或者

1
{=SUMPRODUCT(INDIRECT("C3:C"&MAX((B3:B20<>"")*(ROW(B3:B20))))/INDIRECT("B3:B"&COUNT(B3:B20,"<>")+2))/COUNT(B3:B20,"<>")}

返回非空行号

1
{=MAX((B3:B20<>"")*(ROW(B3:B20)))}

或

1
{=MAX(NOT(ISBLANK(B3:B20))*ROW(3:20))”}

返回非空坐标

1
{=ADDRESS(MAX((B3:B20<>"")*(ROW(B3:B20))),COLUMN(B:B))}

返回列中最后非空单元格值

1
{=INDIRECT(ADDRESS(MAX((B3:B20<>"")*(ROW(B3:B20))),COLUMN(B3:B20)))}

数组公式一些应用

一些应用

简易日历

1、先做好如图大概框架
启用excel开发工具【文件==》（更多）选项==》自定义功能区==》右边选中开发工具】，打开控件,设置控件按钮数字调节阀
设置如下：

1
2
3
4
5
当前：1 //可1-12任意整数
最小：1
最大：12
步长：1
控制：`$AA$1` //控制月份

启用开发工具
2、在第三行和第七行输入以下公式，自动变换日期范围
1
2
3
4
-- 上半月
=TEXTJOIN(,TRUE,$X$1,"年",$AA$1,"月","1日——",$X$1,"年",$AA$1,"月15日")
-- 下半月
TEXTJOIN(,TRUE,$X$1,"年",$AA$1,"月","16日——",$X$1,"年",$AA$1,"月",DAY(DATE($X$1,$AA$1+1,)),"日")

3、在上半月日期第一天单元格输入公式
1
2
=DATE($X$1,$AA$1,COLUMN(A1)) 
-- # "$X$1"取年份，"$AA$1"取月份,“COLUMN(A1)”取列号为日

移到单元格右下角，出现黑十字+,向右填充15格，即到15日。
4、在上半月第一天的星期所在单元格输入公式
1
2
=TEXT(C5,"AAA") 
-- # "AAA"返回的是星期的简写，全称则为"AAAA",不分大小写

移到单元格右下角，出现黑十字+,向右填充15格，即到15日。
5、在下半月表第一个日期所在单元格里面，输入以下公式
1
2
=IF(MONTH(DATE($X$1,$AA$1,COLUMN(P1)))=$AA$1,DATE($X$1,$AA$1,COLUMN(P1)),"")
-- # 判断“MONTH(DATE($X$1,$AA$1,COLUMN(P1)))=$AA$1”，是避免后面多的单元格里面日期不在本月之内。

移到单元格右下角，出现黑十字+,向右扩展填充16格，即到31日。
6、在下半月第一天的星期所在单元格输入公式
1
2
=TEXT(C9,"AAA") 
-- # “C9”是取日期，"AAA"返回的是星期的简写，全称则为"AAAA",不分大小写

移到单元格右下角，出现黑十字+,向右填充16格，即到31日。
用顶栏月份右边调节阀控制月份，即可自动更新日期和星期。

日历简单应用

后续会陆续补充不断完善……

相关文章