excel中隐藏函数

excel中隐藏函数（共10篇）

excel中隐藏函数篇1

打开Excel函数列表，密密麻麻的函数名称一定会让你看的眼花缭乱。在Excel 2013中，函数达到了454个之多，函数功能也越来越细化。好在这些函数都有帮助文件，一些不熟悉的函数在使用时可以看看帮助文件做一下简单的了解。

但是在Excel中，还有几个隐藏的函数，能够实现一些现有的函数无法实现的功能。为什么Excel会有隐藏函数，不知微软出于什么考虑而不公布出来。不过Excel隐藏函数不是很多，常用到的就三个： NUMBERSTRING、DATESTRING、DATEDIF。

首先看NUMBERSTRING函数，它的作用是将数字转换为大写。这个函数有两个参数，第一个参数是要转换的数值，第二个参数是指定返回的类型。类型共三种，其中：1为汉字小写，2为汉字大写，3为汉字读数。

如果数值有小数，该函数会四舍五入后再进行转换。

再来看DATESTRING函数，它的作用是将日期转换为yy年mm月dd天。

如需计算两个日期的差值，隐藏函数DATEDIF可以很好解决。

这个函数是几个隐藏函数中，功能最强大，变化最多、应用最广的一个。

该函数有三个参数，第一个参数是开始日期，第二个参数是结束日期，通过设置最后一个参数，可以指定所需信息的返回类型。

第三参数在使用MD、YM、YD时，在会有不同的bug，使用频率也比较低。而使用D来计算两个日期的间隔天数与日期直接相减的效果相同，相比较而言，还是直接相减快捷方便一些。

除了有bug的3个和不方便的1个，最后就剩下Y和D了。咱们就来说说这两个。

如下图所示，需要根据员工的入厂日期计算工龄，计算的截止日期是14年10月1日。

C2单元格的公式是： =DATEDIF(B2,“14-10-1”,“Y”)参数使用“Y”，表示计算时间段中的整年数。在计算员工工龄等方面，经常会被用到，大家注意看，由于是计算到14年10月1日的整年数，所以B5和B6的日期虽然只差了一天，但是DATEDIF函数的结果却差了一年。

账龄分析是指企业对应收账款，按账龄长短进行分类，并分析其可回收性。是财务工作中一个重要的组成。如下图所示，需要根据业务发生日期计算对应的账龄区间，计算的截止日期是14年10月1日。

C2单元格的公式：

=LOOKUP(DATEDIF(B2,“14-10-1”,“M”),{0,6,12,24},{“6个月以内”,“6个月至一年”,“一至二年”,“二年以上”})DATEDIF函数第二参数使用“M”，计算B2单元格日期与截止日期间隔的整月数，不足一个月的部分被舍去。计算结果7，用作LOOKUP函数的第一参数查询值。

excel中隐藏函数篇2

EXCEL中常用的隐藏函数有DATEDIF函数、NUMBERSTRING函数和DATESTRING函数,这三种函数均适用于EXCEL 2003/2007/2010/2013各版本。

1 DATEDIF函数

如果在EXCEL工作表中要计算两个日期的差值,就可以使用隐藏函数DATEDIF轻松实现。

1)DATEDIF函数的功能:返回两个日期之间间隔的年数、月数或日数。

2)DATEDIF函数的格式:DATEDIF(start_date,end_date,unit)。

3)DATEDIF函数的参数。

start_date(起始日期):时间段内的第一个日期。

end_date(结束日期):时间段内的最后一个日期。

结束日期必须大于起始日期。起始日期和结束日期可以是任意合法的日期格式或单元格引用,日期格式常用带引号的文本串表示。例如,”2015/12/20”表示2015年12月20日。有关日期系列数的详细信息,可参考其他日期时间函数。

Unit(参数):所需信息的返回类型。

参数”Y”:返回start_date与end_date日期间年数的差。

参数”M”:返回start_date与end_date日期间月数的差。

参数”D”:返回start_date与end_date日期间天数的差。

参数”MD”:忽略日期中的月和年,返回start_date与end_date日期间天数的差。

参数”YM”:忽略日期中的日和年,返回start_date与end_date日期间月数的差。。

