엑셀 파일을 검토하다 보면 결과는 보이지만 계산 과정을 이해하기 어려울 때가 있습니다.

특히 다른 시트의 표를 참조하거나 여러 함수가 연결된 수식은 오류가 발생한 위치를 찾는 데 시간이 걸립니다.

Arixcel 활용법을 익혀두면 복잡한 수식의 구성요소와 참조 위치를 단계별로 확인할 수 있습니다.

이번 실습은 판매내역을 집계한 뒤 담당자별 성과급을 계산하는 예제입니다.

판매내역과 성과급 기준 준비하기

실습 파일은 문제, 판매내역, 성과급기준, 실습, 정답, 해설, 확장문제까지 총 7개 시트로 구성했습니다.

판매내역에는 거래일, 담당자, 지역, 상품, 수량, 단가가 입력되어 있습니다.

매출액은 수량과 단가를 곱해 계산합니다.

=E2*F2

성과급기준 시트에는 C부터 S까지 네 개 등급을 입력합니다.

최소매출은 0원, 500만 원, 800만 원, 1,000만 원 순서입니다.

성과급률은 각각 2%, 3%, 5%, 7%입니다.

근사값 검색을 사용하므로 최소매출은 반드시 오름차순으로 정렬해야 합니다.

담당자별 매출과 등급 계산하기

먼저 SUMIFS로 담당자별 매출을 합산합니다.

=SUMIFS(판매내역!$G$2:$G$11,판매내역!$B$2:$B$11,A4)

다음으로 INDEX와 MATCH를 이용해 매출 구간에 맞는 등급을 찾습니다.

=INDEX(성과급기준!$A$2:$A$5,MATCH(B4,성과급기준!$B$2:$B$5,1))

MATCH의 마지막 인수 1은 매출액보다 작거나 같은 최소매출 중 가장 큰 값을 찾습니다.

등급에 해당하는 성과급률은 정확히 일치하는 방식으로 가져옵니다.

=INDEX(성과급기준!$C$2:$C$5,MATCH(C4,성과급기준!$A$2:$A$5,0))

성과급과 최종지급액도 계산합니다.

=B4*D4

=IF(B4>=5000000,B4+(B4*D4),B4)

계산 결과 김민수의 매출은 680만 원, 이서연은 690만 원입니다.

두 사람 모두 B등급과 3%의 성과급률이 적용됩니다.

박지훈의 매출은 330만 원이므로 성과급이 최종지급액에 포함되지 않습니다.

Arixcel로 수식 구조 추적하기

- Ctrl Q, Ctrl Backspace로 하나씩 오류추적 가능(2개만 기억)

정답 시트의 최종지급액 셀을 선택하고 Ctrl+Q를 누릅니다.

Arixcel Explorer가 열리면서 IF 조건과 참조 셀이 트리 형태로 표시됩니다.

먼저 매출합계가 500만 원 이상인지 확인합니다.

이어서 성과급률이 어느 셀에서 가져온 값인지 추적합니다.

INDEX와 MATCH를 펼치면 검색값, 검색 범위, 반환 범위를 각각 확인할 수 있습니다.

참조 항목을 선택하면 판매내역이나 성과급기준 시트의 해당 위치로 바로 이동합니다.

Ctrl+Backspace를 누르면 처음 선택했던 셀로 돌아올 수 있습니다.

잘못된 참조 범위 찾아보기

확장문제 시트에는 일부러 잘못된 수식을 넣었습니다.

성과급률을 가져와야 하지만 최소매출 범위를 반환하도록 설정한 상태입니다.

=INDEX(성과급기준!$B$2:$B$5,MATCH(C10,성과급기준!$A$2:$A$5,0))

성과급률 자리에 500만 원 같은 큰 숫자가 표시되면서 최종지급액도 비정상적으로 커집니다.

해당 셀에서 Ctrl+Q를 누르면 INDEX의 반환 범위가 B열로 잘못 지정된 것을 확인할 수 있습니다.

이를 성과급률이 있는 C열로 수정하면 정상 결과가 나옵니다.

일일히 보셔야 합니다

Arixcel 활용법의 핵심은 수식을 대신 작성하는 것이 아닙니다.

여러 함수와 시트가 연결된 계산 과정을 눈으로 확인하고, 잘못된 참조를 빠르게 찾는 데 있습니다.

복잡한 수식을 처음부터 다시 읽기보다 결과가 의심되는 셀을 선택하고 Ctrl+Q부터 눌러보는 방식으로 활용할 수 있습니다.

매주 일요일 Arixcel 공유드리며, 목/일 파이썬/엑셀/스프레드시트 관련 뉴스레터 업로드 드립니다