Excel減法函數用法的7個實例,含帶條件、數字帶單位、有標識才相減和用表格所有數減一個數

在 Excel 中,計算減法有兩種方法,一種是用加法函數(即求和函數)Sum 計算,另一種是用減號計算。用Sum函數計算減法時,只需在減數前加減號;用減號計算直接用一個數減另一個數即可。

在計算減法過程中,如果遇到帶條件的減法,需要用If函數來判斷條件;如果遇到參與運算的數字帶單位,需要用Left函數和Find函數來截取數字,然后再計算;如果要求有標識的數字才參與計算,則需要結合 Indirect + Match + Row 函數實現;如果要求用表格中的所有數減一個數,則需要用選擇性粘貼。

一、Excel減法函數說明

Excel 并未直接提供減法函數,減法運算需要使用加法運算函數,即求和函數 Sum,所不同的是只需在要減掉的數前加減號(負號)。另外,除可用Sum函數做減法運算外,還可以直接用減號(即 -)。

二、Excel減法函數的使用方法及實例

(一)兩個數求差的實例

1、假如要把 B 列的數字減掉 A 列的數字。雙擊 C1 單元格,把公式 =SUM(-A1,B1) 復制到 C1,按回車,返回 201;選中 C1,把鼠標移到 C1 右下角的單元格填充柄上,鼠標變為黑色加號后,雙擊左鍵,則求出剩余數字之差;操作過程步驟,如圖1所示:

圖1

2、說明:

公式 =SUM(-A1,B1) 中,A1 前的 - 表示減 A1,公式的意思是:用 B1 減 A1,即用Sum函數計算減法只需在減數前加減號(負號)。

(二)相減結果為負數加絕對值轉為正數的實例

1、假如要用 A 列的數字減 B 列的數字,A 列數字小,B 列數字大。雙擊 C1,把公式 =SUM(A1,-B1) 復制到 C1,按回車,返回 -201;雙擊 C1,給公式加返回絕對值函數ABS,則公式變為 =ABS(SUM(A1,-B1)),按回車,返回 201;操作過程步驟,如圖2所示:

圖2

2、公式說明:

公式 =SUM(A1,-B1) 返回的為負數,如果要求做減法運算后返回正數,只需再加一個取絕對值函數ABS。

三、用減號計算減法的實例

1、選中 C1 單元格,輸入公式 =B1-A1,按回車,返回 201;選中 C2,輸入公式 =A2-B2,按回車,返回 -326,雙擊 C2,給公式加上取絕對值函數 ABS,則公式變為 =ABS(A2-B2),按回車,返回 326;操作過程步驟,如圖3所示:

圖3

2、公式說明:

用減號計算減法直接用一個數減另一個數即可,如果所得結果為負數且要求返回正數,同樣加取絕對值函數 ABS。

四、Excel減法函數或用減號計算的擴展應用實例

(一)數字帶單位的減法運算

1、假如要計算1月與2月銷量之差,且1月銷量帶單位。雙擊 C2 單元格,把公式 =LEFT(A2,FIND("斤",A2)-1)-B2 復制到 C2,按回車,返回 9;選中 C2,用雙擊 C2 單元格填充柄的方法求剩余銷量之差;操作過程步驟,如圖4所示:

圖4

2、公式=LEFT(A2,FIND("斤",A2)-1)-B2說明:

A、FIND("斤",A2) 用于找“斤”在 A2 中的位置,結果為 4,以把它作為下一步只提取數字的字符個數。之所以要用Find函數返回要提取的數字個數,是因為 A 列不同單元格數字個數不一樣,而數字后都有“斤”字,只要返回“斤”字在文本中的位置再減 1,就能準確的計算得數字個數。

B、則 LEFT(A2,FIND("斤",A2)-1) 變為 LEFT(A2,4-1),進一步計算,用 Left 從 A2 的左邊開始截取三個字符,結果為 995。

C、則公式變為 =995-B2,B2 為 986,兩數相減,結果為 9。

(二)帶條件的減法運算

