excel精讲.docx
《excel精讲.docx》由会员分享，可在线阅读，更多相关《excel精讲.docx（54页珍藏版）》请在冰豆网上搜索。
excel精讲
充分发挥区域的作用
人们在使用Excel的时候，除了进行琐碎的数据输入工作外，大多数情况下都会用到区域（Range）。
不论是在复制、移动、格式调整、命名还是填充一些内容，区域在Excel日常操作中都会占有重要的角色。
这是为什么呢？
很简单，同时处理一些单元格组成的区域要比一个一个地处理这些单元格容易得多。
例如，假设你想知道人B1到B30中所有列的平均值，那么你可以在AVERAGE函数中输入所有这30个单元格作为参数，但我想这样人们很快就会厌烦。
只要输入=AVERAGE（B1:
B30）就行了，这样速度自然快得多（而且也会更准确）。
换句话说，区域的应用可以节省时间，自然也可以减轻人们用手指输入的工作量。
但区域的应用还不止这些。
区域是一种强大的工具，可以释放出Excel潜在的巨大能量。
因此，人们对于区域的概念掌握得越多，就越能够从购买Excel的高效中获得更大的回报。
本章将先介绍区域的一些基本概念，然后会带领大家进入一些更深入的处理工作，并且介绍可以发挥Excel区域功能全部优点的技术。
Excel区域选择技术综述
在使用Excel的时候，在下面三种情况下人们需要选择一个单元格区域：
对话框中的某个字段要求有一个区域作为输入的时候
输入函数参数的时候
选中一条需要使用一个区域作为输入的命令之前
在对话框中的一个字段或者在函数参数中，选择一个区域最直接的办法是手工输入区域的坐标。
只要输入左上角单元格的地址（称为锚点单元格，即anchorcell），后面跟随一个冒号，然后是右下角单元格的地址。
在使用这种方法的时候，你要么必须能够看到自己想要选中的区域，要么必须事先知道自己所需的区域坐标。
由于人们经常会缺乏这些信息，因此大多数人不是直接输入区域坐标，而是使用鼠标或者键盘来选择所需的区域。
用鼠标选择区域
尽管既可以使用鼠标来选择一个区域，也可以使用键盘来选择，但人们会发现使用鼠标更容易。
下面几节将介绍使用鼠标选择一个区域的几种方法。
用鼠标选择连续的区域
一组连续（没有空隙）的单元格构成的长方形区域是最普通的区域类型。
使用鼠标选择这种区域的操作步骤如下：
1、在该区域的左上角单元格单击鼠标（这个单元格称为锚点），然后按下并保持住鼠标左键。
2、现在鼠标左键仍然保持为按下的状态，向这个区域右下角的单元格拖动鼠标的光标。
单元格选择指示仍然在锚点处显示着，同时Excel区域选择技术综述会将这个选中区域中的其他单元格以反相显示。
公式栏的“名称”（Name）框会你选中的行数和列数。
说明：
上述步骤显示的是如何从左上角向右下角选择一个区域，但这并不是唯一途径。
你也可以从右下角的单元格单击鼠标，然后向左上角选中一个区域。
如果右下角在屏幕上显示着而左上角没有显示出来，那么这种办法就很有用。
拖动选择区的时候，屏幕会自动向上滚动，显示出区域的顶部。
3、释放鼠标按钮，这些单元格仍然会处于选中的状态，显示出你已经定义的区域，而“名称”框会显示出锚点单元格的地址。
用鼠标选择一行或一列
人们可以使用工作表的行和列标题快速地选中一个只由一整行或者一整列构成的区域。
如果是由某一行构成的区域，你可以单击该行的标题；如果是由某一列构成的区域，你可以单击该列的标题。
如果你需要选中相邻的几行或者几列，那么可以单击并拖动鼠标经过相应的行、列标题。
如果想选中所有的行和列（也就是整个工作表）该怎么办？
很简单：
单击工作表左上角附近的“全选”按钮就行了。
也可以按Ctrl+A。
用鼠标在扩展模式中选择区域
另外还有一种用鼠标选择一个矩形连续区域的可选办法，这要使用Excel的扩展（Extend）模式。
激活这种模式后，Excel会把选中的区域扩展到你单击的任何单元格处。
这种办法的步骤如下：
1、单击想要选中的区域的左上角单元格。
2、按F8键，这时Excel会进入扩展模式（在状态栏中会显示出“扩展”字样）。
3、单击区域的右下角单元格。
Excel会选中整个区域。
4、再次按下F8键关闭扩展模式。
用鼠标选择不连续的区域
如果想处理的单元格散布在工作表的多个位置，那么你需要把它们组合起来，形成一个不连续的区域。
定义不连续的区域的秘密在于选择各单元格的时候要按下Ctrl键。
下面是操作的详细步骤：
警告：
用这种方法选择单元格的时候要小心。
选中一个单元格后，取消选择的唯一办法是重新开始。
1、选中你想包含在该不连续区域中的第一个单元格，或者第一个矩形区域。
如果你正在选择一个矩形区域，那么可以使用前面介绍的任何一种方法。
2、按下并且保持住Ctrl键。
3、选择构成这个不连续区域的其他单元格或者矩形区域。
注意，在选择后续矩形区域的过程中不能使用扩展模式。
4、完成了对这些单元格的选择后，释放Ctrl键。
用鼠标进行区域选择的技巧
在选择一个区域的时候，要记住下面的这些有用的技巧：
在选择一个矩形、连续的区域时，你可能有时自己选择右下角单元格是错误的，选择的区域可能太小了，也可能是太大了。
为了解决这种问题，你可以按下Shift再单击正确的右下角单元格，这样区域就会自动做出相应的调整。
在选择了一个较大的区域后，经常会出现由于屏幕滚动而看不到活动单元格的情况。
如果你需要先看到活动单元格然后才能继续操作，那么可以使用滚动条把它显示出来，也可以按Ctrl+退格键。
警告：
在选择不连续区域时，在选中了第一个单元格或者区域后一定要让Ctrl键保持为按下的状态。
否则Excel会把当前选择的单元格或者区域作为这个不连续区域的一部分也包含进来。
如果你是把这个区域作为一个函数的参数，那么这样就会在该函数中出现一个循环的引用。
用键盘选择单元格区域
如果你的鼠标正用于文字处理中，或者你就是喜欢使用键盘，那么我们仍然有多种区域选择办法。
实际上，这种情况下我们的可选有三种以上，如下面几节所述。
用键盘选择连续的区域
如果你想要处理的单元格构成了一个连续的矩形块，那么可以用如下方法通过键盘来选择：
1、使用箭头键选择区域的左上角单元格（也就是锚点单元格）。
2、按下并县保持住Shift键。
3、使用箭头键（或者如果这个区域很大，也可以使用PageUp和PageDown键）加亮显示其余的单元格。
4、释放Shift键。
用键盘选择一行或者一列
用键盘选中一整行或者一整列是很容易的。
只要选择你想要的行或者列中的一个单元格，然后按Ctrl+空格键即可选中该单元格所在的列，按Shift+空格键即可选中该单元格当前所在的行。
如果想选择整个工作表，可以按组合键Ctrl+A。
用键盘选择不连续的区域
如果需要用键盘选择一个不连续的区域，可以按照下列步骤操作：
1、选中自己想要包含在该不连续区域中的第一个单元格或者区域。
2、按组合键Shift+F8进入“添加”模式（在状态栏中会显示出“添加”字样）。
3、选择你想要包含在该不连续区域中的其他单元格或者区域。
4、重复第2步和第3步，直到选完了整个区域。
用键盘进行区域选择的技巧
Excel中也有一些技巧可以让通过键盘来选择区域变得更容易、高效：
如果想选择一个包含数据的连续区域，那么有一种选择整个区域的更容易的方法。
首先，移动到该区域的左上角单元格，为了选择这个左上角单元格以下的连续的其他单元格，你可以铵组合键Ctrl+Shift+向下箭头键；为了选择选中的单元格右侧的连续的单元格，可以按组合键Ctrl+Shift+向右箭头。
如果需要选择的区域很大，在一个屏幕中显示不下，那么可以激活ScrollLock键来滚动屏幕显示。
ScrollLock键被听激活后，按下箭头键（或者PageUp和PageDown）即可滚动屏幕显示的内容，而选中的内容不会受到影响。
处理三维区域
三维区域是指由多个工作表选择而成的一个区域。
这个概念功能强大，因为它意味着人们可以在两个或者更多工作表上选择一个区域，然后向其中输入数据、应用格式或者运行某个命令，所有这些区域都会同时进行上述操作。
为了创建三维区域，首先需要把想要处理的工作表组合。
选择多个工作表时可以采用如下技术：
如果要选择相邻的几张工作表，你可以单击第一张表的标签，按下Shift键，然后单击最后一张工作表的标签。
为了选择不相邻的几张工作表，你可以按下Ctrl键，然后单击自己想要组合的各表的标签。
如果要选择某个工作簿中的所有工作表，你可以右键单击任何一张一的标签，再选择“选定全部工作表”命令。
选定了所需的工作表之后，这些有的标签都会加亮显示，而工作簿的标题栏中会显示出[工作组]字样。
如果想解除这些表椹的组，你可以单击不属于些组中的一个标签。
或者，你也可以右键单击该组中的任何一个标签，再从弹出的菜单中选择“取消成组工作表”命令。
现在这些工作表已经组合起来了，创建三维区域的方法是：
先激活组中任何一张表，再使用前面学过的技术选择一个区域。
Excel会在组中所有其他表中也选择与此相同的单元格。
在进行诸如公式输入这样的操作时，人们也只可以手工输入一个三维区域。
下面是三维一般格式：
FirstSheet:
LastSheet!
ULCorner:
LRCorner
这里的FirstSheet指的是三维区域中第一张表的名称；LastSheet指的是最后一张表的名称；ULCorner和LRCorner指的是想要处理的各表上的单元格区域。
例如，如果要在工作表Sheet1、Sheet2、Sheet4中指定区域A1:
E10,可以使用如下引用：
Sheet1:
Sheet3!
A1:
E10
警告：
如果在三维引用中有一个表的名称包含空格，或者两个名称中都包含空格，注意一定要用单引号将该表名称引起来，例如：
‘FirstQuarter:
FourthQuarter’!
A1:
F16
三维引用应该用在接受此功能的工作表函数中。
这些函数包括：
AVERAGE（）、COUNTA（）、MAX（）、MIN（）、PRODUCT（）、STDEV（）、STDEVP（）、SUM（）、VAR（）和VARP（）。
高级区域选择技术
关于各种基本区域选择的技术就介绍这些。
下面我们介绍一些能够更快速、更简便地完成选择工作的高级技术。
使用“定位”命令选择区域
对于一些非常大的区域，Excel提供了“定位”命令。
它可以用来快速跳转到一个指定的单元格地址或者区域名。
下面就是利用这项功能选择一个区域的步骤：
1、选择区域的左上角单元格。
2、选择菜单命令“编辑（E）>定位（G）”，或者按组合键Ctrl+G。
这时屏幕上会显示出“定位”对话框。
3、使用“引用位置”文本框可以输入区域的右下角单元格地址。
提示：
也可以在使用“定位”对话框的时候在“引用位置”文本框中输入区域坐标，以选择一个区域。
4、按下Shift键并且单击“确定”。
Excellent会选中这个区域。
提示：
另一种选择大型区域的方法是使用菜单命令“视图（V）>显示比例（Z）”，再单击“显示比例”对话框中的一个缩小系数（例如50%或者25%）。
也可以选择大视图来选择所需的区域。
使用“定位条件”对话框
人们一般都是根据工作表中的位置来选择单元格，但Excel还有一种强大的功能，可以根据内容或者其他特殊属性来选择单元格。
如果你选择“编辑（E）>定位（G）”命令，然后单击“定位”对话框中的“定位条件（S）”按钮，屏幕上就会显示出“定位条件”对话框。
根据类型选择单元格
“定位条件”对话框中包含许多选项，但其中只有四个选项能够让你根据单元格中包含的内容类型来选择这些单元格。
选项说明
批注选择包含某个批注的所有单元格
常量选择所有包含指定类型常量的单元格，这些类型在“公式”选项下由一个或者多个复选框列出
公式选择所有包含指定公式的单元格，该公式会生成下列四个复选框中某个或者多个框中指定的结果类型：
数字：
选择包含数字的所有单元格
文本：
选择包含文本的所有单元格
逻辑值：
选择包含逻辑值的所有单元格
错误：
选择包含错误的所有单元格
空值选择所有空值单元格
选择相邻的单元格
为了选择与当前活动单元格相邻的单元格，“定位条件”对话框提供了两个选项。
单击“当前区域”选项以便选择与活动单元格相连的所有非空单元格组成的矩形区域。
如果活动单元格是某个数组的一部分，那么“当前数组”选项可以选择该数组中的所有单元格。
根据内容差异选择单元格
Excel还可以对行或者列中的数据进行比较，并且只选中其中内容不同的单元格。
下面是选择的步骤：
1、选择想要对比的行或者列（保证活动单元格包含你想使用的值）。
2、显示“定位条件”对话框，并且单击下列选项之一：
行内容差异单元格此选项会使用活动单元格所在列中的数据作为比较值。
Excel会选中相关行中内容与此不同的单元格。
列内容差异单元格此选项会使用活动单元格所在行中的数据作为比较值。
Excel会选中相关列中内容与此不同的单元格。
3、单击“确定”。
例如，示例文件.xls，列B是分配给某公司所有部门的预算；列C和D分别是EastDivision（东区部门）和WestDivision（西区部门）实际得到的数值。
假设你想知道各部门有哪些项目超出了预算或者没有达到预算，也就是说，想把列C和D中的数字与列B中的数字进行比较，并且选中C和D中与B不同的单元格。
由于需要比较各行的数据，因此可以从“定位条件”对话框中选择“行内容差异单元格”选项。
根据引用选择单元格
如果一个单元格中包含公式，Excel会把这个单元格的引用单元格（precedents）定义为该公式引用的那些单元格。
例如，如果A4包含公式=SUM（A1:
A3），那么单元格A1、
A2、A3就是A4的引用单元格。
直接引用单元（directprecedents）指的是在公式中显式引用的单元格。
在上面的例子中，A1、A2和A3都是A4的直接引用单元格。
间接引用单元（indirectprecedents）指由引用单元格引用的单元格。
例如，单元格A1如果包含公式=B3*2，那么单元格B3就是单元格A4的间接引用单元。
Excel还定义了一个单元格的从属单元格（dependents），指的是那些包含的公式需要引用该单元格的单元格。
在上面的引用单元格中，单元格A4就是单元格A1的从属单元格（大家可以这样考虑：
单元格A4中的值取决于A1单元格中输入的值）。
与引用单元格相同，从属单元格也有直接引用与间接引用之分。
“定位条件”对话框可供人们选择引用单元格和从属单元格，步骤如下：
1、选择你想要处理的区域。
2、显示出“定位条件”对话框。
3、单击“引用单元格”或者“从属单元格”选项。
4、单击“直属”选项则仅选择直接引用单元格或者直接从属单元格。
如果既需要选中直接关系单元格，也需要选择间接关系的单元格，那么单击“所有级别”选项。
5、单击“确定”。
“定位条件”对话框中的其他选项
“定位条件”对话框中还有其他几个选项可以帮助人们解决进行区域选择中遇到的其他情况：
选项说明
最后一个单元格选择工作表中包含数据或格式的最后一个（也就是右下角处的）单元格
可见单元格仅选中未被隐藏的单元格
条件格式仅选中包含条件格式信息的单元格
数据有效性选中包含数据有效性规则的单元格。
如果你单击了“全部”项，则Excel会选中所有拥有数据有效性规则的单元格；如果你单击了“相同”项，则Excel会包含所有的有效性规则与当前单元格相同的单元格
“定位条件”对话框中使用的快捷键
快捷键选择的对象
Ctrl+*当前区域
Ctrl+/当前数组
Ctrl+\行内容差异单元格
Ctrl+1列内容差异单元格
Ctrl+[直接引用单元格
Ctrl+]直接从属引用单元格
Ctrl+{所有级别的引用单元格
Ctrl+}所有级别的从属单元格
Ctrl+End最后一个单元格
Alt+;可见单元格
区域中的数据输入
如果人们事先已经知道将使用某个区域进行数据输入，那么可以先选中该区域再进行输入，这样可以节省时间。
如果需要在每个单元格中输入数据，那么可以使用下表所示的键在区域中转换位置。
键结果
Enter下移一行
Shift+Enter上移一行
Tab右移一列
Shift+Tab左移一列
Ctrl+.（圆点）从区域的一角移到另一角
Ctrl+Alt+向右箭头移动到不连续选择区域中的下一个区域
Ctrl+Alt+向左箭头移动到不连续选择区域中的上一个区域
这种操作的优点是活动单元格不会离开相应的区域。
例如，如果你在某个区域的最后一行添加完数据后按了Enter键，那么活动单元格会回到最顶行相应的列上。
区域的填充
如果需要将某个区域中填入某种具体的值或者公式，Excel提供了两种方法：
选择想要填充区域，输入相应的值或者公式，再按Ctrl+Enter键。
这样Excel就地用你在公式栏中输入的内容填充整个区域。
输入初始值或公式，然后选择想要填充的区域（包括这个初始单元格），再选择菜单命令“编辑>填充”。
然后从弹出的子菜单中选择适当的命令。
例如，如果想要填充初始单元格下面的某个区域，你可以选择“向下填充”命令。
如果你已经选择了多张工作表，那么使用“编辑>填充>到同级工作表”可以填充每一张工作表中的该区域。
提示：
按Ctrl+D可以选中“编辑>填充>向下填充”命令；按Ctrl+R可以选中“编辑>填充>向右填充”命令。
使用填充柄
填充柄（fillhandle）是指活动单元格或者区域右下角处的小黑框。
这个通用的小工具能完成许多有用的工作，其中包括创建文本序列或者数字序列值，以及对相关区域进行填充、清除、插入或删除操作。
在下面的几节中我们将介绍如何使用填充柄来完成这些操作。
使用“自动填充”创建文本和数字序列
工作表中经常会用到一些文本序列（如January[一月]、February[二月]、March[三月]，或者Sunday[星期日]、Monday[星期一]、Tuesday[星期二]及数字序列（如1、3、5或者2003、2004、2005）。
除了手工输入这种序列之外，人们也可以使用填充柄来自动创建此类内容。
这种方便的功能称为“自动填充”。
下面介绍具体的操作步骤：
1、对于文本序列，可以选中自己想要使用的区域中的第一个单元格，并且输入初始值。
对于数字序列，你需要输入前面两个值，然后将这两个单元格都选中。
2、把鼠标光标放在填充柄上，这时光标会变成一个加号（+）。
3、单击并拖动鼠标的光标，直到出现的灰色边界包围了你想要填充的区域。
如果你不知道该在何处停止，那么可以观察鼠标光标附近弹出的值，它会告诉你最后一个选中单元格的序列值。
4、释放鼠标按钮。
现在Excel会在此区域中填充相应的序列值。
使用“自动填充”功能并且释放鼠标按钮之后，Excel不仅会填充相应的序列值，还会显示出“自动填充选项标志”。
如果想查看这些选项，你可以把鼠标光标移到此标志上，然后单击显示出来的下拉列表中的向下箭头。
这时你具体会看到的选项与创建的序列类型有关（关于这些选项的一些详细介绍，请参见本章稍后的“创建序列”一节）。
一般情况下这里至少会显示下列四个选项：
复制单元格：
单击此选项将以复制原始单元格的方式填充此区域。
以序列方式填充：
单击此选项使用的是默认的序列方式填充。
仅填充格式：
单击此选项只在选择的区域中应用原始单元格的格式。
不带格式填充：
单击此选项会使用序列数据填充，但不带原始单元格的格式。
示例文件：
fillhandleexamples.xls,显示了用填充柄创建的多个序列。
注意，Excel会对任何包含数字的文本值（例如Quarter1和Customer1001）进行递增。
在使用填充柄创建序列的时候，要记住以下事项：
单击后向下或者躺拖动填充柄会使其中的值递增。
单击后向上或者向左拖动填充柄会使其中的值递减。
填充柄能够识别标准的缩写词，例如Jan和Sun。
如果想改变文本序列值变化的增量，你可以输入序列的前面两个值，然后将它们都选中，再单击和拖动。
例如，输入1st和3rd后就可以生成1st、3rd、5th这样的序列值。
如果在使用填充柄的时候事先输入了三个以上的初始值，那么Excel就会按照“最佳符合”或“趋势”线的方式生成。
创建自定义的自动填充列表
大家已经看到，Excel能够识别出一些大型列表中特定的值（例如January[一月]、Sunday[星期日]、1stQuarter[第1部分]）。
当人们从一个包含这些值之一的单元格开始拖动填充柄的时候，Excel就会以适当的序列值填充经过的单元格。
但是这项功能并不局限于Excel本身能够识别的少数几种序列。
人们还完全可以定义自己的自动填充序列，步骤如下：
1、选择菜单命令“工具>选项”以便显示出“选项”对话框。
2、单击“自定义序列”选项卡。
3、在“自定义序列”框中，单击“新序列”。
这时在“输入序列”框中会显示一个插入点。
4、在“输入序列”框中输入你自己的序列，对每一项都重复此操作（保证添加各输入项的顺序与你期望的序列顺序相同）。
5、单击“添加”把这个序列加入“自定义序列”框中。
6、单击“确定”返回工作表。
提示：
如果你已经在工作表的某个区域中输入了这个序列，那么在此就不需要手工输入其中的各组成项了。
你只要单击“从单元格中导入序列”框，再输入对该区域的引用（可以手工输入该引用，也可以直接从工作表中选择）就行了。
单击“导入”按钮即可将该序列添加到“自定义序列”框中。
说明：
如果需要从自定义序列中删除某个序列，那么可以在“自定义序列“框中将它加亮显示，然后单击”删除“按钮。
区域的填充
可以使用填充柄向某个区域中填入一个值或者公式。
为此，可以输入自己的初始值或者公式，将它们选中，然后单击并且拖动填充柄经过目标区域（这里假设复制的值不会产生一个序列）。
释放鼠标按钮后，Excel就会将整个区域填充。
注意，如果初始单元格中包含的公式中在相对引用，那么Excel会自动对引用进行必要的调整，例如，假设初始单元格中包含公式=A1，如果你是向下填充，那么下一个位置将包含公式=A2，再向下的单元格包含的是=A3，依次类推。
创建序列
除了使用填充柄来创建序列，人们也可以使用Excel的“序列“命令来创建，并且对过程进行更多的控制，下面是具体的操作步骤：
1、选中自己想要创建序列的第一个单元格，并且输入初始值。
如果想李建某种特殊类型的序列（例如2、4、6，依次类推），那么应填入能够定义该序列的足够数量的单元格。
2、选择想要填充的整个区域。
3、选择菜单命令“编辑>填充>序列”，这样会显示出“序列”对话框。
4、单击“行”，可以从活动单元格开始创建一行序列，或者也可以单击“列”在列中创建序列。
5、从“类型”中可以选择自己希望的序列类型，具体的选项有：
等差序列此选项通过在序列中的前一个值基础上递增一个步长值业产生下一个值。
等比序列此选项通过在前一个值的基础上乘以一个步长来获得下一个值。
日期此选项会根据你在“日期”组选中的选项（日、工作日、月、年）来创建日期序列。
自动填充此选项的作用与填充柄相似。
你可以用它对某个数值序列或文本序列进行扩展（例如Qtr1、Qtr2、Qtr3）
6、如果想对某个序列趋势进行扩展，可以激活“预测趋势”复选框。
此选项仅适用于“等差序列”或“等比序列”。
7、如果选择的序列类型是“等差序列”、“等比序列”或者“日期”，那么需要在“步长”框中输入一个数字，这个数字就是Excel用来产生各序列中相关值时使用的步长。
8、如果需要给序列施加限制，可以在“终止值”框中输入相应的值。
9、单击“确定”，Excel就会生成相应的序列，并且返回工作表。
示例dataseriesexamples.xls。
注意,Growth（等比序列）终止在单元格C12（值为128），因为该序列的下
升级会员 