excel - 计算附带条件的不同值

Question

我有一个名为 tableName 的表在这样的excel中:

ColumnA ColumnB
 1111    aaaa
 1111    bbbb
 1111    aaaa
 1111    cccc
 1111    aaaa
 1111    aaaa
 1111    aaaa
 2222    aaaa
 2222    aaaa
 2222    ffff
 2222    aaaa
 2222    aaaa
 3333    bbbb
 3333    bbbb
 4444    aaaa
 4444    bbbb

我已经得到了这个(在单元格 B2 中):

{=SUM(IF((tableName[ColumnB]=B$1)*1;1/COUNTIFS(tableName[ColumnA];tableName[ColumnA];tableName[ColumnB];B$1)))}

与 aaaa在单元格中 B1用于计算 ColumnA 中的不同值在哪里 ColumnB包含 aaaa .这里的结果是 3(1111 和 2222 和 4444)，标记的列被计算在内:

ColumnA ColumnB
 1111    aaaa      <----is counted lets call it 1
 1111    bbbb      <--------not counted because ColumnB isn't aaaa
 1111    aaaa      <----not counted because duplicate of 1
 1111    cccc      <--------not counted because ColumnB isn't aaaa
 1111    aaaa      <----not counted because duplicate of 1
 1111    aaaa      <----not counted because duplicate of 1
 1111    aaaa      <----not counted because duplicate of 1
 2222    aaaa      <----is counted lets call it 2
 2222    aaaa      <----not counted because duplicate of 2
 2222    ffff      <--------not counted because ColumnB isn't aaaa
 2222    aaaa      <----not counted because duplicate of 2
 2222    aaaa      <----not counted because duplicate of 2
 3333    bbbb      <--------not counted because ColumnB isn't aaaa
 3333    bbbb      <--------not counted because ColumnB isn't aaaa
 4444    aaaa      <----is counted lets call it 3
 4444    bbbb      <--------not counted because ColumnB isn't aaaa

现在我有 bbbb在单元格中 A2并且想要计数相同，但 ColumnA中必须有另一行具有相同的值和 A2 (=bbbb) 在 ColumnB .

ColumnA ColumnB
 1111    aaaa      <----is counted because x exists
 1111    bbbb      <--------this is x
 1111    aaaa
 1111    cccc
 1111    aaaa
 1111    aaaa
 1111    aaaa
 2222    aaaa      <----not counted because no pair (2222, bbbb) exists
 2222    aaaa
 2222    ffff
 2222    aaaa
 2222    aaaa
 3333    bbbb
 3333    bbbb
 4444    aaaa      <----is counted because y exists
 4444    bbbb      <--------this is y

我不知道该怎么做。

我的完整目标是创建一个列联表，例如:

     aaaa bbbb cccc ffff
aaaa  3    2    1    1    
bbbb  2    3    1    0
cccc  1    1    1    0
ffff  1    0    0    1

Answer 1

试试这个:

=SUMPRODUCT((TableName[ColumnB]=B1)*  
  (0<COUNTIFS(TableName[ColumnA],TableName[ColumnA],TableName[ColumnB],A2))
/COUNTIFS(TableName[ColumnA],TableName[ColumnA],TableName[ColumnB],TableName[ColumnB]))

p.s.它不是 CSE 公式，不需要 Ctrl+Shift+Enter，尽管它也可以。