Excel技巧复习进程.doc

《Excel技巧复习进程.doc》由会员分享，可在线阅读，更多相关《Excel技巧复习进程.doc（32页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Good is good, but better carries it.精益求精，善益求善。Excel技巧-Excel技巧精选Excel快捷键31.分数输入方法52.序列“001”的输入53.日期快捷输入54.填充条纹55.多张工作表中输入相同的内容66.不连续单元格填充同一数据67.在单元格中显示公式68.利用Ctrl*选取文本69.快速清除单元格的内容610.合并单元格内容611.条件显示712.自定义格式713.自动切换输入法914.每次选定同一单元格915.生成备份工作薄916.让序号原地不动917.批量删除空行918.避免错误信息1019.重复表格标题行1120.利用IF函数避免出错

2、信息1121.同时打开多个工作薄1222.滚屏时固定表格头/冻结窗格1223.分割、合并列1224.自动调整列宽1225.平均分布各行/列1227.自定义输入数据下拉列表1328.批量插入固定字符1329.正确输入身份证号码1330.冻结行列标题1331.拆分窗口1332.行列快速转换1333.防止数据重复输入1434.分区域锁定1435.共享工作簿1436.删除通配符*或?1437.选定指定个数单元格1438.数字居中小数点对齐1539.Excel表格转换为图片1540.钩（）的快捷输入1541.将单元格中的数全部变成万元表示1542.更改Excel默认行列标签颜色1542.工作簿间引用及自

3、动更新数据1543.单元格输入时间后前面自动加了等号！1544.未被发现的两个日期格式符号1545.单元格格式自定义中“!”的作用1646.奇数行、偶数行求和1647.日期上、中、下旬区分1648.Excel中为汉字加注拼音1648.男、女性别的快捷输入1649.“-”符号作用1650.增加Excel的后悔次数1651.求平均分时显示出被去掉的最高、最低分数1652.复制、粘贴中回车键妙用1653.求平均值时只对不等于零的数求均值1654.固定数据输入焦点1655.单元格信息输入提示1756.不输入公式直接查看结果17Excel技巧精选Excel快捷键Alt+：显示“样式”对话框Alt+=：用

4、SUM函数插入“自动求和”公式Alt+0165：输入日圆符号（完成输入后放开Alt，Word中也可）Alt+41420：输入（完成输入后放开Alt）Alt+Enter：在单元格中换行Alt+PageDown：向右移动一屏Alt+PageUp：向左移动一屏Alt+向下键：显示清单的当前列中的数值下拉列表Ctrk+Shift+PageUp：选中当前工作表和上一张工作表Ctrl+-：显示删除行、列、单元格对话框Ctrl+;：输入日期Ctrl+：选取由选中区域的公式直接引用的所有单元格Ctrl+：选取包含直接引用活动单元格的公式的单元格Ctrl+：显示插入行、列、单元格对话框Ctrl+0：隐藏选中单元

5、格或区域所在的列Ctrl+1：显示“单元格格式”对话框Ctrl+5：应用或取消删除线Ctrl+6：在隐藏对象，显示对象和显示对象占位符之间切换。Ctrl+9：隐藏选中单元格或区域所在的行Ctrl+A：选中整张工作表。Ctrl+Alt+向右键：在不相邻的选中区域中，向右切换到下一个选中区域。Ctrl+Alt+向左键：向左切换到下一个不相邻的选中区域Ctrl+B：应用或取消加粗格式Ctrl+C,再Ctrl+C：显示MicroftOffice剪贴板（多项复制和粘贴）Ctrl+D：向下填充。Ctrl+Delete：删除插入点到行末的文本。Ctrl+End：移动到工作表的最后一个单元格，该单元格位于数据

6、所占用的最右列的最下行中Ctrl+Enter：用当前输入项填充选中的单元格区域Ctrl+F3：定义名称Ctrl+Home：移动到工作表的开头Ctrl+I：应用或取消字体倾斜格式Ctrl+K：插入超链接Ctrl+PageDown：开始一条新的空白记录。Ctrl+PageDown：取消选中多张工作表CTRL+PageDown：移动到工作薄中的下一张工作表Ctrl+PageUp：移动到工作薄中的上一张工作表或选中其它工作表Ctrl+P或Ctrl+Shift+F12：显示“打印”对话框。Ctrl+R：向右填充。Ctrl+Shift+！：应用带两位小数位，使用千位分隔符且负数用负号（-）表示的“数字”格

