【VB.NET】Computeメソッドで列の集計

DataTable.Computeメソッドで列の集計をします。

Dim obj As Object = dtSample.Compute(expression , filter )

expression‥計算式、filter ‥抽出条件


使用例

フォームにコントロールを配置

Public Class Form1
 
    ''' <summary>
    ''' サンプルデータテーブル
    ''' </summary>
    ''' <remarks></remarks>
    Private dtSample As New DataTable
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        'サンプルデータテーブル
        dtSample.Columns.Add("ItemCD", Type.GetType("System.String"))
        dtSample.Columns.Add("ItemName", Type.GetType("System.String"))
        dtSample.Columns.Add("Price", Type.GetType("System.Double"))
        dtSample.Columns.Add("UnitSales", Type.GetType("System.Double"))
        dtSample.Columns.Add("SalesAmount", Type.GetType("System.Double"))
 
        dtSample.Rows.Add("001", "商品001", 100, 8, 100 * 8)
        dtSample.Rows.Add("003", "商品003", 130, 5, 130 * 5)
        dtSample.Rows.Add("001", "商品001", 100, 3, 100 * 3)
        dtSample.Rows.Add("002", "商品002", 120, 6, 120 * 6)
        dtSample.Rows.Add("002", "商品002", 120, 5, 120 * 5)
        dtSample.Rows.Add("003", "商品003", 130, 7, 130 * 7)
        dtSample.Rows.Add("003", "商品003", 130, 1, 130 * 1)
        dtSample.Rows.Add("002", "商品002", 120, 3, 120 * 3)
        dtSample.Rows.Add("001", "商品001", 100, 9, 100 * 9)
 
        'Me.BindingSource1のDataSource設定
        Me.BindingSource1.DataSource = dtSample
 
        'Me.BindingSource1をDataGridView1へ連結
        Me.DataGridView1.DataSource = Me.BindingSource1
 
        'コンボボックス
        With Me.ComboBox1
            .Items.Add("")
            .Items.Add("001")
            .Items.Add("002")
            .Items.Add("003")
            .SelectedItem = ""
        End With
 
    End Sub
 
    ''' <summary>
    ''' SelectedIndexが変更された時
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
 
        '抽出条件
        Dim strFilter As String = Nothing
 
        '抽出条件の設定
        If Not Me.ComboBox1.SelectedItem = "" Then
            strFilter = "ItemCD = '" & Me.ComboBox1.SelectedItem & "'"
        End If
 
        'データ抽出"
        Me.BindingSource1.Filter = strFilter
 
        'SalesAmountの合計
        Me.TextBox1.Text = dtSample.Compute("SUM(SalesAmount)", strFilter).ToString
 
        'SalesAmountの平均
        Me.TextBox2.Text = Math.Round(dtSample.Compute("AVG(SalesAmount)", strFilter)).ToString
 
        'SalesAmountの最大値
        Me.TextBox3.Text = dtSample.Compute("MAX(SalesAmount)", strFilter).ToString
 
        'SalesAmountの最小値
        Me.TextBox4.Text = dtSample.Compute("MIN(SalesAmount)", strFilter).ToString
 
        'SalesAmountの行数
        Me.TextBox5.Text = dtSample.Compute("COUNT(SalesAmount)", strFilter).ToString
    End Sub
End Class

実行結果