一旦数式をセットして計算させておき、計算結果の値で貼り直すサンプルです。
マクロで計算式を作成できるようになると、大きな表でも苦もなく必要な式を貼り付けていくことができます。
しかも、手操作における誤りも防げます。
但し、それにより巨大なファイルサイズになってしまうことも発生するので、以後に再計算の必要が発生しないシートについては不必要な計算式を残しておかないように心がける必要があります。
'***************************************************************************************************
' 計算式を値に貼り替えるサンプル Module1(Module)
'
' 作成者:井上治 URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev 変更履歴内容------------------------------------------------------------------------>
'03/07/24(1.00)新規作成
'12/01/22(1.01)初回修正
'20/02/23(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit
'***************************************************************************************************
' ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名 ：FormulaReplaceValues
'* 機能 ：計算式を値に貼り替えるサンプル
'---------------------------------------------------------------------------------------------------
'* 返り値 ：(なし)
'* 引数 ：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日 ：2003年07月24日
'* 作成者 ：井上 治
'* 更新日 ：2020年02月23日
'* 更新者 ：井上 治
'* 機能説明：横縦の合計を算出し式を値に置き換える
'* 注意事項：
'***************************************************************************************************
Sub FormulaReplaceValues()
'-----------------------------------------------------------------------------------------------
Dim lngRow As Long ' 行INDEX
With ThisWorkbook.Worksheets(1)
' フィルタモード解除
If .FilterMode Then .ShowAllData
' 最終行の取得(全行埋まることは想定していない)
lngRow = .Range("$A$" & .Rows.Count).End(xlUp).Row
'---------------------------------------------------
' 各行の横合計式の配置＋値貼り替え
With .Range(.Cells(2, 4), .Cells(lngRow, 4))
' ｢金額｣の横計の計算式をセット
.FormulaR1C1 = "=RC2*RC3"
' 再計算
.Calculate
' 値に置き換え
.Value = .Value
End With
'---------------------------------------------------
' 縦合計を作成
lngRow = lngRow + 1
.Cells(lngRow, 3).Value = "合計"
With .Cells(lngRow, 4)
' 縦合計式をセット
.FormulaR1C1 = "=SUM(R2C:R[-1]C)"
' 再計算
.Calculate
' 値に置き換え
.Value = .Value
End With
End With
End Sub
'----------------------------------------<< End of Source >>----------------------------------------
このサンプルを実行すると、次のようになります。
(画像をクリックすると、このサンプルがダウンロードできます)
「Range(Cells(2, 4), Cells(GYO, 4))」は、このサンプルでは「GYO」が「10」になるので、「Range("$D$2:$D$10")」と同じです。この場合は1列ですが、2つの「Cells」間で矩形のセル範囲指定を変数を使って動的に設定できます。
計算式を値に置き換えるのは「コピー」「値の貼り付け」でも可能なのですが、計算式の結果値を取り出してその値を上書きしてしまえば良いので「.Value = .Value」としています。 このサンプルは「自動計算」のままなので計算式をセットした直後に値に置き換えていますが、「手動計算」にしている場合はこの間で再計算(Calculate)を行なう必要があります。
後で計算結果の元となる値が変わることがないのであれば、計算式は値として貼り付け直しておく方が小さなファイルサイズで軽快な表になることは間違いありません。
「売上伝票」のような数量、単価等が常に変わるものは計算式は固定で置いておくのでしょうから、このサンプルはそのような考え方の場合は良くありませんが、あくまで大きなシートに対して結果を得てしまった計算式を置いたままにするとファイルサイズが大きくなるので、その対策として考えて下さい。