『Excel原理解説の実況中継』

トラブルシューターとしての真価が問われる出題です。あなたの「一手」は?!

講師のラボトミです。よろしくお願いします!この講義で「Excel の原理追究」とは何かを知っていただければと思います。
ではまず現状を確認してみましょう。

やりたい作業 E 列[ 検査商品ID] をキーにしてB:C 列の表[商品ID]とマッチングして対応する[商品型]を関数で引っ張る。

結果 VLOOKUP 関数はあっているのに、「#N/A(対応するものが見つからない)」というエラーが返ってきています。

先生、よろしくお願いします! VLOOKUP は確かによく「#N/A」が出ますね。実務でこれに似た現象が頻発し困っています。特に「式は合っているはずなのにうまくいかない」ことが、すごく多いんです。
そうですね。この仕組みが明示的にわかっている人は実務家全体の10% もいないと思います。そもそもの原理が見えていません。
今回は次の3つがポイント。これを見てピンと来ない方はExcel のトラブルシュートが、場当たり的なものになっているはずです。

1.データタイプの把握・・・・そのセルのデータタイプが「数値」なのか「文字列」なのかはっきり把握できていること。
2.ロジカルテスト(論理式)とデータタイプ・・・データタイプが違っていてはExcel は「イコール」とみなさない。
3.データタイプの変換関数・・・「数値」→「文字列」への変換ができる関数は?

まず「データタイプ」という言葉が初めての方がいらっしゃると思います。Excel では各セルの書式と入力したデータ(値)を元にして、データタイプが決定されます。データタイプはいくつかありますが、ここでは2つ「数値」と「文字列」を覚えましょう。
たとえば「あいうえお」とセルに入力すれば、そのセルのデータタイプは「文字列」とです。「345」と入力すれば通常はそのセルのデータタイプは「数値」です。
「文字列」というのは、普通の言葉みたいなものが入力されたらほとんどこれに該当するんですね。むしろ「数値」とみなされないものは大抵「文字列」だと考えればいいのかもしれませんね。

その理解で大丈夫です。では改めて右のシートを見てくださ い。これを見て、B 列とE 列のデータタイプが一瞬で判断で きなければいけませんよ。B 列、E 列それぞれのデータタイプ がわかりますか?
まずB列の「データタイプ」を把握しましょう。これは「文字列」 と推測できます。今回B 列をそう考えた根拠は2つあります。

 

[根拠1] B 列の「0058」が「左揃え」になっていること。「 文字列」のデータタイプをとっているときは、セルの中のデータは左に寄る、つまり「左揃え」に表示される。

[根拠2] B 列の各セルの左上に「エラー」を知らせる緑色の三角(アラート)が付いている。「数字」が「文字列」化しているときによくこのエラー表示が出る。

システムからの出力(CSV 形式)されたID などのデータは、冒頭の「0」が脱落するのを防ぐため「数字」が「文字列」としてExcelに出力されるようになっていることが多いのです。このようにシステムから出力された数字を見たら、今「数値」か「文字列」か、どちらのデータタイプをとっているかを判別できなければいけません。数値は右揃え、文字列は左揃えとして出力されるのが一般です。

●[1.データタイプの把握] データタイプはそれぞれどうなっているか?

