其ノ11 3日前のデータと比較する
データAとデータBの内容を比較したい、というケースはしばしばあると思います。
今回は、その話を。
例によって、下↓からダウンロードをお願いします。
開くとこんな感じ↓※1で、シートが2つあるんですよ。
1つ目のシート「最新データ」というのが、本日送られてきたデータです。 けど、実は3日前にも似たようなデータが送られてきていて、 それがシート2「3日前のデータ」です※2。
つまり、「3日前のデータ」の内容を更新したものが、「最新データ」というワケ。
でね、本日送られてきた「最新データ」でどこが変わったのかを知りたい、
というのが、今回のミッションです。
…でも、これね、入力した人がいじったところに色を塗ったりしてくれれば
こちらも全然楽なんだけど、2つのシートを見比べてみてもうおわかりですよね、
そんなふうにはなってないわけで…。
気遣いがないというか…、そんなに甘くはないというか…、世間の風は冷たいというか…。
まあ、しょうがない!差異のある箇所を、こちらで割り出してやるとしましょうか。
というわけで、どうやったら差異のある箇所を割り出せるか、考えてみてください。
あ!そうそう、表内の「注文番号」は、ユニークな記号ですよ。これヒント。
・
・
できました?
コレね、もし件数が同じだったらラクなんですよ。
並びを整えて、横に並べるだけです。
でも、2つのシートを見比べてもらうとわかると思うのですが、
そんな単純にはなっていないわけで…。悩みどころです。
・
・
どうでしょうか。
「わかんなーい」ってなる人もいるかと思うんですけど、そういう人も、
イメージは作ってみてください。イメージ。
「こうやったらできるんじゃないか」「ああしたらうまくいくかも」とあれこれ考えることが重要で、
結果として間違っていたとしても、そのプロセスが財産になりますから。
ハイ、じゃああと5分、考えてね。
・
・
では、いきましょうか。
この問題、つまるところ、
データがイコールか確かめて、イコールじゃないところが変わってる、
ってことだよね。だから、
1:vlookupで「最新データ」のとなりに「3日前のデータ」をもってくる
2:両者がイコールかどうか、イコール式を使って調べる
とすれば、イケそうですよね※3。
ハイ、ではイメージができたところで、実際に手を動かしてみてください。
「3日前の~」の列(ブルーの列)にvlookupでデータを引っ張ってくる、っていうのが、今回のキモですね。
・
・
わたしも手を動かしてみましょう。
※3のイメージ画像とはちょっとちがうかたちで…。
いや、最終的には※3のかたちにするんだけどね。
えーと…、初期状態はこれ↓※4ですよね。
で、この右側に、3日前のデータを一度ごっそりまとめてもってくるつもりです※5。
じゃあ、やってみます。
最初にですね…、念のためなんですが、其ノ10でやった書式統一をやります。
vlookupのための下準備です。vlookupのときに軸にするのは「注文番号」ですね。
なので、「注文番号」に対して、例の「メモ帳貼り付け→Excelに戻す」をしてやってください※6。
今回、緑の三角付きになるものは1コもないんですけど…(今回の「注文番号」が記号/数字混じりだからです。其ノ10参照)。
でも、「ひと手間かける」「準備に時間をかける」というのがわたしのポリシーでして。
あ!2つのシート、両方ともやってくださいね。
次に、vlookup式を入れます。1コ目の引数はこう↓※7なります。 この式は、後でコピーしたいので、 列番号だけ絶対参照にしますよ。だから$A2ね、$A2。
指定範囲はもちろん「3日前のデータ」の方ですよね。 こちらも絶対参照※8で指定します。
次は「左から何番目?」の指定。
ココで本日は、column関数を使います。
column関数の「column」とは「列」のことで、参照セルの列番号を表示する関数なんです。かたちは、
=column(参照セル)
というもの。
例えば、参照セルに「A1」を指定すると、「A」列って(左から)「1」番目の列だから、
=column(A1)の値は「1」になります。=column(B1)だと「2」です。
そしてこの式、行方向はどこまで行っても無視されるので、
例えばC列99行目のセルを指定し=column(C99)としても、返る値は「3」になります。
其ノ9でvlookup式をコピーしたとき、
「「欲しいのは左から何番目?」のところは動いてくれると楽なのにな~」と思いましたよね?
この部分を、column関数を使って実現しよう、というワケです。
ではさっそく。 こんなふうにしてみました※9。
こうすれば、この式を横方向にコピーしたときに、
2・3・4…とずれていってくれますもんね。
あ、そうそう、ここは絶対参照にしちゃダメですよ!もちろん。
あとはいつもの決まり文句「FALSE」で、いっちょうあがり!※10です。
では試しに、今入れた式を横方向にコピーしてみましょう※11。
うまい具合に機能している、ような感じですね。
では、全体にコピってあげましょう!※12
式を入れたんで、値貼りしておいてくださいね。
これでvlookup式は入れ終えたんですが、わたしはここで念のため、
「3日前のデータ」を本当にちゃんともってきてこれてるのか、1レコードくらいはCtrl+Fで確かめることにしています※13 ※14。
例えば(どれでも良いのですが)最下行の「X02888」は大丈夫かな?データをちゃんともってこれてるかな…?
なぜこういうひと手間を加えるかというと、 式を入れ間違ってないか確かめるため、なんです。 もし目検して、おかしなことが起こっている、としたら、 そもそも式の入力の時点でミスがあった可能性が高いです。 vlookupみたいな複雑な式だと、こちらがミスしてしまうこと、ないとは言えないですからね。
大丈夫そうですね。
では、※3のかたちにしていきましょう※15。
あとは、イコールでぶつけて…※16と。
「FALSE」のところが変わったところ、ですね※17。
あと、「#N/A」のところ※18が2件あるんですが、 これは、その注文番号のレコードがない=新規レコード、ということなんですね。 なので、こちらも差分として計上してあげないといけません。
これで、「3日前のデータ」から変わったところ、割り出せました、かな※19。
…でもね、この「最新データ」、162行161件です。 「3日前のデータ」の方は、161行160件です。 新規レコードで増えたのは2つ。これでは数が合わない…。 つまり、1件減ってるハズなんですね。
じゃあ、どれがなくなったのか、調べてみましょう。
こういうときは、countif関数を使います※20。
「3日前のデータ」シートに、式をいれます。
検索範囲は、「最新データ」シートの方の「注文番号」です。
こうすれば、なくなったレコードは「重複数:0」で返されるはずですからね。
ということで、なくなったレコードは、コイツだ!※21
わかりやすいように、別シートに逃がしておきましょうか※22。
以上で、差分の割り出しは終了です。もう、データ比較、怖くないですよね? 「不安だなー」と思う人は、もう1回やってみてください。