王佩丰老师OfficeExcel2010 学习笔记
视频链接:王佩丰Excel1800分钟****.
VLOOKUP函数
Vlookup函数语法
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
根据姓名查找总分:
H6单元格输入:=VLOOKUP(G6,$B$5:$E$10,4,0)
=VLOOKUP(要找什么,在哪找,返回选中区域的第几列,0为精确查找1为模糊查找)
注意
第二参数要求:必须包含查找列与引用列,可以往后多选,但不能往前多选,要找姓名必须保证姓名在最左侧列;
如果没有选整列,选的是一块区域的话注意绝对引用。
vlookup()
函数查找数据,若重复,只能返回重复数据的第一个
跨表格,根据客户ID查找公司名称:
B2单元格:=VLOOKUP(A2,数据源!A:B,2,0)
第二个参数直接点数据源表格选中AB两列,选中以后加逗号(,)再进行下边操作。
vlookup中使用通配符
根据公司名称查找地址:
原表格中无三川实业,但是有三川实业有限公司,找不到会显示#N/A
,此时不能使用模糊匹配,要用通配符。=VLOOKUP(A2&"*",数据源!B:E,4,0)
vlookup模糊查找
直到表格中无这个值,还要找
模糊查找只能找小于等于的近似值
求提成比例:H9输入:=VLOOKUP(G9,$C$8:$D$13,2,1)
提成金额:I9:=G9*H9
使用isna函数处理数字格式引起的错误
根据表格查找处所数量:
表格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
返回第几行
C14输入:=HLOOKUP(B14,$1:$3,3,0)
选择区域也为列
计算个人所得税
若一个人的工资为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函数嵌套返回多列结果
首先复习单元格引用原理:(九九乘法表)
B2输入:=$A2*B$1
先向右拖(锁定A列不变),在集体向下拉(锁定第一行不变)
通过客户ID找后边一系列信息,后边次序与原表格相同:
E4输入:=VLOOKUP($D4,数据源!$A:$K,COLUMN()-3,0)
先向右拖,在集体向下拉,锁定D列不变
column
函数:返回所在列数
通过客户ID找后边一系列信息,后边次序与原表格不同:
B3输入:=VLOOKUP($A3,数据源!$A:$K,MATCH(返回多列结果!B$2,数据源!$A$1:$K$1,0),0)
锁定A列,第二行
使用函数引用照片
直接看视频