数式を作るVBAで業務効率改善
![img_1 img_1](./24_h03_011_1.png)
事務系の業務で、Excelなどの表計算ソフトは欠かせないものとなっています。Excelで計算するとき、関数が便利です。
たとえば、SUM関数は、セルの数値を合計してくれます。Excelを有効活用するためには、関数の利用は必須といえるでしょう。
• 関数の資料例
LEFT関数の使用例を下図に示します。
[ 画像1. LEFT関数の使用例 ]
C列の各セルには、関数の数式が記入されています。たとえば、C2セルには、「=LEFT(B2 , 1)」と記入されています。
C列は、すぐ左のB列のセルに記入された文字列の、左から1文字を表示しています。たとえば「鈴木」という文字なら、「鈴」を表示しています。
• 関数の課題
関数は便利ですが、「数式が壊れやすい」という課題があります。「壊れやすい」というよりは、以下のように表現したほうがいいかもしれません。
- 意図せずに数式を壊してしまいやすい。
- 数式が変更されていても、そのことに気づきにくい。
- 数式が壊れていても、どう直していいか分からない。
関数には上記のような課題があると、日ごろExcelを利用していて、私は思います。
関数の数式は、Excelのセル上でいじることができるので、「変えるつもりはなかったのに変えてしまった」ということが、よく起こります。
数式が正しくない場合、下図のように「#NAME?」と表示されたりします。[ 画像2. 数式が正しくない例 ]
上図の場合は数式が正しくないことに気づけますが、「数式は正しいが内容の一部を書き換えてしまった」という場合も存在し、その場合は間違いに気づきにくいので厄介です。
• 改善策:VBAで数式を作る
VBAのコード内で数式を作るようにすれば、上記のような課題を改善することが可能です。以下に、#LEFT関数の使用例と同じことを行う、VBAのサンプルコードを示します。
Sub a()
For r = 2 To 5
 cellStr = "C" & CStr(r)
 shiki = "=LEFT(B" & CStr(r) & ", 1)"
 Range(cellStr).Formula = shiki
Next r
End Sub
このコードは、数式を組み立てて、組み立てたその数式を、シートのC列のセルに、記入しています。手で入力するのと、やっていることは同じです。
Sub a()
を実行すれば、数式を初期の状態に戻せます。数式をいつでも元に戻せるので、間違えて数式を壊してしまっても、とりあえず安心です。
• まとめ
VBAで数式を作ってセルに入力するメリットを、簡単な事例をもとに、ご紹介しました。VBAのコード内で数式を作るようにすれば、コードを実行するといつでも数式を初期の状態に戻せます。特に、数式のセルが大量に並んでいるような場合、VBAを利用する方法は威力を発揮します。
「関数の数式が壊れているかも」と不安になった時、数式を元のあるべき状態にすぐ戻せるので、便利だと思います。