7、式。Ctrl+Shift+#：应用含年，月，日的“日期”格式Ctrl+Shift+$：应用带两个小数位的“货币”格式（负数在括号内）Ctrl+Shift+%：应用不带小数位的“百分比”格式Ctrl+Shift+&：对选中单元格应用外边框Ctrl+Shift+）：取消先中区域内的所有隐藏列的隐藏状态。Ctrl+Shift+*：选中活动单元格周围的当前区域（包围在空行和空列中的数据区域）。在数据透视表中，选中整个数据透视表。Ctrl+Shift+：插入时间Ctrl+Shift+：应用含小时和分钟并标明上午或下午的“时间”格式Ctrl+Shift+：应用带两位小数位的“科学记数”数字格式Ctrl+S

8、hift+_：取消选中单元格的外边框。Ctrl+Shift+：选取由选中区域中的公式直接或间接引用的所有单元格Ctrl+Shift+：选取包含直接或间接引用单元格的公式的单元格。Ctrl+Shift+：应用“常规”数字格式Ctrl+Shift+：插入空白单元格Ctrl+Shift+Z：显示“自动更正”智能标记时，撤消或恢复上次的自动更正。Ctrl+Shift+选中含在批注的所有单元格Ctrl+U：应用或取消下划线Ctrl+V：粘贴复制的单元格Ctrl+X：剪切选中的单元格Ctrl+空格：选中整列CTRL+向上键或+向左键（打印预览）：缩小显示时，滚动到第一页CTRL+向下键或+向右键（打印预览

9、）：缩小显示时，滚动到最后一页End：移动到窗口右下角的单元格End+箭头键：在一行或一列内以数据块为单位移动F2：关闭了单元格的编辑状态后，将插入点移动到编辑栏内F3：将定义的名称粘贴到公式中F4或Ctrl+Y：重复上一次操作F5：显示“定位”对话框F6：切换到被拆分的工作表中的下一个窗格F7：显示“拼写检查”对话框F9：计算所有打开的工作簿中的所有工作表Home：移动到行首或窗口左上角的单元格PageDown：移动到前10条记录的同一字段Shift+Ctrl+PageDown：选中当前工作表和下一张工作表SHIFT+F11或ALT+SHIFT+F1：插入新工作表Shift+F2：编辑单元格

10、批注Shift+F3：在公式中，显示“插入函数”对话框。Shift+F4：重复上一次查找操作Shift+F5：显示“查找”对话框。Shift+F6：切换到被拆分的工作表中的上一个窗格Shift+F9：计算活动工作表。Shift+空格：选中整行（英文输入法状态才能实现）1.分数输入方法如果直接输入“1/5”，系统会将其变为“1月5日”，解决办法是：先输入“0”，然后输入空格，再输入分数“1/5”。2.序列“001”的输入如果直接输入“001”，系统会自动判断001为数据1，解决办法是：首先输入“”（西文单引号），然后输入“001”。3.日期快捷输入如果要输入“4月5日”，直接输入“4/5”，再敲

11、回车就行了。如果要输入当前日期，按一组合键“Ctrl+；”即可。4.填充条纹如要在工作簿中加入漂亮的横条纹，可以利用对齐方式中的“填充”功能实现。先在一单元格内填入“*”或“”等符号，然后单击此单元格，向右拖动鼠标，选中横向若干单元格，单击“格式”菜单，选中“单元格”命令，在弹出的“单元格格式”菜单中，选择“对齐”选项卡，在水平对齐下拉列表中选择“填充”，单击“确定”按钮。5.多张工作表中输入相同的内容几个工作表中同一位置填入同一数据时，可以选中一张工作表，然后按住Ctrl键，再单击窗口左下角的Sheet1、Sheet2.来直接选择需要输入相同内容的多个工作表，接着在其中的任意一个工作表中输入

