《疑難千尋千解叢書：Excel 2013 函式與公式》是2015年7月電子工業出版社出版的圖書，作者是黃朝陽 。
基本介紹
- 書名：疑難千尋千解叢書：Excel2013 函式與公式
- 作者：黃朝陽 主編 陳國良榮勝軍編著
- ISBN：978-7-121-26441-2
- 頁數：564頁
- 定價：89.00元
- 出版社：電子工業出版社
- 出版時間：2015年7月
- 裝幀：平裝
- 開本：16
內容簡介,內容提要,作者簡介,目錄,
內容簡介
《Excel 2013 函式與公式》是“疑難千尋千解”叢書之一，為讀者展示運用函式與公式解決疑難問題的實戰技巧，包括基礎理論、操作技巧、分類函式套用、綜合套用及開發實戰。《Excel 2013 函式與公式》將系統的知識點融入於各種實戰案例，在解決問題的同時，引導讀者學習深層次原理和擴展套用思路。閱讀《Excel 2013 函式與公式》，可以直接使用成熟的通用公式和案例，解決工作中的諸多疑難雜症，大大提升工作效率，且有助於提升函式與公式的理論水平和綜合實戰能力。
內容提要
《Excel 2013 函式與公式》是“疑難千尋千解”叢書之一，為讀者展示運用函式與公式解決疑難問題的實戰技巧，包括基礎理論、操作技巧、分類函式套用、綜合套用及開發實戰。《Excel 2013 函式與公式》將系統的知識點融入於各種實戰案例，在解決問題的同時，引導讀者學習深層次原理和擴展套用思路。閱讀《Excel 2013 函式與公式》，可以直接使用成熟的通用公式和案例，解決工作中的諸多疑難雜症，大大提升工作效率，且有助於提升函式與公式的理論水平和綜合實戰能力。
《Excel 2013 函式與公式》在函式語法描述中，採用中文表述習慣，對Excel 2013 幫助檔案進行了提煉和修正，幫助讀者在理解函式時少走彎路。
《Excel 2013 函式與公式》包括311 個案例，最後一章是職工信息、公文管理、工資管理系統3 個大型案例。案例採取疑難描述、解決方案、操作方法、原理分析、知識擴展5 個方面進行講解，力圖解決問題之外還讓讀者可以通曉其思路和原理。
作者簡介
黃朝陽，網名apolloh，ExcelTip.net站長，微軟全球最有價值專家（MVP）。擁有十多年豐富的企業信息化實踐和管理經驗，精通Excel和MS SQL在企業中的套用。早期曾在多個Office技術社區擔任版主，參與過多部Excel暢銷書的編著工作。
陳國良，網名gouweicao78，微軟全球最有價值專家（MVP），ExcelTip.net技術社區總版主，多個Office技術社區資深版主，從事公路、鐵路工程建設管理十多年，精通Excel函式與公式，具有豐富的實戰經驗，參與過多部Excel暢銷書的編著工作。
榮勝軍，網名rongjun，微軟全球最有價值專家（MVP），ExcelTip.net技術社區總版主，網路工程師，長期從事建築工程造價、工程管理工作，精通Excel函式與公式，具有豐富的實戰經驗，致力於專研Excel套用技術，提高工作效率。
目錄
第 1 章公式基礎知識··················································1
1.1 熟悉 Excel 的工作環境····················································1
疑難 1 如何減小功能區所占螢幕空間·········································1
Excel 2013 操作界面概覽···································2
疑難 2 如何不用設定公式實現統計結果預覽····································3
設定 Excel 2013 狀態欄··········································3
疑難 3 如何在一個工作簿中管理多張表格·······························4
理解工作簿、工作表和單元格之間的關係※························5
疑難 4 為什麼表格列標題顯示的是數字而不是字母·····························5
設定 R1C1 引用樣式············································6
A1 引用樣式和 R1C1 引用樣式····································6
疑難 5 如何根據開頭字母的提示快速輸入函式名稱·········6
開啟“公式記憶式鍵入”功能的兩種方法·······················7
疑難 6 如何不讓函式提示信息遮蓋到工作表的列號·······················7
函式螢幕提示工具的妙用·····································8
疑難 7 為什麼公式計算結果不會變化·······································9
設定自動或手動計算模式············································9
疑難 8 為什麼單元格中只顯示公式文本而不顯示計算結果··············10
檢查“顯示公式”選項狀態·······································10
檢查單元格是否為“文本”格式························10
疑難 9 單元格左上角的綠色小三角圖形是什麼含義·················11
錯誤檢查規則及含義※·················································12
使用錯誤檢查巧換文本形式的數字為數值·······························13
疑難 10 如何在新輸入的行中自動填充上一行的公式··························13
設定自動擴展公式功能································13
1.2 公式基礎概念···········································14
疑難 11 為什麼此“公式”不能根據圓的直徑求面積····························14
什麼是公式····································15
什麼是函式·················································15
公式的組成結構················································15
疑難 12 為什麼 IF 函式只有兩個參數也能計算···················16
必需參數與可選參數·············································16
省略參數與省略參數的值·································16
疑難 13 為什麼上百萬的網友算錯數學題 6÷2 × (1+2)·····················17
公式中運算符的類型及含義···································18
公式中運算符的計算優先權·····································18
疑難 14 為什麼開獎號末位判斷總是“大”······································19
Excel 的數據類型※··············································20
數據排列順序的規則·······································20
公式中文本形式數字如何轉換為數值······················20
疑難 15 為什麼數字與“空”單元格相加會出錯·························20
空文本與空單元格的區別································21
公式中的&""有什麼作·······················22
疑難 16 為什麼兩個時間相減得到一長串的#號····················22
產生一長串#號的原因分析及解決方法·································23
疑難 17 如何輸入編號 1-2、1/2 以及比分 1:3 ·······························23
日期、時間數據的表示方法·······························24
打開“設定單元格格式”對話框的 3 種方法····························24
疑難 18 如何快速規範以小數點間隔的偽日期····························25
查找替換法規範“偽日期”數據·····································25
數據分列法規範“偽日期”數據·····························26
根據使用習慣改變默認日期與時間格式·····························26
Excel 中兩位數字的年份··············································27
疑難 19 如何拯救小數表示的偽出生年月·······························28
用剪貼簿規範含有兩位小數的偽日期數據································28
疑難 20 如何實現輸入數字 1、2 後自動將其顯示為性別·················30
為單元格自定義數字格式·······································30
Excel 的數字格式種類············································31
常用數字格式代碼組成部分······································31
疑難 21 如何讓單位為元的金額顯示為萬元····························32
數字格式代碼及含義············································33
日期相關格式代碼·····································34
時間相關格式代碼········································34
數字格式代碼中的日曆和語言區域······································34
疑難 22 如何讓累計求和引用的區域隨公式向下複製變化·······················38
相對引用、絕對引用、混合引用····································39
疑難 23 不同表格相同表頭能否只填一處··································40
跨表引用單元格的表示方法······························41
疑難 24 為什麼身份證號碼 15 位以後的數字都顯示為 0 ···············42
Excel 的計算限制············································43
疑難 25 為什麼有時候匯總金額會比實際差 1 分錢····················44
浮點運算導致意外計算誤差······························45
疑難 26 為什麼即使沒有修改內容關閉表格時也會提示保存檔案·········45
哪些函式具有易失性···············································46
增加“撤銷”次數····················································46
疑難 27 為什麼在 Excel 2003 中打開公式會顯示_xlfn. 前綴······················47
Excel 2003 不支持的新增函式有哪些·····································48
疑難 28 為什麼用 Excel 2013 打開早期版本的檔案後只有 65536 行·············48
Excel 2013 支持哪些檔案格式··········································50
如何在早期 Excel 版本中打開、另外儲存為高版本檔案·······················50
1.3 公式常用操作技巧···············································50
疑難 29 如何選擇合適的函式來解決問題·······················51
如何搜尋函式·················································51
從分類中選擇所需函式···································52
函式的分類················································52
疑難 30 如何快速獲取函式的幫助信息··················53
獲取函式實時提示信息······································53
函式幫助檔案中有哪些信息····························54
疑難 31 如何快速將公式複製到其他單元格·································54
複製貼上公式方法·······························55
填充柄複製公式法································55
填充命令法複製公式············································56
多個單元格快速輸入相同公式································56
疑難 32 如何複製公式且保持相對引用地址不變····················57
用輔助工作表法保持單元格相對引用·································57
疑難 33 如何快速在多個合計行中填充求和公式································59
定位空單元格與批量求和···········································59
篩選可見單元格求和················································60
疑難 34 如何按照合併單元格求對應金額小計··························61
混合引用妙求合併單元格對應數據之和·····················62
計算合併單元格包含多少行···································62
合併與拆分單元格···············································62
疑難 35 如何讓合併單元格中的每個單元格都有內容················63
格式備份讓“合併”單元格都有內容··························64
格式合併與單元格合併的區別··········································65
疑難 36 如何查看公式分步運算結果以便找出錯誤··················66
分步查看運算結果·············································66
用【F9】鍵查看運算結果······························67
疑難 37 如何去掉報表中的公式只保留計算結果···························68
擇性貼上法保留公式計算結果······························68
貼上數值的 3 種效果··································69
斷開連結法去除公式······················69
疑難 38 如何允許填報數據又防止破壞報表中的公式······················70
保護含有公式的單元格·································71
如何隱藏公式···············································72
如何僅可選定需要填報的單元格···············72
疑難 39 如何不切換工作表監控不同區域變化情況··························72
使用監視視窗監控數據·············································73
神奇的單元格“照相機”···································74
疑難 40 如何讓新插入的行被原有公式引用···············75
自動計算新插入行數據····························76
1.4 數組與數組公式··················································· 77
疑難 41 如何不用單元格存儲個稅稅率關係表··············77
什麼是數組····················································78
數組的維度和尺寸································79
疑難 42 如何根據一組商品的單價與數量直接求總金額············79
數組多項運算原理··································79
疑難 43 為什麼運行公式後大括弧{}消失而計算結果出錯·············80
什麼是數組公式··········································81
如何在合併單元格輸入數組公式·····················81
為何建議不使用整列單元格創建數組公式····81
疑難 44 如何判斷多項運算是否需要使用數組公式·····························82
圖解數組公式與普通公式運算的差異·························82
【Ctrl+Shift+Enter】組合鍵對於數組公式的意義··············82
疑難 45 為什麼會出現“不能更改數組的某一部分”的提示···········83
輸入多單元格數組公式································84
為何使用多單元格數組公式··························84
疑難 46 為什麼相同公式在不同位置的計算結果不同·························85
什麼是絕對交集引用·············································85
數組的絕對交集···················································86
如何判別公式是否生成記憶體數組····························86
疑難 47 為什麼銷售業績與提成點數相乘出現#N/A 錯誤·······················87
數組間多項運算與數組維度、尺寸的關係·····················88
疑難 48 為什麼 AND、OR 函式不能返回多項邏輯判斷結果·····················89
多項運算需要邏輯判斷返回數組結果······························90
疑難 49 如何快速取得自然數等差數列·····························90
ROW 函式返回行號作為等差數列·······················91
ROWS 函式與 ROW 函式的區別······························91
獲取數字 0~9 數列····················································92
疑難 50 如何匯總連續多表相同單元格的銷售額················92
什麼是連續多表三維引用··············································93
疑難 51 為什麼 INDIRECT 函式引用多表相同單元格求和出錯··················94
什麼是函式產生的多維引用·······························94
為何使用 N 函式計算結果不一定正確························95
函式產生多維引用模型圖解································95
1.5 在公式中使用名稱···············································96
疑難 52 如何用漢字“稅率”代替常量數組·························96
為什麼要使用名稱·············································97
疑難 53 為什麼字母 C 不能作為名稱使用·································97
名稱的命名原則·············································98
疑難 54 如何快速將單元格區域定義為多個名稱·························98
使用名稱框快速定義名稱·······························99
使用“以選定區域創建名稱”功能批量定義名稱················99
疑難 55 如何在修改名稱中的引用位置時使用方向鍵··················100
切換編輯框中的點選與編輯模式······················100
疑難 56 如何在不同工作表定義表示不同數據的相同名稱·····················100
定義工作表級名稱················································101
工作簿級、工作表級名稱·····························102
疑難 57 如何在名稱中實現工作表標籤“相對引用”····················102
名稱中工作表的“相對引用”····················103
疑難 58 如何直接使用表格標題來引用數據························104
創建“表格”區域·····················································104
什麼是“表格”功能··················································105
什麼是“結構化引用”·················································105
疑難 59 如何讓圖片隨選擇的生肖自動變化····························106
使用名稱動態引用圖片················································106
疑難 60 如何讓列印區域隨數據輸入自動擴展····························107
使用名稱設定動態列印區域·····································108
疑難 61 如何在單元格中設定列印頂端標題行和左端標題列·················109
使用名稱在單元格中設定列印標題行和列······························109
疑難 62 如何快速刪除多個錯誤名稱······························111
使用名稱管理器篩選錯誤名稱·····································111
疑難 63 如何讓名稱隱藏起來·································112
使用 VBA 隱藏名稱·································112
1.6 練習與思考········································113
第 2 章邏輯判斷··································114
2.1 邏輯關係···············································114
疑難 64 如何判斷串聯、並聯、短路、雙控電路的連通狀態·························114
使用 IF 函式進行邏輯判斷···········································115
與、或、非、異或 4 種邏輯關係·························115
疑難 65 如何判斷購房是否需要提供個稅或社保證明························116
邏輯非關係的三種判斷方法········································117
疑難 66 如何判斷同時滿足購買經濟適用住房的 3 個條件··················117
邏輯值與數值轉換規則········································118
疑難 67 如何根據職工性別和職務判斷退休年齡························118
使用四則運算代替邏輯判斷············································119
2.2 多層判斷···································119
疑難 68 如何根據成績判斷優良中差等級·································119
多層 IF 函式嵌套的邏輯關係樹···················120
避免邏輯關係重複、遺漏、冗餘·························120
疑難 69 如何給金卡和銀卡客戶按消費額派發贈品·····························120
不同分支多層邏輯判斷············································121
多層級對應關係邏輯判斷問題變通處理··························122
2.3 常用條件設定··············································· 123
疑難 70 如何將公式返回的錯誤值轉為其他值·····················123
IS 類函式判斷禁止錯誤值通用公式···································124
IFERROR、IFNA 函式禁止錯誤值通用公式······························124
常見 7 種錯誤的產生原因及解決方案··························125
疑難 71 如何判斷必填欄位填寫是否完整····································127
判斷真空單元格····································127
疑難 72 如何判斷單元格內是否有公式並標識顏色····················128
使用 ISFORMULA 函式判斷單元格是否為公式···················129
使用宏表函式 GET.CELL 函式判斷單元格是否為公式············129
疑難 73 如何判斷某年是否為閏年·······························130
判斷閏年的 4 種解法··········································131
疑難 74 如何判斷開獎號碼之和的奇偶性···························131
數字奇偶性判斷··············································132
疑難 75 如何根據身份證號碼判斷性別··································133
取得身份證號中的性別數字······································133
判斷奇偶的函式限制···········································134
疑難 76 判斷數據是否存在重複現象··········································134
區分大小寫判斷重複數據·····································135
疑難 77 如何判斷一個字元是否漢字···························136
雙位元組法判斷字元是否為漢字··································136
與“吖”字比較判斷字元是否為漢字································136
Unicode 函式判斷字元是否為漢字····································137
2.4 練習與思考··········································137
第 3 章數據匯總·····················································138
3.1 匯總求和···········································138
疑難 78 如何匯總連續多個表中相同單元格區域的數據···················138
通配符在輸入連續多表三維引用中的套用····························139
疑難 79 如何匯總茶葉中鐵觀音的銷量···························139
SUMIF 函式單條件求和··································140
SUMIF 函式第 3 參數的簡寫形式··································140
在 SUMIF 函式中使用通配符和數組····························141
疑難 80 如何匯總月薪在 2000 至 4000 元的工資總額························141
SUMIFS 單列多條件求和···························141
其他單列多條件求和解法····································142
SUMIFS 與 SUMIF 函式的區別····························142
疑難 81 如何根據考評係數及評分計算員工的綜合得分·······················142
SUMPRODUCT 求數組乘積之和······································143
SUM 數組公式與 SUMPRODUCT 求數組乘積之和的區別···············143
疑難 82 如何避開單元格中的錯誤值求和······························144
使用 SUMIF 函式排錯求和············································144
疑難 83 如何驗算現金流量表的數據勾稽關係是否正確··············144
使用通配符進行條件求和········································145
疑難 84 如何求出某月各項支出中最大值之和·····························146
資料庫函式中使用數組求多列最大值································146
疑難 85 如何忽略隱藏的行匯總數據······································147
SUBTOTAL 函式對應功能及忽略不可見單元格特性······················147
疑難 86 如何忽略隱藏的列匯總數據···································148
忽略隱藏列求和··············································149
疑難 87 如何匯總固定間隔 n 行的數據··································149
MOD 函式構建間隔 n 行的循環················150
間隔 n 行數據求和通用公式································150
疑難 88 如何匯總某月數據······························150
按月匯總數據 ? ·······································151
疑難 89 如何匯總本科學歷男員工的工資總和································151
SUMIFS 函式多條件求和通用公式···················152
SUMPRODUCT 或 SUM 函式多條件求和通用公式·····················152
疑難 90 如何按月分別匯總每個銷售員的銷售額······························153
MMULT 函式雙條件求和··········································153
疑難 91 如何根據代碼等式求對應數值之和·······················154
根據代碼等式對應求和····························155
疑難 92 如何根據多個條件跨表匯總銷售數據·······················155
SUMIFS 函式多表多條件求和·····································156
3.2 數據計數························································· 157
疑難 93 如何統計成績表中的及格人數·················157
COUNTIF 函式常見條件統計用法·················158
常用特殊條件計數············································159
疑難 94 如何統計 35 歲以上有房有車的職工人數·················159
COUNTIFS 函式多條件計數······························159
SUMPRODUCT 或 SUM 函式多條件計數通用公式··························160
COUNT 函式多條件計數通用公式·····························161
疑難 95 如何統計甲車間生產的產品規格帶*號的產品種類···················161
包含通配符的條件計數··································162
疑難 96 如何統計不重複數據個數·········································162
1/COUNTIF 函式統計不重複值的優缺點······························163
MATCH=ROW 法統計不重複值個數的優缺點······················164
FREQUENCY 函式統計不重複值的優缺點·······················165
疑難 97 如何區分字母大小寫統計不重複數據個數····················165
用 1/MMULT 法求不重複值············································166
3.3 極值均值································· 166
疑難 98 如何設定提成獎金的上、下限·····································166
使用 MAX、MIN 函式設定上、下限通用公式···················167
使用 MEDIAN 函式設定上、下限通用公式·························167
使用 IF、TEXT 函式設定上、下限通用公式····················168
疑難 99 如何按先進先出法進行商品庫齡分析························168
混合引用與上下限解決先進先出問題通用公式····························170
疑難 100 如何標記體檢指標與參考範圍的高低關係·····················171
中值法判斷數值與上、下限範圍的關係·······················171
複數函式法判斷數值與上、下限範圍的關係··························172
