編按：

Hello everyone~我是愛生活愛Excel的小E。如何將數(shù)據(jù)進(jìn)行行列轉(zhuǎn)置？用數(shù)據(jù)透視表、用函數(shù)公式？相信各位小伙伴有不少答案。當(dāng)我們的數(shù)據(jù)體量過大或者是單組別數(shù)據(jù)，我們會選擇Power Query中的“透視列”功能進(jìn)行行列轉(zhuǎn)置，將一維數(shù)據(jù)轉(zhuǎn)換成二維數(shù)據(jù)，具體如何操作？跟小E一起來學(xué)習(xí)一下叭。

有小伙伴問了這樣一個問題：我有一組員工請假日期的數(shù)據(jù)，按行依次記錄，只有姓名這一列可以作為分組依據(jù)，如何將這樣的數(shù)據(jù)由列轉(zhuǎn)置到行，用每一行記錄一名員工的請假情況？

大家先來看一下數(shù)據(jù)。如下圖所示，A列中的數(shù)據(jù)為員工姓名，B列中的數(shù)據(jù)為員工的請假日期，數(shù)據(jù)是縱向排列的，也就是我們常說的一維數(shù)據(jù)。

對于將數(shù)據(jù)由列轉(zhuǎn)置到行這類問題，在PQ中常用“透視列”的功能來實(shí)現(xiàn)?？梢哉f，“透視列”功能是將一維數(shù)據(jù)轉(zhuǎn)換為二維數(shù)據(jù)一大利器。

對于一維轉(zhuǎn)二維這種操作，在數(shù)據(jù)的結(jié)構(gòu)方面，至少需要兩個分組依據(jù)以及一列數(shù)據(jù)值，即一般至少需要三列數(shù)據(jù)。這位小伙伴的數(shù)據(jù)，數(shù)據(jù)值是有的，沒什么問題。但在組別方面，卻只有姓名這一個組別，還缺少一個組別。這可就和常見的“透視列”所需要的數(shù)據(jù)不太一樣了。對于這樣的數(shù)據(jù)，該如何實(shí)現(xiàn)轉(zhuǎn)置呢？今天，我們一起來學(xué)習(xí)一下解決這種問題的方法。

解決問題的思路：先添加一個用來標(biāo)識每名員工請假次數(shù)的輔助列，然后再到PQ中對這個輔助列進(jìn)行“透視列”操作。

一、 添加用來標(biāo)識員工請假次數(shù)的輔助列

首先，我們在C列增加一項(xiàng)“請假次數(shù)”，并在C2單元格中輸入函數(shù)公式=COUNTIF($A$2:A2,A2)，并將公式下拉復(fù)制到C13，得到的結(jié)果如下圖所示。

公式解析：這是COUNIF函數(shù)的一個經(jīng)典應(yīng)用——計(jì)算組內(nèi)序號。在這里，我們就是利用COUNTIF函數(shù)生成一個輔助列，用來標(biāo)識每名員工請假的次數(shù)序號?？梢钥吹?，張三的請假次數(shù)是從1開始，至2結(jié)束，共2次；李四的請假次數(shù)是從1開始，至3結(jié)束，共3次；其余以此類推。每名員工的請假次數(shù)均從1開始，直到該員最后一次的請假次數(shù)為止。所以，我們現(xiàn)在來理解一下“組內(nèi)序號”的含義——所謂“組內(nèi)序號”的“組”，在本例中指的就是員工姓名，即以員工姓名來分組；所謂“序號”，就是該員工請假的次數(shù)，自1開始遞增，直到該員工最后一次請假的次數(shù)為止，與其他員工的請假次數(shù)無關(guān)。

由于剛才寫的COUNTIF函數(shù)只顯示組內(nèi)序號的數(shù)字，看起來不太好理解，下面我們將公式稍微調(diào)整一下，在數(shù)字前面加上“第”，在數(shù)字后面加上“次”，讓它變得更容易理解。我們將C2中的公式改為="第"&COUNTIF($A$2:A2,A2)&"次"，得到的結(jié)果如下圖所示。

以員工張三為例，可以看到，請假次數(shù)由“1”、“2”變成了“第1次”、“第2次”，這樣是不是更好理解了呢？

添加完這個輔助列，接下來我們要做的，就是去PQ中進(jìn)行“透視列”操作啦！

