Excel 2010函式與公式實戰技巧精粹

內容簡介

《實戰技巧精粹：Excel2010函式與公式（含光碟）》分為5篇26章，函式導讀篇介紹了函式與公式基礎知識、數組公式入門、名稱的使用等姜妹雅茅內容；函式常用技巧篇介紹了邏輯與信息函式、文本處理函式、數學計算函式、日期與時間函式、查找與引用函式、統計分析函式、資料庫函式、財務金融函式、工程函式、宏表函式的常用技巧；函式綜合套用技巧篇介紹了循環引用、數字及文本處理技術、條件篩選技術、數據排名與排序技術、數據重構技巧、數據表處理技巧、處理日期數據、多維引用技術等內容；其他功能中的函式套用篇介紹了條件格式中使用函式、數據有效性中使用函式、數據透視表函式綜合套用、圖形製作中的函式套用等內容；實例作證篇列舉了多個Excel函式與公式服務於企業實際套用的實例。

《實戰技巧精粹：Excel2010函式與公式（含光碟）》內容豐富、圖文並茂、和廈影可操作性強且便於查閱，能有效地幫助讀者提高Excel 2010的函式與公式使用水平，提升工作效率。

《實戰技巧精粹：Excel2010函式與公式（含光碟）》主要面向Excel 2010中高級讀者，對於初級讀者也有一定的參考價值。

圖書目錄

緒論　最佳Excel學習方法　1

1　成為Excel高手的捷徑　1

1.1　積極的心態　1

1.2　正確的學習方法　2

1.3　善用資源，學以致用　4

1.4　多閱讀，婚敬組多實踐　5

2　通過網際網路獲取學習資源和解題方法　5

2.1　搜尋引擎的使用　6

2.2　搜尋業區域網路站的內容　7

2.3　在新聞組或BBS中學習　7

2.4　在視頻網站中學習　10

2.5　利用微博和微信學習　10

3　影奔函式與公式學習指南　11

3.1　打好函式基礎　11

3.2　由淺入深，由簡到繁學好函式公式　11

3.3　學好函式公式的基本方法　11

3.4　厚積薄發、脫胎換骨　12

3.5　學以致用，自由馳騁　12

第一篇　函式導讀　13

第 1章　函式與公式基礎介紹　14

技巧1　什麼是公式　14

技巧2　運算符的類型及用途　14

技巧3　公式的運算順序　15

技巧4　透視“單元格引用”　16

4.1　A1引用　16

4.2　R1C1引用　17

4.3　引用運算符　17

4.4　相對引用　18

4.5　絕對引用　19

技巧5　靈活處理跨工作表、工作簿引用　21

5.1　跨表引用　21

5.2　跨工作簿引用　21

技巧6　公式的槓擊和快速批量複製　22

技巧7　公式的自動擴展　23

技巧8　公式中的結構化引用　24

8.1　直觀的結構化引用　24

8.2　自動擴展　26

技巧9　慧眼識函式　26

技巧10　函式的類型　28

技巧11　Excel 2010函式的新特性　29

技巧12　函式參數的省略與簡寫　30

技巧13　使用通配符進行模糊匹配　32

技巧14　通配符在多表引用中的妙用　33

技巧15　認識公式中的數據　34

15.1　數據類型　34

15.2　數據排序規則　35

15.3　常量數據　35

技巧16　文本型數字轉換為數值　35

16.1　利用查錯工具批量轉換　36

16.2　文本型數字轉數值的公式方法　36

技巧17　邏輯值與數值的互換　38

17.1　邏輯值與數值互換準則　38

17.2　用數學運算替代邏輯函式　39

技巧18　正確區分空文本與空單元格　40

18.1 空單元格與空文本的差異　40

18.2　防止空單元格影響統計結果　40

18.3　讓空單元格不顯示為0　41

技巧19　計算規範與限制　42

19.1　數字計算精度限制　42

19.2　函式嵌套層數限制　43

19.3　函式參數個數的限制　44

技巧20　以顯示的精度進行計算　44

技巧21　易失性函式　45

21.1　什麼是易失性　45

21.2　具有易失性表現的函式　45

21.3　易失性的利用與規避　46

