Excel公式

Excel公式（精选十篇）

Excel公式 篇1

1.1 什么是Excel的数组

Excel数组是由数据元素组成的集合, 数据元素可以是数值, 文本, 日期, 逻辑, 错误值等。数据元素以行和列的形式组织起来, 构成一个数据”矩阵”。

1.2 Excel数值分类

根据构成元素的不同, Excel数组可以分为常量数组和单元格区域数组。

1.2.1 常量数组

常量数组就是用一对大括号将构成数组的常量括起来, 行中的元素用逗号分隔, 行之间用分号分隔。常量数组不允许含有其他数组、公式或函数。当输入形如{1, 3, {1, 2}}或{1, 3, SUM (A1:A3) }等, Excel将显示警告消息。

1.2.2 单元格区域数组

通过对一组连续的单元格形成的矩形区域进行引用而得到的数组。大家知道, 引用的作用在于标识工作表中的单元格或单元格区域, 并指明公式中所使用的数据的位置。比如:B5引用了位于列B和行5的单元格;B15:B30引用了列B中行15到列B中行30的单元格区域。

1.3 Excel数组的运算

1.3.1 数组与常数的运算

运算规则1:任何行列数组A与常数b相运算, 就是A中的每一元素与b进行一一运算。

1.3.2 数组与数组之间的运算

必须遵循的规则:一个M1行N1列数组和一个M2行N2列数组的数组运算, 得到的是Max (M1, M2) 行Max (N1, N2) 列的数组。具体分为以下4种:

运算规则2:任何行列数组A与含单元素a数组B (只有一个数据元素的数组) 运算, 就是A中的每一个元素与a进行一一运算。

运算规则3:所有单行 (列) 数组相运算, 等于对应元素之间进行运算, 无法找到对应元素的则运算后结果记为错误值#N/A。

运算规则4:任何行列数组A与单行 (列) 数组相运算, 等于A中每一行 (列) 与单行 (列) 数组一一运算。

运算规则5:多行多列数组相运算, 等于对应元素之间进行数学运算, 无法找到对应元素的则运算后结果记为错误值#N/A。

2. 数组公式

2.1 数组公式

简单地说, 就是含有数组的公式, 可以返回一个或多个结果。一般情况下, 大家见到的数组公式含有内置函数, 而公式中的数组常以单元格区域数组的形式作为函数的参数, 例如:=SUM (A1:A10) 返回一个结果;=IF (A1:A3>2, 1, 0) 返回多个结果。

2.2 数组公式的输入

无论是输入了数组公式, 还是编辑了数组公式, 都要用CTRL+SHIFT+ENTER的组合键完成操作。此时, 数组公式的外面会自动加上大括号给予标记。

特别说明的是, 快捷键F9可以跟踪数组公式的最终返回值或中间值。方法是选择整条公式或数组公式局部表达式, 按下F9键即可, 撤销按ESC键。

在实际操作中, 大家需要将数组公式的返回结果保存在单元格中, 此时, 为了让数据表达有意义, 确定单元格区域成了大家考虑的重点。通常情况下, 可以利用F9功能键或已有的经验来确定返回数组的尺寸, 从而得到需要的单元格区域。

一旦数组公式的返回值保存在单元格后, 既不能更改属于数组部分的单元格内容, 也不能在这里插入新行、列、单元格或删除其中的单元格, 但可以移动或删除整个数组。

2.3. 数组公式的应用

问题1:列出清单1有哪几样商品?

【解答方法】任选单元格B17, 输入公式“=IF (SUM ( ($C$6:C6=C7) *1) , "", C7) ”, 按下Ctrl+Shift+Enter组合键, 然后选中B17单元格, 光标移至其右下角直至出现黑色十字, 最后往下拖拉至B22。

【释疑解惑】题意是要找出没有重复的商品, 这里的做法是将重复的用空格替换。具体是这样的:让当前的数据与C6单元格 (充当C7单元格的前面数据) 开始的前面众多数据进行“=”比较, 然后将众多比较值求和并执行IF判断, 若是非零值 (说明数据已有重复了) , 用空格替换, 否则, 输出当前数据。

虽然任务是实现了, 但也因此产生了许多间隔不一的空白单元格, 为了能让之直观显示, 还可以再次改进:选择单元格B16:B22, 执行“自动筛选”, 并在筛选列表中, 选择“非空白”即可。

问题2:列出清单1和2共有的商品。

【解答方法】任选单元格B26, 输入公式“=IF (SUM ( ($C$7:$C$12=D7) *1) , D7, "") ”, 按下Ctrl+Shift+Enter组合键, 然后选中B26单元格, 光标移至其右下角直至出现黑色十字, 往下拖拉至B32。最后, 消除结果中的重复商品。

【释疑解惑】此题做法是用空格替换清单间的非共有数据。具体是:让清单2的每一个数据与固定单元格区域C$7:$C$12进行“=”比较 (当然也可让清单1的每一个数据与固定单元格区域D$7:$D$13进行“=”比较) , 然后将众多比较值求和并执行IF判断, 若是非零值 (说明数据是共有的) , 输出当前数据, 否则, 用空格替换。

3. 结束语

EXCEL数组及其运算是数组公式能够顺利表达的基础。同时, EXCEL内置函数在数组公式中也有着重要作用, 尤其是常见的SUM函数和IF函数等。巧妙灵活地使用它们能够将各种本是其他方法实现的不同问题统一化、直观化和简单化, 也弥补了一些操作出现的弊端, 大大提高了办公效率。

参考文献

Excel隐藏公式 篇2

一个隐藏单元格的公式，在选定此单元格时，公式不会出现在编辑栏中。隐藏公式的操作步骤如下：

(1) 选定想隐藏公式的单元格范围；从“格式”菜单中选择“单元格”命令，选定“保护”选项卡，如图7-17所示，

(2) 选定标记为“隐藏”的选择框，按下“确定”按钮；进入到“工具”菜单中选择“保护”命令中的子命令“保护工作表”命令，如图7-18所示。

(3) 在屏幕上出现图7-19的口令对话框。

(4) 在其中输入一个口令，接着出现图7-20的确认口令对话框。

操作后，就会看到公式不在编辑栏中出现，如图7-21，请参照图7-16比较它们的变化。

Excel数组公式之新手上路 篇3

计算自行车专卖店的销售总额

首先通过一个应用实例来说明数组公式的基本用法。已知某电动自行车专卖店去年7月销售统计表（如图1），现在要计算7月份的销售总额。这对于使用过Excel进行数据统计的朋友，肯定是易如反掌：先在E4单元格中输入公式“=C4*D4”，然后按回车键得到计算结果4400。接下来向下拖动复制该公式到E5：E19单元格，最后在E20单元格中执行自动求和命令（∑）或插入求和函数SUM，得到销售总额的计算结果89250。

