3.1 员工信息表设计维护

员工信息表是每个公司都需要建立的一项基础信息表，每位HR的工作都与此表息息相关。完善且科学的员工信息表，对HR快速准确地筛选、汇总、分析各项人事数据有很大的帮助；同时还可以为公司建立提供统一的员工姓名和标识，以便于数据快速连接与数据分析。

3.1.1 员工信息表结构设计

设计表格时要先进行分析，盲目操作不仅浪费时间，还得不到理想的结果。一般员工信息表的工作会由人事专员负责，人事专员要全面地考虑各岗位、各项工作对员工信息表的数据需求，在设计前要将自己的思路、想法和上级领导沟通，以保证设计的表格符合需求。

设计员工信息表，需要考虑以下情况：

（1）需要提供哪些数据？如员工基本信息、员工学历信息、员工合同信息、员工部门岗位信息、员工离职信息，将需要的信息项全部列出来，例如需要的项目有：姓名、性别、出生日期、年龄、身份证号、部门、岗位、学历、入职时间、转正时间、签订劳动合同时间（含多次）、劳动合同到期时间（含多次）、工龄、离职时间、离职原因等。

（2）各信息项明细列出之后，要考虑这些能否满足工作需求？如每月有庆生会需提供生日信息、员工转正面谈需提供拟转正日期、员工劳动合同到期需提供到期日期、员工离职有离职日期和离职原因、快速统计指定日期内的离职率，能从部门、岗位、性别、学历、工龄、年龄等方面对在职和离职员工进行多维分析等。

（3）能否实现多人对此表进行数据维护并保证格式和录入信息的统一，如用【数据验证】限定输入。

（4）员工有重名的情况如何能快速区分；和其他岗位的数据如何实现衔接，如在一张表中统计员工基本信息和薪酬专员工作中的员工年收入。可以考虑用员工编号（工号）作为唯一的检索号，实现各岗位对员工各项数据（基本信息、工资数据、培训信息等）的快速统计。

为了保证设计出来的员工信息表能满足工作需求，人事专员不能闭门造车，可以请部门其他人员甚至其他部门人员对此表提出工作或数据需求，来检验员工信息表能否满足。

工作做到这一步，还不能进行表格设计，而是需要进行更深入的分析：

（1）在HR日常操作中，员工入职日期即为公司和员工建立劳动关系的日期，也就是劳动合同生效时间。多次签订劳动合同的，劳动合同到期后下一份合同随即生效，而且自2008年1月1日《中华人民共和国劳动合同法》正式实施以后，其中明确规定“……劳动者提出或者同意续订、订立劳动合同的，除劳动者提出订立固定期限劳动合同外，应当订立无固定期限劳动合同……”，所以2008年以后，一般情况下除非劳动者提出，否则公司与员工的劳动合同签订三次即可。基于此情况，用“入职时间”“第一次劳动合同到期时间”“第二次劳动合同到期时间”三个项目就可以将员工的入职时间、第一次劳动合同起止时间、第二次劳动合同起止时间和第三次劳动合同生效时间表示出来。

（2）“出生日期”和“性别”可以从员工身份证中提取，这两列可设计公式自动提取。

（3）为实现部门、岗位、学历、离职原因等项目的统一输入，可用【数据验证】设置下拉菜单。

（4）需要输入时间的项目，可以用【数据验证】功能保证时间格式的统一。

根据上述情况，建立员工信息表如下：

STEP ① 新建Excel工作簿并命名为“员工信息管理”；

STEP ② 双击工作表Sheet1，重命名为“员工信息表”并单击【Enter】键确定；

STEP ③ 将各项目输入工作表A1:N1区域，最终如图3-1所示。

3.1.2 快速输入员工编号

员工编号的作用很多，例如作为员工工号、员工档案号等，它还可以在全公司中作为唯一检索号，实现员工所有信息的顺利衔接。在员工信息表中，它是唯一的，主要功能可以区分重名的情况。

我们可以采用“公司标识+顺序号”编码规则来设定员工编号，如公司简称“GT”，顺序号以“00001”开始，那么员工编号为“GT00001”。

