《EXCEL的一些技巧应用(2)》由会员分享，可在线阅读，更多相关《EXCEL的一些技巧应用(2)（10页珍藏版）》请在金锄头文库上搜索。
1、EXCEL 的一些技巧应用（2）1、快速找出 Excel 表格中错误的身份证信息作为办公人员，经常要编写关于职工信息档案的表格，其中最常输入的数据就是身份证号码，由于人员众多，输入确实麻烦，一不小心就可能把员工的出生日期弄错了，这可是关系到每个员工的切身利益，如工龄、社会保险金等。要是逐一验证那可是一件让人头疼的事情。下面笔者就教大家一招，通过此方法就能够很快查找出很快查找出 Excel 表中错误的身份证号码表中错误的身份证号码。下面以“XXX 公司员工信息统计表”为例(图 1)。首先从 B 列的身份证号码(注：输入前必须将该列单元格格式统一为“文本”)中提取出生日期到 C列，以 C3 为例，
2、选中 C3 单元格，然后在“编辑栏”中输入如下公式：=TEXT(IF(LEN(B3)=15，“19“，)宏名为：MyMicro;快捷键为：Ctrl+Shift+J(只要不和 Excel 本身的快捷键重名就行);保存在：个人宏工作簿(可以在所有 Excel 工作簿中使用)。2.用鼠标选择“停止录入”工具栏中的方块，停止录入宏。3.选择 Excel 选单下的“工具宏Visual Basic 编辑器”选项。4.在“Visual Basic 编辑器”左上角的 VBAProject 中用鼠标双击 VBAProject (Personal.xls) 打开“模块Module1”。注意：你的模块可能不是 Mo
3、dule1 ，也许是 Module2、Module3。5.在右侧的代码窗口中将 Personal.xls-Module1(Code)中的代码更改为：Sub MyMicro( )OldValue = Val (ActiveCell.Value )InputValue = InputBox ( “输入数值，负数前输入减号”,“小小计算器”)ActiveCell.Value = Val (OldValue + InputValue)End Sub6.关闭 Visual Basic 编辑器。编辑完毕，你可以试试刚刚编辑的宏，按下 Shift+Ctrl+J 键，输入数值并按下“确定”键。3、让 Exce
4、l 表格能录音与回放在给学生讲 Excel 表格时，很多学生感觉到它很难。问我可不可以把我讲课的内容录制下来，让他们拿回家再多看几遍。我想可以用录像机把我讲课的内容录下来，但操作不方便，又费时费力。我想学生只是要看我讲课的内容，又不是看我讲课的台风，如果只把这讲课的内容录下来不就行了吗?经过研究，我找到了一种方法，既可以让学生看见我的 Excel 表格操作步骤，又可以听见我讲话的声音。特写下来以供有这方便需求的读者参考。操作步骤：第一步：打开 Excel单击菜单中的“插入”按钮选择“对象”。第二步：在“对象”对话框中选择“音效”单击“确定”按钮。第三步：单击“开始录制声音”按钮。这时就可以边操
5、作边讲话了。所讲的话全部被录在电子表中了。(注：这种录单方式只能录 60 秒，当然我们可以让它多录制一会，方法是：当快录制完时单击一下“停止录制”按钮，再单击“开始录制声音”按钮，这样循环往复，便可以长时间录制了)第四步：全部录制完毕后单击“文件”菜单中的“退出”按钮再弹出的对话框中单击“是”按钮。最后把这个文件保存起来拿给学生，学生只需要双击电子表格中的图标便可以听见声音了。4、快速输入部分重复的数据在使用 Excel 进行报表处理时，我们常常会输入部分重复的数据部分重复的数据，比如同一地区的身份证号码，前面部分是相同的数字组合。如何快速输入带有部分重复的数据内容呢？其实这个问题可通过 Ex
6、cel 单元格的自定义功能轻松解决。操作过程如下：首先选定设置格式的单元格，然后单击“格式”菜单中的“单元格”命令，打开“单元格格式”对话框。单击“数字”选项卡，在“分类“列表中选定“自定义”选项，任选一种内置格式(一般选择不常用到的格式)，在“类型”框中输入重复部分的数字，如某一地区的身份证号码的前 6 位数字“123456”(一定要在数字上加上双引号)，单击“确定”按钮后退出，这样在输入身份证号码时，只需输入 123456 之后的数字即可。5、Excel 自定义公式防止输入重复数据大家在使用 Excel2007 编辑工作表格时经常要输入大量数据，有时要求输入的这些数据是不允许重复的，例如常
7、见的身份证号码等，一旦错误输入查找起来那可是相当麻烦的一件事。对于这个问题，其实我们可以自定义公式，让 Excel 对重复数据能够自动弹出警告信息，以便及时提醒操作人员。Step1：运行 Excel 2007 程序并新建一个工作表格，然后按下“Ctrl+A ”组合键全选该表格。Step2：单击“数据”菜单，然后在“数据工具”功能区域中选择“数据有效性”选项，接下来在随即弹出的“数据有效性”对话框中选择“设置”选项卡，在“允许”下拉列表框中选择“自定义”标签，在“公式”下面的文本框中输入 “=COUNTIF($1:$65535，A1)=1”(不含双引号)。注意：这里的“$1:$65535”表示对
8、全工作表范围进行重复检查。 如果你需要检查的只是某一特定的单元格范围，可以根据实际情况重新进行调整，但必须使用绝对方式。Step3：切换到“出错警告”选项卡，在这里程序已经自动勾选了“输入无效数据时显示出错警告”的复选框，接下来将“样式”设置为“停止”选项，然后在右侧的“标题”和“错误信息”两个文本框中可以自行输入相关的内容、具体的警告信息等等，然后单击“确定”按钮即可。经过设置之后，今后重复输入相关数据时，程序会自动弹出一个对话框，提示“有重复数据出现”，提供“重试”、“取消”、“帮助”三个选项。 选择“重试”，则对现有数据进行检查校核；选择“取消”可以重新输入。 6、支持粘贴预览 Exce
9、l 2010 粘贴效果未卜先知Excel 的选择性粘贴功能是非常强大的，但是在以往的 Excel 版本中有一个小小的遗憾，就是只有粘贴之后才能看到粘贴的样子。如有的时候不知道在选择性粘贴中的选项最后的样子，有时数据粘贴过来是科学计数法，有的时候连公式一起粘贴过来，因此让人觉得有点晕。在 Excel 2010 中，复制内容之后，在目标单元格上单击鼠标右键，在选择性粘贴右侧有个箭头，单击箭头会出现选择性粘贴的所有粘贴方式，而且按粘贴方式分成了大类，当鼠标停在某个粘贴选项上时，在 Excel 中会出现最终粘贴样式的预览，让用户一眼就可以知道粘贴之后是什么样子的，这样也就不会出现粘贴过来是科学计数法的
10、尴尬了。在 Office 2010 的其他主要组件如 Word 2010 和 PPT 2010 中也有粘贴预览功能。7、为 Excel2007 多个工作表快速创建目录我们经常把同类相关 Excel 工作表集中保存在同一文档中，以便于在各表格间进行引用、查看。当一个文档中的工作表达到一定数量时，要想找到需要的工作表就变得很麻烦了。此时若能建立一张“目录”工作表显示所有工作表的名称和链接，事情将会简单很多。下面介绍一种可以快速为快速为 Excel 工作簿创建工作表目录工作簿创建工作表目录的方法。定义名称定义名称打开 Excel 2007，右击第一张工作表标签选择“重命名”，把它重命名为“目录”工作
11、表。选中 B1单元格，切换到“公式”选项卡，单击“定义名称”，在弹出的“新建名称”窗口中输入名称“工作表名”，在引用位置中则输入公式=INDEX(GET.WORKBOOK(1)，$A1)&T(NOW()，单击确定即可定义出一个名为“工作表名”的名称(图 1)。公式中 GET.WORKBOOK(1)用于提取当前工作簿中所有工作表名称，INDEX 函数则按 A1 中的数字决定要显示第几张工作表的名称。此外，由于宏表函数 GET.WORKBOOK(1)在数据变动时不会自动重算，而 NOW()是易失性函数任何变动都会强制计算，因此我们需要在公式中加上 NOW()函数才能让公式自动重算。函数 T()则是
12、将 NOW()产生的数值转为空文本以免影响原公式结果。注：宏表函数 GET.WORKBOOK，不能直接在单元格公式中使用，必须通过定义名称才能起作用。目录设置目录设置在“目录”工作表的 A1 单元格输入 1，在 B1 单元格输入公式=IFERROR(HYPERLINK(工作表名&“!A1“，RIGHT(工作表名，LEN(工作表名)-FIND(“，工作表名)，“)。公式表示当名称“工作表名”的值为错误值时显示为空“”，否则创建指向“工作表名!A1”的超链接并显示该“工作表名”。然后选中 A1：B1 单元格，把鼠标指向选中区右下角的“填充柄”按住鼠标左键向下拖动到 300 行，把公式和编号填充出
13、300 行，在 A、B 列就会马上自动列出所有工作表目录(图 2)。单击相应工作表名称即可快速切换到该工作表中。请参照可能的最多工作表个数来决定向下填充行数，一般 300 个应该够了。注：公式中 RIGHT(工作表名，LEN(工作表名)-FIND(“，工作表名)这段函数的作用是除去“工作表名”中“”以前的内容。若你不介意工作表名称前显示“BOOK1.xlsx”一类内容的话，可以把 B1 中的公式简化成=IFERROR(HYPERLINK(工作表名&“!A1“，工作表名)，“)。保存设置保存设置切换到“开始”选项卡适当设置一下目录中的字体、字号和颜色等等，建议把字号放大并设置加粗以便查看，还要调
14、整一下 A：B 列的列宽以便完全显示工作表名称。然后右击其他工作表标签选择“删除”，把所有其他工作表全部删除只保留一张“目录”工作表。最后单击“Office”按钮，选择“另存为”，在弹出的另存为窗口中选择保存类型为“Excel 启用宏的模板(*.xltm)”格式、文件名为“目录.xltm”，保存到 C:Program FilesMicrosoft OfficeOffice12XLSTART 文件夹下，关闭 Excel 2007 退出。若你的 Office 不是按默认路径安装，请按实际安装路径修改。三秒创建目录三秒创建目录以后要为工作簿创建目录就简单了，只要用 Excel 2007 打开要创建目
15、录的工作簿，在第一张工作表的标签上右击选择“插入”，在“插入”窗口中双击选择“目录”，即可在第一张工作表前插入一张“目录”工作表，并显示出所有工作表目录。这操作有 3 秒就够了吧？在“目录”工作表中，可通过对目录进行筛选、排序、查找来快速找到工作表名，然后单击工作表名即可打开相应工作表。创建目录后，在这个工作簿中增加、删除工作表或者修改工作表名称，“目录”工作表中的工作表目录都会自动更新。此外，前面我们只复制了 300 行目录公式，因此工作表总数超过 300个时，超出的工作表名就不会显示了，得把“目录”工作表中 A1：B1 的公式再向下复制填充才行。由于宏表函数 GET.WORKBOOK(1)
16、是通过宏功能起作用的，所以插入了工作表目录的文档最后都必须以“Excel 启用宏的工作簿(*.xlsm)”格式另存，这样下次打开时才能正常显示工作表目录。此外，打开工作簿时，Excel 2007 默认会禁用宏，得单击警告栏中的“选项”按钮，选中“启用此内容”单选项，确定后才能显示工作表目录。8、快速为 Excel 工作簿创建工作表目录的方法我们经常把同类相关 Excel 工作表集中保存在同一文档中，以便于在各表格间进行引用、查看。当一个文档中的工作表达到一定数量时，要想找到需要的工作表就变得很麻烦了。此时若能建立一张“目录”工作表显示所有工作表的名称和链接，事情将会简单很多。下面介绍一种可以快速为 Excel 工作簿创建工作表目录的方法。定义名称定义名称打开 Excel 2007，右击第一张工作表标签选择“重命名”，把它重命名为“目录”工作表。选中 B1单元格，切换到“公式”选项卡，单击“定义名称”，在弹出的“新建名称”窗口中输入名称“工作表名”，在引用位置中则输入公式=INDEX(GET.WORKBOOK(1)，$A1)&T(NOW(
