基本介紹
內容簡介,專業書評,媒體推薦,作者簡介,目錄,
內容簡介
《Excel 2010函式與公式》是“Excel疑難千尋千解”叢書之一，為讀者展示了在Excel 2010中運用函式與公式解決疑難問題的實戰技巧，包含函式與公式的基礎理論及操作技巧、各類函式的實際套用、Excel其他功能結合函式的套用和模型實戰等幾個方面。
《Excel 2010函式與公式》包括282疑難案例，每個疑難都包含一個或多個解決方案，並融入了一個或多個知識點的講解，在解決問題的同時，引導讀者學習公式中的原理和擴展知識。書中還介紹了大量成熟的通用公式，讀者可以直接套用於實際工作中。閱讀《Excel 2010函式與公式》，不僅有助於提升讀者在函式與公式方面的理論水平和綜合實戰能力，還有助於解決讀者在實際工作中可能出現的諸多難題，提高工作效率。
《Excel 2010函式與公式》在函式語法描述中，採用中文表述習慣，對Excel 2010幫助檔案進行了提煉和修正，幫助讀者在理解函式時少走彎路。
專業書評
“Excel疑難千尋千解”叢書依託於功能更趨完善的Excel2010版本，用詳盡的文字和圖片介紹了Excel2010各方面的知識和套用，非常適合初學者和有一定基礎的讀者進階學習。書中示例貼近實際，有利於讀者將所學知識與自己的工作結合起來，進而提高數據分析能力和工作效率。
——Excelpx-com 站長 趙志東（網名：蘭色幻想）
不論是在社區中幫助網友解決問題，還是將Excel 方方面面的知識點歸納整理成書，ExcelTip-Net 一直都做得很好，很有自己的特色！
——Officefans-net 站長 謝汝祥（網名：andyxie）
Excel 的博大精深已令活躍於網路社區中的眾多高手深有體會，一直以來人們都在探尋關於Excel 的最佳學習方法和途徑，希望通過其獲取最新而且實用的Excel 套用技術，相信本書的讀者能得到一份這方面的滿意答卷。
——微軟全球最有價值專家MVP 陳 軍（網名：chenjun）
深入淺出的講解，引人入勝的描述，經典的場景和貼心的知識拓展，多思路並舉的操作方法，不知不覺中便使人沉浸於Excel無窮的技巧中，讓人欽佩作者的獨具匠心和非凡智慧。無論是Excel 新手，或是Excel 高手，相信本書都可給人以耳目一新的感覺。
——微軟全球最有價值專家MVP 周建平（網名：守柔）
“Excel 疑難千尋千解”叢書視角獨特，貼近用戶，由點及面，舉一反三，非常適合初學者深入學習，是不可多得的Excel 學習寶典。
——微軟全球最有價值專家MVP 楊彬（網名：BIN_YANG168）
雖然我對Excel 2010 已經不陌生了，但打開本書，仍有一種全新的感覺。本書一改Office幫助的那種“選單式說教”，而是將日常運用過程中經常會遇到的疑難問題一一列出並詳加說明解決方案，有的甚至還列出多項解決方案，這無疑給使用者帶來巨大的方便。本書不僅可以作為Excel 2010 愛好者學習的自學教材，還可以作為經常使用Excel 2010 辦公人士的“解疑字典”，在遭遇“奇怪現象”時可以直接找到解決問題的捷徑。
——微軟全球最有價值專家MVP 方潔影（網名：小妖）
本套叢書立意新穎，內容充實，按照“提出問題→解決問題→擴展問題”的模式，圖文並茂地對大量實際工作中的常見Excel 問題進行了細緻的描述和深入淺出的講解。授人以“魚”，同時授人以“漁”，適合從基礎到高級的Excel 用戶和程式設計師閱讀，使您的工作事半功倍，得心應手！
——微軟全球最有價值專家MVP 楊志寧（網名：northwolves）
媒體推薦
在瀏覽過琳琅滿目、各種類型各種專題的Excel圖書之後，在領略過百科全書式的詳細解析或是武功秘籍般的招式演繹之後，依然有這樣一套“疑難乾尋千解叢書”能夠讓人眼前一亮。刮目相看的原因在於，這套書真正把立場和視角建立在讀者基礎之上——每一個問題都來源於Excel用戶所可能真實面對的疑惑和困擾，每一個解答可能正是許多坐在計算機前束手無策的朋友正在搜尋的目標。
——微軟全球最有價值專家MVP 方驥
作者簡介
陳國良，網名gouweicao78，微軟全球最有價值專家（MVP），ExcelTip-net技術社區總版主，多個Office技術社區資深版主，從事公路、鐵路工程建設管理十年，精通Excel函式與公式，具有豐富的實戰經驗，參與過多部Excel暢銷書的編著工作。
榮勝軍，網名rongjun，Excelrip-net技術社區超級版主，網路工程師，長期從事建築工程造價、工程管理工作，精通Excel函式與公式，具有豐富的實戰經驗，致力於專研Excel套用技術，提高工作效率。
黃朝陽，網名apolloh，ExcelTip-net站長，微軟全球最有價值專家（MVP）。擁有十多年豐富的企業信息化實踐和管理經驗，精通Excel和MS SQL在企業中的套用。早期曾在多個Office技術社區擔任版主，參與過多部Excel暢銷書的編著工作。
目錄
第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 為什麼單元格中只顯示公式文本而不顯示計算結果9
檢查“顯示公式”選項狀態10
檢查單元格是否為“文本”格式10
疑難9 單元格左上角的綠色小三角是什麼含義10
錯誤檢查規則及含義11
使用錯誤檢查巧換文本形式的數字為數值 12
疑難10 如何在新輸入的行中自動填充上一行的公式12
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:322
日期、時間數據的表示方法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 位以後的數字都顯示為039
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
疑難 126 如何根據供貨商名稱跨表查詢201
疑難 127 如何用函式實現行列內容轉置202
獲取轉置後的數據203
疑難 128 如何始終引用上一行數據而不受刪除行的影響203
疑難 129 如何按條件篩選提取數據記錄204
篩選滿足條件記錄列表的通用數組公式 205
疑難 130 如何提取滿足條件的多行多列數據放到同一列中205
疑難 131 如何將滿足條件的多條記錄放在一個單元格207
合併滿足條件的多個查詢結果208
疑難 132 如何將一個單元格內多個“欄位”與內容進行分離208
VLOOKUP 單列查找之妙209
4.2 超級連結209
疑難 133 如何跳轉到最後一行數據下方的空單元格209
設定動態跳轉超連結210
疑難 134 如何在Excel 中用關鍵字搜尋網頁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
疑難 148 如何替換文本中的字元231
疑難 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.5301
數值按 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
疑難 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
散點圖製作時鐘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
