2-数式を数値に修正 - 校務支援システムの森

▼ROW関数をセル内に残すこと

ROW関数は通し番号をつけるときに活用します。

しかし、セル内にROW関数を残したままにすると、思わぬミスにつながるので注意が必要です。

具体例を挙げて解説します。

「通し番号」「生徒名」「順位」の表があります。

通し番号昇順

「通し番号」列にはROW関数が入ったままです。

通し番号にはROW関数

「順位」列をキーにして昇順に並べ替えてみます。

順位昇順

A列の通し番号は昇順のままです。

通し番号は昇順のまま

通し番号「1」の生徒名が「生徒5」になっているのは、明らかにおかしいですよね。

通し番号「1」の生徒名は「生徒1」だったはずです。

このように、ROW関数をセル内に残したまま並べ替えをすると自動計算されてしまうことで通し番号が入れ替わり、ミスの原因になりかねません。

数式を残したまま並べ替えなどの処理をすると、どんなミスが起こるかわからないので危険です。

ですので、可能であれば「数式を数値データに修正」しておくべきだと思います。

そこで今回は、VBAを活用して「数式を数値データに修正する方法」を解説します。

▼数式を数値データに修正する

（ステップ1）で解説した「"1"から"100"までの通し番号を数式で入力すること」を発展させます。

前回のファイルを開いて、続きにコードを記入していきましょう。

1-方針

（ステップ1）で作成したファイルを開き、コードを編集していく。

1-作業

【Alt,F11】同時押しでVBE（マクロのコードを編集する画面）を起動する
【Windows,→】【Windows,←】各同時押しで左右に並べる

1-結果

Sheet1とVBEが左右に並んで表示されます。

なお、VBEは最後に保存した状態になっているのでStep-1のマクロ「通し番号をつける」が入力されたModule1が表示されます。

SheetVBE

2-方針

マクロ「通し番号をつける」2行目下にコードを打っていきます

2-作業（VBEにて）

2行目終わりにて［Enter］で改行する

Sub 通し番号をつける()
    Cells(2,1).Resize(100,1) = "=ROW(A2)-1" '←ここで[Enter]改行
End Sub

↓

Sub 通し番号をつける()
    Cells(2,1).Resize(100,1) = "=ROW(A2)-1" '2行目

End Sub

2行目の左辺をコピーする
3行目にペーストする

Sub 通し番号をつける()
    Cells(2,1).Resize(100,1) = "=ROW(A2)-1" '2行目
    Cells(2,1).Resize(100,1)
End Sub

コード " .value= " を打つ　※.(ﾄﾞｯﾄ)を忘れずに！

Sub 通し番号をつける()
    Cells(2,1).Resize(100,1) = "=ROW(A2)-1" '2行目
    Cells(2,1).Resize(100,1).value=
End Sub

新たに入力したコードの " = " の左側を再度コピーする
" = " の右側にペーストする
［↓］キーで選択行をずらす

Sub 通し番号をつける()
    Cells(2,1).Resize(100,1) = "=ROW(A2)-1"                         '2行目
    Cells(2,1).Resize(100,1).value=Cells(2,1).Resize(100,1).value   '3行目
End Sub

2-結果

Sub 通し番号をつける()
    Cells(2,1).Resize(100,1) = "=ROW(A2)-1"                         '2行目
    Cells(2,1).Resize(100,1).Value = Cells(2,1).Resize(100,1).Value '3行目
End Sub

2-解説

3行目のコードを直訳すると

「Cells(2,1).Resize(100,1).Value = Cells(2,1).Resize(100,1).Value」
→「たて2,よこ1のセルからたて100,よこ1に広げたセル範囲の値に、同じセル範囲の値を代入する」

となります。

「値の貼り付けをする」を再現しています。

つまり、この処理をすると数式が数値データに修正されることになります。

3-方針

マクロをステップインで実行してみましょう
※ステップインの詳細はStep-1参照

3-作業（VBEにて）

マクロ「通し番号をつける」の任意の行をワンクリック
［F8］を数回に分けて押す

3-結果

1回目→変化なし
2回目→変化なし
3回目→A列に「=ROW(A*)-1」入力
4回目→A列の数式が数値データに修正
5回目→変化なし

3-解説

［F8］3回目,4回目にかけての数式バーの変化に注目してください。

セルA2の表示は「1」ですが、3回目で数式、4回目で数値に修正されています。

これで、数式を数値データに修正するマクロを作成することができました。

（まとめ）

「数式を数値データに修正する」コードは、以下の通りです。

***.Value = ***.Value

▼留意点

1つのマクロに2つの処理

ステップ-2までで、1つのマクロの中に2つの処理を組み込むことができました。

これもExcelVBA(マクロ)の醍醐味です。

「手作業でできることをミスなく素早く処理すること」は1つの処理だけでなく、2つ以上の処理を組み込むことができます。

数式を数値データに修正するマクロの扱い

数式を数値データに修正するマクロを組み込むと、セル内に数式は残りません。

ですので、どういった計算のもとその数値が算出されたのかは作成者にしかわからなくなる恐れがあります。

一般に「属人化」と言われます。作成者がいなくなるとエクセルファイルが使えなくなる状態のことです。

しかし、「属人化」はエクセルに限ったことではありません。

そんなことを恐れていては、極論ですが、業務上のいかなる工夫もできません。

ぜひExcelVBA(マクロ)を導入し、周りの先生方に広めて仲間を作っていってほしいです。

これまで解説してきた作業は、このサイトを見れば誰にでも理解できると信じています。

ExcelVBA(マクロ)の便利さと危険さを踏まえ、校務において正しく活用されることを強く願います。

これからも、安心・安全なエクセルファイルの作成を心がけましょう。

▼次回

ステップ2までで作成したマクロのコードです。

改めて眺めて何か感じることはありませんか？

Sub 通し番号をつける()
    Cells(2,1).Resize(100,1) = "=ROW(A2)-1"                         '2行目
    Cells(2,1).Resize(100,1).Value = Cells(2,1).Resize(100,1).Value '3行目
End Sub

･･･同じコードが3つも･･･くどくないですか？

次回は「同じコードをまとめる - With - 」について解説します。