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单元格中输入以下公
式：“=PI()*(B3^2)*4)”；计算球体的体积，可以在D4单元格中输入以下公式：“= (B3^3)*（4* PI())）/3”。
图812.POWER函数：此函数用来计算乘幂。如图9，首先在单元中输入底数和指数，然后在D3中输入以下公式：
“=POWER(B3,C3)”。
图913.PRODUCT函数：此函数可以对所有的以参数形式给出的数字相乘，并返回乘积。例如：某企业xx年度贷款金额为100000元，利率为
1.5%，贷款期限为12个月。如图10所示，直接在单元格E4中输入以下公式：“ =PRODUCT(B4,C4,D4)”。
图1014.RADIANS函数：此函数是用来将弧度转换为角度的。可以在C3单元格中输入以下公式：“=RADIANS (B3)”。
15.RAND函数：此函数可以返回大于等于0及小于1的均匀分布随机数，每次计算工作表时都将返回一个新的数值。如果要使用函数RAND生成一个随机数，并且使之不随单元格的计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按[F9]键，将公式永久性地改为随机数。例如：在全班50名同学中以随机方式抽出20名进行调查，如图11，在单元格中输入开始号码以及结束号码，然后在单元格B4中输入以下公式：
“=1+RAND()*49”。
图1116.ROUND函数：此函数为四舍五入函数。如图12，例如：将数字“12.3456”按照指定的位数进行四舍五入，可以在D3单元格中输入以下公式：“=ROUND(B3,C3)”。
17.ROUNDDOWN函数：此函数为向下舍入函数。例如：出租车的计费标准是：起步价为5元，前10公里每一公里跳表一次，以后每半公里就跳表一次，每跳一次表要加收2元。输入不同的公里数，如图13所示，然后计算其费用。可以在C3单元格中输入以下公式：“=IF(B3TODAY(),“今年没到期
“,IF(DATEDIF($D5,TODAY(),“y“)=1,DATEDIF($D5,DATE($C$2, 6,9),“y“)*100,50)”，以此可计算出员工的工龄补贴。
图2429.计算火车站寄存包裹费用：在火车站寄存包裹是按小时数收费的。有些按整小时计数，有些按半小时计数，没有超过半小时的以半小时计，半小时以上一小时以内的按一小时计。同时包裹的大小不同收费也不同，在本例中假设大的每小时6元，中型的每小时4元，小型的每小时2元，计算在火车站寄存包裹的费用。如图25所示：1）计算寄存天数：首先输入相关的信息，然后在单元格E4中输入以下公式：
“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4), MINUTE(D4),SECOND(D4)),DATE(YEAR(D4),MONTH(D4),DAY(D4))-DATE(YEAR(C4),MONTH(C4),DAY(C4))-
1,DATE(YEAR(D4),MONTH(D4),DAY(D4))-
DATE(YEAR(C4),MONTH(C4),DAY(C4)))”，此时可计算出所有型号
的包裹寄存的天数，在此公式中用到了IF函数，函数中的条件为“TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINU TE(D4),SECO ND(D4)”，它是用来判断取走时间是否超过了寄存时间，如果条件为真则表示还没有超过一天，那么寄存的天数就是“DATE(YEAR(D4),MONTH(D4),DAY(D4))-
DATE(YEAR(C4),MONTH(C4),DAY(C4))-1”，即走取的日期减去寄存的日期再减1，如果时间超过了，那么寄存的天数就是“DATE(YEAR(D4),MONTH(D4),DAY(D4))-
DATE(YEAR(C4),MONTH(C4),DAY(C4))”，即取走的日期与寄存时的日期之差；2)计算寄存小时数：在单元格F4中输入以下公式：“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4), MINUTE(D4),SECOND(D4)),HOUR(1-
TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE (D4),SECOND(D4))),HOUR(TIME(HOUR(D4),MINUTE(D4),SECOND(D4 ))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))))”，此公式中的IF 函数中的条件与计算天数时的条件是一样的，也是判断取走时间是否超过了寄存时间，如果没有超过小时数则为
“TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4),MINU TE(D4),SECOND(D4))”，其中
“TIME(HOUR(C4),MINUTE(C4),SECOND(C4)”表示寄存时间的序列数，其中“TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”表示取走时间的序列数。再通过加减计算得到小时数，如果超过了小时数则
为“HOU R(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-
TIME(HOUR(C4),MINUTE(C4),SECOND(C4)))”，即直接用取走时间减去存在时间，取小时数；3)计算寄存分钟数：在单元格G4中输入以下公式：
“=IF(TIME(HOUR(C4),MINUTE(C4),SECOND(C4))>TIME(HOUR(D4), MINUTE(D4),SECOND(D4)),MINUTE(1-
TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE (D4),SECOND(D4))),MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND( D4))-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))))”，此时即可计算出所有型号的包裹寄存的分钟数，其公式形式和计算小时数的公式相似，只是将HOUR换成了MINUTE，其判断条件和前面的一样，如果取走时间没有超过寄存时间，分钟数则为“MINUTE(1-TIME(HOUR(C4),MINUTE(C4),SECOND(C4))+TIME(HOUR(D4),MINUTE (D4),SECOND(D4)))”。如果超过了，分钟数则为
“MINUTE(TIME(HOUR(D4),MINUTE(D4),SECOND(D4))-
TIME(HOUR(C4),MINUTE(C4),SECOND(C4)))”，即直接用取走时间减去寄存时间，取分钟数；4)计算寄存的累计小时数：在单元格H4中输入以下公式：
“=E4*24+F4+IF(G4=0,0,IF(G4=18,C7=35,C7=50,C7=90,1,IF(F3> =80,2,IF(F3>=70,3,IF(F3>=60,4,5)))),“优秀“,“良好
“,“一般“,“及格“,“不及格“)”，在该公式中用到了多个IF函数，用以判断平均成绩属于哪个区间，再使用CHOOSE函数返
回不同情况下的结果，这里把成绩分为了5个档次，即平均分90以上的是“优秀”.80到90之间的是“良好”.70到80之间的为“一般”.60到70之间的为“及格”.60以下的为“不及格”。
图3035.COLUMN函数：该函数使用方法如图31所示。
图3136.COLUMNS函数：该函数使用方法如图32所示。
图3237.HLOOKUP函数：在实际工作中此函数的应用非常广泛，下面举例说明。在计算销售奖金时，不同的销售业绩对应不同的奖金比例，因此首先需要使用HLOOKUP函数查询奖金比例，然后再计算销售奖金。1）输入如图33所示的业绩奖金以及员工的销售业绩；2）查找适当的奖金比例，在单元格D7中输入以下公式：“=HLOOKUP(D3,$B$3G$4,2)”；3）分别在单元格D
8.D
9.D10中输入以下公式：
“=HLOOKUP(E3,$B$3G$4,2)”.“=HLOOKUP(F3,$B$3G$4,2)”.“= HLOOKUP(G3,$B$3G$4,2)”；3）计算奖金：在单元格E7中输入以下公式：“=C7*D7”。
图3338.HYPERLINK函数：该函数使用方法如图34所示。
图3439.INDEX函数：该函数返回指定单元格中的内容。假设在图35所示的课程表中：1）查找出星期三第4节课所上的课程：只需在单元格C13中输入以下公式：
“=INDEX(C3:H9,C12,C11)”；2）返回星期五的所有课程：选中单元格区域“J2:J9”，然后输入以下公式：
“{=INDEX(B2:H9,,6)}”，此时即可显示出星期五的所有课程；3）计算路程：已知各地之间相隔的距离如图36所示，那么如何计算A地和D地之间相隔的距离呢？只需在单元格C11中输入以下公式：
“=INDEX(B2:G7,MATCH(C9,B2:B7,0),MATCH(C10,B2:G2,0))”。
图35 图3640.INDIRECT函数：该函数使用方法如图37所示。
图3760.PROPER函数：此函数可以自动转换大小写。首先在工作表中输入一些字母或者英文句子，如图61所示，然后在单元格C3中输入以下公式：“=PROPER(B3)”。
图6161.REPLACE函数：此函数可以使用其他的文本字符串并根据所指定的字符数替换某个文本字符串中的部分。例如某市的电话号码要升位，在原来的电话号码的前面加一个“8”，下面使用REPLACE函数完成已知电话号码的升位。具体的操作步骤如下：1）输入已知的电话号码，如图62所示；2）计算升位后的电话号码，在单元格C3中输入以下公式：
“=REPLACE(B3,1,4,“05328“)”，在该公式中，使用REPLACE 函数用“0108”替换B3中字符串中第一位开始的前4位数字，结果相当于区号不变，在原电话号码的前面加一个“8”。其中“05328”加引号是以文本的形式输入的，否则忽略0。
图6262.REPT函数：此函数可以按照给写的次数重复显示文本，也可以通过REPT函数不断地重复显示某一个文本字符串来对单元格进行填充。该函数的用法见图63所示。
图6363.RIGHT函数：使用此函数可以根据所指定的字符数返回文本字符串中最后一个或者多个字符。例如：1）拆分姓名，在实际中人的姓名一般是由姓和名两部分组成的，下面介绍如何利用RIGHT函数将其拆分开，具体的操作步骤如下：在单元格中输入一些姓名，如图64所示，然后在单元格C3中输入以下公式：“=RIGHT(B3,2)”；2）判断性别：假设有一个关于生活消费方面的调查，调查者为了书写方便也为了便于进行统计分析，在对被调查者编号时指定其最后一位表示性别，用“1”代表男性，用“2”代表女性，首先在工作表中输入已知信息，如图65所示，然后在单元格D3中输入以下公式：
“=IF(RIGHT(C3,1)=“1“,“男“,“女“)”，在该公式中，使用RIGHT函数返回编号中的最后一个字符，再利用IF函数判断。如果返回的结果为“1”则为“男”，反之为“女”，由于函数返回的是字符，所以“1”要加引号，当有多种情况时还可以使用嵌套的IF函数。
图64 图6564.SEARCH函数：此函数可以查找文本字符串。该函数的用法见图66所示。
图6665.T函数：此函数可以返加引用的文本。该函数的用法见图67所示。
图6766.TEXT函数：此函数用来将数值转换为指定格式。该函数的用法见图68所示。
图6867.TRIM函数：此函数用来清除文本中的空格。该函数的用法见图69所示。
图6968.UPPER函数：此函数用来将文本转换为大写。该函数的用法见图70所示。
图7069.处理人员信息：文本函数在实际工作中也是一种常用的函数类型。一些大型的企业为了提高员工的素质，使员工能及时地接触到该行业的最新科技信息，有关负责人会时常请一些专家对自己的员工进行培训。下面介绍如何利用文本函数处理人员信息，具体的操作步骤如下：1）在工作表中输入需要的标题项目以及人员编号.姓名和性别等数据信息，以便于在后面使用，如图71所示；2）从姓名中提取姓：在单元格E3中输入以下公式：“=IF(LEN(C3)=4,LEFT(C3,2),LEFT(C3,1))”，由于中国人的姓名有两个字的，有3个字的，还有4个字符，4个字的名字一般是复姓，所以要使用IF函数判断姓名的长度是不是4，如果姓名的长度等于4，则使用LEFT函数返回左边的两个字符，否则返回左边的1个字符；3）从姓名中提取名：在单元格E3中输入以下公式：“=IF(LEN(C3)=2,RIGHT(C3,1),RIGHT(C3,2))”，在该公式中使用IF函数判断姓名的长度是不是等于2，若等于2则利用RIGHT函数返回最右侧的1个字符，若不等于2则返回最右侧的两个字符；4）添加称呼：在单元格G3中输入以下公式：
“=IF(D3=“男“,CONCATENATE(E3,“先生
“),CONCATENATE(E3,“女士“))”，在该公式中，首先使用IF 函数判断性别是“男”还是“女”，如果是“男”则返回先生，如果是“女”则返回女士，然后利用CONCATENATE函数将判断结果和姓连接起来组成该专家的称呼；5）安排入住的宾馆房间号：在单元格H3中输入以下公式：“=IF(B3<=3,“滨海假日
“&TEXT(B3,“300“),“清泉宾馆“&TEXT(B3,“200“))”，在安排专家的宾馆房间时，假设前三名专家在宾馆A中休息，其余的在宾馆B中休息，房间号为他们的编号，在该公式中先使用TEXT函数将B列中的数据转换为对应格式的文本，再使用符号“&”将宾馆和房间号连接起来，最后使用IF函数根据专家的编号判断其入住哪个宾馆；6）输入各个专家的培训人数，然后选中单元格K2，选择[插入]—[符号]菜单位项弹出[符号]对话框，切换到[符号]选项卡中，在[字体]下拉列表中选择[（普通文本）]选项，在[子集]下拉列表中选择[零杂丁贝符（示意符号）]选项，设置完毕单击[插入]按钮即可在单元格输入选定的符号；7）绘制人数比较图：在单元格G3中输入以下公式：
“=REPT($K$2,INT(I3/12))”，在该公式中，使用REPT函数将单元格K2中的方块元素复制“INT(I3/12)”次，为了缩小空间也为了减小培训人数比例，将I列中的培训人数除以12再取整数即可得到需要复制的次数。
图7170.拆分工资金额：在前面已经介绍过利用INT函数和MOD函数进行工资数额的拆分，下面介绍如何使用文本函数将工资数额按其位数分隔开。例如已知某公司部分员工的工资，现要将工资按位数分开，具体的操作步骤如下：1）在工作表中输入姓名和工资数额以及其他的标题项目，如图72所示；2）计算千位上的数字：在单元格D4中输入以下公式：
“=IF(LEN(C4)=4,LEFT(C4,1),0)”,在该公式中使用LEN函数得到C4中字符串的长度，再使用IF函数判断该字符串的长度是否等于4，如果是的话则利用LEFT函数返回第一个字符，否则返回0；3）计算百位上的数字:在单元格E4中输入以下公式：
“=IF(D4=0,IF(LEN(C4)=3,LEFT(C4,1),0),LEFT(C4-
D4*1000,1))”,在该公式中，首先使用IF函数判断单元格D4中的值是否等于0，如果等于0则表明单元格C4中的数字共3位，将使用LEFT函数返回第一个字符；如果不等于0则返回“C4-
D4*1000”所得结果的第一个字符；4）计算位上的数字：在单元格F4中输入以下公式：“=LEFT(C4-D4*1000-E4*100,1)”计算结果的第一个字符。由于工资最少是“988”，即3位数字，所以不必再判断是否有两位数的情况；5）计算个位上的数字：在单元格G4中输入以下公式：“=LEFT(C4-D4*1000-E4*100-F4*10,1)”计算结果的第一个字符。
图7271.CELL函数：使用此函数可以返回某一个引用区域的
左上角单元格的格式.位置或者内容等信息。该函数的用法见图73所示。
图7372.COUNTBLANK函数：此函数可以指定空白单元格的个数。该函数的用法见图74所示。
图7473.ISBLANK函数：此函数可以判断单元格是否为空。例如判断员工是否到岗：1）输入姓名和上班时间，如图75所示；2）判断其是否到岗，在单元格E3中输入以下公式：
“=IF(ISBLANK(D3),“请假“,“到岗“)”。
图7574.ISERR函数：此函数可以判断数值是否为任意错误值。例如：计算应收账款：1）输入已知的数据信息，如货物名称.数量.单价和金额等，如图76所示；2）在单元格E3中输入以下
公式：“=IF(ISERR(C3*D3),“确定价格后再做处理
“,C3*D3)”。
图7675.DAVERAGE函数：此函数可以返回列表或者数据库中
满足指定条件的列中数值的平均值。例如：1）在单元格中输入需要处理的问题，如计算“语文大于59分的平均成绩”和“英语的平均成绩”，如图77所示；2）在单元格C12中输入以下公式：“=DAVERAGE(B2:E8,C10,C10:C11)”；3）在单元格C13中输入以下公式：“=DAVERAGE(B2:E8,4,E2:E8)”。
图7776.DCOUNT函数：使用此函数可以返回数据库或者列表
中满足指定条件并且包含数字的单元格个数。具体的操作步骤如
下：1）如图78所示，首先在单元格中输入需要处理的问题，然后在单元格C12中输入以下公式：
“=DCOUNT(B2:E8,B10,B10:B11)”，即可得到数学成绩及格的单元格个数；2）在单元格C13中输入以下公式：
“=DCOUNT(B2:E8,2,B10:B11)”，即可得到语文成绩大于70并且数学成绩及格的单元格个数。
图7877.DGET函数：使用此函数可以从列表或者数据库的列中提取符合指定条件的单个值。如图79所示，在单元格C12中输入以下公式：“=DGET(B2:E8,1,D1011)”，即可查找出英语成绩大于89分的同学的姓名；在单元格C13中输入以下公式：
“=DGET(B2:E8,1,B10:C11)”，即可查找出语文和数学成绩全部大于80分的同学的姓名。
图7978.DMAX函数：此函数用以返回指定条件的最大数值。首先在单元格中输入需要处理的问题，如图80所示，然后分别在单元格C12和C13中输入以下公式：
“=DMAX(B2:E8,B10,B1011)”.“=DMAX(B2:E8,D10,B1011)”。DMIN函数的使用方法与DMAX函数相似，不过此函数用以返回指定条件的最小数值。
图8079.DSUM函数：此函数用以返回指定条件的数字之和。首先在单元格中输入需要处理的问题，如图81所示，然后在单元格C12和C13中输入以下公式：
“=DSUM(B1:F6,1,C8:C9)”.“=DSUM(B1:F6,5,C89)”。
图8180.处理采购数据：在EXCEL中提供有很多种数据库函数，可以满足采购管理中管理人员对大量数据处理的要求。下面以图82所示，介绍如何使用数据库函数处理采购数据：1）使用DAVERAGE函数计算采购数量的平均值，首先建立一个数据模型；2）在单元格E15中输入以下公式：
“=DAVERAGE(B2:F12,5,G15:G16)”，即可得到台灯的平均采购数量；3）将单元格G16中的“台灯”改为“瓷瓶”就可以计算出瓷瓶的平均采购数量。此外还可以利用DAVERAGE函数在相交或者相并两种条件下计算数据的平均值。在Excel中输入同行的条件为相交的条件，即必须全部满足的条件，然后在单元格E19中输入以下公式：“=DAVERAGE(B2:F12,5,C2122)”，即可计算出采购数量少于20的平均数；4）要计算购买台灯的数量大于10的次数，此时可以使用DCOUNT函数来求解，首先输入需要求解的条件，然后在单元格F26中输入以下公式：
“=DCOUNT(B2:F12,5,C2829)”；5）要计算新新家具公司沙发的价格，此时可以使用DGET函数来求解，首先输入需要求解的条件，然后在单元格E33中输入以下公式：
“=DGET(B2:E12,4,C3536)”；6）要计算家具中沙发的最高价格，此时可以使用DMAX函数来求解，首先输入需要求解的条件，然后在单元格E39中输入以下公式：
“=DMAX(B2:E12,4,C41:C42)”。
图8281.区分函数COUNT和COUNTA：例如：1）制作1月出勤加班统计表，表中包括员工1月出勤加班统计表以及需要统计的内容，如图83所示；2）使用COUNT函数统计各列单元格的个数，在单元格B13中输入以下公式：“=COUNT(B3:B11)”，此时可以看到包含文字的单元格和空白单元格被忽略了，只统计包含数字的单元格；3）使用COUNTA函数统计各列单元格的个数，在单元格B14中输入以下公式：“=COUNTA(B3:B11)”，此时可以看到包含文字的单元格也统计在内了。
图https://www.360docs.net/doc/3514208881.html,RGE函数：该函数用来返回数据集中第K个最大值。例如：根据某企业在一次订货后检验所订产品的合格情况，计算需求量的大小.次品的多少以及不合格率最高.最低的产品等。具体的操作步骤如下：1）制作检验订购的产品合格情况表，如图84所示；2）计算各个产品中订购总量的最大需求与最小需求，分别在单元格G2和G3中输入以下公式：
“=LARGE(B3:B12,1)”.“=SMALL(B3:B12,1)”；3）计算次品的最大值和最小值，分别在单元格G6和G6中输入以下公式：
“=LARGE(C3:C12,1)”.“=LARGE(C3:C12,10)”；4）查找出不合格率最高的产品，在单元格G10中输入以下公式：
“=INDEX(A3:A12,MATCH(LARGE(D312,1),D312,0),1)”,在该