1、公式及函數(shù)的高級應(yīng)用數(shù)組公式及其應(yīng)用 一、數(shù)組公式涵義 數(shù)組公式就是可以同時進展多重計算并返回一種或多種結(jié)果的公式。 數(shù)組參數(shù)：數(shù)組公式中使用的兩組或多組數(shù)據(jù)，它可以是一個數(shù)據(jù)區(qū)域也可以是數(shù)組常量。 注意：公式中的每個數(shù)組參數(shù)必須有一樣的行與列。 二、數(shù)組公式的輸入、編輯與刪除 一數(shù)組公式的輸入 1、輸入步驟： 1選定單元格或單元區(qū)域； 2輸入數(shù)組公式； 3同時按下“Ctrl+Shift+Enter組合鍵。 2、輸入數(shù)組常量的方法 數(shù)組常量：直接鍵入的數(shù)值數(shù)組。 1選取單元區(qū)域 2在公式編輯欄中輸入數(shù)組公式 3同時按下“Ctrl+Shift+Enter組合鍵 注意：直接在公式輸入數(shù)值，必須用“

2、 括?。?不同列的數(shù)值用逗號隔開； 不同行的數(shù)值用分號隔開。 2、輸入數(shù)組公式的方法 1選取單元區(qū)域 2在公式編輯欄中輸入數(shù)組公式 3同時按下“Ctrl+Shift+Enter組合鍵二編輯數(shù)組公式 編輯步驟： 1、在數(shù)組區(qū)域中單擊任一單元格； 2、單擊公式編輯欄，當(dāng)編輯欄被激活時，“ 在數(shù)組公式中消失； 3、編輯數(shù)組公式內(nèi)容； 4、修改完后，按“Ctrl+Shift+Enter組合鍵三刪除數(shù)組公式 步驟： 1、選定存放數(shù)組公式的所有單元格； 2、按下Delete鍵二、數(shù)組公式的應(yīng)用 一計算兩個數(shù)據(jù)區(qū)域的乘積 二計算多個數(shù)據(jù)區(qū)域的和 三同時對多個數(shù)據(jù)區(qū)域進展一樣的計算一、AND函數(shù)、OR函數(shù)、N

3、OT函數(shù) 一AND函數(shù) 1、表示邏輯與，當(dāng)所有條件都滿足時，AND函數(shù)返回TRUE，否那么返回FALSE。 2、格式： =AND條件1，條件2，條件n 二OR函數(shù) 1、表示邏輯或，只要有一個條件都滿足時，該函數(shù)返回TRUE，當(dāng)所有條件都不滿足時才返回FALSE。 2、格式： =OR條件1，條件2，條件n 三NOT函數(shù) 1、只有一個邏輯參數(shù)，可以計算出TRUE或FALSE的邏輯值或邏輯表達式。 2、格式： =NOT條件 注意：以上三個函數(shù)一般與IF函數(shù)結(jié)合使用IF函數(shù) 格式：IFlogical-test,value-if-true,value-if-false 其中： logical-test：

4、為條件； value-if-true：條件為真時執(zhí)行該參數(shù)； value-if-false：條件為假時執(zhí)行該參數(shù)。 如：=IFTRUE，開場,“完畢 =IFA160，及格,“不及格 案例 某企業(yè)根據(jù)各銷售部門的銷售額及銷售費用確定獎金提成比例及提取額，假設(shè)銷售額大于300000元且銷售費用占銷售額的比例不超過1%，那么獎金提取比例為15%，否那么為10%。 IFANDC5300000,D5/C5300000*D5/C52300000,D5/C5300000+D5/C521%, 15%,10%*=AND +=OR 僅限于非數(shù)組公式中。僅限于非數(shù)組公式中。 二、SUM、SUMIF、SUMPRODU

