エクセルのデータ照合（book2つ）

質問

こんばんは。
現在、仕事でデータ照合の仕方に悩んでおります。
つたない説明で恐縮ですが、現状の説明をさせていただきます。

(1)集積されている内容が異なるデータAとBを照合したい。

(2)Ａ、Ｂのブックで集められているデータの内容はイコールではないが、一部のデータを照合したい。

(3)関数等で実現したいことは、

「金額」と「予算元」がイコールなら、Ｂのブックに入っている「伝票番号」を、

Aのブックに自動で入力できるようにしたい。

(3)照合したいデータの量は、Ａブック、Ｂブック、1000以上になる。

自分で考えて関数を入れてみたのですが、どうしても「FALSE」となってしまいます。

私が考えていたのは、＝IFand（AA＝BB,AB＝BC，Vlookup(BD,AC:AD,2,0)）
のような式です。
初心者なので、ちんぷんかんぷんな式ですみません。
一応、私の意図は
(1)もし、ブックAのA列の金額がブックBの金額と同じで、なおかつブックAの予算元とブックBの予算元が一致するなら
(2)ブックBの伝票番号を、ブックAのACからADの範囲の2列めに、入力する

です。
理解不可能であれば、ここは無視したうえで、ご教授いただけると本当に助かります。
どうぞ、よろしくお願いいたします。

回答No.1

keithin · ベストアンサー

ご利用のエクセルのバージョンがご相談で明記されていませんが。

ケース１）
エクセル2007以降を使っている場合
そして「伝票番号」が「数値」である場合に限り
=SUMIFS(相手ブックの伝票番号列，相手ブックの金額列，金額セル，相手ブックの予算元列，予算元セル）
のように計算できます。

＃そのままコピーできる数式の回答を教えてほしいときは、ブック名とかシート名とか、どこ列どこ番地のセルに何を入れてあるとか、ご相談投稿ではキチンと事実を書き記してください。

ケース２）
ＶＬＯＯＫＵＰなどの検索関数は、検索のキーは「必ず１つ」しか指定できません。
なので各ブックのＡ列に
Ａ２：
=予算元セル&"_"&金額セル
などのようにして、「予算元と金額」を一つのセルにキーワードとして結合してしまえば、あなたもご存じのヤリカタで楽勝出来ます

=VLOOKUP(A2,[bookB.xls]sheet1!A:Z,列番号,FALSE)
あるいは
=IF(COUNTIF([bookB.xls]sheet1!A:A,A2),VLOOKUP(A2,[bookB.xls]sheet1!A:Z,列番号,FALSE),"")
あるいはExcel2007以降を使っているなら
=IFERROR(VLOOKUP(A2,[bookB.xls]sheet1!A:Z,列番号,FALSE),"")

ケース３）
お勧めしませんが。
説明のためＡ列が予算元列、Ｂ列が金額列とすると
=INDEX([bookB.xls]Sheet1!伝票番号列,SUMPRODUCT(([BookB.xls]Sheet1!$A$1:$A$1000=予算元セル)*([bookB.xls]Sheet1!$B$1:$B$1000=金額セル),ROW($A$1:$A$1000)))

などのようにして、伝票番号列の先頭セル（１行目セル）には、暫定的にスペースを入れておきます。

この数式は、１０００行ぐらいを対象にする限りはまぁまだ我慢して使えますが、３０００行を超えてたくさんのデータに対しては使えないので、無理をしないよう注意してください。

お礼

こんなつたない内容での質問なのに、お返事いただき、ありがとうございます。
会社にデータがあるので、明日、いただいたアドバイスをよく読み、チャレンジしてみようと思います。
本当にありがとうございました。
頭を悩ましている仕事だったので、お返事いただけて、少し気持ちが明るくなりました！

補足

どうしても、うまくできません。
現状を説明します。

(1)ＢｏｏｋＡ
予算元チーム名使用金額（A列）　 伝票番号（B列）
2役員 10500
3役員 30000
4役員 30000
5役員 17781
※１　予算元チーム名と使用金額を、キーワード結合しています
※２　伝票番号に、BookBから探し出した伝票番号を入力したい

(2)BookB
1予算チーム名（A列）　　　　　　　　　使用金額（B列）　　　伝票番号（C列）
2役員 3495 　　　　30 251
3役員 3600 30 647
4役員 4724 30 73
5役員 5200 30 299
6役員 5300 30 5
7役員 6162 30 30
8役員 7000 30 498
9役員 7416 30 268
10役員 8476 30 324
11役員 10500 30 3

(3)ブックAのB列2に、計算式を入れていますが、エラーがでます。
式は、
＝ｖｌｏｏｋup(A2,BookB!A1132:C1679,2,false)

助けてください。

回答No.2

kmetu

> ブックAのA列の金額がブックBの金額と同じで

ブックAのA列の金額とブックBの金額…ブックBのどこの金額？

> なおかつブックAの予算元とブックBの予算元が一致するなら

セルの番号が不明です。

> ブックBの伝票番号を、ブックAのACからADの範囲の2列めに、入力する

ブックAのACからADの2列目すべてにブックBの伝票番号を？？

表の状態が理解できない説明なので具体的にブックAとブックBのデータを書いた上で
たとえばA3のデータをB5にどうとかこうとか説明したらいかがですか。

お礼

こんばんは。
・・・おっしゃるとおりです。すみません。
会社のセキュリティーの問題で、データを持ってかえることができず、おまけに私のあたまもゆるいので、こんな表現での質問となってしまいました。
明日、再度会社でデータを見て、頭を整理したうえで、質問したいと思います。
それでもお返事、ありがとうございました。