Excel的HYPERLINK函数应用（共8篇）
Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式，以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。
参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。
按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们;而后者必须通过单击“工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。
什么是公式？
函数与公式既有区别又互相联系。如果说前者是Excel预先定义好的特殊公式，后者就是由用户自行设计对工作表进行计算和处理的计算式，
以公式“=SUM(E1:H1)*A1+26”为例，它要以等号“=”开始，其内部可以包括函数、引用、运算符和常量。上式中的“SUM(E1:H1)”是函数，“A1”则是对单元格A1的引用(使用其中存储的数据)，“26”则是常量，“*”和“+”则是算术运算符(另外还有比较运算符、文本运算符和引用运算符)。
如果函数要以公式的形式出现，它必须有两个组成部分，一个是函数名称前面的等号，另一个则是函数本身。
关键词：excel,逻辑,逻辑函数,信息技术
Excel2010是办公软件office2010的一个小套件, 主要用于各种计算。它内置有大量的计算公式, 给用户计算提供了很大方便。其中, 逻辑函数是计算公式的一个类型, 作用是帮助用户判断事物。它包括AND、FALSE、IF、IFERROR、NOT、OR、TRUE七个函数。这些函数是实现办公自动化的重要工具。这些函数的应用, 能够给我们的工作带来很大方便, 收到事半功倍的效果。下面分别介绍其中四个重要的函数。
1 AND函数
这个函数的作用是检查一批参数是否全真。如果被检查的若干个参数全部是正确的, 那么反馈的结果为“正确”;如果其中有一个参数是错误的, 那么反馈的结果为“错误”。
1.1 任务
检验一批电子产品。这批电子产品, 有一段电路串联着A、B、C三个电子元件。现在, 检查每个电子元件的导电情况, 判断这段电路是否通电。
1.2 分析
这段电路通电, 必须是三个电子元件都通电。否则, 这段电路不能够通电。这与AND函数的功能一致, 因此, 可以使用AND函数解决这一问题。
1.3 思路
我们可以在单元格A1、B1、C1分别输入“通电”二字。在D1、E1、F1里分别输入电子元件A、B、C的监测结果。通电, 则输入“通电”二字;不通电, 则输入“不通电”三字。如果A1=D1, B1=E1, C1=F1, 出现三个真命题, 那么AND函数的判断结果为“正确”, 即通电。如果其中有一组单元格里的参数不对等, 那么AND函数判断的结果为“错误”, 即不通电。我们这里所说的“通电”, 计算机反馈的是“TRUE”, “不通电”计算机反馈的是“FALSE”。
类似地, 在A、B、C列的其他单元格全部输入“通电”, D、E、F列的第二行输入第二个电子产品电子元件的检测情况, 第三列输入第三个电子产品电子元件的检测情况, 以此类推, 输入所有电子产品电子元件的检测情况。用复制公式的方法, 把G1中的判断公式粘贴到G列的其他行。这样, G列的其他单元格就会显示出其他电子产品的通电情况。
1.4 操作
(1) 设置预设答案
在单元格A1、B1、C1里输入汉字“通电”。
(2) 输入检测情况
在单元格D1、E1、F1里分别输入电子元件A、B、C的导电情况。
(3) 输入判断公式
在G1里显示这段电路的通电情况。通电, 自动显示“TRUE”, 不通电, 自动显示“FALSE”。
单击单元格G1, 选“公式”菜单, 单击“逻辑”按钮下的AND, 打开AND函数对话框。
在logical1里输入A1=D1 (可以单击单元格A1, 敲击键盘上的“=”号, 再单击D1.) , logical2里输入B1=E1, logical3里输入C1=F1, 单击“确定”按钮。
(4) 判断第一个电子产品的通电情况
在单元格D1、E1、F1里分别输入电子元件A、B、C的检测情况, 敲击“回车”键。在G1里可以看到这段电路的通电情况。
若D1、E1、F1里输入的都是“通电”, 则G1里显示TRUE, 即通电, 否则显示FALSE, 即不通电。
(5) 判断其他电子产品的通电情况
同时选中A1、B1、C1, 向下拖动选区右下角的控制柄, 把该选区的内容粘贴到其他行。选中G1, 向下拖动其右下角的控制柄, 把G1里的判断公式粘贴到G列的其他单元格。这样, 在D2、E2、F2里输入第二个电子产品中三个串联电子元件检测情况, 就可以在G2里看到第二个电子产品的通电情况。在D、E、F列输入其他电子产品三个电子元件的检测情况, 在G列相应的单元格里, 可以看到这些电子产品的通电情况。
如果每一电子产品的预设答案都是一样的, 那么可以不在电子表格里输入预设答案, 而是把预设答案直接输入到AND函数的对话框里。
另外, 这里所说的参数与我们通常见到的参数不完全一样。通常说的参数是数, 可以是常数, 也可以是变数。这里所说的参数, 不仅仅是数, 而且可以是量、数量关系、单元格等, 包括一切表示条件的符号和关系。
使用AND函数的流程:单击插入函数的单元格, 单击“公式”菜单, 单击“逻辑”按钮, 选择“AND”函数, 在对话框里输入条件参数。
2 OR函数
这个函数的功能是检查一组参数是否同假。如果被检测的若干个参数都是假的, 那么反馈的结果是假的;如果有一个参数是真的, 那么反馈的结果就是真的。需要判断事物是否全假的例子在生活中有很多。
2.1 任务
期末考试结束, 为了帮助学习成绩不好的学生查找原因, 鼓励他们努力学习, 教师决定找数学、语文、舞蹈三科成绩都在60分以下的学生座谈。应该怎样使用计算机确定这些学生呢?
2.2 分析
一个学生的数学、语文、舞蹈成绩是一组参数。设不及格事件为假 (FALSE) , 及格事件为真 (TRUE) 。那么这个任务就是求数学、语文、舞蹈成绩全部为假的人。
2.3 思路
要找出数学、语文、舞蹈考试成绩都低于60分的人, 就需要把这些人的姓名和考试成绩输入计算机。为了便于识别三科成绩都不及格的学生, 还要有一列显示判断的结果。因此, 不妨从第3行开始, 在A列输入学生的序列号, B列输入学生的姓名, C列输入数学成绩, D列输入语文成绩, E列输入舞蹈成绩, F列显示判断结果。F列是计算机自动判断的结果, 如果需要座谈, 则显示FALSE;如果不需要座谈, 则显示TRUE。
2.4 操作
(1) 输入原始数据
合并单元格A1:F1, 并在此输入标题“考试成绩分类表”。在A2里输入“序列号”, B2里输入“姓名”, C2里输入“数学”, D2里输入“语文”, E2里输入“舞蹈”, F2里输入“分类”。输入每个学生的序列号、姓名和考试成绩。如图1, 在“分类”这一列, 放置计算机根据学生考试成绩判断的结果。
(2) 判断第一个人
单击F3, 确定判断结果显示的位置, 选“公式”菜单, 单击“逻辑”按钮, 选“OR”函数, 打开OR函数对话框, 单击logical1的后面, 单击C3, 键入“>=60”;单击logical2的后面, 单击D3, 键入“>=60”;单击logical3的后面, 单击E3, 键入“>=60”, 单击“确定”按钮。如图2。
(3) 复制公式
单击F3, 向下拖动F3右下角的控制柄, 得到判断结果。如图3。
F列的符号有两类:TRUE和FALSE。其中, TRUE表示至少有一科及格, FALSE表示各科都不及格。
从这个实例看出, 使用AND函数, 可以做“至少”这一类判断。操作的流程一般分三步:输入原始数据, 判断第一组数据, 复制第一个判断。关键是“判断第一组数据”, 这一步又可以分为几小步进行:确定判断结果存放的位置, 打开OR函数对话框, 输入参数, 单击“确定”。
3 IF函数
这个函数的功能是做“如果……, 那么……, 否则……”这一类判断。
3.1 任务
设计一个计算机自动改卷系统。试卷由20道判断题、20道单选题、20道多选题、20道问答题组成, 其中第一道判断题是“4的平方根是2”。答对记1分, 打错记0分。认为此命题真, 就在题目后面的单元格里输入“对”字, 否则输入“错”字。要求, 学生输入答案后, 计算机要对学生的回答做出判断, 并显示得分情况。
3.2 分析
就第一道判断题而言, 可以修改为“如果回答正确, 那么记1分, 否则记0分”。这符合IF函数的要求。因此, 可以使用IF函数完成这一任务。
3.3 思路
这项任务涉及到判断题的序列号、判断题、学生的判断、教师的评判和计分。教师是先对学生的回答进行评判, 而后才给分。评判和给分是一致的, 根据给分的情况, 可以看出教师的评判。所以, 在设计考试系统的时候, 可以不考虑教师的评判, 只考虑记分。我们可以在第1行输入项目名称, 从第2行起, 在A列输入题目的序列号, B列输入判断题, C列留给学生填写答案, D列显示得分情况。
判断学生的回答是否正确, 其实, 就是将学生的答案与标准答案做比较。学生的答案与标准答案一致, 给1分, 否则计0分。所以, 还需要有一列输入标准答案。不妨把标准答案放到E列。D列和E列, 设计完成之后, 都要隐藏的。不然, 学生会根据显示的分数判断自己是否作对, 而重新做题。
因为每一道题目的给分都是一样的, 所以可以对第一题进行判断, 再把它的判断公式复制到其他题。
4的平方根有两个, 即2和-2。所以, “4的平方根是2”的说法是错误的。
3.4 操作
(1) 输入试题和标准答案
输入的结果如图4。
(2) 对第一道题做出判断
D2单元格———“公式”菜单———“逻辑”按钮———IF函数, 打开IF函数对话框, 在logical-test后面输入“C2=E2”, value-iftrue后面输入“1”, value-if-false后面输入“0”, 点“确定”按钮。
(3) 复制第一道题目的判断题公式
选中D2, 向下拖动单元格D2右下角的控制柄。
(4) 隐藏标准答案和得分列
选中D列和E列, 在选区击右键, 选择“隐藏”命令。
学生只要在C列输入答案并确认, 计算机便根据设定的要求, 做出判断, 并把得分情况写进E列。教师要查看学生得分情况, 只需要取消这两列的隐藏就可以了。
另外, 还可以使用求和函数, 统计整个试卷的得分。
4 NOT函数
这是一个对参数求反的函数。如果提供的参数是真 (TRUE) 的, 那么计算的结果是假 (FALSE) 的。反过来, 如果提供的参数是假的, 那么计算的结果是真的。计算的结果只有两种情况, 这样就把被计算的事物划分成了两个类型。从表面上看, 这个函数是判断命题的真假, 其实是对一批参数进行分类。所以, 我们既可以把计算的结果看成是命题的真假, 也可以把计算的结果看成类别符号。
例如, 2013年国家对民办教师进行生活补贴。某市委为了预算将要投入的资金, 责令民调队对民办教师的数量、任职时间等与民办教师有关的信息进行调研。调研结果表明:该市大约有2万名民办教师, 平均任职年限为8.3年。然而, 当市委领导看了教育局报上来的统计数据, 大吃一惊。全市竟然有10万多民办教师, 平均任教年限为15.6年。而且, 都有证人证言。怎样才能把虚报瞒报的人找出来呢?
有人想了个主意, 计算表上民办教师的任教年龄。文件要求, 补贴文化革命以前的民办教师。文化革命以前, 教育部有文件规定:儿童8岁入学, 小学的学制为6年。小学毕业的最低年龄是14岁。作为教师, 最低应该是小学毕业。参加教育工作时的年龄低于14岁的, 一定有假。所以, 只要计算出报表上任教的年龄, 就可以剔除一部分虚报瞒报人。
4.1 任务
从民办教师统计表中找出虚报瞒报任职年岁的人。
4.2 分析
找虚报瞒报的人, 其实就是把民办教师统计表中的人分为两个类别, 任教时的岁数小于14的和大于等于14的。
在民办教师统计表上, 没有教师任教时的岁数, 但有民办教师出生年份和开始任民办教师的年份。如下表所示:
我们可以根据“开始任教年份”与“出生年份”的差是否小于14, 把民办教师分为两类。小于14, 计算结果为FALSE, 大于等于14计算结果为TRUE。并把使用逻辑函数NOT计算的结果写进D列相应的单元格。我们根据D列的参数, 就可以知道谁在弄虚作假。
4.3 思路
可以先在D1输入“分类”, 在D2单元格使用NOT函数计算第一个人任教年龄的真假。再用拖放控制柄的方法, 计算其他人任教年龄的真假。假设“开始任教年份-出生年份<14”为真命题, “开始任教年份-出生年份>=14”为假命题。函数NOT的参数设为“开始任教年份-出生年份<14”, 则在D列显示FALSE (假) 。
最后, 使用IF函数, 把“FALSE”转化成“假”写进E列。
4.4 操作
单击D1, 输入“分类”, 单击D2选“公式”菜单单击“逻辑”按钮选“NOT”函数, 打开NOT函数对话框, 在logical后面输入“C3-B3<14”, 单击“确定”按钮。如图5。
单击D2, 向下拖动D2右下角的控制点。使用IF函数把TRUE转化为空, FLASE转化为“虚假”, 并把计算的结果填写在E列。得到结果如下表所示:
这是一个真实案例。为了维护公民的人身权益, 在引用这个实例的时候, 只截取了某市“原民办教师发放补贴名单”的一小段, 而且修改了教师的姓名。
使用这个函数大致分两大步: (1) 制作第一个人的判断; (2) 复制第一个人的判断公式到其他人。
制作第一个人的判断分三步: (1) 确定判断结果显示的位置; (2) 打开NOT函数对话框; (3) 输入判断条件, 并予以确认。
使用逻辑函数判断一组参数, 并不一定比人脑快。但是, 使用它判断千万组参数, 其速度比人快得多, 而且不会出现因人脑疲乏做出错误判断的现象。
作者简介:
关键词：学籍管理 Excel函数 应用
学籍管理是每个学校都必须进行的一项繁琐事务。随着计算机的广泛应用和Office办公自动化软件的使用，目前学生的学籍信息一般都使用电子文档存储，以便上交到上级管理部门审核。通常，学籍管理员会根据学籍信息特点和相关要求使用 Excel软件输入和存储管理信息，日常的学籍管理工作，如注册、修改、添加以及提取等，需要经过多次重复操作才能完成。如何让这些工作变得更轻松、省时？可以运用Excel提供的内置函数来帮助提高工作效率。
Excel软件最突出的特点就是具有数据自动处理能力，利用公式计算、公式的复制以及公式的重新计算功能，可以使复杂的事情简单化，特别是Excel提供的内置函数能更方便快捷地完成相同的工作。函数是Excel中预定义好的公式，共有200多个。在学籍管理的日常事务中，灵活选用其中内置的函数，实现对学籍信息的输入和日常处理，可以达到事半功倍的效果。
下面笔者从学籍注册、技能抽考及成绩评定这三个方面介绍Excel部分函数的功能与应用。
一、学籍注册
图1是从各班级收集的纸质数据表。
图1
根据Excel软件所具有的数据处理功能，从上图分析得出，学生的“身份证号”字段中包含了“性别”和“出生年月日”的信息，这样“性别” 和“出生年月日”两列的数据就不用再输入。
首先，新建Excel工作簿，在Sheet1工作表中创建一个电子注册表，在表中输入标题行内容：班级学号、姓名、身份证号、性别、出生日期、专业、班级等。在数据区域输入如图2所示的数据即可，其中A列、F列和G列剩余的数据采用填充柄的复制功能完成。
图2
第二，利用Mid函数（字符串截取函数）从“身份证号”中计算出“出生年月日”。Mid函数的功能是返回文本字符串中从指定位置开始的特定数目的字符。身份证号中第7位至第14位这8个数据代表学生的出生年月日。
例如：计算图2的1号同学的“出生年月日”，在E2结果单元格中，输入：
=MID（C2，7，8）
注：C2为引用身份证号码（数字字符）所在单元格，E列其余的学生的信息依然使用填充柄的复制功能完成。
第三，利用Mod函数（取余函数）计算出第17位数是奇数还是偶数。Mod函数的功能是返回两数相除的余数。身份证号的第17位代表了一个人的性别是“男”还是“女”，分别用奇数、偶数表示，奇数1、3、5、7、9表示男性，偶数0、2、4、6、8表示女性。即：
=MOD（第17位数，2）
第四，利用函数嵌套从“身份证号”中计算出“性别”。函数的嵌套是指一个函数中套入了另一个函数，函数的值作为另一个函数的参数。函数嵌套的层数最多可以达到7层。
由此得出：先用Mid取中间字符串函数，取出第17位的数字字符；再用数学函数Mod取余函数，计算该数字的奇偶性；最后是逻辑函数If条件函数，判断出是“男”性还是“女”性。
例如：计算图2的1号同学的性别，在D2结果单元格中，输入：
=IF（MOD（MID（C2，17，1），2）=0，"女"，"男"）
二、技能抽考
每年的技能抽考是湖北省检测职业院校学生学习情况的规定项目。出于公平公正的原则，笔者采用随机抽测学号的方式进行。例如一个专业注册60人，抽测10%，即随机抽取6人的学号。在Excel中随机函数Rand（ ）可实现这一功能。使用Rand（ ）1次，然后对生成的数进行增倍、取整等处理，即可抽出一个学号，再使用填充柄的复制功能，6单元格即可搞定。
随机函数Rand（ ）的功能是：返回大于等于0及小于1的均匀分布随机数，即产生出[0，1）之间的数：0或者一些小数。从抽测的学号分析，单独使用的Rand（ ）函数所产生的是[0，1）之间的数，不符合“学号”要求（以“学号”为1、2、……60整数为例，没有0），故采用以下方法解决此问题。
首先，随机函数乘以合适的数值型系数A，随机数将产生于0到A区间，即[0，A）。如Rand（ ）*60 函数，将产生大于等于0且小于60的一个随机数。因此通过乘以不同大小的系数，可以产生不同区间的随机数。
然后，配合取整函数，保证随机数为整数型数据项。例如Int（Rand（ ）*60）函数功能将产生0至60间的随机整数，即[0，60）的随机整数。包含了0，而不包含60。由于学号是从1到60号，在上面的基础上加上数字1即可满足抽取的要求。
举例：在“抽测学号”第一个结果单元格中，输入：
=INT（RAND（ ）*60）+1
三、成绩评定
近几年来，湖北省要求职业院校的学生取得英语、计算机等级证才能获得毕业证。如果学生英语、计算机两者有一项成绩不合格（无等级证），则不颁发毕业证。根据这一情况，可以把符合条件的标记为“发”，不符合条件的不作标记。
现假设学生英语、计算机考试状态如图3所示。
图3
从上表分析可知，每一种成绩有三种状态：“优秀”“合格”和“不合格”。
又已知两门成绩中只要有一门标记为“不合格”，即不发毕业证，其余情况均发。可以先用OR（ ）逻辑或函数来判断。OR（ ）函数的功能是任一参数为TRUE时，则结果为TRUE。再用IF嵌套。
举例：在J2单元格中，输入：
=IF（（OR（H2="不合格"，I2="不合格"）），""，"发" ）
计算结果如图4所示。
图4
综上所述，我们不难发现，Excel的内置函数具有强大功能。只要灵活运用Excel函数，发挥其作用，学籍管理工作效率将会大幅提高。
参考文献：
[1]叶亚平.计算机应用基础[M].北京：原子能出版社，2009.
[2]周和平.对口招生考试教程（计算机应用类）[M].北京：国防科技大学出版社，2007.
（作者单位：湖北工业大学）endprint
摘 要：本文以学籍注册、技能抽考及成绩评定三项学籍管理日常事务为例，探讨了如何通过灵活选用Excel内置的函数，实现对学籍信息高效管理。
关键词：学籍管理 Excel函数 应用
学籍管理是每个学校都必须进行的一项繁琐事务。随着计算机的广泛应用和Office办公自动化软件的使用，目前学生的学籍信息一般都使用电子文档存储，以便上交到上级管理部门审核。通常，学籍管理员会根据学籍信息特点和相关要求使用 Excel软件输入和存储管理信息，日常的学籍管理工作，如注册、修改、添加以及提取等，需要经过多次重复操作才能完成。如何让这些工作变得更轻松、省时？可以运用Excel提供的内置函数来帮助提高工作效率。
Excel软件最突出的特点就是具有数据自动处理能力，利用公式计算、公式的复制以及公式的重新计算功能，可以使复杂的事情简单化，特别是Excel提供的内置函数能更方便快捷地完成相同的工作。函数是Excel中预定义好的公式，共有200多个。在学籍管理的日常事务中，灵活选用其中内置的函数，实现对学籍信息的输入和日常处理，可以达到事半功倍的效果。
下面笔者从学籍注册、技能抽考及成绩评定这三个方面介绍Excel部分函数的功能与应用。
一、学籍注册
图1是从各班级收集的纸质数据表。
图1
根据Excel软件所具有的数据处理功能，从上图分析得出，学生的“身份证号”字段中包含了“性别”和“出生年月日”的信息，这样“性别” 和“出生年月日”两列的数据就不用再输入。
首先，新建Excel工作簿，在Sheet1工作表中创建一个电子注册表，在表中输入标题行内容：班级学号、姓名、身份证号、性别、出生日期、专业、班级等。在数据区域输入如图2所示的数据即可，其中A列、F列和G列剩余的数据采用填充柄的复制功能完成。
图2
第二，利用Mid函数（字符串截取函数）从“身份证号”中计算出“出生年月日”。Mid函数的功能是返回文本字符串中从指定位置开始的特定数目的字符。身份证号中第7位至第14位这8个数据代表学生的出生年月日。
例如：计算图2的1号同学的“出生年月日”，在E2结果单元格中，输入：
=MID（C2，7，8）
注：C2为引用身份证号码（数字字符）所在单元格，E列其余的学生的信息依然使用填充柄的复制功能完成。
第三，利用Mod函数（取余函数）计算出第17位数是奇数还是偶数。Mod函数的功能是返回两数相除的余数。身份证号的第17位代表了一个人的性别是“男”还是“女”，分别用奇数、偶数表示，奇数1、3、5、7、9表示男性，偶数0、2、4、6、8表示女性。即：
=MOD（第17位数，2）
第四，利用函数嵌套从“身份证号”中计算出“性别”。函数的嵌套是指一个函数中套入了另一个函数，函数的值作为另一个函数的参数。函数嵌套的层数最多可以达到7层。
由此得出：先用Mid取中间字符串函数，取出第17位的数字字符；再用数学函数Mod取余函数，计算该数字的奇偶性；最后是逻辑函数If条件函数，判断出是“男”性还是“女”性。
例如：计算图2的1号同学的性别，在D2结果单元格中，输入：
=IF（MOD（MID（C2，17，1），2）=0，"女"，"男"）
二、技能抽考
每年的技能抽考是湖北省检测职业院校学生学习情况的规定项目。出于公平公正的原则，笔者采用随机抽测学号的方式进行。例如一个专业注册60人，抽测10%，即随机抽取6人的学号。在Excel中随机函数Rand（ ）可实现这一功能。使用Rand（ ）1次，然后对生成的数进行增倍、取整等处理，即可抽出一个学号，再使用填充柄的复制功能，6单元格即可搞定。
随机函数Rand（ ）的功能是：返回大于等于0及小于1的均匀分布随机数，即产生出[0，1）之间的数：0或者一些小数。从抽测的学号分析，单独使用的Rand（ ）函数所产生的是[0，1）之间的数，不符合“学号”要求（以“学号”为1、2、……60整数为例，没有0），故采用以下方法解决此问题。
首先，随机函数乘以合适的数值型系数A，随机数将产生于0到A区间，即[0，A）。如Rand（ ）*60 函数，将产生大于等于0且小于60的一个随机数。因此通过乘以不同大小的系数，可以产生不同区间的随机数。
然后，配合取整函数，保证随机数为整数型数据项。例如Int（Rand（ ）*60）函数功能将产生0至60间的随机整数，即[0，60）的随机整数。包含了0，而不包含60。由于学号是从1到60号，在上面的基础上加上数字1即可满足抽取的要求。
举例：在“抽测学号”第一个结果单元格中，输入：
=INT（RAND（ ）*60）+1
三、成绩评定
近几年来，湖北省要求职业院校的学生取得英语、计算机等级证才能获得毕业证。如果学生英语、计算机两者有一项成绩不合格（无等级证），则不颁发毕业证。根据这一情况，可以把符合条件的标记为“发”，不符合条件的不作标记。
现假设学生英语、计算机考试状态如图3所示。
图3
从上表分析可知，每一种成绩有三种状态：“优秀”“合格”和“不合格”。
又已知两门成绩中只要有一门标记为“不合格”，即不发毕业证，其余情况均发。可以先用OR（ ）逻辑或函数来判断。OR（ ）函数的功能是任一参数为TRUE时，则结果为TRUE。再用IF嵌套。
举例：在J2单元格中，输入：
=IF（（OR（H2="不合格"，I2="不合格"）），""，"发" ）
计算结果如图4所示。
图4
综上所述，我们不难发现，Excel的内置函数具有强大功能。只要灵活运用Excel函数，发挥其作用，学籍管理工作效率将会大幅提高。
参考文献：
[1]叶亚平.计算机应用基础[M].北京：原子能出版社，2009.
[2]周和平.对口招生考试教程（计算机应用类）[M].北京：国防科技大学出版社，2007.
（作者单位：湖北工业大学）endprint
摘 要：本文以学籍注册、技能抽考及成绩评定三项学籍管理日常事务为例，探讨了如何通过灵活选用Excel内置的函数，实现对学籍信息高效管理。
关键词：学籍管理 Excel函数 应用
学籍管理是每个学校都必须进行的一项繁琐事务。随着计算机的广泛应用和Office办公自动化软件的使用，目前学生的学籍信息一般都使用电子文档存储，以便上交到上级管理部门审核。通常，学籍管理员会根据学籍信息特点和相关要求使用 Excel软件输入和存储管理信息，日常的学籍管理工作，如注册、修改、添加以及提取等，需要经过多次重复操作才能完成。如何让这些工作变得更轻松、省时？可以运用Excel提供的内置函数来帮助提高工作效率。
Excel软件最突出的特点就是具有数据自动处理能力，利用公式计算、公式的复制以及公式的重新计算功能，可以使复杂的事情简单化，特别是Excel提供的内置函数能更方便快捷地完成相同的工作。函数是Excel中预定义好的公式，共有200多个。在学籍管理的日常事务中，灵活选用其中内置的函数，实现对学籍信息的输入和日常处理，可以达到事半功倍的效果。
下面笔者从学籍注册、技能抽考及成绩评定这三个方面介绍Excel部分函数的功能与应用。
一、学籍注册
图1是从各班级收集的纸质数据表。
图1
根据Excel软件所具有的数据处理功能，从上图分析得出，学生的“身份证号”字段中包含了“性别”和“出生年月日”的信息，这样“性别” 和“出生年月日”两列的数据就不用再输入。
首先，新建Excel工作簿，在Sheet1工作表中创建一个电子注册表，在表中输入标题行内容：班级学号、姓名、身份证号、性别、出生日期、专业、班级等。在数据区域输入如图2所示的数据即可，其中A列、F列和G列剩余的数据采用填充柄的复制功能完成。
图2
第二，利用Mid函数（字符串截取函数）从“身份证号”中计算出“出生年月日”。Mid函数的功能是返回文本字符串中从指定位置开始的特定数目的字符。身份证号中第7位至第14位这8个数据代表学生的出生年月日。
例如：计算图2的1号同学的“出生年月日”，在E2结果单元格中，输入：
=MID（C2，7，8）
注：C2为引用身份证号码（数字字符）所在单元格，E列其余的学生的信息依然使用填充柄的复制功能完成。
第三，利用Mod函数（取余函数）计算出第17位数是奇数还是偶数。Mod函数的功能是返回两数相除的余数。身份证号的第17位代表了一个人的性别是“男”还是“女”，分别用奇数、偶数表示，奇数1、3、5、7、9表示男性，偶数0、2、4、6、8表示女性。即：
=MOD（第17位数，2）
第四，利用函数嵌套从“身份证号”中计算出“性别”。函数的嵌套是指一个函数中套入了另一个函数，函数的值作为另一个函数的参数。函数嵌套的层数最多可以达到7层。
由此得出：先用Mid取中间字符串函数，取出第17位的数字字符；再用数学函数Mod取余函数，计算该数字的奇偶性；最后是逻辑函数If条件函数，判断出是“男”性还是“女”性。
例如：计算图2的1号同学的性别，在D2结果单元格中，输入：
=IF（MOD（MID（C2，17，1），2）=0，"女"，"男"）
二、技能抽考
每年的技能抽考是湖北省检测职业院校学生学习情况的规定项目。出于公平公正的原则，笔者采用随机抽测学号的方式进行。例如一个专业注册60人，抽测10%，即随机抽取6人的学号。在Excel中随机函数Rand（ ）可实现这一功能。使用Rand（ ）1次，然后对生成的数进行增倍、取整等处理，即可抽出一个学号，再使用填充柄的复制功能，6单元格即可搞定。
随机函数Rand（ ）的功能是：返回大于等于0及小于1的均匀分布随机数，即产生出[0，1）之间的数：0或者一些小数。从抽测的学号分析，单独使用的Rand（ ）函数所产生的是[0，1）之间的数，不符合“学号”要求（以“学号”为1、2、……60整数为例，没有0），故采用以下方法解决此问题。
首先，随机函数乘以合适的数值型系数A，随机数将产生于0到A区间，即[0，A）。如Rand（ ）*60 函数，将产生大于等于0且小于60的一个随机数。因此通过乘以不同大小的系数，可以产生不同区间的随机数。
然后，配合取整函数，保证随机数为整数型数据项。例如Int（Rand（ ）*60）函数功能将产生0至60间的随机整数，即[0，60）的随机整数。包含了0，而不包含60。由于学号是从1到60号，在上面的基础上加上数字1即可满足抽取的要求。
举例：在“抽测学号”第一个结果单元格中，输入：
=INT（RAND（ ）*60）+1
三、成绩评定
近几年来，湖北省要求职业院校的学生取得英语、计算机等级证才能获得毕业证。如果学生英语、计算机两者有一项成绩不合格（无等级证），则不颁发毕业证。根据这一情况，可以把符合条件的标记为“发”，不符合条件的不作标记。
现假设学生英语、计算机考试状态如图3所示。
图3
从上表分析可知，每一种成绩有三种状态：“优秀”“合格”和“不合格”。
又已知两门成绩中只要有一门标记为“不合格”，即不发毕业证，其余情况均发。可以先用OR（ ）逻辑或函数来判断。OR（ ）函数的功能是任一参数为TRUE时，则结果为TRUE。再用IF嵌套。
举例：在J2单元格中，输入：
=IF（（OR（H2="不合格"，I2="不合格"）），""，"发" ）
计算结果如图4所示。
图4
综上所述，我们不难发现，Excel的内置函数具有强大功能。只要灵活运用Excel函数，发挥其作用，学籍管理工作效率将会大幅提高。
参考文献：
[1]叶亚平.计算机应用基础[M].北京：原子能出版社，2009.
[2]周和平.对口招生考试教程（计算机应用类）[M].北京：国防科技大学出版社，2007.
(1)函数分解
FREQUENCY函数以一列垂直数组返回某个区域中数据的频率分布。
语法:FREQUENCY(data_array,bins_array)
Data_array为一数组或对一组数值的引用，用来计算频率。如果data_array中不包含任何数值，函数FREQUENCY返回零数组;Bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回data_array中元素的个数，
(2)实例分析
首先在工作表中找到空白的I列(或其他列)，自I2单元格开始依次输入20、25、30、35、40...60，分别表示统计年龄小于20、20至25之间、25至30之间等的人数。然后在该列旁边选中相同个数的单元格，例如J2:J10准备存放各年龄段的统计结果。然后在编辑栏输入公式“=FREQUENCY(YEAR(TODAY)-YEAR(E2:E600),I2:I10)”，按下Ctrl+Shift+Enter组合键即可在选中单元格中看到计算结果。其中位于J2单元格中的结果表示年龄小于20岁的职工人数，J3单元格中的数值表示年龄在20至25之间的职工人数等。
1.AND
用途：所有参数的逻辑值为真时返回TRUE(真);只要有一个参数的逻辑值为假，则返回FALSE(假)。
语法：AND(logical1，logical2，…)。
参数：Logical1，logical2，…为待检验的1～30个逻辑表达式，它们的结论或为TRUE(真)或为FALSE(假)。参数必须是逻辑值或者包含逻辑值的数组或引用，如果数组或引用内含有文字或空白单元格，则忽略它的值。如果指定的单元格区域内包括非逻辑值，AND将返回错误值#value!。
实例：如果A1=2、A=6，那么公式“=AND(A1A2)”返回FALSE。如果B4=104，那么公式“=IF(AND(1
2.FALSE
用途：返回逻辑值FALSE。
语法：FALSE
参数：该函数不需要参数
实例：如果在A1单元格内输入公式“=FALSE()”，回车后即可返回FALSE。若在单元格或公式中输入文字FALSE，Excel会自动将它解释成逻辑值FALSE。
3.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也可以是一个表达式。
实例：公式“=IF(C2>=85，”A“，IF(C2>=70，”B“，IF(C2>=60，”C“，IF(C2<60，”D"))))”，其中第二个IF语句同时也是第一个IF语句的参数。同样，第三个IF语句是第二个IF语句的参数，以此类推。例如，若第一个逻辑判断表达式C2>=85成立，则D2单元格被赋值“A”;如果第一个逻辑判断表达式C2>=85不成立，则计算第二个IF语句“IF(C2>=70”;以此类推直至计算结束，该函数广泛用于需要进行逻辑判断的场合。
4.NOT
用途：求出一个逻辑值或逻辑表达式的相反值，
如果您要确保一个逻辑值等于其相反值，就应该使用NOT函数。
语法：NOT(logical)
参数：Logical是一个可以得出TRUE或FALSE结论的逻辑值或逻辑表达式。如果逻辑值或表达式的结果为FALSE，则NOT函数返回TRUE;如果逻辑值或表达式的结果为TRUE，那么NOT函数返回的结果为FALSE。
实例：如果A1=6、A2=8，那么公式“=NOT(A1
5.OR
用途：所有参数中的任意一个逻辑值为真时即返回TRUE(真)。
语法：OR(logical1，logical2，...)
参数：Logical1，logical2，...是需要进行检验的1至30个逻辑表达式，其结论分别为TRUE或FALSE。如果数组或引用的参数包含文本、数字或空白单元格，它们将被忽略。如果指定的区域中不包含逻辑值，OR函数将返回错误#value!。
实例：如果A1=6、A2=8，则公式“=OR(A1+A2>A2，A1=A2)”返回TRUE;而公式“=OR(A1>A2，A1=A2)”返回FALSE。
用途：按指定位数四舍五入某个数字。
语法：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。
42.ROUNDDOWN
用途：按绝对值减小的方向舍入某一数字。
语法：ROUNDDOWN(number，num_digits)
参数：Number是需要向下舍入的任意实数，Num_digits指定计算的小数位数。
注意:ROUNDDOWN函数和ROUND函数的用途相似，不同之处是ROUNDDOWN函数总是向下舍入数字。
实例：如果A1=65.251，则公式“=ROUNDDOWN(A1，0)”返回65;=ROUNDDOWN(A1，2)返回65.25;=ROUNDDOWN(3.14159，3)返回3.141;=ROUNDDOWN(-3.14159，1)返回-3.1;=ROUNDDOWN(31415.92654，-2)返回31400。
43.ROUNDUP
用途：按绝对值增大的方向舍入一个数字。
语法：ROUNDUP(number，num_digits)
参数：Number为需要舍入的任意实数，Num_digits指定舍入的数字位数。
注意:如果num_digits为0或省略，则将数字向上舍入到最接近的整数。如果num_digits小于0，则将数字向上舍入到小数点左边的相应位数。
实例：如果A1=65.251，则公式“=ROUNDUP(A1，0)”返回66;=ROUNDUP(A1，1)返回66;=ROUNDUP(A1，2)返回65.26;=ROUNDUP(-3.14159，1)返回-3.2;=ROUNDUP(31415.92654，-2)返回31500。
44.SERIESSUM
用途：返回幂级数的和。
语法：SERIESSUM(x，n，m，coefficients)
参数：X幂级数的输入值，N为x的首项乘幂，M为级数中每一项的乘幂n的步长增加值，Coefficients为一系列与x各级乘幂相乘的系数。Coefficients的值决定了幂级数的项数。
注意:SERIESSUM函数只有加载“分析工具库”以后方能使用。
实例：如果单元格A1=65.25，则公式“=SERIESSUM(A1，3，2，6)”返回1666835.719。
45.SIGN
用途：返回数字的符号。正数返回1，零返回0，负数时返回-1。
语法：SIGN(number)
参数：Number是需要返回符号的任意实数。
实例：如果A1=65.25，则公式“=SIGN(A1)”返回1;=SIGN(6-12)返回-1;=SIGN(9-9)返回0。
46.SIN
用途：返回某一角度的正弦值。
语法：SIN(number)
参数：Number是待求正弦值的一个角度(采用弧度单位)，如果它的单位是度，则必须乘以PI()/180转换为弧度。
实例：如果A1=60，则公式“=SIN(A1*PI()/180)”返回0.866，即60度角的正弦值。
47.SINH
用途：返回任意实数的双曲正弦值。
语法：SINH(number)
参数：Number为任意实数。
实例：公式“=SINH(10)”返回11013.23287，=SINH(-6)返回-201.7131574。
48.SQRT
用途：返回某一正数的算术平方根。
语法：SQRT(number)
参数：Number为需要求平方根的一个正数。
实例：如果A1=81，则公式“=SQRT(A1)”返回9;=SQRT(4+12)返回6。
49.SQRTPI
用途：返回一个正实数与π的乘积的平方根。
语法：SQRTPI(number)
参数：Number是用来与π相乘的正实数。
注意:SQRTPI函数只有加载“分析工具库”以后方能使用。如果参数number<0，则函数SQRTPI返回错误值#NUM!。
实例：公式“=SQRTPI(1)”返回1.772454，=SQRTPI(2)返回2.506628。
50.SUBTOTAL
用途：返回数据清单或数据库中的分类汇总。如果用户使用“数据”菜单中的“分类汇总”命令创建了分类汇总数据清单，即可编辑SUBTOTAL函数对其进行修改。
语法：SUBTOTAL(function_num，ref1，ref2…)
参数：Function_num为1到11之间的自然数，用来指定分类汇总计算使用的函数(1是AVERAGE;2是COUNT;3是COUNTA;4是MAX;5是MIN;6是PRODUCT;7是STDEV;8是STDEVP;9是SUM;10是VAR;11是VARP)。Ref1、ref2…则是需要分类汇总的1到29个区域或引用。
实例：如果A1=
1、A2=
2、A3=3，则公式“=SUBTOTAL(9，A1:A3)”将使用SUM函数对“A1:A3”区域进行分类汇总，其结果为6。
51.SUM
用途：返回某一单元格区域中所有数字之和。
语法：SUM(number1，number2，...)。
参数：Number1，number2，...为1到30个需要求和的数值(包括逻辑值及文本表达式)、区域或引用。
注意:参数表中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为
1、文本被转换为数字。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。
实例：如果A1=
1、A2=
2、A3=3，则公式“=SUM(A1:A3)”返回6;=SUM(“3”，2，TRUE)返回6，因为“3”被转换成数字3，而逻辑值TRUE被转换成数字1。
52.SUMIF
用途：根据指定条件对若干单元格、区域或引用求和。
语法：SUMIF(range，criteria，sum_range)
参数：Range为用于条件判断的单元格区域，Criteria是由数字、逻辑表达式等组成的判定条件，Sum_range为需要求和的单元格、区域或引用。
实例：某单位统计工资报表中职称为“中级”的员工工资总额。假设工资总额存放在工作表的F列，员工职称存放在工作表B列。则公式为“=SUMIF(B1:B1000，“中级”，F1:F1000)”，其中“B1:B1000”为提供逻辑判断依据的单元格区域，“中级”为判断条件，就是仅仅统计B1:B1000区域中职称为“中级”的单元格，F1:F1000为实际求和的单元格区域。
53.SUMPRODUCT
用途：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。
语法：SUMPRODUCT(array1，array2，array3，...)
参数：Array1，array2，array3，...为2至30个数组，其相应元素需要进行相乘并求和。
实例：公式“=SUMPRODUCT({3，4;8，6;1，9}，{2，7;6，7;5，3})”的计算结果是156。
54.SUMSQ
用途：返回所有参数的平方和。
语法：SUMSQ(number1，number2，...)
参数：Number1，number2，...为1到30个需要求平方和的参数，它可以是数值、区域、引用或数组。
实例：如果A1=
1、A2=
2、A3=3，则公式“=SUMSQ(A1:A3)返回14(即12+22+32=14)。
55.SUMX2MY2
用途：返回两数组中对应数值的平方差之和。
语法：SUMX2MY2(array_x，array_y)
参数：Array_x为第一个数组或数值区域。Array_y为第二个数组或数值区域。
实例：如果A1=
1、A2=
2、A3=
3、B1=
4、B2=
5、B3=6，则公式“=SUMX2MY2(A1:A3，B1:B3)”返回-63。
56.SUMX2PY2
用途：返回两数组中对应数值的平方和的总和，此类运算在统计中经常遇到。
语法：SUMX2PY2(array_x，array_y)
参数：Array_x为第一个数组或数值区域，Array_y为第二个数组或数值区域。
实例：如果A1=
1、A2=
2、A3=
3、B1=
4、B2=
5、B3=6，则公式“=SUMX2PY2(A1:A3，B1:B3)”返回91。
57.SUMXMY2
用途：返回两数组中对应数值之差的平方和。
语法：SUMXMY2(array_x，array_y)
参数：Array_x为第一个数组或数值区域。Array_y为第二个数组或数值区域。
实例：如果A1=
1、A2=
2、A3=
3、B1=
4、B2=
5、B3=6，则公式“=SUMXMY2(A1:A3，B1:B3)”返回27。
58.TAN
用途：返回某一角度的正切值。
语法：TAN(number)
参数：Number为需要求正切的角度，以弧度表示。如果参数的单位是度，可以乘以P1()/180转换为弧度。
实例：如果A1=60，则公式“=TAN(A1*PI()/180)”返回1.732050808;TAN(1)返回1.557407725。
59.TANH
用途：返回任意实数的双曲正切值。
语法：TANH(number)
参数：Number为任意实数。
实例：如果A1=60，则公式“=TANH(A1)”返回1，=TANH(0.5)返回0.462117。
60.TRUNC
用途：将数字的小数部分截去，返回整数。
语法：TRUNC(number，num_digits)
参数：Number是需要截去小数部分的数字，Num_digits则指定保留小数的精度(几位小数)。
注意:TRUNC函数可以按需要截取数字的小数部分，而INT函数则将数字向下舍入到最接近的整数。INT和TRUNC函数在处理负数时有所不同:TRUNC(-4.3)返回-4，而INT(-4.3)返回-5。
实例：如果A1=78.652，则公式“=TRUNC(A1，1)”返回78.6，=TRUNC(A1，2)返回78.65，=TRUNC(-8.963，2)返回–8.96。1.AVEDEV
用途：返回一组数据与其平均值的绝对偏差的平均值，该函数可以评测数据(例如学生的某科考试成绩)的离散度。
语法：AVEDEV(number1，number2，...)
参数：Number1、number2、...是用来计算绝对偏差平均值的一组参数，其个数可以在1～30个之间。
实例：如果A1=79、A2=62、A3=
45、A4=90、A5=25，则公式“=AVEDEV(A1:A5)”返回20.16。
2.AVERAGE
用途：计算所有参数的算术平均值。
语法：AVERAGE(number1，number2，...)。
参数：Number1、number2、...是要计算平均值的1～30个参数。
实例：如果A1:A5区域命名为分数，其中的数值分别为100、70、92、47和82，则公式“=AVERAGE(分数)”返回78.2。
3.AVERAGEA
用途：计算参数清单中数值的平均值。它与AVERAGE函数的区别在于不仅数字，而且文本和逻辑值(如TRUE和FALSE)也参与计算。
语法：AVERAGEA(value1，value2，...)
参数：value1、value2、...为需要计算平均值的1至30个单元格、单元格区域或数值。
实例：如果A1=76、A2=85、A3=TRUE，则公式“=AVERAGEA(A1:A3)”返回54(即76+85+1/3=54)。
4.BETADIST
用途：返回Beta分布累积函数的函数值。Beta分布累积函数通常用于研究样本集合中某些事物的发生和变化情况。例如，人们一天中看电视的时间比率。
语法：BETADIST(x，alpha，beta，A，B)
参数：X用来进行函数计算的值，须居于可选性上下界(A和B)之间。Alpha分布的参数。Beta分布的参数。A是数值x所属区间的可选下界，B是数值x所属区间的可选上界。
实例：公式“=BETADIST(2，8，10，1，3)”返回0.685470581。
5.BETAINV
用途：返回beta分布累积函数的逆函数值。即，如果probability=BETADIST(x，...)，则BETAINV(probability，...)=x。beta分布累积函数可用于项目设计，在给出期望的完成时间和变化参数后，模拟可能的完成时间。
语法：BETAINV(probability，alpha，beta，A，B)
参数：Probability为Beta分布的概率值，Alpha分布的参数，Beta分布的参数，A数值x所属区间的可选下界，B数值x所属区间的可选上界。
实例：公式“=BETAINV(0.685470581，8，10，1，3)”返回2。
6.BINOMDIST
用途：返回一元二项式分布的概率值。BINOMDIST函数适用于固定次数的独立实验，实验的结果只包含成功或失败二种情况，且成功的概率在实验期间固定不变。例如，它可以计算掷10次硬币时正面朝上6次的概率。
语法：BINOMDIST(number_s，trials，probability_s，cumulative)
参数：Number_s为实验成功的次数，Trials为独立实验的次数，Probability_s为一次实验中成功的概率，Cumulative是一个逻辑值，用于确定函数的形式。如果cumulative为TRUE，则BINOMDIST函数返回累积分布函数，即至多number_s次成功的概率;如果为FALSE，返回概率密度函数，即number_s次成功的概率。
实例：抛硬币的结果不是正面就是反面，第一次抛硬币为正面的概率是0.5。则掷硬币10次中6次的计算公式为“=BINOMDIST(6，10，0.5，FALSE)”，计算的结果等于0.205078
7.CHIDIST
用途：返回c2分布的单尾概率。c2分布与c2检验相关。使用c2检验可以比较观察值和期望值。例如，某项遗传学实验假设下一代植物将呈现出某一组颜色。使用此函数比较观测结果和期望值，可以确定初始假设是否有效。
语法：CHIDIST(x，degrees_freedom)
参数：X是用来计算c2分布单尾概率的数值，Degrees_freedom是自由度。
实例：公式“=CHIDIST(1，2)”的计算结果等于0.606530663。
8.CHIINV
用途：返回c2分布单尾概率的逆函数。如果probability=CHIDIST(x，?)，则CHIINV(probability，?)=x。使用此函数比较观测结果和期望值，可以确定初始假设是否有效。
语法：CHIINV(probability，degrees_freedom)
参数：Probability为c2分布的单尾概率，Degrees_freedom为自由度。
实例：公式“=CHIINV(0.5，2)”返回1.386293564。
9.CHITEST
用途：返回相关性检验值，即返回c2分布的统计值和相应的自由度，可使用c2检验确定假设值是否被实验所证实。
语法：CHITEST(actual_range，expected_range)
参数：Actual_range是包含观察值的数据区域，Expected_range是包含行列汇总的乘积与总计值之比的数据区域。
实例：如果A1=
1、A2=
2、A3=
3、B1=
4、B2=
5、B3=6，则公式“=CHITEST(A1:A3，B1:B3)”返回0.062349477。
10.CONFIDENCE
用途：返回总体平均值的置信区间，它是样本平均值任意一侧的区域。例如，某班学生参加考试，依照给定的置信度，可以确定该次考试的最低和最高分数。
语法：CONFIDENCE(alpha，standard_dev，size)。
参数：Alpha是用于计算置信度(它等于100*(1-alpha)%，如果alpha为0.05，则置信度为95%)的显著水平参数，Standard_dev是数据区域的总体标准偏差，Size为样本容量。
实例：假设样本取自46名学生的考试成绩，他们的平均分为60，总体标准偏差为5分，则平均分在下列区域内的置信度为95%。公式“=CONFIDENCE(0.05，5，46)”返回1.44，即考试成绩为60±1.44分。
11.CORREL
用途：返回单元格区域array1和array2之间的相关系数。它可以确定两个不同事物之间的关系，例如检测学生的物理与数学学习成绩之间是否关联。
语法：CORREL(array1，array2)
参数：Array1第一组数值单元格区域。Array2第二组数值单元格区域。
实例：如果A1=90、A2=86、A3=65、A4=
54、A5=
36、B1=89、B2=83、B3=60、B4=50、B5=32，则公式“=CORREL(A1:A5，B1:B5)”返回0.998876229，可以看出A、B两列数据具有很高的相关性。
12.COUNT
用途：返回数字参数的个数。它可以统计数组或单元格区域中含有数字的单元格个数。
语法：COUNT(value1，value2，...)。
参数：value1，value2，...是包含或引用各种类型数据的参数(1～30个)，其中只有数字类型的数据才能被统计。
实例：如果A1=90、A2=人数、A3=〞〞、A4=
54、A5=36，则公式“=COUNT(A1:A5)”返回3。
13.COUNTA
用途：返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区域中数据项的个数。
语法：COUNTA(value1，value2，...)
说明:value1，value2，...所要计数的值，参数个数为1～30个。在这种情况下的参数可以是任何类型，它们包括空格但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。如果不需要统计逻辑值、文字或错误值，则应该使用COUNT函数。
实例：如果A1=6.28、A2=3.74，其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于2。
14.COUNTBLANK
用途：计算某个单元格区域中空白单元格的数目。
语法：COUNTBLANK(range)
参数：Range为需要计算其中空白单元格数目的区域。
实例：如果A1=88、A2=
55、A3=“"、A4=72、A5=”"，则公式“=COUNTBLANK(A1:A5)”返回2。
15.COUNTIF
用途：计算区域中满足给定条件的单元格的个数。
语法：COUNTIF(range，criteria)
参数：Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。
16.COVAR
用途：返回协方差，即每对数据点的偏差乘积的平均数。利用协方差可以研究两个数据集合之间的关系。
语法：COVAR(array1，array2)
参数：Array1是第一个所含数据为整数的单元格区域，Array2是第二个所含数据为整数的单元格区域。
实例：如果A1=
3、A2=
2、A3=
1、B1=3600、B2=1500、B3=800，则公式“=COVAR(A1:A3，B1:B3)”返回933.3333333。
17.CRITBINOM
用途：返回使累积二项式分布大于等于临界值的最小值，其结果可以用于质量检验。例如决定最多允许出现多少个有缺陷的部件，才可以保证当整个产品在离开装配线时检验合格。
语法：CRITBINOM(trials，probability_s，alpha)
参数：Trials是伯努利实验的次数，Probability_s是一次试验中成功的概率，Alpha是临界值。
实例：公式“=CRITBINOM(10，0.9，0.75)”返回10。
18.DEVSQ
用途：返回数据点与各自样本平均值的偏差的平方和。
语法：DEVSQ(number1，number2，...)
参数：Number1、number2、...是用于计算偏差平方和的1到30个参数。它们可以是用逗号分隔的数值，也可以是数组引用。
实例：如果A1=90、A2=86、A3=65、A4=
54、A5=36，则公式“=DEVSQ(A1:A5)”返回2020.8。
19.EXPONDIST
用途：返回指数分布。该函数可以建立事件之间的时间间隔模型，如估计银行的自动取款机支付一次现金所花费的时间，从而确定此过程最长持续一分钟的发生概率。
语法：EXPONDIST(x，lambda，cumulative)。
参数：X函数的数值，Lambda参数值，Cumulative为确定指数函数形式的逻辑值。如果cumulative为TRUE，EXPONDIST返回累积分布函数;如果cumulative为FALSE，则返回概率密度函数。
实例：公式“=EXPONDIST(0.2，10，TRUE)”返回0.864665，=EXPONDIST(0.2，10，FALSE)返回1.353353。
20.FDIST
用途：返回F概率分布，它可以确定两个数据系列是否存在变化程度上的不同。例如，通过分析某一班级男、女生的考试分数，确定女生分数的变化程度是否与男生不同。
语法：FDIST(x，degrees_freedom1，degrees_freedom2)
参数：X是用来计算概率分布的区间点，Degrees_freedom1是分子自由度，Degrees_freedom2是分母自由度。
Excel是微软办公系列软件中的一个重要组成部分, 利用它可以进行数据处理、统计分析, 并对数据进行辅助决策, 在管理、统计财经、金融等众多领域得到了越来越广泛的应用。作为Excel软件的重要组成部分, 函数在软件的实践应用中起到越来越大的作用, Excel中的函数共分为12大类, 分别为日期与时间函数、逻辑函数、数据库函数、统计函数、工程函数、外部函数、数学与三角函数、查找与引用函数、文本函数、财务函数、信息函数以及用户自定义函数, 这些函数的运用, 使得Excel的功能异常强大, 给人们的数据处理带来了极大的便利。本文以逻辑函数为例, 浅析其在实践中的应用。
1 逻辑函数简介
在Excel中, 逻辑函数用来判断真假值, 或者进行复合检验。逻辑值是可以进行直接运算的文本, 一般有TRUE和FALSE两种, 分别代表逻辑真和逻辑假, 有时也用1和0来表示。在Excel中, 逻辑函数有6种, 分别为AND、OR、IF、NOT、FALSE、TRUE函数。这些函数在数据处理中各自扮演着重要的角色。
AND函数的语法为AND (数值1, 数值2, 数值3, ……) , 其中数值1~3均为逻辑值, 当所选区域有文本或空格时, 做忽略处理。全部选区中的逻辑值都为真时, 最终结果才为真。
OR函数的语法类似于AND函数, 所不同的是, 在全部选区中, 只要有一个逻辑值为真, OR函数的最终结果即为真。
IF函数是Excel逻辑函数中最常用的函数, 其功能和语法也最为强大。IF函数的语法为IF (数值所满足的条件, 条件为真时的返回值, 条件为假时的返回值) , 一般情况下, 真假返回值分别设置为TURE和FALSE逻辑值。IF函数可以自定义一个条件, 并对此条件进行判断, 在实际运用中, 灵活使用IF函数可以有效地提高工作效率。
NOT函数、FALSE函数和TRUE函数在实践中使用频率不是很高, NOT函数主要是对所判断条件的真假进行取反, FALSE函数和TRUE函数用来返回逻辑值FALSE和TRUE, 由于逻辑值可以在Excel中直接输入, 所以这两个函数在实践中并不常用。
2 逻辑函数的应用
2.1 IF、AND及OR函数的应用
教师在考试结束后, 必然要对大量的学生成绩进行统计分析, 以了解各个分数段的学生人数, 以便进行后续的分析与总结。由于学生人数及成绩的数量巨大, 如果以人工方式统计, 必然耗时耗力, Excel的逻辑函数恰好可以解决这个问题。我们以图1-图3所示的学生成绩表为例。
在成绩出来后, 教师往往会先查看本班各个分数段的学生, 在实践中可以先通过IF函数, 对学生成绩进行条件判断, 由于IF函数可以套用, 所以可以直接用一个函数, 将从不及格到优秀5个级别, 通过函数一次性区别出来, 函数表示为=IF (C3<60, "不及格", IF (C3<70, "合格", IF (C3<80, "中等", IF (C3<90, "良好", IF (C3<=100, "优秀") ) ) ) ) , 需要注意的是在公式中, 所有字符均为英文字符, 其中的文本需要使用引号, 具体转换方法见图2, 经过公式的复制后, 学生的成绩等次便一目了然了, 具体结果见图3。
学生成绩的等次有了后, 总分的计算和分析即是成绩统计的重点之一, 在实践中, 有时需要查看是否有低于某一标准的总分, 以便发出学业预警通知, 对于大量的成绩来说, 如果靠人工查找, 往往费时费力, 我们可以通过IF函数将总分转换为逻辑值, 再通过AND函数对逻辑值进行计算, 这样可以轻易得知是否有低于标准的总分, 如果没有, 则很轻易地完成了筛选与判断, 如果有, 我们则可以分段使用AND函数, 确定低分的范围, 为人工查找减轻负担。比如我们规定总分为350以下的学生, 必须向其发放学业预警通知, 在面对大量数据时, 首先用IF函数进行逻辑转换, 将大于350的分数转换为TRUE, 小于350的分数转换为FALSE, 随后, 通过AND函数, 对所有逻辑结果进行计算, 公式为=AND (I3:I34) 。如果结果返回如图4所示, 则说明所有学生的成绩均在350以上, 此时, 可以轻松判断出无需向任何学生发出学业预警通知。反之, 如果AND函数的计算值为FALSE, 我们可以继续使用AND函数分段进行计算, 使用公式=AND (I3:I11) 后, 每隔一段对公式进行复制, 缩小范围, 以便进行人工搜索, 本文截取部分结果如图5所示, 对于大批量的数据, 笔者认为可以对分段进行灵活调整。
除了AND函数外, OR函数同样适用于对IF函数逻辑返回值的计算, 比如想知道在这个班级中, 是否有总分高于400分的优秀学生, 我们可以先使用IF函数, 对学生成绩进行逻辑判断, 然后使用OR函数计算, 即可知道优秀学生的存在情况, 如图6所示。
2.2 NOT、TURE及FALSE函数的应用
NOT函数用于求反, 在实践中, 可以单独使用, 也可与其它函数配合使用。如图7所示, 在班级成绩的比较过程中, 我们可以使用IF函数与NOT函数配合, 将各科成绩中, 平均分较高的班级名称计算出来如图8所示。D2单元格中的公式为=IF (NOT (B2>C2) , "二班", "一班") , NOT函数在公式中, 起到了取反的作用。另外, NOT函数也可以单独运用, 比如我们要将一班平均分高的值返回为FALSE逻辑值, 将二班平均分高的值返回为TRUE逻辑值, 以便后续进一步操作与分析, 我们即可使用NOT函数进行计算, E2单元格的计算公式为=NOT (D2="一班") , 结果如图9所示。
TRUE函数和FALSE函数用于返回逻辑值, 其参数一般为空。利用公式的复制, 可以方便地快速返回逻辑值。如图10、11所示, 在C2中的公式为=TRUE () , 在E2中的公式为=FALSE () , 复制后即可快速得到真假值。
3 结语
Excel是办公套装软件中不可缺少的一个组成部分, 而逻辑函数亦是Excel中的重要组成部分。逻辑函数的运用有效地提高了工作效率, 除了在学生成绩的统计与分析中能极大地对教师起到帮助外, 逻辑函数在各行各业的数据处理中都有着不可替代的作用, 灵活运用好逻辑函数, 并能熟练地与其它函数套用, 必将在实践应用中起到事半功倍的功效。
摘要：逻辑函数是Excel函数的重要组成部分, 逻辑函数的使用可以进行真假值的判断及进行复合检验, 有效地使用逻辑函数可以极大地提高工作效率, 给人们的数据处理带来极大便利。通过分析在实践中经常会遇到的案例, 对逻辑函数的使用进行了分析, 提出了逻辑函数灵活运用的方法。
关键词：Excel,逻辑函数,实践应用
参考文献
[1]赵延惠.用Excel统计班级成绩[J].思茅师范高等专科学校学报, 2006 (3) .
[2]赵磊.Excel中进行数据分析的几个常用函数[J].中小企业管理与科技, 2010 (6) .
[3]张山风.Excel中各分数段人数的统计方法[J].办公自动化, 2008 (8) .
现以学校考试成绩为例,谈谈经常需要分析的几种数据指标。例:某次考试成绩(以3个班,每个班按7名学生为例)如图(1):
问题:
1.每名学生总分班名次和年级名次;
2.各班各学科平均分;
3.各科优秀线(总人数的20﹪)及各班优秀人数。
解答:
Step1:选中单元格O2,输入公式“=SUMPRODUCT(($D$2:$D$22=D2)*($N$2:$N$22>N2))+1”,回车,得结果3,向下填充到O22,学生总分班级名次已完成。
Step2:选中单元格P2,录入“=RANK(N2,N$2:N$22)”(不含引号,下同),回车,得结果3。用填充句柄把单元格P2向下复制到P22,学生总分年级名次已完成。结果如图(2):
说明:如果希望在数据表班无序的状态下进行按班级排名,请使用SUMPRODUCT公式:“=SUMPRODUCT(($D$2:$D$22=D2)*($N$2:$N$22>N2))+1”
RANK函数返回某数字在一列数字中相对于其他数值的大小排位。它的函数表达式是:RANK(number,ref,order)。
Step3:选中单元格E25,输入公式“=AVERAGE(IF(($D$2:$D$22=$D25)*(E$2:E$22<>""),E$2:E$22))”,按Ctrl+Shift+Enter结束,向右填充至N25,接着向下填充至E27:O27,结果如图(3):
说明:数组公式{=AVERAGE(IF(($D$2:$D$22=$D25)*(E$2:E$22<>""),E$2:E$22))},表示计算D2:D22等于D25且E2:E22不为空的数值的平均数。
Step4:选中单元格E30,录入“=PERCENTILE(E2:E22,0.8)”,回车,得结果106,填充至N30,得到各科优秀线,见图(4):
说明:PERCENTILE函数返回数组的K百分比数值点,可以使用此函数来建立接受阈值。它的函数表达式是:PERCENTILE(Array,K)。
Step5:选中单元格E33,输入“=SUM(($D$2:$D$22=$D33)*(E$2:E$22>=E$30))”按Ctrl+Shift+Enter结束,得结果3。填充至N30,再将区域E30:N30向下填充至区域E30:N35,见图(5):
注意:数组公式中的花括号是不能在编辑栏录入的,按Ctrl+Shift+Enter结束,自动生成前后花括号,表示该公式为数组公式。在数组公式中,“和”与“或”不是用AND和OR来表示的,而是用运算符号“*”和“+”来表示“和”与“或”。逻辑值参与运算时,TRUE=1,FALSE=0。
说明:数组公式{=SUM(($D$2:$D$22=$D33)*(E$2:E$22>=E$30))}的含义是:判断单元格区域D2:D22中的值是否等于D33(即1班)且单元格区域E2:E22中的值是否大于等于E30(即语文优秀线106),如是,则返回1,否则返回0,最后将得到的结果相加。该数组公式表达既是1班的学生且语文成绩大于等于106的值的个数。绝对引用的考虑填充时“班”列始终不变、数值行始终不变。
【Excel的HYPERLINK函数应用】推荐阅读：
excel中的函数公式07-27
excel函数vlookup的使用方法10-18
Excel统计函数练习09-28
excel中隐藏函数10-25
在Excel中如何使用TEXT函数？07-05
《反比例函数的应用》教学设计范文07-23
《函数在实际生活中的应用》教学反思09-22
一元二次函数应用教案10-03
二次函数最值应用问题05-28
