excel技巧 及 花名册

《excel技巧 及 花名册》由会员分享，可在线阅读，更多相关《excel技巧 及 花名册（17页珍藏版）》请在金锄头文库上搜索。

1、设计标准化规范化的 Excel 基础表格，是高效数据分析 的第一步，因为数据分析的源头就是基础表格数据。 设计基础表格的总体原则是： 结构的科学性 数据的易读性 汇总的便宜性 分析的灵活性 外观的美观性 结构的科学性，就是要按照工作的性质，管理的内 容，数据的种类，分别设计基础管理表格，分别保存不 同数据。基础表格要越简单越好，那些把所有数据都装 在一个工作表中的做法是绝对不可取的。比如，要做入 库出库管理，你会如何设计这样的基础表格呢？要用几 个表格来反映入库出库数据？每个表格要怎么保存数据 ？ 数据的易读性包含两个方面：利用函数读数（取数 ）方便，叫函数读数；眼睛查看数据容易，叫人工读数

2、。一个杂而乱的表格，是很难实现这两种高效读数的。 数据易读性差的主要原因有：表格结构设计不合理；数 据保存不合理；残缺不全的表格数据结构。 汇总的便宜性是指不论多大的数量，汇总要简单方 便容易。你可以问自己：我设计的工作表内据汇总方便 吗？大量表格数据之间的汇总方便吗？如果不方便，或 者做起来非常吃力，Excel 很好用的工具也用不上，那 唯一的原因就是基础表格设计有问题，不要发牢骚说 Ex cel 太难了，总是学不会，尤其是函数太难掌握了！ 分析的灵活性，是指不论做何种分析，要讲究数据 分析的灵活多变。因为我们对数据进行分析的目的，是 要针对企业的数据进行深度挖掘，从不同方面找问题、 找原因

3、、找对策，这就要求基础数据必须能够精准反映 企业的管理流程，制作的分析报告也必须具有灵活性， 能够在几分钟内通过转换分析角度而得到另外一份分析 报告。 外观的美观性。不论是基础表还是报告，都尽量要 求把表格进行美化。基础表的美化以容易管理数据标准 ，而报告的美化以分析结果清楚为标准。特别强调的是 ，不论是基础表还是报告，很多人喜欢把数据区域加上 边框，并保持工作表默认的网格线。其实，我们可以取 消网格线，而把数据区域设置为非常简练的线条表格， 并把单元格字体、颜色、边框等进行合理的设置。 下面我们以人力资源管理中一个简单的员工信息表为例 ，来说明标准化表格的设计方法和技巧。这个表格的基 本要求

4、如下： 员工工号从“G0001”开始编号，中间不能断号， 新入职员工依次分配连续的工号 员工姓名中不允许输入空格 所属部门必须快速准确输入企业存在的部门，要名 称统一 学历必须快速规范输入 婚姻状况要快速规范输入 身份证号码必须是 18 位的文本，不允许重复 出生日期、年龄、性别从身份证号码中自动提取 入职时间必须是合法的日期 本公司工龄自动计算得出 为便于分析流动性，工作表要有离职时间和离职原 因两列数据，离职原因是固定的几种类型 新员工输入后，该员工的工号、生日、年龄、工龄 等等计算公式自动往下复制 表格自动美化 1 1、表格结构设计 根据人力资源中对员工信息管理和分析的需要，创 建一个工

5、作表，命名为“员工信息”，数据列标题如下 图。 2 2、工号的自动连续填充 在单元格 A2 输入公式“=“G“&TEXT(ROW(A1),“0000“) ”，就得到能够往下连续填充工号的计算公式。这里， ROW 函数是获取指定单元格的行号（ROW(A1)的结果就是 1，ROW(A2)的结果就是 2，以此类推），TEXT 函数是把 一个数字按照指定的格式转换为文本。那么这个公式的 结果就是 G0001。如果把该公式复制到单元格 A3，该公 式就变成了“=“G“&TEXT(ROW(A2),“0000“)”，结果就 是 G0002。 3 3、规范姓名的输入，不允许在姓名文字中输入空格 选中单元格 B

6、2，设置有效性，其有效性的自定义公式 为“=SUBSTITUTE(B2,“ “,“)=B2”，如下图。这里， 先使用 SUBSTITUTE 函数把输入的姓名中的所有空格替 换掉，然后再跟输入的姓名进行比较，如果两者相等， 表明输入的姓名中没有空格，否则就是有空格，就不允 许输入到单元格。 4 4、规范快速输入部门名称 公司部门是确定的，在一定时期是不会变化的，因此 可以使用数据有效性来快速规范输入部门名称。假如企 业的部门有：总经办、财务部、人力资源部、贸易部、 后勤部、技术部、生产部、销售部、信息部、质检部、 市场部，那么选择单元格 C2，设置数据有效性，即选择 “序列”，来源为“总经办,财

7、务部,人力资源部,贸易 部,后勤部,技术部,生产部,销售部,信息部,质检部,市 场部”，如下图。这样，就为单元格设置了一个下拉列 表，从下拉列表里快速选择输入某个部门名称。 5 5、快速输入学历名称 员工的学历也是固定的几种。假若是以下几个：博士 、硕士、本科、大专、中专、高中，那么也可以使用数 据有效性来快速规范输入学历名称。选择单元格 D2，设 置数据有效性，即选择“序列”，来源为“博士,硕士, 本科,大专,中专,高中”。 6 6、快速输入婚姻状况 单元格 E2 输入员工的婚姻状况。婚姻状况也就两种 数据：已婚和未婚，因此也可以使用数据有效性来控制 输入，也就是在数据有效性对话框的“来源”

