excel技巧与公式精选80例.docx

excel技巧与公式精选80例.docx

《excel技巧与公式精选80例.docx》由会员分享，可在线阅读，更多相关《excel技巧与公式精选80例.docx（33页珍藏版）》请在冰豆网上搜索。

excel技巧与公式精选80例

目录

一、函数

1、SUBSTITUTE函数···············································3

2、如何不使显示或打印出来的表格中包含有0值·······················3

3、将阿拉伯数字转换为大写金额·····································4

4、多条件数据统计·················································4

5、相同格式多工作表求和···········································5

6、如何判断单元格里是否包含指定文本？

·····························5

7、求某一区域内不重复的数据个数···································5

8、分割文本·····················································6

9、简单判断最后一位是数字或字母···································6

10、多列数据合为一列··············································7

11、根据身份证号提取出生日期······································7

12、多工作表中相同单元格反映在一张工作表中························8

13、将班次变为文本················································8

14、隔行求和问题··················································8

15、在单元格中批量插入固定字符····································8

16、用函数将输入的月份转换为本月所包含的天数····················8

17、在EXCEL中如何统计字数·······································9

18、如何定义有效数字··············································9

19、根据录入搜索对应的内容········································9

20、用SUMIF函数进行条件求和，不限于一个条件·····················10

21、在excel中将已有的数值前加零变成六位···························10

22、求每个月有几个星期日、一、二、三…六··························10

23、绝对值函数ABS················································10

24、向下舍入取整函数INT···········································11

25、分类汇总函数SUBTOTAL·······································11

26、日期计算函数DATEIF···········································11

27、星期函数WEEKDAY············································12

28、计算成绩名次：

CHOOSE函数····································12

29、WORKDAY函数（Excel2007适用）································13

30、HLOOKUP函数与VLOOKUP函数································13

31、超链接函数HYPERLINK·········································13

32、合并字符串函数CONCATENATE··································14

33、将数字转换为美元货币的函数DOLLAR····························14

34、将数字转换为人民币的函数RMB··································14

35、自动转换大小写函数PROPER······································15

36、文本替换函数REPLACE·········································15

37、统计空白单元格的个数函数COUNTBLANK························15

38、利用函数查询费率···············································16

二、技巧

1、控制每列数据的长度并避免重复录入································17

2、把B列与A列同一行数据的不同之处标识出来·······················17

3、快速在一个工作薄中建立多个工作表································17

4、当前日期与时间的快速输入········································18

5、对多张工作表进行操作············································18

6、数字批量加注单位················································18

7、文字批量加注单位················································18

8、保护我们的excel·················································19

9、在EXECL中建立下拉列表按钮····································20

10、快速填充数据···················································20

11、在已有的单元格中批量加入一段固定字符···························20

12、在同一对应单元格内输入相同内容·································21

13、EXCEL中行列互换··············································21

14、输入平方、立方·················································21

15、用Excel为多页表格·············································21

16、不打印单元格中的颜色与底纹·····································21

17、在EXCEL中如何统计字数·······································22

18．运用选择性粘贴批量运算········································22

19、工作表的标签的字体和大小可以更改吗····························22

20、绘制有三条斜线的表头··········································22

21、自动调整小数点················································22

22、快速打开所需工作表：

···········································22

23、让数值完全或部分替换公式······································23

24、在Excel中实现滚屏时固定显示表格头·····························23

25、去除“最近使用过的工作簿”列表································23

26、Excel工作表大纲的建立·········································23

27、插入“图示”····················································24

28、“照相机”功能的妙用···········································24

29、建立“常用文档”新菜单········································25

30、为工作表添加的背景············································25

31、快速填入相同内容··············································26

32、快速复制向上单元格············································26

33、快速输入系统日期··············································26

34、轻松实现多行文本输入··········································26

35、使用Excel中的“宏”··········································26

36、分区域锁定EXCEL工作表·······································27

37、另类录入技巧··················································27

38、宏代码························································27

