Excel for Mac에서 아트 에셋 레이블과 관련된 모든 수량을 반환하려면 FILTER를 사용하세요. 결과 배열은 워크시트 셀에 펼쳐지거나, 다른 계산에 사용되거나, 반복해서 일치하는 항목을 잃지 않고 합산될 수 있습니다. Microsoft는 FILTER가 지정된 기준에 따라 배열을 반환한다고 설명합니다. FILTER 함수.

이 안내에서는 합성 그래픽 에셋 레이블과 수량을 사용합니다. 레이블 하나를 선택하고, 해당 레이블에 대한 모든 수량을 가져온 다음, 각 숫자를 계산에 재사용하는 작업입니다.

어떤 Excel for Mac 버전에서 이 방법을 지원하나요?

Microsoft의 최신 함수 문서에는 Excel용 Microsoft 365 for Mac, Excel 2024 for Mac, Excel 2021 for Mac에서 FILTER를 지원한다고 명시되어 있습니다. 아래 수식을 사용하려면 이 에디션 중 하나를 사용하세요. FILTER 함수.

설치된 버전을 확인하려면 Excel을 열고 Excel → Excel 정보를 선택하세요. Microsoft는 이 Mac 메뉴 경로를 안내하며, 버전 확인 가이드에서는 Mac 앱의 정보 대화상자에 버전 번호와 라이선스 유형이 표시된다고 설명합니다. 빌드 번호뿐 아니라 에디션도 확인하세요. 다른 버전의 Office for Mac과 문서 공유; Office 정보: 사용 중인 Office 버전 확인.

Excel 2019 for Mac 및 Excel 2016 for Mac은 FILTER의 문서상 제공 범위에 포함되지 않습니다. 두 에디션 중 하나를 사용한다면, 이 동적 배열 워크플로를 지원하는 방법은 통합 문서를 명시된 Mac 에디션 중 하나에서 여는 것입니다. 아래 지침은 이 전제 조건에 의존하며, 기존 배열 수식의 대안이 아닙니다.

간단한 에셋 수량 예제 설정하기

빈 워크시트의 A1:B7에 다음 데이터를 입력하세요. 첫 번째 행은 머리글입니다. B6의 0을 포함해 수량은 숫자로 입력하세요.

워크시트 행 A열: 에셋 B열: 수량
1 에셋 수량
2 Tile A 4
3 Arc B 7
4 Tile A 2
5 Dot C 5
6 Tile A 0
7 Tile A 4

D1에 선택한 에셋을 입력하고 D2에 Tile A를 입력하세요. F1에는 일치하는 수량을 입력하세요. 결과가 펼쳐질 공간을 확보할 수 있도록 F2 아래의 셀은 비워 두세요.

이 샘플에는 같은 레이블의 수량 4가 두 개, 수량 0이 하나 포함되어 있습니다. 올바른 결과는 일치하는 네 행 모두를 유지해야 합니다. 즉 4, 2, 0, 4입니다. 반복되는 값을 제거하거나 0을 제외하면 요청한 결과가 달라집니다.

FILTER로 일치하는 모든 수량 반환하기

F2를 선택하고 다음 수식을 입력한 다음 Return을 누르세요.

=FILTER(B2:B7,A2:A7=D2)

이 수식은 수량 배열로 B2:B7을 사용하고 포함 여부 검사로 A2:A7=D2을 사용합니다. 두 범위는 동일한 여섯 개의 원본 행을 포함하므로, 포함 배열의 높이나 너비가 호환되어야 한다는 Microsoft의 요구 사항을 충족합니다. FILTER 함수.

샘플 데이터에서는 결과가 F2:F5에 표시됩니다.

셀 결과
F2 4
F3 2
F4 0
F5 4

수식은 F2에만 입력하세요. Excel이 나머지 결과를 인접한 셀에 자동으로 배치합니다. Microsoft는 이 동작을 스필이라고 부릅니다. 나중에 수식을 변경하려면 결과의 왼쪽 위 셀인 여기서 F2를 편집하세요. 동적 배열 수식 및 스필된 배열 동작.

수량 4가 두 번 나타나는 것은 서로 다른 원본 행이 일치했기 때문입니다. 다음 계산에서 선택된 에셋의 모든 항목을 반영해야 한다면 두 값 모두 유지하세요.

