探悉 Excel 中不为人所知的技巧

察看本文应用于的产品

<script type="text/javascript">function loadTOCNode(){}</script>

文章编号	:	843504
最后修改	:	2005年4月4日
修订	:	1.1

<script type="text/javascript"> var sectionFilter = "type != 'notice' && type != 'securedata' && type != 'querywords'"; var tocArrow = "/library/images/support/kbgraphics/public/en-us/downarrow.gif"; var depthLimit = 10; var depth3Limit = 10; var depth4Limit = 5; var depth5Limit = 3; var tocEntryMinimum = 1; </script> <script src="/common/script/gsfx/kbtoc.js??4" type="text/javascript"></script>

概要

<script type="text/javascript">loadTOCNode(1, 'summary');</script>

本文说明 Microsoft Excel 中尚未被用户发现的一些最强大、最实用的功能和函数。例如，您可能为了执行某项计算而创建了一个新的宏，而事实上该计算任务可以借助已有的公式或函数完成。或者，您可能为了执行某项任务而创建了一个新的宏，而该任务原本可以使用已有的功能执行。

回到顶端

简介

<script type="text/javascript">loadTOCNode(1, 'summary');</script>

本文列出有关如何使用 Microsoft Excel 的一些提示。

回到顶端

更多信息

<script type="text/javascript">loadTOCNode(1, 'moreinformation');</script>

将多个列中的文本联接起来

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 您可以使用 & 运算符或 CONCATENATE 函数将多个列中的文本连缀或合并起来；例如，如果在单元格 A1:C2 中键入以下数据：

A1：名	B1：中间名	C1：姓
A2：Tom	B2：Edward	C2：Smith

要获得全名，请在单元格 D2 中，键入以下公式之一：

$D$2:=CONCATENATE(A2," ",B2," ",C2)

$D$2:=A1&" "&B2&" "&C2

注意：单元格间的空格 (" ") 用于在显示文本间插入空格。

回到顶端

设置打印区域

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 从 Microsoft Excel 97 for Windows 开始，“文件”菜单上提供了“设置打印区域”工具栏按钮。当您单击“设置打印区域”工具栏按钮时，可以将打印区域设置为当前选择的区域。将“设置打印区域”工具栏按钮添加到现有工具栏后，可以单击“设置打印区域”，轻松地将打印区域设置为当前选定的范围。

要在 Excel 中添加“设置打印区域”工具栏按钮，请执行下列步骤：

1.	在“视图”菜单上，指向“工具栏”，然后单击“自定义”。
2.	单击“命令”选项卡。
3.	在“类别”下，单击“文件”，然后向下滚动命令列表，直到看到“设置打印区域”工具栏按钮。
4.	单击“设置打印区域”，然后将该命令拖动到一个现有的工具栏。

回到顶端

排除列表中的重复项目

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 如果您所创建的项目列表中包含重复的项目，而您希望派生出无重复的列表，请使用 Excel 中的 高级筛选命令。

为此，请按照下列步骤操作：

1.	在新工作簿的单元格 A1:A10 中键入以下数据： A1：水果 A2：苹果 A3：樱桃 A4：梨 A5：樱桃 A6：李子 A7：苹果 A8：苹果 A9：梨 A10：苹果
2.	在“数据”菜单中，指向“筛选”，然后单击“高级筛选”。
3.	在“操作”下，单击“复制到”。
4.	在“列表区域”框中，键入 $A$1:$A$10。
5.	单击“选择不重复的记录”，在“复制到”框中键入 $B$1，然后单击“确定”。 列 B 中将显示以下无重复列表： B1：水果 B2：苹果 B3：樱桃 B4：梨 B5：李子

注意，此方法对于多个列同样适用。使用 高级筛选命令时，可以隐藏行。

回到顶端

通过将文本值乘 1 来将文本更改为数字

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 有时，当您从其他源中导入文件时，数值看起来像是数字，但却表现得像文本值一样。要解决此问题，请将这些值转换为数字。执行此操作的方法之一是将这些文本值乘 1。

要转换文本值，请按照下列步骤操作：