技巧22　自動重算和手動重算　47

技巧23　循環引用和疊代計算　47

23.1　記錄操作時間　48

23.2　已知和值求組合　49

23.3　For…Next循環　50

23.4　Do…While循環　52

技巧24　自動完成公式　52

技巧25　妙用函式提示　53

25.1　提示函式語法　54

25.2　參數含義訂察艱盛提示　54

25.3　快速選定相應參數　55

25.4　獲取函式試擔殃幫助信息　55

技巧26　公式的查錯　55

26.1　公式常見錯誤類型　55

26.2　錯誤自動檢查　57

技巧27　公式的審核和監控　58

27.1　錯誤檢查　58

27.2　審核和監控　58

技巧28　分步查看公式結果　59

28.1　使用公式審核工具分步求值　60

28.2　用F9鍵查看公式運算結果　61

技巧29　如何閱讀和理解複雜公式　61

第 2章　數組公式入門　65

技巧30　數組的概念及分類　65

技巧31　數組的維度和尺寸　65

技巧32　多項計算　66

技巧33　數組公式　67

技巧34　多單元格數組公式　68

技巧35　數組的直接運算　69

35.1　數組與單值直接運算　69

35.2　同方向一維數組之間的直接運算　69

35.3　不同方向一維數組之間的直接運算　70

35.4　一維數組與二維數組之間的直接運算　70

35.5　二維數組之間的直接運算　71

技巧36　數組公式中的邏輯運算　72

第3章　靈活使用名稱　74

技巧37　名稱的奧秘　74

技巧38　多種方法定義名稱　76

38.1　使用“定義名稱”功能　76

38.2　使用名稱框創建　77

38.3　根據所選內容批量創建名稱　78

技巧39　名稱的規範與限制　80

技巧40　通過名稱定位區域　80

技巧41　以圖形方式查看名稱　81

技巧42　查看和修改名稱　82

42.1　導出名稱列表　82

42.2　編輯修改名稱　83

42.3　刪除名稱　83

技巧43　認識名稱的作用範圍　84

技巧44　根據單元格顏色進行統計　85

技巧45　固定引用區域的名稱　86

技巧46　使用名稱動態引用圖片　87

技巧47　在其他功能中使用名稱　89

47.1　在條件格式和有效性中使用他表數據　89

47.2　在圖表中使用名稱　91

技巧48　創建動態引用名稱　91

技巧49　自動更新的列印區域　92

第二篇　函式常用技巧　95

第4章　邏輯與信息函式　96

技巧50　IF函式評定業務員等級　96

技巧51　邏輯值在條件匯總中的套用　97

51.1　判斷員工是否符合退休條件　97

51.2　邏輯函式與乘法、加法運算　98

技巧52　數據類型的判斷檢驗　99

52.1　禁止公式返回的錯誤值　100

52.2　有錯誤值的數據求和　101

技巧53　利用ISNUMBER函式模糊匯總　101

技巧54　根據身份證號碼判斷性別　102

技巧55　設定自動更新的檔案標題　103

技巧56　製作方便核對數據的聚光燈　104

技巧57　為公式添加註解信息　106

第5章　文本處理函式　107

技巧58　意義非凡的文本連線符&　107

技巧59　處理文本的基本手段　108

59.1　文本截取　108

59.2　查找定位　109

59.3　文本替換　110

59.4　文本比較　112

59.5　文本重複　112

技巧60　處理字元的基本技巧　112

60.1　字元和數字代碼　112

60.2　字母大小寫轉換　113

60.3　字元全半角轉換　114

60.4　刪除多餘的空格　114

60.5　處理非列印字元　115

技巧61　生成字母序列ABC……　115

61.1　CHAR+CODE方式　115

61.2　列標提取方式　116

技巧62　生成可換行的文本　117

技巧63　規整不規範的日期數據　118

63.1　MID函式文本截取法　118

63.2　TEXT函式方式　119

技巧64　巧用身份證填寫個人信息　120

64.1　提取性別　120

64.2　出生日期　121

64.3　年齡　122

技巧65　財務數字金額拆分實例　122

技巧66　分離中文、數字和字母　124

