这是一个有实际应用价值的案例。

需求：编制合同编号，如图红色字体所示。

合同编号的结构：项目名称-合同类型-日期-编号

需求分析：

项目名称和合同类型有现成数据；

日期也是现成数据但需要修改为制定格式；

编号需要根据合同类型制定，一个类型用一个编号。

任务分解：

第一步：获取日期格式

第二步：获取编号

第三步：连接

第四步：整合公式

复杂的合同编号

日期格式

TEXT是专业的数据转换函数，很容易将日期转换为需要的【年份月份】格式：

=TEXT(C2,"yyyymm")

日期格式

获取编号

以下公式获取编号：

=TEXT(COUNTIF($B$2:B2,B2),"00")

COUNTIF($B$2:B2,B2)是获取编号的核心，公式的巧妙之处在于COUNTIF第一参数单边锁定，向下填充时依次统计合同类型在此之前出现的次数作为编号。

TEXT的作用是将其显示为两位数。

获取编号

连接

Excel2019以上的版本用TEXTJOIN连接将4个部分连接，中间用“-”间隔：

=TEXTJOIN("-",1,A2:B2,D2,E2)

没有这个函数的版本用连接符号连接：

=A2&"-"&B2&"-"&D2&"-"&E2

连接

整合公式

将D列和E列的公式带入到TEXTJOIN中整合公式，就可以删除辅助列：

=TEXTJOIN("-",TRUE,A2:B2,TEXT(C2,"yyyymm"),TEXT(COUNTIF($B$2:B2,B2),"00"))

整合公式