Excel常用函数简介.ppt

《Excel常用函数简介.ppt》由会员分享，可在线阅读，更多相关《Excel常用函数简介.ppt（26页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Confidential 1 1/26/26Excel Excel 常用函數簡介常用函數簡介制作：李強制作：李強 Rev:A Rev:A企劃教育訓練教材企劃教育訓練教材1/16/20231Confidential 2 2/26/26PAGE DOWN PAGE DOWN 往下移一個螢幕往下移一個螢幕 PAGE UP PAGE UP 往上移一個螢幕往上移一個螢幕 ALT+PAGE DOWN ALT+PAGE DOWN 往右移一個螢幕往右移一個螢幕 ALT+PAGE UP ALT+PAGE UP 往左移一個螢幕往左移一個螢幕 CTRL+PAGE DOWN CTRL+PAGE DOWN 移到活頁簿中

2、的下個工作表移到活頁簿中的下個工作表CTRL+PAGE UP CTRL+PAGE UP 移到活頁簿中的上個工作表移到活頁簿中的上個工作表CTRL+F6 CTRL+F6 或或 CTRL+TAB CTRL+TAB 移到下一個活頁簿或視窗移到下一個活頁簿或視窗SHIFT+F5 SHIFT+F5 顯示顯示 尋找尋找 對話方塊對話方塊SHIFT+F4 SHIFT+F4 重複最後一個重複最後一個 尋找尋找 的動作的動作 （同於（同於 尋找下一步尋找下一步）一、鍵盤快捷鍵一、鍵盤快捷鍵1/16/20232Confidential 3 3/26/26CTRL+P or CTRL+SHIFT+F12 CTRL+

3、P or CTRL+SHIFT+F12 顯示顯示 列印列印 對話方塊指令對話方塊指令SHIFT+F11 or ALT+SHIFT+F1 SHIFT+F11 or ALT+SHIFT+F1 插入新的工作表插入新的工作表F11 or ALT+F1 F11 or ALT+F1 使用目前的範圍來建立圖表使用目前的範圍來建立圖表CTRL+SHIFT+ENTER CTRL+SHIFT+ENTER 將公式輸入為陣列公式將公式輸入為陣列公式CTRL+A CTRL+A 當您在公式中輸入一個有效的當您在公式中輸入一個有效的函數名稱之後，顯示公式調色盤函數名稱之後，顯示公式調色盤CTRL+SHIFT+A CTRL+

