当前位置:文档之家› 用Excel创建经验公式并进行预测

用Excel创建经验公式并进行预测

用Excel创建经验公式并进行预测
用Excel创建经验公式并进行预测

用Excel2007创建经验公式并进行预测

据1997-12-16 00:00 作者:江苏陈岁松杨守礼修改

用公式表示事物与事物之间的数量关系,有两种情况:一种是确定关系,例如圆面积S和半径r具有如下关系,S=πr2,知道了圆半径,圆面积立即可以算出。这样的公式可以通过几何关系进行推导。另一种称为非确定关系,例如居民收入与商品零售额的关系,气温与冷饮销售量的关系,吸烟量与肺癌发病率的关系……等等,虽然我们知道它们有一定的关系,但无法直接用数学进行推导。如果要用公式表示它们之间的关系,只有用回归分析方法,用回归分析建立的公式称做经验公式。

用传统的回归分析建立经验公式是很麻烦的,计算过程也极易出错,用Excel的专用工具和专用函数建立经验公式,就使这一繁锁的工作变得轻而易举。

具体应用,我们将通过下面的两个例题(四种方法)来说明。这几种方法各有优点,但方法一最直观,方法四最简单。

例题一

某企业根据记录,企业的产量和生产费用如表1,现在需要分析产量(X)与生产费用(Y)间的关系,并建立经验公式。根据此公式,如果1999年的产量定为9千吨,预计生产费用是多少?

方法一用图形功能建立经验公式。

优点:能清晰地观察XY的几何图形,并直接获得经验公式,这对XY的关系毫不了解的情况特别有利。用图形功能建立经验公式,不仅可以处理简单的直线公式,还可以处理对数、指数、多项式、幂级数等非线性公式,既直观又简便。

具体操作如下:

1.在Excel的一个工作表中将以上数据录入Excel。

2.选取该数据区域,点击菜单“插入”/“散点图(曲线)”/“,完成XY图形曲线。

3.用右键点击XY图形上的XY曲线,出现对话框,选“加上趋势线…”,出现“趋势线”对话框。

4.“趋势线”对话框中的曲线“类型”,有“线性”、“对数”、“多项式”、“升幂”、“指数”、“移动平均”等6种可供选择。根据观察,选取一种适合的线型,点“选项”,选中“显示公式”和“显示R平方值”,然后点“确定”,XY图形上就会自动加上你选定的曲线公式(经验公式)和R2值(参见Excel图1)。其中“多项式”公式有“项”数的设置,一般取2项即可满足要求。

5.R称为相关系数,表示经验公式对原始数据拟合的程度,一般要求R2>0.8,观察经验公式和原曲线的拟合效果,并看R值的大小,如果曲线拟合不够理想,或R值偏低,不妨按第6步重新选择拟合公式再作一次比较,直到满意为止。

6.重新选择拟合公式的方法是,用右键点击XY图形的拟合曲线(曲线稍粗),注意不是点击原始的XY 曲线,再点“趋势线格式”。在“趋势线格式”对话框中重新选取“类型”和“选项”,另外选择一种曲线,以下的工作和第4步完全一样。

本例采用“线性”公式较好,获得的经验公式为y=12.896x+51.323(见Excel图1),相关系数R2=0.9403,拟合情况良好。当x=9时,y=12.896×9+51.323=167.39(万元),这就是该企业1999年预计的生产费用。

方法二用LINEST或LOGEST函数建立经验公式。

适用范围:在已经了解y=f(x)性质的情况下,直接获取经验公式的有关参数。LINEST用于线性公式,LOGEST用于指数曲线公式,使用这两个函数可以解决多元回归的复杂问题。

LINEST和LOGEST的语法相同:LINEST(变量Y,自变量X,Const,Stats)。“变量Y”是Y数据区域;“自变量X”是X数据区域,X可以是一个变量X1,也可以是多个变量(多元回归)X1、X2、……Xn;“Const”是截距处置,设置为1或省略时,截距值正常计算,设置为0时,截距按0计算;“Stats”是统计参数处置,设置为1时,返回全部统计参数,设置为0或省略时,只返回公式y=mnxn+…m2x2+m1x1+b的有关系数mn、…m2、m1、b等。返回的有关参数在Excel图格上的排列规则如表2。

有关统计参数的详细解释,涉及较多的数理统计理论,这里从略。我们可以根据例1的计算,Excel