12、这些相同的数据，此时这些数据会自动出现在选中的其它工作表之中。输入完毕之后，再次按下键盘上的Ctrl键，然后使用鼠标左键单击所选择的多个工作表，解除这些工作表的联系。6.不连续单元格填充同一数据选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部都选中了。在编辑区中输入数据，然后按住Ctrl键，同时敲一下回车，在所有选中的单元格中都出现了这一数据。7.在单元格中显示公式如果工作表中的数据多数是由公式生成的，想要快速知道每个单元格中的公式形式，以便编辑修改，可以这样做：用鼠标左键单击“工具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗口选项”栏

13、下的“公式”项有效，单击“确定”按钮（如图2）。这时每个单元格中的分工就显示出来了。如果想恢复公式计算结果的显示，反向操作即可。8.利用Ctrl*选取文本如果一个工作表中有很多数据表格时，可以通过选定表格中某个单元格，然后按下Ctrl*键可选定整个表格。Ctrl*选定的区域为：根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格，并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。9.快速清除单元格的内容如果要删除内容的单元格中的内容和它的格式和批注，就不能简单地应用选定该单元格，然后按Delete键的方法了。要彻底清除单元格,可用以下方法：

14、选定想要清除的单元格或单元格范围；单击“编辑”菜单中“清除”项中的“全部”命令，这些单元格就恢复了本来面目。10.合并单元格内容根据需要，有时想把B列与C列的内容进行合并，如果行数较少，可以直接用“剪切”和“粘贴”来完成操作，但如果有几万行，就不能这样办了。办法1：在C行后插入一个空列（如果D列没有内容，就直接在D列操作），在1中输入“=B1&C1”，D1列的内容就是B、C两列的和了。选中D1单元格，用鼠标指向单元格右下角的小方块“”，当光标变成后，按住鼠标拖动光标向下拖到要合并的结尾行处，就完成了B列和C列的合并。这时先不要忙着把B列和C列删除，先要把D列的结果复制一下，再用“选择性粘贴”命

15、令，将数据粘贴到一个空列上。这时再删掉B、C、D列的数据。实例：用Autocad绘图时，有人喜欢在Excel中存储坐标点，在绘制曲线时调用这些参数。存放数据格式为“X，Y”的形式，首先在Excel中输入坐标值，将X坐标值放入A列，Y坐标值放入到B列，然后利用“&”将A列和B列合并成C列，在C1中输入：=A1&，&B1，此时C1中的数据形式就符合要求了，再用鼠标向下拖动C1单元格，完成对A列和B列的所有内容的合并。办法2：利用Concatenate函数。此函数的作用是将若干文字串合并到一个字串中，具体操作为“=Concatenate（B1,C1）”。实例：假设在某一河流生态调查工作表中，B2包含

16、“物种”、B3包含“河鳟鱼”，B7包含总数45，那么：输入“=Concatenate（本次河流生态调查结果：，B2，B3，为，B7，条/公里。）”计算结果为：本次河流生态调查结果：河鳟鱼物种为45条/公里。11.条件显示我们知道，利用If函数，可以实现按照条件显示。一个常用的例子，就是教师在统计学生成绩时，希望输入60以下的分数时，能显示为“不及格”；输入60以上的分数时，显示为“及格。这样的效果，利用IF函数可以很方便地实现。假设成绩在A2单元格中，判断结果在A3单元格中。那么在A3单元格中输入公式：=If（A260，“不及格”，“及格”）同时，在IF函数中还可以嵌套IF函数或其它函数。例如

17、，如果输入：=If（A260，“不及格”，If（A2=90，“及格”，“优秀）就把成绩分成了三个等级。如果输入=If（A260，“差，If（A2=70，“中”，If（A20，SUM（A1：A5），0）此式就利用了嵌套函数，意思是，当A1至A5的和大于0时，返回这个值，如果小于0，那么就返回0。提醒：以上的符号均为半角，而且IF与括号之间也不能有空格。12.自定义格式Excel中预设了很多有用的数据格式，基本能够满足使用的要求，但对一些特殊的要求，如强调显示某些重要数据或信息、设置显示条件等，就要使用自定义格式功能来完成。Excel的自定义格式使用下面的通用模型：正数格式，负数格式，零格式，文本

