辦公效率提升之ECEL常用函數(shù)培訓(xùn)(市場口、縣區(qū))

1、網(wǎng)絡(luò)部唐桂瓊 6405辦公效率提升EXCEL常用函數(shù)講解EXCEL辦公能力摸底測評以下問題，您是前者還是后者？前者：分分鐘之內(nèi)搞定后者：不知道要多長時(shí)間1、上萬行用戶號碼，要判斷后四位是否帶“4”2、從成千上萬個(gè)用戶號碼中找出重復(fù)的號碼并把重復(fù)的號碼剔除3、根據(jù)已有的上千行的用戶號碼、保底消費(fèi)信息，得出現(xiàn)有表中用戶號碼的保底消費(fèi)額；4、一個(gè)表是手工錄入的參加活動的用戶號碼，一個(gè)表是系統(tǒng)導(dǎo)出的參加活動的用戶號碼，都是上千行記錄，對比兩者有無差異5、上萬行用戶號碼，知道用戶的入網(wǎng)時(shí)間，要判斷出用戶屬于哪個(gè)網(wǎng)齡段6、根據(jù)表中每個(gè)營業(yè)廳的移動座機(jī)銷售明細(xì)，要求統(tǒng)計(jì)每個(gè)營業(yè)廳銷售了多少臺，各種型號的移動

2、座機(jī)分別銷售了多少臺，每個(gè)營業(yè)廳的座機(jī)采購總價(jià)是多少EXCEL函數(shù)可以幫助您測試題1常用字符處理函數(shù)應(yīng)用上萬行用戶號碼，要判斷后四位是否帶“4”解題步驟：1、因不清楚用戶號碼是否錄入規(guī)范，可能有空格等，先用=TRIM()去除空格，然后用=LEN()統(tǒng)計(jì)用戶號碼長度，看是否是11位。2、用=RIGHT()截取后四位數(shù)。3、用=FIND()查找4所在的位置。4、根據(jù)查找結(jié)果，能找到4的位置數(shù)表示帶“4”，不能找到4的位置結(jié)果為#VALUE!表示不帶“4”。具體操作見附件常用的字符處理函數(shù) =LEFT() 從左往右取字符 =RIGHT() 從右往左取字符 =MID() 從中間指定位置開始往右取一定個(gè)

3、數(shù)的字符 =LEN() 計(jì)算單元格字符的個(gè)數(shù) & 連接號，將兩串或以上字符連接在一起 =FIND() 查找指定的某個(gè)字符所在的位置 =TRIM() 去除空格，但如果字符與字符之間有空格，保留一個(gè)空格 具體操作見附件測試題2IF函數(shù)剔重 從成千上萬行號碼中找出重復(fù)的號碼并把重復(fù)的號碼剔除 解題步驟：1、先將號碼列排序2、用=IF()判斷下一行和上一行是否相同，相同為0，不同為1，篩選出為0的，肯定是與為1的有重復(fù)的，把為0的刪除。具體操作見附件 測試題3VLOOK查詢函數(shù)引用數(shù)據(jù) 根據(jù)已有的上千行的用戶號碼、保底消費(fèi)信息，得出現(xiàn)有表中用戶號碼的保底消費(fèi)額；解題步驟：1、用=VLOOKUP（）將用

4、戶號碼在已有信息表中查詢，如果找到一樣的，就把已有信息表中該號碼對應(yīng)的保底信息引用粘貼過來，如果找不到一樣的，則反饋#N/A;具體操作見附件 測試題4VLOOK查詢函數(shù)比對應(yīng)用 一個(gè)表是手工錄入的參加活動的用戶號碼，一個(gè)表是系統(tǒng)導(dǎo)出的參加活動的用戶號碼，都是上千行記錄，對比兩者有無差異解題步驟：1、如果都只有號碼，需注明來源以示區(qū)分;2、用=VLOOKUP（）將系統(tǒng)中的號碼與手工錄入的號碼查詢比對，如果找到一樣的，就把手工錄入表中號碼對應(yīng)的信息引用過來，如果找不到一樣的，就反饋#N/A;3、再用=VLOOKUP（）將手工錄入的號碼與系統(tǒng)導(dǎo)出的號碼查詢比對，如果找到一樣的，就把系統(tǒng)導(dǎo)出表中號碼對

