用Excel做教务入门篇

第1讲、基本概念与Excel窗口界面元素

1、工作簿：一个Excel文件就是一个工作簿（WorkBook），默认工作簿的名称是工作簿1，在一个工作簿中默认包含3张工作表。

2、工作表：工作簿中的每一张表称为工作表，每张工作表都有标签，默认标签名为Sheet1，Sheet2，Sheet3。

活动工作表：工作簿中用户正在处理的工作表。

⑴新建工作表：单击"插入工作表"按钮或按Shift+F11快捷键

⑵重命名工作表标签：双击标签名输入名称为按Enter键

⑶删除工作表：右击工作表标签，在弹出的快捷菜单中选择"删除"命令

⑷移动或复制工作表：拖曳工作表标签至目标处（按Ctrl键同时拖曳为复制）

工作表总是存储在工作簿中。工作表由排列成行或列的单元格组成。

3、单元格：存储数据的基本单元（1048576×16384）

⑴单元格地址（位置）：列标行号表示，

⑵单元格区域地址：左上角单元格地址:右下角单元格地址

4、活动单元格：活动单元格就是选定单元格，可以向其中输入数据。一次只能有一个活动单元格。活动单元格四周的边框加粗显示。某时刻只有一个活动单元格。

⑴名称框：显示活动单元格地址或者为其定义名称

⑵编辑栏：显示或编辑活动单元格内数据

⑶填充柄：位于选定区域右下角的小黑方块。将鼠标指向填充柄时，鼠标的指针变为黑十字，拖曳填充柄可以自动填充连续有规律的数据，如日期、星期、序号等。

第2讲、公式、运算符与引用

一、四种运算符

优先次序：引用运算符＞算术运算符＞文本运算符＞比较运算符；

1、引用运算符：对单元格区域引用地址进行合并计算的运算符。

2、算术运算符：+、－、*、/、∧、%（加减乘除、乘方、百分号）

⑴算术运算符的优先级：百分号、乘方、乘法和除法、加法和减法；

⑵同级运算符按从左到右的顺序进行；如果有括号，则先进行括号内运算，后进行括号外运算。只有小括号，在多级嵌套但必需成对出现。

3、文本运算符：& 连接多个文本字符串，以生成一段文本。

如："North"&"wind" 的结果为 "Northwind"

4、比较运算符：=、>、<、>=、<=、<>。使用比较运算符比较两个值时，结果返回逻辑值TRUE或FALSE。

二、公式

1、公式结构："=操作数及运算符"，如"=3+5"、"= A1 + B1"、"= A1＝B1"

⑴"＝（等号）"为公式前导符；

⑵"运算符"包括算术运算符、比较运算符、文本运算符和引用运算符。

⑶"操作数"包括常量、函数和单元格地址的引用等；

三、引用单元格地址：一个单元格中的数据被其他单元格的公式使用，称为引用，该单元格的地址称为引用地址。

1、根据引用数据源的位置：内部引用和外部引用

⑴内部引用：引用同一个工作表中单元格地址，或者同一个工作簿上其他工作表中的单元格地址，称为内部引用；

注：引用同一个工作簿上其他工作表中的单元格地址，标识为"工作表名!单元格地址"。

⑵外部引用：引用其他工作簿中的单元格地址。

注：外部引用标识为"[工作簿名]工作表名!单元格地址"。

⑵根据公式所在单元格的位置发生变化时，单元格引用的变化情况，可以将引用分为三种类型：相对引用、绝对引用和混合引用。

⑴如果公式中包含单元格的相对地址，称为相对引用，当把公式复制到其它单元格时，公式中引用的单元格地址作相应的变化。

例：C2单元格中有公式"=A2+B2"，当公式由C2复制到C3单元格，公式中的引用也会变为"=A3+B3"；若公式自C列向下复制，公式所在单元格"行号"每增加1行，公式中引用的行号也自动加1。

⑵如果公式中包含单元格的绝对地址，称为绝对引用，当把公式复制到其它单元格时，公式中引用的单元格地址不作变化。

例：C2中有公式"=$A$3+$B$3"，则无论公式复制到何处，公式中的引用不变，公式仍为"=$A$3+$B$3"。