18、格式，在这个通用模型中，包含三个数字段和一个文本段：大于零的数据使用正数格式；小于零的数据使用负数格式；等于零的数据使用零格式；输入单元格的正文使用文本格式。我们还可以通过使用条件测试，添加描述文本和使用颜色来扩展自定义格式通用模型的应用。（1）使用颜色要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel识别的颜色名为：黑色、红色、白色、蓝色、绿色、青色和洋红。Excel也识别按颜色X指定的颜色，其中X是1至56之间的数字，代表56种颜色。（2）添加描述文本要在输入数字数据之后自动添加文本（如度量衡单位），使用自定义格式为：文本内容；要在输入数字数据之前

19、自动添加文本，使用自定义格式为：文本内容。符号的位置决定了Excel输入的数字数据相对于添加文本的位置。（3）创建条件格式可以使用六种逻辑符号来设计一个条件格式：（大于）、=（大于等于）、（小于）、=（小于等于）、=（等于）、（不等于），如果你觉得这些符号不好记，就干脆使用“”或“=”号来表示。由于自定义格式中最多只有3个数字段，Excel规定最多只能在前两个数字段中包括2个条件测试，满足某个测试条件的数字使用相应段中指定的格式，其余数字使用第3段格式。如果仅包含一个条件测试，则要根据不同的情况来具体分析。自定义格式的通用模型相当于下式：；0正数格式；50000$#,#0.00_);红色999

20、999#,#0,_M吨;999#,_K_M千克;#_K克”可以看到，使用条件格式，千分符和均匀间隔指示符的组合，不用增加公式的数目就可以改进工作表的可读性和效率。另外，我们还可以运用自定义格式来达到隐藏输入数据的目的，比如格式；0只显示负数和零，输入的正数则不显示；格式“；”则隐藏所有的输入值。自定义格式只改变数据的显示外观，并不改变数据的值，也就是说不影响数据的计算。灵活运用好自定义格式功能，将会给实际工作带来很大的方便。13.自动切换数入法在一张工作表中，往往是既有数据，又有文字，这样在输入时就需要来回在中英文之间反复切换输入法，非常麻烦。如果你要输入的东西很有规律性，比如这一列全是单词，

21、下一列全是汉语解释，你可以用以下方法实现自动切换。方法是：（1）选中要输入英文的列，单击“数据”菜单，选择“有效性.”命令，在弹出的“数据有效性”对话框中，选中“输入法模式”选项卡，在“模式”框中选择“关闭（英文模式）”命令，单击“确定”按钮。（2）选中要输入汉字的列，在“有效数据”对话框中，单击“IME模式”选项卡，在“模式”框中选择“打开”命令，单击“确定”按钮。这样，当光标在前一列时，可以输入英文，在下一列时，直接可以输入中文，从而实现了中英文输入方式之间的自动切换。14.每次选定同一单元格为了测试某个公式，需要在某个单元格内反复输入多个测试值。但是，每次输入一个值后按下Enter键查看

22、结果后，活动单元格就会默认移到下一个单元格上，必须用鼠标或上移箭头重新选定原单元格，极不方便。如果你按“Ctrl+Enter”组合键，则题会立刻迎刃而解，既能查看结果，而活动单元格仍为当前单元格。15.生成备份工作薄对新(老)工作薄执行“文件保存(另存为)”命令，打开“另存为”对话框，按右上角的“工具”旁的下拉按钮，选“常规选项”，在随后弹出的对话框中，选中“生成备份选项”，确定保存。以后修改该工作薄(A.Xls)后再保存，系统会自动生成一份名称为“A的备份.Xlk”的备份工作薄，且能直接打开使用。友情提醒：如果将上述功能加到某个模板中，则使用该模板建立工作薄后，就不需要进行上述操作，而快速生

