一個Excel公式搞定提成獎金的上下限

shiyiyuting 2016-03-27

展開全文

請點左下角的“閱讀原文”

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

在工作中，有時候為了規范數據的取值范圍，需要對數據設置一定的上限和下限，即當數值處于下限～上限區間時，取值為數值本身，超過限制時，則取極限值。如圖 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(數值下限,下限,數值))

本例也可以如下公式：

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

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

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

本例也可以使用如下2個公式之一：

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