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