从数据透视表中获取数据并插入特定的标题下 [英] 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屋！