用excel制作学习笔记(用Excel做教务入门篇)

用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函数,它的语法为:

用excel制作学习笔记(用Excel做教务入门篇)(1)

⑴参数是函数最重要的组成部分,分为可选参数和必需参数。在函数语法中,可选参数一般使用一对中括号"[]"包括起来,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()函数来检测数据类型。

用excel制作学习笔记(用Excel做教务入门篇)(2)

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

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

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

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

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

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

④负数:如(10)表示-10

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

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

用excel制作学习笔记(用Excel做教务入门篇)(3)

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

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

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

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

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

用excel制作学习笔记(用Excel做教务入门篇)(4)

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

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

⑵如何在单元格内输入并显示"=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制作学习笔记(用Excel做教务入门篇)(5)

用excel制作学习笔记(用Excel做教务入门篇)(6)

三、排序

用excel制作学习笔记(用Excel做教务入门篇)(7)

数据清单相关概念

用excel制作学习笔记(用Excel做教务入门篇)(8)

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函数返回平均排位。

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页