39、让数据按需排序················································28

40、双击格式刷的妙用··············································28

41、将Excel单元格区域转换为图片···································28

42、在工作表之间使用超级连接······································29

一、函数

1、SUBSTITUTE函数

例：

（1）、求A1单元格中字符"i"出现的次数：

=LEN（A1）-LEN（SUBSTITUTE（A1,"i",""））如图1：

（2）、求字符串长度

=LEN（SUBSTITUTE（B3,"I",""））

如图2：

（3）、用“S”替换字符串中的“i”

=SUBSTITUTE（A1,"i","S"）

如图3：

图1

图2图3

2、如何不使显示或打印出来的表格中包含有0值

　　通常情况下，我们不希望显示或打印出来的表格中包含有0值，而是将其内容置为空。

如何让0值不显示？

=IF（B2*B3=0,"",B2*B3）如图4：

意思为：

若B2*B3=0,则单元格为空，

否则显示B2*B3。

当然，B2*B3也可以

换成其他公式，要以具体问题而定。

图4

3、将阿拉伯数字转换为大写金额

假定需要在A2输入阿拉佰数字,B2转换成中文大写金额（含元角分）,在B2单元格输入如下公式:

如图5：

图5

=IF（（INT（A5*10）-INT（A5）*10）=0,TEXT（INT（A5）,"[DBNum2]G/通用格式"）&"元"&IF（（INT（A5*100）-INT（（A5）*10）*10）=0,"整","零"&TEXT（INT（A5*100）-INT（A5*10）*10,"[DBNum2]G/通用格式"）&"分"）,TEXT（INT（A5）,"[DBNum2]G/通用格式"）&"元"&IF（（INT（A5*100）-INT（（A5）*10）*10）=0,TEXT（（INT（A5*10）-INT（A5）*10）,"[DBNum2]G/通用格式"）&"角整",TEXT（（INT（A5*10）-INT（A5）*10）,"[DBNum2]G/通用格式"）&"角"&TEXT（INT（A5*100）-INT（A5*10）*10,"[DBNum2]G/通用格式"）&"分"））

4、多条件数据统计

假定第一行为表头，A列是“路段”，B列是“入口站”，C列是“车型”，现在要统计“路段”为“京开高速公路”，“入口站”为“西红门站”，“车型”为“A”的条数。

在需要显示结果的单元格内输入如下公式：

=SUM（IF（（A2:

A1000="京开高速公路"）*（B2:

B1000="西红门站"）*（C2:

C1000="A"）,1,0））

输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。

如图6：

图6

5、相同格式多工作表求和

在SHEET101的A1单元格输入公式：

=SUM（'SHEET1:

SHEET100'!

A1）

然后按回车。

如图7：

图7

6、如何判断单元格里是否包含指定文本？

假定对A1单元格进行判断有无"指定文本",以下任一公式均可:

=IF（COUNTIF（A1,"*"&"指定文本"&"*"）=1,"有","无"）

=IF（ISERROR（FIND（"指定文本",A1,1））,"无","有"）

如图8：

图8

7、求某一区域内不重复的数据个数

例：

求A1:

A6范围内不重复数据的个数，某个数重复多次出现只算一个。

有两种计算方法：

如图9：

一是利用数组公式：

=SUM（1/COUNTIF（A1:

A6,A1:

A6））

输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。

二是利用乘积求和函数：

=SUMPRODUCT（1/COUNTIF（A1:

A6,A1:

A6））

注：

公式中的A1:

A6应根据实际情况而定。

图9

8、分割文本

例：

有一列数据，全部是邮箱的，现在想将@前面的账号与@后面的域名分割开，分为两列。

“@”前部分：

=LEFT（A1,FIND（"@",A1）-1）

“@”后部分：

=RIGHT（A1,LEN（A1）-FIND（"@",A1））

如图10：

图10

9、简单判断最后一位是数字或字母

假设需判断A1单元格内最后一位是数字还是字母单元格

将公式

