在管理學(xué)生成績的實踐中，成績的原始數(shù)據(jù)往往是按照學(xué)生的條目來記錄的(圖1)。如果需要按學(xué)號、姓名、科目、年級等轉(zhuǎn)換成表格效果。數(shù)據(jù)量大，一個一個復(fù)制粘貼的操作顯然相當(dāng)繁瑣?？梢岳肊xcel的Microsoft Power Query插件實現(xiàn)快速轉(zhuǎn)換(Excel 2016已經(jīng)包含了Microsoft Power Query組件，可以在“數(shù)據(jù)”選項卡中直接調(diào)用，其他版本需要在微軟http://dwz.cn/2J93Jh官方復(fù)制網(wǎng)站下載安裝)。
1. 數(shù)據(jù)轉(zhuǎn)換為表
打開數(shù)據(jù)表，切換到“數(shù)據(jù)”選項卡，在“獲取和轉(zhuǎn)換”功能組單擊“從表格”按鈕，在隨后彈出的對話框根據(jù)提示選擇數(shù)據(jù)源，Excel會自動將選定區(qū)域轉(zhuǎn)換為表，并打開查詢編輯器界面。
2. 提取學(xué)科數(shù)據(jù)
在“表1-查詢編輯器”窗口選擇學(xué)號、姓名兩列的列標(biāo)，切換到“轉(zhuǎn)換”選項卡，在“任意列”功能組依次選擇“逆透視列→逆透視其他列”，執(zhí)行后可以將當(dāng)前選定列轉(zhuǎn)換為“屬性/值”對，并與每行中的剩余值相結(jié)合，顯示效果如圖2所示(圖2)。
可以看到“屬性”列包含了語文、數(shù)學(xué)、英語等多門學(xué)科的名稱，我們首先需要將這些學(xué)科的名稱從“屬性”列提取出來。選擇“屬性”列的列表，右擊選擇“拆分列→按分隔符”，此時會打開“按分隔符拆分列”對話框，在“選擇或輸入分隔符”列表框選擇“自定義”，輸入“|”進(jìn)行分隔，這里不需要更改其他選項，執(zhí)行之后的效果如圖3所示(圖3)。可以看到原來的“屬性”列已經(jīng)被分隔為屬性1、屬性2兩列，“屬性1”是各門學(xué)科的名稱，“屬性2”則是成績、等第。
由于“屬性2”列仍然混合了成績、等第這兩個內(nèi)容，因此還需要將這些內(nèi)容區(qū)分開來。選擇“屬性2”列，切換到“轉(zhuǎn)換”選項卡，在“任意列”功能組單擊“透視列”按鈕，這一操作是為了使用當(dāng)前選中列中的名稱創(chuàng)建新列，此時會彈出“透視列”對話框，在“值列”下拉列表框選擇“值”，點擊“高級選項”前面的按鈕，在下拉列表框選擇“不要聚合”，最終設(shè)置如圖所示(圖4)。
完成上述設(shè)置之后，原有的“屬性2”列已經(jīng)被轉(zhuǎn)換為成績、等第兩個新的列。如果覺得默認(rèn)的“屬性1”不太合適，可以右擊重命名為“學(xué)科”。關(guān)閉查詢編輯器，此時會提示是否保留更改(圖5)，確認(rèn)之后會返回Excel主界面，此時就可看到最終效果(圖6)。接下來可以根據(jù)對各個項目進(jìn)行適當(dāng)?shù)暮Y選，感興趣的朋友可以一試。
擴(kuò)展閱讀：利用Power Query快速分離混合文本
如圖所示的“DATA”列都是字母、數(shù)字的混合文本(圖7)?，F(xiàn)在要求依次提取純文本、純數(shù)值、不重復(fù)數(shù)值。手工提取顯然是相當(dāng)麻煩，而且也容易出錯，利用Power Query內(nèi)置的函數(shù)可以輕松實現(xiàn)。
在源數(shù)據(jù)選擇“DATA”列的數(shù)據(jù)區(qū)域，切換到“數(shù)據(jù)”選項卡，在“獲取和轉(zhuǎn)換”功能組單擊“從表格”按鈕，此時會打開查詢編輯器，切換到“添加列”選項卡，單擊“常規(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)。