Excel 2016公式與函式套用大全

《Excel 2016公式與函式套用大全》是2017年機械工業出版社出版的圖書，作者諾立教育。

基本介紹

• 書名：Excel 2016公式與函式套用大全
• 作者：諾立教育
• 出版社：機械工業出版社
• 出版時間：2017年

基本信息

作者：諾立教育 編著

ISBN(書號)：978-7-111-56209-2

出版日期：2017-04

版次：1/1

開本：16

定價：￥89.00

內容簡介

本書精選了最常用的函式，結合實例進行詳細講解，並且介紹了公式和函式相關基礎知識以及函式在職場各個領域的具體使用方法。不但可以幫助讀者學習和掌握Excel函式的使用技巧，而且是一本全面的函式實際套用查詢手冊。讀者在工作時，若有不明白的函式，只需要翻開本書查閱相關的講解即可。
本書共20章，前6章介紹公式和函式操作基礎，分別是公式基礎、函式基礎、單元格引用、數組的套用、公式的審核與修正、分析與解決公式返回錯誤值；中間9章介紹了9類函式的實際套用，包括邏輯函式、日期和時間函式、數學與三角函式、文本函式、查找與引用函式、信息函式、統計函式、財務函式、資料庫函式；最後5章結合實際介紹了函式在職場各個領域的套用，包括員工檔案管理、員工考勤管理、員工薪資管理、公司銷售管理、公司固定資產管理。
本書內容全面、結構清晰、語言簡練，不僅適合正在學習函式使用方法的讀者閱讀，而且特別適合作為數據分析人員、財務人員、統計人員、行政人員及教師的案頭手冊。

圖書目錄

第1章 公式基礎
1.1 認識公式2
1.1.1 公式的組成2
實戰實例：根據銷售額和銷售單價計算總銷售額2
1.1.2 公式中運算符的使用3
實戰實例：計算每月總銷售額3
1.1.3 公式中“?”“*”通配符的使用4
實戰實例：統計風衣類服裝的總銷售額4
1.2 公式輸入與修改5
1.2.1 公式輸入5
實戰實例：統計產品總銷售額5
1.2.2 公式修改6
實戰實例：重新計算每月總銷售額6
1.3 公式複製與填充7
1.3.1 公式複製7
實戰實例：統計考核總成績8
1.3.2 公式填充8
實戰實例：統計每一季度的總銷售量9
1.4 公式保護、隱藏與顯示10
1.4.1 公式保護10
實戰實例：保護工作表中的公式10
1.4.2 公式隱藏12
實戰實例：隱藏工作表中的公式12
1.4.3 公式顯示14
實戰實例：顯示工作表中所有的公式14
1.5 改變公式的計算方式15
1.5.1 將公式結果轉化為數值15
實戰實例：將公式結果轉化為數值15
1.5.2 將公式轉換為文本16
實戰實例：將公式轉換為文本16
1.6 為什麼公式正確卻得到不正確的結果17

第2章 函式基礎

2.1 認識函式19

2.1.1 函式的組成19

實戰實例：計算每位學生的總分19

2.1.2 Excel函式類別20

實戰實例：判斷各銷售員月銷量是否達標20

2.2 函式輸入21

2.2.1 手動輸入21

實戰實例：統計員工平均工資22

2.2.2 使用“fx”按鈕輸入23

實戰實例：使用“fx”按鈕輸入公式23

2.2.3 使用“函式自動完成”功能輸入24

實戰實例：使用“函式自動完成”功能輸入24

2.2.4 使用“插入函式”功能輸入26

實戰實例：使用“插入函式”功能輸入26

2.3 函式修改與刪除27

2.3.1 雙擊公式單元格修改函式27

實戰實例：雙擊公式單元格修改函式27

2.3.2 在“編輯欄”中修改函式28

實戰實例：在“編輯欄”中修改函式28

2.3.3 使用單元格引用顏色標識來修改引用單元格29

實戰實例：拖動顏色標識修改公式29

2.3.4 刪除公式中不要的函式30

實戰實例：刪除公式中不要的函式31

2.4 嵌套函式31

實戰實例1：根據平均銷量判斷業績突出銷售員32

實戰實例2：查找指定消費者是否發放獎品33

第3章 單元格的引用

3.1 名稱定義和使用35

3.1.1 快速定義名稱35

實戰實例1：使用“定義名稱”功能定義名稱35

實戰實例2：在名稱框中直接創建名稱36

3.1.2 修改名稱37

