使用宏的Excel数据透视表来合并字段名的值?

I have a table with two relevant columns "src" and "on hand". In the src column I have two types of entries for each row that I want to treat in different ways when creating the pivot table.

我有一个表格，有两个相关的栏目“src”和“on hand”。在src列中，在创建pivot表时，我希望以不同的方式处理每一行的两种类型的条目。

1. For any entry with a value in the src column with a string of length > 2, I want to create a new row in the pivot table with that value.
2. 对于长度为> 2的src列中具有值的任何条目，我希望在具有该值的pivot表中创建一个新的行。
3. For the "on hand" field, I want to get the "on hand" value of the last entry under that row with a src value where the length of the field name is 2;
4. 对于“on hand”字段，我希望得到该行下最后一个条目的“on hand”值，该值具有src值，其中字段名的长度为2;

For example, where the original table reads (Name of Product = src):

例如，原始表的内容为(Product = src):

-------------------------
|Name of Product|on Hand|
-------------------------
|foo            |(1.000)|
-------------------------
|xx             |0.000  |
-------------------------
|xx             |(1.000)|
-------------------------
|xx             |7.000  |
-------------------------
|bar            |0.000  |
-------------------------
|xx             |(1.000)|
-------------------------
|xx             |2.000  |
-------------------------

The pivot table becomes:

数据透视表就变成:

-------------------------
|Name of Product|on Hand|
-------------------------
|foo            |7.000  |
-------------------------
|bar            |2.000  |
-------------------------

Unfortunately I have no sense of orientation on this and am unsure where to start. Is there a general approach to this sort of problem?

不幸的是，我在这方面没有方向感，也不知道从哪里开始。这类问题有一般的解决方法吗?

1 个解决方案

#1

You can do this with two helper columns. Enter the formulas detailed below in columns C and D and copy them down. You then run your pivot table on Columns C and D.

您可以使用两个帮助列来实现这一点。在C和D列中输入下面详细的公式，并将它们复制下来。然后在C和D列上运行pivot表。

See the screenshot:

看到截图:

Category is based on this formula:

类别基于此公式:

=IF(ROW()=2,A2,IF(LEN(A2)=2,C1,A2))

Which says:

这表示:

• if the row is 2 just take the product (because its the first row we can't look at the 'above' value)
• 如果这一行是2，取乘积(因为这是第一行我们不能看到上面的值)
• else, if the product has length of 2 take the value above
• 否则，如果乘积长度为2，取上面的值
• else, take the product - meaning bar becomes the new value because length >3
• 否则，取乘积的意义条作为新的值，因为长度>3

Closing is based on this formula:

结束基于以下公式:

=IF(C2<>C3,B2,0)

Which says:

这表示:

• if the category below is the same then we're not at the closing balance
• 如果下面的分类是相同的，那么我们就不处于期末余额。
• else, the category below is new therefore we've reached the closing balance
• 否则，下面的类别是新的，因此我们已经达到了结余