二維碼 購物車
部落窩在線教育歡迎您!

致命缺陷:不懂一維表!

沒有一維表的支撐,神也無法幫你成為Excel高手

?

作者:老菜鳥來源:部落窩教育發(fā)布時間:2019-01-04 18:29:36點擊:12651

分享到:
0
收藏    收藏人氣:2人
版權(quán)說明: 原創(chuàng)作品,禁止轉(zhuǎn)載。
編按:

今天的教程重點說的是制表習(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

http://zuijiahehuoren.cn/ewebeditor/uploadfile/20180914164059384001.png

相關(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種方法和插入公式