Excel数据透视表:将值字段从行移动到列 [英] Excel pivot table: move value fields from row to column

问题描述

我正在用C#开发一个excel插件，并且必须创建一个数据透视表，我不知道该如何组织数据，应该如何组织数据.

I'm developing an excel addin in c# and have to create a pivot table, which I don't know how to organize the data, how it should be.

首先是数据源:

这就是最终数据透视表的外观:

This is, how the final Pivot Table should looks like:

但是我只能得到这样的信息，而且我不知道该在哪里更改代码:

But I only get something like this and I have no Idea where I have to how to change my code:

最后，我的代码用于排列数据源列:

Finally my code to arrange the data source columns:

/* PIVOT RowFields */
Excel.PivotField nameField = pTable.PivotFields("Name");
nameField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
nameField.Position = 1;

Excel.PivotField monthField = pTable.PivotFields("Monat");
monthField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
monthField.Position = 2;

/* PIVOT Data */
Excel.PivotField sum200Field = pTable.PivotFields("Summe 1");
sum200Field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

Excel.PivotField sum700Field = pTable.PivotFields("Summe 2");
sum700Field.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

Excel.PivotField sumDiffField = pTable.PivotFields("Differenz");
sumDiffField.Orientation = Excel.XlPivotFieldOrientation.xlDataField;

我正在使用.NET Framework 4 ...我在Internet上阅读了很多文章，但是我读到的没什么用...

I'm using .NET Framework 4 ... I read a lot of articles in the internet, but nothing I read was useful...

有人有创意吗?

更新:

@ MP24发布后，在我的代码末尾添加了这两个C#行，这些列将在名称和月份分组的基础上正确显示:

As @MP24 posted, adding these two C#-Lines at the end of my code, the columns will be display right exclusive the grouping of name and month:

Excel.PivotField dataField = pTable.DataPivotField;
dataField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

UPDATE2:

要对月份和名称列"进行分组，以下代码将显示如何:

To group Month and Name Column, the following code will show how to:

/* PIVOT ZEILEN */
Excel.PivotField monthField = pTable.PivotFields("Monat");
monthField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
monthField.LayoutCompactRow = true;
monthField.LayoutForm = Excel.XlLayoutFormType.xlOutline;
monthField.set_Subtotals(1, false);

Excel.PivotField nameField = pTable.PivotFields("Name");
nameField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
nameField.LayoutCompactRow = true;
nameField.set_Subtotals(2, false);

推荐答案

您将不得不更改数据字段的方向:

You will have to change the orientation of your data fields:

VBA代码是

ActiveSheet.PivotTables("PivotTable1").DataPivotField.Orientation = xlColumnField

这将转换为C#代码:

Excel.PivotField dataField = pTable.DataPivotField;
dataField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;

编辑:请注意，为了理解数据透视表，宏记录器将为您提供良好的提示.只需在执行所需的操作之前就开始记录宏，然后再停止宏即可.这样便可以将见解从代码转移到C#中.

Note that for understanding the pivot tables, the macro recorder will give you good hints. Just start recording the macro just before you perform your desired action, and stop the macro afterwards. Transferring the insights from the code into C# will then be easy.

这篇关于Excel数据透视表:将值字段从行移动到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！