ULALA'Sブログ

​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​ 今回は絞り込んだデータを元に合計値を
算出する方法を記載する。

---

①Range.AutoFilter
②WorksheetFunction.Subtotal

---

指定商品に商品名を入力し、
「絞り込み＆合計算出」ボタンを押下 すると
絞り込みされた商品の合計値を算出し
合計欄に記載する。



ソース

Sub setAutoFilterAndSubtotal()

' 合計金額格納用変数
Dim sumPrice As Long

' オートフィルタクリア
Range ("B87"). AutoFilter

' 指定商品による絞り込み
Range AutoFilter 2, Range ("C85"). Value

' 絞り込み後合計値算出
sumPrice = ​WorksheetFunction.Subtotal​ ( ​9​ , Range("D88:D97"))

' 合計値セット
Range ("E85"). Value = sumPrice

End Sub

結果




WorksheetFunction.Subtotalの引数は、、
第1引数：集計方法
第2引数：範囲

となっており、 第1引数は以下のように
指定する数値で集計方法が決まっている。

1：平均値
2：数値の個数の合計
3：データの個数の合計
4：最大値
5：最小値
6：積(掛け算)
7：不偏標準偏差
8：標本標準偏差
9：合計値
10：不偏分散
11：標本分散


と、+100した値を引数に指定しても同じ結果が返却される。

1番台は、VBAからではなくExcelを手作業で非表示にした場合は計算対象にする。
100番台は、手作業で非表示した分も計算対象 外 にする。

という違いがあるが、試験にはまず出ないと思うので覚えなくて良いかと。
Excel関数として設定した場合の話だし、
そもそも手作業はVBAプログラムの範囲外だから。

​​​​​全ての集計方法で表示してみる。
​​ ※数値の個数とデータの個数の違いを見るため
りんごに"無料"と記載したデータを用意している。




ソース(抜粋)

Range ("B100"). Value = WorksheetFunction.Subtotal ( 1 , Range ("D88:D97"))    ' 平均
Range ("C100"). Value = ​WorksheetFunction.Subtotal​ ( 2 , Range ("D88:D97"))    ' 数値の個数
Range ("D100"). Value = WorksheetFunction.Subtotal ( 3 , Range ("D88:D97"))    ' データの個数
Range ("E100"). Value = WorksheetFunction.Subtotal ( 4 , Range ("D88:D97"))    ' 最大値
Range ("F100"). Value = WorksheetFunction . Subtotal ( 5 , Range ("D88:D97"))    ' 最小値
Range ("B104"). Value = WorksheetFunction . Subtotal ( 6 , Range ("D88:D97"))    ' 積
Range ("C104"). Value = WorksheetFunction . Subtotal ( 7 , Range ("D88:D97"))    ' 不偏標準偏差
Range ("D104"). Value = WorksheetFunction . Subtotal ( 8 , Range ("D88:D97"))    ' 標本標準偏差
Range ("E104"). Value = WorksheetFunction . Subtotal ( 9 , Range ("D88:D97"))    ' 合計値
Range ("F104"). Value = WorksheetFunction . Subtotal ( 10 , Range ("D88:D97"))  ' 不偏分散
Range ("G104"). Value = WorksheetFunction . Subtotal ( 11 , Range ("D88:D97"))  ' 標本分散

結果



りんごひとつに"無料"​​​​​と記載したことで、
数値の個数が2、データの個数が3と、
差異が出ていることが確認できた。

恐らく試験には、3、9あたりしか出ないと思うので、
標準偏差などは特に覚えなくても良いかと思う。