エクセルを用いた仕入データと請求データの照合（マッチング）

質問

エクセルでの仕入データと請求データの照合です。
シート１には仕入データ、シート２には請求データがあります。
各シートの様式は以下です。
A行から
日付ー注番ー品名ー数量ー単価ー金額ー税ー税込金額ーチェック
となっています。

仕入データと請求データの注番と金額が合えばチェックの行にaを付けます。

以前投稿した際に
=IF(SUMPRODUCT((Sheet2!$B$2:$B$10000=$B2)*(Sheet2!$F$2:$F$10000=$F2)),"a","")
の式を頂きましたが、
例えばシート１に注番0001・金額1,000の行が１行あり
シート２に注番0001金額・1,000の行が２行あった場合でもチェックの列にaが入力されてしまうのです。
＊この場合だと仕入1,000円、請求2,000円で仕入＝請求になりません。

このように一度照合したものに関しては同じ注番、同じ金額だとしてもチェックしないという条件の式をご回答頂きたくご質問させていただきました。
ご回答何卒宜しくお願い致します。

回答No.7

nobu555

>例えば「品名（C列）の左から８文字目までが合致した場合を含む」の条件をプラスしようとした場合は

下記のようになります。
=IF(B2="","",IF(SUMPRODUCT(($C$2:$C2=LEFT(C2,8))*($B$2:$B2=$B2)*($F$2:$F2=$F2))=1,IF(SUMPRODUCT(SUMPRODUCT((Sheet2!$C$2:$C$10000=LEFT(C2,8))*(Sheet2!$B$2:$B$10000=$B2)*(Sheet2!$F$2:$F$10000=$F2))),"a","該当なし"),"照合済み"))

>ご回答頂いた式の頭ではシートの指定がなく、後半の真偽の式にはシートの指定がしてあります。
>なぜですか？

例えば、Sheet1で『=A1』と『=Sheet1!A1』を入力しても
返ってくる値は、同じになります。

数式が入力されているセルと同じシート内のセルを参照する場合は、
シート名が省略できます。

つまり、シート名の無いセル指定は、同じシート内を参照しています。

ご理解頂けましたでしょうか。

お礼

これまでいろいろお世話になり本当に有難うございました。

数式の説明までして頂き今後の勉強に繋がると思います。

現在仕事で経理（買い）の業務を担当しており今後も「教えてgoo!」を利用することもあると思いますが何かしらでnobu555さんに引っ掛けて頂ければその時はまた宜しくお願いします。

有難うございました。

回答No.6

nobu555

>注番がI列、金額がJ列にあった場合はご回答頂いた論理式のBをI、FをJに変更するだけで宜しいのでしょうか？

はい、そうです、
回答の数式は、注番がB列、金額がF列としてありますので、
仕様に合わて変更してください。

お礼

了解しました。いろいろ試しにやってみます。
ご回答頂いた式に、例えば「品名（C列）の左から８文字目までが合致した場合を含む」の条件をプラスしようとした場合は

=IF(B2="","",IF(SUMPRODUCT((LEFT($C$2:$C$10000,8))*($B$2:$B2=$B2)*($F$2:$F2=$F2))=1,IF(SUMPRODUCT((Sheet2!$C$2:$C$1000,8)*(Sheet2!$B$2:$B$10000=$B2)*(Sheet2!$F$2:$F$10000=$F2)),"a","該当なし"),"照合済み"))

で宜しいのでしょうか？

更に、今式を考えながら感じたのですが
ご回答頂いた式の頭ではシートの指定がなく、後半の真偽の式にはシートの指定がしてあります。
なぜですか？

回答No.5

nobu555

>もしかしたらうまく伝わってないかもです。
いえいえ、こちらが勝手に複雑に考えていました。

以下の数式で、今度は大丈夫だと思います。
=IF(B2="","",IF(SUMPRODUCT(($B$2:$B2=$B2)*($F$2:$F2=$F2))=1,IF(SUMPRODUCT((Sheet2!$B$2:$B$10000=$B2)*(Sheet2!$F$2:$F$10000=$F2)),"a","該当なし"),"照合済み"))

判りやすく、コメントを増やしました。

数式の説明
B2=""
で、注番が未入力の場合、空白とし
SUMPRODUCT(($B$2:$B2=$B2)*($F$2:$F2=$F2))=1
で、注番と金額の組合せが、初めてチェックするものかを確認、
初めてなら、Sheet2と照合し、Sheet2と照合できたら"a"を、
照合できなかったら"該当なし"を、照合済みなら照合済みと表示します。

以上、如何でしょうか。

お礼

おはようございます。ご回答有難うございました。
早速試してみました。
結果、仕入のaをチェックしたものと請求のaをチェックしたものの合計が一致しました。非常に感動してます。

注番がI列、金額がJ列にあった場合はご回答頂いた論理式のBをI、FをJに変更するだけで宜しいのでしょうか？

回答No.4

nobu555

>お返事頂いた中で「片方だけしか付いてないがマーク数が同じになっているのでは？」とはどういうことですか？

例えば
シート１
注番　金額　チェック
0001　1000　　ａ
0002　1200
0003　1500　　ａ
0003　1500　チェック済
シート２
注番　金額　チェック
0001　1000　　ａ
0002　1200　　ａ
0002　1200　チェック済
0003　1500

