第1 章　數學與三角函數 ……………………………………1
求和問題 ……………………………………………………………………… 2
案例1 　匯總三個組彆的産量（SUM） ………………………………………2
案例2 　求所有工作錶相同區域數據之和（SUM） …………………………3
案例3 　求當前錶以外的所有工作錶相同區域的總和（SUM） ……………3
案例4 　求前三名産量之和（SUM） …………………………………………5
案例5 　按條件匯總進庫數量（SUMIF） ……………………………………6
案例6 　對大於1 000 的數據求和（SUMIF） ………………………………7
案例7 　使用通配符對車間人員的工資求和（SUMIF） ……………………8
案例8 　按指定範圍統計工資總和（SUMIF） ………………………………9
案例9 　計算前三名和後三名的數據之和（SUMIF） …………………… 10
案例10 　對包含“車間”二字的部門的工資求和（SUMIF） …………… 11
案例11 　匯總來自湖南和湖北的職工之和（SUMIF） …………………… 12
案例12 　匯總鼠標所在列中大於600 的數據（SUMIF） ………………… 13
案例13 　隻匯總60 ～ 80 分的成績（SUMIFS） ………………………… 14
案例14 　匯總三年級二班人員遲到次數（SUMIFS） …………………… 15
案例15 　計算車間男性與女性人數差異（SUMIFS） …………………… 16
案例16 　計算參保人數（SUMPRODUCT） ……………………………… 17
案例17 　對一車間男性職工的工資求和（SUMPRODUCT） …………… 18
案例18 　求25 歲以上男性人數（SUMPRODUCT） …………………… 19
案例19 　匯總一車間男性參保人數（SUMPRODUCT） ………………… 20
案例20 　匯總所有車間人員工資（SUMPRODUCT） …………………… 21
案例21 　匯總業務員業績（SUMPRODUCT） …………………………… 22
案例22 　根據直角三角形之勾、股求其弦長（SUMSQ） ……………… 23
案例23 　根據三邊長判斷三角形是否為直角三角形（SUMSQ） ……… 24
求積問題 …………………………………………………………………… 25
案例24 　計算每小時生産産值（PRODUCT） …………………………… 25
案例25 　根據三邊長計算三角形的麵積（PRODUCT） ………………… 25
案例26 　跨錶求積（PRODUCT） ………………………………………… 26
案例27 　求不同單價下的利潤（MMULT） ……………………………… 27
案例28 　製作中文九九錶（MMULT） …………………………………… 28
案例29 　計算車間盈虧（MMULT） ……………………………………… 30
案例30 　計算5 個組彆中産量閤計的第三名（MMULT） ……………… 31
案例31 　計算C 産品最大入庫數（MMULT） …………………………… 32
排列組閤問題 ……………………………………………………………… 33
案例32 　計算象棋比賽對局次數（COMBIN） …………………………… 33
案例33 　預計所有賽事完成的時間（COMBIN） ………………………… 34
案例34 　計算中奬率（PERMUT） ………………………………………… 35
餘數問題 …………………………………………………………………… 36
案例35 　計算餘數（MOD） ……………………………………………… 36
案例36 　對奇數行數據求和（MOD） …………………………………… 36
案例37 　根據單價和數量匯總金額（MOD） …………………………… 37
案例38 　設計工資條（MOD） …………………………………………… 38
案例39 　根據身份證號碼計算身份證擁有者的性彆（MOD） ………… 41
案例40 　計算零鈔（MOD） ……………………………………………… 42
進製轉換問題 ……………………………………………………………… 43
案例41 　將10 進製數字轉換成其他進製數的值（BASE） ……………… 43
絕對值問題 ………………………………………………………………… 44
案例42 　計算預報與實際溫度的最大誤差值（ABS） …………………… 44
隨機數問題 ………………………………………………………………… 45
案例43 　産生11 ～ 20 之間的不重復隨機整數（RAND） ……………… 45
案例44 　將20 個學生的考位隨機排列（RAND） ……………………… 46
案例45 　將三校植樹人員隨機分組（RAND） …………………………… 47
案例46 　産生61 ～ 100 之間的隨機整數（RANDBETWEEN） ……… 48
案例47 　産生指定範圍的不重復隨機數（RANDBETWEEN） ………… 49
乘方與開方問題 …………………………………………………………… 50
案例48 　根據三邊長求證三角形是否為直角三角形（POWER） ……… 50
案例49 　根據等邊三角形周長計算麵積（SQRT） ……………………… 51
數據的捨與入問題 ………………………………………………………… 52
案例50 　隨機抽取奇數行的姓名（ODD） ……………………………… 52
案例51 　計算A1:B10 區域中的偶數個數（EVEN） …………………… 52
案例52 　閤計購物金額並保留一位小數（TRUNC） …………………… 53
案例53 　將每項購物金額保留一位小數再閤計（TRUNC） …………… 54
案例54 　將金額進行四捨六入五單雙（TRUNC） ……………………… 55
案例55 　計算年假天數（TRUNC） ……………………………………… 56
案例56 　根據上機時間計算上網費用（TRUNC） ……………………… 57
案例57 　將金額見角進元與見分進元（TRUNC） ……………………… 58
案例58 　分彆統計收支金額忽略小數（INT） …………………………… 58
案例59 　成績錶格式轉換（INT） ………………………………………… 59 6
案例60 　INT 函數在序列中的復雜運用（INT） ………………………… 60
案例61 　統計交易損失金額（CEILING） ………………………………… 61
案例62 　根據員工工齡計算年資（CEILING） …………………………… 62
案例63 　成績錶轉換（CEILING） ………………………………………… 63
案例64 　計算機颱的上網費用（CEILING） ……………………………… 64
案例65 　統計可組建的球隊總數（FLOOR） …………………………… 65
案例66 　統計業務員提成金額，不足20 000 元忽略（FLOOR） ……… 66
案例67 　將統計金額保留到分位（ROUND） …………………………… 66
案例68 　將統計金額轉換成以“萬”為單位（ROUND） ……………… 67
案例69 　將金額保留“角”位，忽略“分”位（ROUNDDOWN） …… 68
案例70 　計算完成工程所需求人數（ROUNDUP） ……………………… 69
分類匯總問題 ……………………………………………………………… 70
案例71 　按需求對成績進行分類匯總（SUBTOTAL） ………………… 70
案例72 　生成永不間斷的序號（SUBTOTAL） ………………………… 71
案例73 　僅對篩選對象求和（SUBTOTAL） …………………………… 72
附加知識——認識數組 …………………………………………………… 73
案例74 　求入庫最多的産品的入庫閤計（SUMIF） ……………………… 80
案例75 　求獲得第一名次數最多者的奪冠次數（COUNTIF） ………… 81
案例76 　根據采購産品的名稱、數量統計金額（SUMIF） ……………… 82
案例77 　根據身份證號碼計算男性人數（MOD） ……………………… 82
第2 章　邏輯函數 …………………………………………………… 84
真假值判斷 ………………………………………………………………… 85
案例78 　判斷兩列數據是否相等（TRUE、FALSE） …………………… 85
案例79 　計算兩列數據同行相等的數據的個數（TRUE、FALSE） …… 86
案例80 　提取A 産品最新單價（TRUE、FALSE） ……………………… 87
案例81 　判斷學生是否符閤奬學金條件（AND） ……………………… 88
案例82 　所有裁判都給“通過”就進入決賽（AND） ………………… 88
案例83 　判斷身份證號碼長度是否正確（OR） ………………………… 89
案例84 　判斷歌手是否被淘汰（OR） …………………………………… 90
案例85 　根據年齡判斷職工是否退休（OR） …………………………… 91
案例86 　沒有任何裁判給“不通過”就進入決賽（NOT） ……………… 92
條件判斷 …………………………………………………………………… 93
案例87 　判斷學生成績是否及格（IF） …………………………………… 93
案例88 　根據學生成績自動産生評語（IF） ……………………………… 94
案例89 　根據業績計算需要發放多少奬金（IF） ………………………… 95
案例90 　求圖書訂購價格總和（SUM） ………………………………… 96 7
案例91 　計算12 月工資及年終奬（IF） ………………………………… 96
案例92 　既求積也求和（IF） ……………………………………………… 97
案例93 　分彆統計收入和支齣（IF） ……………………………………… 98
案例94 　排除空值重組數據（IF） ………………………………………… 99
案例95 　選擇性匯總數據（IF） ……………………………………………100
案例96 　計算最大數字行與文本行（IF） …………………………………101
案例97 　匯總列數是3 的倍數的數據（IF） ………………………………102
案例98 　匯總數據時忽略錯誤值（IFERROR） …………………………103
案例99 　計算異常停機時間（IFERROR） ………………………………104
第3 章　文本函數 ………………………………………………… 106
字節轉換 …………………………………………………………………… 107
案例100 　將全角字符轉換為半角（ASC） ………………………………107
案例101 　從混閤字符串中提取漢字（WIDECHAR） ……………………107
字符碼運用 ………………………………………………………………… 108
案例102 　判斷單元格首字符是否為字母（CODE） ……………………108
案例103 　計算單元格中的數字個數（CODE） …………………………109
案例104 　計算單元格中大寫加小寫字母的個數（CODE） ……………110
案例105 　按照從“A”到“Z”的順序産生大小寫字母序列（CHAR） …111
案例106 　利用公式産生換行符（CHAR） ………………………………112
案例107 　返迴自動換行單元格第二行數據（CHAR） …………………113
文本連接 …………………………………………………………………… 114
案例108 　根據身份證號碼提取齣生年月日（CONCATENATE） ………114
案例109 　計算平均成績及判斷是否及格（CONCATENATE） …………114
字符轉換 …………………………………………………………………… 115
案例110 　將單詞轉換成首字母大寫形式（PROPER） …………………115
案例111 　將所有單詞轉換成小寫（LOWER） ……………………………116
案例112 　將所有句子轉換成首字母大寫，其餘小寫（LOWER） ………117
案例113 　計算字符串中英文字母個數（UPPER） ………………………118
案例114 　將文本型數字轉換成數值（Value） ……………………………119
案例115 　提取混閤字符串中的數字（Value） ……………………………120
案例116 　給公式添加運算說明（T）………………………………………121
案例117 　將所有數據轉換成保留兩位小數再求和（TEXT） ……………122
案例118 　根據身份證號碼判斷性彆（TEXT） ……………………………123
案例119 　根據身份證號碼計算齣生日期（TEXT） ………………………123
案例120 　顯示今天的日期及星期幾（TEXT） ……………………………124
案例121 　顯示今天每項工程的預計完成時間（TEXT） …………………125
案例122 　統計A 列有多少個星期日（TEXT） ……………………126
案例123 　將數據顯示為小數點對齊（TEXT） …………………………127
案例124 　在A 列産生1 ～ 12 月的英文月份名（TEXT） ………128
案例125 　將日期顯示為中文大寫（TEXT） …………………………129
案例126 　將數字金額顯示為人民幣大寫（TEXT） …………………130
案例127 　判斷單元格的數據類型（TEXT） …………………………131
案例128 　計算達成率，以不同格式顯示（TEXT） …………………132
案例129 　計算字母“A”的首次齣現位置、忽略大小寫（TEXT） …132
案例130 　計算年終奬（TEXT） ………………………………………133
案例131 　計算周日完工的工程個數（TEXT） ………………………134
案例132 　檢驗日期是否升序排列（TEXT） …………………………135
案例133 　將數據重復5 次顯示（TEXT） ……………………………136
案例134 　將錶示起止時間的數字格式轉換成時間格式（TEXT） …137
案例135 　根據起止時間計算時間差（TEXT） ………………………138
案例136 　將數字轉化成電話號碼格式（TEXT） ……………………139
案例137 　在A1:A7 産生星期一～星期日的英文全稱（TEXT） ……140
案例138 　將匯總金額保留一位小數並顯示韆分位分隔符（FIXED） 141
案例139 　將數據對齊顯示，將空白以“﹒”占位（REPT） …………141
案例140 　利用公式製作簡易圖錶（REPT） …………………………142
案例141 　利用公式製作帶坐標軸及標示升降的圖錶（REPT） ……143
案例142 　計算單元格中的數字個數（LEN） ………………………144
案例143 　將數字倒序排列（LEN） …………………………………145
案例144 　計算英文句子中有幾個單詞（LEN） ……………………146
去空格 …………………………………………………………………147
案例145 　將英文句子規範化（TRIM） ………………………………147
案例146 　分彆提取省、市、縣名（TRIM） …………………………148
查找與替換 ……………………………………………………………149
案例147 　提取英文名字（FIND） ……………………………………149
案例148 　將分數形式的字符轉換成小數（FIND） …………………150
案例149 　將單位為“雙”與“片”混閤的數量匯總（FIND） …………150
案例150 　提取公式所在工作錶的名稱（FIND） ……………………151
案例151 　提取括號中的字符串（FIND） ……………………………152
案例152 　分彆提取長、寬、高（FIND） ……………………………153
案例153 　計算密碼字符串中的字符個數（FIND） …………………154
案例154 　通訊錄單列轉三列（FIND） ………………………………155
案例155 　將15 位身份證號碼升級為18 位（REPLACE） …………156
案例156 　將産品型號規範化（REPLACE） …………………………157
案例157 　分彆提取小時、分鍾、秒（REPLACE） …………………158
案例158 　將年級或者專業與班級名稱分開（SEARCH） ……………159
案例159 　提取各軟件的版本號（SEARCH） …………………………159
案例160 　店名分類（SEARCH） ………………………………………160
案例161 　查找編號中重復齣現的數字（SEARCH） …………………161
案例162 　刪除多餘的省份名稱（SUBSTITUTE） ……………………162
案例163 　將日期規範化再求差（SUBSTITUTE） ……………………163
案例164 　提取兩個符號之間的字符串（SUBSTITUTE） ……………164
案例165 　産品規格之格式轉換（SUBSTITUTE） ……………………165
案例166 　判斷調色配方中是否包含色粉“B”（SUBSTITUTE） ……166
案例167 　提取姓名與省名（SUBSTITUTE） …………………………167
案例168 　提取最後一次短跑成績（SUBSTITUTE） …………………167
案例169 　根據産品規格計算産品體積（SUBSTITUTE） ……………168
提取字符串 ……………………………………………………………169
案例170 　從地址中提取省名（LEFT） ………………………………169
案例171 　計算小學參賽者人數（LEFT） ……………………………170
案例172 　通過身份證號碼計算年齡（LEFT） ………………………171
案例173 　從混閤字符串中取重量（LEFT） …………………………172
案例174 　將金額分散填充（LEFT） …………………………………173
案例175 　提取成績並計算平均成績（MID） ………………………174
案例176 　從混閤字符串中提取金額（MID） ………………………175
案例177 　從卡機數據提取打卡時間（MID） ………………………176
案例178 　根據卡機數據判斷員工所屬部門（RIGHT） ……………176
案例179 　根據身份證號碼統計男性人數（RIGHT） ………………177
案例180 　從漢字與數字混閤字符串中提取溫度數據（RIGHT） …178
案例181 　將字符串位數統一（RIGHT） ……………………………179
案例182 　對所有人員按平均分排序（RIGHT） ……………………180
清除乾擾符 ……………………………………………………………181
案例183 　清除非打印字符（CLEAN） ………………………………181
案例184 　去除乾擾符從而將文本轉換成數值（NUMBERVALUE） 182
第4 章　統計函數 ………………………………………………… 184
平均值 …………………………………………………………………185
案例185 　計算平均成績（忽略缺考人員）（AVERAGE） …………185
案例186 　計算一車間和三車間女職工的平均工資（AVERAGE） …185
案例187 　計算各業務員的平均奬金（AVERAGE） …………………186
案例188 　計算平均工資（不忽略無薪人員）（AVERAGEA） ………187
案例189 　計算A 産品平均齣口量（AVERAGEA） …………………188
案例190 　計算平均成績，成績空白也計算（AVERAGEA） ………189
案例191 　計算二年級所有人員的平均獲奬率（AVERAGEA） ……190
案例192 　統計前三名人員的平均成績（AVERAGEA） ……………191
案例193 　求每季度平均支齣金額（AVERAGEIF） …………………191
案例194 　計算每個車間産量大於250 的平均産量（AVERAGEIF） 192
案例195 　去掉首尾求平均（AVERAGEIFS） …………………………193
案例196 　計算二車間女職工的平均工資（AVERAGE） ……………194
計算符閤條件的目標數量 ……………………………………………195
案例197 　計算生産車間異常機颱個數（COUNT） …………………195
案例198 　計算及格率（COUNT） ……………………………………196
案例199 　統計屬於餐飲娛樂業的店名個數（COUNT） ……………197
案例200 　統計各分數段人數（COUNT） ……………………………198
案例201 　統計有多少名選手（COUNT） ……………………………199
案例202 　統計齣勤異常人數（COUNTA） …………………………199
案例203 　在不規則的閤並單元格中生成編號（COUNTA） ………200
案例204 　統計未檢驗完成的産品數（COUNTBLANK） ……………201
案例205 　統計産量達標率（COUNTIF） ……………………………202
案例206 　根據畢業學校統計中學學曆人數（COUNTIF） …………203
案例207 　計算兩列數據相同的個數（COUNTIF） …………………204
案例208 　統計連續三次進入前十名的人數（COUNTIF） …………205
案例209 　統計淘汰者人數（COUNTIF） ……………………………205
案例210 　統計區域中不重復數據個數（COUNTIF） ………………206
案例211 　統計三個品牌的手機銷量（COUNTIF） …………………207
案例212 　對比聯想手機和摩托羅拉手機的銷量（COUNTIF） ……208
案例213 　統計冠軍榜前三名（COUNTIF） …………………………209
案例214 　統計真空、假空單元格個數（COUNTIF） ………………210
案例215 　對名冊錶進行混閤編號（COUNTIF） ……………………211
案例216 　提取不重復數據（COUNTIF） ……………………………212
案例217 　中國式排名（COUNTIF） …………………………………213
案例218 　計算奪冠次數最多者的姓名（COUNTIF） ………………214
案例219 　計算産量在60 ～80 之間的閤計與人員個（COUNTIFS） 215
案例220 　統計大於80 分的三好學生個數（COUNTIFS） …………216
案例221 　統計業績在6 萬元～8 萬元之間的女業務（COUNTIFS） 217
案例222 　統計二班和三班獲數學競賽奬的人數（COUNTIFS） ……217
極值與中值 ……………………………………………………………218
案例223 　計算A 列最後一個非空單元格行號（MAX） …………218
案例224 　計算女職工的最大年齡（MAX） …………………………219
案例225 　計算單日最高銷售金額（MAX） …………………………220
案例226 　查找第一名學生的姓名（MAX） …………………………221
案例227 　統計季度最高産量閤計（MAX） …………………………222
案例228 　根據達標率計算員工奬金（MAX） ………………………223
案例229 　提取産品最後報價和最高報價（MAX） …………………224
案例230 　計算衛冕失敗最多的次數（MAX） ………………………225
案例231 　計算個人所得稅（MAX） …………………………………226
案例232 　計算語文成績大於90 分者的最高總分（DMAX） ……228
案例233 　根據下拉列錶計算不同項目的最大值（DMAX） ……… 229
案例234 　計算中間成績（MEDIAN） ………………………………229
案例235 　顯示動態日期，但不能超過9 月30 日（MIN） ………230
案例236 　根據工作時間計算可休假天數（MIN） …………………231
案例237 　計算最佳成績（MIN） ……………………………………232
案例238 　計算文具類産品和傢具類産品的最小利率（MIN） ……233
案例239 　計算得票最少者有幾票（MIN） …………………………234
案例240 　根據工程的難度係數計算奬金（MIN） …………………235
案例241 　計算五個班第一名人員的最低成績（MIN） ……………236
案例242 　計算售價超過850 元的産品最低利率（DMIN） ………237
案例243 　計算文具類和廚具類産品的最低單價（DMIN） ………238
案例244 　計算第三個最小成績（SMALL） …………………………239
案例245 　計算最後三名成績的平均值（SMALL） …………………239
案例246 　將成績按升序排列（SMALL） ……………………………240
案例247 　羅列三個班第一名成績（SMALL） ………………………241
案例248 　查看産品曾經銷售的所有價位（SMALL） ………………242
案例249 　羅列三個工作錶B 列最後三名成績（SMALL） …………243
案例250 　計算與第三個最大值相等的數據個數（LARGE） ………244
案例251 　計算前十大值産量閤計（LARGE） ………………………245
案例252 　按成績列齣學生排行榜（LARGE） ………………………246
案例253 　提取銷量為前三名的外銷産品名稱（LARGE） …………246
案例254 　計算哪種産品的生産次數最多（MODE） ………………247
案例255 　羅列齣被投訴多次的工作人員編號（MODE） …………248
排名次 …………………………………………………………………249
案例256 　對學生成績排名（RANK） ………………………………249
案例257 　查詢某人成績在三個班中的排名（RANK） ………………250
頻率分布 ………………………………………………………………251
案例258 　分彆統計每個分數段的人員個數（FREQUENCY） ………251
案例259 　計算蟬聯冠軍最多的次數（FREQUENCY） ………………252
案例260 　計算最多經過幾次測試纔成功（FREQUENCY） …………253
案例261 　計算三個不連續區間的頻率分布（FREQUENCY） ………254
案例262 　計算因密碼錯誤被鎖定幾次（FREQUENCY） ……………254
案例263 　計算文本的頻率分布（FREQUENCY） ……………………255
案例264 　羅列奪冠排行榜（FREQUENCY） …………………………256
案例265 　計算誰蟬聯冠軍次數最多（FREQUENCY） ………………257
案例266 　完成中國式排名（FREQUENCY） …………………………258
案例267 　計算誰獲得第二名（FREQUENCY） ………………………259
第5 章　日期和時間函數 ………………………………………… 261
日期函數 ………………………………………………………………262
案例268 　記錄當前日期與時間（NOW） ……………………………262
案例269 　計算國慶倒計時（NOW） …………………………………262
案例270 　統計已到收款時間的貨品數量（TODAY） ………………263
案例271 　計算本月需要完成幾批貨物生産（TODAY） ……………264
案例272 　判斷今年是否閏年（TODAY） ……………………………265
案例273 　計算2014 年有多少個星期日（DATE） …………………266
案例274 　計算本月有多少天（DATE） ………………………………267
案例275 　計算今年每個季度的天數（DATE） ………………………268
案例276 　計算已建國多少周年（YEAR） ……………………………269
案例277 　計算2000 年前電腦培訓平均收費（YEAR） ……………270
案例278 　計算本月需要交貨的數量（MONTH） ……………………271
案例279 　計算8 月份筆筒和毛筆的進貨數量（MONTH） ………271
案例280 　統計傢具類和文具類産品的1 月齣庫次數（MONTH） 272
案例281 　計算今年一季度有多少天（DAY） ………………………273
案例282 　計算新進員工轉正日期（DAY） …………………………274
案例283 　計算生産速度是否達標（YEARFRAC） ……………………275
案例284 　計算截至今天的利息（YEARFRAC） ………………………276
案例285 　計算還款日期（EDATE） …………………………………277
案例286 　提示閤同續約（EDATE） …………………………………278
案例287 　計算下個月有多少天（EOMONTH） ……………………278
案例288 　計算本季度天數（EOMONTH） …………………………279
案例289 　計算本月星期日的個數（EOMONTH） …………………280
案例290 　統計兩倍工資的加班小時數（EOMONTH） ……………281
案例291 　計算員工工作天數和月數（DATEDIF） …………………282
案例292 　根據進廠日期計算員工可休假天數（DATEDIF） ………283
案例293 　根據身份證號碼計算歲數（包括年、月、天）（DATEDIF） 284
時間函數 ………………………………………………………………285
案例294 　計算臨時工的工資（TIMEVALUE） ………………………285
案例295 　計算本日工時工資（HOUR） ……………………………285
案例296 　計算工作時間，精確到分鍾（MINUTE） ………………286
案例297 　根據完工時間計算奬金（MINUTE） ……………………287
案例298 　計算工程時間（SECOND） ………………………………288
星期函數 ………………………………………………………………289
案例299 　計算今天是星期幾（WEEKDAY） …………………………289
案例300 　匯總星期日的支齣金額（WEEKDAY） ……………………290
案例301 　匯總第一周的齣庫數量（WEEKDAY） ……………………291
案例302 　按周匯總産量（WEEKDAY） ………………………………292
案例303 　計算周末奬金補貼（WEEKDAY） …………………………293
案例304 　羅列值班日期（WEEKDAY） ………………………………294
案例305 　計算本月加班時間（WEEKDAY） …………………………294
案例306 　計算今天是本年度第幾周（WEEKNUM） ………………295
案例307 　計算本月包括多少周（WEEKNUM） ……………………296
案例308 　判斷本月休息日的天數（WEEKNUM） …………………297
工作日計算 ……………………………………………………………298
案例309 　計算工程完工日期（WORKDAY） …………………………298
案例310 　計算今年第一季度有多少個工作日（NETWORKDAYS） 299
第6 章　查找與引用函數 ………………………………………… 301
引用函數 ………………………………………………………………302
案例311 　填充12 個月的月份名（ROW） …………………………302
案例312 　檢查日倉庫報錶日期是否按升序排列（ROW） …………302
案例313 　判斷最後一次不及格是哪次測試（ROW） ………………303
案例314 　計算第11 ～第30 名的學員的平均成績（ROW） ……304
案例315 　計算成績排名，不能産生並列名次（ROW） ……………305
案例316 　計算第一次收入大於30 元的金額（ROW） ……………306
案例317 　計算扣除所有扣款後的最高薪資（ROW） ………………307
案例318 　羅列今日銷售的諾基亞手機型號（ROW） ………………308
案例319 　根據空白單元格統計圖書數量（ROW） …………………309
案例320 　判斷某數是否為質數（ROW） ……………………309
案例321 　計算某個數的約數個數及羅列所有約數（ROW） ………310
案例322 　將每個人的貸款重新分組（ROWS） ……………………311
案例323 　檢測每個誌願是否與之前的重復（ROWS） ……………312
案例324 　串連單元格中的數字（COLUMN） ………………………313
案例325 　重組人事資料錶（COLUMN） ……………………………314
案例326 　班級成績查詢（COLUMN） ………………………………315
案例327 　將金額分散填充，空位以“-”占位（COLUMNS） ……316
案例328 　整理成績單（INDIRECT） ………………………………316
案例329 　閤並三個工作錶的數據（INDIRECT） …………………317
案例330 　多區域計數（INDIRECT） ………………………………319
案例331 　求積、求和兩相宜（INDIRECT） ………………………320
案例332 　根據英文及數值對員工的卡號排序（INDIRECT） ……321
案例333 　多行多列取唯一值（INDIRECT） ………………………322
案例334 　將三列課程轉換成單列且忽略空值（INDIRECT） ……323
案例335 　B 列最大值的地址（ADDRESS） …………………………324
案例336 　根據下拉列錶引用不同工作錶的産量（ADDRESS） ……325
案例337 　根據姓名和科目查詢成績（OFFSET） …………………326
案例338 　隔4 行閤計産值（OFFSET） ……………………………327
案例339 　在具有閤並單元格的A 列産生自然數編號（OFFSET） 328
案例340 　引用閤並區域時防止産生0 值（OFFSET） ……………328
案例341 　計算10 屆運動會中有幾次破記錄（OFFSET） …………329
案例342 　進、齣庫閤計查詢（OFFSET） …………………………330
案例343 　根據人數自動調整錶格大小（OFFSET） ………………331
案例344 　計算至少兩科不及格的學生人數（OFFSET） …………332
案例345 　列齣成績匯總最好的科目（OFFSET） …………………333
案例346 　計算及格率不超過50% 的科目數量（OFFSET） ………334
案例347 　羅列兩次未打卡人員（OFFSET） ………………………335
案例348 　計算語文、英語、化學、政治哪科總分最高（OFFSET）336
案例349 　羅列及格率最高的學生姓名（OFFSET） ………………337
案例350 　計算EXCEL 類圖書最多進貨量及書名（OFFSET） ……337
案例351 　根據時間和品名動態引用銷量冠軍（OFFSET） ………338
案例352 　計算産量最高是哪一個季度（OFFSET） ………………339
案例353 　分欄打印（OFFSET） ……………………………………340
案例354 　分類匯總（OFFSET） ……………………………………342
案例355 　工資查詢（OFFSET） ……………………………………343
案例356 　多錶成績查詢（OFFSET） ………………………………344
案例357 　計算每個學生進入前三名的科目總數（OFFSET） ……344
案例358 　羅列平均成績為倒三名的班級（OFFSET） ……………345
案例359 　將姓名縱嚮填充三列（OFFSET） ………………………346
案例360 　同時引用多産品單價並匯總金額（OFFSET） …………347
案例361 　引用多産品最新單價並匯總金額（OFFSET） …………348
案例362 　根據完工狀況匯總工程款（OFFSET） …………………349
案例363 　重組培訓科目錶（OFFSET） ……………………………350
案例364 　計算10 個月中的銷售利潤並排名（OFFSET） …………351
案例365 　計算5 個地區的銷售利潤（OFFSET） …………………352
案例366 　對篩選後的可見區域按條件求和（offset） ……………352
案例367 　將錶格轉置方嚮（TRANSPOSE） ………………………353
案例368 　對不同單位的數據轉換並排名（TRANSPOSE） ………354
案例369 　區分大小寫提取産品單價（TRANSPOSE） ……………355
案例370 　區分大小寫查單價且統計三組總金額（TRANSPOSE） …356
案例371 　根據評委評分和權重統計最後得分（TRANSPOSE） ……357
案例372 　根據字母評語轉換得分（TRANSPOSE） …………………358
案例373 　多列隔行數據匯總（TRANSPOSE） ………………………359
案例374 　提取姓名（INDEX） ………………………………………360
案例375 　轉換電話簿格式（INDEX） ………………………………361
案例376 　隔三行取兩行（INDEX） …………………………………362
案例377 　插入空行分割數據（INDEX） ……………………………363