参数”YD”:忽略日期中的年,返回start_date与end_date日期间天数的差。

例1:计算1973-4-1和2016-11-12两个日期间隔的年份数。

公式:=DATEDIF(“1973-4-1”,”2016-11-12”,”Y”)

当参数为”Y”时,计算结果是两个日期间隔的整年数,因此结果为43。

例2:计算日期为1973-4-1和2016-11-12两个日期间隔的月份数。

公式:=DATEDIF(“1973-4-1”,”2016-11-12”,”M”)

当参数为”M”时,计算结果是两个日期间隔的整月数,因此结果为523。

例3:计算日期为1973-4-1和2016-11-12两个日期间隔的天数。

公式:=DATEDIF(“1973-4-1”,”2016-11-12”,”D”)

当参数为”D”时,计算结果是两个日期间隔的天数,因此结果为15931。

例4:计算日期为1973-4-1和2016-11-12两个日期间忽略间隔月数和年数的间隔天数之差。

公式:=DATEDIF(“1973-4-1”,”2016-11-12”,”MD”)

当参数为”MD”时,计算结果是两个日期间忽略间隔月数和年数的间隔天数之差。1日和12日之间间隔11天,因此结果为11。

例5:计算日期为1973-4-1和2016-11-12两个日期间忽略间隔日数和年数的间隔月数之差。

公式:=DATEDIF(“1973-4-1”,”2016-11-12”,”YM”)

当参数为”YM”时,计算结果是两个日期间忽略间隔日数和年数的间隔月数之差。4月和11月之间间隔7个月,因此结果为7。

例6:计算日期为1973-4-1和2016-11-12两个日期间忽略间隔年数的间隔天数之差。

公式:=DATEDIF(“1973-4-1”,”2016-11-12”,”YD”)

当参数为”YD”时,计算结果是两个日期间忽略间隔年数的间隔天数之差。4月1日和11月12日之间间隔的天数为225,因此结果为225。

DATEDIF是精确计算,对于计算年龄、工龄等非常有效。

例如:使用以下公式可以计算出生日期为1986-7-18日的人的年龄。

公式:=DATEDIF(“1986-7-18”,TODAY(),”Y”)

2 NUMBERSTRING函数

NUMBERSTRING函数是EXCEL中隐藏的一个特殊函数,它的功能是将小写数字转换为不同形式的中文大写形式,但是该函数仅支持正整数。当工作中需要显示财务数据的时候,使用这个函数进行处理,可以使数据形式规范化,体现工作的专业性,也能提高工作效率。

1)NUMBERSTRING函数的功能:将小写数字转换成中文大写数字。

2)NUMBERSTRING函数的格式NUMBERSTRING(VALUE,TYPE)。

3)NUMBERSTRING函数的参数。

VALUE(数值):要转化的数字或单元格引用;

TYPE(返回值类型):返回值有1、2、3三种类型,1为汉字小写,2为汉字大写,3为汉字读数。

例如:

=Number String(123456789,1)返回值:一亿二千三百四十五万六千七百八十九

=Number String(123456789,2)返回值:壹亿贰仟叁佰肆拾伍万陆仟柒佰捌拾玖

=Number String(123456789,3)返回值:一二三四五六七八九

3 DATESTRING函数

DATESTRING函数是用于将指定日期转换成文本格式日期(中文显示的日期形式)的隐藏函数。

1)DATESTRING函数的功能:将指定日期转换成文本格式日期。

2)DATESTRING函数的格式:DATESTRING(serial_number)。

3)DATESTRING函数的参数。

serial_number(日期序列号):要转换为文本格式的日期或单元格引用。

例如:若当前日期为2016年8月25日,则公式“=DATESTRING(TODAY())”的返回值为:16年08月25日。

若A1=2016-8-25,则公式“=DATESTRING(A2)”的返回值为:16年08月25日。

摘要：本文介绍了EXCEL中的3种常用隐藏函数DATEDIF函数、NUMBERSTRING函数、DATESTRING函数的功能、格式和参数,举例说明了这3种函数的使用方法。

关键词：EXCEL,隐藏函数,应用

参考文献

[1]张秋林.Excel函数学习使用技巧研究[J].科技创业月刊,2016(9).

