☰

날짜 범위를 기준으로 데이터 검색 및 삽입

tag-icon • tag-icon microsoft-excel worksheet-function
날짜 범위를 기준으로 데이터 검색 및 삽입

~ 안에다른 게시물, 항목과 관련된 날짜가 다른 시트의 날짜보다 작은 경우 항목을 검색하고 삽입하는 데 도움이 되는 수식이 제공되었습니다. 공식은 다음과 같았습니다.

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

그러나 이것은 원하는 대로 작동하지 않았습니다. 내 문제가 약간 다른 것일 수도 있는지 궁금합니다. 본질적으로 내가 하고 싶은 일은 SmallerSheet의 특정 항목에 대해 LargerSheet의 여러 개체와 관련된 날짜를 확인하고 Most Recent Date < SmallerSheet Date인 경우에만 LargerSheet에서 가장 최근 날짜를 반환하는 것입니다. 위의 수식은 지금 수행 중인 작업이 SmallerSheet 날짜가 LargerSheet의 날짜 중 하나 이상보다 최신인지 확인하는 것입니다. 그러면 각 SmallerSheet 항목에 연관된 여러 항목이 있기 때문에 문이 항상 true가 됩니다. LargerSheet는 시간을 아주 오래 전으로 거슬러 올라갑니다. 이게 쉽게 고칠 수 있나요?

답변1

LargerSheet의 데이터를 정렬할 수 있다고 가정하면 다음과 같이 문제를 해결할 수 있습니다.

먼저 LargerSheet에서 사용자 정의 정렬을 수행하십시오. 먼저 이름(AZ)으로 정렬하고 두 번째로 날짜(오래된 것부터 최신 것까지)로 정렬합니다. 이제 동일한 이름 항목이 모두 함께 그룹화되고 각 그룹의 마지막 항목이 해당 이름의 가장 최근 날짜가 됩니다.

--A-- --B-- Alice 2003-08-20 Alice 2005-01-01 Alice 2006-05-16 Bob 2001-08-19 Bob 2003-01-01 Bob 2004-05-15 Charlie 2004-08-19 : :

그런 다음 SmallerSheet의 각 이름 옆 열에서 다음 수식을 사용합니다(LargerSheet에서와 같이 이름이 A 열에 있고 날짜가 B 열에 있다고 가정). 공백과 주석을 제거하세요.

=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.

SmallerSheet의 날짜(각 이름에 대해)보다 작은 경우 LargerSheet의 가장 최근 날짜를 반환하고, 그렇지 않은 경우 SmallerSheet의 날짜를 반환해야 합니다.

날짜 선택에 더 많은 조건을 넣어야 한다면 "AND"와 "OR" 논리 함수를 사용해 보거나 테스트 자체를 변경해 보세요. 예를 들어 수식을 다음과 같이 수정하면 SmallerSheet의 날짜로부터 -2일 이내에 있는 한 LargerSheet에서 가장 최근 날짜가 반환되고 LargerSheet의 C 열 값이 0보다 큰 경우에도 반환됩니다. . 보시다시피 수식이 다루기 어려워 보이기 시작하므로 괄호를 일치시키고 구문을 확인하도록 주의해야 합니다. 여러 열을 사용하여 수식을 여러 단계로 나누는 것이 도움이 될 수 있습니다.

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))>=($B1-2),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))<$B1,INDIRECT("LargerSheet!$C"&MATCH($A1,LargerSheet!$A:$A,1))>0),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1)),$B1)

마지막으로 이 문제를 다른 방식으로 접근할 수도 있습니다. SmallerSheet의 이름에 대한 단일 항목을 조회하는 열을 LargerSheet에 추가하고, 테스트를 수행하고, 테스트에 따라 TRUE/FALSE 값을 반환합니다.

답변2

MATCH가 첫 번째 함수를 반환하므로 나열된 것과 다르게 작동하는 수식을 제공하겠습니다.

이 수식은 배열 수식이므로 다음을 사용하여 입력됩니다.CTRL+SHIFT+ENTER.

이는 큰 테이블이 A 열에 있다고 가정합니다. 비교하려는 날짜는 E2 셀에 있습니다.

=MAX(IF($A$1:$A$33<E2,$A$1:$A$33,0))

큰 테이블의 각 셀에 대해 if 문을 수행합니다. 셀이 E2보다 작은 경우 셀이 E2보다 크면 해당 날짜(Excel이 숫자로 저장함)를 반환합니다. 이 모든 if 문의 최대값을 취하여 E2보다 작은 가장 큰 날짜를 반환합니다.

관련 정보