實戰實例：定義名稱之後修改其引用位置37

3.1.3 編輯名稱38

實戰實例：編輯名稱38

3.1.4 刪除名稱38

實戰實例：刪除不再使用的名稱39

3.1.5 定義公式名稱39

實戰實例：定義公式名稱39

3.1.6 創建動態名稱40

實戰實例：創建動態名稱40

3.1.7 套用名稱42

實戰實例：在公式中套用名稱42

3.2 單元格引用43

3.2.1 相對引用43

實戰實例：在公式中引用相對數據源43

3.2.2 絕對引用44

實戰實例：在公式中引用絕對數據源44

3.2.3 混合引用45

實戰實例：在公式中引用混合數據源45

3.2.4 引用當前工作表之外的單元格46

實戰實例：引用當前工作表之外的單元格46

3.2.5 跨工作簿引用47

實戰實例：跨工作簿引用單元格47

3.2.6 引用多個工作表中的同一單元格48

實戰實例：引用多個工作表中的同一單元格48

3.2.7 使用【F4】鍵互換引用類型49

實戰實例：使用【F4】鍵互換引用類型50

第4章 數組的套用

4.1 數組類型52

4.1.1 一維水平數組52

實戰實例：一維水平數組52

4.1.2 一維垂直數組53

實戰實例：一維垂直數組53

4.1.3 二維數組53

實戰實例：二維數組54

4.1.4 常量數組在公式中的套用54

實戰實例：常量數組在公式中的套用54

4.1.5 記憶體數組55

實戰實例：記憶體數組55

4.2 使用數組公式56

4.2.1 普通公式與數組公式56

實戰實例：使用數組公式計算銷售額56

4.2.2 單個單元格數組公式與多個單元格數組公式57

實戰實例1：使用數組公式計算1到100的自然數之和57

實戰實例2：一次性計算總分58

4.3 多項計算與數組公式的區別58

實戰實例1：根據銷量和銷售單價計算總銷售額59

實戰實例2：統計總銷售金額59

4.4 修改或刪除數組公式60

實戰實例：修改數組公式60

第5章 公式審核與修正

5.1 公式檢測與審核63

5.1.1 使用“監視視窗”監視數據63

實戰實例：使用“監視視窗”監視數據63

5.1.2 使用“錯誤檢查”來檢查公式65

實戰實例：使用“錯誤檢查”來檢查公式65

5.1.3 使用“追蹤錯誤”來追蹤公式錯誤66

實戰實例：使用“追蹤錯誤”來追蹤公式錯誤66

5.1.4 使用“追蹤引用單元格”66

實戰實例：使用“追蹤引用單元格”66

5.1.5 使用“追蹤從屬單元格”67

實戰實例：使用“追蹤從屬單元格”68

5.1.6 移去追蹤箭頭68

實戰實例：移去追蹤箭頭68

5.2 錯誤公式的修正69

5.2.1 使用“顯示公式”來修正69

實戰實例：使用“顯示公式”來修正69

5.2.2 使用“公式求值”來查看公式結果70

實戰實例：使用“公式求值”來查看公式結果70

5.2.3 使用【F9】鍵查看公式中部分公式的結果71

實戰實例：使用【F9】鍵查看公式中部分公式的結果72

5.2.4 修正循環引用不能計算的公式 72

實戰實例：修正循環引用不能計算的公式72

5.2.5 修正公式輸入的參數不對74

實戰實例：修正公式輸入的參數不對74

5.2.6 修正輸入的公式中包含錯誤75

實戰實例：修正輸入的公式中包含錯誤75

5.2.7 修正公式複製後得不到正確結果 75

實戰實例：修正公式複製後得不到正確結果75

5.2.8 修正按順序書寫的公式不按順序計算77

實戰實例：修正按順序書寫的公式不按順序計算77

5.2.9 修正四捨五入後計算結果不正確78

實戰實例：修正四捨五入後計算結果不正確78

5.2.10 修正小數計算結果出錯79

實戰實例：修正小數計算結果出錯79

第6章 分析與解決公式返回錯誤值

6.1 分析與解決 “####”錯誤值81

實戰實例：解決“####”錯誤值81

6.2 分析與解決“#DIV/O！”錯誤值82

實戰實例：解決 “#DIV/O！”錯誤值82

6.3 分析與解決“#N/A” 錯誤值83

實戰實例1：解決“#N/A”錯誤值83

實戰實例2：數組公式中使用的參數的行數或列數與包含數組公式的區域的行數或列數不一致84

