EXCEL系列04-数据高效输入

今天我为大家讲解下如何利用函数、数据有效性及条件格式实现数据高效输入。

一、效果展示

效果展示

如上图，可以看到，当我们在姓名列输入姓“黄”或者“杨”的时候，会出现含有“黄”或“杨”的姓名下拉列表，选择姓名后，序号列会自动生成序号及单元格边框，日期列也会自动生成日期及单元格边框，部门列根据下拉菜单确定后，岗位列会根据部门列生成二级下拉菜单，如当我们选择“工程部”时，岗位列会将工程部的所有岗位列表作为下拉菜单显示。当工资列输入值超过50000时会出现警告。

二、功能讲解

1、条件格式设置。

条件格式

如上图所示，设置区域内单元格为非空值时，有外边框。

2、函数公式。

函数公式

如上图，序号列公式“=IF(B2<>"",ROW(A1),"")”，假设姓名列B2不为空，则返回单元格A1对应的行值。日期列公式“=IF(B2<>"",TODAY(),"")，”假设姓名列B2不为空，则返回日期TODAY。

3、姓名列数据有效性设置

排序

如上图，对表2中需要用到的姓名列表进行升序或降序，这样会让相似内容连续排列。

设置公式

如上图，对所选区域对数据有效性进行设置，选择序列，数据来源引用函数公式“=OFFSET(Sheet2!$A$1,MATCH(B2&"*",Sheet2!$A$2:$A$13,0),0,COUNTIF(Sheet2!$A$2:$A$13,B2&"*"),1)”，这里主要使用OFFSET函数返回包含关键字的数据。OFFSET函数的作用是根据指定的参照单元格，通过给定的偏移量返回新的引用数据。OFFSET（参照系，行偏移量，列偏移量，新引用区域的行数，新引用区域的列数）。

第一参数引用了表2中的A1单元格作为参照系。

第二参数用MATCH(B2&"*",Sheet2!$A$2:$A$13,0)确定行偏移量。MATCH为查找函数，根据查找值B2&”*”(*号是通配符，代表任意不确定字符)在Sheet2!$A$2:$A$13区域中查找，查找方式为0（精确查找）。当B2单元格输入关键字时，该函数将查找出包含关键字的数据在Sheet2!$A$2:$A$13区域中第一次出现的位置。

第三参数为0，因为我们的数据源只有A列一列，所以列偏移量为0，表示不偏移。简单来说，就是offset函数以Sheet2!$A$2单元格为参照，不横向偏移，只向下偏移。

第四参数COUNTIF(Sheet2!$A$2:$A$13,B2&"*")统计A2-A13区域内满足条件B2&"*"，也就是包含B2单元格内的关键词的单元格出现的次数，也就是最终在数据验证下拉菜单中一共会出现几行。

第五参数为新引用区域的列数，因为只有A列一列，所以为1。

如之前动态图所示：offset函数以A1为参照系向下查找，通过match函数在A2-A13中找到包含B2关键字“黄”的数据第一次出现的位置是从表2中单元格A2开始的第6行，再通过countif函数找到总共有2行，最终在下拉菜单中返回这2行1列的数据。

取消勾选

如上图，在“出错警告”选卡中取消勾选，这样输入这样在我们输入时就不会跳出错误提醒。

完成上述设置，姓名列的数据就会实现带搜索功能的下拉菜单。

4、部门及岗位列数据有效性设置

一级菜单

二级菜单

如上图对表2中C列数据组进行命名，命名为“部门”，对E列数据组作为二级菜单进行命名。

一级菜单设置

二级菜单设置

如上图，将表1中部门列的数据有效性设置数据来源为我们之前建立的数据组“=部门”，岗位列的数据有效性设置为“=INDIRECT($C2)”，这样岗位列的数据便成为部门列数据的二级下拉菜单。

5、工资列数据有效性设置

工资数据有效性设置

如上图，将工资列数据有效性设置为“0-50000”，当超出该范围后，会出现警告错误。

总结：利用函数公式及数据有效性不仅可以实现数据的快速录入，还可以提高数据的准确性，我今天教大家的只是一些简单的设置，希望可以起到抛砖引玉的作用，千里之行始于足下！今天就为大家讲解到这里，希望能与大家一起学习成长！