小丸子学 Excel2000 之三

小丸子：黄花菜老师，上次那一课我可真没白学，回去以后我不但仔细地复习了好几遍，还举一反三地做了很多练习，你看，这是我为我二舅那小餐馆做的菜谱，这个是我为我三姨做的常用电话号码表，还有这个，这个是我为我小外甥做的课程表……
    说着，小丸子拿出好一些各式各样的表格，黄花菜一看，嘿，做得还满像那么回事儿！
    小丸子：小丸子，看来你对Excel的基础知识已经掌握得不错了，那么从今天起，我们开始学习稍高一层的知识——如何在Excel里进行计算。你要知道，在整个Office办公软件家族里，Excel是以计算功能见长的，从简单的四则混合运算开始到复杂的财务计算、统计分析，Excel都能轻松解决！
    小丸子：太棒了！
    黄花菜：在Excel里，所有的计算都是靠“公式”来完成的。
    小丸子：“公式”？
    黄花菜：Excel的公式就是对工作表中的数值进行计算的方程式，或者说是计算表达式，它以“=”号开头，并由一些数值和运算符号组成。这里所说的数值，不光是普通的常数，还包括单元格引用、单元格名称以及工作表函数。要判断一个单元格的内容是否是公式很简单，你只要看到在最前面有一个“=”号的话，那肯定就是公式。同样，如果我们要创建公式，一定不要忘了要以“=”号开始。
    我们先来看一下几个正确书写的公式范例：
   

=357+31	常数的加法运算
=A1*B2	包含单元格引用的乘法运算
=SUM（C1：D6）	包含求和函数的运算
=售价 - 进价	包含单元格名称的减法运算

怎么样，刚开始可能会觉得有点复杂，但你看过上面的公式范例后，就能自然而然地了解到它的语法与功能了，是不是挺简单的？
    小丸子：好像不是太难。
    黄花菜：我们再来举一个简单的例子，让你加深对Excel公式的了解。比如我们现在有这样一张工作表，A2是你2月份的工资500，B2是奖金300，C2是你应该上交的伙食费200，那么如果要在D2单元格里计算你这个月的净收入的话，应该怎么写公式呢？
    小丸子：这个我知道，不就是“=500+300-200”吗，结果应该是600（怎么这么少啊？）！
    黄花菜：这样写并没有错，但在Excel里如果这么写公式的话就显得有点委屈比尔盖茨了。我们应该这么来写“=A2+B2-C2”，结果还是600。
    小丸子：这样也能得出600来？
    黄花菜：对啊，这样的公式就是我们刚才所说的包含单元格引用的运算。它像一个方程式，计算在指定的变量之间进行。公式里面的单元格引用就是方程式变量，因为我们已经把A2、B2和C2的值告诉给了Excel，所以Excel能轻松地进行计算。
    小丸子：既然结果一样，为什么说我写得不好呢？
    黄花菜：看来你还没体会到Excel公式中利用单元格引用进行计算的好处。这样吧，假如你3月份的工资涨到了600，其它不变，那么你3月份的纯收入应该如何计算呢？
    小丸子：“=600+300-200”，结果应该是700。
    黄花菜：你是不是得重写一个公式来计算啊？
    小丸子：那当然，参加计算的数值已经改变了，我当然要重写一个公式。
    黄花菜：如果利用单元格引用就不用重写公式啦，你看，只要我将A2的值改成600，我的公式所得出的计算结果也会自动更新到700了！
    小丸子：原来是这样啊！
    黄花菜：是啊，在Excel公式里，作为变量存在的单元格引用，它的值是会随着被引用单元格的值的变化而变化的，而它的变化也会直接影响到公式的计算结果，这是其一；其二，我们刚才的例子毕竟比较简单，如果进行的计算非常复杂，甚至要动用Excel工作表函数，或者你的计算要分几步来完成，前一步的结果是后一步的计算参数，在这种情况下如果用常数的话，恐怕你又得没完没了的加班了；其三，同一单元格可以被别的公式无数次引用，既然数字已经输入到工作表里去了，我们干嘛要多输入一次呢？再说啦，放着现成的数字不用，再去手输一遍，那你还是按计算器快呢！
    小丸子：言之有理啊！看来在公式里，常数应该要尽量避免使用的，不然就体现不出公式在计算上的优势来。
   黄花菜：在输入公式的时候，还有一点要引起注意，如果你正在使用五笔、智能ABC之类的中文输入法，一定要把输入状态由全角改成半角，否则的话对于一些全角的标点符号Excel会不认识，而正确的运算符号又无法输入。 关于Excel公式中的各种运算符，我们也有必要来认识一下，我这里有张表格，表格里的运算符有些一看就能懂，有些就只有在接下去的学习中慢慢掌握了：