如果用数组公式来计算销售总额，则可以不创建E列数据，直接在D20单元格中输入数组公式“=SUM(B4:B19*C4:C19)”，然后按下“Ctrl+Shift+Enter”组合键，这时Excel 会自动使用一对大括号 {}将该公式括起，并依次计算B4*C4、B5*C5、B6*C6、…、B19*C19的乘积（亦即数组B4:B19与C4:C19相乘），再使用 SUM 函数将这些乘积累加，最后算出的销售总额同样等于89250。如果你手动输入大括号{}，数组公式将会转换为文本字符串，并且不再起任何计算作用。

计算销售人员的月度奖金

接下来，再通过另外一个计算销售奖金的问题进一步说明数组公式使用方法（如图2）。已知销售统计表，要计算各位销售人员的月销售额，并取其5%作为销售人员的月度奖金。具体操作方法如下：先选中销售人员数据列，执行“数据/删除重复项”命令，筛选出所有不同的销售人员，并进行基本的格式设置。然后在J4单元格中输入数组公式“= SUM(IF(B4:B19=I4,C4:C19*D4:D19))”，该公式使用了SUM和IF函数的嵌套，具体含义如下：如果数组B4:B19中的数据项等于I4，就将其对应的行的J列和K列数据相乘，然后对它们求和。为了能将该公式正确地向下复制到J5:J9单元格，还需要把三个数组B4:B19、C4:C19和D4:D19设置成绝对定位（在行列标号前面加$符号），即“=SUM(IF($B$4:$B$19=I4,$C$4:$C$19*$D$4:$D$19))”，然后按“Ctrl+Shift+Enter”组合键，得到张成杰的销售额为15500，然后再将该公式向下拖动复制，就可以很快完成其他销售人员的月销售额的计算。当然，你可以先尝试使用分类汇总命令来计算，然后再与使用数组公式的计算过程进行比较，以加深对数组公式使用方法的理解。

计算导师的辅导成果

笔者曾经碰到过一个计算导师跟踪奖的问题（如图3）。已知各位教师所跟踪学生的班级和学号（这些数据由教务处随机生成），学生的期中、期末考试成绩分别在工作表qz和qm当中，S列为年级名次。这里要在工作表qz和qm当中，分别查找对应学生的期中和期末考试的年级名次，并以两者之差作为评价教师跟踪辅导效果的主要指标。通过数组公式可以使这个问题计算更简单：在D2单元格中输入数组公式“=SUM(IF((qz!$A$2:$A$1766=B2)*(qz!$B$2:$B$1766=C2),qz!$S$2:$S$1766))”，具体含义如下：当数组A2:A1766中的某一项等于B2，并且数组B2:B1766中的某一项等于C2时，取其所在数据行中S列的“年名”，因为IF函数只能判断而不能得到数据项，并且符合条件的数据项有且仅有一个，因此这里借用了SUM函数的求和功能，在数组S2:S1766中查找符合前面两个条件的数据项。其中，“*”为多个条件的交集符号，而多个条件的并集符号则为“+”。

与一般公式相比，数组公式也必须以“=”开始，还可以在数组公式中插入任何Excel内置函数，最主要的不同是输入或修改数组公式后必须按 “Ctrl+Shift+Enter”，否则会出现“#VALUE！”等错误提示，也正因如此，数组公式有时也被称为“CSE 公式”。以上操作在Excel 2007完成，对Excel 2003同样适用。

Excel公式与函数的教学体会 篇4

一从公式函数输入方式上的不同进行教学

Excel的公式与函数有两种输入方式, 教学时应循序渐进、先易后难。对于学员入门阶段, 其教学目标是让学员掌握公式函数的基本输入方法, 并能看懂函数的注解。这时公式的运用多是由参加运算的单元格和运算符组成, 函数的应用多是通过编辑栏中的“插入函数”按钮来粘贴函数, 公式与函数多为单独应用, 没有涉及混合应用。这种输入方式比较适合入门阶段的教学。

随着教学的深入, 提高阶段的教学目标是使学员掌握公式与函数、函数与函数、函数嵌套等混合使用方法。这时已经开始涉及公式与函数的混合使用, 光靠“插入函数”对话框已无法完成要求, 还需要在编辑栏中进行相应输入, 输入过程中要注意Excel函数的语法、格式及相关运算符和括号, Excel所有函数都是由“=函数名 (参数1, 参数2, ……) ”组成, 参数之间用逗号隔开, 所有标点符号都必须是英文的标点符号, 这种输入方式适合于提高阶段的教学。

二从公式函数使用层次上的区别进行教学

Excel中公式运算符和函数类别分为多种, 教学中不可能面面俱到, 从日常使用的角度来说, 应该按照常用到专门、单独到混合的原则来进行介绍。以Excel的函数为例, 按照以上原则, 教学中应分层次介绍三类函数。

1. 常用函数的应用

统计函数如求和 (SUM) 、求平均 (AVERAGE) 、求计数 (COUNT) 、求最大值 (MAX) 、求最小值 (MIN) , 这些函数在日常Excel操作中比较常用, 输入方式采用“插入函数”按钮即可完成。如要在一张工作表中求出所有学员的数学成绩总分, 先点击要输出的单元格, 再点击编辑栏中的“fx”插入函数按钮, 选择求和函数SUM, 然后选定所有学员数学成绩的数据区域, 点击“确定”就可完成操作。

2. 混合函数的应用

混合函数的应用是指由多个不同的函数混合使用才能得到结果, 这类函数用“插入函数”的方法无法直接完成, 必须在编辑栏中进行相应输入。如从身份证号码中提取出生日期, 身份证号码有18位数字, 出生日期信息是在第7位至第14位, 使用MID函数可提取出生日期信息。MID函数是从文本字符串中的指定起始位置起返回指定长度的字符。若身份证号码420605198211051516存放在D2单元格, 则我们可输入“=MID (D2, 7, 8) ”, 得到出生日期信息19821105, 但这只是一串数字, 要想把其转换为常见的日期格式还需使用另外一个函数DATE。该函数是指返回代表日期的数字。我们可以输入“=DATE (MID (D2, 7, 4) , MID (D2, 11, 2) , MID (D2, 13, 2) ) ”, 并修改单元格格式就能得到日期格式的结果1982/11/05。

3. 函数嵌套的应用

