테이블이 두 개 있어요. 첫 번째에는 특정 일수(열 제목)에 대한 조건부 요율이 포함되어 있습니다.
| 날짜 | 1 일 | 7 일 | 14 일 | 30 일 | 90일 |
|---|---|---|---|---|---|
| 2021년 11월 1일 | 4.36 | 4.36 | 4.30 | 4.49 | 4.64 |
| 2021년 12월 1일 | 4.32 | 4.34 | 4.36 | 4.48 | 4.63 |
| 2021년 1월 13일 | 4.25 | 4.32 | 4.34 | 4.43 | 4.60 |
| 2021년 1월 14일 | 4.26 | 4.35 | 4.35 | 4.36 | 4.58 |
| 2021년 1월 15일 | 4.33 | 4.35 | 4.37 | 4.34 | 4.57 |
그리고 주어진 일수에 대한 실제 요율을 계산해야 하는 두 번째 테이블
| 날짜 | 날 | 비율 |
|---|---|---|
| 2021년 12월 1일 | 21 | ? |
| 2021년 1월 13일 | 74 | ? |
| 2021년 1월 15일 | 40 | ? |
보시다시피, 어떤 날에는 조건부 요율이 제공되고 다른 날에는 실제 요율이 제공됩니다. 무엇보다도 조건부 요율은 매일 제공되며 실제 요율은 일부에만 제공됩니다. 이 비율을 어떻게 계산할 수 있나요? 예를 들어 여기서 선형 보간법을 사용하는 것을 볼 수 있습니다. 실제 일수가 21일 경우 가장 가까운 조건부 요율은 14일 동안 제공되며 앞으로 며칠 후에 이 값을 추정해야 합니다. 에 대해 읽었 =forecast지만 필요한 작업이 수행되지 않았습니다. 이 작업을 수행하는 데 도움이 되는 Excel 함수가 있습니까? 아니면 이를 위해 VBA 스크립트를 작성해야 합니까?
답변1
오피스 365:
=FORECAST.LINEAR(B7,
INDEX(B2:F2,MATCH(B7,$B$1:$F$1,1)):INDEX(B2:F2,MATCH(B7,$B$1:$F$1,1)+1),
INDEX($B$1:$F$1,MATCH(B7,$B$1:$F$1,1)):INDEX($B$1:$F$1,MATCH(B7,$B$1:$F$1,1)+1))
놀랍게도 선형 보간은 Excel에서 수행하기가 매우 어렵습니다.
엑셀 예측 기능을 사용합니다 =FORECAST(x,known_y's,known_x's).
및 에서 관련 두 점을 선택 =INDEX합니다 .=MATCHknown_y'sknown_x's
이는 Excel 2007에서 작동하지만 (곧 사용 중단됨) =FORECAST.LINEAR로 변경되어야 합니다 .=FORECAST
답변2
우리는 날짜가 설정 지점 사이에 위치하는 비율을 구하고 해당 비율을 상한 값과 하한 값의 차이에 곱한 다음 해당 결과에 낮은 값을 추가해야 합니다.
수행해야 할 한 단계는 헤더를 실제 숫자로 변경하는 것입니다.
OFFICE 365에서는 LET를 사용하여 단계를 단순화할 수 있습니다.
=LET(head,$B$1:$F$1,
lkp,B7,
rng,$B$2:$F$4,
dt,$A$2:$A$4,
rw,INDEX(rng,MATCH(A7,dt,0),0),
mtch,MATCH(lkp,head),
mtchp1,mtch+1,
fst,INDEX(head,,mtch),
scd,INDEX(head,,mtchp1),
fct,(lkp-fst)/(scd-fst),
vlue1,INDEX(rw,,mtch),
vlue2,INDEX(rw,,mtchp1),
(vlue2-vlue1)*fct+vlue1)
허락 없이:
=(INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1)+1)- INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1)))* ((B7- INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1)))/( INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1)+1)- INDEX($B$1:$F$1,, MATCH(B7, $B$1:$F$1))))+ INDEX(INDEX($B$2:$F$4,MATCH(A7, $A$2:$A$4,0),0),, MATCH(B7, $B$1:$F$1))
보시다시피, 관리하기가 매우 까다롭고 어려워집니다.
LET를 사용하는 또 다른 방법은 수학 대신 FORECAST.LINEAR을 사용하여 시작과 끝 x, y를 얻은 후 답을 얻는 것입니다.
=LET(head,$B$1:$F$1,
lkp,B7,
rng,$B$2:$F$4,
dt,$A$2:$A$4,
rw,INDEX(rng,MATCH(A7,dt,0),0),
mtch,MATCH(lkp,head),
mtchp1,mtch+1,
fst,INDEX(head,,mtch),
scd,INDEX(head,,mtchp1),
vlue1,INDEX(rw,,mtch),
vlue2,INDEX(rw,,mtchp1),
FORECAST.LINEAR(lkp,CHOOSE({1,2},vlue1,vlue2),CHOOSE({1,2},fst,scd)))
