实训五 Excel公式与函数的使用

实训五Excel公式与函数的使用及工作表的编辑和格式化

（1）建立公式

复制实训四所建立的工作表，在I2单元格中输入“总分”，在J2单元格中输入“平均分”，并使用公式计算各同学的总分，使用函数计算各同学的平均分。

（2）设置单元格格式

①将标题“学生成绩统计表”位于A1:J1正中并将该字设置成字号为20，颜色为红色，底纹为黄色。

图11-14

②设置“平均分”列中数据只保留两位小数，并且数据靠左显示，“总分”列中所有数据后保留一位小数，居中显示。

③给单元格区域加边框

将A1:J9单元格区域设置为双线外边框，细的内边框线。

（3）移动、复制工作表

移动工作表，将“Shee1”将其改名为“成绩统计表1”，并移动到“Sheet2”后。

复制工作表，为“成绩统计表1”建立复本，并将其改为“成绩统计表2”。

（4）调整行高和列宽

选中第2行和第4行，设置行高为22，列宽为合适的列宽。

将其他行使用鼠标拖动的方式粗略调整行高和列宽。

（5）保存工作簿。

(完整版)excel-公式大全-包含所有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。 30.GEOMEAN 用途：返回正数数组或数据区域的几何平均值。可用于计算可变复利的平均增长率。 语法：GEOMEAN(number1，number2，...)

excel常用函数公式介绍

excel常用函数公式介绍 excel常用函数公式介绍1：MODE函数应用 1MODE函数是比较简单也是使用最为普遍的函数，它是众数值，可以求出在异地区域或者范围内出现频率最多的某个数值。 2例如求整个班级的普遍身高，这时候我们就可以运用到了MODE 函数了 3先打开插入函数的选项，之后可以直接搜索MODE函数，找到求众数的函数公式 4之后打开MODE函数后就会出现一个函数的窗口了，我们将所要求的范围输入进Number1选项里面，或者是直接圈选区域 5之后只要按确定就可以得出普遍身高这一个众数值了 excel常用函数公式介绍2：IF函数应用 1IF函数常用于对一些数据的进行划分比较，例如对一个班级身高进行评测 2这里假设我们要对身高的标准要求是在170，对于170以及170之上的在备注标明为合格，其他的一律为不合格。这时候我们就要用到IF函数这样可以快捷标注好备注内容。先将光标点击在第一个备注栏下方 3之后还是一样打开函数参数，在里面直接搜索IF函数后打开 4打开IF函数后，我们先将条件填写在第一个填写栏中， D3>=170，之后在下面的当条件满足时为合格，不满足是则为不合格 5接着点击确定就可以得到备注了，这里因为身高不到170，所以备注里就是不合格的选项 6接着我们只要将第一栏的函数直接复制到以下所以的选项栏中就可以了

excel常用函数公式介绍3：RANK函数应用 2这里我们就用RANK函数来排列以下一个班级的身高状况 3老规矩先是要将光标放于排名栏下面第一个选项中，之后我们打开函数参数 4找到RANK函数后，我们因为选项的数字在D3单元格所以我们就填写D3就可了，之后在范围栏中选定好，这里要注意的是必须加上$不然之后复制函数后结果会出错 5之后直接点击确定就可以了，这时候就会生成排名了。之后我们还是一样直接复制函数黏贴到下方选项栏就可以了。

excel函数公式大全

excel函数公式大全 类别一:数据库和清单管理函数 DAVERAGE 返回选定数据库项的平均值 DCOUNT 计算数据库中包含数字的单元格的个数 DCOUNTA 计算数据库中非空单元格的个数 DGET 从数据库中提取满足指定条件的单个记录 DMAX 返回选定数据库项中的最大值 DMIN 返回选定数据库项中的最小值 DPRODUCT 乘以特定字段(此字段中的记录为数据库中满足指定条件的记录)中的值DSTDEV 根据数据库中选定项的示例估算标准偏差 DSTDEVP 根据数据库中选定项的样本总体计算标准偏差 DSUM 对数据库中满足条件的记录的字段列中的数字求和 DVAR 根据数据库中选定项的示例估算方差 DVARP 根据数据库中选定项的样本总体计算方差 GETPIVOTDATA 返回存储在数据透视表中的数据 类别二:日期和时间函数 DATEDIF 计算两个日期之间的年、月、日数

