EXCEL函数高级应用笔记.docx
《EXCEL函数高级应用笔记.docx》由会员分享，可在线阅读，更多相关《EXCEL函数高级应用笔记.docx（19页珍藏版）》请在冰豆网上搜索。
EXCEL函数高级应用笔记
第一讲：
二分法查找
（一）查找原理
一、使用二分法查找的函数
1.历遍法（遍历法）查找
适用函数：
Match、Vlookup、Hlookup等函数的精确查找。
查找原理：
是从上之下或者从左至右一个个查找，直到找到合适的为止
2.二分法查找
适用函数：
Lookup函数、Match、Vlookup、Hlookup等函数的模糊查找；
查找原理：
采用二分法查找时，数据需是排好序的。
基本思想：
假设数据是按升序排序的，对于给定值x，从序列的中间位置开始比较，如果当前位置值等于x，则查找成功；若x小于当前位置值，则在数列的前半段中查找；若x大于当前位置值则在数列的后半段中继续查找，直到找到为止；
水管原理：
华罗庚提出，一半一半的查找
二、二分位查找的各种情况
二分位：
=INT（（1+个数）/2）
3.查找值等于二分位值
情况一：
查找范围元素个数是奇数
C
D
E
F
G
25
10
甲
26
60
乙
查找
结果
27
30
丙
60
戊
28
20
丁
29
60
戊
30
30
己
31
80
庚
32
60
辛
33
20
壬
G27中公式：
=LOOKUP（F27,C25:
C33,D25:
D33）
情况二：
查找范围元素个数是偶数
K
L
M
N
O
25
10
甲
26
20
乙
查找
结果
27
30
丙
20
丁
28
20
丁
29
60
戊
30
30
己
31
20
庚
32
50
辛
O27中公式：
=LOOKUP（N27,K25:
K32,L25:
L32）
4.查找值大于二分位值
C
D
E
F
G
H
I
37
20
甲
38
90
乙
查找
结果
39
30
丙
90
壬
40
20
丁
41
60
戊
42
90
己
90
43
80
庚
80
44
50
辛
50
50
45
10
壬
10
10
10
I39中公式=LOOKUP（H39,C37:
C45,D37:
D45）
分析：
二分位是60，要查找的数是90>60，在C42：
C45间查找，二分位80<90，在C44：
C45间查找，二分位50<90，最终查找的数字是10对应的是壬
5.查找值小于二分位值
C
D
E
F
G
H
I
49
90
甲
90
90
50
40
乙
40
查找
结果
51
30
丙
30
10
#N/A
52
10
丁
10
53
60
戊
54
30
己
55
80
庚
56
10
辛
57
20
壬
I51中公式=LOOKUP（H51,C49:
C57,D49:
D57）
6.查到不符合条件的出现后取最后一个符合条件的结果
C
D
E
F
G
H
I
61
60
甲
62
60
乙
查找
结果
63
60
丙
60
辛
64
60
丁
65
60
戊
66
60
己
67
60
庚
68
60
辛
69
50
壬
I63中公式=LOOKUP（H63,C61:
D69）
分析：
查找到二分位等于要查找的值后，继续向下比较，发现还等于要查找的值，继续向下查找直到不等于要查找的值
7.查找最后一个
C
D
E
F
G
H
I
73
60
甲
74
10
乙
查找
结果
75
60
丙
61
壬
76
10
丁
77
60
戊
78
10
己
79
60
庚
80
60
辛
81
10
壬
I75中的公式=LOOKUP（H75,C73:
D81）
注：
只要查找值大于数组内的每一个值，那么最后结果就会查找到最后一个数对应的结果；在excel表中常用9E+307代表最大的数字，来查找最后一个数字；文本时候一般用”々”（EXCEL中快速输入是ALT+41385），但是”々”（搜狗可通过V1打出来）不是文字当中的最大值，文字当中的最大值是”隝”dǎo（EXCEL中快捷输入是ALT+65103）
8.混和
C
D
E
F
G
H
I
85
10
甲
10
86
60
乙
60
查找
结果
87
30
丙
30
30
80
丙
88
90
丁
90
90
89
90
戊
90
30
己
91
80
庚
92
50
辛
93
20
壬
注：
1、最终返回小于要查找值的那个数
2、二分位法最终查找到的值一定不大于要查找的值
9.忽略逻辑值、错误值等
C
D
E
F
G
H
I
I列公式
97
10
甲
#DIV/0!
98
60
乙
#DIV/0!
查找
结果
99
30
丙
#DIV/0!
90
丁
=VLOOKUP（H99,C97:
D105,2,）
100
90
丁
0
90
戊
=LOOKUP（H100,C97:
D105）
101
90
戊
0
90
庚
=LOOKUP（1,0/（C97:
C105=H101）,D97:
D105）
102
30
己
#DIV/0!
103
90
庚
0
104
50
辛
#DIV/0!
105
20
壬
#DIV/0!
F列公式{=0/（C97:
C105=H99）}
注：
利用lookup函数忽略错误值的特点，可以实现查找符合条件的最后一个结果，Lookup条件查找结构=LOOKUP（1,0/（条件区域=条件）,对应结果区域），可用于正向查找、反向查找、错位查找、多条件查找
三、二分位查找的速度
二分法的运算速度：
65536个数据的查找最多用16次；1048576个数据查找最多用20次
第二讲：
二分法查找
（二）示例
一、查找最后一个文本或数字
原理：
只要查找的值比查找范围内任何一个值大，就会返回最后一个值
10.查找最后一个文本
查找文本一般用“々”（可在excel中输入ALT+41385或者搜狗输入法下V1）,除非“々”落在二分位上
还可以用“座”，除非“座”字落在二分位上
11.查找最后一个数字
Excel中可以显示的最大数9E+307，还可以写9^323，实际9^323要大于9E+307
二、提取数字
12.数字在字符串前
C
D
F列公式
F
9
030个
=-LOOKUP（1,-LEFT（C9,ROW（$1:
$10）））
30
10
72.1平方
=-LOOKUP（1,-LEFT（C10,ROW（$1:
$10）））
72.1
11
1.2KG
=-LOOKUP（1,-LEFT（C11,ROW（$1:
$10）））
1.2
注：
1、省字符的小技巧，加一个负号将文本都转换了负数，都不大于0，因此可以通过查找1来提取数字
2、这个函数有个缺点就是只能提取数字，不能提取完整的数字段，例如“030个”，只能提取出“30”而不能提取“030”
13.数字在字符串中间
C
F列公式
F
19
苹果10个
=-LOOKUP（1,-MIDB（C19,SEARCHB（"?
",C19）,ROW（$1:
$9）））
10
20
第05节
=-LOOKUP（1,-MIDB（C20,SEARCHB（"?
",C20）,ROW（$1:
$9）））
5
21
水4.5公斤
=-LOOKUP（1,-MIDB（C21,SEARCHB（"?
",C21）,ROW（$1:
$9）））
4.5
F列中还可以{=-LOOKUP（1,-MID（C19,MATCH（0,MID（C19,ROW（$1:
$9）,1）*0,）,ROW（$1:
$9）））}
三、指定月份最大天数
C
D
E
30
月份
天数
D列公式
31
1
31
=DAY（-LOOKUP（,-（C31&-ROW（$1:
$31））））
32
2
28
=DAY（-LOOKUP（,-（C32&-ROW（$1:
$31））））
33
3
31
=DAY（-LOOKUP（,-（C33&-ROW（$1:
$31））））
34
4
30
=DAY（-LOOKUP（,-（C34&-ROW（$1:
$31））））
35
5
31
=DAY（-LOOKUP（,-（C35&-ROW（$1:
$31））））
36
6
30
=DAY（-LOOKUP（,-（C36&-ROW（$1:
$31））））
37
7
31
=DAY（-LOOKUP（,-（C37&-ROW（$1:
$31））））
38
8
31
=DAY（-LOOKUP（,-（C38&-ROW（$1:
$31））））
39
9
30
=DAY（-LOOKUP（,-（C39&-ROW（$1:
$31））））
40
10
31
=DAY（-LOOKUP（,-（C40&-ROW（$1:
$31））））
41
11
30
=DAY（-LOOKUP（,-（C41&-ROW（$1:
$31））））
42
12
31
=DAY（-LOOKUP（,-（C42&-ROW（$1:
$31））））
注：
超过月份最大日期的部分会变成错误值，lookup会忽略错误值
四、去除重复项
C
D
F
G
H
I
J
K
L
45
结果
46
结果
戊
47
结果
戊
丁
48
结果
戊
丁
己
49
结果
戊
丁
己
乙
50
结果
戊
丁
己
乙
丙
51
结果
结果
戊
丁
己
乙
丙
甲
52
戊
戊
0
1
1
1
1
1
1
53
丁
丁
0
0
1
1
1
1
1
54
丙
己
0
0
0
0
0
1
1
55
甲
乙
0
0
0
0
0
0
1
56
己
丙
0
0
0
1
1
1
1
57
己
甲
0
0
0
1
1
1
1
58
丙
0
0
0
0
0
1
1
59
己
0
0
0
1
1
1
1
60
戊
0
1
1
1
1
1
1
61
乙
0
0
0
0
1
1
1
62
己
0
0
0
1
1
1
1
63
丁
0
0
1
1
1
1
1
64
戊
0
1
1
1
1
1
1
分析：
每列最后一个0出现的位置就是每个非重复项在数据中最后出现的位置，只要查找最后个0的位置即可，查找0不好查找，可以将1转换为错误值，然后查找1或者0；
G：
L列中的公式=COUNTIF（F$46:
F51,$C$52:
$C$64）
D列公式=LOOKUP（1,0/（1-COUNTIF（D$51:
D51,C$51:
C$64））,C$51:
C$64）&""
五、合并单元格统计
数据源
C
D
F
Q
R
68
品名
销量
品名
品名
销量
69
A
523
A
A
1668
70
567
A
B
589
71
578
A
C
2063
72
B
589
B
D
648
73
C
651
C
E
1040
74
648
C
75
764
C
最大销量
2063
76
D
648
D
77
E
578
E
78
462
E
分析：
F列中公式=IF（C69="",F68,C69）或者=LOOKUP（"座",C$69:
C69）
这2个公式返回的结果都不是数组，要使其结果为数组，可以用{=LOOKUP（ROW（1:
10）,ROW（1:
10）/（C69:
C78>""）,C69:
C78）}
R69内的公式=SUM（（LOOKUP（ROW（$1:
$10）,ROW（$1:
$10）/（$C$69:
$C$78>""）,$C$69:
$C$78）=Q69）*$D$69:
$D$78）
求最大销量，需要构成一个数组{1668;589;2063;648;1040},这时候需要用到mmult函数，R75中公式{=MAX（MMULT（N（LOOKUP（COLUMN（A:
J）,ROW（1:
10）/（C69:
C78>""）,C69:
C78）=Q69:
Q73）,D69:
D78））}
第三讲内存数组与多维引用
（一）
一、数组公式
1、以组合键结束的单个结果的公式,例如sum函数
2、不以组合键结束但实质进行了数组运算的公式，例如sumproduct函数，特定形式下的mm函数，公式中的数组以常量形式出现的大多数情况下不需要组合键
3、内存数组
4、伪内存数组
伪内存数组的情况：
1）F9的结果与显示内容不一致
正常情况下，内存数组在单元格内显示的结果和在公式中F9后显示的结果一致
2）无法进行再运算
可以在结果外套sum函数来检查
3）无法用Index查看数组中的每一个值
用index逐一显示数组中的各个值（这种方法不仅可以检查真伪内存数组，还可以分辨多维引用和内存数组）
伪内存数组有哪些
1）vlookup是典型的伪内存数组
例如
C
D
E
F
G
13
A
B
A
B
14
甲
1
乙
2
15
乙
2
丙
3
16
丙
3
甲
1
17
丁
4
丁
4
选中G14：
G17输入公式{=VLOOKUP（F14:
F17,C:
D,2,）}
但是选中单元格内公式按F9后结果显示2，而不是{2;3;1;4}说明这不是内存数组；
且用sum函数求和{=SUM（VLOOKUP（F14:
F17,C:
D,2,））}后的结果是一个数2；最重要的是把VLOOKUP（F14:
F17,C:
D,2,）当做index的参数逐一显示，如果是内存数组，结果会是数组一个数组
2）Index非引用结果行数字和列数字至少一个是数组时结果构成伪内存数组
例如
C
D
E
F
G
21
甲
子
Index
22
乙
丑
乙
乙
23
丙
寅
丁
丁
24
丁
卯
选中F22：
G23输入公式{=INDEX（{"甲","子";"乙","丑";"丙","寅";"丁","卯"},{2;4}）}，如果结果是内存数组则应显示为{"乙","丑";"丁","卯"}
3）Index引用结果行数字和列数字至少一个是数组时结果不构成二维引用和多维引用
二、多维引用
多维引用特征一：
多数多维引用的结果无法在一个二维区域内显示
1.Indirect、Offset行数字和列数字至少一个是数组时结果构成多维引用
1）例1：
=INDIRECT（"列标"&ROW（A1）/COLUMN（A1））
=INDIRECT（"R"&ROW（A1）/COLUMN（A1）&"C"&ROW（A1）/COLUMN（A1）,）
数据源
INDIRECT
扩展
C
D
E
F
G
H
I
37
50
50
#VALUE!
#VALUE!
#VALUE!
E37内公式=INDIRECT（"C"&ROW（A37））
选中G37:
I37后输入{=INDIRECT（"C"&ROW（A37））*COLUMN（A:
C）}让其参与运算，结果是错误值；所以它是一个多维引用；多数的多维引用无法在一个二维区域正常显示
indirect的参数是常量时候，返回的结果不是二维引用
2）例2：
{=INDIRECT（"行号或列标"&ROW（1:
3）/COLUMN（A:
C）,参数）}
C
D
E
F
G
41
数据源
各平面
结果
42
100
C42
#VALUE!
43
200
C43
#VALUE!
44
300
C44
#VALUE!
选中E42：
E44后输入{="C"&ROW（42:
44）}
选中G42：
G44后输入{=INDIRECT（"C"&ROW（42:
44））}显示错误值，其实并没有错误，只是多维引用无法在二维平面显示
注意：
与INDIRECT（”C1：
C10”）有差别，INDIRECT（”C1：
C10”）是一个平面，
=INDIRECT（"C"&ROW（42:
44））是三个平面
3）例3：
{=INDIRECT（"A1:
C"&ROW（1:
3））}
{=INIDRECT（"R1C1:
R1C"&COLUMN（A:
C）,）}
C
D
E
F
G
H
I
49
数据源
各平面
结果
50
9
8
7
c50:
E50
#VALUE!
51
6
5
4
c50:
E51
#VALUE!
52
3
2
1
c50:
E52
#VALUE!
选中G50:
G52后输入{="c50:
E"&ROW（50:
52）}，本例子同例2一样是3个平面，例1是一个平面，本例与前两例不同的是本例子每个平面有不止一个数字
选中I50:
I52后输入{=INDIRECT（"c50:
E"&ROW（50:
52））}
4）例4：
Indirect的其它例子：
{=INDIRECT（"A"&ROW（1:
3）&":
C"&COLUMN（A:
C））}
{=INDIRECT（"R1C"&COLUMN（A:
C）&":
R3C"&ROW（1:
3）,）}
{=INDIRECT（"R1C"&ROW（1:
3）&":
R"&COLUMN（A:
C）&"C1",）}
{=INDIRECT（"R"&COLUMN（A:
C）&"C"&ROW（1:
3）&":
R"&ROW（1:
3）&"C"&COLUMN（A:
C）,）}
……
5）例5：
=OFFSET（起点,ROW（A1）/COLUMN（A1）,ROW（A1）/COLUMN（A1））
OFFSET的第一个参数或者第二个参数是ROW或者COLUMN的
C
D
E
F
G
H
I
62
数据源
结果
与数组运算
63
50
50
#VALUE!
#VALUE!
#VALUE!
和indirect的第一种情况类似，表面来看是普通公式，但是其内部是多维引用，一扩展就可以看出来
E63单元格内=OFFSET（B63,,ROW（A1））
6）例6：
{=OFFSET（起点,ROW（1:
3）/COLUMN（A:
C）,）}
{=OFFSET（起点,,ROW（1:
3）/COLUMN（A:
C））}
C
D
E
F
G
H
I
68
数据源
下移
各平面
结果
69
100
#VALUE!
70
200
#VALUE!
71
300
#VALUE!
选中I69:
I71后输入{=OFFSET（C68,ROW（1:
3）,）}
7）例7:
{=OFFSET（起点,ROW（1:
3）,ROW（1:
3））}
{=OFFSET（起点,COLUMN（A:
C）,COLUMN（A:
C））}
C
D
E
F
G
H
I
J
K
L
76
数据源
下移
右移
各平面
结果
77
9
8
7
#VALUE!
78
6
5
4
#VALUE!
79
3
2
1
#VALUE!
选中L77:
L79后输入{=OFFSET（B76,ROW（1:
3）,ROW（1:
3））}
8）例8:
{=OFFSET（起点,ROW（1:
3）,COLUMN（A:
C））}
{=OFFSET（起点,COLUMN（A:
C）,ROW（1:
3））}
C
D
E
F
G
H
I
J
K
L
M
N
84
升级会员 