Excel技术问询：基于患者诊断数据集构建二进制标记列

方法一：Power Query（适合大批量数据） #

• 选中包含姓名和诊断列的数据区域，点击「数据」选项卡→「从表格/区域」（勾选“我的表格有标题”），进入Power Query编辑器
• 选中诊断列，点击「转换」选项卡→「拆分列」→「按分隔符」，选择实际使用的分隔符（如逗号、分号），在高级选项中选择「拆分为行」
• 选中拆分后的诊断列，点击「主页」选项卡→「删除重复项」，得到所有唯一诊断条目，可将这些值复制到新工作表作为表头（第一行保留「Name」列）
• 返回原数据的Power Query编辑器，选中「姓名」和拆分后的诊断列，点击「转换」选项卡→「透视列」，在弹出窗口中：
  • 值列选择「诊断」
  • 值聚合方式选择「计数」
• 此时没有诊断的单元格会显示null，选中所有诊断列，点击「转换」选项卡→「替换值」，查找值填null，替换值填0，完成后点击「关闭并上载」，即可得到目标格式的数据

方法二：公式法（适合小批量数据） #

假设原始数据：A列为姓名，B列为合并的诊断信息（用逗号分隔）

1. 提取所有唯一诊断作为表头
   • 先拆分所有诊断到行：在C2单元格输入公式=TRIM(TEXTSPLIT(B2,","))，下拉填充到所有行
   • 在空白单元格（比如E1）输入公式=UNIQUE(FILTER(C:C,C:C<>""))，得到所有唯一诊断，将这些值复制到第一行（B1、C1、D1...位置，A1保留「Name」）
2. 标记0/1
   • 在B2单元格输入公式=--ISNUMBER(SEARCH(B$1,$A2))，按下回车后，向右、向下填充所有单元格。公式说明：
     • SEARCH(B$1,$A2)检查表头的诊断是否出现在当前行的诊断单元格中
     • ISNUMBER返回布尔值，--将TRUE转换为1，FALSE转换为0

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