我在 Excel 中有一些电子邮件网络数据作为边列表，这意味着我有列 Vertex1、Vertex2，然后是该边的 N 列属性，例如从一个人向另一个人发送了多少封电子邮件。对于数据中的每一行，Vertex1 是消息的来源，Vertex2 是目标，因此边是有向的。以下是一些示例数据
Vertex1 Vertex2 nMessages
Bob Cindy 12
Cindy Bob 3
Bob Mike 11
Cindy Mike 1
我正在尝试计算以下形式的几何平均值
gm = sqrt[(# of edges ij)*(# of edges ji)]
因此 Bob 和 Cindy 的 gm 是 gm = sqrt[(Bob 发送给 Cindy 的消息)*(Cindy 发送给 Bob 的消息)] 或 sqrt(12*3) = 6。有没有办法在 Excel 中将其制作成公式？
答案1
我可以使用辅助列来完成此操作。（使用 Excel 2007 或更高版本，我不需要辅助列，如果您愿意，我可以发布该答案）
假设您的示例数据在 A、B 和 C 中：
在 D2 中输入=A2&B2，然后复制该数据
在 E1 中，将名字输入
到 E2 中，将第二个名字输入
到 E3 中，将公式输入到=SQRT(SUMIF(D2:D5,E1&E2,C2:C5)*SUMIF(D2:D5,E2&E1,C2:C5))
这求和首先按给定的顺序检查姓名，然后累加电子邮件的数量；其次，按相反的顺序检查姓名，然后累加数量。
请注意，这将给出零如果电子邮件只朝一个方向发送，或者双方之间都没有发送电子邮件。
答案2
无辅助列且适用于所有 xl 版本
=GEOMEAN(SUMPRODUCT(--(A1:A4="Bob"),--(B1:B4="Cindy"),C1:C4),SUMPRODUCT(--(A1:A4="Cindy"),--(B1:B4="Bob"),C1:C4))