员工编号根据员工入职的时间顺序依次递增，工作表第二行增加1名员工信息时，可以在A2单元格输入“GT00001”，第三行增加员工信息时，选中A2单元格，用鼠标向下填充序列即可。

在实际工作中，员工信息表数据的输入维护可能不是一个人，为了防止员工编号的错误输入、重复输入，可以用【数据验证】来避免一些错误、重复的输入。

在设定【数据验证】前，首先要分析，限制哪些输入防止员工编号输错、重复：

（1）员工编号必须为7位。

（2）员工编号是唯一的。

（3）员工编号前两位是“GT”，后五位可以转换为数值格式。

下面进行【数据验证】的设定：

STEP ① 假定员工人数不超过1 000人，选中A2:A1000单元格，在【数据】选项卡【数据工具】功能区单击【数据验证】，弹出【数据验证】窗口，如图3-2所示。

STEP ② 在【允许】下拉列表中选择【自定义】选项，在【公式】文本框中输入公式，如图3-3所示。

=AND(LEN(A2)=7,LEFT(A2,2)="GT",ISNUMBER(-RIGHT(A2,5)),COUNTIF($A$2:$A$1000,A2)=1)

STEP ③ 单击【出错警告】选项卡，在【错误信息】文本框中输入“请检查员工编号是否为7位，是否是唯一的，编码规则是不是按‘GT+00001’进行编码”，单击【确定】按钮，如图3-4所示。

【出错警告】的设置是有错误或重复的员工编号输入时，弹出对话框进行提示，如图3-5所示。

知识点讲解

1．“&”连字符

连字符是将“&”前后的数据连接起来，如图3-6所示。

2．相对引用、绝对引用和混合引用

在学习函数过程中，必须先把绝对引用、相对引用和混合引用理解透彻，因为在应用函数过程中，会有所涉及。

（1）相对引用。如A1，指行号和列标前面都没有符号“$”。如果用相对引用，无论是向上、向下、向左、向右拉公式，所引用的单元格都是相对应变化的。

（2）绝对引用。如$A$1，指行号和列标前面都有符号“$”。绝对引用是无论向上、向下、向左、向右拉公式，所引用的单元格都是无变化的。

（3）混合引用。如$A1和A$1。$A1表示的是绝对列引用，是指无论向上、向下、向左、向右拉公式，所引用的单元格列不变化，只有行变化。A$1表示的是绝对行引用，是指无论向上、向下、向左、向右拉公式，所引用的单元格行不变化，只有列变化。

在了解绝对引用、相对引用和混合引用后，也要了解【F4】键在函数引用中的作用，引用单元格或单元区域，是相对引用，按一下【F4】键是绝对引用，按两下【F4】键是绝对行引用，按三下【F4】键是绝对列引用……依此类推。

如图3-7所示，A列和B列是原数据，在C2、E2、G2、I2四个单元格分别输入=A2、=$A$2、=$A2、=A$2，然后向右、向下拉公式。

3．TEXT( )函数

函数含义及说明

TEXT( )函数可将数值转换为文本，并可使用户通过使用特殊格式字符串来指定显示格式。需要以可读性更高的格式显示数字或需要合并数字、文本或符号时，此函数很有用。

语法及说明

TEXT(value,format_text)

value，数值、计算结果为数值的公式，或对包含数值的单元格的引用。

format_text，使用双引号括起来作为文本字符串的数字格式，例如，"m/d/yyyy"或"#,##0.00"。

语法解释

TEXT（数据，想要数据显示的格式）

示例

TEXT( )函数的功能很多，在本小节中函数所表达的意思如图3-8所示。

4．ROW( )函数

函数含义及说明

返回引用的行号。

语法及说明

ROW([reference])

需要得到其行号的单元格或单元格区域。

示例

如图3-9所示。

5．AND( )函数

函数含义及说明

是指检测所有的条件是否为真。

语法及说明

AND(logical1,[logical2],……)

这个函数用来判断所有条件是否都正确。如果都正确，AND( )函数的结果为真（TRUE）；如果所有的条件有一个或者几个不是正确的，AND( )函数的结果就为假（FALSE）。

