特殊值计算 – Excel22

Excel 计算随机数：RAND函数图解

RAND函数和功能是计算大于等于0及小于1的均匀分布的随机实数，每次计算工作表时都将返回一个新的随机实数。RANDBETWEEN函数的功能是，计算位于指定的两个数之间的一个随机整数，每次计算工作表时都将返回一个新的随机整数。两函数的语法如下：

RAND( )
RANDBETWEEN(bottom,top)

其中，bottom参数为RANDBETWEEN函数将返回的最小整数，top参数为RANDBETWEEN函数将返回的最大整数。

因为这两个函数都是用于返回随机数，所以可以用来模仿一些掷骰子的游戏。本例中要随机返回1～50的整数，投掷次数为5次。打开“RAND函数.xlsx”工作簿，本例的原始数据如图14-50所示。具体操作步骤如下。

STEP01：选中C3单元格，在编辑栏中输入公式“=INT(RAND()*($B$1-$D$1)+$D$1)”，然后按“Enter”键返回，即可计算第1次的投掷结果，计算结果如图14-51所示。

图14-50　原始数据

图14-51　计算第1次投掷结果

STEP02：选中C3单元格，利用填充柄工具向下复制公式至C7单元格，通过填充功能来计算其他次数的投掷结果，如图14-52所示。

STEP03：选中D3单元格，在编辑栏中输入公式“=INT(RANDBETWEEN($D$1,$B$1))”，然后按“Enter”键返回，即可计算出第1次的投掷结果，计算结果如图14-53所示。

图14-52　计算投掷结果

图14-53　计算投掷结果

STEP04：选中D3单元格，利用填充柄工具向下复制公式至D7单元格，通过填充功能来计算其他次数的投掷结果，如图14-54所示。

STEP05：按“F9”键可以查看重新求解的随机结果，如图14-55所示。

对RAND函数来说：如果要生成a与b之间的随机实数，必须使用“RAND()*(b-a)+a”。如果要使用RAND函数生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按“F9”键，将公式永久性地改为随机数。

图14-54　计算其他次数的投掷结果

图14-55　重新查看投掷结果

Excel 计算余数：MOD函数图解

MOD函数功能是计算两数相除的余数。结果的正负号与除数相同。其语法如下：

MOD(number,divisor)

其中，number参数为被除数，divisor参数为除数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“MOD函数.xlsx”工作簿，本例中的原始数据如图14-45所示。要求使用MOD函数来判断这些数字的奇偶性。具体的操作步骤如下。

图14-45　原始数据

STEP01：选中B2单元格，在编辑栏中输入公式“=MOD(B1,2)”，然后按“Enter”键返回即可计算出一个数据除以2后的余数，结果如图14-46所示。

STEP02：选中B2单元格区域，利用填充柄工具向右复制公式至G2单元格，通过填充功能来计算其他数据除以2的余数，计算结果如图14-47所示。

STEP03：选中B3单元格，在编辑栏中输入公式“=IF(B2=1,”奇数”,”偶数”)”，然后按“Enter”键返回即可判断出第1个数据的奇偶性，结果如图14-48所示。

STEP04：选中B3单元格区域，利用填充柄工具向右复制公式至G3单元格，通过填充功能来判断其他数据的奇偶性，计算结果如图14-49所示。

图14-46　计算15除以2的余数

图14-47　计算其他数据除以2的余数

图14-48　计算15的奇偶性

图14-49　判断奇偶性

在MOD函数中，如果参数divisor为零，MOD函数将返回错误值“#DIV/0!”。MOD函数可以借用函数INT来表示：MOD(n,d)=n-d*INT(n/d)。

Excel 计算对数：LN函数图解

LN函数的功能是计算一个数的自然对数，自然对数以常数项e（2.71828182845904）为底。LOG函数的功能是，计算按所指定的底数返回一个数的对数。LOG10函数的功能是计算以10为底的对数。三个函数的语法如下：