66.1　分離中文與數字　124

66.2　字母與中文　125

66.3　提取數字　125

技巧67　統計不重複數字個數　126

技巧68　根據固定間隔符號截取字元串　127

68.1　FIND+SUBSTITUTE　127

68.2　展寬隔離符號　128

技巧69　根據間隔符統計項目個數　128

技巧70　格式化設備編號　129

技巧71　數字格式化為中文數字　130

71.1　數字整體轉換　130

71.2　逐字元轉換　131

71.3　日期格式　131

技巧72　轉換中文大寫金額　132

技巧73　以小時累計時間　132

技巧74　合併單元格區域中的文本　133

第6章　數學計算函式　135

技巧75　基本的數學計算函式　135

75.1　求和與求積　135

75.2　乘冪與對數　136

75.3　組合數與階乘　137

75.4　**小公倍數和最大公約數　137

75.5　進制轉換函式　138

75.6　絕對值和π值　138

75.7　弧度轉換為度數　139

75.8　符號函式　139

技巧76　基本取整函式　139

技巧77　指定數字位捨入　140

技巧78　特定基數取整　141

技巧79　新增的基數取整函式　142

技巧80　捨入到奇數和偶數　143

技巧81　特定條件的捨入設計　143

81.1　設計一舍二入方式　143

81.2　0.2單位和0.5單位捨入　144

技巧82　四捨六入五單雙　144

技巧83　指定有效數字　145

技巧84　初識求模函式　146

技巧85　判斷數字奇偶性　147

技巧86　生成循環序列　148

技巧87　隨機產生數字與字母　149

87.1　初識隨機函式　149

87.2　隨機產生數字與字母　150

技巧88　生成不重複隨機序列　150

88.1　輔助列方式　151

88.2　直接方式　151

第7章　日期與時間函式　153

技巧89　認識日期與時間數據　153

89.1　日期與時間數據的本質　153

89.2　日期與時間數據的識別　153

89.3　輸入日期時間的注意事項　154

技巧90　基本的日期函式　154

90.1　日期生成函式　154

90.2　年、月、日函式　155

90.3　當前日期　156

90.4　星期相關函式　156

技巧91　基本的工作日相關函式　156

技巧92　增強的工作日相關函式　157

技巧93　基本的時間函式　158

93.1　時間生成函式　158

93.2　時、分、秒　159

93.3　當前時間　160

技巧94　處理1900年之前的日期　160

技巧95　判斷閏年和平年　161

技巧97　快速確定員工的退休日期　162

技巧98　判斷一個日期所在的季度　163

技巧99　推算所在季度的季末日期　164

技巧100　計算日期所在季度天數　165

技巧101　計算指定日期鄰近月份的天數　165

技巧102　動態生日提醒　166

技巧103　計算公司員工的工齡　167

103.1　以年為單位　167

技巧104　身份證中提取生日與周歲信息　169

技巧105　目標日期是所在年第幾天　171

105.1　點對點相減　171

105.2　日期值序列　171

技巧106　計算兩個日期相差的年月日數　171

技巧107　下一個星期日和**近的星期日　172

107.1　日期值序列思路　172

107.2　星期幾的歸零思路　173

技巧108　指定日期範圍內星期幾齣現的次數　173

技巧109　推算指定月份第幾個星期幾的日期　174

技巧110　日期A從日期B算起是第幾周　175

技巧111　計算指定月份第幾周的天數　176

技巧112　根據停車時間計算費用　177

112.1　計費時間　177

112.2　每小時費用　178

112.3　總費用　178

技巧113　將時間轉換成標準格式　178

技巧114　三班倒工作時長計算　179

技巧115　在時間日期數據中提取時間或日期　180

115.1　提取日期數據　180

115.2　提取時間數據　180

技巧116　生成指定範圍的隨機時間　181

116.1　以分為單位隨機變化　181

116.2　以秒為單位隨機變化　181

第8章　查找與引用函式　182

技巧117　認識INDIRECT函式　182

技巧118　恆定的引用區域　183

技巧119　輕鬆調用名稱和“表”數據　183

技巧120　返回指定時間段的銷售額　184

技巧121　生成自然數序列　185

