엑셀 날짜를 숫자로 숫자를 날짜로 바꾸는 함수 방법, 원하는 날짜에서의 값 원하는 값에서의 날짜 외삽 추세하기

어떠한 특성값은 시간이 지날수록 선형적 (Linear)으로 감소하는 경향성을 가지고 있는데 그 값이 일정 수준 이하로 내려가면 그 성능을 제대로 발휘하지 못하게 된다. 그래서 일정 시간이 지난 시점에서의 특성값 수준을 확인하는 것과 어느 시점에 그 특성값이 제 성능을 발휘하지 못하는지를 미리 파악할 필요가 있었다.

 

아래는 날짜 별로 확인한 특성값이다. 특성값은 70.0 이하로 내려가면 제 성능을 발휘하지 못한다. 

날짜	특성값
2020년 1월 9일	83.2
2020년 1월 16일	83.1
2020년 7월 25일	77.2
2020년 8월 24일	76.1
2020년 9월 24일	74.8

 

특성값의 열화가 선형적인 특성을 가지고 있으므로 y = ax + b를 사용하면 원하는 날짜 (x)에서의 특성값 (y)를 외삽 추세 (Extrapolation)로 알 수가 있다. a는 기울기이고 b는 y 절편이다. 달력의 일자를 세어서 날짜 별로 숫자를 매겨 해당 수식을 사용할 수도 있겠지만 엑셀에 함수가 있으므로 그 기능들을 사용해 보기로 한다.

 

사용한 엑셀 함수는 아래와 같으며 함수의 정의는 마이크로소프트의 정의를 차용해왔다.

 

---

 

1. VALUE

: 숫자를 나타내는 텍스트 문자열을 숫자로 변환합니다.

2. TEXT

: TEXT 함수를 사용하면 서식 코드를 사용하여 숫자에 서식을 적용하는 방식으로 숫자 표시 방법을 변경할 수 있습니다. 

3. SLOPE

: known_y's와 known_x's 사이의 데이터 요소에 대한 선형 회귀선의 기울기를 반환합니다. 기울기는 선의 두 점 사이의 수직 거리를 수평 거리로 나눈 회귀선의 변화율입니다.

4. INTERCEPT

: 기존 x 값과 y 값을 사용하여 한 개의 선이 y 축과 교차하는 지점을 계산합니다. 절편은 known_x's와 known_y's의 값으로 이루어진 가장 적합한 회귀선을 기반으로 합니다.

5. RSQ

: known_y's와 known_x's의 데이터 요소에 대하여 피어슨의 곱 모멘트 상관 계수의 제곱값을 반환합니다. 

 

---

 

결과부터 확인해 본다.

날짜숫자변환및추세 파일 中

 

B열 (B3:B7)의 날짜를 숫자로 변환했다. 변환한 사유는 앞서 설명한 바와 같이 그래프를 그리고 추세 외삽을 수식으로 하기 위함이었다. 변환을 위해서 C열 (C3:C7)에 VALUE 함수를 사용했다. 엑셀은 날짜 1990년 1월 1일을 숫자 1 이라는 기준을 가지고 있다. 그래서 2020년 1월 9일은 1990년 1월 1일부터 43829일 이후의 날짜인 것으로 보면 된다.

 

다음으로 그래프를 그렸다. 그래프의 x축은 C열 (C3:C7)이고 y축은 D열 (D3:D7)로 했다. 그래프는 차트 분산형 중 "직선 및 표식이 있는 분산형"으로 선정했다. 날짜 별 특성값을 표식으로 나타내고 선형의 특성을 나타내기 가장 적합할 것으로 판단했기 때문이다. 그리고 그려진 짙은 남색의 선 및 표식을 클릭하고 추세선을 추가했고 추세선의 수식과 상관 계수 R^2을 나타나도록 했다. 상관 계수는 추세선이 실제 데이터를 얼마나 잘 반영하는지를 나타내는 지표인데 0.9 이상이면 높은 상관 관계를 가진다고 볼 수 있다. 여기서는 0.9981이니 추세선이 실제 데이터와 거의 유사하다고 볼 수 있겠다. 추세선의 수식으로부터 y = ax + b가 y = -0.0319x + 1480.7 로 확인할 수 있다.

 

그렇다면 이제 앞서 언급한 어떤 날짜에서의 특성값과 어떤 특성값에서의 날짜를 구해보고자 한다. 그래프에서 기울기와 y 절편을 구했지만 그 값을 수식에 정확히 사용할 수 있도록 엑셀 함수를 통해 다시 한번 구해보았다. 모두 그래프에 표시된 값과 동일한 것을 확인할 수 있었다.

 

기울기 : =SLOPE(D3:D7,C3:C7)

y 절편 : =INTERCEPT(D3:D7,C3:C7)

상관 계수 : =RSQ(D3:D7,C3:C7)

 

y는 특성값, x는 숫자로 변환된 날짜이므로 어떤 날짜에서의 특성값은 y = -0.0319x + 1480.7를 그대로 사용하면 되고 (x에 숫자로 변환된 날짜를 삽입) 어떤 특성값에서의 날짜는 y = ax + b를 변환하여 x = (y - b) / a 로 그 값을 구하면 되겠다 (구해진 숫자로 표현된 날짜 x를 날짜로 변환해야함). 최종적으로 C15 셀과 C19 셀을 확인하면 되겠다.

 

숫자로 표현된 날짜를 날짜로 변환하는 수식은 아래와 같다.

=TEXT(C18,"yyyy.mm.dd")

 

---

 

내용을 사용해 볼 수 있도록 아래 파일을 첨부하였으니 함께 참고해도 좋을 듯 하다.

 

날짜숫자변환및추세 파일.xlsx

0.02MB