教学中常用的函数嵌套是IF函数嵌套, 它可以根据逻辑条件来判断条件成立与否 (TRUE或FALSE) , 并给出相应指定的返回值。一个IF函数的输出结果虽然只有两种, 但在实际操作过程中, 我们要判断的结果远不止两种, 这类函数也必须在编辑栏中进行输入。如根据学员的分数判断其成绩等级是属于优、良、中、差4种结果中的哪一种, 就必须用到IF函数的嵌套, 在函数中套用函数, 逐级判断。判断学员成绩等级的依据是:如果学员成绩是在90分以上就列为“优”, 成绩在75分至89分之间列为“良”, 成绩在60分至74分之间列为“中”, 60分以下列为“差”。若要判断的成绩在F2单元格, 可以得到这样一个式子:=IF (F2>=90, “优”, IF (F2>=75, “良”, IF (F2>=60, “中”, “差”) ) ) 。IF函数嵌套中尤其需要注意字符串的双引号及括号的成对出现, 字符串必须为英文状态下的双引号, 几重嵌套就必须有几重括号。由于函数中包含的各级参数和字符较多, 学员操作容易出错。会出现将IF函数嵌套写成死循环, 字符串出错, 括号没有成对出现等错误, 这时要根据IF函数嵌套的特点注重加强引导。

总之, 不断完善Excel公式与函数的课堂教学, 分阶段有效地应用多种教学方法, 充分调动学员学习的积极性, 使学员能熟练掌握知识、技能和方法, 培养学员的自学能力和创新能力, 从而培养出大量的应用型人才。

参考文献

[1]朱凤明等主编.计算机应用基础[M].北京:电子工业出版社, 2007:140~142

[2]刘升贵、黄敏、庄强兵主编.计算机应用基础[M].北京:机械工业出版社, 2010:155~160

Excel常用运算公式 篇5

2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,”/8/30″,FALSE))/360,0)。

3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),”/”,MID(E2,11,2),”/”,MID(E2,13,2))。

4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,”男”,” 女”),IF(MOD(MID(C2,17,1),2)=1,”男”,”女”))公式内的“C2”代表的是输入身份证号码的单元格。

1、求和： =SUM(K2:K56) ——对K2到K56这一区域进行求和;

2、平均数： =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数;

3、排名： =RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名;

4、等级： =IF(K2>=85,”优”,IF(K2>=74,”良”,IF(K2>=60,”及格”,”不及格”)))

5、学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;

6、最高分： =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;

7、最低分： =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;

8、分数段人数统计：

(1) =COUNTIF(K2:K56,”100″) ——求K2到K56区域100分的人数;假设把结果存放于K57单元格;

(2) =COUNTIF(K2:K56,”>=95″)-K57 ——求K2到K56区域95～99.5分的人数;假设把结果存放于K58单元格;

(3)=COUNTIF(K2:K56,”>=90″)-SUM(K57:K58) ——求K2到K56区域90～94.5分的人数;假设把结果存放于K59单元格;

(4)=COUNTIF(K2:K56,”>=85″)-SUM(K57:K59) ——求K2到K56区域85～89.5分的人数;假设把结果存放于K60单元格;

(5)=COUNTIF(K2:K56,”>=70″)-SUM(K57:K60) ——求K2到K56区域70～84.5分的人数;假设把结果存放于K61单元格;

(6)=COUNTIF(K2:K56,”>=60″)-SUM(K57:K61) ——求K2到K56区域60～69.5分的人数;假设把结果存放于K62单元格;

(7) =COUNTIF(K2:K56,”<60″) ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格;

说明：COUNTIF函数也可计算某一区域男、女生人数，

如：=COUNTIF(C2:C351,”男”) ——求C2到C351区域(共350人)男性人数;

9、优秀率： =SUM(K57:K60)/55*100

10、及格率： =SUM(K57:K62)/55*100

11、标准差： =STDEV(K2:K56) ——求K2到K56区域(55人)的成绩波动情况(数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化);

12、条件求和： =SUMIF(B2:B56,”男”，K2:K56) ——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和;

