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