學不完．教不停．用不盡

在 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. 使用樞紐分析表和交叉分析篩選器

在 Excel 中有一個日期是取貨的期限，網友想要在期限前三天內予以警示，但是如果在D欄中已有『提件日期』，則取消警示，該如何處理？

參考下圖，例如今天的日期是2015/10/29，則項目:007~012都要警示，但是009和011因為已有『提件日期』，則取消警示。大部分的人做法都是使用「設定格式化的條件」來處理。

【參考設計做法】

1. 選取儲存格C1:C18。

延續上篇文章：Excel-在統計圖中更改時間標籤的間距，參考下圖，如果想要將多個工作表中的資料共用一個圖表(折線圖)，該如何處理？

在下圖中，原來的表1～表7是放在多個工作表中，現在把它集合在一起，想要透過儲存格I2中的下拉式選單來選取表的名稱，而折線圖自動會顯示該表的數值內容。

【操作與解析】

網友想要依據進場和出場的停車時間來計算停車費，規則如下(參考下圖)：

(1) 停車未滿30分鐘收費0元

(2) 超過30分鐘未滿60分鐘收費30元

(3) 三小時以內每30分鐘收費15元

(4) 第四小時開始每30分鐘收費20元

有網友問到：在 Excel 中根據下圖中的資料表繪出一個統計圖，觀察座標軸上的刻度，是以每5分鐘為單位標記。如何讓座標軸上是以每15分鐘為單位標記？

【參考做法】

在類別座標軸上按右鍵，選取[座標軸格式]選項。

網友問到一個問題：如何在 Excel 檔案含有人員和郵件地址的資料表中查到的結果(如下圖)，可以直接點選打開 Outlook 新增郵件？

【公式設計與解析】

通常你在 Excel 的儲存格中輸入一個網址或是Email郵件地址時，當按下 Enter 鍵，即會產生這個超連結。

網友想要取用 Excel 中的一個含有樓層、房號、入住時間、退房時間的住宿資料表，依據不同樓層，將有住宿的時間內的日期，在該房號中自動標示出來，該如何處理？

就像下圖這樣，當住宿的總表填入資料後，該樓層、房號對應的日期會被標示出來。

參考以下的做法：

1. 定義儲存格名稱