目 錄
第1章 公式基礎知識 1
1.1 熟悉Excel的工作環境 1
疑難1 如何減小功能區所占屏幕空間 1
 Excel 2010操作界麵概覽 2
疑難2 如何不用設置公式實現統計結果預覽 3
 設置Excel 2010狀態欄 3
疑難3 如何在一個工作簿中管理多張錶格 3
 理解工作簿、工作錶和單元格之間的關係 4
疑難4 為什麼錶格列標題顯示的是數字而不是字母 5
 設置R1C1引用樣式 5
 A1引用樣式和R1C1引用樣式 6
疑難5 如何根據開頭字母的提示快速輸入函數名稱 6
 開啓“公式記憶式鍵入”功能的兩種方法 6
疑難6 如何不讓函數提示信息遮蓋到工作錶的列號 7
 函數屏幕提示工具的妙用 8
疑難7 為什麼公式計算結果不會變化 8
 設置自動或手動計算模式 8
疑難8 為什麼單元格中隻顯示公式文本而不顯示計算結果 9
 檢查“顯示公式”選項狀態 10
 檢查單元格是否為“文本”格式 10
疑難9 單元格左上角的綠色小三角是什麼含義 10
 錯誤檢查規則及含義 11
 使用錯誤檢查巧換文本形式的數字為數值 12
疑難10 如何在新輸入的行中自動填充上一行的公式 12
 設置自動擴展公式功能 13
1.2 公式基礎概念 14
疑難11 為什麼此“公式”不能根據圓的直徑求麵積 14
 什麼是公式 14
 什麼是函數 14
 公式的組成結構 15
疑難12 為什麼IF函數隻用兩個參數也能計算 15
 必須參數與可選參數 16
 省略參數與省略參數的值 16
疑難13 為什麼公式=A3×B3返迴#NAME?錯誤 17
 公式中運算符的類型及含義 17
疑難14 為什麼開奬號末位判斷總是“大” 18
 Excel的數據類型 18
 數據排列順序的規則 18
 文本形式數字如何轉換為數值 19
疑難15 為什麼數字與“空”單元格相加齣錯 19
 空文本與空單元格的區彆 20
 公式中的&""有什麼作用 20
疑難16 為什麼兩個時間相減得到長串#####號 21
 産生#####的原因分析及解決方法 21
疑難17 如何輸入編號1-2、1/2以及比分1:3 22
 日期、時間數據的錶示方法 23
 打開“設置單元格格式”對話框的3種方法 23
疑難18 如何快速規範以小數點間隔的僞日期 23
 查找替換法規範“僞日期”數據 24
 數據分列法規範“僞日期”數據 24
 根據使用習慣改變默認日期與時間格式 25
 Excel中兩位數字的年份 25
疑難19 如何拯救小數錶示的僞齣生年月 26
 用剪貼闆規範含有兩位小數的“僞日期”數據 26
疑難20 如何實現輸入數字1、2後自動將其顯示為性彆 27
 為單元格自定義數字格式 28
 Excel的數字格式種類 28
 常用數字格式代碼組成部分 29
疑難21 如何讓單位為元的金額顯示為萬元 29
 數字格式代碼及含義 30
 日期相關格式代碼 31
 時間相關格式代碼 32
 數字格式代碼中的日曆和語言區域 32
疑難22 如何讓纍計求和引用的區域隨公式嚮下復製變化 35
 相對引用、絕對引用、混閤引用 36
疑難23 不同錶格相同錶頭能否隻填一處 37
 跨錶引用單元格的錶示方法 38
疑難24 為什麼身份證號碼15位以後的數字都顯示為0 39
 Excel的計算限製 39
疑難25 為什麼有時候匯總金額會比實際差1分錢 40
 浮點運算導緻意外計算誤差 42
疑難26 為什麼即使沒有修改內容關閉錶格時也會提示保存 42
 哪些函數具有易失性 43
 增加“撤銷”次數 43
疑難27 為什麼在Excel 2003中打開公式會顯示_xlfn. 前綴 44
 Excel 2003不支持的新增函數有哪些 45
疑難28 為什麼用Excel 2010打開早期版本的文件後隻有65 536行 45
 Excel 2010支持哪些文件格式 46
 如何在早期Excel版本中打開、另存為高版本文件 47
1.3 公式常用操作技巧 47
疑難29 如何選擇閤適的函數來解決問題 47
 如何搜索函數 47
 從分類中選擇所需函數 48
 函數的分類 49
疑難30 如何快速獲取函數的幫助信息 49
 獲取函數實時提示信息 49
 快速打開幫助文件 50
 函數幫助文件中有哪些信息 50
疑難31 如何快速將公式復製到其他單元格 51
 復製粘貼公式方法 51
 填充柄復製公式法 51
 填充命令法復製公式 52
 多個單元格快速輸入相同公式 53
