依架情況就係咁,
=IF(AND(F38="1",F46="B1"),"例子1",
IF(AND(F38="2",F46="B1"),"例子2",
IF(AND(F38="3",F46="B1"),"例子3",
IF(AND(F38="4",F46="B1"),"例子4",
IF(AND(F38="5",F46="B1"),"例子5",
IF(AND(F38="6",F46="B1"),"例子6",
IF(AND(F38="7",F46="B1"),"例子7",
IF(AND(F38="8",F46="B1"),"例子8",
IF(AND(F38="1",F46="B2")," 例子9",
IF(AND(F38="2",F46="B2")," 例子10",
IF(AND(F38="3",F46="B2"),"",
IF(AND(F38="4",F46="B2"),"",
IF(AND(F38="5",F46="B2"),"",
IF(AND(F38="6",F46="B2"),"",
IF(AND(F38="7",F46="B2"),"",
IF(AND(F38="8",F46="B2"),"",
IF(AND(F38="1",F46="B3"),"",
IF(AND(F38="2",F46="B3"),"",
IF(AND(F38="3",F46="B3"),"",
IF(AND(F38="4",F46="B3"),"",
IF(AND(F38="5",F46="B3"),"",
IF(AND(F38="6",F46="B3"),"",
IF(AND(F38="7",F46="B3"),"",
IF(AND(F38="8",F46="B3"),"",
IF(AND(F38="1",F46="B4"),"",
IF(AND(F38="2",F46="B4"),"",
IF(AND(F38="3",F46="B4"),"",
IF(AND(F38="4",F46="B4"),"",
IF(AND(F38="5",F46="B4"),"",
IF(AND(F38="6",F46="B4"),"",
IF(AND(F38="7",F46="B4"),"",
IF(AND(F38="8",F46="B4"),"",
IF(AND(F38="1",F46="B5"),"",
IF(AND(F38="2",F46="B5"),"",
IF(AND(F38="3",F46="B5"),"",
IF(AND(F38="4",F46="B5"),"",
IF(AND(F38="5",F46="B5"),"",
IF(AND(F38="6",F46="B5"),"",
IF(AND(F38="7",F46="B5"),"",
IF(AND(F38="8",F46="B5"),"",
IF(AND(F38="1",F46="B6"),"",
IF(AND(F38="2",F46="B6"),"",
IF(AND(F38="3",F46="B6"),"",
IF(AND(F38="4",F46="B6"),"",
IF(AND(F38="5",F46="B6"),"",
IF(AND(F38="6",F46="B6"),"",
IF(AND(F38="7",F46="B6"),"",
IF(AND(F38="8",F46="B6"),"",
IF(AND(F38="1",F46="B7"),"",
IF(AND(F38="2",F46="B7"),"",
IF(AND(F38="3",F46="B7"),"",
IF(AND(F38="4",F46="B7"),"",
IF(AND(F38="5",F46="B7"),"",
IF(AND(F38="6",F46="B7"),"",
IF(AND(F38="7",F46="B7"),"",
IF(AND(F38="8",F46="B7"),"",
有冇方法可以簡化佢,但又做到相同效果???
Excel簡化函數公式???
2011-10-31 4:42 am
回答 (2)
2011-10-31 7:43 am
✔ 最佳答案
當全部條件都不符合時要顯示甚麼？2011-10-30 21:30:05 補充：
2011-10-30 21:14:06 補充
當全部條件都不符合時就=IF(AND(F38="1",F46="B1"),"例子1","")咁.
那麼答案就只有例子1至10及""(共11種)，只有頭10個IF是有用的，為何要SET幾十個呢？
2011-10-30 23:43:09 補充：
以下假設F46是(大寫英文字)B開頭才會計算答案
=IF(LEFT(F46,1)="B","例子"&F38+SUBSTITUTE(F46,"B","")*8-8,"")
或
=IF(CODE(F46)=66,"例子"&F38+SUBSTITUTE(F46,"B","")*8-8,"")
2011-10-31 00:13:53 補充：
若要保証F38一定要是"文字"1至8才計算可用AND引入多以下三個條件
F38=TEXT(F38,"#")
F38*1>0
F38*1<9
希望你F38是固定只會出現1-8和F46固定出現B1-7，否則可能要加多好多條件
2011-10-31 7:34 am
關於公式中有 "B1"... "B2"... 等描述，如果想參照儲存格是不該有 " 的，請問是想參照儲存格﹖還是內容真是文字 B1...B2...﹖
收錄日期: 2021-04-13 18:20:09
原文連結 [永久失效]:
https://hk.answers.yahoo.com/question/index?qid=20111030000051KK01019