☰

날짜가 다른 날짜보다 크고 n일 이내인 경우 날짜 범위 인덱싱

tag-icon • tag-icon microsoft-excel worksheet-function
날짜가 다른 날짜보다 크고 n일 이내인 경우 날짜 범위 인덱싱

안에이전 게시물특정 엔터티와 연결된 가장 최근 날짜를 찾고 해당 값이 Sheet2의 연결된 날짜보다 큰 경우 Sheet2에 해당 값이 반환되도록 하는 방법에 대해 질문했습니다. 그러나 진짜 문제는 조건을 만족하면 가장 최근 날짜를 반환하는 것이 아니라, 기준일로부터 2일(또는 일반적으로 n일) 이상일 경우 날짜를 반환하는 것이라는 사실을 깨달았습니다.

이전에 생성된 공식은 다음과 같습니다.

=IF(
     INDIRECT("LargerSheet!$B"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<$B1,                            //Compare with SmallerSheet date
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1)                            //"Else" return SmallerSheet date.

< $B1INDIRECT()에 다른 조건을 추가하여 그것이 맞는지 뿐만 아니라 날짜까지의 거리도 묻는 방법이 없습니까 <2?

나는 운없이 다음 공식을 시도했습니다.

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

샘플 시트를 찾을 수 있습니다여기.

답변1

문제가 어디에 있는지 확인하기 위해 귀하의 일련의 질문을 역추적해야 했지만 (사소한 수정만으로)@Hannu의 공식거의 맞습니다. 이를 구현할 때 발생하는 문제는 스프레드시트 레이아웃이 @jbmorch에서 지정한 것과 일치하지 않는다는 것입니다.이전 질문에 대한 답변.

레이아웃은 @jbmorch의 답변에 매우 명확하게 지정되어 있으며 몇 가지 이유로 중요합니다.

  • 시트 이름이 포함된 의사 수식 부분을 제외하고 질문에 스프레드시트 레이아웃에 대한 세부 정보를 많이 지정하지 않았습니다. 따라서 @jbmorch와 다른 사람들은 적절한 공식을 구성하기 위해 자신만의 레이아웃을 추측하고 만들어야 했습니다.
  • 답변에 지정된 대로 시트의 열 순서를 지정하지 않으면 조정하지 않으면 시트의 열에 대한 참조가 올바르지 않게 됩니다.
  • LargerSheet의 정렬 순서는 MATCH 기능 방식 때문에 중요합니다. 답변에 지정된 대로 행을 정렬하지 않은 경우 MATCH는 정확한 결과를 생성하지 않습니다.
  • @jbmorch의 답변은 (다른 정보가 제공되지 않았기 때문에) 데이터가 행 1에서 시작되었다고 가정했습니다. 따라서 답변은 그러한 스프레드시트에 대해 작성되었으며 실제 레이아웃에 맞게 조정되지 않으면 잘못된 결과가 생성됩니다.

알아야 할 몇 가지 다른 가능한 문제는 다음과 같습니다.

  • 모든 날짜/시간 항목이 실제로 날짜 및 시간 형식인지 확인해야 합니다. 그렇지 않으면 Excel에서 적절한 비교를 수행할 수 없습니다. 이는 셀 속성 아래의 형식 옵션에서 확인할 수 있으며 셀에 대해 계산을 시도해 볼 수도 있습니다. (예: A2에 가 포함되어 있고 1/7/2003B2에 가 포함된 경우 =A2+2B2의 값은 로 확인되어야 합니다 1/9/2003.)
  • 샘플 시트에는 LargerSheet에서 값을 반환하기 위해 찾고 있는 기준과 실제로 일치하는 Sheet1의 항목이 거의 없습니다. 이로 인해 문제 해결이 약간 번거로워집니다. 실제로 내가 찾은 유일한 일치 항목은 행 9였습니다. (Sheet1의 해당 행에 있는 DAILY, JIM의 날짜는 실제로 LargerSheet의 최신 날짜로부터 2일 이내였습니다.)

즉, 여기에 필요한 공식이 있습니다. Sheet1의 C2에 놓고 복사합니다.

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

다시 한 번 시트 형식이 지정되었는지 주의 깊게 살펴보세요.정확히아래와 같이 그렇지 않으면 조정 없이 수식이 작동하지 않습니다.

  • 모든 날짜~ 해야 하다텍스트나 숫자가 아닌 날짜 형식으로 지정해야 합니다.
  • 두 시트 모두~ 해야 하다A열에 이름이 있고 B열에 날짜가 있으며 실제 데이터는 행 2부터 시작됩니다.
  • 큰 시트~ 해야 하다정렬 우선순위가 설정된 이름(오름차순) 및 날짜(오름차순)로 정렬됩니다.그와 같은 순서로.

또 한 가지 알아두어야 할 점은 "2일 이내"와 "48시간 이내"의 차이입니다. 이 공식에서는 후자를 사용합니다. 즉, 시간이 5/6/2012 03:00:00Sheet1에 있고 LargerSheet의 해당 값이 5/8/2012 03:00:01이면 IF 문은 FALSE로 평가되어 LargerSheet의 값 대신 Sheet1의 값을 반환합니다. "48시간 이내의 언제든지"가 아닌 "향후 이틀 동안의 임의의 시간"을 일치시키려는 경우 이를 설명하기 위해 상당한 수식 수정이 필요합니다.

>또한 IF 문은 크거나 같음 대신 독점적인 보다 큼( )을 사용하기 때문에 시간이 정확하게 일치하면 FALSE로 평가됩니다. 정확한 일치에 대해 TRUE를 평가하려면 >로 바꾸십시오 >=.

답변2

참고 사항: 저는 IF()를 보고 조건문을 변경하고 있습니다.
이것이 효과가 없다면 최소한 작성/변경 방법에 대한 아이디어를 제공할 수 있습니다.

=IF(
     AND(INDIRECT("LargerSheet!$B"& //"$B"로 시작하고 다음으로 끝나는 셀
     MATCH($A1,LargerSheet!$A:$A,1) //이름의 마지막 날짜 행.
     )<$B1, //SmallerSheet 날짜와 비교
     INDIRECT("LargerSheet!$B"& //"$B"로 시작하고 다음으로 끝나는 셀
     MATCH($A1,LargerSheet!$A:$A,1) //이름의 마지막 날짜 행.
     )<($B1+2)), //SmallerSheet(날짜-2)와 비교
     INDIRECT("LargerSheet!$B"& //"Then"은 LargerSheet 날짜를 반환합니다.
     MATCH($A1,큰 시트!$A:$A,1)
     ),$B1)

... 중요한 추가 사항은 IF의 첫 번째 항목 AND( condition1, condition2 )사이에 있습니다.IF(,

관련 정보