I have some data that is displayed in a pivot table, from that pivot table I currently copy and paste them in another table with the corresponding current day, with so many operation numbers it takes about 1hr to complete this process daily, I'm trying to make a macro in which will help me in making this process in less time
我有一些数据显示在一个数据透视表,数据透视表的目前我复制粘贴在另一个表与相应的电流,有这么多操作数字大约需要1小时每天完成这个过程,我想做一个宏观这将帮助我在更短的时间内让这个过程
This is the pivot table in which I copy the data
这是我复制数据的pivot表
and here is where I paste it (in this table I use it to create some graphs)
这里是我粘贴的地方(在这个表中，我用它来创建一些图形)
Both tables consist of many different numbers of operation, as you can imagine this is very tedious.
两个表都包含许多不同数量的操作，您可以想象这是非常乏味的。
Any help or ideas that can help me solve this is appreciated, I'm willing to try some other alternatives like fomulas, vba (preferred), even closedxml for c#.
任何可以帮助我解决这一问题的帮助或想法都是值得赞赏的，我愿意尝试一些其他的替代方法，如fomulas、vba(首选)，甚至c#的closedxml。
I dont know if im explaining my self? This is my logic.
我不知道我是否在解释我自己?这是我的逻辑。
- Look for operation number
- 寻找操作数
- Copy Sum of "Sx"
- 复制的“Sx”
- Look in other sheet for the same operation number.
- 在其他表中查找相同的操作数。
- Paste value in corresponding day.
- 在相应的日期粘贴值。
- Look for same operation number with next "Sx"
.
.
- 查找与下一个“Sx”相同的操作数。
- End
- 结束
Right now I'm just focusing on Operation1, since in Operation2 I use another sheet in which I do the same thing. (will probably use the same code as Operation1).
现在我只关注Operation1，因为在Operation2中，我使用了另一张表，我也做同样的事情。(可能会使用与Operation1相同的代码)。
To clarify I'm not asking for someone to just give me the solution (although it is appreciated ), but any guide on this is helpful.
为了澄清这一点，我并不是要求别人给我解决方案(尽管我很感激)，但是任何关于这个问题的指导都是有帮助的。
1 个解决方案
#1
1
You do not need VBA to do this. Use GETPIVOTDATA.
这样做不需要VBA。使用GETPIVOTDATA。
Consider below replicated data:
考虑下面的复制数据:
In above sample, I copied some of your data in your screen shot, made a pivot out of it, put it in the same sheet and also put the table you need to populate below it. Then I use GETPIVOTDATA. I feel I need to show you how it is done even though it is well explained in the link I posted.
在上面的示例中，我在屏幕快照中复制了您的一些数据，并将其作为轴心，将其放入相同的表中，并将需要填充的表放在它下面。然后我使用GETPIVOTDATA。我觉得我需要向你展示它是如何完成的，尽管在我贴出的链接中有很好的解释。
So we use this formula:
我们用这个公式:
=GETPIVOTDATA("Sum of "&$I18,$I$2,"Date",J$17,"Opt",$I$16,"Id",$J$16)
= GETPIVOTDATA(”和“& I18美元,我2美元,美元“日期”,J 17美元,“选择”,我16美元," Id ",$ J $ 16)
in Cell J14. Take note that instead of using Day 1, Day2.. etc on your destination table, we used the actual date. Why? Because we need that in our GETPIVOTDATA Formula. Then copy the formula to the rest of the cells.
在细胞J14。注意不要使用第一天，第2天。在你的目的地表上，我们用了实际的日期。为什么?因为我们在GETPIVOTDATA公式中需要这个。然后将公式复制到其他单元格中。
Result:
结果:
Now, GETPIVOTDATA errors out if it does not find anything which matches the criteria you supplied, so you might want to incorporate error handling using IFERROR statement.
现在，如果GETPIVOTDATA没有发现任何与您提供的条件匹配的内容，那么您可能需要使用IFERROR语句合并错误处理。
Final Formula:
最后的公式:
=IFERROR(GETPIVOTDATA("Sum of "&$I18,$I$2,"Date",J$17,"Opt",$I$16,"Id",$J$16),0)
= IFERROR(GETPIVOTDATA(”和“& I18美元,我2美元,美元“日期”,J 17美元,“选择”,我16美元," Id ",$ J $ 16),0)
Although you prefer VBA, I don't think a better approach than the built-in Excel functionality is more suitable in your case.
尽管您更喜欢VBA，但我认为没有比内置Excel功能更好的方法更适合您的情况。
#1
1
You do not need VBA to do this. Use GETPIVOTDATA.
这样做不需要VBA。使用GETPIVOTDATA。
Consider below replicated data:
考虑下面的复制数据:
In above sample, I copied some of your data in your screen shot, made a pivot out of it, put it in the same sheet and also put the table you need to populate below it. Then I use GETPIVOTDATA. I feel I need to show you how it is done even though it is well explained in the link I posted.
在上面的示例中，我在屏幕快照中复制了您的一些数据，并将其作为轴心，将其放入相同的表中，并将需要填充的表放在它下面。然后我使用GETPIVOTDATA。我觉得我需要向你展示它是如何完成的，尽管在我贴出的链接中有很好的解释。
So we use this formula:
我们用这个公式:
=GETPIVOTDATA("Sum of "&$I18,$I$2,"Date",J$17,"Opt",$I$16,"Id",$J$16)
= GETPIVOTDATA(”和“& I18美元,我2美元,美元“日期”,J 17美元,“选择”,我16美元," Id ",$ J $ 16)
in Cell J14. Take note that instead of using Day 1, Day2.. etc on your destination table, we used the actual date. Why? Because we need that in our GETPIVOTDATA Formula. Then copy the formula to the rest of the cells.
在细胞J14。注意不要使用第一天，第2天。在你的目的地表上，我们用了实际的日期。为什么?因为我们在GETPIVOTDATA公式中需要这个。然后将公式复制到其他单元格中。
Result:
结果:
Now, GETPIVOTDATA errors out if it does not find anything which matches the criteria you supplied, so you might want to incorporate error handling using IFERROR statement.
现在，如果GETPIVOTDATA没有发现任何与您提供的条件匹配的内容，那么您可能需要使用IFERROR语句合并错误处理。
Final Formula:
最后的公式:
=IFERROR(GETPIVOTDATA("Sum of "&$I18,$I$2,"Date",J$17,"Opt",$I$16,"Id",$J$16),0)
= IFERROR(GETPIVOTDATA(”和“& I18美元,我2美元,美元“日期”,J 17美元,“选择”,我16美元," Id ",$ J $ 16),0)
Although you prefer VBA, I don't think a better approach than the built-in Excel functionality is more suitable in your case.
尽管您更喜欢VBA，但我认为没有比内置Excel功能更好的方法更适合您的情况。