8、中输入“ 已婚,未婚”。 7 7、输入不重复的 1818 位身份证号码 每个员工的身份证号码是不重复的，并且必须是 18 位，因此单元格 F2 输入身份证号码时也要使用数据有 效性来控制。首先将 F 列的单元格格式设置成文本，然 后选择单元格 F2，其有效性的自定义公式 =AND(LEN(F2)=18,COUNTIF($F$2:F2,F2)=1) 这里，使用 LEN 函数判断输入的身份证号码是不是 18 位，即 LEN(F2)=18；使用 COUNTIF 统计在前面已经输入 的身份证号码中，即将输入的身份证号码是不是还没有 输过，即 COUNTIF($F$2:F2,F2)=1；然后用 AND

9、函数把 这两个条件组合起来。如果两个条件都成立，表明输入 的身份证号码有效。 8 8、自动输入性别 员工性别从身份证号码中自动提取，不需要人工输入 。选择单元格 G2，输入公式 =IF(ISEVEN(MID(F2,17,1),“女“,“男“) 这里，先用 MID 函数提取身份证号码的第 17 位数字 ，再用 ISEVEN 函数判断是否为偶数，如果是偶数，该 员工性别就是女，否则就是男，判断处理则使用了 IF 函数。 9 9、自动输入出生日期 员工的出生日期也是从身份证号码中自动提取，不需 要人工输入。选择单元格 H2，输入公式 =1*TEXT(MID(F2,7,8),“0000-00-00“)

10、 这里，先用 MID 函数提取身份证号码的中间 8 位生日 数字，再用 TEXT 函数把这 8 位数字按照日期的格式转 换成文本型日期格式，最后把 TEXT 函数的结果乘以数 字 1，将文本型日期转换为真正的日期。 1010、自动计算年龄 有了出生日期，我们就可以使用 DATEDIF 函数自动计 算年龄。选择单元格 I2，输入下面的公式，就自动得到 员工的实际年龄： =DATEDIF(H2,TODAY(),“Y“) 1111、规范输入入职时间 入职时间是一个非常重要的数据，因为要根据这列日 期计算工龄，分析流动性。由于这列日期要手工输入， 就必须规范输入的入职时间数据合法有效，也就是要输 入正

11、确格式的日期。选择 J2 单元格，设置数据有效性 ，如下图所示 1212、自动计算本公司工龄 有了入职时间，我们就可以使用 DATEDIF 函数自动计 算本公司工龄。选择单元格 K2，输入下面的公式，就自 动得到员工的本公司工龄： =DATEDIF(J2,TODAY(),“Y“) 1313、保证员工基本信息的完整性 由于 B 列至 K 列是员工的最基本信息，是不能缺少这 些数据的，因此需要保证每个员工基本信息完整不缺。 选择 B2 单元格，把数据有效性的条件修改为 =AND(SUBSTITUTE(B2,“ “,“)=B2,COUNTA($B1:$K 1)=10) 也就是增加了一个条件 COUN

12、TA($B1:$K1)=10，它用来 判断上一行的 B 列至 K 列的数据是否都完整了（共有 10 列数据） 1414、规范输入离职时间 离职时间是一个非常重要的数据，因为要根据这列日 期来分析离职。由于这列日期要手工输入，就必须规范 输入的离职时间数据，也就是要输入正确格式的日期。 选择 L2 单元格，设置日期数据的有效性。 1515、规范输入离职原因 离职原因用来分析员工的流动性和离职状态，因此必 须规范离职原因的表述文字。假如企业对离职原因的描 述是下述的文字： 合同到期但个人不愿续签 合同到期但公司不愿续签 因个人原因辞职 因公司原因辞职 违反公司规定辞退 生产任务变化辞退 考核不合要

13、求辞退 退休 死亡 其他 那么就可以使用有效性来快速准确输入这些描述文字 。 1616、创建表格，自动复制有效性和公式，并自动美 化表格 前面我们做的数据有效性、设置公式等，都是仅仅设 置了第 2 行的单元格，并没有选择整列来做，因为这样 的话会使文件变得很大，复制很多公式也是很不方便。 Excel 提供了表格功能，不仅可以自动复制有效性和公 式，还可以自动美化表格。 先在第 1 行输入第一个人的信息，然后单击数据区域 的任意单元格，在单击“插入”选项卡里的“表”命令 （Excel 2007）或“表格”命令（Excel 2010），如图 2-20 所示，即可把普通的数据区域变成了一个表格，然 后再在“设计”选项卡中的“表格样式”选择一个自己 喜欢的样式，就可以把表格自动美化。 这样，我们就得到了一个标准化规范化的员工信息管 理表格，在此表格基础上，就可以建立各种自动化分析 模版了，比如员工属性分析模版，员工流动性分析模版 ，等等。 下图是员工信息基础表的样子： 下面的三个表分别是基于此基本信息表格数据制作 的员工属性分析报告、员工流动性分析报告、以及离职 分析报告。这 4 张工作表，就构成了员工信息统计分析 模版。