Item: 大八大飯店大八 TOP 助餐
Rating: 4
Author: 錦子老師

2811

EXCEL數值區間判斷與處理

楊小萍：「請問錦子老師，如何寫出A8的公式：

1. B2~D6是數字區間，三位數到四位數都有可能。

2. 於B8C8D8輸入數字後，A8要跳出所屬No號碼。

3. 若B8C8D8其中一個與其他不同，A8需顯示錯誤。

先謝謝。」

錦子老師：「點取A8儲存格輸入公式：

=IF(AND(MATCH(B8,VALUE(MID(B2:B6,1,FIND("-",B2:B6,1)-1)),1)=MATCH(C8,VALUE (MID(C2:C6,1,FIND("-",C2:C6,1)-1)),1),MATCH(B8,VALUE(MID(B2:B6,1,FIND("-",B2:B6,1)-1)),1)=MATCH(D8,VALUE(MID(D2:D6,1,FIND("-",D2:D6,1)-1)),1)),OFFSET(A1,MATCH(B8, VALUE(MID(B2:B6,1,FIND("-",B2:B6,1)-1)),1),0),"錯誤")

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。

【公式解說】

MATCH(B8,VALUE(MID(B2:B6,1,FIND("-",B2:B6,1)-1)),1)

首先找尋到-符號在B2:B6儲存格中的位置(FIND("-",B2:B6,1))，再抓取B2:B6儲存格第1個字元到-符號前一個字元的字串(MID(B2:B6,1,FIND("-",B2:B6,1)-1))，抓出來後利用VALUE函數將其變成數值格式，再搜尋B8儲存格內容以MATCH函數的比其值小的最大值比對方式(1)抓取其位於B2:B6儲存格被抓取字串的第幾列。

然後用AND函數來比對B8與C8傳回位置是否相同(MATCH(B8,VALUE(MID(B2:B6,1,FIND ("-",B2:B6,1)-1)),1)=MATCH(C8,VALUE (MID(C2:C6,1,FIND("-",C2:C6,1)-1)),1))、B8與D8傳回位置是否相同(MATCH(B8,VALUE(MID(B2:B6,1,FIND("-",B2:B6,1)-1)),1)=MATCH(D8,VALUE (MID(D2:D6,1,FIND("-",D2:D6,1)-1)),1))，若相同利用OFFSET函數抓取B8傳回位置的值(OFFSET(A1,MATCH(B8, VALUE(MID(B2:B6,1,FIND("-",B2:B6,1)-1)),1),0))，否則輸入錯誤字串。