二、 利用PQ的“透視列”功能轉(zhuǎn)置數(shù)據(jù)

點(diǎn)擊A1:C13區(qū)域中的任意一個單元格，例如B2，然后依次點(diǎn)擊【數(shù)據(jù)】-【來自表格/區(qū)域】，彈出“創(chuàng)建表”對話框，勾選“表包含標(biāo)題”，如下圖所示。

點(diǎn)擊“確定”之后，就可以進(jìn)入到PQ編輯器界面，如下圖所示。

單擊“請假次數(shù)”字段，即可選中“請假次數(shù)”這一列，此時(shí)該列變成綠色（請注意：本步驟很關(guān)鍵，想要對哪一列進(jìn)行透視，則務(wù)必先單擊選中哪一列）。

接下來，依次點(diǎn)擊【轉(zhuǎn)換】-【透視列】，彈出“透視列”對話框，如下圖所示。

在“透視列”對話框中，點(diǎn)擊“值列”下方的下拉菜單，將參數(shù)選擇為“請假日期”；

點(diǎn)擊“高級選項(xiàng)”前方的三角形圖標(biāo)，展開“高級選項(xiàng)”，點(diǎn)擊“聚合值函數(shù)” 下方的下拉菜單，將參數(shù)選擇為“不要聚合”。如下圖所示：

點(diǎn)擊“確定”之后，得到的結(jié)果如下圖所示：

我們可以看到，“姓名”這一列中的數(shù)據(jù)已經(jīng)變成了每個員工的姓名，每個員工的請假日期已經(jīng)被轉(zhuǎn)置到不同的列中。

請小伙伴們注意觀察一下：

原始數(shù)據(jù)中“請假日期”這個字段已經(jīng)消失，字段中的值(即具體的請假日期)，被放置在員工姓名和次數(shù)相交叉的單元格中：原本縱向排列的“請假次數(shù)”，現(xiàn)在變成了各列的標(biāo)識字段，共四列，分別為“第1次”、“第2次”、“第3次”、“第4次”；

同時(shí)，若員工未請假，則數(shù)據(jù)顯示為“null”。例如，對于員工王五來說，由于他請了兩次假，所以在第4行中，“第1次”、“第2次”字段中存放的就是他的兩次請假日期，而“第3次”、“第4次”兩列中的請假日期對應(yīng)的數(shù)據(jù)均為“null”。

由于此時(shí)日期顯示的是日期+時(shí)間的格式，所以，我們再對數(shù)據(jù)進(jìn)行一下微調(diào)，將數(shù)據(jù)顯示為日期格式。依次點(diǎn)擊“第1次”、“第2次”、“第3次”、“第4次”前面的數(shù)據(jù)類型圖標(biāo)（即由日歷和鐘表構(gòu)成的那個圖標(biāo)，它的意思是數(shù)據(jù)類型為日期+時(shí)間），在彈出的下拉列表中，將數(shù)據(jù)格式選擇為“日期”。如下圖所示：

整理后的數(shù)據(jù)如下圖所示。

注：也可以在進(jìn)入PQ編輯器之后，第一步就將“請假日期”的數(shù)據(jù)類型改成日期格式，這樣可以避免剛才我們進(jìn)行的4次修改數(shù)據(jù)類型的工作。本例之所以這樣演示，就是為了讓小伙伴多多熟悉PQ的操作哦！感興趣的小伙伴們，可以在看完本文之后，嘗試一下這種操作方法。

對于“null”值，我們不需要對其進(jìn)行修改，因?yàn)楫?dāng)PQ中的數(shù)據(jù)上載至Excel中之后，它就會自動地顯示為空值了。

接下來，我們依次點(diǎn)擊【主頁】-【關(guān)閉并上載】-【關(guān)閉并上載】，即可將數(shù)據(jù)上載至Excel中，最終的數(shù)據(jù)結(jié)果如下圖所示。

親愛的小伙伴，你學(xué)會了嗎?

本文配套的練習(xí)課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù)，非得用VBA才能實(shí)現(xiàn)嗎？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

4種刪除excel重復(fù)值的小妙招，速收藏

數(shù)據(jù)源表格太雜亂？試一試這個無敵的數(shù)據(jù)清洗神器——PQ

版權(quán)申明：

本文作者阿碩；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。