13、多条件求和： {=SUM(IF(C3:C322=”男”,IF(G3:G322=1,1,0)))｝ ——假设C列(C3:C322区域)存放学生的性别，G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5)，则此函数返回的结果表示求 一班的男生人数;这是一个数组函数，输完后要按Ctrl+Shift+Enter组合键(产生“{……｝”)。“{｝”不能手工输入，只能用组合键产生。

14、根据出生日期自动计算周岁：=TRUNC((DAYS360(D3,NOW( )))/360,0)

———假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。

15、在Word中三个小窍门：

①连续输入三个“~”，按下回车键，可得一条波浪线。

②连续输入三个“-”，按下回车键，可得一条直线。

Excel公式 篇6

案例展示如图1所示，2013级1-9班学生技能成绩已统计出来，放在各工作表的E51:J52中，本例是要通过宏表函数GET.WORKBOOK和查找、引用函数将各工作表中的数据自动填入如图2的统计表汇总表中。

操作步骤如下：

一、提取工作表名称过程：

1）打开本例工作簿文件，选择【公式】-【名称管理器】命令，弹出“名称管理器”对话框。

2）单击“名称管理器”对话框中的“新建”按钮，打开“新建名称”对话框。

3）在“名称”框里输入一个定义名称(本例输入SheetName)，方便在工作表中引用,并在“引用位置”处输入公式“=GET.WORKBOOK(1)&T(NOW())”，如图3所示。

4）点击“确定”按钮后返回工作表，在单元格A2中输入如下公式。

=REPLACE(INDEX(sheetname,ROW(A1))&T(NOW()),1,FIND("]",INDEX(sheetname,ROW(A1))),"")sheetname

提示：sheetname的工作表名称返回的是包括工作簿名称和工作表名的全名称。我们用REPLACE函数是将工作表名以外的所有字符替换掉，即只留下工作表名称。

5）按公式向下填充，就可以看到顺序返回了所有工作表名称，这样就可以在其他函数中引用了。结果如图4所示。

二、批量提取表中数据

工作表名称提取出来后，我们就可以套用在公式中，让指定表中的相关数据乖乖的来到汇总表中了。如图5所示，在D4单元格中建立公式=INDEX(INDIRECT("'"&$K2&"'!52:52")COLUMN(E$52))，确定后即可得出工作表中数据，分别向右、向下复制公式，就可得到如图2的数据了。

计算完后将“统计汇总表”工作表中的K列数据进行隐藏即可。

本例中首先利用宏表函数取出当前工作簿中的所有工作表名称，再利用INDEX函数依次取出各个工作表名称，利用REPLACE函数将工作表名以外的所有字符替换成空白。这里需要说明的是GET.WORKBOOK宏函数公式在工作表发生新建或删除工作表后不能自动更新，所以在公式里用了T(NOW())函数，其意义是让NOW函数产生当前时间，再利用T函数转换成0，从而在不影响提取工作表名称的前提下，能让公式可以自动重算。

另外，如果是在2007版Excel中使用宏表函数，在保存工作簿时可选择另存为97-2003版Excel格式，也就是扩展名为.xls的格式。

参考文献

Excel工作表中公式错误值浅析 篇7

关键词：Excel工作表,公式,错误值

Excel是一个功能强大的电子表格程序,可以进行数据的计算、分析、管理组织,生成图表、图形及其他材料,与各字处理程序、图形处理程序、数据库管理程序共享数据,等等。然而,在使用公式对数据运算、管理和分析时,如果公式不能正确计算出结果,Excel将显示一个错误值。例如,在需要数字的公式中使用文本、删除了被公式引用的单元格,或者使用了其宽度不足以显示结果的单元格时,将产生错误值。错误值可能不是由公式本身产生的。例如,如果公式产生出#N/A或#VALUE!,而是由于公式所引用的单元格含有错误。在此,我们就单元格中公式错误值的显示、原因和解决方法总结如下。

一、显示错误值:#####!

如果单元格中出现这种错误值,则原因可能有以下三种:

1、输入到单元格中的数值太长,在单元格中显示不下

如果是这种原因引起的,我们可以拖动列标之间的边界来修改列的宽度。

2、单元格公式所产生的结果太长,单元格容纳不下

此时,我们的解决方法是拖动列标之间的边界来修改列的宽度,或者修改单元格的数字格式。

3、日期和时间格式做减法,得数为负

为了避免出现这种错误,我们必须保证Excel中的日期和时间格式为正值。

二、显示错误值:#VALUE!

出现这种错误值的原因有如下六种:

1、应输入数字或逻辑值时输入了文本

解决这个错误原因时应确认公式或函数所需的运算符或参数正确,并且公式引用的单元格中包含有效的数值。

2、输入和编辑的是数组公式,然后按了Enter键

解决的方法是选定包含数组公式的单元格或单元格区域, 按“F2”键编辑公式,再按Ctrl+Shift+Enter。

3、将单元格引用、公式或函数作为数组常量输入

此时,我们要确认数组常量不是单元格引用、公式或函数。

4、赋予需要单一数值的运算符或函数一个数值区域

这时我们可以将数值区域改为单一数值。

5、在某个矩阵工作表函数中使用了无效的矩阵

解决的办法是确认矩阵参数中维数正确。

6、运行宏程序所输入的函数返回VALUE!

这时我们要检查确认函数没有使用不正确的参数。

三、显示错误值:#DIV/0!

单元格中出现这种错误值的原因无非有以下三种:

1、在公式中除数使用了指向空单元格或包含零值的单元格引用

我们可以修改单元格引用,或者在用作除数的单元格中输入不为零的值。

2、输入的公式中包含除数零

只要将除数改为非零值问题就解决了。

3、运行的宏程序中包含返回#DIV/0!的函数或公式

解决的方法是确认函数或公式中的除数不为零或不是空值。

四、显示错误值:#NAME?

出现这种错误值的原因可能有四种:

1、删除了公式中使用的名称,或者使用了不存在的名称

这时,我们要确认使用的名称存在,使用“定义”命令确认或添加相应的名称,问题就可以解决。

2、名称或函数名拼写错误

如果是名称或函数名错了,只要修改拼写错误就可解决。

3、在公式中输入文本时没有使用双引号,被解释为名称

解决的方法是将公式中的文本括在双引号中。

4、在区域引用中缺少冒号

解决的方法是确认公式中使用的所有区区域引用都使用了冒号。

五、显示错误值:#N/A

如果单元格中出现这种错误值,则原因可能有以下六种:

1、为工作表函数HLOOKUP、LOOKUP、MATCH或LOOKUP中的查找值参数指定了不正确的值域

解决的方法是确认参数“lookup_value”的数值类型是否正确。例如,可以是数值和单元格引用,但是不能是区域引用。

2、在尚未排序的数据表中使用了“VLOOKUP”或 “HLOOKUP”工作表函数追踪数值

在默认状态下,这些函数查找信息的数据表首行或首列须按升序排序,否则在大致匹配的情况下,函数的返回值将不可预料。但“VLOOKUP”或“HLOOKUP”工作表函数还饮包含一个 “range_lookup”参数,当此参数设置为“FALSE”时,允许函数在没有排序的数据表中查找完全匹配的值。

3、在数组公式中使用的参数的行或列数与包含数组公式的区域的行或列数不一致

如果要在多个单元格中输入数组公式,请确认被公式引用的区域与数组公式占用的区域具有相同的行数或列数,或者减少包含数组公式的单元格。

4、函数或自定义工作表函数中缺少一个或多个参数

只要在函数中输入全部参数就可以解决问题。

5、使用的自定义工作表函数不存在

解决的办法是确认包含此工作表函数的工作薄已经打开并且函数工作正常。

6、运行的宏程序所输入的函数返回#N/A

此时要确认函数中的参数正确并且在正确的位置。

六、显示错误值:#REF!

出现这种错误值的原因可能有三种:

1、删除了由其它公式引用的单元格或将移动单元格粘贴到由其它公式引用的单元格中

如果出错原因是由此引起的,我们可以更改公式或者在删除或粘贴单元格之后立即单击“撤销”按钮,以恢复工作表中的单元格。

2、运行的宏程序所输入的函数返回#REF

解决的方法是检查使用的函数是否有的参数引用了不存在的单元格或单元格区域。

3、远程引用一个未运行的应用程序或一个不存在的 “动态数据交换”(DDE)主题

可以启动相应的应用程序或确认使用的是正确的DDE主题。

七、显示错误值:#NUM!

出现这种错误值的原因也有三种:

1、在需数字参数的函数中使用了不能接受的参数

这时我们要确认函数中使用的参数类型是不是正确。

2、使用了迭代计算机的工作表函数并且函数不能产生有效的结果

解决的方法是为工作表函数试用不同的初始值。

3、公式产生的数字太大或太小,Excel不能表示

如果是这种原因引起的错误,我们要修改公式,使其结果在–1*10307和1*10307之间。

八、显示错误值:#NULL!

出现这种错误值的原因仅有两种可能:

1、区域运算符不正确

解决的方法是引用两个不相交的区域,用联合运算符、逗号。

2、单元格引用不正确

此时要检查区域引用中的键入错误。

Excel公式 篇8

关键词：分数段,Excel表格,单元格,函数

1 问题提出

随着网络的应用和发展,校园网可以实现多媒体教育和便利的教学管理,对于现行教学管理系统而言,使用Office办公软件的组件之一Excel来导出全校学生成绩,常见的操作通常是对各班成绩进行分类汇总、计算各班人数,求取班级平均分,计算班级最高分和最低分等;但是在各班人数不等的情况下,如何通过公式的灵活运用,获得各个班级的统计区域,统计各班不同分数段人数,成为讨论的问题。要统计的全校数据如图1所示。

2 解决方法

2.1 提取分类汇总公式中计算区域

从图1上可以看出,这是全校的统计数据,不是按各个班级分列的数据。全校有多个班级,每个班级的人数又不尽相同,如果想要获得全校各个班级的各个分数段的人数,首先需要得到每个班级的统计区域,这可以通过Excel的分类汇总功能,得到每个班级实现数据汇总的计算区域,然后通过计算范围得到每个班级的总人数,以及各班级区域内各分数段的计算公式取值范围。

Excel的分类汇总功能就是按类别对数据进行分类汇总(求和、均值、极值等)。因此要实现分类汇总,必须选择分类字段,并按选定字段进行升序或降序排序。分类汇总功能的实现可以嵌套使用。嵌套分类汇总属于多级分类汇总,是单一分类汇总的重复使用。

本案例中,在分类汇总前,需要对全校数据按班级进行排序,可以把“班级编号”作为主要关键字,考号作为次要关键字进行排序,如图2所示。

分类字段选择“班级”,汇总方式选择“平均值”,汇总项选择“考试成绩”。因为是第一次分类汇总,所以默认勾选“替换当前分类汇总”,“汇总结果显示在数据下方”,如图3所示。分类汇总结果如图4所示。如果是嵌套分类汇总,例如全校学生成绩先按院系进行第一次分类汇总,再按班级进行第二次分类汇总,可在第二次分类汇总前,取消勾选“替换当前分类汇总”,这样就可以保留第一次分类结果,并在其基础上实现多级分类汇总。

分类汇总功能完成,下一步需要对分类汇总的公式中获得引用位置。

在“公式”选项卡,选择“定义名称”,打开新建名称对话框,在名称栏处输入“area”,在引用位置处输入:

=GET.CELL(6,Sheet1!E2:E5)

注意总计平均值一栏取消,如图5所示。

在单元格F2中输入公式:“=area”,如图6所示。

使用自动填充柄复制公式从F3至F25,如图7所示。可以看到在分类汇总计算平均值的对应行内显示出了计算公式,其余行保持原来的考试成绩,只是显示的方式是以文本左对齐的方式。

分类汇总公式中都是以“=SUBTOTAL(1,”开头,因此计算区域从公式中的第13位开始,截取长度为“LEN(F2)-13”(到公式最后一个右括号之前),因此可以利用Excel的MID函数,作用是从一个字符串中截取出指定数量的字符,MID(text,start_num,num_chars)函数中text表示被截取的字符,start_num表示从左起第几位开始截取,num_chars表示从左起向右截取的长度。

另外要考虑到非汇总行数据,因其表示学生的考试成绩,长度不超过3,所以加上筛选条件“LEN(F2)>3”,当然这里可以选择其他条件进行过滤,此处不在解决问题之范畴,所以简化之。

以G2为例,输入公式:

=IF(LEN(F2)>3,MID(F2,13,LEN(F2)-13),"")

这样可以获得分类汇总行的计算区域,明细数据在此列可置为空。

如图8所示,其余行可以使用自动填充柄复制公式。

2.2 计算各分数段人数

根据G列“区域提取”的区域,计算各分数段的人数,以H2为例,输入公式:

=IF(LEN($F2)>3,COUNTIF(INDIRECT(G2),H$1),"")

如图9所示。

隐藏数据明细,二级显示分类汇总,如图10所示。

选择区域“D1:L25”,在“开始”选项卡的“编辑”区,选择“查找与替换”下拉菜单,选择“定位条件”打开“定位条件”对话框,选中“可见单元格”,如图11所示。

复制选中区域,在新的工作表中粘贴,如图12所示。

用查找与替换功能中替换功能,去除“平均值”字样,并选中数据区A2:A6,E2:I6,如图13所示。

插入图表,如图14所示。

3 结语

利用Excel的计算公式,成功获得各个班级的统计区域,统计各班不同分数段人数。

参考文献

[1]宋志明.三招搞定分数段统计[J].电脑知识与技术,2006,(04).

[2]南玉刚.由浅入深Excel2007中多手段统计分数段[J].电脑迷,2007,(04).

[3]李岗.用EXCEL统计各分数段的人数[J].电脑爱好者,1998,(01).

[4]张山风.EXCEL中各分数段人数的统计方法[J].办公自动化,2008,(08).

Excel公式 篇9

Excel是Microsoft公司推出的一套很著名的办公软件Office中的组件之一,它风靡全球,广泛地应用在社会的各个领域。Excel主要是用来管理、组织和处理各种各样的数据,利用其中的公式和函数可以处理各式各样的数据。在Excel中,还可以使用数组公式对数据进行处理,它具有非凡的功能,可以完成一些看似不可能的任务。

二、数组公式介绍

1、数组公式是用于建立可以产生多个结果或对可以存放在行和列中的一组参数进行运算的单个公式,它的特点就是可以执行多重计算。它返回的是一组数据结果。数组公式中含有一个或多个作为参数的数组。含有多个数组参数时,所有数组参数的行数和列数必须相同。数组公式的输入过程与一般公式相同,只是最后用“Ctrl+Shift+Enter”组合键使公式生效,而一般公式是按下“Enter”键使公式生效。在编辑栏显示时,以一对大括号(“{”、“}”)将整个公式括起来(大括号由系统自动生成),是数组公式区别于一般公式的标志。

2、在Excel中,根据构成元素的不同,可以把数组分为常量数组和单元格区域数组。

常量数组可以同时包含多种数据类型。它用{}将构成数组的常量括起来,行中的元素用逗号“,”分隔,行之间用分号“;”分隔。常量数组不能包含其他数组、公式或函数。例如,{2,"greensky",TRUE,#N/A}是一个1行4列的常量数组,而{1,2,3;4,5,6}则为一个2行3列的常量数组。

单元格区域数组则是通过对一组连续的单元格区域进行引用而得到的数组。在数组公式中A1:B4是一个4行2列的单元格区域数组。

3、数组作为数据的组织形式,它本身可以是多维的,但是Excel的公式中最高只支持二维数组。当数组进行运算(加、减、乘、除、幂)时,要求两个数组具有相同的维数,如果是二维数组还要求两个数组的行数相同,列数也相同。

4、数组公式的优势包括:使用数组公式可以确保公式一致、可以加快公式的执行时间(相对于利用多个公式达到相同的目的)、可以减少内存占用、可以防止公式被意外更改等,最重要的是,数组公式可以实现一些用传统公式无法实现的运算。

5、虽然数组公式的优势很明显,但其不足也是有的,例如:复杂的数组公式很难理解并修改、太多的数组公式将会使计算速度减慢、不能在数组公式中使用列引用(例如,"A:A"或"D:D")、每次输入完成后,都必须按Ctrl+Shift+Enter才能完成数组公式,而很多时候会忘记这条规则。

三、数组公式的具体应用

下面是一些数组公式在工资管理中的具体应用,在以下的例子中,为了直观,笔者在图片上将公式用文本的方式显示出来,但真正输入数组公式时,不能输入“{”和“}”号,而要用Ctrl+Shift+Enter完成数组公式的输入。

1、省略中间公式

如图1,D列的工资额是B列的日工资与C列的出勤天数相乘得到的。例如现在想要得到这些人的工资总额,通常的做法是把每一个人的工资额计算出来,然后对结果进行求和。其实,利用数组公式可以省略中间的公式直接得到结果。

其公式为:=SUM(B2:B13*C2:C13)。

它的工作原理很简单,单元格区域B2:B13中的每一个单元格,分别与C2:C13对应的单元格相乘,即B2*C2、B3*C3直到B13*C13,它们得到的结果形成一个数组,然后用SUM函数把数组中的每一个元素相加。

2、包含错误范围的求和计算

在图2中,B14中的公式为:=SUM(B2:B13),但由于B2:B13中包含错误值(B6和B11),导致B14的合计数也是错误值,这时,可以利用数组公式进行包含错误范围的求和运算。

其公式为:=SUM(IF(ISERROR(B2:B13),0,B2:B13))

这里用到了ISERROR函数,它根据参数是否是错误值,返回一个逻辑值TRUE或者FALSE。

该数组公式判断B2:B13区域中的每一个值,如果是错误值,就返回0,如果不是错误值,就返回原数,将这些数值形成一个数组,这样,公式就把单元格区域中的错误值看作是0,最后用SUM对这个数组求和。

3、范围中N个最大值求和

如图3,要求计算B2:B13范围中前N个最大值的和,N的值保存在单元格A16中。本例中就是按照工资额从高到低计算前六个人员的工资总额。

其公式为:=SUM(LARGE(B2:B13,ROW(INDIRECT("1:"&A16))))

这里用到了三个函数:LARGE函数、ROW函数和INDI-RECT函数。LARGE函数返回数据集中第N个最大值,如LARGE(A1:A10,3)表示得到单元格区域A1:A10中第三大的值;ROW函数返回引用的行号,如ROW(A5)得到5,即单元格A5的行号;INDIRECT函数返回由文本字符串指定的引用,如IN-DIRECT("A10")返回了单元格A10中的内容。

利用ROW函数和INDIRECT函数可以得到内存数组,如ROW(INDIRECT("1:3"))就可以得到一个一行三列的内存数组{1,2,3}。

再来看本例的数组公式,在本例中,LARGE函数计算了6次,每一次使用的第二个参数都不同(1,2,3直到6)。这些计算的结果存储在一个新的数组里,并且把这个数组用做SUM函数的参数。

另外,如果要计算不同数量最大值的和,把单元格A16中的N的值用其他数字代替即可,如果要计算一个范围中N个最小值的和,用SMALL函数代替LARGE函数即可。

4、返回范围中最大值的位置

在图4中,利用下面的数组公式可以得到单元格区域B1:B13中最大值所在单元格的位置=ADDRESS(MAX(IF(B1:B13=MAX(B1:B13),ROW(B1:B13),0)),2,4)ADDRESS函数可以按照给定的行号和列号,建立文本类型的单元格地址。如AD-DRESS(2,3)表示行号为2、列号为3的单元格地址,即“C2”。它得到的结果是一个文本,如果想要得到单元格引用,需要用到上面提到的INDIRECT函数。

这个数组公式的原理是,IF函数用来判断数据区域中每个单元格是否为最大值,如果是,则返回单元格的行,如果不是,返回0,这样形成一个数组,然后用MAX函数得到其中的最大值,这个最大值就是单元格区域中最大值所在的行,因为这些数据都在B列,所以最大值所在的列的列号是2,最后将得到的最大值和2分别作为ADDRESS函数的前两个参数,这样就得到了最大值的位置。ADDRESS的第三个参数4表示是用相对引用来显示地址,如果这个参数是1,则显示“$B$12”。

5、任意位置的小计

在图5中,小计的位置是不确定的,因为每一个小计要计算的范围不同,所以不能简单地用复制小计行的方法来形成其他公式。如果用数组公式就可以达到此目的,例如B14的公式为:=SUM(INDIRECT("B"&MAX(IF($A$1:A13="小计",ROW($1:13),0),1)+1&":B"&ROW()-1))

这个数组公式的原理如下:IF函数得到了一组数据,它们是A1:A13之间单元格内容是“小计”的单元格的行,然后用MAX函数得到它们之中的最大值(10),把这个最大值加1就得到了参与计算的单元格的首行的行号(11)。用公式所在的单元格(B14)的行号减1就得到了要计算的单元格的最后一行的行号(13)。用INDIRECT函数把得到的结果形成一个单元格引用(B11:B13),最后用SUM函数计算此单元格区域的合计值。

这个数组公式可以复制到任意的行,因为公式中即有绝对引用($A$1和$1),又有相对引用(A13和13),假如公式被复制到第22行,那么公式就变成=SUM(INDIRECT("B"&MAX(IF($A$1:A21="小计",ROW($1:21),0),1)+1&":B"&ROW()-1))这样公式仍然可以起作用。

注意:本例中的公式在两个小计在相邻位置时会出错,因为那样会造成循环引用,这时,需要对上面的数组公式用IF函数做一个容错处理就可以了,这里不再赘述。

6、利用数组公式进行条件求和如图6,要求计算

1、“职称为中级并且性别为女并且年龄大于35岁的人员”的数量及他们的工资总额

2、“年龄大于35岁并且职称为中级或助理级的人员”的数量及他们的工资总额

此问题的数组公式如图6所示。

在解释数组公式的工作原理前,需要了解一些数组公式中的逻辑运算规则。

在数组公式中,逻辑“与”(即同时满足条件)不能用AND函数来表示,而要用“*”号来表示;逻辑“或”(即满足条件之一即可)不能用OR函数来表示,而要用“+”号来表示。另外,不同时满足要用“<>”来表示,即要么满足条件1,要么满足条件2,同时满足的不算。

了解了以上这些后,公式就好理解了,以单元格F26的公式为例,(B2:B17="中级")+(B2:B17="助理级")就表示B2:B17之中等于“中级”或者“助理级”,用它得到的结果,和条件D2:D17>35进行“逻辑与”的运算,得到一组逻辑值,这一组逻辑值形成一个数组。对这个数组的元素再进行计算,如果它的元素为TRUE,则得到对应的工资额,如果为FALSE,则得到0,这样的一些数值形成一个新的数组,最后,用SUM函数对这个新形成的数组求和。

在包含逻辑运算的数组公式中,一定要注意逻辑运算的顺序。否则可能会得到错误的结果。例如,将单元格F26的数组公式中的一对括号去掉,变成下面的样子:

=SUM(IF((D2:D17>35)*(B2:B17="中级")+(B2:B17="助理级"),E2:E17,0))它表示计算职称为中级并且年龄大于35岁,或者职称是助理级的人员的工资总额。

这样,得到的结果就不是想要计算的结果。

7、返回范围中唯一元素的列表

在图7中,A1:A20是一些部门的名称,它们之中有些有重复,现在要求得到它们的唯一值列表,即每一个部门的名称只显示一次。

将A1:A20定义一个名称data以方便引用,然后在单元格C1中输入下面的数组公式:=IF(ROW(1:1)>SUM(1/COUNTIF(data,data)),"",INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(data),MATCH(data,data,0)),ROW(1:1))))然后将公式复制到C2:C20。这样在C列就形成了部门的不重复列表。

这个公式比较复杂,它的工作原理如下:

(1)首先要说明一下SUM(1/COUNTIF(data,data))这个公式,它也是一个数组公式,它的作用是得到数据中不重复值的数量。

假设A3单元格内的数据“人事部”在data内共有X个,那么公式对data中20个单元格逐个计算1/COUNTIF(data,A3),当对A3计算时,返回结果1/X,对A4计算时又返回一个1/X,在这20次计算里,每遇到数据“人事部”就回返回一个1/X,共返回X个1/X,将它们求和为1。也就是说,有多少个不同的数据就返回多少个1,这恰好就是不重复的数据的个数。

这样,用SUM(1/COUNTIF(data,data))就得到了data中不重复值的数量。

(2)只得到数据中不重复值的数量不是此问题的要求,此问题要求得到不重复值的列表,那么,下面的公式就要用到两个此前没有用到的函数:MATCH函数和INDEX函数。

MATCH函数可以返回在指定方式下与指定数值匹配的数组中元素的相应位置,如MATCH("人事部",A1:A5,0)可以返回3,即“人事部”在A1:A5中首次出现的位置。

函数INDEX()有两种形式:数组和引用,当参数是数组形式时,返回数组中的元素值,当参数是引用形式时,返回指定的行与列交叉处的单元格引用。例如:INDEX({100,200,300},2)可以返回200,而INDEX(A1:A10,5)可以返回“总务部”

(3)用数组公式SUM(1/COUNTIF(data,data))可以得到data中的不重复值的个数N(本例为9),如果当前行号大于N,就在单元格中显示一个空格,如果小于等于这个值,让其继续进行下面的运算。

(4)由于MATCH函数对列表中的数据进行计算时,总是从上向下计算,所以它总是得到第一个匹配的值的位置,所以,MATCH(data,data,0)得到A1:A20中每一个单元格在data列表中最早出现的行数,如图7中的E列。

(5)用步骤(4)得到的行数与它所在的行号相比较,如果相同得到它所在的行号,如果不同得到逻辑值假(FALSE),这样形成一个内存数组,其中的元素如图7中的F列。

(6)用SMALL函数分别从小到大取前面N个数字,由于数字总是小于逻辑值,所以得到了一个数组,这个数组中保存了过滤出来的唯一值的行号,如图7中的G列。

(7)用INDEX函数从data中取出其中的数据,这样就得到了data中的唯一值。如图7中的H列,它就是题目要求的最终结果。

四、结束语

Excel的数组公式在工资管理中有着一般函数和公式无法比拟的优势,灵活地运用数组公式,可以大大地提高工作效率,我们应该发挥其卓越为工作服务的作用。

参考文献

[1][美]John Walkenbach.路晓村等译.Excel2002公式与函数应用宝典[M].北京:电子工业出版社.2002.

[2]Excel Home.Excel函数与公式实战技巧精粹[M].北京:人民邮电出版社.2008.

Excel公式 篇10

一、按照引用的绝对与相对关系划分

1、相对引用

就是直接用列标和行号表示单元格,这是默认的引用方式。例如:C1。

单元格公式如果为“= A1+ B1" 就是相对引用。当使用相对地址时,单元格公式中的引用地址会随目标单元格的变化而发生相应变化,但其引用单元格地址之间的相对地址不变。

2、绝对引用

就是在公式中引用单元格的地址与 单元格的位置 无关,单元格的地址不随单元格位置的变化而变化。表示时行和列前都有$符号,例如:“:$B$1+$C$1”。

3、混合引用

是指行号固定而列标可以变化,或列标固定而行号可以变化的单元格引用。其表示形式为将固定的部分前面加上“$”符号。也就是:如果“$”符号只在字母前,那么被引用的单元格列的位置是绝对的,而行的位置是相对的;反之,如果数字前面有“$" 符号,则列的位置是相对的,而行的位置是绝对的。

4、三种引用的快速切换

可以利用功能键F4实现三种引用之间的快速切换。操作方法为:选中要改变引用方式的单元格引用后,循环地按“F4”键就能够依照“相对引用———绝对引用———列相对行绝对———列绝对行相对———相对引用———……”的顺序循环改变下去。

二、跨表格单元格引用的三种情形

1、引用同一工作簿的其他工作表中的单元格或区域。在Excel公式中,可以对当前工作簿内其他工作表中的单元格进行引用,其格式为:

工作表标签名!单元格地址

如公式“:Sheet2 !B4*0.8”就表示用Sheet2工作表中的B4单元格数值与0.8相乘。

2、引用同一工作簿多张工作表的相同单元格或区域。在Excel公式中,可直接引用同一工作簿中多张工作表的相同单元格地址,其格式为:

第一个工作表名:最后一个工作表名!单元格地址

例如,想要引用同一工作簿中Sheet1到Sheet3工作表里所有C3单元格中数据之和,可输入公式“=SUM(Sheet1:Sheet3 !C3)”,通过该公式,三个工作表C3中的内容就全被引用。

3、不同工作簿之间的数据引用。在Excel公式中,可以直接引用其他工作簿中相关工作表的单元格地址,其格式为:

【工作簿名】工作表标签名!单元格地址

如在工作簿Book1的工作表Sheet 1单元格A3中输入公式:“=[Book2]Sheet2!C6*80",就是表示将另一个工作簿Book2的工作表Sheet2中的单元格C6数值与80相乘。

三、单元格引用的应用举例

【例1】如图1所示,A1:E9为某公司产品销售信息,现在需要计算每一个销售员的销售提成,并保存在单元格区域F2:F9中。其中,提成率在单元格I1中存放。

问题分析:本例中对于提成比例3.60%,最好是引用单元格I1,而不直接在公式中输入值3.60%,以避免提成比例调整时还需要修改公式。具体操作步骤如下:

1、将光标定位到单元格 F2。

2、在单元格 F2 中输入公式“=E2*I1”,得出对应第一个业务员的销售提成。

3、将单元格 F2 一直向下拖动到单元格 F9 中,但是发现复制后的数据并不正确,如图 2 所示。

说明 :很明显,此处的错误在于单元格I1的引用方式采用了相对引用,这样一来单元格 'F2的公式复制到单元格F3后,其公式就变为了“=E3*I2”,而I2为空白单元格,所以单元格F3的结果为0。

4、将I1的相对引用改为绝对引用。选中单元格F2,在编辑栏里将公式改为“=E2*$I$l”,或在编辑栏里选中单元格I1,然后按一下功能键F4,使之转化为绝对引用方式。

5、再次将单元格 F2 一直向下拖动到单元格 F9 中,得到各个业务员的销售提成,如图 3 所示。

说明:本例中,被单元格F2复制到的单元格与之相比,只是行变化,而列不变,所以其公式中对单元格I1的引用也可用混合方式,即单元格F2的公式也可为“=E2*I$1”。

在单元格F2的公式输入完成后,要想将其复制到F3:F9单元格,除了上面介绍的先选中单元格,然后向下拖动柄的方法之外,还可以直接双击拖动柄 (条件是左侧的一列中间不能有空白单元格)。

【例2】如图4所示,B3:B6为某公司本月三种产品的合计产量以及各自产量,C3:E3是本月的水费、电费以及其他费用的金额。现在请将这三种费用按照平均分担法,分配到各种产品上,并填写到图4中对应的C4:E6以及F3:F6单元格区域。

问题分析:平均分担法,就是将各种费用按照各种产品的数量,平均分配给各种产品。以计算A产品消耗的水费为例,需要用总水费金额除以本月所有产品的合计总量,再乘以A产品的本月产量。在公式编写时,需要认真考虑单元格引用方式。操作步骤如下:

(1)在C4中输入公式“=C$3/$B$3*$B4",按Enter键确认后求出A产品消耗的水费。

(2)选定C4,向下拖动复制一直到单元格C6,即可计算所有产品所消耗的水费。

(3)选定C4:C6单元格区域,然后向右拖动复制,一直到E4:E6单元格区域,即可计算所有产品其余各项费用的金额。

(4)在单元格F3中输入公式“=SUM(C3:E3)”,按Enter键确认后计算出费用的合计。

(5)选定单元格F3,向下拖动复制公式一直到单元格F6,计算所有产品各项费用的合计。

以上操作完成后,最终的费用分配表如图5所示。

说明 :在C4的公式“=C$3/$B$3*$B4”中,充分应用了混合应用和绝对引用,其中:

(1)C$3采用了列不固定而行固定的混合引用形式,则再求A产品所消耗的电费时,只需把C改成D即可,即将C4复制到D4后,D4的公式将为“=D$3/$B$6*$B4”。

(2)$B$3采用了绝对引用形式,这是因为不管求何种费用,合计产量应是固定不变的。

(3)$B4采用了列固定而行不固定的混合引用形式,再求B产品的水费时,只需要把4改成5就可以了,即将C4复制到C5后,C5的公式将为“=C$3/$B$6*$B5”。

【例3】如图6所示,为存储在“计算机平时成绩”工作簿“平时成绩”工作表中的学生平时成绩。如图7、图8所示,分别为存储在“计算机考试成绩”工作簿“期中成绩”工作表和“期末成绩”工作表中的期中考试成绩和期末考试成绩,而期末考试成绩又包括期末笔试成绩和期末上机成绩两个部分,它们各自在期末成绩中所占的比例如图8所示。

问题要求:在“计算机考试成绩”工作簿的“学期成绩总评”工作表,即图9中,计算出每个学生的总评成绩。其中平时成绩、期中成绩和期末成绩在总评中所占的比例已经在图9中给出,而平时成绩就是平时记录10次作业成绩(10分制)的总和。

问题分析:本例的目的就是练习跨工作表和工作簿的单元格引用,操作步骤如下:

(1)将“计算机平时成绩”工作簿和“计算机考试成绩”工作簿全部打开。

(2)将光标定位到“计算机考试成绩”工作簿的“学期成绩总评”工作表中的C2处,如图9所示,准备计算第一个学生的平时成绩。

(3)在编辑栏中输入“=SUM()",然后将光标放置在括号中,用鼠标去选取“计算机平时成绩”工作簿中“平时成绩”工作表的C2:L2单元格区域,之后第一个学生的平时成绩求出,单击C2可以看到其中的公式为:“=SUM([计算机平时成绩.xls]平时成绩!$C$2:$L$2)”。

(4)将上面步骤(3)公式中的单元格引用 ,通过按F4键的方法切换成相对引用方式,然后向下拖动复制,使区域C2:C16得到每一个学生的平时成绩。

(5)将光标置于D2中,输入公式“= 期中成绩!C3”(可通过鼠标点击选取),获得第一个学生的期中成绩,然后向下拖动复制,使区域D2:D16得到每个学生的期中成绩。

(6)将光标置于E2处,然后通过鼠标点击选取的方式输入公式“= 期末成绩!C2* 期末成绩!$G$2+ 期末成绩!D2* 期末成绩!$H$2”,得到第一个学生的期末成绩,然后向下拖动复制,使区域E2:E16得到每个学生的期末成绩。

(7) 将光标置于单 元格F2处 , 输入公式 “=C2* $I$2+D2*$J$2+E2*$K$2”,得到第一个学生的总评成绩,然后向下拖动复制,使区域F2:F16得到每个学生的总评成绩。

说明:上面两个步骤中,为了拖动复制的方便,对成绩所在单元格(C2和D2)使用了相对引用,而对它们各自所占比例的数值的单元格(如G2. H2等)则设置了绝对引用。