第九章 Excel

第一节 Excel基本操作

一、界面

二、工作表的操作

三、单元格的操作

第二节 公式和函数的使用

一、公式基本语法和运算符

在公式输入时，首先必须以“=”开头，然后输入参与计算的单元格名称、数值和运算符。其中运算符包括算术运算符、比较运算符、文本运算符和引用运算符。

1.运算符

2.单元格的表示

2.1 单个单元格的表示

表示方法：列标(大写字母)+行号(数字)   例如：A2  B3

       列标范围：A~XFD    行号范围：1~1048576

       列标变化规律：

2.2 矩形单元格的表示

表示方法：左上角单元格名称 ：右下角单元格名称  例如A2:B5

多行/多列的表示：A:F    3:6

计算题：A2:B5一共有多少个单元格？

公式：(列标-列标+1)×(行号-行号+1)

2.3 多个不连续区域单元格的表示

","      "和"     例如A3:C7,D8:E9

":"      "到"

空格    "交集"

2.4 不同工作表单元格的表示

表示方法：工作表名称!单元格名称  例如：Sheet1!B3

2.5 不同工作簿单元格的表示

表示方法：[工作簿名称]工作表名称!单元格名称  例如=[工作簿1.xlsx]Sheet1!A1

 

3.单元格的引用

3.1 绝对引用、相对引用、混合引用

       相对引用：A1

绝对引用：$A$1

混合引用：$A1 或者 A$1

（切换几种引用方式的快捷键是F4）

3.2 公式的变化

①公式在复制/智能填充时，相对引用变化，绝对引用不变。

【例1】在单元格B4中有公式：=C3+$C5+C$6+$C$7，将B4中的公式复制到E8中，则在E8中的公式为什么？

 变为：   =F7+$C9+F$6+$C$7，

②公式在移动时，相对引用，绝对引用都不变。

【例2】在单元格A1中有公式：=$F$8 + $E9 + D$3，将其剪切粘贴到B8单元格中，则B8单元格中的公式变为多少？

变为：

 =$F$8 + $E9 + D$3

③删除、插入行/列时，相对引用和绝对引用都变。

【例3】在单元格A1中有公式：=$F$8 + $E9 + D$3，同时删除第2、3列和第4行，则A1中的公式变为多少？

变为：=$D$7 + $C8 + B$3

若删除的单元格在引用的单元格中（例如删除第F列，或者珊瑚第8行）则会出现#REF!的错误

问题1：求出D2:D8的总和

=SUM(D2:D8)

问题2：求出D2:D8的最大值

=MAX(D2:D8)

问题3：求出D2:D8的最小值

=MIN(D2:D8)

问题4：求出D2:D8的平均值

=AVERAGE(D2:D8)

问题5：统计A1到D8的数值类型单元格个数

=COUNT(A1:D8)

问题6：统计A1到D8非空单元格的个数

=COUNTA(A1:D8)

问题7：统计D2到D8分数大于60的人数

=COUNTIF(D2:D8,">60")

问题8：统计全班的男生人数

=COUNTIF(D2:D8,"男")或者=COUNTIF(D2:D8,"=男")

问题9：统计全班不是男生的人数

=COUNTIF(D2:D8,"<>男")

问题10：统计全班分数比张伟（D8）高的人数

=COUNTIF(D2:D8,">"&D8)

问题11：求张伟分数的排名

=RANK(D8,D$2:D$8)或者=RANK.EQ(D8,D$2:D$8)

同学们注意，在使用rank函数的时候，99%的情况范围都要使用绝对引用

问题12：全班男生成绩的总和

=SUMIF(B2:B8,"男","D2:D8")

问题13：全班大于60分成绩的总和

=SUMIF(D2:D8,">60")

问题14：最高成绩和最低成绩之差

=MAX(D2:D8)-MIN(D2:D8)

问题15：史珍香的成绩和平均成绩之差

=D7-AVERAGE(D2:D8)

问题16：求成绩在(60,90]的人数

=COUNTIF(D2:D8,">60")-COUNTIF(D2:D8,">90")

问题17：求出每个学生排名，并显示“第n名”

="第"&rank(D2,D$2:D$8)&"名"

第三节 数据特殊处理