エクセルの「表引き達人」になれる!複数条件でも超簡単に表引きできる必殺技

予算や売上など数字を扱うオフィスワークでは、エクセルはなくてはならない必須アイテム。

何万件もある膨大なデータから、必要なデータを瞬時に取得できるのもエクセルのすごさです。

 

悩 太蔵
えーっと、昨年12月にABC代理店に納めているA商品の価格は・・・。
あ~~~。データが複雑すぎて必要な情報が全然見つからない~。
今日中に提出しないと課長に叱られるぅぅ…

 

あらあら、情報検索でお困りのようですね。

 

「表引き関数」をご存じですか?

表引きとは「表の中から条件に合致する情報を検索する動作のこと」をいいます。

 

わかり易い例を挙げると、「宅急便の料金表」が良い例です。

「発送先:東京」で「重量:10kg」なら「送料:650円」

「発送先:大阪」で「重量:15kg」なら「送料:1000円」

のように、表の縦軸と横軸で該当する条件を見つけ、それぞれの条件が交差するところを指でなぞりながら目視することがあると思いますが、「表引き関数」を用いれば、いとも簡単に必要な情報を見つけることが可能となります。

 

悩 太蔵
数字を目で追う毎日から、エクセルが救ってくれるのですか?!

 

エクセルのハウツー本には、VLOOKUP関数やHLOOKUP関数が表引き関数として紹介されていることがほとんどです。

たしかに、行と列から「一つ」の条件で表引きする場合はこれらの関数で十分ですが、条件が「複数」となると、別の処理の仕方を求められることになります。

今回は、あまり知られていない非常に便利な「表引き関数」をご紹介しますので、是非職場で使ってみてください。

 

まずは、条件が「一つ」の場合 『VLOOKUP関数』

あるスーパーの「A町店 食料品売上表」があるとします。

この表から、「2016年2月の売上高」を検索し表示させる場合、検索値の条件は「2016年」の一つだけなので、VLOOKUP関数で「表引き」することができます。

 

検索結果を表示させたいセルG4に以下の関数を入力します。

=VLOOKUP(検索値,検索範囲,列番号,検索方法)

検索値:2016年

検索範囲:A3:E6(表全体)

列番号:3(選択範囲の3列目)

検索方法:FALSE(完全一致)

 

 

どうですか?

この関数を使うことで、セルG3に取得した値が表示できましたね?

 

条件が「複数」の場合 『SUMPRODUCT関数』

続いては、条件が「複数」になった場合を見てみましょう。

今度の売上表には、B町店も含めた全商品区分の売上高が記載されています。

この表から「A町店の2016年1月の食料品売上」を検索したい時、条件となる検索値は「A町店」「食料品」「2016年」と3つになります。

 

ここで登場するのがSUMPRODUCT関数

通常、これは積を求める関数として使われますが、「複数条件の表引き」もできてしまう目から鱗のような関数なのです。

 

使い方ですが、検索結果を表示したいセルに、

=SUMPRODUCT (条件①*条件②*条件③,列の範囲)

を入力します。

店名・商品区分・年の各列から条件と一致するセルを検索し、指定する列と交わったセルが求める値となります。

<行の絞り込み>

条件①:A列から「A町店」を検索→(A3:A15=”A町店”)

条件②:B列から「食料品」を検索→(B3:B15=”食料品”)

条件③:C列から「2016年」を検索→(C3:C15=”2016年”)

3つの条件から絞り込まれたのは6行目となります。

<列を指定>

列の範囲:1月の範囲→D3:D15

セルJ4には、絞り込まれた6行目と、指定したD列の交わったセル「D6」の値が表示されます。

 

実際に入力する関数は、

=SUMPRODUCT((A3:A15=”A町店”)*(B3:B15=”食料品”)*(C3:C15=”2016年”),D3:D15)

となります。

 

セルJ4に取得した値が表示されましたね。

この様に「SUMPRODUCT関数」で複数条件の表引きができてしまうんです。

 

悩 太蔵
この関数は、条件をいくつでも増やすことができるんですね。
すごく使い勝手が良いです。

 

「文字列」から「セル」指定へ 条件を自由自在に操る

ここまではA町店に関する情報を拾ってきましたが、B町店に関しても同じ様に表引きする場合、また一から関数を入力するのは面倒です。

そういう場合は、「表の店舗名」を変えることで「求める値も連動する」ようにしておくと使い勝手がグッと良くなります。

 

今まで、入力した関数は「”A町店”」というように「文字列」を条件を固定していました。

より利便性を高めるためには、この条件を「文字列」でななく「セル」で指定するようにしましょう。

条件になる店舗名が入力されているセルはH4なので、関数の「”A町店”」を「H4」に置き換えます。

 

=SUMPRODUCT((A3:A15=H4)*(B3:B15=”食料品”)*(C3:C15=”2016年”),D3:D15)

と入力したら、セルH4に「B町店」と入力してみましょう。

 

セルJ4の値が、B町店のデータに変わりましたね。

同様に「商品区分」も「セル」で指定することで、関数を打ち直すことなく切り替えることができます。

 

悩 太蔵
よかった~!これで今日中に提出できるゾ!!

 

応用次第で表引きの達人になろう

VLOOKUP関数は知名度が高く、それを用いて「表引き」することは多いと思いますが、複数条件に対応できないデメリットがあります。

そんなときは「SUMPRODUCT関数」を用いることで、より複雑な条件を処理することができ、応用次第では様々なパターンの売上表が作成できます。

とても便利な隠れ関数ですので、習得して同僚よりも早く仕事を終わらせちゃいましょう!

 

【厳選】お勧めのエクセル関連書籍のご紹介

私が今まで読んできた中で、とても役に立ったエクセル書籍をご紹介します。

エクセルは業務効率のために開発されたソフトであるため、使いこなせないとその恩恵を十分に享受することができません。

少しでも業務効率を上げたい方は、是非ご覧になってみてください。

ビジネスエリートの「これはすごい!」を集めた 外資系投資銀行のエクセル仕事術---数字力が一気に高まる基本スキル

私の職場ではコンサル出身の上司がいたこともあり、この本は非常に役立ちました。

「仕事は早く、完璧に」という永遠の課題に取り組む上で、読んでおいて損はないと思います。

中級者以上の方にお勧めですね。

数字力×EXCELで最強のビジネスマンになる本

こちらはエクセルのコツを学びながら、数字力を磨くことができる良書。

エクセルの基本操作がカバーされているので、初心者でも飽きずに読破することができると思います。

ちょっと他の本と趣向が異なるので、お勧めです。

「仕事が速い人」のエクセル関数術 (日経BPムック)

関数が苦手、という方にうってつけの書籍です。

関数は食わず嫌いの人が多いですが、すぐに使いこなせなくても、「どういうことができるのか」ということを知っておくだけでも大きな価値があります。

エクセルの場合、その方法を知っているかどうかで業務時間に雲泥の差が生まれることがよくあるので、是非読んでいただきたいですね。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です