엑셀 배송비 자동 계산 헷갈릴 때: 5만원 이상 무료배송 IF 함수 초보 공식

엑셀 배송비 자동 계산을 하려고 하면 생각보다 자주 헷갈립니다. 특히 “5만원 이상은 무료배송”, “제주·도서산간은 추가배송비”, “최종 결제금액은 상품금액+배송비”처럼 조건이 섞이면 어디 셀에 어떤 수식을 넣어야 하는지 막히는 경우가 많습니다.

이번 글은 초보자용으로, 실제 주문관리 파일에서 바로 만나는 상황을 기준으로 설명해 보겠습니다. 어려운 기능보다 IF, VLOOKUP, SUM처럼 자주 쓰는 기본 함수로 계산 흐름을 잡는 방식입니다.

Q. 예제 표는 어떻게 만들어야 하나요?

먼저 주문내역 표와 배송비 기준표를 나눠서 생각하면 훨씬 쉽습니다. 주문내역은 A4:I10 범위에 만들고, 배송비 기준은 L2와 L5:M9 범위에 둔다고 가정하겠습니다.

A열부터 I열까지는 실제 주문 건별로 계산되는 영역입니다. L열과 M열은 기준표라서 주문이 늘어나도 자주 바꾸지 않는 값입니다.

예제 데이터는 아래처럼 입력해 보겠습니다. 제목 행은 4행, 실제 데이터는 5행부터 10행까지입니다.

Q. 무료배송 기준은 어디에 적어야 하나요?

무료배송 기준 금액은 L2 셀에 입력해 두겠습니다. 예를 들어 L1에는 “무료배송 기준”, L2에는 50000을 입력합니다.

이렇게 기준값을 따로 빼두는 이유는 나중에 무료배송 기준이 5만원에서 7만원으로 바뀌어도 수식을 전부 고치지 않아도 되기 때문입니다. L2 값만 바꾸면 전체 배송비가 다시 계산됩니다.

여기서 중요한 개념이 하나 있습니다. 수식에서 L2를 계속 고정해서 보고 싶을 때는 $L$2처럼 달러 표시를 붙입니다. 이것을 절대참조라고 부릅니다.

초보자분들이 가장 많이 놓치는 부분이 바로 이 지점입니다. F5에 수식을 넣고 아래로 복사했는데 L2가 L3, L4처럼 밀려버리면 계산이 이상해집니다. 그래서 무료배송 기준 셀은 반드시 $L$2로 고정해 주는 것이 안전합니다.

Q. 5만원 이상이면 배송비 0원, 아니면 3,000원은 어떻게 계산하나요?

F5 셀에 기본배송비를 계산해 보겠습니다. E5 셀의 상품금액이 L2 셀의 무료배송 기준 이상이면 0원, 아니면 3,000원을 표시하는 방식입니다.

=IF(E5>=$L$2,0,3000)

이 수식을 F5에 입력한 뒤, F5 셀 오른쪽 아래 작은 네모를 잡고 F10까지 아래로 끌어내리면 됩니다. 이 작은 네모를 채우기 핸들이라고 부릅니다.

IF 함수는 “만약 ~라면 A, 아니면 B”라고 읽으면 됩니다. 위 수식은 “만약 E5가 $L$2 이상이라면 0, 아니면 3000”이라는 뜻입니다.

여기서 E5는 주문마다 달라져야 하므로 그냥 E5로 둡니다. 하지만 무료배송 기준인 L2는 항상 같은 셀을 봐야 하므로 $L$2로 고정합니다.

Q. 제주나 도서산간 추가배송비는 어떻게 붙이나요?

지역별 추가배송비는 VLOOKUP으로 가져오면 초보자도 이해하기 쉽습니다. L5:M9 범위에 아래와 같은 기준표를 만들어 보겠습니다.

이제 G5 셀에 아래 수식을 입력합니다. D5의 배송지역을 L5:M9 표에서 찾아서, 두 번째 열의 추가배송비를 가져오는 수식입니다.

=VLOOKUP(D5,$L$5:$M$9,2,FALSE)

수식 끝의 FALSE는 “정확히 같은 값을 찾아라”라는 뜻입니다. 배송지역처럼 서울은 서울, 제주는 제주로 정확히 맞아야 하는 경우에는 FALSE를 쓰는 습관을 들이는 것이 좋습니다.

수식을 G5에 넣고 G10까지 복사하면 각 주문의 지역별 추가배송비가 자동으로 들어갑니다.

Q. VLOOKUP을 넣었는데 #N/A 오류가 뜨는 이유는 뭔가요?

#N/A는 “찾는 값이 표에 없다”는 뜻으로 보면 됩니다. 예를 들어 주문내역 D열에는 “제주도”라고 되어 있는데 기준표에는 “제주”라고 되어 있으면 서로 다른 글자라서 찾지 못합니다.

