如何在Excel或SPSS中为每个案例插入相同数量的测量值，缺失测量日期填充为‘NA’？

你遇到的这个问题其实很常见——当患者的测量日期不连续时，不管是要保持长表结构还是转成宽表，核心都是先补全每个患者的所有目标日期记录，缺失的分数填NA就行。我分别给你说下Excel和SPSS的实操方案，都是经过验证的高效方法：

一、Excel操作方法 #

方法1：用Power Query（Get & Transform）批量处理（推荐，适合大数据量） #

这是最省心的方式，能自动生成每个患者的完整日期记录：

• 先整理好原始长表，确保有患者ID、测量日期、治疗分数三列，日期列要设为标准日期格式
• 生成目标日期列表：比如你需要至少14天，就用SEQUENCE函数生成连续14天的日期，或者取原始数据中最早到最晚的所有日期
• 进入Power Query：选中原始表 → 「数据」选项卡 → 「从表格/区域」，打开编辑器
• 提取唯一患者ID：选中患者ID列 → 「转换」→ 「去重」，得到所有不重复的患者ID
• 交叉连接生成完整框架：把唯一患者ID表和日期列表表做交叉连接（「主页」→「合并查询」→「交叉连接」），这样每个患者都会对应所有目标日期
• 匹配原始分数：把交叉连接后的表和原始表做合并查询，匹配条件选「患者ID」+「测量日期」，加载治疗分数列，没匹配到的会显示null
• 替换null为NA：选中治疗分数列 →「转换」→「替换值」，把null换成NA，最后点击「关闭并上载」就能得到补全后的长表
• 转宽表：用数据透视表，行选患者ID，列选测量日期，值选治疗分数，缺失的单元格自动显示NA

方法2：用公式+辅助列（适合小数据量） #

如果数据不多，用公式也能快速搞定：

• 先手动或用INDEX+SEQUENCE生成「患者ID+所有目标日期」的基础网格（比如A列重复患者ID，B列是每个患者对应的14天日期）
• 在C列写匹配公式，用XLOOKUP更直观：

  =IFERROR(XLOOKUP([@患者ID]&[@测量日期], 原始表!$A:$A&原始表!$B:$B, 原始表!$C:$C, "NA"), "NA")
  

  （旧版Excel可以用VLOOKUP嵌套IFERROR，逻辑是一样的）
• 下拉公式就能得到所有补全后的记录

二、SPSS操作方法 #

SPSS用语法处理这类批量补全需求更灵活，分两种场景：

场景1：先补全长表，再转宽表 #

这种方式能先确保每个患者的所有日期都有记录，再转成宽表：

• 第一步：生成包含所有患者ID和目标日期的临时数据集，比如需要14天记录的循环生成语法：

  * 生成所有患者ID+14天日期的临时文件.
  DATA LIST FREE / patient_id (F8) measure_date (DATE10).
  BEGIN DATA
  * 假设患者ID是1-1000，日期从2024-01-01到2024-01-14.
  DO REPEAT pid=1 TO 1000 / dt='01-JAN-2024' TO '14-JAN-2024' BY 1 DAY.
  WRITE OUTFILE='C:/temp/all_dates.sav' / pid dt.
  END REPEAT.
  EXECUTE.
  

• 第二步：匹配原始数据和全日期数据集，补全缺失分数：

  * 按患者ID和日期匹配两个数据集.
  MATCH FILES FILE='C:/temp/all_dates.sav'
    / TABLE='你的原始数据.sav'
    / BY patient_id measure_date.
  EXECUTE.
  
  * 把系统缺失值替换成NA字符串.
  RECODE therapy_score (SYSMIS='NA').
  EXECUTE.
  

• 第三步：转宽表，用CASESTOVARS命令：

  CASESTOVARS /ID=patient_id /INDEX=measure_date /GROUPBY=INDEX.
  EXECUTE.
  

场景2：直接从长表转宽表并补全缺失日期 #

如果不需要中间长表，也可以直接转宽表时指定所有目标日期，确保缺失日期的列也会出现：

* 先定义所有需要的日期（按你的实际日期修改）.
DEFINE !target_dates () '01-JAN-2024' '02-JAN-2024' ... '14-JAN-2024' !ENDDEFINE.

* 转宽表，指定所有日期为列，缺失值填充NA.
CASESTOVARS
  /ID=patient_id
  /INDEX=measure_date (!target_dates)
  /GROUPBY=INDEX
  /MISSING=NA.
EXECUTE.

不管用哪种工具，核心思路都是先构建「患者+所有目标日期」的完整记录框架，再把原始测量数据匹配进去，缺失的就填充NA，这样转宽表后每个日期列和患者的对应关系就不会出错啦。

备注：内容来源于stack exchange，提问作者Maarten