學不完．教不停．用不盡

最近又要為校內同仁上 Excel 的研習課程，做一些講義方便同仁課後參閱。本篇是關於 SUMPRODUCT 函數的說明。

下圖是微軟提供的 SUMPRODUCT 函數說明，主要是執行陣列元素的『乘積和』。

下圖是一個實例，要由『姓名、性別、成績』三個欄位中，求取不同性別的人數，和不同性別的及格和不及格人數。(參考下圖)

為了解說方便，選取儲存格B1:C16，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：性別、成績。

有同仁問到：

在 Wrod 中處理文件時，如下圖的內容中，想要在已設定項目符號的段落中，為了要使某些文字對齊，所以先設定了定位點符號(參考下圖)，當插入點移至下圖的第三列的第１個字，然後按一下 Tab 鍵，卻沒有產生內縮的結果，反而是執行預設之「增加縮排」的動作。

該如何解決呢？(要把第三列的起始位置移至冒號(：)後)

答案很簡單：

改按 Ctrl+Tab 鍵即可，參考下圖(不解釋)。

在 Excel 中使用 VLOOKUP 函數來檢索資料是常手的工具，在下圖中可以發現，要使用「學號」來查詢各欄的資料，公式如下：

儲存格I2：=VLOOKUP($I$1,$A$2:$F$24,ROW(2:2),FALSE)

而 VLOOKUP 函數必須要將查詢的值在資料第1欄中查詢。

複製儲存格I2，貼至儲存格I2:I6。

但是，如果你的資料表如果是像下圖這樣，用以檢索的欄位(學號)並非是資料的第1欄，所以無法使用 VLOOKUP 函數。該如何來正確查詢？

在 Excel 中如果要將一個資料數列四捨五入至千位數或萬位數，該如何處理？

(1) 四捨五入

如下圖，要根據原始資料，轉換為四捨五入至百位、千位、萬位和10萬。通常要四捨五入，你會使用 ROUND 函數，例如：ROUND(A1,3)，表示要將儲存格A1的內容，取四捨五入至小數第3位。

一樣也是使用 ROUND 函數，如果要轉換為四捨五入至百位、千位、萬位和10萬等，只要將參數改為『負數』即可。

四捨五入至百位數，儲存格C3：=ROUND(A3,-2)

網友問到：Excel 的資料表有個數列資料，網友想要求其中的項目有多少種，把相同數字算一種，要求有多少不同的數字？(參考下圖)

【公式設計與解析】

(上圖)儲存格C2：=SUMPRODUCT(1/COUNTIF(A2:A24,A2:A24))

其公式原理是先求出每個數字在數列中出現的次數，然後將每個次數求倒數，再將倒數加總，該數即為項目的個數。(每種項目加總後為1)

在 Excel 的工作表中常會用到要重組資料，例如下圖中，每天會輸入幾筆資料，隨著日期不斷的增加，如何能將每天不同欄位的資料重組在一欄中？

下圖中，每天都要輸入甲、乙、丙、丁、戊、己、庚等項目的資料，要將這些資料重組在一個欄位中。

【公式設計與解析】

儲存格J2：=OFFSET($B$2,MOD(ROW(1:1)-1,COUNT(A:A)),COLUMN(A:A)-1+

在 Excel 中繪製圖表時，有時會遇到某些儲存格沒有輸入任何的資料，也就是空白儲存格，但不包含是空字串的儲存格(例如：="")，在圖表中會產生數列資料的折線圖有中斷的現象，該如何來改善？

如下圖，因為幾個空白的儲存格，而導至圖表中的線條產生不連續的狀況。

你可以試著這樣做：

選取折線圖，再按右鍵，選取「選取資料」：

在 Excel 中儲存格可能會放有各種，例如：數字、文字、邏輯值、錯誤訊息等，如何能統計在儲存格中這些資料類型的數量？(參考下圖)

【公式設計與解析】

(1) 計算空白儲存格數量

儲存格F2：=SUMPRODUCT(ISBLANK(A2:C24)*1)

有老師在製作 Excel 的講義時，會需要用到顯示某些儲存格中的公式內容，以下使用兩種方式來呈現，希望對老師們的備課有幫助。(參考下圖)

(1) 使用 FORMULATEXT 函數

在儲存格D2中輸入公式：=FORMULATEXT(C2)

在儲存格D2中會顯示：=SUMPRODUCT(1*(A2:A14>=500))

網友對前一篇文章產生了興趣：Excel-計算儲存格內左、右方連續0的個數(SUMPRODUCT,LEFT,RIGHT,ROW)，想要了解如何計算儲存格內左方、右方連續n個相同數字個數的做法。

以下圖為例，來計算左方連續個1的個數，和右方連續個1的個數。

【公式設計與解析】

1. 計算儲存格左側連續的 1 個數