또는 D열 값 뒤에 눈에 보이지 않는 공백이 붙어 있어도 문제가 생깁니다. “서울”과 “서울 ”은 사람 눈에는 비슷해 보여도 엑셀에서는 다른 값입니다.

초보자라면 먼저 아래 순서로 확인해 보세요.

• D열 배송지역과 L열 기준표의 지역명이 완전히 같은지 확인
• 오타가 없는지 확인
• 앞뒤 공백이 들어가 있지 않은지 확인
• VLOOKUP 범위가 $L$5:$M$9처럼 고정되어 있는지 확인
• 마지막 인수가 FALSE인지 확인

만약 기준표에 없는 지역이 들어올 수 있고, 오류 대신 0으로 표시하고 싶다면 G5 수식을 아래처럼 바꿀 수 있습니다.

=IFERROR(VLOOKUP(D5,$L$5:$M$9,2,FALSE),0)

IFERROR는 앞의 계산에서 오류가 나면 뒤의 값을 대신 보여주는 함수입니다. 위 수식은 “지역을 찾으면 추가배송비를 가져오고, 못 찾으면 0으로 표시”하라는 뜻입니다.

Q. 최종배송비와 결제금액은 어디에서 계산하나요?

기본배송비는 F열, 추가배송비는 G열에 계산되어 있습니다. 그러면 H열에서는 두 금액을 더해서 최종배송비를 만들면 됩니다.

H5 셀에 아래 수식을 입력합니다.

=SUM(F5:G5)

SUM은 합계를 구하는 함수입니다. F5:G5는 F5부터 G5까지, 즉 기본배송비와 추가배송비 두 칸을 더하라는 뜻입니다.

이제 I5 셀에는 상품금액과 최종배송비를 더해 결제금액을 계산합니다.

=SUM(E5,H5)

또는 아래처럼 더하기 기호를 써도 됩니다.

=E5+H5

둘 다 결과는 같습니다. 초보 단계에서는 SUM 함수와 더하기 기호를 모두 써 보면서, 범위를 더할 때는 SUM이 편하고 셀 두세 개만 더할 때는 +도 충분하다는 감각을 익히면 좋습니다.

Q. 5만원 이상인데 제주 추가배송비는 붙는 게 맞나요?

이 부분이 실무에서 은근히 많이 헷갈립니다. “무료배송”이라는 말이 기본배송비만 무료인지, 제주·도서산간 추가배송비까지 무료인지 회사 정책마다 다르기 때문입니다.

이번 예제에서는 5만원 이상이면 기본배송비 3,000원은 무료가 되지만, 제주나 도서산간 추가배송비는 별도로 붙는다고 가정했습니다. 그래서 상품금액이 65,000원인 제주 주문은 기본배송비는 0원이지만 추가배송비 3,000원이 붙습니다.

만약 회사 정책이 “5만원 이상이면 제주 추가배송비까지 모두 무료”라면 수식 구조가 달라져야 합니다. 이때는 H5 최종배송비에서 한 번에 IF로 판단할 수 있습니다.

=IF(E5>=$L$2,0,SUM(F5:G5))

하지만 이 수식은 5만원 이상일 때 모든 배송비가 0원이 됩니다. 따라서 적용 전에 반드시 사내 배송정책을 확인해야 합니다.

Q. 계산 결과가 숫자인데 금액처럼 보이게 하려면요?

계산은 되었는데 3000, 53000처럼 숫자만 보이면 가독성이 떨어집니다. 이럴 때는 셀 서식을 바꾸면 됩니다.

E열부터 I열까지 상품금액, 배송비, 결제금액 영역을 선택합니다. 예제에서는 E5:I10 범위를 드래그해서 선택하면 됩니다.

그다음 상단 메뉴에서 홈 탭 > 표시 형식 > 쉼표 스타일을 누르면 3,000처럼 천 단위 구분기호가 들어갑니다. 원화 기호까지 표시하고 싶다면 홈 탭 > 표시 형식 > 통화를 선택해도 됩니다.

주의할 점은 셀 서식은 “보이는 모양”만 바꾼다는 것입니다. 값 자체가 문자로 바뀌는 것이 아니므로 합계 계산에는 문제가 없습니다.

Q. 배송비 합계가 맞는지 빠르게 확인하려면요?

주문 건수가 몇 건 안 될 때는 눈으로 봐도 되지만, 실무 파일은 보통 수백 행 이상입니다. 이럴 때는 아래쪽에 합계 확인 셀을 만들어 두면 좋습니다.

예를 들어 H12 셀에 “배송비 합계”, I12 셀에 아래 수식을 넣습니다.

