Microsoft Excel數據分析與商業建模（第7版)

第 1 章 Excel 基本模型 018
如何高效計算員工周薪？ .......................................................018
如何高效計算烘焙店各供應商貨款？ .............................................019
如何預測新開業健身房 10 年後客戶數量？ ........................................021
如何遵循 PEMDAS 原則正確編寫 Excel 公式？ ....................................023
如何瞭解咖啡店的咖啡售價及成本變化對利潤的影響？ .............................024

第 2 章 自定義名稱 025
如何創建自定義名稱？ .........................................................025
如何在公式中使用自定義名稱？ .................................................030
如何在公式中引用整列和整行？ .................................................031
工作簿範圍與工作表範圍的自定義名稱有何區別？ .................................032
如何將公式中的單元格地址改為自定義名稱？ .....................................033
如何快捷查看工作表中的所有自定義名稱及對應單元格地址？ .......................034
如何通過自定義名稱讓公式更易讀？ .............................................034

第 3 章 LOOKUP 函數 037
如何用公式基於稅率表查找稅率？ ...............................................038
如何根據產品代碼提取相應的產品價格？ .........................................040
對於售價定期變動的產品，如何根據銷售日期提取售價？ ...........................040
如何在 LOOKUP 函數中使用通配符？ ............................................041
強大的 XLOOKUP 函數如何使用？ ...............................................042
LOOKUP 函數混淆了文本和數值，怎麽辦？. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .045

第 4 章 INDEX 函數 046
如何通過函數計算兩個城市之間的距離？ .........................................046
如何通過一個公式引用整列或整行？ .............................................047

第 5 章 MATCH 函數 048
如何通過公式在銷售表中獲取指定產品在指定月份的銷量？ .........................049
如何通過公式在數據表中獲取某排名位置的數據？ .................................050
如何通過公式根據現金流測算項目的投資回收期？ .................................051
XLOOKUP 函數能否用於雙向查找？ .............................................052

第 6 章 文本函數和快速填充 053
文本函數語法結構 .............................................................053
如何將單元格中的多個信息拆分到獨立單元格中？ .................................059
如何從銷售數據求和公式中提取參與計算的各地區數據？ ...........................061
如何使用 Excel 分列功能提取單元格數據？ ........................................062
如何使用文本函數生成條形圖？ .................................................063
如何用文本函數處理不可見字符？ ...............................................063
如何使用快速填充功能實現快捷操作？ ...........................................064
什麽是 Unicode 字符？ .........................................................066
TEXTJOIN 函數與 CONCATENATE 函數或“＆”連接符相比做了哪些優化？ ..........067
如何用好 TEXT 函數？ .........................................................067

第 7 章 日期函數 069
日期類數據總是顯示為數字，該如何處理？ .......................................069
如何用公式獲取當前的日期？ ...................................................071
如何用公式獲取指定日期之後 50 個工作日的日期？ ................................071
如何計算兩個日期之間有多少個工作日？ .........................................073
如何從指定日期中提取年、月、日等信息？ .......................................074
如何將給定的年、月、日三個數字組合成一個日期？ ...............................075
如何計算兩個日期之間的完整年數、月數、天數？ .................................075
如何獲取靜態（不變）的當天日期？ .............................................076
如何確定某日期位於一年中的第幾周？ ...........................................076
如何獲取指定日期的上一個周日的日期？ .........................................077
如何獲取指定日期的下一個周一的日期？ .........................................078
如何獲取指定日期的當月首日和末日日期？ .......................................078
如何獲取指定日期在其他月的同日日期？ .........................................079

第 8 章 NPV 和 XNPV 函數 080
什麽是凈現值？ ...............................................................080
如何使用 NPV 函數？ ..........................................................082
對於年初或年中發生的現金流，該如何計算凈現值？ ...............................082
對於不定期發生的現金流，如何計算凈現值？ .....................................083

第 9 章 IRR、XIRR 及 MIRR 函數 086
如何計算投資項目的內部收益率？ ...............................................086
投資項目的內部收益率是否總是唯一？ ...........................................087
如何保證投資項目的內部收益率是唯一的？ .......................................088
如何比較兩個項目的內部收益率？ ...............................................089
現金流發生日期無規律，如何計算內部收益率？ ...................................089
什麽是修正內部收益率，如何計算？ .............................................090