5、CT、DSUM 一無條件求和SUM函數(shù) 1、目的：對指定的假設(shè)干數(shù)值或單元格求和。 2、格式： =SUM參數(shù)1，參數(shù)2，參數(shù)N 3、假設(shè)對連續(xù)的一行或一列數(shù)據(jù)進展求和時，可用工具欄中的“按紐實現(xiàn)。SUM的特殊用法 1、SUMA2：A4*B2：B4=SUMPRODUCT A2：A4，B2：B4 2、SUM+IF：用于多條件求和 二條件求和SUMIF函數(shù) 1、目的：根據(jù)指定條件對假設(shè)干單元格求和。 常用于分類匯總計算。 2、格式： =SUMIFrange，criteria,sum_range range：用于條件判斷的單元區(qū)域； criteria：確定哪些單元格將被相加求和的條件； sum-ran

6、ge：需要求和的實際單元格。 三SUMPRODUCT 函數(shù) 1、目的：在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘并返回乘積之和。 2、格式： =SUMPRODUCTarray1，array2，array3， 3、注意：數(shù)組必須具有一樣的維數(shù)。 假設(shè):A2=3,B2=2,C2=5,D2=4 SUMPRODUCTA2:B2,C2:D2=23 SUMPRODUCTA2:B2=5SUMPRODUCT應(yīng)用于多條件統(tǒng)計方面 公式1 =sumproductA1:A710*A1:A710+A1:A710，用數(shù)組公式，返回一組邏輯值； A1:A710*A1:A710*A1:A710,A1:A710,0+A1:A

7、7200+：表示：表示“強迫將邏輯值進展轉(zhuǎn)換強迫將邏輯值進展轉(zhuǎn)換 四DSUM 函數(shù) 1、目的：返回數(shù)據(jù)清單或數(shù)據(jù)庫的列中滿足指定條件的數(shù)字之和。 2、格式： =DSUMdatabase,field,criteria 3、參數(shù)說明： Database 數(shù)據(jù)清單或數(shù)據(jù)庫。 Field 指定函數(shù)所使用的數(shù)據(jù)列。數(shù)據(jù)清單中的數(shù)據(jù)列必須在第一行具有標(biāo)志項。Field 可以是文本，即兩端帶引號的標(biāo)志項，也可以是代表數(shù)據(jù)清單中數(shù)據(jù)列位置的數(shù)字：1 表示第一列。 Criteria 為一組包含給定條件的單元格區(qū)域?？梢詾閰?shù) criteria 指定任意區(qū)域，只要它至少包含一個列標(biāo)志和列標(biāo)志下方用于設(shè)定條件的單元

8、格。 求和總結(jié)1.連續(xù)區(qū)域求和：自動求和2.不連續(xù)區(qū)域求和：SUM3.基于單條件對數(shù)字求和：SUMIF4.基于多個條件對數(shù)字求和：SUM+IF須用數(shù)組公式確定、SUMPRODUCT5、基于存儲在其他單元區(qū)域中的條件，對數(shù)字求和：DSUM三、查找與引用函數(shù) 一LOOKUP、HLOOKUP、VLOOKUP 1.LOOKUP 返回向量單行區(qū)域或單列區(qū)域或數(shù)組中的數(shù)值。 向量形式：是在單行區(qū)域或單列區(qū)域向量中查找數(shù)值，然后返回第二個單行區(qū)域或單列區(qū)域中一樣位置的數(shù)值； =LOOKUPlookup_value,lookup_vector,result_vector Lookup_vector 的數(shù)值必須

9、按升序排序 數(shù)組形式： 在數(shù)組的第一行或第一列中查找指定數(shù)值，然后返回最后一行或最后一列中一樣位置處的數(shù)值。 =LOOKUPlookup_value,array 注意： 1. array的數(shù)值必須按升序排序 2.假如數(shù)組區(qū)域中的列數(shù)多于行數(shù)，LOOKUP 在第一行查找 lookup_value。 3.假如數(shù)組為正方形，或者區(qū)域中的行數(shù)多于列數(shù)那么 在第一列查找 lookup_value。 數(shù)組形式與函數(shù) HLOOKUP 和函數(shù) VLOOKUP 非常相似。不同之處在于函數(shù) HLOOKUP 在第一行查找 lookup_value，函數(shù) VLOOKUP 在第一列查找，而函數(shù) LOOKUP 那么按照數(shù)