=IF（ISNUMBER（--RIGHT（A1，1）），"数字"，"字母"）或

=IF（ISERR（RIGHT（A1）*1），"字母"，"数字"）录到其它任意单元格内即可判断

10、多列数据合为一列

例：

将A、B、C三列数据合为一列，要求将B1置于A2处，将C1置于A3处

将公式

图11

=INDIRECT（"r"&INT（（ROW（）-1）/3）&"c"&MOD（ROW（）-1,3）+1,0）

如图11：

11、根据身份证号提取出生日期

在单元格内录入以下公式（15位、18位均适应）：

=IF（LEN（A1）=18，DATE（MID（A1，7，4），MID（A1，11，2），MID（A1，13，2）），IF（LEN（A1）=15，DATE（MID（A1，7，2），MID（A1，9，2），MID（A1，11，2）），"错误身份证号"））

将单元格格式设为日期格式。

如图12

图12

12、多工作表中相同单元格反映在一张工作表中

=INDIRECT（"Sheet"&ROW（）-2&"!

$B$4"）

如图13：

注：

工作簿中的工作表名字应按一定顺序排列

indirect是把文本变为单元格引用的函数row（）是取当前行号。

例如在a1输入该公式，则row（）=1，公式里的值变为indirect（"sheet2!

a1"）,跟＝sheet2!

a1同效，在a2输入该公式，则row（）=2，公式里的值变为indirect（"sheet3!

a1"）》

图13

13、将班次变为文本

=IF（B:

B="A","大夜",""）&IF（B:

B="B","早班",""）&IF（B:

B="C","中班",""）&IF（B:

B="D","小夜",""）

14、隔行求和问题

假设a1至t1为数据（共有20列），在任意单元格中输入公式：

=SUM（IF（MOD（TRANSPOSE（ROW（1:

20））,3）=0,（a1:

t1））/按ctrl＋shift＋enter结束即可求出每隔三行之和。

15、在单元格中批量插入固定字符

现在新的身份证号码，将旧身份证号码的年份由2位表示改为4位表示，如果我们要将年份的前两位（19）插入旧身份证号码中，可以这样做（假定旧身份号码保存在A列中）：

在B1、C1、D1单元格中分别输入公式：

=LEFT（A1,6）、=RIGHT（A1,9）、=B1&"19"&C1，将上述公式复制到B、C、D列的其它单元格中，即将19插入旧身份证号码中，并显示到D列中。

16、用函数将输入的月份转换为本月所包含的天数

假设A1单元格为月份：

＝TEXT（（DATE（YEAR（NOW（））,A1+1,1）-1）,"d"）

或：

=DAY（DATE（YEAR（NOW（））,A1+1,0））

17、在EXCEL中如何统计字数

假设统计A1至A6间数据

公式：

=SUM（LEN（A1:

A6））

录完后按Ctrl+Shift+Enter

键,让它自动加上数组公式符号"{}"如图14：

图14

18、如何定义有效数字

例：

取两位有效数是从第一个不是零的数字起，取两位。

0.0023666取两位有效数是0.0023。

0.2366取两位有效数是0.23。

用函数可如下:

=FLOOR（A1,SIGN（A1）*10^（INT（LOG（ABS（A1）））-1））,+/-小数有效,0无效.

用-1可调节取数位。

19、根据录入搜索对应的内容

例如：

A1：

A3是编号，B1：

B3是姓名，C1：

C3是性别，当在A4单元格输入A1：

A3范围内的任意一个编号时，B4出现对应的姓名，C4出现对应的性别。

在B4单元格内录入公式

图15

“=IF（A4=0,"",VLOOKUP（A4,A1:

C3,2,FALSE））”；

C21单元格公式“=IF（A4=0,"",VLOOKUP（A4,A1:

C3,3,FALSE））”

20、用SUMIF函数进行条件求和，不限于一个条件

假设A列为名称、B列为数量：

=SUMIF（A:

A,"京开",B:

