エクセルで関数を作成しているときに、引数によってはセルのドラッグしたときに自動で引数の数値が変化してくれないものがあります。
例えば、VLOOKUP関数だと列番号が自動で数値が変わらず固定した数値になってしまいます。10個程度の関数ならば1個ずつ手で直すことはできますが、50個や100個になると手で直すのが面倒くさいです。せっかくのエクセルなのにここは手作業なの？と思うかもしれますが、原始的な方法にはなりますが関数の中身を一度分解して、再度結合することで思うように関数を作成することができます。
作成例
下記の個人情報を整理するためのVLOOKUPを関数を分解して作成する
データはダミーのものです。
http://kazina.com/dummy/
のサイトよりデータ出力しました。
作業エリア
ここの黄色のエリアに全ての関数を作ります。
作業手順
①S5に通常のVLOOKUP関数を作ります。
=VLOOKUP(R5,$B$4:$M$21,2,FALSE)
中身
検索値：R5 固定する必要ありません
範囲：B4からM21を絶対参照にする
列番号：２
検索方法：FALSE
この状態でS5を右のセルにコピペするとこんな感じになってしまいます。
この状態ではダメなので次のステップで式を分解して式を作成します。
②式の分解
作業エリア外の余白にVLOOKUP関数を分解したセルを作成します。
分解前
=VLOOKUP(R5,$B$4:$M$21,2,FALSE)
これを・・・
S25
‘=VLOOKUP(R5,$B$4:$M$21,
＝の前にある ’ ←クオーテーションは必須です。
これがないとエクセルは式の一部として認識してしまいます。
T25
,FALSE)
を入れます。
分解前にあった列番号は次のステップで挿入します。
③列番号の作成
余白の２３行目に挿入したい列番号を数字を並べます。
④セルの結合
この並べた数字の２の下（T24）に次の式を挿入します。
＝$S$25&T23&$T$25
式の中身
$S$25→②で作成したS25です。
&T23&→２３行目に作成した列番号とS25とT25を文章として結合するものです。
$T$25→②で作成したT25です。
これを挿入すると式の計算結果が文字列として
| =VLOOKUP(R5,$B$4:$M$21,2,FALSE) |
という結果が返ってきます。
この結果が正しければ、T24のセルをコピーして23行目に作成した列番号12まで作成していきます。
するとこのようなものができあがります。
⑤セルのコピペ
これをこのまま作業エリアに値の貼り付けたくなりますが、貼り付けても式を貼り付けただけになてしまいます。
24行目に作成した式を全てコピーして、Windowsに入っているメモ帳を起動し、そのまま貼り付けをして下さい
貼り付けが終わったら、また貼り付けしたものを全て選択してコピーします。その後に、作業エリアのT5に貼り付けしてみて下さい。すると作成した式がVLOOKUP関数として認識され正しい値が返ってきます
⑥作業エリア 下のセルにコピペ
S5からAC5を選択した状態で21列目まで式をコピペして下さい。一気に作成できます。作業エリアの完成です。
関数を分解して結合するテクニックは他の関数でも使えるテクニックになります。難しそうにみえますが慣れれば、すぐに作成できるようになります。このテクニックを使いこなせば巨大な関数を作ったとき苦ではなくなります。
コメント