DATEVALUE 将文本格式的日期转换为系列数 DAY 将系列数转换为月份中的日 DAYS360 按每年360 天计算两个日期之间的天数 EDATE 返回在开始日期之前或之后指定月数的某个日期的系列数EOMONTH 返回指定月份数之前或之后某月的最后一天的系列数HOUR 将系列数转换为小时 MINUTE 将系列数转换为分钟 MONTH 将系列数转换为月 NETWORKDAYS 返回两个日期之间的完整工作日数 NOW 返回当前日期和时间的系列数 SECOND 将系列数转换为秒 TIME 返回特定时间的系列数 TIMEVALUE 将文本格式的时间转换为系列数 WEEKDAY 将系列数转换为星期 WORKDAY 返回指定工作日数之前或之后某日期的系列数 YEAR 将系列数转换为年 YEARFRAC 返回代表start_date(开始日期)和end_date(结束日期)之间天数的以年为单位的分数DDE 和外部函数CALL 调用动态链接库(DLL) 或代码源中的过程REGISTER.ID 返回已注册的指定DLL 或代码源的注册IDSQL.REQUEST 连接外部

电子表格常用函数公式

电子表格常用函数公式 1、自动排序函数： =RANK(第1数坐标，$第1数纵坐标$横坐标：$最后数纵坐标$横坐标，升降序号1降0升) 例如：=RANK(X3,$X$3:$X$155,0) 说明：从X3 到X 155自动排序 2、多位数中间取部分连续数值： =MID（该多位数所在位置坐标，所取多位数的第一个数字的排列位数，所取数值的总个数） 例如：612730************在B4坐标位置，取中间出生年月日，共8位数 =MID(B4，7，8) =19820711 说明：B4指该数据的位置坐标，7指从第7位开始取值，8指一共取8个数字 3、若在所取的数值中间添加其他字样， 例如：612730************在B4坐标位置，取中间出生年、月、日，要求****年**月**日格式 =MID(B4，7，4)&〝年〞&MID(B4，11，2) &〝月〞& MID(B4，13，2) &〝月〞&

=1982年07月11日 说明：B4指该数据的位置坐标，7、11指开始取值的第一位数排序号，4、2指所取数值个数，引号必须是英文引号。 4、批量打印奖状。 第一步建立奖状模板：首先利用Word制作一个奖状模板并保存为“奖状.doc”，将其中班级、姓名、获奖类别先空出，确保打印输出后的格式与奖状纸相符（如图1所示）。 第二步用Excel建立获奖数据库：在Excel表格中输入获奖人以及获几等奖等相关信息并保存为“奖状数据.xls”，格式如图2所示。 第三步关联数据库与奖状：打开“奖状.doc”，依次选择视图→工具栏→邮件合并，在新出现的工具栏中选择“打开数据源”，并选择“奖状数据.xls”，打开后选择相应的工作簿，默认为sheet1，并按确定。将鼠标定位到需要插入班级的地方，单击“插入域”，在弹出的对话框中选择“班级”，并按“插入”。同样的方法完成姓名、项目、等第的插入。 第四步预览并打印：选择“查看合并数据”，然后用前后箭头就可以浏览合并数据后的效果，选择“合并到新文档”可以生成一个包含所有奖状的Word文档，这时就可以批量打印了。

EXCEL函数公式大全