技巧122　將單列轉換為多行多列　186

技巧123　認識OFFSET函式　186

技巧124　OFFSET函式偏移方式　187

技巧125　以數組為參數產生多維引用　189

技巧126　計算**近N天的平均銷售額　189

技巧127　使用OFFSET函式生成工資條　190

技巧128　使用CHOOSE函式完成等級考評　191

技巧129　使用INDEX函式隔列取值　192

技巧130　二維數據表中查詢數據　192

技巧131　不重複姓名提取　194

技巧132　認識VLOOKUP函式和HLOOKUP函式　195

技巧133　正向查詢員工信息　195

技巧134　逆向查詢員工信息　196

技巧135　提成比例查詢　197

技巧136　認識LOOKUP函式　197

技巧137　根據客戶簡稱查詢全稱　198

技巧138　有合併單元格的數據查詢　199

技巧139　計算各營業部銷售總額　200

技巧140　創建有超連結的工作表　201

第9章　統計分析函式　202

技巧141　統計銷量前N名的平均銷售額　202

技巧142　求解條件均值　203

技巧143　設定社保繳費基數上下限　204

技巧144　統計各部門職工學歷情況　205

技巧145　多表統計人員工資補助　206

技巧146　統計有效調查問卷　207

技巧147　了解COUNTIF函式條件判斷　208

技巧148　統計不同區間的對應人數　209

技巧149　多條件計數套用　209

技巧150　統計區域中不重複值個數　210

技巧151　檢查重複身份證號　210

技巧152　COUNTIFS函式多條件計數　211

技巧153　SUMPRODUCT函式分類統計　212

技巧154　模糊條件統計業務量　214

技巧155　SUMIF函式求和計算　214

技巧156　SUMIF函式計算商品結存　215

技巧157　條件求和與多條件求和　216

技巧158　不同幣種統計匯總銷售額　217

技巧159　SUMIFS函式多欄位多條件求和　217

技巧160　SUMIFS函式多表多條件求和　218

技巧161　分段統計銷售業績　219

技巧162　製作銷售數據匯總表　221

162.1　銷售額去除重複後由高到低列示　221

162.2　根據銷售額完成情況，列示對應業務員姓名　222

技巧163　統計銷售業績排名　222

163.1　部門內部業績排名　222

163.2　全公司業績排名　223

技巧164　使用RANK.AVG函式排名　223

技巧165　計算百分位排名　224

技巧166　計算剔除極值後的平均值　225

技巧167　篩選、隱藏狀態下的計算　225

技巧168　篩選條件下生成連續序號　227

技巧169　有錯誤值的統計　227

技巧170　統計出現頻率最高的值　229

170.1　數值統計眾數　229

170.2　統計得票最高人員　230

技巧171　統計篩選狀態下的不重複值　230

第 10章　資料庫函式　232

技巧172　資料庫函式的用途和用法　232

技巧173　利用通配符查詢產品信息　234

技巧174　在條件中使用公式　235

技巧176　資料庫函式的批量使用　239

技巧177　使用資料庫函式進行查詢　240

第 11章　財務金融函式　243

技巧178　普通複利終值與現值計算　243

178.1　普通複利終值計算　243

178.2　普通複利現值計算　243

技巧179　年金終值與現值計算　244

179.1　年金終值計算　244

179.2　年金現值計算　244

技巧180　固定利率下混合現金流的終值計算　245

技巧181　變動利率下混合現金流的終值計算　246

技巧182　固定資產投資的動態回收期計算　246

技巧183　現金流不定期條件下決策分析　247

183.1　計算淨現值　248

183.2　計算內部收益率　248

技巧184　名義利率與實際利率　250

184.1　將名義利率轉為實際利率　250

184.2　將實際利率轉為名義利率　250

技巧185　債券發行價格的計算　251

技巧186　每年付息債券的持有收益率計算　252

技巧187　折舊計算函式　252

187.1　用直線法計算固定資產折舊　252

187.2　用年數總和法計算固定資產折舊　253

187.3　用雙倍餘額遞減法計算固定資產折舊　254

第 12章　工程函式　257

技巧188　貝塞爾(Bessel)函式　257