第 10 章 財務函數 092
一次性付款和分期付款，哪個更劃算？ ...........................................092
如何計算年金賬戶在未來能支取多少錢？ .........................................094
如何計算分期償還貸款的每期還款額和總利息？ ...................................095
如何根據貸款本金、還款期數和每期金額推算能承受的貸款利率？ ...................098
如何根據貸款利率、貸款本金和每期金額推算還款總期數？ .........................099
能否使用 Excel 進行設備折舊計算？ ..............................................100
如何理解復利？ ...............................................................101

第 11 章 循環引用 103
循環引用是如何產生的？ .......................................................103
如何讓 Excel 允許循環引用？ ....................................................104

第 12 章 IF、IFERROR、IFS、CHOOSE 和 SWITCH 函數 106
某產品單價隨採購量改變，如何計算某採購量對應的金額？ .........................106
如何計算某投資組合的預期收益率？ .............................................107
如何構建模型檢驗移動平均線交易法則的效果？ ...................................109
如何構建公式判斷擲骰子游戲的輸贏？ ...........................................112
如何用 Excel 製作財務預算表？ ..................................................112
如何處理“#NA”錯誤值以便公式能正常計算？ ...................................117
IF 函數嵌套使用很復雜，如何使用 IFS 函數處理多重條件判斷？ .....................120
CHOOSE 函數如何使用？ .......................................................122
SWITCH 函數如何使用？ .......................................................123
Excel 有許多 IS 類函數，能講解一下嗎？ .........................................124

第 13 章 時間函數 127
如何在 Excel 中輸入時間數據？ ..................................................127
如何在同一個單元格中輸入時間和日期？ .........................................127
如何在 Excel 中進行有關時間的計算？ ............................................128
如何讓工作表始終顯示當前時間？ ...............................................129
如何使用 TIME 函數生成時間數值？ .............................................129
如何使用 TIMEVALUE 函數將文本字符串轉換為時間數值？ .........................130
如何從給定的時間值中提取時、分、秒的值？ .....................................130
如何根據起始時間和結束時間計算工時？ .........................................131
為什麽計算工時數之和時結果值不會超過 24 小時？ ................................131
如何快捷創建有規律的時間值序列？ .............................................132
如何在工作表中輸入當前時間的靜態值？ .........................................133

第 14 章 選擇性粘貼 134
如何將公式的計算結果粘貼到工作表的其他位置？ .................................134
如何將一列數據快捷轉換為一行數據？ ...........................................135
如何在粘貼數值時進行指定運算？ ...............................................136

第 15 章 跨工作表引用和超鏈接 139
如何在一個工作簿中跨工作表引用單元格？ .......................................139
如何在多個工作表之間快捷跳轉？ ...............................................141

第 16 章 公式審核工具和 Inquire 工具 144
Excel 的公式審核工具如何使用？ ................................................144
如何快速在眾多數據中弄清楚單元格的從屬關系？ .................................147
公式計算結果存疑，如何快捷查找問題源頭？ .....................................149
如何跨工作表或工作簿使用公式審核工具？ .......................................150
什麽是 Inquire 加載項，如何啟用？ ..............................................152
如何使用 Inquire 工具比較兩個工作簿？ ..........................................152
如何使用 Inquire 工具分析工作簿的結構？ ........................................153
如何使用 Inquire 工具分析工作表和工作簿之間的聯系？ ............................154
如何使用 Inquire 工具分析單元格之間的引用及從屬關系？ ..........................155
如何使用 Inquire 工具清除多餘的單元格格式？ ....................................155

第 17 章 敏感性分析與模擬運算表 156
如何分析售價和成本的變化對利潤、收入等的影響？ ...............................156
不確定貸款金額與貸款利率，如何推算每月還款額？ ...............................160
如何根據假設的收入和支出增長情況預測實現收支平衡的年數？ .....................161
如何根據模擬運算表創建圖表？ .................................................163

第 18 章 單變量求解 165
每年賣多少杯檸檬水才能實現收支平衡？ .........................................165
如何根據年利率、月還款金額計算能承受的最高貸款金額？ .........................166
Excel 能幫忙解數學應用題嗎？ ..................................................167

第 19 章 使用方案管理器進行敏感性分析 169
如何基於多個變量進行多方案的敏感性分析？ .....................................169

