XLOOKUP SUMPRODUCT 조합으로 곱하기·나누기·빼기 계산 즉시 적용하기
XLOOKUP SUMPRODUCT 조합은 초보자도 실무에서 바로 써먹을 수 있는 최강 콤보입니다. 이 글에서는 품목별 단가×수량(곱), 통화 환산(나눗셈/곱), 반품 차감(빼기), 가중평균까지 한 번에 끝내는 공식을 순서대로 제공합니다. 아래 Quick Fix만 따라 해도 즉시 합계가 나오며, 이어서 원리와 실무 예제를 통해 확실히 이해하도록 구성했습니다.
Quick Fix: 복붙해서 바로 쓰는 4가지 공식
-
품목별 금액 합계(단가×수량)
=SUMPRODUCT( XLOOKUP( A2:A10, 품목표[품목], 품목표[단가] ) * B2:B10 )의미: 주문 목록의 품목(A열)에 맞는 단가를 품목표에서 찾아와 수량(B열)과 곱한 뒤 전부 더합니다.
-
통화 환산 합계(외화 금액→원화)
=SUMPRODUCT( A2:A10 * XLOOKUP( 통화코드, 환율표[코드], 환율표[매매기준율] ) )의미: 외화 금액(A열)에 통화별 환율을 곱해 원화 합계를 구합니다. (원화→외화는
/로 바꾸면 됨) -
반품 차감 순매출
=SUMPRODUCT( XLOOKUP( 주문품목, 품목표[품목], 품목표[단가] ) * 주문수량 ) - SUMPRODUCT( XLOOKUP( 반품품목, 품목표[품목], 품목표[단가] ) * 반품수량 )의미: 매출 합계에서 반품 금액을 빼서 순매출을 냅니다.
-
가중평균 단가(총금액 ÷ 총수량)
=SUMPRODUCT( XLOOKUP( A2:A10, 품목표[품목], 품목표[단가] ) * B2:B10 ) / SUM(B2:B10)의미: 품목별 금액 합계를 총수량으로 나누어 가중평균 단가를 구합니다.
※ 위 수식은 동적 배열이 지원되는 최신 Excel(또는 Microsoft 365)에서 특히 깔끔하게 동작합니다. 표 이름/머리글은 예시이므로, 본인 데이터 범위에 맞게 바꾸세요.
왜 XLOOKUP + SUMPRODUCT 조합인가?
- XLOOKUP: 키(품목, 코드 등)로 원하는 값을 정확히 찾아옵니다(정확/근사, 앞/뒤 방향, 미발견 처리 등).
- SUMPRODUCT: 여러 배열을 요소별로 곱하거나(+빼기/나누기 조합), 조건을 가중치로 적용해 한 번에 합계를 계산합니다.
- 둘을 조합하면 “찾아온 값들을 즉시 연산”하는 형태가 되어, 보조열 없이 한 셀에서 끝낼 수 있습니다.
샘플 데이터(그대로 따라 하세요)
1) 주문표(범위 A1:C10, 표 이름: 주문)
| 품목 | 수량 | 통화 |
|---|---|---|
| AA01 | 3 | USD |
| AA02 | 5 | USD |
| BB10 | 2 | EUR |
| CC90 | 1 | JPY |
| AA01 | 4 | USD |
| BB10 | 2 | EUR |
| CC90 | 7 | JPY |
| AA02 | 1 | USD |
2) 품목표(범위 E1:G6, 표 이름: 품목표)
| 품목 | 단가(외화) | 통화 |
|---|---|---|
| AA01 | 12 | USD |
| AA02 | 20 | USD |
| BB10 | 9 | EUR |
| CC90 | 500 | JPY |
3) 환율표(범위 I1:J4, 표 이름: 환율표)
| 코드 | 매매기준율 |
|---|---|
| USD | 1350 |
| EUR | 1450 |
| JPY | 9.1 |
실무 4대 패턴 — 공식과 원리
① 단가×수량 합계(외화 금액)
=SUMPRODUCT(
XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] )
* 주문[수량]
)원리: XLOOKUP이 각 주문 품목의 외화 단가 배열을 반환 → 수량과 요소별 곱 → SUMPRODUCT가 전체 합산.
② 원화 환산 합계(외화→원화)
=SUMPRODUCT(
XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] )
* 주문[수량]
* XLOOKUP( 주문[통화], 환율표[코드], 환율표[매매기준율] )
)원리: (외화단가 × 수량) 에 통화별 환율을 요소별로 곱해 원화 금액으로 변환한 뒤 합산.
역방향: 원화→외화 환산이면 마지막을 / XLOOKUP(...환율...)로 바꾸세요.
③ 반품 차감 순매출
반품표(예: K1:L5, “반품”), 컬럼은 품목, 반품수량이라 가정.
=SUMPRODUCT( XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] ) * 주문[수량] )
- SUMPRODUCT( XLOOKUP( 반품[품목], 품목표[품목], 품목표[단가(외화)] ) * 반품[반품수량] )원리: 매출 합계에서 반품 금액(단가×반품수량) 합계를 빼줍니다. 필요 시 환율 곱/나눗셈을 동일하게 적용하면 됩니다.
④ 가중평균 단가
=SUMPRODUCT(
XLOOKUP( 주문[품목], 품목표[품목], 품목표[단가(외화)] ) * 주문[수량]
) / SUM(주문[수량])원리: 총금액 ÷ 총수량. 환율을 포함한 원화 기준 가중평균이면 분자에 환율 항을 함께 곱해 주세요.
다중조건 XLOOKUP: 브랜드 + 사이즈 등
XLOOKUP은 조건을 곱해서(부울배열) “참인 행=1”을 찾아가는 패턴이 안전합니다.
=XLOOKUP(
1,
(상품표[브랜드]=H2) * (상품표[사이즈]=H3),
상품표[단가]
)위 반환 배열을 SUMPRODUCT에 넣으면, 다중조건 단가로 곱/나눗셈/빼기를 그대로 확장할 수 있습니다.
자주 하는 실수 & 체크리스트
- 머리글 오타: 표 머리글은 정확히 일치해야 합니다. 구조적 참조(예:
주문[수량])를 습관화하세요. - 통화코드 누락: 환율표에 없는 코드가 있으면
#N/A. XLOOKUP의 미발견 인수로 기본값을 지정하세요. - 데이터 유형: 숫자/텍스트 혼합 시 곱셈이 0이 되거나 오류가 납니다. 필요 시
--로 강제 숫자화. - 범위 길이 불일치: SUMPRODUCT의 배열 길이는 반드시 동일해야 합니다.
트러블슈팅
| 증상 | 원인 | 해결법 |
|---|---|---|
| #N/A 발생 | 조회 키 불일치, 공백/대소문자/숨은 문자 | TRIM/CLEAN로 정리, XLOOKUP의 미발견 인수로 기본값 처리 |
| 합계가 0 또는 너무 큼 | 배열 길이 다름, 텍스트 숫자 | 표로 정리해 구조적 참조 사용, --로 숫자 강제 |
| 통화 환산이 뒤섞임 | 환율 매칭 기준 오류 | 주문[통화]와 환율표[코드] 정확 매칭 확인 |
| 다중조건에서 오작동 | 곱한 조건식 중 하나가 전체 FALSE | 조건 범위/셀 참조 재확인, 보조 셀로 각 조건 TRUE/FALSE 점검 |
대체/응용: SUM/LET/TAKE와 조합
- SUM vs SUMPRODUCT: 단순 합만 필요하면
SUM(XLOOKUP(...)*수량)도 동작(동적배열)하지만, 조건 가중/여러 배열 결합은 SUMPRODUCT가 안정적. - LET: 반복 호출을 변수로 묶어 계산 속도/가독성 개선.
- 가중 평균 원가: 입고(수량·원가) 테이블에도 동일 패턴 적용.
관련 글
외부 출처(권위 문서)
- Microsoft Support — XLOOKUP 함수
- Microsoft Support — SUMPRODUCT 함수
맺음말 — 오늘 소개한 XLOOKUP SUMPRODUCT 4대 패턴만 익히면, 단가×수량, 통화 환산, 반품 차감, 가중평균까지 보조열 없이 한 셀에서 끝납니다. 실무 파일에 바로 붙여 사용해 보세요. 더 깊게 배우고 싶다면 위 관련 글을 추천합니다!