项目三：公式与函数-office与平面设计基础课件.pptx

1、模块二 Excel 2010项目三 公式与函数1.Excel 2010中公式的格式及输入公式、复制公式的方法。2.Excel 2010中函数的格式及输入函数的方法。3.Excel 2010中常用函数的用法。掌握知识点任务1 公式1.了解公式Excel 2010中公式用于对单元格中的数据进行计算。公式总是以“”开头，后面是表达式，表达式中可以包括各种运算符、常量、函数、单元格地址等。2.输入公式输入公式时，用户可以直接在活动单元格中进行输入，也可以选择要输入公式的单元格，在编辑栏中输入，输入完毕按回车键，即可显示公式的结果。若公式中引用的单元格的值发生变化，公式的结果也会跟着发生变化。补充说明：

2、输入公式时，可以直接输入单元格地址，也可以用鼠标选择的方法代替单元格或单元格区域地址的输入。3.复制公式相邻单元格间复制公式：选择含公式的单元格，指向右下角填充柄，呈黑色十字指针时拖到目标位置松手。不相邻单元格间复制公式：选择含公式的单元格，按Ctrl+C，选择目标单元格，按Ctrl+V。补充说明：（1）复制公式时，若公式中引用的单元格地址是相对地址，如：A5，C6，则单元格地址会随位置的变化而变化。即同行复制时，行不变列变；同列复制时，列不变行变。（2）复制公式时，若希望公式中引用的单元格地址不随位置的变化而变化，应使用绝对地址，如：A5，C6。（3）复制公式时，也可以根据实际情况使用混合地

3、址，如A5，A5。4.用公式计算总分（如图2-3-1所示）（1）选择G2单元格。（2）输入“=D2+E2+F2”，按回车键。（3）选择G2单元格，指向填充柄，呈黑色十字指针时按下鼠标左键并拖至G9单元格松手。5.先用公式计算总价，再计算折后价（折后价总价折扣率）（如图2-3-2所示）（1）选择D4单元格。（2）输入“=B4*C4”，按回车键。（3）选择D4单元格，指向填充柄，呈黑色十字指针时按下鼠标左键并拖至D8单元格松手。（4）选择E4单元格。（5）输入“=D4*E2”或“=D4*E2”，按回车键。（6）选择E4单元格，指向填充柄，呈黑色十字指针时按下鼠标左键并拖至E8单元格松手。任务2 函

4、数1.了解函数函数是Excel 2010已经定义好的公式，格式为：函数名（参数）。参数可以有一个，也可以有多个。如果没有参数，圆括号也不能省略，例如输入“=PI()”会返回圆周率的值；输入“=NOW()”会返回当前日期和当前时间。在Excel 2010中可以通过三种方法输入函数：直接输入、利用“插入函数”对话框和“函数库”工作组。2.插入函数（1）利用“插入函数”对话框选择要插入函数的单元格，单击“公式”选项卡，在“函数库”工作组中，单击“插入函数”按钮fx。在弹出的“插入函数”对话框中选择函数类别、函数名，单击“确定”按钮，如图2-3-3所示。在“函数参数”对话框中输入参数，单击“确定”按钮

5、，如图2-3-4所示。（2）利用“函数库”工作组选择要插入函数的单元格，单击“公式”选项卡，在“函数库”工作组（如图2-3-5所示）中，单击所需函数类别，在下拉菜单中选择所需函数，在弹出对话框中输入参数，单击“确定”按钮。3.常用函数及功能（见表2-3-1）4.常用函数的用法（1）IF函数语法：IF（条件，结果1，结果2）。功能：如果条件为真返回结果1，否则返回结果2。实例：单元格公式为“IF（F2=60，及格，）”。功能：如果F2单元格的值大于或等于60，单元格结果为及格，否则为空。（2）SUMIF函数语法：SUMIF（区域，条件，求和区域）。功能：对满足给定条件的单元格求和。实例：打开“e