技巧189　度量衡轉換函式　258

技巧190　數字進制轉換函式　259

技巧191　用於篩選數據的函式　260

191.1　DLETA函式　261

191.2　GESTEP函式　262

技巧192　誤差函式相關計算的函式　263

192.1　ERF函式　263

192.2　ERFC函式　263

技巧193　複數計算的函式　264

第 13章　宏表函式　265

技巧194　認識宏表及宏表函式　265

194.1　什麼是宏表　265

194.2　建立自己的宏表　265

194.3　如何讓宏表程式工作　266

194.4　認識宏表函式　268

194.5　在Excel中觸發重新計算的條件及解決方法　269

技巧195　取指定路徑下所有檔案名稱　269

技巧196　判斷是否打開了指定的工作簿　270

技巧197　取得當前工作簿中所有工作表名稱　271

技巧198　取得當前行所在的列印頁碼　271

技巧199　獲取單元格屬性值　272

技巧200　利用分級顯示的級數來計算提成量　273

技巧201　提取單元格數字格式進行計算　274

技巧202　計算文本表達式的結果　274

技巧203　將文本字元串變成常量數組　276

技巧204　按背景色統計　277

第三篇　函式綜合套用技巧　279

第 14章　循環引用　280

技巧205　認識循環引用　280

技巧206　記錄單元格操作時間　281

技巧207　循環引用計算的一般技巧　282

技巧208　利用循環引用實現字元串連線　283

第 15章　數字及文本處理技術　284

技巧209　提取文本中的數值　284

209.1　基礎知識　284

209.2　當文本中只有一處數值時提取數值　284

209.3　當文本中只有一處數值時提取數值文本　285

209.4　當文本中有多段數值時提取某段數值　286

技巧210　提取文本中所有數字按原序排列　286

技巧211　提取文本中不重複數字按升序排列　287

技巧212　數值格式化修約　288

技巧213　插值計算　288

技巧214　提取帶有固定分割符文本中的一項內容　289

技巧215　提取含有某些特性的文本　290

215.1　基礎知識　290

215.2　只有一段單位元組字元文本的提取　291

215.3　多段文本取其中一段　292

第 16章　條件篩選技術　293

技巧216　一列數據提取不重複數據　293

216.1　方法一　293

216.2　方法二　294

技巧217　二維表提取不重複值　294

技巧222　根據鐵路里程表篩選里程　299

技巧223　用先進先出法計算出貨價值　299

第 17章　數據排名與排序技術　301

技巧224　對一列數據進行中式排名　301

技巧225　有條件的中式排名　302

技巧226　按排名占比確定等級　303

技巧227　對數值大小進行排序　303

227.1　方法一　303

227.2　方法二　304

技巧228　對文本進行排序　304

228.1　MMULT函式法　304

228.2　COUNTIF函式法　305

技巧229　按自定義順序排序　306

技巧230　按數據出現次數排序　307

第 18章　數據重構技巧　308

技巧231　數字的按位與或非　308

技巧232　利用MMULT函式實現數據累加　309

技巧233　幾個典型構造矩陣的套用　309

233.1　行倒置　310

233.2　列倒置　310

233.3　全倒置　311

233.4　兩次複製　311

233.5　插入空行　311

技巧234　計算最大值中的最小值　312

技巧235　填充合併單元格　313

技巧236　文本型一維記憶體數組倒置　314

第 19章　數據表處理技巧　315

技巧237　對數據總表的模糊查詢　315

237.1　一次性顯示　315

237.2　分頁顯示　316

238.1　SUMPRODUCT或SUM函式方式　318

238.2　SUMIFS函式方式　319

技巧239　按客戶名對送貨清單進行分組　319

技巧240　將分表數據合併到總表　321

技巧241　將總表數據拆分到分表　322

第 20章　處理日期數據　324

技巧242　轉換中文日期為規範日期　324

技巧243　將標準日期轉為英文日期　325

技巧244　推算母親節和父親節　326

技巧245　製作簡易萬年曆　327

245.1　普通公式　327

245.2　數組公式　328

技巧246　陽曆與農曆之間的轉換　328

246.1　陽曆轉換為農曆　329

