I have the following situation in database:
我在数据库中有以下情况:
Department table holds all departments in the company.
部门表包含公司的所有部门。
Employee table holds all employees, together with their department id and AllowedAbsenceDays field, which tells us how many days is the employee alowed to be absent in the current year.
员工表包含所有员工以及他们的部门ID和AllowedAbsenceDays字段,该字段告诉我们当前员工有多少天不在。
Absence table holds all absences that occur in the company. It has employee id and date of absence, and also the reason of the absence (vacation, sick leave, personal days, and potentially more in the future...)
缺勤表包含公司中发生的所有缺勤。它有员工身份和缺勤日期,以及缺席的原因(休假,病假,个人日,以及未来可能更多......)
alt text http://i42.tinypic.com/4ktv69.jpg
alt text http://i42.tinypic.com/4ktv69.jpg
I am supposed to create a report which lists all employees and shows how many days they are allowed to be absent together with how many days have they been absent (and what is the reason of absence).
我应该创建一份报告,列出所有员工,并显示他们被允许缺席的天数以及他们缺席的天数(以及缺勤的原因是什么)。
I have grouped the data by employee and by reason of absence:
我按员工和缺席原因对数据进行了分组:
alt text http://i39.tinypic.com/2gt9g8o.jpg
替代文字http://i39.tinypic.com/2gt9g8o.jpg
This is what I am able to get so far, by putting the Department, Employee and Allowed Absence Days in the pivot row area, Reason in the column area and Sum of Days absent in the data area:
这是我迄今为止能够获得的,通过将部门,员工和允许缺勤天数放在数据区域中,列区域中的原因和数据区域中没有的天数总和:
alt text http://i43.tinypic.com/9s86bl.jpg
替代文字http://i43.tinypic.com/9s86bl.jpg
The problem is that I am not able to get the subtotals of allowed absence days per department (which is a firm requirement). The final report should look somewhat like this (i have photoshopped this).
问题是我无法获得每个部门允许缺勤天数的小计(这是一个确定的要求)。最终的报告应该看起来有点像这样(我已经拍了这张照片)。
alt text http://i40.tinypic.com/1415a4y.jpg
替代文字http://i40.tinypic.com/1415a4y.jpg
Is there any way to get these subtotals? Maybe I should prepare the data for the pivot table in a different way? Please note that the Grand Total column should include only the actual days of absence (not the Allowed Absence Days).
有没有办法得到这些小计?也许我应该以不同的方式为数据透视表准备数据?请注意,Grand Total列应仅包括实际缺勤天数(不包括允许缺勤天数)。
Example workbook is available for download here
示例工作簿可从此处下载
Thanks to everybody who is still reading :)
感谢所有仍在阅读的人:)
P.S. The real case is different (in the problem domain). This is a somewhat contrived example, but the basic problem is the same.
附:真实情况是不同的(在问题域中)。这是一个有点人为的例子,但基本问题是一样的。
2 个解决方案
#1
It's a classic data normalisation issue. The "Allowed absence days" field relates to the employee and not to any particular absence. The repetition of this info in every row (record) is what is causing the problem.
这是典型的数据规范化问题。 “允许缺勤日”字段与员工有关,而与任何特定缺勤无关。在每一行(记录)中重复此信息是导致问题的原因。
To achieve this within an Excel sheet and pivot table, you could remove the "Allowed absence days" column and instead use "Allowed absence days" as one of the values in the "Reason" column. It might be beneficial to either show allowed days as a negative amount or to show days for the other absence reasons as negatives. Otherwise the overall grand total will make no real sense.
要在Excel工作表和数据透视表中实现此目的,您可以删除“允许的缺勤天数”列,而是使用“允许的缺勤天数”作为“原因”列中的值之一。将允许的天数显示为负数量或将其他缺席原因显示为负数可能是有益的。否则整体总计将没有任何实际意义。
Sheet and pivot table should look roughly like this:
工作表和数据透视表应该大致如下所示:
显示重组数据的Excel电子表格:A栏是部门,B是员工,C是天缺,D是原因。允许缺席天数现在是一个原因,缺席的相关日子是负面的http://img527.imageshack.us/img527/1979/so1.png
Excel数据透视表显示C到E列中缺少的天数.C列中允许的缺勤天数http://img412.imageshack.us/img412/9201/so2.png
Repeating the department for every employee doesn't seem ideal but I suppose an employee could change department during the year so you might/might not have to account for that somehow. You also need it to make the pivot table work anyway
为每位员工重复部门似乎并不理想,但我认为员工可以在一年内更换部门,因此您可能/可能不必以某种方式对此进行说明。您还需要它来使透视表无论如何都能正常工作
If you absolutely 100% cannot have the grand total affected in this way then I don't believe that you can achieve this with a pivot table. You might want to look at using ADO to query the sheet and use some VBA macros to format and output the results. This page should give you some idea of what's involved
如果你绝对100%不能以这种方式影响总计,那么我不相信你可以通过数据透视表实现这一点。您可能希望使用ADO查询工作表并使用一些VBA宏来格式化并输出结果。这个页面应该让你知道所涉及的内容
#2
Just leave "allowed absence days" blank in all rows, and then at the bottom/top put in rows with the wished for numbers for "allowed absence days" and blanks for the other stuff.
只需在所有行中留下“允许缺席天数”空白,然后在底部/顶部放入行,其中包含“允许缺席天数”的数字和其他内容的空白。
Then just put your "allowed absence days" in data instead of columns in the pivot and drag it to the left.
然后,只需将数据中的“允许缺勤日期”替换为数据透视图中的列,然后将其拖到左侧。
#1
It's a classic data normalisation issue. The "Allowed absence days" field relates to the employee and not to any particular absence. The repetition of this info in every row (record) is what is causing the problem.
这是典型的数据规范化问题。 “允许缺勤日”字段与员工有关,而与任何特定缺勤无关。在每一行(记录)中重复此信息是导致问题的原因。
To achieve this within an Excel sheet and pivot table, you could remove the "Allowed absence days" column and instead use "Allowed absence days" as one of the values in the "Reason" column. It might be beneficial to either show allowed days as a negative amount or to show days for the other absence reasons as negatives. Otherwise the overall grand total will make no real sense.
要在Excel工作表和数据透视表中实现此目的,您可以删除“允许的缺勤天数”列,而是使用“允许的缺勤天数”作为“原因”列中的值之一。将允许的天数显示为负数量或将其他缺席原因显示为负数可能是有益的。否则整体总计将没有任何实际意义。
Sheet and pivot table should look roughly like this:
工作表和数据透视表应该大致如下所示:
显示重组数据的Excel电子表格:A栏是部门,B是员工,C是天缺,D是原因。允许缺席天数现在是一个原因,缺席的相关日子是负面的http://img527.imageshack.us/img527/1979/so1.png
Excel数据透视表显示C到E列中缺少的天数.C列中允许的缺勤天数http://img412.imageshack.us/img412/9201/so2.png
Repeating the department for every employee doesn't seem ideal but I suppose an employee could change department during the year so you might/might not have to account for that somehow. You also need it to make the pivot table work anyway
为每位员工重复部门似乎并不理想,但我认为员工可以在一年内更换部门,因此您可能/可能不必以某种方式对此进行说明。您还需要它来使透视表无论如何都能正常工作
If you absolutely 100% cannot have the grand total affected in this way then I don't believe that you can achieve this with a pivot table. You might want to look at using ADO to query the sheet and use some VBA macros to format and output the results. This page should give you some idea of what's involved
如果你绝对100%不能以这种方式影响总计,那么我不相信你可以通过数据透视表实现这一点。您可能希望使用ADO查询工作表并使用一些VBA宏来格式化并输出结果。这个页面应该让你知道所涉及的内容
#2
Just leave "allowed absence days" blank in all rows, and then at the bottom/top put in rows with the wished for numbers for "allowed absence days" and blanks for the other stuff.
只需在所有行中留下“允许缺席天数”空白,然后在底部/顶部放入行,其中包含“允许缺席天数”的数字和其他内容的空白。
Then just put your "allowed absence days" in data instead of columns in the pivot and drag it to the left.
然后,只需将数据中的“允许缺勤日期”替换为数据透视图中的列,然后将其拖到左侧。