Microsoft PowerPoint - Excel9.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - Excel9.ppt"

汀杭尧宾
9 years ago
Views:

1 常用的進階函數林偉川文字函數 -Left Right 及 Mid 函數語法 : Left (text,num_chars),right (text,num_chars),mid(text, start, num_chars) Text 為含有所要選錄文字之字串 Num_char 指定您要 Left Right 及 Mid 選入的字元數 2 1

2 文字函數 -Concatenate (&) 及 LEN 函數定義 : 將數個文字串連成一個文字串語法 :CONCATENATE (text1,text2,...) Text1, text2,... 是要連接成一個文字串的 1 到 30 個文字串文字項目可以是文字字串數字或單一儲存格的參照位址定義 :LEN 傳回一文字字串的字元個數語法 :LEN (text) Text 為所要計算字元個數的文字串, 字串中所有的空白亦當作字元來處理 3 邏輯函數簡而言之, 邏輯函數就是用來判斷是非黑白的我們先來看看一般邏輯值的應用利用且或來組成的條件就是複合條件 Ex. =c9>=60 4 2

3 邏輯函數 -AND( 且 ) 函數語法 :AND(logical1,logical2,...) logical1,logical2,..., 係指您要測試的 1 到 30 個條件, 可能是 TRUE 或 FALSE 也可以透過函數引數交談窗來協助我們填入適當的引數值! 在 Logical2( 第二個條件式 ) 按一下, 就會出現第三個條件式讓你加入 =and(c3= 男,d3>=170) =and(d3>=165, d3<=175) =IF(AND(C2=" 女 ", D2>=170)," 太高了 ",IF(C2=" 男 ",""," 中等身高 ")) 5 邏輯函數 -OR( 或 ) NOT 函數語法 :OR(logical1,logical2,...) =IF(OR(C2=" 女 ", D2>=170)," 身高 170 公分或為女性 "," 條件不符 ") 定義 : 將引數之數值予以反轉 NOT 可用來確定某一數值不等於某一特定的數值語法 :NOT(logical) =IF(NOT(E2>60)," 不及格 "," 及格 ") 6 3

4 邏輯函數 -IF 函數語法 :IF (logical_test, value_if_true, value_if_false) 當 value_if_true 引數或 value_if_false 引數被執行時, 則 IF 函數傳回這些引數的運算結果, 而非引數本身 =IF(E2>=90,"A",IF(E2>=80,"B",IF(E2>=70,"C",IF (E2>=60,"D","F")))) 7 邏輯函數 - 多層次 IF 函數為配合 value_if_true 與 value_if_false 引數, 以處理更為精巧的條件測試, 則可使用多達七層的 IF 函數若分數是 89 以上 80 到 89 之間 70 到 79 之間 60 到 69 之間 60 以下傳回的等級是 A B C D F 8 4

5 日期與時間函數 -TODAY 函數語法 :TODAY( ) 無時間註解 : Excel 以循序序列值儲存日期, 因此它可被用於計算預設序列值為 1, 表示是 1900 年 1 月 1 日, 並且 2008 年 1 月 1 日的序列值為 39448, 因為此日期是在 1900 年 1 月 1 日的天之後語法 :Now( ) 無時間儲存格格式是 [ 通用 ] 或 [ 日期 ] 就會像下面 : 9 日期與時間函數 -Month( 日期 ) 函數定義 : 傳回 serial_number( 序列值 ) 代表日期的月份月份數為介於 1 (1 月 ) 到 12 (12 月 ) 之間的整數語法 :MONTH(serial_number) 使用 DATE(2008,5,23) 表示 2008 年 5 月 23 日 YEAR,DAY 與 MONTH 用法相同只是分別傳回日期引數的年與日 =IF(MONTH(F2)=MONTH(TODAY())," 本月壽星 ","") 10 5