案例378 　僅僅提取通訊錄中四分之三信息（INDEX） ……………364
案例379 　按投訴次數升序排列客服姓名（INDEX） ………………365
案例380 　羅列導緻産品不良的主因（INDEX） ……………………366
案例381 　按身高對學生排列座次錶（INDEX） ……………………367
案例382 　引用單元格中的公式（FORMULATEXT） ………………368
查找函數 ………………………………………………………………369
案例383 　根據品名查找單價（VLOOKUP） ………………………369
案例384 　亂序資料錶中查找多個項目（VLOOKUP） ……………370
案例385 　將得分轉換成等級（VLOOKUP） ………………………371
案例386 　查找美元與人民幣報價（VLOOKUP） …………………371
案例387 　多條件查找（VLOOKUP） ………………………………372
案例388 　查找最後更新單價（VLOOKUP） ………………………373
案例389 　用不確定條件查找（VLOOKUP） ………………………374
案例390 　按學曆對姓名排序（VLOOKUP） ………………………375
案例391 　多工作錶成績匯總並查找最大值（VLOOKUP） ………376
案例392 　對帶有閤並單元格的區域進行查找（VLOOKUP） ……377
案例393 　查找最高成績者姓名（HLOOKUP） ……………………378
案例394 　計算兩個産品不同時期的單價（HLOOKUP） …………379
案例395 　多條件計算加班費（HLOOKUP） ………………………380
案例396 　製作準考證（HLOOKUP） ………………………………380
案例397 　不區分大小寫判斷兩列相同數據個數（MATCH） ………382
案例398 　按漢字評語進行排序（MATCH） ………………………383
案例399 　提取A 列最後一個數據（MATCH） ……………………384
案例400 　提取字符串中的漢字（MATCH） ………………………385
案例401 　計算補課科目總數（MATCH） …………………………386

