同じような（同じであることが期待される）2つの表の比較方法に関してまとめます。
この記事では、以下のような比較シートを作成します。
- 差分の有るセルについて、それぞれの値が確認できる（差分無しなら空文字列になる）
- 行単位で差分有無が確認できる（= フィルタで差分の無い行の非表示化できる）
- 列単位で差分有無が確認できる（= 差分の無い列について非表示化できる）
以下はサンプルとして作成したシートです。
READMEシートにも一部使い方を記載しています。
また、今回紹介するやり方について、手元のデータでは7万行弱 x 13列のシート同士という巨大な内容でも差分確認できることを確認しています。
比較シートの作り方
この記事では以下の前提を置きます（ヘッダー行でなくヘッダー列の場合などは、よしなに改変してください）。
- 比較対象のシート名はそれぞれ
before/afterとする - 両シートとも1行目はヘッダー、ヘッダー末尾のセルは必ず非空文字列
- 行数・列数共に可変（≒ 比較シートを1度作ればデータ入れ替えだけで使いまわせる）
- ただし、表の行数・列数は
before/afterで同じ - 行数差が有る場合、不足している方へ空行の追加が必要
- ただし、表の行数・列数は
今回紹介するシートでは、差分を以下のように確認できます。
A列及び1, 2行目は制御用の値で、B3から先が各セル毎の差分です。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | diff | a | b | c |
| 2 | 4 | NG | NG | |
| 3 | ||||
| 4 | NG | bar === |
=== baz |
|
| 5 | NG | qux === quux |
比較シートの構成
比較シートには制御行・列が含まれます。
全体は以下のような構成となっています。
- 1行目（
A1:1）はヘッダー - 2行目（
A2:2）は制御行-
A2セルは処理対象列制御用 -
B2:2は列中の差分有無管理に用いる制御行
-
- 1列目（
A3:A）は行中の差分有無管理に用いる制御列
先頭行・列を制御に用いるのは、行数・列数の変化の影響を受けず、なるべくデータインポートだけで比較できるようにするためです。
ヘッダー行の作成
「両シートとも1行目はヘッダー」という前提を置いているため、どちらかのヘッダー行を引き込めば対応できます。
このシートでは、以下をB1セルへ設定しています1。
=ARRAYFORMULA(before!A1:1)
処理対象範囲の計算
列数変化に対応するため、処理対象範囲はヘッダー行から動的に決めます。
このシートではこの値を何度も使いまわすため、以下をA2セルへ設定しています。
=MAX(FILTER(COLUMN(B1:1), B1:1<>""))
この式は、差分シートのヘッダー行の内空文字列でない最後の列を取っています。
「ヘッダー末尾が空文字列となっている場合比較不能」という制約は、このセルから来ています。
セル毎の差分出力
以下をB3セルに設定することで、before/afterシート全体から差分のあるセルだけを表示しています。
=LET(
c, SUBSTITUTE(ADDRESS(1, A2 - 1, 4), "1", ""),
bf, INDIRECT("before!A2:" & c),
af, INDIRECT("after!A2:" & c),
ARRAYFORMULA(IF(bf=af, "", bf & CHAR(10) & "===" & CHAR(10) & af))
)
性能的にはARRAYFORMULAを使うのがミソで、手元のデータでは7万行弱 x 13列のシート同士の差分確認ができることを確認しています。
A2 - 1しているのは、比較シートがデータシートに対し制御列の1列分ズレている分の補正です。
差分が有る場合、以下のように整形して表示されます。
${beforeシートの値}
===
${afterシートの値}
差分の有る行/列の洗い出し
行単位での差分有無は、以下をB3へ設定することで確認できます。
差分の有るセルが有ればNG表示になります。
=BYROW(
INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")),
LAMBDA(row, IF(SUM(ARRAYFORMULA(N(LEN(row))))>0, "NG", ""))
)
同様に、列単位での差分有無は以下をB2へ設定することで確認できます。
=BYCOL(
INDIRECT("B3:" & SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")),
LAMBDA(col, IF(SUM(ARRAYFORMULA(N(LEN(col))))>0, "NG", ""))
)
差分の有る行・列の絞り込み
この章で説明する内容を適用した後は、以下のような表示になります。
差分の有る列の絞り込み
条件式書式を使って色付けし、手動で非表示化（ないし縮小）できるようにします2。
具体的には、B1:1の範囲に、カスタム数式で=B$2="NG"と設定します。
実際に設定した例は以下のとおりです。
差分の有る行の絞り込み
1行目まで固定した上でフィルタを作成し、空白行を非表示とすることで、差分の有る行を絞り込むことができます。
制御行である2行目に関しても、このフィルタで弾くか、手動で非表示化できます。
具体的に指定した例は以下の通りです。
その他比較に関するノウハウ
シートのインポート時、「テキストを数値、日付、数式に変換する」はチェックしない
「テキストを数値、日付、数式に変換する」をチェックしてインポートすると、変換によって想定外の比較結果となる可能性が有るため、外すことをお勧めします。
並び順が不安定なシートもソートすれば比較できる
出力処理においてソートが不十分な場合、出力の度に結果が不安定になることが有ります。
そのような状況でも、それぞれのシートを後から複数キーでソートすれば、正確な比較が行えるようになります。
具体的には、「範囲の並べ替え詳細オプション」から、「並べ替えの基準となる別の列を追加」で、一定の並びになるようなキーを選択します。
巨大なシートの比較について
巨大なシート同士を比較する場合、最大セル数制限に引っかかってしまう場合があります。
スプレッドシートでは空セルもセル数にカウントされてしまうため、この場合は各シートの空列・空行を消す必要が有ります。
また、比較範囲を更に大きくしてしまった場合は「この数式の計算中に、計算の上限に達しました。」というエラーが出る可能性もあります。
この場合、シートを分割するといった工夫が必要となります。