學不完．教不停．用不盡

在Excel中的一個資料表，試著來練習將其摘要出相關統計報表。(如下圖)

(1) 學歷分析

使用SMUPRODUCT函數來計算人數(參考微軟網站：SUMPRODUCT 函數說明)

儲存格H4：=SUMPRODUCT(($E$2:$E$25=$G4)*($D$2:$D$25=$H$2)*($B$2:$B$25=H$3))

複製儲存格H4到儲存格H4:I6。

在Excel中建立一個成績表，如果只要顯示前三名，並且以不同色彩標示，該如何處理呢？

儲存格F2：=IF(E2=LARGE($E$2:$E$26,1),1,IF(E2=LARGE($E$2:$E$26,2),2,IF(E2=LARGE($E$2:$E$26,3),3,"")))

或是

儲存格F2：{=IF(RANK(E2,$E$2:$E$26)<=3,RANK(E2,$E$2:$E$26),"")}

這是陣列公式，輸入請按Ctrl+Shift+Enter。

資料來源：http://office.microsoft.com

摘錄微軟網站提供的 Excel 2010 函數說明，超連結會連到 Microsoft 網站。 如果需要用到時，可以方便查詢。

在Excel中建立一組隨機亂數資料，如何自動配置到另一個資料表中呢？例如依每間隔20為單位來配置。

儲存格G2：{=SMALL(IF($A$2:$E$11>=--LEFT(G$1,FIND("-",G$1,1)-1),IF($A$2:$E$11<=--RIGHT(G$1,LEN(G$1)-FIND("-",G$1,1)),$A$2:$E$11)),ROW(1:1))}

此公式為陣列公式，輸入時要按Ctrl+Shift+Enter。

將儲存格G2複製到儲存格G2:K20。

其中：

在Excel中輸入一個資料表，如下圖左半部，班級是不規則順序輸入的。如果要自動轉換成右半部，變成依班級順序排列，而且只有班級名稱的第一筆才顯示，其餘班級名稱不顯示。

1.

儲存格G2：=SMALL($B$2:$B$24,ROW(1:1))

觀察因為班級全部由數字組成，所以利用SMALL函數，將其由小到大取出。

2.

在Excel中，如果要將一組依序排好的資料(例如：依序的班級編號)隨機重新排序，該如何處理呢？

因為要隨機排序，所以需要產生一組亂數：

儲存格A2：=INT(RAND()*100)+ROW()/1000

複製儲存格A2到儲存格A2:A17。

公式：INT(RAND()*100)產生1~99的整數亂數。

在Excel中如果要輸入一個報名表，希望同一欄中的資料是唯一值(不可重覆)，該如何處理？

例如在運動會一百公尺報名表(每班一名)，希望班級欄位不要重覆，可以藉助資料驗證來把關。

儲存格A3：=IF(B4<>"",A3+1,"")

將儲存格A3往下複製到多個儲存格。

該公式的作用是，如果B欄有輸入資料時，序號會自動加1。

我國的身分證號碼是基於某一種規則之下產生，如何使用Excel來驗證身份證號碼是否為有效的號碼呢？

身分證號碼的第一碼是英文字母，代表的出生後入籍的縣市的代號，如下表所示：

而第2到第10個碼為阿拉伯數字，其中第2碼代表性別：1為男性、2為女性，第3碼至第9碼為流水號。最後一碼為檢查碼，而它的檢查方法透過以下的公式換算而來：

(1) 驗算是否正確(正確：V，不正確：X)

在Excel中，如果要建立一個請假時數的資料表，如何來輸入最少資料而且避免輸入錯誤呢？練習以下的做法：

如果你在日期的儲存格欄位中使用資料驗證(如下圖)，設定日期要小於「=TODAY()」，則可以避免輸入未來日期，但是必須準確的輸入日期格式。

如果你的工作是週期性的，例如每七天輸入一次，則可以利用一個工作表建立以下的資料：

儲存格A7：=TODAY()

在Excel的工作表中使用公式，難免會有錯誤運算訊息，所以提供了許多對公式除錯的工具。

在 E2 儲存格中出現了 #DIV/0! 的錯誤，只要按一下 Ctrl + ] ，就會顯示該儲存格中的公式引用了那些儲存格，並選取這些儲存格。而如果你選取一個儲存格，按一下 Ctrl + ［ ，會顯示那些儲存格中的公式引用了這個儲存格，並選取那些儲存格。

如果你想知道公式中來源的儲存格，可以使用[追蹤前導參照]按鈕，即可以標示該儲存格公式中引用了那些儲存格。如果你想知道某個儲存格被其他儲存格引用，則按一下[追蹤從屬參照]按鈕。只要按一下[移除箭號]按鈕，即可以隱藏這些藍色的指標。

