☰

使用合并的行重建列数据

tag-icon • tag-icon microsoft-excel
使用合并的行重建列数据

我有以下数据

patient-id  last name   first name  date of birth   value   date of measurement
A1          A           BC          1900-01-01      1       1900-01-03 0:00
A1          A           BC          1900-01-01      2       1900-01-04 0:00
A1          A           BC          1900-01-01      3       1900-01-03 0:00
A1          A           BC          1900-01-01      2       1900-01-02 0:00
A1          A           BC          1900-01-01      1       1900-01-05 0:00
D5          D           EF          1900-01-02      4       1900-01-04 0:00
D5          D           EF          1900-01-02      5       1900-01-03 0:00
D5          D           EF          1900-01-02      4       1900-01-02 0:00
F2          G           HI          1900-01-03      6       1900-01-04 0:00
F2          G           HI          1900-01-03      5       1900-01-01 0:00
F2          G           HI          1900-01-03      6       1900-01-05 0:00

在此处输入图片描述

每个病人最多有 10 行。我想将其合并为每个病人一行。

例如上面的数据变成：

patient-id  last name   first name  date of birth   value1  date of measurement1    value2  date of measurement2    value3  date of measurement3    value4  date of measurement4    value5  date of measurement5    value6  date of measurement6    value7  date of measurement7    value8  date of measurement8    value9  date of measurement9    value10 date of measurement10
A1          A           BC          1900-01-01      1       1900-01-03 0:00 2   1900-01-04 0:00 3   1900-01-03 0:00 2   1900-01-02 0:00 1   1900-01-05 0:00                                     
D5          D           EF          1900-01-02      4       1900-01-04 0:00 5   1900-01-03 0:00 4   1900-01-02 0:00                                                     
F2          G           HI          1900-01-03      6       1900-01-04 0:00 5   1900-01-01 0:00 6   1900-01-05 0:00

在此处输入图片描述

有人知道怎么做吗？

答案1

您可以使用辅助列和数据透视表来完成此操作

  1. 辅助列：在表中添加一个带有公式的新列：
    =COUNTIF($A$1:A2,A2)
    这将为每个患者 ID 的每个测量日期分配数字。

在此处输入图片描述

  1. 数据透视表：
    • 添加常量列（只需要一次）作为“行”
    • 将新创建的列添加为“列”
    • 添加要复制为“值”的列
    • 确保每个“按值字段汇总”都设置为“总和”或“平均值”
    • 设置正确的日期数字格式
    • 这仅适用于仅包含数字信息（=无文本）的数据

在此处输入图片描述

答案2

另一种解决方案，可能不如数据透视表优雅，并且需要做更多的工作。

假设您当前的数据位于名为 的表中Old。

  1. 在Old，确保数据按患者 ID 排序
  2. 与 Máté 的解决方案类似，添加一个辅助列，公式如下=COUNTIF($A$1:A2;A2)

你应该有类似这样的内容：

在此处输入图片描述

  1. 创建新工作表（例如New）
  2. 将 A 至 D 列复制Old到New
  3. 在 中New，选择 A 至 D 列，点击数据 > 删除重复项 > 确定
  4. 现在，每个患者应该都有一条唯一的线路（见下方的绿色区域）

使用下面的屏幕截图来构建工作表的其余部分：

  1. 灰色区域是动态标题。如果您在右侧复制/粘贴它们 9 次，它们将自动更新，从 1 到 10。
  2. 白色区域有两个公式：一个用于获取“值”，一个用于获取“测量日期”。您也可以在右侧复制/粘贴它们，它们会自动更新。

在此处输入图片描述

这里有SUMIFS：

=SUMIFS(Old!$E:$E,Old!$G:$G,E$2,Old!$A:$A,$A4)              
=SUMIFS(Old!$F:$F,Old!$G:$G,F$2,Old!$A:$A,$A4)

笔记：这也仅适用于数值。对于非数值，每个测量值和患者都有自己的独特值，我相信唯一的解决方案是 VBA 宏。

相关内容