Excel技巧大全365天每一个Excel技巧.docx
《Excel技巧大全365天每一个Excel技巧.docx》由会员分享，可在线阅读，更多相关《Excel技巧大全365天每一个Excel技巧.docx（72页珍藏版）》请在冰点文库上搜索。
Excel技巧大全365天每一个Excel技巧
Excel技巧大全：
365天，每一个Excel技巧
新增技巧66项
1、两列数据查找相同值对应的位置
2、已知公式得结果
3、强制换行
4、超过15位数字输入
5、如果隐藏了B列，如果让它显示出来?
6、EXCEL中行列互换
7、Excel是怎么加密的
8、关于COUNTIF
9、根据号提取出生日期
10、想在SHEET2中完全引用SHEET1输入的数据
11、一列中不输入重复数字
12、直接打开一个电子表格文件的时候打不开
13、excel下拉菜单的实现
14、10列数据合计成一列
15、查找数据公式两个（基本查找函数为VLOOKUP,MATCH）
16、如何隐藏单元格中的0
17、多个工作表的单元格合并计算
18、获得工作表名称
19、A）如何获取一个月的最大天数
21、EXCEL开方运算
22、单元格中的数据分散对齐
23、查找工作表中的
24、如何让空单元格自动填为0
25、把Word里的数字转换到Excel
26、扩展你的命名域
27、单双行简单选
28、如何在已有的单元格中批量加入一段固定字符？
29、如何设置文件下拉窗口的最下面的最近运行的文件名个数？
30、在EXCEL中输入如“１-１”、“１-２”之类的格式后它即变成１月１日，１月２日等日期形式，怎么办？
31、在EXCEL中如何使它象WORD一样的自动定时保存文件？
32、用Excel做多页的表格时，怎样像Word的表格那样做一个标题，即每页的第一行（或几行）是一样的。
但是不是用页眉来完成？
33、在Excel中如何设置加权平均？
34、如果在一个Excel文件中含有多个工作表，如何将多个工作表一次设置成同样的页眉和页脚？
如何才能一次打印多个工作表？
35、EXCEL中有序号一栏，由于对表格进行调整，序号全乱了,可要是手动一个一个改序号实在太慢太麻烦，用什么方法可以快速解决？
36、用Excel2000做成的工资表，只有第一个人有工资条的条头（如编号、、岗位工资.......），想输出成工资条的形式。
怎么做？
37、在Ｅxcel中小数点无法输入，按小数点，显示的却是逗号，无论怎样设置选项都无济于事，该怎么办？
38、如何快速选取特定区域？
39、如何快速返回选中区域？
40、如何快速定位到单元格？
41、“Ctrl＋*”的特殊功用
42．如何快速选取工作表中所有包含公式的单元格？
43、如何在不同单元格中快速输入同一数容？
44、只记得函数的名称，但记不清函数的参数了，怎么办？
45、如何把选定的一个或多个单元格拖放至新的位置？
46、如何让屏幕上的工作空间变大？
47、如何使用快显菜单？
48、如何使用快显菜单？
49、如何防止Excel自动打开太多文件？
50、如何去掉网格线？
51、如何快速格式化报表？
52、如何快速地复制单元格的格式？
53、如何为表格添加斜线？
54、如何快速地将数字作为文本输入？
55、如何定义自己的函数？
56、如何在一个与自定义函数驻留工作簿不同的工作簿的工作表公式中调用自定义函数？
57、如何快速输入数据序列？
58、使用鼠标右键拖动单元格填充柄
59．如果你的工作表中已有某个序列项，想把它定义成自动填充序列以备后用，是否需要按照上面介绍的自定义序列的方法重新输入这些序列项？
60、上例中，如果你已拥育的序列项中含有许多重复项，应如何处理使其没有重复项，以便使用“引入”的方法快速创建所需的自定义序列？
61、如何对工作簿进行安全保护？
62、如何使单元格中的颜色和底纹不打印出来？
63、工作表保护的口令忘记了怎么办？
64、“$”的功用
65、如何用汉字名称代替单元格地址？
66、如何在公式中快速输入不连续的单元格地址？
67、如何定义局部名字？
68、如何命名常数？
69、工作表名称中能含有空格吗？
70、给工作表命名应注意的问题
71、如何拆分或取消拆分窗口？
72、如何给工作簿扩容？
73、如何减少重复劳动？
74、如何快速地批量修改数据？
75、如何快速删除特定的数据？
76、如何快速删除工作表中的空行？
77、如何使用数组公式？
78、如何不使显示或打印出来的表格中包含有0值？
79、在Excel中用Average函数计算单元格的平均值的，值为0的单元格也包含在。
有没有办法在计算平均值时排除值为0的单元格？
80、如何在Excel中实现“自动更正”功能？
一、基本方法7
1.快速选中全部工作表7
2.快速启动Excel7
3.快速删除选定区域数据7
4.给单元格重新命名7
5.在Excel中选择整个单元格围7
6.快速移动/复制单元格8
7.快速修改单元格式次序8
8.彻底清除单元格容8
9.选择单元格8
10.为工作表命名9
11.一次性打开多个工作簿9
12.快速切换工作簿9
13.选定超级文本（微软Office技巧大赛获奖作品）10
14.快速查找10
15.修改默认文件保存路径10
16.指定打开的文件夹10
17.在多个Excel工作簿间快速切换10
18.快速获取帮助11
19.创建帮助文件的快捷方式11
20.双击单元格某边移动选定单元格11
21.双击单元格某边选取单元格区域11
22.快速选定不连续单元格11
23.根据条件选择单元格11
24.复制或移动单元格12
25.完全删除Excel中的单元格12
26.快速删除空行12
27.回车键的粘贴功能12
28.快速关闭多个文件12
29.选定多个工作表12
30.对多个工作表快速编辑13
31.移动和复制工作表13
32.工作表的删除13
33.快速选择单元格13
34.快速选定Excel区域（微软Office技巧大赛获奖作品）13
35.备份工件簿14
36.自动打开工作簿14
37.快速浏览长工作簿14
38.快速删除工作表中的空行14
39.绘制斜线表头14
40.绘制斜线单元格15
41.每次选定同一单元格15
42.快速查找工作簿15
43.禁止复制隐藏行或列中的数据15
44.制作个性单元格16
二、数据输入和编辑技巧16
1.在一个单元格输入多个值16
2.增加工作簿的页数16
3.奇特的F4键16
4.将格式化文本导入Excel16
5.快速换行17
6.巧变文本为数字17
7.在单元格中输入0值17
8.将数字设为文本格式18
9.快速进行单元格之间的切换（微软Office技巧大赛获奖作品）18
10.在同一单元格连续输入多个测试值18
11.输入数字、文字、日期或时间18
12.快速输入欧元符号19
13.将单元格区域从公式转换成数值19
14.快速输入有序文本19
15.输入有规律数字19
16.巧妙输入常用数据19
17.快速输入特殊符号20
18.快速输入相同文本20
19.快速给数字加上单位20
20.巧妙输入位数较多的数字21
21.将WPS/Word表格转换为Excel工作表21
22.取消单元格21
23.快速输入拼音21
24.插入“√”21
25.按小数点对齐22
26.对不同类型的单元格定义不同的输入法22
27.在Excel中快速插入Word表格22
28.设置单元格字体22
29.在一个单元格中显示多行文字23
30.将网页上的数据引入到Excel表格23
31.取消超级23
32.编辑单元格容23
33.设置单元格边框23
34.设置单元格文本对齐方式24
35.输入公式24
36.输入人名时使用“分散对齐”（微软Office技巧大赛获奖作品）24
37.隐藏单元格中的所有值（微软Office技巧大赛获奖作品）24
38.恢复隐藏列24
39.快速隐藏/显示选中单元格所在行和列（微软Office技巧大赛获奖作品）24
40.彻底隐藏单元格25
41.用下拉列表快速输入数据25
42.快速输入自定义短语25
43.设置单元格背景色25
44.快速在多个单元格中输入相同公式26
45.同时在多个单元格中输入相同容26
46.快速输入日期和时间26
47.将复制的单元格安全地插入到现有单元格之间26
48.在Excel中不丢掉列标题的显示26
49.查看与日期等效的序列数的值27
50.快速复制单元格容27
51.使用自定义序列排序（微软Office技巧大赛获奖作品）27
52.快速格式化Excel单元格27
53.固定显示某列27
54.在Excel中快速编辑单元格27
55.使用自动填充快速复制公式和格式28
56.为单元格添加批注28
57.数据自动输入28
58.在Excel中快速计算一个人的年龄28
59.快速修改单元格次序29
60.将网页上的数据引入到Excel表格中29
三、图形和图表编辑技巧29
1.在网上发布Excel生成的图形29
2.创建图表连接符30
3.将Excel单元格转换成图片形式插入到Word中30
4.将Word容以图片形式插入到Excel表格中30
5.将Word中的容作为图片插入Excel表格中30
6.在独立的窗口中处理嵌式图表31
7.在图表中显示隐藏数据31
8.在图表中增加文本框31
9.建立文本与图表文本框的31
10.给图表增加新数据系列31
11.快速修改图表元素的格式32
12.创建复合图表32
13.对度量不同的数据系列使用不同坐标轴32
14.将自己满意的图表设置为自定义图表类型32
15.复制自定义图表类型33
16.旋转三维图表33
17.拖动图表数据点改变工作表中的数值33
18.把图片合并进你的图表33
19.用图形美化工作表34
20.让文本框与工作表网格线合二为一34
21.快速创建默认图表34
22.快速创建嵌式图表35
23.改变默认图表类型35
24.快速转换嵌式图表与新工作表图表35
25.利用图表工具栏快速设置图表35
26.快速选取图表元素36
27.通过一次按键创建一个Excel图表36
28.绘制平直直线36
四、函数和公式编辑技巧36
1.巧用IF函数清除Excel工作表中的036
2.批量求和37
3.对相邻单元格的数据求和37
4.对不相邻单元格的数据求和37
5.利用公式来设置加权平均38
6.自动求和38
7.用记事本编辑公式38
8.防止编辑栏显示公式38
9.解决SUM函数参数中的数量限制39
10.在绝对与相对单元引用之间切换39
11.快速查看所有工作表公式39
12.实现条件显示39
五、数据分析和管理技巧40
1.管理加载宏40
2.在工作表之间使用超级连接40
3.快速网上的数据40
4.跨表操作数据40
5.查看Excel中相距较远的两列数据41
6.如何消除缩位后的计算误差（微软Office技巧大赛获奖作品）41
7.利用选择性粘贴命令完成一些特殊的计算41
8.Web查询41
9.在Excel中进行快速计算42
10.自动筛选前10个42
11.同时进行多个单元格的运算（微软Office技巧大赛获奖作品）42
12.让Excel出现错误数据提示43
13.用“超级连接”快速跳转到其它文件43
六、设置技巧43
1.定制菜单命令43
2.设置菜单分隔线43
3.备份自定义工具栏44
4.共享自定义工具栏44
5.使用单文档界面快速切换工作簿44
6.自定义工具栏按钮44
1、两列数据查找相同值对应的位置
=MATCH（B1,A:
A，0）
2、已知公式得结果
定义名称=EVALUATE（Sheet1!
C1）
已知结果得公式
定义名称=GET.CELL（6,Sheet1!
C1）
3、强制换行
用Alt+Enter
4、超过15位数字输入
这个问题问的人太多了，也收起来吧。
一、单元格设置为文本;二、在输入数字前先输入'
5、如果隐藏了B列，如果让它显示出来?
选中A到C列，点击右键，取消隐藏
选中A到C列，双击选中任一列宽线或改变任一列宽
将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。
6、EXCEL中行列互换
复制，选择性粘贴，选中转置，确定即可
7、Excel是怎么加密的
（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置
（2）、工具>>选项>>安全性
8、关于COUNTIF
COUNTIF函数只能有一个条件，如大于90，为=COUNTIF（A1:
A10,">=90"）
介于80与90之间需用减，为=COUNTIF（A1:
A10,">80"）-COUNTIF（A1:
A10,">90"）
另外补充：
条件：
列中包含某字符A1->含字符EA
目的：
赋予含该字符对应单元格相应公式或数值
套用公式：
=if（COUNT（search（"EA",A1））,公式或数值1,公式或数值2）
=if（ISERR（search（"EA",A1））,公式或数值2,公式或数值1）
Count——因为Search得到的是代表位置的数字或者#Value错误，Count对数字可以统计，对错误值得到0。
Iserr——条件反转。
Isnumber—当A1为数字时，则返回true
9、根据号提取出生日期
（1）、=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））,"错误号"））
（2）、=TEXT（MID（A2,7,6+（LEN（A2）=18）*2）,"#-00-00"）*1
EXCEL补充：
A）提取生日（年-月-日）：
=IF（LEN（A1）=15,TEXT（CONCATENATE（"19",MID（A1,7,6））,"#-00-00"）,TEXT（MID（A1,7,8）,"#-00-00"））
B）提取年龄：
=YEAR（TODAY（））-IF（LEN（A1）=15,CONCATENATE（"19",MID（A1,7,2））,MID（A1,7,4））
C）提取性别：
=IF（MOD（IF（LEN（A1）=15,MID（A1,15,1）,MID（A1,17,1））,2）=1,"男","女"）
10、想在SHEET2中完全引用SHEET1输入的数据
工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。
11、一列中不输入重复数字
[数据]--[有效性]--[自定义]--[公式]
输入=COUNTIF（A:
A,A1）=1
如果要查找重复输入的数字
条件格式》公式》=COUNTIF（A:
A,A5）>1》格式选红色
12、直接打开一个电子表格文件的时候打不开
“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上
13、excel下拉菜单的实现
[数据]-[有效性]-[序列]
14、10列数据合计成一列
=SUM（OFFSET（$1,（ROW（）-2）*10+1,,10,1））
15、查找数据公式两个（基本查找函数为VLOOKUP,MATCH）
（1）、根据符合行列两个条件查找对应结果
=VLOOKUP（H1，A1：
E7，MATCH（I1，A1：
E1，0），FALSE）
（2）、根据符合两列数据查找对应结果（为数组公式）
=INDEX（C1：
C7，MATCH（H1&I1,A1:
A7&B1:
B7,0））
16、如何隐藏单元格中的0
单元格格式自定义0;-0;;或选项》视图》零值去勾。
呵呵，如果用公式就要看情况了。
17、多个工作表的单元格合并计算
=Sheet1!
D4+Sheet2!
D4+Sheet3!
D4，更好的=SUM（Sheet1:
Sheet3!
D4）
18、获得工作表名称
（1）、定义名称：
Name
=GET.DOCUMENT（88）
（2）、定义名称：
Path
=GET.DOCUMENT
（2）
（3）、在A1中输入=CELL（"filename"）得到路径级文件名
在需要得到文件名的单元格输入
=MID（A1,FIND（"*",SUBSTITUTE（A1,"","*",LEN（A1）-LEN（SUBSTITUTE（A1,"",""））））+1,LEN（A1））
（4）、自定义函数
PublicFunctionname（）
DimfilenameAsString
filename=ActiveWorkbook.name
name=filename
EndFunction
19、A）如何获取一个月的最大天数
:
"=DAY（DATE（2002,3,1）-1）"或"=DAY（B1-1）",B1为"2001-03-01
B）数据区包含某一字符的项的总和,该用什么公式
=sumif（a:
a,"*"&"某一字符"&"*",数据区）
C）最后一行为文本：
=offset（$b$1,MATCH（CHAR（65535）,b:
b）-1,）
D）最后一行为数字：
=offset（$b$1,MATCH（9.9999E+307,b:
b）-1,）
或者：
=lookup（2,1/（b1:
b1000<>""）,b1:
b1000）
E）评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?
同时显示出被去掉的分数。
看看trimmean（）函数帮助。
被去掉的分数：
最大两个：
=large（data,{1;2}）
最小两个：
=small（data,{1;2}）
F）怎样很简单的判断最后一位是字母
right（a1）*1
G）出错的字母
=IF（ISNUMBER（--RIGHT（A1,1））,"数字","字母"）
=IF（ISERR（RIGHT（A1）*1）,"字母","数字"）
H）如何设置单元格，令其不接受包含空格的字符
选定A列
数据——有效性——自定义——公式
=iserror（find（"",a1））
数据--有效性--自定义--公式
=len（a1）=len（trim（a1））注*单元格消空格用函数=TRIM（A1）
I）实现跳格累加如=a1+a4+a7+a10+a13+a16+a19+a22.....
=sum（n（offset（a1,（row（1:
10）-1）*3,）））
J）在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?
=INDIRECT（"A1"&"!
"&"E1"）A1为工作表名
K）奇数行求和=SUMPRODUCT（（A1:
A1000）*MOD（ROW（A1:
A1000）,2））
L）偶数行求和=SUMPRODUCT（（A1:
A1000）*NOT（MOD（ROW（A1:
A1000）,2）））
M）查看字符串字数
=LEN（A1）
N）求非空单元格数量
公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去了）
=COUNTIF（$E$3E$65536,"?
*"）
O）动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.
=SUM（INDIRECT（"A1:
A"&ROW（）-1））
20、比较好用的EXCEL文档修复工具
ExcelRecovery
21、EXCEL开方运算
将8开3次方，可以用这个公式，在单元格中输入 =8^（1/3）
22、单元格中的数据分散对齐
文本格式》全角输入
23、查找工作表中的
Ctrl+~或编辑》
24、如何让空单元格自动填为0
选中需更改的区域》查找》空》替换》0
25、把Word里的数字转换到Excel
方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值
选中》表格转换为文本》粘贴》分列》对分列选项设置为文本
另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置
26、扩展你的命名域
Excel允许你定义命名的域，这样你的方程式就能够更具描述性，例如，你可以使用“=SUM（Bonus）”来替换“=SUM（C2:
C25）”。
尽管Excel的命名域通常都是静态的单元域，例如“C2:
C25”，但你也可以在添加新数据的时候创建一个动态的命名域。
要将一个域更名并让其变成动态，请注意工作簿的名称，以及第一个数据单元所在的地址，它需要不是一个列标题，例如“$C$2”。
点击“插入>名称>定义”，在“在当前工作簿的名称”中输入工作簿名。
在“引用位置”中，输入“=OFFSET（Bonus!
$C$2,0,0,COUNTA（Bonus!
$C:
$C））”，并注意其中不能有空格，接着按下回车键来创建命名域。
这个方程式就能够定义你的命名域，计算列中的数值，并能够按单元格的数字延伸这个域。
27、单双行简单选
在A列前插入一空列，A1中输入1，A2中输入2，选中A1——A2，将光标移到A2右下角，当光标变成实心十字时，按住ctrl见往下拖动到想要的位置，再按这列排序，就把单双行分开了
28、如何在已有的单元格中批量加入一段固定字符？
例如：
在单位的人事资料，在excel中输入后，由于上级要求在原来的职称证书的全部再加两位，即要在每个人的证书前再添上两位数13，如果一个一个改的话实在太麻烦了，那么我们可以用下面的办法，省时又省力：
1）假设证书号在A列，在A列后点击鼠标右键，插入一列，为B列；
2）在B2单元格写入：
="13"&A2后回
升级会员 