Excel 函數簡介和應用 臺師大 資訊中心 陳明芳 VOIP: (林口)

Presentation on theme: "Excel 函數簡介和應用 臺師大 資訊中心 陳明芳 VOIP: (林口)"— Presentation transcript:

1 Excel 函數簡介和應用 臺師大 資訊中心 陳明芳 mfchen@ntnu.edu.tw VOIP: 7714- 8628 (林口)
2010年8月

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. [確定] Excel函數簡介和應用 (Aug. 2010, mfc) 6. [確定]

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”,” ” 百分比: 23.5% Excel函數簡介和應用 (Aug. 2010, mfc)

13 常用數學和運算函數 一般數學運算(34個) 三角函數(15個) 排列組合(3個) INT(數值) – 比數值小最接近的整數
RAND() – 0 ~ 1 間的隨機亂數 RANDBETWEEN(下限值, 上限值) – 兩數間的「整數」隨機亂數 ROUND(數值, 小數位數) – 指定小數位數, 將數值四拾五入 ROUND( , 2)  , Round( , 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 日期和時間函數應用和練習 練習題: 年月日資料的分割(分開儲存格存放) 思考題: 計算你的退休日子 – 數饅頭
假設退休日已知: 例: “ ” 休息一下下… 平常要對滑鼠温柔一點哦..否則… 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( , 2)  $ (四捨五入) 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個) 檢視函數(8個) COLUMN(參照)-傳回參照的欄號 ROW(參照)-傳回參照的列號
HLOOKUP(查詢值, 查詢陣列, 指定列數, 選項) - 水平(列)查詢，選項可略，預設為True指找到部份符合，False則是完全符合，找不到傳回錯誤值#N/A 例: HLookUp(“張三”, A2:F9, 2) ，2 表示陣列A2:F9的第二列 VLOOKUP(查詢值, 查詢陣列, 指定列數, 選項) - 垂直(行)查詢 HYPERLINK(連結目標，顯示名稱)-建立超連結，目標可以是合法的URL, 如網址, 檔案, 信箱… HYPERLINK(“ “師大首頁”) 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(), “ ”, “D”) 邏輯運算的加強 AND(..), OR(…), NOT(…) IF( …) SUMIF(…), SUMIFS(…) COUNTIF(…), COUNTIFS(…) AVERAGEIF(…), AVERAGEIFS(…) Excel函數簡介和應用 (Aug. 2010, mfc)

29 進階學習目標 特殊需求使用者 跨工作表的運算和應用 資料庫的使用 使用巨集和撰寫VBA程式 - 自動化的應用 統計函數、財務函數、工程函數…
例如: 存取工作表 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)