23、成备份工作薄。16.让序号原地不动有时我们对数据进行排序时，序号全乱了。如果在序号列与正文列之间插入一个空列，再排序时，序号就不会乱了。友情提醒：位于序号前面的列中的内容均不发生改变；为了不影响显示美观和正常打印，可将这一空列隐藏起来。17.批量删除空行有时我们需要删除Excel工作薄中的空行，一般做法是将空行一一找出，然后删除。如果工作表的行数很多，这样做就非常不方便。我们可以利用“自动筛选”功能，把空行全部找到，然后一次性删除。做法：先在表中插入新的一个空行，然后按下Ctrl+A键，选择整个工作表，用鼠标单击“数据”菜单，选择“筛选”项中的“自动筛选”命令。这时在每一列的顶部，都出现一个下

24、拉列表框，在典型列的下拉列表框中选择“空白”，直到页面内已看不到数据为止在所有数据都被选中的情况下，单击“编辑”菜单，选择“删除行”命令，然后按“确定”按钮。这时所有的空行都已被删去，再单击“数据”菜单，选取“筛选”项中的“自动筛选”命令，工作表中的数据就全恢复了。插入一个空行是为了避免删除第一行数据。如果想只删除某一列中的空白单元格，而其它列的数据和空白单元格都不受影响，可以先复制此列，把它粘贴到空白工作表上，按上面的方法将空行全部删掉，然后再将此列复制，粘贴到原工作表的相应位置上。18.避免错误信息在Excel中输入公式后，有时不能正确地计算出结果，并在单元格内显示一个错误信息，这些错误的

25、产生，有的是因公式本身产生的，有的不是。下面就介绍一下几种常见的错误信息，并提出避免出错的办法。1）错误值：含义：输入到单元格中的数据太长或单元格公式所产生的结果太大，使结果在单元格中显示不下。或是日期和时间格式的单元格做减法，出现了负值。解决办法：增加列的宽度，使结果能够完全显示。如果是由日期或时间相减产生了负值引起的，可以改变单元格的格式，比如改为文本格式，结果为负的时间量。）错误值：DIV/0!含义：试图除以0。这个错误的产生通常有下面几种情况：除数为0、在公式中除数使用了空单元格或是包含零值单元格的单元格引用。解决办法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。）错误值：

26、VALUE!含义：输入引用文本项的数学公式。如果使用了不正确的参数或运算符，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息VALUE!。解决办法：这时应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如，单元格C4中有一个数字或逻辑值，而单元格D4包含文本，则在计算公式=C4D4时，系统不能将文本转换为正确的数据类型，因而返回错误值VALUE!。）错误值：REF!含义：删除了被公式引用的单元格范围。解决办法：恢复被引用的单元格范围，或是重新设定引用范围。）错误值：N/A含义：无信息可用于所要执行的计算。在建立模型时，用户可以在单元格中输入#N/A，以表

27、明正在等待数据。任何引用含有#N/A值的单元格都将返回#N/A。解决办法：在等待数据的单元格内填充上数据。）错误值：NAME?含义：在公式中使用了Excel所不能识别的文本，比如可能是输错了名称，或是输入了一个已删除的名称，如果没有将文字串括在双引号中，也会产生此错误值解决办法：如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在；如果是名称，函数名拼写错误应就改正过来；将文字串括在双引号中；确认公式中使用的所有区域引用都使用了冒号（：）。例如：SUM（C1：C10）。注意将公式中的文本括在双引号中。）错误值：NUM!含义：提供了无效的参数给工作表函数，或是公式的结果太大或太小而无

28、法在工作表中表示。解决办法：确认函数中使用的参数类型正确。如果是公式结果太大或太小，就要修改公式，使其结果在-110307和110307之间。）错误值：NULL！含义：在公式中的两个范围之间插入一个空格以表示交叉点，但这两个范围没有公共单元格。比如输入：“=Sum(A1:A10C1:C10)”，就会产生这种情况。解决办法：取消两个范围之间的空格。上式可改为“=Sum(A1:A10,C1:C10)”19.重复表格标题行在Excel中，用户不仅可在每页表格中重复表格顶端的标题行，而且还可重复表格左端的标题列。打开需要设置标题行的工作表，选择“文件”菜单中的“页面设置”命令，打开“页面设置”对话框。