语法解释

AND（条件1,条件2,……）

示例

如图3-10所示。

6．LEN( )函数

函数含义及说明

返回文本字符串中的字符数。

语法及说明

LEN(text)

计算单元格里有多少个字符，1个数字、1个汉字、1个字母、1个标点符号、1个空格等都看作1个字符。

示例

如图3-11所示。

7．LEFT( )函数

函数含义及说明

根据所指定的字符数，LEFT( )返回文本字符串中第一个字符或前几个字符。用LEFT( )函数返回的字符为文本格式。

语法及说明

LEFT(text, [num_chars])

text包含要提取的字符的文本字符串。

num_chars指定要由LEFT( )函数提取的字符的数量。

示例

如图3-12所示。

8．RIGHT( )函数

函数含义及说明

RIGHT( )函数根据所指定的字符数返回文本字符串中最后一个或多个字符。RIGHT( )函数与LEFT( )函数正好相反，LEFT( )函数是正序返回字符，RIGHT( )函数是倒序返回字符。用RIGHT( )函数返回的字符为文本格式。

函数含义及说明

RIGHT(text, [num_chars])

text包含要提取的字符的文本字符串。

num_chars指定由RIGHT( )提取的字符的数量。

示例

如图3-13所示。

9．ISNUMBER( )函数

函数含义及说明

判断引用的参数或指定单元格中的值是否为数值，正确返回TRUE，否则返回FALSE。

语法

ISNUMBER(value)

示例

如图3-14所示。

10．COUNTIF( )函数

函数含义及说明

计算区域中满足给定条件的单元格的个数。

语法及说明

COUNTIF(range, criteria)

range，要对其进行计数的一个或多个单元格，其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。

criteria，用于定义将对哪些单元格进行计数的数字、表达式、单元格引用或文本字符串。

语法解释

COUNTIF（查找要找的数据区域，要找的内容）

示例

如图3-15所示。

11．函数嵌套

函数嵌套是指一个函数作为另外一个函数的参数出现。如ISNUMBER(--RIGHT(A2,5))，在这个函数里面，--RIGHT(A2,5)既是一个公式，同时它也是ISNUMBER函数的一个参数。在应用函数的过程中，函数嵌套是很常见的，复杂的公式都是由各个简单的函数嵌套而来。

小技巧

公式中将文本型数字转换为数值型数字的技巧

用文本函数如LEFT( )函数、RIGHT( )函数、MID( )函数等提取出来的数字为文本格式，如果要这些文本格式的数字参与运算，必须将它们转换为数值格式，转换的方法是用“--”、“1”、“-0”、“+0”、value函数等。

“--”是两个减号，先用一个减号将文本型数字转换为负数，再用一个减号将负数转换为正数。value函数是将表示文本型数字转换为数值格式，这里不再做介绍。

在本节讲解的公式中，将“ISNUMBER(--RIGHT(A2,5))”换为ISNUMBER(RIGHT(A 2,5) 1 )、ISNUMBER(RIGHT(A 2,5)+0)、ISNUMBER(RIGHT(A2,5)-0)、ISNUMBER(VALUE(RIGHT(A2,5)))都是可以的。

3.1.3 限制空格输入

在第2章中，讲解了不规范操作中有空格的应用，为了防止在输入姓名时有意或无意输入空格的情况，可以设定【数据验证】限制空格的输入。

STEP ① 假定员工人数不超过1 000人，选中B2:B1000单元格，在【数据】选项卡【数据工具】功能区单击【数据验证】按钮，弹出【数据验证】对话框。

STEP ② 在【允许】下拉列表中选择【自定义】选项，在【公式】文本框中输入公式“=SUBSTITUTE(B2," ","")=B2”，如图3-16所示。

STEP ③ 单击【出错警告】选项卡，在【错误信息】文本框中输入“请检查输入姓名过程中是否输入了空格。”，如图3-17所示。

知识点讲解

SUBSTITUTE( )函数

函数含义及说明

对指定的字符串进行替换。

语法及说明

