Excel公式與函數應用技巧

第1章 公式與函數應用基礎1
1.1如何使用公式2
例1刪除公式保留計算結果2
例2使用R1C1樣式2
例3查看工作表中所有公式3
例4隱藏單元格中的公式4
例5保護公式不被修改5
例6對公式進行「錯誤檢查」6
例7通過分步求值檢查公式6
1.2如何使用函數7
例1一鍵自動求和7
例2快速查詢函數8
例3輸入嵌套函數9
例4自定義函數10
例5調用自定義函數11
1.3如何使用數組公式12
例1創建計算多個結果的數組公式12
例2如何修改數組公式13
1.4常見錯誤的分析與處理13
例1解決「####」錯誤13
例2解決「#DIV/0！」錯誤14
例3解決「#VALUE！」錯誤14
例4解決「#NUM！」錯誤15
例5解決「#NULL！」錯誤16
例6解決「#NAME？」錯誤16
例7解決「#REF！」錯誤18
例8解決「#N/A」錯誤18

第2章 財務函數應用技巧21
2.1計算支付額和累計額22
例1貸款在給定期間累計須償還的利息數22
例2貸款在給定期間累計償還的本金數額23
例3計算貸款的每期付款額24
例4計算給定期限內投資的利息償還額25
2.2計算利率和資產26
例1計算資產在給定期間內的折舊值26
例2計算有效的年利率27
例3計算某項投資的未來值28
例4計算一組現金流的內部收益率29
例5計算連續期間內現金流的修正內部收益率30
例6計算名義年利率31
例7計算投資總期數32
例8計算投資凈現值33
例9計算年金的各期利率34
例10計算資產的線性折舊值36
例11按年限折舊法計算折舊值36
例12計算任何期間內的折舊值38
例13計算現金流的凈現值39
2.3證券的計算40
例1計算定期付息有價證券的應計利息40
例2計算到期一次性付息的有價證券的應計利息42
例3計算當前付息期內截止到成交日的天數43
例4計算從成交日到下一付息日之間的天數43
例5計算成交日和到期日之間的付息次數44
例6計算成交日之前的付息日期45
例7計算有價證券的貼現率45
例8計算一次性付息證券的利率46
例9計算首期付息日不固定的有價證券價格47
例10計算首期付息日不固定的有價證券的收益率48
例11計算末期付息日不固定的有價證券價格49
例12計算末期付息日不固定的有價證券的收益率50
例13計算有價證券到期收回的金額51
例14計算折價發行的有價證券的價格51
例15計算折價發行的有價證券的年收益率52
例16計算到期付息的有價證券的價格53
例17計算到期付息的有價證券的年收益率53
例18計算面值￥100的國庫券價格54
例19計算國庫券的收益率54
例20計算國庫券的等效收益率55

第3章 邏輯與文本函數應用技巧57
3.1邏輯函數應用技巧58
例1創建條件公式58
例2對數據執行真假值判斷59
例3不以TRUE或FALSE作為邏輯判斷結果59
例4判斷支出是否在預算內60
例5返回邏輯值FALSE或TRUE61
例6自動給出成績的優劣等級61
例7使用IF函數計算個人所得稅62
3.2文本操作技巧64
例1計算文本字符串的字符數64
例2計算文本字符串中代表字符的字節數64
例3計算字符串的字符起始位置（區分大小寫）65
例4計算字符串的字節起始位置（區分大小寫）66
例5計算字符第1次出現的位置（不區分大小寫）67
例6計算字符基於字節數的起始位置
（不區分大小寫）67
例7從第一個字符開始返回指定個數的字符68
例8從指定位置起返回指定字節數的字符69
例9從指定位置起返回指定長度的字符70
例10從指定位置起返回指定字節數的字符70
例11從最后一個字符開始返回指定個數的字符71
例12從最后一個字符起返回指定字節數的字符72
例13將部分字符用另一個字符串替換73
例14將部分字符根據指定字節數用新字符串替換73
3.3文本轉換技巧74
例1將半角字符轉換為全角字符74
例2將全角字符轉換為半角字符75
例3將大寫字母轉換為小寫字母75
例4將小寫轉換為大寫形式76
例5將數值轉換為按指定格式表示的文本76
例6將字符串中的首字母轉換為大寫形式77
例7將數字按指定位數取整77
例8將數字轉換為大寫漢字78
例9將文本轉換為數字79
例10返回對應於數字代碼的字符80
例11返回文本字符串中第一個字符的數字代碼80
例12返回值引用的文本81
例13四舍五入數值並轉換為帶﹩和位符號的文本82
例14四舍五入數值並轉換為帶￥和位符號的文本83
3.4文本比較與刪除技巧84
例1在單元格中重復某個字符84
例2計算非空白單元格的個數84
例3比較單元格的內容85
例4從文本左側刪除指定數量的字符86
例5從文本右側刪除指定數量的字符86
例6刪除單元格開頭和結尾的空格87
例7合並電話號碼的區號和號碼87
例8只顯示身份號碼后四位數88
例9從身份證號中提取出生日期和性別89