29、单击“工作表”标签卡，在“打印标题”选项区中选择相应选项可在打印工作表的每页中都打印重复的标题行或列内容。例如要将表格中特定的行作为每页的行标题时，选择“顶端标题行”选项，如果要在每页有垂直方向的标题时，则选择“左端标题列”。然后在工作表上所需的标题行或列中选择相应的单元格或单元格区域，这时“顶端标题行”和“左端标题列”编辑栏中就会出现所选定的行或列名称。如图2所示。单击“确定”按钮保存设置。20.利用IF函数避免出错信息当我们在C8单元格输入公式：=A8/B8，当B8单元格没有输入数据时，C8单元格则会出现“#DIV/0！”的错误信息。如果将公式改为：=IF(B1=0,A1/B1)，就不会显

30、示错误信息了。同样，如果我们在C8单元格中输入：=A8B8，当A8、B8没有输入数值时，C8则显示出“0”，如果将公式改为：=IF(AND(A2=，B2=),A2B2)，这样，如果A8与B8单元格均没有输入数值时，C8也不会显示现“0”了。21.同时打开多个工作薄执行“文件打开”命令，按住“Shift”键或“Ctrl”键，并用鼠标选择彼此相邻或不相邻的多个文件，然后按“确定”按钮，就可以一次打开选中的多个工作薄。执行“开始运行”命令，在对话框中输入Excel，空格，后面加所要打开工作薄文件的绝对路径(每个文件之间用空格分隔)，确定后也可以打开多个工作薄。新建一个文件夹，将需要打开的多个工作薄文

31、件放在里面，然后打开Excel，执行“工具选项”命令，在“常规”标签中，将替补启动目录用“浏览”的方法指向该文件夹，确定。以后当你再次启动Excel时，就会把那个目录中的所有文件一起打开。启动Excel，打开多个工作薄，执行“文件保存工作区”命令，取名(A.Xlw)、确定保存。以后只要打开该工作区文档，即可打开上述多个工作薄(注意：工作区文件只是保存设置，并不保存文件，所以你必须对每个文件所作的更改另行保存)。22.滚屏时固定表格头/冻结窗格选中要固定的表格头的下一行的任意单元格，点击菜单：窗口冻结窗格，即可实现滚屏时不滚动固定表格头（选定单元格的左侧和上面各行被冻结）。23.分割、合并列如将

32、地址字段和邮编字段合并到一起，假设地址在L行，邮编在M行，插入N行，在N2中输入：=L26&，&M26，回车，然后用N2右下角的填充柄快速填充一下即可合并列；分割列：选中M列，选菜单：数据分列：文本分列向导：从其中选择“分隔符号”，然后在下一步中的“分隔符号”中选中逗号，点击下一步，点击完成即可。24.自动调整列宽选中要调整的列，从Excel菜单栏中选择：格式列最合适的列宽即可。或者选中要调整的列，将鼠标移动到任意被选中的两列的列标交界处，光标会变为带左右箭头形状（就是我们要手动调整列宽的位置），左键双击，OK.25.平均分布各行/列在标尺栏上选中相应的行/列：再拖动其中一个行/列的行宽/列高

33、即可调整所有行或列为同一宽度/高度。另外一种方法就是选中相应的行/列，在行高/列宽对话框中直接输入相应的数值即可。26.不连续单元格填充同一数据启动Excel，选中一个单元格，按住Ctrl键，用鼠标单击其他单元格，就将这些单元格全部选中了，在编辑区中输入数据，然后按住Ctrl键，同时按一下回车，在所有选中的单元格中都出现了同一数据。27.自定义输入数据下拉列表如果在某些单元格中需要输入固定格式的数据（如“职称”等），我们可以通过“数据有效性”建立一下拉列表，来进行选择输入，以方便统一输入的数据。选中需要建立下拉列表的单元格区域，执行“数据有效性”命令，打开“数据有效性”对话框，在“设置”标签下

