目錄CONTENTS?養(yǎng)成好習(xí)慣01養(yǎng)成好習(xí)慣2.2偷師………………272.2.12.2.22.2.32.2.42.2.52.2.62.2.72.2.82.2.92.2.102.2.112.2.12輸入多個(gè)0有技巧…………28錄入特殊字符……………29只允許輸入某范圍的日期………………31防止錄入重復(fù)姓名………31數(shù)據(jù)有效性其實(shí)很好騙…33眼見(jiàn)不一定為實(shí)…………34所見(jiàn)即所得………………35哪里不同刷哪里…………36將兩列的內(nèi)容合并成一列………………37將行變成列………………37給單元格加把鎖…………38轉(zhuǎn)換成PDF，放心將文檔交給供應(yīng)商…401.11.21.31.4了解Excel的四類表格…2以身說(shuō)法………………7注重細(xì)節(jié)……………12本章小結(jié)……………142.3本章小結(jié)……………4002向有經(jīng)驗(yàn)的人學(xué)習(xí)03常用小技巧2.1再當(dāng)一回學(xué)生………162.1.12.1.22.1.32.1.42.1.52.1.62.1.72.1.8初識(shí)Excel…………………16這條線怎么畫(huà)……………16怎么邊框顯示不全………19輸入的0跑哪去了…………20分?jǐn)?shù)怎么變成了日期……22生成編號(hào)序列……………22不相鄰單元格輸入同一數(shù)據(jù)……………24快速錄入大量小數(shù)………263.13.2排序………………423.1.13.1.2排序其實(shí)很簡(jiǎn)單…………42按城市的發(fā)展情況排序…43篩選………………453.2.13.2.2篩選中的搜索功能………45將帶顏色項(xiàng)目挑選出來(lái)…46V

目錄3.2.3借助高級(jí)篩選，讓篩選數(shù)據(jù)更貼心……474.2.44.2.54.2.64.2.74.2.84.2.94.2.10無(wú)處不在的IF……………95學(xué)VLOOKUP，認(rèn)識(shí)Excel愛(ài)好者………983.33.4分列………………49LOOKUP潮汕…………經(jīng)典的INDEX+MATCH組合…………1051121171203.3.13.3.23.3.3按分隔符分列……………49將姓名分成兩列顯示……50將不標(biāo)準(zhǔn)日期轉(zhuǎn)換成標(biāo)準(zhǔn)日期…………51OFFSET的運(yùn)用百變神君TEXT………………字符提取MID、LEFT零散小技巧…………52和RIGHT三兄弟……1241273.4.1閃電式數(shù)據(jù)填充…………52刪除錯(cuò)誤值………………54讓數(shù)據(jù)輸入更簡(jiǎn)單………57重復(fù)值問(wèn)題………………58套用表格樣式……………604.2.11SUBSTITUTE的運(yùn)用…3.4.23.4.33.4.43.4.54.34.4為自己量身定做的函數(shù)…………1294.3.14.3.24.3.34.3.4連接所有字符LJ………將借閱的書(shū)籍合并HB…提取批注內(nèi)容PZ………自動(dòng)錄入當(dāng)前時(shí)間NowTime…………1301331351363.5本章小結(jié)……………62本章小結(jié)…………1380405最受歡迎的函數(shù)與公式看透數(shù)據(jù)的數(shù)據(jù)透視表4.1一起來(lái)學(xué)習(xí)函數(shù)與公式……………644.1.1基本概述…………………64初識(shí)函數(shù)…………………66快速、準(zhǔn)確輸入函數(shù)的兩種方法………67輸入公式三招……………68三種不同引用方式………69兩種不同引用樣式………70借助名稱讓公式更容易理解……………73了解另外一片天地——數(shù)組公式………744.1.24.1.34.1.44.1.54.1.64.1.74.1.85.15.2多變的要求………1405.1.1煩人的1234……………140跟領(lǐng)導(dǎo)聊聊數(shù)據(jù)透視表…………1425.2.15.2.25.2.35.2.45.2.55.2.65.2.75.2.8什么是數(shù)據(jù)透視表……多角度分析數(shù)據(jù)………更改值的匯總依據(jù)……排序讓數(shù)據(jù)更加直觀…原來(lái)手工也能排序……對(duì)銷售額進(jìn)行排名……篩選銷售額前5名的客戶………………讓更改的數(shù)據(jù)源隨時(shí)刷新……………1421431471481491501511514.2函數(shù)經(jīng)典再現(xiàn)………774.2.1閑聊SUM…………………78求和之王SUMPRODUCT………………86既生SUMIF（COUNTIF），4.2.24.2.3何生SUMPRODUCT……90VI

目錄5.2.95.2.10插入表格，讓數(shù)據(jù)源“動(dòng)”起來(lái)……日期組合真奇妙………1541555.2.11手工組合，實(shí)現(xiàn)客戶分級(jí)……………158075.2.125.2.13善借輔助列，實(shí)現(xiàn)客戶實(shí)際銷售額分析…利用數(shù)據(jù)透視圖讓分析更直觀………1601605.35.4愜意的生活………165學(xué)E千日，5.3.1準(zhǔn)備好數(shù)據(jù)源，讓領(lǐng)導(dǎo)自己分析去…165用在一時(shí)本章小結(jié)…………166067.17.2年度數(shù)據(jù)分析……1887.1.17.1.27.1.3數(shù)據(jù)核對(duì)………………數(shù)據(jù)匯總………………數(shù)據(jù)分析………………189191195神奇的SQL語(yǔ)句本章小結(jié)…………200086.16.26.3系統(tǒng)是浮云………168SQL是神馬………168試探性查詢………170在娛樂(lè)中學(xué)習(xí)6.3.16.3.26.3.36.3.4認(rèn)識(shí)SQL的儲(chǔ)存地……查詢所有記錄…………查詢不重復(fù)客戶………查詢符合條件的客戶…1701741751768.18.2模仿手機(jī)功能……2026.4有目的統(tǒng)計(jì)………1778.1.18.1.28.1.38.1.48.1.58.1.6手機(jī)號(hào)碼的顯示格式…姓名簡(jiǎn)稱………………防騷擾…………………聯(lián)系人去重復(fù)…………智能撥號(hào)………………QQ通訊錄開(kāi)啟人員……2022032042062072086.4.16.4.26.4.3統(tǒng)計(jì)豬肉金額…………統(tǒng)計(jì)批發(fā)跟零售金額…統(tǒng)計(jì)產(chǎn)品用量…………1771791806.56.6強(qiáng)強(qiáng)聯(lián)合，見(jiàn)證神奇……………1816.5.16.5.2進(jìn)銷存分析……………資金流動(dòng)分析…………181184其實(shí)我是算命的…209本章小結(jié)…………1868.2.1戲說(shuō)生辰八字…………210VII

目錄8.2.28.2.38.2.48.2.58.2.6星座學(xué)…………………生日密碼………………面相……點(diǎn)菜無(wú)煩惱……………別用你的無(wú)知挑戰(zhàn)概率………………2112122142152168.38.4不會(huì)數(shù)學(xué)計(jì)算，Excel來(lái)幫你……2178.3.18.3.28.3.38.3.48.3.5數(shù)學(xué)基本計(jì)算…………解方程…………………預(yù)測(cè)數(shù)字………………隨機(jī)抽樣………………利潤(rùn)最大化……………217218221223224本章小結(jié)…………22625709后記用合適的方法做合適的事9.1簡(jiǎn)單就好…………2289.1.19.1.29.1.39.1.49.1.59.1.69.1.79.1.8粘貼成圖片，排版無(wú)煩惱……………計(jì)算文本表達(dá)式………利用排序生成工資條…多表數(shù)據(jù)核對(duì)…………多行多列提取不重復(fù)…逗號(hào)分隔符……………提取數(shù)字………………多表關(guān)聯(lián)………………2282312322342352382402439.29.3聊聊跟Excel有關(guān)的事兒…………2479.2.19.2.2不知者無(wú)罪……………假如生活就是Excel……247255本章小結(jié)…………256VIII

剛開(kāi)始接觸Excel會(huì)遇到各種疑難，如一些數(shù)據(jù)錄入技巧。只要你虛心請(qǐng)教別人，別人也會(huì)樂(lè)于教你。但每個(gè)人都有自己的為人處事方法，不要因?yàn)閯e人態(tài)度冷漠而退縮，還有就是對(duì)你再好的人也只能幫助你一時(shí)而已，不可能幫助你一世。很多東西還得靠自己自覺(jué)去學(xué)習(xí)，在摸索中成長(zhǎng)。雖然這個(gè)成長(zhǎng)過(guò)程并不會(huì)太快，但工作效率卻在逐步提高。02