1.	单击工作表中的空白单元格，确保此单元格未设置为文本格式，然后在此单元格中键入 1。
2.	在选中此单元格的情况下，单击“编辑”菜单上的“复制”。
3.	选择要转换为数字的值所在的区域。
4.	在“编辑”菜单上，单击“选择性粘贴”。
5.	在“运算”下，单击“乘”，然后再单击“确定”。

此方法可以将文本转换为数字。通过查看数字的对齐方式，可以得知是否成功转换了文本值。如果使用的是“常规”格式，而值靠右对齐，则为数字；文本值会靠左对齐。

回到顶端

使用“文本导入向导”将文本更改为数字

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 为此，请按照下列步骤操作：

1.	选择要转换为数字的值所在的范围。
2.	在“数据”菜单上，单击“分列”。
3.	单击“下一步”两次，转到向导的步骤 3。
4.	在“列数据格式”分组框中，单击“常规”，然后单击“完成”。

此方法可以将文本转换为数字。通过查看数字的对齐方式，可以得知是否成功转换了文本值。如果使用的是“常规”格式，而值靠右对齐，则为数字；文本值会靠左对齐。

回到顶端

对大纲中的小数进行排序

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在单元格 A1:A6 中创建了以下大纲数字：

A1：1.1.0

A2：1.10.0

A3：1.2.0

A4：1.20.0

A5：1.21.1

A6：1.3.0

对大纲数字排序后，它们显示的顺序保持不变。大纲数字仍然以您键入的顺序显示。但是，如果您希望对每个小数点之间的数字进行排序，请使用“文本分列向导”。为此，请按照下列步骤操作：

1.	选择单元格 A1:A6。
2.	在“数据”菜单上，单击“分列”。
3.	在“文本分列向导”的步骤 1 中，单击“分隔符号”，然后单击“下一步”。
4.	在“分隔符号”分组框中，单击以清除所有复选框，但保留“其他”复选框。在“其他”复选框中，键入句点，然后单击“下一步”。
5.	在步骤 2 中，在“目标”框中键入 $B$1，这样原始大纲就不会被覆盖掉，然后单击“完成”。 数字显示在列 B、C 和 D 中。
6.	选择单元格 A1:D6。
7.	在“数据”菜单上，单击“排序”。
8.	在“主要关键字”列表中，单击“按列 B”。
9.	在“次要关键字”框中，单击“按列 C”。
10.	在“第三关键字”列表中，单击“按列 D”，然后单击“确定”。

排序后的列表显示在列 A 中。

回到顶端

使用数据表单将记录添加到列表中

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 若要将记录添加到列表中，请使用预定义的数据表单。首先单击列表中的一个单元格，然后单击“数据”菜单上的“记录单”。

回到顶端

输入当前日期或时间

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 如果希望在单元格中快速输入当前日期，请按 Ctrl+;，然后按 Enter 键。要在单元格中快速输入当前时间，请按 Ctrl+:，然后按 Enter 键。

回到顶端

查看公式中的参数

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 当您在单元格中输入一个公式时，可以按 Ctrl+Shift+A 来查看公式中的参数。如果键入 =RATE，然后按 Ctrl+Shift+A，则可以看到该函数的所有参数 － 例如， =RATE(nper,pmt,pv,fv,type,guess)。如果您希望看到更多的详细信息，则在键入 =RATE 后按 Ctrl+A 以显示“函数向导”。

回到顶端

在某个单元格范围内输入相同的文本或公式

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 若要在某个单元格范围内快速输入相同的文本或公式，请执行下列步骤：

1.	选择要填充的单元格范围。
2.	键入文本或公式，但是不要按 Enter 键，而是按 Ctrl+Enter。

数据将显示在您所选择的范围中。

回到顶端

使文本框与单元格中的数据相链接

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 为此，请按照下列步骤操作：

1.	在“绘图”工具栏中，单击“文本框”，单击工作表，然后拖动指针以创建文本框。
2.	在公式编辑栏中单击或按 F2 键，以便在公式编辑栏中进行更改。
3.	键入链接公式 － 例如，键入 =A1，然后按 Enter 键。