图1的K2:L4区域就是LINEST的返回值,从相应的位置,看到b=51.3232、m1=12.8960、R2=0.9403,所得参数和方法一的结果完全一致,获得的经验公式也是y=12.896x+51.323。

需要指出的是,在使用LINEST函数时,因为返回参数不只一个,所以显示这些参数是一个区域,故操作要按Excel的“数组”处理,即同时按Shift、Ctrl和Enter三个键,完成一次操作。LINEST的操作步骤是:选定返回值的区域,例1只有b、m1两个参数,我们只要求显示R2,故选取2列3行即可(Excel图1的K2:L4区域),具体设置为LINEST(B2:I2,B1:I1,1,1),同时按Shift、Ctrl和Enter三个键,操作完成。

例题二

某地货运公司货运周转量Y(亿吨公里)与当地工农业总产值X1(亿元)、基本建设总投资X2(亿元)和人口总数X3(十万人)有关(见表3),可以建立一个3元经验公式。根据此公式,如果1999年该地工农业总产值预计为4.5亿元,基本建设总投资为2.3亿元,人口总数为5.34(十万人),预测1999年货运周转量是多少?

操作步骤如下:

1.在Excel表格中录入例2数据(Excel图2)。需要说明的是,利用数据分析工具获取经验公式的有关参数,原始数据只能竖直(列)排列。

2.点击菜单“工具”/“数据分析”/“回归”,在“回归”对话框中,“输入Y区域”为B2:B7,“输入X区域”为C2:E7,在“输出选项”中,选择“输出区域”的左上角单元格(本例取B13),点击“确定”,从B13单元格开始立即增加许多新内容(Excel图2的整个下半部分),包括根据经验公式计算的预测值、残差等。在这些新内容中我们还看到大量的统计参数,就一般应用来说,只需“系数”栏的“截距”(即系数b)、“X变量1”(即系数m1)、“X变量2”(即系数m2)、“X变量3”(即系数m3)和“R的平方”几个数据也就够了。相应的经验公式为y=2.8942X3+1.2835X2-1.3005X1-7.4251,相关系数R2=0.9990。

为了核对,我们也用LINEST函数作了一下验算,所得经验公式的参数完全相同(见Excel图2的F3:I5区域)。

将1999年X1、X2、X3的预计值代入经验公式,y=2.8942×5.34+1.2835×2.3-1.3005×4.5-

7.4251=5.13(亿吨公里),这就是该公司新一年的货运周转量。

方法三利用SLOPE函数、INTERCEPT函数和RSQ函数分别计算线性公式的斜率m、截距b和相关系数R2,从而获得经验公式。

SLOPE和INTERCEPT的语法一样,只有2项,每项仍用“,”点隔开。第1项是Y的数据区域,第2项是X的数据区域。对于例1,SLOPE(B2:I2,B1:I1),返回m=12.8960;INTERCEPT(B2:I2,B1:I1),返回b=51.3232;RSQ(B2:I2,B1:I1),返回R2=0.9403。所得结果均列入Excel图1的K7:L9区域,返回数值和方法一的结果相同。

方法四用TREND函数、FORECAST(直线)或GROWTH(指数曲线)函数,直接获得经验公式的拟合值和预测值,但不显示经验公式。

TREND的语法包含4项,TREND(变量Y,自变量X,预测X,截距处置)。“自变量X”可以是一个变量X1,也可以是多元变量X1、X2……Xn;“预测X”设置预测Y对应的X,当然对于多元情况,“预测X”也是一组变量;“截距处置”一般予以省略。对于例1,在B15单元格设置如下(Excel图1),TREND(SB2:SI2,SB1:SI1,B1),将第一项、第二项设置成“绝对地址”,是为了在复制公式时不改变这些设置。B15设置完成后,只需将光标指住单元格右下角,待光标变成十字,向右拖动,即可完成所有的拟合和预测计算。

FORECAST只用于线性回归,其语法为FORECAST(预测X,变量Y,自变量X),由于是一元方程,所以“预测X”只有一个。对于例1,在B16单元格设置如下(Excel图1),FORECAST(B1,SB2:SI2,SB1:SI1)。将光标指住B16单元格右下角(出现十字光标),向右拖动,同样完成所有的拟合和预测计算。用TREND 和FORECAST拟合和预测的结果完全相同。