第 20 章 COUNTIF、COUNTIFS、COUNT、COUNTA 和
COUNTBLANK 函數 173
如何統計每位演唱者被點播的歌曲數？ ...........................................174
如何統計點播曲目中有多少首歌不是 Eminem 演唱的？ .............................175
如何統計有多少首歌的時長不少於 4 分鐘？ .......................................176
如何統計有多少首歌的時長超過平均值？ .........................................176
如何統計有多少首歌是由姓氏以 S 開頭的演唱者唱的？ .............................176
如何統計有多少首歌是由姓氏包含 6 個字母的演唱者唱的？ .........................176
如何統計有多少首歌是在 2005 年 6 月 15 日後被點播的？ ...........................176
如何統計有多少首歌是在 2009 年以前被點播的？ ..................................177
如何統計時長正好是 4 分鐘的歌曲數量？ .........................................177
如何統計由 Springsteen 演唱的、時長為 4 分鐘的歌曲數量？ .........................177
如何統計由 Madonna 演唱的、時長為 3 至 4 分鐘的歌曲數量？ .......................177
如何統計指定範圍內、內容為數值的單元格數量？ .................................178
如何統計指定範圍內空單元格的數量？ ...........................................178
如何統計指定範圍內非空單元格的數量？ .........................................178

第 21 章 SUMIF、AVERAGEIF、SUMIFS、
AVERAGEIFS、MAXIFS 和 MINIFS 函數 179
如何統計各銷售人員的銷售額？ .................................................181
如何統計總退貨數量？ .........................................................181
如何統計 2005 年以來的銷售額？ ................................................182
唇彩的銷售額及銷售數量各是多少？ .............................................182
如何統計某銷售人員之外的銷售人員的總銷售額？ .................................182
如何統計某銷售人員的客單量？ .................................................182
如何統計某銷售人員售出的口紅總金額？ .........................................182
如何統計某銷售人員售出的口紅平均每單成交量？ .................................183
單筆數量 50 支以上且由 Zaret 售出的口紅平均每單成交量是多少？ ...................183
單筆金額 100 元以上且由 Jen 售出的口紅總金額是多少？ ...........................183
Excel 可以根據多個條件求最大值和最小值嗎？ ....................................183

第 22 章 OFFSET 函數 185
如何通過指定偏移行數和列數引用單元格區域？ ...................................185
如何實現從右向左查找？ .......................................................186
OFFSET 和 MATCH 函數組合應用實例 ...........................................187
如何計算每種藥品在各研發階段的總成本？ .......................................188
如何將一個單元格內的文本內容拆分為多個信息？ .................................189
如何使用 Excel 的公式審核功能？ ................................................191
如何創建始終返回指定列中最後一個數值的公式？ .................................192
如何讓自定義名稱自動擴大範圍包含新增數據？ ...................................192
公司的月度銷售數量折線圖能自動根據新數據繪圖嗎？ .............................194

第 23 章 INDIRECT 函數 196
如何在不更改公式本身的情況下調整公式中的引用？ ...............................196
如何將多個工作表中的指定數據列在一張工作表中？ ...............................198
如何讓公式不隨插入操作自動擴展求值區域？ .....................................198
如何使用 INDIRECT 函數識別公式中的自定義名稱？ ...............................200
如何將多個工作表中的數據匯總到一個工作表中？ .................................201
如何快捷列出工作簿中的所有工作表名稱？ .......................................202
如何在工作簿中創建帶超鏈接的工作表目錄？ .....................................203

第 24 章 條件格式 205
如何直觀表現近些年的全球氣溫變化趨勢？ .......................................207
如何突出顯示特定的單元格？ ...................................................209
如何查看或編輯條件格式規則？ .................................................211
如何用數據條可視化展示單元格數值？ ...........................................212
負值對應的數據條如何展現？ ...................................................215
如何使用不同色階實現數據可視化？ .............................................216
如何使用圖標集實現數據可視化？ ...............................................218
如何用不同顏色標記股價的漲跌？ ...............................................221
如何用不同顏色標記營業額的環比增減？ .........................................224
如何用顏色突出顯示日期數據中的周末日期？ .....................................227
如何根據球員場上位置突出顯示其對應級別評分？ .................................229
【如果為真則停止】復選框有什麽作用？ .........................................231
如何使用格式刷復制條件格式？ .................................................233

第 25 章 表和切片器 234
如何實現在表中輸入新數據時自動應用樣式和公式？ ...............................234
如何讓圖表根據新增數據自動更新？ .............................................238
如何快捷根據指定條件匯總數據？ ...............................................239
如何使用切片器對數據進行篩選？ ...............................................242
如何在工作簿中快捷引用表的某部分數據？ .......................................244
條件格式是否能自動應用於添加到表中的新數據？ .................................246
復制包含表引用的公式為什麽會得到錯誤結果？ ...................................247

