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函數可以把原來的區域撐大，