과거 이벤트에 대한 날짜가 2~6개 있고 각 날짜 간의 평균 확산을 기반으로 다음 이벤트가 언제 발생할지 예측해야 합니다.
C4-D4스크린샷에서 기본적으로 ( ),( D4-E4),( E4-F4)의 평균을 구하고 ( F4-G4)가 비어 있으므로 건너뛰고 싶습니다 . 그런 다음 가장 최근 값( )에 평균 일수를 더하여 다음 발생 예측값 C4( )을 도출하려고 합니다.A4
B4평균 일수를 계산하고 셀 중 하나 또는 둘 다 비어 있으면 계산을 건너뛰는 하나의 수식을 갖고 싶습니다 .
나는 시도했다 Max-Min/CountIf:
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")
5그러나 row 의 경우 159이어야 214하고 row 6이어야 하는 경우 매번 너무 낮은 숫자가 나타납니다 337. 여러 날짜에 걸쳐 사용하려고 했더니 AVERAGE날짜가 나오지 않고 평균 날짜가 나왔습니다.
답변1
계산하려는 차이는 실제 숫자가 아니라 분모이므로 수식에서 분모에서 1을 빼야 합니다.
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
도우미 열을 건너뛰려면 다음을 수행하세요.
=IFERROR(MAX(C4:G4) + (MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
FORCAST를 사용할 수도 있습니다.
=FORECAST(0,C4:G4,ROW($1:$5))
아니면 가로채기:
=INTERCEPT(C4:G4,ROW($1:$5))
이 두 가지는 평균이 아닌 추세를 사용하므로 차이가 크게 다를 경우 다른 값이 나타납니다.
답변2
Scott Craner의 답변은 질문에서 묻는 작업을 다루며 평균 간격을 기준으로 다음 날짜를 예측합니다. 또한 추세를 활용하는 대안도 제시합니다. 이는 데이터가 의미하는 바에 따라 더 나은 접근 방식일 수도 있고 더 나쁜 접근 방식일 수도 있습니다. 이 답변은 독자들이 적절한 종류의 솔루션을 적용할 수 있도록 차이점에 중점을 둘 것입니다.
질문과 Scott의 답변을 사용하여 (Max - Min)/(interval count)평균 간격을 찾습니다. 괜찮습니다. 하지만 효과를 설명하기 위해 간격을 계산하고 이를 사용하여 작업하겠습니다. 이렇게 하면 그래프에서 쉽게 볼 수 있기 때문입니다. 행 6 데이터는 5개의 값이 있는 첫 번째 행이므로 사용하겠습니다. 그러면 그 데이터는 다음과 같습니다.
C열의 다섯 번째 사건과 여섯 번째 사건 사이의 추정 간격은 사건 6의 날짜를 제공합니다. 간격을 표시하면 다음과 같습니다.
평균 간격은 다음과 같습니다.
평균은 어느 시점에서나 동일하며 이 경우에는 단지 값일 뿐입니다 225.5. 이를 마지막 날짜에 추가하면 에 다음 발생이 예상됩니다 7/13/2019.
여기에 문제가 있습니다. 패턴을 따르는 프로세스를 기록하고 있습니까, 아니면 무작위에 가까운 것을 기록하고 있습니까? 무작위 이벤트는 톱니처럼 각 연속 이벤트에 따라 오르락내리락하는 예측 가능한 패턴을 따르지 않습니다. 여기에는 동일한 방향의 일련의 관측이 포함됩니다. 데이터가 실제로 무작위인 경우 패턴이 얼마나 가능성이 있는지에 대한 통계 테스트가 있지만, 사람들의 두뇌는 패턴을 보도록 연결되어 있으므로 데이터의 패턴이 의미 있는 것으로 가정되는 경우가 많습니다. 데이터 패턴은 다소 로르샤흐 잉크 얼룩과 유사하며, 사람들은 실제로 존재하지 않을 수도 있는 의미를 패턴에 투사합니다.
패턴을 조사하는 경우 데이터를 보고 패턴처럼 보이는 것을 테스트할지 여부를 결정할 수 있습니다. 그러나 데이터가 무작위일 것으로 예상하거나 다음 이벤트에 대한 편견 없는 추정을 원하는 경우 패턴을 가정하여 시작하고 싶지 않습니다. 맹목적으로 추세선을 사용하는 것이 바로 그렇게 하는 것입니다. 질문에서 제안한 대로 이 상황에서 평균을 사용하여 작업하는 것이 좋습니다.
이 예를 들어보세요. 데이터를 보면서 뇌는 데이터가 곡선을 따르고 있다고 확신시키려고 합니다. 곡선이 평준화되는 것처럼 보이지만 일반적으로 증가하는 것으로 보입니다. 그렇다면 다른 정보가 없는 경우 패턴을 조정하는 가장 좋은 방법은 무엇일까요? 연속적인 고차 적합을 기반으로 다음 간격을 투영하면 다음과 같은 일이 발생합니다.
1차 적합은 간단한 추세로 얻을 수 있는 직선입니다.
이는 일반적으로 값이 증가하는 것으로 인식하고 다음 간격이 가 될 것으로 추정합니다 259.5. 두 번째 순서 맞춤은 다음과 같습니다.
이는 마지막 간격을 높은 지점으로 보고 다음 간격이 더 낮아질 것으로 추정합니다 232. 3차 적합은 4개의 간격으로 수행할 수 있는 최고 수준이며 다음과 같습니다.
세 번째 주문 라인은 4개 지점에 완벽하게 들어맞습니다. 여러 변곡점을 찾아 마지막 지점 이후 더 높은 방향으로 향하게 되어 253다음 구간을 추정합니다.
따라서 "패턴"을 생성하는 기본 프로세스를 가장 잘 나타낸다고 생각되는 라인의 종류에 따라 다음 이벤트의 범위는 에서 까지가 될 수 7/13/2019있습니다 8/16/2019.
일곱 번째 사건을 예측하기 위해 이러한 "추세"를 확장하면 훨씬 더 다양한 결과를 얻을 수 있습니다. 이 결과는 5개의 데이터 포인트를 사용하여 얻은 것입니다. 데이터가 특정 패턴을 따른다고 생각하더라도 추정할 수 있는 데이터가 많지 않습니다. 많은 데이터 행이 있는 것처럼 데이터 포인트가 더 적으면 어떤 형태의 추정도 위험합니다. 데이터가 특정 패턴을 따른다고 믿을 만한 이유가 있고 데이터가 일반적으로 해당 패턴에 맞는 경우 적절한 모양(예: 수식 유형)의 추세선을 사용하면 "최상의" 추정치를 얻을 수 있습니다. 경우에는 점 추정치 대신 또는 그에 추가하여 신뢰 구간을 사용하십시오. 그것은 적어도 당신이 얼마나 멀리 떨어져 있는지에 대한 아이디어를 줄 것입니다.
모든 형태의 추세선은 기본 패턴이 있고 해당 패턴이 데이터에 반영되고 있다고 가정합니다. 실제로 패턴이 있는 경우 일반적으로 몇 가지 데이터 포인트만으로는 패턴을 추정하기에 충분하지 않습니다. 그러나 패턴이 전혀 없을 수도 있고 단지 관찰의 우연한 순서만 있을 수도 있습니다. 이 경우 패턴을 기반으로 추정하면 임의의 방향으로 이동하여 투영에 상당한 오류가 발생할 수 있습니다.
하지만 또 다른 가능성도 있습니다. 많은 일이 주기를 따릅니다. 관찰 내용은 실제로 패턴의 일부일 수 있지만 패턴의 작은 조각일 뿐입니다. 이 예에서 이러한 관찰은 사인파처럼 보이는 수십 년 주기의 일부일 수 있습니다. 이러한 관찰은 주기의 정점에 접근하는 것을 정확하게 반영할 수 있으므로 후속 패턴은 위쪽이 아닌 아래쪽으로 향할 수 있습니다(위의 2차 적합도와 유사). 따라서 패턴이 실제라고 하더라도 패턴 뒤에 숨어 있는 기본 프로세스에 대해 아무것도 모르는 채 데이터 범위 밖에서 추정하는 것은 위험합니다.