Excel 재고 조회에서 일치하는 규칙 중 가장 구체적인 규칙을 선택하려면 먼저 적격 행을 찾은 다음, 지정된 필드 수를 세세요. 최고 점수가 유일한 행의 수량을 반환하고, 동점은 표시하세요. 이 안내에서는 Mac용 Excel에서 가상의 아트 자산을 대상으로 이 결과를 만드는 방법을 설명합니다.

이 방법을 지원하는 Mac용 Excel 버전은 무엇인가요?

아래 수식은 LET, XLOOKUP 및 동적 배열 계산을 사용합니다. Microsoft는 이러한 기능이 Microsoft 365용 Excel의 Mac 버전, Mac용 Excel 2024 및 Mac용 Excel 2021에서 지원된다고 안내합니다. Mac용 Excel 2021 기능 안내에는 LET, XLOOKUP 및 동적 배열이 명시적으로 포함되어 있습니다. Mac용 Excel 2021의 새로운 기능, LET 함수.

Microsoft의 명시적인 호환성 안내에 따르면 Excel 2016 및 Excel 2019는 XLOOKUP를 지원하지 않습니다. 이 수식을 그대로 사용하려면 지원되는 Mac용 버전을 사용하세요. 수식 입력 방법을 바꿔도 누락된 함수를 사용할 수 있게 되지는 않습니다. 이 문서 뒷부분의 선택 사항인 확인 수식은 FILTER를 사용하며, 위의 세 가지 지원 대상 Mac용 버전에서도 지원된다고 안내되어 있습니다. XLOOKUP 함수, FILTER 함수.

재고에서 “가장 구체적인” 규칙은 무엇을 의미하나요?

이 예에서는 자산 라벨, 마감, 크기라는 세 가지 속성을 사용해 아트 자산에 할당된 수량을 조회합니다. 규칙 필드가 비어 있으면 해당 규칙은 그 속성의 값을 모두 허용합니다. 필드에 값이 있으면 해당 입력과 일치해야 합니다. 적격 규칙 중 값이 채워진 필드가 가장 많은 규칙을 선택합니다.

이는 이 예를 위해 정한 명시적인 통합 문서 정책입니다. Excel은 설명만 보고 이 정책을 추론하지 않습니다. 여기서는 모든 속성의 중요도를 동일하게 봅니다. 자산과 마감을 지정한 규칙은 자산과 크기를 지정한 규칙과 구체성 점수가 같습니다. 둘 다 일치하고 최고 점수가 같으면 수식은 동점을 표시합니다.

일반적인 XLOOKUP만으로는 우선순위를 결정할 수 없습니다. Microsoft의 안내에 따르면 이 함수는 기본적으로 처음 일치하는 항목을 반환합니다. 검색 방향을 바꾸면 어느 쪽 끝에서 검색을 시작할지만 달라질 뿐, 구체성에 따라 규칙을 순위화하지는 않습니다. 따라서 수량을 XLOOKUP로 조회하기 전에 수식이 우선 적용할 행을 계산합니다. XLOOKUP 함수.

가상의 아트 자산 규칙 설정하기

빈 워크시트의 A1:D6에 다음 머리글과 값을 입력하세요. 아래 표의 빈 셀은 Excel에서도 비워 두어야 합니다. 모든 라벨과 수량은 예시입니다.

워크시트 행 A: 자산 B: 마감 C: 크기 D: 수량
2 30
3 Tile 18
4 Tile Matte 12
5 Tile Matte Small 7
6 Disc Large 10

2행의 규칙은 의도적인 기본값입니다. 어떤 속성도 지정하지 않아 구체성 점수는 0입니다. 3행은 속성 하나, 4행은 둘, 5행은 셋을 지정합니다. 이 수량은 서로 대체되는 할당 수량이므로 조회 결과는 값 하나입니다. 기본값과 예외 수량을 합산하지 않습니다.

H1:K1에 Asset, Finish, Size, Result 머리글을 입력하세요. 그런 다음 H2에 Tile, I2에 Matte, J2에 Small을 입력하세요. 세 입력 속성은 모두 채워 두세요. 이 설계에서는 규칙 표에서만 빈칸을 제한 없는 조건으로 취급합니다. 입력이 불완전하면 메시지를 표시합니다.