6 日期與時間函數 -Month( 日期 ) 函數在 W4 儲存格輸入 =TODAY()-D4, 即今天的日期減去出生的日期將格式改為 [ 通用 ] 或 [ 數值 ] 結果為日數將公式改成 =(TODAY()-D4)/365, 結果即年齡, 為 19 歲多一些 11 DATEDIF 函數 DATEDIF 函數可以幫我們計算兩個日期之間的年數月數或天數其格式如下 : 12 6

7 DATEDIF 函數應用實例若想計算員工中從到職日至 92 年 10 月 31 日止的服務年資, 就可以這麼計算 : 13 DATEDIF 的差距單位參數在 DATEDIF 函數中, 可依據您要求算的結果, 搭配使用各種差距單位參數, 列表如下供您參考 : 14 7

8 DATEDIF 的差距單位參數假設要計算某人的實際年齡滿幾年幾月幾天, 只要輸入如下的公式即可計算出來 : 15 星期函數 -weekday 函數定義 : 使用 index_num 自引數清單中傳回相對應的引數數值語法 :weekday( 日期 ) WEEKDAY 函數得到星期代碼 =WEEKDAY(F2) =weekday( 2004/12/7 ) 傳回 1 7 ( 星期日到星期六 ) 16 8

9 算數 ( 數學 ) 函數 -INT 函數定義 : 傳回指定小數位數無條件捨去之整數值語法 :INT (number) Number 想要無條件捨去成為一整數的實數 =INT((TODAY()-F2)/365.25) 語法 :Round (number,num_digits) Number 想要四捨五入成為一整數的實數 =ROUND((TODAY()-F2)/365.25,1) 17 算數 ( 數學 ) 函數 -Randbetween 函數定義 : 傳回一個大於等於 bottom 且小於 top 的隨機亂數每當工作表重算時, 便會傳回一個新的隨機亂數工具 / 增益集 / 分析工具箱安裝後才可使用語法 :Randbetween (bottom,top) 試試看在 1~49 號中要抽出一個號碼, 應該怎麼做? = Randbetween(1,49) 18 9

10 算數 ( 數學 ) 函數 -RAND 函數定義 : 傳回一個大於等於 0 且小於 1 的隨機亂數每當工作表重算時, 便會傳回一個新的隨機亂數語法 :RAND( ) 如果您希望產生的亂數是介於 a 與 b 之間的實數, 請使用公式 : RAND()*(b-a)+a 試試看在 1~49 號中要抽出一個號碼, 應該怎麼做? =INT(RAND()*49)+1 19 算數 ( 數學 ) 函數 -ABS 函數定義 : 傳回一個絕對值數值語法 :ABS( 數值或儲存格 ) 20 10

11 算數 ( 數學 ) 函數 -Sqrt 函數定義 : 傳回一個數值取平方根語法 :Sqrt( 數值或儲存格 ) 21 算數 ( 數學 ) 函數 -Round 函數定義 : 傳回一個數值取 4 捨 5 入語法 : Round( 數值或儲存格, 指定位數 ) 指定位數 >0 =round(35.32,1) 35.3 指定位數 =0 =round(35.52,0) 36 指定位數 <0 =round(35.32,-1)

