項目整理でよく使う関数(Excel,スプレ) #Excel

目的->項目の差分を知りたい

基本的にはVLOOKUPを使うことが多いと思いますが、IF文と組み合わせると少し見やすくなります。
以下の状況を想定しましょう。

sample.xlsx

|A列|B列|C列|D列|
|   |  a|  b|   |
|   |  b|  a|   |
|   |  c|  e|   |

この際、B列とC列の差分を抽出したい場合を考えます。

A列にB列にあってC列にないものを
D列にC列にあってB列にないものを

sample.xlsx

|A列|B列|C列|D列|
|   |  a|  b|=VLOOKUP(C2, $B$2:$B$100, 1, false)
|   |  b|  a|   |
|   |  c|  e|   |

vlookup.xlsx

=VLOOKUP(C2, $B$2:$B$100, 1, false)

D2にだけ関数を入れましたが、これをA列、D列全てに適用すれば、以下の結果が得られます。

result.xlsx

|A列 | B列|C列| D列|
|   a|  a|  b|   b|
|   b|  b|  a|   a|
|#N/A|  c|  e|#N/A|

VLOOKUPにて結果が取得できない場合は、エラーになります。
これを利用して、以下の関数に改良してみましょう。

=IF(IFERROR(VLOOKUP(C2, $B$2:$B$100, 1, false), "error")="error", C2, "")

エラーの場合はC2を、そうでない場合は空文字を。
すなわち、エラーの場合=値がない場合でのみ、その項目が表示されます。
以下のようになります。

sample.xlsx

|A列|B列|C列|D列|
|   |  a|  b|   |
|   |  b|  a|   |
|  c|  c|  e|  e|

これにより、本来抽出したい差分のみを視覚的にとらえやすくなります。
ちょっと関数のネストが深くなってしまいますが、やりたいことが分かっていれば特に難しいことはしていないので
簡単に使える便利な関数だと思います。

コピペでなくSELECTしよう

B列C列の差分を抽出してきましたが、「そもそもB列の情報が変わった、増えた」など
解析対象のデータそのものが変更になる場合もあるでしょう。
特に要件が煮詰まりきってない状態で項目だけでもざっくり決めている状態なんかでは
むしろ既存システムや類似システムでの項目と、これから作るシステムの項目を比べたい！なんてことも多いと思います。

なので、精査対象の項目群がコロコロ変わってもいいように
B列C列をコピペではなく、常に最新の状態にしておく必要があります。

2つの方法があります。
・そもそもB, C列が本体で、追記/修正するのはB, C列である
・別シートので追記/修正をするが、QUERY関数によりB, C列にSELECTする

QUERY関数はGoogle Sheetsにある関数でExcelでは使用できないことが残念ですが
スプレが使えさえすればものすごく便利なので記載します。

query.sheets

追記/修正される、DBマスタがわりになる、項目一覧を
シート名「項目一覧」のA列とB列に記載してあるとします。

シート名「項目精査」にて、以下を記述することで
1つの数式で全ての情報を「同期的に」「全量」取得することが可能です

=QUERY('項目一覧'!A:B, "SELECT A, B LABEL A '', B ''", -1)

これをB2に書く感じですね

項目精査シート.sheets

|A列|B列|C列|D列|
|   |=QUERY('項目一覧'!A:B, "SELECT A, B LABEL A '', B ''", -1)|  |   |
|   |  |  |   |
|   |  |  |   |

ちなみにSQK部分ですが、WHERE句指定やORDER BY指定をすることでより便利になります
例えば

項目一覧シート.sheets

|A列|B列|       C列|
|  e|  a|表示しない|
|  d|  b|         |
|  a|  a|表示しない|

となっていた場合

query.sheets

=QUERY('項目一覧シート'!A:C, "SELECT A, B WHERE C <> '表示しない' ORDER BY A, B DESC LABEL A '', B ''" -1)

C列に入力規則でもつけて表示しないフラグの役割を持たせたとして
上記のようなQUERY関数にすれば、表示しないフラグを無視したレコードのみを、降順でSELECTしてくれます
これが勝手に項目精査シートのB, C列に反映されるうえに、
項目一覧シート側で表示しないフラグを変えれば、項目精査シート側でもレコードが変わり
VLOOKUPの結果まで勝手に変わるため、なにもメンテをする必要がないものになります。

名称違うが同じ項目->ババ抜き法

カップとグラス、時計と腕時計
場合によりますが、同じ意味としてとらえるはずなのに違う言い方をしている項目もあると思います。
流石に判断基準が曖昧だと自動化しきれないのですが、IF文を加えたVLOOKUPを見てみましょう

sample.xlsx

|    A列|      B列|    C列|    D列|
|  日にち|   日にち|      b|       |
|       |        b|   日付|   日付|
|  JOKER|    JOKER|      f|     f|

B, C列の差分を抽出しようとすると、このようになりますが
A, D列のみに注目してください

sample.xlsx

|    A列|    D列|
|  日にち|      |
|       |   日付|
|  JOKER|     f|

このうち、何と何が同じ意味かは人間が知っています。
なのでAさんとDさんでババ抜きをさせてあげましょう

日にち　と　日付　が同じカードなので、どちらも捨てます
残ったf と JOKERがババ、すなわち差分になります

差分が多い場合は、「日にち」を「日付」に全置換するなどして、うまいこと「名称が違うが同じ項目」の名称をそろえてあげましょう
置換する関係上、差分が上がった項目にのみ作業をしたいと思いますので
A, D列のみを別シートにコピーしたうえで、サクラエディタなどに張り付けて作業することをお勧めします。