⑶如果公式中包含单元格的混合地址，称为混合引用，包含了一个相对引用和一个绝对引用，单元格的行或列有一个是固定不变的，复制公式时，相对地址作相应的变化而绝对地址不变。

混合引用有"绝对列和相对行"，如"=$A3+$B3"；或"绝对行和相对列"两种形式，如"=A$3+B$3"。

注：选中包含引用的单元格，按 F4 键在相对引用、绝对引用和混合引用间切换。

第3讲、函数、数据类型与函数参数

一、函数：预先定义，用来执行计算、分析等处理数据任务的特殊公式。

1、函数结构：=函数名(参数)，以上一讲公式为例：=SUM(B2:B6)

= → 公式或函数的前导符

SUM → 函数名，决定函数的功能与用途

B2:B6 → 参数，决定函数运算的范围、顺序、方式等

SUM函数，它的语法为：

⑴参数是函数最重要的组成部分，分为可选参数和必需参数。在函数语法中，可选参数一般使用一对中括号"[]"包括起来，SUM函数最多可以支持255个参数，第一个参数是必须的，其余的254个都是可选的，可以根据实际情况选择是否省略。

⑵有些函数有多个参数，参数之间用英文逗号分隔；

⑶没有参数的函数，括号不能省略。

2、函数的分类

按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们；扩展函数（或用户自定义函数）必须通过"加载宏"加载然后才能像内置函数那样使用。

⑴内置函数：Excel 2010提供了十二类四百多个函数（包括查找和引用函数、数学和三角函数、统计函数、日期和时间函数、文本函数、逻辑函数、数据库函数、工程函数、 财务函数、信息函数、多维数据集函数、兼容性函数等），可在"公式"选项卡"函数库"组中查看类别或通过"插入函数"对话框查看可用函数的列表。

⑵扩展函数：将下面的代码复制，粘贴到一个工作簿的模块中，将文件保存为xla格式，即得到一个加载宏，以后在"开发工具"中单击"加载项"，在打开的"加载宏"对话框中加载它，然后才能像内置函数那样使用。

附：两个自定义函数代码（一个从五位学号获取班级名，一个快速由阿拉伯数字得到考场编号）

Public Function BJ(XueHao As Long) As String

'快速将学号转化为班级名称

Application.ScreenUpdating = False

If XueHao = 0 Or XueHao > 91999 Then Exit Function

Dim i As Byte, j As Byte

i = Val(Left(XueHao, 1)) - 1: j = Val(Mid(XueHao, 2, 2)) - 1

StrGrade = Array("一", "二", "三", "四", "五", "六", "七", "八", "九")

StrClass = Array("⑴", "⑵", "⑶", "⑷", "⑸", "⑹", "⑺", "⑻", "⑼", "⑽", "⑾", "⑿", "⒀", "⒁", "⒂", "⒃", "⒄", "⒅", "⒆", "⒇")

BJ = StrGrade(i) & StrClass(j) & "班"

Application.ScreenUpdating = True

End Function

Public Function KC(intShuzi As Integer) As String

'快速输入考场，将阿拉伯数字转化为中文小写

Application.ScreenUpdating = False

If intShuzi = 0 Then Exit Function

Dim zwxx As String

Dim i As Byte, j As Byte, bytL As Byte, bytR As Byte, bytM As Byte

StrName = Array("", "一", "二", "三", "四", "五", "六", "七", "八", "九")

i = Len(Trim(intShuzi))

Select Case i

Case 1

zwxx = StrName(intShuzi)

Case 2

bytL = Val(Left(intShuzi, 1))

bytR = Val(Right(intShuzi, 1))

If bytL = 1 Then

zwxx = "十" & StrName(bytR)

Else

zwxx = StrName(bytL) & "十" & StrName(bytR)

End If

Case 3

bytL = Val(Left(intShuzi, 1))

bytR = Val(Right(intShuzi, 1))

bytM = Val(Mid(intShuzi, 2, 1))

If bytM = 0 And bytR = 0 Then

zwxx = StrName(bytL) & "百"

ElseIf bytM = 0 Then

zwxx = StrName(bytL) & "百零" & StrName(bytR)