SUBSTITUTE(text,old_text,new_text,[instance_num])

text，需要替换其中字符的文本，或者对含有文本（需要替换其中字符）的单元格的引用。

old_text，需要替换的旧文本。

new_text，用于替换old_text的文本。

instance_num是可选的。用来指定要以new_text替换第几次出现的old_text。如果指定了instance_num，则只有满足要求的old_text被替换；否则会将text中出现的每一处old_text都更改为new_text。

语法解释

SUBSTITUTE（需要进行替换的数据，被替换的文本，需要替换成的文本，第几次出现的被替换的字符）

示例

如图3-18所示。

3.1.4 身份证号输入设定

身份证号输入需要一定的技巧，如果不进行设定直接输入，例如输入身份证号“130183198301012210”，就会显示“1.30183E+17”，在编辑栏中会发现显示“130183198301191000”，如图3-19所示。这是因为在Excel中输入数字超过了12位，会自动转换为科学记数格式，如果输入超出了15位，会自动将15位以后的数字转换为“0”。

要想输入正确的身份证号，则需要将身份证号转换为文本格式，有以下两种方法可以进行转换。

方法1：在输入身份证号前，先在单元格中输入一个英文格式的单引号“’”，然后输入身份证号，就可以显示正确的身份证号。

方法2：在【开始】选项卡【数字】功能区单击下拉按钮，选择【文本】选项，再输入身份证号同样可以显示正确的身份证号。

为了防止身份证号输入位数错误或者重复输入，可以设定【数据验证】防止错误或重复身份证号的输入。

和前面小节讲的操作步骤一样，弹出【数据验证】对话框后，在【允许】下拉列表中选择【自定义】选项，在【公式】文本框中输入公式：

=AND(LEN(G2)=18, COUNTIF($G$2:$G$1000,G2&"")=1)

在【错误信息】文本框中输入“请检查身份证号是否18位，是否前面已输入。”，操作步骤如图3-20、图3-21所示。

3.1.5 从身份证号中提取出生日期及性别

在设计表格前已经分析过，出生日期和性别从身份证号中提取，身份证号第7～14位表示出生年、月、日，第17位表示性别，单数为男性，双数为女性。设置步骤如下。

1 出生日期提取

在F2单元格中输入公式，公式可以根据输入的身份证号逐条信息下拉。

=IF(G2="","",--TEXT(MID(G2,7,8), "#-00-00"))

2 性别提取

在E2单元格中输入公式，公式可以根据输入的身份证号逐条信息下拉。

=IF(G2="","",IF(MOD(MID(G2,17,1),2),"男","女"))

知识点讲解

1．MID( )函数

函数含义及说明

返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

语法及说明

MID(text,start_num,num_chars)

text，包含要提取字符的文本字符串。

start_num，文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，依此类推。

num_chars，指定希望MID( )函数从文本中返回字符的个数。

语法解释

MID（需要提取字符的数据，提取第一个字符的位置，提取几个字符）

示例

如图3-22所示。

2．MOD( )函数

函数含义及说明

返回两数相除的余数。结果的正负号与除数相同。

语法及说明

MOD(number, divisor)

number为被除数，divisor为除数。

语法解释

MOD(被除数，除数)

示例

如图3-23所示。

3．TRUE、FALSE

在Excel中，TRUE是对的、真的意思，代表条件为真；FALSE是假的、错的意思，代表条件为假。在运算中，TRUE=1，FALSE=0。

4．IF( )函数

函数含义及说明

根据对指定的条件计算结果为TRUE或FALSE，返回不同的结果。

语法及说明

IF(logical_test,[value_if_true],[value_if_false])

logical_test，计算结果可能为TRUE或FALSE的任意值或表达式。

value_if_true是可选的，logical_test参数的计算结果为TRUE时所要返回的值。

value_if_false是可选的。logical_test参数的计算结果为FALSE时所要返回的值。

语法解释

IF（条件，条件成立时返回的结果，条件不成立时返回的结果）

示例

如图3-24所示。

小技巧

【F9】键的应用