运算符	含义	示例	运算符	含义	示例
+	加	5+8	>=	大于或等于	A1>=B1
-	减	9-6	<=	小于或等于	A1<=B1
*	乘	A1*B3	<>	不等于	A1<>B1
/	除	C1/A1	=	等于	A1=B1
%	百分比	21%	<	小于	A1<B1
^	乘幂	10^3（10的立方）	>	大于	A1>B1
,	合并多个引用	SUM(A1:C1,B2:D5)	(空格)	将两个单元格区域进行相交	A1:C1 B1:B5的结果实际上为B1
:	引用单元格区域	A1:C1	&	连接文本串	“电脑”&”界”产生”电脑界”
( )	改变运算优先级	(1+2)*3

   小丸子：还好绝大部分小丸子我早已在小学里就学过了，我还以为又有很多怪模怪样的东西呢！
   黄花菜：讲到这里，相信小丸子你已经对Excel公式的基本功能与用法有了充分的了解。Excel公式之所以具备异常强大的计算功能，是因为它具备两大特色，一是有众多的工作表函数可以调用，二是可以灵活地对单元格进行引用。而实际上，想要用好Excel的函数的话，首先就要能熟练地使用单元格引用。通过刚才的例子，我们已经不用再怀疑在公式中使用单元格引用的好处了。现在，我们就来重点讲讲怎样使用单元格的引用。
    单元格引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。
    单元格的引用方法有两种，一种是使用被引用单元格的地址，另一种是使用单元格的名称。
    就像刚才的例子里所讲的那样，如果要计算小丸子的纯收入，可以使用公式“=A2+B2-C2”。这就是一个典型的使用被引用单元格的地址进行单元格引用的公式。我们在输入这样一个公式的时候，可以直接手工输入，但是如果因为工作表比较大而不容易知道每一个被引用单元格的地址时，我们也可以这样输入：激活要输入公式的单元格，如D2，先输入一个等于号“=”，然后用鼠标点击A2单元格，这时A2单元格的周围会出现一个虚线框，但光标仍停留在D2单元格中……
   
小丸子：咦，等于号后面自动写上了“A2”！
   黄花菜：别急，我们接着输入一个加号“+”，再点击一下B2单元格，这时B2单元格的周围出现一个虚线框，但光标仍停留在D2单元格中……
   小丸子：和刚才一样，加号“+”后面自动写上了“B2”！

   黄花菜：我们再输入一个减号“-”，然后点击一下C2单元格，最后按回车。看，是不是已经得出了700这个结果？再来看编辑栏里的公式，是不是“=A2+B2-C2”？
   小丸子：好神奇啊！
   黄花菜：这样用鼠标配合键盘进行公式输入表面上慢一些，但如果公式里包含的单元格引用较多而且位置比较分散的时候，其实是既快速又准确地一种方法。用这种方法，无论你要引用的单元格在哪个位置，甚至是别的工作表，或者别的工作簿，也难不倒你了，
    按照这个方法，我们再来做一个实验，如果小丸子你的工资数和奖金数在另一个工作表“Sheet2”里，而伙食费这个数字还在现在这张工作表的C2单元格里，那么计算纯收入的公式应该怎么写呢？小丸子，你来试试！
       小丸子：啊？我啊，我，那就试试吧！
    小丸子先在E2单元格里输入一个等于号，然后用鼠标点击Sheet2的工作表标签以切换到Sheet2，原来工资数与奖金数在B1和B2单元格里。于是，小丸子用鼠标点击了一下Sheet2的B1单元格，然后输入一个加号“+”，接着点击了一下Sheet2的B2单元格，再输入一个减号“-”，然后点击Sheet1的工作表标签以切换回公式所在工作表，再点击C2单元格，敲
