《常用函数PPT课件》由会员分享，可在线阅读，更多相关《常用函数PPT课件（20页珍藏版）》请在金锄头文库上搜索。
1、常 用 函 數w統計函數 (12) MAX / MEDIAN / MIN / MODE / AVERAGE / COUNT / COUNTA /COUNTIF/ FREQUENCY / LARGE / SMALL / RANK / PERCENTRANK/w日期及時間函數 (6) TODAY / NOW / MONTH / DAYS360 / DATEVALUE / WEEKDAY /WEEKNUM(2000)w文字函數 (8) LOWER / UPPER / PROPER / LEFT / MID / RIGHT / TRIM / CONCATENATE / REPT /TRIMw數學函數
2、(9) COUNTBLANK / INT / ROUND / PRODUCT / SUM / SUBTOTAL / SUMIF / SUMPRODUCT w邏輯函數 (1) IF、ISNAw查找與引用函數(4) VLOOKUP / HLOOKUP / CHOOSE / INDIRECTw工程函數(略)w財務函數(略)w數據庫函數(略)-與統計函數類似，但可指定條件w信息函數(略)2021/3/91常用的統計函數常用的統計函數-MAX、MEDIAN、MIN、MODE函數名稱語法備註MAX計算一組參數中的最大值MAX(Number1,Number2)如果要計算參數中空白單元格、邏輯值、及文字，可用
3、MAXA代替。MEDIAN計算一組參數的中間值MEDIAN(Number1,Number2)MIN計算一組參數中的最小值MIN(Number1,Number2)如果要計算參數中空白單元格、邏輯值、及文字，可用MINA代替。MODE出現頻率最高的數MODE(Number1,Number2)2021/3/92常用的統計函數常用的統計函數-AVERAGE、AVERAGEA、COUNT、COUNTA、COUNTIF函數名稱語法備註AVERAGE計算參數的算數平均數AVERAGE(Number1,Number2)AVERAGEA計算所有參數的平均值AVERAGEA(Number1,Number2)COU
4、NT統計指定範圍內數字的個數COUNT(Value1,Value2)COUNTA統計指定範圍內非空白數據的個數COUNTA(Value1,Value2)COUNTIF計算一範圍內指定條件的儲存格數目COUNTIF(Range,Criteria)Range為範圍，Criteria為給定的條件2021/3/93常用的統計函數常用的統計函數-FREQUENCY函數名稱語法備註FREQUENCY求區域中頻率的分布FREOUENCY(Data_array,Bins_array)Data_array為數據範圍Bins_array為頻率範圍可計算不同工資段的人員分布、公司員工的年齡分布、成績分布等狀況。20
5、21/3/94常用的統計函數常用的統計函數-LARGE、SMALL函數名稱語法備註LARGE求數據中第K個最大值= LARGE(Array,K)Array為數組，K為搜尋值SMALL求數據中第K個最小值=SMALL(Array,K)Array為數組，K為搜尋值可用於計算出第幾高的分數，或第排第幾位的業績。LARGE、SMALL與MAX、MIN非常相像，但前者求得的是第K個值最大或最小值，後者求的是最終的大、小值。2021/3/95常用的統計函數常用的統計函數-RANK函數名稱語法備註RANK計算某數字在指定範圍中的排序等級RANK(Number,Ref,Order)Number為指定的數字，R
6、ef為定的範圍，非數字的值會忽略，Order為指定的順序。Ps. 如指定順序為1就由小到大遞增排序，如指定為0或未指定則由大到小遞減排序。 如有數字排序相同，會列入同一等級，而下一級從缺。2021/3/96常用的日期及時間函數常用的日期及時間函數-TODAY、NOW、MONTH函數名稱語法備註TODAY傳回電腦系統中現在的日期。不需加上引數。MONTH計算對應日期的月份數。Month(Serial_number)Serial_number為日期序列值。NOW傳回電腦系統中內建時鍾的日期及時間。不需加上引數。TODAY計算年資、年齡；MONTH可計算每月的“壽星”。2021/3/97常用的日期及
7、時間函數常用的日期及時間函數-DAYS360、DATEVALUE、WEEKDAY函數名稱語法備註DAYS360計算兩個日期間相差的天數，每個月以30天計算DAYS360(Start_date,End_date)Start_date為開始日期，End_date為結束日期。如果會計系統以每月30天為基礎，可計算付款的日期。函數名稱語法備註DATEVALUE將日期數據，轉換為EXCEL的數值日期型。DATEVALUE(Date_text)函數名稱語法備註WEEKDAY計算數值所對應的星期值。WEEKDAY(Serial_number,Return_type)Return_type為返回值的類型：1或
8、省略-1(周日)到7(周六)2-1(周一)到7(周日)2021/3/98常用的文字函數常用的文字函數-LOWER、UPPER、PROPER；LEFT、MID、RIGHT；TRIM函數名稱語法備註LOWERUPPERPROPER將字符串里的各個英文字母轉換成小寫字母。LOWER(Test)將字符串中的英文字母轉換成大寫字母。UPPER(Test)將字符串中的英單字的第一個字母轉成大寫。PRIPER(Test)函數名稱語法備註LEFTMIDRIGHT由最左邊開始，取出指定個數的部份字符串。LEFT(Test,Num_chars)從字符串的第幾個字開始，取出幾個字。 MID(Text,Start_n
9、um,Num_chars)右最右邊開始，取出指定個數的部份文字。RIGHT(Text,Num_chars)LEFTB、MIDB、RIGHTB會將漢字視為兩個字節，不帶B為一個字節。函數名稱語法備註TRIM刪除字符串中多餘的空格，或保留一個空格。TRIM(Text)Text為所要刪除多餘空格的文本。由MIS系統中轉出至EXCEL的料號或品名前面會有空格，可以TRIM函數進行消除，省去一個個刪除空格的麻煩。2021/3/99常用的文字函數常用的文字函數-REPLACE、REPLACEB函數名稱語法備註REPLACEREPLACEB將字符串里某部分取代成其它字元。Replace(old_text,s
10、tart_num,num_bytes,new_text) (舊字元,開始位置,長度,新字元)可用於以個公司代碼所開立之單據核對。2021/3/910常用的文字函數常用的文字函數-CONCATENATE函數名稱語法備註CONCATENATE將數個文本項連接到一個文本項。CONCATENATE(Text1,text2,)Text可為字符串、數字、或單元格引用地址。可用於以身份證，求出出生年月、性別，又可用出生年月求出年齡。2021/3/911常用的文字函數常用的文字函數-REPT 函數名稱語法備註REPT依指定的次數重複顯示文本REPT(Text,Number_times)將字符串，重複顯示N次可
11、用於表格中建立一個簡單的直方圖，生動數據的表示，如：請假時間、零用金金額等2021/3/912常用的數學函數常用的數學函數-COUNTBLANK、COUNTIF；INT、ROUND函數名稱語法備註COUNTBLANK計算指定範圍內的空白單元格個數COUNTBLANK(Range)Range為要計算空白單元格的範圍。COUNTIF計算某範圍內非空白單元格的個數COUNTIF(Range,Criteria)Criteria為準則，是數字、表示式或文字形式的字符串，定義要列入計算的單元格。函數名稱語法備註INT把一個數向下舍位到最近的整數INT(Number)INT(Number)ROUND將數值按
12、所指定的位數四舍五入ROUND(Number,Num_digits)Number為要四舍五入的數字Num_digits為四舍五入計算時所設定的位數。2021/3/913常用的數學函數常用的數學函數PRODUCT、SUM、SUBTOTAL、SUMIF、SUMPRODUCT函數名稱語法備註PRODUCT把所有參數相乘，然後返回乘積PRODUCT(Number1,Nmuber2)SUM計算參數中所有數值的總和。SUM(Number1,Number2)SUBTOTAL計算清單或數據庫的小計。SUBTOTAL(Function_num,Ref)Function_num是111的數字，指定清單中計算小計的
13、函數，Ref是執行小計的範圍或引用地址。1AVERAGE; 2COUNT; 3COUNTA; 4MAX;5MIN; 6PRODUCT; 7STDEV; 8STDEVP; 9SUM; 10VAP; 11VARPSUMIF按指定條件對指定的單元格範圍求和。SUMIF(Range,Criteria,Sum_range)Range為Criteria的搜尋範圍，Criteria為條件，定義哪些單元格相加，是數字、表示式或文字形式的字符串，Sum_range為實際要求和的單元格。SUMPRODUCT計算各數組中所有對應元素乘積的和。SUMPRODUCT(Array1, Array2, Array3,)Ar
14、ray是欲求其對應元素乘積之和的數組。2021/3/914常用的邏輯函數常用的邏輯函數-IF函數名稱語法備註IF某條件，條件成立時，條件不成立時，執行不同的動作。IF(Logical_test,Value_if_true,value_if_false)Logical_test為條件式，表示計算結果為TRUE或FLASE的任意值或表達式。Value_if_true當條件成立時的動作value_if_false當條件不成立的動作IF的應用：將公式計算結果為零時的處理方式：PS. 如果SUM(D8:E8)不等於零，則顯示SUM(D8:D8)的結果，否則顯示字符串。 SUM(D8:E8)0為正規寫法，
15、EXCEL中可省略： =IF(SUM(D8:E8), (SUM(D8:E8),”)2021/3/915常用的邏輯函數常用的邏輯函數-ISNA函數名稱語法備註ISNA當當Value為為#N/A時傳回時傳回TRUE2021/3/916常用的查找與引用函數常用的查找與引用函數-VLOOKUP、HLOOKUP函數名稱語法備註VLOOKUP垂直查表功能在一組數最左列中尋找某特定數值，並返回當前行中指定列的數值。VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)Lookup_value為搜尋值，是打算在數組第一列中的搜尋值，可為數值、引
16、用地址或文字字符。Table_array為搜尋範圍，是要在其中搜尋的數據表格，通常為單元格範圍的引用地址或範圍名稱。Col_index_num為索引值，是數值，代表所返回的值位於搜尋範圍中的第幾列。Range_lookup為搜尋型態邏輯值，用於指定VLOOKUP尋找完全符合或部份符合的值。當為TRUE或省略時為返回部份符合的值，即找不到完全符合的值便返回次於搜尋的值。當為FALSE則尋找完全符合的值，如果找不到則返回#N/A。當索引值為1時，則返回搜尋範圍第一列的值；索引值為2則返回搜尋範圍第二列的值，以此類推。如索引值小於1則返回#VALUE，大於搜尋範圍總列數則返回#REF。HLOOKUP
17、水平查表功能在一組數的第一行中尋找含有某特定值的字段，再返回指定單元格中的值。HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_lookup)Lookup_value為搜尋值，是打算在表格頂端行中的搜尋值，可為數值、引用地址或文字字符。Table_array-同VLOOKUPRow_index_num為索引行編號，是數值，代表所返回的值位於搜尋範圍中的第幾行。Range_lookup-同VLOOKUP。搜尋範圍第一行中的數值必須以遞增順序排列，條件表中不可有空白資料。如為TREN，搜尋範圍第一行需先以遞增排列，如邏輯值為FALSE時，則不
18、需排列。2021/3/917常用的查找與引用函數常用的查找與引用函數-VLOOKUP、HLOOKUP2021/3/918常用的查找與引用函數常用的查找與引用函數-CHOOSE、INDIRECT函數名稱語法備註CHOOSE選擇函數，根據所指定的索引值，從參數序列中取出相應的數字。CHOOSE(Index_num,Value1,Value2,)Index_num為索引值，指定選第幾個參數，參數可為數字、公式、或地址，需介於129之間。INDIRECT根據單一單元格所指定的引用地址，返回被取得單元格的內容。INDIRECT(Ref_text,A1)Ref_text為引用地址；A1邏輯值，單元格的表示方式。2021/3/919放映结束 感谢各位的批评指导！ 谢谢 谢！谢！让我们共同进步2021/3/920
