Excel如何根據指定列，擴展行資料？

2021-05-17 02:30:58

hello，大家好，我是小E，昨天有讀者提問Excel資料擴展的問題。

如下圖所示，如何根據左邊的資料，獲取右邊的資料？

需求很明確，如何將欄位師資力量分配到每個學院？用Excel如何實現呢？話不多說，今天我們介紹兩種方法實現此需求。

方法一公式法

列師資力量一共有5個欄位，也就是說將師資力量5個欄位填充到每個學院後面就可以了。

先將每個學院複製五行，在E2單元格內輸入公式：

=OFFSET($A$2,(ROW(A2)-2)/5,0)

公式，下拉，可以發現每個學院被複製成了5份（5行）。

接著再將5個師資力量欄位填充到每個學院後面即可。

F2單元格內輸入公式：

=OFFSET($C$2,MOD(ROW(C2)-2,5),0)

公式下拉，完成填充。

這樣，資料便完成了擴展。

這裡的公式也可以替換成：

=INDEX($C$2:$C$6,IF(MOD(ROW(C2)-1,5)=0,5,MOD(ROW(C2)-1,5)))

方法二 Power Query

首先選中師資力量列任意單元格，依次點選【資料】—【來自表格/區域】（16版Excel為自表格）點選確定按鈕，進入power query編輯介面。

進入之後，直接點選左上方的「關閉並上載至」—「僅創建連線」，完成設定。

接著選中學院列任意單元格，點選【資料】—【來自表格/區域】（16版Excel為自表格），點選確定按鈕，進入power query編輯介面。

接著在power query編輯介面點選【新增列】—【自定義列】，自定義列公式中輸入公式：表1[師資力量]，這裡地表1就是剛開始創建的師資力量列內容。

點選確定，之後左鍵自定義列，選擇「擴展到新行」，完成資料擴展。

最後一步，點選關閉並上載，將power query中的資料上載到Excel表格中去，資料處理完畢。

注意：第二種方法只適用於2016及其以上版本（Excel內嵌了power query），如果Excel版本過低，只有手動安裝power query，才能使用此功能。

建議大家安裝2016版本，可以看下我的歷史文章，有詳細的安裝教程。

小結

Excel資料擴展小技巧分享給大家，覺得有用歡迎轉發關注，每天分享實用小技巧。