6.4 分析與解決“#NAME?” 錯誤值84

實戰實例1：在公式中引用文本時沒有加雙引號85

實戰實例2：解決“#NAME?”錯誤值85

實戰實例3：區域引用中漏掉了冒號“：”86

6.5 分析與解決“#NUM!” 錯誤值的解87

實戰實例：解決 “#NUM！”錯誤值87

6.6 分析與解決“#VALUE!” 錯誤值88

實戰實例1：在公式中將文本類型的數據參與了數值運算88

實戰實例2：解決 “#VALUE!”錯誤值89

6.7 分析與解決“#REF!” 錯誤值89

實戰實例：解決“#REF!”錯誤值90

6.8 分析與解決“#NULL!”錯誤值91

實戰實例：解決“#NULL!”錯誤值91

第7章 邏輯函式

7.1 邏輯判斷函式93

7.1.1 AND：判斷指定的多個條件是否全部成立93

實戰實例1：判斷面試人員是否能被錄用93

實戰實例2：判斷是否為消費者發放贈品94

7.1.2 OR：判斷參數值是否有一個 為TRUE 95

實戰實例1：判斷是否為員工發放獎金95

實戰實例2：判斷是否為消費者發放贈品96

7.1.3 NOT：判斷指定的條件不成立97

實戰實例1：判斷員工月業績是否達標97

實戰實例2：篩選出20歲以下的應聘人員98

7.1.4 IF：根據邏輯測試值返回指定值99

實戰實例1：判斷員工本月業績是否優秀99

實戰實例2：分區間判斷業績並返回不同結果100

實戰實例3：判斷能夠獲得公司年終福利的員工101

實戰實例4：篩選出符合條件的員工 102

實戰實例5：判斷數據是否存在重複現象103

實戰實例6：根據所有銷售員總銷量計算單人銷量是否達標103

7.1.5 IFERROR：根據錯誤值返回指定值104

實戰實例：解決被除數為空值（或0值）時返回錯誤值問題105

7.2 邏輯值函式105

7.2.1 TRUE: 返回邏輯值TRUE106

實戰實例：判斷產品是否達標（達標的顯示出TRUE）106

7.2.2 FALSE：返回邏輯值FLASE 107

實戰實例：判斷產品是否達標（不達標的顯示出FALSE）107

第8章 日期與時間函式

8.1 返回當前日期和時間109

8.1.1 TODAY: 返回當前日期109

實戰實例1：統計實習員工的工作天數109

實戰實例2：判斷會員是否升級110

8.1.2 NOW: 返回當前的日期和時間111

實戰實例：計算活動剩餘時間111

8.2 用序列號表示或計算時間和日期112

8.2.1 YEAR: 返回某日對應的年份112

實戰實例：計算員工工齡112

8.2.2 DATE: 返回特定的日期和序列號114

實戰實例1：將分列記錄的日期轉換為標準格式日期114

實戰實例2：統計公司產品的促銷天數115

8.2.3 DATEVALUE：將日期值從字元串轉換為序列號116

實戰實例：統計借書天數116

8.2.4 DAY：返回以序列號表示的日期中的天數117

實戰實例1：提取給定日期中的日數117

實戰實例2：按本月缺勤天數計算缺勤扣款118

8.2.5 TIME：返回某一時間的特定小數值119

實戰實例：將給定的數值轉化為12小時制119

8.2.6 TIMEVALUE：返回由文本字元串 所代表的小數值120

實戰實例：將指定時間轉化為時間小數值120

8.2.7 EOMONTH：從序列號或文本中算出指定月最後一天的序列號120

實戰實例1：統計各月份對應的月末日期120

實戰實例2：根據促銷開始時間計算促銷天數122

8.2.8 MONTH：返回以序列號表示的日期中的月份123

實戰實例1：自動填寫報表中月份123

實戰實例2：統計指定月份的銷售額123

8.2.9 WEEKDAY：返回指定日期對應的星期數124

實戰實例1：判斷值班日期是星期幾125

實戰實例2：統計周日營業額125

8.2.10 WORKDAY：從序列號或文本中計算出指定工作日之後的日期126

實戰實例：計算交稿日期127

8.2.11 EDATE：計算出所制定月數之前或之後的日期127

實戰實例：快速計算食品過期日期128

8.2.12 HOUR：返回時間值的小時數128

實戰實例：確定點擊某網站的時間區間129

8.2.13 MINUTE：返回時間值的分鐘數130