Excel效率手冊(cè)——早做完，不加班2.1再當(dāng)一回學(xué)生畢業(yè)并不代表學(xué)習(xí)的結(jié)束，而是另一種學(xué)習(xí)的開(kāi)始。工作上我們會(huì)碰到各種各樣的新事物，不懂的要及時(shí)向有經(jīng)驗(yàn)的人請(qǐng)教。請(qǐng)教別人不僅能更快掌握知識(shí)，同時(shí)也能增進(jìn)彼此間的感情。從今天起，就一起跟著盧子學(xué)習(xí)錄入數(shù)據(jù)的技巧吧！2.1.1初識(shí)Excel2007年，盧子放棄讀大學(xué)的機(jī)會(huì)只身來(lái)到了東莞工作，還好有香姐照顧，工作、生活都挺順利。剛來(lái)公司，香姐就告訴盧子有空的話要學(xué)點(diǎn)Excel的知識(shí)，這個(gè)在工作上經(jīng)常用到。盧子那時(shí)啥也不懂，既然這個(gè)有用，那下班后就學(xué)習(xí)一下。那時(shí)晚上辦公室是開(kāi)放的，允許在里面用電腦。盧子來(lái)到辦公室，打開(kāi)了Excel，一看除了格子外還是格子，這能干嘛呢？先不管三七二十一，在格子里輸入：“我在學(xué)習(xí)Excel，這個(gè)有什么用呢？我相當(dāng)?shù)睾闷??！比鐖D2-1所示，沒(méi)想到小小格子里可以容納那么多內(nèi)容，挺神奇的。往后幾天，Excel就成了盧子抒發(fā)心情的地方，一有什么想法就寫(xiě)在里面。呵呵，其實(shí)盧子的打字水平也是在這個(gè)時(shí)候提高的。圖2-1第一次輸入的內(nèi)容2.1.2這條線怎么畫(huà)剛開(kāi)始上班挺閑的，有空的話就看資料，學(xué)習(xí)Excel。突然有一天，我打開(kāi)了一個(gè)表格，發(fā)現(xiàn)有些字下面有一條線，如圖2-2所示。心想這個(gè)是怎么畫(huà)的呢？看見(jiàn)香姐并不忙，于是就上前請(qǐng)教問(wèn)題。圖2-2文字下面的線怎么畫(huà)盧子：姐，文字下面這條線是怎么畫(huà)出來(lái)的，怎么以前沒(méi)見(jiàn)過(guò)？16

第2章?向有經(jīng)驗(yàn)的人學(xué)習(xí)香姐：這個(gè)是下劃線，具體的操作是，選擇B3單元格，在編輯欄用鼠標(biāo)選中“隆成(新廠)”，然后切換到“開(kāi)始”選項(xiàng)卡，再單擊“U”按鈕，如圖2-3所示。圖2-3添加下劃線盧子：姐，回頭我試試看。香姐：還可以用插入直線，不過(guò)這種方法有點(diǎn)麻煩，你了解下也好。如圖2-4所示，切換到“插入”選項(xiàng)卡，然后單擊“形狀”圖標(biāo)，再選擇直線。按住Shift鍵，然后拉到合適的長(zhǎng)度。圖2-4插入直線盧子：這個(gè)Shift鍵有什么作用，不用它也可以畫(huà)出一條直線。香姐：按Shift鍵是保證畫(huà)出來(lái)的線是直線，如圖2-5所示如果僅僅是畫(huà)一條直線按不按它關(guān)系不大，但如果是畫(huà)一個(gè)圓呢，你能保證畫(huà)出來(lái)的是一個(gè)圓？有的細(xì)節(jié)要從一開(kāi)始就注意。圖2-5Shift鍵的作用盧子：如果要畫(huà)正方形是不是也這樣操作？香姐：沒(méi)錯(cuò)，小腦袋轉(zhuǎn)得挺快的。如果要畫(huà)正圓、正方形等，也需要按Shift鍵。盧子：姐，今天就麻煩你了，回頭我將你教的這兩個(gè)方法練習(xí)一下。香姐：這么客氣干嘛，我們又不是外人。對(duì)了，晚上有空的話，一起到阿伯那里喝茶。盧子：好的，來(lái)這邊這么久還沒(méi)喝過(guò)功夫茶，還有點(diǎn)小小的懷念家鄉(xiāng)的功夫茶。香姐：來(lái)到這邊就不要客氣，我們是“膠己人”。17

Excel效率手冊(cè)——早做完，不加班溫馨提示出門(mén)在外，潮汕地區(qū)的人遇到老鄉(xiāng)，都稱呼“膠己人”，雖然只有三個(gè)字，但意義非同一般。功夫茶小知識(shí)品茶禮儀傳統(tǒng)的潮汕功夫茶一般只有3個(gè)杯子，不管多少客人都只用3個(gè)杯子。第一杯茶一定先給左手第一位客人，無(wú)論其身份尊卑，無(wú)論其年齡大小，也無(wú)分性別。每喝完一杯茶要用滾燙的茶水洗一次杯子，然后再把帶有熱度的杯子給下一個(gè)用。這種習(xí)俗據(jù)說(shuō)是人們?yōu)榱吮硎緢F(tuán)結(jié)、友愛(ài)和互相謙讓的美好品德。品茶，要先聞香味，然后看茶湯的顏色，最后才是品味道，一杯茶要?jiǎng)偤梅譃槿谄吠?。香味從舌尖逐漸向喉嚨擴(kuò)散，最后一飲而盡，可謂暢快淋漓。這就是功夫茶的三個(gè)境界——“芳香溢齒頰，甘澤潤(rùn)喉嚨，神明凌霄漢”。據(jù)說(shuō)專業(yè)的品茶師可以憑一杯茶品出茶藝師當(dāng)時(shí)的心情。說(shuō)得很玄，不過(guò)功夫茶本來(lái)不就是一種平和心境、修身養(yǎng)性的方法嘛。日常生活潮汕工夫茶，在潮汕地區(qū)深受人們喜愛(ài)，不少人早上起來(lái)就泡上一壺茶，倦意頓時(shí)一掃而光，只覺(jué)得神清氣爽。潮汕人喜歡以茶會(huì)友，在細(xì)品慢酌、談笑風(fēng)生中，人們互通信息、加深了感情。品茶早已超越了簡(jiǎn)單的解渴的目的，它還蘊(yùn)含著豐富的文化內(nèi)容。六合家宴的江經(jīng)理介紹說(shuō)，潮汕人把茶叫做“茶米”，茶在潮汕人心目中就像米一樣，足以看到潮汕人嗜茶如命，茶與米的不可分了。到城中的高檔潮菜館吃飯，席間總是穿插上功夫茶。在你吃完海鮮魚(yú)肉的時(shí)候，喝一杯可以消除腥味；在你吃著一碗甜品有點(diǎn)膩的時(shí)候，喝一杯可以去膩開(kāi)胃；在你酒足飯飽覺(jué)得有點(diǎn)撐的時(shí)候，喝一杯解乏消滯。功夫茶與潮菜，就像一個(gè)硬幣的兩面，相輔相成，共同造就了潮汕飲食文化的博大精深。有人說(shuō)吃潮菜不喝功夫茶，總是感覺(jué)不太正宗。18

第2章?向有經(jīng)驗(yàn)的人學(xué)習(xí)2.1.3邊框怎么顯示不全香姐：這是列寬太小導(dǎo)致的。如圖2-7所示，只需選中B列，在標(biāo)題欄向右拖拉到列寬可以容納所有內(nèi)容為止?，F(xiàn)在邊框是不是自動(dòng)出來(lái)了？盧子：姐，我發(fā)現(xiàn)一件怪事，有一個(gè)單元格不管我怎么設(shè)置邊框都不管用，死活不顯示出來(lái)，如圖2-6所示。怎么回事呢？????圖2-6邊框顯示不全圖2-7調(diào)整列寬盧子：如果有多列需要調(diào)整列寬，有沒(méi)有快捷一點(diǎn)的操作方法？香姐：如圖2-8所示，選擇E~H四列，雙擊H列標(biāo)題欄就可以自動(dòng)調(diào)整列寬。圖2-8自動(dòng)調(diào)整列寬盧子：又學(xué)到了一招，還是這招自動(dòng)調(diào)整列寬好用。香姐：有的時(shí)候還可以通過(guò)雙擊獲取最合適的行高。工作上還有一種情況就是行高跟列寬為固定值，這時(shí)就不能通過(guò)這兩種方法來(lái)調(diào)整。如圖2-9所示，切換到“開(kāi)始”選項(xiàng)卡，再單擊“格式”下拉按鈕，選擇“列寬”選項(xiàng)，更改列寬大小，最后單擊“確定”按鈕。用同樣的方法，可設(shè)置行高。圖2-9設(shè)置列寬大小19