수량은 적절한 경우 숫자 0을 포함해 숫자 형식으로 입력하세요. 범위는 실제 규칙 행으로 한정하세요. 사용하지 않는 빈 행까지 범위에 포함하면 이 일치 정책에서 추가적인 모든 필드가 빈 규칙이 생깁니다.

우선 적용할 규칙을 선택하는 수식 입력하기

K2을 선택하고 아래의 완전한 수식을 입력한 다음 Return을 누르세요. 위에서 정의한 규칙 표와 입력 셀을 사용합니다. 동적 배열 계산은 수식 안에서 이루어지며, 최종 결과는 한 셀에 표시됩니다. Microsoft 안내에 따르면 동적 배열 수식은 기존 배열 수식용 키 입력 조합 없이 일반적인 방법으로 입력합니다. 동적 배열 수식 및 분산 배열 동작.

=IF(OR(H2="",I2="",J2=""),"Complete the asset details",
  LET(
    asset,$A$2:$A$6,
    finish,$B$2:$B$6,
    size,$C$2:$C$6,
    qty,$D$2:$D$6,
    eligible,
      (((asset="")+(asset=H2))>0)*
      (((finish="")+(finish=I2))>0)*
      (((size="")+(size=J2))>0),
    specificity,--(asset<>"")+--(finish<>"")+--(size<>""),
    best,MAX(IF(eligible,specificity,-1)),
    winner,eligible*(specificity=best),
    IF(SUM(eligible)=0,"No matching rule",
      IF(SUM(winner)>1,"Tie: review rules",
        IF(XLOOKUP(1,winner,ISNUMBER(qty),FALSE,0),
          XLOOKUP(1,winner,qty,"No matching rule",0),
          "Check rule quantity"
        )
      )
    )
  )
)

예시 입력을 사용하면 K2에 7이 반환되어야 합니다. 네 가지 규칙이 적격이지만 세 속성을 모두 지정한 규칙은 5행뿐입니다.

적격성과 구체성 계산 방식

LET는 범위와 중간 결과에 로컬 이름을 지정해 선택 과정을 쉽게 확인할 수 있도록 합니다. Microsoft는 이 이름이 수식 범위 내에서만 유효하다고 안내합니다. LET 함수.

각 속성의 적격성 표현식은 규칙 셀이 비어 있거나 입력과 같은지 확인합니다. 덧셈은 두 가능성을 결합하고, 비교 연산은 그 결과를 논리값으로 바꿉니다. 세 속성 테스트를 곱하면 모든 조건을 통과해야 합니다. Microsoft는 FILTER 예시에서 배열 조건을 AND 논리로 결합할 때 같은 곱셈 패턴을 사용합니다. FILTER 함수.

specificity은 값이 채워진 규칙 필드의 수를 셉니다. 이중 단항 연산자는 비어 있지 않은지 확인한 결과를 숫자로 변환해 0부터 3까지의 점수를 만듭니다. IF는 순위 계산에서 부적격 행에 -1점을 부여하고, MAX은 적격 행 중 가장 높은 점수를 찾습니다. -1점은 부적격 행이 제한 없는 기본값의 0점보다도 낮게 유지되도록 합니다. IF 함수, MAX 함수.

winner은 최고 점수를 받은 적격 행만 표시합니다. 수식은 수량을 반환하기 전에 일치 항목이 없거나 우승 규칙이 여러 개인지 확인합니다. 마지막 숫자 검사는 의도적으로 수량이 빈칸이거나 텍스트로 저장된 숫자인 경우를 거부합니다. Microsoft 안내에 따르면 ISNUMBER는 텍스트로 된 숫자를 숫자 값으로 변환하지 않습니다. 숫자 0은 유효한 결과입니다. IS 함수.

워크시트를 확장하기 전에 결과 확인하기

H2:J2를 아래 조합으로 바꾸세요. 예상 결과는 샘플 규칙에서 도출한 값이며, 실제 재고 관측 결과가 아닙니다.

자산 마감 크기 예상 결과 우선 규칙 조건
Tile Matte Small 7 지정된 필드 세 개
Tile Matte Large 12 자산과 마감
Tile Gloss Small 18 자산만
Disc Gloss Large 10 자산과 크기
Arc Matte Small 30 제한 없는 기본값

