【エクセル】PL管理に!INDEX関数とMATCH関数を組み合わせた使い方

ecoslymeです。

エクセルExcel)で、縦に項目(PL勘定など)、横に推移の数字(年月など)があり、特定の年月の数字だけ見たかったり、抽出したかったりする時ってありませんか?

 

地道に探してコピペして数字を抽出する方法も一つですが、毎日もしくは毎月そんなことをしていると疲れてきます。

 

5月のデータを見たい時、”5″と入力すれば5月の数字が抽出されてくる。

こんなことが出来れば数多くのデータから見たいデータだけを見れるようになり、データを探すという無駄な作業が削減されます。

 

そんな時に便利な、Index関数とMatch関数を組み合わせた使い方をお教えします。

 

元データと結果

 

MATCH関数の使い方

まずはMATCH関数について説明します。

 

試しに、H3セルに以下の数式を入力して下さい。

「 =MATCH(G3,$A$3:$A$9,0) 」

 

ポイントとしては、A列と同じ項目をG列に作ることです

3ブロックについて説明します。

=MATCH( G3 , $A$3:$A$9 , 0 )
  • 検索値
  • 検索範囲
  • 照合の種類
  • 数字を知りたい項目を選択
  • 対象となる表の項目を選択
  • 後述
  • 検索値に一致

 

<照合の種類について>

照合の種類意味
-1検査値以下の最大の値で検索

※検査範囲を昇順に並べ替える必要あり

0検査値に一致する値を検索
1検査値以上の最小の値で検索

※検査範囲を降順に並べ替える必要あり

 

売上高は上から1番目の(3行目)なので、値は”1“となります。

同様に下へ数式をコピペします。

 

上の説明ではについて説明しましたが、同様にについて何列目にあるか検索してみましょう。

H2セルに以下の数式を入力して下さい。

「 =MATCH(H$2,B$2:E$2,0) 」

 

何行目かを調べるときには、検索範囲にしましたが、

何列目かを調べるときには、検索範囲にします。

”5月”は左から3列目(D列)なので、値は”3“となります。

同様に下へコピペします。

 

全て3(列目)となりました。

 

INDEX関数の使い方

次にINDEX関数について説明します。

H3セルに以下の数式を入力して下さい。

「 =INDEX($B$3:$E$9,1,3) 」

 

3ブロックについて説明します。

結合したいセルの分だけブロック数が増えていきます。

=INDEX( $B$3:$E$9 , 1 , 3 )
  • 配列
  • 行番号
  • 列番号
  • 欲しい数字の表全てを選択(数字部分)
  • 目の数字が欲しいか
  • 目の数字が欲しいか

 

選んだ表の中で、1行目3列目の数字を取ってきます。

つまり、”5月の売上高”を拾ってきます。

 

MATCH関数とINDEX関数を組み合わせた使い方

それでは本題に入ります。

今初回したMATCH関数とINDEX関数を組み合わせて使ってみましょう。

 

H3セルに以下の数式を入力して下さい。

「 =INDEX($B$3:$E$9,MATCH(G3,$A$3:$A$9,0),MATCH(H$2,B$2:E$2,0)) 」

 

見づらいので、3つに分けて説明します。

文字が多いだけですが、INDEX関数で説明した通り、数字の欲しい表を選択してから、を指定しているだけです。

MATCH関数は既に説明したものです。

見づらいので縦に表示します。

=INDEX
( $B$3:$E$9
  • 数字の欲しい表を選択
, MATCH (G3 ,$A$3:$A$9 ,0 )
  • MATCH関数でを指定
, MATCH (H$2 ,B$2:E$2 ,0 ) )
  • MATCH関数でを指定

 

下に数式をコピペしていくと、欲しい数字が出来ました。

 

5月計画の部分も同様にやってみましょう。

H3セルの数式をコピペ(セルのコピーではなく、中身の数式をコピー)した後、H2セル(5月)をI2セル(5月計画)へ移動させれば良いだけです。

 

その後、下へコピペしましょう。

 

仕上げ

最後の仕上げとして、H1セルに数字を入力すると自動的に欲しい数字が出てくるようにしましょう。

 

H2セルに以下の数式を入力して下さい。

「 =JIS(H1)&”月” 」

JIS関数は、半角全角にする関数です。

 

H3セルには以下の数式を入力して下さい。

「 =JIS(H1)&”月計画” 」

 

すると、数字を5から4に変えると4月の値を取ってきてくれます。

 

これで完成です。

 

最後に

いかがでしたでしょうか?

文字がたくさんあると一見難しく見えますが、かみ砕いていくと実は結構簡単に見えてきます

 

今回は関数の組み合わせが多かったため、難しく感じた方も多いと思います。

もう一度見直して、是非実務で利用できるように覚えていきましょう。