探悉 Excel 中不为人所知的技巧.docx
《探悉 Excel 中不为人所知的技巧.docx》由会员分享，可在线阅读，更多相关《探悉 Excel 中不为人所知的技巧.docx（14页珍藏版）》请在冰豆网上搜索。
探悉Excel中不为人所知的技巧
探悉Excel中不为人所知的技巧
概要
简介
更多信息
将多个列中的文本联接起来
设置打印区域
排除列表中的重复项目
通过将文本值乘1来将文本更改为数字
使用“文本导入向导”将文本更改为数字
对大纲中的小数进行排序
使用数据表单将记录添加到列表中
输入当前日期或时间
查看公式中的参数
在某个单元格范围内输入相同的文本或公式
使文本框与单元格中的数据相链接
使图片与某个单元格范围相链接
长公式疑难解答
查看指定名称的图形映射
使用已有单元格的内容填充列中的空白单元格
从相对引用切换到绝对引用
使用OFFSET函数修改插入的单元格中的数据
使用高级筛选命令
使用条件求和对数据求和
使用条件求和对数据计数
使用INDEX函数和MATCH函数查找数据
拖动填充柄以创建数字系列
自动填充数据
将VLOOKUP函数用于未排序的数据
每三个数字返回一个
舍入到最接近的货币单位
安装并使用MicrosoftExcel帮助
不要直接从软盘打开和保存
只需一次击键即可创建新的图表或工作表
在同一个工作表中设置多个打印区域
概要
本文说明MicrosoftExcel中尚未被用户发现的一些最强大、最实用的功能和函数。
例如，您可能为了执行某项计算而创建了一个新的宏，而事实上该计算任务可以借助已有的公式或函数完成。
或者，您可能为了执行某项任务而创建了一个新的宏，而该任务原本可以使用已有的功能执行。
简介
本文列出有关如何使用MicrosoftExcel的一些提示。
更多信息
将多个列中的文本联接起来
您可以使用&运算符或CONCATENATE函数将多个列中的文本连缀或合并起来；例如，如果在单元格A1:
C2中键入以下数据：
A1：
名
B1：
中间名
C1：
姓
A2：
Tom
B2：
Edward
C2：
Smith
要获得全名，请在单元格D2中，键入以下公式之一：
$D$2:
=CONCATENATE（A2,"",B2,"",C2）
$D$2:
=A1&""&B2&""&C2
注意：
单元格间的空格（""）用于在显示文本间插入空格。
设置打印区域
从MicrosoftExcel97forWindows开始，“文件”菜单上提供了“设置打印区域”工具栏按钮。
当您单击“设置打印区域”工具栏按钮时，可以将打印区域设置为当前选择的区域。
将“设置打印区域”工具栏按钮添加到现有工具栏后，可以单击“设置打印区域”，轻松地将打印区域设置为当前选定的范围。
要在Excel中添加“设置打印区域”工具栏按钮，请执行下列步骤：
1.
在“视图”菜单上，指向“工具栏”，然后单击“自定义”。
2.
单击“命令”选项卡。
3.
在“类别”下，单击“文件”，然后向下滚动命令列表，直到看到“设置打印区域”工具栏按钮。
4.
单击“设置打印区域”，然后将该命令拖动到一个现有的工具栏。
排除列表中的重复项目
如果您所创建的项目列表中包含重复的项目，而您希望派生出无重复的列表，请使用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来将文本更改为数字
有时，当您从其他源中导入文件时，数值看起来像是数字，但却表现得像文本值一样。
要解决此问题，请将这些值转换为数字。
执行此操作的方法之一是将这些文本值乘1。
要转换文本值，请按照下列步骤操作：
1.
单击工作表中的空白单元格，确保此单元格未设置为文本格式，然后在此单元格中键入1。
2.
在选中此单元格的情况下，单击“编辑”菜单上的“复制”。
3.
选择要转换为数字的值所在的区域。
4.
在“编辑”菜单上，单击“选择性粘贴”。
5.
在“运算”下，单击“乘”，然后再单击“确定”。
此方法可以将文本转换为数字。
通过查看数字的对齐方式，可以得知是否成功转换了文本值。
如果使用的是“常规”格式，而值靠右对齐，则为数字；文本值会靠左对齐。
使用“文本导入向导”将文本更改为数字
为此，请按照下列步骤操作：
1.
选择要转换为数字的值所在的范围。
2.
在“数据”菜单上，单击“分列”。
3.
单击“下一步”两次，转到向导的步骤3。
4.
在“列数据格式”分组框中，单击“常规”，然后单击“完成”。
此方法可以将文本转换为数字。
通过查看数字的对齐方式，可以得知是否成功转换了文本值。
如果使用的是“常规”格式，而值靠右对齐，则为数字；文本值会靠左对齐。
对大纲中的小数进行排序
假设您在单元格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中。
使用数据表单将记录添加到列表中
若要将记录添加到列表中，请使用预定义的数据表单。
首先单击列表中的一个单元格，然后单击“数据”菜单上的“记录单”。
输入当前日期或时间
如果希望在单元格中快速输入当前日期，请按Ctrl+;，然后按Enter键。
要在单元格中快速输入当前时间，请按Ctrl+:
，然后按Enter键。
查看公式中的参数
当您在单元格中输入一个公式时，可以按Ctrl+Shift+A来查看公式中的参数。
如果键入=RATE，然后按Ctrl+Shift+A，则可以看到该函数的所有参数－例如，=RATE（nper,pmt,pv,fv,type,guess）。
如果您希望看到更多的详细信息，则在键入=RATE后按Ctrl+A以显示“函数向导”。
在某个单元格范围内输入相同的文本或公式
若要在某个单元格范围内快速输入相同的文本或公式，请执行下列步骤：
1.
选择要填充的单元格范围。
2.
键入文本或公式，但是不要按Enter键，而是按Ctrl+Enter。
数据将显示在您所选择的范围中。
使文本框与单元格中的数据相链接
为此，请按照下列步骤操作：
1.
在“绘图”工具栏中，单击“文本框”，单击工作表，然后拖动指针以创建文本框。
2.
在公式编辑栏中单击或按F2键，以便在公式编辑栏中进行更改。
3.
键入链接公式－例如，键入=A1，然后按Enter键。
您在链接单元格中输入的文本会显示在文本框中－例如，A1。
您可以根据需要将文本框移动到工作簿中的任何工作表。
使图片与某个单元格范围相链接
您可以复制某个单元格范围，然后将结果图片粘贴到工作表中。
通过执行此操作，可以在工作表中的任何位置轻松地看到单元格的内容。
您可以使用此方法在同一个页面上打印不相邻的单元格。
图片会与单元格范围相链接，并会根据内容更改和格式更改进行更新。
要生成链接的图片，请按照下列步骤操作：
1.
选择单元格范围。
2.
在“编辑”菜单上，单击“复制”。
3.
选择要在其中显示图片的单元格。
4.
按住Shift键，同时在“编辑”菜单上单击“粘贴图片链接”。
结果会得到一个快照，当源单元格发生更改或格式变化时，该快照会随之更新。
长公式疑难解答
如果创建的长工作表公式未返回预期结果，请拖动指针，在公式编辑栏中选择该公式的一部分，然后按F9键。
执行此操作时，只会对选中的公式部分进行求值。
重要说明：
如果按Enter键，该部分公式将丢失。
因此，请务必改为按Esc键。
不过，如果不慎按了Enter键，可以按Ctrl+Z撤消更改。
查看指定名称的图形映射
注意：
本部分仅适用于Excel97forWindows。
将工作表的“显示比例”框设置为39%或更低时，会在屏幕上的一个矩形中显示一个指定名称，该名称包含两个或更多相邻单元格的单元格范围。
单击“标准”工具栏上的“显示比例”并键入40%或更大值时，标识指定范围的矩形会自动消失。
注意，在更早的MicrosoftExcel版本中，此功能不可用。
使用已有单元格的内容填充列中的空白单元格
假设您在列A中键入以下名称：
为了正确地排列名称，应该在空白单元格中填入名称。
为此，请按照下列步骤操作：
1.
选择单元格A1:
A10。
2.
在“编辑”菜单上，单击“定位”。
3.
单击“定位条件”，单击“空值”，然后单击“确定”。
4.
键入=a1，然后按Ctrl+Enter。
这个步骤会在您选择的空白单元格中输入名称。
5.
选择单元格A1:
A10。
6.
在“编辑”菜单上，单击“复制”。
7.
在“编辑”菜单上，单击“选择性粘贴”。
8.
在“粘贴”组下，单击“数值”，然后单击“确定”。
名称就会根据您的需要沿单元格向下填充。
从相对引用切换到绝对引用
可以按F4键切换公式的相对单元格地址和绝对单元格地址。
当您在公式编辑栏中键入公式时，使用了相对地址格式的单元格引用，例如A1。
键入引用后，按F4键，单元格引用会自动更改为绝对单元格引用，例如$A$1。
继续按F4键，可以显示绝对引用和相对引用的混合格式。
有关单元格引用的更多信息，请单击MicrosoftExcel帮助中的“查找”选项卡，键入绝对和相对（absoluteandrelative），然后双击“相对和绝对引用之间的区别”（Thedifferencebetweenrelativeandabsolutereferences）主题。
使用OFFSET函数修改插入的单元格中的数据
假设您在单元格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的上一行上，当您插入新行时会随之更改。
使用高级筛选命令
如果您在Excel中创建了一列数据，而且希望选择某些项目并将其复制到另一个工作表中，应使用Excel中的高级筛选命令。
要使用此命令，请指向“数据”菜单上的“筛选”，单击“高级筛选”，然后按照屏幕上显示的说明操作。
如果您不理解Excel所提示的信息，请参见MicrosoftExcel帮助。
使用条件求和对数据求和
假设您在单元格A1:
A10中创建了一列数据，而且希望对所有大于50且小于200的值求和。
为此，请使用以下数组公式：
=SUM（IF（A1:
A10>=50,IF（A1:
A10<=200,A1:
A10,0）,0））
注意：
请务必按下Ctrl+Shift+Enter，这样才可以将公式作为数组输入。
执行此操作后，您会看到公式被花括号{}括起。
不要尝试手动输入括号。
此公式对范围内的每个单元格使用嵌套的IF函数，并且仅当两个测试条件同时满足时才追加单元格数据。
使用条件求和对数据计数
假设您在单元格A1:
A10中创建了一列数据，而且希望统计所有大于50且小于200的值的数目。
为此，请使用以下数组公式：
=SUM（IF（A1:
A10>=50,IF（A1:
A10<=200,1,0）,0））
注意：
请务必按下Ctrl+Shift+Enter，这样才可以将公式作为数组输入。
执行此操作后，您会看到公式被花括号{}括起。
不要尝试手动输入括号。
此公式对范围内的每个单元格使用嵌套的IF函数，并且仅当两个测试条件同时满足时才向总数中追加一。
使用INDEX函数和MATCH函数查找数据
假设您在单元格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。
拖动填充柄以创建数字系列
通过拖动一个单元格的填充柄，可以将该单元格的内容复制到同一行或列中的其他单元格。
如果此单元格包含Excel可以按系列顺序推断出的数字、日期或时间段，则这些值将递增，而不是被复制。
例如，如果单元格中包含“1月”，您可以沿行或列在其他单元格中快速填入“2月”、“3月”，依此类推。
您还可以为常用的文本条目创建自定义填充系列，例如贵公司的销售区域。
自动填充数据
您可以双击选定单元格的填充柄，沿某一列向下填充单元格内容，填充行数与相邻列的行数相同。
例如，如果在单元格A1:
A20中键入了数据，在单元格B1中键入了一个公式或文本，按Enter键，然后双击填充柄，则Excel将沿着该列，从单元格B1到单元格B20自上而下填充数据。
将VLOOKUP函数用于未排序的数据
在Excel97forWindows及更高版本中，可以对未排序的数据使用VLOOKUP函数。
但是，您必须向公式中添加一个附加的参数。
如果您未指定Range_Lookup参数的值，那么它默认为TRUE。
注意，Range_Lookup参数是第四个参数。
这样做是为了使函数与早期版本的Excel兼容。
要让VLOOKUP函数正确地处理未排序的数据，请将Range_Lookup参数更改为FALSE。
下面是一个示例函数，它在上文“使用INDEX函数和MATCH函数查找数据”部分所创建的数据表中查找Stan的年龄：
=VLOOKUP（"Stan",$A$2:
$C$5,3,FALSE）
每三个数字返回一个
假设您在单元格A1:
A12中创建了以下数据表，而且希望在某列中每逢第三个数字就将该数字返回，并将收集到的数字放入一个连续列中：
为此，请配合使用ROW函数和OFFSET函数，例如，使用以下公式示例：
=OFFSET（$A$1,ROW（）*3-1,0）
此公式取决于输入它时所在的单元格的行。
在此公式中，ROW函数返回输入此公式时所在的单元格的行号。
将该行号乘以3。
OFFSET函数将活动单元格从单元格A1向下移动指定的行数，每逢第三个数字就将其返回。
舍入到最接近的货币单位
假设您在工作表的单元格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舍入到最接近的百分位。
安装并使用MicrosoftExcel帮助
使用MicrosoftExcel帮助可以搜索有关特定用法主题的信息，浏览主题列表，或搜索特定的词和短语而不是主题。
您还可以使用与上下文相关的帮助（按F1）查看有关任务的信息。
必须安装帮助文件，才能访问这些文件。
如果未安装帮助，请再次运行安装程序，然后单击“添加/删除”安装这些文件。
不要直接从软盘打开和保存
当您打开一个工作簿时，Excel在您保存文件的文件夹中创建临时文件，并在您从中打开此工作簿的文件夹中创建临时文件。
当您关闭文件时，会删除这些临时文件。
另外，当您保存文件时，Excel会在介质上创建此文件的副本。
如果您从软盘打开工作簿或者如果软盘没有足够的可用空间保存此文件，则上述行为会出现问题。
出于这些原因，最好在处理文件前将其复制到硬盘上。
进行修改后，将文件保存到硬盘上，然后将其复制回软盘。
只需一次击键即可创建新的图表或工作表
要快速创建图表，请选择图表数据，然后按F11。
要创建新的工作表，请按Shift+F11。
在同一个工作表中设置多个打印区域
不必使用宏即可在同一个工作表中设置多个打印区域。
为此，应使用“自定义视图”命令和“打印报告”命令。
基本操作是，定义工作表视图，然后使用您所选择的视图定义报告。
有关更多信息，请参见MicrosoftExcel帮助。
升级会员 