#excel #match
#excel #сопоставление
Вопрос:
Я работаю с набором данных, который для целей этого вопроса имеет значения 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
Как вы можете видеть, для меня не имеет значения, какое значение находится в столбце, мне просто нужен номер столбца, в котором находится определенное значение. Я хочу сделать это, если в строке несколько значений.
Пожалуйста, дайте мне знать, есть ли формула, которая может это сделать, и если я даже на правильном пути, чтобы заставить функцию СОПОСТАВЛЕНИЯ работать. Заранее благодарю вас!
Ответ №1:
Вы были бы против использования 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
Комментарии:
1. Это работает почти идеально! Не совсем знаком с VBA, поэтому я могу неправильно понимать, что делает часть вашего кода. Вопрос: Когда у вас есть: [С листом2 .Диапазон («M1»).Значение = «-1 позиция» . Диапазон («M» и i). Значение = OneArray . Диапазон («N1»). Значение = «1 позиция» . Диапазон («N» и i). Значение = twoArray] Похоже, это предназначено для добавления в другой столбец, что было бы идеально. Вместо этого эти значения разделяются запятой в одной ячейке. Это не худший случай, и я могу с этим работать, но есть ли способ изменить этот код, чтобы вместо этого добавить новые ячейки?
2. На самом деле, все в порядке, я думаю, я просто вручную разделю результаты запятой! Ты спаситель! Большое вам спасибо!