Excel 42 函數篇 (完全攻略)

Size: px

Start display at page:

Download "Excel 42 函數篇 (完全攻略)"

吾岳
9 years ago
Views:

1 Excel 42 函數篇 ( 完全攻略 ) Web: [email protected] Date: 2011/10/25-1 -

2 目錄 1. 公式應用前言公式組成項目參照位址運算子公式中執行運算的順序函數應用前言函數的結構如何使用函數函數 Excel 42 函數進階函數附錄 :CSF Excel 2003 函數與題號對照表 Index

3 1. 公式應用 1.1 前言 Microsoft Excel 的應用中, 最強大的功能就是利用公式 ( 與函數不同 ) 進行資料分析, 公式是在工作表中執行數值計算的方程式, 當然亦可以處理字串資料公式以一個等號 (=) 開始, 如 : 下列公式的計算方式是先以 2 乘 3, 再加上 1 得到結果,A1:=1+2*3 會得到結果為 7, 實際應用時最好以括號區別, 如 :=1+(2*3) 文章首先說明何謂公式, 其次再說明函數的使用另為何取名 42 函數呢? 答案在後續文章中 1.2 公式組成項目公式中也可以包含下列任一項目 : 函數參照運算符號與常數 1. 函數 : 如 : 輸入 =TODAY() 函數會傳回今天系統日期的序列值, 請參考後序文章之說明 2. 參照 ( 或名稱 ): A2 會傳回儲存格 A1 中的數值加上 5 如:=A 運算符號 : 如 : ^ 運算符號設定數字為乘冪, 星號 (*) 運算符號則表示相乘 4. 常數 : 直接輸入公式的數字, 日期或文字, 如 : 上圖中 A1:=TODAY() 1.3 參照位址 - 3 -

4 可以識別工作表上的一個儲存格或一個儲存格範圍, 並告訴 Microsoft Excel 在哪裡可以找到公式要用的數值或資料透過參照位址, 您可以在一個公式中使用一個工作表之不同部份中所包含的資料, 或在多個公式中使用同一個儲存格中的值您可以參照至同一個活頁簿中其他工作表上的儲存格參照到其他活頁簿中的儲存格稱之為連結即參照位址包括欄名 (A:IV) 與列號 (1:65536) 參照位址包括相對參照絕對參照混合參照 (1). 相對參照 : 公式內的相對儲存格參照例如 :A1, 是根據包含此公式的儲存格和此參照所指向的儲存格之相對位置若包含此公式的儲存格之位置變更, 此參照也會被變更若沿著列或欄複製公式, 參照會自動地調整在預設的情況, 新公式會使用相對參照例如, 若複製儲存格 B2 內的相對參照至儲存格 B3, 則 B3 會從 =A1 自動調整 =A2 (2). 絕對參照 : 公式內的絕對儲存格參照, 就是有加上 $ 符號例如 : $A$1, 永遠參照到一個特定的位置若包含公式的儲存格之位置變更, 絕對參照將保持不變若將公式沿著列或欄複製, 絕對參照並不會作調整在預設的情況, 新公式使用相對參照, 您無須將它切換至絕對參照例如, 若複製了儲存格 B2 內的絕對參照至儲存格 B3, 兩個儲存格內的參照將會相同 =$A$1 (3). 混合參照 : 混合參照為有一個絕對欄 ( 有加上 $) 及相對列, 或是有一個絕對列 ( 有加上 $) 及相對欄絕對欄參照的型式為 $A1 $B1 等等絕對列參照的型式為 A$1 B$1 等等若包含公式的儲存格位置改變, 相對參照會改變, 但是絕對參照並不會改變若將公式沿著列或欄複製, 相對參照將會自 - 4 -

5 動調整, 但絕對參照並不會作調整例如 : 若從儲存格 B2 複製一個混合參照至儲存格 B3, 它會從 =A$1 調整為 =B$1, 因 A$1 中的 1 列前面有 $, 故複製後仍保持第 1 列注意 : 切換參照位址可於資料編輯列中使用 F4 重要!!! 思考題 : 若來源儲存格 A3=$A1+A$2, 則 COPY 至 C5 儲存格後之結果 ANS: 請自行輸入試一下思考題 : 如何將檔案 BOO1 的儲存格位址參照到 BOOK2 的工作表 A1 絕對位址 ANS:=[Book2]Sheet1!$A$1 1.4 運算子運算子負責指定公式元素的計算類型 Excel 包含四種不同類型的計算運算子 : 算術比較文字以及參照 1. 算術運算子 : 若要執行基本的數學運算, 像是 : 加減乘結合數字以及產生數字結果, 請使用下列的算術運算子算術運算子意義 ( 範例 ) +( 加號 ) 加法 (3+3) ( 減號 ) 減法 (3 1) 負數 ( 1) *( 星號 ) 乘 (3*3) /( 斜線 ) 除法 (3/3) - 5 -

