当字段在水平和垂直轴上时如何创建数据透视表 [英] How to create a Pivot table when fields are on horizontal and vertical axes

问题描述

我正在使用需要在数据透视表中进行转换的表

I am using a table that I need to convert in a pivot table

但是，我在数据透视表中的某些字段在列中，而另一些字段在行中(日期).然后返回以下数据透视表:

However I have some fields from the pivot table are in the columns and some others are in the rows (Dates). which returns then the following pivot table:

有没有办法让数据透视表读取它? (我也准备开始VBA创意)

Is there a way to let the Pivot table read it? (I m also open for VBA Ideas to start)

推荐答案

您的数据当前在交叉表中交叉表"，也就是Pivots无法处理.您需要使用PowerQuery或VBA取消透视"数据.

Your data is currently "Crosstabulated" aka in a crosstab, which Pivots can't handle. You need to "Unpivot" your data using either PowerQuery or VBA.

• 如果您有Excel 2013，请使用PowerQuery aka获取并转换" 之后. Google PowerQuery Unpivot，您将发现数百个 教程，例如我的好朋友Chandoo的这篇教程 https://chandoo.org/wp/2015 /09/29/unpivot-data-with-power-query/
• 使用VBA. Google Unpivot VBA并显示数百个结果， 包括我不久前发布的快速代码: http://dailydoseofexcel.com/archives/2013/11/21/unpivot -shootout/ (在"2013年11月26日更新"标题下查找代码.)

• Use PowerQuery aka "Get and Transform" if you have Excel 2013 or later. Google PowerQuery Unpivot and you will turn up hundreds of tutorials, such as this one from my good pal Chandoo https://chandoo.org/wp/2015/09/29/unpivot-data-with-power-query/
• Use VBA. Google Unpivot VBA and turn up hundreds of results, including this blazingly fast code I posted some time back: http://dailydoseofexcel.com/archives/2013/11/21/unpivot-shootout/ (Look for the code under the —Update 26 November 2013— heading.)

这篇关于当字段在水平和垂直轴上时如何创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！