如何運用Power Query為透視表準備規(guī)范數(shù)據(jù)源—結(jié)構(gòu)規(guī)范

數(shù)據(jù)透視表是Excel中最強大的統(tǒng)計分析工具，而且簡單易用，深受職場精英的歡迎。

但是，透視表要想順利必須滿足規(guī)范的結(jié)構(gòu)和格式要求。結(jié)構(gòu)方面必須滿足“一維數(shù)據(jù)”表格式，就是不能直接使用交叉結(jié)構(gòu)報表作為數(shù)據(jù)源。

• 如果列方向有同類參數(shù)那就是二維表

• 如果每一列都是獨立的參數(shù)那就是一維表

下圖是本節(jié)案例的原始數(shù)據(jù)表，它的結(jié)構(gòu)是典型的二維交叉結(jié)構(gòu)表，表格中實際包含了4個方面的信息：地區(qū)、城市、產(chǎn)品分類、金額。如果用手工調(diào)整成為“一維數(shù)據(jù)”表是很麻煩的，下面我們看看用Excel中的Power Query組件如何快速完成結(jié)構(gòu)轉(zhuǎn)換。

1. 數(shù)據(jù)轉(zhuǎn)置

這個案例中因為要使用Power Query中的“向下填充”功能，完成地區(qū)信息的填充，我們需要將地區(qū)、城市放到左表頭，下面看看Power Query是如何做到的。

將上圖的報表，獲取到Poewr Query編輯器中，然后進行轉(zhuǎn)置操作。轉(zhuǎn)置操作有一個要求：轉(zhuǎn)置的上表頭不能設(shè)置為表格標題

下圖是轉(zhuǎn)置操作與調(diào)整前后的對比。

2. 數(shù)據(jù)填充

完成數(shù)據(jù)轉(zhuǎn)置后，要利用Power Query的數(shù)據(jù)填充功能，解決合并單元格去除后產(chǎn)生的地區(qū)數(shù)據(jù)為空“ null “的情況。

合并單元格是Excel中最常見的格式應(yīng)用，它可以幫助用戶清晰的呈現(xiàn)信息的分類情況，但是單元格在合并時，相關(guān)區(qū)域中出現(xiàn)數(shù)據(jù)為空不完整的情況，嚴重影響了后期數(shù)據(jù)篩選與統(tǒng)計分析。Power Query獲取了這些數(shù)據(jù)后，合并單元格結(jié)構(gòu)被自動解除，還原成一個個獨立的單元格，我們要完成的就是將空單元格中填充上相關(guān)的數(shù)據(jù)，讓數(shù)據(jù)表內(nèi)容完整。

操作方法是，選擇要填充的第一列地區(qū)信息，執(zhí)行“轉(zhuǎn)換“選項卡中的”填充“操作。

填充功能只支持“上“、”下“填充，也是因為這個原因，我們需要先把表格進行轉(zhuǎn)置，將”地區(qū)“信息調(diào)整到左表頭。

3. 二維表逆透視

下面是本節(jié)最后的關(guān)鍵步驟，這里注意“逆透視“操作前，必須先將表頭內(nèi)容設(shè)置到字段標題上，就是執(zhí)行“將第一行用作標題”。

逆透視操作可以在“轉(zhuǎn)換”選項卡中找到。使用時請注意選擇表格的不同字段時，對應(yīng)不同的逆透視操作。

完成逆透視操作后，表格就變成下面的格式，產(chǎn)品類別信息都集中到了“屬性”字段，我們可以適當修改字段標題，這樣標準的一維表就得到了，我們可以順利的使用這個數(shù)據(jù)源完成透視表設(shè)計。