NamedRange.Group 方法 (2007 system)

  • 项目

更新：2007 年 11 月

当 NamedRange 控件表示数据透视表字段的数据范围中的单个单元格时，Group 方法在该字段中执行基于数值或日期的分组。

命名空间：  Microsoft.Office.Tools.Excel
程序集：  Microsoft.Office.Tools.Excel.v9.0（在 Microsoft.Office.Tools.Excel.v9.0.dll 中）

语法

声明
Public Function Group ( _
    Start As Object, _
    End As Object, _
    By As Object, _
    Periods As Object _
) As Object
用法
Dim instance As NamedRange
Dim Start As Object
Dim End As Object
Dim By As Object
Dim Periods As Object
Dim returnValue As Object

returnValue = instance.Group(Start, End, _
    By, Periods)

public Object Group(
    Object Start,
    Object End,
    Object By,
    Object Periods
)

参数

  • Start
    类型：System.Object

    要进行分组的第一个值。如果省略此参数或者它为 true，则使用该字段中的第一个值。

  • End
    类型：System.Object

    要进行分组的最后一个值。如果省略此参数或者它为 true，则使用该字段中的最后一个值。

  • By
    类型：System.Object

    如果该字段为数值字段，则此参数指定每组的大小。如果该字段是日期字段，则此参数指定当 Periods 数组中的元素 4 为 true 而所有其他元素为 false 时每一组中的天数。否则，将忽略此参数。如果省略此参数，则 Microsoft Office Excel 会自动选择一个默认的组大小。

  • Periods
    类型：System.Object

    一个数组，包含七个为该组指定时间段的布尔值，如下所示：

    1 - 秒

    2 - 分钟

    3 - 小时

    4 - 天

    5 - 月

    6 - 季度

    7 - 年

    如果数组中的某个元素为 true，则针对相应的时间创建一个组；如果该元素为 false，则不创建任何组。如果该字段不是日期字段，则忽略此参数。

返回值

类型：System.Object

备注

NamedRange 控件必须是数据透视表字段的数据范围中的单个单元格。如果您尝试将此方法应用于多个单元格，它将失败（不显示错误消息）。

可选参数

有关可选参数的信息，请参见了解 Office 解决方案中的可选参数。

示例

下面的代码示例创建一个数据透视表报表，并在该报表区域的内部创建一个 NamedRange。然后，它使用 PivotTable、LocationInTable、PivotCell、PivotItem 和 PivotField 属性显示有关 NamedRange 在该数据透视表报表内的位置的信息。该示例还使用 Group 方法基于此字段中的第一个值来执行数值分组。

此示例针对的是文档级自定义项。

Private Sub DisplayPivotTableInformation()
    ' Specify values for the PivotTable.
    Me.Range("A1").Value2 = "Date"
    Me.Range("A2").Value2 = "March 1"
    Me.Range("A3").Value2 = "March 8"
    Me.Range("A4").Value2 = "March 15"

    Me.Range("B1").Value2 = "Customer"
    Me.Range("B2").Value2 = "Smith"
    Me.Range("B3").Value2 = "Jones"
    Me.Range("B4").Value2 = "James"

    Me.Range("C1").Value2 = "Sales"
    Me.Range("C2").Value2 = "23"
    Me.Range("C3").Value2 = "17"
    Me.Range("C4").Value2 = "39"

    ' Create and populate the PivotTable.
    Dim table1 As Excel.PivotTable = _
        Me.PivotTableWizard( _
        Excel.XlPivotTableSourceType.xlDatabase, _
        Me.Range("A1", "C4"), Me.Range("A10"), "Sales Table", _
        False, False, True, False, , , False, False, _
        Excel.XlOrder.xlDownThenOver, , , )

    Dim customerField As Excel.PivotField = _
        CType(table1.PivotFields("Customer"), Excel.PivotField)
    customerField.Orientation = _
        Excel.XlPivotFieldOrientation.xlRowField
    customerField.Position = 1

    Dim dateField As Excel.PivotField = _
        CType(table1.PivotFields("Date"), Excel.PivotField)
    dateField.Orientation = _
        Excel.XlPivotFieldOrientation.xlColumnField
    dateField.Position = 1

    table1.AddDataField(table1.PivotFields("Sales"), _
        "Sales Summary", Excel.XlConsolidationFunction.xlSum)

    ' Create a NamedRange in the PivotTable and display the 
    ' location.
    Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange _
        = Me.Controls.AddNamedRange(Me.Range("B11"), _
        "namedRange1")
    namedRange1.Select()

    MessageBox.Show("The NamedRange is in the PivotTable report '" & _
        namedRange1.PivotTable.Name & "' at the location '" & _
        namedRange1.LocationInTable.ToString() & "'.")

    MessageBox.Show("The NamedRange has a PivotCell type of: " & _
        namedRange1.PivotCell.PivotCellType.ToString())

    MessageBox.Show("The NamedRange is in the PivotTable field: " & _
        namedRange1.PivotField.Name)

    MessageBox.Show("The NamedRange is in the PivotTable item: " & _
        namedRange1.PivotItem.Name)

    namedRange1.Group(True, , , )