LN(number)
LOG(number,base)
LOG10(number)

其中，number参数为用于计算对数的正实数，base参数为对数的底数。如果省略底数，则假定其值为10。下面通过实例详细讲解该函数的使用方法与技巧。

已知有4家上市企业，分别是AC钢铁、DF制药、ER玩具和QI服装，并已知其从2018年9月到2019年2月的股票指数数据，投资分析人员需要计算股票的月收益率，以连续复利计。打开“LN函数.xlsx”工作簿，本例中的原始数据如图14-41所示。具体的操作步骤如下。

STEP01：选中F3单元格，在编辑栏中输入公式“=LN(B3/B2)”，然后按“Enter”键返回即可计算出AC钢铁企业在2018年10月的股票收益率，如图14-42所示。

STEP02：选中F3单元格，利用填充柄工具向右复制公式至I3单元格，通过填充功能来计算其他企业的收益率，如图14-43所示。

STEP03：选中F3:I3单元格区域，将鼠标指针移至I3单元格右下角，利用填充柄工具向下复制公式，通过填充功能来计算各个企业在其他日期的收益率，如图14-44所示。

LN函数是EXP函数的反函数。在LOG函数中，如果省略参数base，就假定其值为10。

图14-41　原始数据

图14-42　计算AC钢铁10月份收益率

图14-43　计算其他企业收益率

图14-44　计算结果

Excel 计算最大公约数与最小公倍数：GCD函数

GCD函数的功能是返回两个或多个整数的最大公约数，最大公约数是能分别将参数number1和number2除尽的最大整数。LCM函数的功能是，返回整数的最小公倍数，最小公倍数是所有整数参数number1、number2等的最小正整数倍数。两函数的语法分别如下：

GCD(number1,number2,...)
LCM(number1,number2,...)

其中参数number1、number2……为1～255个参数。如果参数不是整数，则截尾取整。下面通过实例详细讲解该函数的使用方法与技巧。

打开“参数数值.xlsx”工作簿，切换至“Sheet1”工作表，该工作表中给出了两个参数数值，如图14-38所示。需要求解这两个参数的最大公约数和最小公倍数。具体的操作步骤如下。

图14-38　原始数据

STEP01：选中B2单元格，在编辑栏中输入公式“=GCD(B1:C1)”，然后按“Enter”键返回即可计算出两个参数的最大公约数，结果为“24”，如图14-39所示。

STEP02：选中B3单元格，在编辑栏中输入公式“=LCM(B1:C1)”，然后按“Enter”键返回即可计算出两个参数的最小公倍数，结果为“144”，如图14-40所示。

图14-39　计算最大公约数

图14-40　计算最小公倍数

LCM函数可以用于将分母不同的分数相加。对两个函数来说，如果参数为非数值型，则都将返回错误值“#VALUE!”；如果参数小于零，则都将返回错误值“#NUM!”。

Excel 计算数的阶乘：FACT函数

FACT函数的功能是计算某正数的阶乘，主要用来计算不同参数的阶乘数值。其语法如下：

FACT(number)

其中，number参数为要计算其阶乘的数值。一个数的阶乘等于1*2*3*…*。下面通过实例详细讲解该函数的使用方法与技巧。

打开“FACT函数.xlsx”工作簿，本例中要求计算的数值说明如图14-33所示。

图14-33　计算说明

STEP01：选中A2单元格，在编辑栏中输入公式“=FACT(5)”，然后按“Enter”键返回即可计算出5的阶乘，结果如图14-34所示。

STEP02：选中A3单元格，在编辑栏中输入公式“=FACT(8.6)”，然后按“Enter”键返回即可计算出8.6截尾取整后的阶乘，结果如图14-35所示。

STEP03：选中A4单元格，在编辑栏中输入公式“=FACT(-6)”，然后按“Enter”键返回，可以看到返回结果为“#NUM！”，如图14-36所示。

