ＥｘｃｅｌでＡシートとＢシートの比較

質問

ExcelでＡという正しいシートがあります。中味は大きなテーブルです。

Ｂシートも最終的にはＡシート＝Ｂシートの形にしたいのですが、諸事情でＢの方は行が多かったり少なかったり、或いは、数値が異なっていたりします。列数は大丈夫です。

そこで、ＡとＢを比較し、データがおかしいＢの行にエラーを返したいのですが、何か適当な関数をご存知の方教えて下さい。

（例）
【Ａシート】（正）
商品コード・商品名・単価・数量・価格
ab13467…シャツ…1,500…2…3,000
hj28888…スカート…2,000…3…6,000

【Ｂシート】
商品コード・商品名・単価・数量・価格
ab13467…シャツ…1,500…2…5,000 ←（エラーを出す）　
hj28888…スカート…2,000…3…6,000

（※Ａの商品コードは重複データなし、Ｂの商品コードは存在しない又は重複の可能性ありです。
Ｂは入手経路が異なる為に一部データがおかしくなっています。）

以上、わかりにくいかもしれませんが、何か手がかりでも頂ければと思います。但し、マクロは解らないので関数でお願いします。

回答No.6

comv · ベストアンサー

こんばんは

こんな方法もあります

・Ｂシートの項目を含む全データ範囲を選択
・メニューのデータ　フィルタ
　フィルタオプションの設定
　リスト範囲：Ｂシートの選択した範囲が入力済みの筈です
　検索条件範囲：欄をクリックして入力状態にした後
　シートＡの項目を含む全データ範囲を選択

（重複もエラーチェックしたいなら）
　「重複するレコードは無視する」　にチェック
・ＯＫ
・抽出された状態で　[ALT]+[;]キー
・可視セルのみ選択状態になりますから　適当な色で
　塗りつぶし指定
・データ　フィルタ　全て表示

これで色の塗られていない行がシートＡにないデータ
（と重複データの２番目以降）です。

お礼

すみません、たいへん失礼しました。

『（重複もエラーチェックしたいなら）
「重複するレコードは無視する」にチェック 』
で正しかったです。

再度処理をしてみたら書かれた通りの結果となりました。

補足

えっ、何だかわからないけれど、この通りにやってみたら出来たような…気がします。ありがとうございます。

『（重複もエラーチェックしたいなら）
「重複するレコードは無視する」　にチェック 』
は、チェックを外す　でいいですよね？

でもこれは一体なんですか？こんな方法初めて知りました。

ただ、Ａに存在しＢには存在しないデータは、やはりVLOOKUP関数（今、他の適当な関数が思い浮かびません。）などで検索し、Ｂに追加しなければいけませんよね？

回答No.9

comv

こんばんは３

>それからこの教えて頂いた機能は、項目がそれぞれ
>全て同じとして、一部の項目、例えばＣ～Ｆ列を
>比較するという事は可能でしょうか？

抽出範囲　も　検索条件範囲も　Ｃ～Ｆ　にすれば　ＯＫ
の筈です。

>教えて頂いた方法で本番データ（ＡＢ両シートとも
>項目数40、行数約700）を処理していたら、途中で
>「EXCEL.EXE」というエラーが出てしまい勝手に
>終了してしまいました。

私も限界値は試したことがないので申し訳ありませんが
わかりません！数百行単位の重複処理は行ったことは
ありますが精々４項目程度だったので・・・４０項目
７００行を　ＯＲ　処理条件　とするのは確かに負荷的に
キツイかもしれませんね！

>「ＡのあるコードをＢに探しにいって値を返してもらう」
>と言いたかったので、関数が入るのはＡ側です。
>この問題は多分大丈夫だと思うのですが…、
あっ　成る程　読みが足りませんでした　失礼しました
当然、前記された方々の数式で可能だと思います。

お礼

検証作業終了しました。
項目も1/4ぐらいに絞り、行も100ずつシート分けをし、comvさんから伝授して頂いた方法で処理をしました。
あんまりにも簡単に比較が出来てしまうので本当に大丈夫…？かと少し心配でしたが、結果ＯＫでした。
ありがとうございました。助かりました。

回答No.8

comv

再び　こんにちは

>ただ、Ａに存在しＢには存在しないデータは、やはり
>VLOOKUP関数（今、他の適当な関数が思い浮かびません。）
>などで検索し、Ｂに追加しなければいけませんよね？

それはＢ側からでは、VLOOKUP()などの関数式でも
難しいと思います（できない訳ではありませんが）

前回のフィルターオプションでは、Ｂシートの不要部分
（色なし部分）を探し出す作業ですが、Ａシート上で
Ａシートのデータを抽出範囲として、Ｂシートを条件に
する（前回とシートの立場が逆）と色塗りされない行
（抽出されない部分）がＢシートに不足している部分です