Excel效率手冊(cè)——早做完，不加班盧子：我剛才看你設(shè)置列寬的時(shí)候發(fā)現(xiàn)一個(gè)問(wèn)題，這些寬度都是以像素作為單位，但我們平常都是以“cm”作為單位，就如職工的照片寬度是3cm。如何讓像素變成以cm為單位的列寬呢？盧子：沒(méi)想到小小的列寬就有這么多學(xué)問(wèn)！圖2-10“像素”變“cm”香姐：這個(gè)問(wèn)題問(wèn)得好。如圖2-11所示，切換到“視圖”選項(xiàng)卡，再單擊“頁(yè)面布局”圖標(biāo)，然后選擇A列，右擊并從彈出的快捷菜單中選擇“列寬”命令，這時(shí)就是以“cm”作為單位了。將數(shù)字改成3，再單擊“確定”按鈕。用同樣的方法，可設(shè)置行高。圖2-11設(shè)置以“cm”作為單位的列寬2.1.4輸入的0跑哪去了盧子：姐，我輸入了好多次，出貨日期那里的數(shù)字，不管我怎么輸入，0都會(huì)消失，如圖2-12所示，不知道它跑哪去了？香姐：常規(guī)格式下，數(shù)字前面的0會(huì)自動(dòng)被忽略，可以將單元格設(shè)置為文本。具體操作如圖2-13所示，利用組合鍵Ctrl+1，彈出“設(shè)置單元格格式”對(duì)話框。切換到“數(shù)字”選項(xiàng)卡，然后選擇“文本”項(xiàng)，再單擊“確定”按鈕。返回單元格重新輸入數(shù)字，這時(shí)得到的就是文本數(shù)字，文本數(shù)字不管你輸入什么內(nèi)容都不會(huì)改變。圖2-12自動(dòng)消失的0圖2-13設(shè)置文本格式20

第2章?向有經(jīng)驗(yàn)的人學(xué)習(xí)盧子：這個(gè)真好用，0再也跑不掉了。香姐：平常在輸入身份證號(hào)等長(zhǎng)字符串時(shí)，數(shù)字超過(guò)15位都是變成0，只能先將單元格設(shè)置為“文本”格式再輸入才行。因?yàn)槲覀児镜某鲐浫掌诟袷蕉际?位數(shù)，這個(gè)也可以通過(guò)自定義單元格格式做到。按組合鍵Ctrl+1，彈出“設(shè)置單元格格式”對(duì)話框。切換到“數(shù)字”選項(xiàng)卡，然后選擇“自定義”選項(xiàng)，設(shè)置自定義格式代碼，再單擊“確定”按鈕，如圖2-14所示。設(shè)置單元格格式有很多學(xué)問(wèn)，以后你可以慢慢了解。圖2-14自定義單元格格式溫馨提示N年以后，盧子發(fā)現(xiàn)了日期格式的秘密。香姐教我的方法雖然可以解決問(wèn)題，但這樣的日期并不是標(biāo)準(zhǔn)日期，在對(duì)日期進(jìn)行分析時(shí)會(huì)有小小的麻煩。按快捷鍵Ctrl+；可以快速輸入當(dāng)天的日期(靜態(tài))，然后設(shè)置自定義格式代碼為YYMMDD。其中Y代表年，M代表月，D代表日。YY代表年為兩位，MM、DD也是同樣的道理。這樣設(shè)置有一個(gè)好處，就是以后在用透視表的自動(dòng)組合時(shí)，可以輕松按年、月、日分組。如果要獲取動(dòng)態(tài)日期，可以用=TODAY()，如圖2-15所示。圖2-15獲取動(dòng)靜態(tài)當(dāng)天日期21

Excel效率手冊(cè)——早做完，不加班2.1.5分?jǐn)?shù)怎么變成了日期盧子：姐，我輸入的分?jǐn)?shù)怎么總是變成了日期呢？如圖2-16所示，Excel老是自作主張改我的東西。補(bǔ)充說(shuō)明Excel會(huì)對(duì)你輸入的分?jǐn)?shù)進(jìn)行化簡(jiǎn)、約分，將輸入的分?jǐn)?shù)化為帶分?jǐn)?shù)或最簡(jiǎn)真分?jǐn)?shù)。如果你不需要被自作主張改掉，可以通過(guò)設(shè)置自定義單元格格式，得到圖2-17所示的效果。圖2-16分?jǐn)?shù)自動(dòng)變成了日期香姐：前面跟你講過(guò)將單元格設(shè)置為“文本”格式，其實(shí)也可以用在這個(gè)地方，不過(guò)這樣雖然看起來(lái)是分?jǐn)?shù)，實(shí)際上并不是。這里先給你介紹一下分?jǐn)?shù)的小知識(shí)。假分?jǐn)?shù)0/0不被化簡(jiǎn)的分?jǐn)?shù)：分母固定為8。0/8分?jǐn)?shù)的儲(chǔ)存形式如圖2-17所示，分?jǐn)?shù)由五部分組成：整數(shù)部分+空格+分子+斜杠(/)+分母，一個(gè)都不能少，即使整數(shù)部分為0也不能省略。輸入分?jǐn)?shù)后，在編輯欄會(huì)顯示分?jǐn)?shù)對(duì)應(yīng)的小數(shù)的值。圖2-18分?jǐn)?shù)自定義后的顯示盧子：回頭我好好理解一下。轉(zhuǎn)折點(diǎn)聽(tīng)說(shuō)香姐懷孕了，要當(dāng)媽媽了，這是一個(gè)好消息，以后我就不能老麻煩她了。從今以后要換一種方式來(lái)學(xué)習(xí)Excel，畢竟不是每個(gè)人都對(duì)你這么友善，耐心指導(dǎo)你的。圖2-17分?jǐn)?shù)的組成形式2.1.6生成編號(hào)序列今天盧子在給產(chǎn)品輸入編號(hào)，按下面的方法操作：輸入1，回車；輸入2，回車；22

第2章?向有經(jīng)驗(yàn)的人學(xué)習(xí)輸入3，回車；……突然，盧子停下了敲打鍵盤(pán)的手，思索著：應(yīng)該有其他方法的，要不然那么多編號(hào)要輸入到什么時(shí)候？香姐現(xiàn)在的心都放在未出生的寶寶身上，去打擾她又不好。正一籌莫展時(shí)，想起了曾經(jīng)香姐說(shuō)過(guò)的一句話：菜頭是我們公司Excel用得最好的人。想到這里，盧子立馬跑到菜頭面前。盧子：菜頭，向你請(qǐng)教個(gè)問(wèn)題可以嗎？菜頭：可以。盧子：我剛才在輸入編號(hào)，一個(gè)個(gè)手動(dòng)輸入，如圖2-19所示，感覺(jué)輸入很慢，有沒(méi)有快捷的方法？圖2-19手工輸入編號(hào)菜頭：如圖2-20所示，在A2單元格中輸入1，鼠標(biāo)放在A2單元格右下方，出現(xiàn)“+”字形，按住Ctrl鍵，拖動(dòng)鼠標(biāo)到A45就可以生成1~45的編號(hào)。圖2-20自動(dòng)生成編號(hào)序列盧子：謝謝，這回我就省事多了。菜頭：嗯。早就聽(tīng)說(shuō)菜頭這人不好相處，還是溜之大吉，自己去試這個(gè)功能吧。按照菜頭教的方法操作果然成功，試了不按Ctrl鍵數(shù)字全部都是1。23

Excel效率手冊(cè)——早做完，不加班摸索發(fā)現(xiàn)1.編號(hào)序列也可以通過(guò)先在單元格中輸入1、值跟終止值，再單擊“確定”按鈕就可以生成多種形式的序列。2，然后下拉就生成。2.先在單元格輸入2、4，可以生成等差序列，如圖2-21所示。有興趣的朋友可以逐一測(cè)試一下序列的各種功能，如圖2-22所示。圖2-21自動(dòng)生成編號(hào)跟等差3.幾個(gè)月后又發(fā)現(xiàn)序列對(duì)話框N多功能。在“開(kāi)始”選項(xiàng)卡，選擇“編輯”組中的填充，單擊“序列”項(xiàng)，這里提供了好多選擇，只需填寫(xiě)步長(zhǎng)圖2-22“序列”對(duì)話框2.1.7不相鄰單元格輸入同一數(shù)據(jù)在制作不良產(chǎn)品報(bào)告的時(shí)候，經(jīng)常會(huì)輸入多個(gè)相同日期，每次單獨(dú)輸入很麻煩?？吹缴匣夭祟^愿意幫忙，盧子再次跑去請(qǐng)教。盧子：菜頭，如圖2-23所示，有沒(méi)有辦法在不相鄰單元格中同時(shí)輸入當(dāng)天的日期？菜頭：利用Ctrl鍵的三種用途可以實(shí)現(xiàn)。圖2-23在不同單元格中輸入當(dāng)天的日期24