案例402 　生成多段混閤編號（MATCH） …………………………387
案例403 　按金牌、銀牌、銅牌排名次（MATCH） …………………388
案例404 　按班級插入分隔行（MATCH） ……………………………388
案例405 　統計一、二班舉重的參賽人數（MATCH） ………………389
案例406 　纍計銷量並列齣排行榜（MATCH） ………………………390
案例407 　利用公式對入庫錶進行數據分析（MATCH） ……………391
案例408 　對閤並區域進行數據查詢（MATCH） ……………………392
案例409 　根據姓名查找左邊的身份證號（LOOKUP） ……………393
案例410 　將姓名按拼音升序排列（LOOKUP） ……………………394
案例411 　將酒店按星級降序排列（LOOKUP） ……………………395
案例412 　計算某班6 年中誰獲第一名次數最多及其次數（LOOKUP） 396
案例413 　羅列每個名次的所有姓名（LOOKUP） …………………397
案例414 　提取新書的印刷批次（LOOKUP） ………………………398
案例415 　將字母等級轉換成評分（LOOKUP） ……………………399
案例416 　分彆提取身份證號碼中的年、月、日（CHOOSE） ……399
案例417 　根據不良率判斷送貨品處理辦法（CHOOSE） …………400
案例418 　讓VLOOKUP 具備多區域查找功能（CHOOSE） ………401
案例419 　跨錶統計最大閤計組彆（CHOOSE） ……………………402
案例420 　羅列所有參加田徑賽的人員姓名（CHOOSE） …………403
超鏈接 …………………………………………………………………404
案例421 　建立文件目錄（HYPERLINK） …………………………404
案例422 　鏈接“總錶”中B 列最大值單元格（HYPERLINK） ………405
案例423 　選擇冠軍姓名（HYPERLINK） …………………………406
案例424 　選擇二年級曠課人員名單（HYPERLINK） ……………407
案例425 　選擇産量最高工作錶（HYPERLINK） …………………408
案例426 　選擇打印區域（HYPERLINK） …………………………409
第7 章　信息函數 ………………………………………………… 411
奇偶性判斷 ……………………………………………………………412
案例427 　計算期末平均成績（ISEVEN） …………………………412
案例428 　提取每日纍計齣庫數和每日庫存數（ ISODD） …………412
案例429 　根據身份證號碼匯總男、女職工總數（ ISODD） ………413
數據類型信息 …………………………………………………………414
案例430 　計算生産部門人數和非生産部門人數（ISERR） ………414
案例431 　提取A、B 列相同項與不同項（ISERROR） ……………415
案例432 　計算産品體積（ISERROR） ………………………………416
案例433 　引用單價並去除乾擾符（ ISNA） ………………………417
案例434 　查詢書籍在7 年中的最高單價（ ISNA） ………………418
案例435 　數字、字母與漢字個數計算（ ERROR.TYPE） …………419
案例436 　判斷錯誤類型（ ERROR.TYPE） …………………………420
案例437 　羅列某運動員參賽9 次成績（ISTEXT） …………………421
案例438 　將按日期排列的銷售錶轉換成按品名排列（ ISTEXT） 422
案例439 　按月份統計每個産品的機器返修數量（ISNUMBER） …423
案例440 　按文字描述轉換並匯總（ISNUMBER） …………………424
案例441 　計算每日庫存數（ISNUMBER） …………………………424
案例442 　計算A 産品的每日庫存數（ISNUMBER） ………………425
案例443 　纍計每日得分（N） ………………………………………426
案例444 　統計各班所有科目大於60 分者人數（N） ……………427
案例445 　計算達標率（N） …………………………………………428
案例446 　提取當前工作錶名、工作簿名及存放目錄（CELL） ……429

