Excel的加總函數

使用 Excel 做加總，可以用簡單的算式或是使用函數來達到目的。

如果只是簡單的幾個儲存格的相加，用算式就可以了，例如：

要將 A1 及 A2 儲存格的內容相加時，只要在 B1 儲存格寫入 [=A1+A2]即可。

但若是由 A1 到 A100 都要加總時，用函數會比較方便，此時在 B1 儲存格寫入[=SUM(A1:A100)]就可以了，不用寫成 =A1+A2+A3+……+A98+A99+A100

當要做有條件的加總時，需要使用 SUMIF 或 SUMIFS 這兩個函數，

單一條件時，使用 SUMIF 函數

複合條件時，使用 SUMIFS 函數(最多可設定127組條件)(這是從 Excel 2007 開始的函數，之前版本的 Excel 需要使用更複雜的方式才能完成同樣的功能。)

範例：

假設目前有資料如下圖

為方便公式之編寫及日後之擴充，使用動態名稱先行定義名稱如下：

要產生之報表格式如下：

在 F2 儲存格填入 全區總收入 的公式 [=SUM(收入)]

在 H1 儲存格填入 北區總收入 的公式 [=SUMIF(地區,"北區",收入)]

在 H2 儲存格填入 北區 一店 的公式 [=SUMIFS(收入,地區,"北區",分公司,”一店”)]

在 O1 儲存格填入 一月份總收入 的公式 [=SUMIF(月份,”一月”,收入)]

再依照同樣的規格，分別在對應的儲存格填入公式，即可完成。

如果想要直接使用複製貼上的方式將公式複製到對應儲存格時，則公式可改寫如下：

在 H1 儲存格填入 北區總收入 的公式 [=SUMIF(地區,LEFT(G1,2),收入)]，此公式可直接用複製貼上的方式，貼到 J1 及 L1 儲存格。

在 H2 儲存格填入 北區 一店 的公式 [=SUMIFS(收入,地區,LEFT(G$1,2),分公司,G2)]，再將公式貼到 H3,H4,I2,I3,I4,L2,L3及L4 儲存格。

在 O1 儲存格填入 一月份總收入 的公式 [=SUMIF(月份,LEFT(N1,2),收入)]，再將公式貼到 O2 及 O3 儲存格。

如此即完成報表上公式設定。

附記：當資料區有新增資料時，在未完成所有欄位資料之填寫時，會產生[#VALUE!]的錯誤訊息，但在資料填寫完畢時，就會自動產生正確結果。

如何在 Excel 中定義動態名稱範圍

使用[名稱]，可以公式更容易了解及維護。可以為儲存格範圍、函數、常數或資料表定義名稱。一旦採取在活頁簿中使用名稱的做法以後，就可以輕鬆更新、稽核及管理這些名稱。

想要知道更多關於名稱的使用及設定方式，可以在開啟 Excel 時，按下[F1]按鍵，叫出 [Excel 說明]後，用[名稱]為關鍵字搜尋，就可以看到了。

要定義動態名稱範圍時，需要利用到 OFFSET函數及 COUNTA函數，關於函數的進一步說明，請自行使用[Excel 說明]去查詢。

如何定義動態名稱範圍

一、當欄位不會增加，只有列數會增加時：

(通常是搭配應用在 SUM函數或下拉選單之類)

點選[公式]索引標籤下的[定義名稱]

在[名稱]的欄位輸入自行定義的名稱

[在參照到]的欄位輸入[=OFFSET(Sheet2!$A$1,1,,COUNTA(Sheet2!$A:$A)-1,)]

按下[確定]即完成設定

OFFSET(reference, rows, cols, [height], [width])

reference 填入[Sheet2!$A$1]是因為要跨工作表使用，所以要把工作表位置也寫出來。

row 填入[1]是因為有標題列，當要做加總或下拉式選單時，並不需要包含標題列，所以由參照位置向下移一列。

[height] 填入 [COUNTA(Sheet2!A:$A)-1]是因為要計算 Sheet2中A欄中非空白列的筆數且扣除標題列。

cols 和 [width] 沒有輸入任何值，是省略。欄不需位移，而陣列寛度保持原狀。

二、當欄位和列數都會增加時：

(通常是搭配應在用 VLOOKUP函數之類)

點選[公式]索引標籤下的[定義名稱]

在[名稱]的欄位輸入自行定義的名稱

[在參照到]的欄位輸入[=OFFSET(Sheet3!$A$1,,,COUNTA(Sheet3!$A:$A),COUNTA(Sheet3!$1:$1))]

按下[確定]即完成設定

Excel 中的二層式下拉選單並不難設定

Excel動態下拉式選單(二層式下拉選單)

因為懶得想例子，所以就用季別和月份來做範例，反正只是個概念而已。

1.     在任一工作表中，建立下拉式選單之清單

例如：在  Sheet2 中建立如下圖之清單

 

2.     建立第一層的下拉選單的清單內容範圍

選取 A1:D1 儲存格後，在功能區的[公式]頁面下，選取[定義名稱]下的[定義名稱]

在[名稱]的欄位中填入[季別]後，按下[確定]

3.     建立第二層下拉式選單的清單內容範圍

先選取 A1:D4 的範圍，再同時按下[Ctrl]+[Shift]+[F3]三個按鍵(或者是點選[從選取範圍建立])

只勾選[頂端列]這個選項後按[確定]，此時就會產生四個新的名稱，使用[名稱管理員]就可以看到剛才所建立的名稱如下圖

4.     設定第一層清單

切換到  Sheet1，

在選取 A2:A10 的範圍後，點選[功能區]下[資料工具]的[資料驗證]

將[儲存格內允許]改成[清單]，並在[來源]中輸入[=季別]後，按下[確定]

此時，第一層選單就設定完成，如下圖

5.     設定第二層選單

在選取 B2:B10 的範圍後，點選[功能區]下[資料工具]的[資料驗證]，

在[來源]中輸入[=INDIRECT(A2)]後，按下[確定]

(此處雖然只有寫到儲存格 A2，實際上 Excel 會將往下各列，自動調整為儲存格 A3、A4…)

(注意！選取範圍時，必需要由B2選到B10才可以有上述自動調整的方式，若是選取範圍時，是由 B10 選到 B2 時，則會導致設定出錯。)

此時就完成第二層選單的設定

  

完成上面所有步驟後，二層式的下拉選單就完成了。

附記：如果選項清單是會擴充的，配合動態[名稱]會更好用。

依這個方式，也可以做三層或更多層的下拉式選單。