疑難32 如何復製公式且保持相對引用地址不變 53
 用輔助工作錶法保持單元格相對引用 53
疑難33 如何快速在多個閤計行中填充求和公式 54
 定位空單元格與批量求和 54
 篩選可見單元格求和 55
疑難34 如何按照閤並單元格求對應金額小計 56
 混閤引用妙求閤並單元格對應數據之和 57
 閤並與拆分單元格 57
疑難35 如何讓閤並單元格中的每個單元格都有內容 58
 格式備份讓“閤並”單元格都有內容 58
 格式閤並與單元格閤並的區彆 60
疑難36 如何快速將多個單元格內容閤並在一起 60
 用F9鍵轉換單元格區域數據為常量數組 60
疑難37 如何查看公式分步運算結果以便揪齣錯誤 61
 分步查看運算結果 61
疑難38 如何去掉報錶中的公式隻保留計算結果 62
 選擇性粘貼法保留公式計算結果 63
 斷開鏈接法去除公式 63
 粘貼數值的3種效果 64
疑難39 如何允許填報數據又防止破壞報錶中的公式 65
 保護含有公式的單元格 65
 如何隱藏公式 67
 如何僅可選定需要填報的單元格 67
疑難40 如何不切換工作錶監控不同區域變化情況 67
 使用監視窗口監控數據 67
 神奇的單元格“照相機” 68
疑難41 如何讓新插入的行被原有公式引用 70
 自動計算新插入行數據 70
1.4 數組與數組公式 71
疑難42 如何不用單元格存儲常用數據關係錶 71
 什麼是數組 72
 數組的維度和尺寸 73
疑難43 如何根據一組商品的單價與數量直接求總金額 73
 數組多項運算原理 73
疑難44 為什麼運行公式後{}號消失而計算結果齣錯 74
 什麼是數組公式 74
疑難45 如何判斷多項運算是否需要使用數組公式 75
 圖解數組公式與普通公式運算差異 75
 【Ctrl+Shift+Enter】組閤鍵對於數組公式的意義 75
疑難46 為什麼會齣現“不能更改數組的某一部分”的提示 76
 輸入多單元格數組公式 77
 為何使用多單元格數組公式 77
疑難47 為什麼相同公式在不同位置的計算結果不同 78
 什麼是絕對交集引用 78
 數組的絕對交集 79
 如何判彆公式是否生成內存數組 79
疑難48 為什麼銷售業績與提成點數相乘齣現#N/A錯誤 80
 數組間多項運算與數組維度、尺寸的關係 81
疑難49 為什麼AND、OR函數不能返迴多項邏輯判斷結果 82
 多項運算需要邏輯判斷返迴數組結果 82
疑難50 如何快速取得自然數等差數列 83
 ROW函數返迴行號作為等差數列 84
 ROWS函數與ROW函數的區彆 84
 獲取數字0~9數列 84
疑難51 如何匯總連續多錶相同單元格的銷售額 85
 什麼是連續多錶三維引用 85
疑難52 為什麼INDIRECT函數引用多錶相同單元格求和齣錯 86
 什麼是函數産生的多維引用 87
 為何使用N函數計算結果不一定正確 87
 函數産生多維引用模型圖解 88
1.5 在公式中使用名稱 88
疑難53 如何用漢字代替常量數組以避免誤操作修改常量的值 88
 為什麼要使用名稱 89
疑難54 為什麼字母C不能作為名稱使用 90
 名稱的命名原則 90
疑難55 如何快速將單元格區域定義為多個名稱 91
 使用名稱框快速定義名稱 91
 使用“以選定區域創建名稱”功能批量定義名稱 92
疑難56 如何在修改名稱中的引用位置時使用方嚮鍵 92
 切換編輯框中的點選與編輯模式 93
疑難57 如何在不同工作錶定義錶示不同數據的相同名稱 93
 定義工作錶級名稱 93
 工作簿級、工作錶級名稱 94
疑難58 如何在名稱中實現工作錶標簽“相對引用” 95
 名稱中工作錶的“相對引用” 95
疑難59 如何直接使用錶格標題來引用數據 96
 創建“錶”區域 96
 什麼是“錶”功能 97
 什麼是“結構化引用” 98
疑難60 如何讓圖片隨選擇的生肖自動變化 98
 使用名稱動態引用圖片 99
疑難61 如何讓打印區域隨數據輸入自動擴展 100
 使用名稱設置動態打印區域 100
疑難62 如何在單元格中設置打印頂端標題行和左端標題列 101
 使用名稱在單元格中設置打印標題行和列 101
疑難63 如何快速刪除多個錯誤名稱 102
 使用名稱管理器篩選錯誤名稱 103
疑難64 如何讓名稱隱藏起來 104
 使用VBA隱藏名稱 104
