よく使うEXCELの関数集６（検索・行列関数）

論理関数

前回は論理関数のご紹介をいたしました。

今回はEXCELへのデータが多くなってきた、複数のシートにデータがあるなどの場合にそれぞれのデータを検索して対象のデータを抽出する関数をご紹介します。

この関数はのちに豪紹介するACCESSやプログラミングで使う「データベース」の考え方の基本となるのでイメージはしっかり掴んでおきましょう！

表内を検索しその結果を表示する（データを突合する）VLOOKUP、HLOOKUP、LOOKUP

VLOOKUP 　：　指定した範囲内で縦方向に検索を行う
　使用方法　：　(検索する値, 検索される範囲, 検索で見つかった場合の表示する列番号, 検索の型^※)
　　　　　　　　※検索の型：TRUE（もしくは省略）とFALSEがあります。
　　　　　　　　　　　　　　TRUE　：検索する値に一致する値を検索しますが、一致する値がないときには、
　　　　　　　　　　　　　　　　　　　検索する値未満の最大値を表示します。
　　　　　　　　　　　　　　FALSE ：検索する値に一致するもののみ表示します。（通常はこちらを使います）
　使用例　　：　=VLOOKUP(B4,B10:D19,2,FALSE)

【説明】使用例１（完全一致）
　まず、下記の使用例１の前提をご説明します。
　上段の商品番号のB4セルに商品番号を入力すると、下記の商品マスターから縦方向にデータを検索してきて、一致したものの行に商品名と金額を表示します。
　※合わせて個数を入力すると合計金額を出す式も参考として記載しておきました。

　まず、検索する文字としては「1-003」（B4セルの内容）で、検索する範囲はB10からD19（商品マスターの全体の範囲）
　この範囲内から一番左側の列を検索し、「1-003」（B4セルの内容）が見つかった行の2番目の列（ここでは商品名「C列」）を表示します。
　最後にFALSE（もしくは省略）で完全一致を指定します。

【説明】使用例２（近似一致）
　例えば、下の画像（使用例１）のように商品番号に一致しないものがある場合エラーとしてN/Aが返ってきてしまうので、完全に一致しなくても検索の一番近い最大値を表示したい場合は下の画像の（使用例２）のようにTRUEを指定します。

上の画像のように、3-004という商品番号を検索に指定すると、その値に近い最大値、3-003が検索結果として表示されるため、冷蔵庫の値が表示されています。

【参考】
　EXCELにはLOOKUPという関数もあり、この関数では上記のようにTRUEを指定した場合と似たような検索が可能です。
　=LOOKUP（検索する値, 検査される範囲, 表示する範囲）
　検索する値未満の最大値の行を検索結果として表示します。
　使用イメージとしては、指定した金額内で最高額のものを表示します。
　アップセル提案の商品を検索する際などに利用すると便利かもしれません。

★Tips★販売価格と仕入れ価格を比較して利益金額を表示したい（データを突合する）

【使用例３（テータ突合）】
　例えば自社で現在販売している価格の一覧があるとします。
　そこに本日仕入れた商品の仕入れ額を表示させます。
　表示された金額を引き算することで現在の販売価格が適正なのか一覧で判断することができます。
　この考え方はデータベースの考え方に一番近いものですので、是非下の図を参考にしてみてください。

上記の画像ではE列に仕入れ価格をVLOOKUPで表示し、F列で販売価格の金額から仕入れ価格を引いてF列に利益を表示しています。

【参考（絶対参照）】
　検索の範囲に「$B$18:$D$27」と＄マークがついていますが、これは絶対参照といって、EXCELのフィル機能で数式をコピーした際に参照する範囲が動かないように＄マークを付けて固定しています。
　＄マークは列、行、それぞれ別々に指定することができ、自動で動かしたくない列や行の前だけに記載することもできます。

HLOOKUP　：　指定した範囲内で横方向に検索する
　使用方法　：　(検索する値, 検索される範囲, 検索で見つかった場合の表示する行番号, 検索の型^※)
　　　　　　　　※検索の型：TRUE（もしくは省略）とFALSEがあります。
　　　　　　　　　　　　　　TRUE　：検索する値に一致する値を検索しますが、一致する値がないときには、
　　　　　　　　　　　　　　　　　　　検索する値未満の最大値を表示します。
　　　　　　　　　　　　　　FALSE ：検索する値に一致するもののみ表示します。（通常はこちらを使います）
　使用例　　：　=HLOOKUP(B3,B10:H13,2,FALSE)
　　　　　　　　　※下図ではフィルしたときに検索する値のセル番号や間作される範囲が動かないように＄を付けて絶対参照にしています。

【説明】
　VLOOKUPとまったく同じで縦と横が入れ替わったものと考えて頂くのが一番わかりやすいかと思います。

利用シーンとしてはVLOOKUPが圧倒的に多いと感じますが、こちらの関数もまれに使うので覚えておいてもよいかもしれません。

リストから指定した値を表示する（CHOOSE）

CHOOSE 　　：　リスト内にある値を表示する
　使用方法　　：　（表示したい値番号, 値1, 値2, …）※最大254個まで指定できます。
　使用例　　　：　=CHOOSE(B3,”洋画”,”邦画”,”アニメ”)
　
【説明】
　B3～B5セルに入力されている値に基づいて “洋画”、”邦画”、”アニメ”が表示される。
　B列に1が入って入れば”洋画”、２が入っていれば”邦画”、３が入っていれば”アニメ”が表示されます。

いかがでしたでしょうか？
行列関数にはINDEX、COLUMN、ROW、MATCHなど他にも関数は用意されているのですが、よく使うものだけに厳選してご紹介しました。
他の関数も気になる方は、是非エクセルのfxボタンから一覧を参照してみてください。

次回はデータベース関数と呼ばれるDCOUNT、DSUMなどについてご紹介します。
知っていると「おっ！」と思われるような関数だったりしますので是非覚えてみてください！

では、次回をお楽しみに。