[2]黄宗正.Excel函数在学生日常管理中的应用[J].电脑知识与技术,2016(2).

在Excel中绘制数学函数曲线篇3

在下面的步骤1至步骤4中，我们将通过抛物线函数f（x）=x2介绍绘图的基本原理。步骤5至步骤8，我们会介绍一些其他图形的处理方法。

操作步骤

1、定义值范围

我们将x轴的值范围定义为-3到3，首先在“A3”和“A4”单元格输入值“-3”和“-2.5”。选择这两个单元格，拖动选择框右下角到单元格“A15”，Excel将自动按照“A3”和“A4”单元格值的大小差距，自动在其他单元格输入等距的数值。

2、输入公式

抛物线函数f（x）=x2，在Excel中，我们可以在f（x）所对应的单元格“B3”中以公式“=A3*A3”来进行计算。我们可以在单元格“B3”中输入该公式，回车即可执行计算。

3、计算y轴坐标

接下来选择单元格“B3”，拖动选择框右下角到单元格“B15”，Excel将自动为每一个单元格输入公式，并自动调整公式的参数，计算出每一个x轴数值对应的y轴坐标。

4、画出抛物线

选择单元格区域“B3”到“B15”并选择合适的图表类型，例如选择“插入”选项卡“图表”中的“散点图”，我们马上就可以看到根据数据画出的抛物线。接下来，我们还可以根据自己的需要，调整图形以及增减图形中的元素。例如如果希望看到抛物线的数值，可以通过左上角的“添加图表元素”选择“数据标签”，并根据自己的喜好选择数值显示的方式。

5、输入有极点的有理函数

在Excel中输入有极点的有理函数，例如直接输入公式“（1/A3^2）”以计算1/x2。有理函数的极点在Excel中将始终以错误信息“#DIV/0！”显示。不过，我们可以通过IF函数来解决类似的问题。首先，选择单元格“B3”并单击上方的公式图标“f（x）”，选择公式类型为“IF”并确认。在对话框中的第一个输入框中键入“A3=0”，然后在第二个输入框中键入““””，最后在第三个输入框中键入“（1/A3^2）”，按回车键确认。选择单元格“B3”，拖动选择框右下角到单元格“B15”。现在，我们将看到“B9”的单元格是空着的，也就是有理函数的极点。

6、选择和编辑图形类型

在通过数据生成图形之后，我们可以根据需要调整图形。以有极点的有理函数图形为例，我们可以通过“更改图表类型”选择其他类型的图表，例如我们可以选择“散点图”中的“带平滑线和数据标点的散点图”。在创建图表之后，在x轴的极点位置上双击，我们还可以通过选项“无线条”清除不必要的线，如有理函数极点部分的线条。

7、绘制几何图形

在Excel中通过数据直接绘制类似于等腰三角形之类的几何图形有一定的困难，但是通过在x轴和y轴上定义坐标，我们可以绘制几何图形。例如在“A”列中依次输入数据“0”、“30”、“15”、“0”，在“B”列中依次输入数据“5”、“5”、“20”、“5”，通过这两组数据并依上面介绍的方法画图，即可画出一个等腰三角形。

8、计算和绘制正弦曲线

excel中隐藏函数篇4

第一招：分级显示

Excel中的分级显示功能可以简单实现快速隐藏、显示指定行列。以隐藏职称、等级、业绩所在的C、D、F列为例：

选中C、D列，切换到“数据”选项卡，单击“组合”的图标，此时工作表上侧会显示分级的1、2按钮。同样选中F列进行组合。现在只要单击1按钮即可隐藏C、D、F列，单击2按钮则恢复显示。若单击E列上的那个“-”按钮则可只隐藏C、D列，再次单击恢复显示。对于行的设置也是一样，只是组合后行的1、2按钮是在行标的左侧。

注：隐藏的行或列在通过拖动选择或按Shift键选中时会同时被选中，复制粘贴后你会发现隐藏内容也同时被复制过来了。对此可在拖动或按Shift键选中区域后，再按一下“Alt+;”组合键，这样就会只选中可见单元格，而不包括那些被隐藏的行或列，

