エクセルで全体データから特定IDのデータのみを抽出

研究をやっていると、非常に大きなデータセットの中の一部だけを取り出してサブ解析をしたいことがあります。そういう時に、そのサブグループかどうかを示す変数があり、それをソートするだけで取り出すことができるようになっていれば一番楽なのですが、時に、サブグループについて全体での通しIDだけしか分かっておらず、そこから抽出しなければならないことがあります。数例だけならIDを検索してコピペすれば済む話ですが、2000や3000以上の症例数のデータセットから100や200のランダムなIDの症例を引っ張ってこようと思うと骨が折れます。

そこで、エクセル上で、IDだけを入力すれば、あとは自動でそのIDのデータを引っ張ってきてくれるような表を作ってしまいます。



大まかな流れ:膨大なデータから必要な日付のデータだけを抽出する


今回はmatch関数とindex関数を用いて、
0015_1.jpg
のような日付ごとの歩行距離、階段を上った階数、歩数の一覧表から、特定の日付のデータのみを抽出することを考えます。

目標は、
0015_2.jpg
のH2セルに日付を入力すると、上の表から自動的にデータを拾ってきて、
0015_3.jpg
となるようにすることです。

大まかな流れは、
  1. match関数で知りたいID(今回は日付)が何行目か求める
  2. index関数でその行のデータを求める
という流れです。

match(検査値, 検査範囲, 照合型)関数


match関数は、特定の値が特定の範囲内の何行目にあるかを返す関数です。
例えば、上の表で、2016/4/6が何行目にあるかを知りたい場合は、

入力例結果
=match(42466, A:A, 0)7

のように入力すると、2016/4/6があるのはA列の7行目と答えを返してくれます。

ここで、第一引数に入力した42466というのは、エクセルにおける2016/4/6という日付です。エクセルでは、1900/1/1を1として、それより何日後かで日付を管理しています。つまり、エクセルのセル上で2016/4/6と表示されている日付は、1900/1/1から見て42466日後であるため、実際には42466という数値として存在しているのを、表示形式を日付の形式に変えてあるだけなのです。第一引数を2016/4/6にしてしまうと、日付ではなく、"2016/4/6"という数字と/を含む文字列を参照することになるため、正しく日付を参照できません。

しかし、いちいち日付を数値にするのは馬鹿らしいので、上の画像のように、H2セルに日付を打ち込み、その値を第一引数として用いれば、日付のまま値を指定することができます。

ということで、I2セルには
=match(H2, A:A, 0)
と入力しましょう。これで、H2セルに書かれた日付がA列の何行目にあるか返してくれるようになります。

match関数の第3引数は「照合型」を表しますが、
意味
1検査値以下の最大値を調べる
0検査値と等しい値を調べる
-1検査値以上の最小値を調べる
となります。

index(範囲, 行番号, 列番号)関数


index関数は、指定した範囲の指定した行、列にある値を返す関数です。
例えば、上の表では、
入力例結果
=index(A:E, 4, 3)1.38
となるわけです。

では、上述のindex関数と組み合わせるとどうなるか、ですが、I2セルでH2セルの日付の行番号を求めたので、J2セルに
=index(A:E, I2, 2)
と入力すれば、H2セルに入力した日付の曜日が表示されるわけです。

引数を適宜絶対参照にしてコピペすれば、今回作りたいものの完成です。

注意点:欠損値の取り扱い


この方法を用いてデータを抽出する際に注意しなければいけないのが、欠損値の確認です。欠損値がある場合、今回用いたindex関数は、値が入力されていない場合には0を返します。そうなると、この方法で作った表では、元々0なのか、データ欠損で0なのかがわからなくなります。ですので、参照元のデータでは、欠損値は空白にせず、「.」を入力しておくなどの対策を取っておく必要があります。


スポンサーリンク



人気ブログランキングへ

この記事へのコメント

スポンサーリンク