EXCEL函数高级应用笔记

1、目录第一讲：二分法查找（一）查找原理1一、使用二分法查找的函数11.历遍法（遍历法）查找12.二分法查找1二、二分位查找的各种情况11.查找值等于二分位值12.查找值大于二分位值23.查找值小于二分位值24.查到不符合条件的出现后取最后一个符合条件的结果25.查找最后一个36.混和37.忽略逻辑值、错误值等3三、二分位查找的速度4第二讲：二分法查找（二）示例4一、查找最后一个文本或数字41.查找最后一个文本42.查找最后一个数字4二、提取数字41.数字在字符串前42.数字在字符串中间4三、指定月份最大天数5四、去除重复项5五、合并单元格统计6第三讲 内存数组与多维引用（一）6一、数组公式6二、

2、多维引用7第四讲 内存数组与多维引用（二）10一、多维引用(续)1011第一讲：二分法查找（一）查找原理 一、 使用二分法查找的函数1. 历遍法（遍历法）查找适用函数：Match、Vlookup、Hlookup等函数的精确查找。查找原理：是从上之下或者从左至右一个个查找，直到找到合适的为止2. 二分法查找适用函数：Lookup函数、Match、Vlookup、Hlookup等函数的模糊查找；查找原理：采用二分法查找时，数据需是排好序的。 基本思想：假设数据是按升序排序的，对于给定值x，从序列的中间位置开始比较，如果当前位置值等于x，则查找成功；若x小于当前位置值，则在数列的前半段中查找；若x大

3、于当前位置值则在数列的后半段中继续查找，直到找到为止；水管原理：华罗庚提出，一半一半的查找二、 二分位查找的各种情况二分位：=INT(1+个数)/2)1. 查找值等于二分位值情况一：查找范围元素个数是奇数CDEFG2510甲2660乙查找结果2730丙60戊2820丁2960戊3030己3180庚3260辛3320壬G27中公式：=LOOKUP(F27,C25:C33,D25:D33)情况二：查找范围元素个数是偶数KLMNO2510甲2620乙查找结果2730丙20丁2820丁2960戊3030己3120庚3250辛O27中公式：=LOOKUP(N27,K25:K32,L25:L32)2. 查

4、找值大于二分位值CDEFGHI3720甲3890乙查找结果3930丙90壬4020丁4160戊4290己904380庚804450辛50504510壬101010I39中公式=LOOKUP(H39,C37:C45,D37:D45)分析：二分位是60，要查找的数是90>60，在C42：C45间查找，二分位80<90，在C44：C45间查找，二分位50<90，最终查找的数字是10对应的是壬3. 查找值小于二分位值CDEFGHI4990甲90905040乙40查找结果5130丙3010#N/A5210丁105360戊5430己5580庚5610辛5720壬I51中公式=LOOKUP

5、(H51,C49:C57,D49:D57)4. 查到不符合条件的出现后取最后一个符合条件的结果CDEFGHI6160甲6260乙查找结果6360丙60辛6460丁6560戊6660己6760庚6860辛6950壬I63中公式=LOOKUP(H63,C61:D69)分析：查找到二分位等于要查找的值后，继续向下比较，发现还等于要查找的值，继续向下查找直到不等于要查找的值5. 查找最后一个CDEFGHI7360甲7410乙查找结果7560丙61壬7610丁7760戊7810己7960庚8060辛8110壬I75中的公式=LOOKUP(H75,C73:D81)注：只要查找值大于数组内的每一个值，那么最

6、后结果就会查找到最后一个数对应的结果；在excel表中常用9E+307代表最大的数字，来查找最后一个数字；文本时候一般用”（EXCEL中快速输入是ALT+41385），但是”（搜狗可通过V1打出来）不是文字当中的最大值，文字当中的最大值是”隝” do（EXCEL中快捷输入是ALT+65103）6. 混和CDEFGHI8510甲108660乙60查找结果8730丙303080丙8890丁90908990戊9030己9180庚9250辛9320壬注：1、最终返回小于要查找值的那个数2、二分位法最终查找到的值一定不大于要查找的值7. 忽略逻辑值、错误值等CDEFGHII列公式9710甲#DIV/0!

