从数据透视表中获取数据并插入特定的标题下 [英] Taking Data from pivot table and inserting under specific headers
问题描述
我最近刚刚创建了一个宏,以使数据透视表及其工作正常.
I just recently created a macro to make my pivot table and its working pretty good.
以下是数据透视表的格式.该文件称为 Sheet1
Heres how the format of the pivot table pretty much looks. This file is called Sheet1
Name Hours
Bob 100
Jones 200
Jilly 300
etc etc
现在,我有另一个名为 Company
的文件,它的顶部带有许多标头,可从公司网站中读取这些标头.不幸的是,标题中缺少名称和小时数列.这是目前的样子
Now I have another file which is Called Company
, and it comes with many headers at the top, which is read from a corporate website. Unfortunately among the headers it is missing a name and hours column. Heres how it currently Looks
Employee No. Company Code Cost Centre Level Organization Code etc etc
我希望它看起来像雇员编号名称营业时间公司代码...
名称
和 hours
应该仅从数据透视表中获取.关于如何制作脚本来执行此操作的任何想法?谢谢.
And the name
and hours
should just be taken from the pivot table. Any thoughts on how I could make a script to do this? Thanks.
更多信息
最初有一个名为 Cado
Cado 具有很多标题,即
Pers.No Employee Date Hours Time ... ...
12345 Bob June 6
123324 joe June 5
12345 Bob June 5
243123 Smith June 5.5
我为此成功创建了一个宏,该宏创建了一个名为 Sheet1
I succesfully made a macro for this which creates a pivot table named Sheet1
数据透视表如下
Pers.No Sum of Hours
12345 11
123324 5
etc etc
请注意,即使原始工作表中存在重复的具有相同名称和ID的重复项,这也将占用总和.
Notice how this takes the total sum, even though there are duplicates with the same name and Id in the original sheet.
现在还有一个名为 Company 的文件,当前文件为
Now there is one more file called Company It currenly has
Employee no. Level Organization code Manager organization code etc
我会自动获取此excel文件.
I automatically get this excel file.
我想做的是获取PIVOT表 Sheet One
,并在新的 Company File 的中间或末尾再增加两列,这很重要
What I want to do is take the PIVOT TABLE Sheet One
, and make two more columns in middle or at the end of the new Company File it doesent matter say
Employee no. Level Organization code Pers.No Hours etc etc
这两个添加的标头应该简单地从数据透视表中获取所有数据(因为它已经被排序并且是有序的,所以这比从名为 Cado 的原始文件中读取要容易)>
These two added headers should simply take all the data from the pivot table ( as it is already sorted and in order, so this would be easier than reading from the original file called Cado)
推荐答案
不确定文件"Company"是否是枢纽.但是,如果没有,那么,如果两个文件都包含员工编号,则可以简单地使用vlookup提取名称和小时数.
Not sure if file 'Company' is a pivot or not. But, if not, then if both files contain the Employee No. you could simply use a vlookup to pull in the name and hours off of the pivot.
这篇关于从数据透视表中获取数据并插入特定的标题下的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!