學不完．教不停．用不盡

在 Excel 的工作表中，要由一堆資料裡查詢某資料所在的位置(指出第幾欄和第幾列)(參考下圖)，該如何處理？這次練習以陣列公式來處理。

首先，將儲存格A1:G20定義名稱為「資料」。

儲存格J2：{=MAX((資料=J1)*COLUMN(資料))}

儲存格J3：{=MAX((資料=J1)*ROW(資料))}

此為陣列公式，輸入完成請按 Ctrl+Shift+Enter 鍵。

以查詢「欄」為例：

有同事問到，如果要為一個資料表(參考下圖，以10筆資料為例)中的每一列插入一個空白列，該如何處理呢？其實方法很多，例如以下的方法，只能算方便，不見得是個完美的方法。

1. 在A欄處插入一欄。

2. 在儲存格A2:A11產生數列(1~10)。

3. 在儲存格A12輸入公式：=A2+ROW(1:1)/100，

4. 複製儲存格A12，貼至儲存格A12:21，可以產生如下A欄的數列。

在 Excel 的運算式中是可以使用條件運算的，當條件運算遇到文字、數字、空白時會如何處理呢？加上括號時會如何處理呢？(以條件「=」為例)

以下圖為例：儲存格A2:A5都是相同內容「C」，試試以下幾個邏輯運算式：

在 Excel 中取得一個球類的借用狀況報表(如下圖左)，試著來統計分析借用狀況(如下圖右)。

先選儲存格A1:D25，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義「日期、班級、球類、數量」等名稱。

儲存格G2：=SUMPRODUCT(--(球類=G1),數量)

其中「--」是為將 True/False 陣列轉換為 1/0 陣列。

複製儲存格G2到儲存格G2:I2。

儲存格G5：=SUMPRODUCT(--(班級=$F5),--(球類=G$4),數量)

在 Excel 中有一個三組(A,B,C)的數字，現在要來找出互相比較大小的個數，只要使用SUMPRODUCT函數即可達到。

為了簡化公式，所以先定義名稱：選取儲存格A1:C21，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項，定義A組、

(1) 計算 A>B 的個數

儲存格J2：=SUMPRODUCT(--(A組>B組))

其中「--」可以將 True/False 的陣列轉換為 1/0 的陣列。

(2) 計算 A>C 的個數

一般在學校中兼行政的老師可以比照公務人員，每年可以有休假日數，如下所示：

服務滿一年(第二年起)：7天
服務滿三年(第四年起)：14天
服務滿六年(第七年起)：21天
服務滿九年(第十年起)：28天
服務滿十四年(第十五年起)：30天

因為英文字和數字在顯示時可以設定為全型字或半型字，當你在 Excel 中取得文件要資料處理，試著練習將全型文數字轉為半型字，或是將半型文數字轉為全型字。

儲存格B2：=ASC(A2)

複製儲存格B2，往下貼上。複製B欄，貼上時選取「值」，可以將公式消除。

儲存格C2：=BIG5(B2)

複製儲存格C2，往下貼上。複製C欄，貼上時選取「值」，可以將公式消除。

在 Excel 的工作表中，可能為了輸入方便或是由其他資料來源取得，日期輸入為：20110101格式，來表示2011年1月1日；時間輸入為：0102格示，來表示1時2分。如何轉換為 Excel 可以運算的日期格式或是正確的表示方式呢？(參考下圖)

因為「20110101」和「0102」被 Excel 認為只是一個數字或是一串文字，所以要以TEXT函數來轉換：

儲存格B2：=TEXT(A2,"0000-00-00")

將原始日期轉換為「0000-00-00」格式，此轉換結果視日期為文字字串。

如果要拿日期來運算，則應修正為：

有網友閱讀另一篇文章：