第2章?向有經(jīng)驗(yàn)的人學(xué)習(xí)STEP01按住Ctrl鍵依次選擇D2、D4、C8和C9，如圖2-24所示。盧子：原來(lái)Ctrl鍵這么好用，上回就是借用這個(gè)鍵，快速生成序列的。菜頭：嗯。還是老樣子，連一句多余的話也不說(shuō)。盧子沒(méi)辦法只得回到自己座位上去研究。圖2-24選擇單元格摸索發(fā)現(xiàn)STEP02按快捷鍵Ctrl+；生成當(dāng)天日期，如圖2-25所示。其實(shí)還可以在多個(gè)表的同位置單元格中輸入相同內(nèi)容。如在Sheet2~Sheet5的B2中同時(shí)輸入“盧子”。切換到Sheet2，接著按住Shift鍵再單擊Sheet5，這樣就能選中4個(gè)表格。在B2單元格中輸入“盧子”，按組合鍵Ctrl+Enter結(jié)束。這時(shí)就可以看到這4個(gè)表格同時(shí)輸入好了，輸入后的效果如圖2-27所示。如果輸入的表格不相鄰，要按Ctrl鍵依次選擇。圖2-25生成當(dāng)天日期STEP03按快捷鍵Ctrl+Enter結(jié)束，如圖2-26所示。圖2-27多表格操作圖2-26批量生成日期25

Excel效率手冊(cè)——早做完，不加班2.1.8快速錄入大量小數(shù)領(lǐng)導(dǎo)不知從哪里搞來(lái)了一份單價(jià)表，讓盧子錄入數(shù)據(jù)。盧子仔細(xì)一看，都是一些小零件，價(jià)格都是幾毛錢而已，也就是說(shuō)，所有單價(jià)都是零點(diǎn)幾元。既然是領(lǐng)導(dǎo)安排的工作，盧子馬上著手輸入。結(jié)果輸入了幾個(gè)數(shù)據(jù)后，就發(fā)現(xiàn)了一些小問(wèn)題。這樣輸入單價(jià)挺煩瑣的，都得輸入“0.”，前面都是重復(fù)的，還有就是有時(shí)小數(shù)點(diǎn)還會(huì)點(diǎn)錯(cuò)位置，甚至忘記輸入。如果連這件小事都出錯(cuò)，領(lǐng)導(dǎo)會(huì)怎么看盧子，沒(méi)法只得請(qǐng)教菜頭，雖然菜頭不好說(shuō)話，但一切以工作為主。盧子：菜頭，我這里有份單價(jià)表，但所有數(shù)字都是含有小數(shù)點(diǎn)的，如圖2-28所示。有沒(méi)有辦法快速輸入？菜頭：如圖2-29所示，選擇“文件”，然后依次選擇“選項(xiàng)”命令，單擊“高級(jí)”按鈕，選中“自動(dòng)插入小數(shù)點(diǎn)”復(fù)選框，再單擊“確定”按鈕。選中圖2-28輸入大量帶小數(shù)點(diǎn)數(shù)字圖2-29自動(dòng)插入小數(shù)點(diǎn)盧子知道菜頭的為人，所以也不繼續(xù)多問(wèn)，就自己動(dòng)手嘗試。經(jīng)過(guò)這樣的設(shè)置，只要輸入整數(shù)部分即可，如0.89，只要輸入89。沒(méi)多久，盧子就把領(lǐng)導(dǎo)交代的事情完成了，如圖2-30所示。圖2-30錄入小數(shù)效果26

第2章?向有經(jīng)驗(yàn)的人學(xué)習(xí)摸索發(fā)現(xiàn)趁著空閑，盧子又仔細(xì)看了一下這個(gè)功能。發(fā)覺(jué)小數(shù)點(diǎn)的位數(shù)可以調(diào)節(jié)，允許為負(fù)數(shù)。如果將小數(shù)點(diǎn)位置設(shè)置為負(fù)數(shù)就是擴(kuò)大的倍數(shù)，如-3就是擴(kuò)大10倍。但這個(gè)功能有一個(gè)局限，就是設(shè)置后整個(gè)工作表不管輸入什么3數(shù)字都會(huì)自動(dòng)擴(kuò)大或者縮小，如果有其他數(shù)據(jù)錄入，這個(gè)功能是不可取的。解決方案1：障眼法通過(guò)自定義單元格格式得到。!0!.00擊解決方案2：選擇性粘貼（除）如圖2-31所示，在C1中輸入100，然后復(fù)制C1。選擇A1:A5，右擊并選擇“選擇性粘貼”命令，選中“除”單選按鈕，再單擊“確定”按鈕。這個(gè)方案我覺(jué)得最好，得到的是真正的小數(shù)，而不影響其他內(nèi)容圖2-31選擇性粘貼（除）的輸入。2.2偷?師職場(chǎng)中有兩種人，一種是熱情幫助你的人(香姐)，一種是對(duì)你很冷漠的人(菜頭)。遇到前一種是你的福氣，遇到后一種也不要悲傷，畢竟沒(méi)有人有義務(wù)幫助你，即使可以幫你也只是一時(shí)而已，很多事情都得靠自己努力。身邊從不缺乏有能力的人，只是缺乏發(fā)現(xiàn)這些人的眼睛。只要你留心，很多人都是你學(xué)習(xí)的榜樣。明著我們可以不用請(qǐng)教他們，但暗地里卻可以看這些人以往留下的文檔資料來(lái)學(xué)習(xí)。有時(shí)行走的步伐稍微放慢一點(diǎn)都可以學(xué)到知識(shí)，走到有經(jīng)驗(yàn)的人背后，偷偷瞄一眼都可以學(xué)到一個(gè)技能，曾經(jīng)我就這么干過(guò)。偷學(xué)不在乎技能的大小，只要看到了就學(xué)習(xí)。日積月累，常用的小技巧都將被你所掌握。27

Excel效率手冊(cè)——早做完，不加班2.2.1輸入多個(gè)0有技巧我們公司每天的出入賬都是上百萬(wàn)，甚至上千萬(wàn)。這么大的金額，每次輸入的時(shí)候都得數(shù)有多少個(gè)0，怕輸入錯(cuò)誤。如圖2-32所示，怎么才可以快速、準(zhǔn)確輸入這么多個(gè)0呢？教你一招，既能準(zhǔn)確輸入，又能快速有效。圖2-32快速無(wú)誤輸入多個(gè)0STEP01如圖2-33所示，利用組合鍵Ctrl+1調(diào)出“設(shè)置單元格格式”對(duì)話框。選擇“貨幣”選項(xiàng)，小數(shù)點(diǎn)位數(shù)改為0，單擊“確定”按鈕。圖2-33將單元格設(shè)置為沒(méi)有小數(shù)點(diǎn)的貨幣樣式STEP02如圖2-34所示，輸入：數(shù)字**N，只能用兩個(gè)*，N代表幾個(gè)0。圖2-34快速、準(zhǔn)備輸入多個(gè)0的技巧28

函數(shù)與公式可以說(shuō)是Excel的精髓，每天都有無(wú)數(shù)人在討論她的用法。基本上每天都有一些精妙的公式被發(fā)掘出來(lái)。學(xué)公式靠的是邏輯思維和思考問(wèn)題的方法(角度)，不像技巧那樣需死記硬背。不經(jīng)過(guò)認(rèn)真思考、舉一反三是永遠(yuǎn)學(xué)不好公式的。學(xué)好公式，絕大部分的工作都可以輕松搞定。曾經(jīng)有人說(shuō)過(guò)，只要學(xué)好公式再配合一些技巧，你就可以成為高手。讓我們一起向高手邁進(jìn)一小步！04