1.6 練習與思考 105
第2章 邏輯判斷 106
2.1 邏輯關係 106
疑難65 如何判斷串連和並聯電路的連通狀態 106
 邏輯與、邏輯或關係判斷 107
疑難66 如何判斷同時滿足購買經濟適用住房的三個條件 108
 邏輯值與數值轉換規則 108
疑難67 如何判斷能力障礙為非智力因素 109
 邏輯非關係判斷 109
疑難68 如何根據職工性彆和職務判斷退休年齡 109
 使用四則運算代替邏輯判斷 110
2.2 多層判斷 110
疑難69 如何根據成績判斷優良中差等級 110
 多層IF函數嵌套的邏輯關係樹 111
 避免邏輯關係重復、遺漏、冗餘 111
疑難70 如何根據多選題答案給齣評分 112
 多個條件判斷的分支與嵌套 112
疑難71 如何給金卡和銀卡客戶按消費額派發贈品 113
 不同分支多層邏輯判斷 113
 多層級對應關係邏輯判斷問題變通處理 114
2.3 常用條件設置 115
疑難72 如何將公式返迴的錯誤值轉為其他值 115
 IS類函數判斷屏蔽錯誤值 116
 IFERROR函數屏蔽錯誤值 116
 常見的7種錯誤的産生原因及解決方案 117
疑難73 如何判斷單元格是否漏填信息 119
 判斷真空單元格 119
疑難74 如何判斷開奬號碼之和的奇偶性 119
 數字奇偶性判斷 120
疑難75 如何根據身份證號碼判斷性彆 120
 取得身份證號中的性彆數字 121
 MOD函數的限製 121
疑難76 如何判斷自然數是質數還是閤數 121
 質數、閤數概念與判斷 122
疑難77 判斷數據是否存在重復現象 122
 區分大小寫判斷重復數據 123
2.4 練習與思考 123
第3章 數據匯總 125
3.1 匯總求和 125
疑難78 如何匯總連續多個錶中相同單元格區域的數據 125
 通配符在輸入連續多錶三維引用中的應用 126
疑難79 如何匯總茶葉中鐵觀音的銷量 126
 SUMIF函數單條件求和 127
 SUMIF函數第3參數的簡寫形式 127
 在SUMIF函數中使用通配符和數組 128
 Excel中數據庫的特徵 129
 數據庫函數條件參數設置要點 129
疑難80 如何匯總月薪在2000至4000的工資總額 130
 SUMIFS單列多條件求和 130
 其他單列多條件求和解法 131
 SUMIFS與SUMIF函數的區彆 131
疑難81 如何根據考評係數及評分計算員工的綜閤得分 131
 SUMPRODUCT求數組乘積之和 132
 SUM數組公式與SUMPRODUCT求數組乘積之和的區彆 132
疑難82 如何避開單元格中的錯誤值求和 132
 使用SUMIF函數排錯求和 133
疑難83 如何驗算現金流量錶的數據勾稽關係是否正確 133
 使用通配符進行條件求和 134
疑難84 如何求齣某城市各項支齣中最大值之和 135
 數據庫函數中使用數組求多列最大值 135
疑難85 如何忽略隱藏的行匯總數據 136
 SUBTOTAL函數對應功能及忽略不可見單元格特性 136
疑難86 如何忽略隱藏的列匯總數據 137
 忽略隱藏列求和 138
疑難87 如何匯總固定間隔n行的數據 138
 MOD函數構建間隔n行的循環 139
 間隔n行數據求和通用公式 139
疑難88 如何匯總某月數據 139
 按月匯總數據 140
疑難89 如何匯總本科學曆男員工的工資總和 140
 SUMIFS函數多條件求和通用公式 141
 SUMPRODUCT或SUM函數多條件求和通用公式 142
疑難90 如何按月分彆匯總每個銷售員的銷售額 142
 MMULT函數雙條件求和 142
疑難91 如何根據代碼等式求對應數值之和 143
 根據代碼等式對應求和 144
疑難92 如何根據多個條件跨錶匯總銷售數據 144
 SUMIFS函數多錶多條件求和 145
3.2 數據計數 146
疑難93 如何統計成績錶中的及格人數 146
 COUNTIF函數常見條件統計用法 147
 常用特殊條件計數 148
疑難94 如何統計35歲以上有房有車的職工人數 148
 COUNTIFS函數多條件計數 149
 SUMPRODUCT或SUM函數多條件計數通用公式 150
 COUNT函數多條件計數通用公式 150
疑難95 如何統計甲車間生産的産品規格帶*號的産品種類 151
 包含通配符的條件計數 151
疑難96 如何統計不重復數據個數 152
 1/COUNTIF函數統計不重復值的優缺點 153
 MATCH=ROW法統計不重復值個數的優缺點 154
 FREQUENCY函數統計不重復值的優缺點 155
疑難97 如何區分字母大小寫統計不重復數據個數 155
 用1/MMULT法求不重復值 155