選取錯誤的儲存格，按一下[評估值公式]按鈕，可以逐步的檢驗問題在那裡。

在Excel中的資料編輯列中，當你正在輸入函數名稱時，可以即時看到該函數的說明(如下圖)，這個說明區是可以自行拖曳至其他位置的。如果尚未輸入完函數名稱，而在其列出的函數名稱上按二下滑鼠左鍵，Excel會代為輸入這個函數名稱。

當你輸入函數名稱為，再輸入第一個右括號時，則會顯示完整的引數提示。其中以 「[ ]」表示者為選用引數，即可以視需要輸入。

如果點選函數名稱(顯示藍色的超連結)，則會顯示該函數的相關說明。

面對一個已輸入完成的函數，在其引數上(藍色超連結)按一下，則資料編輯列上會將該函數的引數內容，以反白顯示。

在Excel的圖表中，如果只想凸顯資料中的最大值和最小值(如下圖)，該如何處理呢？

必須藉助一列輔助列，輸入以下的公式：

儲存格A3：=IF(OR(A2=MIN($A$2:$J$2),A2=MAX($A$2:$J$2)),A2,NA())

複製儲存格A3到儲存格A3:J3。

只有最大值和最小值會被標示出來，其他以#N/A錯誤訊息表示。

在Excel中的表格，如果儲存格中輸入了含文字及數字的字串，想要將其分離來應用，該如何處理呢？

假設資料輸入在A欄，資料的最大長度為20個字。

(1) 前半部

儲存格B2：{=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),
ROW(INDIRECT("1:20")),99))-1)}

在Excel中，把成績輸入至一個成績記錄表，同時在對應的圖表顯示儲存格中的內容，該如何處理呢？

做法非常簡單，參考以下做法：

1. 先將圖表建構好。

2. 點選一個圖形。

3. 在資料編輯列上輸入「=」，然後點選想要顯示的儲存格。

只要在儲存格輸入的任何資料，都會完全的顯示在圖片中。(注意：公式中的內容，必須為個有效位址參照或是已定義的名稱。)

前一篇文章提到：使用多層下拉式清單結構輸入資料(基礎)

參考網址：http://isvincent.blogspot.com/2010/06/excel_25.html

這次要來練習：不需要每個年級都定義一個名稱，而是要以整個基本資料表為單位來查詢。

各年級資料置於list工作表

1. 將A欄的儲存格設定資料驗證的準則為：

(1) 儲存格內允許：清單

在Excel中輸入資料，如果完全以Key In方式輸入，除了耗費較多時間之外，還可能打錯字。所以如果能以選取方式來輸入資料，應該可以改善這些問題。解決這個問題，利用資料驗證、INDIRECT函數和定義名稱，可以很簡單的解決。

假設要有三個年級，每個年級的班級名稱都不相同，規則上也有所差異。(參考下圖)

假設各年級資料置於list工作表中，作法如下：

1. 定義名稱

(1) 一年級：=list!$A$2:$A$15

在 Excel 中也可以做到選取儲存格內容後，即對應顯示一個圖片。(以Excel 2010為例)

以下就以製作「各地區的天氣預報的報表」來練習，Show工作表為顯示結果，List工作表為基本資料。先將網路上找到天氣相關的圖片，複製到List工作表中，並調整為相同大小，置放於Show工作表的B欄中。

Show工作表

List工作表

首先，為了達到各地區天氣狀況以選取方式產生(免於輸入)(如下圖)，所以要藉助資料驗證的功能。

這次來玩玩在Excel中產生10組偶數和10組奇數，比較其大小後，以設定格式化條件表示結果(如下圖)。如果偶數>奇數，則以紅色圓球表示；如果奇數>偶數，則以黑色圓球表示。

(1) 產生0~1000的偶數

儲存格B2：=INT(RAND()*500)*2

複製儲存格B2到儲存格B2:K2。

(2) 產生0~1000的偶數

在 Excel 中，SUMPRODUCT函數對於製作數字統計相關的摘要表非常容易，再來練習以下的報表製作：

(1) 在B欄要計算每個人的請假次別(不管何種假別)：

儲存格B1：輸入「第1次」

儲存格B2：="第"&COUNTIF(A$2:A3,A3)&"次"

複製儲存格B2到儲存格B2:B21。

在 Excel 的一個大工作表中，如果要選其中某些類型的儲存格，不管使用那一種人工點選的方式，多將是費時費力。你可以讓Excel為你代勞！

選取資料範圍，按一下 F5 鍵，顯示[到]對話框，按一下[特殊]按鈕，顯示[特殊目標]對話框。

例如：選取[空格]選項，則可以快速選取所有的空格，這比起一格一格的選取要方便多了！