2010年10月21日木曜日

データの取り出しをスムーズに進める一つのヒント。(その1)


excel_for_office_14_web / Joey DeVilla


担当者ごとの月別のデータ(Excel)があります。
どのように、担当者別の月ごとのデータを抽出しますか?

Excelのワークシートを使って簡単に取り出すにはどのようにするのが効率的でしょうか?
VLOOKUP(HLOOKUP)関数もいいですね。
ただ、その場合セルの指定などで少々面倒な思いをする場合があります。


そこで、「INDEX関数とMATCH関数」の併用です。
今回は、そのことに触れてみたいと思います。

そもそも「INDEX関数」ってなに?



行番号、列番号(データ位置の番号であってワークシート上のデータ番号ではないもの)からデータを抽出するものです。

=INDEX(配列,行番号,列番号)


配列:範囲または配列定数を指定
行番号:行位置を数値で設定
列番号:列位置を数値で設定

「数値を指定する必要があるのなら、VLOOKUP(HLOOKUP)関数と一緒では?」と思われるかと思います。
しかし、後ほど記載するMATCH関数との組み合わせでこの問題が一気に解決する(はず)です。

では、実際のデータを元に使用方法の違いを見てみましょう。



例として、上記のような表から特定月の商品別売上を求めるとします。

VLOOKUP関数であればこのようになるかと思います。

=VLOOKUP(C10,B3:G8,2,FALSE)


C10セルにある商品名「コーラ」を、B3からG8までの範囲にある中の2番目の値を返す。
という感じですね。

ただ、この場合4月はこれ、5月はこれと関数を追加をしていく必要がありますね?
特に第3引数の列番号の指定などはいちいち数を数えて入れての繰り返しになるはずです。
B10のセルにある月をプルダウン選択で月選択し、商品名も選択できると便利ですよね。
この状態でプルダウン選択を行うとプルダウン選択項目と結果が一致しません。

では、実際に同じ結果を出すためにINDEX関数を使用してみます。



=INDEX(B2:G8,MATCH(C11,B2:B8,FALSE),MATCH(B11,B2:G2,FALSE))


INDEX関数では範囲を選択し、行番号、列番号の指定をしていきます。
最初の「B2:G8」でデータの範囲を選択し、その中から、行番号(コーラの3行目)、列番号(4月の2列目)をそれぞれ選択します。

しかし、行番号、列番号をそれぞれ数値で指定してしまっては、VLOOKUP(HLOOKUP)関数の方が楽であり、とても汎用的とは言いがたいかと思います。

そこで、使用するのはMATCH関数です。

=MATCH(検査値,検査範囲,照合の型)


検査値:この場合は、「商品名(コーラ)」
検査範囲:この場合は商品名(行番号)、月(列番号)
照合の型:False(0でも可)なら完全一致のみ、True(1、-1)なら最大値(最小値)の値

このような、位置を特定するのに有益なMATCH関数を組み合わせることで、検索がスムーズになるはずです。

この組み合わせで行うと、「月」「商品名」をプルダウンリスト化することで、検索項目を入力することなくスムーズに検索する一つのツールとすることができるはずです。

ここまでであれば、「大して変わらないからVLOOKUPやHLOOKUP関数のままで良いか」と思うかもしれません。


複数月の平均を求めてみる



では、3ヶ月の平均を求める。といった場合はどうでしょうか?
4月から6月までの平均を求めるとします。

VLOOKUP関数であればそれぞれ、4月の売上、5月の売上、6月の売上を持ってくるための関数を書き、その値を元に平均を求めるという操作になるかと思います。
これをINDEX関数では1文で書き表すことができます。



F12の式は以下のようになります。
赤字が「始点」となるセル位置(この場合、4月のコーラ)
青字が「終点」となるセル位置(この場合、6月のコーラ)

=AVERAGE(
INDEX(B2:G8,MATCH(D12,B2:B8,FALSE),MATCH(B11,B2:G2,FALSE))
:INDEX(B2:G8,MATCH(D12,B2:B8,FALSE),MATCH(D11,B2:G2,FALSE))
)


INDEX関数であれば、行番号と列番号を求めるので、その求めた答えを「AVERAGE」関数に当てはめます。

AVERAGE関数で範囲を指定するように、始点となるセル位置をINDEX関数で求め、終点となる位置もINDEX関数で求めると、X月からY月までのZヶ月の平均売上といったものも求まります。
さらに、データが売上平均などとなっている場合にSUM関数やCOUNT関数などを併用しながら使えば、汎用的なワークシートの作成が可能となるはずです。

また、この場合は期間、商品をそれぞれセルに保存していますので汎用的な利用ができます。
(5月から8月のビールの売上平均など)


このように「INDEX関数(併用MATCH関数)」を使用することで他の関数の引数としての役割も果たせます。
また、INDEX関数ではシート選択(データ種類の選択)のようなこともできますが、それは今度の機会にでも。


投稿者:@DeDe_NI


0 件のコメント:

コメントを投稿

ツイート数からみる"バーチャルYouTuber"ブーム

今や YouTuber の話題の半分を占めるほどのクチコミ数に 当社が提供するソーシャルビッグデータ検索ツールの「 beInsight (ビーインサイト)」を使って、話題の「バーチャル YouTuber 」について調べてみました。 「バーチャル YouTuber...