XLOOKUP으로는 이 네 행을 가져올 수 없는 이유

Microsoft는 XLOOKUP가 처음 찾은 일치 항목에 해당하는 항목을 반환한다고 설명합니다. 일치하는 행의 여러 열을 반환할 수는 있지만, 같은 레이블을 포함하는 모든 행을 열거하는 기능은 아닙니다. XLOOKUP 함수.

예를 들어 다음 수식은 샘플에서 4만 반환합니다.

=XLOOKUP(D2,A2:A7,B2:B7)

일치하는 행 하나가 답을 제공하는 경우에는 이 결과가 적절합니다. 이 작업에서는 서로 다른 네 행이 수량을 제공하므로 FILTER 결과를 사용하세요.

배열을 추가 계산에 재사용하기

전체 결과를 참조하려면 F2#를 사용하세요. Microsoft는 해시 기호를 스필 범위 연산자로 설명합니다. 시작 셀에서 생성된 배열 전체를 참조합니다. 따라서 후속 수식을 현재 네 셀 결과에 고정하지 않아도 됩니다. 배열 수식 지침 및 예제.

더 많은 일치 수량이 추가될 공간을 남기려면 후속 수식을 결과 바로 아래가 아니라 옆에 두세요. 다음 예에서는 H열부터 J열까지 사용합니다.

일치하는 수량 모두 더하기

H1에 총 수량을 입력한 다음 H2에 다음 수식을 입력하세요.

