EXCEL函数高级应用笔记

目录

第一讲：二分法查找（一）查找原理 (1)

一、使用二分法查找的函数 (1)

1. 历遍法（遍历法）查找 (1)

2. 二分法查找 (1)

二、二分位查找的各种情况 (1)

1. 查找值等于二分位值 (1)

2. 查找值大于二分位值 (2)

3. 查找值小于二分位值 (2)

4. 查到不符合条件的出现后取最后一个符合条件的结果 (2)

5. 查找最后一个 (3)

6. 混和 (3)

7. 忽略逻辑值、错误值等 (3)

三、二分位查找的速度 (4)

第二讲：二分法查找（二）示例 (4)

一、查找最后一个文本或数字 (4)

1. 查找最后一个文本 (4)

2. 查找最后一个数字 (4)

二、提取数字 (4)

1. 数字在字符串前 (4)

2. 数字在字符串中间 (4)

三、指定月份最大天数 (5)

四、去除重复项 (5)

五、合并单元格统计 (6)

第三讲内存数组与多维引用（一） (6)

一、数组公式 (6)

二、多维引用 (7)

第四讲内存数组与多维引用（二） (10)

一、多维引用(续) (10)

第一讲：二分法查找（一）查找原理

一、使用二分法查找的函数

1.历遍法（遍历法）查找

适用函数：Match、Vlookup、Hlookup等函数的精确查找。

查找原理：是从上之下或者从左至右一个个查找，直到找到合适的为止

2.二分法查找

适用函数：Lookup函数、Match、Vlookup、Hlookup等函数的模糊查找；

查找原理：采用二分法查找时，数据需是排好序的。基本思想：假设数据是按升序排序的，对于给定值x，从序列的中间位置开始比较，如果当前位置值等于x，则查找成功；若x小于当前位置值，则在数列的前半段中查找；若x大于当前位置值则在数列的后半段中继续查找，直到找到为止；

水管原理：华罗庚提出，一半一半的查找

二、二分位查找的各种情况

二分位：=INT((1+个数)/2)

1.查找值等于二分位值

分析：二分位是60，要查找的数是90>60，在C42：C45间查找，二分位80<90，在C44：C45间查找，二分位50<90，最终查找的数字是10对应的是壬

分析：查找到二分位等于要查找的值后，继续向下比较，发现还等于要查找的值，继续向下查找直到不等于要查找的值

注：只要查找值大于数组内的每一个值，那么最后结果就会查找到最后一个数对应的结果；在excel表中常用9E+307代表最大的数字，来查找最后一个数字；文本时候一般用”々”（EXCEL中快速输入是ALT+41385），但是”々”（搜狗可通过V1打出来）不是文字当中的最大值，文字当中的最大值是”隝” dǎo （EXCEL中快捷输入是ALT+65103）

F列公式{=0/(C97:C105=H99)}

注：利用lookup函数忽略错误值的特点，可以实现查找符合条件的最后一个结果，Lookup条件查找结构=LOOKUP(1,0/(条件区域=条件),对应结果区域)，可用于正向查找、反向查找、错位查找、多条件查找

三、二分位查找的速度

二分法的运算速度：65536个数据的查找最多用16次；1048576个数据查找最多用20次

第二讲：二分法查找（二）示例

一、查找最后一个文本或数字

原理：只要查找的值比查找范围内任何一个值大，就会返回最后一个值

1.查找最后一个文本

查找文本一般用“々”（可在excel中输入ALT+41385或者搜狗输入法下V1）,除非“々”落在二分位上

还可以用“座”，除非“座”字落在二分位上

2.查找最后一个数字

Excel中可以显示的最大数9E+307，还可以写9^323，实际9^323要大于9E+307

二、提取数字

F列中还可以{=-LOOKUP(1,-MID(C19,MATCH(0,MID(C19,ROW($1:$9),1)*0,),ROW($1: $9)))}

三、指定月份最大天数

四、去除重复项

只要查找最后个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$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},这时候需要用到mmul t函数，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是典型的伪内存数组

但是选中单元格内公式按F9后结果显示2，而不是{2;3;1;4}说明这不是内存数组；且用sum函数求和{=SUM(VLOOKUP(F14:F17,C:D,2,))}后的结果是一个数2；最重要的是把VLOOKUP(F14:F17,C:D,2,)当做index的参数逐一显示，如果是内存数组，结果会是数组一个数组

2)Index非引用结果行数字和列数字至少一个是数组时结果构成伪内存数组

甲","子";"乙","丑";"丙","寅";"丁","卯"},{2;4})}，如果结果是内存数组则应显示为{"乙","丑";"丁","卯"}

3)Index引用结果行数字和列数字至少一个是数组时结果不构成二维引用和多

维引用

二、多维引用

多维引用特征一：多数多维引用的结果无法在一个二维区域内显示

1.Indirect、Offset行数字和列数字至少一个是数组时结果构成多维引用

1)例1：

=INDIRECT("列标"&ROW(A1)/COLUMN(A1))

选中G37:I37后输入{=INDIRECT("C"&ROW(A37))*COLUMN(A:C)}让其参与运算，结果是错误值；所以它是一个多维引用；多数的多维引用无法在一个二维区域正常显示

indirect的参数是常量时候，返回的结果不是二维引用

2)例2：

参数)}

选中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))}

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))

一扩展就可以看出来

E63单元格内=OFFSET(B63,,ROW(A1))

6)例6：

{=OFFSET(起点,ROW(1:3)/COLUMN(A:C),)}

7)例7:

{=OFFSET(起点,ROW(1:3),ROW(1:3))}

8)例8:

{=OFFSET(起点,ROW(1:3),COLUMN(A:C))}

此例子是9个平面

9)例9:

OFFSET函数可以把原来的区域撑大，如果数组发生在OFFSET函数的最后2个参数上，如以下情况

=OFFSET(起点,,,ROW(A1)/COLUMN(A1),ROW(A1)/COLUMN(A1))

{=OFFSET(起点,,,ROW(1:3)/COLUMN(A:C),)}

{=OFFSET(起点,,,,ROW(1:3)/COLUMN(A:C))}

{=OFFSET(起点,,,ROW(1:3),ROW(1:3))}

{=OFFSET(起点,,,COLUMN(A:C),COLUMN(A:C))}

{=OFFSET(起点,,,ROW(1:3),COLUMN(A:C))}

{=OFFSET(起点,,,COLUMN(A:C),ROW(1:3))}

2.Offset第一个参数是多维引用时结果构成多维引用

1)例1：

H117中输入{=SUM(OFFSET(INDIRECT("D"&ROW(117:120)),,))} 2)例2:

{=OFFSET(多维引用起点,数,数,数,数)}

结果为多维引用

第四讲内存数组与多维引用（二）

一、多维引用(续)

多维引用特征二:多维引用可以通过某些公式转成内存数组1.SUM函数