在编辑公式后，如果想知道公式的某一部分运算结果，可以用鼠标左键抹黑相应的部分，再按【F9】键，就会看到运算结果。例如公式“=IF(G2="","",--TEXT(MID(G2,7,8),"#-00-00"))”，将“MID(G2,7,8)”部分用鼠标左键抹黑，按【F9】键，就会看到结果为“=IF(G2="","",--TEXT("19830101","#-00-00"))”，如果想返回，按【Esc】键即可。

3.1.6 日期输入规范

“入职时间”“转正时间”“第一次劳动合同到期时间”“第二次劳动合同到期时间”“离职时间”等几项都是需要手动输入日期的，为了确保日期格式等输入的准确性，在设计前也进行了分析，用【数据验证】来进行限定日期格式。

需要注意的是，虽然【数据验证】能限定输入数据的格式等，但是【数据验证】不是万能的，还要保证数据录入人员在录入数据过程中要细心，否则有些错误【数据验证】是控制不了的。

例如入职时间为“2015-1-10”，由于不小心输入“2015-11-10”，这一类的错误包括其他由于不小心造成的错误，用【数据验证】是发现不了的。所有对这几项日期的【数据验证】设置，只限定输入的日期格式和时间范围，其他的将不再做限定，步骤如下：

STEP ① 选中I2:M1000数据区域，在【数据】选项卡【数据工具】功能区单击【数据验证】按钮，弹出【数据验证】对话框。

STEP ② 在【允许】下拉列表中选择【日期】，数据为【介于】，在【开始日期】和【结束日期】两个文本框中分别输入“2008-1-1”和“2099-12-31”，如图3-26所示。因为在员工信息表中，员工最早入职日期都晚于“2008-1-1”，所以将开始日期设置为“2008-1-1”。

STEP ③ 单击【出错警告】选项卡，在【错误信息】文本框中输入“请检查输入日期是否符合‘YYYY-M-D’格式，是否在2008-1-1至2099-12-31日期范围内。”，单击【确定】按钮，如图3-27所示。

小技巧

当前日期快速输入

员工信息表中，如果及时输入信息，“入职日期”“转正日期”等可以用快捷键【Ctrl+;】来输入。这个快捷键就是快速输入当前日期，可以节省一些输入的时间。

3.1. 7 利用序列实现统一录入

为了实现“学历”和“离职原因”的统一输入，便于数据统计分析，可以用【数据验证】来实现。

1 “学历”【数据验证】设置

STEP ① 在“员工信息管理”工作簿中新建名为“序列”的工作表。

STEP ② 在“序列”工作表中A1单元格输入标题“学历”，A2～A8单元格分别输入“博士、硕士、本科、专科、中专、高中、初中”（这些数据可以在“序列”工作表中任意地方输入，不限A列），如图3-28所示。

STEP ③ 选中A2:A8数据区域，在【公式】选项卡【定义的名称】功能区单击【定义名称】，弹出【新建名称】窗口。在【名称】文本框中输入“学历”，删除【引用位置】文本框内的数据，将光标放在文本框中，选中A2:A8数据区域，如图3-29所示，单击【确定】按钮。

STEP ④ 在“员工信息表”中选中H2:H1000数据区域，调出【数据验证】窗口。在【允许】下拉列表中选择【序列】，在【来源】文本框中输入“=学历”，如图3-30所示。

STEP ⑤ 单击【出错警告】选项卡，在【错误信息】文本框中输入“请在下拉菜单中选择学历，不要输入。”，如图3-31所示，单击【确定】按钮。这样就可以将“学历”列设置成用下拉菜单选择输入的模式。

还有一个比较便捷的设置方法：选中H2:H1000数据区域，弹出【数据验证】对话框，在【允许】下拉列表中选择【序列】，在【来源】文本框中输入“博士，硕士，本科，专科，中专，高中，初中”（各学历之间的标点符号为英文格式下的逗号），如图3-32所示。【出错警告】设置同上，可以得到一样的结果。

2 “离职原因”【数据验证】设置

