企业管理套表E表格函数应用大全.docx

1、企业管理套表E表格函数应用大全（企业管理套表）E表格函数应用大全EXCEL2003公式函数应用大全1、SUMPRODUCT函数：该函数的功能是于给定的几组数组中将数组间对应的元素相乘且返回乘积之和。例如：如图1，如果想计算B3：C6和C3：E6这俩组区域的值，能够用以下公式：“=Sumproduct(B3:C6,D3:E6)”。图12、ABS函数：如果于A1、B1单元格中分别输入120、90，那么如果要求A1和B1之间的差的绝对值，能够于C1单元格中输入以下公式：“=ABS(A1-B1)”。3、IF函数：如图2，如果C3单元格的数据大于D3单元格，则于E3单元格显示“完成任务，超出：”，否则显

2、示“未完成任务，差额：”，能够于E3单元格中输入以下公式：“=IF(C3D3,“完成任务，超出：”,”未完成任务，差额：”。图24、Ceiling函数：该数值向上舍入基础的倍数。如图3，于C3单元格中输入以下公式：“=CEILING(B3,C3)”；而“=FLOOR(B3,C3)”则是向下舍入。图35、GCD函数：该函数计算最大公约数。如图4，如果要计算B3：D3这壹区域中3个数字的最大公约数，能够于E3单元格中输入以下公式：“=GCD(B3,C3,D3)”。图46、INT函数：该函数是向下舍入取整函数。如图5，如果要计算显示器和机箱的购买数量，能够于E3单元格中输入以下公式：“=INT(D3

3、/C3)”。图57、LCM函数：该函数是计算最小公倍数。如图6，如果要计算B3：D3这壹区域中3个数字的最小公倍数，能够于E3单元格中输入以下公式：“=LCM(B3,C3,D3)”。图68、LN函数：该函数是计算自然对数，公式为：“=LN(B3)”。9、LOG函数：该函数是计算指定底数的对数，公式为：“=LOG10(B3)”。10、MOD函数：该函数是计算俩数相除的余数。如图7，判断C3能否被B3整除，能够于D4单元格中输入以下公式：“=IF(MOD(B3,C3)=0,是,否)”。图711、PI函数：使用此函数能够返回数字3.14159265358979，即数学常量PI，可精确到小数点后14位

4、。如图8，计算球体的面积，能够于C4单元格中输入以下公式：“=PI()*(B32)*4)”；计算球体的体积，能够于D4单元格中输入以下公式：“=(B33)*（4*PI()）/3”。图812、POWER函数：此函数用来计算乘幂。如图9，首先于单元中输入底数和指数，然后于D3中输入以下公式：“=POWER(B3,C3)”。图913、PRODUCT函数：此函数能够对所有的以参数形式给出的数字相乘，且返回乘积。例如：某企业2005年度贷款金额为100000元，利率为1.5%，贷款期限为12个月。如图10所示，直接于单元格E4中输入以下公式：“=PRODUCT(B4,C4,D4)”。图1014、RADI

5、ANS函数：此函数是用来将弧度转换为角度的。能够于C3单元格中输入以下公式：“=RADIANS(B3)”。15、RAND函数：此函数能够返回大于等于0及小于1的均匀分布随机数，每次计算工作表时均将返回壹个新的数值。如果要使用函数RAND生成壹个随机数，且且使之不随单元格的计算而改变，能够于编辑栏中输入“=RAND()”，保持编辑状态，然后按F9键，将公式永久性地改为随机数。例如：于全班50名同学中以随机方式抽出20名进行调查，如图11，于单元格中输入开始号码以及结束号码，然后于单元格B4中输入以下公式：“=1+RAND()*49”。图1116、ROUND函数：此函数为四舍五入函数。如图12，例

6、如：将数字“12.3456”按照指定的位数进行四舍五入，能够于D3单元格中输入以下公式：“=ROUND(B3,C3)”。17、ROUNDDOWN函数：此函数为向下舍入函数。例如：出租车的计费标准是：起步价为5元，前10km每壹km跳表壹次，以后每半km就跳表壹次，每跳壹次表要加收2元。输入不同的km数，如图13所示，然后计算其费用。能够于C3单元格中输入以下公式：“=IF(B3=10,5+ROUNDDOWN(B3,0)*2,20+ROUNDDOWN(B3-10)*2,0)*2)”。图1318、ROUNDUP函数：此函数为向上舍入函数。例如：当下网吧的管理壹般是采用向上舍入法，不满壹个单元按照壹

7、个单位计算。现假设每30分钟计价0.5元，请计算如图14中所示的上网所花费的费用。1）计算上网天数：首先于单元格C3中输入以下公式：“=B3-A3”；2）计算上网分钟数：上网分钟数实际上就等于上网天数乘以60再乘以24，所以应于单元格D3中输入以下公式：“=C3*60*24”；3）计算计费时间：本例中规定每30分钟计费壹次，不满30分钟以30分钟计价，所以应于单元格E3中输入以下公式：“=ROUNDUP(D3/30,0)”；4）计算上网费用：于单元格G3中输入以下公式：“=E3*F3”。图1419、SUBTOTAL函数：使用该函数能够返回列表或者数据库中的分类汇总。通常利用数据分类汇总菜单项能

