IFNA를 스필 조회 수식에 적용해 #N/A 결과를 두 번째 참조 표의 값으로 채웁니다. 이 Excel for Mac 예제는 아트 자산 레이블을 일치시키고, 기본 표에서 이미 찾은 수량은 유지하며, 두 표 모두에 없는 레이블은 명확하게 표시합니다.

이 방식을 지원하는 Excel for Mac 버전

Excel for Microsoft 365 for Mac, Excel 2024 for Mac 또는 Excel 2021 for Mac을 사용하세요. Microsoft는 이 에디션들을 XLOOKUP과 IFNA 모두에 대해 나열합니다. XLOOKUP 문서에서는 해당 에디션들이 페이지의 더 넓은 적용 가능성 목록에 나타나는데도 Excel 2016과 Excel 2019는 명시적으로 제외합니다. 아래 수식은 지원되는 Mac 에디션을 대상으로 합니다. XLOOKUP 함수, IFNA 함수

작업은 구체적입니다. 요청한 자산 레이블이 한 열에 있고, 각 레이블마다 수량 하나를 원합니다. 기본 참조를 우선 적용하고, 기본 조회에서 #N/A가 나오는 경우에만 두 번째 참조에서 수량을 가져옵니다. 아래의 모든 레이블과 수량은 예시용입니다.

두 참조 표와 요청 자산 목록 설정

통합 문서의 빈 워크시트에서 시작하세요. 수식의 참조를 바꾸지 않아도 되도록 아래 셀 위치를 사용하세요. 이 작은 참조 표는 일반 워크시트 범위이므로 Excel 표 개체로 변환할 필요가 없습니다.

기본 참조를 D1:E4에 입력합니다.

D열의 셀 자산 레이블 E열의 셀 수량
D1 자산 E1 수량
D2 나뭇잎 윤곽선 E2 8
D3 점 텍스처 E3 0
D4 타일 패턴 E4 12

두 번째 참조를 G1:H5에 입력합니다.

G열의 셀 자산 레이블 H열의 셀 수량
G1 자산 H1 수량
G2 점 텍스처 H2 9
G3 곡선 스케치 H3 5
G4 그레인 텍스처 H4 3
G5 나뭇잎 윤곽선 H5 10

J1에 요청 자산을 입력한 다음, J2:J6에 다음 다섯 레이블을 순서대로 셀마다 하나씩 입력합니다.

  1. 나뭇잎 윤곽선
  2. 점 텍스처
  3. 곡선 스케치
  4. 그레인 텍스처
  5. 아치 모티프

수량은 숫자 형식으로 유지하고 두 참조의 모든 수량 셀을 채우세요. 이 예제에서 0은 기록된 수량이 0이라는 뜻이며, 자산이 누락되었다는 뜻은 아닙니다.

레이블 스필 목록 만들기

A1, B1, C1에 각각 자산, 기본 수량, 완료 수량이라는 제목을 입력합니다. A2:C20은 비워 두고, A2에 다음 수식을 입력합니다.

=FILTER(J2:J20,J2:J20<>"","")

이 수식은 요청 목록의 빈 셀을 걸러냅니다. 제공된 데이터에서는 다섯 자산 레이블이 A2:A6에 스필됩니다. Microsoft는 위에 나열한 세 가지 Mac 에디션에서 FILTER을 지원한다고 설명하며, 조건에 맞는 행이 없을 때 마지막 인수로 결과를 지정할 수 있다고 안내합니다. 여기서는 그 결과가 빈 문자열입니다. FILTER 함수

이후 수식에서는 A2#을 사용해 이 스필 전체를 참조합니다. # 연산자는 스필 크기에 맞춰 작동하므로 현재 끝 셀을 A6처럼 고정해 입력할 필요가 없습니다. Microsoft는 이 동작을 스필 범위 연산자 문서에서 설명합니다.

두 번째 참조에서 누락된 수량 채우기

먼저 B2에 기본 조회 수식을 만듭니다.

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