6、xcel1.1.xlsx”，统计所有卖场各品牌的销售总量，如图2-3-6所示。选择F3单元格。输入“=SUMIF(B3:B11，E3，C3:C11)”，按回车键。选择F3单元格。指向填充柄，呈黑色十字指针时拖至F5单元格松手。（3）COUNTIF函数语法：COUNTIF（区域，条件）。功能：统计某个区域中满足给定条件的单元格数目。实例：打开“excel1.2.xlsx”，统计语文、数学70分以上同学的个数，如图2-3-7所示。选择D11单元格。输入“=COUNTIF(D2:D9，=70)”，按回车键。选择D11单元格。指向填充柄，呈黑色十字指针时拖至E11单元格松手。（4）RANK函数语法：R

7、ANK（数值或单元格，区域，0或1）。功能：返回某一单元格中数值在一列数字中相对于其他数值的大小排名。说明：若数值相同，则排名相同；如果有两个排名相同，则相同排名后面的排名为间隔一个排名。区域一般采用单元格的绝对地址。第3个参数为0或省略，得到从大到小的排名，为1则得到从小到大的排名。实例：打开“excel1.3.xlsx”，如图2-3-8所示，按总分由大到小排名（由图可见，有两个第4名，就没有第5名的排名）。选择G2单元格。输入“=RANK(F2，F2:F9)”，按回车键。选择G2单元格。指向填充柄，呈黑色十字指针时拖至G9单元格松手。（5）ISNUMBER函数语法：ISNUMBER（参数）

8、。功能：检测一个值是否是数值，返回TRUE或FALSE。实例：在某单元格中输入“ISNUMBER(A1)”。功能:若A1中值为数值，则返回TRUE；若为字符或空，则返回FALSE，如图2-3-9所示。（6）NOW函数语法：NOW()，不需要参数。功能：返回当前日期和时间，如图2-3-10所示。（7）MID函数语法：MID（字符串，起始位置，指定长度）。功能：从文本字符串中指定的起始位置起，返回指定长度的字符。实例1：打开“excel1.4.xlsx”文档，根据C2中身份证号在C4单元格显示出生年份，如图2-3-11所示。选择C4单元格。输入“=MID（C2，7，4）”，按回车键。实例2：利用C

9、2中身份证号码在C5单元格显示年龄，如图2-3-12所示。选择C5单元格。输入“=YEAR(NOW()-MID(C2，7，4)”，按回车键。（8）VLOOKUP查找函数语法：VLOOKUP（查找目标，查找区域，返回值的列数，0或1）。功能：从指定的查找区域指定列数中返回查找目标对应的值。说明：查找目标：要查找的对象。查找范围：第二个参数可以从一个单元格区域、一个常量数组或内存数组中查找。给定的第二个参数查找范围，要符合以下条件才不会出错。a.查找目标一定要在该区域的第一列。b.该区域中一定要包含要返回值所在的列。返值的列数：第三个参数中一个整数值，是返回值在第二个参数给定查找区域中的列数。精确

10、OR模糊查找：0或FALSE表示精确查找；1或TRUE表示模糊查找；缺省该参数则为模糊查找，对于模糊查找必须按升序排列查找范围第一列中的值，否则无法返回正确的值。实例：打开“excel1.5.xlsx”，根据左侧表格内容，在右表的I3:I6单元格中利用VLOOKUP函数依次填写各同学对应的记分，如图2-3-13所示。选择I3单元格。输入“=VLOOKUP(H3,B1:f21,5,0)”。按回车键。选择I3单元格。指向填充柄，呈黑色十字指针时拖至I6单元格松手。习题三打开“习题三.xlsx”文档，完成下列操作，部分操作效果如图2-3-14所示。（1）用不同的方法计算每位同学的总分。（2）计算每位

11、同学的平均分。（3）计算每位同学总分的名次，显示为“第n名”。（4）在J列显示每位同学的等次，总分大于或等于168分，显示“优秀”，否则显示为空。（5）在D28:F28单元格区域统计语文、数学、英语的参考人数。（6）在D29:F29单元格区域统计各科成绩的平均分。（7）在D30:F30单元格区域分别统计各科的及格人数（语文、数学满分90分，54分及以上为及格；英语满分30分，18分及以上为及格）。（8）在D31:F31单元格区域计算各科及格率。（9）在D32:F32单元格区域计算各科高分率（语文、数学满分90分，72分及以上为高分；英语满分30分，24分及以上为高分）。（10）在D33:F33单元格区域计算各科低分率（语文、数学满分90分，36分及以下为低分；英语满分30分，18分及以下为低分）。