內容簡介
包括基礎理論、操作技巧、分類函式套用、綜合套用及開發實戰，本書將系統的知識點融入於各種實戰案例，在解決問題的同時，引導讀者學習深層次原理和擴展套用思路。閱讀本書，可以直接使用成熟的通用公式和案例，解決工作中的諸多疑難雜症，大大提升工作效率，且有助於提升函式與公式的理論水平和綜合實戰能力。
目錄
第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
FREQUENCY函式統計不重複值的優缺點 165
疑難97 如何區分字母大小寫統計不重複數據個數 165
用1/MMULT法求不重複值 166
3.3 極值均值 166
疑難98 如何設定提成獎金的上、下限 166
使用MAX、MIN函式設定上限或下限 167
使用MEDIAN函式設定上、下限 167
使用IF、TEXT函式設定上、下限通用公式 168
疑難99 如何按先進先出法進行商品庫齡分析 168
疑難100 如何標記體檢指標與參考範圍的高低關係 171
中值法判斷數值與上、下限範圍的關係 171
複數函式法判斷數值與上、下限範圍的關係 172
疑難101 如何進行線性插值計算 172
使用TREND函式線性插值計算 173
疑難102 如何不計重複返回一組數中的次大、次小值 174
提取不計重複的次大、次小值 174
疑難103 如何計算一組數據中大於某數的最小值 175
提取大於平均值的最小值 175
疑難104 如何統計前10名成績的平均分 176
計算前10名成績平均分 177
疑難105 如何剔除一個最大值和一個最小值後求平均值 177
計算去掉n個最大值和n個最小值後的均值通用公式 178
疑難106 如何根據條件計算平均值 178
使用AVERAGEIF函式單條件求均值 179
AVERAGEIFS函式計算條件均值 180
疑難107 如何計算加權平均值 181
計算加權平均值 181
疑難108 如何計算各條生產線最高工時的平均值 182
SUBTOTAL函式+多維引用對合併單元格計數 182
配權除權法提取多個最大值 182
3.4 頻率分布 183
疑難109 如何獲取一組數值中出現頻率最多的數據 183
提取多個出現頻率最高的數值 184
疑難110 如何根據分數段統計成績分布情況 184
FREQUENCY函式統計分段分布頻率 185
疑難111 如何統計彩票號碼最大連續出現次數 186
計算連續出現頻率通用公式 187
疑難112 如何判斷是否存在連續5次超標的數據 187
COUNTIF+OFFSET多維引用計算連續超標次數 188
3.5 排名排序 188
疑難113 如何實現多列成績統一排名 188
RANK函式多區域數據排名 189
新舊RANK函式差異 189
疑難114 如何進行跨工作表數據總排名 189
RANK函式連續多表三維引用求排名 190
疑難115 如何在年級成績總表中統計學生的班級排名 190
成績總表中班級排名的4種算法 190
疑難116 如何讓並列排名統計只占一個名次 192
中國式排名的3種算法 193
疑難117 如何對智育德育成績按比例折算後排名 194
多科目折算比例排名 194
疑難118 如何根據足球比賽成績進行多關鍵字排名 195
加權算法多關鍵字排名技術 196
疑難119 如何統計中學生運動會獎牌榜排名並按升序排序 196
多關鍵字升、降序排列通用公式 197
疑難120 如何提取員工信息並按照姓名升序排序 198
文本排序 199
疑難121 如何統計參賽選手的最後得分並按降序排序 200
多組成績按剔除極值後均值排名 201
3.6 練習與思考 201
第4章 查找與引用 202
4.1 查詢定位 202
疑難122 如何定位記錄在數據中首次出現的位置 202
MATCH函式定位查找 203
MATCH函式定位與MIN+IF數組公式定位的區別 204
疑難123 如何在升序排列的數據中查找近似匹配值 204
VLOOKUP函式首列查找對應結果 205
LOOKUP函式近似匹配查詢 206
疑難124 如何根據父子級別關係計算物料數量 207
VLOOKUP函式精確匹配查詢 207
疑難125 如何根據輸入工號自動顯示員工基本信息 208
VLOOKUP+MATCH函式組合交叉雙條件精確匹配查詢 208
HLOOKUP+MATCH函式組合交叉雙條件精確匹配查詢 209
引用函式與MATCH組合的交叉雙條件精確匹配查詢 209
疑難126 如何根據品名和規格雙列條件查詢物品單價 209
INDEX+MATCH函式組合雙列條件精確匹配查詢通用公式 210
VLOOKUP+IF雙列條件精確匹配查詢 212
疑難127 如何查找某行或某列最後一個非空單元格數據 212
LOOKUP精確匹配查詢的通用公式 213
LOOKUP返回最後一個文本或數值 213
疑難128 如何根據物料編號查找最新價格 214
倒數法查找多列第一個數值 214
文本通配符法查找多列第一個數值 215
疑難129 如何根據基準單元格偏移引用數據 216
多行多列數據轉換為一列 216
OFFSET函式的偏移引用 216
疑難130 如何根據供貨商名稱跨表查詢 217
將代表單元格地址的文本轉換為引用 218
疑難131 如何用函式實現行列內容轉置 218
獲取轉置後的數據 219
疑難132 如何始終引用上一行數據而不受刪除行的影響 219
始終引用上一行單元格 220
疑難133 如何按條件篩選提取數據記錄 220
篩選滿足條件記錄列表的通用數組公式 221
疑難134 如何提取滿足條件的多行多列數據並放到同一列中 222
多行多列篩選滿足條件記錄列表通用數組公式 223
疑難135 如何將滿足條件的多條記錄放在一個單元格 223
合併滿足條件的多個查詢結果 224
疑難136 如何將一個單元格內多個“欄位”與內容進行分離 225
VLOOKUP單列查找妙用 226
VLOOKUP單列查找線上實時查詢JSON格式股票數據 226
4.2 超級連結 227
疑難137 如何跳轉到最後一行數據下方的空單元格 227
設定動態跳轉超連結 227
疑難138 如何在Excel中用關鍵字搜尋網頁 228
在公式中使用常用搜尋引擎 228
疑難139 如何跳轉到查詢的結果 229
跳轉到滿足條件的查詢結果 230
疑難140 如何使用函式超連結打開帶#號的檔案 230
新增ENCODEURL函式轉換URL編碼 231
4.3 篩選重複 231
疑難141 如何列出某列數據的不重複記錄 231
MATCH=ROW方法提取不重複值 232
MIN+COUNTIF=0方法提取不重複值 232
疑難142 如何列出多行多列單元格區域中的不重複記錄 234
多行多列數據提取不重複值 234
疑難143 如何列出自動篩選後的不重複姓名 235
自動篩選結果中的不重複值提取 235
疑難144 找出A列有而B列沒有的數據並剔除重複值 236
篩選A列有而B列沒有的記錄 237
疑難145 如何提取連續出現次數最多的數據記錄 238
LOOKUP判斷不連續斷點的套用 238
疑難146 如何剔除重複值並按數據出現頻率降序排序 239
篩選滿足條件記錄並排序通用公式 240
4.4 Web查詢 240
疑難147 如何讀取XML字元串中的數據 241
FILTERXML函式讀取XML指定路徑數據 242
XML格式文檔結構 243
XPath表達式簡介 243
疑難148 如何線上翻譯單元格中的內容 244
獲取XML格式的有道翻譯結果 244
WEBSERVICE函式獲取Web服務數據 245
疑難149 如何線上查詢貨幣匯率 246
運用API工具線上查詢 247
線上轉換漢字為拼音 248
線上查詢手機號碼歸屬地 248
疑難150 如何線上查詢中國氣象網天氣情況 248
線上查詢天氣情況 249
4.5 練習與思考 251
第5章 文本處理 252
5.1 截取與連線 252
疑難151 如何將連續單元格區域的字元串合併起來 252
合併連續單元格區域的字元串 253
字元串連線兩種通用方法 253
疑難152 如何提取混合字元串中的連續漢字 254
根據單雙位元組截取連續漢字 254
截取字元串左側或右側的連續漢字 255
疑難153 如何讓公式結果可以換行顯示 256
換行符在公式中的套用 256
疑難154 如何將字元串按特定分隔設定分列 257
函式分列法通用公式 257
函式分列法截取子串長度臨界值的推算 258
5.2 查找與替換 259
疑難155 如何剔除姓名信息中的空格 259
替換法刪除空格字元 259
疑難156 如何替換文本中的字元 260
批量將指定字元替換為其他字元 261
疑難157 如何判斷字元串中是否包含某個字元 261
FIND函式判斷字元串包含子字元串的4種方法 262
替換法判斷包含字元 263
COUNTIF函式判斷包含字元 263
疑難158 如何查找某字元在字元串中第N次出現的位置 263
使用替換法查詢第N次出現的字元 264
疑難159 如何提取包含特定字元的所有記錄 264
提取包含指定字元的記錄 265
疑難160 如何計算包含字元的個數 265
使用替換法統計字元個數 266
疑難161 如何在特定位置插入字元串 267
使用替換零個字元的方法在字元串中插入新字元 267
5.3 比較與轉換 268
疑難162 如何改變英文字母大小寫 268
改變字母大小寫的3個函式 269
疑難163 如何對比兩個文本大小寫是否一致 269
區分大小寫比較文本 270
疑難164 如何轉換單雙位元組字元 270
轉換單、雙位元組字元的兩個函式 271
疑難165 如何查詢漢字區位碼 271
漢字區位碼的推算方法 272
疑難166 如何製作漢字Unicode編碼表 273
5.4 數字變幻 275
疑難167 如何加密手機號碼 275
替換指定位置和長度的字元 275
疑難168 如何升級電話號碼位數 275
根據條件升級電話號碼 276
疑難169 如何將數字接上英文序數後綴 277
數字轉換為帶英文序數後綴的文本 277
數字轉換為英文序數添加後綴的規則 278
疑難170 如何批量修改IP位址格式 278
用函式分列法規範IP位址格式 279
疑難171 如何為數組設定上、下限 279
為數組設定上、下限的通用公式 280
疑難172 如何將阿拉伯數字轉為人民幣大寫金額 280
將數字轉為人民幣大寫金額 281
中文大寫金額的填寫規則 281
疑難173 如何將金額分列填充到各個級位 282
金額分列填充 282
疑難174 如何合併計算商品庫存數量剩餘幾箱幾支 283
“X箱+Y支”格式的數據匯總 284
疑難175 如何根據圖書目錄的大綱級別製作分級編碼 284
根據不同列按級別進行編碼 285
疑難176 如何根據圖書目錄編碼製作分級顯示 286
根據不同級別編碼分列顯示 286
5.5 練習與思考 287
第6章 日期與時間處理 288
6.1 日期推算 288
疑難177 如何獲取日期的年月日要素 288
獲取日期的年月日 289
疑難178 如何推算某個日期所在的月首和月末日期 290
DATE函式返回月首月末日期 290
EOMONTH函式計算月首月末日期 291
疑難179 如何推算某個日期所在季度的季首季末日期 291
計算季度首、末日期 292
疑難180 如何計算兩個日期相隔的年、月、天數 293
DATEDIF函式計算日期間隔時長 293
疑難181 如何推算與某個日期相隔數月的日期 294
EDATE與DATE函式推算數月之後的日期差異 294
疑難182 如何推算職工的退休日期 295
根據出生日期推算退休日期 295
疑難183 如何判斷某個日期在第幾季度 296
推算日期歸屬季度 296
疑難184 如何判斷某個日期在上旬、中旬還是下旬 297
判斷日期上旬、中旬、下旬的3種解法 298
疑難185 如何將M/D/YYYY類型的偽日期轉換成規範日期 298
規範月日年類型的偽日期 298
疑難186 如何實現生日提醒功能 299
實現生日提醒 299
疑難187 如何按某年份的季度分攤費用 299
設定日期上的、下限，計算時段歸屬 300
疑難188 如何根據出生日期得到星座 300
根據生日查詢星座 301
疑難189 如何根據公曆日期返回農曆生肖 301
根據公曆生日查詢農曆生肖 302
6.2 時間計算 303
疑難190 如何獲取時間的時、分、秒要素 303
從時間數據中提取時、分、秒要素 303
疑難191 如何返回超過24小時的時間 304
計算超過24小時的時長 304
疑難192 如何將十進制數時間轉換為標準時間格式 304
轉換十進制數字為標準時間 305
轉換用小數表示分鐘的“偽時間” 305
疑難193 如何提取時間中的毫秒數 306
提取時間數據中的毫秒 306
疑難194 如何計算加班的小時數 306
計算加班小時數 307
疑難195 如何計算列車運行時長 307
跨零點計算兩站點列車運行時間 308
疑難196 如何統計設備開啟後在某個月度中的運行時長 308
設定數組上、下限在跨時段統計中的套用 309
6.3 星期與工作日 310
疑難197 如何判斷某個日期是否為周末 310
MOD函式判斷星期幾 310
WEEKDAY函式判斷星期幾 311
疑難198如何計算離職員工最後一個月的實際工作天數 311
NETWORKDAYS.INTL函式計算任意工作日 312
疑難199 如何計算兩個日期間星期日的天數 313
計算某時段內星期幾的天數 314
疑難200 如何計算某月按周末單雙號休息的休假天數 314
構建某月日期系列的3種方法 315
疑難201 如何根據購銷契約簽訂日期推算交貨日期 315
疑難202 如何推算某個日期處於當年中的第幾周 316
計算指定日期的周數 317
ISO周數 317
疑難203 如何推算某年母親節的日期 318
推算母親節日期的兩種方法 318
推算某年第m個星期n的日期通用公式 319
疑難204 如何推算某年第n周的日期範圍 319
推算某年第n周的日期範圍 320
使用WEEKDAY函式推算第n周日期範圍 320
6.4 練習與思考 321
第7章 其他計算 322
7.1 獲取信息 322
疑難205 如何獲取檔案所在的路徑 322
獲取單元格位置、格式、內容信息 323
CELL函式無法正確返回檔案路徑的原因 324
疑難206 如何檢測錯誤值類型並返回錯誤分析信息 324
獲取錯誤值類型信息 325
疑難207 如何獲取計算機操作環境相關的信息 325
獲取計算機操作環境信息 326
疑難208 如何在公式中添加註解 327
使用N、T函式過濾文本、數值 327
7.2 數值捨入 328
疑難209 如何對數值進行取整 328
數值向下捨入取整的4種方法 329
取整函式之間的區別 329
疑難210 如何按照四捨六入五單雙規則修約數值 329
四捨六入五單雙法修約 330
疑難211 如何根據貨物數量計算所需紙箱數 331
數值向上捨入取整的兩種方法 331
疑難212 如何根據工資預備各種面額的鈔票 332
根據工資準備各面額鈔票 332
疑難213 如何將不足0.25捨棄且超過0.75進位其餘變為0.5 333
數值按0.5捨入 333
疑難214 如何按四捨五入法將數值保留n位有效數字 333
按指定位數保留有效數字 334
7.3 數學計算 334
疑難215 如何進行開方運算 334
計算數值開方 335
疑難216 如何計算長方體體積 335
計算數組元素之間的乘積 335
疑難217 如何將正整數各數字循環累加到只剩一位數 336
對9求余法妙算各數位循環累加結果 336
疑難218 如何轉換角度與弧度 337
角度與弧度轉換 337
疑難219 如何根據視距及傾斜角計算水平距離 337
根據視距、傾斜角計算水平距離 338
疑難220 如何根據兩點坐標求方位角 338
根據兩點坐標計算方位角 339
疑難221 如何將十進制數轉換為七進制數 340
進制轉換原理 340
十進制數轉換為R進制數的通用公式 340
疑難222 如何將數組中的數值進行累加生成新的數組 342
MMULT構建累加數組 343
疑難223 如何求解多元一次聯立方程 343
矩陣算法解多元一次方程組 344
疑難224 如何根據LED色坐標統計某色區內點的數量 344
矢量叉積法判斷一個點在矢量的右側 345
判斷單點M在凸四邊形內部 345
計算凸四邊形內部點的數量 345
疑難225 如何用位異或運算製作簡單密鑰 346
位異或運算加密解密原理 347
位運算系列函式簡介 347
位異或運算製作簡單密鑰 348
疑難226 如何計算投入產出表的直接和完全消耗係數矩陣 348
MUNIT函式生成單位矩陣 349
投入產出表簡介 349
7.4 隨機計算 350
疑難227 如何製作加減乘除隨機練習題 350
生成隨機加減乘除題目 351
疑難228 如何生成年份不變的隨機日期 351
生成年份不變的隨機日期 352
疑難229 如何產生一組設定超限個數及範圍的隨機數 352
產生限定個數及範圍的隨機數 353
疑難230 如何產生一組常態分配的隨機數 353
產生常態分配隨機數 354
疑難231 如何產生一組對稱區間且不包含0值的隨機數 354
產生區間對稱且不含0值的隨機數的通用公式 354
疑難232 如何產生一組不重複的隨機整數 355
產生不重複隨機數的兩種方法 355
疑難233 如何產生一組隨機的英文字母 356
產生隨機的英文字母 356
疑難234 如何根據指定平均值產生隨機數 357
產生指定均值的隨機數 357
7.5 財務金融 357
疑難235 如何製作按揭貸款計算器 358
根據利率計算分期付款額 359
疑難236 如何製作養老金投資計算器 359
計算等額分期付款的利息 360
疑難237 如何用直線法計算固定資產折舊 360
資產線性折舊計算 361
疑難238 如何用雙倍餘額遞減法計算固定資產折舊 361
雙倍餘額遞減法計算資產折舊 362
疑難239如何用年數總和法計算固定資產折舊 363
年數總和法折舊計算 364
疑難240 如何根據等比數列首項及前n項之和求公比 364
RATE函式求解等比數列公比通用公式 365
計算貸款年利率 366
計算投資年化報酬率 366
疑難241 如何按照超額累進法計算提成獎 367
超額累進制稅率與速算扣除數簡介 367
速算扣除數法計算超額累進稅額通用公式 368
速算扣除數計算通用公式 368
7.6 練習與思考 368
第8章 循環引用 370
8.1 循環引用現象與疊代計算原理 370
疑難242 為什麼新產品查找進價時會出現循環引用警告 370
公式引用自身單元格不一定是循環引用 371
檢查和糾正意外的循環引用 372
疑難243 如何讓輸入的值累加到另一單元格 372
循環引用與疊代計算模型 373
能否直接使用名稱完成疊代計算 374
最多疊代次數與最大誤差的作用 374
疊代計算設定的作用範圍 374
疑難244 如何讓“累加”計算器清零 374
設定啟動開關與疊代變數初始值 375
如何修正刷新引起的累加問題 375
表單控制項與ActiveX控制項複選框的區別 376
疑難245 如何遍歷值班表合併查詢結果 376
使用計數器遍歷數據 377
疑難246 為什麼計數器移位後引用公式的計算結果不同 377
循環引用模式下的單元格運算順序 378
8.2 套用案例詳解 378
疑難247 如何逆序返回字元串 379
逆序遍歷與合併字元 379
巧用字元長度做計數器 380
疑難248 如何獲取一組數字串的交集、並集和補集 380
數字交集、並集和補集的判斷與提取 381
疑難249 如何剔除重複後合併查詢結果 381
字元串中的重複數據判別與合併 382
疑難250 如何提取規格描述字元串中的尺寸信息 382
提取字元串中的多個數字子字元串 383
疑難251 如何將單元格歷次輸入的數據記錄到其他單元格 383
記錄單元格歷史值 384
疑難252 如何根據稅後月工資倒推稅前應發金額 384
模擬單變數求解功能 385
疑難253 如何根據稅後年終獎倒推稅前應發金額 385
查表法計算年終獎個稅 386
年終獎個稅通用公式 388
疑難254 如何根據預算收入和利潤推算地價及增值稅額 388
循環引用倒推地價及增值稅 389
疑難255 如何列舉多種鋼筋下料方案 389
模擬規劃求解功能枚舉多組方案 390
疑難256 如何求解多組規定總額的數據不重複組合 391
模擬規劃求解規定總值明細 392
求解規定總額的明細組成 393
如何求解最接近規定總額的明細組成 393
8.3 練習與思考 394
第9章 函式與其他功能組合套用 395
9.1 數據驗證與條件格式 395
疑難257 如何防止輸入重複姓名和身份證號碼 395
設定數據驗證，防止重複錄入 396
疑難258 如何快速輸入不變的當前日期和時間 397
快捷鍵法輸入當前日期、時間 397
下拉選單法輸入當前日期、時間 397
疑難259 如何讓新增數據自動添加到下拉選單中 398
動態引用製作下拉選單 399
“表格”動態區域製作下拉選單 400
忽略空值與提供下拉箭頭選項 401
疑難260 如何製作隨輸入縮小範圍的藥品名下拉選單 401
類似記憶式鍵入的下拉選單 402
疑難261 如何設定隨輸入自動追加的不重複下拉選單 403
製作既可輸入又可選擇的下拉選單 403
生成唯一值列表 404
疑難262 如何製作二級級聯下拉選單 404
定義名稱法製作二級級聯下拉選單 405
“表格”結構化引用法製作二級級聯下拉選單 406
OFFSET函式動態引用二維數據源製作二級級聯下拉選單 407
OFFSET函式動態引用一維數據源製作二級級聯下拉選單 408
疑難263 如何製作省、市、區縣三級級聯下拉選單 409
混合數據表製作三級級聯下拉選單 410
雙二維表法製作三級級聯下拉選單 412
整理三級級聯選單數據源的兩種方式 414
疑難264 如何標識日記賬與銀行對賬單不相符的金額 414
在條件格式中文字標識多餘數據 415
疑難265 如何標識OFFSET函式返回區域 416
標識歸屬特定區域的單元格 416
疑難266 如何標識回彈數據中3個較大值和3個較小值 417
加權比較法標識含重複數據的極值 417
加權比較法原理 418
疑難267 如何設計標識周末的簡易工作日記表 418
標識周末日期 422
使用分類匯總批量添加標題行技巧 422
9.2 數據透視與圖表套用 422
疑難268 如何對數據透視表按半小時進行組合 422
輔助列法製作透視表組合欄位 423
疑難269 如何在公式中引用透視表中的匯總數據 424
獲取透視表中的數據 424
Excel 2000版GETPIVOTDATA函式通用公式 425
疑難270 如何根據給定多個條件進行大數據量匯總 426
透視表法多條件求和 426
使用透視表緩解公式速度瓶頸 428
疑難271 如何根據客流登記明細表分析銷售顧問的戰鬥力 429
動態引用名稱作為透視表數據源 429
疑難272 如何製作可隨選擇區域變化的市場分布餅圖 431
製作動態餅圖 431
圖表中的SERIES公式 434
製作數據透視圖 435
疑難273 如何製作汽車銷量在前5名的對比條形圖 436
使用輔助列製作圖表坐標軸標籤 437
疑難274 如何製作滾動周期的計畫與實際對比進度圖 439
製作固定滾動周期的動態柱形圖 440
疑難275 如何使用分離的數據源製作計畫與實際對比圖 442
記憶體數組構建動態圖表數據源 443
9.3 資料庫函式與高級篩選 444
疑難276 如何查詢某商品在某日期之前的最新進貨價格 444
資料庫函式簡介 445
資料庫函式語法 446
資料庫函式條件參數設定要點 446
在條件區域使用公式4忌 447
疑難277 如何計算日考勤兩次員工某天在崗時長 448
資料庫函式求按指定條件求單個在崗時長 449
IFERROR函式與倒數巧妙配合過濾不滿足條件數據 449
計算日考勤兩次員工某天上班時段內在崗時長 449
疑難278 如何計算日考勤4次員工某天在崗時長 450
疑難279 如何篩選銷售額超過兩萬元的明細記錄 451
使用公式作為高級篩選條件 452
疑難280 如何刪除包含小寫字母的數據行 454
區分大小寫的數據篩選 454
9.4 模擬運算表妙用 455
疑難281 如何分析各門店銷售總額、均值、最高額及冠軍 455
模擬運算表簡介 456
單變數多表頭公式模擬運算表 457
單變數單表頭公式模擬運算表 457
模擬運算表的特點 458
疑難282 如何根據不同長寬尺寸批量套算塑鋼門窗型材費 459
模擬運算表批量替代複雜的過程計算 460
圖解模擬運算表模型 461
模擬運算表設計步驟 461
疑難283 如何列出日考勤兩次的多個員工每天在崗時長表 461
資料庫函式的局限 463
模擬運算表巧妙配合突破資料庫函式限制 463
列出日考勤4次的多個員工每天在崗時長表 463
疑難284 如何根據股票的月收益率計算年化收益率 464
DPRODUCT函式條件連乘計算年化收益率 465
PRODUCT函式連乘計算年化收益率 466
疑難285 如何根據稅後年終獎批量倒推稅前應發金額 466
模擬運算表配合循環引用批量執行疊代運算 467
疑難286 如何批量計算工商註冊號校驗碼 468
遞歸求余計算工商註冊號校驗碼 470
9.5 單變數求解與規劃求解 470
疑難287 如何已知稅後收入求解稅前應發金額 470
單變數求解推算稅前應發金額 471
如何理解單變數求解模型 472
單變數求解一元n次方程 472
疑難288 如何根據貨品數量與紙箱規格制定裝貨策略 472
規劃求解最小值最佳化耗材方案 473
為何“數據”選項卡中沒有“規劃求解”按鈕 474
如何理解規劃求解最佳化模型 475
如何理解規劃求解的參數 476
疑難289 如何合理安排服務員值班 476
規劃求解最小值最佳化排班方案 477
疑難290 如何最佳化年終獎方案合理避稅 478
規劃求解最小值最佳化年終獎方案 479
如何規劃年薪制合理避稅方案 480
疑難291 如何組合消費清單進行“滿五減二”最實惠 481
規劃求解最佳化“滿五減二”優惠方案 482
9.6 宏表函式套用案例 484
疑難292 如何讓查詢結果引用自定義數字格式 484
獲取單元格數字格式代碼 484
如何使用Excel 4.0宏表函式 484
宏表函式獲取單元格自定義數字格式 485
CELL函式提取格式代碼 485
疑難293 如何獲取預算表單元格中的計算式 485
宏表函式獲取單元格中的計算式 486
新增函式FORMULATEXT獲取單元格中的計算式 487
疑難294 如何轉換包含備註的文本公式為計算結果 487
將文本計算式轉換為計算結果 487
疑難295 如何用十六進制表示IP位址 488
轉換十進制IP位址為十六進制 489
疑難296 如何多條件匯總相同結構的多個工作表數據 489
獲取工作簿中各表名稱 490
疑難297 如何製作工作表目錄超連結導航頁面 491
建立工作表目錄超連結 491
疑難298 如何列出指定路徑下所有檔案名稱 492
獲取指定路徑檔案名稱列表 493
9.7 練習與思考 494
第10章 綜合套用實戰 495
10.1 職工信息管理系統設計 495
疑難299 如何設計職工信息管理系統的首頁 495
設計職工信息管理系統首頁 495
疑難300 如何設計信息錄入模組 497
設計職工信息錄入模組 497
疑難301 如何設計信息查詢和修改模組 500
設計職工信息查詢、修改模組 500
疑難302 如何設計信息輸出模組 502
設計職工信息輸出模組 502
疑難303 如何製作職工信息統計報表 504
製作職工信息統計報表 504
10.2 公司收發文管理系統設計 506
疑難304 如何制定電子文檔管理方案 506
規範管理電子文檔 506
疑難305 如何製作發文管理系統 508
製作發文管理系統 508
模糊查詢指定目錄下檔案名稱 511
實現檔案編號提醒功能 511
製作美觀、方便實用的超連結 512
疑難306 如何製作收文查詢系統 512
製作收文查詢系統 512
設定可忽略的日期查詢條件 514
返回指定時段檔案名稱列表 515
可以提示及時維護的信息查詢 515
疑難307 如何製作系統首頁界面 515
製作系統首頁頁面 515
在圖形圖片中引用單元格文字 519
10.3 工資表設計 519
疑難308 如何設計工資表首頁 519
設計工作表首頁 519
疑難309 如何設計工資計算表 520
設計工資計算表 521
疑難310 如何設計工資條 523
設計工資條 523
疑難311如何設計工資查詢表 524
設計工資查詢表 525
附錄A Excel 2013新增函式列表 527
附錄B常用宏表函式語法說明 529
