Excel函數(shù)、圖表與透視表從入門到精通（全新版）

目 錄 第1 篇 入門及函數(shù)篇 第1 章 Excel入門............................. 2 1.1 熟悉Excel 界面................................ 2 1.1.1 Excel 界面瀏覽........................ 2 1.1.2 新界面的優(yōu)勢........................... 4 1.1.3 多功能的狀態(tài)欄....................... 5 1.1.4 自定義用戶界面....................... 5 1.2 Excel 功能區(qū)簡介............................. 6 1.2.1 功能區(qū)的功能分布................... 6 1.2.2 調(diào)用上下文選項卡................... 6 1.3 調(diào)用快捷鍵....................................... 7 1.3.1 萬能的功能鍵【Alt】............. 7 1.3.2 調(diào)用舊版菜單鍵....................... 8 1.3.3 自定義快捷鍵........................... 8 1.4 設置Excel 選項................................ 9 1.4.1 Excel 選項功能介紹................ 9 1.4.2 詳解公式選項......................... 11 1.5 及時調(diào)用幫助................................. 12 1.5.1 調(diào)用函數(shù)幫助的4種方式..... 13 1.5.2 Excel 幫助的缺點.................. 14 第2 章 初識公式............................ 15 2.1 公式的概念與結(jié)構......................... 15 2.1.1 何謂公式................................. 15 2.1.2 公式的結(jié)構............................. 16 2.2 公式的優(yōu)缺點................................. 16 2.2.1 公式的應用前景..................... 16 2.2.2 公式的計算速度..................... 17 2.2.3 公式的3個缺點..................... 17 2.2.4 用VBA擴展內(nèi)部公式的功能.18 2.3 錄入與編輯公式............................. 18 2.3.1 如何錄入公式......................... 18 2.3.2 公式與文本的兩種轉(zhuǎn)換方式. 19 2.3.3 修改公式................................. 20 2.4 認識引用......................................... 21 2.4.1 如何引用單元格..................... 21 2.4.2 跨表引用技巧......................... 21 2.4.3 相對引用、絕對引用與混合 引用......................................... 23 2.4.4 將引用轉(zhuǎn)換成值..................... 24 2.4.5 區(qū)分引用與文本..................... 24 2.5 認識運算符與優(yōu)先級..................... 24 2.5.1 Excel 公式所支持的運算符.. 24 2.5.2 Excel 公式所支持的數(shù)學符號.25 2.5.3 了解運算優(yōu)先級..................... 25 2.6 復制公式......................................... 26 2.6.1 復制公式的必要性................. 26 2.6.2 復制公式的多種技巧............. 26 2.7 定位公式......................................... 28 2.7.1 選擇所有公式......................... 28 2.7.2 隱藏所有公式......................... 28 2.7.3 刪除所有值為錯誤的公式..... 29 2.8 公式的錯誤類型............................. 30 2.8.1 公式錯誤的種類..................... 30 2.8.2 #DIV/0!錯誤........................... 30 2.8.3 #N/A 錯誤............................... 30 2.8.4 #NAME?錯誤......................... 30 2.8.5 #NUM!錯誤............................ 31 2.8.6 #VALUE!錯誤........................ 31 2.8.7 #REF!錯誤.............................. 31 2.8.8 檢查錯誤................................. 31 2.9 公式求值......................................... 32 II Excel 函數(shù)、圖表與透視表從入門到精通（全新版） 2.10 迭代計算....................................... 33 2.10.1 何謂迭代計算....................... 33 2.10.2 迭代計算的應用：累加....... 33 2.10.3 迭代計算的應用：自動 添加不更新的時間............... 34 2.10.4 迭代計算的應用：解方程... 35 第3 章 公式的靈魂——函數(shù).......... 36 3.1 函數(shù)的作用..................................... 36 3.1.1 提升速度................................. 36 3.1.2 簡化公式................................. 37 3.1.3 實現(xiàn)更強大的計算功能......... 37 3.1.4 選擇性地統(tǒng)計運算................. 37 3.2 函數(shù)的分類..................................... 38 3.2.1 按用途分類............................. 38 3.2.2 按參數(shù)分類............................. 38 3.2.3 按刷新方式分類..................... 40 3.3 函數(shù)的參數(shù)..................................... 40 3.3.1 參數(shù)的作用............................. 40 3.3.2 參數(shù)的類型............................. 40 3.3.3 參數(shù)嵌套................................. 42 3.3.4 選擇參數(shù)的技巧..................... 42 3.3.5 省略參數(shù)與省略參數(shù)的值..... 42 3.4 函數(shù)的錄入方式............................. 43 3.4.1 使用函數(shù)庫............................. 43 3.4.2 使用插入函數(shù)向?qū)?................ 44 3.4.3 直接錄入................................. 44 3.5 函數(shù)運算中的空單元格與空文本. 45 3.6 函數(shù)與公式的限制......................... 46 3.6.1 數(shù)字計算精度......................... 46 3.6.2 日期與時間計算精度............. 46 3.6.3 浮點運算偏差......................... 47 3.6.4 公式長度限制......................... 48 3.6.5 參數(shù)個數(shù)限制......................... 49 3.6.6 參數(shù)的類型限制..................... 49 3.6.7 IF 函數(shù)嵌套限制.................... 50 3.6.8 功能限制及例外項................. 50 第4 章 數(shù)學運算............................ 51 4.1 求和運算......................................... 51 4.1.1 SUM 求和............................... 51 4.1.2 SUMIF 條件求和................... 53 4.1.3 SUMIFS多條件求和............. 57 4.1.4 SUMPRODUCT 乘積之和.... 57 4.1.5 SUMSQ求平方和.................. 59 4.2 求積運算......................................... 59 4.2.1 PRODUCT求積..................... 60 4.2.2 MMULT兩數(shù)組的矩陣積..... 61 4.3 商與余數(shù)......................................... 65 4.3.1 QUOTIENT 求商................... 65 4.3.2 MOD 求余數(shù).......................... 66 4.4 隨機數(shù)............................................. 68 4.4.1 RAND隨機小數(shù).................... 68 4.4.2 RANDBETWEEN 隨機整數(shù). 70 4.5 乘方與開方..................................... 71 4.5.1 SQRT 開平方......................... 71 4.5.2 POWER 乘方.......................... 72 4.6 數(shù)據(jù)的舍入問題............................. 73 4.6.1 ODD/EVEN 取奇數(shù)與偶數(shù)... 74 4.6.2 TRUNC/INT截取為指定位數(shù)小 數(shù)或整數(shù)................................. 74 4.6.3 CEILING 向上舍入為最接近基 數(shù)的倍數(shù)................................. 75 4.6.4 FLOOR向下舍入為最接近基數(shù) 的倍數(shù)..................................... 76 4.6.5 ROUND四舍五入................. 76 4.6.6 ROUNDDOWN向下舍入..... 77 4.7 分類匯總......................................... 78 第5 章 邏輯運算............................ 81 5.1 真假值運算..................................... 81 5.1.1 TRUE/FALSE真假運算........ 81 III 目 錄 5.1.2 AND/OR 判斷是否符合 條件......................................... 83 5.1.3 NOT 邏輯值求反................... 85 5.2 條件判斷......................................... 85 5.2.1 IF 條件判斷............................ 86 5.2.2 IFERROR 防錯函數(shù)............... 88 第6 章 文本處理............................ 90 6.1 字符碼轉(zhuǎn)換..................................... 90 6.1.1 CODE返回字符的數(shù)字代碼.... 90 6.1.2 CHAR 返回數(shù)字參數(shù)指定的 字符......................................... 91 6.2 文本合并......................................... 93 6.2.1 CONCATENATE串連字符..... 94 6.2.2 自定義合并函數(shù)..................... 95 6.3 字符轉(zhuǎn)換......................................... 97 6.3.1 PROPER 將單詞轉(zhuǎn)換為 首字母大寫............................. 97 6.3.2 UPPER/LOWER 將單詞 轉(zhuǎn)換為大寫或小寫................. 98 6.3.3 T文本檢查及降維應用......... 99 6.3.4 REPT按指定次數(shù)重復文本... 101 6.3.5 TRIM 去除多余空格............ 103 6.3.6 TEXT 格式化數(shù)字............... 105 6.4 字符比較....................................... 112 6.5 長度計算及提取指定長度的 字符............................................... 114 6.5.1 LEN 字符長度計算.............. 114 6.5.2 LEFT 取左邊的字符............ 115 6.5.3 RIGHT取右邊的字符......... 116 6.5.4 MID 取中間的字符.............. 118 6.6 字符查找與替換........................... 120 6.6.1 FIND 區(qū)分大小寫的字符 查找......................................................120 6.6.2 SEARCH 支持通配符的字 符查找................................... 121 6.6.3 REPLACE 替換指定位置的 字符....................................... 123 6.6.4 SUBSTITUTE替換指定 字符...........................................124 第7 章 統(tǒng)計運算..........................126 7.1 平均值統(tǒng)計................................... 126 7.1.1 AVERAGE 統(tǒng)計參數(shù)的 算術平均值........................... 126 7.1.2 AVERAGEA 統(tǒng)計包括邏輯值 與文本的算術平均值........... 128 7.1.3 AVERAGEIF 按條件統(tǒng)計參數(shù) 的算術平均值....................... 128 7.1.4 AVERAGEIFS按多條件統(tǒng)計參 數(shù)的算術平均值................... 130 7.2 數(shù)量統(tǒng)計....................................... 130 7.2.1 COUNT 統(tǒng)計包含數(shù)字的單元 格個數(shù)................................... 131 7.2.2 COUNTA 統(tǒng)計非空單元格的 個數(shù)....................................... 132 7.2.3 COUNTIF統(tǒng)計區(qū)域中滿足條件 的單元格個數(shù)....................... 134 7.2.4 COUNTIFS 統(tǒng)計區(qū)域中滿足多 條件的單元格個數(shù)............... 138 7.3 極值與中值統(tǒng)計........................... 139 7.3.1 MIN 統(tǒng)計參數(shù)中的最小值.. 139 7.3.2 SMALL統(tǒng)計數(shù)據(jù)中第k個最 小值....................................... 142 7.3.3 MAX 統(tǒng)計參數(shù)中的最大值144 7.3.4 LARGE統(tǒng)計數(shù)據(jù)中第k個最 大值....................................... 145 7.3.5 MEDIAN統(tǒng)計一組數(shù)據(jù)的 中值....................................... 146 7.3.6 MODE統(tǒng)計一組數(shù)據(jù)中的 眾數(shù)....................................... 147 7.3.7 MODE.MULT 統(tǒng)計一組數(shù)據(jù)的 中值的垂直數(shù)組................... 148 IV Excel 函數(shù)、圖表與透視表從入門到精通（全新版） 7.4 排名統(tǒng)計....................................... 149 7.5 頻率分布....................................... 151 第8 章 日期和時間運算............... 153 8.1 日期運算....................................... 153 8.1.1 TODAY獲取當前系統(tǒng)日期153 8.1.2 YEAR/MONTH/DAY 獲取年/ 月/日...................................... 155 8.1.3 DATE 根據(jù)指定的年月日序號 返回日期............................... 157 8.1.4 EDATE返回指定日期之前或之 后幾個月的日期序號........... 158 8.1.5 EOMONTH 返回指定日期之前 或之后的月末日期序號....... 159 8.1.6 DATEDIF計算兩個日期間的間 隔時間................................... 160 8.2 時間運算....................................... 161 8.2.1 NOW 獲取當前日期和時間161 8.2.2 HOUR/MINUTE/SECOND獲取 時/分/秒................................ 163 8.3 星期與工作日運算....................... 163 8.3.1 WEEKDAY 將日期轉(zhuǎn)換成代表 星期的序號........................... 163 8.3.2 WEEKNUM返回日期在一年中 的周數(shù)................................... 166 8.3.3 NETWORKDAYS 返回兩個日 期之間的完整工作日數(shù)....... 167 8.3.4 WORKDAY返回指定工作日之 前/后的日期......................... 169 第9 章 查找與引用....................... 170 9.1 引用函數(shù)....................................... 170 9.1.1 ROW/COLUMN 引用行號/ 列號....................................... 170 9.1.2 ROWS/COLUMNS 引用行數(shù)/ 列數(shù)....................................... 173 9.1.3 ADDRESS 返回指定行/列的引 用地址................................... 174 9.1.4 INDIRECT 返回文本指定的 引用....................................... 175 9.1.5 OFFSET產(chǎn)生指定偏移量的 引用....................................... 179 9.1.6 INDEX 引用區(qū)域或者數(shù)組中指 定行與列的值....................... 181 9.1.7 TRANSPOSE 將引用的方向進 行轉(zhuǎn)置................................... 184 9.1.8 CHOOSE 從參數(shù)中引用索引值 所指定的值........................... 186