なるほど。「数字」は、「数値」と「文字列」という2つのデータタイプに分かれるんですね。左に寄っていれば「文字列」の可能性が高く、右に寄っていれば「数値」の可能性が高いんですね。
「数字」は2つのデータタイプを取りうる
①データタイプ「数値」・・・ 通常はこちら。右揃え表記。
②データタイプ「文字列」・・・ 冒頭の0を表記する目的で数字にこの設定が
掛けられることが多い。左揃え表記。
「数字」と「数値」は違うので注意!
「数字」は一般用語、「数値」はデータタイプの分類を表す用語です!
そうです。先程の2つの根拠より、B 列はほぼ間違いなく「文字列」化した数値だと予想されるでしょう。
ではE 列についてはどうでしょうか。普通は「0058」と入力すると、[58」に変換されてしまいますから、「0058」と表記されている
のは何か設定がかかっていることが想定されます。
では、今回も「文字列」という設定が掛かっているのでしょうか。
いいえ、結論としてはE 列は「数値」です。根拠は2つあります。
① E 列の数字が「右揃え」となっている。
② 結果的にVLOOKUP がエラーを返すこと。
②についてはあとで、データタイプとロジカルテストのところで意味がわかると思います。
ここで疑問が生じるのは、どうしてE 列が「数値」なのに冒頭の0 が脱落せずに済んでいるのかということです。
実は、このExcel ファイルをいじっている担当者は、セルの書式設定から「0000」という「ユーザー定義」を掛けたのです。そうすれば「58」と入力しても、「0058」と表記されて0 が消えませんね。
なるほど。「ユーザー定義」から「0000」と設定をかけると、「58」と入力したときに「0058」と表記されるようになるんですね。
そして、データタイプは「数値」を維持したままだからセルの右側に寄っていますね。そこがさっきまでの「文字列」化との違いですね。
そうです。もしですね、「自分は上記のようなユーザー定義を掛けない」という方がいらっしゃっても、「前の担当者」や「別の担当者」
から引き継いだExcel シートでは一部が上記のような設定が掛かっていることは起こりうることです。とりあえず見た目だけでも「00」が58 の頭につくようにと工夫した結果、E 列に上記のような設定をかけてしまいます。見た目は確かに問題ありませんが、関数の処理では今回のように「マッチングしない」という問題を引き起こします。では、ここまでをまとめましょう。

● B 列のデータタイプ=「文字列」
・・・・「左揃え」で、エラーのアラートもあるので「文字列」化 しているとわかる。
● E 列のデータタイプ=「数値」
・・・・右側に寄っているため「数値」だと思われる。
「00」がついているのは「ユーザー定義」により表示上、頭に「00」がつくように設定されているから。

●[2.ロジカルテストとデータタイプ] Excel における「イコール性」とは?

ここまででB 列とE 列のデータタイプが違うことはわかりました。
でも、それとVLOOKUP がエラーになることの関係はあるんですか?
上記の「E6 セル」の数式を見てください。これはロジカルテストとか論理式と呼ばれるものです。
まず、最初の「=」は「今から数式を入力するよというサイン、すなわち飾り」ですから気にしないでください。
その次の部分で「B3 = E3」を検証しています。つまり「左辺と右辺はイコールか?」を検証しているのです。ここでは「B3 セル」と「E3 セル」がイコールかを検証して、もし等しければTRUE を、等しくなければFALSE を返します。
普通に考えれば、B3 セルとE3 セルは、いずれも「0058」と表記されているので「=」つまり「TRUE」だと思いますが、嫌な予感がしてきました。
結果は「FALSE」です。つまりロジカルテストにおいては見た目が同じ「0058」でも、「データタイプ」が違えば返り値はFALSE、つまり、「イコール性はない」と判断されるのです。この単純なロジカルテストにおいてFALSE を返すような二者は、VLOOKUP 関数やその他の検索関数でも、マッチングさせることができません。ここに多くのマッチング作業で起こるトラブルの原因があります。

データタイプが違えば、Excel は二者を「違うもの」とみなす。

どんなに見た目が似ていても、データタイプが違えば、「ニ者は等しくない」とみなされる。
ロジカルテストでマッチングしない(FALSE になる)ものは、VLOOKUP でもマッチングしない。

●データタイプを変換する関数

つまり、この問題で「VLOOKUP」がエラーを返した理由は、B 列が文字列なのに、F 列は数値だから。ということになるんですね!
なるほど!で、結局問題はどうやったら解けるんですかね。この出題では「=VLOOKUP(○○,○○,2,False)」をうまく埋めることが要求されていましたが、データタイプが違うものをどうやってVLOOKUP するんですか?
データタイプを変換できる関数を使います。VLOOKUP 関数の第一引数は、現状で「数値」なんですよね?それを「数値→文字列」と変換してくれる関数はTEXT関数です。そして数値を文字列に変換できる関数として一般的なものは、「TEXT 関数」です。
VLOOKUP の第一引数において、TEXT(E3,”0000”) と記述することで、E3 セルの0058 という「数値」を「文字列」に変換できます。

●「元データ」自体をいじるという発想は事故のもと

関数により「数値→文字列」の変換を行うには「TEXT 関数」を使えばいいんですね。データタイプなんて全く意識したことがありませんでした!すごく大事なものなんですね!
ところで、例えばB 列のエラーチェックのアラートをクリックして一括で「数値に変換」としてもよかったのではないですか?(下図)
確かに、下図のようにエラーチェック機能から「数値に変換する」とやれば[商品ID」は数値化して、当初のVLOOKUP 関数で動きます。けれど、B 列の商品ID を他の用途でマッチングさせたくても、もう00 は冒頭に付いてないですね。一旦、数値に変換してしまったことで、後からB 列データを再利用することができなくなります。
また、エラーチェックオプションから手作業で「数値に変換」をしている場合には、作業が「自動化」されていないため、データ追加の都度手作業をしなければならない。ということになります。少量のデータならミスはないかもしれませんが、毎回手作業の領域があると「変換し忘れ」が起こります。
だから関数内部での処理の方が、自動的で正確なため良い手筋と言えます。問題文だけでは周りの状況や周辺作業はわかりませんが、現状の「最善手」は「VLOOKUP 関数の中にTEXT 関数を入れる」というものになります。
エラーチェックで数値に変換してもできるが、「00」が脱落したら後で別の作業をするときに、B 列のデータが正規の[ 商品ID](00 がついているもの)とマッチングしなくなる。

上の図のように、あとで別の用途に使う可能性もあるため、
むやみに元データ自体を「数値化」すべきではない。

元データ自体を変換することの デメリット
①データの別用途での利用時に困る。
②追加データがあったときに、その都度手動で変換作業をする必要が生じる。

●Excel の最善手を追究する

いつも、その場しのぎで「とりあえず今触っている関数が動けば良い」と思って作業して、後から「困ったな」となるんですよね。
先生のおっしゃるように、経理実務では「元データを再度別の用途で利用」したり、「追加レコードが生じて、その都度手動で調整」みたいなことをいつも繰り返しているような気がします。
そうですね。各担当者の個人のノウハウとしては、そういう失敗を解決するやり方が蓄積されているかもしれませんが、研修、講義でお会いする受講者の皆様のお話をきくと、多くは「自己流」であり、「曖昧な手筋によって、毎回試行錯誤を繰り返し、結局手作業がその都度入る」という状況に思われます。私は、日本の多くの企業では、そういうExcel の曖昧な手筋を総括して洗練させ、「一本筋の通ったExcel 最善手」をスタンダード化させる時期に来ていると考えています。そのためには皆さんがExcel の「原理」と「操作感」を見つめなおし、再構築し、最善手に習熟するというプロセスが必要です。
今回扱ったような内容を一旦整理してきちんと見つめなおすことによって、日々「右往左往」していたExcel 処理をひも解き、一本道の最善手を誰もが採れるようになる。教室での講義では、短時間でそういったレベルに到達できるようになっています。日本のビジネス界のスタンダードとなるようなExcel の最善手を、皆様とともに探究していきたいと思います!
講義の前はExcel の「原理」って何?とか「今さらExcel で習うことなんてあるかな」と思っていましたが、Excel にある程度慣れた今こそ、体系的な学び直しが必要だと痛感できました。少し難しかったですが、初めてVLOOKUP の失敗原因が見えた気がします。
教室講義では実際にExcel を動かしながら、動的に理解していくのでもっとラクに楽しく理解できますよ。Excel 力は業務効率化と直結します。今回は「原理」を中心に扱いましたが、さらに「操作感」や「高度な関数の組み方」も学んでいくことで飛躍的にレベルアップできます。部署の全員が一段階上のレベルに到達すれば、かなりの作業が高速で正確になります。教室で皆さんとお会いできれば嬉しいです!

この記事が気に入ったら
いいね ! しよう

Excelラボのメルマガ登録

いますぐメルマガ登録して、「Excelラボ」の毎月の講座日程や、新規講座の限定モニターの開催情報、Excel勉強会情報を直接メールで入手しましょう。