8、够很容易地创建带有分类汇总的列表。Function_num函数返回值Function_num函数返回值Function_num函数返回值1Average5Min9Sum2Count6Product10Var3Counta7Stdev11warp4max8Stdevp例如某班部分同学的考试成绩如图15，1）显示最低的语文成绩：首先于单元格B9中输入“显示最低的语文成绩”的字样，然后于单元格E9中输入以下公式：“=SUBTOTAL(5,C3:C7)”；2）显示最高的数学成绩：首先于单元格B10中输入“显示最高的数学成绩”的字样，然后于单元格E10中输入以下公式：“=SUBTOTAL(4,D3:D7

9、)”。图1520、计算库存量和奖金：假设某公司于月底要根据员工的业绩发放工资且进行产品的库存统计，本例中规定员工的基本工资为600元，奖金按照销售业绩的8%提成，总工资等于基本工资和奖金之和。如图16，1）于工作表中输入相应的数据信息；2）计算“现存库量”：于单元格C15中输入以下公式：“=C14-SUM(C3:C9)”；3）计算“销售业绩”：于单元格G3中输入以下公式：“=SUMPRODUCT(C3:F3,$C$13:$F$13)”,函数SUMPRODUCT是计算数组C3：F3和数组$C$13:$F$13乘积的和，用数学公式表示出来就是：“=10*3050.5+10*1560.99+5*44

10、89.9+20*2119”；4）计算奖金：奖金是按照销售业绩的8%提成得到的，这样计算出来的结果可能会是小数，不好找零钱，所以这里采用向上舍入的方式得到整数，于单元格H3中输入以下公式：“=ROUNDUP(G3*8%,0)”；5）计算总工资：由于总工资=基本工资+奖金，所以于单元格J3中输入以下公式：“=SUM(H3:I3)”。图1621、计算工资和票面金额：假设某公司的销售人员的销售情况如图17所示，按照销售业绩的5%计算销售提成，下面需要结合上例中的函数来计算销售人员的销售业绩以及奖金工资，然后再计算出发放工资时需要准备的票面数量。1）计算销售业绩：于单元格H13中输入以下公式：“=SUM

11、PRODUCT(C3:G3,$C$11:$G$11)”；2）计算提成：于本例中假设提成后出现小于1元的金额则舍入为1，所以需要使用ROUNDUP函数，于单元格I3中输入以下公式：“=ROUNDUP(H3*5%,0)”；3）计算工资：于单元格K3中输入以下公式：“=I3+J3”；4）计算100元的面值：于单元格L3中输入以下公式：“=INT(K3/$L$2)”；5）计算50元的面值：于单元格M3中输入以下公式：“=INT(MOD(K3,$L$2)/$M$2)”，此公式是使用MOD函数计算发放“MOD(K3,$L$2)”张100元后剩下的工资，然后利用取整函数INT得到50元票面的数量；6）计算1

12、0元的面值：于单元格N3中输入以下公式：“=INT(MOD(K3,$M$2)/$N$2)”；7）计算5元的面值：于单元格O3中输入以下公式：“=INT(MOD(K3,$N$2)/$O$2)”；8）计算1元的面值：于单元格P3中输入以下公式：“=INT(MOD(K3,$O$2)/$P$2)”。图1722、DATE函数：于实际工作中经常会用到此函数来显示日期。例如：如图18，于单元格中输入相应的年、月和图书馆日等信息，然后于单元格E3中输入以下公式：“=DATE(B3,C3,D3)”。图1823、DATEIF函数：假设有俩个已知日期开始日期和截止日期，那么能够利用DATEIF函数来计算它们之间相差

13、的年数、月数或者天数等。如图19，于单元格D3中输入以下公式：“=DATEDIF(B3,C3,y)”。图1924、DAYS360函数：该函数计算俩个日期之间的天数，于财务中经常会用到，如果财务系统是基于壹年12个月且且每月30天，能够使用该函数帮助计算借款天数或者支付款项等。例如：某企业不同时间的贷款如图20所示，然后利用DAYS360函数来计算其借款的时间，且且计算出仍款利息。1）计算“借款天数”：于单元格D3中输入以下公式：“=DAYS360(B3,C3)”；2）计算“仍款利息”：于单元格G3中输入以下公式：“=D3*E3*F”。图2025、WEEKDAY函数：使用此函数能够返回某个日期为

14、星期几。语法：WEEKDAY（serial_number,return_type）：其中参数serial_number代表要查找的那壹天的日期，参数return_type为确定返回值类型的数字，详细内容如下表：例如：计算当前日期是星期几：如图21所示，于单元格B3中输入计算当前日期的公式：“=WEEKDAY(B3,2)”。图2126、WEEKNUM函数：使用此函数能够计算壹年中的第几周。例如：已知2006年6月9日是星期五，下面利用WEEKNUM函数计算于参数不同的情况下返回的周数。如图22所示，于单元格B3中输入计算当前日期的公式：“=WEEKNUM(B3,C3)”。图2227、WORKDA

