Excel職場手冊 260招菜鳥變達人

聂春霞

2001年毕业于南京邮电大学，毕业后在中国移动通信集团江西有限公司工作近4年，2005年加入腾讯公司，现就职于腾讯公司MIG运营商业务部，专注数据分析岗位12年，勤于总结，乐于分享。2011年开始兼任腾讯学院Excel课程讲师，精心研究，解惑释疑。追求简单有效，实战为王。磨刀不误砍柴工，是对学好Excel的注解。愿以本书作山泉水，濯读者手中柴刀。

佛山小老鼠

真名曹明武，网名“佛山小老鼠”，ExcelHome论坛版主，Excel论坛站长，大型Excel插件“完 美工具箱”作者，从事网络Excel教学多年，精通Excel函数、VBA编程，微信号18664243619。

第 一篇　數據操作與處理

 

第 1章　單元格數據錄入與編輯 2

第 1招　快捷鍵的妙用（基於Windows操作系統） 2

第 2招　常用快捷鍵Windows與Mac對照 4

第3招　怎樣快速輸入幾十萬相同數據 5

第4招　身份證號碼、銀行卡號等超過15位數據的錄入技巧 5

第5招　錄入的分數怎麽變成日期了 5

第6招　帶有上標的指數錄入 5

第7招　等差與等比數列的錄入技巧 6

第8招　怎樣在多張工作表錄入相同的數據——創建工作組 7

第9招　合並單元格取消後如何批量填充空白區域 7

第 10招　神奇的快速填充 8

第 11招　為單元格設置數據錄入的範圍——數據驗證 9

第 12招　數據驗證引用序列不在同一行或同一列怎麽辦 10

第 13招　利用數據驗證給單元格添加註釋，不用批註 11

第 14招　利用數據驗證記錄數據錄入時間 12

第 15招　利用數據驗證限制單元格數據類型 13

第 16招　利用數據驗證防止重復錄入相同的內容 13

第 17招　避免數據重復錄入 14

第 18招　怎樣插入符號或 14

第 19招　Excel中漢字怎樣加上拼音 15

第 20招　怎樣在單元格文字前加空白 16

第 21招　怎樣在單元格中添加項目符號 16

第 22招　如何輸入千分號 17

第 23招　如何批量添加批註和刪除批註 17

 

第 2章　整理單元格數據 19

第 24招　文本與數字格式的相互批量轉換 19

第 25招　如何快速把數據的單位從元轉換為萬元 20

第 26招　對齊兩個字的名字 21

第 27招　單元格數值滿足一定條件字體或背景顏色標紅 21

第 28招　單元格數值滿足一定條件標上相應的圖標 22

第 29招　將單元格數字變成條形圖 23

第30招　查找重復值或唯 一值 23

第31招　國際象棋棋盤式底紋設置方法 24

第32招　奇偶行不同的斑馬紋 25

第33招　永恆的間隔底紋 25

第34招　每隔N行批量填充顏色 26

第35招　突出顯示符合要求的日期 28

第36招　自動實現生日提醒 29

第37招　用條件格式製作項目進度圖 29

第38招　你知道你出生那天是星期幾嗎？——Excel奇妙的自定義單元格格式 30

第39招　篩選後粘貼 34

第40招　從字母和數字的混合字符串中提取數字 36

 

第3章　查找與替換 38

第41招　把單元格的0替換為9，其他非0單元格中的0也被替換了 38

第42招　單元格內容怎麽顯示#NAME？ 38

第43招　怎樣查找通配符波形符～ 39

第44招　怎樣去掉數字中的小數 40

第45招　查找帶有背景顏色的單元格 40

第46招　按住Shift鍵反方向查找 41

第47招　查找能區分大小寫嗎？ 41

第48招　在公式、值、批註範圍查找 41

第49招　在所有工作表中查找 42

第50招　多列按區間查找 42

第51招　查找所有合並單元格 43

第52招　借助Word分離不連續的數字和英文字母 45

第53招　怎樣去掉不可見的換行符 45

 

第4章　行與列的操作 48

第54招　怎樣把行變為列或列變為行 48

第55招　如何在每一行的下麵一次性插入一個空白行 48

第56招　如何批量插入指定行數的空白行 50

第57招　快速把多列數據變為一列數據——利用剪貼板 53

第58招　快速把多列數據變為一列數據——巧妙利用錯位引用 53

第59招　快速把多列數據變為一列數據——利用數據透視表多重合並計算 54

第60招　怎樣將一個字段中的中文和英文分開為2個字段 55

第61招　怎樣保持分列後的格式和分列前的一致 56