STEP04：选中A5单元格，在编辑栏中输入公式“=FACT(171)”，然后按“Enter”键返回，可以看到返回结果为“#NUM！”，如图14-37所示。

图14-34　计算5的阶乘

图14-35　计算8.6截尾取整后的阶乘

图14-36　参数为负数计算结果

图14-37　参数超过170后返回的结果

阶乘主要用于排列和组合的计算。在用FACT函数计算阶乘时，如果参数number不是整数，将截尾取整进行计算；如果参数为负数或超过170，将会返回错误值“#NUM!”，因为计算阶乘时，参数越大，结果越大。

Excel 计算给定数目对象的组合数：COMBIN函数

COMBIN函数的功能是计算从给定数目的对象集合中，提取若干对象的组合数。COMBIN函数的语法如下：

COMBIN(number,number_chosen)

其中，number参数表示项目的数量，number_chosen参数表示每一个组合中项目的数量。下面通过实例详细讲解该函数的使用方法与技巧。

打开“COMBIN函数.xlsx”工作簿，本例中的原始数据如图14-31所示。该工作表记录了工厂车间的5位员工名单，分别是张静、李平、苏刚、王辉和吕丽。现在要从这5人中抽出4人进行技能比赛，要求计算可以组成的组合数。具体的操作步骤如下。

选中C2单元格，在编辑栏中输入公式“=COMBIN(A2,B2)”，然后按“Enter”键返回，即可计算出组合数。可以看到返回结果为“5”，如图14-32所示。

图14-31　原始数据

图14-32　检验组合数

使用COMBIN函数可以确定一组对象所有可能的组合数。在COMBIN函数中的数字参数将截尾取整。如果参数为非数值型，则COMBIN函数将返回错误值“#VALUE!”；如果参数number<0、参数number_chosen<0或参数number<参数number_chosen，则COMBIN函数将返回错误值“#NUM!”。

Excel 计算绝对值：ABS函数

ABS函数的功能是计算数字的绝对值，绝对值是没有符号的。其语法如下：

ABS(number)

其中，number参数为需要计算其绝对值的实数。

函数的功能中提到一个概念——绝对值。绝对值在数轴上表示为一个数的点离开原点的距离。一个正实数的绝对值是它本身；一个负实数的绝对值是它的相反数；零的绝对值是零。下面通过实例详细讲解该函数的使用方法与技巧。

图14-26　原始数据

打开“ABS函数.xlsx”工作簿，本例中的原始数据如图14-26所示。该工作表记录了某工厂一批产品的标准重量与实际重量的数值，要求根据这些数据计算出误差百分率。具体的操作步骤如下。

STEP01：选中D2单元格，在编辑栏中输入公式“=ABS(C2-B2)”，然后按“Enter”键返回，即可计算出标准重量与实际重量之间的差值并对结果取绝对值，如图14-27所示。

STEP02：选中D2单元格，利用填充柄工具向下复制公式至D8单元格，通过自动填充功能计算出其他产品批次对应的差值，计算结果如图14-28所示。

图14-27　计算第1批次产品的差值

图14-28　计算其他产品批次对应的差值

STEP03：选中E2单元格，在编辑栏中输入公式“=ABS（D2/B2）”，然后按“Enter”键返回，即可计算出实际重量相对标准重量的误差百分率并对结果取绝对值，如图14-29所示。

STEP04：选中E2单元格，利用填充柄工具向下复制公式至E8单元格，通过自动填充功能计算出其他产品批次对应的误差百分率，最终计算结果如图14-30所示。

图14-29　计算第1批次产品的误差百分率

图14-30　计算其他产品批次对应的误差百分率

在求取数字的绝对值时就用到ABS函数。对ABS函数来说，如果number参数不是数值，而是一些字符（如A、b等），则ABS函数将返回错误值“#NAME?”。