您好，我需要根據行是否為空或具有任何值來連接列。
即我有這樣的資料
我需要這樣的資料
問題是我要加入的列將超過 50 列，對于每列我必須檢查該列和行中是否有空白或數字。
你能幫我解決這個問題嗎？
uj5u.com熱心網友回復：
如果您要求 VBA 解決方案，請嘗試下一個代碼。它將處理盡可能多Value的列，并在最后一列之后的兩列回傳處理結果。使用陣列，即使對于大范圍，代碼也會非常快：
Sub ConcatenateWithValues()
Dim sh As Worksheet, lastR As Long, lastCol As Long, arr, arrH, arrFin
Dim strConc As String, i As Long, j As Long
Set sh = ActiveSheet 'use here the sheet you need
lastR = sh.Range("A" & sh.Rows.count).End(xlUp).row 'last row on column A:A
lastCol = sh.cells(1, sh.Columns.count).End(xlToLeft).Column 'last column on the first row
arr = sh.Range("A1", sh.cells(lastR, lastCol)).Value 'place the whole range in an array, for faster iteration
arrH = sh.Range("A1", sh.cells(1, lastCol)).Value 'Place headers in an array
ReDim arrFin(1 To UBound(arr), 1 To 2) 'Redim the array to contain the processing result
arrFin(1, 1) = arrH(1, 1): arrFin(1, 2) = "Concatenate_Value"'Place the headers in the array
For i = 2 To UBound(arr)
For j = 2 To lastCol 'build the necessary concatenation
If arr(i, j) <> "" Then strConc = strConc & arrH(1, j) & ":" & arr(i, j) & ";"
Next
arrFin(i, 1) = arr(i, 1): arrFin(i, 2) = left(strConc, Len(strConc) - 1) 'remove the last ";" character
strConc = "" 'reinitialize the string keeping row concatenation
Next i
'Drop the processed array content at once:
sh.cells(1, lastCol 2).Resize(UBound(arrFin), 2).Value = arrFin
End Sub
uj5u.com熱心網友回復：
使用Microsoft-365，您可以嘗試-
=TEXTJOIN("; ",TRUE,FILTER(IF($B$2:$D$5<>"",$B$1:$D$1&":"&$B$2:$D$5,""),$A$2:$A$5=F2))
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/415382.html
標籤：