☰

tag-icon • tag-icon microsoft-excel worksheet-function

나는 다음과 유사한 Excel 파일을 다루고 있습니다(개인 정보 보호를 위해 변경/간소화).

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

주문하다 # 일
이 주문의 일부로                                                                       자동차가 세차되었습니까 ?
1 검토 예
1 타이어 교체 예
1 세차 예
2 검토 아니요
2 타이어 교체 아니요
2 오일 교환 아니요

다른작업같은 것을 공유할 수 있다주문하다 #.

세 번째 열(이미지의 노란색)을 "예"로 채우는 수식이 필요합니다.일의 일부입니다주문하다 #여기에는 다음이 포함됩니다.일"세차", 그렇지 않은 경우 "No"를 반환합니다.

나는 성공하지 못한 채 온라인에서 해결책을 찾았습니다.

답변1

아래는 5가지 공식의 진행입니다. 각 공식은 이전 공식을 기반으로 합니다. 이런 식으로 진행하면 나중에 이해하기가 더 쉬워지기를 바랍니다.

[ 5의 공식 1 ]   기본 공식

=IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "yes"),"no")
  1. 만약에기능 용도성냥.logical_test​
    • 성냥각 주문을 차례로 사용합니다.search_key
    • 인수 range는 다음을 사용하여 채워집니다.필터작업이 "세차"인 주문 목록을 반환하는 함수
  2. 그만큼만약에함수의 value_if_true인수는 다음과 같습니다"Yes"
  3. value_if_false사용된 것은 반환 또는 logical_test반환만 가능하므로 생략됩니다 .TRUE#N/A
  4. IFERROR오류를 다음으로 대체합니다."No"

[ 5의 공식 1 ] 기본 공식

[ 수식 2/5 ]   빈 행 무시

=IF(A2:A7<>0, IFERROR(IF(
   MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
   "Yes"),"No"),"")
  1. 빈 행을 무시하려면만약에value<>0기능 은 으로 추가됩니다 logical_test. 다음이 반환됩니다.
    • TRUE0이 아닌 숫자를 포함하는 모든 셀과 텍스트 문자열(숫자를 포함하는 문자열 포함)에도 적용됩니다. 예.("0"<>0)=TRUE
    • FALSE0Excel이 0으로 강제하는 빈 셀뿐만 아니라 숫자에도 적용됩니다 . 예. 그렇다면OR(ISBLANK(A1),A1=0)​(A1<>0)=FALSE
  2. 그만큼기본 공식로 사용됩니다value_if_true
  3. value_if_false빈 문자열입니다 "".
    이 접근 방식은 "No"데이터 공백으로 인한 행 오프셋을 방지하면서 반환을 건너뜁니다.

[ 수식 2/5 ] 빈 행 무시

[ 5 중 수식 3 ]   제목 행의 수식

수식을 머리글 행으로 이동하면 덮어쓰는 것을 방지할 수 있습니다.

=VSTACK("Wash on Order?",
  IF(A2:A7<>0, IFERROR(IF(
     MATCH(A2:A7,FILTER(A2:A7,B2:B7="Wash car"),0),
     "Yes"),"No"),""))
  1. VSTACK배열을 수직으로 쌓을 수 있습니다
  2. 사용된 첫 번째 배열은 제목이었습니다."Wash on Order?"
  3. 두 번째 배열은 이전 수식입니다.

[ 5 중 수식 3 ] 제목 행의 수식

[ 5의 수식 4 ]   LET 기능 추가

하는 동안허락하다단독으로는 이전 수식에 많은 것을 추가하지 않으며 변환을 명확하게 하기 위해 이 단계에 적용됩니다. 공식이 더욱 복잡해짐에 따라허락하다더 명확해집니다.

=LET(rng,A2:B7,
   a,DROP(rng,,-1), b,DROP(rng,,1),
   VSTACK("Wash on Order?",
     IF(a<>0, IFERROR(IF(
       MATCH(a, FILTER(a, b="Wash car"), 0),
       "Yes"), "No"), "")))
  1. 그만큼허락하다함수를 사용하면 재사용을 위해 값과 수식을 변수에 저장할 수 있습니다. 이렇게 하면 코드 반복과 수식 길이가 줄어들고 복잡한 수식을 더 쉽게 이해하고 관리할 수 있습니다.
  2. 소스 범위는 에 저장됩니다 rng.
  3. 열 A a와 열 B는 b다음을 사용하여 반환됩니다.떨어지다불필요한 열을 파견합니다.
  4. a이제 수식 에서 및 b대신에 사용됩니다 .A2:A7B2:B7

[ 5의 수식 4 ] LET 기능 추가

[ 수식 5/5 ]   자동으로 범위 크기 조정

수식을 수정하지 않고도 데이터가 증가할 수 있습니다. 전체 열을 허용한 다음 채워진 데이터를 기반으로 필요에 따라 배열 크기를 조정합니다. 의 가치허락하다전체 화면에 표시됩니다.

=LET(rng,A:B,  
  arr,DROP(FILTER(rng, ROW(rng)<=
    MAX((rng<>0)*(ROW(rng)))),1),
  a,DROP(arr,,-1), b,DROP(arr,,1),
  VSTACK("Wash on Order?",
    IF(a<>0, IFERROR(IF(MATCH(a,
      FILTER(a, b="Wash car"), 0),
      "yes"),"no"),"")))
  1. 필터arr행 번호가 <=주문 ID를 포함하는 마지막 행 의 행 번호인 행 배열을 반환합니다 .
  2. 마지막 행은 다음을 적용하여 계산됩니다.최대행에 0이 아닌 값이 포함된 행 번호 배열로 변환됩니다. 별표 *는 'and' 연산자입니다.MAX(ROW(rng)*rng<>0)
  3. 결과 배열에는 제목(1행), 순서 및 간격이 포함됩니다. 의도적이거나 우발적인 간격으로 인해 결과가 원래 범위에서 벗어나지 않도록 하기 위해 간격이 포함됩니다.
  4. 그만큼떨어지다함수는 제목 행을 제거하는 데 사용됩니다. 또는 다음을 추가하여 이를 달성할 수 있습니다.필터상태ROW(rng)<>1

[ 수식 5/5 ] 자동으로 범위 크기 조정

답변2

다음 솔루션은 샘플 데이터에 설정된 목표를 충족하는 것으로 보입니다.

  1. 빈 열에서 각 행에 대해 (셀 내 ) 과 열을 C연결합니다 .AB=concat(A2,B2)C2

  2. 빈 열에 에서 D시작하는 다음 수식을 추가합니다 D2. 이것은 배열 수식이므로 CTRL+SHIFT+ENTER작동하려면 동시에 눌러야 합니다 .

    =IF(OR($A$1:$A$6=A1)*(OR($C$1:$C$6=CONCAT(A1,"Wash car"))),"Yes","No")

  3. D2나머지 행에 복사합니다 .

제가 간과하고 있는 부분이 있는 경우(샘플 데이터 세트가 너무 제한되어 있기 때문에 간과하고 있는 것 같습니다) 알려주시면 수정하도록 노력하겠습니다. 이 해결 방법으로 문제가 해결되면 답변으로 표시해 주세요.

관련 정보