6 %( 百分比符號 ) 百分比 (20%) ^( 次方符號 ) 乘冪 (3^2) 2. 比較運算子 : 可以使用下列的運算子來比較兩個數值當您使用這些運算子來比較兩個數值時, 結果將會是邏輯值, 即非 TRUE 即 FALSE 比較運算子意義 ( 範例 ) =( 等號 ) 等於 (A1=B1) >( 大於符號 ) 大於 (A1>B1) <( 小於符號 ) 小於 (A1<B1) >=( 大於或等於符號 ) 大於或等於 (A1>=B1) <=( 小於或等於符號 ) 小於或等於 (A1<=B1) <>( 不等於符號 ) 不等於 (A1<>B1) 3. 文字 ( 關聯 ) 運算子 : 使用 & 符號 ( 數字 7 的上方 ) 來連結 ( 關聯 ) 一個或多個文字串, 以產生一個新的文字文字運算子意義 ( 範例 ) & 連接或連結二個值, 產生一個連續文字值, 如 : =" 歡迎光臨 "&"WEPA 網站 " 4. 參照運算子 : 可以下列運算子結合儲存格範圍加以計算參照運算子意義 ( 範例 ) - 6 -

7 :( 冒號 ) 範圍運算子, 可以將一個參照位址擴大到二個參照位址之間 ( 包含這二個參照位址 ) 的所有儲存格, 如 :=SUM(B1:B15) 白話文 : 選出連續範圍,( 逗號 ) 聯合運算子, 可以將多個參照位址結合成一個參照位址, 如 :=SUM(B5:B15,D5:D15) 白話文 : 選出不連續的範圍 ( 空白 ) 交集運算子, 產生由兩個參照交集部份的儲存格參照, 如 :=A3:C5 C1:C3 結果會顯示 13 白話文 : 選出交集的內容 - 7 -

8 1.5 公式中執行運算的順序公式按特定順序計算數值 Microsoft Excel 中的公式通常以等號 (=) 開始, 表示之後的字元為公式緊隨等號之後的是需要計算的元素, 各元素之間以算術運算子分隔 Excel 根據公式中運算子的特定順序, 由左至右計算公式運算子優先順序 : 若單一個公式中結合多個運算子,Excel 會按照下表中顯示的順序來執行運算如果公式裡面的運算子有相同的前導參照 ( 例如, 一個公式中同時有乘法和除法運算子 )Excel 會由左至右評估運算子, 運算式中可配合括號的使用優先順序運算子敘述 1 :( 冒號 ) 參照運算子 ( 一個空格 ),( 逗號 ) 2 負 ( 如 :-1) 3 % 百分比 4 ^ 乘冪 5 * / 乘和除 6 + 加和減 7 & 連接二個文字字串 ( 連結 ) 8 = < > <= >= < > 比較 <=: 小於或等於符號 >=: 大於或等於符號 < >: 不等於符號 - 8 -

9 2. 函數應用 2.1 前言何謂函數 (Function)? 函數是一些事先定義的公式, 它們使用一些稱為引數的特定數值, 按特定的順序或結構進行計算函數可用於進行簡單或複雜計算例如,ROUND 函數可以四捨五入儲存格中的數字 2.2 函數的結構函數的結構是以等號開頭 (=), 後面是函數名稱左圓括號以逗號分隔的引數和右圓括號如 :=SUM(A1:A10) 2.3 如何使用函數插入函數時可採用以下四種方式 : 1. 按一下儲存格並按 SHIFT+F3(+ 表示同時按下此二個按鍵 ) 2. 直接按下插入函數的工具按鈕 3. 按一下儲存格後再按 F2 輸入內容 4. 按一下儲存格後直接輸入內容, 如 :=SUM(A1:A10), 建議可採用方法 4 較快速直接於儲存格中以鍵盤輸入時, 記得配合 SHIFT CTRL :, END( 按 F8) 滑鼠 ENTER 直接按資料編輯列旁邊的 fx, 其中打 X 表示取消編輯, 相當於按 ESC, 打勾表示確定, 相當於按 ENTER - 9 -

插入函數對話方塊函數中引數可以是數字文字邏輯值 (TRUE 或 FALSE) 陣列如#N/A 的錯誤值或儲存格參照您指定的引數必須能產生有效的值引數也可以是常數公式或其他函數引數工具提示 : 含語法與引數的工具提示會在您鍵入函數時出現, 例如, 您鍵入 =ROUND(, 則工具提示會出現只有在鍵入內建函數時會出現工具提示圖中表示第 1 個引數是數值, 第 2

10 插入函數對話方塊函數中引數可以是數字文字邏輯值 (TRUE 或 FALSE) 陣列如#N/A 的錯誤值或儲存格參照您指定的引數必須能產生有效的值引數也可以是常數公式或其他函數引數工具提示 : 含語法與引數的工具提示會在您鍵入函數時出現, 例如, 您鍵入 =ROUND(, 則工具提示會出現只有在鍵入內建函數時會出現工具提示圖中表示第 1 個引數是數值, 第 2 個引數是四捨五入所指定的位數輸入公式您建立包含函數的公式時,[ 插入函數 ] 對話方塊可協助您輸入工作表函數在您將函數輸入公式時,[ 插入函數 ] 對話方塊會顯示函數的名稱函數的每個引數函數和每個引數的敘述函數目前的結果和整個公式目前的結果巢狀函數 : 在某些情況下, 您可能需要將某函數作為另一函數的引數使用 Excel 中的函數區分十一類別, 約 333 個, 若要全部會用, 似乎是 MI,

11 重點應是明瞭一些常用函數的使用, 以加快日常的資料分析工作, 若有其他的需求可按 F1 查詢之

12 3. 函數第三章中將說明 Excel 42 函數 ( 因 CSF Excel 2003 檢定中會使用約有 42 個不同函數, 故稱為 42 函數, 亦是必須熟練函數 ) 與進階函數 3.1 Excel 42 函數線上搜尋範例 :

13 1.AND =AND(logical1,logical2, ) 如果它的所有引數都是 TRUE, 則傳回 TRUE 如果有一個或多個引數是 FALSE, 則傳回 FALSE logical1, logical2,... 指要測試的 1 到 30 個條件, 可能是 TRUE 或 FALSE 在 D1 輸入 60,E1 輸入公式會顯示 TRUE 2.AVERAGE =AVERAGE(number1,number2,...) 計算引數的平均值 Number1, Number2,... 是 1 到 30 個數字引數引數必須是數字, 或是含有數字的名稱陣列或參照位址如果陣列或參照引數包含文字邏輯值或空白儲存格, 則忽略這些數值 ; 但零值儲存格會加以計算平均 A5=AVERAGE(A1:A4),A3 輸入 WEPA, A5 = 1+2+2, 3 分母為 3 是因為 A2 為文字不到入計算, 如果 A3 改為 0, 則結果為 COUNT A5 = 4 =COUNT(value1,value2,...) 計算含有數字的儲存格數量使用 COUNT 可以在一

14 個數字陣列或範圍中取得一個數字欄位內的項目數量 value1, value2,... 是 1 到 30 個引數, 裡面可能含有或參照到不同類型的資料, 但只計算數字部份可以列入計算的引數有數字日期或代表數字的文字不列入計算的引數有錯誤值, 以及不能轉換成數字的文字

15 4.COUNTIF =COUNTIF(range 範圍,criteria 搜尋準則 ) 計算某範圍內符合搜尋準則的儲存格個數, 搜尋準則須加左右引號 Range 是您儲存格範圍,Criteria 是搜尋準則, 可以是數字表示式或文字如 :A6 計算 A2:A5 範圍中為蘋果之個數 5.DATEDIF =DATEDIF(Date1,Date2,Interval) 十分特殊用法 (DATEs DIFference 缩寫 ), 結果會傳回二個日期時間差異, 在 Excel 2000 輔助文件中有說明, 詳細可參考以下第 1 個網址, 第 2 個網址為微軟技術社群討論區 : (1) if.htm (2) ity/newsgroups/dgbrowser/zh-tw/default.mspx?query=datedif&dg=microsoft.publi c.tw.excel&cat=zh_tw_8df142e8-7dbc-4a8 5-bfca-c2a2e &lang=zh&cr=TW&pt=&

16 catlist=&dglist=&ptlist=&exp=&sloc=zhtw Date1 是第一個日期, 以標準優于連續日期的形式 Date1,Date2 是日期時間,Date1 一定小於或者等於 Date2, 否則會有 #NUM!錯誤如果 Date1 或者 Date2 不是日期時間, 會有 #VALUE 錯誤,Interval 為差異時間格式 Interval 編碼格式如下 : Interval 編碼內容 "m" 月 "d 天 "y" 年 "ym" 月 ( 不考慮年 ) "yd" 天 ( 不考慮年 ) "md" 天 ( 不考慮年, 月 ) B1=DATEDIF(A1,NOW(),"y") 可顯示年齡

6.DAY =DAY( 序列值 ) 傳回日期的天數, 以序列號碼表示之日數的有效範圍是 1 到 31 之間的整數 =DAY("2006/2/6"), 如果 A1 輸入 2006/2/6, 則 A2=DAY(A1) 亦會有相同之結果, 用儲存格當引數時 7.EDATE 不用加引號 =EDATE( 日期, 增加月數 ), 這是什麼東東, 完全沒用過!

17 6.DAY =DAY( 序列值 ) 傳回日期的天數, 以序列號碼表示之日數的有效範圍是 1 到 31 之間的整數 =DAY("2006/2/6"), 如果 A1 輸入 2006/2/6, 則 A2=DAY(A1) 亦會有相同之結果, 用儲存格當引數時 7.EDATE 不用加引號 =EDATE( 日期, 增加月數 ), 這是什麼東東, 完全沒用過!!! 唉本函數是計算增加 ( 減少 ) 月份後的日期 =EDATE("2006/2/6",1) 會對計算 2 月 6 日增加 1 個月後的日期, 結果為 2006/3/6, 本函數須配合增益集的使用, 否則會有錯誤且儲存格之格式設定為日期, 同理 =EDATE("2006/1/6",-1) 工具 / 增益集 / 分析工具箱打勾, 參考以下網址 ( 英文 ) 題 B2 =EDATE(A2,1) 8.FREQUENCY =FREQUENCY(data_array,bins_array) 計算某一個範圍內的值出現的次數, 並傳回一個垂直數值陣列例如, 用 FREQUENCY 來計算某些範圍內的考試成績各有幾個人由於 FREQUENCY 回陣列,

18 因此必須輸入為陣列公式, 陣列公式參考以下的 SHIFT+CTRL+ENTER 說明,Data_array 是一個要計算頻率的數值陣列或數值參照位址 Bins_array 是一個陣列或一個區間的儲存格範圍參照位址, 用來存放 data_array 裏的數值分組之結果例如圖中希望了解學生成績之分佈情形 : STEP1: 選取 C2 至 C7 儲存格 STEP2: 直接輸入 =FREQUENCY(A2:A10,B2:B6) STEP3: 按 CTRL + SHIFT + ENTER 務必自行練習!!!

19 9.HLOOKUP HLOOKUP(lookup_value,table_array,row_i ndex_num,range_lookup) 與 VLOOKUP 類似但方向不同,H 表示 horizontal (1).Lookup_value: 表格第一列中搜尋的值 (2).Table_array: 是要在其中搜尋資料的資料表格通常此引數是個儲存格範圍的參照位址或範圍名稱 (3).Row_index_num: 是個數字, 代表所要傳回的值位於 table_array 列中的第幾列如果 row_index_num 引數值為 1, 傳回 table_array 裡第一列的值, 如果 row_index_num 引數值為 2, 傳回 table_array 裡第二列的值, 依此類推如果 row_index_num 小於 1, 則 HLOOKUP 函數傳回錯誤值 #VALUE!; 如果 row_index_num 超過 table_array 的總列數, 則 HLOOKUP 函數傳回錯誤值 #REF! (4).Range_lookup : 是個邏輯值, 用來指定 HLOOKUP 要尋找完全符合或部分符合的值當此引數值為 TRUE 或被省略了, 會傳回部分符合的值 ; 也就是找不到完全符合的值時, 會傳回僅次於 lookup_value 的值比較與 VLOOKUP 之不同當此引數值為 FALSE 時,HLOOKUP 只會尋找完全符合的值, 如果找不到, 則傳回錯誤值 #N/A

20 思考題 : 如果上例 D5 改成 FALSE? =HLOOKUP(C5,B1:E2,2,FALSE)

21 10.HYPERLINK =HYPERLINK( 連結位置, 顯示文字 ) 連結位置 : 為所要開啟的文件路徑和名稱並可以參照到文件中的某個地方, 如 Microsoft Excel 工作表或活頁簿中的某一儲存格或名稱範圍, 或是 Word 文件中的書籤這個路徑可以是一個硬碟中的檔案在伺服器中的全球命名慣例 (UNC), 或是在網際網路或內部網路中的統一資源位置 (URL) 顯示文字 : 即在儲存格中所顯示的文字或數值資料這個儲存格內容會以藍色和底線來顯示如 : 儲存格輸入以下內容試一下 =HYPERLINK(" 11.IF n9956/", "WEPA home") =IF( 判別式, 判別式為 TRUE 之表示法, 判別式為 FALSE 之表示法 ) 如果您指定的情況結果為 TRUE, 則傳回一個值, 若結果為 FALSE, 則傳回另一個值 IF 函數可用於測試數值和公式的條件如 A1 輸入 80,B1 儲架格輸入 : =IF(A1>=60," 及格 "," 不及格 "),TRY IT! 12.INDIRECT INDIRECT(ref_text,a1) 傳回一文字串所指定的參照位址該參照位址內容會被立刻計算並顯示出來 INDIRECT 函數通常是您在公式中想要改變參照位址卻不想改變公式本身時使用搞不懂在寫什麼???

22 Ref_text: 是個單一儲存格的參照位址 ; 而這個儲存格含有依 A1 格式或 R1C1 格式所指定的參照位址一個定義為參照位址的名稱或是一個定義為參照位址的字串如果 ref_text 不是一個有效的儲存格位址, 則 INDIRECT 傳回錯誤值 #REF! 如果 ref_text 指向另一個活頁簿 ( 外部參照 ), 則那個活頁簿必須已經是開啟的, 否則 INDIRECT 傳回錯誤值 #REF! A1: 是個邏輯值 ; 用以區別 ref_text 所指定的儲存格參照位址, 是以哪種方式表示的如果 a1 為 TRUE 或被省略了, 則 ref_text 被解釋成 A1 參照表示方式如果 a1 為 FALSE, 則 ref_text 被解釋成 R1C1 參照表示方式範例 :E4 輸入 =SUM(INDIRECT("B"&(E2+1)&":B"&(E3+1))), 變更 E2,E3 可做不同計算思考題 : 圖中累計銷售額如何輸入?

23 13.ISBLANK =ISBLANK(VALUE), 判斷 VALUE 值是否為空白, 如果是一個空白儲存格時,ISBLANK 函數會傳回邏輯值 TRUE, 否則便傳回 FALSE A1 輸入 WEPA,A2 輸入 =ISBLANK(A1) IS 函數共有以下 9 種函數會傳回 TRUE 的情況 ISBLANK Value 指的是空白儲存格 ISERR Value 指的是 #N/A 之外的任何一種錯誤值 ISERROR Value 指的是任何一種錯誤值 (#N/A #VALUE! #REF! #DIV/0! #NUM! #NAME? 或 #NULL!) ISLOGICAL Value 指的是邏輯值 ISNA Value 指的是錯誤值 #N/A( 無法使用的數值 ) ISNONTEXT Value 指的是任何非文字的項目 ( 請注意 : 如果數值參照到空白儲存格, 則此函數也會傳回 TRUE) ISNUMBER Value 指的是數字 ISREF Value 指的是參照 14.ISERROR ISTEXT 參考 IS 函數 Value 指的是文字 15.ISNUMBER 參考 IS 函數 16.ISTEXT 參考 IS 函數 A1 =ISTEXT(wepa) 結果 :Try it!

24 17.LEFT =LEFT( 文字, 選取的字元數 ) 傳回文字自左算起所要選取字元數之字串, 選取的字元數預設值為 1 =LEFT( A ,5), 結果為 A1234, 類似於 MID( ) RIGHT( ) 18.LEN =LEN( 文字字串 ) 傳回一文字字串的字元個數範例 :=LEN(" 實用資訊 ") 傳回 4 19.MATCH =MATCH( 尋找的值, 資料表格, match_type) 根據指定的比對方式, 傳回一陣列中 ( 資料表格 ) 與搜尋值相符合之相對位置當您需要取得符合搜尋條件的元素之相對位置而非元素本身時, 您應使用 MATCH 函數, 而非 LOOKUP 函數 match_type=1( 省略時之預設值 ), 則 MATCH 函數會找到等於或僅次於尋找的值資料表格必須以遞增次序排列 :...,-2,-1,0,1,2,...A-Z,FALSE, TRUE 如果 match_type=0, 則 MATCH 函數會找第一個完全等於尋找的值資料表格可以依任意次序排列如果 match_type=-1, 則 MATCH 函數會找到等於或大於尋找的值之最小值資料表格必須以遞減次序排列 :TRUE,FALSE,Z-A,...,2,1,0,-1, -2,... 範例 :