15、Y函数：使用此函数能够返回某个日期（起始日期）之前或之后相隔指定工作日的某壹日期的日期值，工作日不包括周末和专门指定的日期。假设某出版社要求某个编辑从2006年3月1日起开始写稿，利用80天将其完成（其中不包括三天节假日），此时能够利用WORKDAY函数计算出完成日期。如图23所示，于单元格中输入上述信息，然后于单元格C7中输入以下公式：“=WORKDAY(C2,C3,C4:C6)”。图2328、计算年假天数和工龄补贴：假设某公司规定，员工任职满1年的开始有年假，第1至5年每年7天，第6年开始每年10天。截止到2005年6月9日，以工龄计算每年补贴100元，任职不足壹年的按每人50元计算。如图

16、24所示：1）首先于工作表中输入已知数据信息，然后根据公司规定的内容于单元格F5中输入以下公式：“=IF(DATEDIF($D5,TODAY(),y)TODAY(),今年没到期,IF(DATEDIF($D5,TODAY(),y)=1,DATEDIF($D5,DATE($C$2,6,9),y)*100,50)”，以此可计算出员工的工龄补贴。图2429、计算火车站寄存包裹费用：于火车站寄存包裹是按小时数收费的。有些按整小时计数，有些按半小时计数，没有超过半小时的以半小时计，半小时之上壹小时以内的按壹小时计。同时包裹的大小不同收费也不同，于本例中假设大的每小时6元，中型的每小时4元，小型的每小时2元

17、，计算于火车站寄存包裹的费用。如图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函数，函数中的

18、条件为“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”，即走取的日期减去寄存的日期再减1，如果时间超过了，那么寄存的天数就是“DATE(YEAR(D4),MONTH(D4),DAY(D4)-DATE(YEAR(C4),MONTH(C4),DAY(C4)”，即取走的日期和寄存时

19、的日期之差；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函数中的条件和计算天数时的条件是壹样的，也是判断取走时间是否

20、超过了寄存时间，如果没有超过小时数则为“TIME(HOUR(C4),MINUTE(C4),SECOND(C4)TIME(HOUR(D4),MINUTE(D4),SECOND(D4)”，其中“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)”，即直接用取走

21、时间减去存于时间，取小时数；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)”，此时即可计算出所有型号的包裹寄存的分钟数，其公式形

22、式和计算小时数的公式相似，只是将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

23、=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函数：于实际

24、工作中此函数的应用非常广泛，下面举例说明。于计算销售奖金时，不同的销售业绩对应不同的奖金比例，因此首先需要使用HLOOKUP函数查询奖金比例，然后再计算销售奖金。1）输入如图33所示的业绩奖金以及员工的销售业绩；2）查找适当的奖金比例，于单元格D7中输入以下公式：“=HLOOKUP(D3,$B$3:$G$4,2)”；3）分别于单元格D8、D9、D10中输入以下公式：“=HLOOKUP(E3,$B$3:$G$4,2)”、“=HLOOKUP(F3,$B$3:$G$4,2)”、“=HLOOKUP(G3,$B$3:$G$4,2)”；3）计算奖金：于单元格E7中输入以下公式：“=C7*D7”。图3338

25、、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(

26、C10,B2:G2,0)”。图35图3640、INDIRECT函数：该函数使用方法如图37所示。图3741、LOOKUP函数：该函数用于于行（或列）中查找且返回数值。例如某公司员工的工资表如图38所示，查找姓名：首先于单元格C11中输入编辑“0004”，然后于单元格C12中输入以下公式：“=LOOKUP(C11,B3:B9,C3:C9)”，也可输入公式：“=LOOKUP(C11,B3:C9)”，此时即可查找到编辑为“0004”的员工的姓名。查找基本工资、实发工资的公式类似姓名的公式。图3842、MATCH函数：于数组中查找数值的相应位置。该函数使用方法如图39所示。图3943、OFFSET函数

27、：OFFSET函数的功能是返回的引用能够为壹个单元格或者单元格区域，且且能够指定返回的行数或者列数。其语法为：OFFSET(reference,rows,cols,height,width)。其中reference表示作为偏移量参照系的引用区域，此参数必须为单元格或相邻单元格区域的引用，否则函数OFFSET返回错误值“#VALUE！”；rows表示相对于偏移量参照系的左上角单元格上（下）偏移的行数；cols表示相对于偏移量参照系的左上角单元格左（右）偏移的列数；height表示高度，即所要返回的引用区域的行数，此参数必须为正数；width表示宽度，即所要返回的引用区域的列数，此参数必须为正数。该函数的应用方法如图40所示。图4044、ROW函数：该函数的应用方法如图41所示。图41