12 算數 ( 數學 ) 函數 -COUNTIF 函數計算某範圍內符合某搜尋篩選條件的儲存格個數語法 :COUNTIF(range,criteria) Range 是您想計算符合篩選條件之儲存格個數的儲存格範圍 Criteria 是用以決定是否要列入計算的搜尋篩選條件例如 : 我們想要知道每一科及格的人數 =COUNTIF(E2:E10, <60 ), =COUNTIF(C2:C10, 男 ") 23 算數 ( 數學 ) 函數 -SUMIF 函數定義 : 加總符合某特定搜尋篩選條件的儲存格語法 :SUMIF(range, 篩選條件,sum_range) Range 是要計算加總的儲存格範圍篩選條件是用以決定要否列入加總的搜尋篩選條件, 可以是數字表示式或文字 Sum_range 是實際要加總的儲存格 =SUMIF(E2:E10,"<60") 24 12

13 統計函數 - COUNTA 函數 COUNTA 函數可用來計算引數範圍含有 " 非空白 " ( 包括文字或數字 ) 資料的儲存格個數以下圖為例, COUNTA (A1:D3) = 5: 25 統計函數 - FREQUENCY 函數 FREQUENCY 函數可用來計算一儲存格範圍內, 各區間數值所出現的次數, 再以垂直陣列回應各次數使用此函數時, 必須分別指定資料來源範圍以及區間分組範圍, 再以 + + 完成陣列公式的輸入 FREQUENCY 函數的格式為 : Data_array 要計算出現次數的資料來源範圍 Bins_array 資料區間分組的範圍 26 13

14 FREQUENCY 函數實例應用接著請選取 F3:F6 的儲存格範圍, 再輸入公式 "=FREQUENCY (C2:C13,E3:E6)" 然後按下 + + : 27 統計函數 -RANK 函數定義 : 傳回某數字在一串數字清單中的等級數字的等級就是數字相對於清單中其他數值的大小 ( 如果你把這清單中的數字排序, 則此數字的等級就是它所在的位置 ) 語法 :RANK(number,ref,order) Number 是要知道等級的數字 Ref 是一個數值陣列或數值參照位址, 非數值將被忽略 Order 是指定的順序 0 大到小非 0 小到大 =RANK(E2,E$2:E$10,0) 28 14

15 統計函數 -MEDIAN 函數定義 : 傳回引數串列內的中位數中位數為一組數字的中間數字 ; 即一半數字的值大於中位數, 而另一半數字的值小於中位數語法 :MEDIAN(number1,number2,...) Number1, number2,... 是 1 到 30 個數字, 您需要找出這些數字的中位數 =MEDIAN(E2:E10) 29 統計函數 -STDEV 函數定義 : 根據一組樣本估計其標準差標準差主要是用以衡量觀測資料與平均數之間的差異量數語法 :STDEV(number1,number2,...) Number1, number2,... 是對應於某母群體抽樣樣本的一到三十個數字引數 STDEV 函數假設它的引數是某母群體的抽樣樣本如果您的觀測資料代表整個母群體, 則應該使用 STDEVP 函數來計算標準差 = STDEV(E2:E10) 30 15

16 統計函數 -VAR 函數定義 : 估計樣本的變異數語法 :VAR(number1,number2,...) Number1, number2,... 是對應於某母群體抽樣樣本的一到三十個數字引數註解 : VAR 函數假設它的引數串列為母群體的抽樣樣本, 如果您的觀測資料代表整個母群體, 則使用 VARP 來計算變異數變異數 = 標準差 2 = N 2 =sqrt(var(e2:e10)) ( X i µ ) = STDEV(e2:e10) σ 2 i = 1 = N 31 檢視與參照 ( 尋找與參照 ) 函數 -CHOOSE 函數定義 : 使用 index_num 自引數清單中傳回相對應的引數數值, 引數的個數可由 1 到 29 個語法 : CHOOSE(index_num,value1,value2,...) Index_num 是用以指定要選取第幾個引數值的數值如果 index_num 之值為 1, 則 CHOOSE 函數會傳回 value1; 如果其值為 2,CHOOSE 函數會傳回 value2; 依此類推先用 WEEKDAY 函數得到星期代碼但是我們想讓它顯示得正式一點, 利用 CHOOSE 函數將 B 欄的值用星期幾顯示出來 =CHOOSE(A14,"mon","tue","wed","thu","fri") 32 16

17 檢視與參照函數 -VLOOKUP 函數定義 : 在一陣列或表格的最左欄中尋找含有某特定值的欄位, 再傳回同一列中某一指定儲存格中的值語法 : VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup) Lookup_value 是您打算在陣列的最左欄中搜尋的值 Lookup_value 可以是數值參照位址或文字字串 Table_array 是要在其中搜尋的資料表格通常是儲存格範圍的參照位址或類似資料庫或清單的範圍名稱 Table_array 第一欄裡的值, 可以是文字數字或邏輯值字母的大小寫被視為是相同的 33 檢視與參照函數 -VLOOKUP 函數 Col_index_num 是個數值, 代表所要傳回的值位於 table_array 中的第幾欄如果 col_index_num 引數值為 1, 傳回在 table_array 第一欄的值, 如果 col_index_num 引數值為 2, 傳回 table_array 第二欄的值, 依此類推 Range_lookup 是個邏輯值, 用來指定 VLOOKUP 之 Table_array 要先排序與否, 或找尋之值為完全吻合或部分符合 (0 表示要完全吻合 ) =VLOOKUP(4,A2:J10,4) HLOOKUP 函數跟 VLOOKUP 用法大同小異, 只是它是參考橫向資料範圍 =HLOOKUP(80,A2:J10,4)?? 34 17