3.3 極值均值 156
疑難98 如何設置提成奬金的上下限 156
 使用MAX、MIN函數設置上限或下限 157
 使用MEDIAN函數設置上下限 157
 使用IF、TEXT函數設置上、下限 158
疑難99 如何進行綫性插值計算 158
 使用TREND函數綫性插值計算 159
疑難100 如何不計重復返迴一組數中的次大、次小值 159
 提取不計重復的次大、次小值 160
疑難101 如何計算一組數據中大於某數的最小值 161
 提取大於平均值的最小值 161
疑難102 如何統計前10名成績的平均分 162
 計算前10名成績平均分 163
疑難103 如何剔除一個最大值和一個最小值後求平均值 163
 計算去掉n個最大值和n個最小值後的均值通用公式 164
疑難104 如何根據條件計算平均值 164
 使用AVERAGEIF函數單條件求均值 165
 AVERAGEIFS函數計算條件均值 166
疑難105 如何計算加權平均值 166
 計算加權平均值 167
疑難106 如何計算各條生産綫最高工時的平均值 167
 SUBTOTAL函數+多維引用對閤並單元格計數 168
 配權除權法提取多個最大值 168
3.4 頻率分布 169
疑難107 如何獲取一組數值中齣現頻率最多的數據 169
 提取多個齣現頻率最高的數值 170
疑難108 如何根據分數段統計成績分布情況 170
 FREQUENCY函數統計分段分布頻率 171
疑難109 如何統計彩票號碼最大連續齣現次數 172
 計算連續齣現頻率通用公式 173
疑難110 如何判斷是否存在連續5次超標的數據 173
 COUNTIF+OFFSET多維引用計算連續超標次數 174
3.5 排名排序 174
疑難111 如何實現多列成績統一排名 174
 RANK函數多區域數據排名 175
 新舊RANK函數差異 175
疑難112 如何進行跨工作錶數據總排名 176
 RANK函數連續多錶三維引用求排名 176
疑難113 如何在年級成績總錶中統計學生的班級排名 176
 成績總錶中班級排名的4種算法 177
疑難114 如何讓並列排名統計隻占一個名次 178
 中國式排名的3種算法 179
疑難115 如何對智育德育成績按比例摺算後排名 180
 多科目摺算比例排名 181
疑難116 如何根據足球比賽成績進行多關鍵字排名 182
 加權算法多關鍵字排名技術 182
疑難117 如何統計中學生運動會奬牌榜排名並按升序排序 183
 多關鍵字升、降序排列通用公式 184
疑難118 如何提取員工信息並按照姓名升序排序 185
 文本排序 186
疑難119 如何統計參賽選手的最後得分並按降序排序 186
 多組成績按剔除極值後均值排名 187
3.6 練習與思考 188
第4章 查找與引用 189
4.1 查詢定位 189
疑難120 如何定位記錄在數據中首次齣現的位置 189
 MATCH函數定位查找 190
 MATCH函數定位與MIN+IF數組公式定位的區彆 191
疑難121 如何在升序排列的數據中查找近似匹配值 191
 VLOOKUP近似匹配查詢 192
 LOOKUP函數近似匹配查詢 193
疑難122 如何根據輸入工號自動顯示員工基本信息 193
 VLOOKUP+MATCH函數組閤交叉雙條件精確匹配查詢 194
 HLOOKUP+MATCH函數組閤交叉雙條件精確匹配查詢 195
 引用函數與MATCH組閤的交叉雙條件精確匹配查詢 195
疑難123 如何根據品名和規格雙列條件查詢物品單價 195
 INDEX+MATCH函數組閤雙列條件精確匹配查詢通用公式 196
 VLOOKUP+IF雙列條件精確匹配查詢 198
疑難124 如何查找某行或某列最後一個非空單元格數據 198
 LOOKUP精確匹配查找最後一個滿足條件記錄的通用公式 199
 LOOKUP返迴最後一個文本或數值 199
疑難125 如何根據基準單元格偏移引用數據 200
 多行多列數據轉換為一列 200
疑難126 如何根據供貨商名稱跨錶查詢 201
 將代錶單元格地址的文本轉換為引用 201
疑難127 如何用函數實現行列內容轉置 202
 獲取轉置後的數據 203
疑難128 如何始終引用上一行數據而不受刪除行的影響 203
 始終引用上一行單元格 204
疑難129 如何按條件篩選提取數據記錄 204
 篩選滿足條件記錄列錶的通用數組公式 205
疑難130 如何提取滿足條件的多行多列數據放到同一列中 205
 多行多列篩選滿足條件記錄列錶通用數組公式 206
疑難131 如何將滿足條件的多條記錄放在一個單元格 207
 閤並滿足條件的多個查詢結果 208
疑難132 如何將一個單元格內多個“字段”與內容進行分離 208
 VLOOKUP單列查找之妙 209