10、組的維數(shù)查找 。 最好使用 HLOOKUP 或 VLOOKUP 來替代函數(shù) LOOKUP 的數(shù)組形式 2、VLOOKUP 功能：用于搜索表區(qū)域首列滿足條件的元素，確定待檢索單元格在區(qū)域中的行列號，再進一步返回選定單元格的值。 格式： VLOOKUPLOOKUP-VALUE，TABLE-ARRAY， COL-INDEX-NUM，RANGE-LOOKUP 其中： LOOKUP-VALUE：需要在數(shù)據(jù)表第一列中查找的數(shù)值； TABLE-ARRAY： 需要在其中查找數(shù)據(jù)的數(shù)據(jù)表； COL-INDEX-NUM：第二參數(shù)中待返回的匹配值的序列號； RANGE-LOOKUP： 邏輯值，指明該函數(shù)返回時是準確

11、匹配 FALSE 還是近似匹配TRUE或省略。 注意： range_lookup 為 TRUE，那么 table_array 的第一列中的數(shù)值必須按升序排列；為 FALSE，table_array 不必進展排序。 HLOOKUP：與：與VLOOKUP含義一含義一樣，只是樣，只是LOOKUP-VALUE是在第是在第一行。一行。 二MATCH函數(shù) 1、目的：返回在指定方式下與指定數(shù)值匹配 的數(shù)組中元素的相應(yīng)位置。 2、格式： =MATCHlookup-value，lookup-array，match-type Lookup_value 為需要在數(shù)據(jù)表中查找的數(shù)值。 Lookup_array 可能包

12、含所要查找的數(shù)值的連續(xù)單元格區(qū)域。 Match_type 為數(shù)字 -1、0 或 1。 其中： 1：查找小于或等于 lookup_value 的最大數(shù)值。Lookup_array 必須按升序排列0： 查找等于 lookup_value 的第一個數(shù)值。Lookup_array 可以按任何順序排列。-1： 查找大于或等于 lookup_value 的最小數(shù)值。Lookup_array 必須按降序排列。假如省略 match_type，那么假設(shè)為 1。 二INDEX函數(shù) 1、目的：返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的 引用。 2、格式一返回值為數(shù)值或數(shù)組 =INDEXarray，row-num,column

13、-num array：單元區(qū)域或數(shù)組常量； row-num：數(shù)組中某行的行號，函數(shù)從該行返回數(shù) 值；假設(shè)省略，那么必須有column-num column-num：數(shù)組中某列的列號，函數(shù)從該列返回 數(shù)值；假設(shè)省略，那么必須有row-num 注意：1、假設(shè)同時使用row-num和column-num， 該函數(shù)將返回row-num和column-num穿插 處單元格的數(shù)值； 2、假設(shè)將row-num或column-num設(shè)置為0，那么 該函數(shù)返回整個行或列的數(shù)組數(shù)值。但這 時，須按數(shù)組公式的形式輸入。 如： INDEX1,2;3,4,2,2 等于 4 假如作為數(shù)組公式輸入，那么： INDEX1,2

14、;3,4,0,2 等于 2;4 格式二：返回值為引用 =INDEXreference,row_num,column_num,area_num 其中： 1、 Reference 對一個或多個單元格區(qū)域的引用； 假如為引用輸入一個不連續(xù)的選定區(qū)域，必須用括號括起來。 假如引用中的每個區(qū)域只包含一行或一列，那么相應(yīng)的參數(shù) row_num 或 column_num 分別為可選項。例如，對于單行的引用，可以使用函數(shù) INDEXreference,column_num。 2、Row_num 引用中某行的行序號，函數(shù)從該行返回一個引用。 3、Column_num 引用中某列的列序號，函數(shù)從該列返回一個引用