Excel效率手冊(cè)——早做完，不加班4.2.1閑聊SUM盧子：你會(huì)SUM函數(shù)嗎？還沒(méi)等盧子繼續(xù)說(shuō)下去，網(wǎng)友就耐不住性子了。網(wǎng)友：別開(kāi)玩笑啦，這個(gè)誰(shuí)不會(huì)。如圖4-44所示，選擇單元格A7，在“公式”選項(xiàng)卡里，單擊“自動(dòng)求和”按鈕，就自動(dòng)對(duì)區(qū)域進(jìn)行求和，簡(jiǎn)單得要死。網(wǎng)友：幫助也不過(guò)如此，就是對(duì)所有數(shù)字求和，我以為有什么稀奇。盧子：實(shí)際幫助說(shuō)到的用法，僅僅是最基礎(chǔ)的用法，連SUM的冰山一角還沒(méi)有見(jiàn)著。網(wǎng)友：有這么夸張嗎，你倒是說(shuō)說(shuō)看？盧子：這里通過(guò)兩部分來(lái)說(shuō)明SUM的用法——基礎(chǔ)用法與知識(shí)擴(kuò)展?；A(chǔ)用法案例1：如圖4-46所示，這是一份每月銷售清單?，F(xiàn)在想按月份累計(jì)銷售額，該怎么辦？圖4-44自動(dòng)求和盧子：你還知道SUM的其他用法嗎？網(wǎng)友：這個(gè)不就自動(dòng)求和而已，還有什么用法？盧子：按F1鍵調(diào)出幫助，輸入sum搜索，會(huì)出現(xiàn)這個(gè)函數(shù)的用法，如圖4-45所示，圖4-46每月銷售清單選擇C2單元格，輸入公式，并向下填充公式。=SUM(B$2:B2)給第一個(gè)B2的行塞點(diǎn)美元(B$2)，讓行站住不動(dòng)，下拉的時(shí)候不會(huì)有任何變化，依然是B$2。第二個(gè)B2因?yàn)闆](méi)給美元，圖4-45SUM的用法78

第4章?最受歡迎的函數(shù)與公式下拉就變成B3、B4……B13了。在C3區(qū)域就變成了B$2:B3，也就是對(duì)B2:B3區(qū)域求和。在C13區(qū)域變成了B$2:B13，也就是對(duì)B2:B13區(qū)域進(jìn)行求和。適當(dāng)給點(diǎn)美元，會(huì)起到意想不到的效果。案例2：如圖4-47所示，這是一份人員銷售清單，需要匯總銷售額，如果是你會(huì)怎么匯總呢？網(wǎng)友：銷售額分成三列，每一列就用“,”隔開(kāi)，公式如下：=SUM(F2:F7,H2:H7,J2:J7)盧子：看來(lái)你對(duì)幫助還理解不夠透徹，一起來(lái)看看函數(shù)說(shuō)明。圖4-47人員銷售清單函數(shù)說(shuō)明如果參數(shù)是一個(gè)數(shù)組或引用，則只計(jì)算其中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。如果任意參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，Excel將會(huì)顯示錯(cuò)誤。也就是說(shuō)，如果區(qū)域中有文本，將被忽略，所以只要寫(xiě)一個(gè)區(qū)域就行了。=SUM(E2:J7)網(wǎng)友：這些小細(xì)節(jié)還真沒(méi)注意看，多謝提醒。盧子：前面都是基礎(chǔ)的，再來(lái)看看難度大點(diǎn)的。知識(shí)擴(kuò)展案例3：如圖4-48所示，這是一份沒(méi)經(jīng)過(guò)任何處理的不良明細(xì)，存在錯(cuò)誤值，直接求和出錯(cuò)。這個(gè)有辦法解決嗎？圖4-48含錯(cuò)誤值不良明細(xì)79

Excel效率手冊(cè)——早做完，不加班網(wǎng)友：這回我仔細(xì)研究了幫助，如果任意參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，那么Excel將會(huì)顯示錯(cuò)誤。這種問(wèn)題只有刪除錯(cuò)誤值才可以統(tǒng)計(jì)，不然會(huì)出錯(cuò)。呵呵，這回我沒(méi)說(shuō)錯(cuò)吧，我也挺用功的。盧子：幫助僅供參考，我們還需要學(xué)會(huì)思考問(wèn)題?！岸ㄎ弧卞e(cuò)誤值，然后刪除也是一種辦法。其實(shí)也可以直接求和，在這之前先了解一下IFERROR函數(shù)，如圖4-49所示。圖4-49IFERROR函數(shù)說(shuō)明說(shuō)白了，就是可以將錯(cuò)誤值顯示成你想顯示的任何形式。=IFERROR(錯(cuò)誤值,顯示值)如圖4-50所示，既然這樣，可以先通過(guò)將錯(cuò)誤值顯示成0，然后再匯總。雖然這樣可以匯總，但會(huì)產(chǎn)生一個(gè)輔助列?；氐交A(chǔ)知識(shí)，再看看數(shù)組公式的概念。圖4-50輔助列匯總數(shù)組公式可以執(zhí)行多項(xiàng)計(jì)算并返回一個(gè)或多個(gè)結(jié)果。數(shù)組公式必須按組合鍵Ctrl+Shift+Enter結(jié)束，在輸入數(shù)組公式時(shí)，Excel會(huì)自動(dòng)在大括號(hào){}之間插入該公式。利用數(shù)組可以省略輔助列，直接得到結(jié)果。=SUM(IFERROR(B2:B8,0))先將錯(cuò)誤值全部轉(zhuǎn)換成0，然后再匯總，因?yàn)檗D(zhuǎn)換過(guò)程需要重新運(yùn)算，所以需要按組合鍵Ctrl+Shift+Enter結(jié)束。借助數(shù)組可以省略很多中間步驟，如果你想成為別人眼中的高手，數(shù)組必須熟練掌握。下面再通過(guò)兩個(gè)例子來(lái)鞏固對(duì)數(shù)組公式的理解。80

第4章?最受歡迎的函數(shù)與公式案例4：如圖4-51所示，這是一份每月銷售清單，現(xiàn)在要統(tǒng)計(jì)銷售額大于500的次數(shù)？剛好F2是600，顯示1，下拉試試，F(xiàn)3是120，顯示0。跟我們的預(yù)想一樣。如果是整個(gè)區(qū)域判斷！=IF(F2:F13>500,1,0)在編輯欄按F9鍵將公式抹黑，得到{1;0;1;0;0;0;0;1;0;0;0;0}跟我們?cè)趩卧裣吕降玫降慕Y(jié)果是一樣的，只是顯示在數(shù)組中。這樣只是起到判斷而已，還需要求和。只需要在IF外面再嵌套SUM函數(shù)就行了，因?yàn)橐獔?zhí)行多重計(jì)算，所以是數(shù)組公式。圖4-51每月銷售清單先來(lái)了解一下IF函數(shù)的用法，如圖4-52所示。=SUM(IF(F2:F13>500,1,0))網(wǎng)友：貌似看懂了點(diǎn)，對(duì)了這個(gè)F9鍵是干嘛用？盧子：F9鍵，人稱“獨(dú)孤九劍”，看過(guò)《笑傲江湖》的人應(yīng)該知道令狐沖的獨(dú)孤九劍很厲害。既然F9鍵有這個(gè)雅稱，一定有過(guò)人之處。F9鍵是解讀公式的利器，公式如果太長(zhǎng)了看不懂，將看不懂的地方抹黑就知道運(yùn)算結(jié)果了?？赐旰笤侔唇M合鍵Ctrl+Z返回，否則公式就變了。步步高點(diǎn)讀機(jī)有一句廣告詞：哪里不會(huì)點(diǎn)(抹)哪里，soeasy！媽媽再也不用擔(dān)心我的學(xué)習(xí)了。用在這里再適合不過(guò)，如公式：圖4-52IF函數(shù)幫助通俗點(diǎn)就是：=IF(條件,滿足條件的返回值,不滿足條件的返回值)=SUM(SMALL(IF(B$1:B$10=5,ROW($1:$10)),ROW(1:2))*{-1;1})-1可以通過(guò)IF來(lái)判斷銷售額是否滿足大于500，讓大于500的顯示1，小于或等于500的顯示0。可以在單元格中輸入公式，看看是否跟我們想的一樣。這個(gè)ROW($1:$10)看不懂，你就抹黑它再按F9鍵。=SUM(SMALL(IF(B$1:B$10=5,{1;2;3;4;5;6;7;8;9;10}),ROW(1:2))*{-1;1})-1=IF(F2>500,1,0)原來(lái)相當(dāng)于1~10，記得按組合鍵Ctrl+Z返81