您在链接单元格中输入的文本会显示在文本框中 － 例如， A1。您可以根据需要将文本框移动到工作簿中的任何工作表。

回到顶端

使图片与某个单元格范围相链接

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 您可以复制某个单元格范围，然后将结果图片粘贴到工作表中。通过执行此操作，可以在工作表中的任何位置轻松地看到单元格的内容。您可以使用此方法在同一个页面上打印不相邻的单元格。图片会与单元格范围相链接，并会根据内容更改和格式更改进行更新。要生成链接的图片，请按照下列步骤操作：

1.	选择单元格范围。
2.	在“编辑”菜单上，单击“复制”。
3.	选择要在其中显示图片的单元格。
4.	按住 Shift 键，同时在“编辑”菜单上单击“粘贴图片链接”。

结果会得到一个快照，当源单元格发生更改或格式变化时，该快照会随之更新。

回到顶端

长公式疑难解答

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 如果创建的长工作表公式未返回预期结果，请拖动指针，在公式编辑栏中选择该公式的一部分，然后按 F9 键。执行此操作时，只会对选中的公式部分进行求值。

重要说明：如果按 Enter 键，该部分公式将丢失。因此，请务必改为按 Esc 键。不过，如果不慎按了 Enter 键，可以按 Ctrl+Z 撤消更改。

回到顶端

查看指定名称的图形映射

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 注意：本部分仅适用于 Excel 97 for Windows。

将工作表的“显示比例”框设置为 39% 或更低时，会在屏幕上的一个矩形中显示一个指定名称，该名称包含两个或更多相邻单元格的单元格范围。单击“标准”工具栏上的“显示比例”并键入 40% 或更大值时，标识指定范围的矩形会自动消失。注意，在更早的 Microsoft Excel 版本中，此功能不可用。

回到顶端

使用已有单元格的内容填充列中的空白单元格

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在列 A 中键入以下名称：

为了正确地排列名称，应该在空白单元格中填入名称。为此，请按照下列步骤操作：

1.	选择单元格 A1:A10。
2.	在“编辑”菜单上，单击“定位”。
3.	单击“定位条件”，单击“空值”，然后单击“确定”。
4.	键入 =a1，然后按 Ctrl+Enter。 这个步骤会在您选择的空白单元格中输入名称。
5.	选择单元格 A1:A10。
6.	在“编辑”菜单上，单击“复制”。
7.	在“编辑”菜单上，单击“选择性粘贴”。
8.	在“粘贴”组下，单击“数值”，然后单击“确定”。

名称就会根据您的需要沿单元格向下填充。

回到顶端

从相对引用切换到绝对引用

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 可以按 F4 键切换公式的相对单元格地址和绝对单元格地址。当您在公式编辑栏中键入公式时，使用了相对地址格式的单元格引用，例如 A1。键入引用后，按 F4 键，单元格引用会自动更改为绝对单元格引用，例如 $A$1。继续按 F4 键，可以显示绝对引用和相对引用的混合格式。

有关单元格引用的更多信息，请单击 Microsoft Excel 帮助中的“查找”选项卡，键入 绝对和相对 (absolute and relative)，然后双击“相对和绝对引用之间的区别”(The difference between relative and absolute references) 主题。

回到顶端

使用 OFFSET 函数修改插入的单元格中的数据

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在单元格 A1:A7 中使用了以下数据，您希望在此范围内从最后一行中减去第一行：

A1：1

A2：2

A3：3

A4：4

A5：5

A6：

A7：=A5-A1

假设您希望使用一个公式，该公式始终处于最后一个单元格下方的第二行，而且该公式与最后一个包含数据的单元格之间有一个空白单元格。假设您在该空白单元格处插入了一个新行（下例中的第 6 行），您希望公式从单元格 A6 的数据中减去单元格 A1 的数据，而不是从单元格 A5 的数据中减。

注意，在本例中，当您在 A6 中插入一个包含数据的行时，公式 =A5-A1 不会从行 A6 的数据中做减法。

