2012年3月7日 星期三

Excel-依據勾選自動列出項目

在 Excel 中有一個資料表(如下圖左),如果要根據勾選的結果,自動依序列出有勾選的人員名冊,並列出編號、姓名、得分(參考下圖右),該如何處理?

【準備工作】

選取儲存格A1:C28,按一下 Ctrl+Shift+F3 鍵,定義名稱:勾選、姓名、得分。

 

【輸入公式】

(1) 編號

儲存格E2:=IF(ROW(1:1)>COUNTIF(勾選,"V"),"",ROW(1:1))

(2) 姓名

儲存格F2:{=IFERROR(INDEX(姓名,SMALL(IF(勾選="V",ROW(勾選),FALSE),ROW(1:1))-1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

IF(勾選="V",ROW(勾選),FALSE):找出已被標示V之列號的陣列。

SMALL(IF(勾選="V",ROW(勾選),FALSE),ROW(1:1):找出第1個、第2個、…已標示V的列號。ROW(1:1)=1,往下複製後,會變成ROW(2:2)=2、ROW(3:3)、…。

接著利用 INDEX 函數,根據上式的結果(要減1),代入後以查表方式求得姓名。

由於向下複製後可能會發生查表的錯誤,所以使用 IFERROR 函數來將錯誤部分以空白字串顯示。

(3) 得分

儲存格G2:{=IFERROR(INDEX(得分,SMALL(IF(勾選="V",ROW(勾選),FALSE),ROW(1:1))-1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

參考(2)之說明,將公式中的「姓名」改為「得分」即可。

最後,複製儲存格E2:G2,往下各列貼上即可。

 

【補充說明】

相關函數說明,可以參考微軟網站:

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

沒有留言:

張貼留言

好康東東