VLOOKUP函数的多列返回应用、OFFSET引用函数讲解、INDIRECT间接引用函数、数据验证制作二级菜单、文本提取函数
VLOOKUP函数的多列返回应用:
ROW概念:显示所选单元格的首行行号,=ROW(),如果括号里什么都不写,就是返回目前单元格的行号,如果括号里引用该值,就会返回该值的行号
COLUMN概念:显示所选单元格的首列列号,=COLUMN(),如果括号里什么都不写,就是返回目前单元格的列号,如果括号里引用该值,就会返回该值的列号
先求出订货时间,=VLOOKUP(K9,C:I,2,0),张力的位置是K9,选中的是C:I,保证第1列是联系人,订货时间在第2列,输入2,精确查找,求出订货时间后往右拖,生成一行值,但是值明显是不对的
货物代码的值对应的是:=VLOOKUP(L9,D:J,2,0),J列已经在表格外面了,所以不对,把右侧表格标上列号,联系人是第1列,后面的就是2-7列,用=COLUMN()显示所选单元格的首列列号是12,往右拖,依次是12-17列,发现规律了,COLUMN()-10=2-7列
把公式=VLOOKUP(K9,C:I,2,0)修改一下,K9->$K9,保证K列不变,因为往下拖9会发生变化,C:I->$C:$I,2->COLUMN()-10,修改后的公式:=VLOOKUP($K9,$C:$I,COLUMN()-10,0),回车,查出订货日期,再往右拖,这下值都对了,数量的格式是日期,改成常规就可以了,如下图:
往下拖,都出来了,没有问题,都检查过了,和左侧的大表格能对上,如下图:
这种情况是比较幸运的,如果去掉数量和联系电话这两列,剩下的数据就不对了,清空所有数据,只保留张力的订货时间,相应的列数也发生变化,MATCH函数派上用场了,=MATCH(L8,$C$1:$I$1,0),算出订货时间是2,往右拖,2,3,5,7,和上面的列号一样
把=VLOOKUP($K9,$C:$I,COLUMN()-10,0)里的COLUMN()-10,替换成MATCH(L8,$C$1:$I$1,0),就是=VLOOKUP($K9,$C:$I,MATCH(L8,$C$1:$I$1,0),0),回车,算出来订货时间,往右拖没有问题,往下拖报错了,如下图:
看一下报错的,把L8改成L$8,往右拖和往下拖都正常了,如下图:
OFFSET引用函数讲解:
概念:选择一个参考点,引用指定偏移方向与数量的内容
用法:=OFFSET(参考点,偏移行数,偏移列数),参考点是1(就是D15),偏移行数是3,偏移列数是2,就定位到了星期四,如下图:
记账功能讲解,每增加一笔就记录最近日期的消费情况
随着记账的增多,这个公式已经不能满足需要,这三个参数只有第二个变化,用这个函数COUNTA(A:A),数一下所选区域中非空值单元格的个数,增加一个记账这个行数就会变化,如果COUNTA(A:A)-1就会和上面的7能对上,所以替换成=OFFSET(A1,COUNTA(A:A)-1,0),就可以满足了
左侧增加一笔记账,右侧就会自动更新,这三列不能有空单元格,如果没有消费可以写0
函数跨工作表书写方式:
然后新增一个sheet页,在任一个单元格里输入1234,选中该单元格,看到=Sheet1!F7,如下图:
点击回车,在前面的工作表里的输入=号的单元格出现了1234,选中1234,可以看到=Sheet1!F7,同理我也可以在新增的sheet页里输入一个=,在前面的工作表里点击一个有值的单元格,点击回车,在新增sheet页里输入=号的单元格里出现了刚才选中的值
把记账工作表里的右侧三列复制到新增sheet页里,出现如下图:
选中第一个#REF!,将其公式删除,只保留=OFFSET(,回到记账工作表里可以看到=OFFSET(记账!,把这个公式补全,补全后是=OFFSET(记账!A1,COUNTA(记账!A:A)-1,0),回车,跳转到新增sheet页,最近日期的值变成了2024/12/21,往右拖,把金额转换一下,如下图:
INDIRECT间接引用函数:
概念:引用单元格名称,显示其真实内容
用法:=INDIRECT(单元格名称)
直接引用就是在一个单元格里输入内容,在另一个单元格里输入=号,然后选中刚才的单元格,点击回车,就引用了刚才输入的内容;第二种就是在单元格里输入=号+单元格名称,如=d15,回车,就引用了刚才输入的内容
人员季度考核:
在人员季度考核sheet页里一月份的销售成本的单元格里输入=VLOOKUP("王璐",),点击一月份sheet页,选中单元格所有区域,正好第一列就是销售人员,这里要绝对引用,输入$,销售成本是第2列,输入2,精确匹配输入0,所以公式就是=VLOOKUP("王璐",一月份!$A$1:$D$10,2,0),点击回车,销售成本35000就出来了,往右拖发现都是35000,这肯定是不对的,发现右侧的出售金额和利润也是第2列,这里用到match函数,match是查找函数,查找人员季度考核表里的C5,就是销售成本,所在行就选择一月份sheet页里的表头A1:D1,精确查找,公式输入=MATCH(C5,一月份!$A$1:$D$1,0),回车就返回2,往右拖,3和4也出来了,于是替换一下,把2换成match函数,替换后是=VLOOKUP("王璐",一月份!$A$1:$D$10,MATCH(C5,一月份!$A$1:$D$10,0),0),回车,再往右拖,出售金额和利润也正确了,如下图:
横向没有问题了,纵向往下拖就出问题了,首先C5要转成C$5,一月份也不对,把一月份换成B6,这里要引入INDIRECT函数,把一月份!$A$1:$D$10换成INDIRECT(B6&"!$A$1:$D$10"),把match函数里的一月份!$A$1:$D$1换成INDIRECT(B6&"!$A$1:$D$1"),于是公式修改成:=VLOOKUP("王璐",INDIRECT(B6&"!$A$1:$D$10"),MATCH(C$5,INDIRECT(B6&"!$A$1:$D$10"),0),0),纵向往下拖没有问题,但是横向又不对了,要把B列锁定,修改成$B6,于是公式修改成:=VLOOKUP("王璐",INDIRECT($B6&"!$A$1:$D$10"),MATCH(C$5,INDIRECT($B6&"!$A$1:$D$1"),0),0),回车显示35000,横向拖没有问题,纵向拖也正常,填充整个表格也正常,如下图:
数据验证制作二级菜单:
定义名称:选中一个单元格,如E15,点击公式->定义名称,弹出新建名称对话框,如下图:
输入名称,点击确定按钮,在左上角能看到输入的名称,点击下拉按钮,可以看到输入的名称,如下图:
如果这些名称不想要了,点击公式->名称管理器,选中一条或者多条,点击删除按钮,该名称被删除,点击关闭按钮
数据验证序列:在地区二级菜单的sheet页里,选中每个省份下的所有城市,点击定义名称,输入省份的名称,点击确定按钮,就能看到省份的名称,选择一个空白列,点击数据->数据验证,点击数据验证,在弹出的数据验证对话框里,在设置->允许下选择序列,在来源里选择如下图:
点击确定按钮,在空白列边上看到一个倒三角,单元格的值可以从下拉框里选择,如下图:
把这四个省份都选择一下,在一个省份的右边选择一个空白的单元格,点击数据->数据验证,点击数据验证,在弹出的数据验证对话框里,在设置->允许下选择序列,在来源里输入=INDIRECT(H1),H1就是河北省的单元格名称,点击确定按钮,在空白的单元格右边出现一个倒三角,显示河北省的所有城市,如下图:
同理其他省份,也相同的操作,就能看到每个省份下面所有的城市了
文本提取函数:
LEFT、RIGHT、MID,取左边、右边和中间,如下图:
LEFT函数传入一个文本,然后输入3,就是取左边3个字符,汉字、数字、特殊字符以及空格,1个就代表1位,同理RIGHT函数也是一样的用法,如下图:
MID函数用法如下,第1个参数传一个文本,第2个参数传从第几位开始,最后一个参数是开始后的长度,如从第4位开始,取2位,就是如下:
上面是姓名长度一样的,如果姓名长度不一样,就不能用RIGHT了,还是用MID函数,如下图:
从第6位开始,提取的长度是10,只要大于2就行,这样就能提取对了