excel应用技巧.docx
《excel应用技巧.docx》由会员分享，可在线阅读，更多相关《excel应用技巧.docx（13页珍藏版）》请在冰豆网上搜索。
excel应用技巧
你的复制“所见即所得”吗？
在Excel2000中，出于某些目的，如果你对工作表的部份栏或列予以隐藏后，然后对工作表正常显示的内容进行复制贴上时，你会发现，隐藏的内容也同时被复制进来。
或是你使用了“分类汇总”功能后，在将汇总结果复制到另一张工作表或Word中时，就会发现，表面上你复制的是显示的那几行汇总项目，而贴上的结果却包括了汇总与汇总之间的详细项目，非常烦人。
其实，Excel2000提供了一个功能，可以很方便的让你的复制做到“所见即所得”。
步骤如下：
1、选择要复制的区域；
2、选择菜单“编辑”/“定位…”或直接按“CTRL+G”快捷键，计算机显示“定位”对话框。
3、按“定位条件…”按钮。
计算机显示“定位条件”对话框。
选择“可见单元格”。
见图2中红线区。
4、按确定后回到工作表窗口。
5、在选定区域按鼠标右键，选择“复制”或直接按“CTRL+C”快捷键。
这是你会发现，计算机显示的画面与普通复制不一样，有很多个流动框，表示处在多重选择下。
6、点击目地区域的左上角储存格，按鼠标右键，选择“贴上”或直接按“CTRL+V”快捷键。
7、你会发现计算机己按你的要求贴上了你要的项目，干净利落。
锁定和隐藏Excel公式
在Excel中辛辛苦苦编制的计算公式，通常不希望使用者查看和修改。
怎么办呢？
利用Excel锁定、隐藏和保护工作表的功能，把公式隐藏和锁定起来。
1、选中整个工作表数据区域，执行“格式→单元格”命令，打开“单元格格式”对话框。
2、切换到“保护”标签，清除“锁定”选项前面复选框中的“∨”号，确定返回。
3、执行“编辑定位”命令，打开“定位”对话框，单击其中的“定位条件”按钮，打开“定位条件”对话框。
4、选中其中的“公式”选项，确定返回，一次性选定工作表中所有包含公式的单元格。
5、再次打开“单元格格式”对话框，切换到“保护”标签下，在“锁定”和“隐藏”选项前面复选框中加上“∨”号，确定返回。
6、执行“工具→保护→保护工作表”命令，打开“保护工作表”对话框，输入密码，确定，再确认输入一次密码，确定返回即可。
注意：
如果自己需要查看或修改公式，请先执行“工具→保护→撤销工作表保护”命令，解除对工作表的保护。
如何改变回车键的功能？
一般情况下，我们在一个单元格中输入资料后，按下回车键后活动单元格会转入下一行的同列单元格中。
如果要转到右边的单元格，能不能实现呢？
打开“工具”菜单，选择“选项”命令，单击“编辑”选项卡，多后从“方向”下拉列表框中选择“向右”，单击“确定”。
这时再按下回车键，活动单元格就转到右边了。
同样，我们可以将回车键设置为其它跳转方向，也可以取消回车键的单元格跳转功能。
注意：
按Tab键也可以实现活动单元格向右跳转。
如何将姓与名分开？
有这样一个问题，一个数据表中，A列是由姓和名组成了，如何将姓和名分开呢？
一个简单的方法是应用Left函数，此函数可以返回文本串中的第一个或前几个字符。
Left函数的语法形式为：
LEFT（text,num_chars）
其中：
Text：
是包含要提取字符的文本串。
Num_chars：
要提取的字符数量，这个值必须大于或等于0；
如果num_chars大于文本长度，则返回所有文本；
如果忽略num_chars，则假定其值为1。
下面我们看一下这个例子，要将姓分开，且例子中都是单姓的，所以我们可以在B1中添入“=left（A1）”，然后利用自动填充功能，完成下面单元格公式的复制。
我们再看一个例子，某些会计程序在显示负数时会在数值的右边添加负号（–）。
如果导入的文件是在按此方法保存负数的程序中创建的，则MicrosoftExcel会将这些数值作为文本导入。
要将此文本串转换为数值，必须返回除最右边字符（即负号）以外的所有文本串字符，然后乘以–1。
对于num_chars参数，可使用LEN函数计算文本串中字符的个数，然后减去1。
例如，如果单元格A1中的值为“234–”，我们可以在B1中输入“LEFT（A1,LEN（A1）–1）*–1”。
按回车后B1中就显示为“–234”。
同样，我们可以利用自动填充的功能完成其余数据的转换。
好，Left函数的功能就讲这些，与之相对的还有个“right”函数，可以返回文本串中后一个或几个字符，你可以放手一用了！
页码设置
Excel的页码设置是通过对“页面设置”中“页眉/页脚”的设置来实现的。
首先是打开“页面设置”，选择“页眉/页脚”选项，一般情况下选择“自定义页脚”，打开“页脚”设置对话框（如图1）。
图中第二个按钮“#”是用来设置页码的，单击后自动输入“&&［页码］”。
第三个按钮是用来设置总页数的，单击后输入“&&［总页数］”，默认都是用阿拉伯数字表示。
图中的“左”、“中”、“右”三个编辑框，分别表示页码将出现在页脚的左边、中部还是右边。
图1
通常情况Excel文件的页码是从“1”开始计算的，为了使页码连续，刚开始考虑用分别设置每一页的方法，即打印某页时在“页面设置”的页脚设置中插入相应的阿拉伯数字。
可面对60多页内容，这种方法太麻烦了，别说1个小时，就是3个小时也无法完成任务，看来还得寻找其他的办法。
解决问题
解决这个问题的关键是找到一种办法，使文件首页页码从大于1开始设置，而且要自动连续排列。
然后分别设置各个文件即可。
不用不知道，一用之下，竟然还让我发现了两个方法。
为了方便说明，我以首页页码为“5”来介绍。
在 “页脚”对话框中，首先单击按钮“#”，出现“&&［页码］”，然后在“&&［页码］”后面加上“+4”，即“&&［页码］+4”（如图2），则首页的页码将变为“5”，后面诸页的页码依次排列，不需要每页都要进行设置。
图2
第一步是在“页脚”中插入页码；第二步，在“页面设置”的“页面”选项中，在最下面的“起始页码”中输入“5”，则首页的页码将变为“5”，并依次向下排列（如图3）。
图3
问题的延伸
上面介绍的页码设置方法，页码都是用阿拉伯数字表示，如“5”，如果要换一下格式，来一点儿小花样，如“第3页”，能否实现呢？
受第一种方法启示，略作改动即可实现。
单击按钮“#”后出现“&&［页码］”后，在“&&［页码］”的前后分别加上“第”和“页”两个字，则变成了“第&&［页码］页”形式，这样页码就变成了“第×页”的形式。
对于在“自定义页脚”对话框中直接插入数字进行页码设置的方法，虽然每页都要设置，显得笨拙，但设置格式方式灵活，属全能性，输入什么显示什么，如“三”、“第三页”、“第3页 共5页”等。
Excel数据输入范围控制
学生的考试成绩通常位于0—100之间，在用Excel登记学生的成绩时，为了防止输入的成绩超出范围，我们用“数据有效性”功能来控制一下。
选中保存成绩的单元格区域，执行“数据→有效性”命令，打开“数据有效性”对话框，在“设置”标签下，按“允许”右侧的下拉按钮，在随后弹出的快捷菜单中，选择“小数”选项，然后在下面分别输入最小值（“0”）和最大值（“100”）；切换到“输入信息”标签中，设置提示信息；切换到“出错警告”标签下，设置出错提示及处理方法。
全部设置完成后，确定返回。
注意：
不进行“输入信息”和“出错警告”设置，不影响此有效性的使用。
以后再输入成绩时，当输入的成绩不在0—100之间时，系统会给出相应的提示，并拒绝接受录入的数值。
Excel统计信息重复出现次数
考试在学校中是经常的事，监考人员通常也是不固定的，考试次数一多，监考工作量的统计就足够统计人员费心了。
要知道，津贴是按照监考次数发放的，算错了，可对不起老师们的劳动哟！
本人以前采用的是“传统”的笨办法，点着监考安排表逐一计数，头晕脑胀、眼花缭乱不说，稍不留神还容易出错。
直到在Excel中翻翻捡捡，找到了两个函数，才算完全脱离苦海，一劳永逸。
第一步，因为要利用Excel进行统计，所以监考安排中的监考人员数据要复制到Excel中。
其实，Excel制作表格的功能也是相当不错，不妨直接就用Excel进行监考安排的排制。
此例将监考安排数据存放在Sheet1中。
如图1。
第二步，插入一个新工作表，更名为“统计”。
将全部的可能监考的教师名单输入一列，如A列。
第三步，在一人名右侧相邻的单元格内输入公式，例如，B3单元格内姓名为“张三”，则在C3内输入“=COUNTIF（Sheet1!
$1:
$65536,CONCATENATE（"*",B3,"*"））”（也可以利用公式面板输入）。
这里面用了COUNTIF和CONCATENATE两个函数，第一个是计算一个区域中满足给定条件的单元格数目；第二个是条件，即包含B3中姓名的字符串。
回车后即出现计算后的结果。
单击C3单元格可见编辑栏公式，如图2。
第四步，将公式复制到其他姓名对应的单元格，也可用填充柄拖动填充。
可以看到，每个姓名后面的单元格中自动出现了其监考的次数。
如图3。
这里有两个地方需注意：
引用区域要使用绝对应用，这样在填充时才不会出现公式错误；其次，要使用CONCATENATE在查找的姓名前后都加上一个通配符*号，目的是为了使该姓名出现在一个单元格的哪个部位均能被统计到。
当然，还有一点也很重要，那就是考试安排和监考次数统计中的人名一定要一模一样，否则，可要前功尽弃了。
快速选择Excel工作表
在Excel中，要在一个有很多个工作表的工作簿中快速地选择某一个工作表，只需将鼠标指针移到工作簿左下角的工作表滚动轴（Sheet Tabs），然后点击右键，在快捷菜单中选择你想要的工作表即可
快速选中全部工作表
右键单击工作窗口下面的工作表标签，在弹出的菜单中选择“选定全部工作表”命令即可
快速启动Excel
如果想在启动系统自动运行Excel，可以这样操作:
1.双击“我的电脑”图标，进入Windows目录，依次打开“Start Menu\Programs\启动”文件夹;
2.打开Excel所在的文件夹，用鼠标将Excel图标拖到“启动”文件夹，这时Excel的快捷方式就被复制到“启动”文件夹中，下次启动Windows就可快速启动Excel了。
如果Windows系统已启动，你可用以下方法快速启动Excel:
方法一:
单击“开始→文档”命令里的任一Excel工作簿即可。
方法二:
用鼠标从“我的电脑”中将Excel应用程序拖到桌面上，然后从快捷菜单中选择“在当前位置创建快捷方式”，以后启动时只需双击快捷方式即可。
快速删除选定区域数据
如果用鼠标右键向上或向左（反向）拖动选定单元格区域的填充柄时，没有将其拖出选定区域即释放了鼠标右键，则将删除选定区域中的部分或全部数据（即拖动过程中变成灰色模糊的单元格区域，在释放了鼠标右键后其内容将被删除）。
给单元格重新命名
Excel给每个单元格都有一个默认的名字，其命名规则是列标加横标，例如D3表示第四列、第三行的单元格。
如果要将某单元格重新命名，可以采用下面两种方法:
1.只要用鼠标单击某单元格，在表的左上角就会看到它当前的名字，再用鼠标选中名字，就可以输入一个新的名字了。
2.选中要命名的单元格，单击“插入→名称→定义”命令，显示“定义名称”对话框，在“在当前工作簿中的名称”框里输入名字，单击“确定”按钮即可
注意:
在给单元格命名时需注意名称的第一个字符必须是字母或汉字，它最多可包含255个字符，可以包含大、小写字符，但是名称中不能有空格且不能与单元格引用相同。
Excel数据录入技巧
1．快速录入分数
通常在单元格内输入分数8/9会显示为8月9日，如果你想避免这种情况发生，除了将单元格格式设置为“分数”外（Excel2000有分数格式）。
输入8/9时在它前面添加一个0和一个空格（它在0与8/9之间）即可，但是用此法输入的分母最大不能超过99。
2．录入文本格式数字
如果你要在单元格中输入文本格式的数字（如“身份证号码”），除了事先将单元格设置为文本格式外，只须在数字前面加一个“'”（单引号）即可。
3．行或列重复填充
如果你要在同一行或列内重复填充某些数据，可按以下方法操作：
选中包含原始数据的单元格（或区域）；将鼠标移至所选区域右下角的填充柄，当光标变为小黑十字时，按下左键拖过所有需要填充的单元格再松开鼠标。
注意：
如果被选中的是数字或日期等数据，可能会以序列方式填充，这时应按下Ctrl键再拖动。
4．周围数据填充
如果你要填充的数据与周围单元格（或区域）中的数据相同，可用以下方法快速重复填充：
（1）按Ctrl+D键，将上方单元格中的数据填充进来，按Ctrl+R键，将左侧单元格中的数据填充进来。
（2）单击“编辑”、“填充”子菜单下的“向上填充”、“向左填充”命令，可将下方或右侧单元格中的数据填充进来。
注意：
如果要填充的是一个区域，可先将其选中，再按上法操作，即可将对应区域内的数据填充进来。
5．简单序列填充
数字、日期等简单序列填充可按以下方法填充：
（1）在起始单元格中输入序列的初值，然后在下方或右侧的单元格中输入序列的第二个值，这两个值的差就是序列的步长。
（2）选中已输入的所有单元格，将鼠标移至所选区域右下角的填充柄，当光标变为小黑十字时，按下左键沿行或列拖动。
如果要按升序排列，应由上而下或由左而右拖动。
如果要按降序排列，应由下而上或由右而左拖动。
6．自定义序列填充
如果你输入的序列比较特殊，可以先加以定义，再项内置序列那样使用。
自定义序列的方法是：
（1）单击“工具”菜单中的“选项”命令。
打开“选项”对话框中的“自定义序列”选项卡。
（2）在“输入序列”框中输入自定义序列的全部内容，每输入一条按一次回车键，完成后单击“添加”按钮。
整个序列输入完毕后，单击对话框中的“确定”按钮。
此后，你只要输入自定义序列中的前两三项，就可以按前面介绍的方法将其输入。
7．选择填充
若需要填充前一两个单元格面已输入过的数据（文本或文本与数字的混合，纯数字不行）。
可以采用以下方法：
（1）让光标停留在需要填充的单元格。
按Alt+↓键打开本列已填充内容列表。
选择快捷菜单中的“选择列表”命令，打开本列已填充内容列表。
（2）用上下方向键选中列表中你需要填充的内容回车，或直接用鼠标单击所需的内容，它就被填入内容列表上方的单元格。
8．双击序列填充
此法适用于填充列左侧有完整数据列的情况，用于排名次等场合非常方便。
填充方法是：
（1）若要填充数字序列，可在起始单元格中输入两三个数字，全部选中后双击填充柄。
则产生的序列会自动向下填充，直到左侧数据列的空白单元格处为止。
（2）若要填充含数字的文本序列（如“第1名”之类），可在起始单元格中输入序列初始值（如“第1名”），将其选中后双击填充柄。
则产生的序列就会自动向下填充，直到左侧数据列的空白单元格为止。
9．工作表重复填充
如果你想一次填充多张相同的工作表，省略以后的复制、粘贴等操作，可采用以下方法：
（1）选中需要填充相同数据的工作表。
方法是：
若要选中多张相邻的工作表，可先单击第一张工作表标签，按住SHIFT键后单击最后一张工作表标签。
若要选中多张不相邻的工作表，可先单击第一张工作表标签，按住Ctrl键后单击要选中的其他工作表标签。
（2）在已选中的任意一张工作表内输入数据，则所有选定工作表的相应单元格会填入同一数据。
如果需要将某张工作表已有的数据快速填充到其他工作表，可采用以下方法：
按下Ctrl键选中含有数据的工作表和待填充数据的工作表，再选中含有数据的单元格区域，最后单击“编辑”、“填充”子菜单下的“至同组工作表”命令，在对话框中选择要填充的内容（“全部”、“内容”或“格式”）后“确定”。
10个小技巧
1、如何一次打印多个工作表？
按住Ctrl键，选择需要打印的工作表，再打印即可。
2、怎么批量删除单元格内的换行符。
选定数据区域，单击“格式/单元格”，将“单元格格式”对话框“对齐”标签中“自动换行”前的小勾去掉。
3、如何将外部连接的数据转化为固定值？
点编辑菜单的链接，选择断开连接即可。
4、为什么录入数据时，会自动扩大（或缩小）100倍？
试试将[工具]--[选项]--[编辑]中[自动设置小数点]项清除。
5、如何将文本类型的数据转化成数值？
从外部导入Excel的数据常常不能计算，只要复制一个空的单元格，再选择要转化的数据，然后用选择性粘贴——数值/加即可转化
6、如何替换单元格中的“*”、“？
”与“~”（波浪线）?
在这三个字符前加上“~”（波浪线），再替换即可。
7、怎么录入数组公式？
按Ctrl+Shift+Enter可以结束输入即可录入数组公式
8、什么是数组公式？
对一组或多组值执行多重计算，并返回一个或多个结果的公式。
数组公式括于大括号（{}）中。
9、如何正确录入18位的身份证号码？
将单元格格式设定为文本，或在身份证前加“'”即可。
10、怎么在单元格内换行？
按Alt+Enter（回车）即可。
升级会员 