資料庫

Excel 函數 資料分析與圖表 陳嘯虎 說明例題 : 亂數表 九九乘法表 對數表 三角函數值表 座位表 成績單 資料分析 樞紐分析 圖表製作 統計抽樣 迴歸分析等 一. 位址 在使用 Excel 時, 在同欄或同列的儲存格常具有相同性質的計算特性, 所以不需 要一一為每個儲存格設定計算公式, 較有效率的方法是先建立一個公式, 再複製 到其它儲存格之中, 與數學上的遞迴概念相同 1. 位址分為以下兩種類型 : 項目 符號 說明 範例 (1). 相對位址 複製過程中會以相對的位置改變其位址 A1 (2). 絕對位址 複製過程中不會改變其位址, 常用於固定常數 $A$1,$A1,A$1 2. 儲存格位址的表達方法 : 項目 符號 意義 說明 範例 (1). 冒號 : 表示範圍 表位於兩個參照位址間的所有儲存格 A1:C5 (2). 逗號, 表示聯集 表示兩個或兩個以上的參照位址 A1:C5,E9 (3). 空格 表示交集 表示兩個或兩個以上的 A1:C5 參照位址共同的儲存格 A3:E8 3. 儲存格的選取 : 項目 操作方法 意義 (1). SHIFT+ 滑鼠拖曳 選取連續儲存格 (2). CTRL+ 滑鼠拖曳 選取不連續儲存格 (3). 滑鼠選取列號 選取列 (4). 滑鼠選取欄號 選取欄 (5). 滑鼠選取欄列交界處之方塊 選取整張工作表 (6). CTRL+ ` 顯示公式 (7). F9 重新計算公式結果 1. 選範圍 (8). 2. F2 矩陣 3. CTRL+SHIFT+ENTER 4. 特殊技巧 : 項目 操作方法 意義 (1). 工具 + 選項 + 計算 + 手動 改成 手動操作 計算 (2). 複製格式按一下或兩下 複製格式 一次 或 連續 執行 (3). 複製 + 選擇性貼上 + 值 只複製數值結果 (4). 格式 + 儲存格 + 數值 + 自訂 自訂數值或文字顯示格式 1

二. 函數 函數是一些事先定義的公式, 它們使用一些稱為引數的特定數值, 按特定的順序或結構進行計算, 函數可用於進行簡單或複雜計算, 可用函數精靈工具啟動 1. 結構 : = 函數名稱 ( 引數, 引數, ) 2. 函數名稱 : 插入 + 函數或 SHIFT+F3 3. 引數 : 引數型態可以是數值 文字 邏輯值 ( 如 TRUE 或 FALSE) 參考位址 陣列 錯誤值 ( 如 #N/A), 您指定的引數必須能產生有效的值, 引數也可以是常數 公式或其他函數 4. 引數工具提示 : 含語法與引數的工具提示會在您鍵入內建函數時出現 5. 巢狀函數 : 將某函數作為另一函數的引數使用 6. 有效的傳回值 : 當巢狀函數當作引數使用時, 它傳回的數字類型必須與引數使用的數字類型相同, 否則將顯示 #VALUE! 錯誤值 7. 嵌套層級的限制 : 公式中至多可以包含 7 個層級的巢狀函數 當函數 B 當作函數 A 的引數時, 函數 B 稱為第 2 層函數 三. 函數類別 1. 數學和三角函數 : 一般數學運算函數 三角函數 排列組合 陣列函數 2. 日期和時間 : 處理日期與時間格式的函數 3. 統計函數 : 敘述統計函數 機率分配函數 假設檢定 ( 含估計 ) 迴歸分析 4. 邏輯與資訊函數 : 工作表資訊函數 工作表判斷函數 5. 查詢與檢視函數 : 儲存格參照函數 檢視 ( 查詢 ) 函數 6. 資料庫函數 : 針對資料庫篩選的函數, 需設定篩選準則, 與統計函數的差別在於一般統計函數無法於參數中設定只針對某一特定條件下進行資料統計的運算 7. 財務函數 : 現金流量折現函數 折舊與資本預算函數 投資決策評估函數 債券評價函數 8. 文字函數 : 文字資料處理函數 9. 工程函數 : 工程演算函數 ( 需使用工具 + 增益集 + 分析工具箱 VBA 啟動 ) 四. 錯誤訊息 項目 錯誤訊息 說明 1. ##### 在儲存格中輸入的數值太長, 儲存格無法全部顯示, 此時可以拖曳欄位標題之間的邊界, 調整欄位大小 2. #DIV/0! 公式的除數為零 3. #N/A 函數或公式中有無效值 4. #NAME? 無法識別公式中的文字 5. #NULL! 指定的兩個範圍無交集 6. #NUM! 公式或函數中的號碼有問題 7. #REF! 儲存格參照無效 8. #VALUE! 使用的引數或運算元的類型錯誤, 或公式自動校正特性無法更正公式 f x 2

五. 函數指令 項目 內容 函數名稱 函數功能 INT( 數 ) 四捨五入 TRUNC( 數, 小數位數 ) 去尾 SUM( 範圍 ) 範圍內的數相加 ABS( 數 a) 絕對值 a 基 SQRT( 數 ) 正平方根 1. 本 GCD( 數 1, 數 2, ) 最大公因數運 LCM( 數 1, 數 2, ) 最小公倍數 算 PI() 圓週率 π MOD( 被除數, 除數 ) 餘數 RAND() 0 和 1 之間的亂數 RANDBETWEEN( 數 a, 數 b) a 與 b 之間的整數亂數 SUMIF( 範圍, 條件 ) 按指定準則加總範圍內的數 RANK( 數, 範圍 ) 排序 MAX( 範圍 ) 最大值 2. 數 MIN( 範圍 ) 最小值 LARGE( 範圍,k) 取出第 k 大 SMALL( 範圍,k) 取出第 k 小 指 EXP( 數 x) 自然指數的次方 e x 3. 對 POWER( 數 a, 次方 n) 某數的次方 a n 數 LOG( 真數 x, 底數 a) 指定底數的對數值 log a x DEGREES( 弧度 ) 將弧度轉換為度 4. 三 RADIANS( 度 ) 將度轉換為弧度角 SIN( 弧度 θ) 正弦函數 sinθ COS( 弧度 θ) 餘弦函數 cosθ 5. FACT(n) 數的階乘 n! 排列 n PERMUT(n,m) 排列數 P m 組合 n COMBIN(n,m) 組合數 6. 矩陣 MDETERM( 方陣 A) MMULT( 矩陣 A, 矩陣 B) MINVERSE( 方陣 A) TRANSPOSE( 矩陣 A) C m 方陣的行列式值 deta 矩陣的乘積 AB ( 按 CTRL+SHIFT+ENTER) 反矩陣 A -1 ( 按 CTRL+SHIFT+ENTER) 轉置矩陣 A T ( 按 CTRL+SHIFT+ENTER) 3

項目 內容 函數名稱 函數功能 AVERAGE( 範圍 ) 算數平均值 GEOMEAN( 範圍 ) 幾何平均數 MODE( 範圍 ) 眾數 COUNT( 範圍 ) 數值的個數 COUNTBLANK( 範圍 ) 空儲存格的個數 CORREL( 陣列 A, 陣列 B) 相關係數 NORMDIST( 數值, 平均數, 標準差 ) 指定分配之分配函數統 7. NORMINV( 機率, 平均數, 標準差 ) 累計分配的反值計 BINOMDIST( 成功次數, 實驗次數, 二項分配之分配函數成功機率 ) BINOMDIST( 機率, 平均數, 標準差 ) 累計分配的反值 PERCENTILE( 範圍, 百分比 ) 範圍中某百分比的數值 PERCENTRANK( 範圍, 儲存格, 有效位數 ) 儲存格的百分比排位 PROB( 數值, 機率, 下限, 上限 ) 範圍中值上下限間的機率 8. INTERCEPT(y 資料範圍,x 資料範迴迴歸直線的 y 軸截距圍 ) 歸 SLOPE(y 資料範圍,x 資料範圍 ) 迴歸直線的斜率分迴歸線中為每個 x 所預測 y 析 STEYX(y 資料範圍,x 資料範圍 ) 值時所產生的標準差 如果所有條件為真, 則傳回 AND( 條件 1, 條件 2, ) 真邏 9. 如果任一個條件為真, 則傳輯 OR( 條件 1, 條件 2, ) 回真 IF( 條件, 若真之動作, 若假之動作 ) 指定要執行的邏輯測試 從字串中的指定位置開始 MID( 字串, 開始字元, 字元數 ) 傳回指定數目的字元文 RIGHT( 字串, 字元數 ) 字串中最右邊的字元字 LEFT( 字串, 字元數 ) 字串中最左邊的字元 10. 及 LEN( 字串 ) 字串中字元的個數資 T( 數 ) 將數轉換為文字料 VALUE( 文字引數 ) 將文字引數轉換為數字 TRIM( 字串 ) 刪除文字中的空格 尋 INDIRECT( 參照位址 ) 由文字值表示的參照 找 MATCH( 尋找的值, 參照位址, 型態 ) 在參照或陣列中尋找值 11. 和以文字格式傳回對工作表 ADDRESS( 列號, 欄號, 參照方式 ) 參中單一儲存格的參照 照 CHOOSE( 選取引數值, 值 1, 值 2, ) 從值的清單中選擇一個值 4

六. 資料分析 1. 製作敘述統計摘要表 : (1). 執行方法 : 工具 + 資料分析 + 敘述統計 (2). 輸出結果 : 平均數 標準誤 中間值 眾數 標準差 變異數 峰度 偏態 範圍 最小值 最大值 總和 個數 信賴度 (95%) 2. 製作等級與百分比表 : (1). 執行方法 : 工具 + 資料分析 + 等級與百分比 (2). 輸出結果 : 等級 百分比 3. 製作直方圖 : (1). 執行方法 : 工具 + 資料分析 + 直方圖 (2). 輸出結果 : 柏拉圖 ( 經排序的直方圖 ) 累積百分率 長條圖 ( 可改成直方圖 ) 4. 製作成績等第表 : 使用函數 : 加權方法 排序 (RANK) 條件式 (IF) 計算滿足條件人數 (COUNTIF) 平均 (AVERAGE) 母體標準差 (STDEVP) 樣本標準差 (STDEV) 5. 幾何平均數 調和平均數 平均差 四分位差 : 使用函數 : 幾何平均數 (GEOMEAN) 調和平均數 (HARMEAN) 平均差 (AVEDEV) 四分位差 (QUARTILE) 6. 相關係數 : (1). 執行方法 : 工具 + 資料分析 + 相關係數 (2). 輸出結果 : 相關係數 七. 樞紐分析 1. 樞紐分析 : (1). 執行方法 : 資料 + 樞紐分析表及圖報表 (2). 步驟 1:Excel 清單或資料庫 (3). 步驟 2: 鍵入資料範圍 (4). 步驟 3: 拖曳變數 (5). 步驟 4: 設定結果存放位置 2. 樞紐分析表精靈 : (1). 執行方法 : 檢視 + 工具列 + 樞紐分析表 (2). 欄欄位 列欄位設定 : 移至變數後按右鍵 + 欄位設定 + 可設定加總 項目個數 平均值 最大值 最小值 乘積 數字項個數 標準差 變異值等 (3). 欄欄位 列欄位排序顯示 : 移至變數後按右鍵 + 欄位設定 + 進階 + 可設定遞增或遞減排序顯示結果 (4). 資料欄位設定 : 欄位設定 + 選項 + 可設定一般 差異 百分比等 (5). 分頁欄位 : 拖曳變數至分頁欄位 (6). 群組 : 移至變數後按右鍵 + 群組及顯示詳細資料 + 群組 + 設定開始點 結束點及間距值 5

八. 圖表 1. 圖表精靈 : (1). 執行方法 : 插入 + 圖表 (2). 對話方塊 : (a). 選取圖表類型 : 直條圖 橫條圖 折線圖 圓形圖 XY 散布圖 區域圖 環圈圖 雷達圖 區面圖 泡泡圖 股票圖 圓柱圖 圓椎圖 金字塔圖 (b). 選區資料範圍 設定資料範圍 數列資料取自列或欄 設定數列名稱與數值範圍 新增或移除數列 (c). 設定圖表選項 標題 坐標軸 格線 圖例 資料標籤 資料表 (d). 指定圖表位置 新的工作表 工作表中的物件 九. 迴歸 1. 迴歸分析方法一 : (1). 作散佈圖 : 插入 + 圖表 +XY 散布圖 (2). 作趨勢線 : 選取圖表 + 圖表 + 加上趨勢線 + 線性 2. 迴歸分析方法二 : (1). 作散佈圖 : 工具 + 資料分析 + 迴歸 (2). 輸出結果 : 殘差 殘差圖 標準化殘差 樣本迴歸線圖 常態機率圖 迴歸線斜率 迴歸線 Y 截距 3. 複迴歸分析 : (1). 作散佈圖 : 工具 + 資料分析 + 迴歸 (2). 輸出結果 : 殘差 殘差圖 標準化殘差 樣本迴歸線圖 常態機率圖 迴歸線各變數之係數 迴歸線 Y 截距 6