End Sub


private void DisplayPivotTableInformation()
{
    // Specify values for the PivotTable.
    this.Range["A1", missing].Value2 = "Date";
    this.Range["A2", missing].Value2 = "March 1";
    this.Range["A3", missing].Value2 = "March 8";
    this.Range["A4", missing].Value2 = "March 15";

    this.Range["B1", missing].Value2 = "Customer";
    this.Range["B2", missing].Value2 = "Smith";
    this.Range["B3", missing].Value2 = "Jones";
    this.Range["B4", missing].Value2 = "James";

    this.Range["C1", missing].Value2 = "Sales";
    this.Range["C2", missing].Value2 = "23";
    this.Range["C3", missing].Value2 = "17";
    this.Range["C4", missing].Value2 = "39";

    // Create and populate the PivotTable.
    Excel.PivotTable table1 = this.PivotTableWizard(
        Excel.XlPivotTableSourceType.xlDatabase,
        this.Range["A1", "C4"],
        this.Range["A10", missing], "Sales Table", false,
        false, true, false, missing, missing, false, false,
        Excel.XlOrder.xlDownThenOver, missing, missing, missing);

    Excel.PivotField customerField =
        (Excel.PivotField)table1.PivotFields("Customer");
    customerField.Orientation =
        Excel.XlPivotFieldOrientation.xlRowField;
    customerField.Position = 1;

    Excel.PivotField dateField =
        (Excel.PivotField)table1.PivotFields("Date");
    dateField.Orientation =
        Excel.XlPivotFieldOrientation.xlColumnField;
    dateField.Position = 1;

    table1.AddDataField(table1.PivotFields("Sales"),
        "Sales Summary", Excel.XlConsolidationFunction.xlSum);

    // Create a NamedRange in the PivotTable and display the 
    // location.
    Microsoft.Office.Tools.Excel.NamedRange namedRange1 =
        this.Controls.AddNamedRange(
        this.Range["B11", missing], "namedRange1");
    namedRange1.Select();

    MessageBox.Show("The NamedRange is in the PivotTable report '" +
        namedRange1.PivotTable.Name + "' at the location '" +
        namedRange1.LocationInTable.ToString() + "'.");

    MessageBox.Show("The NamedRange has a PivotCell type of: " +
         namedRange1.PivotCell.PivotCellType.ToString());

    MessageBox.Show("The NamedRange is in the PivotTable field: " +
         namedRange1.PivotField.Name);

    MessageBox.Show("The NamedRange is in the PivotTable item: " +
        namedRange1.PivotItem.Name);

    namedRange1.Group(true, missing, missing, missing);
}

权限

另请参见

参考

NamedRange 类

NamedRange 成员

Microsoft.Office.Tools.Excel 命名空间