[[20210227093432]] 『複数条件で差分比較』(じょあ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『複数条件で差分比較』(じょあ)

2つのワークシート間の差分を色分けしたいと考えて行き詰まっており、ご助言を頂きたく相談させていただきました。

ワークシート1(w1)が現在のデータ、ワークシート2(w2)が過去データです。
それぞれAからC列に数字や文字が入力されており、w1とw2間で、3つとも一致すれば同一のデータ(行)になりますが、データの追加削除があるため、行番号は変わってしまいます。
DからF列には日付けや名称があり、変更のあったセルと追加/削除された行を確認したいのです。

以下2点について、VBAでこんなコードが使えるのでは?というヒントを頂けないでしょうか。

w1とw2を比較してA-Cのデータが一致する行で、D-Fのどれかに差がある場合、w1内の差分があったセルに色づけをする方法
w1とw2を比較してA-Cが一致する行が無い場合(行の追加や削除)に、w1とw2の該当行に色づけする方法

< 使用 Excel:Office365、使用 OS:Windows10 >


 1.それぞれ、データが何行くらいあるシートの話なんですか?

 2.比較すべきデータ行が存在する場合、その行のA列には必ずデータが入っていますか?

(半平太) 2021/02/27(土) 10:25


 ユニークがキーがないので、まずはユニークなキーを作りましょう
 (1) ユニークキーの作成
   G列に
      =A2 & "-" & B2 & "-" & C2 
   みたいな式を入れる。つなぐ文字列は "-" でなくても
     何でもいいけど、A,B,C列のデータに絶対に含まれない文字にする

 (2) 統合データ作成
   新規シート(別なシートならOK)にWS1,WS2の全データをコピーする
   このデータを2個作ります。

 (3) 差分データ1の作成
   1個目の統合データについて、ユニークキーのG列で、重複削除します。
  (A,B,C列が同じデータが消えます)
   残ったデータは、行が追加削除されたデータ行なので、
   ユニークキーで検索して、WS1とWS2の各行に色をつけます。

 (4) 差分データ2の作成
     先に作った差分データ1を、2個目の統合データの末尾にコピーします。
   こんどは、この統合データを、全列を対象にして重複削除します。
   (追加削除された行は消えます。WS1とWS2で全く同じデータも消えます)
   残ったデータは、D,E,F列にだけ変更があったデータなので、
   ユニークキーを検索して、WS1の各行に色をつけます。
(´・ω・`) 2021/02/27(土) 10:34

 ごめんなさい
 私の勘違いで、上記の方法では出来ません。

 重複の削除では、ユニークなものだけを残すわけではないのでダメでした。
 恥ずかしいので、消したいところですが、自戒のため残しておくことにします 
(´・ω・`) 2021/02/27(土) 11:36

 >Office365
 スピル機能が使える環境であるならば、ですが・・
 あと、各シート内で重複はないものとするならば・・

 <Sheet2 シート 過去サンプル>ーーーーーーーーーーーーーー

  G1セル =TEXTJOIN("♪",FALSE,A1:C1)
      下にコピー

  行  _A_  _B_  __C__  _D_  _E_  _F_  ______G______
   1  A1   A11  A101                  A1♪A11♪A101
   2  A2   A12  A102                  A2♪A12♪A102
   3  A3   A13  A103                  A3♪A13♪A103
   4  A4   A14  A104                  A4♪A14♪A104

 <追加・削除履歴>ーーーーーーーーーーーーーーーーーーーーーーー
  上の1行目が削除され、3行目と5行目が挿入された。

 <Sheet1 シート 現在サンプル>ーーーーーーーーーーーーーーーー

  (1) G1セル =TEXTJOIN("♪",FALSE,A1:C1)
    下にコピー

  (2) G6セル =UNIQUE(Sheet2!G1:G4) 注:最終行の次の行に入力して、スピル

  (3) H1セル =UNIQUE(G1:G9,FALSE,TRUE)

  行  __A__  __B__  __C__  _D_  _E_  _F_  ________G________  ________H________
   1  A2     A12    A102                  A2♪A12♪A102      B30♪B31♪B32    
   2  A3     A13    A103                  A3♪A13♪A103      C300♪C301♪C302 
   3  B30    B31    B32                   B30♪B31♪B32      A1♪A11♪A101    
   4  A4     A14    A104                  A4♪A14♪A104                       
   5  C300   C301   C302                  C300♪C301♪C302                    
   6                                      A1♪A11♪A101                       
   7                                      A2♪A12♪A102                       
   8                                      A3♪A13♪A103                       
   9                                      A4♪A14♪A104                       

 条件付き書式で色付けーーーーーーーーーーーーーーーーーーーーー
   Sheet2 のA1:F4に条件式
    =COUNTIF(Sheet1!$H:$H,$G1)

   Sheet1 のA1:F5に条件式
    =COUNTIF($H:$H,$G1)

(半平太) 2021/02/27(土) 12:49


半平太様
早速ご助言頂きありがとうございました。
dataは3,000行/sheetくらいあります。
A列には必ずデータが入っています。
スピル機能はありました。
ただいまH列のUNIQUEで#CALC!やら#SPILL!やらで躓いていますので、また後程経過を報告いたします。
VBAのif関数でと思っていたのですが、やはり1つのIDを取るのがSimpleですね。

(´・ω・`)様
ご意見頂きありがとうございました。
私の疑問に興味を持っていただき、ご回答を頂けたことがとてもうれしいですし、私の課題に取り組んでいただいた事を尊敬致します。
やはりUnique keyを持たせるのが良いですよね。アイディアありがたく頂戴致します。
(じょあ) 2021/02/27(土) 15:37


 上の案でよければ、それをマクロ化して差し上げますよ。

 ちょっと外出するので、今晩になりますが・・

(半平太) 2021/02/27(土) 15:46


半平太様
原因は、デモデータが差分の無い同一のデータだったからでしたっ。
差を付けたら無事、行に色が付きました。(一方のSheetしか差が出ていないので、現在原因調査中です)

ありがたいお申し出ありがとうございます。
私にとって1日作業のマクロ化までしていただくのは大変恐れ多いので自分で頑張ってみます。

ただ、G6セル以降に行を追加せず、H列に{}でくくってUNIQUEを2列参照させる方法が分かったら教えて頂きたいです。(今試行錯誤しています。)
理由としてはマクロで関数を設定するのに最終行をできるだけ伸ばしたくない事と、条件付き書式で、G6以降の行にも色がついてしまうので、色フィルタに引っかかってしまうからです。

また、最初の一つ目の疑問(以下)のヒントがありましたらそちらもご助言頂けたら助かります。
・w1とw2を比較してA-Cのデータが一致する行で、D-Fのどれかに差がある場合、w1内の差分があった該当セルのみ(行ではなく)に色づけをする方法
(じょあ) 2021/02/27(土) 16:28


誤解しているかもしれませんが

連結したキーを作成する必要があるのでしょうか。
単純に、COUNTIFS関数で、条件付き書式ではだめなのでしょうか?

Sheet1のD列は、
Sheet2のABCと一致する行が>0 かつ Sheet2のABCDと一致する行が=0

Sheet1のABC列は、
Sheet2のABCと一致する行が=0

(マナ) 2021/02/27(土) 19:07


試してみました
 DEF列が
 =AND(COUNTIFS(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B,Sheet1!$B2,Sheet2!$C:$C,Sheet1!$C2)>0,COUNTIFS(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B,Sheet1!$B2,Sheet2!$C:$C,Sheet1!$C2,Sheet2!D:D,Sheet1!D2)=0)

 ABC列が
 =COUNTIFS(Sheet2!$A:$A,Sheet1!$A2,Sheet2!$B:$B,Sheet1!$B2,Sheet2!$C:$C,Sheet1!$C2)=0

(マナ) 2021/02/27(土) 19:25


 >COUNTIFS関数で、条件付き書式
 なるほど...普段 COUNTIFS関数 使わないので思いつきませんでした
 古い人間なので、どんどん追加される新しい関数になじんでません。
(´・ω・`) 2021/02/27(土) 19:34

マナ様
ご助言ありがとうございます。条件付き書式でできました。
Orはどうやって設定すればよいのかわからず、質問させて頂こうと掲示板に戻ったら例文も書いてくださっていて助かりました。至れり尽くせりで本当にありがとうございました。
マクロのifなどで書くのだと想定していましたが、まさかの書式設定でできるとは思いませんでした。

次のStepとして、変更のあったデータを別シートに引用しようと思っていたので、その時にIndexとMatchで連結したキーを使おうと思っていましたが、Andをうまく使えばバラバラでもできるかもしれませんね。
いろいろチャレンジしてみます。やはりExcelは奥が深いです。
大変勉強になりました。

コメントをくださった皆様、ありがとうございました。
(じょあ) 2021/02/27(土) 20:10


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.