4.2 超級鏈接 209
疑難133 如何跳轉到最後一行數據下方的空單元格 209
 設置動態跳轉超鏈接 210
疑難134 如何在Excel中用關鍵字搜索網頁 211
 在公式中使用常用搜索引擎 211
疑難135 如何跳轉到查詢的結果 212
 跳轉到滿足條件的查詢結果 212
疑難136 如何製作工作錶目錄超鏈接 212
 建立工作錶目錄超鏈接 213
4.3 篩選重復 214
疑難137 如何列齣某列數據的不重復記錄 214
 MATCH=ROW方法提取不重復值 215
 MIN+COUNTIF=0方法提取不重復值 215
疑難138 如何列齣多行多列單元格區域中的不重復記錄 216
 多行多列數據提取不重復值 217
疑難139 如何列齣自動篩選後的不重復姓名 217
 自動篩選結果中的不重復值提取 218
疑難140 找齣A列有而B列沒有的數據並剔除重復值 218
 篩選A列有而B列沒有的記錄 219
疑難141 如何提取連續齣現次數最多的數據記錄 220
 LOOKUP判斷不連續斷點的應用 220
疑難142 如何剔除重復值並按數據齣現頻率降序排序 221
 篩選滿足條件記錄並排序通用公式 222
4.4 練習與思考 222
第5章 文本處理 223
5.1 截取與連接 223
疑難143 如何將連續單元格區域的字符串閤並起來 223
 字符串連接兩種通用方法 224
疑難144 如何提取混閤字符串中的連續漢字 225
 根據單雙字節截取連續漢字 225
 截取字符串左側或右側的連續漢字 226
疑難145 如何讓公式結果可以換行顯示 227
 換行符在公式中的應用 227
疑難146 如何將字符串按特定分隔符分列 228
 函數分列法通用公式 228
 函數分列法截取子串長度臨界值的推算 229
5.2 查找與替換 230
疑難147 如何剔除姓名信息中的空格 230
 替換法刪除空格字符 230
疑難148 如何替換文本中的字符 231
 批量將指定字符替換為其他字符 232
疑難149 如何判斷字符串中是否包含某個字符 232
 FIND函數判斷字符串包含子字符串的4種方法 233
 替換法判斷包含字符 234
 COUNTIF法判斷包含字符 234
疑難150 如何查找某字符在字符串中第N次齣現的位置 234
 使用替換法查詢第N次齣現的字符 235
疑難151 如何提取包含特定字符的所有記錄 235
 提取包含指定字符的記錄 236
疑難152 如何計算包含字符的個數 236
 使用替換法統計字符個數 237
疑難153 如何在特定位置插入字符串 238
 使用替換零個字符的方法在字符串中插入新字符 238
5.3 比較與轉換 239
疑難154 如何改變英文字母大小寫 239
 改變字母大小寫的3個函數 240
疑難155 如何對比兩個文本大小寫是否一緻 240
 區分大小寫比較文本 241
疑難156 如何轉換單雙字節字符 241
 轉換單、雙字節字符的兩個函數 242
疑難157 如何判斷一個字是否是漢字 242
 判斷字符是否為漢字 243
疑難158 如何查詢漢字區位碼 244
 漢字區位碼的推算方法 244
5.4 數字變幻 245
疑難159 如何加密手機號碼 245
 替換指定位置和長度的字符 246
疑難160 如何升級電話號碼位數 246
 根據條件升級電話號碼 247
疑難161 如何將數字接上英文序數後綴 247
 數字轉換為帶英文序數後綴的文本 248
 數字轉換為英文序數添加後綴的規則 249
疑難162 如何批量修改IP地址格式 249
 用函數分列法規範IP地址格式 250
疑難163 如何為數組設置上限下限 250
 為數組設置上、下限的通用公式 250
疑難164 如何將阿拉伯數字轉為大寫金額 251
 將數字轉為人民幣大寫金額 252
 中文大寫金額的填寫規則 252
疑難165 如何將金額分列填充到各個級位 253
 金額分列填充 253
疑難166 如何根據圖書目錄的大綱級彆製作分級編碼 254
 根據不同列按級彆進行編碼 255
疑難167 如何根據圖書目錄編碼製作分級顯示 256
 根據不同級彆編碼分列顯示 257
5.5 練習與思考 257
第6章 日期與時間處理 258
6.1 日期推算 258
疑難168 如何獲取日期的年月日要素 258
 獲取日期的年月日 259
疑難169 如何推算某個日期所在的月首和月末日期 260
 DATE函數返迴月首月末日期 260
 EOMONTH函數計算月首月末日期 261
疑難170 如何推算某個日期所在季度的季首季末日期 261
 計算季度首、末日期 262
疑難171 如何計算兩個日期相隔的年、月、天數 263
 DATEDIF函數計算日期間隔時長 263
