Выбор строки в excel на основе определенных значений в 2 столбцах,

Данные примерно в формате

A B C
ID1 ID2 0.5
ID1 ID3 0.7
ID2 ID3 0.9

Я хочу создать корреляционную матрицу (столбец C является корреляцией между идентификаторами в и B). Это может быть сделано с помощью сводной таблицы, хотя я должен использовать сумму, которая может быть рискованной, если существует дубликат, поскольку ошибка может быть не очевидной. Формат вывода:

ID1 ID2 ID3
ID1 1 .5 .7
ID2 .5 1 .9
ID3 .7 .9 1

("1" легко выполняется с =IF(B$2=$A3,1,0) и заменяя 0 на формулу, чтобы найти корреляцию)

Я в основном хочу совпадение (col a= ID1 && col b = ID2). Я подозреваю, что это можно сделать путем конкатенации, но я не уверен, что это отличное решение? Match/Vlookup и т.д. Возвращают только первое совпадение [в этом столбце], что мне нехорошо. Ода, где я предполагаю?

Мои поисковые запросы не выявили никакой полезной помощи, я уже вычислил корреляцию и помещаю ее в excel от SQL. Так что да, любые идеи будут супер, а сводная таблица - последнее средство.

Благодарю.

2 ответа

Предполагая, что ваш исходный диапазон данных находится на Sheet1, от A1 до C3, а диапазон результатов находится на Sheet2, от A1 до D4.

Вы можете поместить эту формулу в B2:

=SUMPRODUCT((Sheet1!$A$1:$A$3=Sheet2!B$1)*(Sheet1!$B$1:$B$3=Sheet2!$A2)*Sheet1!$C$1:$C$3)

а затем перетащите эту формулу на весь диапазон.


Почему бы вам не создать третий столбец, который объединяет значения из столбцов A и B используя =A1&B1 а затем vlookup для этого значения:

A B C D
ID1 ID2 ID1ID2 0.5
ID1 ID3 ID1ID3 0.7
ID2 ID3 ID2ID3 0.9

licensed under cc by-sa 3.0 with attribution.