close
第十二課 Choose
Choose(參照值，參照值為1時的動作，參照值為2時的動作，...)
該函數至少有二個引數，至多三十個，第一個引數為參照值，第二個引數為參照值為1時之執行動作，第三個引數為參照值為2時之執行動作，以此類推
| 例１，輸入春夏秋冬，將分別輸出ABCD | ||
| 冬 | D | |
| =CHOOSE(FIND(A2,"春夏秋冬"),"A","B","C","D") | ||
| 說明 | ||
| 公式同 =MID("ABCD",FIND(A2,"春夏秋冬"),1) =VLOOKUP(A2,{"春","A";"夏","B";"秋","C";"冬","D"},2,) =HLOOKUP(A2,{"春","夏","秋","冬";"A","B","C","D"},2,) =INDEX({"A","B","C","D"},FIND(A2,"春夏秋冬")) =INDEX({"A","B","C","D"},MATCH(A2,{"春","夏","秋","冬"},)) | ||
| 例２，輸入一數字，判定其為奇數或偶數 | ||
| 5 | 奇數 | =CHOOSE(MOD(A13,2)+1,"偶數","奇數") |
| 說明 | ||
| 公式同 =IF(MOD(A12,2),"奇數","偶數") | ||
| 例３，輸入一數字，判定其是否大於100 | ||
| 378 | 大於100囉 | |
| =CHOOSE((A21>100)+1,"沒有大於100","大於100囉") | ||
| 說明 | ||
| 公式同 =IF(A20>100,"大於100囉","沒有大於100") | ||
| 例４，輸入一數字，判定其為幾位數（限六位數字） | ||
| 1245 | 四位數 | |
| =CHOOSE(LEN(A30),"一位數","兩位數","三位數","四位數","五位數","六位數") | ||
| 說明 | ||
| 公式同 =CHOOSE(LEN(A28),"一","兩","三","四","五","六")&"位數" | ||
| 例５，輸入一數字，判定其數字和是否大於10 | ||
| 67 | 大於10囉 | |
| =CHOOSE((SUMPRODUCT(--MID(A39,ROW(INDIRECT("1:"&LEN(A39))),1))>10)+1,"沒有大於10","大於10囉") | ||
| 說明 | ||
| 進階應用 | ||
| 例６，輸入一數字，判定其是否為三的倍數 | ||
| 4 | 不是三的倍數 | |
| =CHOOSE((MOD(A49,3)>0)+1,"三的倍數","不是三的倍數") | ||
| 說明 | ||
| 進階應用 | ||
全站熱搜
留言列表
