借助Power Query快速轉(zhuǎn)換數(shù)據(jù)

王志軍

1. 數(shù)據(jù)轉(zhuǎn)換為表

打開數(shù)據(jù)表，切換到“數(shù)據(jù)”選項(xiàng)卡，在“獲取和轉(zhuǎn)換”功能組單擊“從表格”按鈕，在隨后彈出的對話框中根據(jù)提示選擇數(shù)據(jù)源，Excel會自動將選定區(qū)域轉(zhuǎn)換為表，并打開查詢編輯器界面。

2. 提取學(xué)科數(shù)據(jù)

在“表1-查詢編輯器”窗口選擇學(xué)號、姓名兩列的列標(biāo)，切換到“轉(zhuǎn)換”選項(xiàng)卡，在“任意列”功能組依次選擇“逆透視列→逆透視其他列”，執(zhí)行后可以將當(dāng)前選定列轉(zhuǎn)換為“屬性/值”對，并與每行中的剩余值相結(jié)合，顯示效果如圖所示（圖2）。

可以看到“屬性”列包含了語文、數(shù)學(xué)、英語等多門學(xué)科的名稱，我們首先需要將這些學(xué)科的名稱從“屬性”列提取出來。選擇“屬性”列的列表，右擊選擇“拆分列→按分隔符”，此時會打開“按分隔符拆分列”對話框，在“選擇或輸入分隔符”列表框選擇“自定義”，輸入“|”進(jìn)行分隔，這里不需要更改其他選項(xiàng)，執(zhí)行之后的效果如圖所示（圖3）?？梢钥吹皆瓉淼摹皩傩浴绷幸呀?jīng)被分隔為屬性1、屬性2兩列，“屬性1”是各門學(xué)科的名稱，“屬性2”則是成績、等第。

由于“屬性2”列仍然混合了成績、等第這兩個內(nèi)容，因此還需要將這些內(nèi)容區(qū)分開來。選擇“屬性2”列，切換到“轉(zhuǎn)換”選項(xiàng)卡，在“任意列”功能組單擊“透視列”按鈕，這一操作是為了使用當(dāng)前所選列中的名稱創(chuàng)建新列，此時會彈出“透視列”對話框，在“值列”下拉列表框選擇“值”，點(diǎn)擊“高級選項(xiàng)”前面的按鈕，在下拉列表框選擇“不要聚合”，最終設(shè)置如圖所示（圖4）。

完成上述設(shè)置之后，原有的“屬性2”列已經(jīng)被轉(zhuǎn)換為成績、等第兩個新的列。如果覺得默認(rèn)的“屬性1”不太合適，可以右擊重命名為“學(xué)科”。關(guān)閉查詢編輯器，此時會提示是否保留更改（圖5），確認(rèn)之后會返回Excel主界面，此時就可看到最終效果（圖6）。接下來可以根據(jù)對各個項(xiàng)目進(jìn)行適當(dāng)?shù)暮Y選，感興趣的朋友可以一試。

如圖所示的“DATA”列都是字母、數(shù)字的混合文本（圖7），現(xiàn)在要求依次提取純文本、純數(shù)值、不重復(fù)數(shù)值。手工提取顯然是相當(dāng)麻煩，而且也容易出錯，利用Power Query內(nèi)置的函數(shù)可以輕松實(shí)現(xiàn)。

在源數(shù)據(jù)選擇“DATA”列的數(shù)據(jù)區(qū)域，切換到“數(shù)據(jù)”選項(xiàng)卡，在“獲取和轉(zhuǎn)換”功能組單擊“從表格”按鈕，此時會打開查詢編輯器，切換到“添加列”選項(xiàng)卡，單擊“常規(guī)”功能組的“添加自定義列”按鈕，此時會彈出“添加自定義列”對話框，首先將默認(rèn)的列名“Custom”修改為“純文本”，接下來在下面的“自定義列公式”窗格輸入“= Text.Trim（Text.Remove（[DATA]，{"0".."9"}），" "）”，檢查無誤之后單擊“確定”按鈕，很快就可以在新添加的自定義列顯示從“DATA”列提取出來的純文本內(nèi)容，按照同樣的方法繼續(xù)添加“純數(shù)值”、“不重復(fù)數(shù)值”兩個自定義列，公式分別為“=Text.Remove（Text.Trim（Text.Remove（[DATA]，{"A".."z"}），" "），" "）”和“=Text.Combine（List.Distinct（Text.ToList（[純數(shù)值]）））”，請注意后一個公式的“純數(shù)值”必須與上一個自定義列的名稱保持一致，否則會提示“Error”。

關(guān)閉查詢編輯器，在提示是否保留更改時，請選擇“保留”，返回Excel主界面之后，就可以看到分離效果（圖8）。