☰

1개월 지연 후 12개월 동안 정액 수익을 계산하는 공식

tag-icon • tag-icon microsoft-excel worksheet-function
1개월 지연 후 12개월 동안 정액 수익을 계산하는 공식

수익을 1개월 연기한 다음 12개월 동안 수익을 일정하게 유지하는 방식으로 수익을 계산할 수 있는 공식을 고안하는 데 어려움을 겪고 있습니다.

이것은 내 워크시트입니다.

워크시트 스크린샷

2018년 주문의 기본 데이터는 셀에 있습니다 F6:Q9.

2019년 주문에 대한 기본 데이터는 셀에 있습니다 S6:AD9.

현재 2018년 3월(셀) 주문이 있고 H6수익 프로필에 1개월 지연이 표시되어 4월 수익이 없지만 2018년 5월부터 £20,000의 3월 주문에 £1,667(4월까지)이 반영되기 시작합니다. 2019).

F6:Q9또한 공식은 (2018년 주문)과 (2019년 주문)의 주문을 모두 고려해야 합니다 S6:AD9.

셀에 (1개월 지연을 반영하기 위해) I1입력했습니다 .1

셀에 (수익을 균일하게 계산해야 하는 개월 수를 반영하기 위해) I2입력했습니다 .12

수익 프로필은 셀에서 시작하는데 AE6여기서 어떤 공식을 사용해야 할지 확인하는 데 어려움을 겪고 있습니다.

오프셋 공식을 사용하려고 했지만 제대로 작동하지 못했습니다. 솔루션에 어떻게 접근해야 할지 모르겠습니다.

답변1

필요한 공식은약간복잡한 배열 수식

다음은 예상 결과를 보여주는 수식이 포함된 스프레드시트입니다.

워크시트 스크린샷

이 수식은 배열로 입력한 AE6다음 필요한 만큼 오른쪽 아래로 채우거나 복사하여 붙여넣어야 합니다.

{=SUM(IFERROR(INDEX(6:6,N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))/$I$2,0))}

설명:

공식의 예쁜 버전은 다음과 같습니다.

{=
SUM(
  IFERROR(
    INDEX(
      (6:6),
      N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))
      +(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))
    )/$I$2,
    0
  )
)}

INDEX()12개월의 기간과 1개월의 지연에 대해 첫 번째 인수의 두 번째 인수 는 대략 다음과 동일하다는 점을 고려하면 공식을 훨씬 더 쉽게 이해할 수 있습니다.

COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-1

이 공식은 기본적으로 현재 셀을 기준으로 이전 I2개월 수의 순서에 액세스하기 위해 오프셋 배열을 생성하는 방식으로 작동합니다 .I1


공식을 단계별로 살펴보면 AK6위의 내용이 더 명확해집니다.

  • COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))
    →{1,2,3,4,5,6,7,8,9,10,11,12}
  • COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-$I$1
    → {37}-({31}-{6})-{1,2,3,4,5,6,7,8,9,10,11,12}-1
    →{10,9,8,7,6,5,4,3,2,1,0,-1}
  • N(IF(1,{10,9,8,7,6,5,4,3,2,1,0,-1}))
    → N({10,9,8,7,6,5,4,3,2,1,0,-1})
    →{10,9,8,7,6,5,4,3,2,1,0,-1}
  • {10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>=COLUMN($R:$R))
    → {10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>={18})
    → {10,9,8,7,6,5,4,3,2,1,0,-1}+{0,0,0,0,0,0,0,0,0,0,0,0}
    →{10,9,8,7,6,5,4,3,2,1,0,-1}
  • INDEX((6:6),{10,9,8,7,6,5,4,3,2,1,0,-1})/$I$2
    → INDEX(6:6,{10,9,8,7,6,5,4,3,2,1,0,-1})/12→ {24000,0,20000,0,0,"Opportunity Name1","bWmd1","Col C val","Col B val","Col A val","Col A val",#VALUE!}/12
    →{2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
  • SUM(IFERROR({2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0))
    →2666.67

INDEX(6:6,N(IF(1,expression)))의 두 번째 인수는 기본적으로 단일 값으로 평가되므로 Excel에서 배열을 반환하도록 하려면 필수 해킹입니다 . 그냥 사용 하면expressionINDEX()INDEX(6:6,expression)AK6

INDEX((6:6),COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))
→ INDEX(6:6,37-(31-6)-COLUMN($A$6:$L$6)-1+(37-(31-6)-COLUMN($A$6:$L$6)-1>=18))
→ → →INDEX(6:6,12-1-1+(12-1-1>=18))
INDEX(6:6,10)
24000

단일 값을 반환하는 표현식 내부에서는 범위의 첫 번째 셀 열을 반환합니다.COLUMN(multi-cell-range)

+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))2018년과 2019년 주문 테이블(열 R) 간의 차이를 조정 합니다. N(IF(이전에 사용된 핵이 이미 의 두 번째 인수에 대한 배열 평가를 강제하여 함수가 배열로 평가되었으므로 이 경우 해킹이 필요 INDEX하지 COLUMN()않습니다 .

이 IFERROR()함수는 시트 왼쪽에 가까운 셀에 수식이 존재하여 텍스트에 액세스하거나 열 왼쪽에 있는 셀에 액세스하려고 하는 경우에 필요합니다 A.

노트:

  • 예쁜 공식은 입력하면 실제로 작동합니다.
  • 가 자체 줄에 유지되도록 (6:6)하려면 예쁜 버전 주변의 괄호가 필요합니다.6:6

주의사항:

  • 주문 왼쪽에 있는 n개 열의 셀에는 숫자가 있을 수 없습니다.(여기서 n은 의 값으로 지정됩니다 I2). 숫자가 있으면 수식 그대로 수익 계산에 포함됩니다.
  • 2018년 12월 수익과 2019년 1월 수익 열 사이에는 간격이 있을 수 없습니다.원하는 경우 이러한 간격을 허용하도록 공식을 수정할 수 있습니다.
  • 두 주문 테이블 사이의 간격( R:R)은 정확히 한 열 너비로 유지되어야 합니다. 그렇지 않으면 수식은 있는 그대로 깨집니다.
  • 주문 간 테이블 간격에는 숫자가 포함될 수 없습니다. 그렇지 않으면 추가 주문으로 처리됩니다.

* 해킹이 작동하는 정확한 이유에 대한 설명은 제가 먼저 알아낼 때까지 기다려야 합니다 ;-)

관련 정보