このような結果場合、チェックマークが両シート共２個ですが
シート１では、0001と0003にあり、
シート２では、0001と0002にあり、
合計は、シート１が2500、シート２は2200となります。
実際に例題のデータで試されると判ると思います。

お礼

もしかしたらうまく伝わってないかもです。

【シート１】
注番　金額
0001　1000 a（シート２の１行目と照合）
0002　2000 a（シート２の２行目と照合）
0003　300　 （シート２の３行目と金額違いのため照合不可→空白）
0004　4000 a（シート２の５行目と照合）
0004　4000 （シート２の５行目と照合可だがシート１の４行目と照合したので照合不可→空白）
0001　5000 （シート２の７行目と注番違いのため照合不可→空白）
0006　6000　（シート２では注番、金額なしのため照合不可→空白）

【シート２】
注番　金額
0001　1000 a（シート１の１行目と照合）
0002　2000 a（シート１の２行目と照合）
0003　3500 （シート１の３行目と金額違いのため照合不可→空白）
0001　1000　（シート１の１行目と照合可だがシート２の１行目と照合済みのため照合不可→空白）
0004　4000 a（シート１の５行目と照合）

という感じです。
エクセルを使ってますのでaをフィルタにかけるとシート１（7000）とシート２（7000）の合計が合致するといった具合です。
うまくお伝えすることができたでしょうか？

回答No.3

nobu555

おそらく、シート１とシート２でチェックマーク"a"が
片方だけしか付いていないがマーク数が同じになっているのでは？

両方にマークの付いている金額だけを、合計すればよいのでは。

チェック欄がI列とするとシート１のJ列に下記を入力
=IF(I2="a",IF(VLOOKUP(B2,Sheet1!$B$2:$I$10000,8,0)="a",VLOOKUP(B2,Sheet1!$B$2:$I$10000,5,0),""),"")

これで金額表示されますので、J列を合計すればよいかと。

お礼

お返事有難うございます。
現在フィルタでaのついたものを抽出しSUBTOTALでシート１の合計とシート２合計を算出しているのです。

お返事頂いた中で「片方だけしか付いてないがマーク数が同じになっているのでは？」とはどういうことですか？

回答No.2

nobu555

>数式を頭から説明
説明の前に、
数式に一部誤りがありましたので訂正します。
×：IF(COUNTIF($B$2:B2,B2)=1,"","チェック済み"))
○：IF(B2=0,"","チェック済み"))
では、順を追って説明します。

IF関数の書式は、IF(論理式,真の場合,偽の場合)ですので、
論理式は、COUNTIF($B$2:B2,B2)=1
真の場合は、,IF(SUMPRODUCT((Sheet2!$B$2:$B$10000=$B2)*(Sheet2!$F$2:$F$10000=$F2))=1,"a","")
偽の場合は、IF(COUNTIF($B$2:B2,B2)=0,"","チェック済み"))
に、なります。

論理式から
論理式のCOUNTIF関数は、範囲($B$2:B2)に、検索条件(B2)に一致するセルの個数を返します。
『=1』が付いているのは、今回の質問で
>一度照合したものに関しては...チェックしないという条件の式
の条件を満たすためです。
また、範囲の『$B$2:B2』で後ろのセルに『$』が無いのは、
コピーしたとき、先頭は固定で、終端は可変するようにです。
つまり、この行の注番が初めてチェックするのかを判断しています。

真の場合
またIF関数ですが、論理式だけ説明します。
論理式に、『=1』を追加しました。
質問中の数式では、有無しか判定しないので、
>シート１に注番0001・金額1,000の行が１行あり
>シート２に注番0001金額・1,000の行が２行あった場合
このようなことになります。
複数存在する場合は、空白表示となります。

偽の場合
セルB2が未入力の場合、空白表示で
それ以外は、最初の論理式ではじかれた複数個目となり、
『チェック済み』と表示します。

以上、数式の説明ですがご理解頂けましたでしょうか。
参考になれば幸いです。

アドバイス
注番が複数入力されることが、基本的に入力ミスとするのなら、
「入力規則」で制限することも出来ます。

お礼

ご説明いただき有難うございます。
式のイメージはぼんやりと掴めた感じです。
ただ、せっかくご回答頂いた論理式でもシート１とシート２にaをチェックした合計金額が合致しませんでした。
私の説明不足でうまく状況を伝え切れてないことが原因だと思います。

何か良い方法はないでしょうか？

回答No.1

nobu555

以下の数式で如何でしょうか。
=IF(COUNTIF($B$2:B2,B2)=1,IF(SUMPRODUCT((Sheet2!$B$2:$B$10000=$B2)*(Sheet2!$F$2:$F$10000=$F2))=1,"a",""),IF(COUNTIF($B$2:B2,B2)=1,"","チェック済み"))

一度照合したものに関しては、判りやすく「チェック済み」と表示します。
Sheet2に注番が複数有ったり、金額が違うものは、空白表示としました。

お礼

早々にご回答いただき有難うございます。
早速試してみたのですが両シートのチェックした合計が合いません。
私、まだ勉強をし始めたばかりですのでもし宜しければご回答頂いた数式を頭から説明していただきたいのです。宜しくお願いします。