2016年10月7日 星期五

Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)

網友問到這類的 Excel 應用問題:參考下圖,在加法、減法、乘法前面以『V』代表勾選,如果依勾選結果列出 X 和 Y 的運算式。
下圖中的例子為勾選『乘法』後,運算式為『X*Y=23*4=92』,該如何撰寫公式?
Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)

【公式設計與解析】
1. 使用輔助儲存格M1
儲存格M1:=(SUMPRODUCT((A1:F1="V")*COLUMN(A1:F1))+1)/2
在儲存格A1:F1中判斷那一個儲存格含有『V』,並傳回一個數字:
儲存格A1為『V』傳回 1;儲存格C1為『V』傳回 2;儲存格E1為『V』傳回 3。
儲存格D4:="X" & CHOOSE(M1,"+","-","*") & "Y=" & H1 & CHOOSE(M1,"+",
"-","*") & J1 & "=" & CHOOSE(M1,H1+J1,H1-J1,H1*J1)
CHOOSE(M1,"+","-","*"):根據儲存格M1的傳回值決定顯示那一個運算子『+、-、*』。
CHOOSE(M1,H1+J1,H1-J1,H1*J1)根據儲存格M1的傳回值決定執行那一個運算式。

2. 不使用輔助儲存格
如果你在工作表上不想顯示輔助儲存格,則可以改用定義名稱的方式來解決。
如下圖,定義名稱『OP』:
Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)
參照:=(SUMPRODUCT((A1:F1="V")*COLUMN(A1:F1))+1)/2 (與儲存格M1相同)
公式調整為:
儲存格D4:="X" & CHOOSE(OP,"+","-","*") & "Y=" & H1 & CHOOSE(OP,"+",
"-","*") & J1 & "=" & CHOOSE(OP,H1+J1,H1-J1,H1*J1)


【延伸練習】
如果設計為下拉式選單來挑選想要的運算,該如何設計?
Excel-挑選不同運算子執行不同運算式(CHOOSE,SUMPRODUCT)
儲存格I1:=VLOOKUP(A1,{"加法",1;"減法",2;"乘法",3},2,FALSE)
儲存格B4:="X" & CHOOSE(I1,"+","-","*") & "Y=" & D1 & CHOOSE(I1,"+",
"-","*") & F1 & "=" & CHOOSE(I1,D1+F1,D1-F1,D1*F1)

沒有留言:

張貼留言

檢視其他文章

好康東東