学以致用——使用公式法实现Excel列标转换为列名（Convert column number to name by using functions）

经过一番努力（包括熬夜到凌晨三点多），终于实现了通过使用Excel现成的函数将Excel列标转换为列名的功能。

“功夫不负有心人”，“世界上最怕的两个字就是认真！”。

效果图如下：

公式如下：

=IFERROR(
LOOKUP(IF(A2>26*26+26,MOD(INT((A2-27)/(26*26))-1,26),""),
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},
{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}),"")&
IFERROR(LOOKUP(IF(A2>26,MOD(INT((A2-MOD(A2-1,26))/26)-1,26),""),
{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},
{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}),"")&
LOOKUP(MOD(A2-1,26),{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},
{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"})

和VBA自定义函数法、全表查询法及VBA子程序法相比较，这个公式的调试时间实在是太长了，开发效率非常低。

但是，开发好了以后，用起来也是很方便的。

公式法特点：

1. 综合使用了VLOOKUP、MOD、INT、IF、IFERROR函数

2. 关键是要找到转换后的各位数的准确表达式（如，十位：）