表格里如何操作提取指定位置满足条件的数据

68654611

表格里如何操作提取指定位置满足条件的数据




用公式或者筛选的方式都可以，只要能实现把同一行里重复的数据自动提取出来就行

jians0529

=IFERROR(INDEX(B2:D2,MATCH(2,COUNTIF(B2:D2,B2:D2),-1)),"")  提取1
=IF(E2="","",INDEX(B2:D2,MATCH(1,COUNTIF(B2:D2,B2:D2),-1)))  提取2
数组公式下拉

臧礼莉

提取1：
E3=IFERROR(MODE(B3:D3),"")

Lgxxxx

jians0529 发表于 2021-9-7 19:28
=IFERROR(INDEX(B2:D2,MATCH(2,COUNTIF(B2:D2,B2:D2),-1)),"")  提取1
=IF(E2="","",INDEX(B2:D2,MATCH( ...

=IFERROR(INDEX(B2:D2,MATCH(2,COUNTIF(B2:D2,B2:D2),-1)),"")

用这个20行的5没出来。

68654611

臧礼莉 发表于 2021-9-8 09:24
提取1：
E3=IFERROR(MODE(B3:D3),"")

用您提供的公式提取1已经解决，谢谢大神！    能不能帮忙看下提取2怎么解决？

68654611

jians0529 发表于 2021-9-7 19:28
=IFERROR(INDEX(B2:D2,MATCH(2,COUNTIF(B2:D2,B2:D2),-1)),"")  提取1
=IF(E2="","",INDEX(B2:D2,MATCH( ...

大佬，提取2提取不出来呢

Lee0509

Sub test()
  Dim arr, i, j, k, n
  arr = [a1:g3].Value
  ReDim brr(1 To UBound(arr, 1), 1 To UBound(arr, 2))
  For i = 1 To UBound(arr, 1)
  n = 0
  For j = 1 To UBound(arr, 2)
  For k = 1 To j - 1
  If arr(i, j) = arr(i, k) Then Exit For
  Next
  If k = j Then
   n = n + 1: brr(i, n) = arr(i, j)
   End If
  Next
  Next
  [a5].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
End Sub

Lee0509

1. Sub test()
   
2.   Dim arr, i, j, k, n
   
3.   arr = [a1:g3].Value
   
4.   ReDim brr(1 To UBound(arr, 1), 1 To UBound(arr, 2))
   
5.   For i = 1 To UBound(arr, 1)
   
6.   n = 0
   
7.   For j = 1 To UBound(arr, 2)
   
8.   For k = 1 To j - 1
   
9.   If arr(i, j) = arr(i, k) Then Exit For
   
10.   Next
    
11.   If k = j Then
    
12.    n = n + 1: brr(i, n) = arr(i, j)
    
13.    End If
    
14.   Next
    
15.   Next
    
16.   [a5].Resize(UBound(brr, 1), UBound(brr, 2)) = brr
    
17. End Sub

复制代码

Lee0509