excel提取不规则字段_以人事档案管理为例详解EXCEL关联套表的设置方法

EXCEL基本功能概括起来就四个，录入、查询、计算、呈现。

有时候我们为了工作方便，需要把同一个工作簿或跨工作薄的几个有内在关联关系的工作表组合起来，形成一个套表模板，以起到一劳永逸或省去重复性工作的作用。这些套表，体现了录入、查询、计算、呈现的所有或部分功能。需求不同，组合的方式不同。下面我们以《人事档案管理套表》(如下图)其中的花名册信息分类查询功能(第12个按钮)的实例详细说明。

图一：套标界面

一 明细需求的功能

图二：功能需求

通过以上GIF展示，可以对这个要实现的功能有一个直观的印象，就是完成花名册中不同预警信息、类别信息和字段信息的提取、查询和展示。

二 工作表的配备与说明

完整的关联表，应该至少包括基础信息表、流水记录表、参数表、过渡表、呈现表五种功能的表格，当然不同的套表需求不同，不一定都用上。

单就以上功能实现我们用到的表格类型如下：

图三 表格关系

1. 基础信息表。也就是图中的“01基础信息表”，也相当于产品销售套表中的《客户基本信息表》《产品基本信息表》等。里面主要包含员工基本信息和职岗信息。

• 主要特点：是一个工号或一个姓名只包含一条信息；格式设置规范。

• 功能体现：EXCEL四个功能的录入功能。

• 主要原则：减少录入字段，能通过公式或函数计算出来的拓展字段可以入在过渡表里自行计算，没有必要出现在基础信息表里，例如：只要在基础信息表里有身份证号字段，出生年月、生日月份、性别、年龄等字段就不要出现；有了入职日期字段，司龄等信息就不要出现。

2. 流水记录表。图一中的学习经历库、劳动合同库、异动记录库，相当于产品销售套表中的《产品销售明细表》。

• 主要特点：一个工号或一个姓名对应多条信息；这些表中会通过一个字段与基础信息表关联(例如：工号、姓名、身份证号等唯一性较强的字段)；格式设置规范。

• 功能体现：录入，提供提取信息，根据后面呈现表的需求设置字段。学习经历库为过渡表提供最后一次的学习经历；劳动合同库为过渡表提供最后一次劳动合同信息；异动记录库为过渡表提供最新职岗信息。如下图：

图四 学习经历表

图五 劳动合同表

图六 异动记录表

3. 过渡表。在图示按钮中没有出现，主要是公式调取基础信息表的全字段并计算拓展字段，再加上劳动合同库最后一次信息提取；学习经历库最后一次信息提取；根据异动记录库职岗信息做实时变更。一般会设置好公式后隐藏或在名称管理器中建立动态名称，供呈现表调用。

• 主要特点：全部公式，无需录入，并具备最全信息和字段。一个工号或姓名对应唯一一条记录；公式设置延展性好，并具备避错功能。

• 主要原则：抓捕基础信息表和流水记录表里的一切变动。

• 功能体现：查询提取、计算

4. 呈现表。结合查询字段的设置在过渡表里提取不同的记录，并予以呈现。

• 主要特点：设计美观，简洁明了，功能满足要求。

• 功能体现：呈现

• 主要原则：尽可能全面的满足功能需求。

5. 参数表。供给除过渡表以外的所有表格中下拉字段列表信息，供下拉菜单提取。

三 套表设立的几个原则

1. 设计之初遵循功能需求评估，尽可以周全，因为是套表，环环相扣，所以修改比普通的单表要繁琐；

2. 以呈现表的功能确定需要哪些基础信息表和流水记录表及详细字段设置；

3. 以功能实现的难度和拓展字段的多少确定是否需要过渡表。

四 小结

关联套表的设置要有一个系统性思维，以功能需求评估为引导，层层设置，环环相扣，这样才能流畅的实现最终需求。