Excel使用技巧.docx
《Excel使用技巧.docx》由会员分享，可在线阅读，更多相关《Excel使用技巧.docx（57页珍藏版）》请在冰豆网上搜索。
Excel使用技巧
Excel使用技巧
蔡琳 收集
在工作当中用电子表格来处理数据将会更加迅速、方便，而在各种电子表格处理软件中，Excel以其功能强大、操作方便著称，赢得了广大用户的青睐。
虽然Excel使用很简单，不过真正能用好Excel的用户并不多，很多人一直停留在录入数据的水平，本文将向你介绍一些非常使用的技巧，掌握这些技巧将大大提高你的工作效率。
⒈快速定义工作簿格式
⒉快速复制公式
⒊快速显示单元格中的公式
⒋快速删除空行
⒌自动切换输入法
⒍自动调整小数点
⒎用“记忆式输入”
⒏用“自动更正”方式实现快速输入
⒐用下拉列表快速输入数据
10..两次选定单元格
11.“Shift＋拖放"的妙用
12.超越工作表保护的诀窍
13.巧用IF函数
14.累加小技巧
15.怎样保护表格中的数据
16如何避免Excel中的错误信息
17.不用编程--Excel公式也能计算个人所得税
18.用EXCEL轻松处理学生成绩
19.用EXCEL轻松准备考前工作
20.Excel的图表功能
21.批量修改数据
22.将Excel数据导入Access
23.办公技巧：
Excel定时提醒不误事
24.办公小绝招构造Excel动态图表
（1）
25.办公小绝招构造Excel动态图表
（2）
26.Excel中三表“嵌套”成一表
27.巧用Excel建立数据库大法
28.Excel最新提速大法之12绝招
29.Excel打印故障问答锦囊
30.Excel计算住房贷款和个人储蓄
（1）
31.Excel计算住房贷款和个人储蓄
（2）
32.Excel计算住房贷款和个人储蓄（3）
33.提高EXCEL录入速度十大绝招
（1）
34.提高EXCEL录入速度十大绝招
（2）
35.EXCEL大量数据快速录入技巧
（1）
36.EXCEL大量数据快速录入技巧
（2）
&返回&
⒈快速定义工作簿格式
首先选定需要定义格式的工作簿范围，单击“格式”菜单的“样式”命令，打开“样式”对话框；然后从“样式名”列表框中选择合适的“样式”种类，从“样式包括”列表框中选择是否使用该种样式的数字、字体、对齐、边框、图案、保护等格式内容；单击“确定”按钮，关闭“样式”对话框，Excel工作簿的格式就会按照用户指定的样式发生变化，从而满足了用户快速、大批定义格式的要求。
&返回&
⒉快速复制公式
复制是将公式应用于其它单元格的操作，最常用的有以下几种方法：
一是拖动制复制。
操作方法是：
选中存放公式的单元格，移动空心十字光标至单元格右下角。
待光标变成小实心十字时，按住鼠标左键沿列（对行计算时）或行（对列计算时）拖动，至数据结尾完成公式的复制和计算。
公式复制的快慢可由小实心十字光标距虚框的远近来调节：
小实心十字光标距虚框越远，复制越快；反之，复制越慢。
也可以输入复制。
此法是在公式输入结束后立即完成公式的复制。
操作方法：
选中需要使用该公式的所有单元格，用上面介绍的方法输入公式，完成后按住Ctrl键并按回车键，该公式就被复制到已选中的所有单元格。
还可以选择性粘贴。
操作方法是：
选中存放公式的单元格，单击Excel工具栏中的“复制”按钮。
然后选中需要使用该公式的单元格，在选中区域内单击鼠标右键，选择快捷选单中的“选择性粘贴”命令。
打开“选择性粘贴”对话框后选中“粘贴”命令，单击“确定”，公式就被复制到已选中的单元格。
&返回&
⒊快速显示单元格中的公式
如果工作表中的数据多数是由公式生成的，如果想要快速知道每个单元格中的公式形式，可以这样做：
用鼠标左键单击“工具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗口选项”栏下的“公式”项有效，单击“确定”按钮。
这时每个单元格中的公式就显示出来了。
如果想恢复公式计算结果的显示，再设置“窗口选项”栏下的“公式”项失效即可。
&返回&
⒋快速删除空行
有时为了删除Excel工作簿中的空行，你可能会将空行一一找出然后删除，这样做非常不方便。
你可以利用“自动筛选”功能来简单实现。
先在表中插入新的一行（全空），然后选择表中所有的行，选择“数据”菜单中的“筛选”，再选择“自动筛选”命令。
在每一列的项部，从下拉列表中选择“空白”。
在所有数据都被选中的情况下，选择“编辑”菜单中的“删除行”，然后按“确定”即可。
所有的空行将被删去。
插入一个空行是为了避免删除第一行数据。
&返回&
⒌自动切换输入法
当你使用Excel2000编辑文件时，在一张工作表中通常是既有汉字，又有字母和数字，于是对于不同的单元格，需要不断地切换中英文输入方式，这不仅降低了编辑效率，而且让人不胜其烦。
在此，笔者介绍一种方法，让你在Excel2000中对不同类型的单元格，实现输入法的自动切换。
新建或打开需要输入汉字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输入法模式”选项卡，在“模式”下拉列表框中选择“打开”，单击“确定”按钮。
选择需要输入字母或数字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输入法模式”选项卡，在“模式”下拉列表框中选择“关闭（英文模式）”，单击“确定”按钮。
之后，当插入点处于不同的单元格时，Excel2000能够根据我们进行的设置，自动在中英文输入法间进行切换。
就是说，当插入点处于刚才我们设置为输入汉字的单元格时，系统自动切换到中文输入状态，当插入点处于刚才我们设置为输入数字或字母单元格时，系统又能自动关闭中文输入法。
&返回&
⒍自动调整小数点
如果你有一大批小于1的数字要录入到Excel工作表中，如果录入前先进行下面的设置，将会使你的输入速度成倍提高。
单击“工具”菜单中的“选项”，然后单击“编辑”选项卡，选中“自动设置小数点”复选框，在“位数”微调编辑框中键入需要显示在小数点右面的位数。
在此，我们键入“2”单击“确定”按钮。
完成之后，如果在工作表的某单元格中键入“4”，则在你按了回车键之后，该单元格的数字自动变为“0.04”。
方便多了吧！
此时如果你在单元格中键入的是“8888”，则在你结束输入之后，该单元格的数字自动变为“88.88”。
&返回&
⒎用“记忆式输入”
有时我们需要在一个工作表中的某一列输入相同数值，这时如果采用“记忆式输入”会帮你很大的忙。
如在职称统计表中要多次输入“助理工程师”，当第一次输入后，第二次又要输入这些文字时，只需要编辑框中输入“助”字，Excel2000会用“助”字与这一列所有的内容相匹配，若“助”字与该列已有的录入项相符，则Excel2000会将剩下的“助理工程师”四字自动填入。
按下列方法设置“记忆式输入”：
选择“工具”中的“选项”命令，然后选择“选项”对话框中的“编辑”选项卡，选中其中的“记忆式键入”即可。
&返回&
⒏用“自动更正”方式实现快速输入
使用该功能不仅可以更正输入中偶然的笔误，也可能把一段经常使用的文字定义为一条短语，当输入该条短语时，“自动更正”便会将它更换成所定义的文字。
你也可以定义自己的“自动更正”项目：
首先，选择“工具”中的“自动更正”命令；然后，在弹出的“自动更正”对话框中的“替换”框中键入短语“爱好者”，在“替换为”框中键入要替换的内容“电脑爱好者的读者”；最后，单击“确定”退出。
以后只要输入“爱好者”，则整个名称就会输到表格中。
&返回&
⒐用下拉列表快速输入数据
如果你希望减少手工录入的工作量，可以用下拉表来实现。
创建下拉列表方法为：
首先，选中需要显示下拉列表的单元格或单元格区域；接着，选择菜单“数据”菜单中的“有效性”命令，从有效数据对话框中选择“序列”，单击“来源”栏右侧的小图标，将打开一个新的“有效数据”小对话框；接着，在该对话框中输入下拉列表中所需要的数据，项目和项目之间用逗号隔开，比如输入“工程师，助工工程师，技术员”，然后回车。
注意在对话框中选择“提供下拉箭头”复选框；最后单击“确定”即可。
&返回&
10..两次选定单元格
有时，我们需要在某个单元格内连续输入多个测试值，以查看引用此单元格的其他单元格的效果。
但每次输入一个值后按Enter键，活动单元格均默认下移一个单元格，非常不便。
此时，你肯定会通过选择“工具”\“选项"\“编辑"，取消“按Enter键移动活动单元格标识框"选项的选定来实现在同一单元格内输入许多测试值，但以后你还得将此选项选定，显得比较麻烦。
其实，采用两次选定单元格方法就显得灵活、方便：
单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格（此时，单元格周围将出现实线框）。
&返回&
11.“Shift＋拖放"的妙用
在拖放选定的一个或多个单元格至新的位置时，同时按住Shift键可以快速修改单元格内容的次序。
具体方法为：
选定单元格，按下Shift键，移动鼠标指针至单元格边缘，直至出现拖放指针箭头“?
"，然后进行拖放操作。
上下拖拉时鼠标在单元格间边界处会变为一个水平“工"状标志，左右拖拉时会变为垂直“工"状标志，释放鼠标按钮完成操作后，单元格间的次序即发生了变化。
这种简单的方法节省了几个剪切和粘贴或拖放操作，非常方便。
&返回&
12.超越工作表保护的诀窍
如果你想使用一个保护了的工作表，但又不知道其口令，有办法吗？
有。
选定工作表，选择“编辑"\“复制"、“粘贴"，将其拷贝到一个新的工作簿中（注意：
一定要新工作簿），即可超越工作表保护。
&返回&
13.巧用IF函数
（1）.设有一工作表，C1单元格的计算公式为：
=A1／B1，当A1、B1单元格没有输入数据时，C1单元格会出现“＃DIV／0！
”的错误信息。
这不仅破坏了屏幕显示的美观，特别是在报表打印时出现“＃DIV／0！
”的信息更不是用户所希望的。
此时，可用IF函数将C1单元格的计算公式更改为：
=IF（B1=0，″″，A1／B1）。
这样，只有当B1单元格的值是非零时，C1单元格的值才按A1／B1进行计算更新，从而有效地避免了上述情况的出现。
（2）.设有C2单元格的计算公式为：
=A2＋B2，当A2、B2没有输入数值时，C2出现的结果是“0”，同样，利用IF函数把C2单元格的计算公式更改如下：
=IF（AND（A2=″″，B2=″″），″″，A2＋B2）。
这样，如果A2与B2单元格均没有输入数值时，C2单元格就不进行A2＋B2的计算更新，也就不会出现“0”值的提示。
（3）.设C3单元格存放学生成绩的数据，D3单元格根据C3（学员成绩）情况给出相应的“及格”、“不及格”的信息。
可用IF条件函数实现D3单元格的自动填充，D3的计算公式为：
=IF（C3＜60，″不及格″，″及格″＝。
&返回&
14.累加小技巧
我们在工作中常常需要在已有数值的单元格中再增加或减去另一个数。
一般是在计算器中计算后再覆盖原有的数据。
这样操作起来很不方便。
这里有一个小技巧，可以有效地简化老式的工作过程。
（1）.创建一个宏：
选择Excel选单下的“工具→宏→录制新宏”选项；
宏名为：
MyMacro；
快捷键为：
Ctrl＋Shift＋J（只要不和Excel本身的快捷键重名就行）；
保存在：
个人宏工作簿（可以在所有Excel工作簿中使用）。
（2）.用鼠标选择“停止录入”工具栏中的方块，停止录入宏。
（3）.选择Excel选单下的“工具→宏→Visual Basic编辑器”选项。
（4）.在“Visual Basic编辑器”左上角的VBA Project中用鼠标双击VBAProject（Personal.xls）打开“模块→Module1”。
注意：
你的模块可能不是Module1 ,也许是Module2、Module3。
（5）.在右侧的代码窗口中将Personal.xls－Module1（Code）中的代码更改为：
Sub MyMacro（ ）
OldValue = Val（ActiveCell.Value）
InputValue = InputBox（“输入数值，负数前输入减号”,“小小计算器”）
ActiveCell.Value = Val（OldValue＋InputValue）
End Sub
（6）.关闭Visual Basic编辑器。
编辑完毕，你可以试试刚刚编辑的宏，按下Shift＋Ctrl＋J键，输入数值并按下“确定”键。
（这段代码只提供了加减运算，借以抛砖引玉。
）
&返回&
15.怎样保护表格中的数据
假设要实现在合计项和小计项不能输入数据，由公式自动计算。
首先，输入文字及数字，在合计项F4至F7单元格中依次输入公式：
=SUM （B4∶E4）、=SUM（B5∶E5）、=SUM（B6∶E6）、=SUM（B7∶E7），在小计项B8至F8单元格中依次输入公式：
=SUM（B4∶B7）、=SUM（C4∶C7）、=SUM（D4∶D7）、=SUM（E4∶E7）、=SUM（F4∶F7）。
在默认情况下，整个表格的单元格都是锁定的，但是，由于工作表没有被保护，因此锁定不起作用。
选取单元格A1∶F8，点击“格式→单元格”选单，选择“保护”选项，消除锁定复选框前的对勾，单击确定。
然后，再选取单元格F4∶F7和B8∶F8，点击“格式→单元格”选单，选择“保护”选项，使锁定复选框选中，单击确定，这样，就把这些单元格锁定了。
接着，点击“工具→保护→保护工作表”选单，这时，会要求你输入密码，输入两次相同的密码后，点击确定，工作表就被保护起来了，单元格的锁定也就生效了。
今后，可以放心地输入数据而不必担心破坏公式。
如果要修改公式，则点击“工具→保护→撤消保护工作表”选单，这时，会要求你输入密码，输入正确的密码后，就可任意修改公式了。
&返回&
16．如何避免Excel中的错误信息
在Excel中输入或编辑公式后，有可能不能正确计算出结果，Excel将显示一个错误信息，引起错误的原因并不都是由公式本身有错误产生的。
下面我们将介绍五种在Excel中常出现的错误信息，以及如何纠正这些错误。
错误信息1—＃＃＃＃
输入到单元格中的数据太长或单元格公式所产生的结果太大，在单元格中显示不下时，将在单元格中显示＃＃＃＃。
可以通过调整列标之间的边界来修改列的宽度。
如果对日期和时间做减法，请确认格式是否正确。
Excel中的日期和时间必须为正值。
如果日期或时间产生了负值，将在整个单元格中显示＃＃＃＃。
如果仍要显示这个数值，请单击“格式”菜单中的“单元格”命令，再单击“数字”选项卡，然后选定一个不是日期或时间的格式。
错误信息2—＃DIV/0!
输入的公式中包含明显的除数0，例如－120/0，则会产生错误信息DIV/0!
。
或在公式中除数使用了空单元格（当运算对象是空白单元格，Excel将此空值解释为零值）或包含零值单元格的单元格引用。
解决办法是修改单元格引用，或者在用作除数的单元格中输入不为零的值。
错误信息3—＃VALUE!
当使用不正确的参数或运算符时，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息＃VALUE!
。
在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。
这时应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。
例如，单元格B3中有一个数字，而单元格B4包含文本，则公式=B3＋B4将返回错误信息＃VALUE!
。
错误信息4—＃NAME?
在公式中使用了Excel所不能识别的文本时将产生错误信息＃NAME?
。
可以从以下几方面进行检查纠正错误：
（1）如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在。
在“插入”菜单中指向“名称”，再单击“定义”命令，如果所需名称没有被列出，请使用“定义”命令添加相应的名称。
（2）如果是名称，函数名拼写错误应修改拼写错误。
（3）确认公式中使用的所有区域引用都使用了冒号（：
）。
例如：
SUM（A1：
C10）。
注意将公式中的文本括在双引号中。
错误信息5— ＃NUM!
当公式或函数中使用了不正确的数字时将产生错误信息＃NUM!
。
要解决问题首先要确认函数中使用的参数类型正确。
还有一种可能是由公式产生的数字太大或太小，Excel不能表示，如果是这种情况就要修改公式，使其结果在－1×10307和1×10307之间。
&返回&
17.不用编程--Excel公式也能计算个人所得税
个人所得税的计算看起来比较复杂，似乎不用VBA宏编程而只用公式来计算是一件不可能的事。
其实，Excel提供的函数公式不但可以计算个人所得税，而且还有很大的灵活：
可以随意改变不扣税基数，随意改变各扣税分段界限值及其扣税税率（说不定以后调整个人所得税时就可以用到。
）
不管是编程还是使用公式，都得将个人所得税的方法转化为数学公式，并且最好将这个公式化简，为以后工作减少困难。
以X代表你的应缴税（减去免税基数）的工薪收入（这里的个人所得税仅以工薪为例），Tax代表应缴所得税，那么：
当500＜X≤2000则TAX=（X-500）*10+500*5=>TAX=X*10-25
当2000＜X≤5000则TAX=（X-2000）*15+2000*10=>TAX=X*15-125
．．．．．．
依此类推，通用公式为：
个人所得税=应缴税工薪收入*该范围税率-扣除数
在此,扣除数=应缴税工薪收入上一范围上限*该范围税率-上一范围扣除数
其实只有四个公式，即绿色背景处。
黄色背景处则为计算时输入数据的地方。
各处公式设置即说明如下：
E3：
=C3*D3-C3*D2+E2
E4-E10：
根据E3填充得到，或者拷贝E3粘贴得到
C15：
=IF（B15>$B$12,B15-$B$12,0）如果所得工薪大于不扣税基数，则应纳税工薪为工薪减去为零不扣税基数，否则，应纳税工薪零。
D15：
=VLOOKUP（C15,$C$2:
$C$10,1）查阅应纳税工薪属于哪个扣税范围。
E15：
=C15*VLOOKUP（D15,$C$2:
$E$10,2）-VLOOKUP（D15,$C$2:
$E$10,3）查阅该扣税范围扣税税率和应减的扣除数。
这里主要用到VLOOKUP函数，可查阅帮助获取更多信息。
C15，D15的公式可以合并到E15中，那样可读性会差很多，但表格会清晰一些。
合并后公式：
=IF（B15>$B$12,B15-$B$12,0）*VLOOKUP（VLOOKUP（IF（B15>$B$12,B15-$B$12,0）,$C$2:
$C$10,1）,$C$2:
$E$10,2）-VLOOKUP（VLOOKUP（IF（B15>$B$12,B15-$B$12,0）,$C$2:
$C$10,1）,$C$2:
$E$10,3）实际上是将公式中出现的C15，D15用其公式替代即可。
&返回&
18.用EXCEL轻松处理学生成绩
期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。
流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。
有了EXCEL，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！
我迅速地打开电脑，启动EXCEL2000，录入学生的考试成绩，如图1所示。
然后在J2单元格处输入公式"=sum（c2:
i2）",然后拖动填充柄向下填充，便得到了每人的总分。
接着在k2单元格处输入公式"=average（c2:
i2）"，然后拖动填充柄向下填充，便得到了每人的平均分。
图1
平均分只需保留一位小数，多了没用。
所以选中第k列，用鼠标右键单击，从弹出的快捷菜单中选"设置单元格格式（F）…",如图2所示，在数字标签中选中"数值"，小数位数设置为1位。
图2
下面按总分给学生排出名次。
在L2单元格处输入公式"RANK（J2,J$2:
J$77,0）",然后拖动填充柄向下填充，即可得到每人在班中的名次（请参考图1）。
说明：
此处排名次用到了RANK函数，它的语法为：
RANK（number,ref,order）
其中number为需要找到排位的数字。
Ref为包含一组数字的数组或引用。
Ref中的非数值型参数将被忽略。
Order为一数字，指明排位的方式。
·如果order为0或省略，MicrosoftExcel将ref当作按降序排列的数据清单进行排位。
·如果order不为零，MicrosoftExcel将ref当作按升序排列的数据清单进行排位。
最后，单击L1单元格，然后在“工具”菜单中选“排序”－＞“升序”，即可按照名次顺序显示各学生成绩。
另外，我们还希望把不及格的学科突出显示，最好用红色显示。
于是拖拉选择C2:
E78（即所有学生语、数、外三科成绩），然后执行"格式"菜单下"条件格式"命令，弹出"条件格式对话框"。
我们把条件设为小于72分的用红色显示（因为这三科每科总分为120分），点击"格式"按钮，把颜色设为红色。
再按"确定"按钮。
然后用同样的方法把理、化、政、历四科小于60分的也用红色显示（因为这四科每科总分为100分）。
下面我们来统计各科的分数段以及及格率、优生率、综合指数等。
下面我们来统计各科的分数段以及及格率、优生率、综合指数等。
（1）60分以下人数：
在C78单元格处输入公式"=COUNTIF（C2:
C77,"<60"）"，拖动填充柄向右填充至I78单元格处；
（2）60分~69分人数：
在C79单元格处输入公式"=COUNTIF（C2:
C77,">=60"）-COUNTIF（C2:
C77,">=70"）"，拖动填充柄向右填充；
（3）70分~79分人数：
在C80单元格处输入公式"=COUNTIF（C2:
C77,">=70"）-COUNTIF（C2:
C77,">=80"）"，拖动填充柄向右填充；
（4）80分~89分人数：
在C81单元格处输入公式"=COUNTIF（C2:
C77,">=80"）-COUNTIF（C2:
C77,">=90"）"，拖动填充柄向右填充；
（5）90分以上人数：
在C82单元格处输入公式"=COUNTIF（C2:
C77,">=90"）"，拖动填充柄向右填充；
（6）平均分：
在C83单元格处输入公式"=AVERAGE（C2:
C77）",拖动填充柄向右填充至I83；
（7）最高分：
在C84单元格处输入公式"=MAX（C2:
C77）",拖动填充柄向右填充至I84；
（8）低分率：
是指各科40分以下人数与总人数的比值。
在C85单元格处输入公式"=COUNTIF（C2:
C77,"<=40"）/COUNT（C2:
C77）*100"，拖动填充柄向右填充至I85；
（9）及格率：
语、数、外三科及格分为72分，所以在C86单元格处输入公式"=（COUNTIF（C2:
C77,">=72"）/COUNT（C2:
C77））*100"，并拖动填充柄向右填充至E86；而理、化、政、历等四科及格分60分，所以在F86单元格处输入公式"=（COUNTIF（F2:
F77,">=60"）/COUNT（F2:
F77））*100"，并拖动填充柄向右填充至I86；
（10）优生率：
语、数、外三科96分以上为优生，所以在C87单元格处输入公式"=（COUNTIF（C2:
C77,">=96"）/COUNT（C2:
C77））*100"，拖动填充柄向右填
升级会员 