學不完．教不停．用不盡

有人問到 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