【職場小劇場】
職場新人小張,好不容易錄完的表,可這里面有一些E+10、E+17和一堆###......
看到這張表,小張頭暈連連的問道:“表姐,我這張表是不是中毒了呀?”
其實(shí)小張的問題,就是因?yàn)樽霰淼臅r(shí)候,數(shù)據(jù)不規(guī)范。
數(shù)據(jù)的規(guī)范錄入
我們先來看看,小張的這張究竟不規(guī)范在哪?
①日期顯示為“####”
原因分析:單元格列寬不夠,造成顯示不全。因此,當(dāng)單元格內(nèi)容過多時(shí),會(huì)顯示為####。
解決方案:將E列“入職時(shí)間”的列寬調(diào)大一些即可。
②身份證號(hào)顯示為“E+”
原因分析:Excel對(duì)于輸入超過11位的數(shù)字,會(huì)以科學(xué)記數(shù)的表達(dá)方式來顯示。并且科學(xué)記數(shù)法的數(shù)字精度為15位,超過15位的所有數(shù)字,Excel都將其自動(dòng)改為0。這也是平時(shí)工作中,我們錄入身份證號(hào)碼后,有時(shí)會(huì)發(fā)生最后三位顯示為“0”的原因所在了。
解決方案:對(duì)于長串?dāng)?shù)字,要讓它以文本的格式進(jìn)行錄入。
表姐口訣:數(shù)字不計(jì)算,文本大法,早用早好。
長文本的錄入技巧:
①在單元格先輸入’(英文狀態(tài)下的單引號(hào)),然后再輸入數(shù)字,此時(shí)單元格會(huì)默認(rèn)寫入的是“文本格式”。錄入完畢后,單元格的左上角出現(xiàn)有一個(gè)綠色小三角,提示你該單元格是“以文本形式存在的數(shù)字”。
②先設(shè)置單元格為:”文本”格式,再錄入內(nèi)容。重新插入一列E列,選中E列后→選擇【開始】選項(xiàng)卡→將【單元格格式】從“常規(guī)”改為“文本”。
設(shè)置完畢后,你再錄入任何數(shù)字,它顯示的格式也都是“文本”型,也就是無論你輸入多長,Excel都將按照你輸入的內(nèi)容進(jìn)行顯示,不會(huì)再改為科學(xué)計(jì)數(shù)法的形式。這個(gè)技巧常常用于錄入:身份證號(hào)碼、手機(jī)號(hào)碼、銀行卡號(hào)等長串?dāng)?shù)字的錄入。
長串?dāng)?shù)字錄入時(shí),必須先設(shè)置單元格格式為文本格式后,再錄入數(shù)據(jù)。否則,如上圖中D列所示,先錄入好了數(shù)字后,再將單元格格式改為“文本格式”,是不起作用的,只能重新錄入才行。
③日期列篩選不自動(dòng)分到年、月
我們?cè)倏匆幌麻_始時(shí)小張的表里“入職時(shí)間”F列,這些日期輸?shù)亩疾惶粯樱河械氖且孕?shù)點(diǎn)分割年月日、有的是寫年月日、有的沒有寫日寫的是號(hào)、還有的是以斜杠分隔……非?;靵y。
當(dāng)選擇【開始】選項(xiàng)卡→【排序和篩選】→點(diǎn)擊【篩選】→打開篩選功能后,不難發(fā)現(xiàn)有些日期,Excel會(huì)自動(dòng)幫我們分類為“年→月→日”,但是有些卻不會(huì),見下圖中紅框的部分。
原因分析:不能夠自動(dòng)歸類的,都是“不規(guī)范的假日期”。咱們?cè)谄綍r(shí)工作中,不用費(fèi)勁記那些是不規(guī)范的,因?yàn)殄e(cuò)誤是不可能窮舉的。咱們只用記住規(guī)范的“真”日期只有以下三種情況:
表姐口訣:一橫一撇年月日,任何符號(hào)輸英文。
【一橫】用短橫線“-”分隔的日期,如:2019-1-1
【一撇】用斜線“/”分隔的日期,如:2091/1/1
【年月日】用中文“年月日”分隔的日期,如:2019年1月1日
解決方案:將“假日期”修改為“真日期”。篩選出“假日期”(見下圖),這三行日期實(shí)際上就是錯(cuò)誤的日期格式,然后依次修改為“真日期”格式。
修改完后,再點(diǎn)擊篩選功能,可以看到所有的日期都自動(dòng)進(jìn)行“年→月→日”的分組了,并且點(diǎn)擊【日期篩選】按鈕,還可以看到更多的篩選方案。而“真日期”也是后面我們利用數(shù)據(jù)透視表,自動(dòng)生成月報(bào)、季報(bào)、年報(bào)的“重要基礎(chǔ)”,它利用的就是“真日期”自動(dòng)分組的功能。
如果要把短期日(如:2019/1/1)顯示為長日期(如:2019年1月1日),只需要選中整列→選擇【開始】選項(xiàng)卡→將單元格格式改為:“長日期”即可。
數(shù)據(jù)驗(yàn)證提前設(shè)
上圖是公司給員工發(fā)住房補(bǔ)貼的表:
如果是住公司就沒補(bǔ)貼,不住公司就有1000元補(bǔ)貼。但是,收回來的統(tǒng)計(jì)表,總有一些人填了亂七八糟的,讓咱沒有辦法統(tǒng)計(jì)。
這個(gè)時(shí)候,你就得挨個(gè)兒跟他們?nèi)ゴ_認(rèn),這些“非主流”的回答后面,真正的含義是啥?
表姐建議大家:“比起事后救火填坑,最好的方法是:事前控制”。在Excel的世界里,像這種事先控制約束,用到的是“數(shù)據(jù)驗(yàn)證”。顧名思義:當(dāng)你滿足了我的驗(yàn)證條件,你才能夠往里輸內(nèi)容,否則那就報(bào)錯(cuò)。這樣發(fā)給別人的時(shí)候,別人只能按照這個(gè)規(guī)范去做。
咱們回到這個(gè)表格空白的時(shí)候,也就是,新建一張空白sheet表,把標(biāo)題表頭,按照?qǐng)D示填好以后,開始進(jìn)行“數(shù)據(jù)驗(yàn)證”的設(shè)置。
①建立驗(yàn)證原則的參數(shù)表:
在表格下方新增一張【參數(shù)】sheet表,輸入部門、是否住公司等參數(shù)。
②設(shè)置“部門來源”的數(shù)據(jù)驗(yàn)證:序列型數(shù)據(jù)驗(yàn)證
(1)選中“部門”A列→選擇【數(shù)據(jù)】選項(xiàng)卡→【數(shù)據(jù)驗(yàn)證】
(2)在彈出的【數(shù)據(jù)驗(yàn)證】對(duì)話框→【允許】→選擇【序列】
(3)設(shè)置【來源】→點(diǎn)擊折疊窗口按鈕→選擇參數(shù)表里的數(shù)據(jù)來源
③查看設(shè)置效果
設(shè)置完成后,點(diǎn)擊部門的下拉框:其中可選的內(nèi)容為上圖中設(shè)置的序列來源(見下圖);如果要手工輸入非允許范圍內(nèi)的值,如:“市場營銷部”,則會(huì)彈出錯(cuò)誤提示【此值與此單元格定義的數(shù)據(jù)驗(yàn)證限制不匹配】
④增加序列內(nèi)容
如果部門內(nèi)容發(fā)生新增或修改,可以在序列允許的范圍內(nèi),即:參數(shù)sheet表A1:A8范圍內(nèi),直接新增或修改就好。
同理,操作“是否住公司”F列的數(shù)據(jù)驗(yàn)證效果。
如果要制作動(dòng)態(tài)數(shù)據(jù)驗(yàn)證,或二級(jí)動(dòng)態(tài)聯(lián)動(dòng)效果的數(shù)據(jù)驗(yàn)證,請(qǐng)關(guān)注后續(xù)章節(jié)《【福利章】超級(jí)表與動(dòng)態(tài)數(shù)據(jù)驗(yàn)證》。
表格的其他規(guī)范性設(shè)置
【1】設(shè)置“員工姓名”的數(shù)據(jù)驗(yàn)證:文本長度型數(shù)據(jù)驗(yàn)證
①選中“員工姓名”B列→選擇【數(shù)據(jù)】選項(xiàng)卡→【數(shù)據(jù)驗(yàn)證】
②在彈出的【數(shù)據(jù)驗(yàn)證】對(duì)話框→【允許】→選擇【文本長度】
③設(shè)置數(shù)據(jù):“介于”指定范圍內(nèi),如:人名的指定長度范圍是2~5
【2】設(shè)置手機(jī)號(hào)、身份證號(hào)的數(shù)據(jù)驗(yàn)證:長串文本型數(shù)字?jǐn)?shù)據(jù)驗(yàn)證
①把手機(jī)號(hào)、身份證號(hào)碼列單元格設(shè)置成文本格式:選擇【開始】選項(xiàng)卡→設(shè)置單元格格式→設(shè)置為【文本】
②設(shè)置手機(jī)號(hào)數(shù)據(jù)驗(yàn)證:選中“手機(jī)號(hào)”C列→選擇【數(shù)據(jù)】選項(xiàng)卡→【數(shù)據(jù)驗(yàn)證】
→在彈出的【數(shù)據(jù)驗(yàn)證】對(duì)話框→【允許】→選擇【文本長度】→【數(shù)據(jù)】→選擇【等于】→長度設(shè)為:11,點(diǎn)擊【確定】按鈕。
③設(shè)置身份證號(hào)碼數(shù)據(jù)驗(yàn)證:選中“身份證號(hào)碼”D列→選擇【數(shù)據(jù)】選項(xiàng)卡→【數(shù)據(jù)驗(yàn)證】;在彈出的【數(shù)據(jù)驗(yàn)證】對(duì)話框→【允許】→選擇【文本長度】→【數(shù)據(jù)】→選擇【等于】→長度設(shè)為:18→繼續(xù)點(diǎn)擊【輸入信息】頁簽→在【輸入信息】欄→輸入:“請(qǐng)您輸入18位身份證號(hào)碼”→點(diǎn)擊【確定】按鈕。這樣當(dāng)填表人鼠標(biāo)選中此列單元格時(shí),就會(huì)自動(dòng)出現(xiàn)“溫馨提示”,避免亂填。
【3】設(shè)置入職時(shí)間的數(shù)據(jù)驗(yàn)證:日期型數(shù)據(jù)驗(yàn)證
①把入職日期列單元格設(shè)置為日期格式:選擇【開始】選項(xiàng)卡→設(shè)置單元格格式為→短日期。
②設(shè)置入職時(shí)間數(shù)據(jù)驗(yàn)證:選中“入職時(shí)間”E列→選擇【數(shù)據(jù)】選項(xiàng)卡→【數(shù)據(jù)驗(yàn)證】→允許值選擇【日期】→數(shù)據(jù)選擇【大于等于】→開始日期設(shè)置為公司創(chuàng)立的時(shí)間,如:2010-1-1(注意規(guī)范的日期格式寫法),點(diǎn)擊【確定】按鈕。
現(xiàn)在,我們已經(jīng)通過“數(shù)據(jù)驗(yàn)證”,完成表格的“事前控制”。這樣再交給別人填寫,咱們采集回來的信息,就會(huì)比較規(guī)范了。
彩蛋
本節(jié)小結(jié):
本節(jié)我們學(xué)習(xí)到的是數(shù)據(jù)規(guī)范性錄入的技巧,比如“早用文本大法”去錄入身份證號(hào)碼、銀行卡號(hào)這樣長串的數(shù)據(jù)錄入,以及怎么去錄入規(guī)范的“真日期”。
在工作當(dāng)中,表姐推薦大家通過“數(shù)據(jù)驗(yàn)證”的方法,給單元格設(shè)置一套填寫規(guī)范,保證我們數(shù)據(jù)采集的準(zhǔn)確。這樣我們往后,才可以做數(shù)據(jù)分析,挖掘數(shù)據(jù)價(jià)值。
聯(lián)系客服