엑셀 고급 스킬 실무: XLOOKUP·SUMIFS·피벗 테이블 검산

엑셀 고급 스킬을 준비할 때는 함수 이름을 많이 적는 것보다 원자료를 정리하고, 원하는 값을 계산한 뒤, 결과가 맞는지 설명하는 과정을 연습하는 것이 유용합니다. 이 글에서는 작은 가상 데이터를 사용해 조회·조건 합계·피벗 집계가 어떻게 연결되는지 확인합니다.

아래는 실제 기업의 실적이나 채용 과제가 아닌 연습 예제입니다. 필요한 기능은 지원 공고와 업무에 따라 정하고, 사용 중인 엑셀 버전에서 실행할 수 있는지도 먼저 확인하세요. 특정 기능을 익혔다고 취업이나 임금 상승이 보장되는 것은 아닙니다.

퇴사말고 취업기회 엑셀 고급 스킬

목차

1. 원자료와 검산 기준 만들기
2. XLOOKUP으로 코드에 맞는 이름 찾기
3. SUMIFS로 두 조건의 합계 계산하기
4. 피벗 테이블로 같은 결과 대조하기
5. Power Query와 제출 전 점검
6. 자주 묻는 질문

원자료와 검산 기준 만들기

연습용 접수 내역을 새 시트의 A1:E7에 입력합니다. 첫 행은 제목, 나머지 여섯 행은 데이터입니다. 금액은 원 단위의 숫자로 입력하고 “원” 문자를 셀 값에 붙이지 않습니다. 표의 금액은 설명용 가정이며 실제 가격이나 매출이 아닙니다.

A: 접수ID	B: 부서	C: 상태	D: 품목코드	E: 금액
S001	영업	완료	P01	120000
S002	운영	완료	P02	80000
S003	영업	대기	P01	50000
S004	영업	완료	P03	180000
S005	운영	취소	P02	30000
S006	운영	완료	P03	40000

검산 기준은 데이터 6건, 모든 상태를 포함한 금액 합계 500,000원입니다. 완료 상태만 집계하면 4건·420,000원입니다. 이 둘은 집계 범위가 다르므로, 표 제목에 조건을 적어 혼동을 막습니다. 접수ID는 이 예제에서는 한 행을 구분하는 값입니다. 같은 ID가 반복되면 단순 중복인지, 품목별 여러 행이 정상인지부터 확인해야 합니다.

• 제목은 한 행으로 만들고 열마다 서로 다른 이름을 붙입니다.
• 분석용 원자료 안의 병합 셀과 중간 합계 행을 피합니다.
• 금액 열은 숫자, 품목코드는 텍스트 등 열의 의미와 형식을 맞춥니다.
• “완료”와 “완료 ”처럼 뒤에 공백이 있는 값이 섞였는지 확인합니다.

XLOOKUP으로 코드에 맞는 이름 찾기

같은 시트의 H1:I4에는 다음 기준표를 입력합니다. D열 품목코드에 해당하는 이름을 F열에 가져오는 연습입니다. F1에는 “품목명”을 적습니다.

H: 품목코드	I: 품목명
P01	노트
P02	파일
P03	펜

F2에 아래 수식을 넣고 F7까지 복사합니다. 조회할 값은 D2이며, 기준표는 복사해도 움직이지 않도록 절대 참조를 사용합니다.

=XLOOKUP(D2,$H$2:$H$4,$I$2:$I$4,"미등록",0)

예상 결과는 위에서부터 노트, 파일, 노트, 펜, 파일, 펜입니다. 마지막 인수 0은 정확히 일치하는 값을 찾도록 명시한 것입니다. 기준표에 없는 코드는 “미등록”으로 표시합니다. Microsoft 안내상 XLOOKUP은 기본적으로 처음 일치하는 항목을 반환하므로, 기준표의 코드가 중복되어 있지 않은지도 확인해야 합니다.

Excel 2016과 Excel 2019에서는 XLOOKUP을 사용할 수 없습니다. 이 예제의 기준표는 코드가 왼쪽, 이름이 오른쪽이므로 해당 버전에서 다음 VLOOKUP 수식으로 정확히 일치하는 이름을 조회할 수 있습니다. 없는 코드의 오류 표시를 확인하고 처리 방식을 별도로 정하세요. Microsoft XLOOKUP 구문·버전 안내

=VLOOKUP(D2,$H$2:$I$4,2,FALSE)

품목명으로 바뀐 화면만 보지 말고, 첫 행과 마지막 행에서 조회 코드·기준표·반환값을 대조합니다. 이어서 원자료의 코드 하나를 연습용 미등록 코드로 바꾸어 예외 표시를 확인한 뒤 원래 값으로 되돌려보세요.

SUMIFS로 두 조건의 합계 계산하기

질문을 “영업 부서이면서 완료 상태인 금액의 합계는 얼마인가?”로 정합니다. 결과를 표시할 빈 셀에 아래 수식을 입력합니다. SUMIFS는 합계 범위를 먼저 쓰고, 조건 범위와 조건을 차례로 지정합니다. Microsoft 여러 조건 합계 안내

=SUMIFS($E$2:$E$7,$B$2:$B$7,"영업",$C$2:$C$7,"완료")

정답은 300,000원입니다. 조건을 만족하는 S001의 120,000원과 S004의 180,000원만 더합니다. S003은 영업 부서이지만 대기 상태이므로 제외됩니다.

검산 항목	포함 대상	예상 결과
전체 합계	S001~S006 전체	500,000원
완료 합계	S001, S002, S004, S006	420,000원
영업·완료 합계	S001, S004	300,000원
운영·완료 합계	S002, S006	120,000원

