☰

Excel에서 공통점으로 날짜 추출하기

tag-icon • tag-icon microsoft-excel
Excel에서 공통점으로 날짜 추출하기

비가 올 때, 어떤 상태에서 기록된 데이터 더미를 상상해 보세요.

State/Date 01Jan 02Jan 03Jan 04Jan 05Jan 
Alabama     YES   YES   NO    YES   NO
Alaska      YES   YES   YES   NO    YES
Florida     NO    NO    NO    YES   NO
Nevada      NO    YES   NO    YES   NO

특정 주에 비가 내린 날짜 목록을 제공하는 공식을 어떻게 작성합니까? 예를 들어, 네바다주에 언제 비가 내렸는지 알고 싶습니다. 02Jan, 04Jan셀에서 답을 얻을 수 있나요 ?

답변1

아래 show 메소드는 문제를 해결합니다.

여기에 이미지 설명을 입력하세요

작동 방식:

  • 더 빠른 결과를 얻으려면 셀 A28의 상태에 대한 드롭다운을 만듭니다.
  • 데이터 탭을 누른 다음 데이터 유효성 검사를 누르세요.
  • 목록 소스로 D28:D31을 선택합니다.

여기에 이미지 설명을 입력하세요

  • 이제 비가 오거나 비가 오지 않을 경우 데이터 유효성 검사를 사용하여 B28 셀에 드롭다운을 만들고 목록 소스에 예 및 아니요를 추가합니다.

여기에 이미지 설명을 입력하세요

  • 이제 A35 셀의 최종 배열(CSE) 수식은 다음과 같습니다.

    {=IFERROR(INDEX($E$27:$I$27,1,SMALL(IF(INDEX($E$28:$I$31,MATCH($A$28,$D$28:$D$31,0),)=$B$28,COLUMN($A$26:$E$26)),ROW(1:1))),"")}

주의

  • 다음으로 수식을 완성하세요.Ctrl+Shift+Enter& 작성하세요.

  • 필요에 따라 수식의 셀 참조를 조정합니다.

답변2

사실, 데이터로 피벗 테이블을 만드는 것이 아마도 최선의 접근 방식일 것입니다. 그러나 수식 접근 방식을 원하는 경우:

데이터가 머리글을 포함하여 A1:F5 셀에 있다고 가정해 보겠습니다. 그런 다음 조회 값이 G8 셀에 있으면 다음이 수행됩니다.

=TEXTJOIN(", ",TRUE,TEXT(IFERROR(IF(IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")/IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0")>0,IF(XLOOKUP(G8,A2:A5,B2:F5)="YES",B1:F1,"0"),""),""),"dd-mmm"))

다음과 같은 출력을 제공합니다.

1월 2일, 1월 4일

"Nevada"를 검색한 경우.

등등.

기본적으로 첫 번째를 사용하여 XLOOKUP()해당 상태에 적용되는 YES 및 NO 데이터 행을 찾습니다. IF()NO의 경우 0을 제공하고 YES의 경우 헤더 행에 날짜를 제공하는 래핑입니다 . 이는 이전 결과를 자체적으로 나누어(ERROR와 1을 생성함) 사용하는 an으로 래핑되어 IF()결과가 1보다 0인지 확인합니다. 해당 오류는 계속 진행되지만 두 번째 오류는 IF()1을 다시 날짜로 대체합니다. 그런 다음 IFERROR()이를 래핑하여 ERROR가 아닌 날짜인 항목으로만 결과를 줄입니다. 이 시점에서는 Excel 정수 날짜 값(예: 43563)이며 헤더 행에 표시되는 형식이 필요한 것처럼 보이므로 TEXT()형식을 지정합니다. 그렇게 함으로써 "숫자성"을 잃는다는 사실은 문제가 되지 않습니다. 목록에 나와 있는 경우(글쎄, 단 하나의 날짜만 반환되지 않는 한... "기술적"이기를 원한다면) 그렇게 하기 때문입니다. ..). 마지막으로 TEXTJOIN()가독성을 위해 "쉼표 공백" 구분 기호를 제공합니다. 따라서 추가 작업에 필요한 경우 하나 또는 전체를 추출하는 데 사용할 수 있는 구분 기호가 있습니다.

아마도 다른 접근 방식이 있을 수 있습니다. XLOOKUP()오랫동안 사용되어 온 "이중 INDEX/MATCH" 기법의 버전은 하나일 수 있습니다. 여러 번의 휴식을 취하면 XLOOKUP()여러 가지 응답(예상대로 5개)이 제공되었지만 그들은 1월 1일 세 개와 02월 2일 두 개였습니다. 예를 들어 1월은 제가 원했던 1월 2일과 1월 4일이 아닙니다. 지금은 새벽 3시인데 아마도 제가 뭔가를 놓쳤을 수도 있습니다. FILTER()너무 빨리 행동하여 네바다에 대한 두 날짜를 얻기 위한 위치 정보를 잃어버렸습니다(즉시 두 결과만 표시되지만 이제는 2-4가 아닌 1-2이므로 헤더에서 날짜를 가져올 수 있는 좋은 방법이 없습니다). 다른 생각일 수도 있습니다. 그런데 또 새벽 3시...

아마도 각각은 위와 같은 요소 더미이기 때문에 중요하지 않을 것입니다. 따라서 단순해 보이는 것으로 줄이는 것은 아마도 일어날 수 없을 것입니다.

관련 정보