1、要求:如果 B2 不為空,用 A2 減 B2;否則如果 C2 不為空,用 A2 減 C2;否則如果 D2 不為空,用 A2 減 D2;否則返回空文本。雙擊 E2 單元格,把公式 =IF(B2<>"",A2-B2,IF(C2<>"",A2-C2,IF(D2<>"",A2-D2,""))) 復制到 E2,按回車,返回 3;選中 E2,雙擊 E2 的單元格填充柄,則返回剩余數值的結果;操作過程步驟,如圖5所示:

圖5

2、公式=IF(B2<>"",A2-B2,IF(C2<>"",A2-C2,IF(D2<>"",A2-D2,"")))說明:

A、公式嵌套了兩個 If,執行時先從最外層的 If 開始,第一次取最外層 If 的條件 B2<>"" 來判斷,如果成立(即 B2 不為空),則執行 A2-B2,否則執行 IF(C2<>"",A2-C2,IF(D2<>"",A2-D2,""));因為 B2 為 6 不空為,因此執行 A2-B2,即 9-6。

B、當公式在 E3 時,則公式變為 =IF(B3<>"",A3-B3,IF(C3<>"",A3-C3,IF(D3<>"",A3-D3,""))),由于 B3 為空,因此執行 IF(C3<>"",A3-C3,IF(D3<>"",A3-D3,""));C3 為 4,因此 C3<>"" 成立,執行 A3-C3,即 6-4。

C、當公式在 E4 時,則公式變為 =IF(B4<>"",A4-B4,IF(C4<>"",A4-C4,IF(D4<>"",A4-D4,""))),由于 B4、C4 和 D4 都為空,因此三個If 的條件沒有一個成立,最后返回最里層 If 的第三個參數,即返回 ""。

(三)一列中有標識的數字才相減

1、假如要求 B 列有 1 的 A 列的兩數字才相減,例如:B2 和 B4 都有 1,則用 A2 減 A4。雙擊 C2 單元格,把公式 =IFERROR(A2-INDIRECT("A"&(ROW()+MATCH(B2,B3:B$9,0))),"") 復制到 C2,按回車,返回 36;選中 C2,雙擊 C2 的單元格填充柄,則計算出剩余有標識的兩數之差;操作過程步驟,如圖6所示:

圖6

2、公式=IFERROR(A2-INDIRECT("A"&(ROW()+MATCH(B2,B3:B$9,0))),"")說明:

A、ROW() 用于返回公式所在行的行號,當公式在 C2,它返回 2。

B、MATCH(B2,B3:B$9,0) 用于返回 B2 下離 B2 最近的標識(即 1)的位置,結果為 2;B2 為查找值,B3:B$9 為查找范圍,0 表示精確匹配;B2 和 B3 為相對引用,當往下拖時,B2 會變為 B3、B4 等,B3 會變為 B4、B5 等;B$9 表示對列相對引用對行絕對引用,當往下拖時,B$9 不會變為 B10、B11 等;之所以要用 B3:B$9,是因為要實現始終在當前單元格的下一個單元格到最后一個單元格的范圍內查找標識。

C、則 INDIRECT("A"&(ROW()+MATCH(B2,B3:B$9,0)) 變為 INDIRECT("A"&(2+2)),進一步計算變為 INDIRECT("A4"),接著再用 Indirect 返回對字符串 "A4" 的引用,即返回 23。

D、則公式變為 =IFERROR(A2-23,""),A2 為 59,A2-23 等于 36;IfError 是錯誤處理函數,如果 A2-23 返回錯誤,IfError 返回 "",否則返回 A2-23。

(四)用表格中所有的數減一個數

1、假如要用表格中所有數字減掉 100。選中 F2 單元格,輸入 100,按回車,再次選中 F2,按 Ctrl + C 復制,框選 B2:E9,右鍵選中的區域,在彈出的菜單中選擇“選擇性粘貼”,打開“選擇性粘貼”窗口,選擇“粘貼”下面的“數值”,再選擇“運算”下的“減”,單擊“確定”,則表格中的所有數字都減掉 100;操作過程步驟,如圖7所示:

圖7

2、如果選擇“粘貼”下的“全部”,則會把 100 所在的 F2 單元格的格式一起粘貼到 B2:E9,也就是用 F2 的格式覆蓋掉 B2:E9 的格式,但選擇“數值”,則只粘貼數值不會覆蓋格式。