Excel效率手冊(cè)——早做完，不加班回哦，Excel是允許你后悔的。網(wǎng)友：原來(lái)是協(xié)助解讀公式的一個(gè)工具。盧子：這個(gè)很好用，我經(jīng)常用。還有一個(gè)叫公式求值的功能，效果跟這個(gè)差不多，有空你也可以了解一下。不過(guò)公式求值讓人覺(jué)得自己就是一個(gè)機(jī)械操作工，而F9鍵讓人覺(jué)得自己是一個(gè)劍客，凡事隨心所欲。再舉一個(gè)例子來(lái)說(shuō)明數(shù)組的用法案例5：還是以每月銷售清單那個(gè)附件來(lái)說(shuō)明，求銷售額大于500的人的總銷售額。有了上面的基礎(chǔ)，再來(lái)了解這個(gè)就很容易了。=SUM(IF(F2:F13>500,F2:F13,0))用IF進(jìn)行判斷，讓大于500的顯示原來(lái)的銷售額，其他顯示0，進(jìn)行求和剛好得到銷售額大于500的人的總銷售額。網(wǎng)友：這回懂了，謝謝盧子。盧子：今天這個(gè)SUM函數(shù)就先告一段落，自己有空再去熟練一下，欲知SUM更多用法，且聽(tīng)下回分解。網(wǎng)友：謝謝，期待下回更精彩的講解！這邊剛結(jié)束，就收到簡(jiǎn)單、Simple的私聊消息。簡(jiǎn)單：辛苦了。盧子：累死人，沒(méi)想到一講就是兩個(gè)小時(shí)，比1000米賽跑還累。簡(jiǎn)單：明天把這些整理一下，分享到群里。盧子：好的。Simple：講得不錯(cuò)，挺有邏輯性的。盧子：已盡力了，但愿這次能收到好的評(píng)價(jià)?；仡^你看看大家的評(píng)價(jià)怎樣？Simple：明后天應(yīng)該就能知道大家的想法，到時(shí)跟你匯報(bào)。盧子：那我先睡了，腦力活原來(lái)比體力活更累。Simple：那早點(diǎn)休息吧?！诙焱砩?，收到Simple的私聊信息。Simple：大家評(píng)價(jià)蠻高的，都在打聽(tīng)什么時(shí)候再組織講座，到時(shí)得提前通知。群里不斷有陌生人加入，說(shuō)要聽(tīng)課。看你太辛苦，昨晚的講座內(nèi)容已經(jīng)幫你整理好了。盧子：謝了，回頭我直接把你整理的分享出來(lái)就行。這回的辛苦總算沒(méi)白費(fèi)，我回頭再準(zhǔn)備下，爭(zhēng)取這兩天舉行第二回講座。82

第4章?最受歡迎的函數(shù)與公式經(jīng)過(guò)兩天的準(zhǔn)備，盧子把SUM函數(shù)的其他資料整理好了。這回只是對(duì)上回知識(shí)的補(bǔ)充，并不會(huì)涉及太多的知識(shí)點(diǎn)。盧子：今晚繼續(xù)學(xué)習(xí)SUM函數(shù)，通過(guò)三個(gè)小例子來(lái)對(duì)上一回的知識(shí)進(jìn)行補(bǔ)充說(shuō)明，大約1小時(shí)就可以講完了，呵呵。晚的正題，別見(jiàn)怪，剛才只是有感而發(fā)而已。剛才提到了替換這個(gè)詞，函數(shù)中也有屬于自己的替換函數(shù)，SUBSTITUTE函數(shù)，用法如圖4-54所示。網(wǎng)友：老師辛苦了，沒(méi)想到小小SUM函數(shù)居然這么神奇，這回要用功學(xué)習(xí)才是。盧子：很好，那一起開(kāi)始學(xué)習(xí)吧。案例1：如圖4-53所示，這是一份含有單位的人員銷售清單，直接求和得不出正確答案，怎么才能讓含單位的銷售額可以求和呢？圖4-54SUBSTITUTE函數(shù)幫助SUBSTITUTE的第四參數(shù)為可選，那就先別管它，其他參數(shù)可以理解為：=SUBSTITUTE(文本,需要替換的舊字符,替換成新的字符)單元格中的“元”是多余的，需要替換成空，空可以用＂＂表示，替換成空后直接求和，可以嗎？圖4-53含單位的人員銷售清單幫助提到，SUM函數(shù)會(huì)自動(dòng)忽略文本，600元就是文本，不屬于數(shù)字。最簡(jiǎn)單的做法就是將“元”替換成空，然后自定義單元格格式G/通用格式“元”。很多人就是搞不明白一格一屬性的道理，才會(huì)造成匯總數(shù)據(jù)困難重重。正確的做法應(yīng)該將“元”寫(xiě)在字段名那里變成銷售額(元)，這樣別人一看便知。廢話了這么多，還沒(méi)扯到今=SUM(SUBSTITUTE(B2:B7,＂元＂,＂＂))網(wǎng)友：這個(gè)是數(shù)組公式，用法也跟前面說(shuō)得差不多，應(yīng)該可以匯總。盧子：SUBSTITUTE函數(shù)屬于文本函數(shù)，所以替換得到的數(shù)字也屬于文本，在這里叫作文本數(shù)字。數(shù)字有兩種類型，一種是文本數(shù)字，一種是真正的數(shù)字，即數(shù)值。數(shù)值可83

Excel效率手冊(cè)——早做完，不加班以求和，而文本不能求和。如賬簿上的數(shù)綜上，最終的數(shù)組公式為：字跟墻上的數(shù)字是不同的，前者我們可以用這些數(shù)字進(jìn)行各種分析，后者只能當(dāng)欣賞用。那有什么辦法還原數(shù)字的本質(zhì)呢？=SUM(--SUBSTITUTE(B2:B7,＂元＂,＂＂))網(wǎng)友：沒(méi)想到數(shù)字還有這些學(xué)問(wèn)，長(zhǎng)見(jiàn)識(shí)了。盧子：再來(lái)看另一種不規(guī)范輸入的案例。利用VALUE函數(shù)可以將文本型轉(zhuǎn)換成數(shù)值型。案例2：如圖4-55所示，這是一份含附加分的成績(jī)明細(xì)表，分?jǐn)?shù)分為基本分（左），附加分（右），怎么匯總分?jǐn)?shù)呢？=VALUE(＂600＂)但一般情況下不會(huì)這么做，而是通過(guò)運(yùn)算轉(zhuǎn)換。一起來(lái)了解“減負(fù)”運(yùn)算在函數(shù)或公式中，運(yùn)算過(guò)程會(huì)自動(dòng)把文本轉(zhuǎn)換為數(shù)值(一個(gè)隱含過(guò)程)，再與數(shù)值進(jìn)行運(yùn)算，負(fù)值運(yùn)算(-)也是一種運(yùn)算，能把文本轉(zhuǎn)換成數(shù)值。圖4-55含附加分成績(jī)明細(xì)仔細(xì)觀察發(fā)現(xiàn)：-＂600＂=-600有附加分的中間都隔著空格，如圖4-56所示。這跟分?jǐn)?shù)寫(xiě)法的前半部分一樣，只是少了斜杠(/)+分母。既然這樣，我們就可以構(gòu)造后半部分。分?jǐn)?shù)&“/1”，E2就得到“905/1”，通過(guò)&函數(shù)得到的是文本數(shù)值，前面加“－－”讓他變成數(shù)值，E3本身就是數(shù)字，不必轉(zhuǎn)換。還記得負(fù)負(fù)得正吧？例如：-(-＂600＂)=-(-600)=600可簡(jiǎn)寫(xiě)為：--＂600＂=600--可以把文本轉(zhuǎn)換為數(shù)值，但它不是標(biāo)準(zhǔn)的轉(zhuǎn)換方式，而是借用負(fù)運(yùn)算的隱含功能。其實(shí)負(fù)負(fù)運(yùn)算稱為減負(fù)運(yùn)算更好，即減去數(shù)字的負(fù)擔(dān)，還原數(shù)字的本質(zhì)。=SUM(--SUBSTITUTE(B2:B7,＂元＂,＂＂))，將這一部分用F9鍵抹黑，得到：=SUM({600;120;1000;210;129;123})這樣就能夠求和了。圖4-56分?jǐn)?shù)的組成部分84