若要实现此目的，应使用 OFFSET 函数。 OFFSET 函数返回对一个范围的引用，该范围是相对于一个单元格或一个单元格范围的指定行数和列数的范围。在本例中，使用以下公式：

=OFFSET(A6,-1,0)-A1

OFFSET 公式不会固定在 A6 的上一行上，当您插入新行时会随之更改。

回到顶端

使用高级筛选命令

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 如果您在 Excel 中创建了一列数据，而且希望选择某些项目并将其复制到另一个工作表中，应使用 Excel 中的 高级筛选命令。要使用此命令，请指向“数据”菜单上的“筛选”，单击“高级筛选”，然后按照屏幕上显示的说明操作。如果您不理解 Excel 所提示的信息，请参见 Microsoft Excel 帮助。

回到顶端

使用条件求和对数据求和

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在单元格 A1:A10 中创建了一列数据，而且希望对所有大于 50 且小于 200 的值求和。为此，请使用以下数组公式：

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,A1:A10,0),0))

注意：请务必按下 Ctrl+Shift+Enter，这样才可以将公式作为数组输入。执行此操作后，您会看到公式被花括号 {} 括起。不要尝试手动输入括号。

此公式对范围内的每个单元格使用嵌套的 IF 函数，并且仅当两个测试条件同时满足时才追加单元格数据。

回到顶端

使用条件求和对数据计数

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在单元格 A1:A10 中创建了一列数据，而且希望统计所有大于 50 且小于 200 的值的数目。为此，请使用以下数组公式：

=SUM(IF(A1:A10>=50,IF(A1:A10<=200,1,0),0))

注意：请务必按下 Ctrl+Shift+Enter，这样才可以将公式作为数组输入。执行此操作后，您会看到公式被花括号 {} 括起。不要尝试手动输入括号。

此公式对范围内的每个单元格使用嵌套的 IF 函数，并且仅当两个测试条件同时满足时才向总数中追加一。

回到顶端

使用 INDEX 函数和 MATCH 函数查找数据

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在单元格 A1:C5 中创建了以下信息表，且此表包含单元格 C1:C5 中的年龄 (Age) 信息：

假设您希望根据某人的姓名 (Name) 查找此人的年龄 (Age)。为此，请按如下公式示例，配合使用 INDEX 函数和 MATCH 函数：

=INDEX($A$1:$C$5, MATCH("Mary",$A$1:$A$5,),3)

此公式示例使用单元格 A1:C5 作为信息表，并在第三列中查找 Mary 的年龄 (Age)。公式返回 22。

回到顶端

拖动填充柄以创建数字系列

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 通过拖动一个单元格的填充柄，可以将该单元格的内容复制到同一行或列中的其他单元格。如果此单元格包含 Excel 可以按系列顺序推断出的数字、日期或时间段，则这些值将递增，而不是被复制。例如，如果单元格中包含“1 月”，您可以沿行或列在其他单元格中快速填入“2 月”、“3 月”，依此类推。您还可以为常用的文本条目创建自定义填充系列，例如贵公司的销售区域。

回到顶端

自动填充数据

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 您可以双击选定单元格的填充柄，沿某一列向下填充单元格内容，填充行数与相邻列的行数相同。例如，如果在单元格 A1:A20 中键入了数据，在单元格 B1 中键入了一个公式或文本，按 Enter 键，然后双击填充柄，则 Excel 将沿着该列，从单元格 B1 到单元格 B20 自上而下填充数据。

回到顶端

将 VLOOKUP 函数用于未排序的数据

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 在 Excel 97 for Windows 及更高版本中，可以对未排序的数据使用 VLOOKUP 函数。但是，您必须向公式中添加一个附加的参数。如果您未指定 Range_Lookup 参数的值，那么它默认为 TRUE。注意， Range_Lookup 参数是第四个参数。这样做是为了使函数与早期版本的 Excel 兼容。

要让 VLOOKUP 函数正确地处理未排序的数据，请将 Range_Lookup 参数更改为 FALSE。下面是一个示例函数，它在上文“使用 INDEX 函数和 MATCH 函数查找数据”部分所创建的数据表中查找 Stan 的年龄：

