5excel函数应用之查询与引用函数doc.docx
《5excel函数应用之查询与引用函数doc.docx》由会员分享，可在线阅读，更多相关《5excel函数应用之查询与引用函数doc.docx（17页珍藏版）》请在冰豆网上搜索。
5excel函数应用之查询与引用函数doc
Excel函数应用之查询与引用函数
编者语：
Excel是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠Excel进行数据管理。
它不仅仅能够方便的处理表格和进行图形分析，其更强人的功能体现在对数据的自动处理和计算，然而很多缺少理工科背景或是对Excel強人数据处理功能不了解的人却难以进一步深入。
编者以为，对Excel函数应用的不了解正是阻挡普通用户完全学握Excel的拦路虎，然而忖前这一部份内容的教学文章却乂很少见,所以特别纟fl织了这一个《Excel两数应用》系列，希望能够对Excel进阶者有所帮助。
《Excel函数应用》系列，将每周更新，逐步系统的介绍Excel各类濒数及其应用，敬请关注！
在介绍查询与引用函数之前，我们先来了解一下有关引用的知识。
1、引用的作用
在Excel屮引用的作用在于标识工作表上的单元格或单元格区域，并指明公式屮所使用的数据的位置。
通过引用，可以在公式中使用工作农不同部分的数据，或者在多个公式中使用同一单元格的数值。
还可以引用同一工作簿不同工作表的单元格、不同工作簿的单元格、甚至其它应用程序屮的数据。
2、引用的含义
关于引用需要了解如下儿种情况的含义：
外部引用-不同工作簿屮的单元格的引用称为外部引用。
远程引用-引用其它程序中的数据称为远程引用。
相对引用-在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。
绝对引用-如果在复制公式时不希望Excel调整引用，那么请使用绝对引用。
即加入美元符号，如$C$lo
3、引用的表示方法
关于引用有两种表示的方法，即A1和R1C1引用样式。
（1）引用样式一（默认）-A1
A1的引用样式是Excel的默认引用类型。
这种类型引用字母标志列（从A到IV,共256列）和数字标志行（从1到65536）。
这些字母和数字被称为行和列标题。
如果要引用单元
格,请顺序输入列字母和行数字。
例如,C25引用了列C和行25交叉处的单元格。
如果要引用单元格区域，请输入区域左上角单元格的引用、旨号（：
）和区域右下角单元格的引用，女IIA20:
C35。
（2）引用样式二-R1C1
在R1C1引用样式中,Excel使用“R“加行数字和”C“加列数字来指示单元格的位置。
例如,单元格绝对引用R1C1与A1引用样式中的绝对引用$A$1等价。
如果活动单-元格是A1,则单元格相对引用R[1]C[11将引用下面一行和右边一列的单元格，或是B2。
在了解了引用的概念后，我们來看看Excel提供的杳询与引用函数。
查询与引用函数对以用来在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用。
Excel中一共提供了ADDRESS.AREAS>CHOOSE、COLUMN>COLUMNS、HLOOKUP、HYPERLINK、INDEX.INDIRECTLOOKUP>MATCH、OFFSET.ROW、ROWS>TRANSPOSE>VLOOKUP16个查询与引用函数。
下面，笔者将分组介绍一下这些函数的使用方法及简单应用。
一、ADDRESS、COLUMN、ROW
1、ADDRESS用于按照给定的行号和列标，建立文本类型的单元格地址。
其语法形式为：
ADDRESS（row_num,column_num,abs_num,a1,sheet_text）
Row_num指在单元格引用中使用的行号。
Column_num指在单元格引用中使用的列标。
Abs_num指明返回的引用类型，1代表绝对引用，2代农绝对行号，相对列标，3代表相对行号，绝对列标，4为相对引用。
A1用以指明A1或R1C1引用样式的逻辑值。
如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用；如果A1为FALSE,函数ADDRESS返回R1C1样式的引用。
Sheet_text为一文本，指明作为外部引用的丄作表的名称，如果省略sheet-text,则不使用任何工作衣名。
简单说，BPADDRESS（行号，列标，引用类型，引用样式，工作衣名称）比如，ADDRESS（4,5,1,FALSE,°[Book1]Sheet1H）等于H[Bookl]Sheetl!
R4C5U参见图1
闿文件g）編辑g）视團插入0）格式（Q）工具0）数据⑪窗口⑩帮助⑪
D16F
Mi
■bnr
1
ADDRESS（2,3）
$C$2
2
ADDKESS（2,J）
$C$2
3
APDRESS（2?
3?
2）
C$2
4
ADDRESS（233）
$C2
5
ADDRESS（2,3,4）
C2
6
APDRESS（2?
3?
2?
false）
R2C[3]
7
ADDRESS（2?
3,2?
false/,[函数]函数明细。
[函数]函数明细!
R2C[3]
8
APDRESS233fhl嘔喳询与引用函数”）
查询与引用函数1R⑵C3
9
10
图1
2、COLUMN用于返回给定引用的列标。
语法形式为：
COLUMN（reference）
Reference为需要得到其列标的单元格或单元格区域。
如果省略reference,则假定为是对函数COLUMN所在单元格的引用。
如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则函数COLUMN将reference中的列标以水平数组的形式返回。
但是Reference不能引用多个区域。
3、ROW用于返冋给定引用的行号。
语法形式为：
ROW（reference）
Reference为需要得到其行号的单元格或单冗格区域。
如果省略reference,则假定是对函数ROW所在单元格的引用。
如果reference为一个单元格区域，并口函数ROW作为垂直数组输入，则函数ROW将reference的行号以垂直数组的形式返回。
但是Reference不能对多个区域进行引用。
二、AREAS.COLUMNS、INDEX、ROWS
1、AREAS用于返冋引用中包含的区域个数。
其中区域表示连续的单元格组或某个单元格。
其语法形式为AREAS（reference）
Reference为对某一单元格或单元格区域的引用，也可以引用多个区域。
如來需要将儿个引用指定为一个参数，则必须用括号括起来。
2、COLUMNS用于返冋数组或引用的列数。
其语法形式为COLUMNS（array）
Array为需要得到其列数的数组、数组公式或对单元格区域的引用。
3、ROWS用于返回引用或数组的行数。
其语法形式为ROWS（array）
Array为需要得到其行数的数组、数组公式或对单元格区域的引用。
以上各函数示例见图2
Al2T=
■A
1
■B
C
1
C0LUMN（A3）
1
2
ROW（CR
6
3
AREAS（B2:
D4）
1
4
AEJEAS（（B2:
D4,E5,F6:
I9））
3
5
COLUMNS®1:
C4）
3
6
ROWS（A1:
C4）
4
7
图2
4、INDEX用于返回表格或区域中的数值或对数值的引用。
函数INDEXO有两种形式：
数组和引用。
数组形式通常返回数值或数值数组；引用形式通常返回引用。
（1）INDEX（array,row_num,column_num）返回数组中指定单元格或单元格数组的数值。
Array为单元格区域或数组常数。
Row.num为数组中某行的行序号，函数从该行返回数值。
Column_num为数组中某列的列序号，函数从该列返回数值。
需注意的是Row_num和column_num必须指向array中的某一单元格，否则，函数INDEX返回错误值#REF!
。
（2）INDEX（reference,row_num,cokimn_num,area_num）返回引用中指怎单元格或单元格区域的引用。
Reference为对一个或多个单元格区域的引用。
Row-num为引用中某行的行序号，函数从该行返冋一个引用。
Column_num为引用中某列的列序号，函数从该列返回一个引用。
需注意的是Row_num、column_num和area_num必须指向reference中的单元格;否则，函数INDEX返回错误值#REF!
。
如果省略row_num和column_num,函数INDEX返回由area_num所指定的区域。
三、INDIRECT、OFFSET
1、INDIRECT用于返冋由文字串指定的引用。
当需要更改公式中单元格的引用，而不更改公式本身，使用函数INDIRECTo
其语法形式为：
INDIRECT（ref_text,al）
其中Ref_text为对单元格的引用，此单元格可以包含A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文字串单-元格的引用。
如呆ref_text不是合法的单-元格的引用，函数INDIRECT返冋错误值#REF!
o
Al为一逻辑值，指明包含在单元格ref_text中的引用的类型。
如果al为TRUE或省略,ref_text被解释为Al・样式的引用。
如果al为FALSE,ref.text被解释为R1C1-样式的引用。
需要注意的是：
如呆ref_text是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。
如果源工作簿没有打开，函数INDIRECT返冋错谋值#REF!
o
2、OFFSET函数用于以指定的引用为参照系，通过给足偏移量得到新的引用。
返回的引用可以是一个单元格或者单元格区域，并可以指定返回的行数或者列数。
其基本语法形式为:
OFFSET（reference,rows,cols,height,width）0
•其中，reference变量作为偏移量参照系的引用区域（reference必须为对单元格或相连单元格区域的引用，否则，OFFSET函数返回错误值#VALUE!
）o
rows变量表示相对于偏移量参照系的左上角单-元格向上（向下）偏移的行数（例如rows使用2作为参数，表示目标引用区域的左上角单元格比reference低2行），行数可为正数（代表在起始引用单元格的下方）或者负数（代表在起始引用单元格的上方）或者（）（代表起始引用单元格）。
cols表示相对于偏移量参照系的左上角单元格向左（向右）偏移的列数（例如cols使用4作为参数，表示Id标引用区域的左上角单元格比reference右移4列），列数可为正数（代表在起始引用单元格的右边）或者负数（代表在起始引用单元格的左边）。
如果行-数或者列数偏移量超出工作表边缘，OFFSET函数将返冋错谋值#REF!
Oheight变量表示高度，即所要返冋的引用区域的行数（height必须为正数）。
wid山变最表示宽度，即所要返回的引用区域的列数（width必须为正数）。
如杲省略height或者width,则假设其高度或者宽度与reference相同。
例如,公式OFFSET（A1,2,3,4,5）S示比单元格A1靠下2行并靠右3列的4行5列的区域（即D3:
H7区域）。
rfl此町见，OFFSET函数实际上并不移动任何单元格或者更改选定区域，它只是返回一个引用。
四、HLOOKUP、LOOKUP、MATCH、VLOOKUP
1、LOOKUP函数与MATCH函数
LOOKUP函数可以返回向量（单行区域或单列区域）或数组中的数值。
此系列函数用于在表格或数值数组的首行查找指定的数值，并由此返冋表格或数组当前列中指定行处的数值。
当比较值位于数据表的首行，并且要查找下而给定行中的数据时，使用函数HLOOKUP。
当比较值位于要进行数据查找的左边一列时，使用函数VLOOKUP。
如果需要找出匹配元素的位置而不是匹配元素木身，则应该使用函数MATCH而不是函数LOOKUPoMATCH函数用来返回在指定方式下与指定数值匹配的数组中元索的相应位置。
从以上分析町知，査找函数的功能，一是按捜索条件，返回被搜索区域内数据的一个数据值;二是按搜索条件，返冋被搜索区域内某一数据所在的位置值。
利用这两人功能，不仅能实现数据的查询，而且也能解决如淀级”之类的实际问题。
2、LOOKUP用于返回向量（单行区域或单列区域）或数组中的数值。
函数LOOKUP冇两种语法形式：
向量和数组。
（1）向量形式
函数LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。
Lookup_value为函数LOOKUP在第一个向址中所要杳找的数值。
Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。
Lookup_vector为只包含一行或一•列的区域。
Lookup_vector的数值可以为文本、数字或逻辑值。
需要注意的是Lookup_vector的数值必须按升序排序：
…、-2、-1、0、1、2、…、A-Z、FALSE>TRUE；否则，函数LOOKUP不能返冋正确的结果。
文本不区分人小写。
Result_vector只包含一行或一列的区域，其人小必须与lookup_vector相同。
如來函数LOOKUP找不至!
jlookup_value,则查找lookup_vector中小于或等于lookup_value的最人数值。
如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返冋错谋值#N/A。
示例详见图3
G14二|=
!
■a
■B
■C
D
「E
1
成绩
姓名
2
85
Andy
3
68
Annie
4
75
Henty
5
90
Jacky
6
100
Jarry
7
82
Mary
8
76
Michael
9
95
10
70
Rose
11
12
查找成绩为100分的学生
Jarry
LOOKUP（100?
B2:
B10?
C2:
C10）
13
查找成绩低于70分的学生
Annie
LOOKUP（69?
B2:
B10?
C2:
C10）
14
•1L
查找成绩低于60分的学生
1
#N/A
LOOKUP（59?
B2:
B10?
C2:
C10）
（2）数组形式
函数LOOKUP的数组形式在数组的笫一行或笫一列查找指定的数值,然后返冋数组的最后一行或最后一列中相同位置的数值。
通常情况下，最好使用函数HLOOKUP或函数VLOOKUP來替代函数LOOKUP的数组形式。
两数LOOKUP的这种形式主要用于与其他电子衣格兼容。
关于LOOKUP的数组形式的用法在此不再赘述，感兴趣的对以参看Excel的帮助。
3、HLOOKUP与VLOOKUP
HLOOKUP用于在表格或数值数组的首行查找指定的数值，并山此返回表格或数组当前列中指定行处的数值。
VLOOKUP用于在表格或数值数组的首列查找指定的数值，并山此返回表格或数组当前行中指定列处的数值。
当比较值位于数据农的首行，并且耍查找下面给定行中的数据时，请使用函数HLOOKUP。
当比较值位于要进行数据查找的左边一列时，请使用函数VLOOKUP。
语法形式为：
HLOOKUP（lookup_value,table_array,row_index_num,range_lookup）
VLOOKUP（lookup_value,table_array,col_index_num,range」ookup）
其屮，Lookup_value表示要查找的值，它必须位于自定义查找区域的最左列。
Lookup_value可以为数值、引用或文字串。
Table.array杳找的区域，用于杳找数据的区域，上面的杳找值必须位于这个区域的最左列。
可以使用对区域或区域名称的引用。
Row_index_num为table_array中待返回的匹配值的行序号。
Row_index_num为1时，返冋table_array第一行的数值，row_index_num为2时，返冋table_array笫二行的数值，以此类推。
Col_index_num为相对列号。
最左列为1,其右边-一列为2,依此类推.
Rangejookup为一逻辑ffi,指明函数HLOOKUP杳找时是精确匹配，还是近似匹配。
下而详细介绍一下VLOOKUP函数的应用。
简言之，VLOOKUP函数可以根据搜索区域内最左列的值，去杳找区域内其它列的数据，并返回该列的数据，对于字母來说，搜索时不分大小写。
所以，函数VLOOKUP的查找对以达到两种目的：
一是精确的查找。
二是近似的查找。
下面分别说明。
（1）精确杳找■■根据区域最左列的值，对其它列的数据进行精确的杳找
示例：
创建工资表与工资条
首先建立员工工资表
・B7US
iA
B
3C
rd
E
1F
G:
H
II
J
tK-
1
xe位沏oi年5月工费表
c
2
鋼号
姓名
所廉科京
琵本工资
加班时间（天）
加班圖
劳动保险
买际工资
3
A001
Sandy
2,500.00
2
236.00
219.00
2,517.00
4
A002
John
技*部
1000.00
3
967.00
36500
4,202.00
5
A003
Jxky
1,80000
1
8500
15100
1,734.00
6
A004
Anny
3,50000
3.500.00
7
A005
Many
技#那
5,00000
5,000.00
8
A006
Henty
办公女
2,000.00
2
139.00
175.00
2,014.00
9
A00?
Robot
企划那
30000
X500.00
10
A008
Lucky
办公宜
2,50000
1
11800
20900
2,409.00
11
A009
David
mm
3.000.00
3,000.00
12
A010
Jenny
企戈卿
4.00000
3
56700
36500
4,202.00
13
总计
31,800.00
12
1,762.00
1.484.00
32,078.00
14
部门小计
企划那
Hf80000
6
88800
73500
11,953.00
15
6fS0000
0
0.00
0.00
6,500.00
16
办公室
4.50000
3
307.00
38400
《42300
17
技术部
%00000
3
56700
36500
9?
20200
18
32,078.00
19
20
21
22
23
04
图4
然后，根据工资表创建各个员工的工资条，此工资条为应用Vlookup两数建立。
以员工Sandy（编号A001）的工资条创建为例说明。
笫一步，拷贝标题栏
第二步，在编号处（A21）写入A001
第三步，在姓名（B21）创建公式
=VL00KUP（$A21,$A$3:
$H$12,2,FALSE）
语法解释:
在$A$3:
$H$12范围内（即工资表中）精确找出与A21单元格相符的行,并将该行中第二列的内容计入单元格中。
第四步，以此类推，在随厉的单元格中写入相应的公式。
塹文件g）编辑视si辺插入a）格式©工具①数据窗口帮助（M）
B21
A
B
c
D
E
FF
G
H
1
XXX单传2001年5角工8E表I||
|2
编号
姓名
所JI科室
基本工资
加班时间（天）
加班费
劳动保险
实际工资
3
A001
Sandy
企划部
2,500.00
2
236.00
219.00
2,517.00
4
A002
John
技术部
4,000.00
3
567.00
365.00
4,202.00
5
A003
Jacky
企划部
1,800.00
1
85.00
151.00
1,734.00
6
A004
Army
销售部
3,500.00
3,500.00
7
A005
Many
技术部
5,000.00
5,000.00
8
A006
Herury
办公室
2,000.00
2
189.00
175.00
2,014.00
9
A007
Robot
企划部
3,500.00
3,500.00
10
A008
Lucky
办公室
2,500.00
1
118.00
209.00
乙409.00
11
A009
David
销售部
3,000.00
3,000.00
12
A010
Jenny
企划部
4,000.00
3
567.00
365.00
4,202.00
13
总计
31,800.00
12
1,762.00
1,484.00
32,078.00
14
部门小计
企划部
11,800.00
6
888.00
735.00
11,953.00
15
销售部
®500.00
0
0.00
0.00
6,500.00
16
办公室
4,500.00
3
307.00
384.00
4?
423.00
|17
技术部
9,000.00
3
567.00
365.00
3202.00
18
32,078.00
19
20
編号
姓名
所属科室
基本工资
加班时间（天）
加班费
劳动保险
实际工资
21
A001
&andy
医划部
2,500.00
2
236.00
219.00
2,517.00
22
1
▼
图5
（2）近似的查找•■根据定义区域最左列的值，対其它列数据迹行不精确值的查找
示例：
按照项目总额不同提取相应比例的奖金
第一步，建立一个项日总额与奖金比例的对照表，如图6所示。
项目
升级会员 