もうひとつのExcelを素早く使う秘訣

Excelを使うと、効率的な計算シートを作ることができますので、その分、時短が可能。しかし、それを作成する場合、作成には時間がかかります。それ一回限りの計算をするのに、そのような計算シートを作成するのはナンセンスで、何度も繰り返し使うもので作成するのが普通です。

しかし、世の中には1回限りの計算もあります。そのような計算をするときには、難しい機能や関数を使わず、小手先の操作でできることを駆使して操作したほうが早い場合が実は多いです。

本当のExcelの「うまさ」というのは、そういった何でもない単純なスキルを組み合わせて、問題を解決できるかというところにあるのかもしれません。

課題：社員の一覧表にほかの表から情報を連携する
プロセスを考える
まとめ

課題：社員の一覧表にほかの表から情報を連携する

今回はその一例として、社員の一覧表にはない、入社日情報を、他の表から組み合わせていくという課題にしました。

単純な組み合わせでは面白くないので、直接入社日が分かるようにではなく、入社日は保険番号でしか参照できないようにしました。保険番号は社員番号と組みついていますので、社員一覧表の社員番号で、保険番号一覧表の保険番号を調べ、そこから入社日の表の入社日を調べるというようにしました。

社員の一覧表

入社日の一覧表

保険番号の一覧表

プロセスを考える

他の表からデータを参照しデータを連携するには、VLOOKUP関数を使うのが王道です。それ以外にもPowerQueryを使う方法もあります。

しかし、どちらも時間がかかります。今回は社員の一覧表に生年月日を組付けるだけの目的ですので、コンパクトに操作していきましょう。

まず、社員の一覧表と保険番号の一覧表を結合するのですが、どうもバラバラなので、VLOOKUP関数で求めたくなるところです。

しかし、ここは、失敗を覚悟しつつ、保険番号表を社員番号の昇順で並べ替えてみます。

そうすると、一致していることがわかりますね。

もし、都合よくこのような状態になるのであれば、保険番号は単純にコピペすることができるのです。

今度は、入社日表と保険番号で組み合わせていきます。

比べてみると、入社年月日のほうが少なくなっているので、ここで、入社年月日がわからない人がいる、ということを覚悟しておく必要があります。実際には照らし合わせてみるまで分かりませんが、一人も保険番号が一致しない、というケースもあり得ます。

この場合は、VLOOKUP関数が最も早い手段になります。

VLOOKUP関数を使わない単純な方法では、どちらの表も保険番号で並べ替えし、見つけやすくして、一致しているところをコピペ、のようなやり方もありますが、ここは関数を使ったほうが早いですね。

ここで、気付いてほしいのは、社員一覧表に張り付けた保険番号は数字でありながら実は文字列になっていることです。対して、入社日表には、保険番号は純然たる数値で入っていることです。

VLOOKUP関数は、文字になっている数値と、純然たる数値では照合できませんので、このままだと一件も照合できなくなります。

この場合は、VLOOKUP関数の中で、文字列を数値に変換するVALUE関数で保険番号を変換するという手がありますが、ちょっとややこしい感じがします。

そこで、社員一覧表の保険番号を、数値に簡単に変換してみたいと思います。

その手段として有効なのは、「データ」タブの中の「区切り位置」機能です。

保険番号の列全体を選択します。

「データ」タブの中の「区切り位置」をクリックします。

そのまま「完了」ボタンを押します。

そうすると、全部、数値になります。

区切り位置は、特に指定しないまま実行すると、区切られた文字は「標準」の表示形式のセルに書き込んだような形になるので、文字になっている数値は全部ただセルにその数字の文字を入力してEnterキーで確定した状態の、ただの数値になるという原理です。

この状態ならVLOOKUP関数ができます。

そのVLOOKUP関数ですが、もし見つからなかったら空白にするとか「なし」のように表示したいときは、IFERROR関数を使いますね。でも関数の中に関数を入れるのは面倒な時もあります。

そこで、まずはVLOOKUP関数だけで計算します。

=VLOOKUP(D2,入社日!A:B,2,FALSE)

入社日表を参照している箇所は、正しいVLOOKUP関数の使い方では、範囲選択し絶対参照にします。しかし、列ごと選択することで、上下にコピーしても絶対その列、という指定ができるので絶対参照はいらないのです。

こうすることで下にオートフィルでコピーできます。

ないところは「#N/A」のエラーとなっています。

この状態で、「入社日」という列のタイトルを入れて、入社日の順で並び替えると、「#N/A」エラーは一か所に集まるので、そこをまとめて「なし」にすれば完成すると思います。

ちょっとここで、一つ考えたいと思うのですが、入社日はVLOOKUP関数で求めていますが、並べ替えて、その参照は変わらないでしょうか。

これは実際には変わらないのですが、これもし不安になる要素があるのであれば、その時点で解決したほうがいいでしょう。

並べ替える前に、計算式がずれないようにするには、そのセル範囲をコピーし、そのまま上書きするように値として貼り付けします。こうすることでそれぞれのセルには計算式ではなく、その答えの値が入るようになるので、並べ替えても問題なくなります。

余計な心配かもしれないのですが、不安な時は、このように先回りして処理しておきます。

今回の場合だと、E列全体を選択し、Ctrlを押したまま、C、Vと連続で押し、貼り付けすると、(Ctrl)の貼り付けオプションが表示されるので、その中から「値」を選択します。

この状態になったら、「入社日」で並べ替えをします。

「#N/A」エラーのセルを範囲選択します。

「なし」と入力し、Ctrlキーを押したままEnterキーを押すことで範囲選択した箇所すべてに「なし」が入力されます。

最後に「社員番号」の昇順で並べ替えます。

これで完成です。

まとめ

今回は、1回きりの操作をする作業において、できるだけ考える時間と操作する時間を少なくし、単純な方法を組み合わせによって、効率よく作業する方法について紹介しました。

複雑な計算式を作ることができる能力も重要ですが、このようなExcelの使い方ができるのもExcel上級者によっては必要なことでしょう。

Excelは正確に操作することが求められる中で、複雑な操作を排除して、単純化していくことで間違いを減らし正しい作業に繋げることもできるでしょう。

だからといって単純な操作だけを知っていればいいというわけではなく、並びが本当にバラバラならばVLOOKUP関数は必要になりますし、今回の区切り位置が文字を数値に一気に変換できるといった知識も必要です。もし、その中でこれをしたらどうなるだろう？と予測して不安なら先取りして対処してしまうという危険察知の感度も上げておく必要があります。

今回、紹介した、普通だったらこの方法は使わないという方法は以下の通りです。