請問EXCEL列出清單的方法

excel

ccenjor 2018-12-20 18:49:43 ‧ 5834 瀏覽

有個EXCEL資料類似圖片左邊，是X/Y方式的對照表
請問要用什麼公式，才能把對照表中有V的項目轉換成右邊的清單(清單A或是清單B都可以)
PS.這是某個EXCEL表格的一部分，資料會一直異動增減，需要用公式的方式讓清單能保持自動更新

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

來杯拿鐵

iT邦新手 2 級 ‧ 2018-12-21 19:28:18

最佳解答

公式放在同一欄很可怕，所以分二欄取列標題和欄標題。

輸入完公式後，請按CTRL+SHIRFT+ENTER。
因為這是陣列公式，不可以用ENTER。

H1=OFFSET($A$3,SMALL(IF($B$4:$E$11="v",ROW($B$4:$E$11),""),ROW($H1))-3,0)
I4=OFFSET($A$3,0,MOD(SMALL(IF($B$4:$E$11="v",ROW($B$4:$E$11)*100+COLUMN($B$4:$E$11),""),ROW($H1))-1,100))

使用方法
填入公式後下拉自動填滿，切記長度要夠高。
另外，這個公式只支援表格寬度100以下，超過的話要修改參數。
晚上九點前我會在線上，歡迎詢問。

回應
分享
檢舉

登入發表回應

jasonh

iT邦新手 5 級 ‧ 2018-12-21 10:01:58

用VBA試試
Sub 巨集1()
k = 0
RowCnt = Cells(65535, 1).End(xlUp).Row - 1
ColCnt = Cells(1, 255).End(xlToLeft).Column - 1
For i = 1 To RowCnt
For j = 1 To ColCnt
If Cells(i + 1, j + 1) <> "" Then
k = k + 1
Cells(k + 3, 9) = Cells(i + 1, 1) & "-" & Cells(1, j + 1)
Cells(k + 3, 12) = Cells(i + 1, 1)
Cells(k + 3, 13) = Cells(1, j + 1)
End If
Next
Next
End Sub