STEP ① 在“序列”工作表中，C1单元格输入标题“离职原因”，C2～C9单元格分别输入不同的离职原因（这些数据可以在“序列”工作表中任意地方输入，不限C列），如图3-33所示。

STEP ② 选中C1:C9数据区域，在【公式】选项卡【定义的名称】功能区单击【根据所选内容创建】，在弹出的窗口中将【首行】勾选，单击【确定】按钮，如图3-34所示。

STEP ③ 在“员工信息表”中选中N2:N1000数据区域，调出【数据验证】窗口，在【允许】下拉列表中选择【序列】，在【来源】文本框中输入“=离职原因”，如图3-35所示，单击【出错警告】选项卡，在【错误信息】文本框中输入“请在下拉菜单中选择离职原因，不要输入。”，单击【确定】按钮，如图3-36所示。

知识点讲解

定义名称

定义名称在Excel帮助里面的解释说明是：使用名称（名称：在Excel 中代表单元格、单元格区域、公式或常量值的单词或字符串）可使公式更加容易理解和维护。读者可根据工作需要为单元格区域、函数、常量或表格定义名称。一旦采用了在工作簿中使用名称的做法，便可轻松地更新、审核和管理这些名称。

定义名称后，在之后编写公式或者引用时，可以很方便地用所定义的名称进行编写，方便、快捷地解答。它有很多好处，例如快速的多次引用、缩短公式的长度、突破公式嵌套层级限制、可用于图表的系列公式、减小文件的大小等。

3.1.8 二级联动下拉菜单设置

在上一小节中，我们讲解了下拉菜单的设置，在员工信息表中“部门”和“岗位”两列同样可以进行设置。但在一个公司中如果岗位很多的话，用【数据验证】设置下拉菜单后，在下拉菜单中选择岗位就需要花费很长时间。

有没有更方便的方法既可以减少输入的工作量又能保证输入的一致性呢？这就涉及二级联动下拉菜单的设置，选定相应部门后，在“岗位”一列对应的单元格中，只出现选中部门中的岗位。设置如下：

STEP ① 在“序列”工作表中，输入部门和对应的各岗位名称，如图3-37所示。

STEP ② 选中E1:E11数据区域，调出【新建名称】对话框，在【名称】文本框中输入“部门名称”，单击【确定】按钮，如图3-38所示。

STEP ③ 选中E1:M11数据区域，在【开始】选项卡【编辑】功能区【查找和选择】下拉菜单中单击【常量】按钮，如图3-39所示。

STEP ④ 上一步骤完成后不要在工作表单元格区域单击或击打键盘，在【公式】选项卡【定义的名称】功能区单击【根据所选内容创建】，在窗口中勾选【最左列】，单击【确定】按钮，如图3-40所示。

STEP ⑤ 选中C2:C1000数据区域，弹出【数据验证】窗口，在【允许】下拉列表中选择【序列】，在【来源】文本框中输入“=部门名称”，如图3-41所示。单击【出错警告】选项卡，在【错误信息】文本框中输入“请在下拉菜单中选择部门，不要输入。”，单击【确定】按钮，如图3-42所示。

STEP ⑥ 选中D2:D1000数据区域，弹出【数据验证】对话框，在【允许】下拉列表中选择【序列】，在【来源】文本框内输入“=INDIRECT(C2)”，如图3-43所示。单击【出错警告】选项卡，在【错误信息】文本框中输入“请在下拉菜单中选择岗位，不要输入。”，单击【确定】按钮，如图3-44所示。

设置完成后当选择不同的部门时，对应的“岗位”单元格中会出现这个部门的岗位名称。公司在发展过程中，组织架构会发生变化，同样岗位也会增减，每次组织架构发生变化或岗位出现增减时，都需要重新设置一下【数据验证】。为了能实现一劳永逸，可以在【数据验证】中设置一些公式，当组织架构发生变化或岗位出现增减情况时，Excel会自动调整数据区域范围。

STEP ① 和上述步骤相同，不再赘述。

STEP ② 打开【新建名称】对话框，在【名称】文本框中输入“部门”，在【引用位置】文本框中输入下列公式，如图3-45所示，单击【确定】按钮。

