Excel 第四课 数据统计计算

授课教师：谢老师数据统计计算数据统计在工作表中输入数据后，往往需对数据进行计算处理，Excel则提供强大的数据计算功能即数据统计计算，可以对Excel工作表中数据进行精确、高速的计算、分析和处理，从而极大地提高工作效率。它包括了简单运算、公式与函数√简单运算是利用Excel提供的工具栏中“”按钮快速完成计算√公式是工作表中对数据进行分析和运算的等式，它是工作表的数据计算中不可缺少的部分。√函数是Excel提供的一些特殊的内置公式，可以进行数学运算或逻辑运算等。与直接使用公式进行计算相比较，使用函数进行计算的速度更快，并可减少错误的发生本讲作为Excel的核心部分，对Excel的数据计算与分析，具有重要作用，特别是公式与函数。简单运算利用Excel2003提供的工具按钮快速完成，对某个单元格区域内的数字进行求和、求平均数、计数、求最大值或最小值运算。√根据需要，选中用于运算的单元格区域（放置运算结果的单元格）√单击【常用】工具栏中的【自动求和】按钮右侧的下拉箭头，在下拉列表框中选择【求和】、【平均值】、【计数】、【最大值】或【最小值】选项。数据统计计算简单运算数据的简单运算求和：选择要求和的数据，并多选一格用于显示结果，然后单击工具栏上的自动求和按钮。求平均值、计数等操作如此类推。自动计算1)求和：选取求和区域→状态栏显示和2）其他计算：右击状态栏→选择计算式→显示结果注：自动计算结果显示在状态栏，自动求和结果显示在单元格中简单运算数据统计计算简单运算公式简介：公式可以用来执行Excel各种计算，使用公式可以方便而准确地分析工作表中的数据。如用户向工作表中输入计算的数值时，就可以用公式。公式是工作表中的数值进行计算的等式，由运算符、单元格引用位置、数值、工作表函数等组成。通过公式，用户可以对工作表中的数据进行较为复杂的运算数据统计计算公式运算符=（B4+25）/SUM（D5:F5）公式符区域范围引用常量函数单元格地址公式结构数据统计计算公式算术运算符^*/+-%说明乘方乘除加减百分号比较运算符===说明大于大于等于小于小于等于等于不等于引用运算符说明：（冒号）对两个引用之间，包括两个引用在内的所有单元格（区域）进行引用，如B2:B8、A2:E10，（逗号）将多个引用合并成一个引用，如SUM（B1:B10,A1:A10,C11）文本运算符说明连字符&将一个或多个数据以文本形式连接起来形成一个新文本。如公式“=”计算机“&”基础“”，结果为“计算机基础”。Excel公式中的运算符数据统计计算公式注意（很重要）：空格--交叉运算符，如：SUM(B5:B15A7:D7)两区域交叉单元格之和，即B7在Excel中,每个运算符都有一定的优先级.优先级从高到低分别为:冒号、单个空格、逗号、负号、百分号、插入符号、星号和正斜杠、加号和减号、和号、比较运算符。Excel中的运算符单元格或区域的表示方法√单元格区域的表示法：独立地址：各地址用（，）隔开。例如，=SUM（A1，A8），表示A1和A8两单元格的和。连续地址：首尾地址用（：）连接。例如，=SUM（A1：A8），冒号表示计算范围从A1到A8单元格之间区域。混合地址：例如，=SUM（A1：A6，A8），表示计算A1到A6再加A8的和。√单元格区域的表示法：1）直接输入2）鼠标选中（1）公式：以等号（=）开始，由连续的一组数字和运算符组成的序列表达式。即：公式形式：=操作数和运算符公式是对数据进行分析与计算的等式，它可以对工作表中的数值进行从简单的四则混合运算直到复杂的财务计算、统计分析。选定单元格→［在编辑栏（或单元格）中］输入等号→输入公式→回车（2）输入公式并计算公式输入准则：＝124.5+118.4+115.2+128.7＝B3+C3+D3+E3＝SUM(B3,C3,D3,E3)＝SUM(B3:E3)＝SUM(B3:E3)数据统计计算公式*若更改求值顺序，请将公式中要先计算的部分用括号括起来。单元格、数字、字符、区域名、区域、函数图输入公式例题公式的操作数据统计计算公式公式中显示和修改默认情况下，编辑栏显示公式，单元格显示公式计算的结果。故一般情况下，在编辑栏中修改公式。公式的复制√用填充法复制公式：先单击已有公式的单元格，然后拖填充柄至最后一个单元格√用“复制/粘贴”法复制公式，如果公式不连续，此法较好。公式的操作对区域可以进行文字性命名，以便于记忆与阅读√命名：选取区域/单击名称框/输入区域名√区域名的引用：在公式或函数的参数中直接引用区域名。访问其他工作表中的数据：工作表名！单元格地址删除公式选择含有公式的单元格，按【Delete】键即可将公式和计算结果全部删除公式公式公式使用示例公式插入时的出错提示错误值的涵义#####！输入或计算结果的数值太长，单元格容纳不下。#VALUE！使用了错误的参数，或运算对象的类型不对。#DIV/0！公式中除数为0，或引用了空单元格，或引用了包含0值的单元格。#NAME?公式中使用了不能识别的单元格名称。#N/A公式或函数中没有可用的数值。#REF！单元格引用无效。#NUM！公式或函数中某一数字有问题。#NULL！对两个不相交的单元格区域引用使用了交叉引用运算符（空格）。函数函数是Excel已定义好的具有特定功能的内置公式，执行计算、分析等处理数据任务数据统计计算函数例:求平均=（B2+B3+B4+B5+B6+B7+B8+B9+B10）/9=Average（B2:B10）为了适应各种计算要求，在Excel中函数共分为十类：常用、财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑和信息函数或按“shift+F3”直接打开321函数的查看√财务函数：可数值进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值等。√日期和时间：在公式中分析和处理日期值，如TODAY（）返回系统当前日期。√查询和引用：查询特定数值，或者查找某一单元格的引用。√数据库：分析数据库中的数和时间值，如数字取整、求和、按给定条件求和等。√数学和三角：处理简单或复杂的数学计算，如直线的斜率和y轴截距等。√.统计：对数据区域进行统计，如计算所有数值大于100且小于250的记录总数。√文本：在公式中处理文字串，如提取文字串中的字符等。√逻辑：进行真假值判断，或复合检验。如IF（）函数可确定条件的真假并返回相应的数值。√信息：可以测试/确认存储在单元格中的数据类型。函数的分类工程函数：对数值进行各种工程上的运算和分析Θ外部函数：使用加载项提供自定义命令或自定义功能的补充程序Excel的任何函数都有一样的标准结构，即：都是由函数名和参数两部分组成。将函数作为另一个函数的参数也称为嵌套函数。不同类型的函数要求给定不同类型的参数函数准则：函数名（参数1，参数2，…）代表函数功能函数输入准则参数：数字、数组、文本、常量、单元格、区域、区域名、公式或其他函数√函数和公式不同，公式是在工作表中队数据进行分析的等式，它可以引用一个工作表的多个单元格、同一工作薄不同工作表的单元格以及其他工作薄中的工作表；而函数是预定义的内置公式，它使用被称为参数的特定数值，并按特定顺序计算√函数是一种特殊的公式，因此所有的函数都要以“=”开使，其次是函数名称，然后是括号，括号表示其中括住的内容是此函数必需的参数。如果函数对应的参数不只一个时，就必须按照规定的顺序依次排列各参数，并且用逗号把它们隔离开来√手工输入：选定单元格→“=”→输入函数→确定√粘贴函数（“函数向导”）：首先选定要输入函数的单元格1）单击工具栏上的“自动求和”按钮中的“其他函数”→选择类别→选择函数→输入参数→确定2）“插入/函数”→选择类别→选择函数→输入参数→确定3）单击“插入函数”钮→选择类别→选择函数→输入参数→确定输入函数方法：直接输入法、向导法常用函数数学函数：（1）SUM：求和函数●格式：=SUM（参数1,参数2,……）功能：求各参数的和，最多30个参数如：√注意：如果参数中有空白单元格、逻辑值、文本，则忽略。（2）SUMIF：计算对满足单元格条件区域数值求和●格式：=SUMIF（参数……条件……参数……）=SUMIF（参数……条件……如=SUMIF(E3:E27,=90)A求：语文成绩大于等于70的和方法：=SUMIF(E4:E8,=70)B求：语文成绩大于等于70的求总分的和方法：=SUMIF(D20:D27,“=70,E20:E27)=SUM(E4:E8，H8:H10，A2:A10)√参数说明：第一个参数为用于条件判断的单元格区域；条件确定哪些单元格将被相加求和，其形式可以为数字、表达式或文本。例如，条件可以表示为32、“男”、“32”；后面的参数是需要求和的数值所在的单元格区域，如果省略了后面参数，则对第一个参数区域中的单元格求和。应用举例：输入公式：=SUMIF(A2:A14,计算机,G2:G14)，表示在A2：A14区域中找出值为“计算机”的单元格区域，将它们的G列数据求和。（3）AVERAGE（）：平均值函数●格式：=AVERAGE（参数1,参数2,……）功能：求个参数的平均值如=AVERAGE(E12:I12)√如果引用区域中包含“0”值单元格，则计算在内；如果引用区域中包含空白或字符单元格，则不计算在内（4）ROUND（）：四舍五入函数●格式：ROUND（数值，位数）功能：对数值项进行四舍五入。√若位数0，表示保留位数指定的小数；位数=0，表示保留整数；位数0，表示从个位向左对第几位进行舍入如：ROUND（123.528，2）结果123.52ROUND（123.528，0）124ROUND(123.528，-2）100（5）INT（）：取整函数●格式：INT（A1）功能：取不大于参数A1的最大整数如：INT（12.45）=12INT（-12.45）=-13常用函数数学函数：（1）求最大值/最小值函数MAX/MIN（）●格式：=MAX/MIN（参数1,参数2,……）功能：求各参数中的最大/小的值√注意：如果参数中有文本或逻辑值，则忽略。（2）计数函数：COUNT/COUNTA（）●格式：=COUNT/COUNTA（参数1,参数2,……）功能：COUNT求各参数中数值型参数和包含数值的单元格个数COUNTA求各参数中包含所有字符的单元格个数如：=COUNT（12，D1:D3，‘CHINA’）若D1:D3中存放的全是数值，则函数的结果是4，若D1:D3中只有一个存放的是数值，则结果为2（3）COUNTIF（）：条件计数函数●格式：=COUNTIF（取值范围，条件式）功能：计算某区域内满足条件的单元格个数如：=COUNTIF(B1:B13,“60”)，确认后，即可统计出B1至B13单元格区域中，数值小于60的单元格数目。=COUNTIF(D3:B27,“男”)√注意：允许引用的单元格区域中有空白单元格出现。常用函数统计函数：（1）条件判断函数：IF●格式：=IF（条件式，值1，值2）功能：若满足条件，则结果取值1，否则，取值2=IF(条件式,值1,值2)条件式条件为假时结果值条件为真时判断条件是否满足，满足则返回值1，不满足则返回值2常用函数逻辑函数：如：=if（A2=100，“good“，”bad“）逻辑函数And同时满足两个条件才能成为真值，否则为假值=AND(F3G3,G6H6)Or只需满足一个条件/成为真值，否则为假值=OR(F3G3,G6H6)常用函数费用：=IF(H350,催,IF(H35=0,清))或=IF(H350,催,清))全勤奖：=IF(OR(L90,M90),0,100)等级：=IF(J6=400,优秀,IF(AND(J6=350,J6400),中等,差))或=IF(J6=400,优秀,IF(AND(J6=350,J6400),中等,if(J3350,差)))常用函数使用：时间函数计算机当前内部的日期和时间now()---括号无参数=(NOW()-E46)/365=（当前日期-进厂日期）/365工龄