エクセルのなかで、これができればエクセルが使える！と言われたりする技術があります。
それがVLOOKUPです。
VLOOKUP自体はそこまで難しい関数式ではないかと思います。
しかしながら、その関数式を複数のセルへ埋め込んでいく際にうまく行かずに挫折してしまったり、作成に時間がかかることがあり、VLOOKUPは難しいと思われているようです。
VLOOKUPの仕組みをまとめると
（１）定義された任意の表の中から検索をする。
（２）任意の表の一番左の列の中から任意のセルに入力されたものを検索する。
（３）一番左の列でヒットしたところの行から任意の行の値を表示させる。これは一番左から数えて何番目のセルであるかを指示して表示させる。
では具体的に見てみましょう。
C13のセルにVLOOKUPで「みかん」と表示するように設定しています。この式を下のセルにも入れるため、セルの右側の下部の角にマウスをあわせて、黒十字になったときに下にドラッグして、式をいれるが、うまく同期してくれない。
なぜならば検索する値がずれたり、検索する表がずれたりするためだ。
そのため、検索する値と、表は固定をして自動で変更されないようにしてからドラッグして下のセルに入れていく必要がある。値の固定は、固定する式の内容を指定し、そこでF4を押す。Macの場合は「command ＋ T」を押す。
しかしながら、それでも、うまく同期できない。
なぜならば、何番目に表示させるセルかを設定する数値が今度は反映されないからだ。反映されず、C13と同じ式がC14、C15、C16のセルに表示される。これは下部の画像をみていただければわかる。
つまり、通常の関数式としてはうまくエクセル側で考えて式を反映してくれないのだ。そのため、この式を手直しする必要がある。式をクリックして、2となっているところを順次、3,4,5と入力し直す必要がある。
これがとてつもなく面倒くさい。
また、この場合は項目が同じ列に表示させるが、そうでない場合、コピーして貼り付けると式が壊れてしまうことがある。これは逆にエクセル側が勝手に考えて反映された結果、式が壊れてしまうというもので、なかなか厄介である。
そのため、効率よく、簡単に式を編集していくためには、この式を一旦、文字列として認識させて、式を編集し、その後改めて関数式として認識させることが最も手っ取り早い方法になる。
もちろん手打ちで変更しても構わないが、項目が数十項目になる場合、手打ちだと、ずれてしまう可能性が高くなる。
今回取り上げたいポイントはまさにここである。
VLOOKUPが効率的にできないNG方法とは、
式の編集や修正をエクセルの自動変換にそのまま頼ったり、手打ちで編集をしてしまいがちな点だ。
これをしてしまうとかなりの時間がかかる。そして、タイプミスなのでうまく式を組み込むことができなかったり、一見、うまくできているようで、実は式がずれていたということを引き起こしてしまう。
今回はこのNGポイントを回避する編集方法を紹介したい。
そこでまずは、関数式の＝を#と置換する。気をつけるポイントとして、置換する式のエリアのみを指定して置換を実行する点だ。１つのセルのみを指定して「すべて置換」を クリックすると他の式も置換してしまう。
編集したい式のみを指定してから、「すべて置換」をクリックすると指定した範囲飲みが置換される。
そして、その後、「,0)」を全角の「＄」などの式では出てこない文字に置き換える。これをしないと、「,0)」にエクセルが反応して,「1)、,2)、,3)」と変換されてしまうからだ。
このように＝を＃に置換して文字列として認識させることができれば、式は以外と簡単に編集し、エクセルの自動変換で適切な式に作り変えることができる。大量の式を変換する際はこの方法を使用することが明暗を分けるといっても過言ではない。ちなみに文字列であるので、置換以外にも、「＆」を使用して、別セルにある文字列と合体させて式を編集するという方法もある
例えば、「#VLOOKUP($B$10,$A$2:$E$6,2」という式をA1にいれ、B1に「,0)」 と入力し、C1に「＝A1$B1」と入れる方法や、C1に「＝A1&”,0)”」と入力する方法などがある。ここは適宜、工夫することによって編集効率を高めて行けばよいだろう。
そして、文字列ですべての式の編集が終了したら、文字列を式に戻すため、「#」と「＝」を置換する。
これによって、適切な式がそれぞれのセルに入る。
注意点として、式に戻した後は他のセルへ移動するのが困難なので、文字列のときに表示させたいセルに貼り付けておくことが必要になる。式に戻すのはすべての編集が終わった最後に行うようにする。
これにより、かなり作業効率が高まり、作成ミスが少なくなるのではないかと思う。
この、式を文字列に変換して編集するという方法は、VLOOKUPだけではなく、他の関数式でも可能なので、
関数を複数組んでいく際には随所で使っていきたいテクニックになると思われる。
置換というテクニックはこのように式を編集していく際にとても重要な役割を果たす。
このテクニックは意外と知られていないのが残念であるが、考え方をマスターしていくと業務効率は飛躍的にあがるだろう。