来自日期范围的线图 [英] Line graph from date ranges

问题描述

我在Excel中有一个客人名单。在每个客人姓名后面都有一个起始日期和一个迄今为止。显然表明他们将要停留的日期范围。

I have a list of guests in Excel. After each guests name there is a from date, and a to date. Obviously indicating the date range they are going to stay.

我如何制作一个折线图，根据这些范围显示我们在特定日期有多少客人？

How do I make a line graph that shows how many guest we have on any given day, based on thees ranges?

例如

GUEST NAME     ROOM      DATE FROM     DATE TO
John Doe       101       26.03.14      01.04.14
Peter Parker   208       24.03.14      28.03.14
Some guy       105       25.03.14      29.03.14
Superman       101       01.04.14      15.04.14
Wonder Woman   101       01.04.14      15.04.14

推荐答案

假设提供的数据包含在 A1：D6 范围内。查找列表中项目的最小和最大日期。让 F2：F24 包含最小和最大日期之间每天的日期，并且 G2：G24 中的对应单元格包含该公式将计算每天的客人人数。假设您使用Excel 2007/2010/2013，在 G2 中，您可以使用公式 = COUNTIFS（$ C：$ C，< = &安培; F2，$ d：$ d，> =&安培; F2）。然后你可以从 G2 到 G24 填充此公式。为了获得折线图，您只需在 F1：G24 中创建一个源数据图。希望这有助于。

Suppose the data provided is contained in the range A1:D6. Find the minimum and maximum dates for items in the list. Let F2:F24 contain dates for each day between the minimum and maximum dates and the corresponding cells in G2:G24 contain the formula that will count the number of guests for each day. Assuming you are using Excel 2007/2010/2013, in G2 you would have the formula =COUNTIFS($C:$C,"<="&F2,$D:$D,">="&F2). You would then fill this formula down from G2 to G24. In order to obtain a line graph, you just create a graph with source data in F1:G24. Hope this helps.

注意：我将示例数据中的日期格式从dd.mm.yyyy更改为mm / dd / yyyy，所以这是您在上述屏幕截图中看到的内容。

Note: I changed the date format in your example data from dd.mm.yyyy to mm/dd/yyyy, so this is what you're seeing in the screenshot above.

这篇关于来自日期范围的线图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！