疑難172 如何推算與某個日期相隔數月的日期 264
 EDATE與DATE函數推算數月之後的日期差異 264
疑難173 如何推算職工的退休日期 265
 根據齣生日期推算退休日期 265
疑難174 如何判斷某年是否為閏年 266
 判斷閏年的3種解法 266
疑難175 如何判斷某個日期在第幾季度 267
 推算日期歸屬季度 267
疑難176 如何判斷某個日期在上旬、中旬還是下旬 267
 判斷日期上、中、下旬的3種解法 268
疑難177 如何將M/D/YYYY類型的僞日期轉換成規範日期 268
 規範月日年類型的僞日期 269
疑難178 如何實現生日提醒功能 269
 實現生日提醒 269
疑難179 如何按某年份的季度分攤費用 270
 設置日期上下限，計算時段歸屬 270
疑難180 如何根據齣生日期得到星座 271
 根據生日查詢星座 271
疑難181 如何根據公曆日期返迴農曆生肖 271
 根據公曆生日查詢農曆生肖 272
6.2 時間計算 273
疑難182 如何獲取時間的時、分、秒要素 273
 從時間數據中提取時分秒要素 274
疑難183 如何返迴超過24小時的時間 274
 計算超過24小時的時長 274
疑難184 如何將十進製數時間轉換為標準時間格式 275
 轉換十進製數字為標準時間 275
 轉換用小數錶示分鍾的“僞時間” 275
疑難185 如何提取時間中的毫秒數 276
 提取時間數據中的毫秒 276
疑難186 如何計算加班的小時數 276
 計算加班小時數 277
疑難187 如何計算列車運行時長 277
 跨零點計算兩站點列車運行時間 278
疑難188 如何統計設備開啓後在某個月度中的運行時長 278
 設置數組上下限在跨時段統計中的應用 279
6.3 星期與工作日 280
疑難189 如何判斷某個日期是否為周末 280
 MOD函數判斷星期幾 280
 WEEKDAY函數判斷星期幾 281
疑難190 如何計算離職員工最後一個月的實際工作天數 281
 NETWORKDAYS.INTL函數計算任意工作日 282
疑難191 如何計算兩個日期間星期日的天數 283
 計算某時段內星期幾的天數 284
疑難192 如何計算某月按周末單雙號休息的休假天數 284
 構建某月日期係列的3種方法 285
疑難193 如何根據購銷閤同簽訂日期推算交貨日期 285
 WORKDAY.INTL函數推算若乾個工作日後的到期日 286
疑難194 如何推算某個日期處於當年中的第幾周 286
 計算指定日期的周數 287
疑難195 如何推算某年母親節的日期 287
 推算母親節日期的兩種方法 288
 推算某年第m個星期n的日期通用公式 288
疑難196 如何推算某年第n周的日期範圍 289
 推算某年第n周的日期範圍 289
 使用WEEKDAY函數推算第n周日期範圍 290
6.4 練習與思考 290
第7章 其他計算 291
7.1 獲取信息 291
疑難197 如何獲取文件所在的路徑 291
 獲取單元格位置、格式、內容信息 292
 CELL函數無法正確返迴文件路徑的原因 293
疑難198 如何檢測錯誤值類型並返迴錯誤分析信息 293
 獲取錯誤值類型信息 294
疑難199 如何獲取操作環境有關信息 294
 獲取計算機操作環境信息 295
疑難200 如何在公式中添加注解 296
 使用N、T函數過濾文本、數值 296
7.2 數值捨入 297
疑難201 如何對數值進行取整 297
 數值嚮下捨入取整的4種方法 298
 取整函數之間的區彆 298
疑難202 如何按照四捨六入五單雙規則修約數值 298
 四捨六入五單雙法修約 299
疑難203 如何根據貨物數量計算所需紙箱數 300
 數值嚮上捨入取整的兩種方法 300
疑難204 如何根據工資預備各種麵額的鈔票 301
 根據工資準備各麵額鈔票 301
疑難205 如何將不足0.25捨棄且超過0.75進位其餘變為0.5 301
 數值按0.5捨入 302
疑難206 如何按四捨五入法將數值保留n位有效數字 302
 按指定位數保留有效數字 303
7.3 數學計算 303
疑難207 如何進行開方運算 303
 計算數值開方 303
疑難208 如何計算長方體體積 304
 計算數組元素之間的乘積 304
疑難209 如何將正整數各數字循環纍加到隻剩一位數 304
 對9求餘法妙算各數位循環纍加結果 305
疑難210 如何轉換角度與弧度 305
 角度與弧度轉換 306
疑難211 如何根據視距及傾斜角計算水平距離 306
 根據視距、傾斜角計算水平距離 306
疑難212 如何根據兩點坐標求方位角 307
 根據兩點坐標計算方位角 307