第二招：保存视图

在“视图”选项卡中单击“自定义视图”打开“视图管理器”窗口，单击“添加”按钮，输入名称为“全部显示”，确定即可在“视图管理器”窗口中增加一个“全部显示”的项。再按给员工看的列表需要分别选中C、D、F列右击选择“隐藏”，然后再单击“自定义视图”打开“视图管理器”窗口，单击“添加” 增加一个“员工版”的项。在此你还可以按各种需要随意隐藏相应行列，再添加其他的视图项。

以后只要单击“视图”选项卡的“自定义视图”打开“视图管理器”窗口，从中双击选用“员工版”即可隐藏相应列变成给员工看的表格，在“视图管理器”中双击“全部显示”则可恢复不隐藏的状态。这样所有隐藏行列操作都可以通过一次双击完成，是不是挺简单呢?

excel中隐藏函数篇5

LOOKUP(lookup_value、lookup_vector、result_vector)

•Lookup_value 是 LOOKUP 在第一个矢量中搜索到的值。Lookup_value 可以是数字、文本、逻辑值，也可以是代表某个值的名称或引用。

•Lookup_vector 是一个仅包含一行或一列的区域。lookup_vector 中的值可以是文本、数字或逻辑值。

重要说明：

lookup_vector 中的值必须按升序顺序排列。例如，-2、-1、0、1、2 或 A-Z 或 FALSE、TRUE。否则，LOOKUP 返回的值可能不正确。大写和小写文本是等效的。

•Result_vector 是一个仅包含一行或一列的区域。它的大小必须与 lookup_vector 相同。

注意

•如果 LOOKUP 找不到 lookup_value，它会匹配 lookup_vector 中小于或等于 lookup_value 的最大值，

•如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会返回 #N/A 错误值。

学习了lookup函数，发现出现了一个问题。

=LOOKUP(“他”,{“你”,“我”,“他”,“的”;1,2,3,4})

得到的不是3 ，

查找百度原来是这个lookup函数需要对数组进行升序排列，才能获取正确值。

例如：

=LOOKUP(“d”,{“d”,“b”,“c”,“a”;1,2,3,4})得不到 1 反而是4

修改成：

=LOOKUP(“d”,{“a”,“b”,“c”,“d”;1,2,3,4})//得到的才是4

修改如下：

=LOOKUP(“他”,{“的”,“你”,“他”,“我”;4,1,3,2})

例二：

=LOOKUP(B2,{“员工”,4;“教导”,3;“副校长”,2;“校长”,1})获取员工值发现是 1//肯定是错误

修改如下：

Excel隐藏公式篇6

一个隐藏单元格的公式，在选定此单元格时，公式不会出现在编辑栏中。隐藏公式的操作步骤如下：

(1) 选定想隐藏公式的单元格范围；从“格式”菜单中选择“单元格”命令，选定“保护”选项卡，如图7-17所示，

(2) 选定标记为“隐藏”的选择框，按下“确定”按钮；进入到“工具”菜单中选择“保护”命令中的子命令“保护工作表”命令，如图7-18所示。

(3) 在屏幕上出现图7-19的口令对话框。

(4) 在其中输入一个口令，接着出现图7-20的确认口令对话框。

操作后，就会看到公式不在编辑栏中出现，如图7-21，请参照图7-16比较它们的变化。

excel中隐藏函数篇7

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) .

excel中隐藏函数篇8

关键词：考务管理;Excel函数嵌套;软件开发

中图分类号：G434 文献标志码：A 文章编号：1673-8454（2015）04-0089-03

在平时教学工作中，经常会用到Excel软件记录、计算和统计数据，许多时候仅使用了Excel的简单功能，如求和、求平均值、计数等。据相关统计，80%使用Office Excel的人其实只是用了它不到20%的功能。实际上，Excel数据处理功能很强大，灵活应用其函数功能，可以完成复杂统计，甚至以它为平台开发小型数据库。本文以应用Excel内置功能制作考务管理系统为例，讨论如何应用函数嵌套，实现学生成绩自动化统计分析。考务管理系统数据流向见图1。

