同一數據對應不同行？

要實現這個【數據轉置】一般都有好多種方法。

先看下面的數據源和要求。

方法一：萬金油公式index+small+if,一對多查找。

公式如下：數組公式，輸入公式后需同時按CTRL+SHIFT+ENTER三鍵才能夠正常顯示結果。

=IFERROR(INDEX($B$1:$B$8,SMALL(IF($A$1:$A$8=$D2,ROW($1:$8),9^9),COLUMN(A1))),"")

此公式的不好的地方：函數公式本來就效率相對低，數組公式效率更低。所以要視乎數據量和實際情況來使用。

方法二：函數輔助列（好處是效率相對方法一較高，也較靈活）

輔助列公式如下：

=A2&"金額"&COUNTIF($A$2:A2,A2)

使用輔助列就可以簡化查詢的公式，提升效率。

查詢公式如下：

=IFERROR(INDEX($B$1:$B$8,MATCH($E2&F$1,$C$1:$C$8,0)),"")

方法三：輔助列+透視表方法

插入透視表，輔助列公式略有更改，如下

="金額"&COUNTIF($A$2:A2,A2)

拖拉字段，輔助列拖至列，項目拖至行，金額拖至值。

這樣就完成了數據的轉置。

普通透視表的這個方法也有一個辦不到的地方。

當金額為文本的時候，就無法處理。如下圖。

方法四：PowerQuery方法

以金額為文本為例子，標題字段就不改了，畢竟方法一樣。

將數據上傳到powerquery編輯器，數據--從表格

選中輔助列，轉換--透視列

設置如下：

結果：

假如沒有輔助列，數據是這樣的。可以如下面這樣處理

同樣將數據上傳到powerquery編輯器

轉換--分組--統計，設置如下：

點擊確定（因為是對文本求和，所以必然是返回錯誤的）

此時，函數公式為：

= Table.Group(更改的類型, {"項目"}, {{"計數", each List.Sum([金額]), type text}})

將函數公式改為：

= Table.Group(更改的類型, {"項目"}, {{"計數", each Text.Combine([金額],","), type text}})

使用Text.Combine函數合并同類項，結果為：

然后執行拆分列操作，按逗號分列成--列

結果：

powerquery的方法相對來說是較好的。

方法五：VBA方法。

VBA方法無往而不利，數組配合字典轉置一下就OK，但這里就不再提供代碼了。

以上方法僅供參考。

歡迎關注套路Excel