在工作中，有時候為了規範數據的取值範圍， 需要對數據設置一定的上限和下限，即當數值處於下限～上限區間時，取值為數值本身，超過限制時，則取極限值。如圖 98‑1所示為某公司2010年3月份的員工銷售業績表，現在需要按照銷售業績的1%計算每個員工的提成獎金，但獎金額度最高不超過1000，保底100，該如何操作呢？
圖98‑1為提成獎金設置上下限
解決方案1
使用MAX和MIN函數組合設置數值的上下限。
操作方法
選擇D3:D18單元格區域，輸入下列2個公式之一，按<Ctrl+Enter>組合鍵結束。
=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單元格區域，輸入下列公式，按<Ctrl+Enter>組合鍵結束。
=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(數值<下限,下限,數值))
本例也可以如下公式：
=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被識別為數字占位符。
圖98‑1為提成獎金設置上下限
解決方案1
使用MAX和MIN函數組合設置數值的上下限。
操作方法
選擇D3:D18單元格區域，輸入下列2個公式之一，按<Ctrl+Enter>組合鍵結束。
=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單元格區域，輸入下列公式，按<Ctrl+Enter>組合鍵結束。
=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(數值<下限,下限,數值))
本例也可以如下公式：
=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被識別為數字占位符。
收藏