1 / 103

公式的進階應用

公式的進階應用. 1. 實作範例一 & 實作範例二. 2. 3. 實作範例五 & 巨集介紹. 4. Excel 2010 進階實務. 實作範例三 & 實作範例四. 公式的進階. 常數、參照位址、函數、範圍名稱. 公式的元素. 常數：數字、日期時間、文字常數等 參照位址：取位址所示之儲存格內容 函數：數學、文字、統計、日期時間、財務、邏輯、檢視參照 … 範圍名稱：取該名稱所代表之範圍. 參照位址. 相對參照 如： D6 。複製時，將隨儲存格而改變其相對位置 絕對參照 如： $D$6 。複製時，並不隨儲存格而改變其位置

carlota-ramos
Download Presentation

公式的進階應用

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 公式的進階應用 1 實作範例一&實作範例二 2 3 實作範例五&巨集介紹 4 Excel 2010進階實務 實作範例三&實作範例四

  2. 公式的進階 常數、參照位址、函數、範圍名稱

  3. 公式的元素 • 常數：數字、日期時間、文字常數等 • 參照位址：取位址所示之儲存格內容 • 函數：數學、文字、統計、日期時間、財務、邏輯、檢視參照… • 範圍名稱：取該名稱所代表之範圍

  4. 參照位址 • 相對參照如：D6。複製時，將隨儲存格而改變其相對位置 • 絕對參照如：$D$6。複製時，並不隨儲存格而改變其位置 • 混合參照如：D$6。有 $ 絕對符號之部份，將不隨儲存格而改變其位置；而無絕對符號者，則仍將隨儲存格而改變其相對位置

  5. 範圍名稱 • 須使用到儲存格範圍之指令及函數甚多，若每次均以位址進行標定範圍，總覺得不易記憶 • 可直接鍵入範圍名稱，對於經常忘記儲存格範圍者，非常方便

  6. 數學與三角函數 • INT(數值): 取整數 • MOD(被除數,除數): 取餘數 • ROUND(數值,小數位數): 四捨五入 • RANDBETWEEN(下限,上限): 取亂數 • SUMIF(準則範圍,條件準則,加總範圍): 依條件算加總

  7. 文字函數 • LEFT(文字串,字數): 左子字串 • RIGHT(文字串,字數): 右子字串 • MID(文字串,第幾個字開始,字數): 中間子字串 • FIND(欲找尋的字串,文字串,第幾個字開始): 找到的位置 • SUBSTITUTE(字串,舊子字串,新子字串) • &連結字串

  8. 邏輯函數 • IF(條件判斷式,成立時傳回值,不成立時傳回值): 判斷條件 • AND(第一判斷式,第二判斷式,…): 條件全部成立 • OR(第一判斷式,第二判斷式,…): 條件之一成立

  9. 函數範例一 • 假定，國際電話以6秒為1單位，每單位0.36元，未滿1單位者仍以1單位計

  10. 統計函數 • COUNT(範圍): 求筆數 • COUNTIF(準則範圍,條件準則):依條件算筆數 • SUMIF(準則範圍,條件準則,加總範圍):依條件算加總 • RANK(數值,範圍,順序) 順序是用來指定排等級順序之方式，為0或省略，表要遞減排等級。反之，若不是0，則表要遞增排等級

  11. 函數範例二 • 將SUMIF()除以COUNTIF()來求算分組平均

  12. 日期時間函數 • NOW(): 目前日期與時間 • TODAY(): 目前的日期 • YEAR(日期):日期的年份 • MONTH(日期):日期的月份 • DATEDIF(開始日期,結束日期,單位): 計算日期間隔 • DATEVALUE(日期字串) :字串轉日期型態

  13. 函數範例三 • 求實際年資或年齡應為幾年？幾月？又幾日？

  14. 民國日期轉西元 • =DATEVALUE(1911+MID(A1,3,2)&SUBSTITUTE(MID(A1,5,8),".","/"))

  15. 民國日期轉西元進階版 • =DATEVALUE(1911+MID(A4,3,(FIND(".",A4,1)-3))&SUBSTITUTE( MID(A4,FIND(".",A4,1),8),".","/"))

  16. 西元日期轉民國

  17. 財務函數(折舊函數) • SLN(成本,殘值,可用年限):直線法第一年可乘上月數/12 • DB(成本,殘值,可用年限,要計算的時間,第一年的月份數default為12):定率遞減法 • DDB(成本,殘值,可用年限,要計算的時間, 遞減的倍數default為2):倍率遞減法 • SYD(成本,殘值,可用年限,要計算的期間單位和可用年限相同):年數合計法

  18. 檢視與參照函數 • VLOOKUP(查表依據,表格,第幾欄,是否不用找到完全相同值) 最後一項參數為TRUE或省略時，會找出僅次於查表依據的值。為FALSE時，必須找尋完全符合的值，如果找不到，則傳回錯誤值#N/A • INDEX(陣列,第幾列,第幾欄) 可於陣列中找出指定之欄與列交會處之儲存格內容

  19. 函數範例四 • VLOOKUP()必須找到完全相同值

  20. 函數範例五 • VLOOKUP()找出僅次於查表依據的值

  21. 函數範例六 • 找出指定之列與欄交會處之儲存格內容

  22. 實作範例一 員工考核成績處理

  23. 求算各科分數加總及各科平均

  24. 設定格式化條件

  25. 編輯規則

  26. 清除規則

  27. 判斷是否符合口試資格

  28. 算出符合口試資格人數

  29. 篩選符合口試資格者

  30. 計算總成績並排名次

  31. 格式化為表格

  32. 建立個人成績查詢

  33. 不符口試資格者無口試成績

  34. 實作範例二 年度預算報表製作

  35. 建立部門和科目名稱

  36. 設定資料驗證清單

  37. 設定來源為部門和科目名稱

  38. 建立科目代號查表公式

  39. 修飾科目代號查表公式

  40. 建立各科預算合計

  41. 複製多行驗證和公式設定

  42. 建立年度和各月份預算總額

  43. 開始編列年度預算

  44. 建立部門別年度預算總額

  45. 建立科目別年度預算總額

  46. 實作範例三 市場調查分析

  47. 繪製甘特圖

  48. 選好範圍插入立體堆疊橫條圖

  49. 刪除結束日期數列

  50. 取消圖例

More Related