数据处理中为了保证数据的准确，使用全国中小学学籍管理系统中的学生基础数据就成为必然。首先，这些数据经过校对和学期初学生变动情况的更新，可以提供最新的、准确的学生基础信息。其次，不用再另起炉灶，减少数据维护的工作量，只需要设计好“班内学号”列的数据构成方式。“班内学号”应包括入学时间、班别、序号三个信息，如班内学号“2009215”，2009表示入学的年份，中间的2表示2班，最后两个数字15，表示第15个序号。这种设计有几项用处，一是班内学号是数字，在数据录入中，从本班第1位到最末位的班内学号，都可以使用数据序列填充的方式完成，减少操作量，避免错误，提高工作效率。二是通过班内学号升序排列，为整个数据建立索引，方便数据的引入和查询，数据维护简单方便。三是学号本身包含信息，可以根据需要调取，完成一些功能设计。四是开放性的设计思想为日后增加信息量预留接口且不影响其原有功能。如学校规模大，每个年级的班级数达到两位数，就把表示班别的数字定义为两位数。如要在班内学号中需要增加学生性别信息，只需在数字中增加一个数位，用0和1分别代表男女生便可实现。

“成绩册”工作表，用来记录每名学生的各门功课的考试成绩，进行初步的统计工作，提供各班成绩单报表。在实际应用中，学生姓名通过函数命令“=IF（COUNTIF（学生基础信息！$AN：$AN，A2）=0，""，LOOKUP（A2，学生基础信息！$AN：$AN，学生基础信息！$B：$B））”的方法引用。先用IF函数执行真假值判断，表达式COUNTIF（学生基础信息！$AN：$AN，A2）返回值如果为0，表示当前行中班内学号值在学生基础信息表中不存在，结果将返回空值，否则返回表达式LOOKUP（A2，学生基础信息！$AN：$AN，学生基础信息！$B：$B）的值，即使用LOOKUP 函数的向量形式，在学生基础信息AN列中查找A2（当前行中的班内学号）数值，然后返回学生基础信息B列（姓名）中相同位置的数值，从而得到学生姓名。学生的性别信息处理方法和姓名信息处理方法类似，区别是IF返回表达式指定C列（性别）数据便可。由于班内学号的数据构成已经包涵了学生所在班级的信息，因此“班级”就可以通过班内学号自动计算出来，方法是“=IF（COUNTIF（学生基础信息！$AN$2：$AN$1000，A2）=0，""，IF（MONTH（TODAY（））>9，（YEAR（TODAY（））-LEFT（$A2，4）+1）*10+MID（$A2，5，1），（YEAR（TODAY（））-LEFT（$A2，4））*10+MID（$A2，5，1）））”。先用IF函数执行真假值判断，如果当前月份大于9月份（即新学年），则用当前年份减去班内学号中的入学年份再加1得到年级数，用MID函数引用出班内学号中的班别数，再用年级数乘10加班别数得到班级名称。否则，用当前年份减去班内学号中的入学年份得到年级数（不用再加1），用MID函数引用出班内学号中的班别数，再用年级数乘10加班别数得到班级名称。

提起排名，大家首先就会想到RANK函数，但RANK函数使用时局限较大，排名时班级、年级不同，对数字列表的引用范围就会有变化。如果手动给RANK函数指定引用范围，统计数据就得固定下来，不允许修改数据，容错率很低。此外，如果学校规模大，手动指定引用范围本身的工作量也是很大的，失去了编写公式的意义。因此，学生成绩班级排名和年级排名时，使用了“SUM+if”的数组函数组合。班级排名方法：“=IF（OR（COUNTIF（学生基础信息！$AN$2：$AN$1000，A2）=0，SUM（I2：K2）=0），""，IF（$H2>0，SUM（IF（bj=$D2，IF（zf>H2，1，0）））+1，""））”，先对表达式bj=$D2进行条件检测，bj是班级标题下的数据，是提前定义的名称，通过检测，筛选出同一个班级的总分数据进一步处理。再嵌套第二层函数IF，对表达式zf>H2进行条件检测，zf代表总分标题下的数据。当表达式zf>H2为TRUE时返回1，为FALSE时返回0。年级排名方法：“=IF（OR（COUNTIF（学生基础信息！$AN$2：$AN$1000，A2）=0，SUM（I2：K2）=0），""，IF（$H2>0，SUM（IF（LEFT（bj，1）=LEFT（D2，1），IF（zf>H2，1，0）））+1，""））”。