EXCEL表格公式大全

E X C E L表格公式大全 集团标准化工作小组 #Q8QGGQT-GX8G08Q8-GNQGJ8-MHHGN#

Excel表格公式大全 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 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*+M2*+N2* ——假设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~分的人数;假设把结果存放于K58单元格; (3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~分的人数;假设把结果存放于K59单元格; (4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~分的人数;假设把结果存放于K60单元格; (5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ——求K2到K56区域70~分的人数;假设把结果存放于K61单元格; (6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ——求K2到K56区域60~分的人数;假设把结果存放于K62单元格; (7) =COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格; 说明:COUNTIF函数也可计算某一区域男、女生人数。

Excel函数公式

Excel函数公式 在会计同事电脑中,保保经常看到海量的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")&天" &合同续签日期(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,'本科")

Excel常用电子表格公式大全

Excel常用电子表格公式大全 2011-04-24 08:53:41 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 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单元格;

Excel常用电子表格公式大全【汇总篇】

Excel 常用电子表格公式大全【汇总篇】 篇一:Excel 常用电子表格公式汇总 Excel 常用电子表格公式汇总 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 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”代表的是输入身份证号码的单元格。 5、求和: =SUM(K2:K56)——对 K2 到 K56 这一区域进行求和; 6、平均数: =AVERAGE(K2:K56)——对 K2 K56 这一区域求平均数; 7、排名: =RANK(K2,K$2:K$56)——对 55 名学生的成绩进行排名; 8、等级: =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格"))) 9、 学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设 K 列、 M 列和 N 列分别存放着学生的“平 时总评”、“期中”、“期末”三项成绩; 10、最高分: =MAX(K2:K56) ——求 K2 到 K56 区域(55 名学生)的最高分; 11、最低分: =MIN(K2:K56) ——求 K2 到 K56 区域(55 名学生)的最低分; 12、分数段人数统计: (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," 说明:COUNTIF 函数也可计算某一区域男、女生人数。 如:=COUNTIF(C2:C351,"男") ——求 C2 到 C351 区域(共 350 人)男性人数; 1 / 10

15个常用的Excel函数公式

15个常用的Excel函数公式,拿来即用 1、查找重复内容 =IF(COUNTIF(A:A,A2)>1,"重复","") 2、重复内容首次出现时不提示 =IF(COUNTIF(A$2:A2,A2)>1,"重复","") 3、重复内容首次出现时提示重复 =IF(COUNTIF(A2:A99,A2)>1,"重复","")

4、根据出生年月计算年龄 =DATEDIF(A2,TODAY(),"y") 5、根据身份证号码提取出生年月 =--TEXT(MID(A2,7,8),"0-00-00") 6、根据身份证号码提取性别 =IF(MOD(MID(A2,15,3),2),"男","女") 7、几个常用的汇总公式 A列求和:=SUM(A:A)

A列最小值:=MIN(A:A) A列最大值:=MAX (A:A) A列平均值:=AVERAGE(A:A) A列数值个数:=COUNT(A:A) 8、成绩排名 =RANK.EQ(A2,A$2:A$7) 9、中国式排名(相同成绩不占用名次) =SUMPRODUCT((B$2:B$7>B2)/COUNTIF(B$2:B$7,B$2:B$7))+1 10、90分以上的人数

=COUNTIF(B1:B7,">90") 11、各分数段的人数 同时选中E2:E5,输入以下公式,按Shift+Ctrl+Enter =FREQUENCY(B2:B7,{70;80;90}) 12、按条件统计平均值 =AVERAGEIF(B2:B7,"男",C2:C7) 13、多条件统计平均值 =AVERAGEIFS(D2:D7,C2:C7,"男",B2:B7,"销售")

Excel表格公式大全

沁园春·雪 北国风光,千里冰封,万里雪飘。望长城内外,惟余莽莽;大河上下,顿失滔滔。 山舞银蛇,原驰蜡象,欲与天公试比高。 须晴日,看红装素裹,分外妖娆。江山如此多娇,引无数英雄竞折腰。惜秦皇汉武,略输文采;唐宗宋祖,稍逊风骚。 一代天骄,成吉思汗,只识弯弓射大雕。 俱往矣,数风流人物,还看今朝。 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。

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、平均数:=A VERAGE(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、分数段人数统计:

常用excel函数公式大全

常用的excel函数公式大全 一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,"") 说明:如果是错误值则显示为空,否则正常显示。

2、IF多条件判断返回值 公式:C2 =IF(AND(A2<500,B2="未到期"),"补款","") 说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2) 说明:如果返回值大于0说明在另一个表中存在,0则不存在。

2、统计不重复的总人数 公式:C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。 四、求和公式

1、隔列求和 公式:H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2 =SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法

3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如"*A*"就表示a前和后有任意多个字符,即包含A。

4、多条件模糊求和 公式:C11 =SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11) 说明:在sumifs中可以使用通配符* 5、多表相同位置求和 公式:b2 =SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。 6、按日期和产品求和

用Excel做考勤统计的详细方法

用Excel做考勤统计的详细方法 花了整整五天的时间,终于完成了。虽然还不是很完善,但从零开始,边学边做,到最后终于可以用了,还是有那么点小小的成就感的。 下面将具体方法详述如下,欢迎提出建议: 上班安排: 假设某公司有甲、乙、丙、丁四个部门,以甲部门为例(因为其他部门的方法是一样的,只不过时间设置不同而已),其上下班时间安排是: A班:7:30-16:30 B班:12:00-21:00 C班:10:00-19:00 D班:8:30-17:30 考勤规则:上班时间后5分钟内打卡不算迟到,加班半小时以下不计加班。 首先,按名称整理好每个人的上下班的打卡时间(有电子打卡机的可以直接导入数据,手动打卡钟的就只能手动输入时间了) 第二步,在整理好的上下班时间工作表的第一行依次输入姓名(即A1格)、日期(即B1格)、排班(即C1格)、上班时间(后面的以此类推)、下班时间、考勤结果(上班)、考勤结果(下班),标准下班时间、加班时间、加班时间修正等行名 第三步,如果有几个部门,且每个部门的上下班时间不一致,则最好按部门将员工分类在同一个工作表的不同工作薄里, 第四步,设置单元格的格式 1、凡是用时间表示的,都用“hh:mm”的格式 2、记得在输入时间的时候关闭输入法 第五步,下面将进行具体的计算公式设置(以甲部门的A班为例,其他部门的不同班次,只是公式里的时间不同) 1、上班的考勤结果计算公式: =IF(AND(D2>=VALUE("07:35"))=TRUE,"迟到",IF(D2=0,"未打卡","")) 此公式的意思是,如果“D2”格,即上班时间列中的时间大于等于“7:35”,则显示“迟到”,如果“D2”格中无数据,即为“0”的时候,则显示“未打卡”,以上两个条件都不符合的时候,则显示为空白,即正常上班的意思; 2、下班的考勤结果计算公式: =IF(AND(E2>=VALUE("16:30"))=TRUE,"加班",IF(E2=0,"未打卡","早退")) 此公式的意思是,如果“E2”格,即下班时间列中的时间大于等于“16:30”,则显示为“加班”,如果“E2”格中无数据,即为“0”的时候,则显示“未打卡”,以上两个条件都不符合的时候,则显示为“早退” 3、加班时间的计算公式 =IF((E2-H2)<0,"0",E2-H2) 此计算结果本来只需要“E2-H2”即可,即用“下班打卡时间”减去“标准下班时间”即可,但这样的计算结果有可能会产生负数,比方说员工早退的时候,以至于造成后面的计算产生错误,因此需要调整一下公式。此公式的意思是:如果“E2-H2”的计算结果小于“0”,则将计算结果显示为“0”,否则显示“E2-H2”的计算结果。 4、加班时间修正的计算公式 =IF(HOUR(E2)-IF(((E2-H2)*24)>=1,16,16.5)+(IF(MINUTE(I2)>=30,0.5,0))<0,"",HOUR(E2

Excel表格公式大全(苦心整理)

Excel表格公式大全 来源:鲍利的日志 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 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)——对K2K56这一区域求平均数; 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单元格;

Excel统计函数大全

Excel函数应用教程:统计函数 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

EXCEL常用函数公式大全与举例

EXCEL常用函数公式大全及举例 一、相关概念 (一)函数语法 由函数名+括号+参数组成 例:求和函数:SUM(A1,B2,…) 。参数与参数之间用逗号“,”隔开(二)运算符 1. 公式运算符:加(+)、减(-)、乘(*)、除(/)、百分号(%)、乘幂(^) 2. 比较运算符:大与(>)、小于(<)、等于(=)、小于等于(<=)、大于等于(>=)、不等于(<>) 3. 引用运算符:区域运算符(:)、联合运算符(,) (三)单元格的相对引用与绝对引用 例: A1 $A1 锁定第A列 A$1 锁定第1行 $A$1 锁定第A列与第1行 二、常用函数 (一)数学函数 1. 求和 =SUM(数值1,数值2,……) 2. 条件求和 =SUMIF(查找的范围,条件(即对象),要求和的范围) 例:(1)=SUMIF(A1:A4,”>=200”,B1:B4) 函数意思:对第A1栏至A4栏中,大于等于200的数值对应的第B1列至B4列中数值求和 (2)=SUMIF(A1:A4,”<300”,C1:C4)

函数意思:对第A1栏至A4栏中,小于300的数值对应的第C1栏至C4栏中数值求和 3. 求个数 =COUNT(数值1,数值2,……) 例:(1) =COUNT(A1:A4) 函数意思:第A1栏至A4栏求个数(2) =COUNT(A1:C4) 函数意思:第A1栏至C4栏求个数 4. 条件求个数 =COUNTIF(范围,条件) 例:(1) =COUNTIF(A1:A4,”<>200”) 函数意思:第A1栏至A4栏中不等于200的栏求个数 (2)=COUNTIF(A1:C4,”>=1000”) 函数意思:第A1栏至C4栏中大于等1000的栏求个数 5. 求算术平均数 =AVERAGE(数值1,数值2,……) 例:(1) =AVERAGE(A1,B2) (2) =AVERAGE(A1:A4) 6. 四舍五入函数 =ROUND(数值,保留的小数位数) 7. 排位函数 =RANK(数值,范围,序别) 1-升序 0-降序 例:(1) =RANK(A1,A1:A4,1) 函数意思:第A1栏在A1栏至A4栏中按升序排序,返回排名值。 (2) =RANK(A1,A1:A4,0) 函数意思:第A1栏在A1栏至A4栏中按降序排序,返回排名值。 8. 乘积函数 =PRODUCT(数值1,数值2,……) 9. 取绝对值 =ABS(数字) 10. 取整 =INT(数字) (二)逻辑函数

如何用EXCEL统计问卷

如何用excel统计调查问卷 有些人可能经常要对调查问卷进行统计,使用手写的方法(就是在纸上写正字的方法)虽然很方便,不过不方便在一定条件下进行统计,如在男士的条件下,使用诺基亚的手机有多少人。而用excel可以很方便得出此类统计结果,并且由于数据输入excel方便备查,也可以导入access等数据库再进行较复杂的处理。另一方面,因为excel大部分人都会,所以可以几台电脑同时录入数据,汇总时只需将数据复制到一个表中,简单快捷。 调查问卷的问题可以分为三类:单选题、多选题、主观题。主观题比较难统计,本文不涉及这方面的。以下所说的用excel统计只涉及用excel 算出答案个数和百分比的最基本的统计。 一、录入问卷原始数据 下面是一简化了的调查问卷: 手机使用的调查 1、您的姓别是: 1、先生 2、女士 2、您使用过的手机的品牌有: 1、诺基亚 2、摩托罗拉 3、索爱 4、三星 5、联想 6、其他 假设问卷有10份。录入数据如下图:

在此,有几个要注意的问题: 1、录入数据前要对每张问卷进行编号,方便录入后问卷答案的备查,也方便对数据排序后返回到原始录入的状态。 2、录入时将问题的答案如ABCD等替换为1234,如上图。毕竟录入数字比录字母要快得多。因此在设计问卷时可考虑将问题的选项设为1234,避免录入时头脑将字母转化为数字时出现错误。 3、单选和多选题每道题的答案均只用一个单元格,这也是为了方便录入。多选题的答案当然也可以分开,不过会影响录入速度,分开则会方便以后使用其他软件对数据进行处理。 4、如上图,将第一行冻结起来,使问卷数多时,录入时依然可看到各个字段名,减少出错。(冻结的方法:选中要冻结行下边一行和要冻结列右边一列的那个单元格,再从菜单里点窗口--冻结窗格就可以了!如要冻结第一行,只要点中A2单元格,然后菜单,冻结窗格) 5、统计区暂时放在问卷原始数据的下面。 6、数据录入的两种方法。一是直接在单元格中输入,左手方向键,右手小键盘。因为问卷的答案统一为数字,所以直接输入也有较快的速度。二是使用excel记录单输入,左手Tab 键,右手小键盘。选中数据区域的任意一个单元格,执行“数据→记录单”命令,打开“记录单”窗体(如下图),点“新建”即可录入新记录,Tab键向下移动光标,Shift+Tab向上移动光标,Enter键完成新记录的输入,并自动打开新的录入窗口,方便下条记录的输入。

Excel表格公式大全 太强大了!

Excel表格公式大全太强大了! 2014-02-23免费订阅??每天一点经济学 1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。 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单元格;

Excel常用的函数计算公式大全(一看就会)

计算机等级考试 =公式名称(参数1,参数2,。。。。。) =sum(计算范围) =average(计算范围) =sumifs(求和范围,条件范围1,符合条件1,条件范围2,符合条件2,。。。。。。) =vlookup(翻译对象,到哪里翻译,显示哪一种,精确匹配) =rank(对谁排名,在哪个范围里排名) =max(范围) =min(范围) =index(列范围,数字) =match(查询对象,范围,0) =mid(要截取的对象,从第几个开始,截取几个) =int(数字) =weekday(日期,2) =if(谁符合什么条件,符合条件显示的内容,不符合条件显示的内容) =if(谁符合什么条件,符合条件显示的内容,if(谁符合什么条件,符合条件显示的内容,不符合条件显示的内容)) EXCEL的常用计算公式大全 一、单组数据加减乘除运算: ①单组数据求加和公式:=(A1+B1) 举例:单元格A1:B1区域依次输入了数据10和5,计算:在C1中输入=A1+B1 后点击键盘“Enter(确定)”键后,该单元格就自动显示10与5的和15。 ②单组数据求减差公式:=(A1-B1) 举例:在C1中输入=A1-B1即求10与5的差值5,电脑操作方法同上; ③单组数据求乘法公式:=(A1*B1) 举例:在C1中输入=A1*B1即求10与5的积值50,电脑操作方法同上; ④单组数据求乘法公式:=(A1/B1) 举例:在C1中输入=A1/B1即求10与5的商值2,电脑操作方法同上; ⑤其它应用: 在D1中输入=A1^3即求5的立方(三次方); 在E1中输入=B1^(1/3)即求10的立方根 小结:在单元格输入的含等号的运算式,Excel中称之为公式,都是数学里面的基本 运算,只不过在计算机上有的运算符号发生了改变——“×”与“*”同、“÷”与 “/”同、“^”与“乘方”相同,开方作为乘方的逆运算,把乘方中和指数使用成分数 就成了数的开方运算。这些符号是按住电脑键盘“Shift”键同时按住键盘第二排 相对应的数字符号即可显示。如果同一列的其它单元格都需利用刚才的公式计算,只 需要先用鼠标左键点击一下刚才已做好公式的单元格,将鼠标移至该单元格的右下 角,带出现十字符号提示时,开始按住鼠标左键不动一直沿着该单元格依次往下拉到 你需要的某行同一列的单元格下即可,即可完成公司自动复制,自动计算。

excel常用公式详解

一、数字处理 1、取绝对值 =ABS(数字) 2、取整 =INT(数字) 3、四舍五入 =ROUND(数字,小数位数) 二、判断公式 1、把公式产生的错误值显示为空 公式:C2 =IFERROR(A2/B2,'') 说明:如果是错误值则显示为空,否则正常显示。

2、IF多条件判断返回值 公式:C2 =IF(AND(A2<500,B2='未到期'),'补款','') 说明:两个条件同时成立用AND,任一个成立用OR函数。 三、统计公式 1、统计两个表格重复的内容 公式:B2 =COUNTIF(Sheet15!A:A,A2)

说明:如果返回值大于0说明在另一个表中存在,0则不存在。 2、统计不重复的总人数 公式:C2 =SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)) 说明:用COUNTIF统计出每人的出现次数,用1除的方式把出现次数变成分母,然后相加。 四、求和公式

1、隔列求和 公式:H3 =SUMIF($A$2:$G$2,H$2,A3:G3) 或 =SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)说明:如果标题行没有规则用第2个公式 2、单条件求和 公式:F2 =SUMIF(A:A,E2,C:C) 说明:SUMIF函数的基本用法

3、单条件模糊求和 公式:详见下图 说明:如果需要进行模糊求和,就需要掌握通配符的使用,其中星号是表示任意多个字符,如'*A*'就表示a前和后有任意多个字符,即包含A。

4、多条件模糊求和 公式:C11 =SUMIFS(C2:C7,A2:A7,A11&'*',B2:B7,B11) 说明:在sumifs中可以使用通配符* 5、多表相同位置求和 公式:b2 =SUM(Sheet1:Sheet19!B2) 说明:在表中间删除或添加表后,公式结果会自动更新。 6、按日期和产品求和

利用EXCEL进行数据统计若干技巧

利用Excel 进行数据统计的若干技巧 一、认识常用的统计函数: 1、SUM :计算单元格区域内所有数据的和。 表达式为:=SUM (D2: 2、A VERAGE :计算单元格区域内所有数据的算术平均数。 表达式为:=A VERAGE (D2: F50) 3 、COUNT 表达式为:=COUNT (D2:F50) 4、COUNTIF 表达式为:=COUNTIF (D2:F50, 5、MAX :返回一组数值中的最大值。 表达式为:=MAX (D2:F50) 6、MIN :返回一组数值中的最小值。 表达式为:=MIN (D2:7、MIDB 8、LARGE :返回数据中第K 个最大值。 表达式为:=LARGE(F2:F34,9、SMAL :返回数据中第K 个最小值。 表达式为 10、RANK 表达式为:利用好统计公式中的定位符$ 例:RANK(I2,$I$2:$I$180) 11、EXACT :比较两个字符串是否完全相同(区分大小写),如果完全相同,返回TREU ,否则返回FALSE 。 表达式为:=EXACT(D2,J2)

12、V ALUE:将代表数值的文本字符串转换成数值。 表达式为:=V ALUE(F2) 13 注意:表达式中输入的数据、符号必须在英文状态下输入。 二、简单数据的简便统计: (一)数据整理。 1、按单位整体集中数据(班为单位),统一编序号,删除与统计无关项目。把学校、班级列调整到靠近数据区。(操作方法:剪切要移动列→选中要移入的目标列→点击“【插入已剪切的单元格】”) 2、按升序或降序方式逐项清理各科数据,处理不合理数据(文本数据,超满分数据)。 注意:利用升降序工具进行操作时,数据必须至少有一方与数据区域相连。而按菜单【数据】→【排序】操作则可避免此问题。 3、划分分数线。 按升序或降序方式:例:按A段人数(3500*40%=1400),选中语文科第1400行数据,将语文科降序排列,选中单元格即语文科A段分数线;其它学科照此类推。 (二)利用分类汇总工具进行数据统计。

excel表格公式大全

一、在Word中三个小窍门: ①连续输入三个“~”可得一条波浪线。 ②连续输入三个“-”可得一条直线。 连续输入三个“=”可得一条双直线。 一、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比 如: A1〉1时,C1显示红色 0“条件格式”,条件1设为: 公式 =A1=1 2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。 条件2设为: 公式 =AND(A1>0,A1<1) 3、点“格式”->“字体”->“颜色”,点击绿色后点“确定”。 条件3设为: 公式 =A1<0 点“格式”->“字体”->“颜色”,点击黄色后点“确定”。 4、三个条件设定好后,点“确定”即出。 二、EXCEL中如何控制每列数据的长度并避免重复录入 1、用数据有效性定义数据长度。 用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置","有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。 还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"。

2、用条件格式避免重复。 选定A列,点"格式"->"条件格式",将条件设成“公式=COUNTIF($A:$A,$A1)>1”, 点"格式"->"字体"->"颜色",选定红色后点两次"确定"。 这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。 三、在EXCEL中如何把B列与A列不同之处标识出来? (一)、如果是要求A、B两列的同一行数据相比较: 假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: “单元格数值” “不等于”=B2 点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 (二)、如果是A列与B列整体比较(即相同数据不在同一行): 假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: “公式”=COUNTI F($B:$B,$A2)=0 点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。 四、EXCEL中怎样批量地处理按行排序 假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作? 由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字 排序。所以,这一问题不能用排序来解决。解决方法如下: 1、假定你的数据在A至E列,请在F1单元格输入公式: =LARGE($A1:$E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列。如有需要可用“选择性粘贴/数值” 复制到其他地方。

相关主题
文本预览
相关文档 最新文档