學不完．教不停．用不盡

網友問到：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 個數

在 Excel 中的一個資料表，其中儲存格可能在最左或是最右側有連續的0(參考下圖)，如何得知這些連續的 0 分別有幾個？

【公式設計與解析】

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

在一個 Excel 中有一個資料表(如下圖左)，如果想要在另一個資料表依編號重組資料，而且依原順序呈現，但是因為編號會重覆，所以要如何能依原順序列出資料呢？(參考下圖右)

例如資料清單中編號1者(位於儲存格A2,A3,A7,A10,A13,...)，當在重組資料時是置於儲存格G2,G5,G9,G12,G15,...。如下圖中的箭號指示，呈現時必須依原來的順序出現，該如何處理？

【公式設計與解析】

為說明方便，先選取儲存格A1:A25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：編號。

學校教師在處理行政和備課時，常會用到簡報製作，因應不同班級或是公務需要，如果要在列印簡報紙本時調整其排列順序，除了重排投影片之外，還有其他方法處理？在 PowerPoint 中設計好簡報檔，當你要列印投影片時，如果想要依照某種特定的順序列印時，該如何處理？

以 PowerPoint 預設的列印模式來說明，其分為水平和垂直方向來排列投影片(投影片中的紅色數字為投影片順序)：

如果你想自訂順序，該如何處理呢？其實很簡單，例如在「投影片數」方塊中，輸入以下的順序：9-7,4,1-3,5-6，列印時會依 9, 8, 7, 4, 1, 2, 3, 5, 6 的順序來列印。其中『-』用以表示一段範圍，範圍中的數字可以由小至大，也可以由大至小。『,』用以區隔一段一段的範圍。

日後，你可以在不變動原始投影片的情況下，修改列印時的投影片順序了。

在 Excel 中要處理資料時常會用到篩選和進階篩選工具，可以讓你不用設計公式，即可獲想要的結果。本篇要反過來操作，來練習如何利用公式達到進階篩選的結果。我們根據一個資料表透過進階篩選工具，並使用 AND 或是 OR 的條件來篩選資料。

建議先參考前二篇文章再往下閱讀：

Excel-計算數列中合於多條件的個數(AND和OR運算,SUMPRODUCT)

Excel-挑出一欄中非空白的儲存格內容(SUMPRODUCT,OFFSET,非陣列公式)

1. 找出『國文>=60』且『數學>=60』的資料

在 Excel 中的 SUMPRODUCT 函數是個十分好用的工具，如果能配合邏輯 AND 和 OR 的關念來設計公式，可以將單純的乘積和運算達到多條件的邏輯運算。

參考下圖，有A組和B組二組數列，以下用 6 個不同的運算來介紹 SUMPRODUCT 函數的應用。(關於 SUMPRODUCT 函數的介紹，請自行參考部落格中其他文章。)

【公式設計與解析】

為了解說方便，先選取儲存格B1:C21，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：A組、B組。

在先前的文章中：Excel-挑出一欄中非空白的儲存格內容(陣列公式,OFFSET)，為了挑出非空白的儲存格，使用陣列公式來處理。有網友很熱血的想要使用非陣列公式的方式來處理，今天我也花了一些時間來想想，網友們再看看是否有更恰當的做法。

如下圖，要根據『項目』這一欄中非空白的儲存格，將數值和項目集合至另一欄，該如何處理？

【公式設計與解析】

(1) ($B$2:$B$24<>"")*ROW($B$2:$B$24))

在 Excel 中『資料驗證』是一個好用的工具，讓你在輸入資料時，可以設計一些防錯的機制，增加資料輸入的準確性。

以下例舉10個不同的『資料驗證』應用，大部分都有結合公式運算和一些判斷式。

要啟動『資料驗證』，必須先選取儲存格，然後在[資料／資料工具]功能表中，選取「資料驗證」。

在 Excel 中輸入一個數值，如何直接判斷是否為質數？如下圖，產生一堆的亂數，立即判斷該數是否為一個質數。

【公式設計與解析】

儲存格B2：
=IF(SUMPRODUCT(--(MOD(A2,ROW(INDIRECT("2:"&(A2-1))))=0))=0,"質數","")

在 Excel 中有一個資料表，其中下圖左的原始字串，想要從中取出【】中間的字元，該如何處理？

觀察下圖，其中【】的前、中、後的字元數量，每個儲存格都不相同。

【公式設計與解析】

網友想要根據 Excel 中的一個物品規格與售價的資料清單中，在指定規格後查詢到對應的售價，該如何處理？

參考下圖，這個物品規格的資料清單中，包含了欄位：Model、CPU、Memory、Storage、GPU、Price等。

【公式設計與解析】

首先，處理規格查詢的部分，想要建立可以使用下拉式清單來選取規格，以免 Keyin 規格造成的問題。

網友想要由一個 Excel 資料表(下圖左)中查詢資料，但顯示時欄位內容時，想要由橫式轉為直式顯示(如下圖右)，該如何處理？

【公式設計與解析】

儲存格S5：=OFFSET($C$4,SUMPRODUCT(($A$4:$A$111=$S$3)*
($B$4:$B$111=$S$4)*ROW($A$4:$A$111))-4,ROW(1:1)-1,,)

延續前一篇文章：

Excel-在月曆型式中顯示排班結果(SUMPRODUCT,OFFSET,DATE,ROW)

如果想要給每一位員工一張個人的值班表，該如何處理？

【公式設計與解析】

有網友想要將一個 Excel 的排班清單(下圖右)直接在一個月曆中顯示(下圖右)，該如何處理？

【公式設計與解析】

先將日期範圍內的儲存格定義名稱為：日期。

當在 Excel 中取得一個運動會的報名表(如下圖)，除了各班的所有學生基本資料之後，還有各個比賽項目，其中的值為 TRUE 者代表有報名，FALSE 代表沒有報名。

如何能快速計算各個比賽項目的各班男生／女生的參數人數？

1. 使用樞紐分析表和交叉分析篩選器