學不完．教不停．用不盡

在 Excel 中的一份資料表，如何選取男或女性別後，能以不同色彩格式區隔資料中的男生和女生？

首先透過「資料驗證」方式，將儲存格H2中加入「男,女」。

接著選取所有的資料內容(本例為儲存格A2:F214)，然後在設定格式化的條件中，輸入二個設定值：

在 Excel 的工作表中取得一個跑步記錄表(如下圖左)，利用B欄的資料要來做統計分析(如下圖右)。B欄中的資料格式固定，透過陣列的應用，只要分別以一行公式即可算出各個統計結果。

假設B欄的資料在儲存格B2:B50中，輸入以下的公式：

(以下公式全為陣列公式，輸入完成要按 Ctrl+Shift+Enter。)

(1) 最多圈

儲存格D3：{=MAX(VALUE(MID(B2:B50,3,2)))}

根據前二篇查表方式的練習：

http://isvincent.blogspot.com/2011/04/excel-indexvlookuphlookup.html

http://isvincent.blogspot.com/2011/04/excel-indexmatch.html

這次要使用OFFSET+MATCH來執行查表。

儲存格G14：=OFFSET(A1,MATCH(F14,A2:A11),MATCH(E14,B1:K1),,)

在 Excel 的工作表中，如果想要輸入一個二進制數、八進制數或十六進制數，然後要產生遞增1的數列(參考下)該如何處理呢？

必須要使用進制轉換的函數：DEC2BIN、BIN2DEC、DEC2OCT、OCT2DEC、DEC2HEX、HEX2DEC。

儲存格B3：=DEC2BIN(BIN2DEC($B$2)+ROW(1:1))

因為要進行加1的運算所以得先把二進制轉成十進制，加1後，再轉回二進制顯示。

八進制和十六進制的加1運算也一樣：

儲存格C3：=DEC2OCT(OCT2DEC($C$2)+ROW(1:1))

前一篇文章：Excel-Index+Match練習(http://isvincent.blogspot.com/2011/04/excel-indexmatch.html)，提到了查表的功能。這次來練習使用VLOOKUP和HLOOKUP函數來融入查表。

以下三種公式可以得到相同查表結果：

(1) 儲存格G14：=INDEX(A1:K11,MATCH(F14,A1:A11,0),MATCH(E14,A1:K1,0))

公式為：INDEX(表格，找尋F14在A1:A11中的第幾個，找尋E14在A1:K1中的第幾個)

此方法以欄、列交叉的位置取得查表結果。

當你在 Word 中選取[檔案/資訊]時，在視窗最右側會顯示該檔案的摘要資訊。按一下最下方的「顯示所有屬性」，可以顯示完整的內容。

在這個區塊中，你可以自行輸入內容。(記得要儲存，這些資料才會寫入。)

如果你選取[內容]功能表中的「顯示文件面板」選項，則會在文件上方顯示檔案資料。

這次要來練習利用 Index 和 Match 函數來查表的功能。

(1) 建立基本表

儲存格A2：=CHAR(ROW(1:1)+64)，複製儲存格A2至儲存格A2:A11。

利用COLUMN(1:1)+64來產生65,66,67 …，再代入CHAR( )產生A,B,C …。

儲存格B1：=CHAR(COLUMN(A:A)+80)，複製儲存格B1至儲存格B1:K1。

利用COLUMN(A:A)+80來產生81,82,83…，再代入CHAR( )產生Q,R,S …。

根據上一篇文章：Excel-輸入答案後自動畫出答案卡 

http://isvincent.blogspot.com/2011/04/excel_09.html

已經學會將單選題轉換為答案卡的劃記，現在來試試如果是複選題該如何處理？

根據以下的答案資料：

試著自動轉換成答案卡劃記：

儲存格C53：=IF(FIND(CHAR(64+COLUMN(A:A)),OFFSET(Anss!$C$3,(ROW(1:1)-1)/2,,,))>0,1,0)

現在在學校中，利用電腦閱卷答案卡(如下圖範例)作答是很常見的事，如果要畫一張100個答案的答案卡，可能對已經非常忙碌的老師而言，也是一個「小小」的負擔。

如果能在 Excel 的工作表中輸入好答案(參考下圖)，自動幫你轉成答案卡(參考下下圖)，而且印在一般A4的白紙上即能使用，是不是很方便呢？！

其實答卡紙上的紅色部分是給人看的，讀卡機並不會讀取到這些資料，只有黑色部分才是機器要的資料。所以可以把答案卡分為二個部分來設計，左邊的黑色線條是用來做為機器校正之用，所以你只要稍加調整製作出接近原來答案卡差不多的線條即可。

至於右邊答案畫記的部分，則要靠公式和設定格式化條件來自動產生，參考以下的做法：

在 Excel 中取得一個學生考試的讀卡檔案(如下圖)，利用這些資料要來計算各班(201~214)的平均成績，和每個題目學生的作答鑑別情形，該如何設計呢？

(1) 計算各班平均分數

儲存格I2：{=AVERAGE(IF($B$2:$B$504=VALUE(RIGHT(H2,2)),$E$2:$E$504,""))}

陣列公式，輸入完成成後要按 Ctrl + Alt + Enter 鍵。複儲儲存格I2到儲存格I2:I15。

公式中的 VALUE(RIGHT(H2,2)) 為取得H欄的班級代碼之後2碼。