4、SHIFT+A 當您在公式中輸入有效的函數當您在公式中輸入有效的函數名稱之後，替函數插入引數名稱和括號名稱之後，替函數插入引數名稱和括號ALT+=ALT+=插入自動加總公式插入自動加總公式CTRL+;CTRL+;輸入日期輸入日期CTRL+SHIFT+:CTRL+SHIFT+:輸入時間輸入時間1/16/20233Confidential 4 4/26/26 （三）（三）CTRL+CTRL+在顯示儲存格數值和顯示儲存格公式之間替換在顯示儲存格數值和顯示儲存格公式之間替換CTRL+9 CTRL+9 隱藏列隱藏列CTRL+SHIFT+(CTRL+SHIFT+(顯示隱藏的列顯示隱藏的列CTRL+0(CT

5、RL+0(零零)隱藏欄隱藏欄CTRL+SHIFT+)CTRL+SHIFT+)顯示隱藏的欄顯示隱藏的欄CTRL+C CTRL+C 複製選取區複製選取區CTRL+X CTRL+X 剪下選取區剪下選取區CTRL+V CTRL+V 貼上選取區貼上選取區DELETE DELETE 清除選取區的內容清除選取區的內容CTRL+-CTRL+-刪除選取範圍刪除選取範圍CTRL+Z CTRL+Z 復原上一個動作復原上一個動作CTRL+SHIFT+CTRL+SHIFT+加號加號 插入空白儲存格插入空白儲存格1/16/20234Confidential 5 5/26/26二、工作表函數運用二、工作表函數運用1 1、D

6、ATEDIF DATEDIF（）（）語法語法 DATEDIF(start_date,end_date,unit)DATEDIF(start_date,end_date,unit)Start_date Start_date 為一個日期，它代表時間段內的第一個日期或為一個日期，它代表時間段內的第一個日期或 起始起始日期。日期有多種輸入方法：帶引號的文字串（例如日期。日期有多種輸入方法：帶引號的文字串（例如“2001/1/30”“2001/1/30”）、序）、序列數（例如，如果使用列數（例如，如果使用 1900 1900 日期系統則日期系統則 36921 36921 代表代表 2001 2001 年

7、年 1 1 月月 30 30 日）日）或其他計算式或函數的結果（例如，或其他計算式或函數的結果（例如，DATEVALUE(“2001/1/30”)DATEVALUE(“2001/1/30”)）End_date End_date 為一個日期，它代表時間段內的為一個日期，它代表時間段內的最後日期或結束日期最後日期或結束日期 Unit Unit 為所需資訊的傳回類型為所需資訊的傳回類型1/16/20235Confidential 6 6/26/26Unit Unit 傳回傳回Y“Y“週期中的整年數週期中的整年數M“M“週期中的整月數週期中的整月數D“D“週期中的天數週期中的天數MD“start_da

8、te MD“start_date 與與 end_date end_date 日期中天數的差。忽略日期中的月和年日期中天數的差。忽略日期中的月和年YM“start_date YM“start_date 與與 end_date end_date 日期中月數的差。忽略日期中的日和年日期中月數的差。忽略日期中的日和年“YD“start_date“YD“start_date 與與 end_date end_date 日期中天數的差。忽略日期中的年日期中天數的差。忽略日期中的年範例範例DATEDIF(“2001/6/1”,“2002/8/15”,“D”)DATEDIF(“2001/6/1”,“2002/8

9、/15”,“D”)等於等於 440 440，即在，即在 2001 2001 年年 6 6 月月 1 1 日和日和 2002 2002 年年 8 8 月月 15 15 日之間有日之間有 440 440 天天DATEDIF(2001/6/1,2002/8/15,YD)DATEDIF(2001/6/1,2002/8/15,YD)等於等於 75 75，即在，即在 6 6 月月 1 1 日與日與 8 8 月月 15 15 日之間有日之間有 75 75 天，忽略日期中的年天，忽略日期中的年1/16/20236Confidential 7 7/26/262 2、TODAYTODAY（）（）語法語法 TODA

10、Y()TODAY()傳回電腦系統內建時鐘的最近日期的序列值傳回電腦系統內建時鐘的最近日期的序列值3 3、WEEKDAYWEEKDAY（）（）語法語法 WEEKDAY(serial_number,return_type)WEEKDAY(serial_number,return_type)傳回對應於序列值傳回對應於序列值(serial_number)(serial_number)的星期數值的星期數值 傳回值類型傳回值類型 傳回的數字傳回的數字 1 1 或省略或省略 1 1（週日）到（週日）到 7 7（週末）（週末）2 1 2 1（週一）到（週一）到 7 7（週日）（週日）3 0 3 0（週一）到（

11、週一）到 6 6（週日）（週日）範例範例 WEEKDAY(2003/02/23,3)WEEKDAY(2003/02/23,3)等於等於 6 6（星期日）（星期日）1/16/20237Confidential 8 8/26/264 4、WEEKNUMWEEKNUM（）（）語法語法 Serial_num Serial_num（序列值）（序列值）一星期中的日期。關於一星期中的日期。關於 Microsoft Excel Microsoft Excel 使使用用 serial number serial number 日期與時間序列值之更詳細的資訊，請參閱日期與時間序列值之更詳細的資訊，請參閱 註解註解

12、 一一節。節。Return_typeReturn_type（傳回類型）（傳回類型）是決定一周從哪天開始的數字。預設值是是決定一周從哪天開始的數字。預設值是 1 1（一周從星期天開始）、（一周從星期天開始）、2 2（一周從星期一開始）（一周從星期一開始）範例範例 WEEKNUM(2005/08/31)WEEKNUM(2005/08/31)等於等於 36 361/16/20238Confidential 9 9/26/265 5、ANDAND（）（）語法語法 AND(logical1,logical2,.)AND(logical1,logical2,.)如果它的所有引數都是如果它的所有引數都是 T

13、RUE TRUE，則傳回，則傳回 TRUE TRUE。如果有一個或多個。如果有一個或多個 引數是引數是 FALSEFALSE，則傳回，則傳回 FALSE FALSE。範例範例 AND(TRUE,FALSE)AND(TRUE,FALSE)等於等於 FALSE FALSE IF(AND(12,23),100,The value is out of range.)IF(AND(12,289,A,IF(AverageScore79,B,IF(AverageScore89,A,IF(AverageScore79,B,IF(AverageScore69,C,IF(AverageScore59,D,F)IF

14、(AverageScore69,C,IF(AverageScore59,D,F)1/16/202310Confidential 1111/26/267 7、VLOOKUPVLOOKUP（）（）語法語法 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value Lookup_value 是您打算在陣列的最左欄中搜尋的值是您打算在陣列的最左欄中搜尋的值Lookup_value Lookup_v

15、alue 可可以是數值、參照位址或文字字串以是數值、參照位址或文字字串 Table_array Table_array 是要在其中搜尋的資料表格。通常是儲存格範圍的參照是要在其中搜尋的資料表格。通常是儲存格範圍的參照位址或類似資料庫或清單的範圍名稱位址或類似資料庫或清單的範圍名稱 在一陣列或表格的最左欄中尋找含有某特定值的欄位，再傳回同一列中在一陣列或表格的最左欄中尋找含有某特定值的欄位，再傳回同一列中某一指定儲存格中的值某一指定儲存格中的值 如果如果 VLOOKUP VLOOKUP 函數找不到函數找不到 lookup_value lookup_value，且，且 range_lookup r

16、ange_lookup 為為 TRUE TRUE 時，則時，則 VLOOKUP VLOOKUP 傳回錯誤值傳回錯誤值#N/A#N/A1/16/202311Confidential 1212/26/268 8、ROUNDROUND（）（）語法語法 ROUND(number,num_digits)ROUND(number,num_digits)Number Number 是個您想執行四捨五入的數字。是個您想執行四捨五入的數字。Num_digits Num_digits 是對數字執行四捨五入計算時所指定的位數。是對數字執行四捨五入計算時所指定的位數。依所指定的位數，將數字四捨五入。依所指定的位數，將

17、數字四捨五入。範例範例 ROUND(2.15,1)ROUND(2.15,1)等於等於 2.2 2.2 ROUND(2.149,1)ROUND(2.149,1)等於等於 2.1 2.11/16/202312Confidential 1313/26/269 9、ROUNDDOWNROUNDDOWN（）（）語法語法 ROUNDDOWN(number,num_digits)ROUNDDOWN(number,num_digits)Number Number 是要無條件捨去的任何實數是要無條件捨去的任何實數 Num_digits Num_digits 是捨去時所採用的位數是捨去時所採用的位數 將數值作無條

18、件捨去將數值作無條件捨去範例範例 ROUNDDOWN(3.2,0)ROUNDDOWN(3.2,0)等於等於 3 3 ROUNDDOWN(76.9,0)ROUNDDOWN(76.9,0)等於等於 76 761/16/202313Confidential 1414/26/261010、ROUNDUPROUNDUP（）（）語法語法 ROUNDUP(number,num_digits)ROUNDUP(number,num_digits)Number Number 是要無條件進位的任何實數是要無條件進位的任何實數 Num_digits Num_digits 是做無條件進位時所採用的位數是做無條件進位時所

19、採用的位數 將數值做無條件進位將數值做無條件進位範例範例 ROUNDUP(3.2,0)ROUNDUP(3.2,0)等於等於 4 4 ROUNDUP(76.9,0)ROUNDUP(76.9,0)等於等於 77 77 ROUNDUP(3.14159,3)ROUNDUP(3.14159,3)等於等於 3.142 3.1421/16/202314Confidential 1515/26/261111、SUBTOTALSUBTOTAL（）（）語法語法,Function_num Function_num 是數字是數字 1 1 到到 11 11，用以指定要用下列哪一個函數來計，用以指定要用下列哪一個函數來計

20、算清單中的小計（傳回一個清單或資料庫的小計）算清單中的小計（傳回一個清單或資料庫的小計）Function_Num Function_Num 函數函數 1 AVERAGE 1 AVERAGE 平均數平均數 2 COUNT 2 COUNT 計算含有數字的儲存格的數量計算含有數字的儲存格的數量 3 COUNTA 3 COUNTA 計算非空白儲存格數量計算非空白儲存格數量 4 MAX 4 MAX 最大值最大值 5 MIN 5 MIN 最小值最小值 6 PRODUCT 6 PRODUCT 乘積乘積 7 STDEV 7 STDEV 標準差（僅計算數值）標準差（僅計算數值）8 STDEVP 8 STDEVP

21、 標準差（文字、邏輯值等被視為標準差（文字、邏輯值等被視為“1”or“1”or“0”“0”）9 SUM 9 SUM 求和求和 10 VAR 10 VAR 變異數（僅計算數值）變異數（僅計算數值）11 VARP 11 VARP 變異數（文字、邏輯值等被視為變異數（文字、邏輯值等被視為“1”or“1”or“0”“0”）SUBTOTAL(9,C3:C5)SUBTOTAL(9,C3:C5)將使用將使用 SUM SUM 函數產生函數產生 C3:C5 C3:C5 之小計之小計1/16/202315Confidential 1616/26/261212、SUMSUM（）（）語法語法 SUM(number1,

22、number2,.)SUM(number1,number2,.)Number1,number2,.Number1,number2,.是您想要計算總和的是您想要計算總和的 1 1 到到 30 30 個數值引數個數值引數 傳回引數串列中所有數值的總和傳回引數串列中所有數值的總和範例範例 SUM(3,2)SUM(3,2)等於等於 5 5 SUM(C40:C42,15)SUM(C40:C42,15)等於等於 257 2571/16/202316Confidential 1717/26/261313、SUMIFSUMIF（）（）語法語法 SUMIF(range,criteria,sum_range)SU

23、MIF(range,criteria,sum_range)Range Range 是要評估加總的儲存格範圍是要評估加總的儲存格範圍準則是用以決定要否列入加總的搜尋準則，可以是數字、表示式或文字準則是用以決定要否列入加總的搜尋準則，可以是數字、表示式或文字,例例如，準則可以是如，準則可以是 32 32、3232、32 32 或或 apples apples Sum_range Sum_range 是實際要加總的儲存格。是實際要加總的儲存格。sum_range sum_range 和範圍是相對應的和範圍是相對應的,當範圍中的儲存格符合搜尋準則時，其對應的當範圍中的儲存格符合搜尋準則時，其對應的 s

24、um_range sum_range 儲存格會被加入儲存格會被加入總數。如果省略掉總數。如果省略掉 sum_range sum_range，則計算加總範圍中的儲存格，則計算加總範圍中的儲存格 加總符合某特定搜尋準則的儲存格加總符合某特定搜尋準則的儲存格範例範例 假設假設 B45:B48 B45:B48 內含下列的四個房屋的價值，分別是：內含下列的四個房屋的價值，分別是：100,000100,000元、元、200,000200,000元、元、300,000300,000元、元、400,000400,000元。而元。而 C45:C48 C45:C48 則內含下列的相對銷則內含下列的相對銷售佣金：售

25、佣金：7,0007,000元、元、14,00014,000元、元、21,00021,000元、元、28,00028,000元。則：元。則：SUMIF(B45:B48,160000,C45:C48)SUMIF(B45:B48,160000,C45:C48)等於等於$63,000$63,0001/16/202317Confidential 1818/26/261414、AVERAGEAVERAGE（）（）語法語法 AVERAGE(number1,number2,.)AVERAGE(number1,number2,.)Number1,Number2,.Number1,Number2,.是是 1 1

26、到到 30 30 個數字引數，您需要這些個數字引數，您需要這些引數的平均值引數的平均值 傳回這些引數的平均值傳回這些引數的平均值(算術平均數算術平均數)範例範例 如果如果 B51:B55 B51:B55 命名為命名為 Scores Scores，裡面含有數字，裡面含有數字 10 10、7 7、9 9、27 27 和和 2 2，則：，則：AVERAGE(B51:B55)AVERAGE(B51:B55)等於等於 11 11 AVERAGE(Scores)AVERAGE(Scores)等於等於 11 11 AVERAGE(B51:B55,5)AVERAGE(B51:B55,5)等於等於 10 101

27、/16/202318Confidential 1919/26/261515、MAXMAX（）（）語法語法 MAX(number1,number2,.)MAX(number1,number2,.)Number1,number2,.Number1,number2,.是您想找出最大值的是您想找出最大值的 1 1 到到 30 30 個數字。個數字。傳回一組數值中的最大值傳回一組數值中的最大值範例範例 如果如果 B51:B55 B51:B55 中包含數字中包含數字 10 10、7 7、9 9、27 27 和和 2 2，則：，則：MAX(B51:B55)MAX(B51:B55)等於等於 27 27 MAX

28、(B51:B55,30)MAX(B51:B55,30)等於等於 30 30 MIN MIN（）函數傳回一組數值中人最小值，與（）函數傳回一組數值中人最小值，與MAXMAX（）相反（）相反 MIN(B51:B55)MIN(B51:B55)等於等於 2 2 MIN(B51:B55,0)MIN(B51:B55,0)等於等於 0 01/16/202319Confidential 2020/26/261616、LEFTLEFT（）（）&LEFTB()&LEFTB()LEFT LEFT 傳回一文字字串中第一個字元或字元組，以您指定字元組的數值為準傳回一文字字串中第一個字元或字元組，以您指定字元組的數值為準

29、 LEFTB LEFTB 傳回一文字字串中第一個字元或字元組，以您指定字元組的數值為傳回一文字字串中第一個字元或字元組，以您指定字元組的數值為準。這個函數是使用在二位元的字元組準。這個函數是使用在二位元的字元組 語法語法 LEFT(text,num_chars)LEFT(text,num_chars)LEFTB(text,num_bytes)LEFTB(text,num_bytes)text text文字為含有所要選錄文字之字串文字為含有所要選錄文字之字串 Num_chars Num_chars指定您要指定您要 LEFT LEFT 選錄的字元數選錄的字元數 Num_bytes Num_byte

30、s指定您要指定您要 LEFTB LEFTB 選錄的字元數（依據位元組）選錄的字元數（依據位元組）範例範例 LEFT(“Sale Price”,4)LEFT(“Sale Price”,4)等於等於“Sale”“Sale”LEFTB(“LEFTB(“東京東京”,5)”,5)等於等於“東京東京 1/16/202320Confidential 2121/26/261717、LEN()&LENB()LEN()&LENB()LEN()LEN()傳回一文字字串的字元個數傳回一文字字串的字元個數 LENB()LENB()傳回在文字字串中代表該字元組的位元數。這個函數是使用傳回在文字字串中代表該字元組的位元數。

31、這個函數是使用在二位元的字元組在二位元的字元組 語法語法 LEN(text)LEN(text)LENB(text)LENB(text)文字為所要計算字元個數的文字串，字串中所有的空白亦當作字元來文字為所要計算字元個數的文字串，字串中所有的空白亦當作字元來處理處理 範例範例 LEN(“Phoenix,AZ”)LEN(“Phoenix,AZ”)等於等於 11 11 LEN(“”)LEN(“”)等於等於 0 0 LENB(“LENB(“中國北京中國北京”)”)等於等於 8 81/16/202321Confidential 2222/26/261818、MIDMID（）（）語法語法 MID(text,

32、start_num,num_chars)MID(text,start_num,num_chars)Start_num Start_num 為用以指定您要由為用以指定您要由 text text 的第若干個位元組開始抽選的第若干個位元組開始抽選 text text 中的第一個字元為中的第一個字元為 start_num 1 start_num 1，依此類推，依此類推 Num_chars Num_chars指定您要指定您要 MID MID 從字串傳回的字元組。如果從字串傳回的字元組。如果 num_chars num_chars 為為負值，負值，MID MID 將傳回將傳回#VALUE!#VALUE!的

33、錯誤值的錯誤值範例範例 MID(Fluid Flow,1,5)MID(Fluid Flow,1,5)等於等於 Fluid“Fluid“MID(Fluid Flow,7,20)MID(Fluid Flow,7,20)等於等於 Flow“Flow“MID(1234,5,5)MID(1234,5,5)等於等於 (empty text)(empty text)1/16/202322Confidential 2323/26/261919、RIGHTRIGHT（）（）語法語法 RIGHT(text,num_chars)RIGHT(text,num_chars)RIGHTB(text,num_bytes)R

34、IGHTB(text,num_bytes)text text文字是含有您想選錄的部分字串之文字字串文字是含有您想選錄的部分字串之文字字串 Num_chars Num_chars指定您要以指定您要以 RIGHT RIGHT 所選錄的字串長度所選錄的字串長度 Num_bytes Num_bytes指定您要以指定您要以 RIGHTB RIGHTB 所選錄的字串長度所選錄的字串長度範例範例 RIGHT(Sale Price,5)RIGHT(Sale Price,5)等於等於 Price“Price“RIGHT(Stock Number)RIGHT(Stock Number)等於等於 r“r“RIGHT

35、B(“RIGHTB(“江蘇昆山江蘇昆山”,4)”,4)等於等於“昆山昆山 RIGHT(Sale Price,5)等於PriceRIGHT(Stock Number)等於rRIGHTB(,2)等於1/16/202323Confidential 2424/26/262020、TEXTTEXT（）（）語法語法 TEXT(value,format_text)TEXT(value,format_text)Value Value 可以是數值、一個會傳回數值的、或者是一個參照到含有數值可以是數值、一個會傳回數值的、或者是一個參照到含有數值資料的儲存格參照位址資料的儲存格參照位址 Format_text Fo

36、rmat_text 為為 儲存格格式儲存格格式 對話方塊的對話方塊的 數字數字 索引標籤上的索引標籤上的 類別類別 方塊中所使用之文字形式的數字格式。方塊中所使用之文字形式的數字格式。format_text format_text 不能含有代表不能含有代表通配字元的星號通配字元的星號(*)(*)，也不可以是一般數字格式，也不可以是一般數字格式 以指定的數字格式將數值轉成文字以指定的數字格式將數值轉成文字範例範例 TEXT(2.715,“$0.00”)TEXT(2.715,“$0.00”)等於等於“$2.72”“$2.72”TEXT(4/15/91,mmmm dd,yyyy)TEXT(4/15/

37、91,mmmm dd,yyyy)等於等於 April 15,1991 April 15,19911/16/202324Confidential 2525/26/262121、TRIMTRIM（）（）語法語法 TRIM(text)TRIM(text)文字為您所要移除多餘空白的文字資料文字為您所要移除多餘空白的文字資料 移除文字字串中多餘的空格，僅保留一個空白字元移除文字字串中多餘的空格，僅保留一個空白字元範例範例TRIM(First Quarter Earnings)TRIM(First Quarter Earnings)等於等於 First Quarter First Quarter EarningsEarnings1/16/202325Confidential 2626/26/262222、CTRL+SHIFT+ENTER CTRL+SHIFT+ENTER 組合陣列公式運用組合陣列公式運用 陣列常數中不能含有：陣列常數中不能含有：1 1、貨幣符號、貨幣符號$、圓括號或百分比符號、圓括號或百分比符號%。2 2、儲存格位址。、儲存格位址。3 3、長度不相等的欄或列。、長度不相等的欄或列。1/16/202326