2017年9月16日 星期六

Excel-依數值區間查詢分級(VLOOKUP)

網友問到:如何在 Excel 的工作表中,依數值區間查詢分級的作法?
分級方式如下:
正常值       男生12~18                  ,女11~15
異常D級     男生10~11.9或18.1~19 ,女生10~10.9或15.1~16
異常C級     男生8~9.9或19.1~20    ,女生8~9.9或16.1~17
網友原本想要使用巢狀 IF 函數,但是我不建議這樣做。
參考下圖,由網友提供的分級方式,先轉換成可以供 VLOOKUP 函數查詢的表格,再使用VLOOKUP 函數建立公式。
如此,會比網友使用巢狀 IF 函數來的好,因為公式比較簡捷,而且分級對照的數值如果變動,只要修改對照表,不用修改公式。
Excel-依數值區間查詢分級(VLOOKUP)

【公式設計與解析】
儲存格B3:=CHOOSE(MATCH(B1,A8:B8,0),VLOOKUP(B2,A9:C15,3,TRUE),
VLOOKUP(B2,B9:C15,2,TRUE))
(1) MATCH(B1,A8:B8,0)
利用 MATCH 函數依據儲存格B1查詢在儲存格A8:B8範圍中的位置。若為男生,則傳回『1』;若為女生,則傳回『2』。
(2) VLOOKUP(B2,A9:C15,3,TRUE)
利用 VLOOKUP 函數依儲存格B2查詢儲存格A9:C15範圍的資料,傳回第 3 欄的結果。
(3) VLOOKUP(B2,B9:C15,2,TRUE)
利用 VLOOKUP 函數依儲存格B2查詢儲存格B9:C15範圍的資料,傳回第 2 欄的結果。
(4) CHOOSE(第(1)式,第(2)式,第(3)式)
利用 CHOOSE 函數,根據第(1)式的傳回值,若為『1』(男生),則執第(2)式;若為『2』(女生),則執第(3)式。

沒有留言:

張貼留言

檢視其他文章

好康東東