回车，OK！算出来了，不多不少刚好700！
   黄花菜：不错嘛，小丸子！不但学得快，还能举一反三，照这个样子下去，我看用不了多久就能把Excel给学好！ 嘿嘿，嘿嘿，嘿嘿嘿嘿……平时少有人夸的小丸子被黄花菜这么一夸，竟有点不好意思起来，就只会傻笑了。
    黄花菜：其实，在使用被引用单元格的地址进行单元格引用这种方法中，还分两种情况，一种是绝对引用，另一种是相对引用。
    小丸子正乐着，一下子又被黄花菜弄出来的这两个新名词给唬住了。
    黄花菜：在讲相对引用与绝对引用之前，我们先来学习一下公式的复制，因为所谓相对、绝对都是针对公式的复制而言的。
    正如上一次我们所讲到的，单元格内容都是可以复制的，这对于包含公式的单元格也是一样。我们先来复制一个公式试试。先激活D2单元格，按Ctrl-C，再激活D3单元格并按Ctrl-V，好，复制完成，我们来看看D3单元格。
    小丸子：怎么结果是0啊？公式也从“=A2+B2-C2” 变成了“=A3+B3-C3”？这是怎么回事情？
    黄花菜：这就是因为单元格的引用有相对与绝对之分。所谓相对引用，就是说单元格或单元格区域的引用是相对于包含公式的单元格的相对位置。就拿刚才来说吧，D2 的公式是“=A2+B2-C2”，Excel 并不是直接去找A2、B2、C2这样的单元格地址，而是在距D2 左面第三、二、一个单元格中查找数值。这就是相对引用。我们在复制包含相对引用的公式时，Excel 将自动调整复制公式中的引用，以便引用相对于当前公式位置的其他单元格。所以在D2的公式复制到D3时，包含公式的单元格变成了D3，Excel就去找D3左边第三、二、一个单元格了，这样我们就看到公式变成了“=A3+B3-C3”。
    小丸子：这也就是说公式里如果是相对单元格引用的话，一旦公式的所在位置发生了变化，公式里面的单元格引用也会有相应的变化了？那我要是把D2的公式复制到D4，是不是公式会变成“=A4+B4-C4”了？
    黄花菜：你自己试试看！
    小丸子用刚才的方法把D2的公式复制到D4，果然公式变成了“=A4+B4-C4”。
    黄花菜：如果不想在复制公式的时候产生这种变化，我们就要用到绝对引用了。绝对引用与相对引用恰恰相反，它牢牢记住被引用单元格的真实地址，任你怎么复制，也不会产生任何变化。
    小丸子：那绝对引用是怎么个用法呢？
    我们刚才用到的所有单元格引用都是相对引用，这是Excel默认的引用方式。如果要使用绝对引用，我们只用在相对引用的基础上，在字母和数字前面加上美元符号“$”就行了。如果要把相对引用修改成绝对引用，可以先在编辑栏里把相对引用抹黑，再按一下F4，Excel会自动为你加上“$”。下面我们可以来试一下。
    黄花菜用鼠标激活了D2单元格，然后在编辑栏里把公式中“=”后面的部分都抹黑，接着按了一下F4键，只见原来的“=A2+B2-C2”马上变成了“=$A$2+$B$2-$C$2”，最后一个回车搞定！
    黄花菜：小丸子，你再来复制一下D2的公式试试！
    小丸子：好的！
    于是小丸子又照刚才的方法把D2单元格的公式复制到了D3单元格里。
    小丸子：700！算出来了。而且公式也与D2是一模一样的！
    黄花菜：这就是相对引用与绝对引用的区别，如果只是对一个公式而言，它们的计算结果没有任何区别，但如果你要把这个公式复制若干次时，两者的效果就大不相同了！
    小丸子：感觉稍微有点儿难度了！
   
    黄花菜：刚才我们讲的是使用被引用单元格的地址进行单元格引用，下面我们再来讲讲使用单元格的名称进行引用。 在Excel里，我们可以为一个或一些单元格起一个名字，这样的话在写公式的时候，我们就可以直接用这些名字代替一大堆什么A1、B4之类的单元格地址了。
    给单元格起名字的方法很多，我们今天先来说其中比较常用的一种。比如说，我们现在要把A2和B2单元格分别命名成“工资”和“奖金”。先用鼠标激活A2单元格，然后依次点击菜单“插入”—“名称”—“定义”，这时候会出现一个窗口，我们在“在工作簿中的名称”下方的空框里填上“工资”，然后按“确定”就行了。用同样的方法，我们还可以把B2单元格命名成“奖金”。
    现在我们来点击一下编辑栏左边的名称框上的小箭头，刚才起好的名字都在这里呢！我们点击一下“工资”试试！
    小丸子：A2单元格被激活了！
    黄花菜：对，给单元格起名字不但在写公式时有用，在单元格的快速定位上也非常有用！小丸子，你来试试给C2单元格起个名字吧！
    用黄花菜的方法，小丸子很快地把C2单元格起名字叫“伙食费”。
    黄花菜：好，现在我们利用刚才起的这些名字来写公式。
    说着，黄花菜在D2单元格里直接输入“=工资+奖金-伙食费”，然后敲了一下回车。
    小丸子：真的算出来了！
    黄花菜：小丸子，这两种单元格的引用方法你一定要掌握好，要想用好Excel公式，它们可是基础哟！
    小丸子：明白，回去以后一定再好好练练！
    黄花菜：下次你来时，我们要好好的学习Excel的工作表函数，如果说单元格引用是公式的基础，那工作表函数就是精髓了，今天我不讲太多，不过有一个“自动求和”功能比较简单，应该学学！我们上次做的表格你带来了吗？
    小丸子：在这儿呢！
    说着，小丸子把上次黄花菜送给他的软盘插到了电脑的软盘驱动器里。
    黄花菜：我们先来打开上次做的“小丸子”工作簿。
   
   黄花菜点击了一下工具栏里的打开 按钮，这时Excel弹出了一个关于文件打开的窗口，黄花菜在“查找范围”里选择了“3.5寸软盘”，然后在文件列表里选择“小丸子”，最后点击“打开”，于是上次那张“丸子公司上半年销售业绩一览表”就又呈现在眼前了。
