Excel应用技巧.docx
《Excel应用技巧.docx》由会员分享，可在线阅读，更多相关《Excel应用技巧.docx（76页珍藏版）》请在冰豆网上搜索。
Excel应用技巧
EXCEL应用技巧
编辑与操作3
如何在Excel中快速输入大写中文数字3
如何在Excel中实现多个工作表的页眉和页脚同时设置3
如何在Excel中实现单元格文字随时换行3
如何在Excel中快速插入空白行3
如何在Excel中消除0值3
如何在Excel中批量转换日期格式3
如何在Excel中快速选定“空白”和“数据”单元格4
如何在Excel中防止误改4
如何在Excel中快速隐藏4
如何在Excel中用条件格式为单元格自动加边框4
如何在Excel中隔行调整行高5
如何在Excel中只选中包含文本的单元格8
如何在Excel中巧用右键拖移实现快速复制10
如何利用宏给Excel工作簿文档自动添加密码14
如何用单元格数据作为Excel工作簿名称15
如何在Excel中实现多条件求和18
如何让隐藏的Excel工作表别人无法取消隐藏22
如何快速找到所需要的Excel函数25
如何利用Excel记录单快速录入表格数据27
如何对Excel作数据校验27
如何在Excel单元格中多行与一行并存29
如何在同一Excel单元格中混用文本与数值数据31
如何急救受损的Excel文件32
Excel公式应用常见错误及处理34
#DIV/0!
错误34
#N/A错误35
#NAME?
错误37
#NUM!
错误39
#VALUE 错误39
#REF!
错误41
#NULL!
错误42
如何实现Excel表格数据行列巧互换43
Excel鲜为人知的秘技44
建立分类下拉列表填充项44
建立“常用文档”新菜单44
让不同类型数据用不同颜色显示45
制作“专业符号”工具栏46
用“视面管理器”保存多个打印页面47
让数据按需排列47
把数据彻底隐藏起来48
让中、英文输入法智能化地出现48
让“自动更正”输入统一的文本48
在Excel中自定义函数49
表头下面衬张图片49
用连字符“&”来合并文本49
快速打印学生成绩条50
同时查看不同工作表中多个单元格内的数据50
为单元格快速画边框51
控制特定单元格输入文本的长度51
成组填充多张表格的固定单元格52
改变文本的大小写53
提取字符串中的特定字符54
把基数词转换成序数词54
用特殊符号补齐位数55
创建文本直方图56
计算单元格中的总字数57
关于欧元的转换57
给表格做个超级搜索引擎58
Excel工作表大纲的建立60
插入“图示”61
熟悉Excel的“从文本到语音”62
Excel中“摄影”功能的妙用64
在多张表格间实现公用数据的链接和引用65
“驯服”Excel的剪贴板窗口66
利用公式审核工具查看数据出处66
巧用Excel的“智能鼠标”67
Excel“监视”窗口的应用67
在使用EXCEL的过程中，我们通常会遇到一些小问题，看似简单，一时半会儿又研究不通解决不了，很让人头痛。
现在信息系统部从编辑与操作、宏和公式、函数等几个方面给大家整理了一些处理方法，带大家找找解决这些问题的思路。
◆编辑与操作
●如何在Excel中快速输入大写中文数字
将光标移至需要输入大写数字的单元格中。
利用数字小键盘在单元格中输入相应的小写数字（如12345）。
右击该单元格，点击“设置单元格格式”，从弹出的“单元格格式”对话框中选择“数字”选项；然后从“类型”列表框中选择“中文大写数字”选项。
最后单击“确定”按钮，这时输入的12345就自动变成“壹万贰仟叁佰肆拾伍”。
●如何在Excel中实现多个工作表的页眉和页脚同时设置
我们有时要把一个Excel文件中的多个工作表设置成同样的页眉和页脚，分别对一张张工作表去设置感觉很烦琐。
如果用下面的方法就可以一次将多个工作表中的页眉和页脚同时设置好：
把鼠标移到工作表的名称处（如果没有给每张表取名的话，Excel自动设置的名称就是Sheet1、Sheet2、Sheet3等等），然后点右键，在弹出的菜单中选择“选择全部工作表”的菜单项，这时再进行页眉和页脚设置就是针对全部工作表了。
●如何在Excel中实现单元格文字随时换行
在Excel中，我们有时需要在一个单元格中分成几行显示文字等内容。
那么实现的方法一般是通过选中格式菜单中的“单元格”下“对齐”的“自动换行”复选项，单击“确定”即可，这种方法使用起来不是特别随心所欲，需要一步步地操作。
还有一种方法是：
当你需要重起一行输入内容的时候，只要按住Alt键的同时按下回车键就可以了，这种方法又快又方便。
●如何在Excel中快速插入空白行
如果想在某一行上面插入几行空白行，可以用鼠标拖动自此行开始选择相应的行数，然后单击右键，选择插入。
如果在每一行上面均插入一空白行，按住Ctrl键，依次单击要插入新行的行标按钮，单击右键，选择插入即可。
●如何在Excel中消除0值
有Excel中当单元格计算结果为0时，默认会显示0，这看起来显然有点碍眼。
如果你想显示0时，显示为空白，可以试试下面的方法。
打开“工具→选项→视图”，取消“0值”复选项前的√，确定后，当前工作表中的值为0的单元格将全部显示成空白。
●如何在Excel中批量转换日期格式
以前在Excel中输入职工出生时间时，为了简单都输入成“yymmdd”形式，但上级部门一律要求输入成“yyyy-mm-dd”格式，那么一千多名职工出生时间肯定不能每个手工转化。
最快速的方法是：
先选定要转化的区域。
点击“数据→分列”，出现“文本分列向导”对话框。
勾选“固定宽度”，连续两次点击“下一步”按钮，在步骤三对话框的“列数据格式”中，选择“日期”，并选定“YMD”形式，按下“完成”按钮，以前的文本即转化成了需要的日期了。
●如何在Excel中快速选定“空白”和“数据”单元格
在Excel中，经常要选定空白单元格，逐个选定比较麻烦，如果使用下面的方法就方便多了：
打开“编辑→定位”，在“定位”窗口中，按下“定位条件”按钮；选择“定位条件”中的“空值”，再按“确定”，空白单元格即被全部选定。
如果要选定只含数据的单元格，在上面方法的“定位条件”窗口中，选择“常量”，再点“确定”，含有数据的单元格全部选定。
●如何在Excel中防止误改
在包含多个工作表的工作薄中，为了防止误修改，我们常常采取将行（列）隐藏或者设置编辑区域的方法，但是如果要防止整个工作表的误修改怎么办呢?
单击“格式→工作表→隐藏”，将当前的工作表隐藏，这样操作者连表格都看不到，误操作就无从谈起了。
要重新显示该表格，只须单击“格式→工作表→取消隐藏”。
要注意的是：
如果设置了工作表保护，则不能进行隐藏操作。
●如何在Excel中快速隐藏
在打印工作表时，我们有时需要把某些行或者列隐藏起来，可是用菜单命令或调整行号（列标）分界线的方法比较麻烦，介绍一个简单方法：
在英文状态下，按“Ctrl+9”或“Ctrl+0”组合键，就可以快速隐藏光标所在的行或列。
●如何在Excel中用条件格式为单元格自动加边框
Excel有许多“自动”的功能，如能合理使用，便会效率倍增。
经过试验，本人找到一种利用条件格式为Excel单元格自动添加边框的方法，可谓“所键之处，行即成表”。
下面是具体的步骤：
1.在首行中选择要显示框线的区域，如本例中的A1:
D1。
2.执行“格式”→“条件格式”，打开“条件格式”对话框。
单击打开“条件1”下拉列表，单击选择“公式”，在随后的框中输入下面的公式“=OR（$A1<>"",$B1<>"",$C1<>"",$D1<>""）”，意即只要A1、B1、C1或D1中有一个单元格内存在数据，将自动给这四个单元格添加外框线。
注意：
公式中的单元格引用为混合引用，如改为相对引用则效果不同，朋友们可以一试。
3.单击“格式”按钮，打开“单元格格式”对话框，切换到“边框”选项卡，为符合条件的单元格指定外边框。
如图1。
4.选择A1:
D1区域，复制，选择A→D列，粘贴（如果A1:
D1中已输入数据，可执行“编辑”→“选择性粘贴”→“格式”），这样就把第2步中设置的格式赋予了A→D列的所有单元格。
如图2。
在这四列中任意一个单元格中输入数据（包括空格），此行（A→D列）各单元格将会自动添加框线。
如图3。
提示：
对于已经显示条件格式所设置框线的单元格，仍然允许在“单元格格式”对话框的“边框”选项卡中设置其框线，但外边框不能显示（使用工具栏上的“边框”按钮进行设置也是如此，除非删除条件区域内的全部数据），只能显示斜线。
●如何在Excel中隔行调整行高
要求把一份Excel表格的偶数行行高调整一下。
这份表格可是有上百行的，逐一调整行高显然是不科学的。
如下方法可实现：
一、直接定位法
先在表格的最后增加一个辅助列。
在该列的第一行的单元格中输入数字“1”，然后在第二行的单元格中输入公式“=1/0”，回车后会得到一个“#DIV/0!
”的错误提示。
现在选中这两个单元格，将鼠标定位于选区右下角的填充句柄，按下鼠标右键，向下拖动至最后一行。
松开鼠标后，在弹出的快捷菜单中选择“复制单元格”的命令。
好了，现在该列的奇数行均是数字1，而偶数行则都是“#DIV/0!
”的错误提示了，如图1所示。
点击菜单命令“编辑→定位”，在打开的“定位”对话框中点击“定位条件”按钮，然后在打开的“定位条件”对话框中，选中“公式”单选项，并取消选择除“错误”以外的其它复选项，如图2所示。
确定后，就可以看到，所有的错误提示单元格均处于被选中状态。
现在我们所需要做的，只是点击菜单命令“格式→行→行高”，然后在打开的“行高”对话框中设置新的行高的值就可以了，如图3所示。
行高调整完成后，记得将辅助列删除。
二、筛选法
也是先增加一个辅助列。
然后在该列的第一个单元格输入数字“0”，第二行的单元中输入“1”。
选中这两个单元格，然后按下右键后向下拖动填充句柄，并在弹出的快捷菜单中选择“复制单元格”命令。
现在点击菜单命令“数据→筛选→自动筛选”。
点击辅助列第一个单元格的下拉按钮，在列表中选择“1”，如图4所示。
单击后，则可将数值为“1”的单元格筛选出来。
选中该列所有数值为1的单元格，点击菜单命令“格式→行→行高”，设置需要的行高。
最后，别忘了，再次点击菜单命令“数据→筛选→自动筛选”，取消“自动筛选”前的对勾，使全部数据正常显示出来。
三、选择性粘贴法
相比之下，这种方法说简单多了。
先选中第二行，调整其行距至合适。
然后点击左侧行号，选中第一行和第二行，点击“复制”按钮。
然后点击左侧行号，选中其余各行。
再点击菜单命令“编辑→选择性粘贴”，打开“选择性粘贴”对话框。
点击其中的“格式”单选项，如图5所示。
这样，就可以得到需要的效果了。
此法固然简单，但它只适合于各行的格式完全一致的情况。
如果某行中有合并单元格或者行与行的格式并不完全一致，那么此法就不太好用了。
四、格式刷法
如果选择性粘贴法能用的话，那么，格式刷当然也能用。
如同上法，先调整好第一行和第二行，选中它们，点击“格式刷”按钮。
鼠标变成小刷子形状时，点击左侧行号至需要的选区。
这样，就可以获得调整行高的目的。
需要注意的是，各行的格式应该完全一致。
此外，必须点击左侧行号选中整行操作，否则，行高是不会调整的。
●如何在Excel中只选中包含文本的单元格
在一个Excel工作表中，通常会包含许多类型的数据，诸如文本、数值、货币、日期、百分比等等，而有时会需要从这些不同类型的数据中只选中某种类型的数据，例如文本，然后对其进行删除、填充、锁定或修改格式等操作。
本文要介绍的就是如何在Excel中实现只选中包含文本的单元格。
具体操作步骤如下。
方法一：
使用“定位条件”
1.按F5键，或选择菜单命令“编辑|定位”（也可按快捷键Ctrl+G），打开如图1所示的“定位”对话框。
图1
2.在“定位”对话框中，单击“定位条件”按钮。
3.在“定位条件”对话框中，选择“常量”，如图2所示，然后只选中“文本”复选框，选中后单击“确定”按钮即可。
同理，如果要只选择工作表中的数字，也可以用上述同样的方法。
图2
方法二：
使用条件格式
使用“条件格式”可以一次性改变特定类型数据的格式，可以改变的格式包括字体样式、下划线、删除线和颜色。
例如，我们意图将工作表中所有的文本都改为红色，通常的做法是先选中这些文本，然后再改变其颜色，而使用“条件格式”会很快完成这一操作。
1.在工作表中选择包含数据的区域。
2.选择菜单命令“格式|条件格式”。
3.在“条件格式”对话框中，从“条件1”下方选择“公式”，然后在右方输入框中输入=Istext（A1），如图3所示。
图3
4.单击“条件格式”对话框中的“格式...”按钮，打开“单元格格式”对话框，然后将颜色设置为红色，如图4所示。
图4
5.单击“确定”按钮回到“条件格式”对话框，然后再单击“确定”按钮，关闭对话框即可。
使用这种方法，当以后再次向该区域中添加文本时，文本也会自动变为红色。
●如何在Excel中巧用右键拖移实现快速复制
在Excel工作表中，我们经常会将一个单元格或区域中的数据复制到另一位置。
实现复制Excel表格数据的方法有许多种，最基本的是采用“编辑”菜单或鼠标右键中的复制/粘贴命令，或者使用快捷键Ctrl+C和Ctrl+V。
其实，还有一种鲜为人知的方法，让我们可以用最省时省力的操作来实现数据的快速复制。
下面通过实例向大家介绍这一技巧。
1.例如，我们要将如图1所示的A13:
E21中的内容复制到A1:
E9处。
首先选中A13:
E21。
图1
2.移动鼠标指针到选中区域的黑色边框处，直到鼠标指针变为如图2所示的形状。
图2
3.这时按下鼠标右键拖动鼠标，当移动到A1:
E9时，松开右键，出现如图3所示菜单，单击“链接此处”。
图3
这样就实现了所选内容的快速复制，结果如图4所示。
图4
举一反三：
细心的读者一定会注意到，如图3所示的右键菜单中还有许多其它命令，都是跟复制和移动有关的，以后如果要实现这些相关操作，都可以用以上所介绍的方法来实现。
●如何利用宏给Excel工作簿文档自动添加密码
在Excel中在给工作簿文档添加密码时，需要通过选项一个一个的设置，比较麻烦。
下面，我们利用一个自动运行的宏，让软件自动给文档添加密码。
1、启动Excel，执行“工具→宏→VisualBasic编辑器”命令，进入VBA编辑状态（如图1）。
2、在左侧的“工程资源管理器”窗口中，选中“VBAproject（PERSONAL.XLS）”（个人宏工作簿）选项。
3、执行“插入→模块”命令，插入一个模块（模块1）。
4、将下述代码输入到右侧的代码编辑窗口中：
SubAuto_close（）
ActiveWorkbook.Password="123456"
ActiveWorkbook.Save
EndSub
退出VBA编辑状态。
注意：
这是一个退出Excel时自动运行的宏，其宏名称（Auto_close）不能修改。
5、以后在退出Excel时，软件自动为当前工作簿添加上密码（123456，可以根据需要修改），并保存文档。
●如何用单元格数据作为Excel工作簿名称
在Excel中，通常用Book1、Book2……作为工作簿名称。
能不能让Excel采用我们选定的某个单元格中的数据做为工作簿名称来保存文档呢?
答案是肯定的。
1、启动Excel，执行“工具→宏→VisualBasic编辑器”命令，进入VBA编辑状态（如图1）。
2、在左侧的“工程资源管理器”窗口中，选中“VBAproject（PERSONAL.XLS）”（个人宏工作簿）选项。
3、执行“插入→模块”命令，插入一个模块（模块1）。
4、将下述代码输入到右侧的代码编辑窗口中：
Subbaocun（）
lj=InputBox（"请输入文档保存路径"）
ActiveWorkbook.SaveAsFilename:
=lj&ActiveCell.Value&".xls"
EndSub
退出VBA编辑状态。
5、以后要保存某个工作簿文档时，先选中作为名称的字符所在的单元格（参见图2），然后执行“工具→宏→宏”命令，打开“宏”对话框（如图3）。
6、选中刚才编辑的宏（PERSONAL.XLS!
baocun），单击“执行”按钮，系统弹出如图4所示的对话框。
7、输入保存文档的路径（如“E:
\office技巧\”），单击“确定”按钮。
文档保存成功（参见图5）。
注意：
如果不需要保存路径，文档将被保存到“我的文档”文件夹中。
●如何在Excel中实现多条件求和
在平时的工作中经常会遇到多条件求和的问题。
如图1所示各产品的销售业绩工作表，我们希望分别求出“东北区”和“华北区”两部门各类产品的销售业绩，或者在同一部门中的不同组也要求出各产品的销售业绩。
在Excel中，我们可以有三种方法实现这些要求。
一、分类汇总法
首先选中A1:
E7全部单元格，点击菜单命令“数据→排序”，打开“排序”对话框。
设置“主要关键字”和“次要关键字”分别为“部门”、“组别”，如图2所示。
确定后可将表格按部门及组别进行排序。
然后将鼠标定位于数据区任一位置，点击菜单命令“数据→分类汇总”，打开“分类汇总”对话框。
在“分类字段”下拉列表中选择“部门”，“汇总方式”下拉列表中选择“求和”，然后在“选定汇总项”的下拉列表中选中“A产品”、“B产品”、“C产品”复选项，并选中下方的“汇总结果显示在数据下方”复选项，如图3所示。
确定后，可以看到，东北区和华北区的三种产品的销售业绩均列在了各区数据的下方。
再点击菜单命令“数据→分类汇总”，在打开的“分类汇总”对话框中，设置“分类字段”为“组别”，其它设置仍如图3所示。
注意一定不能勾选“替换当前分类汇总”复选项。
确定后，就可以在区汇总的结果下方得到按组别汇总的结果了。
如图4所示。
二、输入公式法
上面的方法固然简单，但需要事先排序，如果因为某种原因不能进行排序的操作的话，那么我们还可以利用Excel函数和公式直接进行多条件求和。
比如我们要对东北区A产品的销售业绩求和。
那么可以点击C8单元格，输入如下公式:
=SUMIF（$A$2:
$A$7,"=东北区",C$2:
C$7）。
回车后，即可得到汇总数据。
选中C8单元格后，拖动其填充句柄向右复制公式至E8单元格，可以直接得到B产品和C产品的汇总数据。
而如果把上面公式中的“东北区”替换为“华北区”，那么就可以得到华北区各汇总数据了。
如果要统计“东北区”中“辽宁”的A产品业绩汇总，那么可以在C10单元格中输入如下公式:
=SUM（IF（$A$2:
$A$7="东北区",IF（$B$2:
$B$7="辽宁",Sheet1!
C$2:
C$7）））。
然后按下“Ctrl+Shift+Enter”键，则可看到公式最外层加了一对大括号（不可手工输入此括号），同时，我们所需要的东北区辽宁组的A产品业绩和也在当前单元格得到了，如图5所示。
拖动C10单元格的填充句柄向右复制公式至E10单元格，可以得到其它产品的业绩和。
把公式中的“东北区”、“辽宁”换成其它部门或组别，就可以得到相应的业绩和了。
三、分析工具法
在EXCEL中还可以使用“多条件求和向导”来方便地完成此项任务。
不过，默认情况下EXCEL并没有安装此项功能。
我们得点击菜单命令“工具→加载宏”，在打开的对话框中选择“多条件求和向导”复选项，如图6所示。
准备好Office2003的安装光盘，按提示进行操作，很快就可以安装完成。
完成后在“工具”菜单中会新增添“向导→条件求和”命令。
先选取原始表格中A1:
E7全部单元格，点击“向导→条件求和”命令，会弹出条件求和的向导对话框，在第一步中已经会自动添加了需要求和计算的区域，如图7所示。
点击“下一步”，在此步骤中添加求和的条件和求和的对象。
如图8所示。
在“求和列”下拉列表中选择要求和的数据所在列，而在“条件列”中指定要求和数据应满足的条件。
设置好后，点击“添加条件”将其添加到条件列表中。
条件列可多次设置，以满足多条件求和。
点击“下一步”后设置好结果的显示方式，然后在第四步中按提示指定存放结果的单元格位置，点击“完成”就可以得到结果了。
如果要对多列数据按同一条件进行求和，那么需要重复上述操作。
●如何让隐藏的Excel工作表别人无法取消隐藏
在Excel中，通常隐藏工作表的操作方法如下:
把需要隐藏的工作表激活成当前工作表，执行一下“格式→工作表→隐藏”命令，即可将其隐藏起来。
这样隐藏的工作表，通过执行“格式→工作表→取消隐藏”命令，打开“取消隐藏”对话框（如图1），选中需要显示出来的工作表名称，单击一下“确定”按钮即可将其显示出来。
今天，我给大家介绍一种隐藏工作表的方法，通过这种方法隐藏的工作表，别人显示不出来。
1、启动Excel，打开相应的工作簿文档。
2、按下Alt+F11组合键进入VBA编辑状态（如图2）。
3、按下F4功能键，展开“属性”窗口（参见图3）。
4、选中相应工作簿中需要隐藏的工作表（如“Sheet3（PPT）”），然后在下面的属性窗口中，找到“Visible”选项，单击其右侧的下拉按钮，在随后出现的下拉列表中，选择“0-xlSheetVeryHidden”选项。
注意:
每个工作簿文档中，至少要有一个工作表不被隐藏。
5、再执行“工具→VBAProject属性”命令，打开“VBAProject-工程属性”对话框（如图3）。
6、切换到“保护”标签下，选中“查看时锁定工程”选项，并输入密码，确定返回（参见图3）。
7、退出VBA编辑状态，保存一下工作簿文档，隐藏实现。
经过这样的设置以后，我们发现“格式→工作表→取消隐藏”命令是灰色的，无法执行;如果想通过VBA编辑窗口修改属性，发现需要提供密码（如图4），不知道密码就无法取消隐藏了。
●如何快速找到所需要的Excel函数
面对众多的Excel函数，想必没有几位朋友可以把它们记得清清楚楚吧。
哪天真正要用到这些函数时，您又该怎么办呢？
也许有的朋友会翻阅相关的书籍，有的朋友会查询Excel的随机帮助。
可我在使用Excel函数时几乎很少需要查这查那的，我会让Excel自己帮我把需要的函数找出来。
想知道我是怎么操作的吗？
（以下操作技巧已在微软Office2003版本上测试通过）
操作步骤如下:
1.打开Excel软件
2.执行“插入”菜单→“函数”命令，弹出如图1所示的窗口
图1
3.在图1窗口中，不知大家注意到箭头标注的那个区域没有，这其实就是一个小型的函数搜索器，在这里我们可以像搜索引擎那样输入需要查找的函数描述，然后点击“转到”按钮等待查询结果即可。
如图2所示
图2
【小提示】此处输入的函数描述要求尽量简明，最好能用两个字代替，比如“统计”、“排序”、“筛选”等等，否则Excel会提示“请重新表述您的问题”而拒绝为您进行搜索
4.怎么样？
结果很快就出来了吧。
要是您还是觉得Excel所推荐的函数过多而拿不准主意用哪个时，还可以在图2的函数列表中依次点
升级会员 