☰

在 Excel 中计算值对

tag-icon • tag-icon microsoft-excel microsoft-excel-2010
在 Excel 中计算值对

我在 Excel 中有一组数据，其中每行（除其他外）有两个值，我想要生成所有配对的排序列表。

示例数据：

Val1  Val2
------------
A     B
B     A
C     A
B     C
A     B
C     A
etc..

示例输出：

Val1  Val2  Count
------------------
A     B     2
B     A     1
B     C     1
C     A     2

有没有办法不编写 VBA 代码来做到这一点。

编辑：我应该补充一点，我的数据集正在增长，我更喜欢一种能够在输入新数据时自动更新的解决方案。

答案1

COUNTIF()最简单的方法是将两列连接在一起，对该列执行操作，最后删除所有重复的行。

示例截图

答案2

假设：第 1 行是标题行，第 2 行是第一行数据（A B）。

脚步

  1. 在 D2: 输入=A2 & "|||" & B2并复制到 D3:D999
    （使用一些罕见的字符串，如"|||"）

  2. 在 C2: 输入=COUNTIF(D:D, D2)并复制到 C3:C999

  3. 复制 C:C 列，然后“选择性粘贴”作为值到 C:C 列 ( Alt-E > S > V > Enter)

  4. 选择 A:D 列，然后按 D 列排序

  5. 在 E2: 输入=IF(OR(ROW(D2)=2, C2=1, AND(C2>1,D2<>D1)), "Keep", "Delete")并复制到 E3:E999

  6. 打开自动筛选 ( Alt-D > F > F)

  7. 在E列下拉列表中选择“删除”

  8. 选择第 2 行：999，然后右键单击 > 删除
    （有些#REF!可能显示在 E 列，这是正常的）

  9. 关闭自动筛选 ( Alt-D > F > F)

  10. 删除虚拟列 D:E

答案3

使用countifs宏。

原始表格：

      A     B
    Val1  Val2
    ------------
 1  A     B
 2  B     A
 3  C     A
 4  B     C
 5  A     B
 6  C     A
    etc...

在这种情况下，宏是：

=COUNTIFS(A1:A6, A1, B1:B6, B1)

其内容为“如果在范围 A1:A6 中有 A，并且如果在范围 B1:B6 中有 B，则计算它”。

这将导致类似这样的结果（COUNTIFS 在 G 列中）：

      E    F     G
    Val1  Val2  Count
    ------------------
 1  A     B     2
 2  B     A     1
 3  B     C     1
 4  C     A     2

可以使范围绝对化：

=COUNTIFS($A$1:$A$6, A1, $B$1:$B$6, B1)

或者使用真值表（或密钥），这样它就COUNTIFS不是自指的。

      H    I    
    Keys1 Keys2 
    ------------
 1  A     A     
 2  B     B     
 3  C     C

这将有助于创建一个从原始数据集收集数据但与参考表进行比较的 countif，如下所示：

=COUNTIFS($A$1:$A$6, H1, $B$1:$B$6, I1)

所以这是同样的事情，但现在表上的数据可以更新和排序而不会变得COUNTIFS不准确。

相关内容