第 26 章 控件 249
如何通過按鈕調節參數值快捷查看公式計算結果？ .................................249
如何通過復選框控制條件格式的啟用或禁用？ .....................................253
如何通過單選按鈕選擇公式的輸入參數？ .........................................257
如何通過組合框和列表框快捷輸入數據？ .........................................258

第 27 章 從文本導入數據 260
如何導入文本類數據？ .........................................................260

第 28 章 Power Query 編輯器 266
如何將網頁中的數據導入 Excel ？ ................................................267
如何將二維表轉換為一維表？ ...................................................271
如何基於一維表創建二維表？ ...................................................273
如何補全表格中省略的重復數據？ ...............................................274
如何將一列中的兩種數據拆開？ .................................................275
如何將文件夾中的所有文件合並到單個文件中？ ...................................277
如何對表中符合條件的值批量進行指定運算？ .....................................279
如何將多個表的數據合並到一個表中？ ...........................................281

第 29 章 合並計算 284
如何合並多地區的銷售數據並統計各產品的總銷量？ ...............................284

第 30 章 直方圖和帕累托圖 288
如何創建直方圖？ .............................................................288
直方圖有哪些常見形態？ .......................................................294
如何通過對比直方圖分析數據？ .................................................297
如何創建帕累托圖？ ...........................................................298

第 31 章 描述統計 300
如何定義數據集的典型值？ .....................................................300
如何衡量數據的分佈情況？ .....................................................304
均值和樣本標準差能提供什麽信息？ .............................................305
如何使用描述統計指標分析數據集？ .............................................307
如何輕松找到數據集中指定百分位排名的值？ .....................................307
如何輕松找到數據集中第 2 大或第 2 小的值？ .....................................309
如何獲取某數值在數據集中的排名？ .............................................310
什麽是數據集的切尾均值？ .....................................................310
如何快捷獲取指定單元格區域中數據的統計信息？ .................................311
為什麽金融分析師常用幾何平均值衡量投資收益率？ ...............................311
如何使用箱形圖匯總和比較數據集？ .............................................312

第 32 章 排序 317
如何進行多條件排序？ .........................................................317
是否能根據單元格或字體的顏色進行排序？ .......................................320
可以依據條件格式圖標對數據進行排序嗎？ .......................................322
含中文數字的月份數據如何正確排序？ ...........................................322
如何不打開【排序】對話框對數據進行排序？ .....................................324
是否可以創建自定義列表實現拖動填充相應內容？ .................................325
是否可以按行排序？ ...........................................................326
是否可以按字母的大小寫進行排序？ .............................................327

第 33 章 數據篩選與重復值刪除 329
如何篩出某銷售人員在指定地區的某產品銷售記錄？ ...............................330
如何篩出多位銷售人員在多個地區的多個產品銷售記錄？ ...........................332
如何將篩選結果另存到其他工作表中？ ...........................................332
如何取消篩選？ ...............................................................333
如何根據數值的大小進行數據篩選？ .............................................333
如何根據年份信息進行數據篩選？ ...............................................335
如何根據月份信息進行數據篩選？ ...............................................337
如何根據文本內容進行數據篩選？ ...............................................337
如何根據單元格顏色進行數據篩選？ .............................................338
如何根據數值排名進行數據篩選？ ...............................................339
如何輕松刪除某列中的重復值？ .................................................340
數據變更後如何刷新篩選結果？ .................................................341
可以在不顯示篩選器圖標的情況下快速篩選數據嗎？ ...............................341
如何依據不連續的數值區間進行數據篩選？ .......................................343

第 34 章 數據集統計函數 345
如何統計某銷售人員某產品的總銷售額？ .........................................346
能否指定銷售人員、產品名稱和所屬地區計算平均銷量？ ...........................347
如何將滿足指定條件之一的值篩出來求和並匯總？ .................................347
如何統計多位銷售人員在指定地區的口紅總銷售額？ ...............................348
如何統計東部之外地區的口紅訂單數量？ .........................................348
如何按時間區間統計銷售額？ ...................................................348
如何統計平均售價高於某值的產品數量？ .........................................349
如何統計各銷售人員售出的各產品總銷售額？ .....................................349
設置數據集統計函數的條件時有什麽技巧嗎？ .....................................350
如何根據交易日期和產品代碼快速獲得相關的銷售額？ .............................351
如何從數據集中提取符合多個條件的記錄？ .......................................352