第62招　怎樣把常規的數字格式變為日期格式 57

第63招　怎樣把一個單元格內多行內容分成多個單元格 57

第64招　100行數據全選為什麽只有80行數據 58

第65招　隔列刪除 59

第66招　隔列粘貼 60

第67招　怎樣將表格首行和首列同時固定不動 61

第68招　怎樣將表格首行和末行同時固定不動 62

第69招　Excel中如何一次選中多個圖表或圖形對象 63

第70招　兩列數據互換位置 64

第71招　復製表格怎樣保持行高和列寬不變 64

 

第5章　工作表以及文件的操作 65

第72招　工作表標簽的顯示與隱藏 65

第73招　工作表的顯示與隱藏 65

第74招　怎樣將Excel工作表深度隱藏起來 65

第75招　怎樣將Excel隱藏的所有工作表批量刪除 67

第76招　Excel工作表和工作簿的保護 67

第77招　Excel不同版本**多能裝載的行數與列數 70

第78招　Excel不同版本的轉換 71

第79招　快速關閉所有打開的Excel文件 71

第80招　Excel文件保存顯示信息不能通過“文檔檢查器”刪除 72

第81招　Excel 文件減肥瘦身秘訣 74

第82招　Excel打開CSV文件為亂碼的解決方法 75

第83招　Excel中外部數據鏈接無法刪除怎麽辦 76

第84招　Excel中鮮為人知的“照相機”功能 77

第85招　Excel文件有很多頁，每頁打印標題行 79

第86招　Excel文件打印怎樣插入頁碼、頁眉、頁腳 80

第87招　Excel文件行多列少，打印莫煩惱 82

第88招　Excel文件工作表中的錯誤值不打印 83

 

第 二篇　數據分析

 

第6章　排序、篩選、分類匯總、合並計算 86

第89招　按照自己的想法隨心所欲地排序 86

第90招　為什麽排序結果有時候不對 87

第91招　組內排序 88

第92招　隱藏數據更好的方法——創建組 89

第93招　讓報表收放自如——分類匯總 89

第94招　像“傻瓜相機”一樣簡單易用的篩選 90

第95招　利用通配符根據模糊條件篩選 91

第96招　滿足多條件中的一個或者任意個高 級篩選 92

第97招　篩選不重復的記錄 94

第98招　篩選2個或多個工作表相同數據 94

第99招　怎樣篩選指定長度的文本 94

第 100招　刪除重復項 95

第 101招　怎樣匯總多張字段相同的工作表數據——強大的合並計算 96

第 102招　怎樣核對兩張表數據是否一致 97

 

第7章　數據分析利器—數據透視表 99

第 103招　利用數據透視表實現滿足一定條件的計數和求和 99

第 104招　Excel 2013如何通過透視表實現不重復計數 102

第 105招　為什麽我的數據透視表不能按日期創建組 103

第 106招　用數據透視表瞬間一表變多表 104

第 107招　不會VBA也可以這樣玩控件篩選——數據透視表切片器 106

第 108招　在數據透視表中添加計算字段——計算字段之算術運用 108

第 109招　在數據透視表中添加計算字段——計算字段之函數運用 109

第 110招　用數據透視表將二維表轉一維表 111

第 111招　用Excel 2016逆透視將二維表轉一維表 113

第 112招　引用數據透視表的數據，為什麽拖動公式結果沒有變化 114

 

第三篇　公式與函數

 

第8章　公式與函數基礎 118

第 113招　公式類型（普通公式、數組公式、命名公式） 118

第 114招　一個容易走火入魔的點：引用類型（相對引用、絕 對引用、混合引用） 119

第 115招　九九乘法口訣表的製作方法 120

第 116招　三招走天下，三表概念（參數表、源數據表、匯總表） 120

第 117招　Excel單元格中為什麽只顯示公式不顯示計算結果 121

第 118招　函數不一定是解決問題的**佳辦法 121

 

第9章　常用的數學和統計函數 123

第 119招　COUNT、COUNTA、AVERAGE、MAX、MIN、LARGE、SMALL等常用函數 123

第 120招　根據多個小計項求總計 123

第 121招　多表相同位置求和 124

第 122招　如何批量輸入求和公式 124

第 123招　合並單元格求和 125

第 124招　怎樣給合並單元格添加連續序號 126

第 125招　多個數值的乘積——PRODUCT函數 126

第 126招　多個乘積之和——SUMPRODUCT函數 127

第 127招　對角線求和 128

第 128招　數值取捨函數——INT/TRUNC/ROUND/ROUNDUP/ROUNDDOWN等 130

