查找并发，重叠，日期范围的数量 [英] Find number of concurrent, overlapping, date ranges

问题描述

我有一个难题，我一直在努力解决年龄，但它是非常简单的超出我。

我有一个包含3列的电子表格。 A列是指导员身份证号码，B列是他们的课程开始日期，C列是课程结束日期。每个讲师ID有多个课程。

我基本上是试图回答这个问题，这个教练在任何给定的时间教授的最大课程是多少。
本质上，我需要为每个身份证号码找到最大，并发和重叠日期范围的数量。

麻烦的是，虽然我知道如何要查找重叠的日期范围，我不知道如何计数并发课程的数量。

例如。
教练员115具有以下日期范围：

 
 9/10/13 / 11/04/13 
 9/17/13 / 11/11/13 
 11/05/13 / 12/30/13 
 11/12/13 / 1/20/14 

虽然11/05/13课程与9/17/13课程和11/12/13课程重叠，但它们不相互重叠，所以该教练只能教授最多2门课程任何时候。

有没有办法编写一个函数，它将为每个ID返回最大数量的并发重叠日期范围？

编辑不要从OP传出详细信息：

我可以在几何上解决这个问题，但是我不知道如何做一个VBA功能（我还很新的编程）。如果我在代码之外解决这个问题，我将为每个ID创建一个表，每一个列都是一个列。然后，我将为每个日期范围创建一行，在每个范围重叠的列中标记一个 1 。那么我会总结每一天的总重叠。那么我将使用一个简单的MAX函数来返回最大数量的连续重叠。有没有办法做这个功能，没有Excel物理绘制这些表？

解决方案

如果你有数据下到1000行，那么这个数组公式将给出E2中教师ID的并发课程的最大数量。

= MAX（COUNTIFS（A ：A，E2，B：B， < = 和B $ 2：C $ 1000，C：C， > = 和B $ 2：C $ 1000））

确认使用 CTRL + SHIFT + ENTER

I have a puzzle I've been trying to solve for ages now, but it's quite simply beyond me.

I have a spreadsheet with 3 columns. Column A is instructor ID numbers, Column B is their course Start date and Column C is their course end date. There are multiple courses for each instructor ID.

I'm basically trying to answer the question, what is the maximum number of courses this instructor is teaching at any given time. Essentially, I need to find, for each ID number, the number of maximum, concurrent, overlapping date ranges.

The trouble is, while I know how to find overlapping date ranges, I don't know how to count the number of concurrent courses.

Eg. Instructor 115 has the following date ranges listed:

 9/10/13  / 11/04/13
 9/17/13  / 11/11/13
11/05/13  / 12/30/13 
11/12/13  /  1/20/14

While the 11/05/13 course overlaps with both the 9/17/13 course and the 11/12/13 course, they do not overlap with each other... so this instructor is only teaching a maximum of 2 courses at any time.

Is there a way to write a function that will return the highest number of concurrent overlapping date ranges for each ID?

Edit not form OP to transfer details from a comment:

I can solve this geometrically, but I don't know how to do that in a VBA function (I'm still very new to programming). If I were to solve this outside of code, I would create a table for each ID making a column for every day. I'd then create a row for each date range, marking a 1 in each column that range overlaps with. then I’d sum the total overlaps for each day. Then I’d use a simple MAX function to return the highest number of consecutive overlaps. Is there a way to do this inside of a function without having Excel physically draw out these tables?

解决方案

If you have data down to row 1000 then this "array formula" will give the maximum number of concurrent courses for an Instructor ID in E2

=MAX(COUNTIFS(A:A,E2,B:B,"<="&B$2:C$1000,C:C,">="&B$2:C$1000))

confirmed with CTRL+SHIFT+ENTER

这篇关于查找并发，重叠，日期范围的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！