=OFFSET(序列!$E$1,,,COUNTA(序列!$E:$E),)

STEP ③ 选中C2:C1000数据区域，调出【数据验证】窗口，在【允许】下拉列表中选择【序列】，在【来源】文本框中输入“=部门”，如图3-46所示。单击【出错警告】选项卡，在【错误信息】文本框中输入“请在下拉菜单中选择部门，不要输入。”，单击【确定】按钮，如图3-47所示。

STEP ④ 选中D2:D1000数据区域，调出【数据验证】窗口，在【允许】下拉列表中选择【序列】，在【来源】文本框中输入：

=OFFSET(序列!$E$1,MATCH(C2,序列!$E:$E,0)-1, 1,,COUNTA(OFFSET(序列!$F$1:$AZ$1, MATCH(C2,序列!$E:$E,0)-1,)))

单击【出错警告】选项卡，在【错误信息】文本框中输入“请在下拉菜单中选择岗位，不要输入。”，单击【确定】按钮。

每次组织架构调整或岗位增减，只需调整部门名称或岗位名称，并且保持部门名称和岗位名称中间不要有空格，这样就可以实现二级联动下拉菜单中的内容实时调整。

最后输入数据并美化，效果如图3-48所示。

知识点讲解

1．INDIRECT( )函数

函数含义及说明

返回由文本字符串指定的引用。

语法及说明

INDIRECT(ref_text, [a1])

ref_text，对单元格的引用，此单元格包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对作为文本字符串的单元格的引用。如果ref_text不是合法的单元格的引用，INDIRECT( )函数返回错误值#REF!。

A1是可选。一个逻辑值，用于指定包含在单元格ref_text中的引用的类型。如果a1为TRUE或省略，ref_text被解释为A1样式的引用。如果a1为FALSE，则将ref_text解释为R1C1样式的引用。

示例

如图3-49所示。

2．COUNTA( )函数

函数含义及说明

计算数据区域中不为空的单元格的个数。数据区域可以是一个或多个，也可以是相邻的或者不相邻的。COUNTA( )函数可对包含任何类型信息的单元格进行计数，这些信息包括错误值和空值("")。如果区域包含一个返回空字符串的公式，则COUNTA( )函数会将该值计算在内。COUNTA( )函数不会对空单元格进行计数。

语法及说明

COUNTA(value1, [value2], ...)

value1是必需的，表示要计数的值的第一个参数。

value2，... 是可选的，表示要计数的值的其他参数，最多可包含 255 个参数。

示例

如图3-50所示。

3．MATCH( )函数

函数含义及说明

在数据区域中搜索指定项，然后返回该项在数据区域中的相对位置。

语法及说明

MATCH(lookup_value,lookup_array,[match_type])

lookup_value是必需的，需要在lookup_array中查找的值。lookup_value参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。

lookup_array是必需的，是指要搜索的数据区域。

match_type是可选的，数字-1、0或1。1或者省略代表查找小于或等于指定内容的最大值，而且指定区域必须按升序排列。0代表查找指定内容的第1个数值。-1代表查找大于或等于指定内容的最小值，而且指定区域必须按降序排列。

语法解释

MATCH(要查找的内容，查找的数据区域,-1、0或1)

示例

如图3-51所示。

4．OFFSET( )函数

函数含义及说明

以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，并可以指定返回的行数或列数。

语法及说明

OFFSET(reference, rows, cols, [height], [width])

reference是必需的，作为偏移量参照系的引用区域。reference必须为对单元格或相连单元格区域的引用；否则，OFFSET返回错误值#VALUE!。

rows是必需的。相对于偏移量参照系的左上角单元格，上（下）偏移的行数。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。

cols是必需的。相对于偏移量参照系的左上角单元格，左（右）偏移的列数。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

height是可选的。高度，即所要返回的引用区域的行数。height必须为正数。

width是可选的。宽度，即所要返回的引用区域的列数。width必须为正数。

语法解释

OFFSET（参照单元格或数据区域，向下或向上移动的行数，向左或向右移动的列数，返回几行，返回几列）

示例

如图3-52所示。