當前位置:首頁 > 科技 > 正文

Excel數據分析篇:使用Power Query對數據進行逆透視

在Excel中,我們可以對一份原始數據通過數據透視表進行深入的分析處理,從而生成所需的數據報告,反之,如果有數據已經是“透視”(pivoted)的,如果對其進行反向的操作呢?Power Query編輯器中便有這樣一個功能來幫助我們。

在上一期中,我們通過Power Query編輯器對數據進行了一系列的轉換,在Excel中得到如下的數據表格,不過此數據表格的最後5列是關于評分的數據,對于我們做進一步的數據分析還是有困難的,因其當前的格式為“透視”的情況,而我們所要用來分析的數據并非此種格式,故需要我們對其進行轉換,使得當前作為列标簽的“5、4、3、2、1”等在同一列中。

通常情況下,我們的原始數據可能都是如下圖中所示的,月份的時間都是在同一列中;為了更好地查看相關數據,可能會對此月份的數據進行透視,以便對比每個月的數據。

回到上面Evaluations數據表中,我們所要做的就是将後面5列進行“逆透視”(unpivot),以獲得相關數據,便于我們分析數據。

雙擊“Evals”查詢,進入Power Query編輯器。首先我們需要選擇所要逆透視的列,或者選擇不需逆透視的列。比如,我們在此例中要逆透視的是最後5列,可以選擇後5列,再點擊“逆透視列”的第一個功能選項;或者選擇前3列(無需逆透視),再點擊第二個功能選項“逆透視其他列”。

逆透視操作後,得到如下新的數據表格。

在M代碼編輯欄更改“屬性”标簽為“Rating”,将“值”列删除。

“關閉并上載”以上更新的數據查詢到Excel工作表中,我們即可獲得更新後的數據表格。

對此更新的數據表格,我們進一步做數據分析:點擊Evaluation Report工作表,在其C4單元格上,插入一個數據透視表,數據源即為以上更新的數據表Evals。

将“Question”字段拖到行區間,“Rating”字段拖至值區間,默認“Rating”顯示的是計數項,鼠标右鍵更改其“值彙總方式”為“平均值”,結果返回一個DIV錯誤。

經初步分析判斷,出現錯誤的原因應該是“Rating”列的數據類型非數字型,回到其對應的數據查詢中,發現“Rating”列的數據的确是“文本”,所以我們将其改為“整數”。

再次“關閉并上載”此查詢,回到Excel的Evaluation Report工作表中,刷新插入的數據透視表後,不再出現錯誤。

調整“Rating”的數字格式,保留小數點後兩位即可。

對行标簽“Question”進行篩選,僅顯示“包含”trainer的數據。

在“數據透視圖分析”選項卡下,添加篩選後的數據透視表對應的數據透視圖。

簡單調整一下透視圖的格式與設計:不顯示“圖例”和“字段按鈕”,添加合适的圖表标題,選擇合适的樣式等。

完善此圖的标題,點擊B1單元格,在編輯欄中,後加上一個連接符“&”以及Evaluations工作表中A4單元格的文本數據,相應的課程名。

至此,我們完成了一個從數據處理到制作圖表的這樣一個數據分析過程,那問題來了,以上使用的查詢,能否重複利用,即隻需更改數據來源,通過同樣一系列查詢的步驟,一步到位即可完成數據分析過程,并生成所需的數據圖表?答案是肯定的。

再次回到Evals查詢中,點擊其步驟的第一步“Source”,即可有不同的方式來更改數據的來源:第一是編輯M代碼,第二是“主頁”選項卡下的“數據源設置”,第三十點擊“Source”右側的設置按鈕,然後選擇數據源即可。

你可能想看:

有話要說...

取消
掃碼支持 支付碼