致命缺陷:不懂一維表!
沒有一維表的支撐,神也無法幫你成為Excel高手
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2019-01-04 18:29:36點擊:12651
今天的教程重點說的是制表習(xí)慣,只有采用一維表制作數(shù)據(jù)源表(原始數(shù)據(jù)表)才能夠讓后續(xù)工作變得輕松,才不會去花大量無用功學(xué)模板,寫復(fù)雜公式??梢哉f,從接觸Excel那天開始,你就需要懂什么是一維表什么是二維表,否則永遠也成不了Excel高手。什么是Excel中的一維表和二維表呢?最好的答案就在本篇教程中。
我們每天都在和各種各樣的數(shù)據(jù)打交道:財務(wù)數(shù)據(jù)、進銷數(shù)據(jù)、客戶管理數(shù)據(jù)、人事管理數(shù)據(jù)……有時候需要將數(shù)據(jù)按照領(lǐng)導(dǎo)的需要做成各種各樣的報表,有時候需要將數(shù)據(jù)導(dǎo)入到某個管理系統(tǒng)中。很多Excel新手、半灌水在處理數(shù)據(jù)的過程中問題層出不窮,究其原因,最主要一條:原始數(shù)據(jù)表(數(shù)據(jù)源表)不規(guī)范,不是一維表。
何為Excel中的一維表二維表
那么什么樣的原始數(shù)據(jù)表才是規(guī)范的呢?在說這個問題之前,需要明白什么是一維表、什么是二維表。
先來看一維表。
表1 表2
上圖就是兩個常見的一維表。簡單來說,一維表具有兩個典型的特征:
第一、單元格中的數(shù)據(jù)屬性等于對應(yīng)的列標題。
譬如:表1中單元格值“第一季度”“第二季度”屬性就是季度時間,所以其列標題就是“季度”;表2中單元格值“36”“45”屬性就是人的年齡,所以其列標題就是“年齡”。
第二、所有列標題之間的關(guān)系不是排除性的選填關(guān)系。
譬如:表1中“季度”“實際金額”標題下數(shù)值,并非是填寫了“季度”就不能填寫“實際金額”了。可能這一條,大家還有疑惑,不急,接著看二維表就了解了。
下面看二維表。
表3
表4
表3中單元格值“23954.89”“14122.31”的屬性是金額,但是列標題是“東北地區(qū)”“東南地區(qū)”等,屬性與列標題不一致。其實,如果把表3的表頭完整地繪制出來,大家看得更明白:
很明顯,表中各數(shù)字的屬性是“金額”,而非列標題“地區(qū)”。
表4中列標題“博士”“碩士”“本科”“大專及以下”對當(dāng)前表而言屬于典型的選填關(guān)系,同一行數(shù)據(jù)填寫了“博士”,就不能填寫“碩士”“本科”等了。同樣,把表4的表頭完整繪制出來,大家看得更明白:
很明顯“博士”“碩士”“本科”等列標題實際屬于同一個屬性“學(xué)歷”下的具體屬性值,按照習(xí)慣,學(xué)歷都登記最高學(xué)歷,因此各屬性值之間具有排除性,填寫了“博士”就不能填寫“本科”了。
在Excel中我們可以把所有非一維表的表都稱為二維表,上面的表3、表4都是典型的二維表。(注意:Excel中所說的二維表與關(guān)系模型中所說的二維表、關(guān)系表不是一個概念?。?/span>)
一維表適合存儲數(shù)據(jù)、記錄數(shù)據(jù),一般作為數(shù)據(jù)源使用,我們通過一維表進行統(tǒng)計分析時,不管使用公式函數(shù)還是透視表都很方便。更加重要的是,當(dāng)我們需要向某個系統(tǒng)導(dǎo)入數(shù)據(jù)時,一般只能以一維表的形式導(dǎo)入。
相對于一維表來說,二維表更加符合我們的視覺習(xí)慣,因此二維表經(jīng)常用來展示數(shù)據(jù),也可以簡單的理解為二維表適合用來做給人看的各種報表。
了解以上知識后,很多朋友可能會有一個疑問:到底什么時候使用一維表,什么時候使用二維表才算是正確呢?
很簡單,做數(shù)據(jù)源的時候或者是需要導(dǎo)入系統(tǒng)時,就得使用一維表;做數(shù)據(jù)展示時,就得使用二維表。舉個例子:辦公人員領(lǐng)用辦公用具的時候一般需要在一張領(lǐng)用表上登記,記錄上某年某月某日某部門的某某領(lǐng)取某型號(編碼)的某物品多少。這樣的登記表就必須是一維表,它根據(jù)事件記錄數(shù)據(jù),是后續(xù)數(shù)據(jù)的源頭。而一個月后,管理人員向上級匯報公司各部門的辦公用品領(lǐng)用情況,就使用二維表。
一維登記表
二維匯總表
數(shù)據(jù)源表采用二維表的危害
毫不夸張的說:只有正確地使用一維表和二維表才有可能高效率地使用Excel。我們就是因為沒有在適合的時候正確地使用表格形式,所以給自己的工作帶來很多麻煩。
如果數(shù)據(jù)源表采用了二維表,那在后續(xù)的數(shù)據(jù)匯總、分析中,就可能造成非常大的麻煩,無法直接通過透視表或者簡單的公式實現(xiàn)匯總分析。譬如暢銷書作者盧子就曾分享過自己的慘痛經(jīng)驗:為了從下面的原始數(shù)據(jù)表中得到不良品匯總表,苦思一周最終在朋友的幫助下編寫了一個超長的公式才解決問題。
原始數(shù)據(jù)表
不良匯總表
公式:
=INDEX(小天使!G:G,RIGHT(TEXT(LARGE(MMULT((LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>"",ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(MONTH(小天使!$I$4:$DJ$4)=$F$4)*(小天使!$G$5:$G$136<>"檢查數(shù)")*(小天使!$G$5:$G$136<>"其它")*小天使!$I$5:$DJ$136,ROW($1:$106)^0)+(LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>"",ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(小天使!$G$5:$G$136<>"檢查數(shù)")*(小天使!$G$5:$G$136<>"其它")*(小天使!$G$5:$G$136<>"")*0.1+ROW($5:$136)%%,ROW(A1)),"0.0000"),3)*1)
一維表與二維表的轉(zhuǎn)換
這時候就會出現(xiàn)另一個問題,為了用好一維表和二維表,我們可能經(jīng)常需要在兩種表之間進行轉(zhuǎn)換。會不會很麻煩呢?接下來為大家分享兩種簡單的轉(zhuǎn)換方法,希望大家能夠動手操作,將其掌握。
一維表轉(zhuǎn)為二維表
推薦使用數(shù)據(jù)透視表進行轉(zhuǎn)換,具體操作步驟看動畫演示:
平時更多的是使用一維表制作各種統(tǒng)計報表,單純將一維表轉(zhuǎn)換為二維表的需求并不多。
反而二維表轉(zhuǎn)為一維表的需求比較多,推薦使用公式的方法進行轉(zhuǎn)換。
二維表轉(zhuǎn)為一維表
(1)在A11處輸入如下公式后向下拉獲取二維表中的季度值,作為一維表的首列。
=OFFSET($A$1,INT((ROW(A1)-1)/7)+1,0)
這個公式中需要注意“7”這個數(shù)字,因為有7個機構(gòu),所以每個季度都需要重復(fù)7次。關(guān)于這個公式的原理,可以參考之前的教程:
網(wǎng)站用戶點擊:《Excel教程:等差數(shù)列、循環(huán)數(shù)列和重復(fù)數(shù)列構(gòu)造》
微信用戶點擊:《3個“規(guī)律”讓你輕松寫出Excel公式,比小學(xué)入學(xué)題還簡單!超實用!》
(2)在B11處輸入如下公式并向下拉獲取二維表中的機構(gòu)名稱,作為一維表的第2列。
=OFFSET($A$1,0,MOD(ROW(A1)-1,7)+1)
在這個公式中同樣要注意“7”,與機構(gòu)數(shù)相同即可。公式利用的是循環(huán)構(gòu)造數(shù)據(jù)的原理。
(3)在C11處輸入如下公式向下拉獲取二維表中的具體數(shù)據(jù)作為一維表的第3列。
=OFFSET($A$1,INT((ROW(A1)-1)/7)+1,MOD(ROW(A1)-1,7)+1)
這個公式相信大家都看出來了,有兩個地方出現(xiàn)了7,同時兼顧了重復(fù)性和循環(huán)性。
利用公式將二維表轉(zhuǎn)為一維表的幾個公式,套路相對比較固定,只要根據(jù)二維表的列數(shù)去修改公式中的數(shù)字就行,本例中數(shù)字是7。
小結(jié):
一維表和二維表互相轉(zhuǎn)換的方法很多,就不一一列舉了。我們今天重點闡述的是這兩種表的特性和用途。同時告訴大家,養(yǎng)成采用一維表制作數(shù)據(jù)源表的習(xí)慣,遠比掌握幾個技巧會用幾個公式更加重要。當(dāng)然數(shù)據(jù)表的制作規(guī)范并非僅僅是一維表這一條要求,更多要求我們會逐漸介紹給大家。
本文配套的練習(xí)課件請加入QQ群:264539405下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
寫公式必須會構(gòu)造數(shù)列《 Excel教程:等差數(shù)列、循環(huán)數(shù)列和重復(fù)數(shù)列構(gòu)造》
數(shù)據(jù)表的幾條制作規(guī)范《 excel教程:Excel規(guī)范的數(shù)據(jù)錄入》
數(shù)據(jù)透視表入門教程1《按需匯總數(shù)據(jù)和工作表拆分》
數(shù)據(jù)透視表入門教程2《常見疑難雜癥的處理方法》
數(shù)據(jù)透視表入門教程3《透視表排序的4種方法和插入公式》
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的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單元格中的算式,四種求和方法請收好!