そこで、エクセル上で、IDだけを入力すれば、あとは自動でそのIDのデータを引っ張ってきてくれるような表を作ってしまいます。
大まかな流れ：膨大なデータから必要な日付のデータだけを抽出する
今回はmatch関数とindex関数を用いて、
のような日付ごとの歩行距離、階段を上った階数、歩数の一覧表から、特定の日付のデータのみを抽出することを考えます。
目標は、
のH2セルに日付を入力すると、上の表から自動的にデータを拾ってきて、
となるようにすることです。
大まかな流れは、
- match関数で知りたいID（今回は日付）が何行目か求める
- 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) |
match関数の第3引数は「照合型」を表しますが、
| 値 | 意味 |
|---|---|
| 1 | 検査値以下の最大値を調べる |
| 0 | 検査値と等しい値を調べる |
| -1 | 検査値以上の最小値を調べる |
index(範囲, 行番号, 列番号)関数
index関数は、指定した範囲の指定した行、列にある値を返す関数です。
例えば、上の表では、
| 入力例 | 結果 |
|---|---|
| =index(A:E, 4, 3) | 1.38 |
では、上述のindex関数と組み合わせるとどうなるか、ですが、I2セルでH2セルの日付の行番号を求めたので、J2セルに
| =index(A:E, I2, 2) |
引数を適宜絶対参照にしてコピペすれば、今回作りたいものの完成です。
注意点：欠損値の取り扱い
この方法を用いてデータを抽出する際に注意しなければいけないのが、欠損値の確認です。欠損値がある場合、今回用いたindex関数は、値が入力されていない場合には0を返します。そうなると、この方法で作った表では、元々0なのか、データ欠損で0なのかがわからなくなります。ですので、参照元のデータでは、欠損値は空白にせず、「.」を入力しておくなどの対策を取っておく必要があります。
この記事へのコメント