1 / 7 技巧1 模糊匹配查找数据 用户常常需要搜索某一类有规律的数据，比如以A开头的人名，以B结尾的货品编码，或者包含66的电话号码等。这时就不能以完全匹配目标内容的方式来精确查找了，而需要利用Excel提供的通配符进行模糊查找。 在Excel中，有两个可用的通配符能够用于模糊查找，分别是半角问号“？”和星号“*”。“？”可以在搜索目标中代替任何单个的字符或数字，而“*”可以代替任意多个连续的字符或数字。 默认情况下，“查找和替换”对话框中的“单元格匹配”复选框是未被勾选的，如图 1-1所示。 图 1-1 “查找和替换”的单元格匹配属性 此时查找“A*”将匹配所有以“A”开头的单元格，同时所有包含“A”的单元格都会被选中。勾选“单元格匹配”复选框后，查找“A*”将仅匹配以“A”开头的单元格。更多的匹配规则请参阅表格 1-1所示。 搜索目标 模糊搜索 备注 以A开头的单元格 A* 勾选“单元格匹配” 以B结尾的单元格 *B 勾选“单元格匹配” 包含66的电话号码 66 取消勾选“单元格匹配” 王姓双名姓名 王?? 勾选“单元格匹配” 任意单姓、单名强的人名 ？强 勾选“单元格匹配” 2 / 7 表格 1-1 模糊搜索 如果要查找通配符本身，可在“查找内容”文本框中输入通配符时在前面输入~，如表格 1-2所示。 搜索目标 模糊搜索写法 ?Print ~?Print ** ~*~* 表格 1-2 查找通配符 模糊查找除了在“查找和替换”中使用到，在“筛选”中也经常用到，规则是相通的，多实践是掌握这个技巧的最好方法。 技巧2 查找单元格格式 图 2-1是一个数据区域，其中数值大于15的单元格填充为紫色，数值大于10不大于15的单元格填充为蓝色，其他单元格无填充色。当数据量巨大时，希望对具有不同填充色的数据进行进一步分析（例如统计其个数、平均值、最大值），那么靠手工操作是无法胜任的。此时需要通过查找格式，将具有相同格式的单元格定义为名称以作进一步处理。 图 2-1 标记了不同格式的数据 步骤1 选中数据区域A1:E16，按组合键调出“查找和替换”对话框，单击“选项”按钮，进入“查找和替换”高级模式。 步骤2 在“查找”选项卡中，单击“格式”按钮右侧的下拉箭头，在下拉菜单中单击“从单元格选择格式”，如图 2-2所示。 3 / 7 图 2-2 根据单元格格式查找目标单元格 此时，光标变成吸管形状，单击目标单元格（如B13单元格）提取作为“查找内容”的单元格格式。 步骤3 单击“查找全部”按钮，此时在对话框下侧列出所有符合条件的单元格，按组合键选中所有目标单元格，单击“关闭”按钮关闭“查找和替换”对话框。 此时，工作表中所有符合条件的单元格都处于选中状态。 步骤4 在名称框输入名称，例如“紫色单元格”，以指代当前处于选中状态的单元格，如图 2-3所示。 图 2-3 为选中单元格定义名称 步骤5 重复步骤1-4为蓝色单元格创建名称“蓝色单元格”。 经以上操作后，就可以使用函数公式来处理这些离散数据了，如图 2-4所示。 图 2-4 对相同格式的数据进行统计处理 4 / 7 其中H2:I5单元格区域中的公式都使用了上面定义的名称，简要介绍如下： 数据个数：=COUNT(蓝色单元格)，=COUNT(紫色单元格) 平均值：=AVERAGE(蓝色单元格)，=AVERAGE(紫色单元格) 最大值：=MAX(蓝色单元格)，=MAX(紫色单元格) 大于平均值的个数：{=SUM(N(LARGE(蓝色单元格,ROW(1:15))>H3))}，{=SUM(N(LARGE(紫色单元格,ROW(1:11))>I3))} 带有“{}”的公式是数组公式，在单元格中输入公式字符后按组合键，完成数组公式的录入。 技巧3 替换单元格格式 从Excel 2002开始，查找替换功能新增了对单元格格式的支持，这意味着用户可以查找应用了特定格式的单元格，并将这些格式进行快速替换。在图 3-1中有些单元格设置了灰色填充色，参照以下步骤可以将灰色填充色替换为蓝色填充色。 图 3-1 设置了填充色的单元格 步骤1 选中工作表中任意一个单元格，按组合键调出“查找和替换”对话框，单击“选项”按钮，进入“查找和替换”的高级模式。 步骤2 单击“查找内容”右侧的“格式”按钮，在弹出的“查找格式”对话框中设置查找格式为灰色填充色，如图 3-2所示。 5 / 7 图 3-2 设置“查找格式”和“替换为格式” 步骤3 类似步骤2，单击“替换为”右侧的“格式”按钮，在弹出的“替换格式”对话框中设置替换格式为蓝色填充色。 步骤4 单击“全部替换”按钮执行格式替换，在弹出的提示替换完成的对话框中单击“确定”按钮关闭对话框。 步骤5 单击“关闭”按钮关闭“查找和替换”对话框，最后格式替换的效果如图 3-3所示。 图 3-3 将灰色填充色替换为蓝色填充色 注意：在设置“查找内容”的格式及“替换为”的格式时，不仅可以手工设定格式，还可以单击“格式”按钮右侧的下拉按钮，在弹出的扩展菜单中选择“从单元格选择格式”命令，直接从单元格中提取格式。 技巧4 巧用查找替换批量删除换行符 将数据从其他系统导入到Excel时常常会包含大量换行符、空格或者其他不可见的不明字符。如何快速地删除这些字符是用户迫切想知道的，因为这些字符可能造成公式意外出错等情况。 如图 4-1，根据姓名查询员工才艺时，由于基础表中的内容是从其他系统导入的，各单元格 6 / 7 包含有换行符，导致使用姓名进行查找时找不到对应的数据而报错。参照以下步骤可以快速删除目标区域中的换行符： 图 4-1 由换行符引起的公式意外出错情况 步骤1 选定要删除换行符的目标单元格区域，如A列和B列。 注意：如果要在整个工作表范围内删除所有的换行符，确保只选中一个单元格。当选中的单元格为两个或两个以上时，Excel将自动限定在选定区域内进行查找和替换。 步骤2 在Excel程序窗口中按组合键打开“查找和替换”对话框，如图 4-2所示。 图 4-2 删除换行符后公式正常运行 步骤3 将光标定位到“查找内容”文本框，按住键的同时使用数字小键盘键入“10”，松开键，输入换行符；在“替换为”文本框中不输入任何字符，保持为空。 输入的换行符不但不可见，而且不占任何字符位置，但确实是存在的。因此绝不要重复输入，以至于查找内容变成连续两个换行符，这样反而查找不到换行符。 步骤4 单击“全部替换”按钮执行替换，单击“确定”按钮关闭替换完成提醒对话框。此时，基础表中的所有换行符被删除，公式返回预想结果。 如果要批量删除空格，可以在“查找和替换”对话框的“查找内容”文本框中输入一个空格；如果要删除不可见的不明字符，可以先选中相应单元格，在编辑栏中复制一个不明字符，并粘贴到“查找内容”文本框中。 本篇文章节选自《Excel 2007 实战技巧精粹》 ISBN 9787115218360 人民邮电出版社 7 / 7
1 Win7版Excel2007实用技巧总结 1选定、取消超链接文本 单击一次则可跟踪超链接，如果需要在Excel中选定超级链接文本而不跳转到目标处，单击并按住不放即可选定此单元格。右键单击选中的含超链接的单元格，单击“取消超链接”即可。 2 一次性打开多个工作簿 打开工作簿(*.xlsx)所在的文件夹，按住Shift键或Ctrl键，并用鼠标选择彼此相邻或不相邻的多个工作簿，将它们全部选中，然后按右键单击，选择“打开”命令，系统则将上述选中的工作簿全部打开。 3重新给工作表命名 双击当前工作表下部的名称，如“Sheet1”，再输入新的名称。或右键单击需要重命名的工作表名称，选择“重命名”后修改即可。 4快速选中整个单元格范围 在Excel中，如果想要快速选择正在处理的整个单元格范围，按下“Ctrl+Shift+ *”。 注意:该命令将选择整个列和列标题，而不是该列表周围的空白单元格——你将得到所需的单元格。若想选定整个工作表，则右键单击工作窗口下面的工作表标签，在弹出的菜单中选择“选定全部工作表”命令，或单击行列交叉处左上角的按钮即可。 5快速查找 在输入查找内容时，可以使用问号（？）和星号（*）作为通配符，以方便查找操作。问号（？）代表一个字符，星号（*）代表一个或多个字符。需要注意的问题是，既然问号（？）和星号（*）作为通配符使用，那么如何查找问号（？）和星号（*）呢？只要在这两个字符前加上波浪号(～)就可以了。 6修改默认文件保存路径和文件保存格式 点击打开工作簿左上角的圆形office按钮，点击打开“Excel选项（I）”，点击“保存”，即可修改默认文件保存位置和文件保存格式（与97‐03版兼容的.xls格式等）。 7在多个Excel工作簿间快速切换 按下“Ctrl+Tab”可在打开的工作簿间切换。 8 快速设置序号值或其它有序文本 法一：选中序号值为“1”的起始单元格，将鼠标指针指向被选中单元格的右下角，按下鼠标左键的同时再按住ctrl键，拖动鼠标即可。除快速排序外，在松开ctrl键时，可通过拖动鼠标快速复制被选中单元格内容到一个或多个连续的单元格中。 法二：同时选中序号值为“1”和“2”的起始单元格，将鼠标指针指向被选中单元格的右下角，将鼠标移至第2个单元格的右下角成细十字线状时，按住左键向后（或向下）拖拉至该序列的最后一个单元格，松开左键即可。 9选定不连续单元格区域 选定单元格的同时按住ctrl键即可。 10根据条件选择单元格 单击“开始→编辑→查找和选择→定位条件”命令，在打开的“定位条件”对话框中选择需要选
1 / 10 技巧1 控制自动超链接 默认情况下，用户在输入类似电子邮件地址或网站地址的数据时，Excel会自动将其转换为超链接的形式。具有超链接特性的单元格，当把鼠标移动到其上方时，光标会变成手的形状，并提示超链接的目标地址，如图 1-1所示。此时如果单击，Excel就会启动相应的程序（IE浏览器或者Outlook）跟踪目标地址。 图 1-1 含有超链接的内容 但是，很多时候用户并不需要这种智能转换，此时它就显得有些自作聪明，把不应该转换的内容也转换了；而且具有超链接形式的单元格很难被选中，往往需要使用鼠标右键才能选中，用左键单击时往往会触发其他程序的窗口。以下介绍几种方法来取消超链接转换。 1-1 用撤销功能取消超链接转换 如果在单元格中输入的内容被自动转换为超链接，可以按组合键，此时即可撤销普通内容到超链接的转换，并不会删除刚输入的内容，如图 1-2所示。 图 1-2 使用撤销功能取消超链接转换 要避免输入的内容被转换为超链接，还可以在输入内容前先输入一个半角单引号“'”。这样，所输入的内容就会被当作纯文本，因而不会进行超链接转换，如图 1-3所示。 图 1-3 前插半角逗号取消超链接转换 2 / 10 1-2 关闭自动超链接转换功能 步骤1 在Excel工作簿窗口中，依次单击键、键和键，打开“Excel 选项”对话框，在左侧列表中选择“校对”类别，然后在右侧区域中单击“自动更正选项”按钮，如图 1-4所示。 图 1-4 关闭自动超级链接转换功能 步骤2 激活“键入时自动套用格式”选项卡，取消勾选“Internet及网络路径替换为超链接”复选框。 步骤3 单击“确定”按钮关闭“自动更正”对话框，再次单击“确定”按钮关闭“Excel 选项”对话框。 1-3 选定超链接单元格 如果要选中包含超链接的单元格，方法如下： 单击单元格时，按住鼠标左键长许，当手型光标变成空心十字型时再松开。 先使用鼠标右键来单击单元格，再单击鼠标左键关闭单击右键时启开的快捷菜单并保持选中单元格。 先选中附近的单元格，再用方向键移动到包含超链接的单元格。 单击单元格时，将鼠标尽可能指向单元格的空白区域，在光标为空心十字型时再单击。 3 / 10 技巧2 取消现有的超链接 Excel允许在单元格中存储具有超链接特性的内容，当把鼠标移动到其上方时，光标会变成手的形状，并提示超链接的目标地址，如图 2-1所示。 图 2-1 单元格含有超链接的内容 此时如果单击单元格，Excel就会启动相应的程序（IE浏览器或者Outlook）跟踪目标地址。如果用户希望取消工作表中现有的超链接，把它们转换为普通文本，可以使用以下方法： 2-1 取消现有的单个超链接 如果只需要取消一个单元格中的超链接，可以右键单击单元格，在快捷菜单中选择“取消超链接”命令，如图 2-2所示。 图 2-2 取消单个超链接 此方法无法同时取消多个单元格的超链接。 2-2 批量取消超链接 步骤1 选定任意空白无格式的单元格，按组合键进行复制。 步骤2 按住键，单击所有需要取消超链接的单元格，使它们都处于选中状态。更多选择单元格或单元格区域的方式请参阅图书《Excel 2007实战技巧精粹》相关章节。 4 / 10 步骤3 单击鼠标右键，在弹出的快捷菜单中选择“选择性粘贴”命令，如图 2-3所示。 图 2-3 使用选择性粘贴取消超链接属性 步骤4 在“选择性粘贴”对话框中选中“加”单选框，单击“确定”按钮关闭对话框。此时虽然超级链接已经取消，但是格式上依然是超级链接的格式。 此处，使用“加、减、乘、除”任意一种运算，都可以取消超级链接属性。 步骤5 重复步骤3，并在“选择性粘贴”对话框中选中“格式”单选框，单击“确定”按钮关闭对话框。通过格式覆盖去除残留的超级链接格式，如图 2-4所示。 图 2-4 选择性粘贴“格式”去除残留的超链接格式 2-3 使用VBA来取消超链接 如果需要取消一个工作表中的所有超链接，更快的方式是使用VBA。 步骤1 激活需要取消超链接的工作表，按组合键打开VBA编辑器。 步骤2 在VBA编辑器中按组合键调出“立即窗口”。 步骤3 在“立即窗口”中输入以下代码，然后按键，如图 2-5所示。 cells.Hyperlinks.Delete 5 / 10 图 2-5 在VBA编辑器“立即窗口”中执行删除超链接的命令 注意：在没有关闭自动超链接的情况下（请参阅技巧1 ），以上三种方式取消现有超链接后，如果对单元格重新编辑，超链接还会自动复原。 技巧3 提取超链接信息 当用户从网页或其他的超文本中复制数据到Excel工作表中时，所得到的数据中可能会包含大量的超链接，许多有价值的信息隐藏在这些超链接里面。如图 3-1，用户希望看到真正的邮件地址，可是它们被隐含在B列的超链接中。 图 3-1 隐含信息的超链接 Excel并没有提供用于提取超链接信息的功能，在这个需求中，用户可以通过自定义函数的方法来实现，请参照以下步骤： 步骤1 按组合键打开VBA编辑器。 步骤2 在“工程”窗口中激活当前工作簿，单击“插入”→“模块”来插入一个新的模块，默认情况下为“模块1”，如图 3-2所示。 6 / 10 图 3-2 编写提取超链接信息的自定义函数 步骤3 双击“模块1”，在右侧打开“模块1”的代码窗口，在代码窗口中输入以下代码： Function GetName(HyCell) Application.Volatile True GetName = HyCell.Hyperlinks(1).Name End Function Function GetAddress(HyCell) Application.Volatile True With HyCell.Hyperlinks(1) GetAddress = IIf(.Address = "", .SubAddress, .Address) End With End Function 现在，用户得到了两个自定义函数，GetName函数用于提取超链接的名称，GetAddress函数用于提取超链接的目标地址。用户可以像使用Excel内置函数一样在工作表中使用这两个自定义函数。 步骤1 在C2单元格中输入公式： ＝GetName(B2) 步骤2 在D2单元格中输入公式： ＝GetAddress(B2) 步骤3 把C2、D2的公式分别往下填充，最后效果如图 3-3所示。 7 / 10 图 3-3 自定义函数提取超链接信息 技巧4 智能创建超链接 在实际工作中，有时希望Excel能智能地创建超链接，实现工作簿之间的快速跳转。编写VBA代码固然可以实现这样的智能要求，但相信很多初学者对VBA是望而生畏的。下面介绍使用名称定义和Excel的基本操作来实现同样的效果： 4-1 静态目标地址 如图 4-1所示，A2,B2单元格指定了超链接的地址，在C2单元格中使用名称作为超链接的目标地址，单击C2单元格后直接跳转到“表2!C1”位置。下面介绍如何使用名称作为超链接的地址： 图 4-1 名称做超链接目标地址的效果演示 步骤1 在“公式”选项卡中单击“定义名称”按钮，激活“新建名称”对话框。 步骤2 在“名称”文本框中输入适当的文本作为新名称的名字（比如，“静态目标”），在引用位置中输入“=表2!$C$1”。单击“确定”按钮完成新名称的创建。 这里对名称的要求是，返回值是对目标单元格的地址引用。 8 / 10 图 4-2 创建作为超链接目标地址的名称 步骤3 选中C2单元格，单击鼠标右键，在快捷菜单中选择“超链接”命令，打开“插入超链接”对话框，如图 4-3所示。 图 4-3 编辑超链接以名称作为超链接目标地址 步骤4 在左侧列表中选择“原有文件或网页”，保留要显示的文字“跳转到”（也可以由任何其他合适的文本替代），在“地址”文本框中输入“#静态目标”，单击“确定”按钮关闭对话框。 此时，单击C2单元格就会直接跳转到目标地址了。创建这类静态的超级链接其实无需使用名称定义，这里用名称定义先实现这个静态目标地址的超级链接主要是为了理清思路：先创建一个指向单元格地址的名称，然后在“插入超级链接”对话框中为超链接指定目标名称作为超链接地址。 4-2 动态目标地址 如图 4-4所示，如果需要为C2:C6单元格区域中的每个单元格都设置超链接，地址分别为对应A列、B列指定的地址，那么参照上述“静态目标地址”的方式是非常不方便的，当需要设置的单元格数量巨大时更是不现实的。参照以下步骤可以方便地批量设置单元格的超链接： 图 4-4 批量设置超链接 步骤1 在单元格A7中输入“表2”，单元格B7中输入“B1”，在C7单元格中先输入“=”， 9 / 10 在单元格编辑状态下用鼠标单击选中表2的B1单元格，按键，此时单元格C7内容为“=表2!B1”，值为“0”，如图 4-5所示。 图 4-5 使用鼠标提取目标单元格地址 使用鼠标单击目标单元格来提取目标地址是非常具有实战意义的，尤其当目标地址存在于另一个工作簿时，这是一种快捷准确地提取单元格引用的方式。例如，当其中一个表的表名称为数字“1”时，引用该表B2单元格时的正确引用方式为“='1'!B2”，在表名称两边加上了单引号，使用鼠标点选的方式就能避免了写成“=1！B2”的可能。 步骤2 根据上一步提取的目标地址“=表2!B1”，把C7单元格公式修改成“=A7&"!"&B7”，显示结果为“表2!B1”。 这里使用公式生成代表目标地址的字符串，这是本技巧的关键。 步骤3 再次编辑公式，将C7单元格的公式修改成“=INDIRECT(A7&"!"&B7)”，即在步骤2公式的基础上套上了INDIRECT函数，返回目标引用地址。 步骤1~3的主要任务是创建一个基于公式单元格所处位置的公式，返回目标单元格的地址引用。 实际工作中并不一定要使用INDIRECT函数，只要最后返回值为目标单元格的地址引用，均可。此处，C7单元格公式所谓“基于公式单元格所处的位置”是指，返回的单元格引用是基于A7、B7单元格的。 步骤4 单击C7单元格使其成为当前单元格，然后在“公式”选项卡中单击“定义名称”按钮，激活“新建名称”对话框，输入名称“动态目标”，在引用位置文本框中输入C7单元格的公式“=INDIRECT(A7&"!"&B7)”，单击“确认”按钮关闭对话框，如图 4-6所示。 图 4-6 创建名称 本步骤要确保C7单元格为当前单元格时输入C7单元格内的公式。这样做的效果是，当当前单元格变为其他单元格，如C6单元格时，名称“动态目标”的引用位置公式也将变为 10 / 10 “=INDIRECT(A6&"!"&B6)”，保持一一对应性。 步骤5 选中C2:C6单元格区域，单击鼠标右键，在快捷菜单中选择“超链接”命令，打开“插入超链接”对话框，如图 4-7所示。 图 4-7 用名称作地址 步骤6 在左侧列表中选择“原有文件或网页”，保留要显示的文字“跳转到”（也可以由任何其他合适的文本替代），在下方“地址”文本框中输入“#动态目标”，单击“确定”按钮关闭对话框完成超链接创建，如图 4-8所示。 图 4-8 批量设置的超链接 此时，单击C2:C6单元格区域中的任意一个单元格，都能会直接跳转到目标地址了，并且修改A列、B列数据后超链接的目标地址会自动更新，具备一定的智能性。 本篇文章节选自《Excel 2007 实战技巧精粹》 ISBN 9787115218360 人民邮电出版社
第1章 初识Excel 2007
1.1 认识Excel 2007用户界面
1.1.1 启动Excel 2007
1.1.2 Excel 2007用户界面介绍
1.1.3 退出Excel 2007
1.2 理解工作簿、工作表和单元格
1.2.1 工作簿
1.2.2 工作表
1.2.3 单元格
本章 小结
习题1
上机实验1
第2章 ExceI2007基本操作
2.1 创建工作簿
2.1.1 创建空白工作簿
2.1.2 基于模板创建工作簿
2.1.3 基于现有工作簿创建新工作簿
2.2 保存工作簿
2.2.1 保存新建工作簿
2.2.2 保存现有工作簿
2.2.3 设置自动保存
2.2.4 设置工作簿密码
2.3 打开和关闭工作簿
2.3.1 打开工作簿
2.3.2 在工作簿之间切换
2.3 13关闭工作簿
2.4 操作工作表
2.4.1 在工作表之间切换
2.4.2 插入工作表
2.4.3 重命名工作表
2.4.4 设置工作表标签颜色
2.4.5 选择工作表
2.4.6 移动或复制工作表
2.4.7 隐藏和显示工作表
2.4.8 删除工作表
2.4.9 保护工作表
本章 小结
习题2 上机实验2
第3章 编辑工作表
3.1 手动输入数据
3.1.1 输入文本
3.1.2 输入日期和时间
3.1.3 输入数字
3.1.4 设置数据有效性
3.2 自动填充数据
3.2.1 使用记忆式输入
3.2.2 填充相邻单元格内容
3.2.3 填充内置序列
3.2.4 填充自定义序列
3.2.5 快速输入相同数据
3.3 修改单元格数据
3.3.1 编辑单元格内容
3.3.2 查找和替换
3.3.3 撤销、恢复或重复
3.4 移动和复制数据
3.4.1 选择单元格或区域
3.4.2 使用剪贴板移动或复制单元格
3.4.3 使用鼠标移动或复制单元格
3.4.4 以插入方式移动或复制单元格
3.4.5 移动或复制单元格数据
