엑셀 환율 적용이 안 맞을 때: 주말 환율 #N/A와 원화금액 차이 잡는 실무 수식

해외매입 정산 파일에서 환율 적용이 안 맞거나 VLOOKUP #N/A가 뜨는 문제는 생각보다 자주 나옵니다. 특히 매입일이 토요일·일요일인데 환율표에는 평일 고시일만 들어 있는 경우, 원화금액이 0으로 나오거나 ERP 금액과 몇 천 원씩 차이가 납니다.

이번 예시는 실제 정산 파일처럼 매입일 텍스트 정리 → 적용환율일 찾기 → 환율 가져오기 → 원화금액 계산 → 월별 검산 순서로 잡아보겠습니다. 낯선 기능으로 한 번에 끝내기보다, SUMIFS, COUNTIFS, VLOOKUP, INDEX, MATCH, IF, IFERROR, LEFT, RIGHT, MID, TEXT, DATE, EOMONTH, ROUND처럼 실무에서 자주 쓰는 함수 조합으로 풀어가겠습니다.

해외매입 내역에서 주말 날짜 때문에 환율 VLOOKUP이 안 되는 상황

시트 이름은 매입정산이라고 가정하겠습니다. A열부터 I열까지는 해외매입 내역이고, J열부터 N열까지는 환율표입니다. 데이터는 2행부터 시작하며, 실제 파일에서는 5000행 정도까지 내려간다고 보면 됩니다.

예시 데이터는 아래처럼 들어 있다고 보겠습니다.

환율표는 J열부터 N열에 둡니다. J열은 통화, K열은 고시일, L열은 매매기준율, M열은 조회키, N열은 조회환율입니다.

원인은 대부분 날짜 형식, 주말 고시일, 조회키 불일치입니다

환율표에는 보통 은행 영업일 기준 환율만 있습니다. 그런데 매입일은 토요일, 일요일도 들어올 수 있습니다. 이 상태에서 VLOOKUP(D2&TEXT(B2,"yyyymmdd"),...)처럼 정확히 같은 날짜만 찾으면 주말 행은 당연히 못 찾습니다.

또 하나는 날짜가 겉으로는 2026-07-04처럼 보여도 실제로는 텍스트인 경우입니다. 텍스트 날짜와 실제 날짜는 표시가 같아도 SUMIFS, COUNTIFS, VLOOKUP에서 다르게 취급될 수 있습니다. 그래서 먼저 A열의 매입일원본을 B열에서 날짜값으로 정리해야 합니다.

매입일 텍스트를 DATE, LEFT, MID, RIGHT로 실제 날짜로 바꾸기

A2에 2026-07-04처럼 하이픈이 포함된 텍스트 날짜가 들어 있다면, B2에 아래 수식을 넣고 B5000까지 복사합니다. 자기 파일에서 원본 날짜 열이 다르면 A2만 바꾸면 됩니다.

=IFERROR(DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)),A2)

이 수식은 A2의 앞 4자리는 연도, 가운데 2자리는 월, 오른쪽 2자리는 일로 잘라서 DATE 함수로 다시 조립합니다. 날짜가 이미 정상 날짜라서 변환이 필요 없는 경우에는 IFERROR가 A2 값을 그대로 돌려줍니다.

수식을 넣은 뒤 B열 표시 형식을 yyyy-mm-dd로 맞춰두면 확인이 편합니다. B2:B5000 범위를 선택하고 셀 서식에서 날짜 표시를 통일해 주세요.

COUNTIFS로 주말 매입일을 직전 환율 고시일로 돌리기

핵심은 F열의 적용환율일입니다. 매입일 당일 환율이 있으면 당일을 쓰고, 없으면 전일, 전전일 순서로 환율표에 존재하는 날짜를 찾습니다. 보통 주말은 2~3일만 밀리지만, 휴일이 이어질 수 있어 여기서는 5일 전까지 확인하도록 만들겠습니다.

F2에 아래 수식을 입력한 뒤 F5000까지 복사합니다. 환율표 범위가 더 길다면 $J$2:$J$1000, $K$2:$K$1000 부분을 실제 마지막 행에 맞게 늘리면 됩니다.

=IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2)>0,B2,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-1)>0,B2-1,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-2)>0,B2-2,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-3)>0,B2-3,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-4)>0,B2-4,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-5)>0,B2-5,"환율일 없음"))))))

예를 들어 B3 매입일이 2026-07-04 토요일이고, J:K 환율표에 JPY 2026-07-04가 없다면 B3-1인 2026-07-03을 확인합니다. 2026-07-03에 JPY 환율이 있으면 F3에는 2026-07-03이 표시됩니다.

