Excel数据计算及其他技巧.docx
《Excel数据计算及其他技巧.docx》由会员分享，可在线阅读，更多相关《Excel数据计算及其他技巧.docx（13页珍藏版）》请在冰点文库上搜索。
Excel数据计算及其他技巧
1.Excel中“照相机”功能的妙用
在Excel中可以利用“照相机”功能抓取工作表中需要的内容，抓取后的内容以图片的形式显示。
其具体操作步骤如下：
（1）选择“工具自定义”命令，在打开的对话框中单击“命令”选项卡。
（2）在“类别”列表框中选择“工具”选项，再在“命令”列表框中选择“照相机”选项，并将其拖放到工具栏上。
（3）在工作表中选择要抓取的内容，单击“照相机”按钮，然后在需要该数据的位置单击鼠标左键，拍下的内容即以图片的形式显示在工作表中。
（4）利用“图片”工具栏根据个人需要对拍下的图片进行旋转、缩放等处理。
2.Excel中一些常见公式错误提示及其产生原因
在编辑表格过程中经常出现公式错误的提示信息，以下是一些常见的公式错误提示以及错误原因。
#DIV0：
在公式中出现了除以0的错误。
#NA：
在公式中引用的一些数据不可用。
#NAME：
公式中的文字，系统不能识别。
#NULL!
：
指定的相交并没有发生。
#REF：
单元格引用无效。
#NUM!
：
参数输入值不正确。
#VALUE!
：
提供的参数没有使用系统期望的数值类型。
HYPERLINK函数的妙用
HYPERLINK函数用于创建快捷方式或超链接，如打开文档或网络驱动器、跳转到某个网址、引用工作簿等，以方便工作。
使用该函数的操作步骤如下：
（1）选择要输入该函数的单元格，单击“常用”工具栏中的“插入函数”按钮，打开“插入函数”对话框。
（2）在“或选择类别”下拉列表框中选择“常用函数”选项，在“选择函数”列表框中选择“HYPERLINK”选项。
（3）单击按钮，在打开的“函数参数”对话框中的“Link_location”文本框中输入要链接到的目标文件名称及路径（也可以输入某个网址），在“Friendly_name”文本框中输入单元格中要显示的文本。
（4）单击按钮，返回到工作表中单击插入了函数的单元格，即可打开已链接的文件或网址。
3.为Excel2000设置自动保存
在Execl2000中也可以实现文档的定时自动保存，以防止因突然死机导致的数据丢失。
其具体操作步骤如下：
（1）选择“工具加载宏”命令，打开“加载宏”对话框。
（2）在“当前加载宏”列表框中选中“自动保存”复选框。
（3）单击按钮，系统自动加载安装。
（4）选择“工具自动保存”命令，打开“自动保存”对话框。
（5）根据需要在对话框中进行设置后单击按钮。
此后每当达到指定的时间间隔时，系统就会自动保存。
4.修复因故障导致损坏的文件
自动修复因Excel程序运行出现故障关闭程序或断电导致的文件损坏的操作步骤如下：
（1）选择“工具选项”命令，打开“选项”对话框，单击“保存”选项卡。
（2）取消选中“工作簿选项”栏中的“禁用自动恢复”复选框，然后选中“保存自动恢复信息，每隔”复选框，并在后面的数值框中输入保存的间隔频率，单击按钮完成设置。
（3）当Excel出现故障关闭后，重新启动Excel，系统自动打开“文档恢复”窗格，列出已自动恢复的所有文件。
（4）在“文档恢复”窗格中选择要打开的文档，并单击指定文件名旁边的下拉按钮，从打开的下拉列表中选择“显示修复”选项即可。
提示：
如果没有自动打开“文档恢复”窗格，可选择“文
件打开”命令，在打开对话框的“查找范围”下拉列表
框中选择保存有自动恢复文件的文件夹。
如果不知道自
动恢复文件在哪个文件夹，可以先在“选项”对话框的
“保存”选项卡中查看“自动恢复文件保存位置”文本
框中的路径，然后在“打开”对话框的“文件类型”下
拉列表框中选择“所有文件”选项，并选择要恢复的文
件，单击按钮。
5.创建“常用工作簿”新菜单
在Excel中可将常用的工作簿文档放置于常用文档菜单中以便于随时调用。
其具体操作步骤如下：
（1）在工具栏的空白处单击鼠标右键，在弹出的快捷菜单中选择“自定义”命令，在打开的“自定义”对话框中单击“命令”选项卡。
（2）在“类别”列表框中选择“新菜单”选项，再将“命令”列表框中的“新菜单”选项拖放到菜单栏中。
（3）单击对话框中的按钮，在打开的下拉列表中的“命名”文本框中输入“常用文档”，然后单击下拉列表外的任意位置。
（4）在“类别”列表框中选择“插入”选项，在“命令”列表框中选择“工作表”选项，并将其拖放到新菜单中，单击按钮进行命名。
（5）重复第4步的操作，添加多个文档列表名称。
（6）选择“常用文档”菜单中的一个菜单项，单击按钮，在打开的下拉菜单中选择“分配超链接打开”命令，打开“分配超链接”对话框。
（7）单击对话框左侧的相应按钮，在右侧窗格中设置定位到的工作簿文件夹，并选择该工作簿文档。
（8）重复第7步操作，将菜单项与对应的工作簿文档链接起来。
（9）在需要打开“常用文档”菜单中的某个工作簿文档时，从“常用文档”菜单中选择需要的文档即可。
6.创建专业符号工具栏
在编辑专业性很强的表格时，常常需要用到一些特殊的专业符号。
为了使用方便，可以将这些专业符号制作成一个工具栏，然后像使用常用工具一样快速调用。
其具体操作步骤如下：
（1）选择“工具宏录制新宏”命令，打开“录制新宏”对话框。
（2）在“宏名”文本框中输入要保存的宏名，在“快捷键”栏中可设置录制宏的快捷键，并在“保存在”下拉列表框中选择宏保存的位置，然后单击按钮开始录制。
（3）单击录制宏工具栏上的“相对引用”按钮，然后将需要的特殊符号输入到某个单元格中，再单击录制宏工具栏上的“停止”按钮即可完成宏的录制。
（4）选择“工具自定义”命令，打开“自定义”对话框，在“工具栏”选项卡中单击按钮，打开“新建工具栏”对话框。
（5）在“工具栏名称”文本框中输入要存放特殊符号的工具栏名称，然后单击按钮，新建的工具栏便显示在“工具栏”列表框和窗口中。
（6）单击“命令”选项卡，选择“类别”列表框中的“宏”选项。
（7）将“命令”列表框中的所有选项拖放到窗口中的新建工具栏上。
（8）在新建工具栏的“自定义按钮”上单击鼠标右键，在弹出的快捷菜单中选择“指定宏”命令，打开“指定宏”对话框。
（9）选中相应的宏，然后单击按钮。
（10）重复第7步，将按钮与相应的宏链接起来。
（11）关闭“自定义”对话框，之后就可以像使用普通工具栏一样使用新建工具栏了。
7.利用Word修复损坏的工作簿
用Word打开受损的Excel文档的操作步骤如下：
（1）在Word中通过“打开”对话框选择需要打开的Excel文件（在首次运用Word程序打开Excel文件时，需根据提示进行安装操作）。
（2）在打开的“打开工作表”对话框中按照提示选择打开整个工作簿或是某个工作表，单击按钮。
（3）将文件中被损坏的数据删除，再选择表格，选择“表格转换表格转换成文字”命令。
（4）在打开的对话框中选择“制表符”作为分隔符，单击按钮将表格内容转为文本内容，然后另存为纯文本文件。
（5）启动Excel，再打开保存的文本文件。
（6）根据“文本导入向导”对话框的提示将其打开。
（7）对修复工作表中的所有公式、文字、数字格式进行重新设置。
8.利用函数改变字母的大小写
在Excel中，可利用函数改变字母的大小写，方法有如下两种。
方法一：
在目标单元格中输入“=UPPER（源数据单元格）”，按【Enter】键即可将字母全部转换为大写。
方法二：
在目标单元格中输入“=LOWER（源数据单元格）”，将字母全部转换成小写；输入“=PROPER（源数据单元格）”，将字母转换成适当的大小写，如让每个单词的首字母大写等。
在Excel2007的单元格中显示公式
当在Excel2007工作表中多处使用公式或函数时，为了查看公式的输入是否正确，可在单元格中将公式显示出来，其方法如下：
单击“公式”选项卡，在“公式审核”组中单击按钮。
9.在Excel中使用数组公式
在Excel中输入数组公式的方法如下：
选择用来存放计算结果的单元格区域，在编辑栏中输入公式，然后按【Ctrl+Shift+Enter】组合键锁定数组公式，Excel将在公式两边自动加上括号“{}”（不要直接输入花括号，否则Excel认为输入的是一个正文标签）。
要编辑或清除数组公式时，需选择数组区域并激活编辑栏，公式两边的括号将消失，然后编辑或清除公式，最后按【Ctrl+Shift+Enter】组合键。
10.在Excel中隐藏单元格中的数据
在Excel中可隐藏单元格中的数据，具体操作步骤如下：
（1）选择所需的单元格区域，选择“格式单元格”命令，打开“单元格格式”对话框。
（2）在“数字”选项卡的“分类”列表框中选择“自定义”选项。
（3）在“类型”文本框中输入“；；；”。
（4）单击按钮。
11.在“相对引用”与“绝对引用”间切换
在选择的公式中可进行“相对”与“绝对”引用的快速转换，其方法如下：
定位插入点至有公式的单元格中，如果该公式为相对引用，按【F4】键即将该单元格公式中的所有引用变为绝对引用，再次按【F4】键则将引用又切换为相对引用。
12.在同一工作簿的不同工作表之间引用数据
设置同一工作簿不同工作表之间的数据引用的具体操作步骤如下：
（1）打开要引用的工作簿，在其中的一个工作表的某个单元格中输入一个等号“=”。
（2）单击工作表标签切换到另外一个工作表中，单击要引用公式的单元格，此时编辑栏中出现引用的单元格。
（3）公式中的单元格或单元格区域是相对引用，可添加符号$，更改为绝对引用。
宏代码去除法批量删除超链接
13.在Excel2007中用宏代码批量删除超链接的具体操作步骤如下：
（1）单击“视图”选项卡，单击“宏”按钮下边的按钮，在打开的下拉菜单中选择“录制宏”命令，打开“录制新宏”对话框。
（2）在“宏名”文本框中输入名称，如“RemoveHyperlinks”，单击关闭对话框。
（3）再次单击“宏”下拉按钮，在打开的下拉菜单中选择“查看宏”命令，打开“宏”对话框，在“宏名”列表框中选择刚才创建的宏。
（4）单击按钮，打开MicrosoftVisualBasic编辑器，用如下内容替换右侧窗口中的所有代码：
SubRemoveHyperlinks（）
‘Removeallhyperlinksfromtheactivesheet
ActiveSheet.Hyperlinks.Delete
EndSub
（5）保存关闭编辑器窗口，单击“宏”下拉按钮，在打开的菜单中选择“查看宏”命令，打开“宏”对话框，在“宏名”列表框中选择刚才创建的宏。
（6）单击按钮即可去除该工作表中的超链接。
14.对数值四舍五入
设置数值四舍五入的操作步骤如下：
（1）单击“常用”工具栏中的“插入函数”按钮，打开“插入函数”对话框。
（2）在“或选择类别”下拉列表框中选择“数学与三角函数”选项，在“选择函数”列表框中选择名为“ROUND”的函数（该函数有number和num-digits两个参数。
number是要进行四舍五入的数值，或用公式计算的结果；num-digits是希望结果数值的小数点后的位数）。
（3）单击按钮，在打开的“函数参数”对话框中进行设置。
巧用Excel2002的“智能鼠标”功能操作页面
通过“智能鼠标”的设置缩放页面、翻页或进行分级显示的具体操作步骤如下：
（1）在Excel中选择“工具选项”命令，打开“选项”对话框，在其中单击“常规”选项卡。
（2）选中“用智能鼠标缩放”复选框，单击按钮完成设置。
（3）在工作表中上下滚动鼠标滚轮，此时表格以递增或递减15%的比例放大或缩小。
（4）如果按住【Ctrl】键，再滚动鼠标滚轮，工作表进行上下翻页。
（5）在有分级显示的情况下，按住【Shift】键，再滚动鼠标滚轮，可以控制各分级条目的显示和隐藏。
15.巧用IF函数核对录入的数据
在Excel中使用IF函数可核对数据，其方法如下：
在输入的数据（如A1单元格）后面另增B1与C1单元格，C1单元格用做核对，在C1单元格中输入“=IF（A1=B1,正确,错误）即可。
当在A列中输完数据后，再在B列中重输一遍，如C列中提示“错误”，则表示该行数据中A列或B列有一项是错误的。
16.巧用IF函数避免计算错误
为了避免出现公式引用的单元格因无数据而出现类似“#DIV0!
”错误信息，可以使用以下方法。
使用IF函数将单元格的计算公式写为：
=IF（B2=0,,A2B2）。
当B2单元格的值为非零时，单元格的值将按A2B2进行计算更新。
17.快速在公式中输入不连续的单元格地址
在函数中输入比较长的单元格区域字符串时很麻烦，尤其是当字符串由许多不连续单元格区域组成时。
这时可以为这些单元格区域定义一个名称以简化操作，具体操作步骤如下：
（1）按住【Ctrl】键，依次选择不连续的单元格区域。
（2）选择“插入名称定义”命令，打开“定义名称”对话框，在对话框中输入单元格区域名称，将此区域命名，如“zuhe1”，单击按钮。
（3）在公式中使用这个名称，如“=SUM（zuhe1）”，按【Ctrl+Enter】组合键即可得到结果。
18.快速恢复上一次撤销的操作
用快捷键快速恢复上一次撤销的操作如下：
当撤销了某项操作后，按【F4】键或【Ctrl+Y】组合键可快速恢复上一次操作。
19.快速撤销上一次操作
用快捷键快速撤销上一次操作的方法如下：
当执行了错误操作后，按【Ctrl+Z】组合键即可快速撤销操作。
20.打开旧的宏
在Excel2003中，如果需要打开旧的宏（来自Excel972000），就需要设置宏安全性。
其具体操作步骤如下：
（1）选择“工具选项”命令，打开“选项”对话框。
（2）单击“安全性”选项卡，然后在其中单击按钮，打开“安全性”对话框。
（3）在其中选中“低”单选项，然后单击按钮。
21.持续显示单元格中的公式
输入一个公式后按【Enter】键，单元格中就不会再显示该公式，而只显示出计算结果。
当我们需要此单元格显示出公式而不是计算结果时，方法如下：
在单元格中输入公式后，选择“工具公式审核公式审核模式”命令，即可在单元格中显示公式；而如果想显示出公式计算结果时，再次选择该命令即可。
22.用快捷键快速关闭Excel窗口
按【Alt+F4】组合键即可快速关闭工作簿或Excel窗口。
计算Excel公式的一部分
在Excel中调试一个复杂的公式时，可能需要知道公式某一部分的值，这时可以使用以下办法来获得，具体操作步骤如下：
（1）在单元格内选择公式中需要计算值的部分。
（2）按【F9】键，Excel就将被选定的部分替换成计算的结果。
提示：
按【Ctrl+Z】组合键可以恢复刚才的替换。
如果选定的是整个公式，就可以看到最后的结果。
23.计算两个日期之间的天数
在Excel中计算两个日期之间的天数的方法如下：
将要放置计算结果的单元格格式设置为数值型，且小数位数为0，然后用公式将两个日期相减即可得到两个日期间相差的天数。
24.计算有单位的数值
计算有单位的数值的方法如下：
通过“单元格格式”对话框将有单位的单元格转换为数字单元格，进行计算后再通过“单元格格式”对话框加上单位。
25.设置不同工作簿的工作表间引用公式
设置不同工作簿的工作表之间的数据引用公式的操作步骤如下：
（1）打开要引用的两个工作簿，在其中一个工作簿的某个工作表的某个单元格中输入等号“=”。
（2）通过“窗口”菜单切换到另外一个已打开的工作簿，选择需要的工作表，再选择要引用的单元格。
此时编辑栏中将出现引用的公式。
（3）公式中的单元格或单元格区域是绝对引用，将$删除可以改为相对引用。
26.设置错误指示器的颜色
在编辑电子表格时，Excel会自动将其认为错误的内容用波浪线显示出来。
用户可通过设置改变波浪线的颜色，具体操作步骤如下：
（1）选择“工具选项”命令，打开“选项”对话框。
（2）单击“错误检查”选项卡，在“错误指示器颜色”下拉列表框中选择需要的颜色。
（3）单击按钮完成设置。
当Excel再次检查出错误信息时，就会按照设置的线条颜色进行显示。
27.追踪公式中的错误
当利用公式计算出的结果出现错误时，可快速追踪产生错误的引用单元格，从而检查出产生错误的原因。
其方法如下：
选择存放公式结果且包含错误的单元格，选择“工具公式审核追踪错误”命令，单元格中立即出现箭头，从公式中引用单元格处指向存放公式结果的单元格。
通过查看引用单元格的内容，即可检查出公式产生错误的原因。
28．通过对话框输入函数
在Excel中通过对话框输入函数的操作步骤如下：
（1）选择需输入函数的单元格，选择“插入函数”命令，打开“插入函数”对话框。
（2）在“或选择类别”下拉列表框中选择要插入的函数类别，在“选择函数”列表框中选择具体应用的函数。
（3）单击按钮，打开“函数参数”对话框。
（4）单击第一个文本框右边的按钮折叠对话框，在工作表中选择数据来源。
（5）单击按钮，再次展开“函数参数”对话框。
（6）单击按钮返回到工作表中，此时单元格中会自动显示函数计算的结果。
29.通过帮助学习函数
在Excel中应用复杂的函数时，如果只知道函数名称而不会具体应用，可以通过函数帮助来学习函数的应用，其具体操作步骤如下：
（1）选择“插入函数”命令，打开“插入函数”对话框。
（2）在“或选择类别”下拉列表框中选择想要查找的函数所属的类别，在“选择函数”列表框中选择要查找的函数。
（3）单击“插入函数”对话框左下角的“有关该函数的帮助”超链接，打开“MicrosoftExcel帮助”窗口，即可查看对该函数的介绍和该函数的具体应用方法。
30.通过数组公式简化计算
在数据输入过程中出现统计模式相同而引用单元格不同的情况时，可以通过数组公式来简化计算。
若在G3G8单元格区域中计算并填充“A3A8B3B8”的操作步骤如下：
（1）选择G3G8之间的区域，在编辑栏中输入公式，这里输入“=A3A8B3B8”。
（2）按【Ctrl+Shift+Enter】组合键，则公式变为“{=A3A8B3B8}”，同时G3G8之间的区域完成填充。
此时若单独改变G3G8之中的任何一个单元格，都会打开“MicrosoftExcel”对话框，提示用户不能更改数组的某一部分。
31.通过编辑栏输入函数
通过工具栏编辑函数的操作步骤如下：
（1）选择需输入函数的单元格，在编辑栏中输入“=”。
（2）单击编辑栏右边的按钮，在打开的下拉列表中选择需要的函数。
32.隐藏部分工作表
出于保密的目的，可将部分工作表隐藏起来，其方法如下：
选择“格式工作表隐藏”命令，将当前工作表隐藏起来。
如果想要显示隐藏的工作表，选择“格式工作表取消隐藏”命令，在打开的对话框中选择要取消隐藏的工作表，再单击按钮即可。
升级会员 