[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数条件で差分比較』(じょあ)
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
(´・ω・`)様
ご意見頂きありがとうございました。
私の疑問に興味を持っていただき、ご回答を頂けたことがとてもうれしいですし、私の課題に取り組んでいただいた事を尊敬致します。
やはりUnique keyを持たせるのが良いですよね。アイディアありがたく頂戴致します。
(じょあ) 2021/02/27(土) 15:37
上の案でよければ、それをマクロ化して差し上げますよ。
ちょっと外出するので、今晩になりますが・・
(半平太) 2021/02/27(土) 15:46
ありがたいお申し出ありがとうございます。
私にとって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
次のStepとして、変更のあったデータを別シートに引用しようと思っていたので、その時にIndexとMatchで連結したキーを使おうと思っていましたが、Andをうまく使えばバラバラでもできるかもしれませんね。
いろいろチャレンジしてみます。やはりExcelは奥が深いです。
大変勉強になりました。
コメントをくださった皆様、ありがとうございました。
(じょあ) 2021/02/27(土) 20:10
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.