黄花菜：我们先把B4到H4单元格抹黑，然后点击一下工具栏里的自动求和 按钮，看看H4单元格里现在有了什么？ 小丸子：让我先算算看……它把B4到G4单元格里的数字累加起来了！这么快！
    黄花菜：其实，这个结果仍是由Excel公式来完成计算任务的，自动求和这项操作只不过是让Excel帮我们写了这个公式而已！你再仔细看看！
    小丸子：是有这么一个公式来着：“=SUM(B4:G4)”，但是我看不太懂！
    黄花菜：这是一个包含工作表函数的公式，其中的SUM（）就是一个用于求和的函数，而B4:G4是求和的范围，现在看懂了吧！来，你来试试把华东区的销售业绩进行求和计算！
    只见小丸子把B4到B10单元格抹黑，然后点击了自动求和按钮，于是，华东区的上半年销售业绩很快就有了一个合计数。小丸子一高兴，又用同样的方法把西北区的合计数给算了出来，正当他还要接着往下算的时候，突然被黄花菜叫了声“慢！”
    小丸子：怎么了黄花菜老师？我又弄错了什么？
    黄花菜：错倒是没错，只不过像你这样一个一个地求和，虽然是自动的，但还是太慢！我来教你一个更快速的方法！
    小丸子：还有更方便的？
    来，我们先点击B10单元格，然后把鼠标移动到这个单元格的右下角，这时鼠标指针会变成一个小的十字架样式。按下鼠标左键不放的同时，将鼠标向右拖动，一直拖到G10单元格才将鼠标左键放开，你看，是不是完成了所有的求和？
   
   小丸子：真的啊！
   黄花菜：刚才我所用到的方法，在Excel里叫做“拖曳”，在对序列数或者公式的大量复制工作中异常有用，而刚才所说的单元格的相对引用，在这种情况下是不是也显得特别有用了呢？
    小丸子：那可不，如果是绝对引用的话我可得一个一个来修改公式了！
   
   黄花菜：用同样的方法，我们还可以快速算出每个月各区域的销售业绩和来。然后在单元格A10、H3里分别加上“合计”两个字，再用上次我们学到的表格修饰方法对表格进行重新修饰，你瞧，这份表格是不是更专业了？
    小丸子：……
    黄花菜：小丸子！小丸子！！
    只见我们的小丸子痴痴地望着这份改进后的“丸子公司上半年销售业绩一览表”，表情时而严肃，时而高兴，心思已经不知道飞到哪里去了！