Excel高效辦公——表格、圖表、透視表、函數、數據分析5項修煉

李艳慧，东北师范大学毕业，北京四中教师。致力于信息技术、数学教学二十多年，辅导学生的作品曾多次在全国、省、市获奖。

第1章 表言表語的藝術功 1

1.1 表格佈局清晰的4個原則 2

1.1.1 表格不要從A1單元格開始 2

1.1.2 用文字列把數字列隔開 2

1.1.3 快速自動調整列寬 3

1.1.4 使用“跨列居中”，為表格插入清晰的標題 3

1.2 讓表格變得專業 4

1.2.1 使用Excel自帶的表格樣式 4

1.2.2 表格邊框的設計要點：內無框，四周框 5

1.2.3 “專業範”爆棚的斜線表頭 6

1.3 讓表格的標題在滾動表格時一動不動 7

1.3.1 凍結首行、首列 7

1.3.2 凍結窗格 8

1.4 表格文字設計法則 9

1.4.1 中文字體用微軟雅黑，數字和英文字體用Arial 9

1.4.2 除了標題，全部使用同樣的字號 9

1.4.3 行高統一設置為18 10

1.4.4 為數字添加易讀的千位分隔符 11

1.4.5 去掉“迷惑人心”的小數碼 11

1.4.6 縮減位數，把整數部分超過5位的數字用“萬”表示 12

1.4.7 數字居右，非數字居左，長短一樣居中 13

1.5 讓報表變得一目瞭然 14

1.5.1 用灰色填充突出報表中的主要項目 14

1.5.2 報表邊框的設計要點：上下粗，中間細 15

1.5.3 空數據不能用留白顯示 16

1.5.4 單位要自成一欄才能清晰可見 17

1.5.5 項目縮進體現層次 17

1.5.6 用組合把明細數據隱藏 18

1.5.7 將報表的標題放在內部左上角 19

1.6 單元格定位的經典用法 20

1.6.1 在所有的空單元格中輸入“99” 20

1.6.2 定位與替換的區別 22

1.6.3 選擇數據區域中的所有數字 22

1.6.4 選擇數據區域中的所有錯誤值 23

1.6.5 批量取消合並單元格並填充數據 24

1.7 選擇並處理單元格 25

1.7.1 快速選擇數據區域 26

1.7.2 遷移選擇的數據區域 27

高手私房菜

技巧1：巧使名字對齊顯示 28

技巧2：快速篩選出不重復的數據 29

技巧3：批量刪除工作表中的空白行 29

第2章 左手數據，右手圖表 31

2.1 數據可視化的4要素 32

2.2 創建圖表的5種方法 33

2.3 不可不知的圖表元素 35

2.4 常用圖表類型 36

2.5 圖表設計 39

2.6 圖表元素格式設置 40

2.7 16種常用圖表與經典案例 48

2.7.1 柱形圖─單一指標正負變動對比 48

2.7.2 二維柱形圖—各分項對比關系 50

2.7.3 柱形堆積圖─各分項與趨勢關系 52

2.7.4 柱線復合圖─分項同期對比與變動率 52

2.7.5 條形圖─多類指標單項對比 55

2.7.6 條形堆積圖─多分項目對比 57

2.7.7 面積圖─歷年變化趨勢 59

2.7.8 趨勢折線圖─實際預計趨勢 61

2.7.9 餅狀分佈圖─分類占比對比 63

2.7.10 散點圖─線性回歸分析 64

2.7.11 象限圖─二維指標對比 66

2.7.12 樹狀圖─按板塊分類對比 68

2.7.13 環形圖─分類占比解析 69

2.7.14 瀑布圖─環比變動關聯 70

2.7.15 多指標柱線復合圖─預算分項節約對比 71

2.7.16 復合餅圖——單項二次細分拆解 73

2.8 製作迷你圖 74

2.8.1 創建單個迷你圖 74

2.8.2 創建多個迷你圖 75

2.8.3 編輯迷你圖 76

2.8.4 清除迷你圖 77

高手私房菜

技巧：打印工作表時不打印圖表 78

第3章 一樣的數據，不一樣的看法 79

3.1 數據源相關規範 80

3.2 一分鐘完成海量數據分析 80

3.3 認識數據透視表結構 82

3.4 從不同視角快速提煉數據 84

3.4.1 更換佈局區域 84

3.4.2 查看數據明細 86

3.4.3 設置值匯總依據 87

3.4.4 設置值顯示方式 87

3.4.5 按月統計數據 89

3.5 讓領導刮目相看的數據透視表 90

3.6 用數據透視表統計文本型數據 92

3.6.1 分析文本型數據的統計信息 93

3.6.2 按年齡進行分組統計 94

3.6.3 按指定順序排列 96

3.7 統計分析公司各部門的工資情況 98

3.8 創建動態透視表 101

3.9 創建數據透視圖 104

高手私房菜

技巧1：創建與刪除切片器 105

技巧2：篩選多個項目 106

第4章 千算萬算不如公式算 107

4.1 什麽是公式和函數 108

4.2 公式中的運算符 110

4.2.1 認識運算符 110

