為什么你做了一天的表格，老板看一眼就不想看了？
處理簡(jiǎn)單的數(shù)據(jù)沒(méi)問(wèn)題，但是一有較為復(fù)雜的數(shù)據(jù)就懵了。一行一行地去手動(dòng)處理數(shù)據(jù)，交給老板后卻發(fā)現(xiàn)錯(cuò)誤百出。
Excel作為職場(chǎng)人必備的底層能力，幾乎每個(gè)人的簡(jiǎn)歷上都寫(xiě)著“熟練掌握”，但是真正懂門道的可能沒(méi)幾個(gè)。
其實(shí)Excel比我們想象的要智能很多，學(xué)會(huì)數(shù)據(jù)驗(yàn)證、快速填充等功能，我們的效率不僅能提高，準(zhǔn)確率也能大幅提升。
不要讓你的勞力去彌補(bǔ)技能的不足。如果你還不會(huì)快速處理較為復(fù)雜的數(shù)據(jù)，就讓我們一起進(jìn)入這期的Excel魔法學(xué)院，學(xué)學(xué)吧！
數(shù)據(jù)驗(yàn)證
數(shù)據(jù)驗(yàn)證，也就是數(shù)據(jù)有效性，是指對(duì)單元格設(shè)置一定的規(guī)則，符合規(guī)則的允許錄入，而不符合規(guī)則的則禁止錄入。
數(shù)據(jù)驗(yàn)證是一種效率極高的數(shù)據(jù)輸入方式，可以對(duì)數(shù)據(jù)的類型、范圍、輸入條件等進(jìn)行限定，是Excel中非常有價(jià)值的工具。
技巧1：限制數(shù)據(jù)輸入長(zhǎng)度
很多信息都有固定的長(zhǎng)度，比如手機(jī)號(hào)是11位，身份證號(hào)一般是18位，如何保證數(shù)據(jù)的輸入長(zhǎng)度不多不少呢？
操作步驟：選中數(shù)據(jù)區(qū)域，調(diào)出【數(shù)據(jù)驗(yàn)證】對(duì)話框，【允許】下拉列表中選擇【文本長(zhǎng)度】，【數(shù)據(jù)】為【等于】，長(zhǎng)度為【11】，點(diǎn)擊【確定】。手機(jī)號(hào)輸入必須是11位就設(shè)置完成啦！
技巧2：限制重復(fù)輸入
如何避免信息重復(fù)錄入？
操作步驟：選中數(shù)據(jù)區(qū)域，調(diào)出【數(shù)據(jù)驗(yàn)證】對(duì)話框，【允許】下拉列表中選擇【自定義】，【公式】中輸入=COUNTIF($A$4:$A$7,$A4)=1，點(diǎn)擊【確定】。
在這里我們通過(guò)COUNTIF函數(shù)來(lái)協(xié)助，只要數(shù)據(jù)個(gè)數(shù)是1，就代表不重復(fù)，超過(guò)1，就代表重復(fù)了，不能輸入。
技巧3：設(shè)置下拉菜單，變輸入為選擇
對(duì)于表格中需要輸入的固定信息，比如部門名稱、性別、城市等，我們可以通過(guò)設(shè)置下拉菜單的方式變輸入為選擇。
操作步驟：選中數(shù)據(jù)區(qū)域，調(diào)出【數(shù)據(jù)驗(yàn)證】對(duì)話框，【允許】下拉列表中選擇【序列】，【來(lái)源】處單擊右側(cè)折疊按鈕，選中菜單項(xiàng)所在區(qū)域，點(diǎn)擊【確定】。
技巧4：設(shè)置二級(jí)聯(lián)動(dòng)下拉菜單
數(shù)據(jù)間經(jīng)常存在著前后關(guān)聯(lián)關(guān)系，比如某個(gè)部門下對(duì)應(yīng)的員工列表，某個(gè)地市所轄的區(qū)縣等。那么，設(shè)置完一級(jí)下拉菜單后，對(duì)應(yīng)的二級(jí)下拉菜單能否隨著一級(jí)下拉菜單的不同而出現(xiàn)的選項(xiàng)也不同呢。
答案當(dāng)然是可以的。下面我們以關(guān)聯(lián)地市-區(qū)縣的二級(jí)下拉菜單為例進(jìn)行設(shè)置。
步驟一：批量創(chuàng)建名稱
在Excel中，名稱可以用來(lái)代替單元格區(qū)域的引用。通過(guò)定義名稱，可以搭建起一級(jí)、二級(jí)下拉菜單的橋梁。
操作步驟：同時(shí)按下【CTRL】+【G】，在【定位條件】對(duì)話框中，選擇【常量】，點(diǎn)擊【確定】。我們這樣就選中了包含信息的所有單元格。
之后在【公式】選項(xiàng)卡下，選擇【根據(jù)所選內(nèi)容進(jìn)行創(chuàng)建】命令，默認(rèn)勾選【首選】，批量創(chuàng)建名稱完成。
調(diào)出名稱管理器，我們可以看到已經(jīng)定義的名稱及其包含的數(shù)據(jù)區(qū)域。
步驟二：設(shè)置二級(jí)菜單
在城市信息列中，我們已經(jīng)設(shè)置了一級(jí)菜單項(xiàng)。接下來(lái)在區(qū)/縣信息列中設(shè)置二級(jí)下拉菜單。
操作步驟：選中數(shù)據(jù)區(qū)域，調(diào)出【數(shù)據(jù)驗(yàn)證】對(duì)話框，【允許】下拉列表中選擇【序列】，【來(lái)源】中輸入“=INDIRECT($M2)”，點(diǎn)擊【確定】。
技巧5：設(shè)置事先提醒
數(shù)據(jù)驗(yàn)證，不僅能夠制定數(shù)據(jù)輸入規(guī)則，還能提醒你該如何填寫(xiě)，是不是非常貼心！
操作步驟：選中數(shù)據(jù)區(qū)域，調(diào)出【數(shù)據(jù)驗(yàn)證】對(duì)話框，點(diǎn)擊上方【輸入信息】項(xiàng)，可以設(shè)置標(biāo)題和信息。比如我們輸入信息“請(qǐng)輸入信件可送達(dá)的地址”，填寫(xiě)時(shí)，就會(huì)出現(xiàn)這句提醒了。
技巧6：數(shù)據(jù)無(wú)效，那就圈出來(lái)
數(shù)據(jù)驗(yàn)證還能對(duì)已經(jīng)存在的數(shù)據(jù)進(jìn)行檢查。檢查無(wú)效，那就圈出來(lái)。
操作步驟：先設(shè)置數(shù)據(jù)驗(yàn)證，選中數(shù)據(jù)區(qū)域，調(diào)出【數(shù)據(jù)驗(yàn)證】對(duì)話框，【允許】下拉列表中選擇【整數(shù)】，【數(shù)據(jù)】選擇【介于】，【最小值】為60，【最大值】為100，點(diǎn)擊【確定】。
然后在【數(shù)據(jù)驗(yàn)證】下點(diǎn)擊【圈釋無(wú)效數(shù)據(jù)】，不在60-100之間的而成績(jī)就被“朱批”出來(lái)了。
選擇性粘貼
復(fù)制粘貼似乎人人都會(huì)，不就是CTRL+C/CTRL+V。但其實(shí)很多人都不知道，在粘貼下方還有一個(gè)被大多數(shù)人忽略的選擇性粘貼，其實(shí)非常實(shí)用，不僅能進(jìn)行數(shù)據(jù)轉(zhuǎn)換，還能進(jìn)行各種計(jì)算。
技巧7：將公式粘貼為數(shù)值
有時(shí)候我們希望直接呈現(xiàn)結(jié)果而不保留計(jì)算過(guò)程，就需要將帶有公式的數(shù)據(jù)區(qū)域轉(zhuǎn)換為數(shù)值。如何轉(zhuǎn)換呢？
操作步驟：復(fù)制已填充公式的數(shù)據(jù)區(qū)域，在【開(kāi)始】選項(xiàng)卡的【粘貼】下，選擇【粘貼數(shù)值】，公式即可轉(zhuǎn)為數(shù)值。
技巧8：將文本轉(zhuǎn)換為數(shù)值
從數(shù)據(jù)系統(tǒng)里導(dǎo)出的表格經(jīng)常無(wú)法求和，SUM一下就會(huì)顯示結(jié)果為0。這都是數(shù)字格式惹的禍，看起來(lái)像數(shù)值，實(shí)則為文本格式。如何將這種文本轉(zhuǎn)換為數(shù)值？
操作步驟：先在某個(gè)空白單元格輸入1，復(fù)制這個(gè)數(shù)字1。
選中要將文本轉(zhuǎn)換為數(shù)值的單元格區(qū)域，調(diào)出【選擇性粘貼】對(duì)話框，點(diǎn)選【數(shù)值】按鈕，運(yùn)算選擇【乘】，點(diǎn)擊【確定】。
技巧9：金額顯示單位為萬(wàn)元
如何將數(shù)字單位顯示變成以萬(wàn)為單位？只要我們將數(shù)值批量縮小10000倍，顯示的數(shù)值就是以萬(wàn)為單位。如何快速實(shí)現(xiàn)呢？
操作步驟：先在某個(gè)空白單元格輸入10000，復(fù)制。選中現(xiàn)將金額顯示以萬(wàn)為單位的區(qū)域，調(diào)出【選擇性粘貼】對(duì)話框，點(diǎn)選【數(shù)值】按鈕，運(yùn)算選擇【除】，點(diǎn)擊【確定】。
技巧10：快速兩表比對(duì)
如何快速核對(duì)兩個(gè)數(shù)據(jù)表的值是否一致？選擇性粘貼給你解決這個(gè)問(wèn)題的新方法。
操作步驟：先復(fù)制其中一個(gè)表格，然后選中另一個(gè)表格，調(diào)出【選擇性粘貼】對(duì)話框，運(yùn)算選擇【減】，點(diǎn)擊【確定】。
兩個(gè)表格相減為0，則數(shù)據(jù)一致；不為0，則數(shù)據(jù)不一致。
技巧11：粘貼為圖片/帶鏈接的圖片
有時(shí)為了排版簡(jiǎn)潔或閱讀方便，我們需要將數(shù)據(jù)表轉(zhuǎn)換為圖片格式。可如果當(dāng)圖中數(shù)據(jù)會(huì)反復(fù)發(fā)生變化時(shí)，就會(huì)很麻煩。
不用煩惱，我們可以轉(zhuǎn)換為帶鏈接的圖片，原數(shù)據(jù)表變化，圖片上的數(shù)據(jù)也會(huì)直接變化。
操作步驟：復(fù)制數(shù)據(jù)區(qū)域，選中任意一個(gè)空白單元格，在【開(kāi)始】選項(xiàng)卡的【粘貼】下，點(diǎn)擊【其他粘貼選項(xiàng)】中的“粘貼為帶鏈接的圖片”標(biāo)識(shí)。
分列功能
Excel中的分列功能，相信大家都有所耳聞，平常也可能經(jīng)常會(huì)用到。分列功能非常強(qiáng)大，不僅可以拆分、提取，還能規(guī)范數(shù)據(jù)。
技巧12：根據(jù)固定符號(hào)拆分
當(dāng)我們要對(duì)打卡信息進(jìn)行拆分，將日期和時(shí)間放在兩列，但日期和時(shí)間中間都存在著空格符號(hào)。我們就可以以空格這個(gè)固定符號(hào)來(lái)進(jìn)行信息拆分。
操作步驟：選中數(shù)據(jù)列，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡下的【分列】，在【文本】分列向?qū)е羞x擇【分隔符號(hào)】，下一步中勾選【空格】，在下一步中選擇存放數(shù)據(jù)的位置，點(diǎn)擊【確定】，拆分完成。
技巧13：根據(jù)固定寬度拆分
除了根據(jù)固定符號(hào)，還可以根據(jù)固定寬度來(lái)拆分。比如提取郵政編碼，固定寬度都是6位，用分列就非常容易。
操作步驟：選中數(shù)據(jù)列，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡下的【分列】，在【文本】分列向?qū)е羞x擇【固定寬度】，在下一步中，單擊目標(biāo)區(qū)域即可添加分割線，分割完成后，點(diǎn)擊【確定】，拆分完成。
技巧14：批量修改日期格式
分列還可以直接用來(lái)修改數(shù)據(jù)格式，比如對(duì)于各種不規(guī)范的日期數(shù)據(jù)，通過(guò)分列功能，可以馬上規(guī)范統(tǒng)一起來(lái)。
操作步驟：選中數(shù)據(jù)列，點(diǎn)擊【數(shù)據(jù)】選項(xiàng)卡下的【分列】，在【文本】分列向?qū)е兄苯狱c(diǎn)擊【下一步】，一直到最后一步，將【列數(shù)據(jù)格式】選擇為日期，點(diǎn)擊【確定】。
快速填充
自Excel2013以來(lái),表格中就多了“快速填充”功能。什么是快速填充呢？快速填充是“基于示例填充數(shù)據(jù)，自動(dòng)識(shí)別數(shù)據(jù)規(guī)律并重復(fù)運(yùn)行，能讓一些不太復(fù)雜的字符串處理工作變得更簡(jiǎn)單”。
使用快速填充，我們可以輕松完成提取/拆分、合并/組合、位置轉(zhuǎn)換等操作?？焖偬畛涞牟僮鞣浅：?jiǎn)單，先輸入1-2個(gè)數(shù)據(jù)結(jié)果，拖曳填充后，填充方式選擇【快速填充】即可。
技巧15：智能合并
技巧17：智能提取
技巧18：智能組合
今天的內(nèi)容就到這里了，下周將會(huì)給大家?guī)?lái)排序、篩選、單元格格式及條件格式的內(nèi)容，敬請(qǐng)期待！
聯(lián)系客服