實戰實例：計算產品秒殺分鐘數130

8.2.14 SECOND：返回時間值的秒數131

實戰實例：計算產品秒殺的秒數131

8.3 期間差132

8.3.1 DAYS360：返回兩日期間相差的天數（按照一年360天的算法）132

實戰實例1：計算賬齡133

實戰實例2：計算還款剩餘天數133

8.3.2 NETWORKDAYS：計算某時段的工作日天數134

實戰實例：計算員工退休前的工作天數134

8.3.3 YEARFRAC：從開始到結束日所經過的天數占全年天數的比例135

實戰實例：計算全年的盈利額135

8.3.4 DATEDIF：用指定的單位計算起始日和結束日之間的間隔數136

實戰實例1：計算員工工齡137

實戰實例2：計算各分店開業至今的時長137

實戰實例3：動態生日提醒138

8.4 其他日期與時間函式139

8.4.1 WEEKNUM：返回序列號對應的一年中的第幾周139

實戰實例：返回各項活動是全年中的第幾周139

8.4.2 DATESTRING：將指定日期的序列號轉換為文本日期140

實戰實例：將指定日期的序列號轉換為中文格式的日期140

第9章 數學與三角函式

9.1 計算函式142

9.1.1 SUM：求和142

實戰實例1：統計費用支出總額142

實戰實例2：統計總營業額143

實戰實例3：按月份統計銷售額144

9.1.2 MOD：求餘144

實戰實例：對數據進行取餘數145

9.1.3 SUMIF：根據指定條件對若干單元格求和145

實戰實例1：統計各倉庫的出庫總量145

實戰實例2：統計某個時段的銷售業績總金額146

實戰實例3：按部門統計銷售業績之和147

實戰實例4：用通配符對某一類數據求和148

實戰實例5：用通配符求所有車間人員的工資和149

實戰實例6：對產量大於1500的記錄進行求和150

9.1.4 SUMIFS：對區域中滿足多個條件的單元格求和150

實戰實例1：統計某一日期區域的銷售總金額151

實戰實例2：統計指定店面中指定品牌的銷售總金額152

9.1.5 GCD：求最大公約數152

實戰實例：求兩個或多個整數的最大公約數 153

9.1.6 LCM：求最低公倍數153

實戰實例：求兩個或多個整數的最低公倍數154

9.1.7 PRODUCT：求所有參數的乘積155

實戰實例：計算指定數值的階乘 155

9.1.8 SUMSQ：求參數的平方和155

實戰實例：計算平方和156

9.1.9 QUOTIENT：求除法的整除數156

實戰實例：對數據進行取整157

9.1.10 SUMX2MY2：求兩數組中對應數值的平方差之和157

實戰實例：計算數值的平方差之和157

9.1.11 SUMX2PY2：求兩數組中對應數值的平方和之和158

實戰實例：計算數值的平方和之和 158

9.1.12 SUMXMY2：求兩數組中對應數值差的平均和159

實戰實例：計算數值之差的平方和159

9.1.13 SUMPRODUCT：求數組間對應的元素乘積的和160

實戰實例1：計算多產品數量與金額乘積並計算總金額 160

實戰實例2：計算商品的折後金額 160

實戰實例3：統計指定班級中大於指定分值的人數161

實戰實例4：統計非工作日消費金額162

實戰實例5：統計各店鋪的總銷售金額163

9.1.14 SUBTOTAL：求數據列表或資料庫中的分類匯總164

實戰實例1：統計平均銷售金額 164

實戰實例2：統計最高銷售金額165

9.1.15 ABS：求出相應數值或引用單元格中數值的絕對值166

實戰實例1：求兩地溫差166

實戰實例2：比較銷售員上月與本月銷售額167

9.1.16 SIGN：求數值的符號168

實戰實例：返回指定數值對應的符號168

9.2 零數處理函式 169

9.2.1 INT：返回實數向下取整後的整數值169

實戰實例：對平均銷售量取整 169

9.2.2 TRUNC：將數字的小數部分截去，返回整數170

實戰實例：匯總金額只保留一位小數170

9.2.3 ROUND：按指定位數對數值四捨五入171

實戰實例1：按指定位數對數值四捨五入171

實戰實例2：隨機獲取參賽人員編號172

實戰實例3：以1個百分點為單位計算獎金或扣款172

9.2.4 MROUND：按照指定基數的倍數對參數四捨五入173

實戰實例：求解指定倍數的數值173