5、應(yīng)的信息引用過來，如果找不到一樣的，就反饋#N/A;4、兩個(gè)表都篩選出#N/A，則是兩個(gè)表存在差異的號碼。具體操作見附件 VLOOKUP最最最常用的函數(shù)=VLOOKUP() 以兩個(gè)表中共同的字段為關(guān)鍵字段，以關(guān)鍵字段進(jìn)行查詢，找到和關(guān)鍵字段完全相同的就把它所在行對應(yīng)的其他信息引用到特定的單元格中，其作用相當(dāng)于“無數(shù)次重復(fù)的查找、復(fù)制、粘貼”。常用于兩個(gè)表的數(shù)據(jù)進(jìn)行比對、把一個(gè)表中的數(shù)據(jù)引用到另一個(gè)表中，無需考慮排序問題。=VLOOKUP(參數(shù)1，參數(shù)2，參數(shù)3，0） 參數(shù)1，進(jìn)行查詢的關(guān)鍵字段所在的單元格或所在列 參數(shù)2，要在哪個(gè)表中查詢就點(diǎn)到哪個(gè)表，從雙方共有的關(guān)鍵字段所在列拖動到要引用信息

6、所在的列 參數(shù)3，從雙方共有的關(guān)鍵字段所在列拖動到要引用信息所在的列，拖動了幾列，就輸入幾，比如A到D列，就輸入4測試題5LOOKUP區(qū)間段查詢函數(shù)應(yīng)用 上萬行用戶號碼，知道用戶的入網(wǎng)時(shí)間，要判斷出用戶屬于哪個(gè)網(wǎng)齡段解題步驟：1、用=TODAY（）得出當(dāng)前的日期2、用=DAYS360（）得出當(dāng)前與入網(wǎng)日期相差的天數(shù)（即在網(wǎng)天數(shù)）;3、將在網(wǎng)天數(shù)折算為年;4、根據(jù)網(wǎng)齡段確定每個(gè)網(wǎng)齡段的起始值5、用=LOOKUP（）將得出的在網(wǎng)年限與每個(gè)網(wǎng)齡段的起始值進(jìn)行比對，判斷得出所屬的網(wǎng)齡段。具體操作見附件 =LOOKUP() 根據(jù)某列數(shù)據(jù)，從一組區(qū)間段數(shù)據(jù)中查詢，得出該列數(shù)據(jù)所屬的對應(yīng)區(qū)間段。其作用相當(dāng)于

7、“無數(shù)次設(shè)定篩選條件篩選，然后將結(jié)果填入”，或者相當(dāng)于“將多個(gè)IF函數(shù)嵌套使用”。LOOKUP區(qū)間段查詢函數(shù)應(yīng)用2 成千上萬用戶號碼，知道每個(gè)號碼對應(yīng)的編號，要根據(jù)編號段對應(yīng)的營業(yè)廳名稱，得出每個(gè)用戶號碼屬于哪個(gè)營業(yè)廳解題方法1：1、先用=LEFT()得出編號段的起始值，然后“復(fù)制選擇性粘貼數(shù)值加”，將屬性轉(zhuǎn)換為數(shù)值；2、用=LOOKUP()得出每個(gè)號碼的編號屬于哪個(gè)編號段；3、用=VLOOKUP()將每個(gè)編號段對應(yīng)的營業(yè)廳名字引用粘貼過來。解題方法2：1、先用=LEFT()得出編號段的起始值，并將屬性轉(zhuǎn)換為數(shù)值；2、用=LOOKUP() 以B列編號為查詢的關(guān)鍵字段，到原始信息表中查詢，先判斷

8、屬于哪個(gè)編號段，然后將該編號段對應(yīng)的營業(yè)廳的名稱引用粘貼過來。具體操作見附件 測試題6常用統(tǒng)計(jì)函數(shù)應(yīng)用根據(jù)表中每個(gè)營業(yè)廳的移動座機(jī)銷售明細(xì)，要求統(tǒng)計(jì)每個(gè)營業(yè)廳銷售了多少臺，各種型號的移動座機(jī)分別銷售了多少臺，每個(gè)營業(yè)廳的座機(jī)終端采購總價(jià)是多少解題步驟：1、先以營業(yè)廳排序，用=IF()然后篩選得出不同的營業(yè)廳名稱，然后用=COUNTIF()以不同的營業(yè)廳名稱為統(tǒng)計(jì)條件，得出每個(gè)營業(yè)廳的銷售臺數(shù)。2、以座機(jī)型號排序，用=IF()然后篩選得出不同的座機(jī)型號，然后用=COUNTIF()以不同的座機(jī)型號為統(tǒng)計(jì)條件，得出每種型號座機(jī)的銷售臺數(shù)。3、用=SUMIF()以不同的營業(yè)廳為求和條件，得出每個(gè)營業(yè)廳的座機(jī)終端采購總價(jià)。具體操作見附件如何自學(xué)函數(shù)如何自學(xué)：1、利用先前所學(xué)的函數(shù)能否解決，LEFT函數(shù)?不行，因?yàn)長EFT函數(shù)只能對文本字符進(jìn)行操作，入網(wǎng)時(shí)間是日期格式，不可行。2、我們需要新學(xué)函數(shù)，點(diǎn)擊fx,彈出“插入函數(shù)”的對話框，