エクセルのデータ集計の定番といえばVLOOKUP関数ですが、検索値より左側のデータを取得できないのがネックです。そのような場合にはOFFSET関数とMATCH関数の組み合わせで対処することができます。
関数の特徴
システムからエクスポートしたデータを集計する場合など、データの並び順によっては、VLOOKUP関数よりもOFFSET関数とMATCH関数の組み合わせを使った方が効果的なケースがあります。
まずは各々の関数の特徴をご説明いたします。
｜MATCH関数とは
MATCH関数は指定した値が、指定した範囲の中で何番目にあるかを教えてくれる関数です。値ではなく「数字」を返すのが特徴です。
数式は、MATCH（検索値,検索範囲,照合の種類）となっていまが、最後の照合の種類はとりあえずゼロを入力してください。
｜OFFSET関数とは
OFFSET関数は基準となるセルを決めて、その位置から縦・横に指定の数を動かした先のセルを参照する関数です。正の値であれば下・右へ、負の値であれば上・左へ移動します。
数式は、OFFSET（参照,行数,列数,高さ,幅）となっていまが、高さと幅は今回は無視します。
｜組み合わせ方法
まずは検索したいセルと検索範囲を定めます。それをMATCH関数で何番目かを返すようにします（列を検索範囲としたらその列の頭から何番目）。
次に基準となるセル（表であればいずれかの項目名）を決めて、OFFSET関数により、下（MATCH関数による数字）・右（指定）へ移動した先のセルを参照するようにします。数式は以下のようになります。
＝OFFSET（基準セル,MATCH（検索セル,検索範囲,0）,右への移動数）
エクセル実践
文章の説明のみではわかりにくいので、実際に計算をしてみます。資料は当事務所の特典としております、エクセル帳簿の仕訳帳と総勘定元帳を使っています。
下の仕訳帳シート（上段）における売掛金（Code：売掛金2）が発生した日付・金額・相手科目・摘要を総勘定元帳シート（下段）に引っ張りたいのですが、借方発生と貸方発生があるため、VLOOKUP関数では厳しいです。
そこで科目名+序数を検索キー（B6セル）とし、仕訳帳にあるそのセルを含む列（G列）を検索範囲としてMATCH関数により、何番目（9番目）かを指定し、OFFSET関数では「Code」と入力されたG4セルを起点に、MATCH関数指定の列数マイナス４の数（９－４＝５）を下に、１を右に移動するようにしました。
例として「借方発生金額」を見てみます。
総勘定元帳のF6セルの計算式は
=IFERROR(OFFSET(‘仕訳帳(Journal)’!$G$4,MATCH($B6,’仕訳帳(Journal)’!$G:$G,0)-4,1),””)
です。
※「仕訳帳(Journal)」は仕訳帳を作成したシート名です。
これで、2017/4/5の仕訳の売掛金（Code：売掛金2）借方の金額1,000（仕訳帳のH9セル）が総勘定元帳に転記されました。
まとめ
複数の資料のデータを提出用に見やすい1つの表にまとめたくても、VLOOKUP関数だけでは集計がうまくいかない経験はどなたにもあるかと思います。
今回ご紹介させていただいた、OFFSET関数とMATCH関数の組み合わせはかなり柔軟性がありますので、是非ご活用いただければ幸いです。
マクロなしで総勘定元帳を作成したのですが、仕訳帳から完全に転記するにはもう少し関数の追加が必要です。今回は作業効率UPの為の関数のご紹介でしたので割愛させていただきました。