でも余分なものを消して不足部分を補うとＡシート
そのものになっちゃうのでは？
それならＢシートのデータを全て消してＡシートの
データをコピーしたのと同じでは？

補足

こんばんは。
再び回答ありがとうございます。

>それはＢ側からでは、VLOOKUP()などの関数式でも
難しいと思います（できない訳ではありませんが）

言葉が足りなくて申し訳ありません。
「ＡのあるコードをＢに探しにいって値を返してもらう」と言いたかったので、関数が入るのはＡ側です。この問題は多分大丈夫だと思うのですが…、

教えて頂いた方法で本番データ（ＡＢ両シートとも項目数40、行数約700）を処理していたら、途中で「EXCEL.EXE」というエラーが出てしまい勝手に終了してしまいました。
これは比較するデータが大きすぎたせいでしょうか？それともノートPCの処理能力や容量、メモリの問題でしょうか？

一応、かなり項目を絞って再度やってみたら結果は出たのですが、これから詳細をチェックするのでまだ…という感じなのですが。

それからこの教えて頂いた機能は、項目がそれぞれ全て同じとして、一部の項目、例えばＣ～Ｆ列を比較するという事は可能でしょうか？

回答No.7

toshihiko_ooishi

＞VLOOKUP関数というのは、例えば返す列目に数字を入れる箇所にSUM関数などは入れられないのでしょうか？

　申し訳ないですが、しばらく質問の意味がつかめませんでした…。
　ちょっと勘違いかもしれませんが、B～E列の各列の比較を論理和又は論理積で集計したいということですか？
　もし、そうなら、「配列数式」を使うことになるかと思います。

　仮に、BシートのF列に比較結果用の列を作るとすれば、F2に次のとおり入力します。

=OR($B2:$E2<>VLOOKUP($A2,Aシート全体,COLUMN($B2:$E2),FALSE))

…と入力し、ここでEnterの代わりに、Ctrl+Alt+Enterとします。
　これをこのまま一番下のセルまでコピーしてください。

　B～E列のうち、1つでもAシートと異なっていれば、TRUEと表示されるはずです。
　これで、オートフィルタを使えば、抽出もできます。

＞それから、「条件付き書式」で色をつけた場合、後でフィルタ機能などを使ってそれだけを抽出する事は可能でしょうか？

　これは多分・・・、出来ないと思います。

お礼

再度お礼が遅くなり申し訳ありません。
[＃6]の方が教えてくれた方法でなんとか誤データをピックアップする事は出来たのですが、この方法だと比較するデータが大きすぎる場合エラーになってしまうので、比較される側(誤データ)をいくつかのシートに分けて処理する必要がありました。

私としては出来ればVLOOKUP関数と何か他の関数を利用して比較が出来ればと思い、toshihiko_ooishiさんの方法も試してみているのですがどうもうまくいきません。

VLOOKUPの後に$A2と数式には書かれていますが、これは$B2の間違いではないですか？このままでは$A2の値をキーにしてAシートを探しに行くという形になってしまいますので、それは当然存在する筈だと思うのですが…？

それから通常、返して欲しい値が存在する列を入力する箇所に、COLUMN($B2:$E2)を持ってきていますが、これは一体なんの値を返してくるのでしょう？
この関数は、ヘルプでは範囲の列番号を返すとありますが…。

ちなみに私はこの箇所にSUM関数を入れてみましたが、やっぱりエラーが出てしまいました。
返す値の列目だけを5→6→7→8→9と変え、5つのVLOOKUP関数の値を加算する方法でなんとかやれるみたいですが、こんなに式が長くなると何処で間違っているのか探すのがたいへんです。

もしお時間がありましたら再度ご教授願えればと思います。

補足

すみません、以下のお礼に自分の勘違いがありました。
> VLOOKUPの後に$A2と数式には書かれていますが、これは$B2の間違いではないですか？
の部分ですが、VLOOKUPの後の$A2はAシートではなくBシートのセル番地ですね。Aシートだと前にシート名が入りますものね。
とんだ勘違いで申し訳ありません。そもそも紛らわしいシート名を例として使ってしまった私の誤りです。

回答No.5

papayuka

こんにちは。

ちょっと手間な方法ですが、、

１．Ａシート、Ｂシートとも、データの連結フィールド（列）を作る。
　　例えば、D列に =A2&B2&C2&D2　で、ab13467シャツ150023000 ってなデータになる。

２．Ｂシートの E列に ＡシートとＢシートのD列（例はD2）を比較する MATCH関数を入れる。
　　=MATCH(D2,A!$D$2:$D$100,0)　で #N/A が返る行はＡシートにない。
　　A!$D$2:$D$100 は Ａシートのデータ範囲

