28個Excel技巧，3秒完成採購報表！（二）

原標題:28個Excel技巧，3秒完成採購報表！（二）

前幾天小易分享了《17個excel技巧，3秒完成採購報表！（一）》，非常的強大，獲得了採購小夥伴們的大量收藏與轉發。所以今天接著分享採購愛用的excel小技巧（二）

採購報表-SUM函數

很多採購童鞋在做報表時一定會用到SUM函數來求和。但是SUM函數還有多種求和方式。

1.如下圖所示，要求在黃色單元格設置求和公式

傳統做法是設置一個SUM公式，然後複製複製。其實只需要按CTRL+G定位空單元格，然後點一下∑（或按住「ALT」和「=」）

2.設置總計公式

如下圖所示，在設置總計公式時，不需要用小計1+小2+小3...了，只需要設置公式：

=SUM（B2：B14）/2

3.多表求和

栗：合計從1日到31日的所有B2單元格之和。

=SUM(1日：31日！B2）

4.多條件求和

雖然不是SUM函數的強項，但可以給一個小攻是

{=SUM（（a1：a100=「A產品」）b1:b100）}

5.合併單元格求和

合計單元格的求和是一個難點，如果用一般的方法公式很複雜，如果用SUM函數的倒減法，就會變簡單

=SUM（C2：C13）-SUM（D3：D13）

在輸入公式時不能拖動複製，需要選取D2：D13，先在D3輸入公式，然後把游標放在公式結尾處，按ctrl+回車鍵批量輸入

6.任意多表求和

如果N個不相鄰的表的同一個單元格求和，不能直接用SUM，需要用SUM（SUM（結構，這裡等同與SUM(N，提取三維引用的單元格求和）

=SUM（SUM（INDIRECT（{1,2,3}&「！A1」）））

=SUM（N（INDIRECT（{1,2,3}&「！A1」）））

採購報表-Countif函數

如果評工作中最常用的函數是哪個，Vlookup函數是採購公認的NO.1函數，但它只能用於查找。在Excel中還有一個函數比它更有用，是Excel中最重要的一個函數。那就是Countif函數——

1.精確統計

1）返回A列包含值12的單元格數量

=COUNTIF (A:A,12)

2）返回A列部門為「採購部」的數量

=COUNTIF（A:A,「採購部」）

字元在公式中需要加雙引號

3）返回包含值；邏輯值為TRUE的單元格數量

=COUNTIF（A:A,TRUE）

4）返回A1：A10空單元格的數量

=COUNTIF（A1：A10，「=」）

5）返回A列非空單元格的個數（相當於counta函數）

=COUNTIF（A1：A10，「」）

6）返回A1：A10區域，真空+假空的數量

=COUNTIF（A1：A10，「」）

7）返回A1：A10區域所有單元格的數量（非空+空值）

=COUNTIF（data，「「」」）

8）返回A1：A10假空單元格數量

=COUNTIF（A2：A32，「」）-COUNTIF（A2：A32，「=」）

真空+假空-真空=假空

9）統計A列身份證號410105198504182965出現的數量

=COUNTIF（A:A，「410105498504182965*」）

默認情況下，countif函數對數字只識別前15位，而帶上通配符*後可以識別其為文本型格式。

2.模糊計數

1）返回A列單元格包含「採購部」的數量（如：A公司採購部）

=COUNTIF（A:A，「*採購部*」）

2）返回包含以單詞「AB」（部分大小寫）開頭內容的單元格數量

=COUNTIF（A:A，「AB*」）

通配符*是表示任意多個字元，放在字元後面表示，該字元後可以有任意多個字元

3）返回A列單元格中5個字元的數量

=COUNTIF（A:A，「？？？？？」）

通配符？表示佔用一個字元位置，五個？表示五位字元長度

4）返回包含文本內容的單元格數量

=COUNTIF（A:A，「*」）

3.區間統計

1）返回包含負值的單元格數量

=COUNTIF（A:A，「

2）返回不等於0的單元格數量

=COUNTIF（A:A，「0」）

3）返回大於5的單元格數量

=COUNTIF（A:A，「>5」）

4）返回大於單元格A1中內容的單元格數量

=COUNTIF（DATA，「>」&A1）

在引用單元格值時，一定要用&鏈接，千萬不要表示為：「>A1」

5）返回>10並且

=SUM（COUNTIF（A1：A10，「>」&{10，15}）*{1，-1}）

{}表示常量數組，數組的值可以在公式內分別進行運算，每次運算返回一個值。*{1，-1}的目的是上前面的計算結果一個為正，一個為負，用sum求和時達到相減的目的。

6）返回>=10並且

=SUM（COUNTIF（A1：A10，{「>=10」，「>15」}）*{1，-1}）

7）返回包含值為3或-3的單元格數量

=SUM（COUNTIF（A1：A10，{10，-3}））

4.引用函數結果統計

1）返回包含當前日期的單元格數量

=COUNTIF（A:A，TODAY（））

2）返回大於平均值的單元格數量

=COUNTIF（A:A，「>」&AVERAGE（A:A））

推薦閱讀：