表格公式函数.docx
- 文档编号:10293033
- 上传时间:2023-02-09
- 格式:DOCX
- 页数:23
- 大小:375.70KB
表格公式函数.docx
《表格公式函数.docx》由会员分享,可在线阅读,更多相关《表格公式函数.docx(23页珍藏版)》请在冰豆网上搜索。
表格公式函数
如果您从事设备管理工作,有近千台机械设备需要定期进行精度检测,那么,就得每天翻阅“设备鉴定台账”来寻找“到期”的设备——实在是太麻烦了!
用Excel建立一本“设备鉴定台账”是不是方便得多?
方法是:
用Excel的IF函数嵌套TODAY函数来实现设备“到期”自动提醒。
首先,运行Excel,将“工作簿”的名称命名为“设备鉴定台账”,输入各设备的详细信息、上次鉴定日期及到期日期(日期的输入格式应为“年-月-日”,如:
2003-10-21,如图1)。
图1
然后,选中图1所示“提示栏”下的F2单元格,点击插入菜单下的函数命令,在“插入函数”对话框中选择“逻辑”函数类中的IF函数,点击[确定]按钮,就会弹出“函数参数”对话框,分别在Logical_test行中输入E2=TODAY()、value_if_true行中输入“到期”、Value_if_false行中输入“""”(如图2),并点击[确定]按钮。
这里需要说明的是:
输入的""是英文输入状态下的双引号,是Excel定义显示值为字符串时的标识符号,即IF函数在执行完真假判断后显示此双引号中的内容。
为了醒目,可在“单元格属性”中将F2单元格的字体颜色设置为红色。
图2
最后,拖动“填充柄”,填充F列以下单元格即可。
我们知道Excel的IF函数是一个“条件函数”,它的语法是“IF(logical_test,value_if_true,value_if_false)”,具体地说就是:
如果第一个参数logical_test返回的结果为真,则执行第二个参数Value_if_true的结果,否则执行第三个参数Value_if_false的结果;Excel的TODAY函数[语法是TODAY()]是返回当前系统日期的函数。
实际上,本文所应用的IF函数语句为IF(E2=TODAY(),"到期",""),解释为:
如果E2单元格中的日期正好是TODAY函数返回的日期,则在F2单元格中显示“到期”,否则就不显示,TODAY函数返回的日期则正好是系统当天的日期。
Excel的到期提醒功能就是这样实现的。
目标:
教您轻松画好一条复杂的函数曲线。
实例:
给出了一个函数式所对应的曲线的例子。
难点分析:
一些教师会遇到画函数曲线的问题吧!
如果想快速准确地绘制一条函数曲线,可以借助EXCEL的图表功能,它能使您画的曲线既标准又漂亮。
您一定会问,是不是很难学呀?
其实一点儿也不难,不信您就跟我试一试。
以绘制y=|lg(6+x^3)|的曲线为例,其方法如下:
1)自变量的输入
在某张空白的工作表中,先输入函数的自变量:
在A列的A1格输入“X=”,表明这是自变量。
再在A列的A2及以后的格内逐次从小到大输入自变量的各个值;实际输入的时候,通常应用等差数列输入法,先输入前二个值,定出自变量中数与数之间的步长,然后选中A2和A3两个单元格,使这二项变成一个带黑色边框的矩形,再用鼠标指向这黑色矩形的右下角的小方块“■”,当光标变成“+”字型后,按住鼠标拖动光标到适当的位置,就完成自变量的输入。
2)输入函数式
在B列的B1格输入函数式的一般书面表达形式,y=|lg(6+x^3)|。
在B2格输入“=ABS(LOG10(6+A2^3))”,B2格内马上得出了计算的结果。
这时,再选中B2格,让光标指向B2矩形右下角的“■”,当光标变成“+”时按住光标沿B列拖动到适当的位置即完成函数值的计算。
3)绘制曲线
点击工具栏上的“图表向导”按钮
,选择“X,Y散点图”,然后在出现的“X,Y散点图”类型中选择“无数据点平滑线散点图”。
此时可察看即将绘制的函数图像,发现并不是我们所要的函数曲线。
单击“下一步”按钮,选中“数据产生在列”项,给出数据区域。
单击“下一步”按钮。
单击“下一步”按钮,单击“完成”按钮。
这时曲线就在我们面前了。
4)相关问题
需要注意:
如何确定自变量的初始值,数据点之间的步长是多少,这是要根据函数的具体特点来判断,这也是对使用者能力的检验。
如果想很快查到函数的极值或看出其发展趋势,给出的数据点也不一定非得是等差的,可以根据需要任意给定。
从简单的三角函数到复杂的对数、指数函数,都可以用EXCEL画出曲线。
如果用得到,您还可以利用EXCEL来完成行列式、矩阵的各种计算,进行简单的积分运算,利用迭代求函数值(如x^2=x^7+4,可用迭代方法求x值),等等,凡是涉及计算方面的事,找EXCEL来帮忙,它一定会给您一个满意的答案。
举一反三:
讲解了EXCEL绘制各种函数曲线的方法,相信您对EXCEL已经有了很多认识和理解。
但EXCEL的功能远不只这些,这里只是给您一个思路,在实际工作中,如果遇到更复杂的情况,您只要结合上面的例子,对相应的公式进行调整和扩展,就一定能完成任务。
单击“常用”工具栏上的“粘贴函数”按钮
,在“粘贴函数”对话框中可以查到很多函数,点击其中的一个函数,在下面就有函数的名称和解释,相信您一定会用的,并且会建立更美观、更实用的工作表。
Excel中要将人民币小写金额转换成大写格式,将自定义格式类型中的“G/通用格式”改为“G/通用格式“元””来实现。
但在转换小数时却出现了问题,比如¥6,050.09只能转换为“陆仟零伍拾.零玖圆”。
那怎么解决这一先天不足呢?
方法有以下两种(以下均对B1单元格转换,假设B1通过函数ROUND()四舍五入取得二位小数的小写金额数,其值为¥6,050.09)。
一、在三个连续的同行单元格中转换
1.B2中输入公式“=IF(B1<0,"金额为负无效",INT(B1))”,计算结果为¥6,050.00,然后点击“格式→单元格→数字→特殊→中文大写数字→确定”,B2显示“陆仟零伍拾”,再点击“格式→单元格→数字→自定义”,将“类型”编辑框中的“G/通用格式”修改为:
(“人民币”)G/通用格式“元”,此时B2显示:
“(人民币)陆仟零伍拾元”。
2.C2中输入“=IF(B1<0,"",INT(B110)-INT(B1)10)”,同样C2改为:
G/通用格式“角”,C2显示:
“零角”。
3.D2中输入“=IF(B1<0,"",INT(B1100)-INT(B110)10)”,同样D2改为:
G/通用格式“分”,D2显示:
“玖分”。
这样在连续的三个同行单元格中实现了大写金额格式的转换,如图1。
为了使转换符合财务格式,B2应右对齐,C2列宽无间隔,且不设左右边框线,D2要左对齐。
图1显示大写金额
二、用Excel提供的函数转换
个位为0,则不显示;不为0,显示“XXX元”;不为0、且角位和分位均为0,显示“XXX元整”;元角分位均为0,则显示“零元整”。
小数点后面第一位为0、且元位或分位有一为0,则不显示;为0、且元位和分位均不为0,则显示“零”;不为0,显示“X角”。
小数点后面第二位为0,则显示“整”;不为0,显示“X分”。
根据以上思路在B2中输入公式:
“IF(B1<0,"金额为负无效",IF(OR(B1=0,B1=""),"(人民币)零元整"));
IF(B1<1,"(人民币)",TEXT(INT(B1),"(人民币)G/通用格式")&&"元")&&IF(INT(B110)-INT(B1)10=0);
IF(INT(B1)(INT(B1100)-INT(B110)10)=0,"","零"),TEXT(INT(B110)-INT(B1)10,"")&&"角")&&IF((INT(B1100)-INT(B110)10)=0,"整",TEXT((INT(B1100)-INT(B1*10)*10),"")&&"分")))”。
确认后B2显示:
“(人民币)陆仟零伍拾元零玖分”。
以后使用时将公式复制到有关单元格中,若转换的对象B1发生了变化,可点击“编辑→替换”将B1替换掉。
两种转换测试结果如图2所示。
图2两种方式对话结果
[编者] 前面我们介绍了《Excel常用函数的功能和使用方法》,现在我们学以致用,介绍一系列用这些函数实现的数据统计实例解析。
今天我们做一个奖金计算表。
某公司规定:
一个月奖金基数为300元,病假1天扣15元,事假一天扣30元,旷工一天扣60元,扣完为止。
使用这个奖金计算表时,只要将员工的出勤情况记录在表中,该员工的奖金将自动计算出来,兼有考勤和计算奖金两种功能。
自动统计表做好以后还可以保存成模板,以便以后使用。
文章末尾提供.xls文件供大家下载参考。
本文所涉及到的Excel函数有:
1、COUNTIF(Range,Criteria)
2、MONTH(serial_number)
3、TODAY()
注意:
图中符号的含义是:
B表示病假,S表示事假,G表示旷工,Q表示出勤,J表示法定休息日。
1、启动Excel2003,按照图1所示的样式,制作一张二维表格,并填入相关文本。
2、将A1至BM1单元格合并成一个单元格后,输入表格标题(如“晓风公司考勤及奖金表”),并设置好字体、字号等。
3、将C2和D2单元格合并后,输入公式:
=MONTH(TODAY())&"月",用于显示出当前的月份。
注意:
在上述单元格中直接输入月份数值也是可行的。
4、选中BM4单元格,输入公式:
=IF(300-COUNTIF(C4:
BL4,"B")*7.5-COUNTIF(C4:
BL4,"S")*15-COUNTIF(C4:
BL4,"G")*30>0,300-COUNTIF(C4:
BL4,"B")*7.5-COUNTIF(C4:
BL4,"S")*15-COUNTIF(C4:
BL4,"G")*30,0),确认,计算出第一位员工的当月奖金额。
注意:
上述公式的含义是:
用“COUNTIF(C4:
BL4,"B")、COUNTIF(C4:
BL4,"S")、COUNTIF(C4:
BL4,"G")”分别用于统计每位员工的病假、事假、旷工的次数,再分别乘以“7.5、15、30”(每“半天”为一次)计算出所要扣去的奖励额,再用基数“300”减去上述扣去的金额。
如果(IF)差大于“0”,则显示差(即员工的奖金额),否则显示“0”(扣完为止)。
5、再次选中BM4单元格,用“填充柄”将上述公式复制到下面的单元格中,用于计算其它员工的当月奖励额。
6、清空表格中所填写的数据,执行“文件→保存”命令,打开“另存为”对话框(如图2),按“保存类型”右边的下拉按钮,选中“模板(*.xlt)”选项,在文件名方框中输入一名称(如“奖金表.xlt”),单击“保存”按钮,将制作好的表格文档保存为模板。
7、以后,每个月第一天,启动Excel,执行“文件→新建”命令,展开“新建工作簿”任务窗格(如图3)。
点击其中的“本机上的模板”选项,打开“模板”对话框(如图4),选中“奖金表”模板文件,单击“确定”按钮即可新建一个空的“奖金表1”工作簿文档。
选中C2单元格(即保存月份的单元格),将鼠标移到编辑栏中,然后按一下F9功能键,将公式(=MONTH(TODAY())&"月")转换为具体的值(如“12月”),最后将文档保存一下。
8、以后就可以用这个计算表逐日对员工进行考勤,到了月底就可以自动计算出奖金数据了。
.xls文件下载
Excel自动评分计算表功能:
参加比赛的选手为20人,评委9人,去掉1个最高分和1个最低分后,求出平均分,然后根据平均分的高低排定选手的名次。
本实例所涉及到的函数有:
SUM(Number1,Number2……)
MAX(number1,number2……)
MIN(number1,number2……)
RANK(Number,ref,order)
一、评委评分表的制作
1、启动Excel2003,新建一空白工作簿。
2、在Sheet1工作表中,仿照如图1所示的样式,制作一份空白表格。
3、执行“文件→保存”命令(或按“Ctrl+S”组合键),打开“另存为”对话框(如图2)。
4、单击工具条上“工具”按钮,在随后出现的下拉列表(参见图2)中,选择“常规选项”,打开“保存选项”对话框(如图3),设置好“打开权限密码”后,确定返回。
注意:
①密码需要重新确认输入一次。
②此处只需要设置“打开权限密码”,如果设置了“修改权限密码”,则评委在保存评分时,必须提供密码,反而造成不须要的麻烦。
5、然后取名(如1.xls)保存。
6、再执行“文件→另存为”命令,再次打开“另存为”对话框,然后仿照上面的操作重新设置一个密码后,另取一个名称(如2.xls)保存一下。
7、重复第6步的操作,按照评委数目,制作好多份工作表(此处为9份)。
二、汇总表的制作
1、新建一工作簿,仿照如图4所示的样式,制作一张空白表格。
(图片较大,请拉动滚动条观看)
2、分别选中B3至J3单元格,依次输入公式:
=[1.xls]sheet1!
B3、=[2.xls]sheet1!
B3……=[3.xls]sheet1!
B3,用于调用各评委给第一位选手的评分。
注意:
请将评委评分表和汇总表保存在同一文件夹内。
3、选中K3单元格,输入公式:
=(SUM(B3:
J3)-MAX(B3:
J3)-MIN(B3:
J3))/7,用于计算选手的最后平均得分。
4、选中L3单元格,输入公式:
=RANK(K3,$K$3:
$K$22),用于确定选手的名次。
5、同时选中B3至L3单元格区域,用“填充柄”将上将上述公式复制到下面的单元格区域,完成其它选手的成绩统计和名次的排定。
6、取名(如hz.xls)保存该工作簿。
注意:
在保存汇总表的时候,最好设置“打开权限密码”和“修改权限密码”。
三、电子评分表的使用
1、将上述工作簿文件放在局域网上某台电脑的一个共享文件夹中,供各们评委调用。
注意:
当我们移动整个工作簿所在的文件夹时,系统会自动调整公式相应的路径,不影响表格的正常使用。
2、比赛开始前,将工作簿名称和对应的打开权限密码分别告知不同的评委,然后通过局域网,让每位评委打开各自相应的工作簿(如1.xls、2.xls……)文档。
注意:
评委在打开文档中,系统会弹出一个如图5所示的对话框,输入“打开权限密码”,确定即可。
2、某位选手比赛完成后,评位将其成绩输入到相应的单元格中,并要求评委执行一下保存操作。
注意:
每次要求评委评分成执行一次保存操作,其目的是为了防止出现意外情况而造成数据丢失。
3、整个比赛结束后,主持人只要打开“hz.xls”工作簿,即可公布比赛结果了(如图6)。
(图片较大,请拉动滚动条观看)
注意:
主持人在打开“hz.xls”工作簿时,系统会弹出如图7所示的对话框,请单击其中的“更新”按钮。
(图片较大,请拉动滚动条观看)
每到学期结束时,教师的一项重要工作就是要统计学生的成绩,在电脑逐步普及的今天,我们就把这项繁杂的工作交给Excel去自动完成吧。
涉及函数:
AVERAGE、COUNTIF、IF、ISERROR、MAX、MIN、RANK、SUM
1、启动Excel,同时选中A1至K1单元格,按“格式”工具条上的“合并及居中”按钮,将其合并成一个单元格,然后输入统计表的标题(参见图1)。
(图片较大,请拉动滚动条观看)
2、根据统计表的格式,将有关列标题及相关内容输入到相应的单元格中(参见图1)。
3、选中J3单元格,输入公式:
=SUM(C3:
I3),用于计算第一位学生的总分。
4、选中K3单元格,输入公式:
=RANK(J3,$J$3:
$J$62),计算出第一位学生总分成绩的名次(此处,假定共有60位学生)。
注意:
如果将上述公式修改为:
=COUNTIF($J$3:
$J$62,">="&J3),同样可以计算出第一位学生的名次。
5、同时选中J3和K3单元格,将鼠标移至K3单元格右下角的成“细十字”状时(通常称这种状态为“填充柄”状态),按住左键向下拖拉至K62单元格,完成其他学生的总分及名次的统计处理工作。
6、分别选中C63、C64单元格,输入公式:
=MAX(C3:
C62)和=MIN(C3:
C62),用于统计“语文”学科的最高分和最低分。
7、选中C65单元格,输入公式:
=IF(ISERROR(AVERAGE(C3:
C62)),"",AVERAGE(C3:
C62)),用于统计“语文”学科的平均分。
注意:
直接用“=AVERAGE(C3:
C64)”公式也可以计算平均分,但是,如果成绩表中没有输入成绩时,这一公式将显示出一个错误的值,因此,我们用“ISERROR”函数来排除这一错误。
8、选中C66单元格,输入公式:
=SUM(C3:
C62),用于统计“语文”学科的总分。
9、同时选中C63至C66单元格,用“填充柄”将上述公式复制到D63至J66单元格中,完成其它学科及总分的最高分、最低分、平均分和总分的统计工作。
至此,一个基本的成绩统计表制作完成(参见xls文件下载),下面我们来进一步处理一下。
10、选中C3单元格,执行“格式、条件格式”命令,打开“条件格式”对话框(如图2),按最左边一个方框右侧的下拉按钮,在随后出现的下拉列表中,选“公式”选项,并在右侧的方框中输入公式:
=C3>=AVERAGE(C3:
C62),然后按“格式”按钮,打开“单元格格式”对话框,将字体颜色设置为“蓝色”。
再按“添加”按钮,仿照上面的操作,添加一个公式为“=C3 C62)”、字体颜色为“红色”的条件格式(参见图2)。 C62)”、字体颜色为“红色”的条件格式(参见图2)。 注意: 经过这样的设置后,当学生的“语文”成绩大于或等于平均分时,显示蓝色,反之显示红色。 C62)”、字体颜色为“红色”的条件格式(参见图2)。 11、再次选中C3单元格,按“格式”工具条上的“格式刷”按钮,然后在D3至J3单元格区域上拖拉一遍,将上述条件格式复制到相应的区域中,完成其他学科及总分的条件格式设置工作。 12、再一次选中C3单元格,打开“条件格式”对话框,将其中的两项公式修改为: =C3>=AVERAGE($C$3: $C$62)和=C3 $C$62),确定返回(参见图3)。 $C$62),确定返回(参见图3)。 依照上面的操作,将D3至J3单元格的条件格式中的公式修改为绝对引用(添加“$”符号)。 注意: 这样做的目的,是为了将其条件格式,通过“格式刷”复制到下面的各单元格中。 13、同时选中C3至J3单元格,按“格式”工具条上的“格式刷”按钮,然后在C4至J62单元格区域上拖拉一遍,将上述条件格式复制到相应的区域中,完成其他学生各科成绩及总分的条件格式设置工作。 把学生的成绩填入到表格中试试看,效果不错吧。 如果你经常要统计学生的成绩,我们将其保存为模板,方便随时调用。 $C$62),确定返回(参见图3)。 14、将工作表中的学生成绩等内容删除,执行“文件、保存(另存为)”命令,打开“另存为”对话框(如图4),按“保存类型”右侧的下拉按钮,在随后出现的下拉列表中,选“模板(*.xlt)”选项,然后给定一个名称(如“成绩统计”),按下保存按钮。 15、以后需要统计成绩时,启动Excel,执行“文件、新建”命令,展开“新建工作簿”任务窗格(如图5),点击其中的“本机上的模板”选项,打开“模板”对话框,双击“成绩统计”模板,即可新建一份新的成绩统计表。 16、将学生的成绩填入相应的单元格中,取名保存即可快速完成成绩统计处理工作。 函数名 功能 ABS 求出参数的绝对值。 AND “与”运算,返回逻辑值,仅当有参数的结果均为逻辑“真(TRUE)”时返回逻辑“真(TRUE)”,反之返回逻辑“假(FALSE)”。 AVERAGE 求出所有参数的算术平均值。 COLUMN 显示所引用单元格的列标号值。 CONCATENATE 将多个字符文本或单元格中的数据连接在一起,显示在一个单元格中。 COUNTIF 统计某个单元格区域中符合指定条件的单元格数目。 DATE 给出指定数值的日期。 DATEDIF 计算返回两个日期参数的差值。 DAY 计算参数中指定日期或引用单元格中的日期天数。 DCOUNT 返回数据库或列表的列中满足指定条件并且包含数字的单元格数目。 FREQUENCY 以一列垂直数组返回某个区域中数据的频率分布。 IF 根据对指定条件的逻辑判断的真假结果,返回相对应条件触发的计算结果。 INDEX 返回列表或数组中的元素值,此元素由行序号和列序号的索引值进行确定。 INT 将数值向下取整为最接近的整数。 ISERROR 用于测试函数式返回的数值是否有错。 如果有错,该函数返回TRUE,反之返回FALSE。 LEFT 从一个文本字符串的第一个字符开始,截取指定数目的字符。 LEN 统计文本字符串中字符数目。 MATCH 返回在指定方式下与指定数值匹配的数组中元素的相应位置。 MAX 求出一组数中的最大值。 MID 从一个文本字符串的指定位置开始,截取指定数目的字符。 MIN 求出一组数中的最小值。 MOD 求出两数相除的余数。 MONTH 求出指定日期或引用单元格中的日期的月份。 NOW 给出当前系统日期和时间。 OR 仅当所有参数值均为逻辑“假(FALSE)”时返回结果逻辑“假(FALSE)”,否则都返回逻辑“真(TRUE)”。 RANK 返回某一数值在一列数值中的相对于其他数值的排位。 RIGHT 从一个文本字符串的最后一个字符开始,截取指定数目的字符。 SUBTOTAL 返回列表或数据库中的分类汇总。 SUM 求出一组数值的和。 SUMIF 计算符合指定条件的单元格区域内的数值和。 TEXT 根据指定的数值格式将相应的数字转换为文本形式 TODAY 给出系统日期 VALUE 将一个代表数值的文本型字符串转换为数值型。 VLOOKUP 在数据表的首列查找指定的数值,并由此返回数据表当前行中指定列处的数值 WEEKDAY 给出指定日期的对应的星期数。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 表格 公式 函数