Excel若干使用技巧详解.docVIP

Excel若干使用技巧详解 一、前言 Excel作为常用的办公软件，其主要的功能包括数据的统计、分析，并为相关决策提供辅助，本文通过对其常用功能使用技巧的描述，为用户提供一种简便快捷的操作方法，解决使用Excel 过程中的一些困扰。 二、Excel若干使用技巧介绍 （一）在工作薄之间复制宏：（1）打开某个含有宏的工作薄，点击“工具/宏(M)…”，选中要复制的宏，选择“编辑”即可调出VB 编辑器界面。（2）点击“文件/导出文件”，在“文件名”框中自定义 文件名，或使用默认的文件名，文件扩展名是“.bas”， 选择“保存”。（3）将保存的文件拷贝到另一台PC，打开EXECL，选中“工具/ 宏/VB编辑器”，再次调出VB编辑器界面，点击“文件/导入文件”，然后在硬盘中找到要复制过来的文件，然后“打开”，退出VB编辑器。 （二）求某个字符出现的次数：例：求B1单元格中字符m出现的次数：=LEN(B1)-LEN(SUBSTITUTE(B1,m,)) （三）设置单元格是否可编辑：（1）选中所有单元格，右击鼠标，选择“设置单元格格式”---“保护”，取消“锁定”前面的“√”。（2）选定需要设备保护的单元格，用(1)中的方法在“锁定”前面打上“√”。（3）点击“工具”---“保护”---“保护工作表”，输入保护密码两次，按“确定”即可。 （四）控制每列数据长度：点击“数据”---“有效性”---“设置”，“有效性条件”设置为“允许”---“文本长度”=8（具体长度可根据需要更改），而且可以定义相关的提示信息、出错的警告信息和是否打开其它输入法等，定义好后点击“确定”。 （五）应用条件设置避免重复录入：选定M列，点击“格式”---“条件格式”，设置条件为：“公式=COUNTIF($M:$M,$M1)1”，选择“格式”---“字体”---“颜色”，选定红色后点击“确定”这样当输入数据重复时，字体将会自 动变为红色。 （六）建立下拉列表：选择某个要设置下拉菜单的单元格，点击“数据”---“有效性”---“设置”，在“允许”，选择“序列”，在“来源”文本框中输入下拉菜单的内容，内容之间用半角逗号隔开，如： X1,X2,X3,X4选中“提供下拉剪头”，选择“确定”。 （七）批量地处理按行排序的方法：针对大量的数据，需要将每一行按从大到小排序的操作方法，由于按行排序与按列排序都是要求一个主关键字，主关键字相同的条件下才能按次关键字排序。 这个问题的解决方法如下：（1）假设需要排序的数据的数据在B至D 列，请在E1单元格输入公式：=LARGE($A1:$D1,COLUMN(B1))；用填充柄将公式向下复制到相应行。（2）用鼠标选定E列，用“查 找/替换”的方式，将该列的$A替换成$B$，$D替换成$D$。（3）用鼠标选定E列所有有公式的单元格，用填充柄将公式向右复制到F列。 原有数的据将按行从大到小排序出现在E至F列。如有需要可用“选择性粘贴/数值”将公式复制到其他地方。备注：第（1）步的公式可根据自己的实际情况（数据范围）进行设置。 （八）某个单元格内文字行间距的调整：当某个单元格需要容纳大量文字内容时，做如下操作：右击某个单元格，点击“设置单元格格式”---“对齐”，将“水平对齐”选择为“靠左”，将“垂直对齐”选择为“分散对齐”，选中“自动换行”，点击“确定”。然后右击鼠标，选择“行高”，设置为适当的高度即可，也可直接拖动该单元格所 在的行调整行高。 （九）多工作表汇总求和的方法：假设在同一工作薄中，有SHEET1至SHEET99共99个相同格式的工作表需要 汇总求和，结果放在SHEET100工作表中，则右在SHEET100的任意单元格中（B1）输入如下公式：=SUM（单击SHEET1标签，按住Shift键的同时单击SHEET99标签，单击B1单元格，再输 入：），此时公式看上去内容如下： =SUM(SHEET1:SHEET99!B1)按回车后公式变为 =SUM(SHEET1:SHEET99!B1) ，因此，最方 便快捷的操作就是在SHEET100的B1单元格直接输入公式： =SUM(SHEET1:SHEET99!B1)然后按回车。 （十）替换通配符“?”和“*”的方法：在EXECL表中更行查找和替换的时候，?表示任意单个字符，*表示任意多个字符。如果要将Excel中的“?”、“*”替换成其他的字符，在查框中输入~?~与~*~即可进行替换。 （十一）EXECL中排名次的两种方法：方法一是使用用RANK()函数：假设B列为成绩，C列为名次，C2单元格公式如下：=RANK(B2,E:E)，在分数相同时，名次也是相同的，后面的名次将空缺，