第4章?最受歡迎的函數(shù)與公式通過(guò)上面的分析，問(wèn)題已經(jīng)解決一大半了，只需判斷分?jǐn)?shù)是不是數(shù)值，是的話就顯示本身的值，不是就用--(分?jǐn)?shù)&“/1”)。怎么判斷單元格的值是不是數(shù)值呢？先來(lái)了解一下ISNUMBER函數(shù)，它只有一個(gè)參數(shù)。案例3：如圖4-57所示，這是一份人員銷售清單，經(jīng)常要在總銷售額處插入新的人員。用SUM直接統(tǒng)計(jì)不會(huì)對(duì)新增加人員的銷售額進(jìn)行匯總，該怎么處理呢？=ISNUMBER(值)如果是數(shù)字就顯示TRUE，否則顯示FALSE。到這里思路都出來(lái)了，只需輸入數(shù)組公式到單元格里來(lái)驗(yàn)證我們的想法是否正確就可以了。圖4-57人員銷售清單按組合鍵Ctrl+F3定義一個(gè)叫“上一行”的名稱，引用位置為B20。=SUM(IF(ISNUMBER(E2:E7),E2:E7,--(E2:E7&＂/1＂)))這個(gè)引用為相對(duì)引用，每插入一行引用位置就會(huì)動(dòng)態(tài)變化，所以用下面的公式就可以搞定，以后插入行也會(huì)自動(dòng)匯總進(jìn)去，如圖4-58所示。網(wǎng)友：測(cè)試通過(guò)，原來(lái)寫(xiě)公式跟斷案一樣，需要嚴(yán)謹(jǐn)?shù)姆治?，才能不判錯(cuò)案子。盧子：公式主要是靠邏輯，大腦要經(jīng)常動(dòng)，這樣才不會(huì)生銹。=SUM(B15:上一行)溫馨提示分子不能超過(guò)5位數(shù)，否則出錯(cuò)。如：1100000/1用=ISNUMBER(＂1100000/1＂)測(cè)試顯示為FALSE，證明這個(gè)是文本。圖4-58定義名稱正常的話，不會(huì)出現(xiàn)這樣的分?jǐn)?shù)，稍微了解一下就行。一個(gè)過(guò)來(lái)人的忠告有人說(shuō)直接用SUM(區(qū)域)就行，插入行后區(qū)域會(huì)自動(dòng)擴(kuò)展。但我說(shuō)這是Excel的BUG85

Excel效率手冊(cè)——早做完，不加班你信嗎？曾經(jīng)我就因?yàn)橹苯佑肧UM(區(qū)域)導(dǎo)致開(kāi)錯(cuò)兩張單，最后核對(duì)金額的時(shí)候才發(fā)現(xiàn)異常，還好金額不大，如果是大金額，這將給公司帶來(lái)多大的損失呀。小心駛得萬(wàn)年船，如果你堅(jiān)持你的想法，用SUM(區(qū)域)插入行后請(qǐng)選擇區(qū)域，查看狀態(tài)欄的總計(jì)跟公式匯總是否一致。圖4-59為直接用SUM(區(qū)域)插入行后的結(jié)果，數(shù)量添加的行會(huì)自動(dòng)匯總進(jìn)去，金額卻沒(méi)有自動(dòng)增加。為了保險(xiǎn)起見(jiàn)還是定義名稱，當(dāng)然還有其他方法，例如：=SUM(B15:INDEX(B:B,ROW()-1))圖4-59在“合計(jì)”上面插入行后的結(jié)果關(guān)于SUM的用法到這里已經(jīng)結(jié)束了，如果還有什么疑問(wèn)可以向Simple提出來(lái)，到時(shí)我再統(tǒng)一解答。網(wǎng)友：又學(xué)到了幾招，謝謝老師。盧子：對(duì)了，以后叫我盧子就行，這樣我還習(xí)慣點(diǎn)，下節(jié)課根據(jù)大家的反饋再?zèng)Q定講什么函數(shù)。4.2.2求和之王SUMPRODUCT盧子：經(jīng)過(guò)這幾天的反饋，有兩個(gè)問(wèn)題提的人比較多。其1：實(shí)例是用公式=SUM(IF(F2:F13>500,F2:F13,0))，但現(xiàn)實(shí)有很多公式是這種形式=SUM((F2:F13>500)*F2:F13)，后者是怎么得出來(lái)的？其2：SUM的數(shù)組公式要按組合鍵Ctrl+Shift+Enter結(jié)束才能得到正確答案，很麻煩，經(jīng)常會(huì)忘記按組合鍵Ctrl+Shift+Enter，有沒(méi)有其他函數(shù)可以取代SUM的數(shù)組形式呢？一起來(lái)看看問(wèn)題1，如圖4-60所示，以銷售額大于500的人的總銷售額為例進(jìn)行說(shuō)明。圖4-60銷售額大于500的人的總銷售額86

第4章?最受歡迎的函數(shù)與公式=SUM((F2:F13>500)*F2:F13)可能很多人想知道抹黑地方的意思，有句話叫心急吃不了熱豆腐，凡事得一步步慢慢來(lái)，急不得。開(kāi)始先不要直接用整個(gè)區(qū)域解讀，先分成一個(gè)單元格的判斷解讀，單元格理解后再轉(zhuǎn)換成區(qū)域理解。這樣更有助于理解，適當(dāng)?shù)臅r(shí)候配合F9鍵，效果會(huì)更好。網(wǎng)友：原來(lái)公式是這么解讀的，老想一步就到位，反而理解不好。先拆開(kāi)，再合并，先記住這個(gè)方法。=B2>500如果成立就顯示TRUE，否則顯示FALSE。在這里B2>500成立，顯示TRUE。=(B2>500)*B2等同于=TRUE*B2，得到600，也就是說(shuō)在這里TRUE相當(dāng)于1。盧子：?jiǎn)栴}2用SUMPRODUCT可以取代SUM的數(shù)組公式。還是老方法，先看SUMPRODUCT函數(shù)的幫助，如圖4-61所示，對(duì)多個(gè)區(qū)域先相乘，后匯總。=(B3>500)*B3等同于=FALSE*B3，得到0，也就是說(shuō)在這里FALSE相當(dāng)于0。*可以讓符合條件的值顯示其本身，不符合條件的值顯示0，但它不是標(biāo)準(zhǔn)的轉(zhuǎn)換方式，而是借用乘法運(yùn)算的隱含功能。再回到公式上=SUM((F2:F13>500)*F2:F13)按F9鍵抹黑得到：=SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})*B2:B13)圖4-61SUMPRODUCT幫助得到一組由TRUE跟FALSE組成的數(shù)組：基礎(chǔ)用法=SUM(({TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE})*B2:B13)畢竟是出自一家人，幫助都差不多，非常好理解，如圖4-62所示，統(tǒng)計(jì)總金額。數(shù)組*B2:B13，讓符合條件的都顯示其本身，其他顯示0。=SUM({600;0;1000;0;0;0;0;1000;0;0;0;0})到這步應(yīng)該可以理解了吧。解讀公式一圖4-62統(tǒng)計(jì)總金額87

Excel效率手冊(cè)——早做完，不加班=SUMPRODUCT(B2:B4,C2:C4)只強(qiáng)調(diào)一句，SUMPRODUCT將非數(shù)值型的數(shù)組元素作為0處理，如B4現(xiàn)在的值是文本=SUMPRODUCT({2;5;＂無(wú)＂},C2:C4)，“無(wú)”在這里等同于0。在此不再對(duì)這個(gè)函數(shù)的基礎(chǔ)用法進(jìn)行說(shuō)明，否則會(huì)讓大家誤認(rèn)為侮辱了你們的智商。知識(shí)擴(kuò)展通過(guò)對(duì)SUM的學(xué)習(xí)知道了它可以求和、計(jì)數(shù)，SUM能做到的SUMPRODUCT都能做到，而且做得更好。SUMPRODUCT函數(shù)本身就支持?jǐn)?shù)組，所以條件計(jì)數(shù)、求和的時(shí)候不需要按組合鍵Ctrl+Shift+Enter，正因?yàn)檫@樣它才受到大多數(shù)人的喜歡。有人把它比喻成璀璨的明珠，光芒四射，魅力無(wú)窮，稱為求和之王也不為過(guò)。通用公式計(jì)數(shù)：=SUMPRODUCT((條件1)*(條件2)*(條件3)*…*(條件N))求和：=SUMPRODUCT((條件1)*(條件2)*(條件3)*…*求和區(qū)域)如圖4-63所示，這是IT部落窩隨機(jī)抽查的人員資料表，下面通過(guò)10個(gè)小例子來(lái)說(shuō)明條件計(jì)數(shù)、求和的用法。圖4-63IT部落窩隨機(jī)抽查的人員資料表計(jì)數(shù)例子1：女性有幾個(gè)人？=SUMPRODUCT(--(C4:C22=＂女＂))例子2：潛水時(shí)間大于15天的男人有多少？=SUMPRODUCT((E4:E22>15)*(C4:C22=＂男＂))88

