office辦公自動化excel函數(shù)應用大全

1、目錄index函數(shù)用法和實例應用INDEX和MATCH函數(shù)應用index函數(shù)用法和實例應用內容提要：本文通過直觀的實例來認識Excel中index函數(shù)的使用方法，更進階的用法可以參考文章最后的INDEX和MATCH函數(shù)應用。Excel中INDEX函數(shù)是很常用的查找引用函數(shù)。通常情況下，嵌套其余函數(shù)一起使用，比如index+match。在使用INDEX()函數(shù)時，第二、三參數(shù)一般情況與MATCH()函數(shù)配合使用，以實現(xiàn)動態(tài)查找引用的目的。第一：index函數(shù)用法解釋INDEX函數(shù)的用法是返回列表或數(shù)組中的元素值，此元素由行序號和列序號的索引值確定。即行列交叉點所在的引用。 INDEX函

2、數(shù)的語法為： INDEX(區(qū)域,行,列) 需要注意一點的是：此處的行列序號是相對于第一參數(shù)的區(qū)域而言，不是Excel工作表中的行或列序號。如果區(qū)域只包含一行或一列，則相對應的參數(shù) row_num 或 column_num可以相應省略1個。第二：index函數(shù)簡單舉例應用如下表所示，根據(jù)A1：D5單元格區(qū)域，使用index函數(shù)查找A8單元格對應的產品名稱。提供三種解法，任意一種均可。一：=INDEX(A4:D4,1,4)二：=INDEX(A4:D4,4)三：=INDEX(A1:D5,4,4)解法一，在A4:D4一行中，查找第一行，第四列的值。解法二，根據(jù)第一部分index函數(shù)解釋，如果

3、區(qū)域只包含一行，則對應的行參數(shù)可以省略，因此省略后就成為：=INDEX(A4:D4,4)。解法三：在A1:D5區(qū)域，查找第四行，第四列的值。本文只是index函數(shù)的一個基礎用法，更進階的用法，請參考：INDEX和MATCH函數(shù)應用INDEX和MATCH函數(shù)應用內容提要：本講座通過案例講解INDEX和MATCH函數(shù)嵌套使用的方法。第一部分：INDEX和MATCH函數(shù)用法介紹第一，MATCH函數(shù)用法介紹MATCH函數(shù)也是一個查找函數(shù)。MATCH 函數(shù)會返回匹配值的位置而不是匹配值本身。在使用時，MATCH函數(shù)在眾多的數(shù)字中只查找第一次出現(xiàn)的，后來出現(xiàn)的它返回的也是第一次出現(xiàn)的位置。MATCH函數(shù)語

4、法：MATCH(查找值，查找區(qū)域，查找模式)可以通過下圖來認識MATCH函數(shù)的用法：=MATCH(41,B2:B5,0)，得到結果為4，返回數(shù)據(jù)區(qū)域 B2:B5 中 41 的位置。=MATCH(39,B2:B5,1)，得到結果為2，由于此處無正確匹配，所以返回數(shù)據(jù)區(qū)域 B2:B5 中(38) 的位置。注：匹配的查找值，MATCH 函數(shù)會查找小于或等于(39)的最大值。=MATCH(40,B2:B5,-1)，得到結果為#N/A，由于數(shù)據(jù)區(qū)域 B2:B5 不是按降序排列，所以返回錯誤值。第二，INDEX函數(shù)用法介紹INDEX函數(shù)的功能就是返回指定單元格區(qū)域或數(shù)組常量。如果同時使用參數(shù)行號和列號，函

