很多經(jīng)常需要做Excel統(tǒng)計(jì)的童鞋都知道,一份表發(fā)給各部門的同事去填寫瘫辩,收上來(lái)的表格通常都是五花八門的。格式不規(guī)范不說(shuō)伐厌,就是數(shù)據(jù)本身也經(jīng)常會(huì)出現(xiàn)錯(cuò)誤。很多童鞋雖然苦口婆心要求填寫的同事按照要求處理弧械,但效果總是不那么如意。那么有沒(méi)有更好的辦法來(lái)避免這些問(wèn)題呢刃唐?今天給大家簡(jiǎn)單介紹3個(gè)簡(jiǎn)單的技巧。
規(guī)范數(shù)據(jù)錄入格式
這是一切問(wèn)題的根源画饥,如果我們能從源頭上對(duì)分發(fā)下去的表格做好相應(yīng)的設(shè)置,就會(huì)避免掉很多后續(xù)處理方面的問(wèn)題抖甘。我們就以下面的表格作為例子,一一說(shuō)明如何設(shè)置才能讓填表人按照我們的要求去填寫衔彻。
這是一份簡(jiǎn)單的汽車銷售記錄表,我們要求姓名列的所有單元格最好不要有重復(fù)記錄(因?yàn)楹苌儆腥艘淮钨I兩臺(tái)車的)澄港;身份證號(hào)碼的長(zhǎng)度要么15位柄沮,要么18位,長(zhǎng)度不對(duì)即為錯(cuò)誤祖搓,為了提高錄入準(zhǔn)確性,我們可設(shè)置身份證號(hào)碼列文本長(zhǎng)度為15位或者18位……
1.制作下拉列表的小技巧
如果要填入某些列的數(shù)據(jù)比較固定拯欧,只有有限的幾項(xiàng)填寫內(nèi)容。這樣的列我們可利用Excel數(shù)據(jù)有效性設(shè)置下拉選項(xiàng)镐作,填入時(shí)只需選擇即可蚓再。這即可提高數(shù)據(jù)錄入的效率,更可以避免不規(guī)范的數(shù)據(jù)格式(便于統(tǒng)計(jì),格式不統(tǒng)一通常會(huì)導(dǎo)致統(tǒng)計(jì)結(jié)果錯(cuò)誤)靶庙。下面我們就來(lái)看看設(shè)置下拉列表:
1)選中C2:C10單元格區(qū)域,在“數(shù)據(jù)”選項(xiàng)卡 中單擊“數(shù)據(jù)驗(yàn)證”按鈕六荒,打開“數(shù)據(jù)驗(yàn)證”對(duì)話框;
2)單擊“設(shè)置”選項(xiàng)卡掏击,在“允許”下拉列表中選擇“序列”類別卵皂;注意“允許”下拉列表右側(cè)的“提供下拉箭頭”一定要勾選灯变;
3)鼠標(biāo)直接選取表中的G2:G7區(qū)域,單擊“展開”按鈕捅膘,返回“數(shù)據(jù)驗(yàn)證”對(duì)話框;
4)單擊“確定”按鈕即可寻仗。
2.杜絕單元格中空格的技巧
很多同事輸入兩個(gè)字的姓名時(shí),喜歡在姓與名之間加上空格耙替,已達(dá)到同其他三個(gè)字的姓名對(duì)齊(美觀)的目的,但這樣的操作卻會(huì)給我們的統(tǒng)計(jì)帶來(lái)很大的麻煩俗扇。我們可設(shè)置“單元格中禁止空格輸入”達(dá)到規(guī)范輸入的目的。按以下步驟操作吧:
1)選中A2:A10單元格區(qū)域狐援,在“數(shù)據(jù)”選項(xiàng)卡 中單擊“數(shù)據(jù)驗(yàn)證”按鈕,打開“數(shù)據(jù)驗(yàn)證”對(duì)話框究孕;
2)單擊“設(shè)置”選項(xiàng)卡,在“允許”下拉菜單中選擇“自定義”厨诸,“公式”框中輸入公式:=ISERROR(FIND(" ",$A2))
3)單擊“出錯(cuò)警告”選項(xiàng)卡,左側(cè)“樣式”下拉菜單選擇“停止”微酬,右側(cè)框中按圖所示輸入內(nèi)容颤陶,標(biāo)題輸入:“姓名之間請(qǐng)勿插入空格”陷遮,內(nèi)容輸入:“請(qǐng)檢查是否在性名之間插入了空格!”自己想怎么輸入就怎么輸入咯帽馋。
4.單擊“確定”即可。
出錯(cuò)警告中的樣式有3種吧慢,“停止”、“警告”以及“信息”检诗。選擇“停止”,你將無(wú)法再單元格中輸入不符合規(guī)范的內(nèi)容岁诉;“警告”會(huì)提示你是否繼續(xù)輸入;“信息”只是提示你是否取消輸入涕癣。
3.規(guī)范錄入“車牌號(hào)碼”的技巧
貴陽(yáng)的車牌號(hào)碼為7位,以“貴A”開頭坠韩,我們?cè)阡浫氲臅r(shí)候,可以簡(jiǎn)單設(shè)置只搁,避免錄入錯(cuò)誤或者無(wú)效的數(shù)據(jù);跟上文相同的操作步驟我不再贅述氢惋,直接進(jìn)入設(shè)置--自定義--公式洞翩,公式中錄入:=AND(LEFT(D2,2)="貴A",LEN(D2)=7)焰望。
規(guī)避或者圈釋無(wú)效數(shù)據(jù)的技巧
無(wú)效數(shù)據(jù)沒(méi)用,而且還會(huì)影響我們的統(tǒng)計(jì)結(jié)果来屠。如何在制表之初就將這個(gè)問(wèn)題解決掉,避免后續(xù)諸多的麻煩俱笛?且看一下技巧:
1.限定輸入本月日期的技巧
我們統(tǒng)計(jì)提成或者銷售額也好一般都是按月統(tǒng)計(jì)的,那么我們每月收上來(lái)的表格所有的內(nèi)容就應(yīng)該是當(dāng)月的內(nèi)容迎膜。下面的技巧可以避免員工錄入其他月份的日期或者其他不在范圍內(nèi)的日期。
1)單擊“數(shù)據(jù)”--“數(shù)據(jù)驗(yàn)證”--“數(shù)據(jù)驗(yàn)證”星虹,打開“數(shù)據(jù)驗(yàn)證”對(duì)話框零抬;
2)單擊設(shè)置--允許下拉列表選擇“日期”--輸入開始日期:=DATE(YEAR(TODAY()),MONTH(TODAY()),1),也可以直接輸入本月的1號(hào)--輸入結(jié)束日期:=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)蝶棋,也可以直接輸入本月的最后一天卸亮;--出錯(cuò)警告--停止--輸入內(nèi)容--確定即可兼贸。
3)此栗子中可直接輸入本月的開始日期:2016-05-01,結(jié)束日期:2016-05-30吃溅。但是這樣輸入非常簡(jiǎn)單,但每個(gè)月都得更改一遍决侈,遠(yuǎn)遠(yuǎn)不如用公式來(lái)得方便。請(qǐng)仔細(xì)琢磨本例中的公式赖歌。
2.提高身份證錄入準(zhǔn)確率的小技巧
盡可能減少身份證號(hào)碼錄入的錯(cuò)誤率庐冯,下面是一個(gè)小技巧。本例中身份證號(hào)碼的驗(yàn)證規(guī)則包括身份證號(hào)碼長(zhǎng)度必須為15位或者18位展父。
1)打開“數(shù)據(jù)驗(yàn)證”對(duì)話框“設(shè)置”窗口,“允許”下拉菜單選擇“自定義”;
2)公式框中輸入:=or(len($B2)=15,len($B2)=18)
3)填寫“出錯(cuò)警告”內(nèi)容后點(diǎn)擊確定栖茉,如圖所示:
3.Excel錄入避免重復(fù)值的技巧
Excel中如何避免同列錄入重復(fù)值?請(qǐng)參考以下設(shè)置技巧,請(qǐng)按圖中步驟操作即可弃榨,設(shè)置的公式為:=countif(A:A,$A2)=1。
4.圈釋無(wú)效數(shù)據(jù)的技巧
數(shù)據(jù)驗(yàn)證只是針對(duì)數(shù)據(jù)錄入的鲸睛,但一個(gè)表格已經(jīng)填寫完整,我們只是設(shè)置“數(shù)據(jù)驗(yàn)證”是無(wú)法找出“無(wú)效數(shù)據(jù)”的官辈。如果別人給到我們一個(gè)表格,如何快速地標(biāo)示出其中的不符合規(guī)范或者無(wú)效的數(shù)據(jù)呢拳亿?這里就要用到“詮釋無(wú)效數(shù)據(jù)”的技巧。
1)首先肺魁,請(qǐng)根據(jù)需要設(shè)置“數(shù)據(jù)驗(yàn)證”电湘;
2)使用數(shù)據(jù)詮釋:數(shù)據(jù)--數(shù)據(jù)驗(yàn)證--詮釋無(wú)效數(shù)據(jù)鹅经。
3)紅色標(biāo)示出的即是“無(wú)效數(shù)據(jù)”。如圖:
數(shù)據(jù)驗(yàn)證的刪除技巧
設(shè)置了數(shù)據(jù)有效性贷痪,但是有些驗(yàn)證不需要了如何刪除呢?首先呢劫拢,我們要找出表格中或者選區(qū)中設(shè)置了數(shù)據(jù)有效性的單元格,然后再執(zhí)行刪除的操作胖缤。
1)利用定位功能查到設(shè)置了數(shù)據(jù)有效性的單元格區(qū)域:選擇中A2:E10,按下Ctrl+G或者F5打開“定位”對(duì)話框;
2)單擊定位條件哪廓,進(jìn)入“定位條件”,選擇“數(shù)據(jù)驗(yàn)證”涡真。單元格中的數(shù)據(jù)有效性即被選中;
3)按Alt+A+V+V,進(jìn)入"數(shù)據(jù)驗(yàn)證“--”設(shè)置“哆料,點(diǎn)擊最下方的”全部清除“--確定即可缸剪。
今天的分享就到此結(jié)束了东亦,幾個(gè)新技能你get到了嗎唬渗?這可是我熬夜的成果哦。如果對(duì)你有所幫助镊逝,我將萬(wàn)分高興。
公眾號(hào)如需轉(zhuǎn)載撑蒜,務(wù)必聯(lián)系我索要授權(quán)。