Excel 使用技巧 实用 集锦.docx
《Excel 使用技巧 实用 集锦.docx》由会员分享，可在线阅读，更多相关《Excel 使用技巧 实用 集锦.docx（19页珍藏版）》请在冰豆网上搜索。
Excel使用技巧实用集锦
Excel使用技巧实用集锦
1、快速输入大量含小数点的数字
输入大量带有小数位的数字时，按照普通的输入方法，例如0.05这个数字，我们会原样输入到表格中，这样工作量会变大，输入效率会降低。
其实，我们可以使用ExcelXP中的小数点自动定位功能，让小数点自动定位，从而快速提高输入速度。
用鼠标依次单击"工具"/"选项"/"编辑"标签，在弹出的对话框中选中"自动设置小数点"复选框，然后在"位数"微调编辑框中键入需要显示在小数点右面的位数就可以了。
以后我们再输入带有小数点的数字时，直接输入数字，而小数点将在回车键后自动进行定位。
例如，要在某单元格中键入0.06，可以在上面的设置中，让"位数"选项为2，然后直接在指定单元格中输入6，回车以后，该单元格的数字自动变为"0.06"。
怎么样，简单吧？
2、快速录入文本文件中的内容
如果您需要将纯文本数据制作成ExcelXP的工作表，那该怎么办呢？
重新输入一遍，大概只有头脑有毛病的人才会这样做；将菜单上的数据一个个复制/粘贴到工作表中，也需花很多时间。
其实只要在ExcelXP中巧妙使用其中的文本文件导入功能，就可以大大减轻工作量。
依次用鼠标单击菜单"数据/获取外部数据/导入文本文件"，然后在导入文本会话窗口选择要导入的文本文件，再按下"导入"钮以后，程序会弹出一个文本导入向导对话框，您只要按照向导的提示进行操作，就可以把以文本格式的数据转换成工作表的格式了。
3、快速输入大量相同数据
如果你希望在不同的单元格中输入大量相同的数据信息，那么你不必逐个输入。
具体方法为：
首先同时选中需要填充数据的单元格。
若某些单元格不相邻，可在按住Ctrl键的同时，点击鼠标左键，逐个选中；其次输入要填充的某个数据。
按住Ctrl键的同时，按回车键，则刚才选中的所有单元格同时填入该数据。
4、快速进行中英文输入法切换
一张工作表常常会既包含有数字信息，又包含有文字信息，要录入这样一种工作表就需要我们不断地在中英文之间反复切换输入法，非常麻烦，为了方便操作，我们可以用以下方法实现自动切换：
首先用鼠标选中需要输入中文的单元格区域，然后在输入法菜单中选择一个合适的中文输入法；接着打开"有效性"对话框，选中"输入法模式"标签，在"模式"框中选择打开，单击"确定"按钮；然后再选中输入数字的单元格区域，在"有效数据"对话框中，单击"输入法模式"选项卡，在"模式"框中选择关闭（英文模式）；最后单击"确定"按钮，这样用鼠标分别在刚才设定的两列中选中单元格，五笔和英文输入方式就可以相互切换了。
5、快速删除工作表中空行
删除ExcelXP工作表中的空行，一般是将空行都找出来，然后逐行删除，但这样做操作量非常大，很不方便。
那么如何才能减轻删除工作表中空行的工作量呢？
可以使用下面的操作方法来进行删除：
在打开的工作表中单击菜单栏中的"插入"菜单项，并从下拉菜单中选择"列"，从而插入一新的列Ｘ，在Ｘ列中顺序填入整数；然后根据其他任何一列将表中的行排序，使所有空行都集中到表的底部。
删去所有空行中Ｘ列的数据，以Ｘ列重新排序，然后删去Ｘ列。
按照这样的删除方法，无论工作表中包含多少空行，您就可以很快地删除了。
6、快速对不同单元格中字号进行调整
在编辑文件时常常需要将某一列的宽度固定，但由于该列各单元格中的字符数目不等，致使有的单元格中的内容不能完全显示在屏幕上，为了让这些单元格中的数据都显示在屏幕上，就不得不对这些单元格重新定义较小的字号。
如果依次对这些单元格中的字号调整的话，工作量将会变得很大。
其实，您可以采用下面的方法来减轻字号调整的工作量：
首先新建或打开一个工作簿，并选中需要ExcelXP根据单元格的宽度调整字号的单元格区域；其次单击用鼠标依次单击菜单栏中的"格式"/"单元格"/"对齐"标签，在"文本控制"下选中"缩小字体填充"复选框，并单击"确定"按钮；此后，当你在这些单元格中输入数据时，如果输入的数据长度超过了单元格的宽度，ExcelXP能够自动缩小字符的大小把数据调整到与列宽一致，以使数据全部显示在单元格中。
如果你对这些单元格的列宽进行了更改，则字符可自动增大或缩小字号，以适应新的单元格列宽，但是对这些单元格原设置的字体字号大小则保持不变。
7、快速输入多个重复数据
我们经常要输入大量重复的数据,如果依次输入，工作量无疑是巨大的。
现在我们可以借助ExcelXP的"宏"功能，来记录首次输入需要重复输入的数据的命令和过程，然后将这些命令和过程赋值到一个组合键或工具栏的按钮上，当按下组合键时，计算机就会重复所记录的操作。
打开工作表，在工作表中选中要进行操作的单元格；接着再用鼠标单击菜单栏中的"工具"菜单项，并从弹出的下拉菜单中选择"宏"子菜单项，并从随后弹出的下级菜单中选择"录制新宏"命令；设定好宏后，我们就可以对指定的单元格，进行各种操作，程序将自动对所进行的各方面操作记录复制。
8、快速处理多个工作表
无论打开多少工作表，在某一时刻我们只能对一个工作表进行编辑，那么能够同时处理多个工作表么？
您可采用以下方法：
首先按住"Shift"键或"Ctrl"键并配以鼠标操作，在工作簿底部选择多个彼此相邻或不相邻的工作表标签，然后就可以对其实行多方面的批量处理了。
在选中的工作表标签上按右键弹出快捷菜单，进行插入和删除多个工作表的操作；然后在"文件"菜单中选择"页面设置......"，将选中的多个工作表设成相同的页面模式；再通过"编辑"菜单中的有关选项，在多个工作表范围内进行查找、替换、定位操作；通过"格式"菜单中的有关选项，将选中的多个工作表的行、列、单元格设成相同的样式以及进行一次性全部隐藏操作；接着在"工具"菜单中选择"选项......"，在弹出的菜单中选择"视窗"和"编辑"按钮，将选中的工作表设成相同的视窗样式和单元格编辑属性；最后选中上述工作表集合中任何一个工作表，并在其上完成我们所需要的表格，则其它工作表在相同的位置也同时生成了格式完全相同的表格。
二、Excel2000中的快速操作
★打开或关闭多个文件★
快速打开多个文件的方法是：
按住"Ctrl"键或"Shift"键，选中多个Excel文件，右键单击弹出菜单，单击"打开"命令，即可同时打开选中的多个文件。
快速关闭多个文件的方法是：
在Excel2000中，按住"Shift"键，打开"文件"菜单，单击"关闭所有文件"命令，可将当前打开的所有文件快速关闭。
★对多个工作表快速编辑★
按住"Ctrl"键或"Shift"键，选中多个工作表，这样在选中的工作表中可进行相同的操作。
一般情况下，进行的操作主要有如下几种：
1、页面设置。
可快速对选中工作表设置相同的页面。
2、输入相同的数据。
可快速在多个工作表中输入相同的数据。
3、快速操作。
在多个工作表中进行一系列相同的操作，如设置字号、字体、颜色，进行单元格的合并或撤销合并的单元格等。
4、输入公式。
快速输入相同的公式，进行公式计算。
★简单序列的快速填充★
请看下面的的数据，班级：
99.1、99.2、99.3......99.10、99.11；数字序列：
1.2、1.3、1.4......2.0、2.1等，你知道如何快速输入它们吗？
你一定会说，这还不简单，点击"编辑/填充/序列"命令，很快就输入完成，但你知道还有更快的办法吗？
请听我说。
打开"格式/单元格"中的"数字"选项，选中"文本"，再在输入区的第一个单元格中输入"99.1"，然后选中99.1所在的单元格，鼠标移至选中区域的右下角，出现黑"+"符号时按下左键向下（或向右）拖动，即可快速输入。
对于像1.2、1.3、1.4......2.0、2.1这样的数字序列也可以这样快速输入，等等，1.9之后出现的是1.10而不是2.0？
自己将1.10改为2.0不就行了吗，修改完后选中2.0所在的单元格，然后按照上面的操作向下（或向右）拖动鼠标即可大功告成。
★快速输入分数★
通常在单元格中输入分数6/7会显示为6月7日，要想快速输入，在输入分数6/7时在它前面添加一个"0"和一个空格即可，但是用此法输入的分母不超过99（分母超过99就会变成另一个分数）。
★快速输入文本格式的数字★
将"00级1班"写成"00.1"进行输入时，在Execl2000会把它看做数字，显示为"0.1"，需要将单元格的输入格式转换为文本格式，但还有更快的好办法直接输入文本格式数字，输入时只需在前面加一个"'"（单引号，在英文状态下输入）即可。
★快速应用格式★
要将某一单元格（或区域）的格式（字体、字号、行高、列宽等）应用于其他区域，可将上述单元格选中，单击或双击工具栏中的"格式刷"按钮，然后将鼠标拖过要设置格式的单元格即可。
说明：
单击"格式刷"只能应用一次，而双击"格式刷"则可以多次使用，完成后再单击"格式刷"按钮退出操作。
★自己定制工具栏★
在Word2000或WPS2000中，使用工具栏上的"增大字号"、"减小字号"及"合并单元格"、"拆分单元格"等快捷按钮进行操作是非常方便的，可是在Excel2000中就没有那么幸运了。
但你也不用太遗憾，我们可以自己动手去定制工具栏。
定制方法是：
单击"工具/自定义"，打开"自定义"对话框，选择"命令"选项，在"类别"中单击"格式"后，在右侧的"命令"中找到"增大字号"命令，左键拖动到快捷工具栏上即可，用同样方法可将"减小字号"、"合并单元格"、"撤销合并单元格"等命令拖到快捷工具栏上，这样你以后在操作时就很方便了。
三、Excel2000中的公式
Excel2000是办公室中常用的软件，我们用它制作表格的时候，经常需要进行增加一列、删除一列等操作，这时就需要调整表格中的公式。
如果人工逐个去调整相关的公式，工作量很大而且容易出错。
笔者在使用Excel2000制作表格、填写公式的时候，摸索到以下技巧：
1.最好使用函数
使用公式最大的好处在于，日后修改表格结构的时候，公式会自动进行修改，而函数还可以完成一些复杂的操作。
在上表中税金的公式就必须利用函数，由于税金要根据不同的情况进行不同的处理，这时就只能利用IF（）函数进行判断了。
有的人也许会觉得使用函数麻烦，其实不必担心，使用Excel中的函数向导功能可以帮助你完成函数的填写。
先把光标定位在需要使用函数的单元格中，单击"常用工具栏"中的"粘贴函数"按钮；或使用菜单中的"插入/函数"选项，在目标单元格中就会出现一个"="号表示这是一个公式，同时会出现"粘贴函数"对话框（如图1），框中左边为函数的分类，右边为每一类函数中具体的函数。
在右边选中一个函数，下边会出现该函数的功能解释，单击"确定"。
选定了一个函数并"确定"后，会出现如图2所示的引导界面，在这里函数向导会一步一步地指导我们填入函数所需要的各个参数，给出每个参数的含义以及需要的数据类型，并给出最终的计算结果。
2.相对公式与绝对公式
所谓相对公式和绝对公式是指在公式中如何引用一个单元格。
在相对公式中，引用单元格的时候，直接填写单元格的坐标即可。
其特点是，当我们复制一个公式的时候，它的坐标会发生变化。
例如：
在A5中填入SUM（A1:
A4）的意思是求A1到A4所有单元格中数值的总和，我们把它复制到B5的时候，公式就会变成SUM（B1:
B4）。
复制公式时变化的规律是，相对于当前单元格，向右移动几列，公式的列坐标就增加几，向下移动几行，行坐标就增加几；如果向左或向上移动，坐标就减掉移动的行数或列数。
使用相对公式的好处在于，当我们复制或移动公式的时候，公式中的坐标会自动发生变化而不需要我们再去修改它。
但有时候，我们在几个公式中都需要引用同一个单元格，这时，采用相对公式就不合适了。
我们可以采用绝对公式：
绝对公式是指公式中的行坐标或列坐标被固定的坐标。
我们都知道，在Excel中引用一个单元格的方法是指明它的列坐标和行坐标。
例如：
A1表示A列第一行。
如果我们在一个单元格的行坐标或列坐标前面加上一个"$"符号，则相应的行坐标或列坐标就被固定下来了。
绝对公式的特点是，在复制公式或移动公式的时候，被固定住的行坐标或列坐标不会发生变化。
例如在B7中的公式为：
=$B$1*$B$2*B6，如果复制到C7中公式变化为：
=$B$1*$B$2*C6。
由于公式中的"B6"没有被固定，所以发生了变化，而"B1"和"B2"被固定了，因而没有发生变化。
需要注意的是，在使用绝对公式的时候，如果需要固定为某一个单元格，需要在行坐标和列坐标前面均加上"$"。
如果只在行坐标或列坐标的前面加上"$"表明只固定行坐标或列坐标。
3.添加一列时公式的变化
当我们在表格中增加一列而新增加的列要添加到公式中参与计算的时候，需要调整公式，运用以下规律，可使得在增加列时不必调整公式。
（1）适当增加一些空白列，而且这些空白列放到公式中。
（2）增加新列时，不要添加到公式包括的范围之外，而应该放在公式包括的范围之间，不过这只在使用函数的时候起作用。
（3）尽量使用函数，而不要使用诸如"A1+A3"这种形式的公式。
例如：
在工资表中，应发工资出的公式为：
=SUM（C2:
E2）-SUM（F2:
I2）。
其中SUM（C2:
E2）计算的是所有应发项的和；SUM（F2:
I2）计算的是所有扣除项的和。
每年都需要发放煤火费，这样就需要增加一列。
在增加新列的时候，注意一下加入的位置：
如果我们加在E2和F2之间的话就必须要调整应发工资处的公式，以便把煤火费一项加入公式；但如果我们在C2到E2之间添加一列的话，则新增加的列会自动添加到公式中。
这时应发工资处的公式自动调整为：
=SUM（C2:
F2）-SUM（G2:
K2）。
（4）公式的保护
当做好一个表格，填写好各种公式后，一定不希望公式被误操作而破坏。
要保护一个单元格的内容（包括公式）需要做到：
这些单元格处于"锁定"状态，方法是：
选中单元格，使菜单"格式/单元格/保护"中的"锁定"处于选中状态；发出保护工作表命令，方法是：
点击"工具/保护/保护工作表"。
注意：
在默认状态下，所有的单元格都处于锁定状态。
如果直接点击"保护工作表"命令，则所有的单元格都无法输入数据。
所以应该先把所有的单元格解除"锁定"，再把需要保护的单元格进行"锁定"。
四、Excel2000提高效率妙招二则
用Excel2000处理大量数据非常方便，但输入大量数据可不是件好玩的事情。
下面就介绍两种方法，希望会对大家有所帮助和启发。
方法一：
下拉菜单实现输入
有时候我们在各列各行中都输入同样的几个值，比如说，输入学生的等级时我们只输入四个值：
优秀，良好，合格，不合格。
我们希望Excel2000单元格能够像下拉框一样，让输入者在下拉菜单中选择就可以实现输入。
操作步骤：
先选择要实现效果的行或列；再点击"数据有效性"，打开"数据有效性"对话框；选择"设置"选项卡，在"允许"下拉菜单中选择"序列"；在"数据来源"中输入"优秀，良好，合格，不合格"（注意要用英文输入状态下的逗号分隔！
）；选上"忽略空值"和"提供下拉菜单"两个复选框。
点击"输入信息"选项卡，选上"选定单元格显示输入信息"，在"输入信息"中输入"请在这里选择"。
方法二：
自动实现输入法中英文转换
有时，我们在不同行或不同列之间要分别输入中文和英文。
我们希望Excel2000能自动实现输入法在中英文间转换。
操作步骤：
假设我们在A列输入学生的中文名，B列输入学生的英文名。
先选定B列，点击进入"数据有效性"，打开"数据有效性"对话框；选择"输入法"对话框，在"模式"下拉菜单中选择"关闭（英文模式）"；然后再"确定"，看看怎么样。
这两个例子中我们都用了"数据有效性"，平时可能大家都不太注意它，但它其实很有用。
Office作为一个庞大而优秀的软件，可以用来创造性地做很多事，关键是你要常提出问题"想怎么样？
怎么通过这个工具实现它？
"
五、让Office办公带上你的特色
1、在Excel中定制自己的工具条
通过此方法，可以把Excel的工具栏"缩水"。
打开Excel，点击菜单"视图工具栏自定义"，打开工具栏的自定义窗口。
在"工具栏"属性选项中点击"新建"，输入你喜欢的名称，点击"确定"。
这时你会看见一个以你定义的名称命名的工具栏，而且上面什么也没有。
接着，把常用工具栏上的"保存"按钮（就是那个小磁盘）拖到你刚才新建的工具栏（注意：
拖动的时候要按住"Ctrl"键，否则常用工具栏上的"保存"按钮会消失）。
这时，你可以在刚才拖过来的"保存"按钮上单击鼠标右键，弹出一个菜单，点击"编辑按钮图标"，就可以在上面画上自己喜欢的按钮图标。
你还可以进入"更改按钮图标"，选择自己喜欢的按钮图标。
图标改变完之后，你还可以点击"重命名"来把此按钮改名。
然后，依次把你常用的按钮按住"Ctrl"拖到自己的工具栏，最后把其他工具栏隐藏。
此时，一个完全按照你自己意愿的工具栏制作完成了。
是不是很酷呢？
2、让保存的Word文档自动设置好作者、公司等项目
打开Word，新建一个空白文档，点击菜单"文件另存为"，弹出"保存"对话框，在"保存文件名"上输入"Normal"，在"保存类型"上选择"文档模板"，保存在任意一个地方。
然后关闭Word。
下一步就是用资源管理器打开Office存放模板的路径（比如我的Office存放模板的路径是C:
ProgramFilesMicrosoftOfficeTemplates），把Normal.dot改名为aa.dot（此文件只是以后恢复所用，因此更改的文件名可自定），然后把刚才保存的Word文档模板Normal.dot移动到此文件夹，并且在这个文档模板上单击鼠标右键，选择"属性"，在"摘要"选项卡上依次把标题、主题、作者、上司、公司、类别、关键字、说明等填写上去。
以后，你保存的Word文档就自动填写上这些项目，可以为你工作时减去一点负担。
六、详解Excel中的自动填充
我们使用Excel处理表格时，经常要输入大量重复的、有规律的数据，使用其"自动填充"功能会大大减少我们的工作量，提高工作效率。
下面就让我们一起来看看使用这一功能的一些技巧。
首先我们要做如下设置。
1、在"工具"菜单上，单击"选项"命令，再单击"编辑"选项卡。
2、确保"单元格拖放功能"复选框被选中，如果没有选中"单元格拖放功能"复选框，那么以后的操作将无法进行下去。
准备工作完成了，开始吧！
（一）在同一行或列中复制数据
以表中第一列的学校名为例：
1、在A2单元格中输入"北京第五中学"。
2、将鼠标指针指向A2单元格的"填充柄（位于选定区域角上的小黑块，如图2。
鼠标指向它时，鼠标的形状变为黑十字）"，向下拖动鼠标到A12，同时在黑十字旁边应有"北京第五中学"字样，松开鼠标后"北京第五中学"会自动填充到A2~A12的每一个单元格。
你可以试一试在同一行中复制数据。
（二）填充数字、日期或其他序列
1、现在录入学生的学号，也存在2000年问题。
去年入学的学生的学号是以"0"开头的，如"000325001"，如果不对单元格进行设置，它会自动将前面的3个"0"删除。
选定B2单元格，然后在"格式"菜单上单击"单元格"命令，再单击"数字"选项卡，对选定的空单元格应用"文本格式"，这时再输入"000325001"。
2、和上面第2条的做法一样，在B2~B12单元格中会自动填充"000325001~000325011"的学号。
如果你想在B2~B12中都填充"000325001"，在拖动时按住"Ctrl"键。
3、在填充日期时对单元格不需任何设置，操作步骤和上面的一样，你可以在某列第一行单元格中输入"1月"试试。
4、如果要让序列号按给定的步长增长，请再选定下一单元格，在其中输入序列的第二个数值。
头两个单元格中数值的差额将决定该序列的增长步长。
例如：
在B2中输入"000325001"，在B3中输入"000325003"，同时选中B2、B3单元格，向下拖动"填充柄"，学号将以奇数序列递增。
（三）创建自定义填充序列或排序次序
通过工作表中现有的数据项或以临时输入的方式，可以创建自定义序列或排序次序。
1、如果已经输入了填充序列的数据清单，请选定工作表中相应的数据区域。
2、在"工具"菜单上，单击"选项"命令，再单击"自定义序列"选项卡。
3、然后单击"导入"按钮，即可使用选定的数据清单。
如果要键入新的序列列表，请选择"自定义序列"列表框中的"新序列"选项，然后在"输入序列"编辑列表框中，从第一个序列元素开始输入新的序列。
键入每个元素后，按回车键。
整个序列输入完毕后，请单击"添加"按钮
七、在Excel中实现中文大写数字
开发表格模板时，为做一个能在Excel中构造数字的汉字写法（中文大写）的一段VB函数花了不少时间。
事后仔细想来，其实实现数字读法的方法实际上至少有三种，即VB编程法、自动更正法及工作表函数的替换法。
用VB编程实际上是最复杂的一种方法（但也是一劳永逸的方法），在很多时候完全可以用较简单的方法实现。
以下分别介绍这几种方法。
1、自动更正法
使用自动更正功能，可以简化用户对大写汉字的输入操作，例如可以将"1//"定义更正为"壹"，那么当要输入"壹"时，就可通过键入"1//"来实现。
另外，这种定义以后不仅在Excel中有效，在Word中也同样有效，因为"自动更正"程序是Office组件所公用的。
用"自动更正"是最简单易行的，用户可自由、方便地输入要写的大写读法。
然而它的局限性也很明显，即它虽然给使用者提供了输入大写数字的方便手段，但终究还是需要人工输入，不能自动、实时地依当前的实际数字结果进行更新。
2、工作表函数替换法
要做到数值的自动更新，一般都会想到用VB构造函数法，但其实我们可采用一个简单的方法，即工作表函数的替换法。
顾名思义，所谓"工作表函数的替换法"，就是将原来的结果换去一部分。
那么换什么，又怎样换?
我们知道，在Excel中，提供了多种数字的显示格式。
其中文版实际上也包括数字的大写读法显示格式，但遗憾的是这个功能对一般用户来说基本上不能用，因为Microsoft在实现这个数字显示格式时犯了一个小小的错误，例如"33.56"这个数，它显示出来的是"叁拾叁.伍陆"。
很显然，没有将小数点作有效处理。
虽然这个显示格式是错的，但其劳动成果还是能为我们所用--只要能设法将其小数点作有效处理。
做到这一点也并不难，可以先将数字以其内置的中文大写显示格式转变为文本格式，即使用TEXE（）函数将其结果转化为字符串，然后将小数点"."用汉字"点"换掉（使用SUBSTITUTE（）函数）。
例如，若要将B1单元格内容重写为大写，则可以在单元格中输入如下公式:
=SUBSTITUTE（TEXT（B1，″G/通用格式″），″.″，″点″）
使用这样一个小小的公式应当说并不复杂，不过，这样做仍不能满足某些情况下的要求，因为使用大写表示数额一般只应用于财会方面，其他很少这么用。
在上例中，对数字"33.56"，往往要求结果写为"叁拾叁元伍角陆分"。
那么，如何将"33.00"写为"叁拾叁元整"呢
实现这一功能的逻辑也不太复杂。
我们可以看到，Excel显示格式的小数点前的数字读法是没有错的，可将其取出来作为一个字符串；小数点后面的数字则麻烦些，必取出来，再以低位是否都为"0"来判断是要写"整"还是"零"。
还要将"元"、"角"、"分"等字串适当地插入，最后把几个字串连接起来就行了。
我们仍将B1单元格内容作格式转化，则具体实现如下:
=TEXT（INT
升级会员 