Блог

Я работаю с набором данных, который для целей этого вопроса имеет значения 0, 1 и -1. Я хочу запустить две отдельные функции сопоставления. Я хочу найти все столбцы, содержащие единицы, а затем найти все столбцы, содержащие -1. Я хочу, чтобы эти результаты располагались рядом друг с другом в новых ячейках по горизонтали. Я успешно использую функцию сопоставления, но только тогда, когда есть только одно вхождение каждого числа.

Я много искал в Google, но могу найти только случаи, когда люди комбинируют ИНДЕКС СОВПАДЕНИЕ (https://exceljet.net/formula/extract-multiple-matches-into-separate-columns ).

  id   2001  2002  2003  2004  2005  2006  2007  2008 2009  2010  
  1      0     0     0     0     0    -1     0     0    1    0   
  2      0     0     0    -1     0     0     1     0    0    0     
  3      0     0     0    -1     0     0     0     1    0    0  
  4      0    -1     0     0     0     0     1     0    0    0   
  5      0     0     0     1     0     0    -1     1    0    0 

# Match function:

= MATCH(-1, B2:B11,0)
= MATCH(1, B2:B11,0)

# This would output, for example: 

 id   2001  2002  2003  2004  2005  2006  2007  2008 2009  2010  neg1.pos1 
  1      0     0     0     0     0    -1     0     0    1    0     6
1.pos1
  9

# But this doesn't work if there is more than one instance of -1 or 1, it just gives the index of the first column that contains either number.
  

Я хочу:

  id   2001  2002  2003  2004  2005  2006  2007  2008 2009  2010  neg1.pos1 
5      0     0     0     1     0     0    -1     1    0    0      7
1.pos1 1.pos2
   4     8
  

Как вы можете видеть, для меня не имеет значения, какое значение находится в столбце, мне просто нужен номер столбца, в котором находится определенное значение. Я хочу сделать это, если в строке несколько значений.

Пожалуйста, дайте мне знать, есть ли формула, которая может это сделать, и если я даже на правильном пути, чтобы заставить функцию СОПОСТАВЛЕНИЯ работать. Заранее благодарю вас!

Вы были бы против использования VBA? Я не уверен в способе визуализации всех значений в формуле массива, но я могу придумать способ перебора каждой строки и создания массива для заполнения каждым столбцом #.

Я делаю несколько предположений:

столбец «id» находится в столбце A

заголовки находятся в строке 1

Будет переменная # строк

Не будет переменной # столбцов

 Sub ColCheck()

Dim ColNum, rCount, i As Long
Dim OneArray, TwoArray As Variant

rCount = Sheet2.Range("A" amp; Rows.Count).End(xlUp).Row

i = 2

Do While i <= rCount

OneArray = ""
TwoArray = ""

For Each cell In Sheet2.Range("B" amp; i amp; ":K" amp; i)
    If cell.Value = -1 And OneArray = "" Then
        OneArray = cell.Column
    ElseIf cell.Value = -1 And OneArray <> "" Then
        OneArray = OneArray amp; ", " amp; cell.Column
    End If
Next


For Each cell In Sheet2.Range("B" amp; i amp; ":K" amp; i)
    If cell.Value = 1 And TwoArray = "" Then
        TwoArray = cell.Column
    ElseIf cell.Value = 1 And TwoArray <> "" Then
        TwoArray = TwoArray amp; ", " amp; cell.Column
    End If
Next

If OneArray = "" Then OneArray = "No value found"
If TwoArray = "" Then TwoArray = "No value found"

With Sheet2
    .Range("M1").Value = "-1 Position"
    .Range("M" amp; i).Value = OneArray
    .Range("N1").Value = "1 Position"
    .Range("N" amp; i).Value = TwoArray
End With

i = i   1


Loop

End Sub