1.自定义快速访问工具栏
可以把自己喜欢的又经常使用的加进来
![](https://img.haomeiwen.com/i6483728/3b8cb9e60c95a9bc.png)
2.新增自定义选项卡
File-->Option-->Customize Ribbon
![](https://img.haomeiwen.com/i6483728/3170cb11615b9d6c.png)
3. Open developer Tool.
如果需要宏或者一些功能找不到的话,也可以来这里添加
![](https://img.haomeiwen.com/i6483728/43fc30032a4ad71f.png)
![](https://img.haomeiwen.com/i6483728/3217d35ea9842c46.png)
4.保护工作簿结构
如果你不希望他人对你的表格进行新增或者删除,可以试试如下方法
Protect the workbook.The workbook can not be edit or add or delete.
![](https://img.haomeiwen.com/i6483728/2976f0291ba949b8.png)
![](https://img.haomeiwen.com/i6483728/ccfce7d0ff92b245.png)
5. 对表格内容进行设置,只允许他们对你的表格进行一定范围内的更改
保护工作表格
1)选中几行-->Ctrl+1-->Protection +cancel Locked
2)Review-->Protect sheet
![](https://img.haomeiwen.com/i6483728/5069b7e7a63fb24b.png)
![](https://img.haomeiwen.com/i6483728/403a3d8a65680c1e.png)
![](https://img.haomeiwen.com/i6483728/6888ff2fdd73537e.png)
设置成功后如果去更改划定范围以外的内容就会提示不可以。
像不像孙悟空给师傅画了一个圈,圈子以内可以走动,圈子以外出不去哈。
![](https://img.haomeiwen.com/i6483728/0a6ef18158ad3755.png)
6.文件加密
如果这个文件你想设置加密,方法如下
Save as-->Tools-->General Options
![](https://img.haomeiwen.com/i6483728/f8d0c1e2efa40216.png)
![](https://img.haomeiwen.com/i6483728/12f4444f4d41292c.png)
7.2个好玩的随时生成数
=rand()
![](https://img.haomeiwen.com/i6483728/d188d7c3159dd9a6.png)
=RANDBETWEEN(10,15)
![](https://img.haomeiwen.com/i6483728/8c9ecd33511e4c31.png)
8.自定义格式的使用
举例:如果有很多数据,你需要添加后缀,一行一行的输入是一件很郁闷的事情。
![](https://img.haomeiwen.com/i6483728/2a4ab37cb545c1d0.png)
解决方法如下:
Format Cell -->Custom-->Type:@部门
![](https://img.haomeiwen.com/i6483728/3e459dd4246c5a60.png)
同理:
![](https://img.haomeiwen.com/i6483728/fc568ed0cc38a60f.png)
![](https://img.haomeiwen.com/i6483728/6a69c01cc5e41246.png)
再举个例子:
![](https://img.haomeiwen.com/i6483728/04d457ab1933ae72.png)
![](https://img.haomeiwen.com/i6483728/a81bde5642a42615.png)
9.连接字符串
效果如果
![](https://img.haomeiwen.com/i6483728/0fcedaed81b7d26e.png)
实现如下:
方法一:
![](https://img.haomeiwen.com/i6483728/2e97e80f14e4c0f2.png)
方法二:
![](https://img.haomeiwen.com/i6483728/71eea573dd8a3923.png)
10.批量输入
效果如下
![](https://img.haomeiwen.com/i6483728/9cae91f2cede670b.png)
实现如下:
选中区域-->输入value-->ctrl+Enter
11.作为老师,想给学生的成绩做个排名,如何快捷迅速知道结果么?
![](https://img.haomeiwen.com/i6483728/80d709c51c3b8ed5.png)
12.常用统计函数
![](https://img.haomeiwen.com/i6483728/7edef6da5d06e36d.png)
![](https://img.haomeiwen.com/i6483728/2e05207cd517c170.png)
![](https://img.haomeiwen.com/i6483728/2269117c61608457.png)
![](https://img.haomeiwen.com/i6483728/a71e85af08d05f3d.png)
![](https://img.haomeiwen.com/i6483728/1dfd0361f6ff083c.png)
13.条件统计函数
![](https://img.haomeiwen.com/i6483728/c287e4955d796a78.png)
![](https://img.haomeiwen.com/i6483728/45e19d821ff46ad7.png)
![](https://img.haomeiwen.com/i6483728/d7cd33dd61f360c2.png)
14.常用文本函数
![](https://img.haomeiwen.com/i6483728/328ceaaa266f0b4f.png)
![](https://img.haomeiwen.com/i6483728/c5af1056ddb705c3.png)
第二个字符开始的后面3个
![](https://img.haomeiwen.com/i6483728/3c9f327094fb008a.png)
![](https://img.haomeiwen.com/i6483728/3068f03a9a214471.png)
![](https://img.haomeiwen.com/i6483728/ed370dd15ee59dcb.png)
![](https://img.haomeiwen.com/i6483728/825b842c9cdb1949.png)
15.If函数
![](https://img.haomeiwen.com/i6483728/7b28cce06982e0d3.png)
16.常用日期函数
今天
![](https://img.haomeiwen.com/i6483728/9a2696dbb942ad07.png)
现在
![](https://img.haomeiwen.com/i6483728/b3a46cccd447abbf.png)
返回月份
![](https://img.haomeiwen.com/i6483728/eeb7031a00c3f771.png)
返回年
![](https://img.haomeiwen.com/i6483728/8007c5011227dc8a.png)
返回日期
![](https://img.haomeiwen.com/i6483728/ee59839674ae245f.png)
Date(年,月,日)返回日期类型
![](https://img.haomeiwen.com/i6483728/f4c68cc9fd236296.png)
去年今天
![](https://img.haomeiwen.com/i6483728/599f95a5828251c6.png)
显示往后6个月的日期
![](https://img.haomeiwen.com/i6483728/38fd31839a366436.png)
显示往前的6个月的日期
![](https://img.haomeiwen.com/i6483728/9c99bbe5a322605f.png)
保留小数点一位小数
roundup 进位
![](https://img.haomeiwen.com/i6483728/e763427abe0b6be6.png)
rounddown 舍掉位数
![](https://img.haomeiwen.com/i6483728/3484c30d9e7bee86.png)
17.其他常用函数
![](https://img.haomeiwen.com/i6483728/87e5c144b5a83b6d.png)
![](https://img.haomeiwen.com/i6483728/dc9c675690994d4b.png)
![](https://img.haomeiwen.com/i6483728/006a4b7bd617e386.png)
![](https://img.haomeiwen.com/i6483728/ae93f4e4b142058c.png)
![](https://img.haomeiwen.com/i6483728/fab0b67ca181fcde.png)
18.条件格式
效果如下:
![](https://img.haomeiwen.com/i6483728/bdbd669b8ced83e7.png)
操作如下:
![](https://img.haomeiwen.com/i6483728/a8569d01ae34bd30.png)
![](https://img.haomeiwen.com/i6483728/a518e453ccbd76e2.png)
18.条件格式-不显示数据,把数据显示为进度条
操作如下:
![](https://img.haomeiwen.com/i6483728/88bf6be9a15ef6b2.png)
效果如下:
![](https://img.haomeiwen.com/i6483728/acb31191478717f6.png)
19.主题套用
PAGE LAYOUT-->THEMES
![](https://img.haomeiwen.com/i6483728/7e8f5db141cf28f9.png)
20.打印每一页都带标题
![](https://img.haomeiwen.com/i6483728/d27acc731473d7e8.png)
21.数据有效性
--限制字数
举例:
限制表格内只能输入12位数
效果如下:
![](https://img.haomeiwen.com/i6483728/6ebaa8a25143d50b.png)
操作如下:
![](https://img.haomeiwen.com/i6483728/ac1cb69c0b0eeb32.png)
![](https://img.haomeiwen.com/i6483728/169b6943f75ffc72.png)
--日期
--序列
--设置输入提示信息
结果如下:
![](https://img.haomeiwen.com/i6483728/be0b58f171162ba4.png)
![](https://img.haomeiwen.com/i6483728/30b161c5b1e3dd15.png)
--设置出错警告
--=mod(row(),2)=0 偶数行输入内容
---Counta(G$2:G2)=row(A1)必须连续输入
22.更新数据透视表
当根据一组数据做好一张数据透视表
后期需要添加数据,如何让已经完成的数据透视表也同时可以更新后期添加进来的数据呢?
![](https://img.haomeiwen.com/i6483728/bab1c3e1b1992810.png)
![](https://img.haomeiwen.com/i6483728/8719999c5e67a909.png)
![](https://img.haomeiwen.com/i6483728/62604f7de718c930.png)
23.自定义排序
![](https://img.haomeiwen.com/i6483728/1d4278c1a9780904.png)
![](https://img.haomeiwen.com/i6483728/3f87aa0cc72eeb49.png)
24.把身份证号码的出生年份提取出来
结果如下
![](https://img.haomeiwen.com/i6483728/44232e4c283ac053.png)
操作如下
![](https://img.haomeiwen.com/i6483728/94dbfa31be2c524e.png)
![](https://img.haomeiwen.com/i6483728/d1c4aac1ba589d08.png)
![](https://img.haomeiwen.com/i6483728/9abac9d28a1763ff.png)
25.多表透视
需要多个表格的内容做成一张数据透视表
ALT+D+P
![](https://img.haomeiwen.com/i6483728/66ecd5f530c1998e.png)
![](https://img.haomeiwen.com/i6483728/fb1296d99a54805c.png)
26.去掉数据透视表中的 grand total.
![](https://img.haomeiwen.com/i6483728/0386b556c5bcfc49.png)
27.vlookup
表述就是VLOOKUP(查找值,查找范围,查找列数,精确匹配或者近似匹配)
举例:
效果如下:
![](https://img.haomeiwen.com/i6483728/47619390b2e8af3e.png)
如何运用vlookup实现呢
引入一个相对数 F4
B1:C12 如何变成 $B$1:$C$12-->F4
![](https://img.haomeiwen.com/i6483728/e54ead91d209cde0.png)
![](https://img.haomeiwen.com/i6483728/98e99afd12c05ad3.png)
![](https://img.haomeiwen.com/i6483728/c41fbb2d13bbed47.png)
网友评论