vba 查找并发、重叠、日期范围的数量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19596982/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find number of concurrent, overlapping, date ranges
提问by user2653858
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.
我有一个包含 3 列的电子表格。A 列是讲师 ID 号，B 列是他们的课程开始日期，C 列是他们的课程结束日期。每个讲师 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.
我基本上是想回答这个问题，这位讲师在任何给定时间教授的课程的最大数量是多少。本质上，我需要为每个 ID 号找到最大的、并发的、重叠的日期范围的数量。
The trouble is, while I know how to find overlapping date ranges, I don't know how to countthe number of concurrent courses.
问题是，虽然我知道如何找到重叠的日期范围，但我不知道如何计算并发课程的数量。
Eg. Instructor 115 has the following date ranges listed:
例如。讲师 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
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.
虽然 11/05/13 课程与 9/17/13 课程和 11/12/13 课程重叠，但它们彼此不重叠……因此该讲师最多只教授 2 门课程时间。
Is there a way to write a function that will return the highest number of concurrent overlapping date ranges for each ID?
有没有办法编写一个函数来为每个 ID 返回最大数量的并发重叠日期范围？
Edit not form OP to transfer details from a comment:
编辑不形成 OP 以从评论中传输详细信息：
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 1in 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?
我可以几何地解决这个问题，但我不知道如何在 VBA 函数中做到这一点（我对编程仍然很陌生）。如果我要在代码之外解决这个问题，我会为每个 ID 创建一个表，为每一天制作一列。然后我会为每个日期范围创建一行，1在与范围重叠的每一列中标记一个。然后我会总结每天的总重叠。然后我会使用一个简单的 MAX 函数来返回最大数量的连续重叠。有没有办法在函数内部执行此操作而无需 Excel 物理绘制这些表？
回答by mr.Reband
Using VBA, assuming Column A contains your start dates, and column B contains your end dates, and assuming your data starts in row 1 and there are no blank rows in your data, the below sub will do what you outlined in your comment:
使用 VBA，假设 A 列包含您的开始日期，B 列包含您的结束日期，并假设您的数据从第 1 行开始并且您的数据中没有空白行，下面的子将执行您在评论中概述的内容：
Sub getMaxConcurrent()
'get minimum date (startDate)
Dim startDateRange
Set startDateRange = Range("A1", Range("A1").End(xlDown))
Dim startDate As Date
startDate = WorksheetFunction.Min(startDateRange)
'get maximum date (endDate)
Dim endDateRange
Set endDateRange = Range("B1", Range("B1").End(xlDown))
Dim endDate As Date
endDate = WorksheetFunction.Max(endDateRange)
'get date range (dateInterval)
Dim dateInterval As Integer
dateInterval = DateDiff("d", startDate, endDate)
'Create daily table header
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim x As Integer
For x = 0 To dateInterval
Dim dateVal As Date
dateVal = DateAdd("d", startDate, x)
Cells(1, 3 + x).Value = dateVal
Next
'Fill in daily table
Dim y As Integer
y = 2
Dim startDateValue As Date
startDateValue = Cells(y, 1).Value
Do Until IsEmpty(Cells(y, 1).Value)
For x = 3 To dateInterval + 3
If (Cells(y, 1).Value <= Cells(1, x).Value) Then
If (Cells(y, 2).Value >= Cells(1, x).Value) Then
Cells(y, x).Value = 1
Else
Cells(y, x).Value = 0
End If
Else
Cells(y, x).Value = 0
End If
Next
y = y + 1
Loop
'sum up each day
For x = 3 To dateInterval + 3
Cells(y, x).Value = WorksheetFunction.Sum(Range(Cells(2, x).Address & ":" & Cells(y - 1, x).Address))
Next
MsgBox ("Max concurrent courses: " & WorksheetFunction.Max(Range(Cells(y, 3).Address & ":" & Cells(y, x).Address)))
End Sub
回答by barry houdini
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
如果您的数据低至第 1000 行，则此“数组公式”将给出 E2 中讲师 ID 的最大并发课程数
=MAX(COUNTIFS(A:A,E2,B:B,"<="&B$2:C$1000,C:C,">="&B$2:C$1000))
=MAX(COUNTIFS(A:A,E2,B:B,"<="&B$2:C$1000,C:C,">="&B$2:C$1000))
confirmed with CTRL+SHIFT+ENTER
用CTRL+ SHIFT+确认ENTER
回答by nsg
Let's assume there is only one instructor and you have start and end dates in A1:B4.
假设只有一名讲师，并且您在 A1:B4 中有开始和结束日期。
Copy A1:A4 to A7:A10, copy B1:b4 to A11:a14 (right under it). Select A7:A14, hit Sort (on data tab) and "remove duplicates". You have a list unique list of dates in ascending order. Let's assume there were no duplicates (as in your example), your of date is same A7:a14. Select it copy, and paste spacial with transpose to C5.
将 A1:A4 复制到 A7:A10，将 B1:b4 复制到 A11:a14（正下方）。选择 A7:A14，点击排序（在数据选项卡上）并“删除重复项”。您有一个按升序排列的唯一日期列表。假设没有重复项（如您的示例中所示），您的日期与 A7:a14 相同。选择它复制，并将空间与转置粘贴到 C5。
At this point You have start and end dates in A1:B4 and list of uniqe dates in C5:J5. Put formula =IF(AND($A1<=C$5,C$5<=$B1),1,0) in C1 and copy it to C1:J4.
此时，您在 A1:B4 中有开始和结束日期，在 C5:J5 中有唯一日期列表。将公式 =IF(AND($A1<=C$5,C$5<=$B1),1,0) 放入 C1 并将其复制到 C1:J4。
put formula =SUM(C1:C4) in C6 and copy it to C6:J6.
将公式 =SUM(C1:C4) 放入 C6 并将其复制到 C6:J6。
Maximum number in C6:j6 is your maximum concurrent courses for this instructor
C6:j6 中的最大数量是您为该讲师开设的最大并发课程数
