エクセルでデータ照合の仕方

質問

エクセルのデータ照合の仕方

仕事でデータの照合をしたいのですが・・・今現在紙ベースで合していて大変です。

こちらが把握している事務データと営業さんがあげるデータを照合したいのです。
A列商品名　B列数量　C列金額　D列合計金額（数式C*D）となります。

例えばこちらが　A　ビール　B　10　C　300　D　3000　と打ち込みます。
営業さんも　　　 A　ビール　B　10　C　300　D　3000 と打ち込むとデータが表示されないようにしたい。

こちらが　　　　　A　ビール　B　10　C　300　D　3000と打ち込み、
営業さんが　　　A　ビール　B　5　C　300　D　1500
　　　　　　　　　　A　ビール　B　5　C　300　D　1500

　　　　　　　　　　とこちらが１行営業さんが２行打ち込んでも品名があっていて合計金額もあっているのでデータが表示されないようにしたい。

こちらが　　　　　A　ビール　B　10　C　300　D　3000と打ち込み、

営業さんが　　　A　ビール　B　10　C　500　D　5000　と合計金額が違うのでこちらのデータも営業さんのデータを表示させたい。

間違い探しをしたいのです。

営業さんがこちらのデータ合わせて打ち込んでくれれば、一番良いのですが・・・そうもいかずに困っています。

こんな都合の良い照合の仕方ってありますかね？

回答No.3

kagakusuki · ベストアンサー

質問者様が仰る方法ではありませんが、間違い探しをされるのでしたら、条件付き書式を使用して、数量の総計や合計金額の総計が、Sheet1とSheet2で異なっているセルの色が変わる様にされては如何でしょうか？

　その方法は、まず、営業さんのデータはSheet1にのみ入力して頂く様にして貰い、質問者様の部署が把握しておられるデータはSheet2に入力する様にされた上で、以下の操作を行います。

Sheet1のC1セルをクリックして選択
　　↓
メニューの[書式]をクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ウィンドウの左端の欄をクリック
　　↓
現れた選択肢の中にある「数式が」をクリック
　　↓
「条件付き書式の設定」ウィンドウの右端の欄に次の数式を入力

=SUMIF($A:$A,$A1,B:B)<>SUMIF(INDIRECT("Sheet2!A:A"),$A1,OFFSET(INDIRECT("Sheet2!A:A"),,COLUMNS($A:B)-1))

　　↓

「条件付き書式の設定」ウィンドウの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ウィンドウの[パターン]タグをクリック
　　↓
適当な色（例えば赤色）の四角形をクリック
　　↓
「セルの書式設定」ウィンドウの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ウィンドウの[OK]ボタンをクリック
　　↓
Sheet1のC1セルを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
Sheet1のD1セルを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック

　　↓
現れた「形式を選択して貼り付け」ウィンドウの中にある、[書式]と記されている箇所をクリックしてチェックを入れる
　　↓
「形式を選択して貼り付け」ウィンドウの[OK]ボタンをクリック
　　↓
Sheet1のC1セルにカーソルを合わせてから、マウスの左ボタンを押し放しにして、そのままカーソルをSheet1のD1セルに移動させてから、マウスの左ボタンを放す事で、Sheet1のB1～D1の範囲を範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスをを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
Sheet1のC2セルをクリックしてから、Sheet1のD列の最下段（データが入力される可能性のある行の内で最も下の行）にカーソルを合わせ、キーボードのShiftキーを押しながらマウスをクリックする事で、Sheet1のB～D列におけるデータが入力される可能性がある全ての範囲を範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせて、マウスをを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付け」ウィンドウの中にある、[書式]と記されている箇所をクリックしてチェックを入れる
　　↓
「形式を選択して貼り付け」ウィンドウの[OK]ボタンをクリック
　　↓
同様の操作で、Sheet2のC1セルに、以下の数式による条件付き書式を設定し、その条件付き書式を、Sheet2のC列とD列のデータが入力される可能性がある他のセルに貼り付ける

=SUMIF($A:$A,$A1,B:B)<>SUMIF(INDIRECT("Sheet1!A:A"),$A1,OFFSET(INDIRECT("Sheet1!A:A"),,COLUMNS($A:B)-1))

　これで、同一商品の数量の総計がSheet1とSheet2で異なっている場合には、その商品のデータが表示されている行のC列のセルの色が赤くなり、同じく合計金額が異なっている場合には、その行のD列のセルの色が赤くなりますから、何処が間違っているのかが、判り易くなると思います。

