返回列表下一主題上一主題發帖

請問這儲存格內函數可以簡化嗎?

jeffrey628litw

中學生 Rank: 2

帖子: 228
主題: 62
精華: 0
積分: 364
點名: 17
作業系統: Win 10
軟體版本: Office 2007 & 2010
閱讀權限: 20
性別: 男
註冊時間: 2011-3-5
最後登錄: 2024-3-12

2011兔年全勤獎章

1^# 跳轉到 » 倒序看帖

打印

字體大小: tT

發表於 2019-12-21 11:20 | 只看該作者

請問這儲存格內函數可以簡化嗎?

請問這儲存格內函數可以簡化嗎?

I欄的公式可以簡化嗎?(因為只能用在2010以後的Excel 版本，2007不能寫以下那麼多的函數)

=IF(O2="GAA",IF(T2<>"",T2&" ","")&IF(U2="-","",U2&" ")&IF(V2<>"",V2&" ","")&IF(Z2<>"",Z2&" ","")&IF(AD2<>"",AD2&" ","")&IF(AE2<>"",AE2&" ","")&IF(W2<>"",W2&" ","")&IF(Y2<>"",Y2&" ","")&IF(AG2<>"",RIGHT(AG2,2),"")&IF(AG2<>"","-","")&IF(AH2<>"",RIGHT(AH2,2),"")&IF(AI2="-","","("&AI2&")"),IF(O2="GAF",IF(T2<>"",T2&" ","")&IF(U2="-","",U2&" ")&IF(V2<>"",V2&" ","")&IF(W2<>"",W2&" ","")&IF(X2<>"",X2&" ","")&IF(Y2<>"",Y2&" ","")&IF(Z2<>"",Z2&" ","")&IF(AD2<>"",AD2&" ","")&IF(AG2<>"",RIGHT(AG2,2),"")&IF(AG2<>"","-","")&IF(AH2<>"",RIGHT(AH2,2),"")&IF(AI2="-","","("&AI2&")"),IF(O2="GBE",IF(T2<>"",T2&" ","")&IF(U2="-","",U2&" ")&IF(V2<>"",V2&" ","")&IF(X2<>"",X2&" ","")&IF(AD2<>"",AD2&" ","")&IF(AC2<>"",AC2&" ","")&IF(W2<>"",W2&" ","")&IF(AE2<>"",AE2&" ","")&IF(Y2<>"",Y2&" ","")&IF(AG2<>"",RIGHT(AG2,2),"")&IF(AG2<>"","-","")&IF(AH2<>"",RIGHT(AH2,2),"")&IF(AI2="-","","("&AI2&")"),IF(O2="GAE",IF(T2<>"",T2&" ","")&IF(U2="-","",U2&" ")&IF(V2<>"",V2&" ","")&IF(W2<>"",W2&" ","")&IF(Y2<>"",Y2&" ","")&IF(X2<>"",X2&" ","")&IF(AA2<>"",AA2&" ","")&IF(AB2<>"",AB2&" ","")&IF(AC2<>"",AC2&" ","")&IF(AD2<>"",AD2&" ","")&IF(AE2<>"",AE2&" ","")&IF(AG2<>"",RIGHT(AG2,2),"")&IF(AG2<>"","-","")&IF(AH2<>"",RIGHT(AH2,2),"")&IF(AI2="-","","("&AI2&")"),""))))

檔案下載：