Articles

HLOOKUPで検索結果から横2列ずれた値を返す

この記事をdel.icio.usに登録する この記事をはてなブックマークに登録する この記事をGoogleブックマークに登録する この記事をYahoo!ブックマークに登録する この記事をdiggに登録する この記事をlivedoor clipに登録する この記事をNewsGattor Onlineに登録する

e16-0.png
2次元テーブルのLOOKUP

表を作っていてこういう表を扱ったことはありませんか?今回はこういった2次元テーブルでBの材料a金額「6,000」を引っ張ってくる方法です。
2パターンあります。

e16-1.png



HLOOKUP?

通常であれば=HLOOKUP(A1:G5 , “B” , 3 , 0) くらいの式でいいんですが、この例ですと1行目のところ(B:DおよびE:G)をセルの結合してしまっています。これだと先程の式の返す値はE列の数量150を返すようになってしまいます。結合のセルの場合、一番頭のセルとして判定されるからです。
e16-2.png

行挿入が出来るならばパターンA

フォーマットをいじっても問題なければ2行目の後ろに1行挿入すると簡単に出来ます。
e16-3.png
挿入し、1行目の種類と2行目の項目をくっつけます。式はB3セルだと「=B1 & B2」になります。
これでHLOOKUPで検索出来るようになります。
「=HLOOKUP(A3:G5 , “B金額” , 2 , 0)」

ただし、行挿入などフォーマットをいじるとマクロが不具合を起こす可能性がありますので、簡単に挿入などが出来ない場合があります。その場合はパターンBで可能です。

INDEXとMATCHの組み合わせ

INDEXとMATCHを使えば行挿入しなくてもセル結合されたままでも検索可能です。
先に式を示唆しますとこのようになります。
e16-4.png
「=INDEX( 3:3 , MATCH( “B” , 1:1 , 0 )+2 )」

解説していきますとINDEX関数は指定された行と列が交差する位置にある値を返します。
INDEX関数について

ここでは行指定は3行目を指定していますので「材料a」行です。
e16-5.png
では列指定はどうかというと、MATCH関数を使っています。MATCH関数は指定された列または行から検索した値が何列目、何行目なのかを返します。
ここでは”B”は何列目にあるのか?を検索しており、返される値はE列なので5(列目)です。
e16-6.png

それに+2を足すとG列になりますので、先程のINDEXで3行目のG列を引っ張ってくるようになります。
e16-7.png

よって値はG3の「6,000」となるわけです。
e16-8.png

パターンAとBではAの方が一般的に解りやすい式になります。関数も1つですし。ただ色んな条件を加味しBを使うこともしばしばあるかと思います。あまり馴染みがない関数ですが覚えると幅は広がりますね。

関連記事




この記事をdel.icio.usに登録する この記事をはてなブックマークに登録する この記事をGoogleブックマークに登録する この記事をYahoo!ブックマークに登録する この記事をdiggに登録する この記事をlivedoor clipに登録する この記事をNewsGattor Onlineに登録する

トラックバック一覧

コメント一覧

コメント投稿フォーム
Name
Subject
Mail
URL
Comment

Password
Secret
管理者にだけ表示を許可する

Navigations, etc.

Navigations

ブログ記事を検索

SPONSORS

人気記事ランキング

ブログパーツ

メールフォーム

名前:
メール:
件名:
本文:

Tweet

最新記事