《Excel函數(shù)教程》PPT課件.ppt

歐派財務(wù)部 Excel函數(shù)教程 作成：楊春梅 歐派財務(wù)部 一，數(shù)組介紹 二， SUMIF函數(shù) 三， VLOOKUP函數(shù) 四， COUNTIF函數(shù) 五， MATCH函數(shù) 六， SUMPRODUCT函數(shù) 七， LOOKUP函數(shù) 八，日期與時間函數(shù) 九，數(shù)學函數(shù) 十，統(tǒng)計函數(shù) 十一，查找與引用函數(shù) 十二，文本函數(shù) 十三，邏輯、信息函數(shù) 01 05 09 14 18 21 27 31 32 35 37 39 43 生產(chǎn)管理科 復雜函數(shù)的運算原理 數(shù)組運算 運算類型： 1）四則運算：、 、 2）邏輯運算：、 、 、 生產(chǎn)管理科 以數(shù)組的角度理解函數(shù)應(yīng)用 理解最基本原理，學會舉一反三 授人于魚，不如授人于漁 數(shù)組的運算原理 1， 1， 0， 0， 1， 0， 0， 0， 1， 0， 1 0， 1， 1， 0， 1， 1， 0， 0， 1， 1， 1 = ? 0 , 1 , 0 , 0 , 1 , 0 , 0 , 0 , 1 , 0 , 1 1， 1， 0， 0， 1， 0， 0， 0， 1， 0， 1 0， 1， 1， 0， 1， 1， 0， 0， 1， 1， 1 0， 1， 0， 0， 1， 0， 0， 0， 1， 0， 1 | | | | | | | | | | | 1）相運算的數(shù)組里數(shù)字的個數(shù)必須相等，如例子，數(shù)組 1與數(shù)組 2均為 11個。 2）運算時，數(shù)組中的數(shù)字，按順序分別進行計算，不會交叉或錯亂。 3）數(shù)組進行求和， SUM(0,1,0,0,1,0,0,0,1,0,1)=0+1+0+0+1+0+0+0+1+0+1=4 相當于： SUM(0,1,0,0,1,0,0,0,1,0,1) 運算方法如下 SumIF 單條件求和函數(shù) H7:H19=E,E,D,E,E,A,C,B,C,A,D,A,B M7=A，在函數(shù)中，自動轉(zhuǎn)換成數(shù)組形 式，即 A,A,A,A,A,A,A,A,A,A,A,A,A E,E,D,E,E,A,C,B,C,A,D,A,B A,A,A,A,A,A,A,A,A,A,A,A,A | | | | | | | | | | | | | 0,0,0,0,0,1,0,0,0,1,0,1,0 1）按次序逐對進行比較。 2）相符（ TRUE）為 1，不相符（ FALSE） 為 0。 第步得出的 結(jié)果，再進行 下一步運算 需要求 A組 的段取時 間累計， 怎么做呢？ SumIF 單條件求和函數(shù) 0,0,0,0,0,1,0,0,0,1,0,1,0 第步比較運算得出的結(jié)果： 再與 K7： K19（如下）數(shù)據(jù)按順序相乘 0.50,0.83,1.170.50,0.50,0.33,0.67 結(jié)果 0,0,0,0,0,0.50,0,0,0,0.50,0,0.33,0 最后求和得： 1.33 1 1=1 0 1=0 SumIF 單條件求和函數(shù) SumIf（ Range， Criteria， Sum_Range） Range：判斷區(qū)域，一維數(shù)組 Criteria：條件，可以是固定值 Sum_Range：求和區(qū)域，一維數(shù)組 運算步驟及注意點： 1） Range與 Criteria進行邏輯運算，得出結(jié)果 2）結(jié)果 與 Sum_Range進行相乘運算 3） Range與 Sum_Range的范圍大小必須一致 SumIF 單條件求和函數(shù) SumIf（ Range， Criteria， Sum_Range） 例子： 1） SUMIF（ A1:A100， D2， B1:B100） 2） SUMIF（ A1:A100，” A” ， B1:B100） 3） SUMIF（ A1:A100，” 3” ， B1:B100） 解釋： 1）匯總等于 D2單元格的所有值 2）匯總等于” A” 的所有值 3）匯總大于 3的所有值 Vlookup 單條件引用函數(shù) A1,A2,A3,A4,A5,A6,A7 B1,B2,B3,B4,B5,B6,B7 二維數(shù)組 D2， D2， D2， D2， D2， D2， D2 A1， A2， A3， A4， A5， A6， A7 首先 D2與首列進行逐個比較運算 逐一按 順序進 行比較 | 0， 0， 0， 0， 1， 0 結(jié)果 Vlookup 單條件引用函數(shù) A1,A2,A3,A4,A5,A6,A7 B1,B2,B3,B4,B5,B6,B7 二維數(shù)組 第 步結(jié)果 0， 0， 0， 0， 1， 0 與第 2列比較 B1,B2,B3,B4,B5,B6,B7 返回 1對 應(yīng)的數(shù)據(jù) B6=Z-0 Vlookup 單條件引用函數(shù) Vlookup（ lookup_value,table_array,col_index_num,range_lookup） Lookup_value： 條件值，是值，非區(qū)域，比如 A1，而 非 A1:A100，即使 A1:A100，也只選擇左上角值 A1 Table_array： 搜索的區(qū)域 /范圍，是一個多維數(shù)組，如 A1:D100之類 Col_index_num： 需要返回的值所在列，從搜索范圍 的起始列開始算 Range_lookup： 匹配方式，一般使用精確匹配，即 0 或 FALSE I:10-15成品庫報表 .xls Vlookup 單條件引用函數(shù) Vlookup（ lookup_value,table_array,col_index_num,range_lookup） 例子： 1） Vlookup（ D2， A1:C100， 2， 0） 2） Vlookup（” 848K 15293” ， A1:C100， 2， 0） 3） Vlookup（ D2COUNTBLAN用 于計算區(qū)域內(nèi)空值單元格的個數(shù) 2）空值單元格不等于含空格或零值的單元格，它是指單元格內(nèi) 字符長度為零的一個空值，函數(shù)中常用兩個雙引號表示，如”， 另外可用 LEN函數(shù)檢測其與空格單元格的差別 Max(區(qū)域 )、 Min(區(qū)域 ) 求區(qū)域內(nèi)的最大值（ MAX）和最小值（ MIN），文本型的數(shù)值將 不參與運算 統(tǒng)計函數(shù) Large（區(qū)域，排位） -第幾大 1）此函數(shù)用于計算所屬區(qū)域中，從大到小排行第幾位的數(shù)值， 比如， Large（ A1:A100,5）意思是，在 A1:A100范圍內(nèi)，求得第 5大的數(shù)值，利用其它函數(shù)（如 ROW和 COLUMN等函數(shù)或單元格 值），將”排位”替換成變量，可以快速地從一組數(shù)據(jù)中提取從 大到小的排列順序 2）不允許存在并列第幾的狀態(tài)，如果有相同的數(shù)值，將順位排 序，如 100,90,90,85,82 取第二大數(shù)值時是 90，第三大是 90， 第四大是 85. Small（區(qū)域，排位） -第幾小 原理與 LARGE相同，參照如上。 其它查找與引用函數(shù) ROW、 COLUMN 1）直接 =ROW()用于取得當前單元格的行數(shù)，進行拖拉時，可以 取得與 EXCEL行數(shù)相對應(yīng)的遞增或遞減的數(shù)值。 2）添加參數(shù)，如 =ROW(單元格 )，如 =ROW(A1)可返回該單元格 的行號，如 =ROW(B2)返回 2 第 2行，作用同上。 3）將一些函數(shù)中固定系數(shù)、邏輯值按需要替換成變量，可利用 這兩個函數(shù)，以方便拖拉，而不須逐個更改函數(shù)中的固定系數(shù)值， 提高效率。 參數(shù)值可變量代替，可以實現(xiàn)一次性拖拉得出，節(jié)省時間 其它查找與引用函數(shù) INDIRECT(用字符表示的引用區(qū)域 ) 1）在函數(shù)中引用位置可以變成變量形式， INDIRECT（” A1” ） 即等于單元格 A1的值 注意兩者形式比較 B2相當 于” Sheet2” 在統(tǒng)計年、月度報表（日報表分散成一個一個工作表時）時 用處較大，可巧妙地運用此函數(shù)，快速地匯總所需數(shù)據(jù)。 2）此函數(shù)屬易失函數(shù)，即在引用另一個工作薄數(shù)據(jù)時，必須 在兩個 EXCEL同時打開的情況下，才有效。 3）我們在工作表中直接引用，即 =A1之類，在刪除單元格時， 引用會出錯（ #REF），如果改用 =INDIRECT(A1)則不管如何 刪除，它都能保證引用返回 A1的值。 文本函數(shù) FIND(單個字符，字符串，起始位置 ) 函數(shù)說明： =FIND(“ A” ,” BDFCAFDAD” ,2)在字符串中，從 第 2位開始，查找” A” 首次出現(xiàn)的位置，即返回 5，如不包含， 則返回錯誤值 #VALUE!，如果省略”起始位置”，則默認為從第 1位開始查找。 LEN(單元格或字符 ) 函數(shù)說明： =LEN(A1)，計算 A1單元格中字符串的長度，不能用 區(qū)域，如 LEN(A1:A10)，也只返回最左上角 A1單元格的長度值 VALUE(單元格或字符 ) 函數(shù)說明： =VALUE(A1)，將 A1單元格中文本型的數(shù)字轉(zhuǎn)換成數(shù) 值型，以便參與函數(shù)公式的運算。相當于 =-A1，即在 A1前面添 加兩個減號，含“負負得正”的意思。另一種方法是用數(shù)值性粘 貼方法，將 A1單元格乘于 1，即轉(zhuǎn)換成數(shù)值型。 文本函數(shù) LOWER、 UPPER、 TRIM、 REPT 1） LOWER、 UPPER用于將小 /大寫字母轉(zhuǎn)換成大 /小寫字母，如 下圖 : 2） TRIM函數(shù)用于刪除字符前后的空格，字符中間的空格無法刪 除，此函數(shù)常用于整理手工輸入時誤輸空格符的情況 3） REPT(字符，重復個數(shù) )函數(shù)用于產(chǎn)生 N個重復的字符，當函 數(shù)中需要用到多個重復字符時，用這個函數(shù)減少函數(shù)體積 4） TEXT(字符，格式類型 )它相當于單元格格式設(shè)置中的自定義 設(shè)置，一般在嵌套在其它函數(shù)中使用，如下圖： 使資料規(guī) 范統(tǒng)一 兩種方式的效 果對比 文本函數(shù) SUBSTITUTE 字符替換函數(shù) 1） Substitute(需要替換掉的字符，字符串，需要替換成的字符 ) 如 Substitute(“ a” ,” AFGERHAaFe” ,” 中” )意思是將字符串 中 A字符全部替換成”中”，返回結(jié)果是“中 FGERH中中 Fe” ， 故它是不區(qū)分大小寫的。 2）經(jīng)常用途，需要去掉部番中間的空格時（ 848K 15293）如 果單純用查找替換方式，容易將一些 059E開頭的部番變成科學 記數(shù)法，而成為亂碼，影響數(shù)據(jù)準確性，此時，可先用本函數(shù) 去除空格，再數(shù)值性粘貼到文本區(qū)域。 3）替換空格時，可以簡寫成這樣： SUBSTITUTE（“ ”， A1，）即將 A1中的空格清除 空 格 字符 空 值 字符，可以不填，或 用”表示 文本函數(shù) LEFT、 RIGHT、 MID 1） Left(字符串，位數(shù) N) 截取字符串左邊的 N個 字符，用法示例： Left(“ 中華人民共和 國” ,3)=“ 中華人” 2） Right(字符串，位數(shù) N) 截取字符串右邊的 N 個字符，用法示例： Right(“ 中華人民共和 國” ,3)=“ 共和國” 3） Mid(字符串，起始位置 Y，位數(shù) N) 從字符串 的第 Y位開始，截取 N位字符，用法示例： MID(“ 中華人民共和國”， 2， 3)=“ 人民共” 邏輯、信息函數(shù) IF函數(shù) IF（條件，條件成立時返回結(jié)果，條件不成立時返回結(jié)果） 1） IF（ A160，“合格”，“不合格”）即判斷 A1單元格的值， 如果從于 60，則返回“合格”，反之，則返回“不合格” 2）嵌套使用，如 IF(A190,” 優(yōu)秀” ,” 合 格” )意思為如果 A1小于 60則返回“不合格”，大于 60但小于 90，返回“合格”，大于 90則返回“優(yōu)秀”，依此類推，共可 以嵌套七層，也就是說只可以區(qū)分七個等級，超過則會出錯，同 時，如果函數(shù)判斷層級達到 4-5個以上，函數(shù)式會變得很長，極 不利于檢查核對，故此時需要考慮改用 LOOKUP函數(shù)來替代 3）使用此函數(shù)時，邏輯關(guān)系務(wù)必 100%嚴密，否則將返回不到正 確的結(jié)果 邏輯、信息函數(shù) ANDOR函數(shù) AND（條件 1，條件 2，條件 3 ） 1） AND表示多個條件并列，如 AND(A12,B160,A12,B12或 B1<3時 條件成立，返回 TRUE，用 OR時，所有條件只要有一個符合， 就返回 TRUE， IF(OR(A1=2,A1=3),” 良好” ,” 其它等級” ) 即 A1在等于 2或 3時都返回“良好” OR （條件 1，條件 2，條件 3 ） 邏輯、信息函數(shù) Iserror、 Isna函數(shù) 用于判斷錯誤值， ISNA只判斷 #NA型的錯誤值， ISERROR可判 斷所有錯誤值，一般結(jié)果其它函數(shù)使用，如下： 無法計 算合計 數(shù) 添加 ISERROR 函數(shù)后，避免無 法合計現(xiàn)象