15、4、Area_num 選擇引用中的一個區(qū)域，并返回該區(qū)域中 row_num 和 column_num 的穿插區(qū)域。選中或輸入的第一個區(qū)域序號為 1，第二個為 2，以此類推。假如省略 area_num，函數(shù) INDEX 使用區(qū)域 1。 假如引用描繪單元格為 A1:B4,D1:E4,G1:H4，那么 area_num 1 為區(qū)域 A1:B4、area_num 2 為區(qū)域 D1:E4、而 area_num 3 為區(qū)域 G1:H4。 例8 INDEXFruit,2,3 等于引用 C3，內(nèi)容為 38 INDEXA1:C6,A8:C11,2,2,2 等于引用 B9，內(nèi)容為 $3.55 SUMINDEXSt

16、ock,0,3,1 等于 SUMC1:C11 等于 216 SUMB2:INDEXFruit,5,2 等于 SUMB2:B6 等于 2.42INDEX+MATCH INDEXD2:D7,MATCH1,A10=B2:B7*B10=C2:C7,01=TRUE*TRUE ，即兩個條件同時滿足公式必須是數(shù)組確定，即Ctrl + Shift+Enter四、數(shù)據(jù)庫函數(shù)一DGET函數(shù) 目的：從數(shù)據(jù)清單或數(shù)據(jù)庫中提取符合條 件且唯一存在 的值。 假如沒有滿足條件的記錄，那么 DGET 將返回錯誤值 #VALUE!。 假如有多個記錄滿足條件，那么 DGET 將返回錯誤值 #NUM!。 格式：=DGETdatab

17、ase,field,criteria 其中： database：構(gòu)成數(shù)據(jù)清單或數(shù)據(jù)庫的單元區(qū)域； Field：雙引號括住的列標(biāo)簽或該列在列表中的數(shù)字； Criteria：為一組包含給定條件的單元格區(qū)域 二DMAX 功能：返回數(shù)據(jù)清單或數(shù)據(jù)庫的列中滿足指定條件的最大數(shù)值。 格式： =DMAX DatabaseRange, FieldName,CriteriaRange DMIN 與DMAX相反。 三DSUM 四DCOUNT 目的：返回數(shù)據(jù)庫或數(shù)據(jù)清單的列中滿足指定條件并且包含數(shù)字的單元格個數(shù)。 格式：=DCOUNT database,field,criteria 參數(shù) Field 為可選項。假如

18、省略，那么函數(shù) 將返回數(shù)據(jù)庫中滿足條件的所有記錄數(shù)。 五DCOUNTA：返回數(shù)據(jù)庫或數(shù)據(jù)清單的列中滿足指定條件并且包含數(shù)字的非空單元格個數(shù)。 COUNTIF函數(shù) 功能：計算區(qū)域中滿足給定條件的單元格的個數(shù) 格式：COUNTIFrange,criteria 其中： Range 為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。 Criteria 為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。計數(shù)函數(shù)總結(jié) 1、根據(jù)條件計數(shù)：COUNTIF、DCOUNT、DCOUNTA 2、區(qū)域中空白單元格的個數(shù)：COUNTBLANK 3、列表中含數(shù)字或文本的單元格個數(shù)：COUNTA 4、統(tǒng)計列

19、表中包含數(shù)字的單元格的個數(shù)：COUNT文本函數(shù) 1、EXACT 功能：測試兩個字符串是否完全一樣。假如它們完全一樣，那么返回 TRUE；否那么，返回 FALSE。函數(shù) EXACT 能區(qū)分大小寫，但忽略格式上的差異。 格式：= EXACTtext1,text2 2、FIND 功能：用于查找其他文本字符串 within_text 內(nèi)的文本字符串 find_text，并從 within_text 的首字符開場返回 find_text 的起始位置編號。 格式：=FINDfind_text,within_text,start_num 3、FINDB 功能：用于查找其他文本字符串 within_text 內(nèi)的文本字符串 find_text，并基于每個字符所使用的字節(jié)數(shù)從 within_text 的首字符開場返回 find_text 的起始位置編號。 此函數(shù)用于雙字節(jié)字符 4、LEFT 功能：基于