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中实现“自动更正”功能？
一、基本法2
1.快速选中全部工作表2
2.快速启动Excel2
3.快速删除选定区域数据2
4.给单元格重新命名2
5.在Excel中选择整个单元格围2
6.快速移动/复制单元格2
7.快速修改单元格式次序2
8.彻底清除单元格容2
9.选择单元格2
10.为工作表命名2
11.一次性打开多个工作簿2
12.快速切换工作簿2
13.选定超级文本（微软Office技巧大赛获奖作品）2
14.快速查找2
15.修改默认文件保存路径2
16.指定打开的文件夹2
17.在多个Excel工作簿间快速切换2
18.快速获取帮助2
19.创建帮助文件的快捷式2
20.双击单元格某边移动选定单元格2
21.双击单元格某边选取单元格区域2
22.快速选定不连续单元格2
23.根据条件选择单元格2
24.复制或移动单元格2
25.完全删除Excel中的单元格2
26.快速删除空行2
27.回车键的粘贴功能2
28.快速关闭多个文件2
29.选定多个工作表2
30.对多个工作表快速编辑2
31.移动和复制工作表2
32.工作表的删除2
33.快速选择单元格2
34.快速选定Excel区域（微软Office技巧大赛获奖作品）2
35.备份工件簿2
36.自动打开工作簿2
37.快速浏览长工作簿2
38.快速删除工作表中的空行2
39.绘制斜线表头2
40.绘制斜线单元格2
41.每次选定同一单元格2
42.快速查找工作簿2
43.禁止复制隐藏行或列中的数据2
44.制作个性单元格2
二、数据输入和编辑技巧2
1.在一个单元格输入多个值2
2.增加工作簿的页数2
3.奇特的F4键2
4.将格式化文本导入Excel2
5.快速换行2
6.巧变文本为数字2
7.在单元格中输入0值2
8.将数字设为文本格式2
9.快速进行单元格之间的切换（微软Office技巧大赛获奖作品）2
10.在同一单元格连续输入多个测试值2
11.输入数字、文字、日期或时间2
12.快速输入欧元符号2
13.将单元格区域从公式转换成数值2
14.快速输入有序文本2
15.输入有规律数字2
16.巧妙输入常用数据2
17.快速输入特殊符号2
18.快速输入相同文本2
19.快速给数字加上单位2
20.巧妙输入位数较多的数字2
21.将WPS/Word表格转换为Excel工作表2
22.取消单元格2
23.快速输入拼音2
24.插入“√”2
25.按小数点对齐2
26.对不同类型的单元格定义不同的输入法2
27.在Excel中快速插入Word表格2
28.设置单元格字体2
29.在一个单元格中显示多行文字2
30.将网页上的数据引入到Excel表格2
31.取消超级2
32.编辑单元格容2
33.设置单元格边框2
34.设置单元格文本对齐式2
35.输入公式2
36.输入人名时使用“分散对齐”（微软Office技巧大赛获奖作品）2
37.隐藏单元格中的所有值（微软Office技巧大赛获奖作品）2
38.恢复隐藏列2
39.快速隐藏/显示选中单元格所在行和列（微软Office技巧大赛获奖作品）2
40.彻底隐藏单元格2
41.用下拉列表快速输入数据2
42.快速输入自定义短语2
43.设置单元格背景色2
44.快速在多个单元格中输入相同公式2
45.同时在多个单元格中输入相同容2
46.快速输入日期和时间2
47.将复制的单元格安全地插入到现有单元格之间2
48.在Excel中不丢掉列标题的显示2
49.查看与日期等效的序列数的值2
50.快速复制单元格容2
51.使用自定义序列排序（微软Office技巧大赛获奖作品）2
52.快速格式化Excel单元格2
53.固定显示某列2
54.在Excel中快速编辑单元格2
55.使用自动填充快速复制公式和格式2
56.为单元格添加批注2
57.数据自动输入2
58.在Excel中快速计算一个人的年龄2
59.快速修改单元格次序2
60.将网页上的数据引入到Excel表格中2
三、图形和图表编辑技巧2
1.在网上发布Excel生成的图形2
2.创建图表连接符2
3.将Excel单元格转换成图片形式插入到Word中2
4.将Word容以图片形式插入到Excel表格中2
5.将Word中的容作为图片插入Excel表格中2
6.在独立的窗口中处理嵌式图表2
7.在图表中显示隐藏数据2
8.在图表中增加文本框2
9.建立文本与图表文本框的2
10.给图表增加新数据系列2
11.快速修改图表元素的格式2
12.创建复合图表2
13.对度量不同的数据系列使用不同坐标轴2
14.将自己满意的图表设置为自定义图表类型2
15.复制自定义图表类型2
16.旋转三维图表2
17.拖动图表数据点改变工作表中的数值2
18.把图片合并进你的图表2
19.用图形美化工作表2
20.让文本框与工作表网格线合二为一2
21.快速创建默认图表2
22.快速创建嵌式图表2
23.改变默认图表类型2
24.快速转换嵌式图表与新工作表图表2
25.利用图表工具栏快速设置图表2
26.快速选取图表元素2
27.通过一次按键创建一个Excel图表2
28.绘制平直直线2
四、函数和公式编辑技巧2
1.巧用IF函数清除Excel工作表中的02
2.批量求和2
3.对相邻单元格的数据求和2
4.对不相邻单元格的数据求和2
5.利用公式来设置加权平均2
6.自动求和2
7.用记事本编辑公式2
8.防止编辑栏显示公式2
9.解决SUM函数参数中的数量限制2
10.在绝对与相对单元引用之间切换2
11.快速查看所有工作表公式2
12.实现条件显示2
五、数据分析和管理技巧2
1.管理加载宏2
2.在工作表之间使用超级连接2
3.快速网上的数据2
4.跨表操作数据2
5.查看Excel中相距较远的两列数据2
6.如消除缩位后的计算误差（微软Office技巧大赛获奖作品）2
7.利用选择性粘贴命令完成一些特殊的计算2
8.Web查询2
9.在Excel中进行快速计算2
10.自动筛选前10个2
11.同时进行多个单元格的运算（微软Office技巧大赛获奖作品）2
12.让Excel出现错误数据提示2
13.用“超级连接”快速跳转到其它文件2
六、设置技巧2
1.定制菜单命令2
2.设置菜单分隔线2
3.备份自定义工具栏2
4.共享自定义工具栏2
5.使用单文档界面快速切换工作簿2
6.自定义工具栏按钮2
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后回车；
3）看到结果为13xxxxxxxxxxxxx了吗？
鼠标放到B2位置，单元格的下不是有一个小点吗，按着鼠标左键往下拖动直到结束。
当你放开鼠标左键时就全部都改好了。
若是在原证书号后面加13则在B2单元格中写入：
=A2&“13”后回车。
29、如设置文件下拉窗口的最下面的最近运行的文件名个数？
打开“工具”，选“选项”，再选“常规”，在“最近使用的文件清单”下面的文件个数输入框中改
升级会员 