第4章?最受歡迎的函數(shù)與公式例子3：2月份發(fā)言的男人有多少？=SUMPRODUCT((MONTH(D4:D22)=2)*(C4:C22=＂男＂))這里涉及一個(gè)新函數(shù)MONTH，其作用就是將日期轉(zhuǎn)換成月份。相關(guān)聯(lián)的函數(shù)還有YEAR，其作用是將日期轉(zhuǎn)換成年，DAY函數(shù)可將日期轉(zhuǎn)換成日。例子4：不包括“笑看今朝”的男人有幾個(gè)？=SUMPRODUCT((A4:A22<>＂笑看今朝＂)*(C4:C22=＂男＂))<>（不等于）屬于比較運(yùn)算符，還有=（等于），>（大于），<（小于），>=（大于等于）和<=（小于等于），跟數(shù)學(xué)的表示方法略有差別，但作用一樣。求和例子5：女性潛水總天數(shù)。=SUMPRODUCT((C4:C22=＂女＂)*E4:E22)例子6：潛水時(shí)間大于15天的男性的潛水天數(shù)。=SUMPRODUCT((E4:E22>15)*(C4:C22=＂男＂)*E4:E22)例子7：2月份發(fā)言的男性的潛水天數(shù)。=SUMPRODUCT((MONTH(D4:D22)=2)*(C4:C22=＂男＂)*E4:E22)例子8：QQ號(hào)首位是8的人的潛水天數(shù)。=SUMPRODUCT((LEFT(B4:B22)=＂8＂)*E4:E22)LEFT的語(yǔ)法：LEFT(文本,N)，提取左邊的N位文本，省略第二參數(shù)，就是提取1位。例子9：姓名字符數(shù)為2，不包括“月亮”的人的潛水天數(shù)。=SUMPRODUCT((LEN(A4:A22)=2)*(A4:A22<>＂月亮＂)*E4:E22)LEN的語(yǔ)法：LEN(字符)，統(tǒng)計(jì)字符個(gè)數(shù)，漢字、字母、數(shù)字都是一個(gè)字符；LENB(字符)，統(tǒng)計(jì)字節(jié)個(gè)數(shù)，漢字兩個(gè)字節(jié)，字母、數(shù)字為一個(gè)字節(jié)。例子10：“笑看今朝”和“冷逸”的潛水天數(shù)。=SUMPRODUCT(((A4:A22=＂笑看今朝＂)+(A4:A22=＂冷逸＂))*E4:E22)+在這里是“或”的意思，只要滿足其中一個(gè)條件就行，它有時(shí)可以替代OR的功能，如=IF(OR(A4=＂笑看今朝＂,A4=＂冷逸＂),1,0)等同于=IF((A4=＂笑看今朝＂)+(A4=＂冷逸＂),1,0)，但OR不能替代+在數(shù)組中的用法，切記！89

Excel效率手冊(cè)——早做完，不加班簡(jiǎn)化=SUMPRODUCT((A4:A22={＂笑看今朝＂,＂冷逸＂})*E4:E22)公式剖析，老辦法，先轉(zhuǎn)換成單元格比較。A5={＂笑看今朝＂,＂冷逸＂}，一個(gè)單元格跟兩個(gè)值同時(shí)比較，滿足就顯示TRUE，否則顯示FALSE。A5={＂笑看今朝＂,＂冷逸＂}，按F9鍵得到{TRUE,FALSE}。({TRUE,FALSE})*E5，按F9鍵得到{6,0}。也就是說(shuō)，只要單元格滿足其中一個(gè)值，就一定會(huì)得到由0跟單元格本身組成的常量數(shù)組，完全不滿足就顯示{0,0}。因?yàn)閱卧癫豢赡芡瑫r(shí)滿足兩個(gè)條件，所以不會(huì)出現(xiàn){6,6}這種情況。=SUMPRODUCT((A5={＂笑看今朝＂,＂冷逸＂})*E5)抹黑得到：=SUMPRODUCT({6,0})同理：=SUMPRODUCT((A4:A22={＂笑看今朝＂,＂冷逸＂})*E4:E22)抹黑得到：=SUMPRODUCT({0,0;6,0;0,5;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0;0,0})這里就不再進(jìn)行解釋了，留點(diǎn)空間給大家思考。有SUM作為鋪墊，理解SUMPRODUCT會(huì)異常簡(jiǎn)單。今天就到此結(jié)束，有疑問(wèn)可以反饋出來(lái)。網(wǎng)友：謝謝，回去好好消化一下。4.2.3既生SUMIF（COUNTIF），何生SUMPRODUCT網(wǎng)友：SUMPRODUCT函數(shù)太好用了，導(dǎo)致現(xiàn)在Excel專門(mén)的條件求和、計(jì)數(shù)函數(shù)SUMIF(COUNTIF)都不去使用了。盧子：一部《三國(guó)演義》讓大家把諸葛亮當(dāng)神看待——神機(jī)妙算，無(wú)所不能。以至于周瑜感嘆“既生瑜，何生亮”。其實(shí)周瑜也是一個(gè)很有才華的人，只是被掩蓋了。扯遠(yuǎn)了，回到正題。SUMIF(COUNTIF)其實(shí)也很好用，有好事者測(cè)試了SUMIFS跟SUMPRODUCT多條件求和統(tǒng)計(jì)速度，前者是后者的三倍。那SUMIF單條件統(tǒng)計(jì)速度比SUMPRODUCT快一點(diǎn)還是可以肯定的。不過(guò)對(duì)你我來(lái)說(shuō)，可以忽略這個(gè)速度的問(wèn)題。90

第4章?最受歡迎的函數(shù)與公式先來(lái)了解一下COUNTIF函數(shù)。怎么學(xué)函數(shù)，還是老話按F1鍵調(diào)出幫助。不要對(duì)每次的重復(fù)操作厭倦，幫助可以給我們提供很多有用的信息。如圖4-64所示，這是COUNTIF函數(shù)的幫助。通俗點(diǎn)的語(yǔ)法：=COUNTIF(條件區(qū)域,條件)統(tǒng)計(jì)區(qū)域滿足條件的個(gè)數(shù)。圖4-64COUNTIF幫助下面通過(guò)幾個(gè)小例子來(lái)說(shuō)明COUNTIF的用法。如圖4-65所示，這是2006年電腦配件銷售一覽表。例子1：數(shù)量大于30的有幾個(gè)？=COUNTIF(D4:D22,＂>30＂)例子2：營(yíng)業(yè)部中含“河”字的有幾個(gè)？=COUNTIF(A4:A22,＂*河*＂)通配符的說(shuō)明：*代表所有字符，？代表一個(gè)字符。如果需要統(tǒng)計(jì)營(yíng)業(yè)部中的兩個(gè)字符，且“河”字在最后面，可以這么寫(xiě)公式：=COUNTIF(A4:A22,＂?河＂)圖4-652006年電腦配件銷售一覽表91

Excel效率手冊(cè)——早做完，不加班例子3：在商品列中是否有鍵盤(pán)？=IF(COUNTIF(B4:B22,＂鍵盤(pán)＂)>0,＂存在＂,＂不存在＂)如果存在鍵盤(pán)COUNTIF統(tǒng)計(jì)出來(lái)的次數(shù)大于0；否則等于0；公式可以稍做簡(jiǎn)化。=IF(COUNTIF(B4:B22,＂鍵盤(pán)＂),＂存在＂,＂不存在＂)網(wǎng)友：>0這部分怎么可以省略？這是什么原理？盧子：一起來(lái)看看下面幾個(gè)判斷。=IF(3,＂存在＂,＂不存在＂)，返回存在；=IF(-3,＂存在＂,＂不存在＂)，返回存在；=IF(0,＂存在＂,＂不存在＂)，返回不存在。也就是說(shuō)任何不等于0的數(shù)字在這里都等同于TRUE，0等同于FALSE。如果不相信，可以自己多試幾個(gè)看看。不過(guò)建議初學(xué)者不要用簡(jiǎn)寫(xiě)，用標(biāo)準(zhǔn)寫(xiě)法更有助于理解。前面幾個(gè)例子的條件都是手寫(xiě)的，其實(shí)條件可以直接引用單元格。圖4-66引用單元格例子4：如圖4-66所示，統(tǒng)計(jì)每個(gè)營(yíng)業(yè)部出現(xiàn)的次數(shù)。=COUNTIF($A$4:$A$22,L4)因?yàn)楣揭吕?，為防止區(qū)域改變，所以加絕對(duì)引用。絕對(duì)引用、相對(duì)引用和混合引用，可以通過(guò)用F4鍵切換得到。例子5：統(tǒng)計(jì)共有幾個(gè)不重復(fù)營(yíng)業(yè)部？=SUMPRODUCT(1/COUNTIF(A4:A22,A4:A22))=SUMP