EXCEL破冰 - 如何為透視表組織資料

wait4friend發表於2018-05-11

Excel

背景

我會把一些原來部落格中比較好的文章轉移到這裡來。

EXCEL用於日常資料分析的工具中，最便利並且最強大的莫屬透視表了。因為透視表對原始資料有一定的要求，所以本次的破冰之旅也將把焦點放在如何整理基礎資料這個方面。

1.1 初識透視表

如果有人告訴你，下面這兩個看起來很高大上的報表，其實只需要在EXCEL裡面用滑鼠點幾下就完成了。你是不是就已經感受到透視表的強大了？

1.2 透視表的資料要求

其實資料透視表對原始資料的要求並不苛刻，只要不是奇形怪狀的資料就已經滿足要求了。比如剛才的兩個透視表，原始資料片段如下圖。

簡單的說，原始資料在格式上需要滿足：

Ø 第一行必須是標題，且不能有空白標題或重複標題；

Ø 單元格格式正確，特別是日期和數字，不能用文字型別替代；

Ø 橫平豎直，不能有合併單元格等異形樣式；

Ø 一般情況下，所有資料都包含在一個工作表內；且使用簡單的一維表格式；

那麼，如果你的原始資料不滿足上面的要求，就需要進行一些轉換。下面，我們就介紹一些常用的資料轉換方法。

2 格式化時間

使用Excel處理資料的一個基本原則是，“單元格格式正確”。當我們需要分析的資料中包括日期或者時間的時候，這個原則尤其重要，一定不要使用文字或者數字格式來替代。因為Excel內部提供了很多針對日期的函式，可以極大的簡化資料分析工作。但是，如果我們用了文字格式來儲存日期，那麼這些函式將無法使用。

理想是豐滿的，現實是骨感的。在我們處理資料的過程中，大量的原始資料因為各種各樣奇葩的原因，選擇了用文字或者數字的方式儲存日期資料。常見的幾種格式如下圖所示：

2.1 資料分列

如果只是需要對日期部分進行格式化，那簡單的資料分列功能就可以實現我們的需求。在選中原始資料列的前提下，依次點選資料 – 分列 – 分隔符號 – 下一步，如下圖：

在文字分列嚮導第3步，選中日期YMD格式，然後點選完成，如下圖：

最終結果已經成功轉換為日期格式，如下圖：

2.2 公式轉換

資料分列的方法，僅能處理日期格式的資料。如果我們需要格式化日期時間的資料，可以使用公式來實現格式化。下面是一個常用的公式，可以把yyyymmddhhmiss格式的原始文字(或科學計數法)轉換成yyyy-mm-dd hh:mi:ss日期時間格式，效果如下圖：

=TEXT(G2,"0000-00-00 00!:00!:00")+0

為了能夠正確的顯示日期時間格式，還需要把單元格的格式設定成我們需要的日期時間格式，如下圖：

3 拆分合並單元格

有時候，資料錄入人員為了視覺效果會進行單元格的合併。這種合併過的資料表格，是沒辦法直接用來生成資料透視表的。

如上圖，左邊的業務員欄位進行了合併單元格處理，我們必須把這個欄位轉換成右邊這種格式才方便進行進一步分析。

怎麼破？難道取消合併單元格後一個一個的拷貝嗎？當然不用，只需要簡單幾個操作，就能實現這個要求。

在保證G列（業務員欄位）被選中的前提下，依次點選開始 – 取消單元格合併，如下圖

取消單元格合併之後的資料如下圖，每個業務員的名字僅在第一行內出現，其他行是空白。

在保證G列（業務員欄位）被選中的前提下，按快捷鍵F5，彈出“定位”視窗，點選“定位條件。。。”按鈕，在彈出的視窗中，依次點選空值 – 確定，如下圖

不要點選任何其他地方，直接在公式編輯框裡面輸入G3單元格的公式 =G2，最關鍵的步驟，同時按下 Ctrl + Enter，如下圖

空白單元格自動複製了對應的資料。如下圖，完成資料轉換。

4 查詢單個值（VLOOKUP函式）

有如下圖的原始訂單資訊，以及業務人員的ID/名稱對應關係表。

希望得到下圖的結果，要求是：

1) 根據業務員ID查詢對應的名字；

2) 把銷售金額進行分段標示；

這樣的需求，可不能傻乎乎的一個一個人肉查詢替換。用一個函式簡單搞定。

VLOOKUP是一個查詢函式，給定一個查詢的目標，它就能從指定的查詢區域中查詢返回想要查詢到的值。它的基本語法為：

VLOOKUP（查詢目標，查詢範圍，返回值的列數，精確OR模糊查詢)

4.1 精確查詢

如圖，實現對業務員ID和名稱的精確查詢

公式C2=VLOOKUP(B2,$J$1:$K$4,2,0)

引數說明：

Ø 查詢目標VLOOKUP(B2,$J$1:$K$4,2,0)：就是你指定的查詢的內容或單元格引用。圖中B列的業務員ID就是查詢目標。我們要根據“業務員ID”在J列進行查詢。

Ø 查詢範圍VLOOKUP(B2,$J$1:$K$4,2,0)：指定了查詢目標，如果沒有說從哪裡查詢，EXCEL肯定會很為難。所以下一步我們就要指定從哪個範圍中進行查詢。VLOOKUP的這第二個引數可以從一個單元格區域中查詢，也可以從一個常量陣列或記憶體陣列中查詢。本例中要在J1:K4這個區域中進行查詢，並且使用了$J$1這樣的絕對引用方式，保證公式在縱向進行復制的時候保持不變。

Ø 返回值的列數VLOOKUP(B2,$J$1:$K$4,2,0)：這是VLOOKUP第3個引數。它是一個整數值。它怎麼得來的呢。它是“返回值”在第二個引數給定的區域中的列數。本例中我們要返回的是“業務員”，它是第二個引數查詢範圍J1:K4的第2列。這裡一定要注意，列數不是在工作表中的列數（不是第K列），而是在查詢範圍區域的第幾列。

Ø 精確OR模糊查詢VLOOKUP(B2,$J$1:$K$4,2,0)：，最後一個引數是決定函式精確和模糊查詢的關鍵。精確即完全一樣，模糊即包含的意思。引數如果指定值是0或FALSE就表示精確查詢，而值為1 或TRUE時則表示模糊。