2011年4月26日 星期二

Excel-欄名和欄數的轉換

有人問到 Excel 2010 的最大欄數是XFD,這個數字代表第幾欄呢?我們可以把Excel的欄名順序看成是一種26進制的表示法,A, ~ , Z, AA ~ AZ, BA ~ ZZ, AAA ~ XFD。

以3個位元字母的第XFD欄(最後一欄)為例:

XFD1:=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),ROW(),"") 

=SUBSTITUTE(ADDRESS(1,16384,4),ROW(),"") [取得列數和欄數]

=SUBSTITUTE("XFD1",ROW(),"")  [將列數和欄數轉成欄名列號字串]

=SUBSTITUTE("XFD1",1,"")  [將欄名列號字串中的列號去掉]

=XFD

XFD2:=(CODE(MID(XFD1,1,1))-64)*26*26+(CODE(MID(XFD1,2,1))-64)*26+(CODE(MID(XFD1,3,1))-64)

因為 A~Z 有26個,所以看成是26進制的觀念。

MID(XFD1,1,1)="X",MID(XFD1,2,1)="F",MID(XFD1,1,3)="D"。

CODE(MID(XFD1,1,1))-64 = CODE("X")-64 = 88–64 = 24

CODE(MID(XFD1,2,1))-64 = CODE("F")-64 = 70–64 = 6

CODE(MID(XFD1,3,1))-64 = CODE("D")-64 = 68–64 = 4

XFD2:= 24*26*26+6*26+4 = 16384

複製XFD1:XFD2,可以貼在所有3個位元字母的欄名儲存格中。

由此看出Excel 2010的最後一欄是編號16384(=65536/4)。

如果想要不管1,2,3位元的欄位名稱都能適用,則儲存格XFD2中的公式改為:

= CHOOSE(LEN(XFD1),CODE(MID(XFD1,1,1))-64,(CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64)

利用LEN函數判斷儲存格的欄位名稱有幾個位元,再利用CHOOSE函數來選擇:

1:CODE(MID(XFD1,1,1))-64

2:CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64

3:CODE(MID(XFD1,2,1)))-64CODE(MID(XFD1,1,1))-64)*26+(CODE(MID(XFD1,2,1)))-64

沒有留言:

張貼留言

檢視其他文章

好康東東