第4章 時間與日期函數應用技巧91
4.1日期和時間的輸入技巧92
例1插入當前日期和時間92
例2插入可更新的日期和時間92
例3在時間單位之間轉換時間93
例4輸入時間值的小時數94
例5把時間值換算成秒數95
例6將小時從十進制數轉換為標准時間格式96
例7快速輸入有規律的日期系列96
例8將日期轉換為日期序列號97
例9顯示員工進入公司的年份98
例10顯示商品的銷售月份99
4.2計算期間差100
例1計算兩個日期之間的工作日100
例2計算兩個日期之間的年份數101
例3計算兩個日期之間的月份數102
例4計算兩個時間之間的小時數103
例5計算兩個時間間隔的小時和分鍾數103
例6計算兩個時間間隔的精確時間104
例7計算某個日期之前的天數105
例8計算月底日期106
例9計算某一年的天數107
例10計算日期對應的星期數108
例11計算兩個日期之間的周數109
例12根據生日計算員工年齡110
例13計算員工的工齡111
4.3對日期求和112
例1為日期添加天數112
例2為日期添加月數113
例3為日期添加年數113
例4對24小時制時間求和114
例5按12小時制給時間添加小時114
例6按12小時制給時間添加分鍾115
例7按12小時制給時間添加秒116

第5章 查找與引用函數應用技巧117
5.1查找函數應用技巧118
例1基於索引號返回參數列表中的數值118
例2設置數字格式以隱藏零值119
例3通過與首行的值對比來查找值119
例4通過與首列值對比來查找值121
例5在一個未排序的區域中查找值122
例6在未排序且大小不定的區域里查找值123
例7計算某個值重復出現的次數125
例8計算多個值重復出現的次數126
例9提取表格中的姓名127
例10計算60分到90分之間的人數128
例11根據姓名查找身份證號129
例12將字母轉換為評分131
5.2引用函數應用技巧132
例1返回由文本字符串指定的引用132
例2查詢抽查的產品編號地址133
例3計算引用中包含的區域個數134
例4返回給定引用的列標135
例5返回指定行列交叉處引用的單元格136
例6多區域計數138
例7計算多個工作表中成績的最大平均值138
例8返回引用的行號139
例9產生每兩行空一行后累加1的編號140
例10檢查每個志願是否與前面的重復141
例11返回數組或引用的行數143
例12將行轉置成列143

第6章 統計函數應用技巧145
6.1計算平均值146
例1計算滿足條件的單元格的平均值146
例2計算參數列表中數值的平均值147
例3計算滿足多重條件的單元格的平均值148
例4計算數據與其均值的絕對偏差的平均值150
例5計算幾何平均值151
例6計算數據集合的調和平均值152
6.2其他統計函數應用技巧153
例1統計空白單元格數目153
例2計算區域中滿足給定條件的單元格的個數154
例3使用通配符155
例4計算數據集的峰值156
例5計算數據集中出現最多的值157
例6計算數據集中第k個最大值157
例7返回分布的偏斜度158
例8返回參數列表中的最大值159
例9返回參數列表中的最小值160
例10返回給定數值集合的中值161
例11計算給定數目對象的排列數162
例12基於樣本估算標准偏差162
例13計算基於整個樣本總體的標准偏差163
例14基於整個樣本總體計算方差164

第7章 信息與工程函數應用技巧165
7.1返回相應信息函數166
例1返回數值的類型166
例2判斷公式的錯誤類型167
例3返回錯誤值#N/A169
例4返回一個字符串的格式代碼169
例5返回當前操作環境的信息172
7.2數據檢查與轉換174
例1檢測一個值是否為文本174
例2判斷測試對象是否為數值175
例3判斷數值是否為奇數176
例4判斷測試對象是否為邏輯值176
例5判斷測試對象是否為空單元格177
例6檢測指定單元格是否為錯誤值178
例7判斷測試對象是否為#N/A錯誤值179
例8判斷對象是否為除#N/A外的錯誤值179
例9將參數中指定的值轉換為數值形式180
7.3數據的換算技巧181
例1換算數值的單位181
例2將二進制數換算為十進制數182
例3將二進制換算為十六進制數183
例4將十進制數換算為二進制數184
例5將十進制數換算為十六進制數185
例6將十六進制數換算為二進制數186
例7將十六進制數換算為十進制數187
例8測試兩個數值是否相等187

第8章 其他函數應用技巧189
8.1數據庫函數應用技巧190
例1計算員工的銷售額190
例2計算公司男／女員工的人數191
例3統計公司有「聯系電話」的員工人數193
例4統計銷售額最高的員工194
例5統計銷售量最少的員工195
例6計算A組總成績196
例7計算A組的平均成績198
例8統計某個單項成績大於90分的最高總成績199
例9統計某個單項成績小於80分的最低總成績200
8.2外部函數應用技巧201
例1使用CALL函數201
例2返回已注冊過的指定動態鏈接庫203
例3返回已注冊過的代碼源的注冊號203
例4與外部數據源連接204
例5歐洲各國的貨幣及其ISO代碼207
例6換算歐洲貨幣208
例7歐元與法國法郎的相互換算209
例8將德國馬克轉換為歐元210
例9將荷蘭盾轉換為歐元210
例10德拉馬克與里拉的相互換算211
8.3多維數據集函數212
例1返回關鍵性能指標KPI屬性212
例2返回多維數據集中的成員或元組213
例3返回多維數據集中成員屬性的值214
例4返回集合中的項目數214
例5返回集合中的第n個成員或排名成員214
例6從多維數據集中返回匯總值215
例7定義成員或元組的計算集215