お礼

遅くなりましたが回答ありがとうございます。
質問の仕方が悪かったですが、kagakusukiさんの言うとおり間違い探しで正解です。
条件付き書式がこんなに便利だとは思いませんでした。
大変助かりました。

回答No.2

nayuta_lot

こんにちは

データそのものを比較するなら

=EXACT(文字列1,文字列2)

という関数を使用すれば、一致した場合にTRUE、不一致の場合にFALSEを
返す関数があります。

=IF(C7=C8,TRUE,FALSE)

というようにIF文使っても同じですが・・

ただ、そちらがどのようにシートを作成してるのかわかりません。
データを２重管理しているのでしょうか？

データの保持状況がわかりませんので、どうしたら照合できるかはお答え
しかねます。

営業さんの入力するシートのフォーマット、事務で入力するシートのフォーマット
営業さんのコードや、受注ＮＯなどのキーになるものがあるのかないのか、など

ただ、やり方としては

関数で検証するために、営業さんがそれぞれ持っているデータのフォーマットと
事務側で持っているフォーマットを統一した上で、営業さんから１日１回
データを回収する。
※その際、受注No.などのような個別に特定できるキーを必ず用意する。

事務側のファイルでは、入力データシートとは別に検証用に同じフォーマットの
シートを用意する。

ここに回収したデータを順番に張り付ける。

データの行数をまず比較する。　同じ数でなければその時点でどちらかに入力漏れがある。

一方のデータ（例えば事務側）を基準にして、空いている列に受注No.などをキー
にして、VLOOKUP関数で項目を参照して比較する。

※項目（例えば商品名）とVLOOKUPでその受注No.に対応する営業さんが入力した商品名を
　参照して、先ほどのEXACTを使用して比較してあげれば検証はできます。

　例　商品名と商品名の比較

　=IF(EXACT(商品名のセル,VLOOKUP(受注No.のセル,営業シート!A:G,2,0)
,"","不一致")

　　VLOOKUP(検索キー,検索範囲,列,0)

　　検索キーはユニーク（一つしかない）なキー
　　検索範囲は検索キーを最左列とした検索するデータの範囲
　　列は、検索して見つかった場合、検索キーを1列目として、何列目を参照するか
　　最後の0は完全一致

　　検索の結果検索キーが存在しない場合は#N/Aと表示される。

　営業シート!A:Gは受注No.から一番右の列。
　一番左の列には受注No.があることが前提。
　この比較をアイテムの項目数だけ行う。
　つまり、データの右側の空いてる列に同じ項目分、チェック用の式をいれる
　間違っている個所は"不一致"と表示される。

もう一方のデータも同じようにVLOOKUP関数を使用して項目を参照して比較する。

事務側のデータと営業さんのデータと両面からチェックすれば、入力漏れが発見できる。

こんな感じですかねぇ・・

ちょっと考えてみてください。

ご参考まで

お礼

回答ありがとうございます。データは２重に管理しています。商品名に関しては、上記の関数で、照合することができました。ありがとうございました。
金額の集計に関してはツール→データの集計で今までより早く金額間違いが探せるようになりました。ありがとうございました。

回答No.1

imogasi

やりたいことは良くあることだが、質問者の力量が心配です。
エクセルの関数しか判らないようだと解決策は複雑に思うかも知れない。
エクセルの理解度もいまいちのようです。なぜならシートに表示させる、させないはエクセル関数では不可能なのに、それを期待するような質問になっている点などから推測する。
こういう仕事に絡んだニーズをエクセルでやるとすれば、最低エクセルＶＢＡでプログラムが組めないと、進まないと思う。
ーー
質問者のデータと営業のデータがどういう状態でチェックを始めるのか、余りシステムに慣れてないようで、説明されて無くて
回答が難しい。データの入力が終わってデータが両ブックにあるところから始めるなら、各行データに何かキー項目があるなら（各行識別ＩＤのようなデータ。人事データなら職員番号のようなもの）、其れで他方のデータをＶＬＯＯＫＵＰ関数で索引して、照合し比較は出来る。
ＶＢＡが出来なければ、方法はその辺どまりでしょう。

お礼

質問の仕方が悪くて申し訳ないです。おっしゃる通り、表示する、しないというとデータの抽出やVBAができないと駄目みたいですね。
アドバイスありがとうございました。