=SUM(F2#)

예시 결과는 10입니다. 4 더하기 2 더하기 0 더하기 4입니다. SUM는 범위를 받아 숫자 값을 더합니다. SUM 함수.

합계만 필요한 경우 빈 셀에 바로 계산할 수도 있습니다.

=SUM(FILTER(B2:B7,A2:A7=D2))

각 수량을 확인하거나 재사용하려면 배열이 표시되는 방법을 선택하세요. 워크시트에 최종 합계만 필요하다면 중첩 수식을 선택하세요.

각 수량에 공통 계수 곱하기

예시에서 일치하는 각 수량을 세 배로 늘린다고 가정해 보겠습니다. I1에 복사본을 입력하고 I2에 숫자 3을 입력하세요. J1에 조정된 수량을 입력한 다음 J2에 다음 수식을 입력하세요.

=F2#*$I$2

J2:J5에서 예상되는 결과는 12, 6, 0, 12입니다. Microsoft는 값을 곱해 여러 결과를 반환하는 배열 계산을 설명하며, Mac 계산기 안내에서는 별표를 곱셈 연산자로 소개합니다. 배열 수식 지침 및 예제; Excel for Mac에서 Excel을 계산기로 사용하기.

조정된 합계 하나만 필요하다면 H4에 다음을 입력하세요.

=SUM(F2#*$I$2)

예상 합계는 30입니다. 이는 제공된 합성 데이터를 사용한 예시 계산입니다. 산술 결과는 별도로 확인했으며, 통합 문서 수식은 Excel for Mac에서 실행하는 대신 Microsoft 문서와 대조해 검토했습니다.

일치 항목이 없을 때의 동작 정하기

D2을 샘플에 없는 레이블인 Wave D로 변경하세요. 두 인수로 구성된 기존 FILTER 수식은 Excel이 빈 배열을 반환할 수 없어 #CALC!를 반환합니다. Microsoft는 세 번째 인수를 선택 사항으로 두어 결과가 없을 때 반환할 값을 지정하는 방법을 설명합니다. #CALC! 오류 수정 방법.

다음 계산에 필요한 값에 따라 그 값을 정하세요. 다음 판단 가이드는 이 워크시트에 적용됩니다.

요구 사항 수식 또는 방법 결과
계산 중 일치 항목이 없다는 사실을 드러내야 함 =FILTER(B2:B7,A2:A7=D2) 유지 일치 항목이 없으면 #CALC!가 발생해 확인이 필요하다는 것을 알립니다.
주로 읽기 쉬운 목록이 필요함 =FILTER(B2:B7,A2:A7=D2,"No matches") 사용 일치 항목이 없으면 텍스트 메시지를 반환합니다.
없는 레이블은 합계에 0으로 반영되어야 함 =SUM(FILTER(B2:B7,A2:A7=D2,0)) 사용 일치 항목이 없으면 합계가 0이 됩니다.

대체값으로 0을 사용하는 것은 명시적인 계산 규칙입니다. 수량이 0인 일치 원본 행이 존재한다는 뜻은 아닙니다. 이 샘플에서 Tile A에는 실제로 수량이 0인 행이 있지만 Wave D에는 원본 행이 없습니다. 에셋의 존재 여부가 중요하다면 이 차이를 유지하세요.

텍스트 메시지도 주의가 필요합니다. Microsoft는 SUM가 참조된 셀의 텍스트를 무시한다고 설명합니다. 따라서 No matches만 포함하는 결과를 더하면 0이 되어 일치 행이 없다는 사실이 감춰질 수 있습니다. 레이블이 없을 때 후속 계산을 중단해야 한다면 오류가 표시되는 방법을 사용하세요. SUM 함수.

원래 숫자 예제를 계속하려면 D2를 Tile A로 되돌리세요.

작업에 필요한 경우에만 두 번째 조건 추가하기

나중에 수량이 양수인 항목만 요청하는 작업이 있다면 레이블 검사와 양수 수량 검사를 모두 사용하세요.

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

샘플에서는 4, 2, 4을 반환합니다. Microsoft는 FILTER에서 조건 사이의 곱셈을 AND 검사로 설명합니다. 이 변형은 일치하는 0을 의도적으로 제외하므로, 일치하는 모든 수량이 필요하면 원래 수식을 유지하세요. FILTER 함수.

결과 문제 해결 및 새 행 포함하기

수식이 예상한 네 개의 수량을 반환하지 않으면 다음 순서로 워크시트를 확인하세요.

  1. 출력 공간을 확인하세요. 결과 범위가 막혀 있으면 #SPILL!이 발생합니다. 다시 시도하기 전에 스필 영역에 있는 필요한 내용을 옮기세요.
  2. 수식 위치를 확인하세요. 스필된 배열 수식은 Excel 표 바깥의 워크시트 격자에 있어야 합니다.
  3. 통합 문서의 종속성을 확인하세요. Microsoft는 두 통합 문서가 모두 열려 있을 때만 통합 문서 간 동적 배열 연결을 지원합니다. 원본을 닫은 뒤 새로 고치면 #REF!가 발생할 수 있습니다.

이러한 제한 사항은 동적 배열 수식 및 스필된 배열 동작에 설명되어 있습니다.

그다음 원본 레이블과 수량을 확인하세요. D2를 의도한 에셋 셀과 비교하고 수량을 숫자로 입력했는지 확인하세요. SUM은 텍스트 값을 무시할 수 있으므로 그럴듯한 합계만으로는 충분히 확인할 수 없습니다. SUM 함수.

샘플 수식은 원본 행 2부터 7까지를 참조합니다. 8행에 데이터를 추가하면 두 원본 범위를 모두 편집해 해당 행을 포함하세요. 예를 들어 A8에 Tile A를 입력하고 B8에 숫자 수량 3을 입력한 뒤 F2의 수식을 다음으로 바꾸세요.

=FILTER(B2:B8,A2:A8=D2)

예상 배열은 4, 2, 0, 4, 3가 되며 합계는 13이 됩니다. F2# 참조는 확장된 결과를 따르지만, 원래 원본 범위에서 빠진 행을 포함시키지는 못합니다.

목록이 자주 늘어난다면 Microsoft는 행을 추가하거나 제거할 때 구조화된 참조가 조정되는 Excel 표를 권장합니다. 스필 결과는 해당 표 바깥에 두세요. 동적 배열 수식 및 스필된 배열 동작.

합계에 의존하기 전에 개별 결과를 확인하세요. 원본 행 여섯 개에서 Tile A를 선택하면 4가 두 번, 0이 한 번 포함된 네 개의 수량이 반환되어야 합니다. 결과가 맞으면 계산에 F2#를 재사용하고 워크시트의 목적에 맞는 일치 항목 없음 규칙을 명시적으로 선택하세요.

출처