备注一栏系统设计了数据自动校对功能，可对录入的考试成绩进行各类错误的检查与提示，对学号错误、学号重复、成绩不全、语文成绩异常、数学成绩异常、英语成绩异常、重名等数据问题进行提示。应用逻辑函数指令拓展、IF函数多层嵌套、文字提示智能化等技术来实现，方法为“=IF（COUNTIF（学生基础信息！$AN：$AN，A2）=0，"此为空号"，IF（COUNTIF（A：A，A2）>1，"学号重复"，IF（OR（AND（$D2>30，COUNTBLANK（I2：K2）>0），COUNTBLANK（I2：J2）>0），"成绩不全"，CONCATENATE（IF（OR（$I2>100，$I2<0），"语文"，""），IF（OR（$J2>100，$J2<0），"数学"，""），IF（OR（$K2>100，$K2<0），"英语"，""），IF（OR（$I2>100，$I2<0，$J2>100，$J2<0，$K2>100，$K2<0），"成绩异常"，IF（AND（$D2>10，$D2<30，COUNTBLANK（K2）=0），"低年级不考英语"，IF（COUNTIF（B：B，B2）>1，"重名"，""）））））））”。IF函数可以嵌套七层，用 value_if_false 及 value_if_true 参数与逻辑函数套用，可以构造复杂的检测条件，满足备注栏的数据校对、错误提示等需求。

汇总表，对全校各班各门功课的考试成绩汇总成绩进行统计，形成各班教学质量报表。任课教师引用方法是“=HLOOKUP（B2，任课教师！$B$1：$K$13，MATCH（AW2，任课教师！$A$1：$A$13，0），FALSE）”。在“任课教师！表中$B$1：$K$13”数据区域查找B2单元格（课程科目）数值，并由此返回表达式MATCH（AW2，任课教师！$A$1：$A$13，0）指定行处的数值。实考数统计方法是数组函数“=INDEX（FREQUENCY（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy）），{0.1，100.1}），2）”，这段数组函数也是多个函数嵌套形成的，其中yw、sx、yy是提前定义的数据区域名称，yw是语文成绩数据区域，sx是数学成绩数据区域，yy是英语成绩数据区域。B列单元格是考试科目，科目各不相同，属于变量，因此，用HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE）语句，把科目转换为序号，用CHOOSE函数选择科目所在的定义名称数据区域，再用IF函数对指定班级的数据进行逻辑筛选，用FREQUENCY函数返回筛选出的符合要求数据的频率分布，最后用INDEX函数调用所需数据，统计出各科目的实际参加考试的人数。最高分统计方法是数组函数“=MAX（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy）））”。用IF函数对指定班级的数据进行逻辑筛选，再用MAX函数返回其中的最大值，也就是指定班级、指定科目的最高分。最低分统计方法只需要把MAX函数换为MIN 函数返回其中的最小值便可。年级前10名（人）统计方法是数组函数“=INDEX（FREQUENCY（IF（bj=$AW2，njpm），{0.1，10.1}），2）”。其中njpm是定义的年级排名标题数据区域名称，先用IF函数筛选出指定班级的年级排名数据，再用FREQUENCY返回10以内数值的频率分布，最后用INDEX函数调用其所需数据，统计出指定班级的年级排名前10的具体人数。平均分统计方法是数组函数“=ROUND（AVERAGE（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy））），2）”。对筛选出的指定班级、指定科目的考试成绩计算平均分，四舍五入保留2位小数。标准偏差统计方法是数组函数“=ROUND（STDEV（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy））），2）”，标准偏差反映相对于平均分班级整体成绩的离散程度。90至100分（人）统计方法是数组函数“=INDEX（FREQUENCY（IF（bj=$AW2，CHOOSE（HLOOKUP（$B2，{"语文"，"数学"，"英语";1，2，3}，2，FALSE），yw，sx，yy）），{0，9.9，19.9，29.9，39.9，49.9，59.9，69.9，79.9，89.9，100.1}），11）”。用FREQUENCY对指定班级、科目的整体成绩按照每10分一个分段进行频率分布计算，最后用INDEX函数返回所需分数段的具体人数。各科90至100分（人）统计方法是数组函数“=IF（$AW2<30，SUM（IF（bj=$AW2，IF（yw>=90，IF（sx>=90，1，0）））），SUM（IF（bj=$AW2，IF（yw>=90，IF（sx>=90，IF（yy>=90，1，0））））））”。各科的含义对每个年级不尽相同，一、二年级是指语文和数学两个科目，三至六年级是语文、数学、英语三个科目，因此要用IF函数进行分类，再使用不同的统计方法。