疑難213 如何生成斐波那契數列的內存數組 308
 生成“斐波那契數列”前n項的內存數組 309
疑難214 如何將十進製整數轉換為七進製數 309
 進製轉換原理 310
疑難215 如何將數組中的數值進行纍加生成新的數組 311
 MMULT構建纍加數組 311
疑難216 如何求解多元一次聯立方程 311
 矩陣算法解多元一次方程組 312
7.4 隨機計算 312
疑難217 如何製作加減乘除隨機練習題 313
 生成隨機加減乘除題目 313
疑難218 如何産生一組設定超限個數及範圍的隨機數 314
 産生限定個數及範圍的隨機數 315
疑難219 如何産生一組正態分布的隨機數 315
 産生正態分布隨機數 315
疑難220 如何産生一組對稱區間且不包含0值的隨機數 316
 産生區間對稱且不含0值的隨機數的通用公式 316
疑難221 如何産生一組不重復的隨機整數 316
 産生不重復隨機數的兩種方法 317
疑難222 如何産生一組隨機的英文字母 318
 産生隨機字母 318
疑難223 如何根據指定平均值産生隨機數 318
 産生指定均值的隨機數 319
7.5 財務金融 319
疑難224 如何製作按揭貸款計算器 319
 根據利率計算分期付款額 321
疑難225 如何製作養老金投資計算器 321
 計算等額分期付款的利息 322
疑難226 如何用直綫法計算固定資産摺舊 322
 資産綫性摺舊計算 323
疑難227 如何用雙倍餘額遞減法計算固定資産摺舊 323
 雙倍餘額遞減法計算資産摺舊 324
疑難228 如何用年數總和法計算固定資産摺舊 325
 年數總和法摺舊計算 326
7.6 練習與思考 326
第8章 循環引用 327
8.1 循環引用現象與迭代計算原理 327
疑難229 為什麼新産品查找進價時會齣現循環引用警告 327
 公式引用自身單元格不一定是循環引用 328
 檢查和糾正意外的循環引用 329
疑難230 如何讓輸入的值纍加到另一單元格 329
 循環引用與迭代計算模型 330
 能否直接使用名稱完成迭代計算 331
 最多迭代次數與最大誤差的作用 331
 迭代計算設置的作用範圍 331
疑難231 如何讓“纍加”計算器清零 331
 設置啓動開關與迭代變量初始值 332
 如何修正刷新引起的纍加問題 332
 錶單控件與ActiveX控件復選框的區彆 333
疑難232 如何遍曆值班錶閤並查詢結果 333
 使用計數器遍曆數據 334
疑難233 為什麼計數器移位後引用公式的計算結果不同 334
 循環引用模式下的單元格運算順序 335
8.2 應用案例詳解 335
疑難234 如何逆序返迴字符串 335
 逆序遍曆與閤並字符 336
 巧用字符長度做計數器 336
疑難235 如何獲取一組數字串的交集、並集和補集 337
 數字交集、並集和補集的判斷與提取 338
疑難236 如何剔除重復後閤並查詢結果 338
 字符串中的重復數據判彆與閤並 338
疑難237 如何提取規格描述字符串中的尺寸信息 339
 提取字符串中的多個數字子字符串 339
疑難238 如何將單元格曆次輸入的數據記錄到其他單元格 340
 記錄單元格曆史值 340
疑難239 如何根據稅後收入倒推稅前應發金額 341
 模擬單變量求解功能 342
疑難240 如何根據預算收入和利潤推算地價及增值稅額 342
 循環引用倒推地價及增值稅 343
疑難241 如何列舉多種鋼筋下料方案 343
 模擬規劃求解功能枚舉多組方案 344
疑難242 如何求解多組規定總額的數據不重復組閤 345
 模擬規劃求解規定總值明細 346
 求解規定總額的明細組成 347
 如何求解最接近規定總額的明細組成 347
8.3 練習與思考 347
第9章 函數與其他功能組閤應用 348
9.1 數據有效性和條件格式 348
疑難243 如何防止輸入重復姓名和身份證號碼 348
 設置數據有效性，防止重復錄入 349
疑難244 如何讓新增數據自動添加到下拉菜單中 350
 動態引用製作下拉菜單 350
 “錶”動態區域製作下拉菜單 351
 忽略空值與提供下拉箭頭選項 352
疑難245 如何製作隨輸入縮小範圍的藥品名下拉菜單 352
 類似記憶式鍵入的下拉菜單 353
疑難246 如何設置隨輸入自動追加的不重復下拉菜單 354
 製作既可輸入又可選擇的下拉菜單 354
 如何求取唯一值列錶 355
疑難247 如何製作省、市、區縣三級級聯下拉菜單 355
 製作動態三級級聯下拉菜單 356
疑難248 如何標識日記賬與銀行對賬單不相符的金額 358
 在條件格式中文字標識多餘數據 359