Else

zwxx = StrName(bytL) & "百" & StrName(bytM) & "十" & StrName(bytR)

End If

End Select

KC = "第" & zwxx & "考场"

Application.ScreenUpdating = True

End Function

二、数据的类型

常见数据类型：数值、文本、逻辑、错误，可用Type()函数来检测数据类型。

在默认情况下：文本：左对齐；数值：右对齐；逻辑值、错误值：居中对齐。

1、数值：数字型有多种形式：小数、分数、百分数、科学记数、货币、日期与时间。

⑴在Excel中，最多能保存15位有效数字，如果有效数字超过15位，Excel自动将15位整数以后的变为零，直接舍弃小数的15位后数字。

①数字超过单元格宽度时，将自动使用科学记数法来表示，如"1234567891230"默认显示为"1.23457E+12"；

②如果数字太长，单元格宽度太小，显示为一连串"####"

③分数：先输入0和空格，再输入分数，否则当作日期型数据

④负数：如（10）表示－10

⑤文本型数字：如电话号码、身份证号码，先输入一个英文半角引号(')，再输入数字；或者先设置数字格式为文本，再输入数据

⑵日期和时间：Excel中日期和时间是以一种特殊的数值形式储存的"序列值"，本质上日期是整数，时间是小数。

1天数值单位就是1，1小时是=0.041666667，一分钟是×，一秒钟是×。日期时间可以直接参与加减乘除数值运算，但与数值不同的是，当日期和时间以常量的形式出现在公式中时，必须使用半角双引号包裹起来。如使用公式：="2020-3-16"+100计算2020年3月16日的100天后的日期。

①三种输入方法："年－月－日"、 "年/月/日"、 "月/日"。如输入"6/1"得到"6月1日"。

②按组合键"Ctrl+；"可快速输入当前日期；函数=Today()自动返回系统当前日期。

③按组合键"Ctrl+Shift+；"可快速输入当前时间，函数=Now()自动返回系统当前时间。

2、文本，又称字符串，是指包含非数值字符的数据，当文本出现在公式中时，必须使用半角双引号包括起来。

文本型数值不能参与数学运算，左上角有绿色标志，单击该单元格，再单击左侧下拉按钮可将其转换为数字。

⑴给数字后面加上中文单位：用&连接或设置数字格式

⑵如何在单元格内输入并显示"＝A+B"：先输入'

3、 逻辑值：False和True，False为假，True为真。在逻辑函数IF中，通过比较运算符返回的逻辑值，可以让计算机具有逻辑判断能力。

＝A1=B1

4、 错误值：公式与函数计算如果出现错误，不同的错误返回不同错误值，如#REF!、#DIV/0!、#NAME?、#N/A、#REF!、#VALUE!、#NULL!，它们代表不同的含义，可根据错误值快速诊断错误的原因，进而加以修正公式与函数。

不同数据类型比较大小依据：数值<文本<FALSE<TRUE

5、 数组：是由一到多个元素构成的有序集合，元素可以是数值、文本、逻辑值甚至是错误值，常量数组和区域数组两类。

⑴常量数组放在"{}"内部

单行数组元素之间使用半角逗号间隔，例如{1,2,3,4,5}、{"a","b","c","d"}

单列数组元素之间使用半角分号间隔，例如{1;2;3;4;5}

如："{56,78,89;90,76,80}"表示2行3列的常量数组。

区域数组是一个矩形的单元格区域，该区域中的单元格共用一个公式。

三、函数参数

1、按照数据类型分：数值（日期和时间）、文本、逻辑值、错误值和数组等。2、按照函数运算过程中是否发生变化来分：变量和常量。

3、按照数据来源分：引用和非引用。

⑴引用同一工作簿不同工作表的单元格，甚至引用其他工作簿中的数据。根据公式所在单元格的位置发生变化时，单元格引用的变化情况，我们可以引用分为相对引用、绝对引用和混合引用三种类型。

⑵引用单元格或单元格区域名称，更加方便与直观①选中要命名的单元格或单元格区域，在"名称框"输入名称后回车。

②在"公式"选项卡"定义的名称"组中，单击"定义名称"或"名称管理器"均可。 创建好的名称可以被所有工作表引用，而且引用时不需要在名称前面添加工作表名(这就是使用名称的主要优点)，因此名称引用实际上是一种绝对引用。

第4讲、工作表美化与数据排序、筛选

一、数据输入

凡是输入到Excel工作表中的内容，包括文字、数字、公式以及函数等等，统统称为数据。

1、学号是连续有规律数据，可用填充柄自动填充。使用自动填充功能时，不但填充数据而且单元格格式也一并复制。

2、⑴先选取单元格，再输入数据；输完数据后，按Tab键、Enter键，光标键或单击另一个单元格或编辑栏中的"输入"，可确认数据输入；

⑵默认：按Tab键向右移动光标、按Enter键向下移动；

⑶若要在单元格中换行，应按 Alt + Enter 组合键(插入换行符)。

或者在"开始"选项卡"对齐方式"组中，单击"自动换行"按钮。

⑷选取多个单元格后，按下 Ctrl+Enter 组合键可在选定单元格区域内输入相同的内容。

二、表格的美化

1、标题格式设置：

⑴对齐方式：跨列居中或合并后居中；

⑵字体、字号

2、数据区域：表格边框设置、对齐方式与字体、字号

三、排序

数据清单相关概念

或

Excel中排序是指数据依照某种属性的递增或递减规律重新排列，该属性称为关键字 。

递增规律称为升序（A 到 Z，或 0 到 9）

递减规律称为降序（Z 到 A，或 9 到 0）。

1、单关键字排序

选定关键字列任一单元格，在"数据"选项卡"排序和筛选"组中，单击"降序"按钮或"升序"按钮。

2、多键字排序

⑴选取数据清单中任一单元格；⑵在"数据"选项卡"排序和筛选"组中，单击"排序"按钮，打开"排序"对话框；⑶确保勾选"数据包含标题"复选框，在"排序"对话框选择"主要关键字"、在"次序"下拉列表框中选择"升序"或"降序"；⑷单击"添加条件"按钮，选择"次要关键字"在"次序"下拉列表框中选择"升序"或"降序"；根据需要再添加条件并设置条件，最后单击"确定"按钮。

3、设置数据有效性防止数据输入错误。

四、数据筛选

数据筛选：指让某些符合条件的数据记录显示出来，而暂时隐藏不符合条件的数据记录。这样可以更清楚地显示需要的数据。

Excel筛选分为"自动筛选"、"自定义筛选"和"高级筛选"等方式。

第5讲、对成绩进行基本分析

1、 基本分析函数：Sum、Average、Max、Min

1、四种函数功能及语法

2、注意：

⑴Number1必需；Number2, ... Number255可选，最多可包含 255个参数。

⑵如果区域或单元格引用参数包含文本、逻辑值或空单元格，则这些值将被忽略；但包含零值的单元格将被计算在内。如果参数为错误值或不能转换为数字的文本，将会导致错误。

⑶AverageA、MaxA、MinA

MinA语法：MinA(value1, [value2], ...)，参数可以是：数值；包含数值的名称、数组或引用；数字的文本表示；或者引用中的逻辑值。

第6讲、对成绩进行排名

人生重要的考试中考、高考、研究生入学考试乃至考编，大多都是选拔性考试，分数不重要关键是名次要靠前，因为分数受试卷难易程度影响，名次才是王道，直接反映了他在竞争对手中所处的位置。成绩分析时对各学科成绩与总分进行排名显得尤为重要，大家千万不要说这是应试教育，与素质教育格格不入背道而驰，其实素质教育并不反对分数嘛，只不过素质教育不唯分数而已。

1、成绩排名函数：RANK 函数

⑴作用：返回一个数字在数字列表中的排位。

⑵语法：RANK(number,ref,[order])

RANK(要排位的数字, 数字列表的引用,[排位方式])

数字排位方式：0或省略：降序排列；1：升序排列。

2、RANK.AVG函数和RANK.EQ 函数（Average、Equal）

如果多个重复值RANK和 RANK.EQ返回该组数值的最高排位。

如果多个值具有相同的排位，RANK.AVG函数返回平均排位。