成绩查询工作表，可以按照学号和姓名两种方式查询学生考试成绩，并且可以跟踪反馈历次考试成绩。班内学号的查询方法是“=IF（$L2&$M2=""，""，IF（$L2=""，IF（MAX（IF（成绩册！$A$1：$B$1000=$M2，ROW（$1：$1000）））=0，""，INDEX（成绩册！$A$1：$L$1000，MAX（IF（成绩册！$A$1：$B$1000=$M2，ROW（$1：$1000））），MAX（IF（成绩册！$A$1：$K$1000=$M2，COLUMN（$A$1：$I$1）））-1）），IF（MAX（IF（成绩册！$A$1：$B$1000=$L2，ROW（$1：$1000）））=0，""，VLOOKUP（$L2，成绩册！$A$2：$K$1000，1，FALSE））））”。该组函数首先判断查询方式，L2单元格接收学号信息，M2单元格接收姓名信息。如果L2单元格为空值，M2单元格不为空值，说明使用者是按照姓名方式查询的，即使用MAX（IF（成绩册！$A$1：$B$1000=$M2，ROW（$1：$1000）））和MAX（IF（成绩册！$A$1：$K$1000=$M2，COLUMN（$A$1：$I$1）））-1查询该生对应的班内学号在数据区域内的行号、列号，再用INDEX函数引用该生班内学号，显示在单元格。否则，进一步判断L2单元格有无学号信息，如果使用者已经在L2单元格填入了班内学号，则使用VLOOKUP（$L2，成绩册！$A$2：$K$1000，1，FALSE）语句查证成绩册工作表的班内学号数值，若学号正确则显示于单元格，否则显示空值，并在备注栏显示文字“这是空号”。因为成绩查询工作表与成绩册工作表结构相同，所以姓名、性别、班级、班级排名、年级排名、总分、平均分、语文、数学、英语的数据查询使用同一方法“=IF（$L2&$M2=""，""，IF（$A2=""，""，VLOOKUP（$A2，成绩册！$A$2：$K$1000，COLUMN（），FALSE）））”。表结构相同，每个查询项目处于同一位置，因此，以函数COLUMN返回所在列号为变量，引导VLOOKUP函数在数值数组中查找指定的数值，并由此返回数组当前行中指定列处的数值，在单元格中显示所需要的信息。

在处理变量复杂的任务时，单个函数的功能显得苍白无力，通过函数嵌套的灵活运用往往可以解决问题。嵌套函数是拓展函数功能的主要方式，将函数返回值作为另一函数的参数使用，层级叠加，性能提升[1]。合理运用Excel函数嵌套，使电子表格具备数据处理的五大要素，即数据录入、校对、查询、统计和分析，不需要专业编程软件技术支持，仅采用Excel内置的函数库，经过严密的设计，也可以形成类似数据库的简单功能，实现学校教学质量统计分析的自动化。基于统计方便、准确、全面的目标，界面设计和系统操作采用Excel工作表默认模式，运行环境为Excel，为大家所熟知，操作简单，上手容易，只要有一点计算机基础的教师都可以熟练掌握，免除培训环节。学生考试成绩录入完成之后，使用预设的公式进行数据填充，双击鼠标在几秒钟时间内，就可以对46个统计项目、几千个单元格数据进行统计，准确高效的完成工作任务。由于函数代码开放，使用者可以根据实际需要修改相关代码，重构统计项目，只需变更单位信息就可为学校教务工作服务，成为教学管理的好助手。

参考文献：

