王佩丰老师OfficeExcel 11-12讲 VLOOKUP函数 学习笔记

时间:2024-03-19 12:24:53


视频链接:王佩丰Excel1800分钟****.

VLOOKUP函数

Vlookup函数语法

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

根据姓名查找总分:
王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
H6单元格输入:=VLOOKUP(G6,$B$5:$E$10,4,0)
=VLOOKUP(要找什么,在哪找,返回选中区域的第几列,0为精确查找1为模糊查找)

注意
第二参数要求:必须包含查找列与引用列,可以往后多选,但不能往前多选,要找姓名必须保证姓名在最左侧列;
如果没有选整列,选的是一块区域的话注意绝对引用。

vlookup()函数查找数据,若重复,只能返回重复数据的第一个

跨表格,根据客户ID查找公司名称:
王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
B2单元格:=VLOOKUP(A2,数据源!A:B,2,0)
第二个参数直接点数据源表格选中AB两列,选中以后加逗号(,)再进行下边操作。

vlookup中使用通配符

王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
根据公司名称查找地址:
原表格中无三川实业,但是有三川实业有限公司,找不到会显示#N/A,此时不能使用模糊匹配,要用通配符。
=VLOOKUP(A2&"*",数据源!B:E,4,0)

vlookup模糊查找

直到表格中无这个值,还要找
模糊查找只能找小于等于的近似值
王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
求提成比例:H9输入:=VLOOKUP(G9,$C$8:$D$13,2,1)
提成金额:I9:=G9*H9

使用isna函数处理数字格式引起的错误

王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
根据表格查找处所数量:
表格1:原表格编号为文本,统计列表编号为数值,G4输入:=VLOOKUP(F4&"",$A$2:$C$6,3,0),将统计表格数值强行转换为文本。

表格二:原表格编号为数值,统计列表编号为文本,G12输入:=VLOOKUP(F12*1,$A$10:$C$14,3,0)或者=VLOOKUP(F12+0,$A$10:$C$14,3,0) 或者=VLOOKUP(--F12,$A$10:$C$14,3,0) 将统计表格文本强行转换为数值。

表格三:原表格与统计表格同时有文本和数据类型,G20输入:=IF(ISNA(VLOOKUP(F20*1,$A$18:$C$22,3,0)),VLOOKUP(F20&"",$A$18:$C$22,3,0),VLOOKUP(F20*1,$A$18:$C$22,3,0))
若按照数值找的出现了NA错误(使用isna函数),那就按照文本查找,否则按照数值查找
(一般情况下将表格格式统一为文本或者数值后再操作,不考虑使用此方法)

Hlookup函数

vlookup返回第几列,hlookup返回第几行
王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
C14输入:=HLOOKUP(B14,$1:$3,3,0) 选择区域也为列

计算个人所得税

王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
若一个人的工资为12500,其个税的计算过程:
应纳税金额=12500-起征点3500=9000
9000在4500级别,乘以税率20%,9000*0.2=1800
再减去速扣数1800-555=1245

G7输入:=IF(F7<=3500,0,VLOOKUP(F7-3500,$A$6:$D$12,3,1)*(F7-3500)-VLOOKUP(F7-3500,$A$6:$D$12,4,1))

Match与Index函数

函数语法

MATCH(lookup_value,lookup_array,[match_type])
match(要找的值,找的区域,1小于0精确匹配-1大于),返回找的值在区域内是第几行

INDEX(array,row_num,[column_num])
index(在哪里拿,拿第几个)

Match+Index与Vlookup函数比较

利用客户ID查找公司名称:vlookup函数用法见上
方法二:=INDEX(数据源!B:B,MATCH(查询!A2,数据源!A:A,0))

利用公司名称找客户ID:此时不能用vlookup函数
=INDEX(数据源!A:A,MATCH(查询2!A2,数据源!B:B,0))

使用Match与Vlookup函数嵌套返回多列结果

首先复习单元格引用原理:(九九乘法表)
王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
B2输入:=$A2*B$1 先向右拖(锁定A列不变),在集体向下拉(锁定第一行不变)

通过客户ID找后边一系列信息,后边次序与原表格相同:
王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
E4输入:=VLOOKUP($D4,数据源!$A:$K,COLUMN()-3,0) 先向右拖,在集体向下拉,锁定D列不变

column函数:返回所在列数

通过客户ID找后边一系列信息,后边次序与原表格不同:
王佩丰老师OfficeExcel2010 11-12讲 VLOOKUP函数 学习笔记
B3输入:=VLOOKUP($A3,数据源!$A:$K,MATCH(返回多列结果!B$2,数据源!$A$1:$K$1,0),0) 锁定A列,第二行

使用函数引用照片

直接看视频