B）+SUMIF（A:

A,"京石",B:

B）+SUMIF（A:

A,"六环",B:

B）

21、在excel中将已有的数值前加零变成六位

＝RIGHT（"00000"&A1,6）

22、求每个月有几个星期日、一、二、三…六

将公式输入到制定单元格，向右复制、向下复制。

录完后按ctrl+shift+Enter。

加入数组。

如图16

图16

=SUM（IF（WEEKDAY（DATE（$A2,$B2,ROW（INDIRECT（"$A$1:

$A$"&DAY（DATE（$A2,$B2+1,1）-1）））））=COLUMN（）-2,1））}，

23、绝对值函数ABS

例如在A1、B1单元格中分别输入120、180，那么如果要求A1与B1之间的差的绝对值，可以在C1单元格中输入以下公式：

=ABS（A1-B1）。

如图17

图17

24、向下舍入取整函数INT

例如要计算显示器和硬盘的购买数量，可以在D3单元格中输入以下公式：

=INT（C3/B3）。

如图18

图18

25、分类汇总函数SUBTOTAL

分类汇总函数SUBTOTAL拥有十余项分类功能最常用到的是“4”求数据列中最大值，“5”求数据列中最小值，“9”求数据列之和

1、9月单日最高收费额：

在单元格E4中输入=SUBTOTAL（4,B3:

B32）

2、9月单日最低收费额：

在单元格E5中输入=SUBTOTAL（5,B3:

B32）

3、9月总收费额：

在单元格E6中输入=SUBTOTAL（9,B3:

B32）

如图19

图19

26、日期计算函数DATEIF

假设有两个已知日期：

开始日期和截止日期，那么可以利用DATEIF函数来计算它们之间相差的年数、月数或者天数。

参数可以是：

"y":

计算周年

"m":

计算足月

"d":

计算天数

"ym":

计算除了周年之外剩余的足月

"yd":

计算除了周年之外剩余的天数

"md":

计算除了足月之外剩余的天数

例如：

要计算A2与A3间相差的整年则在C2内输入

=DATEDIF（A2,A3,"Y"），

计算相差的整月则输入

=DATEDIF（A2,A3,"m"）图20

如图20

27、星期函数WEEKDAY

此函数可以返回某个日期为星期几

参数为：

1或省略数字1（星期日）到数字7（星期六），

2数字1（星期一）到数字7（星期日）。

3数字0（星期一）到数字6（星期日）。

例如可以输入：

=WEEKDAY（A1,2）

28、计算成绩名次：

CHOOSE函数

利用CHOOSE函数可以计算成绩名次，在L4单元格中输入以下公式：

“=CHOOSE（IF（K4>=95,1,IF（K4>=90,2,IF（K4>=85,3,IF（K4>=80,4,5））））,"五星","四星","三星","二星","一星"）”

图21

29、WORKDAY函数（Excel2007适用）

此函数可以返回某个日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值，工作日不包括周末和专门指定的日期。

例如：

给定一项工作，要求在50天完成（其中不包括3天节假日），此时可以利用WORKDAY函数计算出完成日期。

在单元格中输入上述信息，然后在单元格B6中输入以下公式：

“=WORKDAY（B1,B2,B3:

B5）”。

30、HLOOKUP函数与VLOOKUP函数

HLOOKUP函数与VLOOKUP函数同为数据查找函数，只不过他们两者的查找方式不但不相同，后者以第1列为查找目标，前者以第1行为查找目标；两者皆返回最终查找到的数据对应的行列值！

如图22：

要查询姓名为张三的数学成绩，公式为=HLOOKUP（"张三",A1:

C4,2,FALSE）

要查询张三下一个是谁，公式为=VLOOKUP（"张三",A1:

C4,2,FALSE）

图22

31、超链接函数HYPERLINK

超链接函数HYPERLINK不常被用到，但功能却很强大，如我们想间一个查询界面，如图23，可以输入如下公式

如图23

图23

=HYPERLINK（"","