34、，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“序列”选项，然后在下面“来源”方框中输入序列的各元素（如“高级工程师,工程师,助理工程师,技术员,其他职称”等），确定返回。注意：各元素间请用英文状态下的逗号隔开。28.批量插入固定字符现在新的身份证号码，将旧身份证号码的年份由2位表示改为4位表示，如果我们要将年份的前两位(19)插入旧身份证号码中，可以这样做(假定旧身份号码保存在A列中)：在B1、C1、D1单元格中分别输入公式：=Left(A1,6)、=Right(A1,9)、=B1&19&C1，将上述公式复制到B、C、D列的其它单元格中，即将19插入旧身份证号码中，并显示到D列中。

35、29.正确输入身份证号码即输入文本型数字，不需要计算。方法1：英文状态的单引号加数字混合输入，用于少量数据的录入；方法2：将单元格格式化为文本格式，直接输入数据即可。30.冻结行列标题在拉动滚动条查看一个大的表格时，我们发现列标题、行标题常常被移到窗口外面去了，这样数据与标题常常会对不上号。我们可以将行、列标题冻结起来：如果我们需要将第1行和第1、2列作为标题冻结起来，可以这样操作：选中C2单元格，（选中单元格是关键，从该单元格的左边和上边开始全部冻结，其他操作如插入、拆分单元格同理）执行一下“窗口冻结窗格”命令即可。注意：执行“窗口取消冻结窗格”命令，即可将冻结的窗口解除。如果只冻结A列（或

36、第一行），只需选中B1（或A2）进行操作即可。（原理同上，Excel做出的选择都是选定单元格的左边和上边，B1上边没有，所以冻结的是列，A2左边没有，所以冻结的是行）31.拆分窗口选中窗口中部的某个行（列），执行“窗口折分”命令，即可将当前整个窗口折分为上下（左右）两个区域，然后在每一个窗口中分别浏览到同一工作表中不同区域的数据。注意：如果选中工作表中某个单元格，再执行折分窗口操作，即可将窗口折分为四个区域。32.行列快速转换如果需要要将Excel按行（列）排列的数据，转换为按列（行）排列，可以通过“选择性粘贴”来实现。选中需要转换的数据区域，执行一下“复制”操作；选中保存数据的第一个单元格，

37、执行“编辑选择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“转置”选项，确定返回即可。33.防止数据重复输入员工的身份证号码应该是唯一的，为了防止重复输入，我们用“数据有效性”来提示大家。选中需要建立输入身份证号码的单元格区域（如D3至D14列），执行“数据有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“自定义”选项，然后在下面“公式”方框中输入公式：=Countif(D:D,D3)=1，确定返回。以后在上述单元格中输入了重复的身份证号码时，系统会弹出提示对话框，并拒绝接受输入的号码。34.分区域锁定当多人编辑同一个工作簿

38、文档时，为了防止修改由他人负责填写的数据，我们可以对工作表进行分区域加密。启动Excel，打开相应的工作簿文档，执行“工具保护允许用户编辑区域”命令，打开“允许用户编辑区域”对话框。单击其中的“新建”按钮，打开“新区域”对话框，在“标题”下面的方框中输入一个标题（如“报建”），然后按“引用单元格”右侧的红色按钮，让对话框转换为浮动条，用鼠标选中相应的区域，再按浮动条右侧的红色按钮返回对话框，设置好密码，按下“确定”按钮，再确认输入一次密码，确定返回。重复上述操作，为其他区域设置密码。设置完成后，按下“保护工作表”按钮，加密保护一下工作表即可。35.共享工作簿现在很多单位都建立了内部局域网，我们

39、把Excel文档设置成共享，可以让多人在局域网上同时对一个文档进行编辑操作。启动Excel，打开需要共享的工作簿文档，执行“工具共享工作簿”命令，打开“共享工作簿”对话框，选中其中的“允许多用户同时编辑，同时允许工作簿合并”选项，再确定返回。将设置了共享的工作簿文档，保存的局域网某台电脑的一个共享文件夹中，局域网上的用户即可随时调用编辑。注意：如果在“共享工作簿”对话框中，切换到“高级”选项，设置相应的参数，可以实现更多的共享效果。36.删除通配符*或?编辑替换，“查找内容”处输入“*”或“?”，“替换为”留空。37.选定指定个数单元格方法1：先选第1个有公式的单元格（比如说A1），然后按F8