第 35 章 數組公式和函數 355
如何理解數組公式？ ...........................................................355
如何讓縱向排列的數據橫向顯示並能同步更新？ ...................................357
如何統計數據中各數值區間的值的數量並能同步更新統計結果？ .....................357
如何用一個公式計算一組整數的第 2 位數字之和？ .................................358
如何快捷查看在兩個名單中都出現過的姓名？ .....................................359
如何快捷計算數組中符合條件的數值的平均值？ ...................................360
如何使用數組函數對銷售數據進行統計和分析？ ...................................361
如何使用數組常量？ ...........................................................364
如何編輯數組公式？ ...........................................................365
如何使用數組公式進行分類匯總？ ...............................................365
如何基於多字段組合計算對應數據的標準差？ .....................................366
如何使用 SUMPRODUCT 函數進行條件計數、條件求和等計算？ ....................367

第 36 章 動態數組函數 371
如何使用 UNIQUE 函數從一組數據中提取唯一值列表？ ............................371
如何使用 SORT 和 SORTBY 函數對數據進行排序？ ................................374
如何使用 FILTER 函數對數據進行篩選？. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .376
如何使用 SEQUENCE 函數生成連續的編號？ ......................................378

第 37 章 數據驗證 381
如何設置輸入的數值的取值範圍？ ...............................................381
如何避免把今年的日期輸錯為去年的？ ...........................................383
如何避免在輸入數字時錯誤地輸入其他字符？ .....................................384
如何通過從列表中選擇選項來減少手工錄入數據導致的錯誤？ .......................385
能否使用動態數組函數創建嵌套下拉菜單？ .......................................386

第 38 章 數據透視表和切片器 389
如何使用數據透視表匯總多家商店的銷售記錄？ ...................................389
Microsoft 365 提供了哪些數據透視表報表佈局？ ...................................394
數據透視表這個名稱的由來是什麽？ .............................................396
如何輕松更改數據透視表的設計？ ...............................................396
如何輕松更改數據透視表中字段的格式？ .........................................396
如何折疊或展開字段？ .........................................................397
如何對數據透視表的字段進行排序？ .............................................398
如何對數據透視表的數據進行篩選？ .............................................400
如何使用數據透視圖展示數據透視表數據？ .......................................403
如何使用數據透視表的篩選功能？ ...............................................404
如何使用數據透視表的切片器？ .................................................405
如何調整數據透視表的外觀和樣式？ .............................................406
如何在數據透視表中應用條件格式？ .............................................407
如何更新數據透視表的計算結果？ ...............................................409
如何對不同人群的旅游消費數據進行分析？ .......................................409
如何通過數據分析確定影響消費者購買商品的因素？ ...............................414
如何將銷售數據匯總為直觀且易於理解的表格？ ...................................416
什麽是計算字段？ .............................................................419
如何使用數據透視表的篩選器和切片器？ .........................................421
如何對數據透視表中的項目進行分組？ ...........................................422
什麽是計算項？ ...............................................................423
什麽是向下鑽取？ .............................................................426
如何用公式從數據透視表中提取指定的匯總值？ ...................................426
如何使用日程表功能匯總不同時間段的數據？ .....................................429
如何使用數據透視表匯總年度累計數據？ .........................................431
如何使用數據透視表進行同比分析？ .............................................431
如何基於保存在不同位置的數據創建數據透視表？ .................................433
如何基於現有數據透視表新建數據透視表？ .......................................436
如何使用報表篩選器創建多個數據透視表？ .......................................437
如何快捷更改數據透視表的默認設置？ ...........................................438
如何使用數據透視表統計投資收益？ .............................................438
能否使用動態數組函數創建能自動更新的數據透視表？ .............................440

第 39 章 數據模型 442
如何將已有數據添加到數據模型？ ...............................................443
如何在數據模型中創建關系？ ...................................................444
如何使用數據模型創建數據透視表？ .............................................445
如何向數據模型添加新數據？ ...................................................446
如何編輯、停用或刪除數據模型中的關系？ .......................................447
如何不重復計數？ .............................................................448

第 40 章 Power Pivot 450
如何將數據導入 Power Pivot ？ ..................................................451
如何使用 Power Pivot 創建數據透視表？ ..........................................456
如何在 Power Pivot 中使用切片器？ ..............................................458
什麽是 DAX 函數和計算列？ ....................................................459
如何使用 RELATED 函數？ .....................................................461
如何使用 CALCULATE 函數？什麽是度量值？ ....................................467