为数组设置上限、下限

gouweicao78

【声明】原创作品，非允勿转。

【概念】
上、下限是工作中常用的限制数值的需求，即当数值处于下限~上限区间时，取值为数值本身，超过限制时，则取限制值。
【为单值设置上下限示例】
比如：当一个值超过100时，返回100，否则返回数值本身；当一个值小于20时，返回20，否则返回数值本身。
其中，上限为100，下限为20。

【解法1】
=IF(A1>100,100,IF(A1<20,20,A1))
【解法2】
=MIN(100,MAX(A1,20))
【解法3】
=MEDIAN(100,20,--A1)
【解法4】
=--TEXT(A1,"[>100]1!0!0;[<20]2!0")

上述解法，当A1为空单元格时，除了解法2返回下限外，其它都返回0。
貌似解法4的公式长度比较长，呵呵。

【为数组设置上下限】
如果要求将A1:A10返回为设置了上限为100、下限为20的数组时，上述4个解法只有解法1、解法4能胜任，解法2、3使用的MAX、MEDIAN均只能返回单值。
比如：
=IF(A1:A10>100,100,IF(A1:A10<20,20,A1:A10))
=--TEXT(A1:A10,"[>100]1!0!0;[<20]2!0")

相对而言，TEXT的解法变得更短些。

【通用式】
从解法4可以视为：=--TEXT(数值,"[>上限]上限;[<下限]下限")
其中，红色的上限、下限为“条件”，可以直接用文本连接符&相连
而蓝色的上限、下限为需要返回的值，因数值中常有0出现，需设置占位符!或\，但如果上下限是一个变量，无从得知是否有0时，可以用CHAR(34)&上限&CHAR(34)将它强制用CHAR(34)返回的引号"包含起来，作为“文本”。

通用式为：
=--TEXT(数值,"[>"&上限&"]"&CHAR(34)&上限&CHAR(34)&";[<"&下限&"]"&CHAR(34)&下限&CHAR(34))

在Excel2007中，通用式为：

=--TEXT(A1,"[>"&B1&"]"&CHAR(34)&B1&CHAR(34)&";[<"&C1&"]"&CHAR(34)&C1&CHAR(34)&";G/通用格式")

【案例】：比较麻烦的问题！跨月如何判断??在线等！

[ 本帖最后由 gouweicao78 于 2009-8-13 17:12 编辑 ]

补充内容 (2017-10-29 08:53):
在ExcelHome技术论坛的最佳学习方法：http://club.excelhome.net/thread-117862-1-1.html

水平一般

谢谢分享。

biaotiger1

通用式为：
=--TEXT(数值,"[>"&上限&"]"&CHAR(34)&上限&CHAR(34)&";[<"&下限&"]"&CHAR(34)&下限&CHAR(34))

在Excel2007中，通用式为：

=--TEXT(A1,"[>"&B1&"]"&CHAR(34)&B1&CHAR(34)&";[<"&C1&"]"&CHAR(34)&C1&CHAR(34)&";G/通用格式")
学习了，我还真不知道
谢谢斑竹

ggtang

通过版主的介绍来学习，很好的用法！

xcyd

很受启发，多谢了！

guideming

受益非浅

多谢!

zxzyer2009

我想请问一下,如果用窗体录入的方法.在我确定的时候,超出设置的下限上限的数量时就发生提示.而且可能不同品名的上限下限不一样.请问怎么解?

gouweicao78

窗体的问题，应该属于VBA方面的吧。请到VBA程序版提问吧，呵呵，我就帮不上忙了。

百香果

谢谢，好极了

bibisin

谢谢版主分享,学习了