根据2列中的特定值在excel中选择一行， [英] Selecting a row in excel based on specific values in 2 columns,

问题描述

数据大致以格式

  ABC 
 ID1 ID2 0.5 
 ID1 ID3 0.7 
 ID2 ID3 0.9 
  

我想创建一个相关矩阵（列C是ID之间的相关性A和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 &&code> col b = ID2 ）。我怀疑它可以通过连接来完成，但我不确定这是一个很好的解决方案？ Match / Vlookup等只返回第一场比赛[在那列]，对我来说是不好的。 Ode到'where'子句我猜？

我的搜索没有显示任何可用的帮助，我已经计算了相关性，并将其从SQL中提取出来。所以是的，任何想法都是超级的，一个枢纽表是最后的手段。

谢谢。

假设您的源数据范围在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）
  

，然后在整个范围内拖放此公式。

Data roughly in the format

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

I want to create a correlation matrix (column C being the correlation between the IDs in A and B). It can definitely be done with a pivot table, though I have to use sum which could be risky if a duplicate existed since an error might not be apparent. Output format would be:

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

(the '1' is easily done with an =IF(B$2=$A3,1,0) and replacing 0 with the formula to find the correlation)

I basically want a match (col a= ID1 && col b = ID2). I suspect it could be done by concatenation, but I am not sure that is a great solution? Match/Vlookup etc only return the first match [in that column], which is no good to me. Ode to a 'where' clause I guess?

My searches did not reveal any usuable help, I have already calculated the correlation and am putting it into excel from SQL. So yeah, any ideas would be super, a pivot table being a last resort.

Thanks.

解决方案

Assuming your source data range is on Sheet1, from A1 to C3 and your results range is on Sheet2, from A1 to D4.

You can put this formula on B2:

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

and then, drag and drop this formula on the whole range.

这篇关于根据2列中的特定值在excel中选择一行，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！