1 / 30

Excel 函數簡介和應用

Excel 函數簡介和應用. 臺師大 資訊中心 陳明芳 mfchen@ntnu.edu.tw VOIP: 7714- 8628 ( 林口 ) 2010 年 8 月 http://140.122.66.90/excel. 課程大綱. 教材的使用 - ppt 說明和 excel 練習檔案範例配合 函數簡介 Excel 函數的分類 常用函數語法和簡單範例 應用實例 上機練習. 課程目標. 了解 Excel 函數的分類和功能 使用 Excel 函數，加強資料處理的效率 使用 Excel 函數強大的功能，發揮您的工作創意 使用複合函數，可以舉一反三

rodney
Download Presentation

Excel 函數簡介和應用

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. Excel 函數簡介和應用 臺師大 資訊中心 陳明芳 mfchen@ntnu.edu.tw VOIP: 7714- 8628 (林口) 2010年8月 http://140.122.66.90/excel

  2. 課程大綱 • 教材的使用 - ppt 說明和 excel 練習檔案範例配合 • 函數簡介 • Excel函數的分類 • 常用函數語法和簡單範例 • 應用實例 • 上機練習 Excel函數簡介和應用 (Aug. 2010, mfc)

  3. 課程目標 • 了解Excel函數的分類和功能 • 使用Excel函數，加強資料處理的效率 • 使用Excel函數強大的功能，發揮您的工作創意 • 使用複合函數，可以舉一反三 • 加強邏輯思考，發揮創意 • 修行在個人 Excel函數簡介和應用 (Aug. 2010, mfc)

  4. 什麼是函數(Function)? • 維基百科: 在數學意義上，一個函數表示每個輸入值對應唯一輸出值。函數 f 中對應輸入值的輸出值 x 的標準符號為 f(x)。 • 數學: 設 x, y 是兩個變量，如果 y 的值是隨著 x 的取值，依某一種對應法則而唯一確定，那說 y 是 x 的函數，用記號y=f(x) 表示。 • 軟體程式: 電腦是幫助人們解決問題的工具，在解決問題的時候，常會出現一些相同的運算，如開平方，求三角函數等，但這些運算對電腦而言並不是一件簡單的工作，它必須執行一連串的敘述才可得到結果，為了方便使用，將這些常用的運算設計成副程式(subroutine)、或稱函數(function)，提供使用者使用。 Excel函數簡介和應用 (Aug. 2010, mfc)

  5. Excel函數分類 • Excel函數可分為12類, 共約４００個以上的函數 • 函數分類 • 數學和三角函數 FLOOR, INT, ROUND, SUM, … • 日期和時間函數 DATE, TIME, TODAY … • 統計函數 AVERAGE, COUNT, … • 邏輯函數 AND, OR, … • 文字和數據函數 LEFT, MID, RIGHT, TRIM, UPPER, LOWER, … • 查詢和檢視函數 COLUMN, ROW, LOOKUP, … • 資訊函數 CELL, INFO, TYPE … • 財務函數 PMT, RATE, … • 資料庫函數 DCOUNT, DMAX, DMIN, … • 工程函數 DEC2HEX … • 加載項與自動化函數 CALL, SQL. REQUEST, … • 多維數據集函數 CUBEKPIMEMBER, CUBEMEMBER, … Excel函數簡介和應用 (Aug. 2010, mfc)

  6. Excel函數的語法 • 函數名稱( 引數 ) ，引數個數為 0個到N 個，引數用小括號() 括起來，多個引數中間以逗號(,)分隔 • 實例說明: • TODAY() 無引數 • TRIM(A1) 一個引數 • LEFT(A1, 2) 二個引數 • SUMIF(C2:C9, “>60”, D2:D9) 三個引數 • Concatenate(A1, B1, C1, D1, E1, F1, …) 多個引數 • 有些函數, 可以支援不同個數的引數 • SUMIF(C2:C9, “>60”) • SUMIF(C2:C9, “>60”, D2:D9) • “引數”有些書叫做”參數” (Argument或Parameter) Excel函數簡介和應用 (Aug. 2010, mfc)

  7. Excel函數使用(一)-介面輸入 1. [插入函數] 5. 選取引數(儲存格) (引數數量依函數語法而定) (可用介面選取或直接輸入) 2. 選一下分類 3. 選取函數 4. [確定] 6. [確定] Excel函數簡介和應用 (Aug. 2010, mfc)

  8. Excel函數使用(二)-手動輸入 • 在函數輸入編輯區，先輸入 = 號，再輸入函數、引數，引數應放在 ( ) 小括號內，語法要正確，在=前不可有空白字元 • 例如: 先選取要使用函數的儲存格 2.先輸入= 號, 再輸入函數和引數 輸完可按一下綠色的 ˇ 驗證是否正確 1. 選取要使用函數的儲存格 Excel函數簡介和應用 (Aug. 2010, mfc)

  9. Excel函數的種類 • 簡單函數 • 函數名稱( ) – 不需要引數，如: Today(), Now(), Rand(), PI(), NA(), True(), False() • 函數名稱(引數1, 引數2, …) • 複合函數 • 定義: 函數的引數包含一個或多個函數 • 函數A名稱(引數A1, 引數A2, 函數B(引數B1)) Excel函數簡介和應用 (Aug. 2010, mfc)

  10. 常用的符號 • 數學符號 • 加(+) 減(-) 乘(*) 除(/) • 例: A2 + B2, A2 – B2, A2 * B2, A2 / C2 • 百分比(%), • 指數或次方(^), 例: E3 ^ 3 儲存格E3值的三次方 (E3 * E3 * E3) • 比較符號 • 等於(=), 大於(>), 小於(<), 大於等於(>=), 小於等於(<=), 不等於(<>) • 文字連結符號 • & • 文字字串的表示，用 “ “ 括起來，例: “陳阿明” • 例如: D3 & E3 ，A1 & “先生” Excel函數簡介和應用 (Aug. 2010, mfc)

  11. Excel儲存格的相對和絕對位置 • 一般在Excel中，使用函數或公式時，常會指定儲存格的「相對位置」或「絕對位置」，Excel 在使用儲存格時預設是相對位置 • 絕對位置只要在儲存格代號前面加上「$ 」，Excel的儲存格都是先「行」Column (英文字母)再「列」 Row(數字), 例如： • A1，表示「行」為A，「列」為 1 所代表的位置 • A$1 表示「行A」為相對位置，但「列1」為絕對位置 • $A$1 表示「行A」和「列1」都是絕對位置 • 預設的相對位置，在使用複制函數或公式時，會自動使用相對的儲存格 (Demo: 流水編號，和函數複制操作) Excel函數簡介和應用 (Aug. 2010, mfc)

  12. Excel儲存格的資料格式設定 • 不同資料應使用不同資料格式, 資料庫中必要的規定 • 設定正確的資料格式，有助於資料輸入的正確性，Excel會依格式做適當的驗證，例如：小數點位數, 日期輸入的格式…等, Excel在運算時才做較嚴謹的驗證 • 常用格式: • 通用(G): 預設 • 數值: 12.34 • 文字: “Mike Jordon” • 日期: “2010/8/17”,”2010-8-17” • 百分比: 23.5% Excel函數簡介和應用 (Aug. 2010, mfc)

  13. 常用數學和運算函數 • 一般數學運算(34個) • INT(數值) – 比數值小最接近的整數 • RAND() – 0 ~ 1 間的隨機亂數 • RANDBETWEEN(下限值, 上限值) – 兩數間的「整數」隨機亂數 • ROUND(數值, 小數位數) – 指定小數位數, 將數值四拾五入 • ROUND(123.456, 2)  123.46, Round(123.456, 0)  123 • ROUNDDOWN 無條件捨去, ROUNDUP 無條件進位 • SUM(數值1, 數值2, …) – 加總和, 引數一般使用儲存格的範圍，例: A3:A12 • SUMIF(範圍, 條件, 加總範圍) ，若加總範圍省略，則用範圍加總 • SUMIF(A2:A9, “>60”), SUMIF(A2:A9, “>60”, B2:B9) • TRUNC(數值, 小數位數), 和Rounddown同，但引數二可略，Rounddown的引數二不行省略 • 三角函數(15個) • 排列組合(3個) Excel函數簡介和應用 (Aug. 2010, mfc)

  14. 數學和運算函數應用和練習 • 練習題: 計算學生個人平均成績到小數二位, 四捨五入 • ROUND() • 思考題: 計算男生和女生的總分 • SUMIF() • 休息輕鬆一下下 電腦使用教學 Excel函數簡介和應用 (Aug. 2010, mfc)

  15. 常用日期和時間函數 • 共有21個函數 • DATEDIF(開始日期, 結束日期, “傳回代碼”) • 代碼: 年(Y),月(M),日(D) • 年YEAR(),月MONTH(),日DAY()(該月第幾天), HOUR(0~23),分MINUTE(), 秒SECOND() • 引數用日期和時間合法的序列值 • YEAR(“2010/05/30”), MONTH(“1-Nov-99”), • TODAY()-目前電腦之日期 • NOW()-目前電腦之日期和時間 • WEEKDAY(日期值, 型態) – 禮拜幾 • 型態代表禮拜幾的代號: 1 (1日, 7六), 2(1一, 7日), 3(0一, 6日) Excel函數簡介和應用 (Aug. 2010, mfc)

  16. 日期和時間函數應用和練習 • 練習題: 年月日資料的分割(分開儲存格存放) • 思考題: 計算你的退休日子 – 數饅頭 • 假設退休日已知: 例: “2015-8-1” • 休息一下下… 平常要對滑鼠温柔一點哦..否則… Excel函數簡介和應用 (Aug. 2010, mfc)

  17. 簡單的統計函數 • 分類：敍述統計(34個)、機率分配(31個)、迴歸分析(14個) • AVERAGE(數值1, 數值2, …)-平均值，非數值引數被略過，母數變小 • AVERAGEA() - 非數字引數以零分計，母數變大 • COUNT(數值1, 數值2, …)-計數，只含數值, 文字數字, 日期，不含空白, 邏輯值, 文字或錯誤值 • COUNTA (數值1, 數值2, …)-全部計數，空白除外 • COUNTIF(範圍, 條件)-計算「範圍｣內符合「條件｣的數量 • MAX (數值1, 數值2, …)-最大值, MIN (數值1, 數值2, …)-最小值 • MEDIAN (數值1, 數值2, …)-中位數, 一組數字的中間數字 • MODE (數值1, 數值2, …)-眾數, 一組數字出現次數最多的數值 • RANK (數值, 參考陣列, 指定順序)-某數字在一組數字中的等級 • 指定順序 - 由小到大(1), 由大到小(0或省略) • RANK(A1, A1:A9, 1) Excel函數簡介和應用 (Aug. 2010, mfc)

  18. 統計函數應用和練習 • 學生平均分數計算, 要不要包含缺考? • 及格和不及格人數計算 • 思考題: 班級排名 • 等級數值包含在參考陣列之中 • 提示: 參考陣列固定(絕對位置) • 休息一下下…大陸餐廳… Excel函數簡介和應用 (Aug. 2010, mfc)

  19. 資訊和邏輯函數 • 工作表資訊 • CELL(資訊型態, 參照或範圍)-儲存格之相關資訊 • COUNTBLANK(範圍)-計算「範圍｣中空白的儲存格個數 • INFO(資訊型態)-作業環相關資訊 • IS….ISBLANK(參照)-空白, ISNUMBER(參照)-數字, ISTEXT(參照)-文字 • TYPE(參照)-傳回資料型態代碼: 數字(1), 文字(2), 邏輯(4), 公式(8), 錯誤值(16), 陣列(64) • 邏輯判斷 • AND(判斷1, 判斷2, …)-判斷「全部都｣成立，傳回True，否則為False • OR (判斷1, 判斷2, …)-判斷「只要一個｣成立，傳回True，全部都不成立，才傳回False • NOT(邏輯值)- True和False對調 • IF(判斷式，判斷式成立的作業，判斷式不成立的作業） • IF(A2 >= 60, “及格”, “當掉”) Excel函數簡介和應用 (Aug. 2010, mfc)

  20. 常用文字函數(一) • 「資料正確輸入｣是資訊系統維持「正常運作｣的必要條件 • 例如: 資訊系統的日格式可能是 YYYYMMDD, YYYY/M/D, YYYY-MM-DD, 或 DD-MM-YY … • 中文文字處理的問題 • 全型和半型的使用(數字和英文字母) • 文字長度計算: 全型長度可以是1或2，視使用的函數而定，函數名稱後有加”B”的函數，中文全型字長度視為2(含全型的數字和英文字母)，例如: 計算文字長度的LEN() 和 LENB() 函數 • LEN(“陳阿明”) 長度為 3 • LENB(“陳阿明”) 長度為 6 • 空白字(鍵)的誤用，是資訊系統中資料處理永遠的痛 • 不小心按到(不自知)，一個空白／二個空白，半型空白／全型空白 Excel函數簡介和應用 (Aug. 2010, mfc)

  21. 常用文字函數(二) • 文字函數有35個 • ASC(文字)-全型英數字改半型, BIG5(文字)-半型改全型 • CONCATENATE(文字1, 文字2, …)-串接所有文字，和 & 符號相同功能 • DOLLAR(數字, 小數)-貨幣格式，依指定小數位數轉成文字 • DOLLAR(123.456, 2)  $123.46 (四捨五入) • EXACT(文字1, 文字2)-比較兩個文字是否相等 • FIND(搜尋字串, 原始字串, 起始位置)-在原始字串中找到搜尋字串的位置, 起始位置省略則預設為1，大小寫不同，FINDB(…) • LEFT(文字, 長度)-依指定長度取文字之左側字元，LEFTB(…) • LEN (文字)-文字長度(個數), LENB(…) • MID(文字, 起始位置, 長度)-依指定起始位置取指定長度之字元，MIDB(…) • REPLACE(舊字串, 起始位置, 長度, 新字串)-將舊字串依指定位置用新串取代, 舊/新字串長度可以不同, REPLACEB(…) • REPT(文字, 顯示次數)-文字重複顯示次數 • RIGHT (文字, 長度)-依指定長度取文字之右側字元，RIGHTB(…) • SEARCH(…)-和FIND類似，但大小寫不區分, SEARCHB(…) • TEXT(數值, 格式)-將數值轉換成指定的格式 • VALUE(文字)-將文字轉換成數值 • TRIM(文字)-將文字中多餘的空白移除，中間保留一個空白 • LOWER(文字)-將文字變小寫, UPPER(文字)-將文字變大寫 Excel函數簡介和應用 (Aug. 2010, mfc)

  22. 文字函數應用和練習 • 姓和名的分割, 或合併 • 生日年月日的分割, 或合併 • 找尋符合條件的資料 • 中文或空白資料或大小寫資料的處理 • 思考題: 日期格式固定長度為 YYYYMMDD 八碼 • 「月｣和「日｣小於10時如何變兩位數? • 休息一下下…. 網路笑話 Excel函數簡介和應用 (Aug. 2010, mfc)

  23. 常用查詢和檢視函數 • 參照函數(8個) • COLUMN(參照)-傳回參照的欄號 • ROW(參照)-傳回參照的列號 • 檢視函數(8個) • HLOOKUP(查詢值, 查詢陣列, 指定列數, 選項) - 水平(列)查詢，選項可略，預設為True指找到部份符合，False則是完全符合，找不到傳回錯誤值#N/A • 例: HLookUp(“張三”, A2:F9, 2) ，2 表示陣列A2:F9的第二列 • VLOOKUP(查詢值, 查詢陣列, 指定列數, 選項) - 垂直(行)查詢 • HYPERLINK(連結目標，顯示名稱)-建立超連結，目標可以是合法的URL, 如網址, 檔案, Email 信箱… • HYPERLINK(“http://ww.ntnu.edu.tw/”, “師大首頁”) Excel函數簡介和應用 (Aug. 2010, mfc)

  24. HLookUp v.s VLookUp Excel函數簡介和應用 (Aug. 2010, mfc)

  25. VLookUp 的搜尋比對方式 查詢值 A2~A7 查詢陣列 $E$2:$G$10 第一欄 第二欄 第三欄 Excel函數簡介和應用 (Aug. 2010, mfc)

  26. 查詢和檢視函數應用和練習 • 大海撈針，資料比對 • 僑先部申請入學人數2292人，正式報到人數1453人，請找出未報到的人 • 函數研究: • =IF(ISNA(VLOOKUP(C2270,$M$4:$M$1459,1,FALSE)) = TRUE, "X", VLOOKUP(C2270,$M$4:$M$1459,1,FALSE)) • ISNA(…) – 是否為 #N/A 的錯誤 (沒找到) • FALSE – 表示找到完全符合的資料 • 語法的意思: 如果找尋結果是 #N/A (表示沒找到), 則顯示 “X”, 否則(找到完全符合)顯示找到的資料 • 可能的問題: 同名不同人 … 第二條件找尋或人工… Excel函數簡介和應用 (Aug. 2010, mfc)

  27. 函數或公式資料的複制 • 函數或公式儲存格資料一般多為”相對位置”的資料產生 • 複制函數或公式的值操作: • 選取函數或公式的儲存格 • 移到要貼上的儲存格 • 按右鍵 • 選擇[選擇性貼上] • 在介面上選取[值]和[無]運算 • 如右圖 Excel函數簡介和應用 (Aug. 2010, mfc)

  28. 實務應用的加強 • 函數使用的熟練 • 參考書/Excel函數字典, 多練習 • 複合函數的使用 • DATEDIF (Today(), “2020-8-1”, “D”) • 邏輯運算的加強 • AND(..), OR(…), NOT(…) • IF( …) • SUMIF(…), SUMIFS(…) • COUNTIF(…), COUNTIFS(…) • AVERAGEIF(…), AVERAGEIFS(…) Excel函數簡介和應用 (Aug. 2010, mfc)

  29. 進階學習目標 • 特殊需求使用者 • 統計函數、財務函數、工程函數… • 跨工作表的運算和應用 • 例如: 存取工作表 sheet1 儲存格A1 資料  sheet1!A1 • 資料庫的使用 • 資料庫函數 • 和Office家族 Access 資料庫的應用(外部資料庫連結) • 使用巨集和撰寫VBA程式 - 自動化的應用 • 自訂函數和VBA • 需要程式設計的能力 – Visual Basic Script Excel函數簡介和應用 (Aug. 2010, mfc)

  30. 結語 • Office為何使用者那麼多? • Office功能那麼多，你用到多少? • 先知能做什麼，才能發揮創意，提升Office生產力 • 多練習，熟能生巧 • 參考書: • Excel函數範例, 科海, 陳偉忠/林宏諭 編著 • Excel 2007函數公式查詢與應用寶典, 機械工業(大陸), 張軍翔 編著 • Q&A • 感謝參與，請填寫問卷，寫下你的電腦課程訓練需求 Excel函數簡介和應用 (Aug. 2010, mfc)

More Related