Excel

違う時期に抽出したデータで、

各行の並びがバラバラになっている事がある。

「嗚呼、こっちのデータとあっちのデータを照合したい…」

という時に便利な関数。

例題

eg. 下記のようなDeta AData Bがあるとする。

Data2

データA:

A B C D
1 Data A data A-1 data A-2 data A-3
2 apple 21 32 21
3 orange 31 44 32
4 grapes 52 64 71
5 lemon 42 30 29
6 kiwi 16 10 20

データB:

F G H I
1 Data B data B-1 data B-2 data B-3
2 grapes 100 200 400
3 kiwi 200 300 100
4 orange 500 400 300
5 lemon 600 500 700
6 apple 900 800 700

 

Data AとData Bの数字を比較したいが、果物の順番が、Data AとData Bで異なるため、横のセル同士を比較できない。

そこで、

  1. Match関数
  2. Index関数

の2つの関数を使って、Data AとData Bのデータの行を揃える。

Match関数

まずは、Data Aの"apple"が、Data Bでは上から何番目の行にあるかを示す

1-1. 任意のセル(今回はK2)にMatch関数を入れる

K2: =MATCH(A2,F$2:F$6,0)

Match関数

  • A2: "apple" のあるA2セル
  • F$2:F$6: 参照するセル(後の事を考えて、行No.の前に "$" を入れておく。)
  • 0: "apple" という言葉と完全に一致する

結果は、"5" という数字が返される。

これは、

「選択範囲(F2:F6)内で、"apple" という単語と完全に一致した言葉がはいっているセルは、上から5番目ですよ。」

という意味。

 

1-2. オートフィルで下の行 (A6) までコピーする。

Match関数-2

1-3. Kの列に、それぞれの果物の名前がF2:F6範囲で上から何番目に存在しているかが示される

Index関数

次に、Index関数で、選択範囲内の目的の行番号に入っている値を返す。

 

Index関数には、

「=INDEX (配列, 行番号, [列番号]」

「=INDEX (配列, 行番号, [列番号], [領域番号]」

の二種類がある。

 

今回は、「=INDEX (配列, 行番号, [列番号]」の方を使う。

 

2-1. eg. L2のセルに、

= INDEX ($F$2:$I$6, $K2,1)

と入力。

  • $F$2:$I$6: 参照したいセルの範囲(Data Bのデータ)
  • $K2: 何行目の値か(Match関数で算出した、"5行目" )
  • 1: 選択範囲中の何列目の値火(1列目は果物の名前、2-4列目は数値)

L2に、"apple" という値が返る

2-2. オートフィルでL6までコピー

3-4. 2-4列目も同様にIndex関数で返す

Index-2

2-4. L-Oの列に、Data Aの行順で並んだData Bのデータが返る

 

…あとは、A-DのData AとL-OのData Bのデータを比べればOK。

References

にほんブログ村 科学ブログ 脳科学へ
にほんブログ村 子育てブログ 海外ワーキングマザー育児へ
PVアクセスランキング にほんブログ村