18 檢視與參照函數 -VLOOKUP 函數 =IF(ISNA(VLOOKUP(A5, 客戶資料檔!$A$1:$B$6,2,0))," 沒找到 ",VLOOKUP(A5, 客戶資料檔!$A$1:$B$6,2,0)) 35 檢視與參照函數 -TRANSPOSE 函數定義 : 將儲存格之垂直範圍以水平範圍的格式傳回, 反之亦可 TRANSPOSE 必須是個有欄和列的陣列分別以有相同數目的欄和列範圍的陣列公式輸入使用 TRANSPOSE 來移動工作表上陣列的垂直和水平方向語法 :TRANSPOSE(array) Array 是工作表或巨集表中您所要轉置的矩陣或儲存格範圍轉置矩陣的建立是以陣列的第一列作為新陣列的第一欄, 而陣列的第 2 列則為新陣列的第 2 欄, 依此類推 Step1: 選取欲轉置目的儲存格範圍, 並按 [ 插入函數 ] 36 18

19 檢視與參照函數 -TRANSPOSE 函數 Step2: 開啟 [ 插入函數 ] 交談窗, 選取類別檢視與參照, 並選取 TRANSPOSE 函數 Step3: 按 [Array] 之摺疊鈕 Step4: 選取原始儲存格範圍表示我們想轉置這個範圍的資料, 並按摺疊鈕回來 Step5: 並確定鈕回來 Step6: 在資料編輯列按一下, 並按 Ctrl+Shift+Enter Step7: 看到結果! 先選定目的範圍為 9 列 10 欄於目的地左上角輸入 =TRANSPOSE(A1:J10) 再按 Ctrl+shift+enter 37 檢視與參照函數 - INDEX 函數 INDEX 函數會在陣列中找到指定欄列交會處的儲存格內容其公式如下 : 38 19

20 INDEX 函數實例應用假設想要在星座圖中查詢男女雙方的速配程度, 就可以利用 INDEX 函數來找到結果 : 39 檢視與參照函數 - MATCH 函數 MATCH 函數是用來比對一陣列中內容相符的儲存格位置其函數格式為 : 當 Match_type 設為 0 時, 表示陣列內容不用排序直接找到完全相符的值 ; 若設為 1 或省略, 表示陣列內容會先遞增排序, 再找等於或僅次於 Lookup_value 的值 ; 若設為 -1, 則表示陣列內容會先遞減排序, 再找等於或大於 Lookup_value 的最小值 40 20

21 MATCH 函數實例應用 41 MATCH 函數實例應用 2. 接著將插入點移至 B11, 輸入公式 =MATCH (A11, A1:H1, 0): 42 21