40、，在选最后一个单元格（比如说A100），按组合键CTRL+D则可。方法2：先选第1个有公式的单元格（比如说A1），拖动滚动条到指定单元格，此时按住Shift键并单击该单元格，按组合键CTRL+D则可。38.数字居中小数点对齐选中某列，点击“居中”工具按钮；格式单元格自定义，输入“?.?”或“?.0?”类型的字符，问号个数可根据实际添减。39.Excel表格转换为图片按下Shift键的同时点击“编辑”菜单，原来的复制和粘贴就会变成“复制图片”和“粘贴图片”。利用这一功能，可以将一个数据表以图片的形式进行复制，从而将其转换为图片。40.钩（）的快捷输入按住ALT键输入41420后放开ALT键即出现

41、“”。41.将单元格中的数全部变成万元表示自定义单元格格式：0!.000042.更改Excel默认行列标签颜色桌面-属性-外观-项目-高级_已选定的项目，设置颜色。42.工作簿间引用及自动更新数据新建工作簿，输入数据（作为源工作簿），保存，保存类型选wk4(1-2-3)(*.wk4)，文件名为book1；再建立一个工作簿，输入要引用源工作簿的公式，保存文件名称为book2；关闭工作簿；再打开book2看是不是不出现链接对话框，或打开book1，修改一下数据，保存，关闭，再打开book2，是不是已经更新了数据。另：引用多个源工作簿也可以，但源工作簿要保存wk4(1-2-3)(*.wk4)类型，我

42、已试过，大家可以试试看。以上是winXP+excel2003，较低版本的朋友，请把保存类型选为：wk3或wk1。43.单元格输入时间后前面自动加了等号！工具选项1-2-3帮助转换lotus123公式，去掉钩。44.未被发现的两个日期格式符号bb或bbbb：如2005-1-1设置自定义格式bb或bbbb,结果为48或2548,与2005年份的差为543，发现任何日期这个差数是固定的，经查询有关资料，公元前544元是佛历元年，可能与佛历有关（佛教的英文是B开头的）e：对日期设置自定义格式e，结果是公历的四位年份，为2005-1-1显示为2005,完全可以代替yyyy格式符号以上是在EXCEL200

43、3下发现的，经检验在EXCEL2000下没有。45.单元格格式自定义中“!”的作用“！”的作用是把后面所带的字符作为符号处理46.奇数行、偶数行求和奇数行：=Sumproduct(A1:A1000)*Mod(Row(A1:A1000),2)偶数行：=Sumproduct(A1:A1000)*Not(Mod(Row(A1:A1000),2)47.日期上、中、下旬区分=Lookup(Day(A1),0,11,21,31,上旬,中旬,下旬,下旬)48.Excel中为汉字加注拼音格式_拼音信息_编辑(显示或隐藏)48.男、女性别的快捷输入自定义单元格格式，=0男;=1女;则可实现输入0显示为“男”。输

44、入1显示为“女”。49.“-”符号作用把文本数字转换为数字型数字，与+0,*1,等价。50.增加Excel的后悔次数Excel默认是16次，可修改注册表来自定义：Hkey_Current_UserSoftwareMicrosoftOffice11.0ExcelOptions，新建dword值，键名为undohistory，双击，10进制，输入“30”则可后悔30次。51.求平均分时显示出被去掉的最高、最低分数求平均分可用Trimmean函数，返回被去掉的分数：最大（2个），=Large(Data,1;2)；最小（2个），=Small(Data,1;2)52.复制、粘贴中回车键妙用先选要复制的目标单元格，复制后，直接选要粘贴的单元格，回车OK；先选要复制的目标单元格，复制后，选定要粘贴的区域，回车OK；先选要复制的目标单元格，复制后，选定要粘贴的不连续单元格