根据详细活动生成汇总甘特图 [英] Generate summary gantt chart from detailed activities

问题描述

我想创建一个甘特图摘要，以显示一个人的整个忙碌"状态.和免费"通过详细的甘特图，按天排成一行进行计划，该图表包含多行中不同人员的活动列表.

I want to create a gantt chart summary that shows a person´s whole "busy" and "free" schedule by day and in a single row, from a detailed gantt chart with a list of activities of different people in multiple rows.

基本上是这样的:

为此:(我是手动创建的)

为了能够总结人们在两次活动之间的轮班空闲时间.

To be able to give a summary of people´s shifts free time between activities.

现在，我只是使用此公式比较每行的开始日期和结束日期，并产生一个"1"如果条件为True，那么我只需对整个甘特图单元格进行格式化.

Right now I´m just using this formula to compare the start and end date in each row and produce a "1" if the condition is True, then I just condition formatted the whole Gantt cells.

= IF(AND(Q $ 8> = $ N12，DAY($ K12)< DAY($ J12))，1，IF(AND(Q $ 8> = $ N12-0.00001，Q $ 8< $ O12-0.00001)，1，;"))

我不知道如何开始.我正在考虑做嵌套的事情:

I have no idea how to start. I was thinking of doing the nest things:

1. 创建一个表格，列出要在甘特图中添加的所有可能人员的姓名.
2. 对宏进行编程，以使用相同的模板创建新的工作表.
3. 编写一个循环，以每个人的名字开始迭代:
4. 对于要汇总的甘特图中存在的每个人的名字，开始他们计划好的活动的每一天开始创建新行(我仍然无法弄清楚如何重复此行).
5. 在每个人的循环中，开始迭代原始工作表上的每一行，评估每个开始和结束日期，并在新工作表的当前人的当前日期行上粘贴"1".如果条件在相应的小时内为真.
6. 循环直到每个人的所有个人活动结束.
7. 与下一个人继续.

我想知道这是否是符合逻辑的方法，并且如果您有任何指针或类似的代码可以回收，我不精通VBA和Excel宏.

I´d like to know if this is the logical way to go and if you have any pointers or similar code to recycle, I am not proficient in VBA and Excel macros.

推荐答案

您似乎正在尝试提取每人每天的唯一记录，以获取人/天的时间可用性摘要但希望它随着您添加更多的人和更多的日子而自动化.

It looks like you are trying to extract unique records per person and day to get a person/day summary of time availability but also want it to be automated as you add more people and days.

我能够结合使用powerquery和数据透视表来做到这一点.添加或更改新人员/日期后，报告将更新，但您需要使用CTRL + ALT + F5

I was able to do this with a combination of powerquery and a pivot table. When new persons/dates are added or changed the report will update but you will need to refresh using CTRL+ALT+F5

1. 您想突出显示整个报告或您认为可以扩大的区域.当突出显示时，您将在配方"选项卡下使用命名范围功能->定义的名称功能区->定义的名称下拉列表.我们可以将其命名为REPORTAREA或类似的名称.

请确保您将报表中的条件格式公式更改为显示0而不是".这样就可以正常工作

2转到数据"标签->获取并转换功能区->从其他来源->空白查询.这将以空白查询的形式打开超级查询编辑器

2 go to DATA tab -> GET AND TRANSFORM ribbon -> From other sources -> Blank Query. This will open the power query editor as a blank query

3在编辑栏中键入= Excel.CurrentWorkbook()，区分大小写很重要

3 In the formula bar type =Excel.CurrentWorkbook() case sensitive is important

4从那里您将看到内容"和名称"列.在名称"列中，选择下拉菜单，然后转到文本过滤器"->等于...键入您命名的范围的名称，这样查询就不会意外提取任何其他内容.

4 From there you will see CONTENT and NAME column. In the NAME Column select the drop down and go to TEXT FILTERS -> Equals... Type in the name of your named range so the query does not pick up anything else on accident.

5右键单击以删除名称"列，然后将其删除.

5 Remove the NAME column by right click selecting it and then remove.

6您会注意到CONTENT列有两个向左和向右弯曲的箭头，而不是像您在excel中习惯的那样向下笔直.点击这些箭头，并确保您取消选中使用原始列名称作为前缀"选项框，并确保选择了EXPAND选项.然后单击确定".

6 you will notice the CONTENT column has two curved arrows pointing left and right instead of straight down like you are used to in excel. Click these arrows and make sure you uncheck the "use original column name as prefix" option box and ensure that the EXPAND option is selected. Then click okay.

7此时，它看起来很像您的报告.转到首页"标签->TRANSFORM功能区->使用第一行作为标题.

7 At this point it looks alot like your report. Go to the HOME tab -> TRANSFORM ribbon -> Use first row as headers.

8.仅选择不是报告的24小时样式标签的列，然后右键单击->取消透视其他列

8.Select only the columns that are NOT THE 24 hr STYLE TIME LABELS of your report and then right click -> Unpivot other columns

9此时，您可以通过右键单击并删除来删除一些不需要的列.还要双击并根据需要重命名列.您可以右键单击该列的顶部，然后将类型更改为所需的类型.不必担心24HR样式的时区看起来不正确，因为稍后会解决此问题，此列应更改为十进制类型，而不是时区类型.

9 At this point you can start removing some of the columns you dont want by right click and remove. Also double click and rename the columns as you wish. You can right click the top of the column and change types to what you want. Dont worry about the 24HR style time zones not looking correct as this will be fixed later, this column should be changed to decimal type and not time zone type.

1. 选择具有所需日期信息的列，然后右键单击->列重复->更改日期类型.

11.在屏幕的左上方，有一个关闭并加载"下拉菜单，您可以在其中加载新的工作表.

11.At the top left part of the screen there is a CLOSE AND LOAD drop down where you will load to a new worksheet.

1. 这将产生一个绿色表格.选择表格，然后按ALT + D + P生成与您从查询生成的绿色表格链接的数据透视表.

1. That will produce a green table. Select the table and press ALT+D+P to produce a pivot table linked to the green table you produced from the query.

您可能需要关闭打开的查询和连接"框，才能看到将显示在右侧的数据透视表选项.将24hr样式列拖动到columns区域.将人员拖到行区域，然后将在步骤10中创建的列拖到行区域.将条件格式列拖到值"区域.

You may need to close the Queries and Connections box that opened in order to see the pivot table options that will appear on you right. Drag the 24hr style column to the columns area. Drag the People to the Rows area and after Drag the Column you made in step 10 to the Rows area. Drag the conditional format column to the Values area.

您的数据透视表不会完全像您想要的那样.选择枢纽分析表后，前往[设计]标签->报告布局->表格和小计->在同一标签中不要显示小计.

Your pivot table wont look exactly like what you want. while pivot table is selected go to DESIGN tab -> REPORT LAYOUT -> Tabular and also SUBTOTALS -> DO NOT SHOW SUBTOTALS while in the same tab.

13突出显示所有24小时制时间标签并设置其格式，然后突出显示数据透视表的内部，其中1和0都将位于其中，并应用之前应用的条件格式.不要忘记您最初更改了公式，因此您的if语句不以"结尾.而是改为0.

13 Highlight all of the 24hr style time labels and format them and after highlight the inside of the pivot table where all the 1 and 0 will be and apply the conditional formatting you applied previously. Dont forget you changed the formula originally so your if statement does not end with "" but instead with 0.

如果您希望我认为更容易在数据透视表字段的ROWS和COLUMNS区域之间切换，以使报告更易于阅读.我选择在图片中这样做.如果您希望以习惯的方式保留报告，则可以按照前面的说明进行操作.

If you would like i think it is easier to switch around the ROWS and COLUMNS area of the pivot table fields so that the report is easier to read. I have chosen to do so in the pictures. If you want to keep the report the way you are used to you can follow previous instructions.

这篇关于根据详细活动生成汇总甘特图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！