Excel公式（精选10篇）
Excel公式 第1篇
数组, 通俗地说, 即一组数据。这组数据是由相同类型的元素按照一定的规律性排列组合而成的集合。给这组数据命名并以编号作为下标来区分各个变量, 就可以把他们表示出来。数组的种类有一维数组、二维数组、字符数组和多维数组等。因Excel是本身就是二维表所组成的, 其可以处理的数组也是二维数组。在Excel工作簿中, 表格用行号和列标所结合, 来描述数据所对应的位置。如通常行用大写英文字母表示, 列用阿拉伯数字表示, 则A3就表示第一列第三行的数据位置, 即使对于非计算机专业的人员来说, 也显得通俗易懂。所以, 利用数组公式来进行计算, 也可以看成是把大宗数据进行批量计算或处理, 既可以节省计算及操作步骤, 又可以结合实际工作中对数据信息的要求, 对大量的数据按使用者所定制的各种要求进行计算或统计等。
2 数组公式使用举例
进行批量计算, 利用数组公式可以省略中间过程, 忽略范围内无数据的区域, 直接得到结果。
(1) 根据职工的职称和超工作量, 汇总出总计数据。
注:单价依据职称, 高级60, 中级50, 初级40, 如有兴趣用IF函数实现, 可以在E3单元格输入=IF (C3="高级"60, IF (C3="中级", 50, 40) ) , 然后向下填充, 如图1所示。
需要注意的是, 数组公式显示的是{=SUM (D3:D12*E3:E12) }, 在输入的时候{}不是手动输入的, 而是在最后按Ctrl+Shift+Enter键自动生成的。公式中D3:D12*E3:E12表示两个数据区域进行乘法计算, 但是不要和矩阵的乘法相混淆, 两者是不同的, 上述公式计算相当于=SUM (D3*E3, D4*E4, D5*E5, D6*E6, D7*E7, D8*E8, D9*E9, D10*E10, D11*E11, D12*E12) , 在数据范围内, 如果没有输入数据, 默认将其当0处理, 不会出现错误。
(2) 根据用户定义的条件, 统计生成数据报表。
对于条件统计, Excel本身提供了IF、Sumif、Countif等带有条件处理功能的函数, 但是这些函数单独使用的话, 都具有一定的局限性, 而利用Excel数组功能, 能够很好地解决这些问题。下面, 通过一些例子来阐述数组功能的应用。假设某单位人员总表如表1所示。
(3) 根据单位的人员总表, 制作科室人员学历统计报表。
如图2所示, 统计表中的数据从人员总表汇总得出, 在C5单元格输入数组公式{=SUM ( (人员总表!$B$3:$B$22=$B5) * (人员总表!$G$3:$G$22=C$4) ) }, 除总计外的其余数据, 均可通过填充功能完成。该表通过2个条件的的与运算, 统计出满足条件的数据个数, 再用sum进行求和统计。公式中人员总表!$B$3:$B$22=$B5, 表示查找人员总表中B列数据=甲科室, 人员总表!$G$3:$G$22=C$4表示查找人员总表中G列数据=博士, 两个条件用“*”进行与计算, 表示要同时满足这两个条件。如果满足条件, 计算结果为1, 否则为0。每行数据通过数组计算一遍后, 就能统计出N个1和M个0, 用SUM将所有的数据加起来, 就得到了人员个数的统计数值。需要注意的是, 如果要使用Excel的填充功能, 那么需要对公式中单元格的相对和绝对引用有一定的理解, 否则列出的公式可能当前单元格计算正确, 但是填充到其他位置的时候就要出现错误。
本例通过3个条件的分段统计, 汇总出各科室的年龄分布数据, 可以看成是例2.1的扩展。其操作方法是:在C4单元格输入{=SUM ( (人员总表!$B$3:$B$22=$B4) * (DATEDIF (人员总表!$H$3:$H$22, TODAY () , "y") <=30) ) }, 可以进行纵向填充, 老年统计只需要将前一公式中的<=30修改为>=45即可, 而中年统计需要多增加一个年龄区间, 公式为{=SUM ( (人员总表!$B$3:$B$22=$B4) * (DATEDIF (人员总表!$H$3:$H$22, TODAY () , "y") >30) * (DATEDIF (人员总表!$H$3:$H$22, TODAY () , "y") <45) ) }。公式中用到的条件1是人员总表B列数据=甲科室, 条件2是利用DATEDIF函数计算出人员的年龄, 其格式为DATEDIF (日期1, 日期2, "y") , 日期1表示出生年月, 日期2表示现在的日期, 计算出的年龄忽略月、日, 取整数。从中年的年龄段数据统计可以看出, 数组也可以超过2个条件进行同时运算, 大大提高了数组公式的应用范围, 如图3所示。
(4) 根据指定条件, 自动生成人员信息表, 如图4所示。
本例的计算公式较为复杂, 建议可制作成模板使用。其具体操作方法为:在C4单元格输入{=INDEX (人员总表!$C:$C, SMALL (IF ( (人员总表!$G$3:$G$22="博士") * (人员总表!$B$3:$B$22=C$3) , ROW (人员总表!$G$3:$G$22) , 65536) , ROW (1:1) ) ) &""}, 其余学历只要在该学历的第一个单元格内复制上面数组公式, 将"博士"修改成相应学历即可, 其余单元格都可以通过填充功能完成。公式中用到多个函数, 其中INDEX在指定区域中返回行列交叉处的单元格, 利用SMALL函数, 逐个寻找数组区域中的最小值, 前一个ROW函数计算出该值在区域中的行号, 后一个ROW (1:1) 可以在向下填充的时候变成ROW (2:2) , ROW (3:3) 目的是依次查找到下一个满足条件的数据, 最后为了除去填充时空缺数据显示的0, 利用连接符号&"", 连接空格使没有数据的地方不显示0。
3 结语
通过对以上例题的阐述, 能够看出数组公式具有非常实用的功能, 它能够进行批量查找、统计, 制作出来的统计报表能够进行数据的自动更新。虽然数组公式书写比普通的公式要复杂一些, 但是其能够处理的功能比单一的函数功能要强大得多, 如果能够将工作中常用的一些统计报表做成模板, 那么将大大提高工作效率。
参考文献
[1]支馨悦.在Excel中使用数组公式实现多表联动查询[J].甘肃教育, 2011, (7) .
Excel函数公式 第2篇
在会计同事电脑中，保保经常看到海量的Excel表格，员工基本信息、提成计算、考勤统计、合同管理....看来再完备的会计系统也取代不了Excel表格的作用。
于是，小呀尽可能多的收集会计工作中的Excel公式，所以就有了这篇本平台史上最全的Excel公式+数据分析技巧集。
员工信息表公式
1、计算性别(F列)
=IF(MOD(MID(E3,17,1),2),“男”,“女”)
2、出生年月(G列)
=TEXT(MID(E3,7,8),“0-00-00”)
3、年龄公式(H列)
=DATEDIF(G3,TODAY,“y”)
4、退休日期(I列)
=TEXT(EDATE(G3,12*(5*(F3=“男”)+55)),“yyyy/mm/dd aaaa”)
5、籍贯(M列)
=VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,)注：附带示例中有地址库代码表
6、社会工龄(T列)=DATEDIF(S3,NOW,“y”)
7、公司工龄(W列)
=DATEDIF(V3,NOW,“y”)&“年”&DATEDIF(V3,NOW,“ym”)&“月”&DATEDIF(V3,NOW,“md”)&“天”
8、合同续签日期(Y列)
=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1
9、合同到期日期(Z列)
=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY,“[<0]过期0天;[<30]即将到期0天;还早”)
10、工龄工资(AA列)
=MIN(700,DATEDIF($V3,NOW,“y”)*50)
11、生肖(AB列)=MID(“猴鸡狗猪鼠牛虎兔龙蛇马羊”,MOD(MID(E3,7,4),12)+1,1)
1、本月工作日天数(AG列)
=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)
2、调休天数公式(AI列)=COUNTIF(B9:AE9,“调”)
3、扣钱公式(AO列)
婚丧扣10块，病假扣20元，事假扣30元，矿工扣50元
=SUM((B9:AE9={“事”;“旷”;“病”;“丧”;“婚”})*{30;50;20;10;10})
1、本科学历人数
=COUNTIF(D:D,“本科”)
2、办公室本科学历人数
=COUNTIFS(A:A,“办公室”,D:D,“本科”)3、30~40岁总人数
=COUNTIFS(F:F,“>=30”,F:F,“<40”)
1、提成比率计算
=VLOOKUP(B3,$C$12:$E$21,3)
2、个人所得税计算
假如A2中是应税工资，则计算个税公式为：
=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)
3、工资条公式
=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3)，),“")注：
A3:标题行的行数+2,如果标题行在第3行，则A3改为A5
工资数据源!A$1:工资表的标题行的第一列位置
4、Countif函数统计身份证号码出错的解决方法
由于Excel中数字只能识别15位内的，在Countif统计时也只会统计前15位，所以很容易出错。不过只需要用 &”*“转换为文本型即可正确统计。=Countif(A:A,A2&”*")
1、各部门人数占比
统计每个部门占总人数的百分比
2、各个年龄段人数和占比
公司员工各个年龄段的人数和占比各是多少呢？
3、各个部门各年龄段占比
分部门统计本部门各个年龄段的占比情况
4、各部门学历统计
各部门大专、本科、硕士和博士各有多少人呢？
5、按年份统计各部门入职人数 每年各部门入职人数情况.今天分享的Excel公式虽然很全，但实际和会计实际要用到的excel公式相比，很多遗漏。欢迎做会计的同学们补充你工作中最常用到的公式。
Excel公式 第3篇
【关键词】Excel软件 公式函数 审计实务运用
Excel功能之强大让人叹为观止，我无数次地给同行以及学生说过的一句话是：“只有你想不到，没有Excel做不到”。Excel提供的大量的内置函数从本质上来说是一些预定义的公式，这些函数使用参数按预先定义好的顺序或结构进行计算。用户可以直接应用这些函数对活动工作表的某个区域内的数值进行系列计算。在实务中，我们只需要掌握部分函数的使用方法，便可事半功倍。笔者将会介绍几个较为实用的函数。
一、LEFT、MID、RIGHT、LEN和FIND函数
数据的整理工作在审计实务中占了很大比重，因为大部分从客户财务系统中导出的数据格式并不全都是规范有序的，那审计师就需要利用各种Excel函数从中提取审计所需要的数据信息。
（一）函数语法
1. LEFT（text， num_chars）：从字符串的最左端位置提取指定数量的字符；
2. MID（text， start_num， num_chars）：从字符串中间的任意位置提取指定数量的字符；
3. RIGHT（text，num_chars ）：从字符串的最右端位置提取指定数量的字符。
其中，text 表示要提取字符的字符串位置；start_num表示开始提取字符串的位置；num_chars 表示需要提取的字符数，忽略时为1。LEFT、MID和RIGHT 函数的运用基本一致，区别在于一个从左开始提取字符串， 一个从中间任意位置开始提取字符串，一个从右开始提取字符串。
4. LEN（text）：返回字符串中的字符个数，即计算字符串的长度。
5. FIND（find_text，within_text，s tart_num）：查找其他文本字符串（within_text） 内的文本字符串（find_text），并从within_text 的首字符开始返回find_text 的起始位置编号。其中，“find_text”表示要查找的字符串；“within_text”表示要在其中进行搜索的字符串，“start_num”表示起始搜索位置，在within_text 中第一个字符的位置为1，忽略时，start_num= 1。
（二）在审计中的运用实例
实务中经常会有财务人员将存货数量直接填列在摘要栏内，表1列示了类似情况，如果逐个摘录摘要文本中的数量信息并手工录入，既耗时且还容易出错，但若审计员了解上述一组提取数据的函数，提取出存货数量就会迎刃而解。
由于文本中每笔交易数量的位数不同，所以我们不能直接用RIGHT函数提取摘要栏内的数量信息。假设我们需要提取第一笔摘要栏“电源模块6ES73071EA000AA0：257”中的数量信息“257”， 则复合函数公式为“=RIGHT（B3，LEN（B3）-FIND（“：”，B3，1）”。拆分各公式后具体介绍如下：
LEN（B3）： 测量该字符串长度，结果为24；
FIND（“：”，B3，1）： 查找冒号在该字符串中所处位置， 结果为21；
RIGHT（B3，3）： 通过LEN 和FIND函数组合运用，得出需要在B3单元格中从右返回3个字符（即24-21= 3）， 结果为“257”。
熟练掌握后， 同理还可以提取处于最左边或者中间任意位置的数量信息。
二、VLOOKUP、HLOOKUP函数
VLOOKUP函数和HLOOKUP函数是用户在查找数据时使用频率非常高的Excel函数。利用这两个函数可以实现一些简单的数据查询。
（一）函数语法
1. VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
2. HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）：在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
其中，lookup_value ： 需要在数据表首列进行搜索的值。
table_array： 需要搜索数据的信息表。
col_index_num：满足条件的单元格在数组区域table_array中的列序号。
row_index_num：满足条件的单元格在数组区域table_array中的行序号。
range_lookup ： 在查找时， 是否需要精确匹配。如果为FALSE，则大致匹配，如果为TRUE 或忽略，则精确匹配（并区分全/ 半角）。在实务中， 一般选择大致匹配， 即“FALSE”。
VLOOKUP函数和HLOOKUP函数的语法非常相似，功能基本相同。主要区别是VLOOKUP主要用于搜索用户查找范围中的首列中满足条件的数据，并根据指定的列号返回对应的值，唯一的区别在于VLOOKUP函数按列进行查询，而HLOOKUP函数按行查询。
（二）在审计中的运用实例
在进行审计工作时，往往会需要以前年度的对比数，比如说按客户分类的应收账款余额就需要列示出该客户上年末的对比数，通过两期对比数，我们可以判断有无客户余额变化情况、有无新增客户等情况。表2、表3分别给出了某公司截至2013年12月31日以及2012年12月31日按客户分类的应收账款余额。endprint
首先，在“2013年应收账款余额”工作表的D2单元格内粘贴“2012年12月31日”字符串， 作为行标题。然后， 在“2013年”工作表D3单元格内使用VLOOKUP 函数， 公式为“=VLOOKUP（A3，'2012年应收账款明细账'！$A$3：$C$7，3，FALSE）”，依次类推，其中“$”符号是为了绝对引用和相对引用单元格信息，以便通过鼠标的单元格拖放功能， 让电脑自动生成D列内其他行次的单元格信息。在表2 和表3 的基础上， 经上述操作并略作整理， 便可得到表4 所示结果。其中，因为海克斯康测量技术是2013年度新增的一家客户，所以其在2012年12月31日没有余额，公式自动返回“#N/A”。
三、COUNTIF、SUMIF、SUBTOTAL函数
（一）函数语法
1. COUNTIF（range，criteria）：主要用于有目的地统计指定范围内满足特地条件的数据个数。其中，range必须是对单元格区域的直接引用或由引用函数产生的间接引用；crieria是定义的特点条件。
2. SUMIF（range，criteria，[sum_range]）：主要用于按指定条件在查找区域进行查找，并返回查找区域内满足条件数值的和。SUMIF的前两个参数和COUNTIF函数完全一致，如果不输入数据求和区域sum_range，则SUMIF函数会对查找区域range自动求和。
3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于筛选下的数据统计，是Excel中唯一一个可以只统计可见单元格的函数。其中：function_num参数使该函数具备求和、计数、求平均值等等功能，在审计实务中，运用最多的function_num是9，即对可见单元格求和。
（二）在审计中的运用实例
表5是某公司按产品分类的主营业务收入明细账，在审计实务中，审计人员可能需要统计出每类产品的销量以及销售金额。这里就可以用到COUNTIF函数以及SUMIF函数。
首先编制表6列出所需要整理的信息，在E3单元格使用COUNTIF函数，公式为“=COUNTIF（$A$3：$A$13，D3）”，在F3单元格使用SUMIF函数，公式为“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次类推，让电脑自动生成E列以及F列内其他行次的单元格信息，经上述操作之后便可得出如下表6。
当然，上述功能也可以通过筛选之后利用SUBTOTAL函数对可见单元格进行求和处理完成。
本文所介绍的几个公式函数仅仅是Excel庞大公式函数中的“冰山一角”，在既定的审计程序目标和有限的审计工作时间内，若想不断地提高工作效率，就需要审计人员不断地去探索和完善。
【参考文献】
[1]张宇. 浅谈Excel软件在审计实务中的运用[J].会计之友，2008（10）.
[2]孙良文.基于 Excel 的审计数据挖掘方法与路径研究[J.]财会通讯，2011（12）.
[3]Excel Home. Excel应用大全[M] . 北京：人民邮电出版社，2008.endprint
首先，在“2013年应收账款余额”工作表的D2单元格内粘贴“2012年12月31日”字符串， 作为行标题。然后， 在“2013年”工作表D3单元格内使用VLOOKUP 函数， 公式为“=VLOOKUP（A3，'2012年应收账款明细账'！$A$3：$C$7，3，FALSE）”，依次类推，其中“$”符号是为了绝对引用和相对引用单元格信息，以便通过鼠标的单元格拖放功能， 让电脑自动生成D列内其他行次的单元格信息。在表2 和表3 的基础上， 经上述操作并略作整理， 便可得到表4 所示结果。其中，因为海克斯康测量技术是2013年度新增的一家客户，所以其在2012年12月31日没有余额，公式自动返回“#N/A”。
三、COUNTIF、SUMIF、SUBTOTAL函数
（一）函数语法
1. COUNTIF（range，criteria）：主要用于有目的地统计指定范围内满足特地条件的数据个数。其中，range必须是对单元格区域的直接引用或由引用函数产生的间接引用；crieria是定义的特点条件。
2. SUMIF（range，criteria，[sum_range]）：主要用于按指定条件在查找区域进行查找，并返回查找区域内满足条件数值的和。SUMIF的前两个参数和COUNTIF函数完全一致，如果不输入数据求和区域sum_range，则SUMIF函数会对查找区域range自动求和。
3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于筛选下的数据统计，是Excel中唯一一个可以只统计可见单元格的函数。其中：function_num参数使该函数具备求和、计数、求平均值等等功能，在审计实务中，运用最多的function_num是9，即对可见单元格求和。
（二）在审计中的运用实例
表5是某公司按产品分类的主营业务收入明细账，在审计实务中，审计人员可能需要统计出每类产品的销量以及销售金额。这里就可以用到COUNTIF函数以及SUMIF函数。
首先编制表6列出所需要整理的信息，在E3单元格使用COUNTIF函数，公式为“=COUNTIF（$A$3：$A$13，D3）”，在F3单元格使用SUMIF函数，公式为“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次类推，让电脑自动生成E列以及F列内其他行次的单元格信息，经上述操作之后便可得出如下表6。
当然，上述功能也可以通过筛选之后利用SUBTOTAL函数对可见单元格进行求和处理完成。
本文所介绍的几个公式函数仅仅是Excel庞大公式函数中的“冰山一角”，在既定的审计程序目标和有限的审计工作时间内，若想不断地提高工作效率，就需要审计人员不断地去探索和完善。
【参考文献】
[1]张宇. 浅谈Excel软件在审计实务中的运用[J].会计之友，2008（10）.
[2]孙良文.基于 Excel 的审计数据挖掘方法与路径研究[J.]财会通讯，2011（12）.
[3]Excel Home. Excel应用大全[M] . 北京：人民邮电出版社，2008.endprint
首先，在“2013年应收账款余额”工作表的D2单元格内粘贴“2012年12月31日”字符串， 作为行标题。然后， 在“2013年”工作表D3单元格内使用VLOOKUP 函数， 公式为“=VLOOKUP（A3，'2012年应收账款明细账'！$A$3：$C$7，3，FALSE）”，依次类推，其中“$”符号是为了绝对引用和相对引用单元格信息，以便通过鼠标的单元格拖放功能， 让电脑自动生成D列内其他行次的单元格信息。在表2 和表3 的基础上， 经上述操作并略作整理， 便可得到表4 所示结果。其中，因为海克斯康测量技术是2013年度新增的一家客户，所以其在2012年12月31日没有余额，公式自动返回“#N/A”。
三、COUNTIF、SUMIF、SUBTOTAL函数
（一）函数语法
1. COUNTIF（range，criteria）：主要用于有目的地统计指定范围内满足特地条件的数据个数。其中，range必须是对单元格区域的直接引用或由引用函数产生的间接引用；crieria是定义的特点条件。
2. SUMIF（range，criteria，[sum_range]）：主要用于按指定条件在查找区域进行查找，并返回查找区域内满足条件数值的和。SUMIF的前两个参数和COUNTIF函数完全一致，如果不输入数据求和区域sum_range，则SUMIF函数会对查找区域range自动求和。
3. SUBTOTAL （function_num，ref1，ref2， …）：SUBTOTAL主要用于筛选下的数据统计，是Excel中唯一一个可以只统计可见单元格的函数。其中：function_num参数使该函数具备求和、计数、求平均值等等功能，在审计实务中，运用最多的function_num是9，即对可见单元格求和。
（二）在审计中的运用实例
表5是某公司按产品分类的主营业务收入明细账，在审计实务中，审计人员可能需要统计出每类产品的销量以及销售金额。这里就可以用到COUNTIF函数以及SUMIF函数。
首先编制表6列出所需要整理的信息，在E3单元格使用COUNTIF函数，公式为“=COUNTIF（$A$3：$A$13，D3）”，在F3单元格使用SUMIF函数，公式为“=SUMIF（$A$3：$A$13，D3，$B$3：$B$13）”，依次类推，让电脑自动生成E列以及F列内其他行次的单元格信息，经上述操作之后便可得出如下表6。
当然，上述功能也可以通过筛选之后利用SUBTOTAL函数对可见单元格进行求和处理完成。
本文所介绍的几个公式函数仅仅是Excel庞大公式函数中的“冰山一角”，在既定的审计程序目标和有限的审计工作时间内，若想不断地提高工作效率，就需要审计人员不断地去探索和完善。
【参考文献】
[1]张宇. 浅谈Excel软件在审计实务中的运用[J].会计之友，2008（10）.
[2]孙良文.基于 Excel 的审计数据挖掘方法与路径研究[J.]财会通讯，2011（12）.
Excel公式 第4篇
一般, 总课表的形式见图1 (表名为zkb总课表) 。
此类型的总课表是按班级和上课时间进行的, 主要是便于排课, 信息较为齐全或可补充得更为齐全, 基本可以包含所有的信息, 但除了班级课表一目了然外, 其他信息查找起来极为不便。
那么, 如何利用Excel公式从以上形式的总课表中较快查询获取各类相关信息呢?本文将分别从单一信息获取和综合信息获取两方面进行介绍。
一、单个信息的获取 (以场地调度表为例)
(一) 场地调度表的结构。
场地调度表的结构一般如图2 (表名为cdb场地表) , 为方便公式表述, 此处的场地调度表与图1的总课表同在一个工作簿中, 两者行标题中的星期节次完全对应。
(二) 输入并复制粘贴公式。
1.输入公式。
在图2所示表格的B2单元格中输入公式:B2=IF (ISERROR (MATCH ("*"&$A2&"*", 'zkb'!B$2:B$190, 0) ) , "", INDEX ('zkb'!B$2:B$190, MATCH ("*"&$A2&"*", 'zkb'!B$2:B$190, 0) ) &"/"&INDEX ('zkb'!$A$2:$A$190, MATCH ("*"&$A2&"*", 'zkb'!B$2:B$190, 0) ) ) 说明:此公式在总课表 (zkb) 的B列中查找是否出现A2单元格中的场地 (1-606) , 如果出现, 则把查找到的内容以及A列同一行的班级名称在B2单元格中显示出来, 否则显示为空。如果仅是需要知道场地是否使用, 那么, 只需B2=COUNTIF ('zkb'!B$2:B$4, "*"&$A2&"*") 进行计数即可, 如果计数结果0就说明该场地并未使用, 如果计数结果不为0则有使用。
2.复制并粘贴公式。
复制B2单元格的公式并粘贴至区域B2:D4, 可得结果如图3示, 即可得到一个信息完整的场地使用情况表。特别说明, 此处11网络2班使用场地2-502并未显示出来 (因为先找到11网络1班使用的2-502, 而此处只能显示一个班的) , 但并不影响对场地的管理和使用, 即如果仅是用于场地调度, 此方法是有效的。
3.信息选取。
一般来说, 场地管理部门并不需要这么多的信息, 一般仅需要知道某个场地在哪些时间有课就行了。因此, 这时可以仅起止周部分保留下来, 其他部分去掉, 此时可采用*号把多余的部分替换掉;再者, 如果起止周都是一样的从开学到期末, 那么可以把有内容的单元格 (即该场地有使用的) 替换为一个符号, 如1或V之类的, 这样就可以得到一个更为简洁的场地调度表。
4.课表制作。
类似可以得到教师个人课表或课程方面的课表, 当然, 由于此时Excel函数只是提取列中第一个数据, 其余数据会丢失, 造成提取后的信息不全的情况, 所以在使用此方法提取相关数据时, 需要根据使用范围来进行选择, 如果不影响使用, 那么可用此方法, 比如上面的场地调度表, 如仅是给场地管理部门开关门使用, 那么已足够了 (并不受11网络2班使用的场地2-502未显示出来影响) 。
二、综合信息的获取
(一) 总课表制作。
为了更方便地获得各方面的信息, 经作者实践摸索, 总结得到以下类型的总课表, 见图4。
以上课表包含的信息更为全面, 通过Excel的自动筛选功能, 可以根据需要非常方便地查获相应的信息, 使用起来极为方面。但此类型的总课表极不利于排课, 或者说, 根据不可能使用这种形式的表格对课表进行编排。而一般的教务管理系统都会生成第一种类型的总课表 (图1) , 却很少生成此第二种类型的总课表 (图4) 。所以, 以下讨论如何使用Excel公式快速把第一种类型的总课表转换成第二种类型的总课表。
(二) 课表类型的转换。
1.信息转换。
把每个班每次课的信息转换成一行 (如图5示) , 操作时只要对图1所示总课表采用复制粘贴即可。
2.输入公式。
把课表中的课程、起止周、任课教师及地点等信息分离出来, 在图5所示表格以下单元格中输入公式:
D2=LEFT (B2, FIND ("/", B2) -1) , 提取课程名称;
E2=MID (B2, FIND ("/", B2) +1, FIND ("", B2) -FIND ("/", B2) -1) , 提取起止周;
F2=MID (B2, FIND ("", B2) +1, FIND ("{", B2) -FIND ("", B2) -1) , 提取任课教师;
G2=MID (B2, FIND ("{", B2) +1, FIND ("}", B2) -FIND ("{", B2) -1) , 提取地点。
并复制向下粘贴, 即可得到如图6所示的结果。
3.进一步完善整理课表。
此处需要得到的是信息尽可能全面的课表, 所以, 还需要进一步整理或补充相关信息, 比如哪些班级是合班上课, 以及上课的班级人数等。都需要做进一步的处理和完善, 有些是可以从该表本身统计出来的, 有些则需要用到含有其他相关信息的表格。比如, 合班的信息可以从上表中统计得到, 即同一教师、同一地点、同一时间上课的班级就是合班的, 只要把以上信息用连接符连接起来, 使用Countif函数进行计数, 再按计数结果和连接的内容排序, 即可确定上课的班级有几个, 增加一列标出即可;另外, 关于班级人数, 不管是单班、合班或部分学生选课, 也可采用增加一列标出实际的上课人数, 当然, 对于部分学生选课的情况, 需要有一个学生的选课名单表, 把对应班级、课程的人数统计一下, 再粘贴过来, 或使用Vlookup函数对应过来。最后整理就可以得到如图4所示的信息全面的课表。其他的信息可以根据实际需要进行增加或减少。
另外, 以上第二步信息分离的公式部分, 可以使用Excel菜单“数据”“分列”来进行, 但如果数据规范得不够, 使用“分列”会造成后继整理数据的困难;如果数据都是标准的, 那么使用“分列”会更快一些, 此处仅做此提示。
摘要：本文从两个方面介绍利用Excel公式提取高职院校课程表中的相关信息, 以得到各类人员需要的各种形式的课表。一方面是提取信息单一的课表, 另一方面是综合性的课表。
关键词：EXCEL公式,信息提取,课表信息
参考文献
[1].刘晓芒.浅谈编排学院课表工作[J].中国科技信息, 2009
Excel常用运算公式 第5篇
2、用出生年月来计算年龄公式：=TRUNC((DAYS360(H6,”2009/8/30″,FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式：=CONCATENATE(MID(E2,7,4),”/”,MID(E2,11,2),”/”,MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别，可以输入以下公式：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,”男”,” 女”),IF(MOD(MID(C2,17,1),2)=1,”男”,”女”))公式内的“C2”代表的是输入身份证号码的单元格。
1、求和： =SUM(K2:K56) ——对K2到K56这一区域进行求和;
2、平均数： =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数;
3、排名： =RANK(K2，K$2:K$56) ——对55名学生的成绩进行排名;
4、等级： =IF(K2>=85,”优”,IF(K2>=74,”良”,IF(K2>=60,”及格”,”不及格”)))
5、学期总评： =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;
6、最高分： =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;
7、最低分： =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;
8、分数段人数统计：
(1) =COUNTIF(K2:K56,”100″) ——求K2到K56区域100分的人数;假设把结果存放于K57单元格;
(2) =COUNTIF(K2:K56,”>=95″)-K57 ——求K2到K56区域95～99.5分的人数;假设把结果存放于K58单元格;
(3)=COUNTIF(K2:K56,”>=90″)-SUM(K57:K58) ——求K2到K56区域90～94.5分的人数;假设把结果存放于K59单元格;
(4)=COUNTIF(K2:K56,”>=85″)-SUM(K57:K59) ——求K2到K56区域85～89.5分的人数;假设把结果存放于K60单元格;
(5)=COUNTIF(K2:K56,”>=70″)-SUM(K57:K60) ——求K2到K56区域70～84.5分的人数;假设把结果存放于K61单元格;
(6)=COUNTIF(K2:K56,”>=60″)-SUM(K57:K61) ——求K2到K56区域60～69.5分的人数;假设把结果存放于K62单元格;
(7) =COUNTIF(K2:K56,”<60″) ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格;
说明：COUNTIF函数也可计算某一区域男、女生人数，
如：=COUNTIF(C2:C351,”男”) ——求C2到C351区域(共350人)男性人数;
9、优秀率： =SUM(K57:K60)/55*100
10、及格率： =SUM(K57:K62)/55*100
11、标准差： =STDEV(K2:K56) ——求K2到K56区域(55人)的成绩波动情况(数值越小，说明该班学生间的成绩差异较小，反之，说明该班存在两极分化);
12、条件求和： =SUMIF(B2:B56,”男”，K2:K56) ——假设B列存放学生的性别，K列存放学生的分数，则此函数返回的结果表示求该班男生的成绩之和;
13、多条件求和： {=SUM(IF(C3:C322=”男”,IF(G3:G322=1,1,0)))｝ ——假设C列(C3:C322区域)存放学生的性别，G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5)，则此函数返回的结果表示求 一班的男生人数;这是一个数组函数，输完后要按Ctrl+Shift+Enter组合键(产生“{……｝”)。“{｝”不能手工输入，只能用组合键产生。
14、根据出生日期自动计算周岁：=TRUNC((DAYS360(D3,NOW( )))/360,0)
———假设D列存放学生的出生日期，E列输入该函数后则产生该生的周岁。
15、在Word中三个小窍门：
①连续输入三个“~”，按下回车键，可得一条波浪线。
②连续输入三个“-”，按下回车键，可得一条直线。
论EXCEL中公式与函数的应用 第6篇
我在此以“中数21班成绩表”37人为例讲解:
1 计算总分、名次、平均分、最高分、高低分、总人数
如表1具体方法如下:
(1) 总分:=SUM (C3:I3) , 其他同学的总分计算, 用填充柄拖动复制公式。
(2) 名次:=RANK (J3, $J$3:$J$39) 注意:要使用绝对引用。
(3) 平均分=AVERAGE (C3:I3)
(4) 最高分=MAX (C3:C39)
(5) 最低分=MIN (C3:C39)
(6) 总人数=COUNTA (B3:B39) 注意:算总人数时, 区域为人名时, 则用COUNTA, 当区域为数值时, 则用COUNT。
2 统计平均分的分段人数及占的百分比
如表2所示:
(7) 90-100分间的人数:=COUNTIF (L3:L39, ">=90")
(8) 算80-90分间的人数:=COUNTIF (L3:L39, ">=80") -COUNTIF (L3:L39, ">=90")
(9) 算70-80分间的人数:=COUNTIF (L3:L39, ">=70") -COUN-TIF (L3:L39, ">=80")
(10) 算60-70分间的人数:=COUNTIF (L3:L39, ">=60") -COUN-TIF (L4:L40, ">=70")
⑾算60分以下的人数:=COUNTIF (L7:L43, "<60")
⑿占的百分比=分段的人数/总人数=J47/37, 其他的均用填充柄拖动即可复制公式。
3 计算等级和奖金
如表3所示:
⒀等级:=IF (AND (C23>=90, D23>=90, E23>=90, F23>=90, G23>=90, H23>=90, I23>=90) , "一等", IF (AND (C23>=80, D23>80, E23>=80F23>=80, G23>=80, H23>=80, I23>=80) , "二", IF (AND (C23>=75D23>=75, E23>=75, F23>=75, G23>=75, H23>=75, I23>=75) , "三等", "无") ) )
⒁奖金:=IF (M23="一等", 1000, IF (M23="二等", 500, IF (M23="三等", 100, "无") ) )
4 小结
4.1 公式的使用
公式是对单元格中数据进行分析的等式, 它可以对数据进行加、减、乘、除或比较等运算。
Excel中公式语法:
即最前面是等号 (=) , 后面是参与计算的元素 (运算数) 和运算符。
1) 输入公式的方法
a) 选定要输入公式的单元格;
b) 在单元格中输入“=” (等号) ;
c) 在等号右侧输入公式内容;
d) 按回车键。
4.2 函数的使用
函数是预定义的内置公式。它使用被称为参数的特定数值, 按照语法的特定顺序进行计算。
函数语法:
以函数名称开始, 后面是左圆括号, 以逗号隔开的参数和右圆括号。
4.3 输入函数
1) 选定要插入函数的单元格;
2) 单击“插入”“函数”, 弹出对话框;
3) 在对话框中找出要计算的函数名;
4) 单击“确定”, 弹出对话框;
5) 在参数框中看默认的参数是否正确, 若不正确则重新选择区域;
6) 单击“确定”。
通过上述成绩表的各数据的计算分析, 相信你一定会处理这些数据吧!
摘要：EXCEL中的公式与函数已经广泛应用于数据统计当中, 该文通过“中数21班的成绩表”为例, 先计算总分、名次、平均分、最高分、高低分、总人数, 再统计分段人数及占的百分比值, 然后计算等级和奖金, 本实例能够为教师提供成绩管理方面的借鉴和参考。
关键词：EXCEL,公式与函数,计算
参考文献
[1]甘登岱, 徐建平.精通Office2000[M].北京:清华大学出版社, 2001.
[2]邢铁申, 冯冰.计算机应用基础[M].西安:西北工业大学出版社, 2007.
Excel公式 第7篇
综而观之, 本课由两大板块组成:第一个板块是以公式的概念和单元格的三种引用作为本课的基础内容;第二个板块是以三个函数辅以三个项目任务串联组成的主体内容。
分析教材后, 结合学生实际, 我如此确定课堂教学内容结构 (见左图) :
A.教学内容:公式与单元格引用、函数, 项目应用
B.任务一:求和函数 (使用默认参数)
C.任务二:求平均值函数 (必须修改参数表)
D.任务三:排位函数的使用 (相对引用绝对引用相结合)
E.函数的参数, 单元格的引用
一、第一个“陷阱”:从“=1+1”到单元格引用和函数, 初步了解函数
本课的第一个要解决的问题是让学生理解什么是公式, 我让学生打开一个工作簿, 在两个单元格中分别输入“1+1”和“=1+1”, 然后让学生观察结果, 看两个单元格的区别。然后告诉学生:公式是一个以“=”号开头的进行数据分析的等式。这样处理的目的是因为公式的定义虽说简单但却很抽象, 先让学生动动手做一做再抛出定义, 抽象的东西就显象具体了, 学生用一次简单操作就领会了公式比较抽象的概念。接下来让学生完成学生成绩表的总分计算, 一般学生会用刚才“=1+1”的方法逐个计算总分。针对这一做法, 我抛出一个问题, 如果这个成绩表上有12门功课, 学生人数有300人, 要求计算每个学生的总分, 怎么处理?由此引出单元格的引用和公式的复制问题。对于三种引用, 主要讲解相对引用和绝对引用, 我通过两个操作分别使用相对引用和绝对引用, 然后进行公式复制, 让学生观察结果, 分析两个公式的异同, 同时掌握相对引用和绝对引用的概念。这样, 就完成了基础板块内容的教学。
公式的定义与单元格的三种引用为接下来的函数环节作好了充分必要的铺垫, 我认为在函数的教学上应该做到先概括再具体化, 先让学生掌握函数的共性的东西, 然后才是对常用函数的学习与掌握, 这样有利于学生触类旁通、举一反三。于是我这样带着学生进入函数环节:函数是一种特殊的公式, 是EXCEL内置的公式, 它当然也以“=”开头, 其结构是=函数名 (参数) , 参数主要是单元格区域的引用, 参数可以有多个, 多个参数以逗号来分隔。这是对函数的高度的概括。接下来具体学习第一个函数:求和函数SUM。作为学生接触的第一个函数, SUM函数要细讲, 让学生充分了解函数的结构, 特别是参数。讲解完SUM函数后, 让学生完成项目的第一个任务, 在总分表上用求和函数计算出学生的总分, 这一任务学生都能顺利完成。
二、第二个“陷阱”:从默认参数到编辑参数, 深入理解函数参数
简短点评学生第一个任务的完成情况后, 介绍第二个函数:求平均值函数AVERAGE。讲解该函数时, 我故意在总分表上把总分字段改成平均分字段, 完成讲解后, 我让学生在平均分工作表上用AVER-AGE函数完成对学生平均分的操作。这又是一个陷阱, 学生一般没有想到平均分工作表上的清单和总分表上的清单是不一样的, 这里多了总分字段, 以刚才任务一的方法来操作, 就把总分字段当成是一个单科成绩了。巡视发现, 绝大部分学生落入了陷阱, 其中少数学生发现了问题:算出的平均分大于100分了。我顺势引导:为什么会出问题?函数没有用错, 错只会错在参数上。最后再引导学生修改函数的参数, 加深了他们对函数的整体理解。
三、第三个“陷阱”:由RANK函数的使用引出单元格绝对引用, 全面掌握参数
接下来的第三个函数排位函数RANK把函数的内容推向了一个新的层次:首先这个函数可以有三个参数, 作为降序排位的话用到两个参数;其次在实际应用上, 参数分别要用到相对引用和绝对引用。讲解该函数时重点强调两个参数的意义, 操作时我故意只对第一个学生作出成绩排位, 不复制函数完成全班学生的成绩排位, 然后让学生名次工作表上用RANK函数完成学生成绩排名。这里是我设下的第三个陷阱, 不出我所料, 学生在操作该函数时几乎全军覆没, 第二个参数都想不到用绝对引用, 少数学生在复制函数后发现了问题, 我便让他们观察操作结果, 分析问题所在, 提示在复制函数时, 什么参数应该随着目标单元格变化而变化, 什么参数不应该随首目标单元格变化而变化。经提示后, 大多数学生都能把数组区域的引用改成绝对引用了, 演示操作后完成了该环节的教学。
项目操作是本课主体内容的重点, 它的作用有两方面, 一是强化学生对单元格引用的理解, 二是检测学生对函数使用的掌握程度。可以说教学内容和项目操作是相互服务的, 教学内容的安排与项目任务的设计是统一的。本课讲了三个函数, 分别是求和函数SUM、求平均值函数AVERAGE、排位函数RANK, 分析教学内容, 我挖掘出了两个层次。一是AVERAGE函数与SUM函数之间的层次。在项目任务设计时故意设计成求和时只需用函数的默认参数即可, 而在完成第二个项目任务时必须编辑AVERAGE的参数, 同时用IBM公司的经典广告词“思考留给自己, 工作留给机器”告诉学生不要轻信了计算机, 活跃了课堂气氛。二是RANK函数和前两个函数之间的层次。这一层次包括两个方面, 一是该函数使用了多个参数, 二是该函数是相对引用和绝对引用的结合。在讲解RANK完成排名时, 我故意对两个参数都采用相对引用, 不复制函数, 让学生去操作项目任务三。
Excel公式 第8篇
关键词：教务工作效率,Excel公式设计
教务工作涉及成绩管理、工作量统计、课酬计算、教学日志管理等等, 需要统计大量的数据, 编制大量的教务文件。目前, 许多学校教务管理都在信息化管理平台进行, 教务工作效率有了很大提高, 但仍有大量数据统计、教务表格不能由教务管理系统完成。此外, 系统导出的文件格式不是标准格式, 需要进行格式转换。如果人工处理此类教务工作, 由于数据量大, 必然花费大量时间。
Excel是一种电子表格软件, 具有很强的表格内和表格间计算功能, 具有强大的数据库管理功能, 能对大量数据进行排序、分类、统计和查询等处理。若能设计出实用的Excel计算公式, 由于公式可复制或使用填充柄填充, 数据修改后, Excel会按公式自动重新计算, 大量统计和文件格式转换可快速完成, 教务工作效率可得到很大提高。下面介绍几项常规教务工作用到的Excel公式设计。
成绩统计分析、工作量统计、课酬计算的Excel公式设计
在成绩统计分析中, 总分、平均分、最高分、最低分是基本数据指标, 通过函数SUM () 、AVERAGE () 、MAX () 、MIN () 可轻松计算得到。运用条件计数函数COUNTIF () 可统计及格人数、不及格人数、成绩优秀或良好人数, 如要统计某科及格人数只需在单元格中输入公式:=COUNTIF (B2:B40, ”>=60”) , 此处“B2:B40”为某科成绩单元格区域, 其余依此类推。
统计工作量和课酬时, 上课次数由COUNT () 函数计算获得, 乘以课时系数即得到课时数, 课时数和课酬标准相乘得到课酬。假设课酬统计工作表中D2单元格为课时数, 在E2中输入课酬标准, F2用来存放课酬, 那么在F2单元格中输入公式:=D2*E2或输入函数:=PRODUCT (D2:E2) 即可计算出课酬。
上述统计数据的计算由单个函数即可解决, 简单易学, 计算机应用教程或一级MS Office教程都有详细讲解, 本文不再赘述。
不同工作表中信息比对的Excel公式设计
毕业生名单审核时, 为确保毕业生名单准确无误, 常需将毕业生名单和学籍管理系统导出的学生名单进行比对, 检查两份名单有无出入。如果人数较少, 将两份表中的名单按同样的方式排序, 再将一份表中的名单复制到另一份表中, 逐个对照, 就能完成比对。但如果毕业生有数千名之多, 当两份表中名单顺序不一致, 学生人数不一致时, 这种方法显然不是一种有效的方法。下面介绍用Excel查找与引用函数解决这一问题的公式设计。
学籍管理系统导出的名单放在“系统中名单”工作表中 (如表1所示) , 毕业生名单放在“毕业生”工作表中 (如表2所示) , 只要检查“毕业生”工作表中学生名单能否在“系统中名单”工作表中查找到, 就能核查出两份表中名单有无出入。
具体操作是:在“毕业生”工作表D1、E1、F1单元格中分别输入“系统序号”、“系统年级”、“系统班级”, 在D2单元格中输入公式:=INDEX (系统中名单!$A$2:$A$3000, MATCH ($C2, 系统中名单!$C$2:$C$3000, 0) ) 。该公式含义是:先用匹配函数MATCH () 查找出“毕业生”工作表C2单元格学生在“系统中名单”工作表中对应的顺序, 以此顺序确定索引行, 再用索引函数INDEX () 确定索引列A列与索引行交叉的单元格位置, 将该位置内容引用到D2单元格中。公式中参数“C2”为匹配内容, “系统中名单!$C$2:$C$3000”为匹配范围, “系统中名单!$A$2:$A$3000”为索引列范围, 参数“0”表示准确匹配。考虑后面的操作需用到公式复制或填充柄, 单元格引用为绝对引用, 公式中必须使用绝对地址。将D2单元格中公式填充到E2、F2中, E2公式中索引列A改为E, F2公式中索引列改为F, 对应公式分别为:=INDEX (系统中名单!$E$2:$E$3000, MATCH ($C2, 系统中名单!$C$2:$C$3000, 0) ) 和=INDEX (系统中名单!$F$2:$F$3000MATCH ($C2, 系统中名单!$C$2:$C$3000, 0) ) , 然后选中D2:F2区域, 往下拖动填充柄填充到D2901:F2901 (毕业生假设为2900人) 。结果如表3所示。
从表中可以看出, “王六”对应的结果为“#N/A”, 说明在系统中查无此人, 经查后发现是输入错误, 应为“王五”。单元格区域D2:F2501计算完毕后, 以“系统班级”作为关键字进行排序, 含“#N/A”的行集中在一起, 有问题的毕业生名单一目了然, 数千甚至上万名毕业生的名单比对瞬间就可完成。
教学日志格式转换的Excel公式设计
目前, 教学日志一般由任课教师本人填入教务管理系统, 系统导出的教学日志是按周次、课次排序但班级没有分开的数万条记录组合在一起的数据库 (如图1所示) 。此种教学日志不便于存档管理和检查审阅。因此, 教学日志由系统导出后, 必须转换成班级标准日志格式 (如图2所示) 。
教学日志格式转换的一般方法是:先对数据库进行自动筛选, 得到某个班级一个学期的记录后, 按周次、星期、节次将科目、授课内容、课堂情况、作业情况、考勤情况、备注、作业的相关内容逐项复制粘贴到“课堂教学日志”中。一个学校班级数多达数十个甚至上百个, 每班每学期有19周的周教学日志, 数据库记录多达数万条, 逐项复制粘贴工作量非常大, 耗时非常多。如果能设计一个Excel公式, 自动引用数据库中相关内容, 由于计算机处理速度会非常快, 数十个班的教学日志格式转换很快就能完成。下面介绍一种新型的运用Excel公式进行教学日志格式转换的操作。
自动筛选由于一个学校一学期的教学日志记录可能多达数万条, 为简化计算量, 先按班级进行自动筛选, 得到某个班级一学期教学日志, 日志记录一般五百条左右。
建立“班级”工作表将自动筛选结果复制粘贴到“班级”教学日志工作表中 (如图3所示) 。在“班级”工作表L1单元格中输入开学日期 (如2009年9月7日) , 在M1单元格中输入第1周结束日期 (如2009年9月11日) 。
编辑第1周课堂教学日志建立工作表“1”, 编辑标准格式课堂教学日志, 在C2单元格中输入周次“1”。
计算周起止时间在“1”工作表E3单元格中输入公式:=班级!$L$1+ ($C$2-1) *7, 在H3单元格中输入公式:=班级!$M$1+ ($C$2-1) *7, 这两个公式能够根据每学期第1周起止时间和周次自动计算周起止时间。考虑到后面操作中要复制这两个公式, 此处引用单元格使用绝对地址。
引用数据库记录引用数据库记录使用查找与引用函数。在工作表“1”单元格C6中输入公式:{=INDEX (班级!F$2:F$500, MATCH ($C$2&"1"&LEFT ($B6, 1) , 班级!$C$2:$C$500&班级!$D$2:$D$500&班级!$E$2:$E$500, 0) ) }。因为要根据对应周
次、对应星期和对应节次引用相关内容, 因此先用匹配函数MATCH () 根据上述条件确定引用行, 由于涉及周次、星期、节次三个限定条件, 而且条件之间是逻辑与的关系, 因此公式中选用连接符“&”。单元格引用中“班级!$C$2:$C$500”、“班级!$D$2$D$500”、“班级!$E$2:$E$500”表示匹配的数据范围, 公式中“班级!F$2:F$500”表示引用列范围, 参数“0”表示准确匹配。后面要将此公式复制粘贴到其他单元格, 引用地址必须使用绝对地址。由于公式中涉及数组的匹配计算, 因此输入完公式后按Ctrl+Shift+Enter组合键锁定数组公式。
计算第1周的教学日志将C6单元格公式往下填充到C7:C19, 将公式中表示星期一的1相应改为2, 3, 4, 5;然后选中C6:C19, 向右填充到D6:E19和I6:I19, 将公式中引用的列范围“班级!F$2:F$500”中的F相应改为G、H、I, 这样第1周的教学日志就计算完成了 (如图4所示) 。
计算整个班级的教学日志新建工作表2, 3, , 19, 将工作表1全选、复制、粘贴到工作表2, 3, , 19。在每张工作表表示周次的C2单元格中相应输入2, 3, , 19。单元格中内容会根据周次自动计算, 个别单元格中出现“#N/A”表示没有教学日志, 删除即可。至此整个班级1~19周的教学日志就全部计算完成了。从此操作过程显而易见, 有了第1周的计算模板后, 其他周次的教学日志只要2个步骤就可完成。
计算另一个班级的教学日志在数据库中自动筛选出该班级的记录, 复制粘贴到“班级”工作表中。此时工作表1~19单元格中的内容会根据更新的工作表中的记录自动重新计算;然后选中工作表1~19作为工作组, 在C3单元格中输入班级名称, 至此另一个班级的教学日志即全部计算完成, 另存为一个文档即可。可以看出, 有了第1个班级的计算模板后, 其他班级19周的教学日志只要4个简单的步骤就可完成, 非常快捷方便。
Excel提供了11类函数, 每一类有若干个不同的函数, 具有强大的计算功能和处理问题的能力。通过函数的组合和嵌套, 函数的公式可以千变万化。如果在教务工作中能善用Exce函数, 巧妙设计Excel公式, 教务工作可以得到极大简化, 工作效率可大大提高。
参考文献
[1]教育部考试中心.一级MS Office教程[M].天津:南开大学出版社, 2008:195-203.
[2]曹晓光.Excel函数在工程数据处理中的应用[J].山西建筑, 2010, 36 (19) :367-367.
[3]李飞.Excel提高教学管理工作效率的探索与实践[J].广西广播电视大学学报, 2009, 20 (4) :38-40.
[4]胥家萍.巧用Excel编制工资报表[J].电脑学习, 2010, 2 (1) :112-114.
Excel公式 第9篇
职业院校主要是培养学生的职业技能。公式与函数的学习主要是教会学生如何运用Excel 2003解决他们在以后实际工作中遇到的数据运算的问题, 真正培养他们解决实际问题的能力。
在公式与函数教学过程中, 首先要让学生弄清楚公式与函数的概念, 掌握公式的基本语法规则, 学会如何编辑公式, 然后再用实例教会学生如何使用公式与函数进行数据运算。
1 公式
Excel 2003的公式包含运算符、单元格地址、数值、函数等元素, 公式运算的结果是一个值, 公式类似于数学的表达式。不同的是每个公式都以等号“=”开头, 等号后面才是公式的表达式。
Excel 2003的公式可以使用算术运算符、字符运算符和比较运算符进行运算。
算术运算符包含:+、-、*、/、^、%。算术运算符的优先运算顺序依次为:先百分号“%”;其次乘方“^”;再其次乘除“*”、“/”;最后加减“+”、“-”。同级运算按从左到右的顺序进行。如果有圆括号, 则先算括号内的, 后算括号外的。
Excel 2003还提供了字符运算符和比较运算符。它的字符运算符可以连接字符, 比较运算符可以根据条件进行判断, 返回逻辑真“TRUE”或逻辑假“FALSE”。
使用公式时可以对同一工作表的数据进行计算, 也可以对同一工作簿不同工作表的数据进行计算, 还可以对不同工作簿的数据进行计算。
在单元格或数据编辑栏的“编辑栏”输入公式, 即可进行计算。
2 函数
Excel 2003提供了大量的函数。每个函数有一个函数名, 函数名后面有一对括号, 括号内是函数的参数。函数的一般格式为:函数名 (参数)
有的函数不需要参数, 但许多函数需要参数。函数的参数可以是数字、字符、单元格地址、公式或其它函数。当函数的参数不止一个时, 各参数之间通常用逗号分开。
使用“插入函数”对话框调用函数即可进行计算。
函数还可以用在公式中。在公式中使用函数进行计算时, 应该掌握函数的名称、功能、格式、以及参数的意义。
3 实例
在图2成绩表中, 用公式和函数计算各学生的“总分”、“平均分”和“成绩等级”。将平均分大于或等于85的同学评为“优秀”, 平均分小于85且大于等于75的同学评为“良好”, 平均分小于75且大于等于60的同学评为“及格”, 小于60为“不及格”。
3.1 利用公式计算各学生的“总分”
选择单元格H3, 在该单元格或数据编辑栏的“编辑栏”输入公式“=D3+E3+F3+G3”, 并按“Enter”键或单击数据编辑栏的“输入”按钮, 计算出学生李光明“总分”, 拖动H3单元格的填充柄将公式复制到单元格区域H4:H11, 即可计算所有学生的总分。
3.2 利用公式计算各学生的“平均分”
选择单元格I3, 在该单元格或数据编辑栏的“编辑栏”输入公式“=H3/4”, 并按“Enter”键或单击数据编辑栏的“输入”按钮, 计算出学生李光明“平均分”, 拖动I3单元格的填充柄将公式复制到单元格区域I4:I11, 即可计算所有学生的平均分。
3.3 利用函数计算各学生的“总分”
选择单元格H3, 选择“插入”“函数”命令, 打开“插入函数”对话框, 选择函数类别为“常用函数”, 在“选择函数”列表框选择函数“SUN”, 对话框下半部分将显示该函数的格式和功能说明, 单击“确定”按钮, 屏幕上又出现输入参数对话框, 把“Number1”框的参数改为D3:G3, 或先单击“Number1”框右边的“压缩对话框”按钮, 折叠对话框后, 在“公式与函数”工作表中选择需要计算的单元格区域D3:G3, 选定区域的地址将自动填写到“Number1”框内, 再单击该“压缩对话框”按钮展开对话框。单击“确定”按钮, H3单元格显示计算结果324, 数据编辑栏的编辑框显示出计算公式“=SUM (D3:G3) ”, 用鼠标向下拖动H3单元格的填充柄, 直到选中区域H3:H11为止。松开鼠标左键后, 即可计算出所有学生的总分。
3.4 使用公式和函数计算各学生的“成绩等级”
在J2单元格中输入“成绩等级”, 选择单元格J3, 在公式编辑栏中输入函数表达式“=IF (I3>=85, “优秀”, IF (I3>=75, “良好”, IF (I3>60, “及格”, “不及格”) ) ) ”, 按“Enter”键或单击数据编辑栏的“输入”按钮, 即可计算出李光明的“成绩等级”:良好。拖动J3单元格的填充柄将公式复制到单元格区域J4:J11, 即可计算所有学生的成绩等级。
IF函数可包含多达七级的嵌套。嵌套函数作为参数使用时, 它返回的数值类型必须与参数使用的数值类型相同。本题中第二个IF函数是第二级函数, 因为它是第一个IF函数的参数。第三个IF函数则为第三级函数, 以此类推。
本实例中对第一个同学的成绩等级计算时, 先选择函数IF, 如图1所示, 输入函数的前两个参数, 再将鼠标点击在第三个参数输入框内, 重新从左边的“函数”中再次选择IF函数, 此时在第一个函数中嵌套了一个IF函数。
按照上一步的设置在第二个IF中输入前两个参数“I3>=75”, “良好”, 再在第三个参数的输入框内再次嵌套一个IF函数。在最后一个IF内的三个参数分别输入“I3>=60”, “及格”, “不及格”, 最后单击“确定”按钮。
上述计算的结果如图2所示。
通过对“公式与函数”理论的讲解和实例的操作, 使学生真正掌握了公式与函数的概念, 掌握了公式的基本语法规则, 知道如何编辑公式, 如何使用公式与函数进行数据运算, 为他们在以后工作中解决实际问题打下坚实基础。
摘要：公式与函数在数据运算中有着极其重要的位置。文章以成绩表数据计算为例, 阐述了公式与函数的基本语法规则、公式的编辑和利用公式与函数进行数据运算的方法, 旨在培养学生的数据处理能力, 为学生在以后实际工作中解决数据运算的问题打下坚实基础。
关键词：Excel 2003,公式,函数
参考文献
[1]吴振峰.计算机应用基础[M].高等教育出版社, 2009.
Excel公式 第10篇
打开一个Excel文件, 设置两个文本框: 上面的文本框用于输入阿拉伯数字, 下面的文本框用于显示对应的大写金额。
(1) 设置界面外观
拖动鼠标, 选中区域“B2:F4”, 单击鼠标右键, 运行“设置单元格格式”命令, 在弹出的“单元格格式”对话框中, 选择“边框”选项卡, 在“颜色”中选择红色, 在“边框”中选择4个方向的线条, 在“线条样式”中选择合适的线条, 如图1所示。
单击“确定”按钮, 区域“B2:F4”就产生了一个红色的边框, 如图2所示。
选中“C2:F4”区域, 运行“设置单元格格式”命令, 运行“对齐”|“合并单元 格”命令 , 将单元格 合并。然 后在“单元格格式”对话框中 , 选择“图案”选项卡 , 在单元格底纹中选择“绿色”, 单击确定, 并在单元格B3中输入提示信息“请输入阿拉伯数字 :”, 适当调整单元格的宽度 , 这样就设置好区域“B2:F4”的外观了。
为了使区域“B7:F9”有同样的外观, 可利用格式刷轻松搞定。最后在单元格B8输入提示信息“金额大写:”, 这样整个界面就设计好了, 如图3所示。
(2) 输入公式
将小写金额转换为大写金额。
为了使在单元格C2中输入阿拉伯数字时, 在单元格C7中自动显示大写金额, 需要在单元格C7中输入以下公式:
其中, TEXT (INT (C2) ," [dbnum2]") 的作用是将单元格C2中的数字转换为大写。
(3) 测试该公式
在单元格C2中输入123456.75, 在单元格C7中自动显示对应大写金额, 如图4所示。
