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

目 錄 第1 篇 入門及函數(shù)篇 第1 章 Excel入門............................. 2 1.1 熟悉Excel 界面................................ 2 1.1.1 Excel 界面瀏覽........................ 2 1.1.2 新界面的優(yōu)勢(shì)........................... 4 1.1.3 多功能的狀態(tài)欄....................... 5 1.1.4 自定義用戶界面....................... 5 1.2 Excel 功能區(qū)簡(jiǎn)介............................. 6 1.2.1 功能區(qū)的功能分布................... 6 1.2.2 調(diào)用上下文選項(xiàng)卡................... 6 1.3 調(diào)用快捷鍵....................................... 7 1.3.1 萬能的功能鍵【Alt】............. 7 1.3.2 調(diào)用舊版菜單鍵....................... 8 1.3.3 自定義快捷鍵........................... 8 1.4 設(shè)置Excel 選項(xiàng)................................ 9 1.4.1 Excel 選項(xiàng)功能介紹................ 9 1.4.2 詳解公式選項(xiàng)......................... 11 1.5 及時(shí)調(diào)用幫助................................. 12 1.5.1 調(diào)用函數(shù)幫助的4種方式..... 13 1.5.2 Excel 幫助的缺點(diǎn).................. 14 第2 章 初識(shí)公式............................ 15 2.1 公式的概念與結(jié)構(gòu)......................... 15 2.1.1 何謂公式................................. 15 2.1.2 公式的結(jié)構(gòu)............................. 16 2.2 公式的優(yōu)缺點(diǎn)................................. 16 2.2.1 公式的應(yīng)用前景..................... 16 2.2.2 公式的計(jì)算速度..................... 17 2.2.3 公式的3個(gè)缺點(diǎn)..................... 17 2.2.4 用VBA擴(kuò)展內(nèi)部公式的功能.18 2.3 錄入與編輯公式............................. 18 2.3.1 如何錄入公式......................... 18 2.3.2 公式與文本的兩種轉(zhuǎn)換方式. 19 2.3.3 修改公式................................. 20 2.4 認(rèn)識(shí)引用......................................... 21 2.4.1 如何引用單元格..................... 21 2.4.2 跨表引用技巧......................... 21 2.4.3 相對(duì)引用、絕對(duì)引用與混合 引用......................................... 23 2.4.4 將引用轉(zhuǎn)換成值..................... 24 2.4.5 區(qū)分引用與文本..................... 24 2.5 認(rèn)識(shí)運(yùn)算符與優(yōu)先級(jí)..................... 24 2.5.1 Excel 公式所支持的運(yùn)算符.. 24 2.5.2 Excel 公式所支持的數(shù)學(xué)符號(hào).25 2.5.3 了解運(yùn)算優(yōu)先級(jí)..................... 25 2.6 復(fù)制公式......................................... 26 2.6.1 復(fù)制公式的必要性................. 26 2.6.2 復(fù)制公式的多種技巧............. 26 2.7 定位公式......................................... 28 2.7.1 選擇所有公式......................... 28 2.7.2 隱藏所有公式......................... 28 2.7.3 刪除所有值為錯(cuò)誤的公式..... 29 2.8 公式的錯(cuò)誤類型............................. 30 2.8.1 公式錯(cuò)誤的種類..................... 30 2.8.2 #DIV/0!錯(cuò)誤........................... 30 2.8.3 #N/A 錯(cuò)誤............................... 30 2.8.4 #NAME?錯(cuò)誤......................... 30 2.8.5 #NUM!錯(cuò)誤............................ 31 2.8.6 #VALUE!錯(cuò)誤........................ 31 2.8.7 #REF!錯(cuò)誤.............................. 31 2.8.8 檢查錯(cuò)誤................................. 31 2.9 公式求值......................................... 32 II Excel 函數(shù)、圖表與透視表從入門到精通（全新版） 2.10 迭代計(jì)算....................................... 33 2.10.1 何謂迭代計(jì)算....................... 33 2.10.2 迭代計(jì)算的應(yīng)用：累加....... 33 2.10.3 迭代計(jì)算的應(yīng)用：自動(dòng) 添加不更新的時(shí)間............... 34 2.10.4 迭代計(jì)算的應(yīng)用：解方程... 35 第3 章 公式的靈魂——函數(shù).......... 36 3.1 函數(shù)的作用..................................... 36 3.1.1 提升速度................................. 36 3.1.2 簡(jiǎn)化公式................................. 37 3.1.3 實(shí)現(xiàn)更強(qiáng)大的計(jì)算功能......... 37 3.1.4 選擇性地統(tǒng)計(jì)運(yùn)算................. 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ù)運(yùn)算中的空單元格與空文本. 45 3.6 函數(shù)與公式的限制......................... 46 3.6.1 數(shù)字計(jì)算精度......................... 46 3.6.2 日期與時(shí)間計(jì)算精度............. 46 3.6.3 浮點(diǎn)運(yùn)算偏差......................... 47 3.6.4 公式長(zhǎng)度限制......................... 48 3.6.5 參數(shù)個(gè)數(shù)限制......................... 49 3.6.6 參數(shù)的類型限制..................... 49 3.6.7 IF 函數(shù)嵌套限制.................... 50 3.6.8 功能限制及例外項(xiàng)................. 50 第4 章 數(shù)學(xué)運(yùn)算............................ 51 4.1 求和運(yùn)算......................................... 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 求積運(yùn)算......................................... 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 隨機(jī)數(shù)............................................. 68 4.4.1 RAND隨機(jī)小數(shù).................... 68 4.4.2 RANDBETWEEN 隨機(jī)整數(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 章 邏輯運(yùn)算............................ 81 5.1 真假值運(yùn)算..................................... 81 5.1.1 TRUE/FALSE真假運(yùn)算........ 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 防錯(cuò)函數(shù)............... 88 第6 章 文本處理............................ 90 6.1 字符碼轉(zhuǎn)換..................................... 90 6.1.1 CODE返回字符的數(shù)字代碼.... 90 6.1.2 CHAR 返回?cái)?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文本檢查及降維應(yīng)用......... 99 6.3.4 REPT按指定次數(shù)重復(fù)文本... 101 6.3.5 TRIM 去除多余空格............ 103 6.3.6 TEXT 格式化數(shù)字............... 105 6.4 字符比較....................................... 112 6.5 長(zhǎng)度計(jì)算及提取指定長(zhǎng)度的 字符............................................... 114 6.5.1 LEN 字符長(zhǎng)度計(jì)算.............. 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)計(jì)運(yùn)算..........................126 7.1 平均值統(tǒng)計(jì)................................... 126 7.1.1 AVERAGE 統(tǒng)計(jì)參數(shù)的 算術(shù)平均值........................... 126 7.1.2 AVERAGEA 統(tǒng)計(jì)包括邏輯值 與文本的算術(shù)平均值........... 128 7.1.3 AVERAGEIF 按條件統(tǒng)計(jì)參數(shù) 的算術(shù)平均值....................... 128 7.1.4 AVERAGEIFS按多條件統(tǒng)計(jì)參 數(shù)的算術(shù)平均值................... 130 7.2 數(shù)量統(tǒng)計(jì)....................................... 130 7.2.1 COUNT 統(tǒng)計(jì)包含數(shù)字的單元 格個(gè)數(shù)................................... 131 7.2.2 COUNTA 統(tǒng)計(jì)非空單元格的 個(gè)數(shù)....................................... 132 7.2.3 COUNTIF統(tǒng)計(jì)區(qū)域中滿足條件 的單元格個(gè)數(shù)....................... 134 7.2.4 COUNTIFS 統(tǒng)計(jì)區(qū)域中滿足多 條件的單元格個(gè)數(shù)............... 138 7.3 極值與中值統(tǒng)計(jì)........................... 139 7.3.1 MIN 統(tǒng)計(jì)參數(shù)中的最小值.. 139 7.3.2 SMALL統(tǒng)計(jì)數(shù)據(jù)中第k個(gè)最 小值....................................... 142 7.3.3 MAX 統(tǒng)計(jì)參數(shù)中的最大值144 7.3.4 LARGE統(tǒng)計(jì)數(shù)據(jù)中第k個(gè)最 大值....................................... 145 7.3.5 MEDIAN統(tǒng)計(jì)一組數(shù)據(jù)的 中值....................................... 146 7.3.6 MODE統(tǒng)計(jì)一組數(shù)據(jù)中的 眾數(shù)....................................... 147 7.3.7 MODE.MULT 統(tǒng)計(jì)一組數(shù)據(jù)的 中值的垂直數(shù)組................... 148 IV Excel 函數(shù)、圖表與透視表從入門到精通（全新版） 7.4 排名統(tǒng)計(jì)....................................... 149 7.5 頻率分布....................................... 151 第8 章 日期和時(shí)間運(yùn)算............... 153 8.1 日期運(yùn)算....................................... 153 8.1.1 TODAY獲取當(dāng)前系統(tǒng)日期153 8.1.2 YEAR/MONTH/DAY 獲取年/ 月/日...................................... 155 8.1.3 DATE 根據(jù)指定的年月日序號(hào) 返回日期............................... 157 8.1.4 EDATE返回指定日期之前或之 后幾個(gè)月的日期序號(hào)........... 158 8.1.5 EOMONTH 返回指定日期之前 或之后的月末日期序號(hào)....... 159 8.1.6 DATEDIF計(jì)算兩個(gè)日期間的間 隔時(shí)間................................... 160 8.2 時(shí)間運(yùn)算....................................... 161 8.2.1 NOW 獲取當(dāng)前日期和時(shí)間161 8.2.2 HOUR/MINUTE/SECOND獲取 時(shí)/分/秒................................ 163 8.3 星期與工作日運(yùn)算....................... 163 8.3.1 WEEKDAY 將日期轉(zhuǎn)換成代表 星期的序號(hào)........................... 163 8.3.2 WEEKNUM返回日期在一年中 的周數(shù)................................... 166 8.3.3 NETWORKDAYS 返回兩個(gè)日 期之間的完整工作日數(shù)....... 167 8.3.4 WORKDAY返回指定工作日之 前/后的日期......................... 169 第9 章 查找與引用....................... 170 9.1 引用函數(shù)....................................... 170 9.1.1 ROW/COLUMN 引用行號(hào)/ 列號(hào)....................................... 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 將引用的方向進(jìn) 行轉(zhuǎn)置................................... 184 9.1.8 CHOOSE 從參數(shù)中引用索引值 所指定的值........................... 186