本文作者：農(nóng)夫

本文審核：瑪奇鵝

本文編輯：竺蘭

大家好，我是農(nóng)夫，專治疑難雜「數(shù)」的農(nóng)夫~

日常工作中，我們在用 Excel 錄入或瀏覽數(shù)據(jù)時，將一些比較重要的數(shù)據(jù)標(biāo)記為不同的顏色或格式，會使整個數(shù)據(jù)表重點突出，分類明確。

但標(biāo)記一時爽，匯總統(tǒng)計心慌慌

比如下表，現(xiàn)在要對不同顏色的數(shù)據(jù)進(jìn)行求和：

有些小伙伴面對這種情況，馬上開啟「人工智能」模式，選中每個數(shù)據(jù)來求和。

但是，這樣不僅浪費時間，而且很容易漏掉數(shù)據(jù)，最終等候你的，大概率就是加班的問候了

有沒有更高效的解決辦法呢？

這個真的有，接下來我就為大家介紹一下，對于單列、多列和區(qū)域中標(biāo)記顏色數(shù)據(jù)的求和方法。

單列按顏色求和

我們先來看在單列中，如何對標(biāo)記為相同顏色的單元格求和。

這里我們介紹兩種方法，一是 SUBTOTAL 函數(shù)法；二是查找+定義名稱法。

SUBTOTAL 函數(shù)：在指定范圍內(nèi)，根據(jù)指定的分類匯總函數(shù)進(jìn)行計算，具體公式如下：

▲ 左右滑動查看

其中，函數(shù)中的公式代碼：1~11（數(shù)據(jù)計算中包含隱藏值），101~111（數(shù)據(jù)計算中不包含隱藏值），詳細(xì)解讀，可點擊以下傳送門：

隱藏行匯總，為什么用的時候總想不起來？看完這篇你就會了！

這里選用了公式代碼 109，使得 SUBTOTAL 函數(shù)只對當(dāng)前可見單元格進(jìn)行求和，即對篩選后的數(shù)據(jù)重新加總。

因此，通過對數(shù)據(jù)列進(jìn)行顏色篩選，即可獲取相應(yīng)顏色單元格的求和結(jié)果。

SUBTOTAL 函數(shù)只能對單列中，特定顏色的數(shù)據(jù)，在篩選后進(jìn)行求和。

而查找+定義名稱法，既可以在單列中求和，也可以在數(shù)據(jù)區(qū)域中求和，進(jìn)而簡化操作。

? 按快捷鍵【Ctrl+F】，調(diào)出查找窗口，點擊【格式】，選擇【從單元格選擇格式】。

當(dāng)鼠標(biāo)變?yōu)?strong>十字加吸管時，我們就可以點擊任何一個單元格，將其格式完全復(fù)制過來，不需要自己重新設(shè)置。

? 格式設(shè)置好后，再選中要查找的數(shù)據(jù)范圍，點擊【查找全部】，按【Ctrl+A】鍵，這樣就可以選中所有查找到的單元格了。

? 關(guān)閉「查找替換」窗口，選擇【公式】選項卡，點擊【定義名稱】，在【新建名稱】頁面中，更改相應(yīng)的名稱即可，這里我們將名稱命名為黃色，點擊【確定】。

或直接在名稱框中定義，只需要選中所有填充為黃色的單元格，然后在名稱框中輸入黃色-敲回車即可。

這樣所有你選定的數(shù)據(jù)，就成為你所定義的這個名稱下的成員了。

? 利用 SUM 函數(shù)，將函數(shù)的參數(shù)改為剛剛定義的名稱，按【Enter】回車，即可得出求和結(jié)果了。比如：

同樣的，你還可以對綠色和藍(lán)色的數(shù)據(jù)進(jìn)行求和~

注意，如果【從單元格選擇格式】吸取了單元格的格式，查找后出現(xiàn)這種情況：

這就需要自己按照單元格的格式來進(jìn)行設(shè)置了~

利用上述原理，對于某區(qū)域中的特定格式單元格，無論是連續(xù)區(qū)域，還是零散的單元格，都能在定義名稱后，利用 Sum 函數(shù)實現(xiàn)求和。

?? 操作步驟如下圖所示：

數(shù)據(jù)區(qū)域中對每列求和

針對數(shù)據(jù)區(qū)域的每列進(jìn)行求和，可以利用宏表函數(shù) Get.Cell 及 SUMIF 函數(shù)實現(xiàn)。

