找出同一天交易了多少次帐户的公式 [英] Formula to find out how many times accounts transacted on the same day

问题描述

我正在寻找一种方法来找出哪些帐户在同一天交易最多.

I am looking for a way to find out which accounts appear to transact on the same days together the most.

我已经研究过使用相关函数，但是决定这将无法提供我想要的结果，因为我需要能够在6000多个不同的acct#之间执行此操作.因此，我决定尝试通过创建帐户#的矩阵并找出每个帐户与另一个帐户在同一天交易了多少次来解决此问题，但是如果有人拥有一个帐户，我愿意解决该问题的其他想法更好的主意.

I have looked into using the correlation function, but decided this would not provide the results I am looking for as I would need to be able to do this among the 6000+ different acct #s. Therefore, I have decided to try to solve this by creating a matrix of the account #s and finding out how many times each account transacted on the same day as another account, but I am open to other ideas of solving this if anyone has a better idea.

我的源数据是一个由2列组成的大型数据集-日期(A列)和Acct#(B列).

My source data is a large dataset consisting of 2 columns - Date (Column A) and Acct # (Column B).

我目前正在研究求和积，但是由于我不想查看任何特定日期，而是整个日期范围，所以我没有得到想要的东西.

I am currently looking into sum product, but since I do not want to look at any specific date, rather the date range as a whole, I am not getting what I want.

=sumproduct(('Trxs'!A:A='Trxs'!A:A)*('Trxs'!B:B=A$2)*('Trxs'!B:B=$a2))

我要寻找的是一个公式，我可以使用该公式来遍历acct #s的矩阵，该矩阵将累加同一时间段内在同一日期交易的2个acct #s中的每一个的次数不到一个月的时间，而没有使用特定的日期来解决这个问题.

What I am looking for would be a formula that I could use to pull across a matrix of the acct #s that would add up the number of times each of the 2 acct #s transacted on the same date over the time span of a month and not using a specific date to figure this out by.

1/1/2019    123456
1/1/2019    987654
1/2/2019    987654
1/3/2019    123456
1/3/2019    123456
1/3/2019    987654
1/3/2019    567890

结果将是

       123456   567890    987654
123456   3         1        2
567890   1         1        1
987654   2         1        3

推荐答案

我提出了一个使用帮助程序表的解决方案，该表计算该帐户当天是否有活动:

I've came up with a solution using a helper table that counts if that account had activity that day:

助手表上的单元格F12的公式为:

The formula for the cell F12 on the helper table is:

=+IF(COUNTIFS($B$3:$B$12,$E12,$C$3:$C$12,F$11)>0,1,0)

以及Final Resul表上的公式:

And the formula on the Final Resul table:

我同意Domenic的观点，F4单元格上的公式与IF部分的效果更好:

Edit 2: I agree with Domenic, the formula on cell F4 works better with the IF section:

=IF($E4<>F$3,SUMPRODUCT(INDEX($F$12:$J$14,0,MATCH($E4,$F$11:$J$11)),INDEX($F$12:$J$14,0,MATCH(F$3,$F$11:$J$11))),COUNTIF($C$3:$C$12,$E4))

这篇关于找出同一天交易了多少次帐户的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！