Excel使⽤技巧-----轻松输⼊⾝份证号码
Excel使⽤技巧
----- 轻松输⼊⾝份证号码
当我们在Excel中输⼊⾝份证号码时，由于⾝份证是18位数字，超过了Excel单元格的默认值，因此会⾃动改变记数格式⽽变成类似1.23457E+17的格式。那么，怎样才能保证输⼊的⾝份证号码是正确的呢？有没有什么简便⽅法呢？下⾯就告诉⼤家两个⼩技巧。
⼀、选中要输⼊⾝份证号码的单元格，然后点击右键选择设置单元格格式，选择其中的⽂本格式点击确定退出。怎么样？已经是正确的了。
⼆、⼤家知道，⾝份证的前六位代表的是地域信息，如果是批量输⼊⾝份证号，反复的去输⼊这六位数岂不是很⿇烦。当你看过下⾯的技巧之后，你就会感到批量输⼊也轻松。
依然是选中要输⼊⾝份证号码的单元格，然后点击右键选择设置单元格格式，选择其中的⾃定义，在类型的空格当中输⼊六位地域代码如：123456，然后输⼊12个0（如果都是19××年出⽣的，还可以在123456后边输⼊19，再输⼊10个0就可以了），点击确定退出，再输⼊12（10）位个⼈代码。是不是完整的⾝份证号码出来了？⽅便很多吧！
看不懂的请看视频教程
www.doczj/doc/403084df89eb172ded63b736.html /b/12708342-38716548.html
Excel使⽤技巧2008-10-03 11:23
分类：办公⾃动化
字号：⼤中⼩
在⼯作当中⽤电⼦表格来处理数据将会更加迅速、⽅便，⽽在各种电⼦表格处理软件中Excel以其功能强⼤、操作⽅便著称，赢得了⼴⼤⽤户的青睐。虽然Excel使⽤很简单，不过真正能⽤好Excel的⽤户并不多，很多⼈⼀直停留在录⼊数据的⽔平，本⽂将向你介绍⼀些⾮常使⽤的技巧，掌握这些技巧将⼤⼤提⾼你的⼯作效率.
⒈快速定义⼯作簿格式
⾸先选定需要定义格式的⼯作簿范围，单击“格式”菜单的“样式”命令，打开“样式”对话框；然后从“样式名”列表框中选择合适
的“样式”种类，从“样式包括”列表框中选择是否使⽤该种样式的数字、字体、对齐、边框、图案、保护等格式内容；单击“确定”按钮，关闭“样式”对话框，Excel⼯作簿的格式就会按照⽤户指定的样式发⽣变化，从⽽满⾜了⽤户快速、⼤批定义格式的要求。
⒉快速复制公式
复制是将公式应⽤于其它单元格的操作，最常⽤的有以下⼏种⽅法：
⼀是拖动制复制。操作⽅法是：选中存放公式的单元格，移动空⼼⼗字光标⾄单元格右下⾓。待光标变成⼩实⼼⼗字时，按住⿏标左键沿列（对⾏计算时）或⾏（对列计算时）拖动，⾄数据结尾完成公式的复制和计算。公式复制的快慢可由⼩实⼼⼗字光标距虚框的远近来调节：⼩实⼼⼗字光标距虚框越远，复制越快；反之，复制越慢。
也可以输⼊复制。此法是在公式输⼊结束后⽴即完成公式的复制。操作⽅法：选中需要使⽤该公式的所有单元格，⽤上⾯介绍的⽅法输⼊公式，完成后按住Ctrl键并按回车键，该公式就被复制到已选中的所有单元格。
还可以选择性粘贴。操作⽅法是：选中存放公式的单元格，单击Excel ⼯具栏中的“复制”按钮。然后选中需要使⽤该公式的单元格，在选中区域内单击⿏标右键，选择快捷选单中的“选择性粘贴”命令。打开“选择性粘贴”对话框后选中“粘贴”命令，单
击“确定”，公式就被复制到已选中的单元格。
⒊快速显⽰单元格中的公式
如果⼯作表中的数据多数是由公式⽣成的，如果想要快速知道每个单元格中的公式形式，可以这样做：⽤⿏标左键单击“⼯具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗⼝选项”栏下的“公式”项有
效，单击“确定”按钮。这时每个单元格中的公式就显⽰出来了。如果想恢复公式计算结果的显⽰，再设置“窗⼝选项”栏下的“公式”项失效即可。
⒋快速删除空⾏
有时为了删除Excel⼯作簿中的空⾏，你可能会将空⾏⼀⼀出然后删除，这样做⾮常不⽅便。你可以利⽤“⾃动筛选”功能来简单实现。先在表中插⼊新的⼀⾏（全空），然后选择表中所有的⾏，选择“数据”菜单中的“筛选”，再选择“⾃动筛选”命令。在每⼀列的项部，从下拉列表中选择“空⽩”。在所有数据都被选中的情况下，选择“编辑”菜单中的“删除⾏”，然后按“确定”即可。所有的空⾏将被删去。插⼊⼀个空⾏是为了避免删除第⼀⾏数据。
⒌⾃动切换输⼊法
当你使⽤Excel 2000编辑⽂件时，在⼀张⼯作表中通常是既有汉字，⼜有字母和数字，于是对于不同的单元格，需要不断地切换中英⽂输⼊⽅式，这不仅降低了编辑效率，⽽且让⼈不胜其烦。在此，笔者介绍⼀种⽅法，让你在Excel 2000中对不同类型的单元格，实现输⼊法的⾃动切换。
新建或打开需要输⼊汉字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输⼊法模式”选项卡，在“模式”下拉列表框中选择“打开”，单击“确定”按钮。
选择需要输⼊字母或数字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输⼊法模式”选项卡，在“模式”下拉列表框中选择“关闭（英⽂模式）”，单击“确定”按钮。
之后，当插⼊点处于不同的单元格时，Excel 2000能够根据我们进⾏的设置，⾃动在中英⽂输⼊法间进⾏切换。就是说，当插⼊点处于刚才我们设置
为输⼊汉字的单元格时，系统⾃动切换到中⽂输⼊状态，当插⼊点处于刚才我们设置为输⼊数字或字母单元格时，系统⼜能⾃动关闭中⽂输⼊法。
⒍⾃动调整⼩数点
excel怎么自动求减法如果你有⼀⼤批⼩于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，录⼊学⽣的考试成绩。然后在J2单元格处输⼊公式"=sum(c2:i2)"，然后拖动填充柄向下填充，便得到了每⼈的
总分。接着在k2单元格处输⼊公式"=average(c2:i2)"，然后拖动填充柄向下填充，便得到了每⼈的平均分。
平均分只需保留⼀位⼩数，多了没⽤。所以选中第k列，⽤⿏标右键单击，从弹出的快捷菜单中选"设置单元格格式（F）…"，在数字标签中选中"数值"，⼩数位数设置为1位。
下⾯按总分给学⽣排出名次。
在L2单元格处输⼊公式"RANK(J2,J$2:J$77,0)"，然后拖动填充柄向下填充，即可得到每⼈在班中的名次（请参考图1）。
说明：此处排名次⽤到了RANK函数，它的语法为：
RANK(number,ref,order)
其中number为需要到排位的数字。
Ref为包含⼀组数字的数组或引⽤。Ref 中的⾮数值型参数将被忽略。
Order为⼀数字，指明排位的⽅式。
·如果order 为0 或省略，Microsoft Excel 将ref 当作按降序排列的数据清单进⾏排位。
·如果order 不为零，Microsoft Excel 将ref 当作按升序排列的数据清单进⾏排位。
最后，单击L1单元格，然后在“⼯具”菜单中选“排序”－＞“升序”，即可按照名次顺序显⽰各学⽣成绩。
另外，我们还希望把不及格的学科突出显⽰，最好⽤红⾊显⽰。于是拖拉选择C2:E78(即所有学⽣语、数、外三科成绩)，然后执⾏"格式"菜单下"条件格式"命令，弹出"条件格式对话框"。我们把条件设为⼩于72分的⽤红⾊显⽰（因为这三科每科总分为120分），点击"格式"按钮，把颜⾊设为红⾊。再按"确定"按钮。然后⽤同样的⽅法把理、化、政、历四科⼩于60分的也⽤红⾊显⽰（因为这四科每科总分为100分）。
下⾯我们来统计各科的分数段以及及格率、优⽣率、综合指数等。