尾数相同数据提取前三个

sanpiao

尾数相同数据提取前三个

望帝春心

高版本

1. =TAKE(FILTER(B4:U4,RIGHT(B4:U4)=AI4&""),,3)

复制代码

低版本，数组

1. =IFERROR(INDEX($B4:$U4,SMALL((RIGHT($B4:$U4)<>$AI4&"")/1%%+COLUMN($A:$T),COLUMN(A1))),"")

复制代码

hcm19522

{=IFERROR(INDEX(4:4,SMALL(IF(RIGHT($B4:$U4)-$AI4=0,COLUMN($B:$U)),COLUMN(A1))),AN4)

心正意诚身修

1. Sub 数据提取()
   
2. Dim oldarr, newarr(), i As Integer, j As Integer, k As Integer
   
3. Dim c As Byte, s As Integer, x As Byte
   
4. j = Range("b4").End(xlDown).Row
   
5. oldarr = Range("b4:ai" & j)
   
6. i = UBound(oldarr)
   
7. ReDim newarr(1 To 100, 1 To 3)
   
8. For k = 1 To i
   
9.     s = s + 1
   
10.     x = 1
    
11.     For c = 1 To 20
    
12.       If Right(oldarr(k, c), 1) = Format(oldarr(k, 34), "0") Then
    
13.             
    
14.             newarr(s, x) = oldarr(k, c)
    
15.             x = x + 1
    
16.             Do While x = 4
    
17.                 GoTo 1
    
18.             Loop
    
19.      End If
    
20.      If x = 3 Then
    
21.         newarr(s, 3) = newarr(s, 2)
    
22.     End If
    
23.     Next
    
24. 1
    
25. Next
    
26. Range("ao4").Resize(s, 3) = newarr
    
27. End Sub
    

复制代码

又一年没做过练习了。

ssg365

=LET(a,FILTER(B4:U4,RIGHT(B4:U4)-AI4=0),TAKE(HSTACK(a,TAKE(a,,-1)),,3))

cabcyvr

AO4=IFERROR(SMALL(IF(RIGHT($B4:$U4,1)/1=$AI4,$B4:$U4,"/"),COLUMN(A1)),AN4)  数组