그런 다음 6행을 자산 Tile, 마감 Matte, 크기 Small, 수량 9로 임시 변경하세요. 원래 입력을 복원하면 결과가 Tie: review rules로 바뀌어야 합니다. 이제 세 가지 일치 속성을 모두 지정한 규칙이 두 개입니다. 수량이 같더라도 규칙 선택이 모호하므로 동점으로 처리됩니다. 작업이 끝나면 6행을 원래대로 복원하세요.

일치 항목 없음 분기를 테스트하려면 수식의 규칙 범위 네 개를 임시로 3행부터 시작하도록 변경해 기본값을 제외하세요. 입력이 Arc, Matte, Small이면 No matching rule이 반환되어야 합니다. 테스트를 마치면 범위를 2행부터 시작하도록 복원하세요. 수량 검사를 테스트하려면 원래 입력을 사용하면서 D5을 비우세요. Check rule quantity가 표시되어야 합니다. 그런 다음 7을 복원하세요.

서로 겹치는 규칙을 확인하고 동점 해결하기

현재 입력에 적격한 규칙을 모두 보려면 M2에 별도의 수식을 입력하세요. 먼저 M2:P6을 비워 두세요. 출력에는 일치하는 규칙 행이 원래 순서대로 표시되며, 순위가 매겨지지는 않습니다.

=FILTER(
  $A$2:$D$6,
  ((($A$2:$A$6="")+($A$2:$A$6=H2))>0)*
  ((($B$2:$B$6="")+($B$2:$B$6=I2))>0)*
  ((($C$2:$C$6="")+($C$2:$C$6=J2))>0),
  "No matching rule"
)

FILTER는 불리언 포함 배열로 선택된 행을 반환하며, 마지막 인수는 조건을 충족하는 행이 없을 때 표시할 메시지를 지정합니다. 이 확인 결과에서는 원본의 빈 셀이 0으로 표시될 수 있습니다. 원래 규칙 표의 빈 셀은 계속 제한 없는 조건으로 취급하세요. FILTER 함수.

확인 수식은 주변 셀로 분산됩니다. Excel 표 바깥에 입력하고 출력 영역을 비워 두세요. Microsoft 안내에 따르면 분산 배열 수식은 표 안에서 지원되지 않으며, 출력 영역에 값이 있으면 #SPILL! 오류가 발생할 수 있습니다. 동적 배열 수식 및 분산 배열 동작.

중복을 수정하거나, 겹치는 규칙의 적용 범위를 좁히거나, 추가 우선순위 정책을 정해 동점을 해결하세요. 예를 들어 자산과 마감 규칙, 자산과 크기 규칙은 이 예에서 모두 2점을 받습니다. 어느 속성이 더 중요한지 선호도를 입력하지 않으면 통합 문서가 결정할 수 없습니다. 이 문서에서 사용하는 정책에서는 행의 위치를 바꿔도 결과가 달라지지 않아야 합니다.

의미를 바꾸지 않고 조회 확장하기

다른 자산의 세 속성을 H3:J3에 입력하고 K2을 K3에 복사하세요. 입력 참조는 수식과 함께 이동하고, 달러 기호는 규칙 범위를 고정합니다. 6행 이후에 규칙을 추가할 때는 기본 수식의 범위 네 개와 확인 수식의 두 차원을 모두 일관되게 확장하세요.

각 속성은 별도 열에 두고, 불필요한 공백 없이 일관된 라벨을 사용하세요. 이 수식은 각 필드의 전체 값을 비교합니다. 여러 속성이 합쳐진 자산 설명 안에서 일부 문자열을 찾지는 않습니다. 수량 검사로 선택된 값이 숫자인지는 확인하므로, 음수가 아닌 정수 수량과 같은 추가 재고 요건은 원본 데이터에 적용하세요.

완성된 조회는 명확한 순서로 판단합니다. 입력이 모두 있는지 확인하고, 적격 규칙을 찾고, 가장 높은 구체성을 확인하고, 동점을 거부한 다음, 선택된 숫자 수량을 반환합니다. 이 순서에 따라 일반적인 기본값과 상세한 예외를 함께 사용할 수 있으며, 해결되지 않은 선택은 눈에 띄게 표시됩니다.

출처