Excelの置換機能はとても便利で、普通に文字を置き換えるだけではなく、セル内の空白を削除するというときにも活躍します。その置換機能は、数式の中も置き換えることができるのです。
Excel中級者から上級者向けに、その数式を置き換える場合に有効な例をいくつか紹介します。
VLOOKUP関数の列番号違いを一斉に入力
VLOOKUP関数では、別の表からの情報を持ってくることができます。複数の項目を調べる場合は、VLOOKUP関数の入っている計算式をコピーできたとしても、列番号をひとつひとつ書き換える必要があり、とても面倒です。
とは言っても、列番号で確実に指定できるというメリットもあり、XLOOUP関数でなら列番号を意識せずにコピーできるようになっているとしても、数式の確実性から考えるとVLOOKUP関数で設定したいところです。
そこで、列番号の違うVLOOKUP関数を、置換機能を使って一気に入力する方法を考えました。
上記のような表において、セルB2からE14までに、G1からK7まである商品一覧表の中から同じ商品コードのものを探して、それぞれ商品名、メーカー、販売単価、仕入単価を求めたい場合は、VLOOKUP関数を作成します。
普通であれば、セルB2にVLOOKUP関数を作成し、それを横にコピーするとA列の参照が横にずれるのと、列番号を変えなければならないので、結局、Ｃ列からＥ列目までに計算式をそれぞれ作成し、その計算式を下にコピーする、ということをし、なんとかならないかといつも思います。
そこで、次の方法で作成します。
セルB2に「v2f」と入力します。「v」と「f」は何の文字でもよかったのですが、「VLOOKUP」の「v」と「FALSE」の「f」のつもりで設定しています。「2」はVLOOKUP関数の列番号です。
横にフィルすれば、文字と数字の組み合わせなので、文字の部分が連続データになります。
そのままの選択範囲のまま、Ctrlキーを押したままHのキーのショートカットキーで検索と置換ダイアログボックスを表示し、検索する文字列に「f」、置換後の文字列に「,FALSE)」と入力し、すべて置き換えします。
選択している範囲の内容が「v2,FALSE)」のようにVLOOKUP関数の後半になります。
再度検索で、「v」を「=VLOOKUP(A2, $G$2:$K$7,」にすべて置き換えます。
「=」で始まるようになるので、計算式として認識されるようになり、すべてのセルにVLOOKUP関数が入ります。
あとは下にフィルすれば完成です。
ポイントは、フィルで自動的に行番号を作るところ、はじめに「=」がある置換をすると計算式になりその時点で計算式が完全ではないとエラーになるので後ろから置き換えるところです。
IF関数で条件を付ける
VLOOKUP関数では、もし、検索値が空白だったら空白とするのに、IF関数を使うときがあります。
置換機能を使うことでその処理を一度に行うことができます。
上の状態では、下に４つ、商品コードが入っていないため、「#N/A」のエラーになります。もしも商品コードが空欄だったら空欄になるような計算式に置換機能を使って置き換えます。
範囲の中の１行目の数式が入っているセル範囲を選択します。
置換で「=」を「IF(A2=””,””,」にすべて置き換えます。
この時点でいったん計算式ではなくなります。
「）」を「））」に置き換えます。これで後ろのVLOOKUP関数の閉じの「）」の後ろにIF関数の閉じの「）」が入ります。
最後に「IF」を「=IF」にし、計算式として変換します。
IF関数を含んだ計算式となるので下までフィルし、計算式を上書きします。
下の商品コードのないところでもエラーにならず空白になりました。
ポイントは、置き換えると後ろにIF関数の閉じの「)」がうまく入らないので、一回計算式を外すために「=」をなくす変換をすることです。
またこの場合、B2からE14までの範囲を一気にこの方法で変換すると、IF関数の対象がセルA2にしかならないので、１行だけを置き換えてそのあとフィルで計算式を上書きする必要があるところです。
まとめ
今回の方法を見てどう思われましたか？もしかしたら人によっては手で作業したほうが早いと思った方もいると思います。全くその通りで、特にIF関数の処理は項目数が今回程度であれば、１つずつ手作業で変更したほうが早いです。
しかし、今回の方法でお伝えしたいのは、置換機能を使って手作業をしないことで、正確性につながるということなのです。機能を使って自動的に効率よくできないかなと考えれば、機械任せにできるので、その作業は正確です。大量に処理したいものがある場合と、正確性を上げたいときは、この方法をお勧めします。
また、今回の方法は、ひらめきとアイデアを生み出す想像力も必要です。計算式を置換で作ろう、修正しようと思ったら、まずその手順を考えてみること、その手順を試してうまくいかないときに、うまくいく方法を考える力が必要です。
ちょっと面倒に感じるかもしれませんが、今回のような手順を考えることはExcelのスキル向上のよい材料になりますので、ぜひチャレンジしてみてください。
置換できるものはたくさんあって、もちろん、計算の参照が間違っている場合の修正には大きく役立ちます。
今回、計算式の中身も置換できることをお伝えしましたが、このように新たにできることを発見したら、その発見したことで何に役立つのか、この記事のように具体的な例を考えてみるとよいと思います。