5、數(shù)INDEX返回 行號和列號交叉處的單元格中的值。INDEX函數(shù)語法：INDEX(單元格區(qū)域，行號，列號)可以通過下圖來認識INDEX函數(shù)的用法：=INDEX(A1:C6,2,3)，意思就是返回A1:C6中行號是2 列號是3 ,即第二行與第三列的交叉處，也就是C2單元格的值，為84。第二部分：INDEX和MATCH函數(shù)應用案例介紹下圖工作表所示的是一個產品的型號和規(guī)格的價格明細表。通過這個表的數(shù)據(jù)，進行一些對應的查詢操作。第一，單擊B5單元格下拉按鈕，選擇型號，然后在B6單元格完成型號所在行號的查詢。如下圖所示：隨意選擇一個型號，比如A0110，然后在B6單元格輸入公式：=MATCH($B$5

6、,$D$4:$D$12,0)，得到結果1。公式解釋：用MATCH函數(shù)查找B5單元格這個型號在D4:D12區(qū)域中對應的位置。其中的0參數(shù)可以省略不寫。MATCH函數(shù)中0代表精確查找，1是模糊查找。第二，單擊B9單元格下拉按鈕，選擇規(guī)格，然后在B10單元格完成規(guī)格所在列號的查詢。如下圖所示：隨意選擇一個規(guī)格，比如101，然后在B10單元格輸入公式：=MATCH(B9,E3:G3,0)，得到結果1。第三，查詢B6和B10單元格所對應的價格。價格的查詢，可以使用index函數(shù)完成，輸入公式：=INDEX(E4:G12,B6,B10)可以得到結果為78。嵌套上面的match函數(shù)，可以將公式改為：=IND