22 MATCH 函數實例應用 3. 最後再將插入點移至 B12, 輸入公式 =INDEX (A1:H7, B10, B11): 43 財務函數 -PV 函數定義 : 傳回某項投資的年金現值年金現值為未來各期年金現值的總和語法 :PV(rate, nper, pmt, fv, type) =pv(5%,5,10000) Rate 為各期的利率 nper 為年金的總付款期數 pmt 為各期所應給付 ( 或所能取得 ) 的固定金額 fv 為最後一次付款完成後, 所能獲得的現金餘額 ( 年金終值 ) 如果省略 fv 引數, 會自動假定為 0 ( 例如貸款的年金終值是 0 ) Type 為 0 或 1 的數值, 用以界定各期金額的給付時點 0 或省略代表期末付款,1 代表期初付款範例 1 : 某銀行為推銷某種基金, 年利率 4.5%, 請你預繳 45,000,5 年內每年領 10,000 元, 我們想計算一下現值, 看是否值得投資輸入 = PV(4.5%,5,10000), 這 5 年給我的錢, 換成現在只有 43,899.77, 比我投資的錢還多, 當然不值得投資 44 22

23 財務函數範例 2 : 某保險公司為推銷某種保險, 年利率 3%, 請你預繳每年 67,140 元, 20 年後每年領 45,666 元, 我們想計算一下現值, 看是否值得投資? 只將 67,140 放定存 20 年, 利率年息為 3%: =67140*(1+3%)^20= 總共財務函數貸款每月還款 -PMT 函數定義 : 固定利率與固定期數下, 傳回每期付款金額語法 :PMT(rate,nper,pv,fv,type) 有關 PMT 中引數的完整說明, 請參閱 PV 函數 Rate 為各期的利率,Nper 為年金的總付款期數 Pv 為未來各期年金現值的總和 Fv 為最後一次付款完成後, 所能獲得的現金餘額 ( 年金終值 ) 如果省略 fv 引數, 會自動假定為 0, 也就是說, 貸款的年金終值是 0 範例 1 : 假設 A 同學想申請助學貸款, 年利率 7.8%, 可借 50,000 元, 期限為 5 年, 看看每月必須負擔多少貸款輸入 = PMT(7.8%/12,5*12,50000), 一個月只要付 1,009 元範例 2 : 假設 A 先生想貸款買房子, 年利率 3.8%, 可借 2,000,000 元, 期限為 20 年, 看看每月必須負擔多少貸款輸入 = PMT(3.8%/12,20*12, ) 46 23

24 財務函數零存整付 -FV 函數語法 :FV(rate,nper,pmt,pv,type) FV 計算零存整付存款之本利和 FV( 月利率, 期數 ( 年 )*12, 每月存款金額 ) v.s *(1+1.5%/12)^1 累積 24 次每月存款金額期數 ( 年 ) 年利率年金終值 % =FV(C2/12,B2*12,A2) 47 財務函數存款 / 預借現金利率 - RATE 函數 RATE 函數可以幫我們計算借了一筆錢, 在固定期數每期要償還固定金額下, 算出其利率為何 RATE 函數的格式為 : 格式 Rate(Nper,pmt,pv,fv,type) Nper 為付款的總期數 Pmt 為各期所應給付的固定金額 Pv 為未來各期年金現值的總合 Fv 為最後一次付款後, 所能獲得的現金餘額 Type 為一邏輯值, 當為 1 時, 代表每期期初付款 ; 當為 0 時, 代表每期期末付款 Rate( 期數, 金額, - 最後一次現金餘額, - 年金現值的總合 ) 48 24

25 RATE 函數實例應用 1 假設古堡銀行推出全新的百萬儲蓄計劃, 強調每月只要儲蓄 7,500 元, 10 年後保證領回 100 萬元, 那到底這個百萬儲蓄計劃的年利率是多少呢? 帶入函數計算的結果, 比目前銀行定存約 2% 的利率還要高一些每月存款金額期數 ( 年 ) 年利率年金終值 =rate(a3,a2*12,,-a5) RATE 函數實例應用 2 假設古堡銀行提出個人小額信用貸款方案, 借款 30 萬, 每月只要還款 16000, 2 年即可還清帶入函數得知, 和信用卡循環利息一樣高耶, 還是划不來每月存款金額期數 ( 年 ) 年利率預借現金 =rate(a3,a2*12,-a5)

