請點(diǎn)左下角的“閱讀原文”

在Excel中，如何設(shè)置提成獎金的上下限

在工作中，有時(shí)候?yàn)榱艘?guī)范數(shù)據(jù)的取值范圍， 需要對數(shù)據(jù)設(shè)置一定的上限和下限，即當(dāng)數(shù)值處于下限～上限區(qū)間時(shí)，取值為數(shù)值本身，超過限制時(shí)，則取極限值。如圖 98?1所示為某公司2010年3月份的員工銷售業(yè)績表，現(xiàn)在需要按照銷售業(yè)績的1%計(jì)算每個員工的提成獎金，但獎金額度最高不超過1000，保底100，該如何操作呢？

圖98?1為提成獎金設(shè)置上下限

→ 解決方案1：

使用MAX和MIN函數(shù)組合設(shè)置數(shù)值的上下限。

→ 操作方法

選擇D3:D18單元格區(qū)域，輸入下列2個公式之一，按組合鍵結(jié)束。

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

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

→ 原理分析

使用MIN、MAX函數(shù)設(shè)置上限或下限

1．首先將銷售業(yè)績乘以1%與100進(jìn)行比較，使用MAX函數(shù)提取最大值，當(dāng)1%銷售業(yè)績低于100時(shí)取100，即給提成獎金設(shè)置了下限。

2．將MAX函數(shù)返回的值與1000比較，使用MIN函數(shù)提取最小值，當(dāng)MAX超過1000時(shí)取1000， 即給提成獎金設(shè)置了上限，達(dá)到限制提成獎金處在100～1000的目的。

使用MAX、MIN函數(shù)組合設(shè)置上、下限的通用公式為：

=MIN(上限,公式或數(shù)值)

=MAX(下限,公式或數(shù)值)

MAX函數(shù)用于返回一組值中的最大值。MIN函數(shù)用于返回一組值中的最小值。語法如下：

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

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

其中，各個number參數(shù)為需要找出最大值（最小值）的 1 到 255 個數(shù)字參數(shù)，可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。如果參數(shù)為數(shù)組或引用，則只使用該數(shù)組或引用中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。如果參數(shù)不包含數(shù)字， MAX、MIN 函數(shù)返回 0。如果參數(shù)為錯誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會導(dǎo)致錯誤。如果要使計(jì)算包括引用中的邏輯值和代表數(shù)字的文本，請使用 MAXA 、MINA函數(shù)。

→ 解決方案2：

使用MEDIAN函數(shù)設(shè)置數(shù)值上下限。

→ 操作方法

選擇D3:D18單元格區(qū)域，輸入下列公式，按組合鍵結(jié)束。

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

→ 原理分析

使用MEDIAN函數(shù)設(shè)置上下限

當(dāng)需要同時(shí)設(shè)置上限和下限時(shí)，只存在3個數(shù)值需要進(jìn)行比較，即下限值100、上限值1000、計(jì)算值C3*1%，因而可以利用MEDIAN函數(shù)取中間值的特性，當(dāng)計(jì)算值小于100時(shí)，MEDIAN函數(shù)返回中間值100，當(dāng)計(jì)算值大于1000時(shí)，MEDIAN函數(shù)返回中間值1000，當(dāng)計(jì)算值處在100～1000區(qū)間時(shí)，MEDIAN函數(shù)返回計(jì)算值，達(dá)到限制提成獎金處在100～1000的目的。

其通用公式為：

=MEDIAN(上限,下限,公式或數(shù)值)

MEDIAN函數(shù)用于返回給定數(shù)值的中值（中值是在一組數(shù)值中居于中間的數(shù)值），語法如下：

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

其中，各個number參數(shù)是要計(jì)算中值的 1 到 255 個數(shù)字，如果參數(shù)集合中包含偶數(shù)個數(shù)字，函數(shù) MEDIAN 將返回位于中間的兩個數(shù)的平均值。參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的單元格將計(jì)算在內(nèi)。如果參數(shù)為錯誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會導(dǎo)致錯誤。

→ 知識擴(kuò)展

使用IF、TEXT函數(shù)設(shè)置上、下限

1．使用IF函數(shù)設(shè)置數(shù)值的上、下限，其通用公式為：

=IF(數(shù)值>上限,上限,數(shù)值)

=IF(數(shù)值下限,下限,數(shù)值)

=IF(數(shù)值>上限,上限,IF(數(shù)值下限,下限,數(shù)值))

本例也可以如下公式：

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

2．使用TEXT函數(shù)設(shè)置數(shù)值的上、下限，其通用公式為：

=--TEXT(數(shù)值,'[>上限]上限值文本;[下限]下限值文本;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前面使用!或\號強(qiáng)制顯示為0，公式2使用''100''將其表示為文本，目的均是將防止其中的0被識別為數(shù)字占位符。

版權(quán)所有 轉(zhuǎn)載須經(jīng)Excel技巧網(wǎng)許可