4.2.2 運算符的優先級 112

4.2.3 通過嵌套括號來改變運算符優先級 112

4.3 單元格引用 113

4.3.1 單元格地址的輸入方式 113

4.3.2 單元格引用的類型 113

4.3.3 引用自動更新 115

4.3.4 選擇正確的引用類型 116

高手私房菜

技巧1：Excel下方快速顯示統計數據 119

技巧2：引用其他工作表中的單元格或單元格區域 119

技巧3：引用其他工作簿中的單元格或單元格區域 120

第5章 小函數，大魔法 121

5.1 快而有效的函數學習方法 122

5.1.1 Excel函數輸入方法 122

5.1.2 善於借助幫助功能 122

5.1.3 不用背誦，也能“玩轉”函數 123

5.1.4 借助AI，輕松學會函數 123

5.2 認識函數 125

5.2.1 函數的概念及類型 125

5.2.2 函數的組成元素 125

5.2.3 認識函數參數 126

5.2.4 函數的輸入 128

5.2.5 函數的調試 129

5.2.6 函數錯誤檢查 130

5.2.7 顯示公式本身 133

5.3 邏輯函數 134

5.3.1 TRUE和FALSE函數 134

5.3.2 AND函數 135

5.3.3 OR函數 136

5.3.4 NOT函數 137

5.3.5 IF函數 137

5.4 統計函數 139

5.4.1 SUM函數 139

5.4.2 SUMIF函數 140

5.4.3 SUMIFS函數 141

5.4.4 COUNT函數 142

5.4.5 COUNTA函數 143

5.4.6 COUNTIF函數 144

5.4.7 AVERAGE函數 145

5.4.8 AVERAGEIF函數 145

5.4.9 MAX、MIN函數 146

5.5 查找與引用函數 146

5.5.1 ROW函數 146

5.5.2 VLOOKUP函數 147

5.5.3 LOOKUP函數 148

5.6 日期與時間函數 150

5.6.1 日期與時間的本質 150

5.6.2 常用日期函數 150

5.6.3 常用時間函數 151

5.6.4 工作日函數 152

5.7 財務函數 152

5.7.1 計算貸款、投資的現值和終值 152

5.7.2 FV函數 153

5.7.3 PV函數 154

5.7.4 PMT函數 155

5.7.5 PPMT、IPMT函數 155

5.7.6 CUMPRINC、CUMIPMT函數 156

5.7.7 SLN 函數 157

5.7.8 DDB函數 157

5.7.9 SYD函數 157

高手私房菜

技巧：函數的循環引用 158

第6章 數據整理的秘密 159

6.1 文本型數據與數值型數據的轉化 160

6.1.1 將文本型數據轉換為數值型數據 160

6.1.2 文本型數據與數值型數據的比較 160

6.1.3 文本型數據的計算 161

6.1.4 大量文本型數據與數值型數據的相互轉化 162

6.2 數據分列 163

6.2.1 數據分列的兩種方法 163

6.2.2 數據分列案例1：將文本型數據變為數值型數據 165

6.2.3 數據分列案例2：提取特定符號前的數據 167

6.3 查找，快速找到滿足條件的單元格 168

6.3.1 精確查找 168

6.3.2 找出以某個字/詞結尾的單元格 169

6.3.3 查找含有某個字且字數固定的單元格 170

6.3.4 找出包含某個字且帶有填充色的單元格 171

6.3.5 查找通配符本身 172

6.3.6 將包含特定文字的單元格內容替換為數字 173

6.3.7 查找值或公式 174

6.3.8 查找值並設置樣式 175

6.4 合並計算，將多個表的數據合並 176

6.4.1 合並計算案例1—建立分戶報表 176

6.4.2 合並計算案例2—分公司數據合並 177

6.4.3 合並計算案例3—快速合並多個結構相同的工作表 179

6.5 數據驗證，給數據把個關 182

6.5.1 製作下拉選項，限制輸入的類型 182

6.5.2 設置輸入提示信息 184

6.5.3 設置出錯警告 184

6.5.4 復制設置的數據驗證條件 185

6.5.5 圈釋無效數據 186

6.6 條件格式，讓重點數據更醒目 187

6.6.1 設置條件格式 187

6.6.2 新建規則 188

高手私房菜

技巧：管理條件格式 190

第7章 小數據，大智慧 191

7.1 排序，讓數據一目瞭然 192

7.1.1 使用簡單排列提高數據的易讀性 192

7.1.2 自定義排序，滿足個性需求 193

7.1.3 復雜排序，凸顯你的專業性 194

7.1.4 排序的別樣玩法—按顏色排序 195

7.2 篩選，快速找到需要的數據 195

7.2.1 按文字篩選 195

7.2.2 按數字篩選 196

7.2.3 按時間篩選 197

7.2.4 高級篩選 198

7.3 單變量求解：尋找盈虧臨界點 198

7.4 模擬運算表：計算不同情況下的結果 200

7.5 規劃求解：尋找利潤最大化的方案 202

高手私房菜

技巧1：每隔一行插入一個空行 204

技巧2：篩選的結果不一定可信怎麽辦 205