이 수식은 기본 자산 열을 검색해 해당 수량을 반환합니다. XLOOKUP은 기본적으로 정확히 일치하는 항목을 찾으며, 일치 항목이 없고 대체 결과를 지정하지 않으면 #N/A를 반환합니다. XLOOKUP 함수

이 예제에서는 B2:B6에 8, 0, #N/A, #N/A, #N/A가 표시되어야 합니다. 다음 단계에서 사용할 수 있도록 일치 항목이 없을 때의 오류를 그대로 둡니다.

이제 C2에 보완 수식을 입력합니다.

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA는 기본 스필을 확인합니다. #N/A가 있는 위치에는 두 번째 조회의 해당 결과를 넣고, 나머지 기본 결과는 그대로 전달합니다. Microsoft는 첫 번째 인수가 배열 수식일 때 IFNA이 결과 배열을 반환한다고 명시합니다. IFNA 함수

예상되는 워크시트 결과는 다음과 같습니다.

A열의 자산 B열의 기본 수량 C열의 완료 수량
나뭇잎 윤곽선 8 8
점 텍스처 0 0
곡선 스케치 #N/A 5
그레인 텍스처 #N/A 3
아치 모티프 #N/A 목록에 없음

이 행을 우선순위 확인으로 읽으면 됩니다. 두 번째 참조에 10이 있어도 나뭇잎 윤곽선은 8로 유지됩니다. 두 번째 참조에 9가 있어도 점 텍스처는 0으로 유지됩니다. 곡선 스케치와 그레인 텍스처는 두 번째 참조의 수량을 가져옵니다. 아치 모티프는 어느 참조에도 없으므로 미해결 상태로 표시됩니다.

두 배열은 동일한 요청 자산을 동일한 순서로 나타내야 합니다. 이 설정에서는 둘 다 A2#에서 가져옵니다. 기존 통합 문서를 수정하는 경우, 한 순서로 정렬된 기본 결과와 순서가 다른 레이블 목록을 기반으로 한 대체 조회를 결합하지 마세요.

중간 열이 필요하지 않으면 수식 하나로 처리

기본 조회와 보완을 하나로 합치려면 C2의 수식을 다음 완성형 수식으로 바꿉니다.

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

이 수식은 같은 예제 데이터를 사용하며 완료 수량 다섯 개가 동일하게 나와야 합니다. 계산에는 더 이상 B열이 필요하지 않습니다. 통합 문서를 설정하는 동안 2단계 버전을 유지하면 각 결과가 어느 참조에서 나왔는지 더 쉽게 확인할 수 있습니다.

결과가 실제로 누락되었는지 판단

수식을 수정하기 전에 첫 번째 조회에서 무엇을 반환하는지 확인하세요. 레이블이 없는 경우, 빈 것처럼 보이는 결과, 수량 0은 각각 다르게 처리해야 합니다.

기본 결과 이 보완 수식의 동작 확인할 사항
0을 포함한 숫자 수량 그대로 유지 의도한 기본 수량인지 확인합니다.
#N/A 두 번째 조회 사용 이 오류가 레이블이 없음을 뜻하는지 확인합니다.
빈 문자열, "" 그대로 유지 기본 수식이 누락된 일치 항목을 숨기는지 확인합니다.
다른 오류 표시된 상태로 유지 근본 원인이 되는 수식이나 참조를 수정합니다.

예를 들어 기존 기본 조회에서 if_not_found 인수에 ""을 사용하면 IFNA은 그 빈 문자열을 #N/A로 인식할 수 없습니다. 기본 조회에서 해당 인수를 제거하세요. B2에 예시가 나와 있거나, 위의 통합 수식을 사용하면 됩니다. 보완 로직이 작동하려면 누락된 일치 항목을 알아볼 수 있는 결과로 남겨야 합니다. XLOOKUP 함수, IFNA 함수

참조 자체의 수량도 확인하세요. IFNA은 오류 값에 반응하며, 수량 셀이 채워졌는지는 검사하지 않습니다. 일치하는 기본 레코드의 수량이 미완성이라면 이 작업 흐름을 적용하기 전에 해당 레코드를 완성하거나 미완성 수량에 대한 별도 규칙을 정하세요.

