需要 Excel 数据透视表的帮助
我正在使用 Excel 2007，需要创建数据透视表的帮助。
我的 Excel 工作表看起来有点像这样
姓名 日期 团队位置
John 2011-05-01 Project NY
John 2010-10-12 Information NY
John 2010-02-04 Development CA
Sam 2011-05-01 Development CA
Sam 2010-01-01纽约项目
Sam 2008-01-01 程序员 NY
Brad 2011-04-03 纽约项目
Brad 2009-01-01 信息 NY
Brad 2007-01-01 设计师 CA
现在，如果我根据上面的数据创建一个数据透视表，并在“日期”上放置一个过滤器，以查看谁在又名“位置”下工作什么“团队”，假设在“2010-01-01 到 2011-12-31”之间，
那么它将计算“John”三次，“Sam”两次， “布拉德”一次。总共 6 名员工在“2010-01-01 至 2011-12-31”期间工作
现在我想删除这些重复项，这样如果“John”被计算一次，即使他切换到“John”，他也不会再被计算在内。不同的“团队”或“位置”，这样我就可以计算“2010-01-01 至 2011-12-31”期间的员工总数，没有任何重复。
据我所知，如果我想编辑数据透视表并创建唯一值来删除这些重复项，我需要添加另一列。但我需要帮助创建此专栏。
有人可以帮我吗？
非常感谢大家！
I am working on Excel 2007 and I need help with creating a pivot table.
My excel sheet looks some what like this
Name Date Team Location
John 2011-05-01 Project NY
John 2010-10-12 Information NY
John 2010-02-04 Development CA
Sam 2011-05-01 Development CA
Sam 2010-01-01 Project NY
Sam 2008-01-01 Programmer NY
Brad 2011-04-03 Project NY
Brad 2009-01-01 Info NY
Brad 2007-01-01 Designer CA
Now, if I create a pivot table based on the data above, and put a filter on the "Date" to see who worked at where aka "Location" under what "Team", let's say between "2010-01-01 to 2011-12-31"
Then it will count "John" three times, "Sam" twice and "Brad" once. And total of 6 employeses working during "2010-01-01 to 2011-12-31"
Now I want to remove these duplicates so that if "John" is counted once, he won't be counted anymore, even if he switched to different "Team" or "Location" so I can count for the total number of employees during "2010-01-01 to 2011-12-31" without any duplicates.
I understand that if I want to edit the pivot table and create unique value to remove these duplicates, I need to add another column. But I need help creating this column.
Could anyone help me out here?
Thanks a lot guys!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不管怎样，请告诉我这是否适合你。
1) 首先按“名称”对电子表格进行排序，然后按“日期”排序。
2) 添加一个名为“旧位置”的额外列。
3) 沿着排序列表向下查找，对于遇到的每个具有重复行的名称，保留第一次出现的位置，但在“旧位置”列中为所有较旧的重复项添加“X”。
现在，您可以通过保留“旧位置”列不等于“X”的行来进行过滤。这应该为您提供所有员工的最新职位。
只要不存在两个具有完全相同名字的不同员工，我认为这应该可行（否则尝试使用员工 ID 或每个人独有的东西而不是他们的名字）。
Anyway, tell me if this would work for you.
1) Sort your spreadsheet by 'Name' first and by 'Date' second.
2) Add an extra column called 'Old Position'.
3) Go down the sorted list and for every name with duplicate rows that you encounter, leave the first occurance alone, but add an 'X' to the column 'Old Position' for all of the older duplicates.
Now you can filter by keeping rows that have their 'Old Position' column not equal to 'X'. This should give you just the most recent positions for all employees.
As long as there are not two distinct employees with the exact same name, I think this should work (otherwise try to use an employee id or somethings unique to each individual instead of their name).
将“日期”放入报告过滤器中，将“名称”放入行标签中，将“位置”过滤器设置为“NY”，然后根据您希望如何查看数据，“位置”可以放置在报告过滤器或行标签中。
Put "Date" in report filter, "Name" in row labels, set filter for "Location" as "NY" then "Location" can in placed in either report filter or row labels depending on how you want to see data.