求一个列的序列函数

qe12541254

求一个列的序列函数，如附件，行转列真就个人脑袋转不动了，辛苦大神帮忙看看

solextrade

=INDEX('常温（原版）'!$A:$E,COLUMN(A1),ROW(A1))&""

qe12541254

solextrade 发表于 2024-5-13 12:55
=INDEX('常温（原版）'!$A:$E,COLUMN(A1),ROW(A1))&""

大神不好意思我没表达清楚，可以否做一个直接引用Sheet1的，不引用原版那个页签的。

dambcer

Sub test()
    Dim arr, s, i, ii, j, t, n, m
    arr = Sheet1.Range("a2:d" & Sheet1.Cells(65536, 1).End(3).Row)
    For j = 3 To 4
        For i = 1 To UBound(arr)
            m = 0
            If arr(i, j) > 0 Then
                n = n + 1
                t = IIf(j = 3, "常温", "温控")
                For ii = 1 To arr(i, j)
                    s = arr(i, 1) & arr(i, 2) & t & arr(i, j) & "-" & ii
                    m = m + 1
                    Sheet3.Cells(m, n) = s
                Next
            End If
        Next
    Next
    MsgBox "ok"
End Sub

bsgn2017

1. =SUM(IFERROR(FIND($A2&$B2,'常温（原版）'!$A$1:$E$5),))

复制代码

1. =SUM(IFERROR(FIND($A2&$B2,温控!$A$1:$E$5),))

复制代码

fsw0812

=IFERROR(INDEX($B$2:$B$13&$C$2:$C$13,SMALL(IF($C$2:$C$13<>"",ROW($1:$12)),COLUMN(A$1)))&"-"&1/(1/MOD(MIN(ROW(A1),INDEX($C$2:$C$13,SMALL(IF($C$2:$C$13<>"",ROW($1:$12)),COLUMN(A$1)))+1),INDEX($C$2:$C$13,SMALL(IF($C$2:$C$13<>"",ROW($1:$12)),COLUMN(A$1)))+1)),"")数组

mykitami

=LET(_a,IFNA(REDUCE("",B2:B13,LAMBDA(x,y,HSTACK(x,IF(OFFSET(y,,1)<>"",OFFSET(y,,-1)&y&LEFT(C$1,2)&OFFSET(y,,1)&"-"&SEQUENCE(OFFSET(y,,1)),"")))),""),FILTER(_a,TAKE(_a,1)<>""))

swl4561255

1. Sub test()
   
2. ar = [A1].End(4).Row
   
3. arr = Range("A2:D" & ar)
   
4. Dim brr(1 To 100, 1 To 100)
   
5. Dim crr(1 To 100, 1 To 100)
   
6. For i = 1 To ar - 1
   
7.     If arr(i, 3) <> "" Then
   
8.         n = n + 1
   
9.         For j = 1 To arr(i, 3)
   
10.             brr(j, n) = arr(i, 1) & arr(i, 2) & Left(Cells(1, 3), 2) & arr(i, 3) & "-" & j
    
11.         Next
    
12.     Else
    
13.         m = m + 1
    
14.         For k = 1 To arr(i, 4)
    
15.             crr(k, m) = arr(i, 1) & arr(i, 2) & Left(Cells(1, 4), 2) & arr(i, 4) & "-" & k
    
16.         Next
    
17.     End If
    
18. Next
    
19. Sheets(Left(Cells(1, 3), 2)).Range("A1").Resize(UBound(brr), UBound(brr, 2)) = brr
    
20. Sheets(Left(Cells(1, 4), 2)).Range("A1").Resize(UBound(crr), UBound(crr, 2)) = crr
    
21. End Sub

复制代码

boyuanoffice

1. =DROP(REDUCE("",C2:C13,LAMBDA(x,y,IF(y<>"",VSTACK(x,CONCAT(OFFSET(y,,-COLUMN(B1)),OFFSET(y,,-COLUMN(A1)),LEFT(C1,2),y,"-")&SEQUENCE(y)),x))),1)

复制代码

右拉

rym9401

1. =IFNA(DROP(REDUCE("",FILTER(A2:A13&B2:B13&":"&C2:C13,C2:C13<>""),LAMBDA(x,y,HSTACK(x,TEXTBEFORE(y,":")&LEFT(C1,2)&TEXTAFTER(y,":")&"-"&SEQUENCE(TEXTAFTER(y,":"))))),,1),"")

复制代码