條件求和——Excel公式的拿手好戲

在Excel中，如何匯總茶葉中鐵觀音的銷售量

在工作中，經常需要對數據進行求和，有時候還需要對滿足某個特定條件的數據求和。如圖79?1所示為某茶企2010年1月份的茶葉銷售數據，如何對其中的「鐵觀音」銷售量進行求和？

圖79?1茶葉銷售數據表

→ 解決方案1：

使用SUMIF函數進行單條件求和。

→ 操作方法

在G3單元格輸入下列公式：

=SUMIF(B3:B26,F3,D3:D26)

→ 原理分析

SUMIF函數單條件求和

本例中設置條件區域為B3:B26，條件為F3單元格，求和區域為D3:D26，使用SUMIF函數進行單條件求和求出鐵觀音的銷售量。

SUMIF 函數語法如下：

SUMIF(range, criteria,[sum_range])

其中，range參數表示用於條件計算的單元格區域。每個區域中的單元格都必須是數字或名稱、數組或包含數字的引用。空值和文本值將被忽略。criteria參數表示用於確定對哪些單元格求和的條件，其形式可以為數字、表達式、單元格引用、文本或函數。例如，條件可以表示為 32、">32"、B5、"32"、"蘋果" 或 TODAY()。

sum_range參數為可選參數，是需要求和的實際單元格。如果 sum_range 參數被省略，Excel 會對在 range 參數中指定的單元格（即應用條件的單元格）求和。

→ 知識擴展

SUMIF函數第3參數的簡寫形式

SUMIF函數的sum_range 參數與 range 參數的大小和形狀可以不同，但實際求和區域是以sum_range參數左上角單元格為起始單元格的大小和形狀與range參數一致的區域。例如以下2個公式計算效果一致：

公式1 =SUMIF(B3:B26,F3,D3)

公式2 =SUMIF(B3:B26,F3,D3:E4)

公式1是常用的簡寫形式。但由於與range參數大小、形狀不一致，公式在計算時需要根據range參數重新定位sum_range參數所對應的單元格區域，因而表現出「易失性」現象。

在SUMIF函數中使用通配符和數組

SUMIF函數支持在 criteria 參數中使用通配符（包括"?"和"*"）。例如要求出品名中最後一個字為「茶」的茶葉銷售量，可以使用如下公式：

=SUMIF(B3:B26,"*茶",D3:D26)

criteria 參數還可以使用數組。例如要求出其中鐵觀音和龍井的銷售量之和，可以使用如下公式：

=SUM(SUMIF(B3:B26,{"鐵觀音";"龍井"},D3:D26))

其中，SUMIF函數計算得出的計算結果為2行1列的內存數組，再使用SUM函數對此結果求和。由於使用常量數組，因此可以不必按<Ctrl+Shift+Enter>組合鍵。

→ 解決方案2：

使用DSUM函數進行單條件求和。

→ 操作方法

在G3單元格輸入下列公式之一，按Enter鍵結束。

公式1 =DSUM(A2:D26,4,F2:F3)

公式2 =DSUM(A2:D26,D2,F2:F3)

公式3 =DSUM(A2:D26,"銷售量",F2:F3)

→ 原理分析

本例中以A2:D26為列表的單元格區域，field參數設置為4（即表示對區域中的第四列求和），條件區域設置為F2:F3，使用DSUM函數進行單條件求和求出鐵觀音的銷售量。公式2、公式3是field參數的不同表示方式。

DSUM函數用於返回列表或資料庫中滿足指定條件的記錄欄位（列）中的數字之和。語法如下：

DSUM(database, field,criteria)

其中，database參數為構成列表或資料庫的單元格區域；field參數為指定函數所使用的列。輸入兩端帶雙引號的列標籤，如 "品名" 或 "銷售量"；或是代表列在列表中的位置的數字：1 表示第一列，2 表示第二列，依此類推；criteria參數為包含指定條件的單元格區域。可以為參數 criteria 指定任意區域，只要此區域包含至少一個列標籤，並且列標籤下方包含至少一個指定列條件的單元格。

→ 知識擴展

Excel中資料庫的特徵

資料庫是包含一組相關數據的列表，其中包含相關信息的「行」為記錄，而包含數據的「列」為欄位。列表的第一行包含每一列的標籤，必須具備欄位名唯一的特徵，不得使用同名欄位，同時也不能使用合併單元格。

資料庫函數條件參數設置要點：

1．設置單列單條件：條件區域包含列標誌（即欄位名）和條件，欄位名下方為條件數據。如果條件為空單元格，則匯總所有數據。單列單條件區域設置如圖 79?1的F2:F3單元格區域所示。

2．設置單列多個「或」條件：OR(行1條件，行2條件，……，行n條件)。在條件區域的欄位名下方的單獨行中依次鍵入條件。例如匯總鐵觀音與毛尖的銷售量之和，條件區域設置如圖 79?2的K1:K3單元格區域所示。

圖79?2設置單列多條件區域

3．設置多列「且」條件：AND(列1條件，列2條件，……，列n條件)。多個欄位名放在同一行，條件放在欄位名下方的同一行中。例如匯總鐵觀音在2010年1月10日之後的銷售量之和，條件區域設置如圖 79?3的K1:L2單元格區域所示。

圖79?3設置多列並列條件區域

4．設置多列「或」條件：OR(列1條件，列2條件，……，列n條件)。多個欄位名放在同一行，條件放在欄位名下方的不同行中。例如匯總鐵觀音及日期小於等於2010年1月10日的銷售量之和，條件區域設置如圖 79?4的K1:L3單元格區域所示。

圖79?4設置多列「或」條件區域

5．設置多列複合條件：OR(AND(條件，……)，AND(條件，……))。多個欄位名放在同一行，同行條件為並，不同行條件為或。例如匯總鐵觀音在2010年1月10日之前的銷售量與日期大於2010年1月20日的銷售量總和，條件區域設置如圖 79?5的K1:L3單元格區域所示。

圖79?5設置多列複合條件區域

6．在條件中使用通配符：在條件中可以使用問號(?)、星號(*)等通配符設置模糊條件。例如匯總品名中包含「龍」字的所有銷售量之和，條件區域設置如圖 79?6的K1:K2單元格區域所示。

圖79?6使用通配符做條件

7．在條件中使用公式：在資料庫函數的條件區域中使用公式，與高級篩選條件區域的公式設置相似。例如匯總大於平均值的銷售量之和，則在K2單元格輸入下列公式作為條件：

=D3>AVERAGE($D$3:$D$26)

條件區域設置如的K1:K2單元格區域所示。