2つの表の合計の差額を特定するVLOOKUP

Question

ご教授いただけると幸いです。

左にBank(銀行)、右にBook(帳簿)という２つの表があるとします。

Bankの合計は5,265でBookの合計は6,240であるとします。　差額は975で、理由はそれぞれの表のコード(B列、E列）が同じでも額が違ったり、お互いに存在しないコードがあるからだとします。　なんとか式を入れてコードが同じならO、コードが違うならXを入れる式をオレンジのセルに作りました。

しかし、これではコードの違うレコードはすぐ見つけられますが、コードが同じでも金額が違うレコードの特定ができません。　（ただOとなっているだけですから・・・）。　ですのでオレンジのセルにBankとBookのコードを比較し、同じなら金額の差額を入れる、違うならXだけを入れる・・・という式を入れることはできるでしょうか？

それともそもそもこういうものはVBAでやるべきなのでしょうか？

あまりにもVBAは初心者のため、なんとか関数でやろうとしているところです。

これは会計のプロジェクトで実際はもっと長いリストなので、手で差額を特定するのが大変で、大変でしょうがないのでお知恵をいただきたいと思い、投稿しております。

初心者質問で申し訳ないのですがよろしくお願いいたします。

まり

Answer 1

E列のデータがB1:B14になければ、×、あれば単純に差額ということでよろしいですか。

D3に以下の様に入れ、このセルを下へコピーでいいです。

=IF(COUNTIF($B$3:$B$14,E3)>0,F3-VLOOKUP(E3,$B$3:$C$14,2,FALSE),"×")

Answer 2

lili803(2) さん、こんにちは。

マイクロソフト コミュニティをご利用いただきありがとうございます。

セル内のコードを比較して同じであれば金額の差額を入れたり、違うなら × だけを入れられるような式は作成できるかという質問ですね。

こちらの手元にある Excel 2007 で例の表を使ってよい方法があるかを調べてみました。

作成方法も色々あると思いますが、VLOOKUP で作成されている関数式にさらに式を挿入するという方法であると式がとても長くなってしまいます。

コードの一致で 〇 と × の判定が出来ているので、その数式を生かして列を 1 つ追加し、コードが一致した場合の差額を出す式を挿入してみてはどうでしょう。

列を挿入後、コードが一致していて、なおかつ金額が違う場合の差額を計算する数式を作成してみたので、よろしければ参考としてみてください。

※ lili803(2) さんの表であれば、 E 列に 1 つ列を追加するイメージです。

※ 表の一部を抜き出して表を作成しています。

※ 金額が一致していれば差額は 0 で 一致していなければ差額が出ます。

上記のような方法であれば表の編集なども行いやすくなると思いますし、 [Viriance] のところにも上記の関数で出した合計も関数を使えば出しやすくなると思うので 1 つの例として参考としてみてくださいね。

もし行いたいことと大きく違っていたり、何か追加で質問や不明点があれば遠慮なく追加で投稿してみてください。

試してみた結果についても返信をお待ちしていますね。

Answer 3

lili803(2) さんこんにちは。

マイクロソフト コミュニティのご利用ありがとうございます。

同様のことでお悩みの方にも、ご参考となるように、このスレッドを [ 回答としてマーク ] をさせていただきました。

またなにかありましたら、ぜひ マイクロソフト コミュニティをご利用くださいね。

Answer 4

大沢様：mombo様：

さっそく御回答いただきましてありがとうございました。

できました！

Cell A3:A14に今度はBankにあってBookにないコードを特定する式を入れるのにすこし手間取りましたが、お二人が教えてくださったD3:D18(大沢様のｹｰｽですとE3:E18)の式をコピーして、よーーーく元の式を観察し、Bank用に式を書き換えることができました。

大沢様の式ですと確かにVariance表を作成する時大いに役立ちます。mombo様の式ですと、シンプルで短期間にさっと分析できます。今後は与えられた表の大きさですとか、上司に頼まれた内容によりお二人の下さった式をあわせて使わせていただきます。

本当にありがとうございました。　『２つの表の合計の差額を特定せよ』というのはバリエーションはいろいろありますがスタッフレベル会計士の永遠のテーマですので、これからも大いに活用させていただきます。

返信の仕方がよくわからないのですが、これであっていることを祈って『返信ボタン』を押します。