跟著視頻學Excel數據處理：函數篇

內容描述

本書主要介紹Excel函數知識。主要內容包括函數基礎知識、函數使用小技巧、文本函數、查找引用函數、邏輯函數、日期和時間函數、其他函數，以及函數初級綜合案例、函數中級進階案例及函數高級案例。其中共涉及140個函數初級案例、79個函數中級案例、28個函數高級案例和40個常用技巧，讓讀者循序漸進學習Excel函數，並將其應用到數據處理中。本書適合在校大學生和職場辦公人員、會計、統計、數據分析員、文員。

目錄大綱

目 錄

第1章 函數基礎知識：Excel數據處理利器 1
1.1 函數基礎 1
1.2 函數小技巧 2
第2章 基礎函數：打好基礎，輕松實現數據處理 5
2.1 文本函數 5
2.1.1 RIGHT：從右側提取字符函數 5
案例01 使用RIGHT函數提取單元格中的字符 5
2.1.2 LEFT：從中間提取字符函數 6
案例02 使用LEFT函數提取單元格中的字符 6
2.1.3 MID：從中間提取字符函數 7
案例03 使用MID函數提取單元格中的字符 7
2.1.4 LEN：計算文本長度函數 7
案例04 使用LEN函數統計單元格中有多少個字符 7
2.1.5 LENB：文本長度計算函數（區分單雙字節） 8
案例05 使用LENB函數統計單元格中共有幾個字符 8
案例06 使用LEFT函數提取單元格中左邊的漢字 9
案例07 使用RIGHT函數提取單元格中右邊的數字 9
2.1.6 MIDB：從指定位置提取字符函數（區分單雙字節） 10
案例08 使用MIDB函數從單元格中提取指定的字符 10
2.1.7 SEARCH：不區分大小寫、支持通配符的查找函數 10
案例09 使用SEARCH函數查找指定字符的位置 11
2.1.8 SEARCHB：查找指定字符的位置函數（區分單雙字節） 11
案例10 使用SEARCHB函數提取漢字中間的數字 11
2.1.9 FIND：區分大小寫、不支持通配符的查找函數 12
案例11 使用FIND函數從字符串中查找某個字符串所在的位置 12
2.1.10 ASC：將全角雙字節轉換為半角單字節函數 13
案例12 使用LENB函數把逗號轉為單字節 13
2.1.11 WIDECHAR：把單字節轉換為雙字節函數 13
案例13 使用WIDECHAR函數把單字節字符轉換成雙字節字符 14
2.1.12 CHAR：將數字轉換為字符函數 14
案例14 使用CHAR函數自動填充26個大寫字母 14
2.1.13 CODE：將字符轉換為數字函數 15
案例15 使用CODE函數求字母A的ASCII碼 15
2.1.14 UPPER：將小寫字母轉換為大寫字母函數 15
案例16 使用UPPER函數把字母由小寫轉換為大寫 15
2.1.15 REPLACE：查找和替換函數 16
案例17 使用REPLACE函數把銀行卡號每隔4位加一個空格 16
2.1.16 TEXT：格式轉換函數 17
案例18 用TEXT函數將0值屏蔽且保留1位小數。 17
案例19 用TEXT函數計算2008-8-8是星期幾 17
案例20 使用TEXT函數根據分數判斷成績等級 18
案例21 使用TEXT函數將大於100的數值顯示為100，將小於
或等於100的數值顯示為其本身 19
2.1.17 T：檢測給定值是否為文本函數 19
案例22 使用T函數實現只保留文本 20
2.1.18 TRIM：清除空格字符專用函數 20
案例23 使用TRIM函數清除單元格兩端的空格 20
2.1.19 SUBSTITUTE：按值替換函數 21
案例24 使用SUBSTITUTE函數統計單元格中有多少個“c” 21
2.2 查找引用函數 21
2.2.1 LOOKUP：查找函數 21
案例25 使用LOOKUP函數查找A列中最後一個數值 22
案例26 使用LOOKUP函數查找A列中的最後一個文本 22
案例27 使用LOOKUP函數查找A列中的最後一個值 23
案例28 使用LOOKUP函數根據分數判斷成績等級 24
2.2.2 MATCH：查找址函數 24
案例29 使用MATCH函數查找某一個值的位置 25
案例30 使用MATCH函數根據日期返回對應的季度 25
案例31 使用MATCH函數根據數值返回對應的位置 26
案例32 使用MATCH函數查找最後一個銷售數量出現的位置 26
2.2.3 VLOOKUP：垂直查找函數 27
案例33 使用VLOOKUP函數根據姓名查找對應的銷量 27
案例34 使用VLOOKUP函數根據學生分數判斷成績等級 28
案例35 使用VLOOKUP函數查找和數據源列中的字段順序
不一樣的信息 28
2.2.4 HLOOKUP：水平查找函數 29
案例36 使用HLOOKUP函數根據月份查找銷量 29
2.2.5 INDEX：引用函數① 30
案例37 使用INDEX函數引用單元格區域中的數值 30
案例38 使用INDEX函數實現反向查找 31
案例39 使用INDEX函數動態查詢每門科目的總分數 31
2.2.6 OFFSET：引用函數② 32
案例40 使用OFFSET函數向下引用某個單元格中的內容 32
案例41 使用OFFSET函數向上引用某個單元格中的內容 33
案例42 使用SUM函數動態求每列的數量之和 33
2.2.7 INDIRECT：引用函數③ 34
案例43 使用INDIRECT函數引用單元格中的值 34
案例44 使用INDIRECT函數根據工號查找姓名 35
2.2.8 CHOOSE：引用函數④ 35
案例45 使用CHOOSE函數引用單元格中的值 36
案例46 更改列數據的位置 36
案例47 使用VLOOKUP函數輕松實現反向查找 36
2.2.9 ROW：返回行號函數 37
案例48 使用ROW函數輸入26個英文字母 37
案例49 使用SUM和ROW函數求從1加到100的結果 38
2.2.10 COLUMN：返回列函數 38
案例50 使用COLUMN函數計算數值 39
2.2.11 ADDRESS：單元格地址函數 39
案例51 使用ADDRESS函數根據列號返回對應的字母 39
2.2.12 TRANSPOSE：轉置函數 40
案例52 使用TRANSPOSE函數把單元格區域中的內容橫向顯示 40
2.2.13 HYPERLINK：超鏈接函數 41
案例53 使用HYPERLINK函數為單元格中的值設置超鏈接 41
案例54 使用HYPERLINK函數實現單元格之間的跳轉 41
2.3 邏輯函數 42
2.3.1 IF：條件判斷函數 42
案例55 使用IF函數判斷學生成績：小於60分為不及格，
否則為及格 42
案例56 使用IF函數判斷成績 43
2.3.2 TRUE：邏輯真函數 43
案例57 求TRUE加TRUE等於幾 43
2.3.3 FALSE：邏輯假函數 44
案例58 求TRUE+FALSE等於幾？ 44
案例59 使用IF函數把0值屏蔽 44
2.3.4 AND：檢查所有參數是否為TRUE函數 45
案例60 使用AND函數判斷學生成績：如果三科成績都大於或
等於60就返回“通過” 45
2.3.5 OR：檢查所有參數是否為FALSE函數 45
案例61 使用OR函數判斷學生成績 46
2.3.6 NOT：相反函數 46
案例62 使用NOT 函數判斷參數的邏輯值 46
2.3.7 IFERROR：屏幕錯誤值函數 47
案例63 使用IFERROR函數屏蔽公式中的錯誤值 47
2.4 日期和時間函數 47
2.4.1 YEAR：年函數 47
案例64 使用YEAR函數提取日期中的年份 48
2.4.2 MONTH：月函數 48
案例65 使用MONTH函數提取日期中的月份 48
2.4.3 DAY：日函數 49
案例66 使用DAY函數提取日期中的日 49
2.4.4 DATE：日期函數 49
案例67 使用DATE函數根據年、月、日返回“年-月-日”
格式的日期 49
2.4.5 EOMONTH：月末函數 50
案例68 使用EOMONTH函數統計每個月有多少天 50
2.4.6 HOUR：時函數 51
案例69 使用HOUR函數提取時間中的小時數 51
2.4.7 MINUTE：分函數 51
案例70 使用MINUTE函數提取時間中的分鐘數 51
2.4.8 SECOND：秒函數 52
案例71 使用SECOND函數提取時間中的秒數 52
2.4.9 NOW：系統時間函數 52
案例72 使用NOW函數返回當前日期和時間 52
2.4.10 TODAY：系統日期函數 53
案例73 使用TODAY函數返回當前日期 53
2.4.11 WEEKDAY：計算星期幾函數 53
案例74 使用WEEKDAY函數高亮顯示周六和周日 54
2.4.12 DATEDIF：日期處理函數 55
案例75 使用DATEDIF函數根據出生日期計算年紀 56
2.5 其他函數 56
2.5.1 AVERAGE：求平均值函數 56
案例76 使用AVERAGE函數求單元格區域的平均值 57
2.5.2 AVERAGEIF：單條件求平均值函數 57
案例77 使用AVERAGEIF函數求單元格區域中大於或等於某個
值的值的平均值 57
2.5.3 AVERAGEIFS：多條件求平均值函數 58
案例78 求大於300且小於800的值的平均值 58
2.5.4 COUNT：計算數字個數函數 59
案例79 使用COUNT函數統計單元格區域中的數據有多少個
為數值型 59
2.5.5 COUNTA：非空計數函數 59
案例80 統計單元格區域中非空單元格的個數 60
2.5.6 COUNTIF：單條件計數函數 60
案例81 使用COUNTIF函數統計字符 60
2.5.7 COUNTIFS：多條件計數函數 61
案例82 使用COUNTIFS函數統計業務員是“曹麗”且銷量
大於500的記錄個數 61
2.5.8 SUM：求和函數 62
案例83 使用SUM函數求多個工作表單元格中的值的和，
但不包括當前的工作表 62
2.5.9 SUMPRODUCT：計算乘積之和函數 62
案例84 使用SUMPRODUCT函數求產品名稱是A且型號是
大號的產品數量 63
2.5.10 PRODUCT：計算所有參數的乘積函數 63
案例85 計算體積 63
2.5.11 SUMIF：單條件求和函數 64
案例86 使用SUMIF函數匯總數據 64
2.5.12 SUMIFS：多條件求和函數 64
案例87 使用SUMIFS函數多條件求產品數量 65
2.5.13 MIN：最小值函數 65
案例88 使用MIN函數判斷單元格中的數值 65
2.5.14 MAX：最大值函數 66
案例89 使用MAX函數判斷單元格中的數值 66
2.5.15 SMALL：返回第k個最小值函數 67
案例90 使用SMALL函數升序排序單元格中的數值 67
2.5.16 LARGE：返回第k個最大值函數 67
案例91 使用LARGE函數降序排序單元格區域中的數值 67
2.5.17 SUBTOTAL：分類匯總函數 68
案例92 使用SUBTOTAL函數給隱藏的行自動編號 68
2.5.18 ROUND：四捨五入函數 69
案例93 使用ROUND函數將單元格區域中的數值保留兩位小數 69
2.5.19 ROUNDDOWN：向下舍入函數 69
案例94 使用ROUNDDOWN函數保留一位小數，不進行四舍
五入，全部舍棄 69
2.5.20 ROUNDUP：向上舍入函數 70
案例95 使用ROUNDUP函數保留一位小數，不進行四捨五入，
全部進入 70
2.5.21 CEILING：按倍數向上進位函數 70
案例96 使用CEILING函數進行數值舍入：十分位不足0.5
就按0.5算，大於或等於0.5就向上進1 71
2.5.22 FLOOR：按倍數向下舍入函數 71
案例97 使用FLOOR函數進行數值舍入，十分位不足0.5就
按向下捨去，大於或等於0.5就按0.5處理 71
2.5.23 INT：取整函數 72
案例98 使用INT函數把日期提取出來 72
2.5.24 MOD：取餘函數 73
案例99 使用MOD函數求餘數 73
2.5.25 REPT：重復函數 73
案例100 使用REPT函數製作符號編號 73
2.5.26 N：將非數值型數值轉換為數值型數值函數 74
案例101 使用N函數將數值進行轉換 74
2.5.27 ABS：取絕對值函數 75
案例102 使用ABS函數求絕對值 75
2.5.28 CELL：獲取單元格信息的函數 75
案例103 使用CELL函數獲取工作簿路徑及工作表信息 76
2.5.29 ISNUMBER：檢測是否為數值型數字函數 76
案例104 使用ISNUMBER函數判斷數值型數值 76
2.5.30 ISTEXT：檢測一個值是否為文本函數 77
案例105 使用ISTEXT函數判斷文本型數值 77
2.5.31 PHONETIC：另類文本字符連接函數 77
案例106 使用PHONETIC函數連接文本 77
2.5.32 RAND：取隨機小數函數 78
案例107 使用RAND函數生成隨機數 78
2.5.33 RANDBETWEEN：取隨機整數函數 78
案例108 使用RANDBETWEEN函數生成指定大小的隨機整數 79
2.5.34 MODE：取眾數函數 79
案例109 使用MODE函數判斷出現次數最多的數值 79
2.6 初級函數綜合案例 80
案例110 使用TEXT函數把秒數轉換為分鐘數 80
案例111 為什麽使用SUMIF函數的求和結果是0 81
案例112 對比兩張表中的數據 83
案例113 判斷奇偶行的兩種方法 85
案例114 使用SUMIF函數遇到通配符時如何解決 86
案例115 提取單元格中靠左側的漢字 87
案例116 從漢字中提取數字的最簡單的方法 88
案例117 求A1:C1的和（簡單的數值相加為什麽會報錯） 88
案例118 使用LOOKUP函數實現反向查找 89
案例119 比IF函數還經典的判斷用法 90
案例120 引用每個表的C列中的最後一個值 92
案例121 將單元格中的內容進行分列 93
案例122 求18:00—23:00 有幾個小時 94
案例123 為什麽使用SUM函數無法求和 95
案例124 提取小括號里的數據 95
案例125 每隔4行提取數據組成新的一列 97
案例126 最簡單的分類匯總方法 98
案例127 判斷漢字和字母 99
案例128 用VLOOKUP函數實現多表查找 100
案例129 將一列數據快速轉換為兩列數據 101
案例130 根據產品名返回最後一次進價 101
案例131 提取最後一個月的數據 102
案例132 為什麽使用VLOOKUP函數得不到正確的結果 103
案例133 為什麽使用 SUMPRODUCT函數得不到正確的結果 104
案例134 如何實現“六舍七入” 105
案例135 動態獲取當前工作表名稱 107
案例136 Excel中的兩個通配符的用法 108
案例137 ROW函數與ROWS函數的區別 108
案例138 如何把“2017-10-20”轉換為“20171020” 111
案例139 為什麽公式=IF(2,3,4)返回3 112
案例140 隱藏0值 112
案例141 計算表達式 113
案例142 如何把“2017.8.30”轉換成“2017年8月30日” 114
第3章 中級函數：實現批量數據處理 115
3.1 數組 115
3.2 中級數組函數經典案例 118
案例143 用數組求1～100的和 118
案例144 用數組求文本中的數字之和 119
案例145 使用MID函數求單元格中的數字之和 119
案例146 使用LEN函數統計單元格區域中有多少個字母A 119
案例147 使用RIGHT函數提取單元格中右邊的數字 120
案例148 使用SUMIF函數求張三和李四的銷量之和 120
案例149 使用VLOOKUP函數求每個員工上半年和下半年的
銷量之和 121
案例150 使用COUNTIF函數統計字符共出現多少次 121
案例151 使用MATCH函數統計不重復值的個數 122
案例152 使用FIND函數查找最後一個“/”的位置 123
案例153 使用FIND函數查找單元格中第一個數字出現的位置 123
案例154 使用數組根據日期返回對應的季度 124
案例155 使用數組隔行求和 124
案例156 使用數組引用每一行單元格中的最後一個數據 125
案例157 使用數組引用每一行單元格中的第一個數據 125
案例158 使用數組統計超過15位數字的個數 126
案例159 使用MID函數提取單元格中最後一個逗號後面的數據 126
案例160 使用MID函數從中英文中提取數字 127
案例161 使用IF+VLOOKUP函數實現反向查找 127
案例162 使用CHOOSE函數實現反向查找 128
案例163 使用COUNTIF函數統計大於100且小於200的
數字個數 128
案例164 雙條件查找的7種方法 129
案例165 使用INDEX函數實現一對多查詢並且縱向顯示結果 132
案例166 使用INDEX函數實現一對多查詢並且橫向顯示結果 133
案例167 實現一對多查詢並且將結果用頓號分隔 133
案例168 LOOKUP+FIND函數的經典組合應用 134
案例169 單列去重 135
案例170 多列去重 135
案例171 中國式排名 136
案例172 美國式排名 137
案例173 多工作表匯總 137
案例174 目錄製作 138
案例175 VLOOKUP函數的第1參數數組用法 139
案例176 計算體積 139
案例177 把92030中的數字分別提取到3個單元格中 140
案例178 將多列轉為一列 140
案例179 將一列轉為多列 141
案例180 用全稱匹配簡稱 141
案例181 用簡稱匹配全稱 142
案例182 使用LOOKUP函數實現多條件查找 142
案例183 對合並單元格按條件求和 143
案例184 查找最後一次出現的位置對應的值 144
案例185 雙條件計數 144
案例186 如何生成序列 145
案例187 引用合並單元格中的數據 145
案例188 從漢字中提取數字 146
案例189 將通話記錄里的分和秒相加 146
案例190 使用COUNTIF函數統計不連續列中的字符個數 147
案例191 使用COUNTIF函數統計不重復值的個數 147
案例192 使用COUNT函數統計不重復值的個數且
排除空單元格 148
3.3 中級函數 149
案例193 根據身份證號提取戶籍所在地址 149
案例194 根據身份證號提取出生日期 149
案例195 根據身份證號提取性別 150
案例196 根據身份證號計算年齡 150
案例197 根據名稱顯示照片 151
案例198 使用VLOOKUP函數製作工資條 152
案例199 將周末高亮顯示 152
案例200 使用定義名稱功能+INDIRECT函數實現二級下拉菜單 154
案例201 使用數組公式實現二級下拉菜單 155
案例202 將TEXT函數當IF函數用 155
案例203 為銀行卡號每隔4位加空格 156
案例204 動態求每周的銷量 156
案例205 設置七天內生日提醒 157
案例206 使序號隨著篩選而自動編號 158
案例207 給合並單元格編號 158
案例208 不顯示錯誤值的3種方法 159
案例209 TEXT函數十!的用法 159
案例210 計算經過多少個工作日完成任務 160
案例211 向下和向右填充公式生成26個字母 161
案例212 提取括號里的數據 161
案例213 計算一個日期為當月的第幾周 162
案例214 隔列求和的3種方法 163
案例215 取得單元格的列號 164
案例216 篩選在19:00—23:00範圍內的時間 164
案例217 判斷某月有多少天 165
案例218 獲取當前工作表的名稱 165
案例219 輸出4位數，不足4位在左邊加0 166
案例220 限制單元格中只能輸入15位或者18位字符 167
第4章 高級函數：Excel函數高級驗法 168
4.1 MMULT：矩陣乘積函數 168
案例221 使用MMULT函數求各科成績總和 169
案例222 通過MMULT函數求每一個人的總分 169
案例223 使用MMULT函數單條件求和 170
案例224 使用MMULT函數實現多行多列查找 171
案例225 找出每個銷售員銷量最大的4個數值 171
案例226 按數量生成姓名 172
4.2 FREQUENCY：頻率函數 173
案例227 使用FREQUENCY函數統計分數出現的頻率 173
案例228 使用FREQUENCY函數統計不重復值的個數 174
案例229 使用FREQUENCY函數實現去重 174
案例230 合並單元格條件求和 175
4.3 降維函數 176
案例231 使用N函數降維求奇數行的和 176
案例232 使用T函數降維動態求和 177
案例233 使用SUMIF函數進行降維匯總多個工作表 178
案例234 使用SUBTOTAL函數降維實現隔列求和 178
案例235 為何MATCH函數會報錯？ 179
4.4 加權函數 180
案例236 提取多段數字 180
案例237 動態引用每一列單元格中的最後一個值並求和 180
4.5 高級函數徑變案例 181
案例238 把月份數轉為“#年#月”的格式 181
案例239 提取單元格中的數字再相乘 182
案例240 使用SUBSTITUTE函數根據身份證號計算年齡
是幾歲幾個月 183
案例241 VLOOKUP函數的第1參數為數組的用法 184
案例242 使用VLOOKUP函數實現一對多查詢 184
案例243 使用MATCH和MID函數找到單元格中第一個
出現的數字 185
案例244 如何給LOOKUP函數構建參數 186
案例245 把小括號里的數字相加 186
案例246 提取多段數字並放在多個單元格中 187
案例247 將文字和數字分開 188
案例248 提取多段數字且求和 189
第5章 Excel中常用技巧：提高 數據處理效率 191
5.1 第1個技巧：批量填充 191
5.2 第2個技巧：批量填充上一個單元格中的內容 191
5.3 第3個技巧：把不規範的日期轉為規範的日期 192
5.4 第4個技巧：自動為單元格添加邊框 193
5.5 第5個技巧：使單元格中的內容自動適合列寬 194
5.6 第6個技巧：批量快速定義單元格區域名稱 195
5.7 第7個技巧：Tab鍵的妙用 195
5.8 第8個技巧：設置文檔自動保存時間 196
5.9 第9個技巧：從身份證號碼中提取出生日期 196
5.10 第10個技巧：製作斜線表頭 197
5.11 第11個技巧：計算文本表達式 198
5.12 第12個技巧：凍結單元格 199
5.13 第13個技巧：標示單元格中的重復值 200
5.14 第14個技巧：給工作簿加密 201
5.15 第15個技巧：使用快捷鍵Ctrl+快速對比兩列數據 202
5.16 第16個技巧：使用快捷鍵Alt+=一鍵求和 202
5.17 第17個技巧：快速合並單元格中的內容 203
5.18 第18個技巧：隔列復制數據 203
5.19 第19個技巧：輸入當前的日期和時間 204
5.20 第20個技巧：數值和日期之間的轉換 204
5.21 第21個技巧：妙用快捷鍵F4隔行插入空行 205
5.22 第22個技巧：使用快捷鍵F4切換單元格引用方式 205
5.23 第23個技巧：輸入1顯示“男”；輸入2顯示“女” 206
5.25 第24個技巧：顯示和隱藏Excel的功能區 206
5.26 第25個技巧：跨列居中優於合並單元格 207
5.27 第26個技巧：如何輸入以0開頭的數字 208
5.28 第27個技巧：通過自定義單元格格式快速錄入數據 208
5.29 第28個技巧：快速跳轉到數據列的最後一個單元格 209
5.30 第29個技巧：讓每一頁工作表打印出來都有標題行 209
5.31 第30個技巧：把“*”替換成“×” 210
5.32 第31個技巧：使用快捷鍵Alt+↓快速彈出下拉菜單 211
5.33 第32個技巧：按所選內容進行篩選 211
5.34 第33個技巧：使用快捷鍵Ctrl+D復制上一個單元格中的內容 212
5.35 第34個技巧：在多個工作表中批量輸入 212
5.36 第35個技巧：設置單元格區域保護 213
5.37 第36個技巧：設置數值以萬為單位 214
5.38 第37個技巧：如何讓復制的表格列寬不變 215
5.39 第38個技巧：快速打開“選擇性粘貼”對話框 216
5.40 第39個技巧：快速添加邊框 216
5.41 第40個技巧：快速刪除邊框 217