이 방식의 장점은 원인을 눈으로 확인하기 쉽다는 점입니다. F열만 봐도 어떤 행이 당일 환율을 썼고, 어떤 행이 직전 영업일 환율을 썼는지 바로 보입니다.

VLOOKUP용 조회키를 TEXT로 통일해서 환율 가져오기

이제 환율표에 조회키를 만들어 줍니다. M2에는 통화와 고시일을 붙인 값을 만들고, N2에는 실제 환율을 그대로 가져오겠습니다.

M2: =J2&TEXT(K2,"yyyymmdd")

N2: =L2

M2:N2 수식을 환율표 마지막 행까지 복사합니다. 이렇게 해두면 USD와 2026-07-03이 USD20260703이라는 하나의 조회키가 됩니다. 날짜 표시가 2026-7-3처럼 보여도 TEXT 함수가 항상 8자리 날짜로 맞춰주기 때문에 조회 오류가 줄어듭니다.

매입 내역의 G2에는 아래 수식을 넣습니다. D2의 통화와 F2의 적용환율일을 붙여 환율표 M:N에서 환율을 찾아옵니다.

=IFERROR(VLOOKUP(D2&TEXT(F2,"yyyymmdd"),$M$2:$N$1000,2,FALSE),"환율없음")

만약 회사 양식상 M열을 환율표 맨 앞쪽으로 옮기기 어렵다면 INDEX와 MATCH 조합을 써도 됩니다. 같은 결과를 내는 수식입니다.

=IFERROR(INDEX($L$2:$L$1000,MATCH(D2&TEXT(F2,"yyyymmdd"),$M$2:$M$1000,0)),"환율없음")

이때 중요한 점은 INDEX 범위와 MATCH 범위의 시작 행이 같아야 한다는 것입니다. 위 수식은 둘 다 2행부터 시작하므로 환율 행이 어긋나지 않습니다.

ROUND로 원화금액을 계산하고 1원 차이를 줄이기

H2에는 원화금액을 계산합니다. 외화금액 E2에 환율 G2를 곱하고, 원 단위로 반올림하려면 ROUND를 사용합니다.

=IFERROR(ROUND(E2*G2,0),"계산불가")

여기서 ROUND의 마지막 인수 0은 소수점 없이 원 단위로 맞춘다는 뜻입니다. 회사 기준이 십 원 단위 반올림이면 -1, 백 원 단위 반올림이면 -2로 바꾸면 됩니다.

정산 차이가 1원씩 계속 난다면 ERP가 행별 반올림을 하는지, 거래처별 합계 후 반올림을 하는지 확인해야 합니다. 실무에서는 행별 원화금액을 먼저 ROUND 처리한 뒤 SUMIFS로 합계를 내는 경우가 많습니다.

오류체크 열을 만들어 환율 누락 행만 빠르게 걸러내기

I2에는 확인용 메시지를 넣습니다. 정산 파일이 길어질수록 눈으로 하나씩 보기 어렵기 때문에, 오류체크 열이 있으면 필터만 걸어서 문제 행을 바로 찾을 수 있습니다.

=IF(F2="환율일 없음","환율일 확인",IF(G2="환율없음","환율표 확인",IF(H2="계산불가","금액 확인","OK")))

I열이 OK가 아닌 행은 반드시 확인해야 합니다. 특히 통화 코드가 USD 처럼 뒤에 공백이 붙어 있거나, 환율표의 고시일이 텍스트로 들어온 경우가 많습니다.

SUMIFS와 COUNTIFS로 월별 합계가 맞는지 검산하기

이제 월별 검산표를 오른쪽에 만들어 보겠습니다. P2에는 기준월을 실제 날짜 2026-07-01로 입력하고, 표시 형식만 yyyy-mm으로 바꿉니다. Q2에는 거래처명, R2에는 원화매입액, S2에는 건수를 표시하겠습니다.

R2에는 거래처와 기준월, 오류체크 OK 조건을 모두 만족하는 원화금액만 합산합니다.

=SUMIFS($H$2:$H$5000,$C$2:$C$5000,Q2,$B$2:$B$5000,">="&P2,$B$2:$B$5000,"<="&EOMONTH(P2,0),$I$2:$I$5000,"OK")

S2에는 같은 조건의 건수를 세어봅니다.

=COUNTIFS($C$2:$C$5000,Q2,$B$2:$B$5000,">="&P2,$B$2:$B$5000,"<="&EOMONTH(P2,0),$I$2:$I$5000,"OK")

