2D矩阵上的Sumproduct或Countif [英] Sumproduct or Countif on a 2D matrix

问题描述

我正在研究来自过敏人群的数据.每个人都有一个唯一的ExceptionID，每个过敏原都有一个唯一的过敏原ID(总数为451).

I'm working on data from a population of people with allergies. Each person has a unique ExceptionID, and each allergen has a unique AllergenID (451 in total).

我有一个包含2列的数据表(ExceptionID和AllergenID)，其中每个人的过敏症逐行列出.这意味着ExceptionID列对于具有多种过敏的人具有重复的值，而AllergenID列对于具有这种过敏的不同人具有重复的值.

I have a data table with 2 columns (ExceptionID and AllergenID), where each person's allergies are listed row by row. This means that the ExceptionID column has repeated values for people with multiple allergies, and the AllergenID column has repeated values for the different people who have that allergy.

我正在尝试计算此人群中每对过敏症出现的次数(例如，Allergen#107& Allergen#108，Allergen#107& Allergen#109等).为简单起见，我创建了一个451行X 451列的矩阵，代表每对(实际上是A/B和B/A等价的两倍).

I am trying to count how many times each pair of allergies is present in this population (e.g. Allergen#107 & Allergen#108, Allergen#107 & Allergen#109,etc). To keep it simple I've created a matrix of 451 rows X 451 columns, representing every pair (twice actually because A/B and B/A are equivalent).

我不知何故需要使用行名(allergenID)在我的数据表中查找ExceptionID，并计算与列名(也包括AllergenID)中的ExceptionID相匹配的情况.我使用Vlookup或Index/Match没问题，但是我正在为查找和Sumproduct或Countif公式的正确组合而苦苦挣扎.

I somehow need to use the row name (allergenID) to lookup the ExceptionID in my data table, and count the cases where that matches the ExceptionIDs from the column name (also AllergenID). I have no problem using Vlookup or Index/Match, but I'm struggling with the correct combination of a lookup and Sumproduct or Countif formula.

任何帮助将不胜感激！

迈克 PS我正在使用Excel 2016(如果发生任何更改).

Mike PS I'm using Excel 2016 if that changes anything.

-=更新=- 因此Dirk和MacroMarc建议的方法都可以工作，尽管我无法将后者应用于我的整个数据集(超过17,000行)，因为这花费了很长时间.

-=UPDATE=- So the methods suggested by Dirk and MacroMarc both worked, though I couldn't apply the latter to my full data set (17,000+ rows) because it was taking a long time.

此后，我决定将其转换为VBA宏，因为我们现在要查看三元组的数量，而不是成对的数量.

I've since decided to turn this into a VBA macro because we now want to see the counts of triplets instead of pairs.

推荐答案

以开始的2列为好，这是不可能的...您将需要检查每个ExceptionID以具有2个不同的特定AllergenID.最好使用辅助表，将ExceptionID作为行，将AllergenID作为列(或者相反，随便...).帮助器表需要一个类似以下的公式:

With the 2 columns you start with, it is as good as impossible... You would need to check every ExceptionID to have 2 different specific AllergenID. Better use a helper-table with ExceptionID as rows and AllergenID as columns (or the opposite... whatever you like). The helper table needs a formula like:

=COUNTIFS($A:$A,$D2,$B:$B,E$1)

然后可以自动填充. (范围是从我的示例中得出的，您需要将其更改为自己的需求).

Which then can be auto-filled. (The ranges are from my example, you need to change them to your needs).

使用此辅助矩阵，您可以轻松地选择更大的矩阵，如下所示:

With this helper-matrix you can easily go for your bigger matrix like this:

=COUNTIFS(E:E,1,INDEX($E:$G,,MATCH($I2,$E$1:$G$1,0)),1)

同样，您可以自动填写此公式，但是您需要对其进行更改，以使其适合您的需求.
因为这些列具有相同的ID2(将是您的AllergenID)，所以无需查找它们，因为E:E随着自动填充而自动更改.

Again, you can auto-fill with this formula, but you need to change it, so it fits your needs.
Because the columns have the same ID2 (would be your AllergenID), there is no need to lookup them because E:E changes automatically with the auto-fill.

公式中最重要的部分是$，不应弄乱它，否则您将无法自动填充它.

Most important part of the formulas are the $ which should not be messed up, or you can not auto-fill it.

我的自制示例图片(公式来自每个表格的左上角单元格):

Picture of my self-made example (formulas are from the upper left cell in each table):

如果您还有任何疑问，请问:)

If you still have any questions, just ask :)

这篇关于2D矩阵上的Sumproduct或Countif的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！