excel常用函数公式及技巧搜集（常用的）【身份证信息？提取】 从身份证号码中提取出生年月日 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =TEXT(MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")*1 =IF(A2<>"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,) 显示格式均为yyyy-m-d。（最简单的公式，把单元格设置为日期格式） =IF(LEN(A2)=15,"19"&MID(A2,7,2)&"-"&MID(A2,9,2)&"-"&MID(A2,11,2),MID(A2,7,4)& "-"&MID(A2,11,2)&"-"&MID(A2,13,2)) 显示格式为yyyy-mm-dd。（如果要求为“1995/03/29”格式的话，将”-”换成”/”即可） =IF(D4="","",IF(LEN(D4)=15,TEXT(("19"&MID(D4,7,6)),"0000年00月00日 "),IF(LEN(D4)=18,TEXT(MID(D4,7,8),"0000年00月00日")))) 显示格式为yyyy年mm月dd日。（如果将公式中“0000年00月00日”改成“0000-00-00”,则显示格式为yyyy-mm-dd） =IF(LEN(A1:A2)=18,MID(A1:A2,7,8),"19"&MID(A1:A2,7,6)) 显示格式为yyyymmdd。 =TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")+0 =IF(LEN(A2)=18,MID(A2,7,4)&-MID(A2,11,2),19&MID(A2,7,2)&-MID(A2,9,2)) =MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日" =IF(A1<>"",TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00")) 从身份证号码中提取出性别 =IF(MOD(MID(A1,15,3),2),"男","女") （最简单公式） =IF(MOD(RIGHT(LEFT(A1,17)),2),"男","女") =IF(A2<>””,IF(MOD(RIGHT(LEFT(A2,17)),2),”男”,”女”),) =IF(VALUE(LEN(ROUND(RIGHT(A1,1)/2,2)))=1,"男","女") 从身份证号码中进行年龄判断 =IF(A3<>””,DATEDIF(TEXT((LEN(A3)=15*19&MID(A3,7,6+(LEN(A3)=18*2),”#-00-00”),T ODAY(),”Y”),) =DATEDIF（A1，TODAY（），“Y”） （以上公式会判断是否已过生日而自动增减一岁） =YEAR(NOW())-MID(E2,IF(LEN(E2)=18,9,7),2)-1900 =YEAR(TODAY())-IF(LEN(A1)=15,"19"&MID(A1,7,2),MID(A1,7,4)) =YEAR(TODAY())-V ALUE(MID(B1,7,4))&"岁" =YEAR(TODAY())-IF(MID(B1,18,1)="",CONCATENATE("19",MID(B1,7,2)),MID(B1,7,4)) 按身份证号号码计算至今天年龄 =DATEDIF(TEXT((LEN(A1)=15)*19&MID(A1,7,6+(LEN(A1)=18)*2),"#-00-00"),TODAY(),"y") 以2006年10月31日为基准日,按按身份证计算年龄(周岁)的公式

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函数公式大全

常用的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、查找重复内容公式：=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常用函数公式大全及举例 一、相关概念 （一）函数语法 由函数名+括号+参数组成 例：求和函数：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常用函数大全（做表不求人！） 2013-12-03 00:00 我们在使用Excel制作表格整理数据的时候，常常要用到它的函数功能来自动统计处理表格中的数据。这里整理了Excel中使用频率最高的函数的功能、使用方法，以及这些函数在实际应用中的实例剖析，并配有详细的介绍。 1、ABS函数 函数名称：ABS 主要功能：求出相应数字的绝对值。 使用格式：ABS(number) 参数说明：number代表需要求绝对值的数值或引用的单元格。 应用举例：如果在B2单元格中输入公式：=ABS(A2)，则在A2单元格中无论输入正数（如100）还是负数（如-100），B2中均显示出正数（如100）。 特别提醒：如果number参数不是数值，而是一些字符（如A等），则B2中返回错误值“#VALUE！”。

2、AND函数 函数名称：AND 主要功能：返回逻辑值：如果所有参数值均为逻辑“真（TRUE）”，则返回逻辑“真（TRUE）”，反之返回逻辑“假（FALSE）”。 使用格式：AND(logical1,logical2, ...) 参数说明：Logical1,Logical2,Logical3……：表示待测试的条件值或表达式，最多这30个。 应用举例：在C5单元格输入公式：=AND(A5>=60,B5>=60)，确认。如果C5中返回TRUE，说明A5和B5中的数值均大于等于60，如果返回FALSE，说明A5和B5中的数值至少有一个小于60。 国美提醒：如果指定的逻辑条件参数中包含非逻辑值时，则函数返回错误值“#VALUE!”或“#NAME”。 3、AVERAGE函数 函数名称：AVERAGE 主要功能：求出所有参数的算术平均值。

(完整版)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*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单元格；

(完整版)工作中最常用的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中可以使用通配符*

excel常用函数公式(有实例有分析有重点).docx

excel公式笔记 一、vlookup 1.查找A列中第一个以”厦门”开头的记录对应B列的值。 =vlookup(H厦门性A:B20) 其中第一个参数为要寻找的文本，第二个参数为一个区域，第二个参数的第一列为要寻找的区域,第三个参数的2表示第二个参数的第二列显示出来,第四个参数的0表示精确查找。 二、countif 1.统计Al:A10区域中型号为" 2.5m*3m"的记录个数。 =countif(Al:A10,,,2.5m~*3m H) 在excel常用函数中,支持通配符的主要有vlookup、hlookup> match> sum讦、courttif、search> searchB,而find、findB> subsitute 不支持通配符。*表示任意字符,？表示单个字符解除字符的通配性。 2.统计Al:A10中不重复数的个数。 =SUMPRODUCT((1/COUNTIF(A1:A10,A1:A10)))

3?求小于60的数据有多少 二count(A2:AKVv6(T) sumproduct 1?求购物总花费,A列表示购买数量月列表示购买单价 =sumproduct(A2:A8,B2:B8) 意思为A2*B2+A3*B3oooo +A8*B8 2.求二班有多少学生学习了数学 =sumproduct((A2:A10=H~ B,,)*(B2:B10=H数学“)) 表示二班的数学有几个 3.求二班数学分数总和 =sumproduct((A2:A10=M Z：B M)*(B2:B10=H数学H)*(C2:C1O)) 4.统计“技术部”考试成绩为0的个数(缺考除外) =sumproduct((B2:B9=H技术部,,)*(E2:E9=0)*(E2:E9o,,H)) excel会将空值看成0,所以在统计成绩为0的考生时，需要把成绩为空的考牛去除。

excel函数公式大全

excel函数公式大全 1.SUMPRODUCT函数：该函数的功能是在给定的几组数组中将数组间对应的元素相乘并返回乘积之和。例如：如图1，如果想计算B3：C6和C3：E6这两组区域的值，可以用以下公式： “=Sumproduct(B3:C6,D3:E6)”。 图1 2.ABS函数：如果在A 1.B1单元格中分别输入120.90，那么如果要求A1与B1之间的差的绝对值，可以在C1单元格中输入以下公式：“=ABS(A1- B1)”。 3.IF函数：如图2，如果C3单元格的数据大于D3单元格，则在E3单元格显示“完成任务，超出：”，否则显示“未完成任务，差额：”，可以在E3单元格中输入以下公式：“=IF(C3>D3, “完成任务，超出：”,”未完成任务，差额：””。 图2 4.Ceiling函数：该数值向上舍入基础的倍数。如图3，在C3单元格中输入以下公式：“=CEILING(B3,C3)”；而 “=FLOOR(B3,C3)”则是向下舍入。 图3

5.GCD函数：该函数计算最大公约数。如图4，如果要计算 B3：D3这一区域中3个数字的最大公约数，可以在E3单元格中输入以下公式：“=GCD(B3,C3,D3)”。 图4 6.INT函数：该函数是向下舍入取整函数。如图5，如果要计算显示器和机箱的购买数量，可以在E3单元格中输入以下公式：“=INT(D3/C3)”。 图5 7.LCM函数：该函数是计算最小公倍数。如图6，如果要计算B3：D3这一区域中3个数字的最小公倍数，可以在E3单元格中输入以下公式：“=LCM(B3,C3,D3)”。 图6 8.LN函数：该函数是计算自然对数，公式为：“=LN(B3)”。 9.LOG函数：该函数是计算指定底数的对数，公式为： “=LOG10(B3)”。 10.MOD函数：该函数是计算两数相除的余数。如图7，判断C3能否被B3整除，可以在D4单元格中输入以下公式： “=IF(MOD(B3,C3)=0,“是“,“否“)”。 图711.PI函数：使用此函数可以返回数字 3.14159265358979，即数学常量PI，可精确到小数点后14位。如图8，计算球体的面积，可以在C4单元格中输入以下公

excel常用公式函数大全

excel常用公式函数大全 1．求和函数SUM 语法：SUM（number1，number2，．．．）。 参数：number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。 注意：参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。 应用实例一：跨表求和 使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：选中Excel XP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。“确定”后公式所在单元格将显示计算结果。 应用实例二：SUM函数中的加减混合运算 财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。按照规定，工作表中的这些项目没有输入负号。这时可以构造“=SUM（B2:B6，C2:C9，-D2，-E2）”这样的公式。其中B2:B6，C2:C9引用是收入，而D2、E2为支出。由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:B6，C2:C9，-D2，-D3，D4）”这样的形式。 应用实例三：及格人数统计 假如B1:B50区域存放学生性别，C1:C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。可以使用公式“=SUM（IF（B1:B50=″女″，IF（C1:C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。公式两边会自动添加上大括号，在编辑栏显示为“{=SUM（IF （B1:B50=″女″，IF（C1:C50>=60，1，0）））}”，这是使用数组公式必不可少的步骤。 2.平均值函数AVERAGE 语法：AVERAGE（number1，number2，．．．）。 参数：number1、number2．．．是需要计算平均值的1～30个参数。 注意：参数可以是数字、包含数字的名称、数组或引用。数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。 应用实例一：跨表计算平均值

Excel函数计算公式大全(完整)

EXCEL函数公式大全(完整) 函数说明 CALL调用动态链接库或代码源中的过程 EUROCONVERT用于将数字转换为欧元形式，将数字由欧元形式转换为欧元成员国货币形式，或利用欧元作为中间货币将数字由某一欧元成员国货币转化为另一欧元成员国 货币形式（三角转换关系） GETPIVOTDATA返回存储在数据透视表中的数据 REGISTER.ID返回已注册过的指定动态链接库(DLL) 或代码源的注册号 SQL.REQUEST连接到一个外部的数据源并从工作表中运行查询，然后将查询结果以数组的形式返回，无需进行宏编程 函数说明 CUBEKPIMEMBER返回重要性能指标(KPI) 名称、属性和度量，并显示单元格中的名 称和属性。KPI 是一项用于监视单位业绩的可量化的指标，如每月 总利润或每季度雇员调整。 CUBEMEMBER返回多维数据集层次结构中的成员或元组。用于验证多维数据集内 是否存在成员或元组。 CUBEMEMBERPROPERTY返回多维数据集内成员属性的值。用于验证多维数据集内是否存在 某个成员名并返回此成员的指定属性。 CUBERANKEDMEMBER返回集合中的第n 个或排在一定名次的成员。用于返回集合中的一 个或多个元素，如业绩排在前几名的销售人员或前10 名学生。 CUBESET通过向服务器上的多维数据集发送集合表达式来定义一组经过计算 的成员或元组（这会创建该集合），然后将该集合返回到Microsoft Office Excel。 CUBESETCOUNT返回集合中的项数。 CUBEVALUE返回多维数据集内的汇总值。 数据库函数 函数说明 DAVERAGE返回所选数据库条目的平均值 DCOUNT计算数据库中包含数字的单元格的数量 DCOUNTA计算数据库中非空单元格的数量 DGET从数据库提取符合指定条件的单个记录 DMAX 返回所选数据库条目的最大值 DMIN 返回所选数据库条目的最小值 DPRODUCT将数据库中符合条件的记录的特定字段中的值相乘 DSTDEV基于所选数据库条目的样本估算标准偏差 DSTDEVP基于所选数据库条目的样本总体计算标准偏差

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

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”键同时按住键盘第二排相对应的数字符号即可显示。如果同一列的其它单元格都需利用刚才的公式计算，只需要先用鼠标左键点击一下刚才已做好公式的单元格，将鼠标移至该单元格的右下角，带出现十字符号提示时，开始按住鼠标左键不动一直沿着该单元格依次往下拉到你需要的某行同一列的单元格下即可，即可完成公司自动复制，自动计算。 二、多组数据加减乘除运算： ①多组数据求加和公式：(常用) 举例说明：=SUM（A1:A10）,表示同一列纵向从A1到A10的所有数据相加； =SUM（A1:J1）,表示不同列横向从A1到J1的所有第一行数据相加； ②多组数据求乘积公式：（较常用） 举例说明：=PRODUCT（A1:J1）表示不同列从A1到J1的所有第一行数据相乘； =PRODUCT（A1:A10）表示同列从A1到A10的所有的该列数据相乘； ③多组数据求相减公式：(很少用) 举例说明：=A1-SUM（A2:A10）表示同一列纵向从A1到A10的所有该列数据相减； =A1-SUM（B1:J1）表示不同列横向从A1到J1的所有第一行数据相减； ④多组数据求除商公式：(极少用) 举例说明：=A1/PRODUCT（B1:J1）表示不同列从A1到J1的所有第一行数据相除； =A1/PRODUCT（A2:A10）表示同列从A1到A10的所有的该列数据相除； 三、其它应用函数代表： ①平均函数 =AVERAGE（:）；②最大值函数 =MAX （:）；③最小值函数 =MIN （:）； ④统计函数 =COUNTIF（:）：举例：Countif ( A1：B5，”>60”) 说明：统计分数大于60分的人数，注意，条件要加双引号，在英文状态下输入。

HR常用的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(),"[ 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}) 三、员工数据分析公式

工作中最常用地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、按日期和产品求和 公式：F2