外企常用函数NEW

常用函數

<所舉範例為Excel插入形式，可雙擊查看>統計函數(12)MAX/MEDIAN/MIN/MODE/AVERAGE/COUNT/COUNTA/FREQUENCY/LARGE/SMALL/RANK/PERCENTRANK日期及時間函數(6)

TODAY/NOW/MONTH/DAYS360/DATEVALUE/WEEKDAY

文字函數(8)

LOWER/UPPER/PROPER/LEFT/MID/RIGHT/TRIM/CONCATENATE/REPT

數學函數(9)

COUNTBLANK/COUNTIF/INT/ROUND/PRODUCT/SUM/SUBTOTAL/SUMIF/SUMPRODUCT邏輯函數(1)

IF查找與引用函數(4)

VLOOKUP/HLOOKUP/CHOOSE/INDIRECT工程函數(略)財務函數(略)數據庫函數(略)-----與統計函數類似，但可指定條件信息函數(略)12/20/20231Ver:2.0常用的統計函數------AVERAGE、AVERAGEA、COUNT、COUNTA函數名稱語法備註AVERAGE計算參數的算數平均數AVERAGE(Number1,Number2……)AVERAGEA計算所有參數的平均值AVERAGEA((Number1,Number2……)COUNT統計指定範圍內數字的個數COUNT(Value1,Value2……)COUNTA統計指定範圍內非空白數據的個數COUNTA(Value1,Value2……)12/20/20232Ver:2.0常用的統計函數------FREQUENCY函數名稱語法備註FREQUENCY求區域中頻率的分布FREOUENCY(Data_array,Bins_array)Data_array為數據範圍Bins_array為頻率範圍可計算不同工資段的人員分布、公司員工的年齡分布、成績分布等狀況。12/20/20233Ver:2.0常用的統計函數------MAX、MEDIAN、MIN、MODE函數名稱語法備註MAX計算一組參數中的最大值MAX(Number1,Number2……)如果要計算參數中空白單元格、邏輯值、及文字，可用MAXA代替。MEDIAN計算一組參數的中間值MEDIAN(Number1,Number2……)MIN計算一組參數中的最小值MIN(Number1,Number2……)如果要計算參數中空白單元格、邏輯值、及文字，可用MINA代替。MODE出現頻率最高的數MODE(Number1,Number2……)12/20/20234Ver:2.0常用的統計函數-----LARGE、SMALL函數名稱語法備註LARGE求數據中第K個最大值=LARGE(Array,K)Array為數組，K為搜尋值SMALL求數據中第K個最小值=SMALL(Array,K)Array為數組，K為搜尋值可用於計算出第幾高的分數，或第排第幾位的業績。LARGE、SMALL與MAX、MIN非常相像，但前者求得的是第K個值最大或最小值，後者求的是最終的大、小值。12/20/20235Ver:2.0常用的統計函數------RANK函數名稱語法備註RANK計算某數字在指定範圍中的排序等級RANK(Number,Ref,Order)Number為指定的數字，Ref為定的範圍，非數字的值會忽略，Order為指定的順序。Ps.如指定順序為1就由小到大遞增排序，如指定為0或未指定則由大到小遞減排序。如有數字排序相同，會列入同一等級，而下一級從缺。12/20/20236Ver:2.0常用的日期及時間函數------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或省略---1(周日)到7(周六)2---1(周一)到7(周日)12/20/20237Ver:2.0常用的文字函數------LOWER、UPPER、PROPER；LEFT、MID、RIGHT；TRIM函數名稱語法備註LOWERUPPERPROPER將字符串里的各個英文字母轉換成小寫字母。LOWER(Test)將字符串中的英文字母轉換成大寫字母。UPPER(Test)將字符串中的英單字的第一個字母轉成大寫。PRIPER(Test)函數名稱語法備註LEFTMIDRIGHT由最左邊開始，取出指定個數的部份字符串。LEFT(Test,Num_chars)從字符串的第幾個字開始，取出幾個字。MID(Text,Start_num,Num_chars)右最右邊開始，取出指定個數的部份文字。RIGHT(Text,Num_chars)LEFTB、MIDB、RIGHTB會將漢字視為兩個字節，不帶B為一個字節。函數名稱語法備註TRIM刪除字符串中多餘的空格，或保留一個空格。TRIM(Text)Text為所要刪除多餘空格的文本。由MIS系統中轉出至EXCEL的料號或品名前面會有空格，可以TRIM函數進行消除，省去一個個刪除空格的麻煩。12/20/20238Ver:2.0常用的日期及時間函數------TODAY、NOW、MONTH函數名稱語法備註TODAY傳回電腦系統中現在的日期。不需加上引數。MONTH計算對應日期的月份數。Month(Serial_number)Serial_number為日期序列值。NOW傳回電腦系統中內建時鍾的日期及時間。不需加上引數。TODAY計算年資、年齡；MONTH可計算每月的“壽星”。12/20/20239Ver:2.0常用的文字函數----CONCATENATE函數名稱語法備註CONCATENATE將數個文本項連接到一個文本項。CONCATENATE(Text1,text2,……)Text可為字符串、數字、或單元格引用地址。可用於以身份證，求出出生年月、性別，又可用出生年月求出年齡。12/20/202310Ver:2.0常用的文字函數------REPT函數名稱語法備註REPT依指定的次數重複顯示文本REPT(Text,Number_times)將字符串，重複顯示N次可用於表格中建立一個簡單的直方圖，生動數據的表示，如：請假時間、零用金金額等12/20/202311Ver:2.0常用的數學函數----COUNTBLANK、COUNTIF；INT、ROUND函數名稱語法備註COUNTBLANK計算指定範圍內的空白單元格個數COUNTBLANK(Range)Range為要計算空白單元格的範圍。COUNTIF計算某範圍內非空白單元格的個數COUNTIF(Range,Criteria)Criteria為準則，是數字、表示式或文字形式的字符串，定義要列入計算的單元格。函數名稱語法備註INT把一個數向下舍位到最近的整數INT(Number)INT(Number)ROUND將數值按所指定的位數四舍五入ROUND(Number,Num_digits)Number為要四舍五入的數字Num_digits為四舍五入計算時所設定的位數。12/20/202312Ver:2.0常用的數學函數—PRODUCT、SUM、SUBTOTAL、SUMIF、SUMPRODUCT函數名稱語法備註PRODUCT把所有參數相乘，然後返回乘積PRODUCT(Number1,Nmuber2……)SUM計算參數中所有數值的總和。SUM(Number1,Number2……)1~AVERAGE;2~COUNT;3~COUNTA;4~MAX;5~MIN;6~PRODUCT;7~STDEV;8~STDEVP;9~SUM;10~VAP;11~VARPSUBTOTAL計算清單或數據庫的小計。SUBTOTAL(Function_num,Ref)Function_num是1~11的數字，指定清單中計算小計的函數，Ref是執行小計的範圍或引用地址。SUMIF按指定條件對指定的單元格範圍求和。SUMIF(Range,Criteria,Sum_range)Range為Criteria的搜尋範圍，Criteria為條件，定義哪些單元格相加，是數字、表示式或文字形式的字符串，Sum_range為實際要求和的單元格。SUMPRODUCT計算各數組中所有對應元素乘積的和。SUMPRODUCT(Array1,Array2,Array3,…)Array是欲求其對應元素乘積之和的數組。12/20/202313Ver:2.0常用的邏輯函數------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為正規寫法，EXCEL中可省略：

=IF(SUM(D8:E8),(SUM(D8:E8),””)12/20/202314Ver:2.0常用的查找與引用函數------VLOOKUP、HLOOKUP函數名稱語法備註VLOOKUP垂直查表功能在一組數最左列中尋找某特定數值，並返回當前行中指定列的數值。VLOOKUP(Lookup_value,Table_array,Col_index_num,Range_lookup)Lookup_value為搜尋值，是打算在數組第一列中的搜尋值，可為數值、引用地址或文字字符。Table_array為搜尋範圍，是要在其中搜尋的數據表格，通常為單元格範圍的引用地址或範圍名稱。Col_index_num為索引值，是數值，代表所返回的值位於搜尋範圍中的第幾列。Range_lookup為搜尋型態邏輯值，用於指定VLOOKUP尋找完全符合或部份符合的值。當為TRUE或省略時為返回部份符合的值，即找不到完全符合的值便返回次於搜尋的值。當為FALSE則尋找完全符合的值，如果找不到則返回#N/A。‧當索引值為1時，則返回搜尋範圍第一列的值；索引值為2則返回搜尋範圍第二列的值，以此類推。‧如索引值小於1則返回#VALUE，大於搜尋範圍總列數則返回#REF。HLOOKUP水平查表功能在一組數的第一行中尋找含有某特定值的字段，再返回指定單元格中的值。HLOOKUP(Lookup_value,Table_array,Row_index_num,Range_lookup)Lookup_value為搜尋值，是打算在表格頂端行中的搜尋值，可為數值、引用地址或文字字符。Table_array------同VLOOKUPRow_index_num為索引行編號，是數值，代表所返回的值位於搜尋範圍中的第幾行。Range_lookup------同VLOOKUP。搜尋範圍第一行中的數值必須以遞增順序排列，條件表中不可有空白資料。如為TREN，搜尋範圍第一行需先以遞增排列，如邏輯值為FALSE時，則不需排列。12/20/202315Ver:2.0常用的查找與引用函數------VLOOKUP、HLOOKUP12/20/2023