=VLOOKUP("Stan",$A$2:$C$5,3,FALSE)

回到顶端

每三个数字返回一个

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在单元格 A1:A12 中创建了以下数据表，而且希望在某列中每逢第三个数字就将该数字返回，并将收集到的数字放入一个连续列中：

为此，请配合使用 ROW 函数和 OFFSET 函数，例如，使用以下公式示例：

=OFFSET($A$1,ROW()*3-1,0)

此公式取决于输入它时所在的单元格的行。在此公式中， ROW 函数返回输入此公式时所在的单元格的行号。将该行号乘以 3。 OFFSET 函数将活动单元格从单元格 A1 向下移动指定的行数，每逢第三个数字就将其返回。

回到顶端

舍入到最接近的货币单位

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 假设您在工作表的单元格 A1:A3 中输入了以下公式：

A1：=1.23/2

A2：=1.21/2

A3：=SUM(A1:A2)

假设您正计算资金，且计算结果已设置为货币格式。返回的值如下：

A1：$0.62

A2：$0.61

A3：$1.22

正如您所见，单元格 A3 中的总计不正确。问题是，尽管数值格式（货币）对显示的值进行了舍入，基础值还是无法舍入到最接近的货币单位。使用 ROUND 函数可以解决此问题。例如，将公式更改为：

A1：=ROUND(1.23/2,2)

A2：=ROUND(1.21/2,2)

A3：=ROUND(SUM(A1:A2),2)

ROUND 函数的第二个参数告知 Excel 要舍入到哪一位。在本例中，2 告知 Excel 舍入到最接近的百分位。

回到顶端

安装并使用 Microsoft Excel 帮助

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 使用 Microsoft Excel 帮助可以搜索有关特定用法主题的信息，浏览主题列表，或搜索特定的词和短语而不是主题。您还可以使用与上下文相关的帮助（按 F1）查看有关任务的信息。

必须安装帮助文件，才能访问这些文件。如果未安装帮助，请再次运行安装程序，然后单击“添加/删除”安装这些文件。

回到顶端

不要直接从软盘打开和保存

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 当您打开一个工作簿时，Excel 在您保存文件的文件夹中创建临时文件，并在您从中打开此工作簿的文件夹中创建临时文件。当您关闭文件时，会删除这些临时文件。另外，当您保存文件时，Excel 会在介质上创建此文件的副本。如果您从软盘打开工作簿或者如果软盘没有足够的可用空间保存此文件，则上述行为会出现问题。

出于这些原因，最好在处理文件前将其复制到硬盘上。进行修改后，将文件保存到硬盘上，然后将其复制回软盘。

回到顶端

只需一次击键即可创建新的图表或工作表

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 要快速创建图表，请选择图表数据，然后按 F11。要创建新的工作表，请按 Shift+F11。

回到顶端

在同一个工作表中设置多个打印区域

<script type="text/javascript">loadTOCNode(2, 'moreinformation');</script> 不必使用宏即可在同一个工作表中设置多个打印区域。为此，应使用“自定义视图”命令和“打印报告”命令。基本操作是，定义工作表视图，然后使用您所选择的视图定义报告。有关更多信息，请参见 Microsoft Excel 帮助。

有关其他信息，请单击下面的文章编号，以查看 Microsoft 知识库中相应的文章：

142529 (http://support.microsoft.com/kb/142529/) XL：如何创建多个视图，如何创建并打印报告

回到顶端

---

这篇文章中的信息适用于:

•	Microsoft Excel 2000 标准版
•	Microsoft Excel 2002 标准版
•	Microsoft Excel 97 标准版
•	Microsoft Office Excel 2003

回到顶端

关键字：	kbhowto kbinfo kbfunctions KB843504

回到顶端

Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性，不作任何声明。 所有该等文件及有关图形均"依样"提供，而不带任何性质的保证。Microsoft和/或其各供应商特此声明，对所有与该等信息有关的保证和条件不负任何责任，该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下，在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中，Microsoft