excel - 引用一个数字生成单元格中 "(x,y)"数据的个数

Question

(例如:1=(x1,y1), 3=(x1,y1,x2,y2,x3,y3)

我如何删除如下所示的不必要的“(，)”并引用可靠性失败标题下的数字放置可靠性失败的x，y坐标的位置数？
例如:设备 WLR8~LW~VBD~MNW 中的可靠性故障计数 =2 应该给我该故障计数在与 columnX 的设备相同的行的位置。无论如何，请忽略我图片中 V 和 W 列下的数据。

当前输出基于我的代码


我真正想要的


目前的问题


当前问题2

它应该在哪里

Dim output As Variant
Dim outputrow As Integer
output = ""
outputrow = 0
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets(1)

Dim ia As Long
Dim lastrow2 As Long
lastrow2 = ws1.Range("U2:U" & ws1.Rows.Count).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

If ws1.Range("U1") = "Reliability Fail" Then
 For ia = 2 To lastrow2
    If ws1.Cells(ia, "U").Value = 0 Then
    output = output & "(" & ws1.Cells(ia, "Y").Value & "," & ws1.Cells(ia, "Z").Value & "),"
    ElseIf output = "(,)," Then 'if there are no x and y values in Y and Z column stop showing "(,),"
    output = ""
    End If
    If ws1.Cells(ia, "U").Value > 0 Then
    ws1.Cells(ia, "U").Offset(0, 3).Value = Left(output, Len(output) - 1) 'extract the x and y values obtain in (x,y) format
                    'if there is "value" under reliability fails(column U), put the x y position at the same row as the "value" at column X
    End If
    Next
End If

Answer 1

我建议使用内部循环，这样一开始就不会添加额外的括号

Option Explicit

Sub test()


Dim output As Variant
Dim outputrow As Integer
Dim valueCount As Long, ib As Long
output = ""
outputrow = 0
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets(1)

Dim ia As Long
Dim lastrow2 As Long
lastrow2 = ws1.Range("U2:U" & ws1.Rows.Count).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

If ws1.Range("U1") = "Reliability Fail" Then

' Outer loop over all rows
 For ia = 2 To lastrow2
  valueCount = ws1.Cells(ia, "U").Value
  output = ""

  ' Inner loop to process repeated rows
  For ib = 1 To valueCount
    output = output & "(" & ws1.Cells(ia + ib - 1, "Y").Value & "," & ws1.Cells(ia + ib - 1, "Z").Value & ")"
    If ib < valueCount Then output = output & ","
   Next ib
   ws1.Cells(ia, "U").Offset(0, 3).Value = output
 Next ia
End If

End Sub

编辑

以下是根据 OP 后面的示例修改后的代码:

Option Explicit

Sub test()

Dim output As Variant
Dim outputrow As Integer
Dim valueCount As Long, ib As Long, rowPointer As Long
output = ""
outputrow = 0
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets(1)

Dim ia As Long
Dim lastrow2 As Long
lastrow2 = ws1.Range("U2:U" & ws1.Rows.Count).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

If ws1.Range("U1") = "Reliability Fail" Then

rowPointer = 2
' Outer loop over all rows
 For ia = 2 To lastrow2
  valueCount = ws1.Cells(ia, "U").Value
  output = ""

  ' Inner loop to process repeated rows
  For ib = 1 To valueCount
    output = output & "(" & ws1.Cells(rowPointer, "Y").Value & "," & ws1.Cells(rowPointer, "Z").Value & ")"
    If ib < valueCount Then output = output & ","
    rowPointer = rowPointer + 1
  Next ib
  ws1.Cells(ia, "U").Offset(0, 3).Value = output
 Next ia
End If

End Sub