한 가지 더 구분할 점이 있습니다. 기본 수량 데이터에 기존 #N/A 오류가 있으면 이 대체 처리도 작동합니다. 이 예제에서는 수량 열에 유효한 숫자만 있고, #N/A는 조회 실패로만 발생한다는 조건이 전제됩니다. 모든 대체 결과를 자산이 일치하지 않은 경우로 처리하기 전에 이 조건을 확인하세요.

우선순위를 확인하고 스필 유지하기

자산 레이블을 바꾸기 전에 예제의 다섯 행을 간단한 승인 기준으로 사용하세요. 이 행들은 기본 일치 항목, 유효한 0, 두 번째 참조의 일치 항목 두 개, 두 참조 어디에도 없는 레이블을 함께 확인합니다.

추가 확인을 위해 나뭇잎 윤곽선의 두 번째 참조 수량을 10에서 14로 임시 변경합니다. 완료 수량은 8으로 유지되어야 합니다. 그런 다음 곡선 스케치의 두 번째 참조 수량을 5에서 6로 바꿉니다. 이때 완료 수량은 6이 되어야 합니다. 이후 원래 값을 복원하세요. 이는 통합 문서에서 수행할 수 있는 예시 확인이며, 보고된 Excel 테스트 결과가 아닙니다.

각 참조에 자산 레이블당 레코드를 하나씩만 유지하세요. XLOOKUP은 보통 첫 번째 항목부터 검색해 처음 일치하는 항목을 반환하므로, 반복되는 레이블의 수량을 합산할 것으로 기대해서는 안 됩니다. 이 조회 방식을 사용하기 전에 중복 레이블의 의미를 정리하세요. XLOOKUP 함수

출력 범위가 막혔을 때 해결하기

각 스필 수식은 시작 셀에만 입력합니다. Excel이 인접한 셀을 자동으로 채우며, 스필 범위의 왼쪽 위 셀만 편집할 수 있습니다. 수식은 Excel 표 개체 밖에 입력하세요. Microsoft는 표를 원본 데이터로 사용하는 것은 지원하지만, 표 내부의 스필 수식은 지원하지 않습니다. 동적 배열 수식과 스필 배열 동작

#SPILL!이 표시되면 수식 셀을 선택해 출력 범위의 경계를 확인합니다. 사용 가능한 경우 오류 알림에서 Select Obstructing Cells 옵션을 사용하고, 결과를 막는 기존 항목을 옮깁니다. 병합된 셀도 스필을 막을 수 있으므로 출력 위치를 옮기거나 해당 셀의 병합을 해제하세요. #SPILL! 오류 해결 방법

입력 범위를 계획적으로 확장하기

요청 목록 수식은 J2:J20을 확인합니다. 이 범위 안에 추가한 레이블은 자동으로 포함되지만, 이 아래에 입력한 레이블을 포함하려면 수식 범위를 확장해야 합니다. 마찬가지로 참조 조회는 4행과 5행에서 끝나는 고정 범위를 사용합니다. 레코드를 추가할 때는 각 자산 범위와 이에 대응하는 수량 범위를 함께 확장하세요.

더 큰 워크시트에서는 구조화된 참조가 있는 Excel 표를 사용하면 행을 추가하거나 삭제할 때 원본 범위가 조정됩니다. 출력 수식은 해당 표 밖의 워크시트 셀에 두세요. 동적 배열 수식과 스필 배열 동작

이 예제의 입력 데이터는 같은 통합 문서에 두세요. Microsoft는 통합 문서 간 동적 배열 지원에 제한이 있다고 설명합니다. 연결된 동적 배열을 사용하려면 두 통합 문서를 모두 열어 두어야 하며, 원본을 닫은 뒤 새로 고치면 #REF!가 발생할 수 있습니다. 동적 배열 수식과 스필 배열 동작

예상한 행이 일치하면 완료 열을 자산 수량 결과로 사용하세요. 기본 우선 규칙을 유지하고, 의미 있는 0은 보존하며, 두 참조 어디에도 없는 레이블은 목록에 없음으로 표시해 참조 데이터를 필요한 만큼 정확히 보완하세요.

출처