今天来点传统的，用Excel掌控天干地支

甲午战争、戊戌变法、辛亥革命……，用来给这些重大事件命名的，是极具中国特色的纪年方式——天干地支。

虽然这套纪念方式已经传承了几千年，但是要想让通俗的公元纪年快速地匹配对应的天干地支，却总要费些周折。本文将与读者分享的，就是用Excel几个简单的函数来制作一个简单的速配工具。

• 天干地支的基本规律

使用Excel函数，首先需要摸清规律。对待这个传统的纪年法也不例外。

通俗来讲，天干地支是两套互不搭理独立运行的符号的组合，具体内容如图1：

图1 天干地支列表

从图1可以看出，天干一共是10个符号（从甲到癸），地支一共是12个符号（从子到亥）。据此，天干地支的第1个组合便是“甲子”，随后两套阵营按各自的周期循环，当第10个组合“癸亥”出现后，天干将返回到“甲”，而地支将绝情地抛弃携手10年后被打回原形的搭档，继续自己的第11个符号“戌”挺进。

于是第11个组合便是“甲戌”。而当第12个组合“乙亥”结束后，地支也终于走到了劲头，此时你让天干摒弃前嫌和地支同归于好携手并进自然也是不可能的了，于是第13个组合为“丙子”。

由于10和12的最小公倍数是60，所以当这对冤家闹别扭的第51个年头（61-10），双方会在同一时刻打回原型，于是“甲子”又来了。

根据资料显示，中国拿到奥运会第1块金牌的1984年正是甲子年，找到了规律，我们开始接招吧。

 

• 函数准备

从前文的信息可以发现，天干地支纪年有以下特性：

1、有周期性规律，这个可以联想到除法的商和余数概念。

2、不管是天干还是地支，每个符号都用对应的序号，这就提示我们，可以通过对照方式进行查找。

这样，我们需要为完成这个工具准备以下函数：

1、MOD，这个函数可以帮我们寻找周期性规律。

2、VLOOKUP，这个函数自不必多说，算是查询函数的大腕了。

3、IF，这个函数虽然只会一根筋地非此即彼，但是它却是一个万精油级别的家伙。

4、&，严格说，它不是公式，而是一个焊接工。

鉴于篇幅限制，且以上三个函数并不复杂，所以本文就不对它们进行隆重介绍了。如果你还对它们不甚熟悉，可先通过Excel函数中自带的帮助文件进行了解，或者参阅拙作《偷懒的技术：打造财务Excel达人》第四章的相关内容。

 

• 公式设计

下面我们来看看怎样利用上面三个函数在Excel中玩转天干地支。为便于讲解，我们先建立如图2中的工作表（E列年份可任意设置）。

图2 天干地支查询表

（一）、天干

由于我们已经知道天干10年一轮回，且1984年的天干为“甲”（1号），那么可以推定，年数尾数减去3，即可得到对应的天干序号，如果尾数减去3小于1，则加上一个轮回（10），即可得到对应的天干代号。

上述逻辑也可以理解为，将年数除以10余数减去3，即为对应天干的序号。若该数小于1，则需要加10，即当年数除以10的余数小于4（1+3）时，则余数加7（-3+10）。

以F3单元格为例，我们得出天干序号的计算公式：

=MOD(E3,10)+IF(MOD(E3,10)<4,7,-3)

有了序号，要找符号，自然就交给VLOOKUP了：

=VLOOKUP(序号计算公式,$A$3:$B$14,2,0)

F3单元格完整公式（如图3）：

=VLOOKUP(MOD(E3,10)+IF(MOD(E3,10)<4,7,-3),$A$3:$B$14,2,0)

图3 天干公式

（二）、地支

同理，因为已知地支以12年为一轮回，我们用1984除以12，余数依然是4。也就是说，地支的计算公式和天干的唯一区别，就是将计算余数的除数，从10调整为12。

同时，当余数减去3的值小于1时，则需要将该数加12。即当年数除以12的余数小于4（1+3）时，则余数加9（-3+12）。

于是可得G3单元格公式为（如图4）：

=VLOOKUP(MOD(E3,12)+IF(MOD(E3,12)<4,9,-3),$A$3:$C$14,3,0)

图4 地支公式

说到地支，自然要说一下和它一一对应的那些动物，它们大概要算是中国历史上最早的“明星组合”了，大号“十二生肖”。

有了地支的公式逻辑，完成十二生肖的快速对应公式，应该就不需要我再啰嗦啦。

最后，有请焊接工&将天干地支合拢（如图5）。

H3单元格公式：=F3&G3

图5 天干地支合拢

• 未尽事宜

细心的朋友也许已经发现，上述公式，其实只适合公元纪年为正数的年份的换算，对于公元前的年代，则需要考虑到负数的“逆反”效果和没有“公元0年”这个年份。

当然，还是那句话，只要有规律可循，就有办法解决，如果你有兴趣，不妨也来试试吧。

作者：钱勇