vlookup関数をコピー、貼り付ける際にはmatch関数の組み合わせる

会計ソフトから月次推移のExcelデータを出力し、推移を確認することをよく行っています。
今回、数字を確認しやすいように月次推移資料を作り直した際、vlookup関数のコピーが上手くいかず悩んでしまったことについて公開します。

スクリーンショット 2015-04-10 01.22.52

vlookup関数で月次推移資料を作るときに困った。。

会計ソフトから出力した月次推移の元データ。
スクリーンショット 2015 04 09 22 32 35

ひとつのExcelブックの中に会計データのシートと月次推移資料のlシートを用意。会計データをExcelのシートに貼り付けると自動的に月次推移資料が出来上がり。
そんなことをイメージして関数を組もうとしていました。

次の画像の様に各月に対応する数字が表示されることを想定していましたが
スクリーンショット 2015 04 09 22 35 53

実際に作成出来たものは1月度の数字は想定通りの数字となりましたが、2月度以降の月にも、1月度の関数をコピーしたところなぜか全て1月度の数字が2月度以降にも表示されることに。。

見事に失敗しておりました。
スクリーンショット 2015 04 09 22 33 51

お手軽な修正方法は手作業で関数を直していくことですが、今後も同様の作業をすることが億劫に感じ、数式自体を見直しに取り組みました。

列番号はmatch関数を組み合わせてコピペに対応

貼り付けたvlookup関数が失敗した理由

分析用のExcelシートのC7のセルと、貼り付けたD7のセルの関数を見比べてみたところ、どちらも次の関数が入っていました。

=VLOOKUP($B7,損$1:$1:$1048576,2,FALSE)

スクリーンショット 2015 04 09 23 07 25

ここで想定外だったのが列番号として入力していた ,2, の数字の箇所、
C7のセルが 2 なら、右隣のD7のセルにコピーすれば 3 になり、参照するセルも右にずれていくと思い違いをしていました。

列番号はmatch関数を組み合わせてコピー&ペーストに対応させる

調べてみたところ、match関数という参照する範囲の中で検索する値が何番目なのかを数字で表すものがあり、vlookup関数の列番号に組み込むことでコピー&ペーストに対応が可能になることが分かりました。

match関数の組み込み方

当初 =VLOOKUP($B7,損$1:$1:$1048576,2,FALSE) と入力していた『,2,』の部分を『,MATCH(Sheet1!C$6,損!$8:$8,0),』に変更し、C7のセルの関数を次の数式に変えます。

=VLOOKUP($B7,損$1:$1:$1048576,MATCH(Sheet1!C$6,損!$8:$8,0),FALSE) 

こうすることで当初想定していた2月度以降の月にも、対応する月の数字が2月度以降にも表示される様になりました。

mach関数の説明

match関数の数式の構成は 検査値、検査範囲、照合の種類の3つ。

『,MATCH(Sheet1!C$6,損!$8:$8,0),』

検査値:Sheet1!C$6 表示したい月のセル
6は$で固定し、英語の部分は他のセルに貼り付けるとC,D,E…と変えていく
スクリーンショット 2015 04 09 23 50 34

検査範囲:損!$8:$8 参照元データの月のある列全体
参照元の 損 シートの 8行目の全て
スクリーンショット 2015 04 09 23 51 32

照合の種類 0 完全に一致するものは0を入力します。

まとめ

vlookup関数を使い始めたものの、月次推移の作成で関数のコピペに失敗。
他の行のセルに貼り付ける際には改めて修正をするのかと負担に感じていました。

vlookupとmatch関数との組み合わせはかなり手間を減らせます。是非組み合わせて活用していきましょう。

|||||||||||||||||||||||||||||||||||||||||||||||||||||||

【編集後記】

Excelデータをやりとりしバージョン管理に失敗し、

追加更新したデータを消してしまう誤操作をしてしまうなど。。

明日も頑張ろう!

【昨日の一日一新】

4/8 NEW YORKER’S Cafeでランチ

一日一新のきっかけはこちら→一日一新

お知らせ
東京都の杉並区荻窪で守屋冬樹税理士事務所を営んでいます。
お客様の担当は必ず守屋冬樹自身、監査法人での経験を駆使した決算申告のみ対応が強みです。

■サービス一覧
内容と料金は下記のリンク先にて明示。ご入用の際はぜひクリックしてご覧下さい。
小規模会社個人事業主の税務顧問
・創業から2年内の会社様限定メニュー 創業顧問創業借入
・小規模会社向け顧問契約外で行う決算・申告のみ業務
出版、講演、記事執筆(監査実務、各種税金など)
個別相談
■ブログ投稿リクエストフォーム
moriya-blogで書いて欲しい内容について募集中です。是非こちらからリクエストのご連絡を下さい。

■ブログランキングに参加中
にほんブログ村 士業ブログ 公認会計士へ
にほんブログ村

■スポンサードリンク