여기서 기준월 P2를 텍스트 2026-07로 넣으면 날짜 조건이 흔들릴 수 있습니다. 반드시 실제 날짜 2026-07-01을 넣고 표시만 월 형식으로 바꾸는 편이 안전합니다.

자주 틀리는 부분은 조회키보다 날짜와 통화 코드입니다

환율 수식이 맞는데도 계속 #N/A가 뜬다면 먼저 F열의 적용환율일이 날짜인지 확인합니다. 셀을 선택했을 때 수식 입력줄에 날짜처럼 보이더라도, 왼쪽 정렬된 텍스트일 수 있습니다.

다음은 통화 코드입니다. 매입 내역 D열에는 USD인데 환율표 J열에는 USD 처럼 공백이 붙어 있으면 COUNTIFS가 0을 반환합니다. 필터에서 통화별 개수를 확인하거나, 같은 통화끼리 복사해서 직접 비교해 보면 금방 찾을 수 있습니다.

또 하나 흔한 실수는 VLOOKUP 범위입니다. 조회키가 M열에 있는데 범위를 $J$2:$N$1000으로 잡으면 VLOOKUP은 J열에서 조회값을 찾으려고 합니다. 조회키를 기준으로 찾으려면 반드시 $M$2:$N$1000처럼 조회키가 범위의 첫 열에 오도록 잡아야 합니다.

반복 정산 파일에는 수식 복사 매크로를 붙여두면 편합니다

매월 새 파일을 받을 때마다 B열부터 I열까지 수식을 복사하는 작업이 반복된다면 간단한 VBA로 줄일 수 있습니다. 아래 코드는 매입정산 시트의 A열 마지막 행을 기준으로 B:I 수식을 2행부터 마지막 행까지 채우는 용도입니다.

실행 전에는 파일을 먼저 복사해 백업해 두세요. 매크로 실행으로 채워진 수식은 저장 전이라면 실행 취소가 안 될 수 있으므로, 잘못 실행했다면 저장하지 말고 파일을 닫은 뒤 백업본에서 다시 시작하는 방식이 가장 안전합니다.

Sub FillImportFxFormulas()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("매입정산")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "채울 데이터가 없습니다."
        Exit Sub
    End If

    ws.Range("B2:B" & lastRow).Formula = "=IFERROR(DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)),A2)"
    ws.Range("F2:F" & lastRow).Formula = "=IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2)>0,B2,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-1)>0,B2-1,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-2)>0,B2-2,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-3)>0,B2-3,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-4)>0,B2-4,IF(COUNTIFS($J$2:$J$1000,D2,$K$2:$K$1000,B2-5)>0,B2-5,""환율일 없음""))))))"
    ws.Range("G2:G" & lastRow).Formula = "=IFERROR(VLOOKUP(D2&TEXT(F2,""yyyymmdd""),$M$2:$N$1000,2,FALSE),""환율없음"")"
    ws.Range("H2:H" & lastRow).Formula = "=IFERROR(ROUND(E2*G2,0),""계산불가"")"
    ws.Range("I2:I" & lastRow).Formula = "=IF(F2=""환율일 없음"",""환율일 확인"",IF(G2=""환율없음"",""환율표 확인"",IF(H2=""계산불가"",""금액 확인"",""OK"")))"

    MsgBox "환율 정산 수식 채우기가 끝났습니다."
End Sub

이 코드는 환율표 범위를 J2:N1000으로 고정해 두었습니다. 환율표가 3000행까지 있다면 코드 안의 $J$2:$J$1000, $K$2:$K$1000, $M$2:$N$1000을 실제 범위에 맞게 바꿔야 합니다.

실무에서 적용할 때 볼 것

해외매입 환율 정산은 수식 자체보다 기준을 정하는 일이 더 중요합니다. 매입일 당일 환율이 없을 때 직전 영업일 환율을 쓰는지, 선적일이나 통관일 기준 환율을 쓰는지부터 먼저 확인해야 합니다.

기준이 정해졌다면 파일에서는 B열 날짜 정리, F열 적용환율일, G열 환율, H열 원화금액, I열 오류체크 순서로 나누어 두는 편이 좋습니다. 중간 열을 숨기지 말고 남겨두면 ERP 금액과 차이가 날 때 어느 지점에서 틀어졌는지 바로 추적할 수 있습니다.

마지막으로 월별 합계는 반드시 SUMIFS로 검산해 보세요. 개별 행의 VLOOKUP 오류는 한두 줄이라 눈에 안 띄지만, 월별 합계에서 거래처별 차이를 보면 문제 행을 훨씬 빨리 찾을 수 있습니다.