技巧一:制作动态下拉菜单栏的偏移函数
案例描述:如上图,当我们重新添加部门时,下拉菜单选项会自动更新。
函数公式:
=OFFSET(G$1,0,0,COUNTA(G:G))
功能说明:
1、偏移函数从G1单元格开始偏移;使用 $ 固定行,这样向下拖动时不会改变;
2、第四个参数选择的范围是counta函数统计文本单元格的个数,得到选择多少行;
技巧二:Countif 函数如何执行不等式计算
案例描述:使用Countif函数计算工龄6年以上的人数
函数公式:
函数=COUNTIF(D:D,">6")=3
功能分析:
countif在进行不等式计算时,需要对不等号和对应的值进行双引号处理。
技巧三:vlookup函数向左查询数据
案例描述:根据员工编号,在左侧找到对应的姓名
函数公式:
=VLOOKUP(G5,IF({1,0},B1:B7,A1:A7),2,0)
功能说明:
vlookup函数向左查询时,需要配合IF函数。
1、这里if函数的作用主要是作为第二个参数。 if函数的第一个参数使用{1, 0}进行数据判断,从而形成一个新的二维数据区。我们选择IF函数,按F9分析下图:
2、有了上面的IF字母,我们就可以组成一个新的数据区了。当我们找到对应的第一个参数job number后,向右查询2位即可找到对应的名字。
技巧四:如何去除vlookup函数查询到的错误值?
答:可以使用IFerror函数来替换错误值。如下所示:
技巧五:人员信息登记保证录入数据的唯一性
案例描述:如上图所示,我们在登记人员信息的时候,需要保证工号的唯一性。 例如,当我们重复输入工号GD06\GD07时,表单会自动提示我们不能输入。
步骤:
第一步:点击菜单栏:数据——数据有效性;
第二步:点击Data Validation Settings,条件选择Custom,输入公式:=COUNTIF(B:B,B9)=1,这个操作是为了保证B列的数据,每个对应值的个数保证为1。
以上就是今天分享给大家的几个技巧了,快去试一试吧。