246.2　農曆轉換為陽曆　330

246.3　直接使用比對表　331

技巧247　推測某年度二十四節氣的具體日期　332

技巧248　推算陽曆日期所屬生肖年　333

248.1　由陽曆日期返回對應生肖年份　333

248.2　由生肖年份返回對應的生肖　334

技巧249　年月日時的天干地支　334

249.1　乾支紀年　334

249.2　乾支紀月　335

249.3　乾支紀日　337

249.4　乾支紀時　337

第 21章　多維引用技術　339

技巧250　計算應收款賬齡　339

技巧251　統計行銷任務完成的員工人數　340

技巧252　多表多條件匯總技術　342

技巧253　創建動態適應篩選條件的統計公式　343

技巧254　求取各小組最高分的總和　344

第四篇　其他功能中的函式套用　347

第 22章　條件格式中使用函式　348

技巧255　突出顯示總分最高的學生　348

技巧256　標示三個月銷售量均在90以上的員工　349

技巧257　按憑證號分色顯示　351

技巧258　日程安排提醒　352

技巧259　製作動態表格線　353

技巧260　突出顯示包含關鍵字的記錄　354

技巧261　動態標示計畫完成情況　356

技巧262　按模糊條件標示符合條件的記錄　358

技巧263　複雜條件下的條件格式設定　360

技巧264　在包含錯誤值的數據區域中標示特定值　361

第 23章　數據有效性中使用函式　363

技巧265　限制輸入數據的類型　363

265.1　只能輸入整數　363

265.2　只能輸入日期　365

265.3　只能輸入文本　365

265.4　只能輸入數值　367

技巧266　限制重複錄入　367

技巧267　只能輸入身份證號碼　368

技巧268　根據庫存量自適應的限制範圍　369

技巧269　使用公式設定下拉選單式輸入　371

技巧270　設定二級下拉選單　374

技巧271　根據輸入內容動態更新下拉選項　376

第 24章　數據透視表函式綜合套用　378

技巧272　初識數據透視表函式　378

技巧273　自動匯總方法下靜態獲取數據透視表數值　380

技巧274　自動匯總方法下動態獲取數據透視表數值　382

技巧275　自定義匯總方法下獲取數據透視表數據　384

技巧276　特殊的Excel 2000版數據透視表函式　386

技巧277　使用Excel 2000版數據透視表函式公式靜態獲取數據　387

技巧278　使用Excel 2000版數據透視表函式公式動態獲取數據　388

技巧279　數據透視表函式與其他函式聯合使用　390

技巧280　同時引用多個欄位進行計算　392

技巧281　從多個數據透視表中獲取數據　393

技巧283　套用數據透視表函式根據關鍵字取值　396

技巧284　套用數據透視表函式製作進貨單　397

技巧285　計算分類百分比　399

第 25章　圖形製作中的函式套用　402

技巧286　突出顯示極值　402

技巧287　製作可排序的動態條形圖　404

技巧288　高亮顯示游標選定數據　407

技巧289　製作動態滾動曲線圖　408

第五篇　實例作證　413

第 26章　經典實例　414

技巧290　計畫排產表格　414

技巧291　判斷火車是否晚點　415

技巧292　汽車裡程修理費補貼計算　416

技巧293　速算個人所得稅　418

技巧294　提示豐富的萬年曆　419

294.1　豐富的提示信息　420

294.2　條件格式凸顯特殊日期　422

294.3　顯示生肖圖像　423

294.4　輪訓日期的天干地支　424

294.5　當月的二十四節氣　425

技巧295　巧妙提取缺失的發票單號　425

技巧296　加油站投資項目動態盈虧平衡分析　427

技巧297　製作會計三欄賬　432

技巧298　按最新價格編制出庫商品統計表　439

技巧299　綜合計算進貨商品平均單價　440

技巧300　製作保險繳費數據匯總表　442

附錄　444

附錄A　Excel 2010限制和規範　445

附錄B　Excel 2010常用快捷鍵　448

附錄C　Excel 2010常用函式　452

Excel 2010函式與公式實戰技巧精粹

基本介紹

內容簡介

圖書目錄

相關詞條

熱門詞條