EXCEL 公式和统计函数
Excel具有强大的计算功能。它不但能利用公式进行简单的代数运算,而 且能分析复杂的数学模型。它的数学、统计、财务等10类300多种函数 (预定 义的内装公式),可以直接用于计算。它可以使用数组公式同时进行多重计算, 并得 出一个或多个结果。本章根据统计计算的要求,重点讲述在Excel中如何 使用公式和函数。
公式是在工作表中对数据进行分析计算的等式。公式的计算范围不仅限于 本工作表的数值,还可以包括同一工作簿中不同工作表的数值,乃至其他工作簿 中工作表的数值。公式的内容包括运算项(元素)和运算符。运算项可以是数值, 也可以是单元格或单元格区域的引用,或单元格的标志和名称,还可以是工作表 函数。这些运算项的具体内容将在后面分别详述,本节先对公式的运算符、运算 顺序、输入公式、编辑公式等作一简介。
一、公式的运算符
公式的运算符有以下四种类型。(一)算术运算符:包括+、-、*(乘)、 /(除)、%、 ^(乘方)。遇有负数可在前面加-号,或用括号表示。%和前面的 数字连在一起作为一个数值处理,例如5%,系统将自动转换为0.05计算。
(二)比较运算符:包括;、>、<、>=(大于或等于)、<=(小于或等于)、 <>(不等于)。
统计员常用的excel公式比较运算符可用于比较两个数值,其运算结果产生逻辑值TRUE或FALSE.。 例如,对公式:A2>100进行运算,结果如出现TRUE,表示公式成立,A2真的大 于100;如出现FALSE,则表示公式不能成立,A2不大于100。
(三)文字运算符:连字符&。Excel 不仅能进行数值运算,而且能进行 文字值(文字型数据)的运算。利用连字符&,可以把一个或多个文字值连接起来,
,即把“一季度” 求得一个连续的文字值。例如:公式:
“一季度”&“销售额”
和“销售额”两个文字值连接起来,成为“一季度销售额” 。如果一季度所在的 单元格为 A3,还可以将公式改为;A3&“销售额” ,即把单元格和文字值连接起 来。但要注意:文字值必须加双引号,以便识别。
(四)引用运算符:在公式中如要引用单元格或单元格区域的地址(行列 号)进行运算,必须加引用运算符。引用运算符有三种。一是联合运算符,以逗
号(,)表示。例如:公式:SUM(B3,B4,B5,B6,B7),表明要将B3、B4、B5、 B6、B7五个单元格的数值相加求得合计。式中:SUM是求和函数,括号内是函数 的参数,五个单元格之间要用逗号隔开。二是区域运算符,以冒号(:)表示。例 如:、公式:SUM(B3:B7),表明要把B3 至B7单元空格表示。
例如:公式:SUM(B3: B5 A4:C4),表明竖向 B3:B5 和横向 A4:C4 两个交叉区域的数值相加求和,中 间要用空格表示交叉,交叉处的单元格 B4 同时从属于两个区域的引用。交叉运 算符使用较少。
二、公式的运算顺序
工作表中的公式是按照一定的顺序进行计算的,这种顺序可称之为语 法。按照语法的要求,公式必须从:号开始,后面是参加计算的运算项和运算符。 在各类运算符中,首先运行引用运算符,其次运行算术运算符,再其次运行文字 运算符和比较运算符。其中,算术运算符的计算顺序是:负数-、%、 ^、*和/、 +和-。例如=5+2*3,首先计算2*3=6,然后计算5+6=11。如要提前计算5+2,需 用括号将其括起来,公式改为:(5+2)*3,计算结果为 21。同一级的运算符如* 和/,按从左到右的顺序进行计算:如有需要提前计算的部分,也要用括号括起 来。
三、公式中数值的转换
在公式中,各种运算符都要求有一定类型的数据与之相适应。如果输入 的数据类型不符合要求,有些将自动转换为可用的类型,有些则不能计算。举例 如下:
[例]公式:
“1”+“2” 。式中使用+号,其运算项应是数字;现在1和2 带有双引号,表明是文字型数据。但系统能自动
将其转换为数字进行计算,计算 结果为3。
[例]公式=10-“$4.00”
。式中$4.00是文字型数据;但系统不考虑其货 币符号,转换为数字计算,计算结果为6。
。式中是两个表示日期的数值,要求 [例]公式=“98-7-1”-“98-5-25”
计算二者之间相差的天数,系统可将其转换为内置的序列数然后计算,计算结果 为37天。
[例]公式=SQRT(“8+1”)。式中$QRT 是求平方根函数,括号内的参数
“8+1”格式不对,不能转换为数字,屏幕显示出错信息#VALUE!。如果把参数改
,则可转换为数字计算,求得结果为3。
为“9”或“8”+“1”
四、输入公式
比较简单的公式,可直接在单元格中输入。首先单击待输入公式的单元格, 然后输入:号和公式的内容
(窗口上方编辑栏中同时显示公式)。输入完毕回车确 认,计算结果即自动记入该单元格内,编辑栏中的公式消失。如要再查看公式内 容,可双击该单元格,公式即重复出现:再回车,又返回计算结果。
比较复杂的公式,最好在编辑栏中输入。首先也要选定待输入公式的单 元格,然后单 击编辑栏中的“编辑公式”按钮(:),其右侧框中即出现一个: 号(同时在其左侧出现“输入”按钮√和“取消”按钮 X;还在编辑栏下方弹出 计算结果显示框,框的右端并列“确定”和“取消”两个按钮),用户可在:后 面输入公式,输入终了,计算结果即显示在编辑栏下的显示框中。再回车确认, 或按“确定”按钮、或按“输入”按钮,均可将计算结果记入选定的单元格内。
在公式中如需使用函数,可单击编辑栏左端框旁的向下箭头,从弹出的常用 函数列表中选定所需要的函数,即出现该函数的对话框(帮助信息称“公式选项 板”)。在对话框上部参数框中输入必要的参数,对话框底部即显示出计算结果。 再回车确认,计算结果即记入选定的单元格内。如果常用函数列表中没有所需要 的函数,可单击“其他函数”项,屏幕弹出“粘贴函数”对话框,再从中选择所 需要的函数。
输入数组公式与输入单值公式方法基本相同。 首先单击待输入公式的单 元格,如要求给出多个结果需单击待输入公式的单元格区域;然后输入公式,系 统将自动为公式加上大括号<);最后按 Ctrl+Shift+Enter 组合键结束操作,计 算结果即显示在选定的单元格内。
五、编辑公式
如要修改公式,需先单击包含待修改公式的单元格,.然后在编辑栏中进行 修改,修改后回车确认。公式中如果引用了单元格行列号,将会自动按修改后的 行列号重新计算,得出新的结果。如要修改公式中的函数,必须同时修改函数的 参数。修改数组公式,可单击编辑栏,大括号自动消失;修改后按 Ctrl+Shift+Enter组合键结束操作。
如要移动公式,也需先单击包含待移动公式的单元格,然后将鼠标指针指向 选定区域的边框,按住鼠标左键将其拖至目标区域左上角的单元格,放开鼠标, 即替换了目标区域的全部数据。复制公式与移动公式操作基本相同,只在拖动选 定区域时需按住Ctrl键。应当指出,移动或复制公式,需从“工具”菜单的“选 项”中选择“视图”卡,在“窗口选项”下单击“公式”选项。还需指出,移动 公式单元格引用不改变;复制公式,单元格绝对引用
也不改变,但单元格相对引用将会改变。关于绝对引用和相对引用的含义下 节详述。
删除公式也要先单击包含待删除公式的单元格, 然后按Delete键即可删除。 如要删除
数组公式, 需先单击数组区域中任一单元格, 然后在 “编辑” 菜单中选择 “定 位” 命令,
按下“定位条件”按钮,从其对话框中选择“当前数组”项,再按 Delete 键删除。
六、公式的出错提示
如果公式不能正确算出结果,系统将会显示一个错误值。有时公式引用韵 单元格有错误,
也会使公式产生错误。现将各项错误值的涵义和常见的错误简述如下。
(一)错误值的涵义
·>! 输入或计算结果的数值太长,单元格容纳不下。
·#VALUE! 使用了错误的参数,或运算对象的类型不对。
·#DIV/0! 公式中除数为0,或引用了空单元格或包含0值的单元格。
· #NAME? 公式中使用了不能识别的单元格名称。
·#N/A 公式或函数中没有可用的数值。 .
·#REF! 单元格引用无效。
· #NUM! 公式或函数中某一数字有问题。
· #NULL! 对两个不相交的单元格区域引用使用了交叉引用运算符(空 格)。 .
-
如要了解各项错误值产生的原因和处理方法,可单击“帮助”菜单中的“创
,从中选择“公式与错误值疑难解答”
。
建公式和审核工作表”
(二)常见错误 ,
公式可能出现的错误很多,常见的主要有以下几项。
1.圆括号()未成对出现,二者缺一。
2.引用单元格区域使用了不正确的运算符,应使用冒号(:) 。
3.缺少必选的参数,或输入了多余的参数。
4.在函数中使用嵌套函数不符合要求。
5.引用工作簿或工作表的名称中含有非字母字符,但未加单引号。
6.外部引用缺少工作簿名称和路径。
7.公式中输入数字不应加格式,如$1,000,应输入 1000,不应带货币符 号和千分位点。
公式中单元格的引用
一、引用的作用和常设形式:
公式中“引用”的作用在于标出工作表上参加计算的单元格或单元格区 域, 指明公式计算用数据的位置。 一个公式可以引用工作表上不同单元格的数据, 多个公式也可以引用同 一单元格数据; 还可以引用同一工作簿中不同i作表 的数据,或是不同工作簿中工作表的数据,乃至其他应用程序的数据。引用其他 工作簿的数据叫做外部引用,引用其他应用程序的数据叫做远程引用。 引用有 A1 和 R1C1 两种表现形式,常设为 A1 形式(默认形式)。这种形式用英文字母表 示列,用数字表示行。例如:
要引用A列 5行交叉处的单元格,可输入 A5。
要引用A列 5行至10行的单元格区域,可输入A5:A10(中间的冒号表 示起止范围)。
要引用A列 5行至 E列 5行的单元格区域,可输入A5:E5。
要引用5行各列所有的单元格,可输入5:5。
要引用5行至10行各列所有的单元格,可输入5:10。
要引用C列各行所有的单元格,可输入C:C。
要引用C列至 E列各行所有的单元格,可输入C:E。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
统计员岗位需要掌握哪些EXCEL技能?
« 上一篇
excel表格业绩统计公式
下一篇 »
发表评论