Как автоматически заполнить комбинацию обратной пары в Excel
У меня есть электронная таблица, которая выглядит так:
В настоящее время я вручную ввожу свои значения «Y» и «N» в столбец «Значение» на основе исследований. Однако, чтобы сэкономить время, я бы хотел, чтобы значение для начальной пары заполнялось автоматически для ее обратного спаривания. Например, я присвоил паре яблоко-апельсин значение «Y». Я хотел бы автоматически заполнить пару апельсина и яблока с тем же значением «Y». Я думал о сопоставлении индексов, но он не учитывает комбинации пар. Любые мысли будут оценены.
Ответы 2
Я не знаю о формуле, но вот решение VBA - вы даже можете превратить это в событие Worksheet_Change, которое будет постоянно обновлять значения, если вы внесли изменения в сам столбец.
Как сказал Марк, у вас не должно быть столбца со смесью значений и формул (особенно если он считывается сам по себе, тогда вы столкнетесь с проблемой рекурсия).
Option Explicit
Sub PopulateValue()
Dim i As Long, j As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 3).Value = "" Then
'Look for a true match
For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If i <> j Then
If Cells(i, 1).Value = Cells(j, 1).Value And _
Cells(i, 2).Value = Cells(j, 2).Value Then
If Cells(j, 3).Value <> "" Then
Cells(i, 3).Value = Cells(j, 3).Value
Exit For
End If
End If
End If
Next j
'Look for a reverse match
For j = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If i <> j Then
If Cells(i, 1).Value = Cells(j, 2).Value And _
Cells(i, 2).Value = Cells(j, 2).Value Then
If Cells(j, 3).Value <> "" Then
Cells(i, 3).Value = Cells(j, 3).Value
Exit For
End If
End If
End If
Next j
End If
Next i
End Sub
Вот формула рабочего листа. Предполагая, что заголовок в строке 1, данные начинаются в строке 2, а Entity1, Entity2 и Value в столбцах A, B и C соответственно, поместите
=IFERROR(INDEX(C$2:C2,MATCH(B3&"$"&A3,A$2:A2&"$"&B$2:B2,0)),"")
в C3 как формулу массива (CTRL-SHIFT-ENTER) и заполнить.
Объяснение
Формула делает индекс/соответствие, как вы считали. Для поисковой части совпадения Entity2 и Entity объединяются в одну строку. Существует необязательный разделитель $ между двумя двумя объектами (поясняется ниже). Для части массива совпадают Entity1 и Entity2, соединенные одним и тем же разделителем. Массив начинается с первой строки данных и заканчивается строкой над текущей строкой.
Если совпадение возвращает индекс, он подключается к INDEX вместе с массивом значений до текущего. Если совпадение возвращает ошибку #VALUE, оно перехватывается обёрткой в функцию IFERROR.
Разделитель Рассмотрим две пары Entity1 и Entity2, первая пара (пики, пик), а вторая (щука, говор). Объединение любого из них без разделителя дает "пикепик" и может привести к ошибочному совпадению. Разделитель защищает от этого. Вы можете выбрать любую строку, которая не встречается в ваших данных, в качестве разделителя.
надеюсь, это поможет
Спасибо за это — я ввожу формулу массива в столбец C, однако, когда я ввожу в C новое значение, например «Y» или «N», формула стирается и не совпадает с ее обратной парой. Может я что-то не так делаю.
@thedatasleuth. После ввода формулы начните с верхней части списка и вручную введите «Y» или «N». Все обратные совпадения ниже будут вычисляться как «Y» или «N». Перейдите к следующему пустому месту и вручную введите другое «Y» или «N». Рассчитываются дополнительные совпадения ниже. Продолжайте движение вниз по списку, вручную обновляя пробелы и пропуская рассчитанные ячейки.
Дело в том, что если вы поместите статическое значение в столбец
value, вы перезапишете формулы, которые будут вычислять пары. Вам, вероятно, где-то понадобится справочная таблица, но вы не указали, 1) это вариант или 2) есть ли шаблон для ваших ref-данных.