? 選擇【公式】選項卡，點擊【定義名稱】，在【新建名稱】頁面中，更改相應(yīng)的名稱即可，這里我們將名稱命名為顏色，【引用位置】填入相應(yīng)的公式，點擊【確定】。

這樣所有相同格式的數(shù)據(jù)，就相當(dāng)于存儲在你所定義的名稱下面了。

之后，在使用等號引用這個名稱的時候，所填入的函數(shù)即可發(fā)揮作用了。

這里面最為重要的就是引用位置所設(shè)置的函數(shù)了，下面我們來對函數(shù)進(jìn)行拆分講解：

Excel 中 Get.Cell 函數(shù)的功能就是獲取單元格的信息。其具體語法如下：

其中，類型代碼范圍為 1-66，即 Get.Cell 函數(shù)可以獲取單元格中的 66 種信息。

這里我只列舉幾種常用的類型代碼，如果想要獲取全部類型代碼，可在后臺回復(fù)： Get 

這里需要注意：Get.Cell 函數(shù)如果按照常規(guī)方法在單元格中輸入是沒有任何用處的，并且還會提示函數(shù)無效。

因此，Get.Cell 函數(shù)不能寫在公式里，一般都是與定義名稱結(jié)合使用。

其中，「GET.CELL(63,帶顏色求和!B2)」中，Get.Cell 主要是獲取單元格的格式內(nèi)容，而類型代碼 63 則代表單元格的背景顏色~

那么，「&T(NOW())」又代表什么意思呢？

NOW 函數(shù)會返回當(dāng)前的日期和時間序列，它是隨著電腦時間更新而變化的，同時，該函數(shù)并沒有參數(shù)值；

T 函數(shù)主要是判定單元格內(nèi)容是否為文本，若是的話，返回當(dāng)前內(nèi)容；否則，返回為空。

因為時間數(shù)據(jù)不是文本，所以返回的內(nèi)容永遠(yuǎn)為空。

因此，T 函數(shù)的作用是通過 NOW 函數(shù)的不斷變化，讓 Get.Cell 函數(shù)實現(xiàn)自動刷新功能。

? 接下來，我們設(shè)置一個與原數(shù)據(jù)行列相同的區(qū)域，在相應(yīng)的單元格中寫入「=顏色」，結(jié)果會返回填充單元格顏色的代碼。

我們發(fā)現(xiàn)返回的結(jié)果區(qū)域只包含 0 和 6 兩個數(shù)字。

這是因為原數(shù)據(jù)區(qū)域單元格中只有兩類單元格：沒有任何填充的單元格和填充為黃色的單元格。

而在 Get.Cell 函數(shù)下，類型代碼 63 會將沒有任何填充的單元格返回 0，而填充為黃色的單元格會返回代表黃色的編碼 6。

? 然后，我們就可以利用 SUMIF 單條件求和函數(shù)，對每一列標(biāo)黃的單元格分別進(jìn)行求和，具體語法如下：

這里，我們將只包含 0 和 6 的 K2:L13 列為條件區(qū)域，對應(yīng)的 B2:B13 區(qū)域為求和區(qū)域，而黃色對應(yīng)的編碼 6 為求和條件，具體公式如下：

這樣，就可以對每列中標(biāo)黃單元格分別進(jìn)行求和了。

更方便的是，利用這種方法，當(dāng)源數(shù)據(jù)變動時，只需對工作表進(jìn)行刷新（按快捷鍵【F9】），計算結(jié)果即可自動更新。

這樣數(shù)據(jù)有變時，就不必每次都從頭操作一遍啦~

總結(jié)回顧

經(jīng)過上面的講解，大家是否學(xué)會了對標(biāo)記顏色的數(shù)據(jù)求和呢？最后，我們再來總結(jié)回顧下相關(guān)操作方法：

單列按顏色求和

方法一：Subtotal 函數(shù)

?公式代碼為 109

?按顏色篩選

方法二：查找+定義名稱

?【Ctrl+F】

?按【Shift】鍵選中所有查找到的數(shù)據(jù)

?定義名稱

?SUM 函數(shù)

數(shù)據(jù)區(qū)域中對每列求和

? 定義名稱

? 引用位置輸入公式=GET.CELL(63,帶顏色求和!B2)&T(NOW())

? 引用所定義的名稱創(chuàng)建一個與原數(shù)據(jù)行列相同的區(qū)域

? SUMIF 函數(shù)

?【F9】刷新

練習(xí)文件獲取方式