３．Ｂシートをソートし、F列（例はF3） に =IF(E3=E2,"重複","") の関数を入れる。
　　* 元の並びが必要なら、ソート前に Ｂシートにインデックス行を作っておく。

お礼

お礼が遅くなり申し訳ありません。
papayukaさんは多分パニックに陥っても自分が知り得る情報で順序だてて物事を考えられる方なんですね。
「#6」さんの方法でとりあえず誤データの抽出作業は簡単に行えたのですが、ただこの方法だと比較するデータが大きすぎる場合エラーになってしまうので、誤シートの方をいくつかのシートに分けてから処理を行う必要があるという手間がありました。

関数だけで抽出するには？と思い、papayukaさんの方法も試してみました。MATCH関数がちょっとよくわからなかったので、VLOOKUP関数と文字列を比較するEXACT関数を使ってやりました。
結果は「#6」さんの方法と同じになったので検証ＯＫです。
回答ありがどうございました。

回答No.4

toshihiko_ooishi

私も、VLOOKUP関数を使うことが妥当だと思います。
　それと、「条件付き書式」を併用すれば、Bシートの商品名、単価、数量、価格に、Aシートと異なる値がセットされている場合、色をつけることが出来るかと思います。
　＃３の方の言うとおり、条件付き書式は他のシートは直接参照できませんが、名前をつけることで、参照可能になるようです。

　Aシート全体を選択
→数式バーの左の「A1」が表示されている場所に「Aシート全体」（名前は任意で可）と入力し、Enter
→BシートのB列からE列を選択
→[書式]
→[条件付き書式]
→「セルの値が」を「数式が」に変更
→右のテキストボックスに下記の式を入力し、書式を設定し、OK

=B1<>VLOOKUP($A1,Aシート全体,COLUMN(),FALSE)

　これでいいと思いますが、間違ってたらゴメンナサイ。

補足

回答ありがとうございます。
私も、やはりVLOOKUP関数が妥当だと思い、先ほどからずっと検証していたのですが、焦っているせいかうまくいきません。
VLOOKUP関数というのは、例えば返す列目に数字を入れる箇所にSUM関数などは入れられないのでしょうか？
おそらく数値の間違いが殆んどだと思われるので…。
それから、「条件付き書式」で色をつけた場合、後でフィルタ機能などを使ってそれだけを抽出する事は可能でしょうか？

回答No.3

lipton55

私も「条件付書式」がいいと思います。
ただ条件付書式は別のシートは参照できないと思いますので、BシートのデータをAシートのデータに並べるように貼り付けをしてからの方がよいと思います。
データの修正が終わったら、条件付書式を削除し任意の領域をコピーし、Bシートに貼り付ければよいかと思います。

お礼

お礼が遅くなり申し訳ありません。
あまりにもデータが大きすぎるので一つのシートに両方を並べて作業をさせることは不可能でした。
でも「条件付書式」を利用することについては、なるほどそんな手もあるのかと新しい発見でした。
回答ありがとうございました。

回答No.2

coco1

Ｂシートにエラーを表示したいとのことですが、Ａシートに表示させた方が簡単だと思います。それでは不都合でしょうか？

Ａシートに例えば「照合」という列を作り、vlookup関数で、Ａシートの商品コードをキーに、Ｂシートの商品コードを検索列に、Ｂシートの例えば数量を返値に設定して、ちゃんと値が返ってくればキーがＢシートにも存在する、値が返らなければＡにある商品コードがＢには存在しないと言うことになります。

ただし、キー順で整列していることが条件ですが...

お礼

お礼が遅くなり申し訳ありません。
> Ｂシートにエラーを表示したいとのことですが、Ａシートに表示させた方が簡単だと思います。それでは不都合でしょうか？

はい、不都合というかこれはＡにあってＢにない(未入力データの検索)には有効なのですが、逆にＢにあって(余計なデータが入力されている)Ａにないデータは探せないという結果になってしまいます。

皆様方のお陰でなんとか検証作業もあともう少しで終了といった感じです。
回答ありがとうございました。

回答No.1

tksoft

書式の「条件付書式」を使用するのはどうでしょう。
例えばBシートのA1に
「セルの値が」「次の値に等しくない」「=Aシート!A1」
のときセルの背景を赤にする
とかいうのはいかがですか。

補足

回答ありがとうございます。
私は「条件付書式」という機能を今まで利用した事がないのですが、もしかしてこの機能はＡシートとＢシートが同じように並んでいないと使えないですか…？
件数が一緒ではないのでいろいろ悩んでます。

今回、Ａシートの商品コードのキーを手がかりにＢシートに探しに行って、同じ商品コードがＢシートのどこかの行に存在した場合、Ａ・Ｂ双方のそれぞれのデータが一致するかを確認したいのですが…。