疑難249 如何標識OFFSET函數返迴區域 360
 標識歸屬特定區域的單元格 360
疑難250 如何標識迴彈數據中3個較大值和3個較小值 361
 加權比較法標識含重復數據的極值 361
 加權比較法原理 362
9.2 數據透視與圖錶應用 362
疑難251 如何對數據透視錶按半小時進行組閤 362
 輔助列法製作透視錶組閤字段 363
疑難252 如何在公式中引用透視錶中的匯總數據 365
 獲取透視錶中的數據 365
 使用Excel 2000版GETPIVOTDATA函數用法 366
疑難253 如何根據給定多個條件進行大數據量匯總 366
 透視錶法多條件求和 367
 使用透視錶緩解公式速度瓶頸 368
疑難254 如何製作可隨選擇區域變化的市場分布餅圖 369
 圖錶中的SERIES公式 372
 製作數據透視圖 373
疑難255 如何製作滾動周期的計劃與實際對比進度圖 374
 製作固定滾動周期的動態柱形圖 375
疑難256 如何使用分離的數據源製作計劃與實際對比圖 377
 內存數組構建動態圖錶數據源 378
疑難257 如何用圖錶製作精美時鍾 379
 XY散點圖製作時鍾 380
9.3 數據篩選與假設分析 383
疑難258 如何篩選銷售額超過兩萬的明細記錄 383
 如何使用公式作為高級篩選條件 384
疑難259 如何刪除包含小寫字母的數據行 386
 區分大小寫的數據篩選 387
疑難260 如何已知稅後收入求解稅前應發金額 387
 單變量求解推算稅前應發金額 388
 如何理解單變量求解模型 388
 單變量求解一元n次方程 389
疑難261 如何根據貨品數量與紙箱規格製定裝貨策略 389
 規劃求解最小值優化耗材方案 389
 為何【數據】選項卡中沒有【規劃求解】按鈕 391
 如何理解規劃求解優化模型 391
 如何理解規劃求解的參數 392
疑難262 如何閤理安排服務員值班 393
 規劃求解最小值優化排班方案 393
9.4 宏錶應用案例 394
疑難263 如何快速調整行數相同的單元格的行高 394
 獲取單元格行高值 395
 如何使用Excel 4.0宏錶函數 396
 Excel的行高、列寬單位 397
疑難264 如何讓查詢結果引用自定義數字格式 397
 獲取單元格數字格式代碼 397
 CELL函數提取格式代碼 398
疑難265 如何獲取預算錶單元格中的計算式 398
 獲取單元格中的計算式 398
疑難266 如何轉換包含備注的文本公式為計算結果 399
 將文本計算式轉換為計算結果 399
疑難267 如何用十六進製錶示IP地址 400
 轉換十進製IP地址為十六進製 401
疑難268 如何多條件匯總相同結構的多個工作錶數據 401
 獲取工作簿中各錶名稱 402
疑難269 如何列齣指定路徑下所有文件名 403
 獲取指定路徑文件名列錶 403
9.5 練習與思考 404
第10章 綜閤應用實戰 405
10.1 職工信息管理係統設計 405
疑難270 如何設計職工信息管理係統的首頁 405
 設計職工信息管理係統首頁 405
疑難271 如何設計信息錄入模塊 407
 設計職工信息錄入模塊 407
疑難272 如何設計信息查詢和修改模塊 410
 設計職工信息查詢、修改模塊 410
疑難273 如何設計信息輸齣模塊 412
 設計職工信息輸齣模塊 412
疑難274 如何製作職工信息統計報錶 414
 製作職工信息統計報錶 414
10.2 公司收發文管理係統設計 416
疑難275 如何製定電子文檔管理方案 416
 規範管理電子文檔 416
疑難276 如何製作發文管理係統 418
 製作發文管理係統 418
 模糊查詢指定目錄下文件名 421
 實現文件編號提醒功能 421
 製作美觀、方便實用的超鏈接 421
疑難277 如何製作收文查詢係統 422
 製作收文查詢係統 422
 設置可忽略的日期查詢條件 424
 返迴指定時段文件名列錶 424
 可以提示及時維護的信息查詢 424
疑難278 如何製作係統首頁界麵 425
 製作係統首頁頁麵 425
 在圖形圖片中引用單元格文字 428
10.3 工資錶設計 429
疑難279 如何設計工資錶首頁 429
 設計工作錶首頁 429
疑難280 如何設計工資計算錶 430
 設計工資計算錶 430
疑難281 如何設計工資條 432
 設計工資條 433
疑難282 如何設計工資查詢錶 434
 設計工資查詢錶 434
附錄A Excel 2010函數列錶 437
附錄B 常用宏錶函數語法說明 449
附錄C Excel鍵盤快捷鍵 457
附錄D Excel 2010規範與限製 461
附錄E 光盤內容 464
· · · · · · (收起)