7、EX(E4:G12,MATCH(B5,D4:D12,0),MATCH(B9,E3:G3,0)。大家可以變化C3中的型號來看看結果是否正確。通過下面工作表的源數(shù)據(jù)，利用index函數(shù)實現(xiàn)行列匯總查詢。INDEX函數(shù)的幫助文件知道，如果將 row_num 或 column_num 設置為 0，函數(shù) INDEX 則分別返回整個列或行的數(shù)組數(shù)值。通過用法說明，來實現(xiàn)上圖的行列匯總。首先，單擊C3單元格下拉按鈕，選擇數(shù)據(jù)，比如選擇A0111，然后在C4單元格進行C3單元格對應的行號查找，公式為：=MATCH(C3,E2:E10,)，得到結果為2，說明A0111在E2:E10區(qū)域的第二行。然后對行號所對應

8、的價格進行匯總求和。在C5單元格輸入公式：=SUM(INDEX(F2:H10,MATCH(C3,E2:E10,),)如果選中公式中的INDEX(F2:H10,MATCH(C3,E2:E10,),)部分，按下F9鍵，執(zhí)行結果是80,97,84，可以看到就是對第二行的數(shù)據(jù)進行求和。同樣的方法，可以進行列匯總，在C9單元格輸入公式：=SUM(INDEX(F2:H10,MATCH(C7,F1:H1,)。說明：查找行號和查找列號，只是過渡一下，幫助新手朋友加深對match函數(shù)的理解和使用，對熟悉的朋友，可以直接在行匯總一步完成公式的輸入。根據(jù)下面的工作表，進行區(qū)域匯總求和。首先，分別在開始行號、結束行號

9、、結束列號、結束列號選定需要求和的區(qū)域，比如A0110、A0111、201、301。此題可以套用下圖的格式進行求和。因此，在C20單元格輸入求和公式：=SUM(INDEX(F16:H24,MATCH(C16,E16:E24,),MATCH(C17,E16:E24,):INDEX(F16:H24,MATCH(C18,F15:H15,),MATCH(C19,F15:H15,)從身份證號碼中提取性別=IF(MOD(MID(A2,15,3),2),"男","女")根據(jù)出生年月計算年齡=DATEDIF(A2,TODAY(),"y")&&

10、quot;周歲"某個日期是星期幾=TEXT(A2,"aaaa")某個日期所在月有幾天=DAY(EOMONTH(A2,0)下一個工作日是哪天=WORKDAY(A2,1,A5:A7)區(qū)分指定日期是上中下旬=LOOKUP(DAY(A2),0,11,21,"上旬","中旬","下旬")根據(jù)身份證號計算出生年月=-TEXT(MID(A2,7,8),"0-00-00")查找最后一條符合條件的記錄公式的模式化寫法為：=LOOKUP(1,0/(條件區(qū)域=條件),查詢區(qū)域)查詢符合多個條件的記錄公式的模

11、式化寫法為：=LOOKUP(1,0/(條件區(qū)域1=條件1)*(條件區(qū)域2=條件2),查詢區(qū)域)逆向查詢LOOKUP函數(shù)最后的查詢區(qū)域可以靈活寫成任意一列，從右至左、自下而上查詢，統(tǒng)統(tǒng)沒問題。如下圖所示，要查詢原料類別為"A3"的采購員姓名。C13單元格公式為：=LOOKUP(1,0/(B4:B11="A3"),A4:A11)查詢最后一筆業(yè)務日期如下圖所示，要查詢不同業(yè)務員最后一筆業(yè)務的日期。公式的模式化寫法為：=LOOKUP(1,0/(條件區(qū)域<>""),查詢區(qū)域)模糊查詢考核等級如下圖所示，要根據(jù)E:F列的等級對照表，判

12、斷B列的考核成績。C2單元格公式為：=LOOKUP(B4,E$5:F$8)這種方法可以取代IF函數(shù)完成多個區(qū)間的判斷查詢，前提是對照表的首列，必須是升序處理。提取單元格內的數(shù)字如下圖所示，要從A列的混合內容中提取出數(shù)值。A2公式為=-LOOKUP(1,-LEFT(A2,ROW($1:$99)首先用LEFT函數(shù)從A2單元格左起第一個字符開始，依次返回長度為ROW($1:$99)也就是1至99的字符串，添加負號后，數(shù)值轉換為負數(shù)，含有文本字符的字符串則變成錯誤值。LOOKUP函數(shù)使用1作為查詢值，在由負數(shù)、0和錯誤值構成的數(shù)組中，忽略錯誤值提取最后一個等于或小于1的數(shù)值。最后再使用負號，將提取出的

13、負數(shù)轉為正數(shù)。sumifs函數(shù)多條件求和實例日期：2011-12-07   來源：IT部落窩  作者：IT部落窩   閱讀：171540次字體：大 中 小內容提要：文章首先介紹sumifs函數(shù)基本用法，然后以一個綜合的實例來剖析sumifs函數(shù)的詳細深入使用。第一部分，sumifs函數(shù)用法介紹excel中sumifs函數(shù)是2007以后版本新增的多條件求和函數(shù)。sumifs函數(shù)的語法是：SUMIFS(求和區(qū)域, 條件區(qū)域1,條件1, 條件區(qū)域2,條件2, .)說明：以內的條件區(qū)域2、條件2為可選參數(shù)。最多允許 127 個區(qū)域/條件對。第二部分，s

14、umifs函數(shù)實例介紹項目一：客戶A的銷售額=SUMIFS(C2:C10,A2:A10,A2)項目二：客戶A的1月份銷售額=SUMIFS(C2:C10,A2:A10,A2,B2:B10,B2)項目三：客戶A的1月份和3月份銷售額=SUM(SUMIFS(C2:C10,A2:A10,A2,B2:B10,1,3)項目四：客戶A和C的銷售額=SUM(SUMIFS(C2:C10,A2:A10,"A","C")項目五：客戶A和C的1月份銷售額合計=SUM(SUMIFS(C2:C10,A2:A10,"A","C",B2:B10,B2)項目六：客戶A的1月份和客戶C的3月份銷售額合計=SUM(SUMIFS(C2:C10,A2:A10,"A","C",B2:B10,1,3)項目七：客戶A和客戶C的1月份3月份4月份銷售額合計=SUM(SUMIFS(C2:C10,A2