☰

結合された行で列化されたデータを再構築する

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 行あります。これを患者ごとに 1 行に結合します。

たとえば、上記のデータは次のようになります。

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を選択し、データ > 重複を削除 > OK
  4. これで、患者ごとに固有の行が作成されます（下の緑色の領域を参照）。

以下のスクリーンショットを使用して、シートの残りの部分を作成します。

  1. 灰色の領域は動的ヘッダーです。右側に 9 回コピー/貼り付けすると、1 から 10 まで自動的に更新されます。
  2. 白い領域には、2 つの数式があります。1 つは「値」を取得するための数式、もう 1 つは「測定日」を取得するための数式です。右側にコピー/貼り付けすることもでき、自動的に更新されます。

ここに画像の説明を入力してください

以下がそれです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 マクロになると思います。

関連情報