项目三 公式与函数电子课件 高教版 中职 计算机应用基础Windows 7+Office2010.pptx

项目三 公式与函数电子课件 高教版 中职 计算机应用基础Windows 7+Office2010项目三 公式与函数【项目指引】【项目指引】用 Excel 制作的表格往往需要有大量的数据需要计算和统计，如求工资的总和、求平均工资、求学生考试成绩的最高分、最低分等。Excel2010 具有非常强的计算和统计功能，从简单的四则运算，到复杂的财务计算、统计分析，都能轻松解决。张老师在完成了学生信息和成绩的录入后，需要进行统计分析，比如由学生的身份证号，统计学生的年龄，统计各学生总分、平均分、各科目最高分、最低分等。因此张老师需要班长来协助处理这些工作，班长怎么才能又快有好多的完成班主任交给的任务呢？知识目标知识目标要理解单元格地址的引用，理解常用函数的功能、用法。技能目标技能目标掌握如何插入公式、函数，如何复制公式、函数，如何通过修改单元格地址引用方式来使运算结果正确、有效。任务一任务一自动计算与常用自动计算与常用统计函数统计函数Excel为初学者提供了非常方便的自动计算功能，在自动计算包含了求和、平均值、计数、最大值、最小值等常用统计。引用引用单元格引用在公式和函数中非常重要，引用的形式形如“A1”，代表单元格的地址，A列与第1行的交叉处单元格。其中的A是列标，1是行号。如果是引用一个区域，可以使用引用运算符“:（冒号）”来表示，形如“A1:B3”，其中A1指的是区域最左上角单元格地址，B3指的是最右下角的单元格地址，A1:B3就是A1到B3这样一个矩形区域。相对引用引用格式形如“A1”。这种对单元格的引用是完全相对的，当引用单元格的公式被复制时，新公式引用的单元格的位置将会发生改变。混合引用引用格式形如“$A1”或者“A$1”，前面加上一个$符号，表示绝对引用，不加表示相对引用。如，“$A1”表示列绝对引用，而行相对引用，在复制公式时，新公式引用的列不变，而行会根据实际情况而改变。绝对引用引用格式形如“$A$1”，表示行列均使用绝对引用，在复制公式时，不论怎么复制，引用的单元格地址都不会改变。在编辑引用地址时，可以通过按下“F4”功能键，快速切换绝对引用和相对引用。公式和函数简介公式和函数简介公式公式是一个等式，是以“=”号开始，数据和运算符组成的表达式。运算符包括：加号、减号、乘号、除号、幂、括号等。Excel公式可以包括数据、运算符、单元格引用和函数，但是不能包括空格。运算符的优先级顺序序运算符运算符说明明1:（冒号）（空格）,（逗号）引用运算符2-负号3%百分比4乘方5*、/乘、除6+、-加、减7&文本运算符8=、=、比较运算符函数Excel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。函数是Excel2010 中已经定义好的计算公式，函数使用称为参数的特定数值按特定的顺序或结构进行计算。函数同样必须以“=”号开始，函数的结构一般形如：“=函数名称（参数1,参数2）”，函数名称表明函数的功能，函数的参数指的是参与运算的数值、引用、条件等。有些函数没有参数，但是也必须有括号，比如函数PI的作用是返回圆周率的值，在使用时也必须输入“=PI()”。公式和函数的输入公式和函数的输入选定需要输入公式的单元格，直接输入公式或者在编辑，然后回车。例如：在单元格B1中计算A1+100，则选定B1单元格，输入公式“=A1+100”，按回车键。函数的输入直接输入函数在函数比较熟悉的情况下，可以直接选择需要输入函数的单元格，直接输入函数以及参数或者在编辑栏内输入函数和参数，然后直接回车。例如：在A1单元格内输入“=SUM（1,2,3）”，然后回车确认。指的是将括号中的三个数求和，结果放在A1中。又例如：在D1中输入“=SUM（A1:C1）”，然后回车确认。是将A1:C1单元格区域中的数值求和，结果放在D1中。通过功能区插入函数在Excel 2010中的“公式”功能区，可以看到Excel 2010提供的所有函数，如图所示。单击任意一个函数类型，就打开该类型的所有函数，将鼠标放置在某函数上，可出现该函数的相关说明，单击鼠标左键，即选择了该函数。选中需要输入函数的单元格，单击“插入函数”按钮常用统计函数常用统计函数SUM用途：返回某一单元格区域中所有数字之和。语法：SUM（number1，number2，）。参数：number1、number2为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。AVERAGE用途：计算所有参数的算术平均值。语法：AVERAGE（number1，number2，）。参数：number1、number2是需要计算平均值的130个参数。注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。COUNT用途：返回数字参数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。语法：COUNT（value1，value2，）。参数：value1，value2是包含或引用各类数据的130个参数。注意：COUNT函数计数时数字、日期或文本表示的数字会参与计数，错误值或其他无法转换成数字的文字被忽略。如果参数是一个数组或引用，那么只有数组或引用中的数字参与计数；其中的空白单元格、逻辑值、文字或错误值均被忽略。最大值函数MAX用途：返回数据集中的最大数值。语法：MAX（number1，number2，）。参数：number1，number2是需要找出最大值（最小值）的130个数值、数组或引用。注意：函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。如果参数为数组或引用，则只有数组或引用中的数字参与计算，空白单元格、逻辑值或文本则被忽略。最小值函数MIN用途：返回给定参数表中的最小值。语法：MIN（number1，number2，）。参数：number1，number2是需要找出最大值（最小值）的130个数值、数组或引用。选中单元格，单击“开始”选项卡“编辑”命令组“自动求和”按钮右侧的下拉菜单按钮，在菜单中选择“平均值”项。因为自动计算会自动扩展右侧或者上方有数据的区域，因此H3中将出现函数“=AVERAGE(D3:G3)”，且单元格引用“D3:G3”处于选中、反黑状态，此时如果需要计算的单元格区域是正确的，直接回车即可，如果不正确，直接用鼠标选正确区域，然后回车即可。打开ex3-1-1.xlsx，完成如下操作，以原文件名保存。打开sheet1工作表，完成所需统计。统计每位同学的平均分、总分。统计各个科目的最高分、最低分、平均分、总分。统计班级人数。打开Sheet2工作表，完成计算。计算调薪后的工资，计算公式为：“调薪后工资=现工资+现工资*增长幅度”。打开Sheet3工作表，完成统计。计算各部门销售额所占比例，公式为：“各部门销售额所占比例=各部门销售额/合计”。任务二其它常用函数任务一中介绍了常用统计函数，常用统计函数可以完成日常的一些统计工作，但是这只是Excel强大统计和数据处理功能的冰山一角，为了能够完成更加复杂的统计工作，需要学习掌握其他更多的函数，比如字符函数、日期函数、逻辑函数等。数学数学函数函数ABS用途：返回某一参数的绝对值。语法：ABS(number)参数：number是需要计算其绝对值的一个实数。实例：如果A1=-16，则公式“=ABS(A1)”返回16。INT用途：将任意实数向下取整为最接近的整数。语法：INT(number)参数：Number为需要处理的任意一个实数。实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。MOD用途：返回两数相除的余数，其结果的正负号与除数相同。语法：MOD(number，divisor)参数：Number为被除数，Divisor为除数(divisor不能为零)。实例：如果A1=51，则公式“=MOD(A1，4)”返回3；=MOD(-101，-2)返回1。数学函数数学函数PI用途：返回圆周率，精确到小数点后14位。语法：PI()参数：不需要实例：公式“=PI()”返回3.14159265358979。RAND用途：返回一个大于等于0小于1的随机数，每次计算工作表(按F9键)将返回一个新的数值。语法：RAND()参数：不需要注意：如果要生成a，b之间的随机实数，可以使用公式“=RAND()*(b-a)+a”。如果在某一单元格内应用公式“=RAND()”，然后在编辑状态下按住F9键，将会产生一个变化的随机数。实例：公式“=RAND()*1000”返回一个大于等于0、小于1000的随机数。数学函数数学函数ROUND用途：按指定位数四舍五入某个数字。语法：ROUND(number，num_digits)参数：Number是需要四舍五入的数字；Num_digits为指定的位数，Number按此位数进行处理。注意：如果num_digits大于0，则四舍五入到指定的小数位；如果num_digits等于0，则四舍五入到最接近的整数；如果num_digits小于0，则在小数点左侧按指定位数四舍五入。实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3；“=ROUND(82.149，2)”返回82.15；“=ROUND(21.5，-1)”返回20。SQRT用途：返回某一正数的算术平方根。语法：SQRT(number)参数：Number为需要求平方根的一个正数。实例：如果A1=81，则公式“=SQRT(A1)”返回9；“=SQRT(4+12)”返回6。字符函数字符函数LEN或LENB用途：LEN返回文本串的字符数。LENB返回文本串中所有字符的字节数。语法：LEN(text)或LENB(text)。参数：Text待要查找其长度的文本。注意：此函数用于双字节字符，且空格也将作为字符进行统计。实例：如果A1=电脑爱好者，则公式“=LEN(A1)”返回5，=LENB(A1)返回10。LEFT或LEFTB用途：根据指定的字符数返回文本串中的第一个或前几个字符。此函数用于双字节字符。语法：LEFT(text，num_chars)或LEFTB(text，num_bytes)。参数：Text是包含要提取字符的文本串；Num_chars指定函数要提取的字符数，它必须大于或等于0。Num_bytes按字节数指定由LEFTB提取的字符数。实例：如果A1=电脑爱好者，则“=LEFT(A1，2)”返回“电脑”，“=LEFTB(A1，2)”返回“电”。RIGHT或RIGHTB用途：RIGHT根据所指定的字符数返回文本串中最后一个或多个字符。RIGHTB根据所指定的字节数返回文本串中最后一个或多个字符。语法：RIGHT(text，num_chars)，RIGHTB(text，num_bytes)。参数：Text是包含要提取字符的文本串；Num_chars指定希望RIGHT提取的字符数，它必须大于或等于0。如果num_chars大于文本长度，则RIGHT返回所有文本。如果忽略num_chars，则假定其为1。Num_bytes指定欲提取字符的字节数。实例：如果A1=学习的革命，则公式“=RIGHT(A1，2)”返回“革命”，“=RIGHTB(A1，2)”返回“命”。MID或MIDB用途：MID返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB返回文本串中从指定位置开始的特定数目的字符，该数目由用户指定。MIDB函数可以用于双字节字符。语法：MID(text，start_num，num_chars)或MIDB(text，start_num，num_bytes)。参数：Text是包含要提取字符的文本串。start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推；num_chars指定希望MID从文本中返回字符的个数；Num_bytes指定希望MIDB从文本中按字节返回字符的个数。实例：如果a1=电子计算机，则公式“=MID(A1，3，2)”返回“计算”，“=MIDB(A1，3，2)”返回“子”。日期与时间函数日期与时间函数DATE用途：返回代表特定日期的序列号。语法：DATE(year，month，day)参数：year为一到四位，根据使用的日期系统解释该参数。默认情况下，Excel for Windows使用1900日期系统，而Excel for Macintosh使用1904日期系统。Month代表每年中月份的数字。如果所输入的月份大于12，将从指定年份的一月份执行加法运算。Day代表在该月份中第几天的数字。如果day大于该月份的最大天数时，将从指定月份的第一天开始往上累加。注意：Excel按顺序的序列号保存日期，这样就可以对其进行计算。如果工作簿使用的是1900日期系统，则Excel会将1900年1月1日保存为序列号1。YEAR用途：返回某日期的年份。其结果为1900到9999之间的一个整数。语法：YEAR(serial_number)参数：serial_number是一个日期值，其中包含要查找的年份。日期有多种输入方式：带引号的文本串(例如1998/01/30)、序列号(例如，如果使用 1900 日期系统则 35825 表示 1998 年 1 月 30 日)。实例：公式“=YEAR(2013/8/6)返回2013”，=YEAR(2015/05/01)返回2015，=YEAR(35825)返回1998。MONTH用途：返回以序列号表示的日期中的月份，它是介于 1(一月)和12(十二月)之间的整数。语法：MONTH(serial_number)参数：serial_number表示一个日期值，其中包含着要查找的月份。实例：公式“=MONTH(2001/02/24)”返回2，=MONTH(35825)返回1。DAY用途：返回用序列号(整数1到31)表示的某日期的天数，用整数 1 到 31 表示。语法：DAY(serial_number)参数：serial_number是要查找的天数日期。实例：公式“=DAY(2001/1/27)”返回27，=DAY(35825)返回30。TODAY用途：返回系统当前日期的序列号。参数：无语法：TODAY()实例：公式“=TODAY()”返回执行公式时的系统时间。NOW用途：返回当前日期和时间所对应的序列号。语法：NOW()参数：无实例：如果正在使用的是1900日期系统，而且计算机的内部时钟为 2015-2-14 13:14，则公式“=NOW()”返回42049.55。逻辑运算逻辑运算AND用途：所有参数的逻辑值为真时返回TRUE(真)；只要有一个参数的逻辑值为假，则返回FALSE(假)。语法：AND(logical1，logical2，)。参数：logical1，logical2，为待检验的130个逻辑表达式，它们的结论或为TRUE(真)或为FALSE(假)。参数必须是逻辑值或者包含逻辑值的数组或引用，如果数组或引用内含有文字或空白单元格，则忽略它的值。如果指定的单元格区域内包括非逻辑值，AND将返回错误值#value!。OR用途：所有参数中的任意一个逻辑值为真时即返回TRUE(真)。语法：OR(logical1，logical2，.)参数：与AND相同。NOT用途：求出一个逻辑值或逻辑表达式的相反值。如果您要确保一个逻辑值等于其相反值，就应该使用NOT函数。语法：NOT(logical)参数：logical是一个可以得出TRUE或FALSE结论的逻辑值或逻辑表达式。如果逻辑值或表达式的结果为FALSE，则NOT函数返回TRUE；如果逻辑值或表达式的结果为TRUE，那么NOT函数返回的结果为FALSE。打开ex3-2-1.xlsx，完成如下操作，以原文件名保存。打开sheet1工作表，完成所需统计。对A4中的数字，分别完成“保留两位小数”、“取绝对值”、“取整数”。对A6中的字符串，分别完成“求字符串长度”、“取左边五个字符”、“取右边两个字符”、“从中间第6个字符开始，取2个字符”。根据A8中的身份证号码，分别完成求“出生日期”、使用函数获得“当前日期”、“计算年龄”、根据身份证号码的第17位判断“性别”（双数为女，单数为男）。对A10，A11中的逻辑值计算“A10 AND A11”、“A10 OR A11”、“NOT A10”的结果。打开Sheet2表，完成计算。计算综合评定，公式为“综合评定=操行分*0.3+学业分*0.7”。根据综合评定来判断综合等级，综合等级在85100（含80）分以上为“优秀”等次，60（含60）85分为“合格”等次，60分以下为“不合格”。统计“不及格人数”和“分数90的人数”。IF用途：执行逻辑判断，它可以根据逻辑表达式的真假，返回不同的结果，从而执行数值或公式的条件检测任务。语法：IF(logical_test，value_if_true，value_if_false)。参数：logical_test计算结果为TRUE或FALSE的任何数值或表达式；value_if_true是logical_test为TRUE时函数的返回值，如果logical_test为TRUE并且省略了value_if_true，则返回TRUE。而且value_if_true可以是一个表达式；value_if_false是logical_test为FALSE时函数的返回值。如果logical_test为FALSE并且省略value_if_false，则返回FALSE。value_if_false也可以是一个表达式。Excel2010函数应用进阶除了常用统计外，还需统计各科及格率，或者为了分析成绩，还要统计类似于“女生的语文总分”，“数学分数高于90分（含90分）的女生人数”、“前十名男生人数”等等，若数据量大，使用传统方法，统计起来也很麻烦，这个时候就需要使用一些功能更加强大的统计函数排序函数排序函数RANK语法：RANK（number，ref，order）。参数：number是需要计算其排位的一个数字；ref是包含一组数字的数组或引用（其中的非数值型参数将被忽略）；order是用来说明排序方式的数字（如果order为0或省略，则以降序方式给出结果，为1则为升序方式）。带条件的统计带条件的统计COUNTIF语法：COUNTIF（range，criteria）。参数：range为需要统计的符合条件的单元格区域；criteria为参与计算的单元格条件，其形式可以为数字、表达式或文本（如1、“60”和“男”等）。条件中的数字可以直接写入，表达式和文本必须加引号。SUMIF用途：根据指定条件对若干单元格、区域或引用求和。语法：SUMIF(range，criteria，sum_range)参数：range为用于条件判断的单元格区域，criteria是由数字、逻辑表达式等组成的判定条件，sum_range为需要求和的单元格、区域或引用。数据库函数数据库函数DAVERAGE用途：返回数据库或数据清单中满足指定条件的列中数值的平均值。语法：DAVERAGE(database，field，criteria)参数：database构成列表或数据库的单元格区域。field指定函数所使用的数据列。criteria为一组包含给定条件的单元格区域。DCOUNT用途：返回数据库或数据清单的指定字段中，满足给定条件并且包含数字的单元格数目。语法：DCOUNT(database，field，criteria)参数：同上DSUM用途：返回数据清单或数据库的指定列中，满足给定条件单元格中的数字之和。语法：DSUM(database，field，criteria)参数：同上【技能练习】打开ex3-3-1.xlsx，完成如下操作，并以原文件名保存。对Sheet1工作表的数据使用数据库函数完成统计“统计基本工资在4500（含4500）元至5000（不含5000）的女讲师的人数”，“基本工资高于6000（含6000）的男讲师的实发工资总额”，“用数据库函数计算姓吴的男教师中的平均实发工资”，条件区域自定。计算sheet2工作表的“总积分”列的内容（利用公式：“总积分=第一名项数*8+第二名项数*5+第三名项数*3”），按总积分的降序次序计算“积分排名”列的内容（利用RANK函数，降序）。2打开ex3-3-2xlsx，完成如下操作：计算sheet1工作表的捐款总计（结果放入B6单元格）和各班捐款的百分比（百分比=各班捐款金额/总计）。求sheet2工作表的“同比增长”列的内容（同比增长=（13年销售量-12年销售量）/12年销售量，百分比型，保留小数点后两位）；如果“同比增长”列内容高于或等于20%，在“备注”列内给出信息“较快”，否则内容为空“”（一个空格）（利用IF函数）。计算sheet3工作表的平均成绩列的内容（数值型，保留小数点后2位），计算一班学生人数（置G3单元格内，利用COUNTIF函数）和一班学生平均成绩（置G5单元格内，利用SUMIF函数）。