7、9860乙#DIV/0!查找结果9930丙#DIV/0!90丁 =VLOOKUP(H99,C97:D105,2,)10090丁090戊 =LOOKUP(H100,C97:D105)10190戊090庚=LOOKUP(1,0/(C97:C105=H101),D97:D105)10230己#DIV/0!10390庚010450辛#DIV/0!10520壬#DIV/0!F列公式=0/(C97:C105=H99)注：利用lookup函数忽略错误值的特点，可以实现查找符合条件的最后一个结果，Lookup条件查找结构=LOOKUP(1,0/(条件区域=条件),对应结果区域)，可用于正向查找、反向查找、错位

8、查找、多条件查找三、 二分位查找的速度二分法的运算速度：65536个数据的查找最多用16次；1048576个数据查找最多用20次第二讲：二分法查找（二）示例一、 查找最后一个文本或数字原理：只要查找的值比查找范围内任何一个值大，就会返回最后一个值1. 查找最后一个文本查找文本一般用“”（可在excel中输入ALT+41385或者搜狗输入法下V1）,除非“”落在二分位上还可以用“座”，除非“座”字落在二分位上2. 查找最后一个数字Excel中可以显示的最大数9E+307，还可以写9323，实际9323要大于9E+307二、 提取数字1. 数字在字符串前CDF列公式F9030个 =-LOOKUP(

9、1,-LEFT(C9,ROW($1:$10)301072.1平方 =-LOOKUP(1,-LEFT(C10,ROW($1:$10)72.1111.2KG =-LOOKUP(1,-LEFT(C11,ROW($1:$10)1.2注：1、省字符的小技巧，加一个负号将文本都转换了负数，都不大于0，因此可以通过查找1来提取数字2、这个函数有个缺点就是只能提取数字，不能提取完整的数字段，例如“030个”，只能提取出“30”而不能提取“030”2. 数字在字符串中间CF列公式F19苹果10个=-LOOKUP(1,-MIDB(C19,SEARCHB("?",C19),ROW($1:$9)1

10、020第05节=-LOOKUP(1,-MIDB(C20,SEARCHB("?",C20),ROW($1:$9)521水4.5公斤=-LOOKUP(1,-MIDB(C21,SEARCHB("?",C21),ROW($1:$9)4.5F列中还可以=-LOOKUP(1,-MID(C19,MATCH(0,MID(C19,ROW($1:$9),1)*0,),ROW($1:$9)三、 指定月份最大天数CDE30月份天数D列公式31131 =DAY(-LOOKUP(,-(C31&-ROW($1:$31)32228 =DAY(-LOOKUP(,-(C32&

11、;-ROW($1:$31)33331 =DAY(-LOOKUP(,-(C33&-ROW($1:$31)34430 =DAY(-LOOKUP(,-(C34&-ROW($1:$31)35531 =DAY(-LOOKUP(,-(C35&-ROW($1:$31)36630 =DAY(-LOOKUP(,-(C36&-ROW($1:$31)37731 =DAY(-LOOKUP(,-(C37&-ROW($1:$31)38831 =DAY(-LOOKUP(,-(C38&-ROW($1:$31)39930 =DAY(-LOOKUP(,-(C39&-ROW(

12、$1:$31)401031 =DAY(-LOOKUP(,-(C40&-ROW($1:$31)411130 =DAY(-LOOKUP(,-(C41&-ROW($1:$31)421231 =DAY(-LOOKUP(,-(C42&-ROW($1:$31)注：超过月份最大日期的部分会变成错误值，lookup会忽略错误值四、 去除重复项CDFGHIJKL45结果46结果戊47结果戊丁48结果戊丁己49结果戊丁己乙50结果戊丁己乙丙51结果结果戊丁己乙丙甲52戊戊011111153丁丁001111154丙己000001155甲乙000000156己丙000111157己甲00011

13、1158丙000001159己000111160戊011111161乙000011162己000111163丁001111164戊0111111分析：每列最后一个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)&""五、 合并单元格统计数据源CDFQR68品名销量品名品名销量69A523AA1

14、66870567AB58971578AC206372B589BD64873C651CE104074648C75764C最大销量206376D648D77E578E78462E分析：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$

15、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函数，

16、公式中的数组以常量形式出现的大多数情况下不需要组合键3、 内存数组4、 伪内存数组伪内存数组的情况：1) F9的结果与显示内容不一致正常情况下，内存数组在单元格内显示的结果和在公式中F9后显示的结果一致2) 无法进行再运算可以在结果外套sum函数来检查3) 无法用Index查看数组中的每一个值用index逐一显示数组中的各个值（这种方法不仅可以检查真伪内存数组，还可以分辨多维引用和内存数组）伪内存数组有哪些1) vlookup是典型的伪内存数组例如CDEFG13ABAB14甲1乙215乙2丙316丙3甲117丁4丁4选中G14：G17输入公式=VLOOKUP(F14:F17,C:D,2,)但是

17、选中单元格内公式按F9后结果显示2，而不是2;3;1;4说明这不是内存数组；且用sum函数求和=SUM(VLOOKUP(F14:F17,C:D,2,)后的结果是一个数2；最重要的是把VLOOKUP(F14:F17,C:D,2,)当做index的参数逐一显示，如果是内存数组，结果会是数组一个数组2) Index非引用结果行数字和列数字至少一个是数组时结果构成伪内存数组例如CDEFG21甲子Index22乙丑乙乙23丙寅丁丁24丁卯选中F22：G23输入公式=INDEX("甲","子""乙","丑""丙&qu

18、ot;,"寅""丁","卯",2;4)，如果结果是内存数组则应显示为"乙","丑""丁","卯"3) Index引用结果行数字和列数字至少一个是数组时结果不构成二维引用和多维引用二、 多维引用多维引用特征一：多数多维引用的结果无法在一个二维区域内显示1. Indirect、Offset行数字和列数字至少一个是数组时结果构成多维引用1) 例1：=INDIRECT("列标"&ROW(A1)/COLUMN(A1)=INDIRECT(&

19、quot;R"&ROW(A1)/COLUMN(A1)&"C"&ROW(A1)/COLUMN(A1),)数据源 INDIRECT扩展CDEFGHI375050#VALUE!#VALUE!#VALUE!E37内公式=INDIRECT("C"&ROW(A37)选中G37:I37后输入=INDIRECT("C"&ROW(A37)*COLUMN(A:C)让其参与运算，结果是错误值；所以它是一个多维引用；多数的多维引用无法在一个二维区域正常显示indirect的参数是常量时候，返回的结果不是二维引

20、用2) 例2：=INDIRECT("行号或列标"&ROW(1:3)/COLUMN(A:C),参数)CDEFG41数据源各平面结果42100C42#VALUE!43200C43#VALUE!44300C44#VALUE!选中E42：E44后输入="C"&ROW(42:44)选中G42：G44后输入=INDIRECT("C"&ROW(42:44)显示错误值，其实并没有错误，只是多维引用无法在二维平面显示注意：与INDIRECT（”C1：C10”）有差别，INDIRECT（”C1：C10”）是一个平面，=INDIRE

21、CT("C"&ROW(42:44)是三个平面3) 例3：=INDIRECT("A1:C"&ROW(1:3)=INIDRECT("R1C1:R1C"&COLUMN(A:C),)CDEFGHI49数据源各平面结果50987c50:E50#VALUE!51654c50:E51#VALUE!52321c50:E52#VALUE!选中G50:G52后输入="c50:E"&ROW(50:52)，本例子同例2一样是3个平面，例1是一个平面，本例与前两例不同的是本例子每个平面有不止一个数字选中I50

22、: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)&"

23、;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的CDEFGHI62数据源结果与数组运算635050#VALUE!#VALUE!#VALUE!和indirect的第一种情况类似，表

24、面来看是普通公式，但是其内部是多维引用，一扩展就可以看出来E63单元格内=OFFSET(B63,ROW(A1)6) 例6：=OFFSET(起点,ROW(1:3)/COLUMN(A:C),)=OFFSET(起点,ROW(1:3)/COLUMN(A:C)CDEFGHI68数据源下移各平面结果69100#VALUE!70200#VALUE!71300#VALUE!选中I69:I71后输入=OFFSET(C68,ROW(1:3),)7) 例7:=OFFSET(起点,ROW(1:3),ROW(1:3)=OFFSET(起点,COLUMN(A:C),COLUMN(A:C)CDEFGHIJKL76数据源下移右

25、移各平面结果77987#VALUE!78654#VALUE!79321#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)CDEFGHIJKLMN84右移85数据源下移123结果8698711 11 21 3#VALUE!#VALUE!#VALUE!8765422 12 22 3#VALUE!#VALUE!#VALUE!8832133 13 23 3#VALUE!#VALUE!#VALUE!选中L86:M88后输入=OFFSET(B85,ROW(1:3),COLUMN(A:C)此例子是9个平面9) 例9:OFFSET函数可以把原来的区域撑大，