只再說最后一次:Excel數(shù)據(jù)源表家規(guī)
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2019-01-17 17:25:44點擊:5690
Excel處理數(shù)據(jù)有自己的游戲規(guī)則,尤其是數(shù)據(jù)源表規(guī)矩森嚴。想Excel運行快,操作順,就必須遵守Excel的家規(guī)。在數(shù)據(jù)處理中,那種蔑視規(guī)則的人,忽視規(guī)則的人,注定要碰得頭破血流,因為Excel不!留!情!
在前幾天我們分享了一篇文章,專門講解了用一維表做數(shù)據(jù)源的重要性,今天繼續(xù)這個話題,介紹數(shù)據(jù)源的規(guī)范。
Excel對數(shù)據(jù)處理有一套自己的規(guī)則,只有按照它的規(guī)則去做,我們后續(xù)才能輕松地完成各種統(tǒng)計匯總數(shù)據(jù)處理。下面以一個不規(guī)范的原始數(shù)據(jù)表格為例,介紹Excel的家規(guī)。希望大家能夠引以為戒,最大程度的規(guī)范數(shù)據(jù),提高效率。
這是某單位的銷售記錄表:
這種記錄表大家一定不陌生,或者你自己就是這樣做記錄的。這種記錄表的確是一維表,而且還挺美觀。但是這個記錄表有多處違反Excel數(shù)據(jù)源表家規(guī),數(shù)據(jù)后續(xù)處理麻煩不斷。你發(fā)現(xiàn)有哪些地方不符合家規(guī)了嗎?
第1項錯誤:出現(xiàn)表格標(biāo)題(名稱)
Excel家規(guī)第3條:不要表格標(biāo)題(名稱)。
作為數(shù)據(jù)源的記錄表,表格內(nèi)部不需要表格標(biāo)題(名稱)。本例有表格標(biāo)題“衛(wèi)龍商貿(mào)銷售記錄表”,違反了家規(guī)第2條。其實表格標(biāo)題完全沒有必要放在表中,如果只是為了說明數(shù)據(jù)涉及的內(nèi)容,可以在文件名稱或者sheet名稱中去說明。
與家規(guī)3“外貌”類似實質(zhì)不同的第2條家規(guī)是:單行表頭。
單行表頭就是數(shù)據(jù)源記錄表的表頭只能是一行,不能多行。這一條與家規(guī)第1條“必須是一維表”密切相關(guān)。往往多行表頭的表格都不是一維表。多行表頭更常見的是工資表、考勤表等等,例如這種:
表格的第一行和第二行都是表頭信息。這類報表在創(chuàng)建數(shù)據(jù)透視表之前需要將多行表頭合并為一行。當(dāng)然上面的考勤表不僅僅是多行表頭,它還違反了一維表家規(guī)。
第2項錯誤:數(shù)據(jù)格式不一致
Excel家規(guī)第4條:同列單元格格式一致(表頭除外)。
當(dāng)前表格中日期的記錄方式很隨意:
同一列數(shù)據(jù)格式不同通常是由于不同的人進行數(shù)據(jù)錄入時完全根據(jù)個人習(xí)慣操作所致。如果已經(jīng)有了大量的不規(guī)范日期,也不需要重新去修改,可以通過分列進行規(guī)范:
選中A3:A12進行分列,分隔符號設(shè)置為“Tab鍵”,列數(shù)據(jù)格式選擇“日期”。
細心的朋友可能注意到了,我們在分列的時候是選擇的單元格區(qū)域,而沒有選擇整列,如果數(shù)據(jù)行數(shù)比較多的話,選取也是比較麻煩的。
為什么不直接選擇整列去分列呢?我們可以選擇整列試試看會發(fā)生什么:
相信大家都明白了,就是第一行標(biāo)題中的合并單元格造成了麻煩。如果沒有這一行的話,選擇整列完全沒問題。
第3項錯誤:數(shù)字帶單位
Excel家規(guī)第5條:數(shù)字不帶單位。
很多朋友習(xí)慣在數(shù)字中帶上單位,例如300元,20天,10個等等,其實這種習(xí)慣不但為自己增加了工作量,而且對數(shù)據(jù)統(tǒng)計也帶來了麻煩。譬如下面,我們進行求和操作就會出現(xiàn)問題:
可能會有人告訴你一個公式,對帶單位的數(shù)據(jù)進行求和:
=SUMPRODUCT(--MID(C3:C16,1,LEN(C3:C16)-2))
注:這是一個SUMPRODUCT函數(shù)套路公式,利用兩個負號,把(MID(C3:C16,1,LEN(C3:C16)-2))變成了乘積運算,它實質(zhì)等于SUMPRODUCT(1*MID(C3:C16,1,LEN(C3:C16)-2))。關(guān)于 SUMPRODUCT函數(shù)的用法,請查看部落窩教育相關(guān)教程。
那也不能每次為了求和的問題就到處找人幫忙吧,正確的做法是數(shù)字與單位分兩列存放:
但有時候老板非要讓你把單位和數(shù)字寫在一起,怎么辦?可以用障眼法?。?/span>
看明白了吧,就是利用自定義格式添加單位,注意單位要放在英文狀態(tài)下的引號中間。
第4項錯誤:單元格合并
Excel家規(guī)第6條:不能合并單元格。
合并單元格會帶來很多麻煩。譬如:
因為合并單元格,篩選的數(shù)據(jù)不全,而且不能排序。更糟糕的是,這種數(shù)據(jù)源在使用數(shù)據(jù)透視表工具時錯誤百出:
很多數(shù)據(jù)被計入了空白項,由此可見合并單元格的破壞性了。合并單元格雖然在視覺效果上有優(yōu)勢,但是完全打亂了數(shù)據(jù)結(jié)構(gòu),統(tǒng)計時就會有各種各樣的問題。
那么如何來處理這些合并單元格呢?也不難!
操作步驟大致是這幾步:選擇范圍后取消合并單元格,然后按F5或者CTRL+G組合鍵打開定位功能,定位空值,確定后先按一次=鍵,再按一下方向鍵↑,不要點擊任何地方,接著按Ctrl+Enter鍵。最后復(fù)制這部分數(shù)據(jù)選擇性粘貼為數(shù)值。具體過程看動畫演示:
經(jīng)過這樣處理后,排序篩選統(tǒng)統(tǒng)不受影響,將數(shù)據(jù)透視表刷新一下,結(jié)果也正確了:
與家規(guī)6相似的家規(guī)7:不用合計行。不要在數(shù)據(jù)源表中邊記錄數(shù)據(jù)邊進行匯總。
可見很多問題并不是技術(shù)不好造成的,而是壞習(xí)慣造成的。相比技術(shù)學(xué)習(xí),更重要的是養(yǎng)成好習(xí)慣。下面把Excel數(shù)據(jù)源表家規(guī)集中列出:
家規(guī)1:必須是一維表
家規(guī)2:單行表頭
家規(guī)3:不要表格標(biāo)題
家規(guī)4:同列單元格格式一致
家規(guī)5:數(shù)字不帶單位
家規(guī)6:不能合并單元格
家規(guī)7:不用合計行
以上就是今天分享的內(nèi)容,覺得有用就告訴更多人吧。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
Excel數(shù)據(jù)源表規(guī)范1《致命缺陷:不懂一維表!》
數(shù)據(jù)表的幾條制作規(guī)范《Excel教程:Excel規(guī)范的數(shù)據(jù)錄入》
SUMPRODUCT函數(shù)教程1《加了*的 SUMPRODUCT函數(shù)無所不能》
SUMPRODUCT函數(shù)教程2《Excel必會函數(shù)之SUMPRODUCT函數(shù)》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!