Excel pivot table question – How to get subtotals for a row area field?

Excel pivot table question – How to get subtotals for a row area field?

苍暮颜 发布于 2021-11-26 字数 2805 浏览 708 回复 2 原文

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.

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

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

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

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

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).

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.

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。



需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。


友欢 2022-06-07 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.

扎心 2022-06-07 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.

Sheet and pivot table should look roughly like this:

Excel spreadsheet showing restructured data: column A is department, B is employee, C is days absent, D is reason. Allowed absence days is now a reason and associated days absent is negative http://img527.imageshack.us/img527/1979/so1.png

Excel pivot table showing sums of days absent in columns C through E. Allowed absence days in column C

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