ラベル 計算 の投稿を表示しています。 すべての投稿を表示
ラベル 計算 の投稿を表示しています。 すべての投稿を表示

VBA　数式の挿入



数式の挿入


VBAを使ってセルに数式を入力する方法です。
VBA処理で計算するのではなくエクセルシートに数式を残します。

関数、メソッドなど
FormulaプロパティとFormulaR1C1プロパティの2種類で実行できます。
Range.Formula = "=A2+B2"
Range("A1").FormulaR1C1 = "=R2C1+R2C2"
「Formula =」以降はダブルクォーテーションで囲った中に「=数式」という形式で記述します。

「Formula =」以降変数を使用する場合はCells形式とAddressプロパティを使用します。

サンプルコード
Sub formula1()
Dim a As Long, b As Long
    
    'そのまま数式を直接
    Range("A1").Formula = "=A2+B2"
    '変数使う場合
    a = 1
    b = 2
    Range("A1").Formula = "=" & Cells(b, a).Address & "+" & Cells(b, b).Address
End Sub


解説
上記サンプルでは両方ともA1セルにA2+B2を入力しています。
（Cells形式+Addressだと既定値は絶対参照になるので"=$A$2+$B$2"になっています。
絶対参照ではなく相対参照にするには

        Range("A1").Formula = "=" & Cells(b, a).Address(RowAbsolute:=False, ColumnAbsolute:=False) & _
     "+" & Cells(b, b).Address(RowAbsolute:=False, ColumnAbsolute:=False)

としてAddressプロパティのRowAbsoluteとColumnAbsoluteをFalseにします。）

○その他
・「Formula =」あと["=…"]の[=]を入れないと文字列として扱われます。

・エクセル関数を使用する場合も基本的に同じです。
Sub formula2()
    Range("A1").Formula = "=sum(A2,B2)"
    Range("A1").Formula = "=sum(" & Cells(b, a).Address & "+" & Cells(b, b).Address & ")"
End Sub
ラベル: , ,

配列（連想配列：ハッシュ）の使い方　（Ｄｉｃｔｉｏｎａｒｙ）



構文など
Set ◯◯ = CreateObject(Scripting.Dictionary )

Key（キー）とItem（値）の組みとなる連想配列（ハッシュ）を扱うＤｉｃｔｉｏｎａｒｙオブジェクトの使用方法です。
WorksheetFunctionのSumIfのような条件付きでの集計は、対象レコード数が多いと割りと時間がかかりますが、配列化してから加算するとより高速化することが可能かもしれないです。（サンプル１）

また、セル上からKey、及びItemの値を取得する場合はセルのaddressだけでなく.Valueとして値を指定します。

名前 説明
Add レコードを追加する
Remove レコードを削除する
Exists 指定されたキー(key)が存在するかどうか
RemoveAll レコードを全て削除する
Keys キーを配列にして返す
Items 値（Item)を配列にして返す
メソッド一覧


名前 説明
Count Dictionaryのレコード数を返す
Item Itemに対するKeyを返す
Key Keyに対するItemを返す
CompareMode 文字列比較キー比較モードの設定
プロパティ一覧


サンプルコード
サンプル用Excelファイル
Sub DicTest2()
Dim Ws1 As Worksheet, DicT As Object, LR As Long, Ke, It

Set Ws1 = Workbooks("ハッシュ3ple.xls").Worksheets(1)
'Dictionaryオブジェクトをセット
Set DicT = CreateObject("Scripting.Dictionary")
'範囲の最終セル取得
LR = Ws1.Cells(Rows.Count, 1).End(xlUp).Row
    '配列化
    Dim i As Long
      '範囲内でKey取得を繰り返す
       For i = 2 To LR
       'Keyが既に配列にある場合は金額を加算する
        If DicT.Exists(Ws1.Cells(i, 1).Value) Then
            DicT.Item(Ws1.Cells(i, 1).Value) = _
            DicT.Item(Ws1.Cells(i, 1).Value) + Ws1.Cells(i, 4).Value
        Else
       'Keyがなければ配列に追加する
            DicT.Add Ws1.Cells(i, 1).Value, Ws1.Cells(i, 4).Value
        End If
       Next i
        
    '取得したKeyをセルに返す
    Ke = DicT.keys
    For i = 0 To DicT.Count - 1
    Ws1.Cells(2 + i, 8) = Ke(i)
    Next i
    
    '合計したItemをセルに返す
    It = DicT.Items
    For i = 0 To DicT.Count - 1
    Ws1.Cells(2 + i, 10) = It(i)
    Next i
    
End Sub

エクセル上のHにKey、JにItemを表示しています。
Itemの値の列を変更すれば数量、粗利のデータを取得出来ます。


関連・類似ページ
検索条件一致集計[SumIf]メソッド
ラベル: ,

丸める計算　Round関数



Round(expression ,numdecimalplaces)

引数expressionには数字・数式を指定して、numdecimalplacesには0以上の整数を指定します。
得られる結果は、最近接偶数への丸めとなり四捨五入とは異なります。
例えば、2.5の小数点第一位での端数処理の結果は2となり、3.5の小数点第一位での端数処理の結果は4となります。


例　
Sub TestRou() MsgBox round(2.5, 0) '結果→2 MsgBox round(3.5, 0) '結果→４ End Sub
ラベル: