메뉴
실무 위키›응용 공식›두 정산표 금액 차이 찾기 공식

두 정산표 금액 차이 찾기 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

월말 대사에서 주문번호별 금액과 누락을 확인합니다.

예제파일 다운로드
두 정산표 금액 차이 찾기 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=장부 금액 - IFERROR ( VLOOKUP ( 주문번호, 정산표, 2, 0 ), 0 )
=IF ( ISNA ( VLOOKUP ( 주문번호, 정산표, 2, 0 ) ), "상대 표 없음", IF ( 장부 금액 - VLOOKUP ( 주문번호, 정산표, 2, 0 ) = 0, "일치", "차이 확인" ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
주문번호필수양쪽 표에서 같은 거래를 연결할 현재 행의 주문번호 셀입니다. 숫자와 텍스트 형식, 앞뒤 공백까지 같아야 정확히 찾을 수 있습니다.
장부 금액필수정산 금액과 비교할 우리 장부의 현재 행 금액 셀입니다. 차액은 장부 금액에서 정산 금액을 빼 계산합니다.
정산표필수첫 번째 열에 주문번호, 두 번째 열에 금액을 둔 정산 내역 범위입니다. 마지막 인수 0은 완전히 같은 주문번호를 찾는 고정값입니다. VLOOKUP 함수는 첫 번째 일치값만 반환하므로 주문번호는 중복 없이 입력합니다.
"상대 표 없음"필수정산표에서 주문번호를 찾지 못했을 때 반환할 문구이며 이 공식에서는 이 값으로 고정입니다.
"일치"필수두 표의 금액 차이가 0일 때 반환할 문구이며 이 공식에서는 이 값으로 고정입니다.
"차이 확인"필수두 표의 금액 차이가 0이 아닐 때 반환할 문구이며 이 공식에서는 이 값으로 고정입니다.
C2=B2-IFERROR(VLOOKUP(A2,$F$2:$G$8,2,0),0)
D2=IF(ISNA(VLOOKUP(A2,$F$2:$G$8,2,0)),"상대 표 없음",IF(B2-VLOOKUP(A2,$F$2:$G$8,2,0)=0,"일치","차이 확인"))
A
B
C
D
E
F
G
H
1
주문번호
금액
차액
판정
주문번호
금액
2
ORD-2608-006
1,580,000
30,000
차이 확인
ORD-2608-006
1,550,000
3
ORD-2608-005
430,000
430,000
상대 표 없음
ORD-2608-003
2,100,000
4
ORD-2608-008
760,000
-40,000
차이 확인
ORD-2608-001
1,250,000
5
ORD-2608-001
1,250,000
0
일치
ORD-2608-008
800,000
6
ORD-2608-002
840,000
0
일치
ORD-2608-002
840,000
7
ORD-2608-003
2,100,000
0
일치
ORD-2608-007
920,000
8
ORD-2608-004
675,000
0
일치
ORD-2608-004
675,000
9
ORD-2608-007
920,000
0
일치
10
C열 차액은 우리 장부 금액에서 정산 금액을 뺀 값입니다. 양수는 장부 금액이 더 크고 음수는 정산 금액이 더 크다는 뜻입니다. 정산표에 주문번호가 없으면 조회 결과를 0으로 바꿔 차액을 계산하지만, D열에는 '상대 표 없음'을 표시해 금액 차이와 구분합니다.
이 공식이 사용하는 함수
VLOOKUP 함수 IFERROR 함수 IF 함수 ISNA 함수

동작 원리

VLOOKUP 함수로 같은 주문번호의 정산 금액을 가져오고, IFERROR 함수가 없는 주문의 조회 결과를 0으로 바꿔 차액을 계산한 뒤 ISNA 함수와 IF 함수가 누락·일치·금액 차이를 구분합니다.

예제 시트에서 정산 금액이 다른 ORD-2608-006의 C2와 D2 셀, 정산 내역에 없는 ORD-2608-005의 D3 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

정산표에서 같은 주문번호의 금액을 찾습니다

A2의 주문번호와 같은 ORD-2608-006을 정산표에서 찾습니다. VLOOKUP 함수가 해당 행의 금액 1,550,000원을 반환합니다.

=VLOOKUP ( A2, $F$2:$G$8, 2, 0 )
= 1,550,000
2

장부 금액에서 정산 금액을 뺍니다

우리 장부의 1,580,000원에서 조회한 정산 금액 1,550,000원을 빼면 차액은 30,000원입니다.

=B2 - IFERROR ( VLOOKUP ( A2, $F$2:$G$8, 2, 0 ), 0 )
= 30,000
3

차액이 0이 아니면 확인 대상으로 표시합니다

ORD-2608-006은 정산표에 있고 차액 30,000원이 0이 아니므로 D2에 '차이 확인'을 반환합니다.

=IF ( ISNA ( VLOOKUP ( A2, $F$2:$G$8, 2, 0 ) ), "상대 표 없음", IF ( B2 - VLOOKUP ( A2, $F$2:$G$8, 2, 0 ) = 0, "일치", "차이 확인" ) )
= 차이 확인
4

정산표에 없는 주문을 따로 표시합니다

정산표에는 ORD-2608-005가 없어 VLOOKUP 함수가 오류를 반환하고 ISNA 함수의 결과가 TRUE가 됩니다. 바깥쪽 IF 함수가 D3에 '상대 표 없음'을 반환해 금액 차이와 구분합니다.

=IF ( ISNA ( VLOOKUP ( A3, $F$2:$G$8, 2, 0 ) ), "상대 표 없음", IF ( B3 - VLOOKUP ( A3, $F$2:$G$8, 2, 0 ) = 0, "일치", "차이 확인" ) )
= 상대 표 없음 차액 C3의 430,000원은 조회 실패를 0으로 바꿔 계산한 값이므로 판정과 함께 확인합니다.
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료