25 公式 (A6 輸入 ) 結果 =MATCH(39,B2:B5,1) 因為沒有任何完全相符的項目, 傳回在範圍 B2:B5 中次低的值 38--> 結果 : 2( 表示第 2 個 ) =MATCH(41,B2:B5,0) 在範圍 B2:B5 中,41 的位置結果 :4( 表示第 4 個 ) =MATCH(40,B2:B5,-1) 因為範圍 B2:B5 不是以降冪次序排列, 所以傳回錯誤 (#N/A)

26 20.MAX =MAX( 資料範圍 ) 傳回一組數值 ( 最多 30 個 ) 中的最大值同理 =MIN( 資 21.MID 料範圍 ) MID(text, start_num, num_chars) 傳回自一字串您所指定的某個起始位置傳回特定數目的字元組 ( 依據您指定的字元組數 ) Start_num: 為用以指定您要由 text 的第若干個位元組開始抽選 text 中的第一個字元為 start_num1, 依此類推 Num_chars: 指定您要 MID 從字串傳回的字元組 Num_bytes: 指定您要 MID 從字串傳回的字元組 ( 以位元組 ) 範例 :=MID( 中華民國,2,2) 22.MIN =MIN( 資料範圍 ) 23.MOD 傳回一組數值 ( 最多 30 個 ) 中的最小值傳回兩數相除後之餘數餘數和除數具有相同的正負號 =MOD(number, divisor) Number 為計算餘數時做為被除數的實數,Divisor 為計算餘數時做為除數的實數如果除數為 0,MOD 將傳回 #DIV/0! 的錯誤值 MOD 函數的值可以使用 INT 函數來表示 :MOD(n, 24.MONTH d) = n - d*int(n/d) =MONTH( 序列值 ) 傳回序列值日期的月份月份數為介於 1(1 月 ) 到 12(12 月 ) 之間的整數

27 Excel 以循序數列的序列值來儲存日期, 以至於它們可以用來計算依預設值,1900 年 1 月 1 日是序號 1, 而 1933 年 10 月 18 日則是序號 12345, 因為這是 1900 年 1 月 1 日之後的第天計算今天的月份, =MONTH(TODAY())

28 25.PMT =PMT(rate,nper,pv,fv,type) 傳回每期付款金額及利率固定之年金期付款數額有關 PMT 中引數的完整說明, 請參閱 PV 函數 Rate 為各期的利率 Nper 為年金的總付款期數 Pv 為未來各期年金現值的總和 Fv 為最後一次付款完成後, 所能獲得的現金餘額 ( 年金終值 ) 如果省略 fv 引數, 會自動假定為 0, 也就是說, 貸款的年金終值是 0 Type 為 0 或 1 的數值, 用以界定各期金額的給付時點設定 type 為 0 或省略期末,1 期初 A3 輸入 PMT 函數,PMT 有負號每月須繳 30,299 元 26.RANK =RANK( 欲知道等級的數字, 數值陣列, 指定的順序 ) 傳回某數字在一串數字清單中的等級其中指定的順序包含 0( 或省略 ) 1, 差異如下 : 如果指定的順序為零或被省略, 則 Excel 將數值陣列當成從大到小排序如果指定的順序不是 0, 則將數值陣列當成從小到大排序來評定範例 : 名次有可能重複

29 注意 :$B$2:$B$6 為絕對位址 27.REPT =REPT( 文字字串, 重複次數 ) 依指定的次數重複顯示文字 REPT 函數通常用來在儲存格中填入重複出現的文字字串文字字串是您所要重複顯示的文字資料重複次數是個正數, 用以指定所要重複的次數如果重複次數為 0,REPT 函數將傳回 " " ( 空字串 ) 如果重複次數不是整數, 其小數部分會被捨去 REPT 函數運算的結果最長不得超過 32,767 個字元, 否則 REPT 將傳回 #VALUE! 錯誤 =REPT( *,3)

30 28.RIGHT =RIGHT( 文字, 選取的字元數 ) 29.ROUND 傳回自一文字串的自右算起字元或字元組 =ROUND(number,num_digits) 依所指定的位數, 將數字四捨五入 Number: 是個您想執行四捨五入的數字 Num_digits: 是對數字執行四捨五入計算時所指定的位數如果 num_digits 大於 0, 則數字將被四捨五入到指定的小數位數如果 num_digits 等於 0, 數字將被四捨五入成整數如果 num_digits 小於 0, 數字將被四捨五入到小數點左邊的指定位數 30.ROUNDDOWN =ROUNDDOWN(number,num_digits) 將數值作無條件捨去 Number: 無條件捨去的任何實數 Num_digits: 無條件進位時所採用的位數範例 : 公式結果 =ROUNDDOWN(3.5,0) 3 =ROUNDDOWN( ,1) -3.1 =ROUNDDOWN( ,-2) 31, ROUNDUP =ROUNDUP(number,num_digits) 將數值做無條件進位 Number: 無條件進位的任何實數 Num_digits: 無 32.STDEVP 條件進位時所採用的位數 =STDEVP(number1,number2,...)

31 將引數串列視為母體本身, 傳回其母體標準差該標準差主要是用以衡量觀測資料與其平均數之間的差異量數 Number1, number2, 是對應於某母群體的一到三十個數字引數您也可以使用一個陣列或是陣列的參照位址, 不必都用逗號分隔開的一串引數 TRUE 和 FALSE 等邏輯值及文字, 將被忽略當邏輯值和文字不許被忽略時, 請使用工作表函數 STDEVPA STDEVP 函數假定它的引數串列是整個母群體如果您的觀測資料代表該母群體的抽樣樣本, 則應該使用 STDEV 函數來計算標準差當樣本個數愈大時, STDEV 與 STDEVP 函數所算出的標準差估計值會愈趨於相等標準差的計算是採用偏誤估計或 n 法 STDEVP 函數的計算公式是 : n i= 1 ( X X ) i n 2, 其中,X 為母體平均數, 而 n 為母體大小 (STDEV 函數計算時分母為 n 1)

32 33.SUM =SUM(number1,number2,...) 傳回引數串列中所有數值的總和記得利用連續範圍符號 : 與不連續範圍符號, (CTRL 鍵 ) 空白儲存格邏輯值文字或錯誤值都會被忽略 =SUM(A1:A3,A5,A7:A10) =SUM("5",15,TRUE) 加總 5 15 和 1, 因為文字 5 34.SUMIF 被轉換成數值, 而邏輯值 TRUE 則被轉換成數字 1 SUMIF( 範圍, 決定是否列入加總的搜尋準則, 實際要加總的儲存格 ) 加總符合某特定搜尋準則的儲存格範例 : 公式 =SUMIF(A2:A5,"> ",B2:B5) 結果財產總值超過的佣金總和結果 :63000 注意 : 實際是加總符合條件之佣金 35.SUMPRODUCT =SUMPRODUCT( 陣列 1, 陣列 2,, 陣列 30) 傳回各陣列中所有對應元素乘積的總和範例 :

33 =SUMPRODUCT(A2:B4,C2:D4) -->3*2+4*7+8*6+6*7+1*5+9*3--> 結果

34 36.TEXT =TEXT(value,format_text) 以指定的數字格式將數值轉成文字 Value 可以是數值一個會傳回數值的或者是一個參照到含有數值資料的儲存格位址 Format_text 為 [ 儲存格格式 ] 對話方塊的 [ 數值 ] 索引標籤上的 [ 類別 ] 方塊中所使用之文字形式的數字格式 format_text 不能含有代表通配字元的星號 (*) 以 [ 數值 ] 索引標籤 ([ 格式 ] 功能表的 [ 儲存格 ] 命令 ) 上的選項來設定含有數字資料之儲存格的格式, 僅能改變其顯示的格式, 而不會改變其數值使用 TEXT 函數設定數字的顯示格式, 則是將數值資料轉換成文字資料, 其結果將不再是數值資料範例 : 37.TODAY =" 總金額為 : " & TEXT(60000,"$#,##0.00") =TODAY( ) 函數 TODAY() 可傳回電腦系統中現在日期二個日期相減 = 二個日期差距天數, 相減後的格式設定須以數值表示, 否則會顯示錯誤範例 :A1 表示到職日期, 則下到結果為? =(TODAY()-A1)/ TRANSPOSE =TRANSPOSE(array) 將儲存格之垂直範圍以水平範圍的格式傳回, 反之亦可 Array 是工作表或巨集表中您所要轉置的矩陣陣列的轉置是以陣列的第一列作為新陣列的第一欄, 而陣

35 列的第 2 列則為新陣列的第 2 欄, 依此類推範例 : STEP1: 先輸入 A1:C3 數值 STEP2: 選取 A5:C7 範圍 STEP3: 輸入 =TRANSPOSE(A1:C3), 不要按 ENTER STEP4: 還記得有

36 39.VALUE =VALUE( 文字資料 ) 代表某數值的文字字串轉換成數字資料範例 : =VALUE("$1,000") 40.VLOOKUP =VLOOKUP( 欲搜尋值之儲存格, 遞增排序後之範圍, 範圍中第幾行, 邏輯值 ) 資料表格, 通常是儲存格範圍的參照位址或清單的範圍名稱或類似資料庫資料表格中第 1 欄 ( 對應到搜尋的值 ) 須由小至大排序才不會有錯誤邏輯值可省略, 用來指定 VLOOKUP 要尋找完全符合或部分符合的值當此引數值為 TRUE 或被省略了, 會傳回部分符合的數值 ; 也就是說, 如果找不到完全符合的值時, 會傳回僅次於搜尋的值當此引數值為 FALSE 時,VLOOKUP 函數只會尋找完全符合的數值, 如果找不到, 則傳回錯誤值 #N/A #N/A 可配合 ISNA 函數使用一般採用 FALSE 即可, 超級好用之函數, 務必記下於工作或生活中可使用, 另外一個 41.WEEKDAY 是 HLOOKUP =WEEKDAY(serial_number,return_type) 傳回符合日期的星期給定的日預設為介於 1 ( 星期日 ) 到 7 ( 星期六 ) 之間的整數 Serial_number 想要尋找該天日期的序列值日期應以 DATE 函數輸入, 或是其他公式或函數的結果例如, 以 DATE(2008,5,23) 來表示 2008 年 5 月 23 日如果日期以文字格式輸入將會產生錯誤 Return_type 是決定傳回值類型的數字

37 Return_type 傳回的數字 :1 或省略表示 1 ( 週日 ) 到 7 ( 週末 ), 與 Microsoft Excel 舊版的性質相同 2 數字 :1 ( 星期一 ) 到 7 ( 星期日 ) 3 數字 :0 ( 星期一 ) 到 6 ( 星期六 ) 範例 : 42.YEAR 參考 MONTH

38 3.2 進階函數函數說明 * COLUMN( 範圍 ) 傳回範圍中的欄號範圍 : 是您想知道欄號的單一儲存格或儲存格範圍如果範圍被省略了, 則 COLUMN 函數會引用本身的儲存格位址如果範圍為一個儲存格範圍, 而且 COLUMN 函數以水平陣列的格式輸入, 則 COLUMN 函數會以水平陣列的方式傳回各直欄的欄號 --> 技巧範圍不可指向多個區域 * DB( 購入成本, 殘值, 使用年限, 第幾年, 傳回以固定餘額遞減法計算之一定期間內資產的折舊第一年月份數 ) * DDB( 購入成本, 殘值, 使用年限, 第幾年, 餘額遞減的速率 ) 傳回某項固定資產在某期間內按加倍餘額遞減法或其他指定方法計算所得之折舊值, 餘額遞減的速率預設值為 2( 即採用加倍餘額遞減法 -- 其折舊數額在第一個週期是最高的, 然後依次遞減 ) * FV( 利率, 期數, 每期付款金額 ) 根據週期固定支出以及固定利率, 傳回投資的未來值

39 一般使用每期付款金額以負數表示則 FV 結果會有正值, 但如果題目要求不可修改題目中每期付款金額以正數表示故只好於 FV 之前加上負號 * INDEX( 陣列, 第幾列, 第幾行 ) 範例 : INDEX({1,2;3,4},2,1) 等於 3 第 1 行第 2 行第 1 列 1 2 第 2 列 3 4 * SQRT( 數值 ) 傳回數值的正平方根, 如果此數字是負數, 則會傳回錯誤值 #NUM! * SYD( 購入成本, 殘值, 使用年限, 第幾年 ) 傳回某項固定資產在某期間按年數合計法 (sum-of-years) 計算的每期折舊金額

40 附錄 :CSF Excel 2003 函數與題號對照表編號函數題號 1 AND 307,309 2 AVERAGE 102,107,201,202,204,205,210,303,306,510 3 COUNT COUNTIF 106,507 5 DATEDIF DAY EDATE FREQUENCY HLOOKUP HYPERLINK IF 108,205,307,310, INDIRECT 308, ISBLANK 108, ISERROR ISNUMBER ISTEXT LEFT 307, LEN MATCH MAX 104,

41 編號函數題號 21 MID MIN 104,202, MOD MONTH PMT RANK 106,204,209, REPT RIGHT ROUND 303,306, ROUNDDOWN ROUNDUP STDEVP SUM 101,104,105,204,207,210,302, SUMIF SUMPRODUCT TEXT TODAY TRANSPOSE VALUE 306,307, VLOOKUP 108,209,306,308,310, WEEKDAY YEAR

42 Index: 1.AND AVERAGE COUNT COUNTIF DATEDIF DAY EDATE FREQUENCY HLOOKUP HYPERLINK IF INDIRECT ISBLANK ISERROR ISNUMBER ISTEXT LEFT LEN MATCH

43 20.MAX MID MIN MOD MONTH PMT RANK RIGHT ROUND ROUNDDOWN ROUNDUP STDEVP SUM SUMIF SUMPRODUCT TEXT TODAY TRANSPOSE VALUE VLOOKUP WEEKDAY

44 42.YEAR 全篇完!!! 有任何問題, 歡迎討論 --- END

ACI pdf

ACI pdf 09 9.1 -...9-2 9.1.1...9-2 9.1.2...9-3 9.2 -...9-4 9.2.1 PMT - ()...9-4 9.2.2...9-6 9.3 -...9-8 9.3.1 PMT - ()...9-8 9.4...9-10 9.4.1... 9-11 9.4.2...9-12 9.4.3...9-14 9.5 -...9-17 9.5.1...9-18 1 Excel...9-21