26 財務函數算存款期數 - NPER 函數 NPER 函數是指每期投入相同金額, 在固定利率的情形下, 計算欲達到某一投資金額的期數 NPER 函數的格式為 : Rate 為各期的利率 Pmt 為各期所應給付的固定金額 Pv 為未來各期年金現值的總合 Fv 為最後一次付款後, 所能獲得的現金餘額 Type 為一邏輯值, 當為 1 時, 代表每期期初付款 ; 當為 0 時, 代表每期期末付款 51 實例應用小風想買一間需頭期款 60 萬元的小套房, 目前小風每個月可以存 17,000 元, 而定存年利率為 2.05%, 小風需要存多久才能存夠小套房的頭期款呢? 帶入函數計算結果, 表示小風只要存 35 個月就可湊足小套房的頭期款了 52 26

27 財務函數 - IRR 函數 IRR 函數可以用來計算某一連續期間的內部報酬率其中要注意的是 : 投入資金必須以負值表示, IRR 才可以計算 IRR 函數的格式為 : Values 要計算報酬率的現金流量數值 Guess 預測利率, 若不填則以 10% 為預設值來計算投入資金第 1 年第 2 年第 3 年第 4 年第 5 年第 6 年內部投資報酬率 % 53 折舊函數計算折舊的方法有很多種, 通常會依公司習慣的方式來提列由於使用不同的折舊函數, 所需用到的參數亦有些許差異, 我們先介紹共通的部份 : Cost 採購設備或資產所花費的成本 Salvage 殘值, 亦即此設備或資產過了耐用年限時可回收的價值 Life 耐用年限, 亦即此設備或資產的可用年限或生產數量 54 27

28 財務函數 -SLN 函數茹葳公司採購一生財設備花了 60 萬元, 預估可以使用 5 年, 殘值餘 4,500 元若以直線法來攤為費用, 則可使用直線法折舊函數 SLN, 其格式如下 : 55 財務函數 -SLN 函數將插入移至 B4 儲存格, 接著輸入公式 =SLN ($B$1, $D$1, $F$1) 56 28

29 財務函數直線折舊額 -SLN 函數直線折舊額 =( 成本 - 殘值 )/ 使用年限 57 財務函數 -SYD 函數若茹葳公司想要以年數合計法 (SYD) 來計算每年需攤提的費用, 則可改用 SYD 函數來計算其格式如下 : 58 29

30 財務函數 -SYD 函數 59 財務函數直線折舊額 -SYD 函數年數合計法每期折舊額 =( 成本 - 殘值 )* 年數之倒數 / 年數之合計 60 30

31 財務函數 -DB 函數承上例, 若茹葳公司想要以定率遞減法 (DB) 來計算每年需攤提的費用, 則須採用 DB 函數, 其格式為 : 61 財務函數 -DB 函數在 B5 拉曳填滿控點至 F5, 即可求得定率遞減法各年度的折舊費用這是初期折舊的費用較高, 然後逐年遞減的一種加速折舊法 62 31

32 財務函數直線折舊額 -DB 函數定率遞減法 = 固定資產之帳面價值 * 折舊率 DB(cost, salvage, life, period, month) 63 財務函數 -DDB 函數若茹葳公司想要以倍率遞減法 (DDB) 來計算每年需攤提的費用, 則可使用 DDB 函數, 其格式如下 : 64 32

33 財務函數 -DDB 函數 65 財務函數直線折舊額 -DDB 函數倍數餘額遞減法 = 直線法折舊率的兩倍, 不考量殘值 DDB(cost, salvage, life, period, factor) 66 33

34 HomeWork 文字函數包括 Left Right 等函數邏輯函數包括 And Or Not 及 If 函數日期與時間函數包括 Today Date Time Year Month Day Dateif Weekday 函數數學函數包括 Int Rand Countif Sumif 函數統計函數包括 Counta Frequency Rank Median Stdev Var 函數檢視與參照函數包括 Choose Vlookup Hlookup Transpose 函數財務函數包括 Pv Pmt Fv Rate Nper Irr Sln Syd Db Ddb 函數 67 34

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