Реконструировать столбчатые данные с помощью объединенных строк

Question

У меня есть следующие данные

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                                                     

Кто-нибудь знает, как это сделать?

Answer 1

Это можно сделать с помощью вспомогательного столбца и сводной таблицы.

1. вспомогательный столбец: добавьте новый столбец в таблицу с формулой:
   =COUNTIF($A$1:A2,A2)
   это назначит номера для каждой даты измерения для каждого идентификатора пациента

2. Сводная таблица:
   • добавьте постоянные столбцы (которые вам понадобятся только один раз) как «строки»
   • добавить вновь созданный столбец как «столбцы»
   • добавить столбцы для репликации как «значения»
   • убедитесь, что для каждого из них «суммировать поле значений по» установлено значение «сумма» или «среднее»
   • установить правильный числовой формат для дат
   • это работает только для данных, содержащих только числовую информацию (= без текста)

Answer 2

Альтернативное решение, менее элегантное, чем сводная таблица, и, возможно, требующее немного больше работы.

Предположим, ваши текущие данные находятся на листе с именем Old.

1. В Old, убедитесь, что данные отсортированы по идентификатору пациента.
2. Аналогично решению Мате, добавьте вспомогательный столбец с формулой=COUNTIF($A$1:A2;A2)

У вас должно получиться что-то вроде этого:

---

1. Создайте новый лист (например New)
2. Скопируйте столбцы A–D из OldвNew
3. В New, выберите столбцы от A до D, щелкните наДанные > Удалить дубликаты > ОК
4. Теперь у вас должна быть уникальная строка для каждого пациента (см. зеленую область ниже).

Используйте снимок экрана ниже для создания остальной части листа:

1. Серая область — это динамические заголовки. Если вы скопируете/вставите их 9 раз справа, они будут автоматически обновлены с 1 по 10.
2. В белой области есть 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.