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

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, нажмите « Данные»> «Удалить дубликаты»> «Ok».
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.