Excel-数学函数

常用数学函数1.ABS()返回绝对值2.EXP()返回自然对数的底e的n次幂问：e是怎样来的？e=lim(1+1/n)^n,当n趋向无穷大时问：e有什么用？计算连续复利本息A=P*e^(r*t),r是年利率，t是时间，P是本金例：本金100年利率10%时间（年）5连续复利本息164.8721=D13*EXP(D14*D15)3.FACT()返回某数的阶乘例：n5n的阶乘120=FACT(D21)4.INT()向下取整例：8.98=INT(C27)-8.9-9=INT(C28)5.MOD()求余，余值符号与除数相同被除数7被除数7除数3除数-3余数1=MOD(D32,D33)余数-2被除数7被除数7除数3.3除数-3.3余数0.4余数-2.9求余函数在应用中多半是用于判断奇偶性或返回重复序列。6.PI()返回圆周率，精确到小数后14位7.RAND()/RANDBETWEEN()返回随机数RAND()返回一个0-1之间的15位小数RANDBETWEEN()，返回指定的两数之间的随机整数首数尾数-999=RANDBETWEEN(C49,D49)-8=INT(RAND()*(D49-(C49)+1))+(C49)=INT(RAND()*(尾数-首数+1))+首数-9=INT(MOD(RAND()*9999,D49-C49+1))+C49=INT(MOD(RAND()*9999,尾数-首数+1))+首数特例1：返回不重复随机数0.79017320.87319310.43024330.41993940.03221160.1669525=RAND()=RANK(C60:C65,$C$60:$C$65)特例2：按指定的几率返回随机数返回值几率30.350.370.4共返回10个值值分布样本随机种子30.4146635=INDEX($C$75:$C$84,RANK(D75,$D$75:$D$84))30.082492730.7905350.093383750.364628750.85129370.282091770.623314570.961585370.75420858.ROUND()/ROUNDUP()/ROUNDDOWN()按位数舍入ROUND()按位数四舍五入ROUNDUP()按位数向上舍入ROUNDDOWN()按位数向下舍入值舍入位数54.38154.4=ROUND(C92,D92)54.4=ROUNDUP(C92,D92)54.3=ROUNDDOWN(C92,D92)值舍入位数54.38-150=ROUND(C99,D99)60=ROUNDUP(C99,D99)50=ROUNDDOWN(C99,D99)9.SQRT()返回整数的平方根10.SUBTOTAL请见工作表《SUBTOTAL》11.SUM/SUMIF/SUMIFS求和SUM求和SUMIF/SUMIFS按条件求和515=SUM(C116:C118)1521=SUM(5,15,TRUE)TRUE参数为引用时，SUM只对数值进行运算参数为常量时，SUM能对文本数字和逻辑值进行自动转换姓名语文吴楚文66张泽敏69张祖坤62李小敏61李小萍52李美玲74李智健69李嘉敏64杨廷芳74苏佩雯56姓“李”的同学的语文成绩总和320=SUM(IF(ISNUMBER(FIND(李,C123:C132)),D123:D132))=SUM((ISNUMBER(FIND(李,C123:C132))*(D123:D132)))320=SUMIF(C123:C132,李*,D123:D132)=SUMIF(条件区域，条件，求和区域）原本需要利用数组公式解决的按条件求和问题，现在可以考虑使用SUMIF函数解决。姓名语文数学英语吴楚文669079张泽敏697666张祖坤627941李小敏618477李小萍529269李美玲748770李智健698871李嘉敏648677杨廷芳747882苏佩雯568883姓“李”的，而且语文成绩合格的同学的数学成绩总和345=SUM(ISNUMBER(FIND(李,C142:C151))*(D142:D151=60)*(E142:E151))345=SUMIFS(E142:E151,C142:C151,李*,D142:D151,=60)=SUMIFS(求和区域，条件区域1，条件1，条件区域2，条件2，……）12.SUMPRODUCT求乘积和产品单价销量电视机500010冰箱20008空调250015洗衣机300012总销售额139500=SUMPRODUCT(D161:D164,E161:E164)139500=SUM((D161:D164)*(E161:E164))13.LN/LOG/LOG10返回自然对数，对数和常用对数14.GCD/LCNGCD返回最大公约数LCN返回最小公倍数15.MDETERM返回行列式的值16.MINVERSE/MMULT返回逆矩阵/返回两个矩阵的乘积*.IEEE754浮点运算机制1、请利用Excel计算(0.74-0.8)*100，并用INT（）函数对其计算结果取整2、请利用Excel计算(0.64-0.7)*100，并用INT（）函数对其计算结果取整-6=(0.74-0.8)*100-6=(0.64-0.7)*100-7=INT((0.74-0.8)*100)-6=INT((0.64-0.7)*100)=(0.74-0.8)*100=(0.64-0.7)*1003、请利用Excel比较(4.2-4.1)与0.1的大小4、请利用Excel比较(3.2-3.1)与0.1的大小0.1=4.2-4.10.1=3.2-3.10.1=0.10.1=0.1FALSE=C201=C203TRUE=G201=G203**.循环引用（迭代）-0.0600000000000001-0.05999999999999990.10000000000000100.1000000000000000Excel浮点运算机制的问题Excel的浮点运算受到是计算机自身硬件和IEEE754标准的限制，PC机的微处理器在计算浮点数时会将我们所见到的十进制数转换成二进制进行运算。在转换的过程中，有一些十进制数，比如0.1，在二进制下会被表示为0.0001100011000111000111……（是一个不断重复且无限循环的数值）。当前迭代次数迭代值迭代和100139800=SUM(D218:E218)文件——选项——公式利用循环引用解决产生不重复随机数的问题产生一组6个，值为1-6的不重复随机整数3100000=IF(SUM($D$237:$D$242)6,INT(RAND()*6+1),C237)=COUNTIF($C$237:$C$242,C237)上述方法的关键是“穷举”。***.关于随机值现在通过计算机所产生的随机数都只是“伪随机”数，“真随机”还无法产生。所谓随机问题并非只是计算机科学的一个特有难题，其事实上的本质是一个哲学问题（是一个关于决定论和自由意志的争议）。利用RAND/RANDBETWEEN所产生的随机数的实际应用层面其实是很有限的，因为这些产生出来的随机数一般无法满足具有统计学需要。RAND所产生的随机数的分布特征只有是离散的，若要产生如“正态、泊松、二项式”等分布的随机值，则必须要使用“随机数发生器”这个特殊的数原本需要利用数组公式解决的按条件求和问题，现在可以考虑使用SUMIF函数解决。=SUM(ISNUMBER(FIND(李,C142:C151))*(D142:D151=60)*(E142:E151))函数名称SUBTOTAL函数功用返回列表或数据库中的分类汇总。函数原型SUBTOTAL(function_num,ref1,[ref2],...])中文参数SUBTOTAL(汇总方式代码,汇总区域1,[汇总区域2],...])参数2.Ref1必需。要对其进行分类汇总计算的第一个命名区域或引用。注意2.SUBTOTAL函数适用于数据列或垂直区域。不适用于数据行或水平区域。汇总代码表Function_numFunction_num（包含隐藏值）（忽略隐藏值）1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP姓名1月份2月份3月份平均工资马骏1025103010651040=SUBTOTAL(1,OFFSET($B$34:$D$34,ROW($1:$10),))王柳妮1150105513851197车思敏1225145513551345邓伟良1380141013351375叶健聪845143512951192刘帆1295130013151303刘思政1195145510701240刘嘉靖127597011451130吕珅1380141011001297成佳1475106014751337例：直接一季度员工最高平均工资1375=MAX(SUBTOTAL(1,OFFSET($B$34,ROW($1:$10),,,3)))①OFFSET($B$34,ROW($1:$10),,,3)相当于：1.Function_num必需。1到11（包含隐藏值）或101到111（忽略隐藏值）之间的数字，用于指定使用何种函数在列表中进行分类汇总计算。3.Ref2,...可选。要对其进行分类汇总计算的第2个至第254个命名区域或引用。1.当function_num为从1到11的常数时，SUBTOTAL函数将包括通过“隐藏行”命令所隐藏的行中的值。当您要对列表中的隐藏和非隐藏数字进行分类汇总时，请使用这些常数。当function_num为从101到111的常数时，SUBTOTAL函数将忽略通过“隐藏行”命令所隐藏的行中的值。当您只想对列表中的非隐藏数字进行分类汇总时，请使用函数OFFSET($B$34,1,,,3)OFFSET($B$34,2,,,3)……OFFSET($B$1,10,,,3)即全部返回十组员工的1~3月份的工资，并在内存中进行分组存放：102510301065115010551385………………147510601475②SUBTOTAL(1,OFFSET($B$34,ROW($1:$10),,,3))10251030106510401150105513851197……………………1475106014751337③=MAX(SUBTOTAL(1,OFFSET($B$34,ROW($1:$10),,,3)))在上述返回的平均值数组中找出最大值问：AVERAGE(OFFSET($B$34,ROW($1:$10),,,3))的情况如何？AVERAGE没能识别OFFSET的分组返回，其运算过程如下：姓名Ａ产品销售量Ａ产品销售额张三220#########刘八260#########赵六250#########王五450#########王五430#########马二425#########王五280#########隐藏81、85行的李四，并对C80:C88的产品销售量进行求和：2745=SUM(C80:C88)2745=SUBTOTAL(9,C80:C88)2315=SUBTOTAL(109,C80:C88)1.SUM，对全部区域进行计算；2.SUBTOTAL/9，对全部区域进行计算和SUM一样；3.SUBTOTAL/109，仅对可见区域进行计算，忽略隐藏区域的值。姓名Ａ产品销售量Ａ产品销售额李四130#########刘八260#########李四300#########通过筛选，仅显示李四和刘八，并对C101:C109的产品销售量进行求和：AVERAGE(OFFSET($B$1,ROW($1:$10),,,3))=AVERAGE({1025;1150;1225;1380;845;1295;1195;1275;1380;1475})值得注意的是，若对OFFSET($B$34,ROW($1:$10),,,3)进行