Excel-關於數列的排名(http://isvincent.blogspot.com/2011/06/excel_5404.html)

問到：如果根據下圖的左表要查出前10名(參考下圖右表)，該如何做呢？

當然你可以透過排序等操作，可以得到結果，但是網友可能想要以公式自動產生。

首先，定義名稱：

選取儲存格A1:D25，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」選項，設定：編號、姓名、分數、名次等名稱。再次選取儲存格A1:D25，將其名稱定義為：資料。

在執行 Word 的尋找與取代功能時，可以使用萬用字元「*、?」，來擴大找尋範圍。其中「*」可以代表0到多個字元，而「?」代表1個字元。

你必須在[尋找與取代]對話框中：

1. 按一下[較多]按鈕。

2. 勾選[使用萬用字元]選項。

3. 在[尋找目標]文字方塊中輸入含有萬用字元的搜尋文字。

4. 按一下[尋找下一筆]按鈕。

最近同事問到，如果要將文件中的某些文件設定同一格式，那種方式較方便？

有的人會先在指定文字上設定好格式，再以複製格式的方式套用到每個相同文字上，但這樣的手動處理，總是速度較慢、較費工，且容易出錯。

建議如下的做法：

1. 按一下 Ctrl+H 鍵，開啟[尋找與取代]對話框。

2. 在[尋找目標]文字方塊中輸入想要被設定格式的文字，本例為：「中華電信」。

3. 將滑鼠游標移至[取代為]文字方塊中按一下。(不需輸入任何文字)

在 Excel 中，資料是放在一般的儲存格範圍中，你可以將這些儲存格範圍，轉換成表格，可以方便進行樣式設定及計算。

1. 選取表格中的一個儲存格。

2. 選取[插入/表格]按鈕，或是按一下 Ctrl+T 鍵。

3. 確認表格資料來源。

此時，該表格即多了「設計」功能表標籤，並且可以選取顯示/隱藏：標題列、首欄、合計列、末欄、帶狀列、帶狀欄等。也有多種表樣式可以選取。

當你在 Excel 2010 中開啟舊版 Excel 文件時，會發生無法使用新版 Excel 功能的狀況，例如：

2003格式：

2010格式：

你可以透過選取[檔案/資訊]，再按一下[轉換]按鈕。

延續前三篇文章：

http://isvincent.blogspot.com/2011/07/excel-countif.html

http://isvincent.blogspot.com/2011/07/excel-indirect.html

http://isvincent.blogspot.com/2011/07/excel.html

這次，要根據研習者的基本資料，進行各種資料的統計。(參考下圖)

先選取儲存格J1:N30，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」。定義了：姓名、單位、職務、性別、時數等名稱，並將儲存格J1:N30命名為：資料。

延續前二篇文章：

http://isvincent.blogspot.com/2011/07/excel-countif.html

http://isvincent.blogspot.com/2011/07/excel-indirect.html

這次要練習以下拉式清單選取人名，並在報名資料表中的各時段以紅色顯示該姓名(參考下圖)。

1. 選取儲存格L1:L30，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」。定義名稱：姓名。

上一篇文章：http://isvincent.blogspot.com/2011/07/excel-countif.html將研習人員的名冊做了轉換，這次要以清單方式選取不同時段，進而顯示該時段的人員參與標記。(如下下圖)

首先，定義一些名稱：

1. 選取儲存格A1:H21。

2. 按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」。定義名稱：時段1、時段2、…、時段8。

在 Excel 中取得一個研習人員報表(如下圖)，依照這個報表要產生每個人在每個時段的參與勾選記號，要如何處理？(參考下下圖)

要將上圖轉換至下圖，要透過COUNTIF函數。

儲存格K2：=IF(COUNTIF(A$2:A$21,$J2)=1,"V","")

(其公式結果和「=IF(COUNTIF(A$2:A$21,$J2),"V",""))」一樣，因為條件成立時會傳回TRUE，轉成數值為1)

複製儲存格K2，貼至儲存格K2:R2。

在 Excel 中取得幾個名字的字串(如下圖儲存格B1:B8)，每個名字以「、」隔開，要如何取出姓名，並置於儲存格中呢？(參考下圖)

因為其中每個姓名都是三個字，所以根據這個規則撰寫公式：

儲存格B11：=MID(INDIRECT(ADDRESS(COLUMN(A:A),2)),(ROW(1:1)-1)*4+1,3)

COLUMN(A:A)往右複製，會產生COLUMN(A:A)=1→COLUMN(B:B)=2→ … →COLUMN(I:I)=8。

ROW(1:1)往下複製，會產生ROW(1:1)=1→ROW(2:2)=2→ … →ROW(20:201)。

ROW(1:1)-1)*4+1往下複製，可得1→5→9→ …。

在 Excel 中如果要計算以下各月的業績中超過40000的次數，只要使用COUNTIF函數即可輕易達到。

儲存格L2：=COUNTIF(F2:K2,$L$1)

複製儲存格L2，往下各列貼即可。

但是，如果1月到6月是分別置於命名為1月、2月、…、6月的工作表中，當使用COUNTIF函數會發生錯誤：

儲存格B2：=COUNTIF('1月:6月'!B2,$B$1)

在 Excel 中可以很方便的由一個數列中找出某數的排名，如何來找出某個排名的內容呢？

首先，將A欄和B欄有資料的儲存格分別命名為「編號」和「數列」。

(1) 找出某數在數列的排名

儲存格C2：=RANK(B2,數列)　

複製儲存格C2，往下貼在儲存格C2:C21。

或是