换公司啦,职位也由玩法/系统策划改为了数值策划。专门出一篇来纪录日常会用到的Excel常用函数及其功能。
Vlookup函数
VLOOKUP函数是Excel中的一个纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数。

Vlookup函数中一共有四个值:查找值、数据表、序列数、匹配条件。Vlookup函数的应用场景一般为:有一个数据量较大的表格,数据表的第一列对应着表内的很多项信息。要找到第一列的某个值对应表内的某个数值。
查找值为数据表第一列的某个值,数据表就是整个大的表格,序列数为数据表的某一列(一般不会是第一列)。
匹配条件为是否是模糊查找,即函数 VLOOKUP 查找时是精确匹配,还是近似匹配。如果为FALSE或0 ,则返回精确匹配,如果找不到,则返回错误值 #N/A。如果匹配条件为TRUE或1,函数 VLOOKUP 将查找近似匹配值,也就是说,如果找不到精确匹配值,则返回小于 匹配条件的最大数值。如果匹配条件省略,则默认为模糊匹配。匹配条件最好是明确指定,默认是模糊匹配!
举例:某公司新来了10名员工,入职登记表上有编号、员工姓名、年龄、职位、性别、薪酬等信息。现在要把全部员工的薪酬信息引用出来,列成薪酬表。并且保证入职登记表上的薪酬改动时,薪酬表上的薪酬也会改动。

在薪酬表上的“薪酬”一列使用Vlookup函数。以编号1为例:
查找值为A3,即编号1。数据表为$A$3:$F$12,即整个A3到F12的区域。加“$”表明该引用为绝对引用,下拉时不会改变引用区域。序列数为6,即整个表的第6列,即“薪酬”列。匹配条件填FALSE,即为精确匹配。填完编号1的员工后,下拉到编号10即可。
CONCATENATE函数
功能:把多个字符文本或数值连接在一起,实现合并的功能。一般可用于若一个格内同时存在文字和引用的数字。可先将文字和引用数字分别放在不同格内,然后用函数将这几个字符串合在一起。


如何利用公式实现111222333444...一直顺延下去(连续出现n个数字)


在起始E1填入“1”。第二格E2开始写入公式。
1.COUNT($E$1:E1),计算所写公式格数到E1的距离。下拉后会变成“123456789”。

2.在COUNT函数外嵌套MOD函数。MOD(COUNT($E$1:E1),3),计算这些距离除以3所得的余数。下拉后形成120120120不停重复。

3.在MOD函数外嵌套IF函数,判断余数是否为0,若为0,则给上一格值+1后再赋予本格,不为0则直接将上一格值赋予本格。下拉后就形成了111222333......
IF(MOD(COUNT($E$1:E1),3)=0,E1+1,E1)

若想连续重复n次,则MOD函数中的除数为n即可。
MOD函数
MOD函数是一个求余函数,即时两个数值表达式做除法运算后的余数。最后得到的值即为数值除以除数得到的余数。

利用刚刚的111222333......数列,横向一行一行引用表格
现在有一个表格,有三列,需要横向引用。引用完第一行后,继续引用第二行。
先利用刚刚的公式拉一个辅助列。辅助列的格式为111222333...同一个数字连续重复n次,n取决于表格有几列。

辅助列第一行,即第一个“1”,用INDEX函数。数组为第一列,行序数为E1,即第一个“1”。注意数组对第一列的引用为绝对引用。

辅助列第二行,即第二个“1”,用INDEX函数。数组为第二列,行序数为E2,即第二个“1”。

辅助列第三行,即第三个“1”,用INDEX函数。数组为第三列,行序数为E3,即第三个“1”。

把第一行的各个列引用完后,下拉,即可获得横向引用表格。
INDEX函数
INDEX函数就是引用表格中的某个值。

数组:要引用的区域。行序数:要引用的行数。列序数:要引用的列数。

上图的例1中,数组为B1到B12的一列绝对引用,行序数为E2,E2的值为1,即B1-B12列的第一行。因为只有一列,所以列序数不用填。
网友评论