使用MEDIAN函数或MAX和MIN函数组合设置数值的上下限

网友投稿 1814 2022-06-01

在工作中,有时候为了规范数据的取值范围, 需要对数据设置一定的上限和下限,即当数值处于下限~上限区间时,取值为数值本身,超过限制时,则取极限值。如图 98‑1所示为某公司2010年3月份的员工销售业绩表,现在需要按照销售业绩的1%计算每个员工的提成奖金,但奖金额度最高不超过1000,保底100,该如何操作呢?

图98‑1为提成奖金设置上下限

解决方案1

使用MAX和MIN函数组合设置数值的上下限。

操作方法

选择D3:D18单元格区域,输入下列2个公式之一,按组合键结束。

=MIN(1000,MAX(100,C3*1%))

=MAX(100,MIN(1000,C3*1%))

原理分析

使用MIN、MAX函数设置上限或下限

1.首先将销售业绩乘以1%与100进行比较,使用MAX函数提取最大值,当1%销售业绩低于100时取100,即给提成奖金设置了下限。

2.将MAX函数返回的值与1000比较,使用MIN函数提取最小值,当MAX超过1000时取1000, 即给提成奖金设置了上限,达到限制提成奖金处在100~1000的目的。

使用MAX、MIN函数组合设置上、下限的通用公式为:

=MIN(上限,公式或数值)

=MAX(下限,公式或数值)

MAX函数用于返回一组值中的最大值。MIN函数用于返回一组值中的最小值。语法如下:

MAX(number1,[number2], ...)

MIN(number1,[number2], ...)

其中,各个number参数为需要找出最大值(最小值)的 1 到 255 个数字参数,可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为数组或引用,则只使用该数组或引用中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果参数不包含数字, MAX、MIN 函数返回 0。如果参数为错误值或为不能转换为数字的文本,将会导致错误。如果要使计算包括引用中的逻辑值和代表数字的文本,请使用 MAXA 、MINA函数。

解决方案2

使用MEDIAN函数设置数值上下限。

操作方法

选择D3:D18单元格区域,输入下列公式,按组合键结束。

=MEDIAN(100,1000,C3*1%)

原理分析

使用MEDIAN函数设置上下限

当需要同时设置上限和下限时,只存在3个数值需要进行比较,即下限值100、上限值1000、计算值C3*1%,因而可以利用MEDIAN函数取中间值的特性,当计算值小于100时,MEDIAN函数返回中间值100,当计算值大于1000时,MEDIAN函数返回中间值1000,当计算值处在100~1000区间时,MEDIAN函数返回计算值,达到限制提成奖金处在100~1000的目的。

其通用公式为:

=MEDIAN(上限,下限,公式或数值)

MEDIAN函数用于返回给定数值的中值(中值是在一组数值中居于中间的数值),语法如下:

MEDIAN(number1,[number2], ...)

其中,各个number参数是要计算中值的 1 到 255 个数字,如果参数集合中包含偶数个数字,函数 MEDIAN 将返回位于中间的两个数的平均值。参数可以是数字或者是包含数字的名称、数组或引用。逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果数组或引用参数包含文本、逻辑值或空白单元格,则这些值将被忽略;但包含零值的单元格将计算在内。如果参数为错误值或为不能转换为数字的文本,将会导致错误。

知识扩展

使用IF、TEXT函数设置上、下限

1.使用IF函数设置数值的上、下限,其通用公式为:

=IF(数值>上限,上限,数值)

=IF(数值<下限,下限,数值)

=IF(数值>上限,上限,IF(数值<下限,下限,数值))

使用MEDIAN函数或MAX和MIN函数组合设置数值的上下限

本例也可以如下公式:

=IF(C3*1%>1000,1000,IF(C3*1%<100,100,C3*1%))

2.使用TEXT函数设置数值的上、下限,其通用公式为:

=--TEXT(数值,"[>上限]上限值文本;[<下限]下限值文本;G/通用格式")

本例也可以使用如下2个公式之一:

公式1        =--TEXT(C3*1%,"[>1000]1!0!0!0;[<100]1!0!0;G/通用格式")

公式2        =--TEXT(C3*1%,"[>1000]""1000"";[<100]""100"";G/通用格式")

其中,公式1下限值100用“1!0!0”表示,在0前面使用!或\号强制显示为0,公式2使用""100""将其表示为文本,目的均是将防止其中的0被识别为数字占位符。

版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。

上一篇:文档背景黑色怎么处理(文档背景是黑色的怎么去掉)
下一篇:文档目录怎么自动生成(word文档目录怎么自动生成)
相关文章