案例447 　提取第一次參賽的最佳成績者姓名與成績（ISBLANK） 430
第8 章　財務函數 ………………………………………………… 432
存款與利息 ……………………………………………………………433
案例448 　根據利率、存款與時間計算存款加利息數（FV） ………433
案例449 　計算7 個投資項目相同收益條件下誰投資更少（PV） 434
案例450 　根據利息和存款計算存款達到3 萬元需要幾月（NPER）434
案例451 　根據投資金額、時間和目標收益計算增長率（RATE） …435
案例452 　根據貸款、利率和時間計算某段時間的利息（CUMIPMT）436
案例453 　根據貸款、利率和時間計算需償還的本金（CUMPRINC）437
固定資産 ……………………………………………………………438
案例454 　以固定餘額遞減法計算資産摺舊值（DB） ……………438
案例455 　以雙倍餘額遞減法計算資産摺舊值（DDB） ……………439
案例456 　以年限總和摺舊法計算摺舊值（SYD） …………………440
案例457 　使用雙倍餘額遞減法計算任何期間的資産摺舊值（VBD）441
第9 章　宏錶函數 ………………………………………………… 442
獲取信息 ………………………………………………………………443
案例458 　獲取當前工作簿中工作錶數量（GET.WORKBOOK） …443
案例459 　建立工作錶目錄與超鏈接（GET.WORKBOOK） ………443
案例460 　引用單元格數據同時引用格式（GET.CELL） …………444
案例461 　跨錶匯總數據且適應工作錶的增減（GET.CELL） ……445
案例462 　在任意單元格顯示當前頁數及總頁數（GET.DOCUMENT） 446
數值運算 ………………………………………………………………447
案例463 　將單元格的公式轉換成數值（EVALUATE） ……………447
案例464 　按分隔符取數並求平均（EVALUATE） …………………448
案例465 　根據産品規格計算體積（EVALUATE） …………………449
案例466 　計算減肥前後的三圍差異（EVALUATE） ………………450
案例467 　計算各樓層空餘麵積（EVALUATE） ……………………451
案例468 　按圖書編號匯總價格（EVALUATE） ……………………452
第10 章　WEB 函數 ……………………………………………… 454
中英翻譯 ……………………………………………………………… 455
案例469 　將中文譯為英文、將英文譯為中文（WEBSERVICE） …455
第11 章　函數在數據有效性與條件格式中的運用 ……………… 457
公式與條件格式 ……………………………………………………… 458
案例470 　標示B 列中的重復值 ………………………………458
案例471 　將數據間隔著色 ………………………………………458
案例472 　隱藏錯誤值 ………………………………………459
案例473 　突顯前三個最大值 ……………………………………460
案例474 　將成績高於平均值的姓名標示“優等” ………………461
案例475 　突顯奇數行 …………………………………………462
案例476 　突顯非數值 ………………………………………463
案例477 　突顯有公式的單元格 ………………………………464
公式與數據有效性 ……………………………………………… 465
案例478 　B 列中禁止輸入重復數據 ……………………………465
案例479 　僅允許輸入英文姓名 …………………………………467
案例480 　強製輸入規範化的日期 ……………………………468
案例481 　建立動態下拉選單 …………………………………469
案例482 　建立二級下拉選單 ……………………………………470
案例483 　建立不重復的下拉選單 ………………………………471
案例484 　設置D 列隻能輸入男職工的姓名 ………………………472
案例485 　禁止輸入不完整的産品規格 …………………………473
第12 章　公式的迭代計算 ………………………………………… 475
迭代計算的原理與設置 …………………………………………… 476
迭代計算的運用實例 ………………………………………………… 477
案例486 　自動記錄進庫時間 …………………………………477
案例487 　記錄曆史最高值 ……………………………………477
案例488 　解一元二次方程 …………………………………478
案例489 　解二元一次方程 ……………………………………479
第13 章　自定義函數應用 ………………………………………… 481
引用類 …………………………………………………………… 482
案例490 　文本連接——& 運算的增強型函數 ……………………482
案例491 　引用區域中的唯一值 …………………………………484
案例492 　分彆提取字母、數字和漢字等對象 …………………486
案例493 　按文本與數值順序對單元格的值分列 …………………487
案例494 　根據身份證號碼引用持有者信息 ……………………489
案例495 　按分隔分段取值 ………………………………………491
案例496 　按中國式排序提取第N 大值 …………………………492
案例497 　一對多數值查詢 ……………………………………493
轉換類 …………………………………………………………… 495
案例498 　計算錶達式 …………………………………………495
案例499 　將姓名轉換成拼寫首字母 …………………………496
案例500 　將金額轉換為大寫形式 ………………………………497
案例501 　按顔色匯總 ……………………………………………498
案例502 　查找指定字符在另一個字符中第幾次齣現時的順序 …499
案例503 　混閤字符串求和 ………………………………………500
案例504 　生成發票格式的金額 …………………………………501
第14 章　利用圖錶分析數據 ……………………………………… 503
柱形圖 ……………………………………………………………… 504
案例505 　利用簇狀柱形圖展示成績分布 ……………………504
案例506 　為軟件銷量設計水晶圖錶 ……………………………505
案例507 　微調圖錶突顯銷量走勢 ………………………………507
案例508 　使用堆積柱形圖展示各季度銷量增長趨勢及總量 ……509
案例509 　通過圖錶展示各季度銷量比例 ………………………511
摺綫圖 ……………………………………………………………… 513
案例510 　使用摺綫圖分析産量趨勢 …………………………513
案例511 　使用摺綫圖分析産量增減關係 ………………………514
案例512 　利用平滑麯綫展示日産量趨勢 …………………………516
餅圖 ………………………………………………………………… 517
案例513 　設計軟件銷量餅圖 ……………………………………517
案例514 　設計筆記本電腦銷量復閤餅圖 ……………………518
案例515 　利用復閤餅圖分析産品質量原因 ……………………520
條形圖 ……………………………………………………………… 522
案例516 　利用條形圖比較白晚班産量 …………………………522
案例517 　設計形象化的跳遠條形圖 …………………………523
其他圖錶 …………………………………………………………… 524
案例518 　批量創建迷你圖 ……………………………………524
案例519 　利用條件格式的圖標集體現數據發展趨勢 …………525
案例520 　利用條件格式創建數據條 ………………………526
第15 章　圖錶的高級應用 ………………………………………… 528
圖錶條件格式 …………………………………………………… 529
案例521 　將圖錶三等分 ………………………………………529
案例522 　將繪圖區任意劃分 ……………………………………531
案例523 　對升學率圖錶創建分割綫 ………………………533
案例524 　對盈利圖錶創建三色條件格式 ………………………535
案例525 　單獨顯示不良率超標的生産綫數據 …………………537
案例526 　對摺綫圖按星期創建分割綫…………………………539
案例527 　插用VBA 插件創建圖標條件格式 ………………………541
案例528 　將數據標簽按值的大小顯示為不同顔色的優、良、差 …543
案例529 　標示産量錶的最大值 ………………………………544
案例530 　添加平均綫並對高於平均綫者突齣顯示 ………………546
案例531 　按退貨率標示圖錶係列點 ……………………………548
案例532 　任意標示進齣庫第N 大值 …………………………551
案例533 不采用輔助區而創建最大值、最小值與平均值的網格綫 552
混閤圖錶 ………………………………………………………… 554
案例534 　利用雙軸圖同時顯示不同類型的數據 ……………554
案例535 　設計具有層次感的立體餅圖 ………………………555
案例536 　設計成長狀況混閤圖 …………………………………557
案例537 　汽車銷量比例餅圖 ………………………………559
案例538 　在不添加係列的前提下讓圖錶顯示目標與實際的差異 561
案例539 　對柱形圖標記達成率 ………………………………563
案例540 　設計公司大事記圖錶 ……………………………………565
案例541 　形象化的銷售金額升降圖 ……………………………567
案例542 　設計虛擬縱坐標分彆比較4 個柱形圖 ………………569
圖錶的其他高級應用 …………………………………………… 573
案例543 　在縱坐標軸顯示等級 …………………………………573
案例544 　在一個圖錶中使用數值和百分比兩種刻度 …………575
案例545 　繪製頻率分布圖 ………………………………………577
第16 章　利用控件強化圖錶 ……………………………………… 580
通過控件設計動態圖錶 …………………………………………… 581
案例546 　動態截取一周數據生成圖錶 …………………………581
案例547 　利用柱形圖展示前N 名總成績 ………………………584
案例548 　動態標示最大值、最小值與最接近平均值 …………587
案例549 　設計總是顯示一周數據的摺綫圖 ……………………589
案例550 　設計可以任意調整起止時間的柱形圖 …………………591
案例551 　對柱形圖設置雙分割綫 ………………………………593
· · · · · · (收起)