=SUM(H5:H10)

또 결제금액 전체 합계를 보고 싶다면 H13 셀에 “결제금액 합계”, I13 셀에 아래 수식을 넣습니다.

=SUM(I5:I10)

이렇게 해두면 주문이 추가될 때 배송비가 누락되었는지 빠르게 볼 수 있습니다. 특히 G열 추가배송비가 #N/A로 남아 있으면 H열과 I열 계산도 같이 이상해질 수 있으니, 오류가 보이면 먼저 G열부터 확인하는 것이 좋습니다.

Q. 실무에서 가장 흔한 실수는 무엇인가요?

첫 번째는 무료배송 기준 셀을 고정하지 않는 실수입니다. F5에 =IF(E5>=L2,0,3000)처럼 입력하고 아래로 복사하면, 다음 행에서는 L3을 보게 됩니다. L3이 비어 있으면 의도와 다른 계산이 나올 수 있습니다.

두 번째는 VLOOKUP 범위를 고정하지 않는 실수입니다. $L$5:$M$9가 아니라 L5:M9로 둔 상태에서 아래로 복사하면, 기준표 범위가 L6:M10, L7:M11처럼 밀립니다. 그러면 서울이나 제주가 표 범위에서 빠져 #N/A가 뜰 수 있습니다.

세 번째는 지역명이 기준표와 다르게 입력되는 실수입니다. 주문내역에는 “제주특별자치도”, 기준표에는 “제주”라고 되어 있으면 정확히 일치하지 않습니다. 초보자일수록 기준표에 있는 표현을 그대로 쓰는 것이 안전합니다.

네 번째는 상품금액이 숫자가 아니라 문자로 들어간 경우입니다. 예를 들어 E5에 42,000원이 문자로 들어가 있으면 IF 비교가 예상대로 되지 않을 수 있습니다. 셀을 클릭했을 때 왼쪽 위에 초록색 삼각형이 있거나, 숫자가 왼쪽 정렬되어 있다면 문자 숫자를 의심해 보세요.

Q. 주문이 10행보다 더 늘어나면 수식은 어떻게 하나요?

예제에서는 5행부터 10행까지만 사용했지만, 실제 파일에서는 11행, 12행으로 계속 주문이 늘어납니다. 이때는 F5:I5에 만든 수식을 새 행까지 복사하면 됩니다.

가장 쉬운 방법은 F5:I5 범위를 선택한 뒤, 오른쪽 아래 채우기 핸들을 아래로 끌어내리는 것입니다. 이미 F5:I10까지 수식이 있다면 F10:I10을 선택해서 아래로 복사해도 됩니다.

복사한 뒤에는 F열, G열, H열, I열이 모두 같은 행의 값을 보고 있는지 확인하세요. 예를 들어 11행이라면 F11 수식은 E11을 보고, G11 수식은 D11을 봐야 정상입니다.

Q. 배송비 상태를 “무료배송”, “배송비있음”으로 표시할 수도 있나요?

가능합니다. J열을 하나 추가해서 배송비 상태를 표시하면 상담팀이나 물류팀이 보기 편합니다.

J4 셀에 “배송상태”라고 입력하고, J5 셀에 아래 수식을 넣습니다.

=IF(H5=0,"무료배송","배송비있음")

이 수식은 최종배송비가 0원이면 “무료배송”, 아니면 “배송비있음”이라고 표시합니다. 여기서 기준은 H열의 최종배송비입니다.

만약 기본배송비만 무료인지 보고 싶다면 H5가 아니라 F5를 기준으로 바꿔야 합니다. 이처럼 어떤 셀을 기준으로 판단하는지에 따라 결과가 달라지므로, 수식 앞에서 먼저 “무엇을 기준으로 볼 것인가”를 정하는 습관이 중요합니다.

이럴 때 이렇게 쓰면 된다

상품금액 기준으로 무료배송만 판단하면 된다면 F열에 IF 함수 하나면 충분합니다. 기준 금액은 L2처럼 따로 빼두고, 수식에서는 $L$2로 고정해서 사용하면 됩니다.

지역별 추가배송비가 있다면 G열에서 VLOOKUP으로 기준표를 찾아오면 됩니다. 기준표 범위는 $L$5:$M$9처럼 고정하고, 정확히 일치해야 하므로 마지막에는 FALSE를 넣습니다.

최종배송비는 기본배송비와 추가배송비를 더하고, 결제금액은 상품금액과 최종배송비를 더하면 됩니다. 계산이 이상할 때는 무료배송 기준 셀 고정, VLOOKUP 범위 고정, 지역명 일치 여부, 상품금액이 숫자인지 순서대로 확인하면 대부분 해결됩니다.