합계가 다르면 원자료 필터로 포함 행을 먼저 확인합니다. 상태의 철자·공백, 숫자로 저장된 금액, 참조 범위의 시작과 끝을 점검하세요. 위 수식은 2~7행만 다루는 고정 범위 예제이므로 새 행을 추가하면 범위도 함께 조정해야 합니다. 결과를 맞추려고 오류 셀을 모두 0으로 바꾸기보다 어떤 입력을 제외했는지 기록합니다.

✨ 실무에서 바로 통하는 엑셀 고급 기능

피벗 테이블로 같은 결과 대조하기

A1:E7을 원본으로 피벗 테이블을 만들고 행에 부서, 열에 상태, 값에 금액을 배치합니다. 값 요약 방식이 “합계”인지 확인하세요. Microsoft는 금액이 텍스트로 해석되면 합계 대신 개수로 표시될 수 있다고 안내합니다. Microsoft 피벗 테이블 만들기 안내

부서	완료	대기	취소	총합계
영업	300,000	50,000	0	350,000
운영	120,000	0	30,000	150,000
총합계	420,000	50,000	30,000	500,000

금액 단위는 원입니다. 위 표는 예상 결과를 읽기 쉽게 재배열한 것으로, 실제 피벗의 열 순서는 다를 수 있습니다. 해당 내역이 없는 조합은 표에서 0으로 표시했지만 엑셀에서는 표시 설정에 따라 빈칸으로 보일 수 있습니다.

피벗의 영업·완료 교차값 300,000원이 SUMIFS 결과와 같은지 비교합니다. 원본에 데이터가 추가되면 원본 범위와 새로고침 결과를 함께 확인하세요. Excel 표를 원본으로 쓰면 표에 추가한 행을 새로고침 시 포함시키기 편리합니다. 자동 새로고침 여부를 가정하지 말고, 실제 결과가 갱신됐는지 확인하는 것이 좋습니다.

Power Query와 제출 전 점검

같은 형식의 자료를 반복해서 가져오고 정리한다면 Power Query를 연습할 수 있습니다. 연결·변환·결합·로드 과정으로 데이터를 준비하며, 적용한 변환 단계는 쿼리에 기록됩니다. 새로고침하면 단계가 다시 실행됩니다. Microsoft Power Query 개요

1. 연습용 원본을 별도로 보관하고 필요한 표에 연결합니다.
2. 접수ID·코드·금액의 형식을 확인하고, 제외할 행의 조건을 정합니다.
3. 변환 결과를 시트에 로드하고 원자료와 건수·합계를 비교합니다.
4. 원본을 일부 바꾼 뒤 새로고침하여 어떤 행과 합계가 달라졌는지 확인합니다.

새로고침이 실행됐다고 원본 오류까지 자동으로 해결되는 것은 아닙니다. 열 이름, 파일 위치, 입력 형식이 바뀌면 결과를 다시 확인해야 합니다. 지원되는 연결과 편집 기능은 운영체제·버전에 따라 다를 수 있습니다. 특히 Microsoft 안내상 Excel 2016·2019 for Mac은 Power Query를 지원하지 않습니다.

포트폴리오에 남길 네 가지: 해결할 질문, 사용한 원자료의 범위, 수식·변환 과정, 검산 결과를 함께 적습니다. “엑셀 고급 가능”만 쓰기보다 “가상 접수 내역에서 영업·완료 합계 300,000원을 계산하고 피벗 결과와 대조했다”처럼 수행 범위를 보여줄 수 있습니다.

검산 근거를 정리할 때는 보고서 구조화와 근거 정리를, 작업 과정을 발표할 때는 경험을 잇는 제목·근거·전환 문장을 참고하세요. 제출 전에는 사용 환경, 계산 결과, 새로고침 시점과 설명용 데이터라는 표시를 함께 확인합니다.

자주 묻는 질문

XLOOKUP이 없으면 엑셀 고급 학습이 불가능한가요?

아닙니다. 지원 환경에서 사용할 수 있는 조회 방식으로 원리와 검산을 연습할 수 있습니다. 위 VLOOKUP 예제처럼 정확히 일치하는 조건을 명시하고, 다른 버전으로 전달했을 때도 결과가 맞는지 확인하세요.

수식과 피벗 결과가 같으면 검산이 끝난 건가요?

두 계산이 같은 잘못된 범위를 참조할 수도 있습니다. 원자료의 행 수와 상태별 대상, 제외한 행을 먼저 확인하고 합계와 대조해야 합니다.

VBA나 다른 분석 도구도 한꺼번에 배워야 하나요?

반복 작업의 종류와 목표 직무를 보고 우선순위를 정하세요. 이 예제는 VBA 없이 진행할 수 있습니다. 조회·집계·검산을 설명한 뒤 더 필요한 작업을 정하는 방식으로 학습 범위를 넓혀보세요.

이 예제를 실제 업무 성과로 소개해도 되나요?

연습용 가상 데이터 분석으로 소개해야 합니다. 실제 업무 개선 실적으로 바꾸거나 측정하지 않은 시간 절감률을 덧붙이지 마세요. 실제 자료를 쓸 때는 제출 가능한 범위를 확인하고 본인의 작업을 구분합니다.

학습 완료의 기준을 “함수 한 개를 외웠다”에서 “입력·조건·결과·예외를 설명하고 다시 계산할 수 있다”로 넓혀보세요. 작은 표에서 정확히 검산하는 연습을 마친 뒤 실제 업무 크기에 맞춰 확장할 수 있습니다.