如何在Excel中基于双属性条件自动统计关联出现次数？

针对你每行3个属性的数据集，要统计任意两属性共同出现的频次来制作热力图，这里提供三个实用的自动化方案，无需手动计数：

一、Excel公式方案（零编程，快速实现） #

适合数据量不大的场景，直接在统计表格中用通用公式自动计算：
假设你的原始数据在D2:F100（三列，共99行数据），统计表格的行标题（A列）和列标题（第1行）是所有16个属性。在统计表格的第一个数据单元格（比如B2）输入以下公式：

=SUMPRODUCT(((($A2=$D$2:$D$100)+($A2=$E$2:$E$100)+($A2=$F$2:$F$100))>0)*((($B$1=$D$2:$D$100)+($B$1=$E$2:$E$100)+($B$1=$F$2:$F$100))>0)*($A2<>$B$1))

公式说明：

• ($A2=$D$2:$D$100)+($A2=$E$2:$E$100)+($A2=$F$2:$F$100))>0：判断行标题属性是否出现在当前数据行的任意一列
• ($B$1=$D$2:$D$100)+($B$1=$E$2:$E$100)+($B$1=$F$2:$F$100))>0：判断列标题属性是否出现在当前数据行的任意一列
• $A2<>$B$1：排除属性与自身的无效统计

输入完成后，拖动公式填充整个统计表格即可得到所有属性对的共现频次，之后直接基于这个表格插入热力图。

二、Power Query方案（批量处理，适合大数据） #

如果数据量较大，Excel公式计算较慢，用Power Query可以高效完成批量统计：

1. 选中原始数据区域，点击「数据」选项卡→「从表格/区域」导入Power Query
2. 添加自定义列：在「添加列」选项卡点击「自定义列」，输入公式：

   List.Combinations(List.RemoveNulls({[列1],[列2],[列3]}), 2)
   

   这个公式会把每行的3个属性生成所有两两组合（自动忽略空值）
3. 展开组合列：点击自定义列右侧的展开按钮，选择「展开到新行」
4. 分组计数：选中展开后的组合列，点击「转换」选项卡→「分组依据」，设置「新列名」为「频次」，「操作」为「行数」
5. 拆分组合列：选中组合列，点击「转换」选项卡→「拆分列」→「按分隔符」，选择逗号（或组合的分隔符），拆分为两列，命名为「属性1」和「属性2」
6. 生成透视表：点击「转换」选项卡→「透视列」，设置「值列」为「频次」，「列」为「属性2」，「值聚合函数」为「求和」
7. 加载回Excel：点击「关闭并上载」，将结果导入工作表，之后可以用公式填充对称位置的空白（比如B3=C2），再生成热力图。

三、Python脚本方案（灵活扩展，适合复杂场景） #

如果需要更灵活的处理（比如自动画热力图），用Python的pandas库可以快速实现：

import pandas as pd
from itertools import combinations

# 读取原始数据（假设是CSV格式，三列名为col1、col2、col3）
df = pd.read_csv("your_data.csv")

# 遍历每行，生成所有有效属性的两两组合
all_pairs = []
for _, row in df.iterrows():
    # 提取非空属性
    valid_attrs = [attr for attr in [row['col1'], row['col2'], row['col3']] if pd.notna(attr)]
    # 生成两两组合并加入列表
    all_pairs.extend(combinations(valid_attrs, 2))

# 统计每个属性对的频次
pair_counts = pd.Series(all_pairs).value_counts().reset_index()
pair_counts.columns = ["属性对", "频次"]

# 拆分属性对为单独列
pair_counts[["属性1", "属性2"]] = pd.DataFrame(pair_counts["属性对"].tolist(), index=pair_counts.index)

# 转换为热力图所需的透视表，补充对称值
heatmap_df = pair_counts.pivot(index="属性1", columns="属性2", values="频次").fillna(0)
heatmap_df = heatmap_df.add(heatmap_df.T, fill_value=0)

# 保存统计结果到CSV（可导入Excel用）
heatmap_df.to_csv("属性共现频次表.csv")

# 直接生成热力图（可选）
import seaborn as sns
import matplotlib.pyplot as plt

plt.figure(figsize=(12, 10))
sns.heatmap(heatmap_df, annot=True, cmap="YlGnBu", fmt="g")
plt.title("属性共现频次热力图")
plt.show()

运行脚本后，会得到可直接用于热力图的透视表，甚至能自动生成可视化图。

内容的提问来源于stack exchange，提问作者llewmihs