第 129招　兩數相除取餘數函數——MOD函數 131

第 130招　統計出現頻率**多的數值——MODE函數 132

第 131招　滿足一定條件計數——COUNTIF 133

第 132招　為什麽不重復的身份證號碼計數視為重復 134

第 133招　滿足一定條件求和——SUMIF 134

第 134招　多條件統計函數（COUNTIFS、SUMIFS、AVERAGEIFS） 135

第 135招　分類匯總——SUBTOTAL函數 138

第 136招　表格篩選後序號能連續顯示嗎 139

第 137招　怎樣計算給定數值的中值——MEDIAN函數 139

第 138招　如何將幾百號名單隨機分組——借助RAND函數 140

第 139招　排序——RANK函數 140

第 140招　生成不重復隨機整數 141

 

第 10章　文本函數 142

第 141招　怎樣根據身份證號碼提取出生日期、地區代碼、性別 142

第 142招　怎樣把中英文分開 143

第 143招　怎樣統計字符串中分隔字符的個數 143

第 144招　清除空格的美顏大師——TRIM函數 143

第 145招　清除不可見字符的美顏大師——CLEAN函數 144

第 146招　字母大小寫的轉換——LOWER、UPPER、PROPER函數 144

第 147招　如何判斷2個字符串是否完全相同——EXACT函數 144

第 148招　多個文本合並——CONCATENATE函數、& 和PHONETIC函數 145

第 149招　如何查找某個字符串的位置——FIND和FINDB函數 146

第 150招　查找第 一個字符串在第 二個中起始位置編號——SEARCH和SEARCHB函數 147

第 151招　怎樣分離姓名和電話號碼 147

第 152招　文本字符串的替換——SUBSTITUTE函數 148

第 153招　文本字符串指定次數的替換——REPLACE函數 149

第 154招　長相相似但功能相反的兩個函數——CODE和CHAR函數 149

第 155招　將數值轉換為指定的數字格式——TEXT函數 150

第 156招　怎樣將帶有小數點的小寫數字轉化為大寫 151

 

第 11章　查找與引用函數 152

第 157招　VLOOKUP函數基本用法 152

第 158招　VLOOKUP函數多列查找 153

第 159招　VLOOKUP函數多條件查找 153

第 160招　VLOOKUP函數模糊查找 153

第 161招　VLOOKUP函數一對多查找 154

第 162招　怎樣通過簡稱或關鍵字模糊匹配查找全稱 157

第 163招　VLOOKUP查詢常見錯誤 158

第 164招　天生絕配——INDEX函數和MATCH函數 162

第 165招　從多個列表中選中指定的數值——CHOOSE函數 163

第 166招　VLOOKUP函數的兄弟——LOOKUP函數 164

第 167招　怎樣提取**後一列非空單元格內容——LOOKUP函數 165

第 168招　會漂移的函數——OFFSET函數 166

 

第 12章　日期與時間函數 168

第 169招　日期與時間函數——TODAY、NOW、YEAR、MONTH、DAY 168

第 170招　距離某天的第 20個工作日是哪一 天——WORKDAY函數 168

第 171招　員工工作了多少個工作日——NETWORKDAYS函數 169

第 172招　某日期是星期幾——WEEKDAY函數 169

第 173招　某天是一年中的第幾周——WEEKNUM函數 170

第 174招　返回特定日期的序列號——DATE函數 170

第 175招　如何統計兩個日期之間的年數、月數、天數——DATEDIF函數 170

第 176招　根據日期計算季度（8種方法） 171

第 177招　根據日期判斷閏年還是平年 173

 

第 13章　其他函數的應用 174

第 178招　邏輯函數IF和IFERROR 174

第 179招　Excel中如何把全角字符轉換為半角字符——ASC函數 175

第 180招　怎樣使公式中不出現#N/A等錯誤值—— Excel IS類函數介紹 175

第 181招　計算貸款的月償還金額——PMT函數 177

第 182招　轉置函數——TRANSPOSE函數 179

第 183招　怎樣批量給單元格地址添加超鏈接 180

第 184招　在單元格中提取當前文件的路徑、文件名或工作表 181

第 185招　什麽是宏表函數 182

第 186招　常用宏表函數的應用 183

 

第四篇　圖表製作

 

第 14章　常用圖表製作以及圖表優化 188

第 187招　圖表製作的基本理論知識 188

第 188招　基本圖表的製作，柱形圖、條形圖、折線圖、餅圖、散點圖等製作 190

第 189招　給Excel條形圖添加參考線 195

第 190招　一個橫坐標兩個縱坐標圖表 198

