2018年2月2日 星期五

Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)

在 Excel 中以欄名列號當為儲存格名稱,如果想要根據欄的名稱轉換為欄的數值,該如何處理?(參考下圖)
Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)

【公式設計與解析】
參考下圖,在 Excel 中是以A~Z、AA~AZ、BA~BZ、...為欄名的順序。欄的數值為A=1、B=2、C=3、...、AA=27欄。
Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)
所以,XEZ:第 16380 欄;XFD:第 16384 欄
Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)
1. 計算欄名對應的數值
儲存格C2:=SUM((CODE(MID(A2,{1,2,3},1))-64)*26^{2,1,0})
(1)MID(A2,{1,2,3},1)
本例使陣列來簡化公式,利用 MID 函數找出儲存格A2中的第 1, 2, 3 個字。
(2) CODE(MID(A2,{1,2,3},1))
找出儲存格A2中每個字的ASCII碼。
(3) CODE(MID(A2,{1,2,3},1))-64
將上式減64,可得A=1、B=2、C=3、...。
(4) (CODE(MID(A2,{1,2,3},1))-64)*26^{2,1,0}
將第1碼X26^2,將第2碼X26^1,將第3碼X26^0。
最後利用 SUM 函數予以加總,即為所求。

2. 計算數值對應的欄名
儲存格G2:=SUBSTITUTE(ADDRESS(1,E2,4),1,"")
(1) ADDRESS(1,E2,4)
ADDRESS 函數可以自動轉換數值為欄名,本例參數 1 會傳回列號為 1 的儲存格位址。
(2) SUBSTITUTE(ADDRESS(1,E2,4),1,"")
利用 SUBSTITUTE 函數將列號 1 置換為空字串("")。
Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)

沒有留言:

張貼留言

檢視其他文章

好康東東