[1]钱秀峰.Excel中函数嵌套功能的实际应用[J].人力资源管理，2010（4）.

excel中隐藏函数篇9

Excel中frequency函数怎么用............................................................................................2

1.frequency函数的含义................................................................................................2

11.frequency函数的含义......................................................................................2 2.frequency函数的语法格式........................................................................................2

12.frequency函数的语法格式...............................................................................2 3.frequency函数案例...................................................................................................3

13.如图所示，统计各个成绩段的个数。...............................................................3 24.如图所示，分为4组，所以有3个分段点。....................................................3 35.也可以使用countif函数，有关countif函数，可以参看..................................4

“使用Excel函数统计各分数段的人数”的操作步骤：...........................................................6

1、打开Excel工作表；................................................................................................6

2、由已知条件可知，需要将B2:B34单元格的分数，按照分数段统计出相应的个数，在Excel中可通过SUMPRODUCT、SUM、COUNTIF、FREQUENCY等函数来实现。......................................................................................................................................7

3、方法一：SUMPRODUCT函数...............................................................................7 方法二：SUM函数.......................................................................................................7 方法三：COUNTIF函数...............................................................................................8 方法四：FREQUENCY函数........................................................................................9 Excel中frequency函数怎么用

你还在为Excel中frequency函数怎么用而苦恼吗，今天小编教你Excel中frequency函数怎么用，让你告别Excel中frequency函数怎么用的烦恼。

更多经验请关注Excel小新，如果帮到了你，请在上方给个投票谢谢支持。好人一生平安。先顶后看年薪百万，如果帮到了你，帮忙给个好评，这对小编很重要，万分感谢。

1.frequency函数的含义 11.frequency函数的含义

以一列垂直数组返回一组数据的频率分布。

2.frequency函数的语法格式 12.frequency函数的语法格式

FREQUENCY(data_array,bins_array)Data_array 是一组数值，然后根据Bins_array 中对 data_array 中的数值进行分组的情况，统计频率。

Bins_array 用于对 data_array 中的数值进行分组。

3.frequency函数案例

13.如图所示，统计各个成绩段的个数。

Data_array ——数值，要统计的——范围为B2：B10.Bins_array ——分组情况——范围E3：E5.24.如图所示，分为4组，所以有3个分段点。

<=70,分段点为70，注意如果是<70，分段点就是69。以此类推。最后一个不用算分段点。但是注意公式中要包括进去。

35.也可以使用countif函数，有关countif函数，可以参看

Excel中countif函数的使用方法

“使用Excel函数统计各分数段的人数”的操作步骤：

1、打开Excel工作表；

2、由已知条件可知，需要将B2:B34单元格的分数，按照分数段统计出相应的个数，在Excel中可通过SUMPRODUCT、SUM、COUNTIF、FREQUENCY等函数来实现。

3、方法一：SUMPRODUCT函数

在E2单元格输入以下公式，然后向下填充公式

=SUMPRODUCT((B$2:B$34>=--LEFT(D2,FIND(“-”,D2)-1))*(B$2:B$34<--RIGHT(D2,LEN(D2)-FIND(“-”,D2))))公式表示：将B2:B34单元格中同时满足大于等于D2连接符左侧数据且小于D2连接符右侧数据的个数统计出来。

方法二：SUM函数

在E2单元格输入以下公式数组，按Ctrl+Shift+Enter组合键结束，然后向下填充公式 =SUM((B$2:B$34>=--LEFT(D2,FIND(“-”,D2)-1))*(B$2:B$34<--RIGHT(D2,LEN(D2)-FIND(“-”,D2))))

方法三：COUNTIF函数

在E2单元格输入以下公式数组，按Ctrl+Shift+Enter组合键结束，然后向下填充公式 =COUNTIF(B$2:B$34,“>=”&--LEFT(D2,FIND(“-”,D2)-1))-COUNTIF(B$2:B$34,“>=”&--RIGHT(D2,LEN(D2)-FIND(“-”,D2)))公式表示：从B2:B34数据区域，统计大于等于D2连接符左侧条件的个数，再减去大于D2连接符右侧条件的个数，得到分数段的个数。

方法四：FREQUENCY函数