第 191招　Excel復合餅圖製作技巧 200

第 192招　Excel二級關系復合餅圖(母子餅圖)製作技巧 203

第 193招　Excel 預算與實績對比圖表（溫度計式柱形圖） 204

第 194招　左右對比條形圖（旋風圖） 207

第 195招　怎樣修改Excel圖表的圖例系列次序 208

第 196招　如何將3個柱形圖放在一張圖表中 209

第 197招　堆積柱形圖2個柱子怎樣靠在一起 210

第 198招　動態標出折線圖中的**大值、**小值 211

第 199招　Excel數據標簽實用小工具 212

第 200招　Excel圖表繪圖區背景按網格線隔行填色 213

第 201招　怎樣修改折線圖部分折線的顏色 216

第 202招　條形圖每個條形能自動生成不同的顏色嗎 217

第 203招　一個縱坐標兩個橫坐標的柱形圖 219

 

第 15章　高 級圖表的製作 221

第 204招　年報圖表 221

第 205招　矩陣式百分比圖 223

第 206招　多軸坐標圖 224

第 207招　四象限散點圖 226

第 208招　漏鬥圖 229

第 209招　迷你圖 231

第 210招　瀑布圖（Excel 2013版本） 233

第 211招　瀑布圖（Excel 2016版本） 235

第 212招　超霸氣的Excel 2016新圖表——樹狀圖 236

第 213招　超霸氣的Excel 2016新圖表——旭日圖 237

第 214招　Excel 2016新圖表——直方圖 238

第 215招　玫瑰圖 239

第 216招　半圓餅圖 241

第 217招　甘特圖 242

第 218招　動態甘特圖 244

第 219招　利用CHOOSE函數製作Excel動態餅圖 248

第 220招　巧妙利用OFFSET函數製作Excel動態圖表 249

第 221招　折線圖亂如麻，怎麽破？（動態圖表） 250

第 222招　折線圖亂如麻，怎麽破？（靜態圖表） 251

第 223招　儀表盤式圖表 252

 

第五篇　高 級應用與綜合應用

 

第 16章　宏與VBA 256

第 224招　Excel中的宏是什麽意思 有什麽用途 256

第 225招　宏腳本結構和常用語句 256

第 226招　在Excel中如何實現選擇日歷控件 256

第 227招　會唱歌的Excel文檔 258

第 228招　Excel中如何根據單元格的背景顏色來計數和求和 259

第 229招　怎樣快速給多個工作表創建超鏈接目錄 261

第 230招　多個工作簿合並到一個工作簿多個工作表 262

第 231招　多個工作簿合並到一個工作簿一個工作表 263

第 232招　怪哉，Excel的A列跑到**右邊了 264

 

第 17章　Excel與Access雙劍合璧 266

第 233招　初識Access 266

第 234招　SQL語句基礎 269

第 235招　實際工作中的案例 271

 

第 18章　多種技巧綜合運用 275

第 236招　如何批量實現Excel合並相同內容的單元格 275

第 237招　如何批量實現Excel多個單元格內容合並到一個單元格並且換行 277

第 238招　合並同類項 279

第 239招　如何批量刪除Excel單元格中的空格字符 280

第 240招　Excel與Word的並肩作戰之郵件合並 282

第 241招　創建動態數據透視表的五種方法 285

第 242招　怎樣在一張Excel工作表（含多個字段）找出重復的記錄 290

第 243招　如何在多個Excel工作簿中查找相同的數據——數據透視表方法 292

第 244招　如何在多個Excel工作簿中查找相同的數據——VLOOKUP函數方法 293

第 245招　如何在多個Excel工作簿中查找相同的數據——高 級篩選方法 293

第 246招　如何在多個Excel工作簿中查找相同的數據——借助Access數據庫處理 294

第 247招　多表合並——借助Power Query工具 295

第 248招　多表合並——SQL方法 298

第 249招　多表合並——函數與公式方法 299

第 250招　多表合並——VBA方法 300

第 251招　用Excel做線性回歸預測分析 301

第 252招　Excel 2016預測工作表 302

第 253招　Excel中的規劃求解 306

第 254招　Excel二級聯動下列菜單 307

第 255招　Excel三、四級更多級聯動下拉菜單 310

第 256招　怎樣把一列數據每個數據復制3次 311

第 257招　怎樣批量修改文件名 312

第 258招　Excel中強大的翻譯功能 314

第 259招　怎樣找出不同類別前5位**大數值 315

第 260招　HR工作中常用的Excel操作 318

 

附錄 323

附錄1　Excel限制和規範 324

附錄2　Excel常用快捷鍵 327