EXCEL函数高级应用笔记.docx

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

2、；水管原理：华罗庚提出，一半一半的查找二、二分位查找的各种情况二分位：=INT(1+个数)/2)3.查找值等于二分位值情况一：查找范围元素个数是奇数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)4.查找值大于二分位值CDEFGHI3720甲3890乙查找结果

3、3930丙90壬4020丁4160戊4290己904380庚804450辛50504510壬101010I39中公式=LOOKUP(H39,C37:C45,D37:D45)分析：二分位是60，要查找的数是9060，在C42：C45间查找，二分位8090，在C44：C45间查找，二分位50),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中公式=

4、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)无法用I

5、ndex查看数组中的每一个值用index逐一显示数组中的各个值（这种方法不仅可以检查真伪内存数组，还可以分辨多维引用和内存数组）伪内存数组有哪些1)vlookup是典型的伪内存数组例如CDEFG13ABAB14甲1乙215乙2丙316丙3甲117丁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的参数逐一显示，如果是内存

6、数组，结果会是数组一个数组2)Index非引用结果行数字和列数字至少一个是数组时结果构成伪内存数组例如CDEFG21甲子Index22乙丑乙乙23丙寅丁丁24丁卯选中F22：G23输入公式=INDEX(甲,子;乙,丑;丙,寅;丁,卯,2;4)，如果结果是内存数组则应显示为乙,丑;丁,卯3)Index引用结果行数字和列数字至少一个是数组时结果不构成二维引用和多维引用二、多维引用多维引用特征一：多数多维引用的结果无法在一个二维区域内显示1.Indirect、Offset行数字和列数字至少一个是数组时结果构成多维引用1)例1：=INDIRECT(列标&ROW(A1)/COLUMN(A1)=INDIR

7、ECT(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的参数是常量时候，返回的结果不是二维引用2)例2：=INDIRECT(行号或列标&ROW(1:3)/COLUMN(A:C),参数)CDEFG41数据源各平面结果42

8、100C42#VALUE!43200C43#VALUE!44300C44#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),)CDEFGHI49数据源各平面结果50987c50:E50#VA

9、LUE!51654c50:E51#VALUE!52321c50: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,)=INDIRE

10、CT(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的第一种情况类似，表面来看是普通公式，但是其内部是多维引用，一扩展就可以看出来E63单元格内=OFFSET(B63,ROW(A1)6)例6：=OFFSET(起点,ROW(1:3)/COLUMN(A:C),)=O

11、FFSET(起点,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数据源下移右移各平面结果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