如何在Excel中的数据透视表的父字段的前面转置所有子字段 [英] How to transpose all subfields in front of the parent field of a pivot table in Excel

问题描述

我有一个汽车零件的数据，这些零件在仓库中有多个存储位置. 我想做的就是获取零件号前面的位置，这样很容易知道特定零件号的所有位置.

I have a data of automotive spare parts with their multiple store locations in a warehouse. all I want to do is get the locations in front of the part number, so that it is easy to know all the locations of a specific part number.

当前枢轴数据如下所示

The current pivot data looks like this

我在下图中手动转置了几行，但是数据包含大约7万行，因此我正在寻找更好的解决方案

I've manually transposed a few rows in the below image, but the data contains around 70K rows, Hence I'm looking for a better solution

请参阅下表

+--------------+-----+-------+-------------+
|  Item name   | Qty |  UoM  |    Stock    |
+--------------+-----+-------+-------------+
| '0450000115  | 324 | piece | G12B04      |
| '0450000A61  | 312 | piece | G12B05      |
| '0450000115  | 336 | piece | G12B06      |
| '0450000A61  | 228 | piece | G12B07      |
| '0450000115  | 336 | piece | G12B08      |
| '0450000115  | 192 | piece | G12B09      |
| '087902E200A | 470 | piece | G12B10      |
| '087902E200A | 760 | piece | G12B13      |
| '087902E200A | 759 | piece | G12B14      |
| '0450000115  | 336 | piece | G12B15      |
| '087902E200A | 400 | piece | G12B16      |
| '087902E200A |  10 | piece | G3B32       |
| '084B410426  | 100 | piece | G3B32       |
| '087902E200A | 300 | piece | G4B08       |
| '0450000A61  |   2 | piece | GDB01       |
| '084B410426  |  60 | piece | GR.04.C.04. |
| '087902E200A | 327 | piece | HD.03.K.05. |
+--------------+-----+-------+-------------+

推荐答案

您需要使用CONCATENATEX函数创建一个度量.为此，您需要将数据添加到数据模型.您可以通过选中create pivottable对话框底部的add this data to the datamodel框来完成此操作.

You need to create a measure, using the CONCATENATEX function. For this you need to add your data to the datamodel. You can do this by checking the box add this data to the datamodel on the bottom of the create pivottable dialogbox.

右键单击可旋转字段"窗格中的表，然后选择"add measure".然后创建以下度量:= CONCATENATEX('table','table'[Stock],", ")

Rightclick the table on the Pivottable Fields Pane and select add measure. Then create the following measure: = CONCATENATEX('table','table'[Stock],", ")

现在将[项目名称]放在行"上，并将度量[StockText]放在值"上.结果应该是:

Now put [Item name] on Rows and the measure [StockText] on Values. This should be the result:

这篇关于如何在Excel中的数据透视表的父字段的前面转置所有子字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！