前幾天看到Excel效率王分享的「邊選邊消失/下拉式選單」，突然來了一個靈感，覺得可以用Excel做一個電子試卷，類似像考卷那樣的形式，或者是電子問卷的模式。
KNB會分成三篇來說明，其中前兩篇是跟著Excel效率王的步驟，最後一篇則是KNB另外延伸出確認答案的設定。就讓我們繼續看下去吧。
第三篇：確認答案、錯誤變色
5) 函數>正確與否、解答回寫
5) 「正確與否」=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))
5) 「解答>>」=IF(D4="錯誤",VLOOKUP(B4,K4:L8,2,0),"-")
6) 格式化條件設定>表格變色
5) 「只格式化包含下列的儲存格 >>儲存格值_等於_=”錯誤” >>格式 >>確定 >>確定」
△ 情境：電子試卷用下拉選單，讓答案邊選邊消失。後續，可自動批改答案並帶出解答。
步驟一：新增「正確與否」「>>解答」欄位，以及「[答案]」。
🡺 [D欄] 新增「正確與否」欄位
🡺 [E欄] 新增「>>解答」欄位
🡺 [K、L欄] 新增「答案」中日文對照
步驟二：設定「正確與否」函數_vlookup + if
● 函數拆解說明：D4儲存格「=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))」
● vlookup函數：將對照資料回寫相對應的橫軸欄位。
🡺 [Lookup_value] 對照資料_儲存格內的內容「B4」
🡺 [Table_array] 回寫的答案範圍「$K$3:$L$8」；因為是絕對範圍要上鎖符號「$」
🡺 [Col_index_num] 回寫第幾格欄位_回傳欄位第二格「2」
🡺 [Range_lookup] 數字「0」表示完全符合才可回寫。也可填寫「False」
🡺 「確定」
註解：相關函數及細節可參考以下連結
1) vlookup函數：2019/03/23 Excel函數-001_vlookup-查閱與參照
2) 絕對範圍：2019/04/13 Excel技巧-002_欄與列上鎖
● 小主語IF函數說明：D4儲存格「=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))」
● if函數：如果符合A條件，就會得B，若不符合A條件，則是得C。
🡺 [Logical_test] 若符合A條件_若指定資料等於C4儲存格的內容「C4=VLOOKUP(B4,$K$3:$L$8,2,0)」；因為是絕對範圍要上鎖符號「$」
🡺 [Value_if_true] 就會得B_回寫正確「”正確”」
🡺 [Value_if_false] 若不符合A條件，則是得C_回寫錯誤「”錯誤”」
🡺 「確定」
註解：相關函數及細節可參考以下連結
1) if函數：2019/04/25 Excel函數-011_if-邏輯
2) 回寫「"正確"」「"錯誤"」：2019/04/23 Excel技巧-003_如何判斷資料是「值」還是「文字」？
● 大主語IF函數說明：D4儲存格「=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))」
● if函數：如果符合A條件，就會得B，若不符合A條件，則是得C。
🡺 [Logical_test] 若符合A條件_若C4儲存格的內容等於空白「C4=””」
🡺 [Value_if_true] 就會得B_回寫空白「””」
🡺 [Value_if_false] 若不符合A條件，則是得C_回寫裡面的小主語條件「IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤")」；因為是絕對範圍要上鎖符號「$」
🡺 「確定」
🡺 Ctrl+C(複製)D4儲存格 >>Shift+向下鍵(反白經過的儲存格)至D8 >>Enter(貼上)
註解：相關函數及細節可參考以下連結
1) if函數：2019/04/25 Excel函數-011_if-邏輯
2) 絕對範圍：2019/04/13 Excel技巧-002_欄與列上鎖
3) 回寫「"正確"」「"錯誤"」：2019/04/23 Excel技巧-003_如何判斷資料是「值」還是「文字」？
步驟三：設定「>>解答」函數_vlookup + if
● 函數說明：E4儲存格「=IF(D4="錯誤",VLOOKUP(B4,$K$4:$L$8,2,0),"-")」
● 大主語if函數：如果符合A條件，就會得B (vlookup函數)，若不符合A條件，則是得C。
🡺 [Logical_test] 若符合A條件_若D4儲存格的內容等於錯誤「D4="錯誤"」
🡺 [Value_if_true] 就會得B_回寫vlookup函數的對應欄位「VLOOKUP(B4,$K$4:$L$8,2,0)」；因為是絕對範圍要上鎖符號「$」
🡺 [Value_if_false] 若不符合A條件，則是得C_回寫「”-“」
🡺 「確定」
🡺 Ctrl+C(複製)E4儲存格 >>Shift+向下鍵(反白經過的儲存格)至E8 >>Enter(貼上)
註解：相關函數及細節可參考以下連結
1) if函數：2019/04/25 Excel函數-011_if-邏輯
2) vlookup函數：2019/03/23 Excel函數-001_vlookup-查閱與參照
3) 絕對範圍：2019/04/13 Excel技巧-002_欄與列上鎖
步驟四：格式化條件設定_表格變色
● 設定格式化的條件：建立規則使儲存格變色
🡺 選取儲存格D4 >>「常用」 >>「設定格式化的條件」 >>「管理規則」
🡺 「新增規則」
🡺 「只格式化包含下列的儲存格」 >>選取「等於」 >>輸入「錯誤」 >>「格式」
🡺 左圖：「字型」分頁 >>「色彩」選色
🡺 右圖：「填滿」分頁 >>選色 >>「確定」
🡺 「確定」
🡺 測試設定：將「日文諺語」答案選錯，確認是否會變色。
🡺 選取儲存格D4 >>「常用」 >>「複製格式」 >>選取儲存格D5:D8
設定完成：判斷序號 + 清單公式
● 測試設定：將「日文諺語」答案選錯，確認各個設定是否正常。
🡺 紅框：錯誤變色 (函數組合IF + Vlookup + 設定格式化條件設定)
🡺 藍框：確認答案 (函數組合IF + Vlookup)
KNB碎碎唸：
打了三篇連載，不曉得各位看不看得懂呢？
KNB盡量用直覺式的方式做筆敘，如果有在哪個環節，有不清楚的地方都可以發問喔。
其實工作這麼多年，雖然只有快十年XD 講得好像很久一樣。
Excel建置之類的設定，小本認為也算是工程性質的工作，雖然說Excel好像每個人都會用，但是要使用到能得心應手，建置成自己想要方便的系統也不是件容易的事情阿。小本到現在也都還是學習中
引用資料：
01# Excel效率王「邊選邊消失/下拉式選單」
函數說明：
1. 2019/03/23 Excel函數-001_vlookup-查閱與參照
2. 2019/04/25 Excel函數-011_if-邏輯
3. 2019/04/13 Excel技巧-002_欄與列上鎖
延伸閱讀：
1. 2020/01/19 Excel活用-011_第一篇：製作電子試卷
2. 2020/01/22 Excel活用-012_第二篇：製作電子試卷
3. 2019/04/23 Excel技巧-003_如何判斷資料是「值」還是「文字」？
KNB會分成三篇來說明，其中前兩篇是跟著Excel效率王的步驟，最後一篇則是KNB另外延伸出確認答案的設定。就讓我們繼續看下去吧。
第三篇：確認答案、錯誤變色
5) 函數>正確與否、解答回寫
5) 「正確與否」=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))
5) 「解答>>」=IF(D4="錯誤",VLOOKUP(B4,K4:L8,2,0),"-")
6) 格式化條件設定>表格變色
5) 「只格式化包含下列的儲存格 >>儲存格值_等於_=”錯誤” >>格式 >>確定 >>確定」
△ 情境：電子試卷用下拉選單，讓答案邊選邊消失。後續，可自動批改答案並帶出解答。
步驟一：新增「正確與否」「>>解答」欄位，以及「[答案]」。
🡺 [D欄] 新增「正確與否」欄位
🡺 [E欄] 新增「>>解答」欄位
🡺 [K、L欄] 新增「答案」中日文對照
步驟二：設定「正確與否」函數_vlookup + if
● 函數拆解說明：D4儲存格「=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))」
● vlookup函數：將對照資料回寫相對應的橫軸欄位。
🡺 [Lookup_value] 對照資料_儲存格內的內容「B4」
🡺 [Table_array] 回寫的答案範圍「$K$3:$L$8」；因為是絕對範圍要上鎖符號「$」
🡺 [Col_index_num] 回寫第幾格欄位_回傳欄位第二格「2」
🡺 [Range_lookup] 數字「0」表示完全符合才可回寫。也可填寫「False」
🡺 「確定」
註解：相關函數及細節可參考以下連結
1) vlookup函數：2019/03/23 Excel函數-001_vlookup-查閱與參照
2) 絕對範圍：2019/04/13 Excel技巧-002_欄與列上鎖
● 小主語IF函數說明：D4儲存格「=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))」
● if函數：如果符合A條件，就會得B，若不符合A條件，則是得C。
🡺 [Logical_test] 若符合A條件_若指定資料等於C4儲存格的內容「C4=VLOOKUP(B4,$K$3:$L$8,2,0)」；因為是絕對範圍要上鎖符號「$」
🡺 [Value_if_true] 就會得B_回寫正確「”正確”」
🡺 [Value_if_false] 若不符合A條件，則是得C_回寫錯誤「”錯誤”」
🡺 「確定」
註解：相關函數及細節可參考以下連結
1) if函數：2019/04/25 Excel函數-011_if-邏輯
2) 回寫「"正確"」「"錯誤"」：2019/04/23 Excel技巧-003_如何判斷資料是「值」還是「文字」？
● 大主語IF函數說明：D4儲存格「=IF(C4="","",IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤"))」
● if函數：如果符合A條件，就會得B，若不符合A條件，則是得C。
🡺 [Logical_test] 若符合A條件_若C4儲存格的內容等於空白「C4=””」
🡺 [Value_if_true] 就會得B_回寫空白「””」
🡺 [Value_if_false] 若不符合A條件，則是得C_回寫裡面的小主語條件「IF(C4=VLOOKUP(B4,$K$3:$L$8,2,0),"正確","錯誤")」；因為是絕對範圍要上鎖符號「$」
🡺 「確定」
🡺 Ctrl+C(複製)D4儲存格 >>Shift+向下鍵(反白經過的儲存格)至D8 >>Enter(貼上)
註解：相關函數及細節可參考以下連結
1) if函數：2019/04/25 Excel函數-011_if-邏輯
2) 絕對範圍：2019/04/13 Excel技巧-002_欄與列上鎖
3) 回寫「"正確"」「"錯誤"」：2019/04/23 Excel技巧-003_如何判斷資料是「值」還是「文字」？
步驟三：設定「>>解答」函數_vlookup + if
● 函數說明：E4儲存格「=IF(D4="錯誤",VLOOKUP(B4,$K$4:$L$8,2,0),"-")」
● 大主語if函數：如果符合A條件，就會得B (vlookup函數)，若不符合A條件，則是得C。
🡺 [Logical_test] 若符合A條件_若D4儲存格的內容等於錯誤「D4="錯誤"」
🡺 [Value_if_true] 就會得B_回寫vlookup函數的對應欄位「VLOOKUP(B4,$K$4:$L$8,2,0)」；因為是絕對範圍要上鎖符號「$」
🡺 [Value_if_false] 若不符合A條件，則是得C_回寫「”-“」
🡺 「確定」
🡺 Ctrl+C(複製)E4儲存格 >>Shift+向下鍵(反白經過的儲存格)至E8 >>Enter(貼上)
註解：相關函數及細節可參考以下連結
1) if函數：2019/04/25 Excel函數-011_if-邏輯
2) vlookup函數：2019/03/23 Excel函數-001_vlookup-查閱與參照
3) 絕對範圍：2019/04/13 Excel技巧-002_欄與列上鎖
步驟四：格式化條件設定_表格變色
● 設定格式化的條件：建立規則使儲存格變色
🡺 選取儲存格D4 >>「常用」 >>「設定格式化的條件」 >>「管理規則」
🡺 「新增規則」
🡺 「只格式化包含下列的儲存格」 >>選取「等於」 >>輸入「錯誤」 >>「格式」
🡺 左圖：「字型」分頁 >>「色彩」選色
🡺 右圖：「填滿」分頁 >>選色 >>「確定」
🡺 「確定」
🡺 測試設定：將「日文諺語」答案選錯，確認是否會變色。
🡺 選取儲存格D4 >>「常用」 >>「複製格式」 >>選取儲存格D5:D8
設定完成：判斷序號 + 清單公式
● 測試設定：將「日文諺語」答案選錯，確認各個設定是否正常。
🡺 紅框：錯誤變色 (函數組合IF + Vlookup + 設定格式化條件設定)
🡺 藍框：確認答案 (函數組合IF + Vlookup)
KNB碎碎唸：
打了三篇連載，不曉得各位看不看得懂呢？
KNB盡量用直覺式的方式做筆敘，如果有在哪個環節，有不清楚的地方都可以發問喔。
其實工作這麼多年，雖然只有快十年XD 講得好像很久一樣。
Excel建置之類的設定，小本認為也算是工程性質的工作，雖然說Excel好像每個人都會用，但是要使用到能得心應手，建置成自己想要方便的系統也不是件容易的事情阿。小本到現在也都還是學習中
引用資料：
01# Excel效率王「邊選邊消失/下拉式選單」
函數說明：
1. 2019/03/23 Excel函數-001_vlookup-查閱與參照
2. 2019/04/25 Excel函數-011_if-邏輯
3. 2019/04/13 Excel技巧-002_欄與列上鎖
延伸閱讀：
1. 2020/01/19 Excel活用-011_第一篇：製作電子試卷
2. 2020/01/22 Excel活用-012_第二篇：製作電子試卷
3. 2019/04/23 Excel技巧-003_如何判斷資料是「值」還是「文字」？
留言
張貼留言